AI Postgres Migration Review Stack for Subscriptions

An AI agent can help with Postgres migrations, but only if you stop it from acting like a wizard and force it into a narrow, auditable lane. For a subscription product, the real job is not "generate SQL." The job is to review one migration PR, estimate blast radius, catch lock risk, confirm the rollback path, and leave a human with enough evidence to approve or reject the change.

This guide is for a senior AI/full-stack engineer who owns a subscription billing or entitlement system and needs a repeatable way to review schema changes before they touch production. The budget is small, the privacy boundary is strict, and the downside of a bad migration is real: blocked writes, broken invoices, duplicate entitlements, or a table rewrite that turns a quick deploy into a midnight incident.

Courtesy: Supabase Studio screenshot captured from a local test project. Canonical public reference: Supabase database overview. The image shows the SQL-definition view the reviewer should inspect before approving a schema change.

Original diagram by Essa Mamdani. It is a conceptual workflow for stack selection, ecosystem integration, and context engineering. It is not a benchmark result or a production trace.

Why this niche deserves its own stack

Most AI/database guides are too broad. "AI for backend" usually means an overview of coding assistants, RAG, or generic SQL help. That is not what an on-call engineer needs when a migration PR is waiting in review and the app runs subscription state, dunning, invoices, and entitlements.

The narrow problem here is very specific:

1. A schema or migration change is already proposed in git.
2. The engineer needs to know whether it is safe to merge.
3. The review must respect production privacy, least privilege, and a rollback plan.
4. The result must be decision-ready, not just "looks good."

That makes this a good fit for the AI Stack Architecture & Operating Guide series. The article is not about database theory and not about generic AI tooling. It is about one concrete workflow: review a Postgres migration for a subscription system without letting the model become the authority.

If you want the surrounding stack context, the most useful adjacent posts on this site are the AI agent stack guide, Structured Outputs for Reliable AI APIs, AI Dev Containers for Reproducible Rust Debugging, and Setting Up a Robust Supabase Local Development Environment. They are close enough to reuse mentally, but none of them solves the migration-review decision itself.

Persona, risk, and privacy boundary

Recommended stack

Recommended stack: subscription migration review lane

Reader: a senior engineer reviewing one Postgres migration PR for a subscription app.

Job: determine whether the migration is safe, what it changes, what it might block, and what to test before production.

Model roles: one planning pass that converts the diff into a strict review packet, one reviewing pass that scores risk and missing evidence, and one deterministic executor that runs the SQL and CI checks.

Skills: postgres-drizzle for migration and schema hygiene, supabase-postgres-best-practices for query, schema, locking, and RLS review, brainstorming once at the start to pin the review scope, and find-skills only when you need a more specialized database or CI helper.

Plugins: none on the hot path. If your team already uses Slack, keep that connector draft-only for review summaries. If specs live in Notion or Google Drive, mount them read-only. Do not give the agent deploy, billing, or production database plugins.

MCP/tool categories: repository filesystem, read-only Supabase/Postgres inspection, docs/search for official database references, CI artifacts for review evidence, and optional local browser capture if a schema change affects UI copy or table rendering.

Memory: store only the durable facts that matter next time: the table names, the lock class, the rollback plan, the owner, and the date you approved or rejected the change.

Permissions: read-only on production data, write access only to the worktree and local migration files, no direct deploy, and a human approval gate before any production mutation.

Alternatives: a human DBA-only review if the team is small and changes are rare; a full automation gate if the schema is stable and the business risk is low; or a local-first open model if the data is too sensitive for a hosted reviewer.

The key design choice is to keep the model in a review role, not an execution role. The agent can summarize the migration, compare it against the modelled workload, and point out missing evidence. It should not be the thing that decides whether the deploy happens.

Layer 1: stack selection

Choose one primary model role and keep it narrow

OpenAI's current Structured Outputs documentation recommends gpt-5.6 for new projects, and it is explicit that schema adherence is stronger than "please return valid JSON." That makes it a reasonable default for this workflow if you want a model to produce a review packet, a risk memo, or a structured checklist.

For this use case, I would use the model in two passes:

1. Planner pass - turn the migration diff into a compact hypothesis list and a review plan.
2. Reviewer pass - score the migration against the plan, identify missing evidence, and recommend approve/revise/reject.

Do not let the model free-associate through the whole repo. Give it the migration file, the relevant schema files, one or two query plans, and a small amount of context about the subscription system. That is enough to be useful and small enough to audit.

Prefer a terminal-first interface

The interface should be terminal-first for the core loop. Postgres migration review is command-driven:

• supabase init
• supabase start
• npx drizzle-kit generate
• npx drizzle-kit migrate
• EXPLAIN (ANALYZE, BUFFERS)
• git diff

The terminal is where the evidence lives. A browser or editor assistant is optional, but the review should not depend on a chat surface that hides the actual SQL.

Keep the skills list short

The only reusable skills that materially improve this workflow are the two database-specific ones:

• postgres-drizzle for schema, relation, migration, and query hygiene.
• supabase-postgres-best-practices for query performance, connection management, security, locking, monitoring, and data access patterns.

brainstorming is useful at the start to turn "review this migration" into a specific checklist. find-skills is only the discovery step; it is not part of the runtime lane.

Treat plugins as optional, not foundational

There is no plugin required for the core review path. That is deliberate. The more a migration reviewer can do through the filesystem, the SQL client, and the CI logs, the less likely it is to leak policy-sensitive data through a general-purpose connector.

If your team insists on a collaboration plugin, use Slack only for draft review summaries and only after the AI has produced a structured memo. Keep it out of the approval path.

Layer 2: ecosystem integration

Anchor the review in the local Supabase stack

Supabase's current CLI docs describe the local workflow clearly: supabase init creates the project scaffolding, supabase start brings up the local stack, and the local environment exposes Studio, REST, GraphQL, Edge Functions, and a database URL. That is exactly the kind of reproducible boundary a migration reviewer needs.

For a subscription system, the local stack should contain synthetic data only:

• a few active subscriptions
• one canceled subscription
• one invoice in a failed state
• one entitlement edge case
• one backfill row that exercises nullability or uniqueness

That fixture set is enough to tell you whether a migration is harmless or dangerous. You do not need real customers to know whether a NOT NULL change or a default value rewrite is risky.

Use Drizzle as the migration contract if the schema lives in TypeScript

Drizzle Kit's current docs make the command split clear:

• generate creates SQL from schema changes
• migrate applies pending migrations
• push pushes schema directly and is for development, not production
• studio opens a browser for inspection

The important review rule is simple: never let "it works on my machine" become the deploy criterion. If the repo uses Drizzle, generate the migration, inspect the SQL, and then review the resulting diff as a production change. If the repo is Supabase-native, keep the same discipline with the SQL file and the local database snapshot.

Use Postgres evidence, not model intuition

The model should never be asked to infer lock risk from a code comment alone. Get the facts from Postgres itself:

• EXPLAIN tells you what the planner will do.
• EXPLAIN ANALYZE tells you what actually happened.
• BUFFERS helps you see whether a query is turning into a heavier read path than expected.
• The explicit locking docs tell you which operations take stronger locks than a casual reviewer might assume.

That matters for subscription systems because the tables that carry invoices, events, and entitlements are often the ones you least want to lock for long periods.

Keep review evidence as artifacts

The CI job should preserve the review packet as an artifact:

• the migration SQL
• the schema diff
• the query plan
• the rollback sketch
• the model's structured memo
• the human decision and timestamp

That artifact is not there to be pretty. It is there so the next engineer can read the exact reason the change was approved or rejected without rerunning the whole investigation.

Layer 3: context engineering and agent steering

Force the model to speak in structured output

This workflow works much better when the model is forced into a strict schema. OpenAI's Structured Outputs docs make the distinction explicit: JSON mode gives you valid JSON, but structured outputs are the feature that actually guarantees schema adherence.

Use that boundary for the review memo. For example:

1import OpenAI from 'openai';
2import { z } from 'zod';
3import { zodResponseFormat } from 'openai/helpers/zod';
4
5const RiskItem = z.object({
6  severity: z.enum(['low', 'medium', 'high', 'critical']),
7  issue: z.string(),
8  evidence: z.string(),
9  why_it_matters: z.string(),
10  fix: z.string(),
11});
12
13const MigrationReview = z.object({
14  verdict: z.enum(['approve', 'revise', 'reject']),
15  summary: z.string(),
16  risks: z.array(RiskItem).max(5),
17  missing_evidence: z.array(z.string()).max(5),
18  rollback_notes: z.array(z.string()).max(5),
19  human_checks: z.array(z.string()).max(5),
20});
21
22const client = new OpenAI();
23
24const review = await client.chat.completions.parse({
25  model: 'gpt-5.6',
26  messages: [
27    { role: 'system', content: 'Review the migration for a subscription database. Stay within the evidence packet.' },
28    { role: 'user', content: 'Here is the migration diff, the relevant schema, and the local EXPLAIN output.' },
29  ],
30  response_format: zodResponseFormat(MigrationReview, 'migration_review'),
31});

This is not about using a newer model for its own sake. It is about making the memo machine-readable so the CI gate, the human reviewer, and the later audit trail all agree on the same shape.

Build the evidence packet before you ask for a verdict

The packet should be small enough to inspect, but complete enough to answer the obvious questions. A good packet contains:

1. The migration file.
2. The affected schema files.
3. The top 1-3 queries that touch the changed tables.
4. The current row counts or rough table sizes.
5. One EXPLAIN ANALYZE for each hot path that could change.
6. Any RLS policy or permission change.
7. The rollback path.

If the packet is missing any of those, the model should say so. The reviewer is not there to fill in business logic that the author forgot to provide.

Keep memory concise and operational

The memory pattern should be short enough to stay useful:

• migration goal
• tables touched
• lock class
• backfill strategy
• rollback command
• human approver
• expiry date for any exception

Do not store raw customer data, SQL dumps, or free-form chat logs. The only memory worth keeping is the stuff that saves the next review from starting cold.

Set hard budget controls

The agent should not have unbounded context. A practical control set looks like this:

• planner pass: one evidence packet, one output memo, no extra browsing
• reviewer pass: one independent pass over the packet, no new assumptions
• max review turns: two, then escalate to a human
• max token budget: enough for one compact memo, not a novel

If the model needs more context than that, the answer is usually better instrumentation, not a larger prompt.

Operating workflow

1. Freeze the change request

Start by writing the change in plain English:

• what table is changing
• why it is changing
• what user flow depends on it
• what would break if it goes wrong

For a subscription system, that could be as concrete as "add a nullable grace_period_ends_at column and backfill existing active subscribers." The more specific the request, the easier it is to evaluate.

2. Generate the migration and inspect the SQL

Use the schema toolchain to generate the SQL, but do not apply it yet. Review the file as text first. Look for:

• full table rewrites
• unbounded backfills
• dropped defaults
• dangerous type conversions
• new indexes on very hot write paths
• changes that affect uniqueness or nullability

This is where postgres-drizzle earns its keep. It is much easier to review a generated migration when you already know the schema pattern it came from.

3. Run the local database and collect evidence

Bring up the local Supabase stack and run the smallest relevant commands against synthetic data. The question is not "does the migration file exist?" The question is "what will this do to the actual subscription tables and the queries that touch them?"

Use the local database to answer:

• How many rows will this touch?
• Does the backfill fit inside the maintenance window?
• Does the new index help the real query path?
• Does any constraint change collide with existing rows?
• Is the rollback path actually reversible?

4. Ask the planner model for a structured memo

Give the model the migration, the schema, the local outputs, and the release criteria. Ask it for:

• a verdict
• the top risks
• the missing evidence
• the rollback notes
• the human checks still required

Do not ask for "thoughts" or "analysis." Ask for the memo shape you actually need.

5. Run an independent reviewer pass

The second pass should not see the first pass's conclusion. It should see the same evidence and make its own call. This is the easiest way to catch prompt bias and overconfidence.

The second pass is there to ask uncomfortable questions:

• Did the author model the existing data correctly?
• Is there an unbounded lock?
• Is the unique constraint safe with current rows?
• Did the backfill rely on a false assumption about event ordering?

6. Require human approval before merge or deploy

The model can approve a memo. It cannot approve the release.

Human approval should check:

• the SQL
• the rollback path
• the evidence packet
• the risk memo
• any data model edge cases the model flagged

If the human cannot explain the migration back to you in one paragraph, the packet is not ready.

What to test before merge

For a subscription migration, the verification checklist should be boring and specific:

The most important thing is not the number of checks. It is the fact that the checks are tied to the business risk, not to an abstract CI green state.

Failure modes

The model summarizes the diff but misses the business meaning

This usually happens when the packet only contains the SQL and not the business context. A new column may look harmless until you realize it feeds proration, renewal, or a webhook consumer.

Fix: include the relevant user flow and one sentence about why the table matters.

The model underestimates lock risk

This happens when the review relies on intuition instead of Postgres evidence. A migration that seems small can still hold a table lock long enough to hurt a busy subscription workload.

Fix: always pair the migration with EXPLAIN, table size, and the explicit locking reference.

The model trusts a false assumption about existing rows

Subscription systems tend to accumulate edge cases: nulls, duplicates, canceled-but-not-closed accounts, and legacy values from old billing systems.

Fix: include representative fixtures and at least one bad row that forces the reviewer to reason about reality instead of ideal data.

The reviewer sees too much context

If you dump the entire repo into the model, it will spend tokens on trivia and still miss the risky edge.

Fix: cap the packet, give the model a schema, and make it ask for more only if it can justify the need.

The team over-automates approval

This is the most dangerous failure. A model can be a good reviewer and a terrible release authority.

Fix: hard-code the human gate. If the memo verdict is "approve," that still means "approve for human review," not "approve for production."

Internal links that fit naturally here

• AI Agent Stacks: Skills, Plugins, MCP, ACP, Prompts and Memory for the broader agent-stack architecture.
• Structured Outputs for Reliable AI APIs for the schema contract that keeps the memo typed.
• Setting Up a Robust Supabase Local Development Environment for the local database boundary.
• AI Dev Containers for Reproducible Rust Debugging for the reproducible environment pattern.
• OpenTelemetry GenAI Observability: A Production Guide if you want to instrument the review loop without leaking sensitive data.

Sources I checked

These are the current primary sources that shaped the stack and the checks:

• Supabase CLI docs for the local stack, supabase init, supabase start, and the current CLI workflow.
• Drizzle Kit migrations docs for generate, migrate, push, and studio.
• OpenAI Structured Outputs docs for the schema-first memo pattern and the current model guidance.
• PostgreSQL EXPLAIN docs for query-plan inspection and EXPLAIN ANALYZE.
• PostgreSQL explicit locking docs for table-level and row-level lock behavior.
• MCP architecture overview for the client/server boundary when a docs or filesystem tool is used.

FAQ

Is this stack meant to fully automate migration approval?

No. It is meant to make the review faster, more consistent, and more evidence-driven. The final approval stays with a human.

Why use Structured Outputs instead of plain JSON mode?

Because JSON mode only promises valid JSON. Structured Outputs promises adherence to the schema you defined, which is what you want for a risk memo or review packet.

Why keep the model out of the production database?

Because the reviewer should inspect evidence, not mutate live state. Production writes should stay with the deploy pipeline and the human owner.

What is the smallest useful evidence packet?

Usually the migration SQL, the affected schema, one or two hot queries, one EXPLAIN ANALYZE, the rollback path, and the release criteria. If you need more than that, the packet is probably missing the right data.

Should the reviewer be a frontier model or a small model?

Use a stronger model for the actual risk memo and a smaller or cheaper model only for summaries or formatting. The cost savings disappear quickly if the cheap model misses the real lock or data-risk issue.

Can I use this for non-billing tables?

Yes, but the economics change. The stricter the business impact, the more valuable the human gate and the evidence packet become.

Bottom line

The useful AI pattern for Postgres migrations is not "let the model write SQL." It is "let the model compress the evidence so the engineer can make a safe release decision." Keep the model in a schema-bound review lane, keep the database on a local or read-only boundary, and keep the final approval with a human who understands the product.