AI Postgres Migration Review: A Safe Stack

Meta description: Review subscription-system Postgres migrations with a read-only AI stack, Supabase diffs, lock checks, tests, approvals, rollback controls, and safer releases.

Primary keyword: AI Postgres migration review

Secondary keywords: Supabase migration review, safe database migrations, Postgres schema migration, AI coding agent permissions, subscription database design

Changing a Postgres schema behind a subscription product is not a formatting task. A new status column can affect access control, invoice reconciliation, webhook retries, customer support tools, analytics, and every background job that assumes the old shape. An AI coding agent can shorten the review, but only if it sees the right evidence and cannot quietly apply the wrong SQL.

This guide builds an AI Postgres migration review workflow for one specific person: a senior full-stack engineer maintaining a Supabase-backed SaaS with recurring subscriptions, a small team, a modest staging budget, and a strict rule that customer and payment data never leaves the approved environment. The agent may inspect a local schema, propose a migration, run disposable tests, and produce a review packet. A human still owns the production decision.

Original architecture diagram by EssamAmdani.com. It shows the three layers used in this workflow: stack selection, ecosystem integration, and context engineering with a human release gate.

The example uses Supabase CLI conventions, plain SQL, and a terminal-first coding agent. The pattern also works with a managed Postgres service, Prisma, Drizzle, or another migration runner after you verify its transaction and deployment behavior.

The gap this stack fills

Supabase has documentation for database migrations, local development, branching, and CLI commands. Existing guides are useful for learning the mechanics. They do not replace a domain review that asks whether a migration preserves subscription entitlements, webhook replay behavior, tenant isolation, and a reversible rollout.

The narrow job-to-be-done here is: “Can I review and safely stage a schema change to subscription state without giving an AI tool production access or pasting customer records into a model?”

That creates four boundaries:

• Persona: senior AI/full-stack engineer who can read SQL and own a release, but does not have a separate database reliability team.
• Risk level: medium-to-high. A bad migration can deny access, grant access incorrectly, or block writes during a billing event.
• Budget: use a low-cost model for inventory and formatting, reserve a stronger reasoning model for the SQL and failure-mode review, and cap the number of passes.
• Privacy: send schema definitions, migration files, query plans, redacted fixtures, and aggregate counts. Do not send names, email addresses, payment-method details, invoice descriptions, access tokens, or raw webhook payloads unless the provider and policy explicitly allow it.

Recommended stack

Recommended stack — subscription migration review

Interface: terminal-first coding agent in a disposable branch or worktree.

Model roles: small planner for file inventory; stronger SQL reviewer for lock, constraint, RLS, and rollback analysis; small summarizer for the final checklist.

Skills: supabase-postgres-best-practices, a local subscription-migration-review skill, and seo-content-writer only if the review packet becomes public documentation. The first is a Supabase/Postgres performance and security reference; the second is the domain contract described below.

Plugins: official repository/CI integration, approved documentation lookup, and an issue tracker in draft-only mode. Do not install a billing plugin just to read private customer data.

MCP/tool categories: read-only Postgres catalog and query-plan inspection; approved vendor/Postgres documentation; repository and CI status; isolated browser or webhook-fixture runner. Default to no write-capable database tool.

Memory: commit a small decision record containing invariants, accepted risks, migration ID, test evidence, and rollback owner. Do not persist customer rows or secrets.

Permissions: read repository; write only the branch; run local containers and tests; read-only database connection with a short statement timeout; no production INSERT, UPDATE, DELETE, DDL, billing API, secret manager, or deploy permission.

The reason to split model roles is control, not ceremony. A planner can inventory the change without touching tools. The reviewer gets a bounded evidence packet rather than an entire monorepo. The summarizer cannot approve a migration because its output is only a report. If the team has one model, keep the same role boundaries in prompts and permissions.

Layer 1: choose the stack around the failure

Start with the failure you are trying to prevent. For subscription systems, the important question is rarely “does the SQL parse?” It is closer to “what happens if a renewal webhook arrives while the backfill is halfway through?”

Do not pick a model from a benchmark headline. Test the complete task: context preparation, tool calls, SQL proposal, validation, and review edits. A model that writes plausible SQL but misses a lock or a webhook race is not cheaper in practice.

The terminal is the best default interface for this job because the evidence is already in the repository and the acceptance checks are commands. An IDE agent is reasonable for interactive review. A chat-only interface is a poor default when it cannot reproduce the exact migration, local database state, and test commands.

Layer 2: connect only the evidence the reviewer needs

Repository and local database

Keep migrations, seed fixtures, database tests, and the review prompt in one branch:

1.
2├── AGENTS.md
3├── supabase/
4│   ├── config.toml
5│   ├── migrations/
6│   ├── seed.sql
7│   └── tests/database/
8├── src/billing/
9│   ├── entitlements.ts
10│   ├── webhook-handler.ts
11│   └── subscription-repository.ts
12└── docs/decisions/
13    └── 2026-07-24-subscription-migration-review.md

The first commands should be local and inspectable:

1supabase init                 # once, if this repo is not initialized
2supabase start
3supabase db reset             # rebuild from migrations and seed data
4supabase db diff              # inspect schema drift before writing SQL
5supabase db lint              # run Supabase database checks where supported

Supabase’s current documentation describes migrations as versioned SQL files and documents supabase db diff for generating or inspecting schema changes. Treat the CLI output as evidence to review, not as proof that the migration is safe. Pin the CLI version in your developer setup and record it in the review packet.

The database tool should be boring

If you add an MCP server or database tool, expose a narrow interface such as:

1get_schema(object_names[])       -> columns, constraints, indexes, policies
2explain_readonly(sql, params)    -> plan only, with statement_timeout
3get_lock_snapshot()              -> current lock waits, redacted

The tool should reject DDL and all writes. It should not accept arbitrary connection strings from model output. Use a dedicated read-only role, a fixed project or replica, a low statement_timeout, and a network boundary that cannot reach production writes. If your tool cannot enforce those controls, do not connect it.

For a visual reference, the official Supabase SQL-definition view exposes table definitions, columns, keys, and relationships in a read-oriented surface. It is useful for a human reviewer who wants to compare the model’s description with the actual schema; it is not evidence from this site’s database.

Courtesy: Supabase. Source: Supabase SQL definitions screenshot, from the Supabase local development documentation. Accessed July 24, 2026 UTC.

Billing is an external contract

Stripe describes subscriptions as a lifecycle that includes invoices, payment collection, status changes, and webhook events. That means the review packet needs more than a table definition. Include the status transition map and the code that turns an event into an entitlement decision.

For example, do not let an agent silently replace a provider status with a product access status. Keep them explicit:

1provider_subscription_status: the billing provider's observed state
2entitlement_state: the product's access decision
3last_provider_event_id: the idempotency boundary
4effective_at: when the product accepted the state

If the proposed migration collapses those concepts into one enum, the reviewer should flag it even when the SQL is valid.

Layer 3: context engineering and agent steering

The most useful context is a compact evidence pack with a known order. Do not dump the whole repository and hope the model finds the dangerous line.

The migration brief

Put this in the task prompt or a review file:

1Goal: add a nullable entitlement_state column and backfill it from the existing
2provider status without changing customer access during the first deploy.
3
4Invariants:
5- an acknowledged billing event is processed idempotently;
6- no customer loses access because a backfill row is temporarily null;
7- tenant_id remains part of every access query and policy;
8- the old read path remains available until the new path is measured;
9- rollback can stop the backfill and restore reads without dropping data.
10
11Out of scope: production execution, destructive drops, billing API calls,
12secret access, and changes unrelated to subscription state.
13
14Return: migration risks, lock behavior, index impact, data invariants, test
15cases, rollback steps, and evidence for every recommendation.

The domain skill

Create a local skill named subscription-migration-review with five sections:

1. Invariants: tenant isolation, idempotent events, access fallback, and auditability.
2. Schema checks: nullability, defaults, enum changes, foreign keys, indexes, RLS policies, and generated types.
3. Operational checks: lock level, transaction length, backfill batches, retry behavior, replica lag, and statement timeouts.
4. Rollout checks: expand, dual-read or dual-write, backfill, compare, cut over, then contract.
5. Evidence format: every finding has severity, SQL/object, why it matters, test, owner, and decision.

This skill is more valuable than a generic “write a migration” instruction because it tells the agent what counts as a failure in this domain. Keep it versioned with the application and review changes to the skill like code.

The evidence pack

Attach only these files and outputs:

• the proposed migration and the previous migration that created the affected table;
• supabase db diff output and a schema-only dump if needed;
• table columns, keys, indexes, RLS policies, and relevant functions or triggers;
• the subscription repository, entitlement function, webhook handler, and retry/idempotency code;
• redacted fixtures for active, trialing, past-due, canceled, and duplicate-event cases;
• EXPLAIN output for the old and new access query;
• the exact test and lint commands.

Ask the reviewer to label each statement as observed, inferred, or needs verification. This prevents a generated explanation from becoming an invented production fact.

A step-by-step operating workflow

1. Freeze the boundary before the agent sees the repo

Write AGENTS.md rules that prohibit production writes, secret inspection, unrelated edits, and unapproved network access. Define the commands the agent may run. A useful rule is: “If the task requires a new credential, stop and ask; do not search env files.”

Create a branch or disposable worktree. If Supabase branching is available for the project, use a preview branch for the database experiment and keep production credentials out of the agent environment. The Supabase branching guide explains the platform’s branch model; confirm current plan limits and data behavior before relying on it.

2. Inventory the blast radius

Have the planner return table names, code paths, policies, generated types, background jobs, and tests that mention the affected field. It should not edit files. Reject the plan if it cannot distinguish provider state from entitlement state or if it lists no rollback path.

3. Generate the smallest expand migration

For a new derived state, start with a nullable column and a comment. Avoid a default that forces a table rewrite until you have checked the version, table size, and lock impact:

1-- supabase/migrations/20260724090000_add_entitlement_state.sql
2ALTER TABLE public.subscriptions
3  ADD COLUMN IF NOT EXISTS entitlement_state text;
4
5COMMENT ON COLUMN public.subscriptions.entitlement_state IS
6  'Product access state; populated by the controlled backfill and event path.';
7
8ALTER TABLE public.subscriptions
9  ADD CONSTRAINT subscriptions_entitlement_state_check
10  CHECK (entitlement_state IS NULL OR entitlement_state IN
11    ('active', 'grace', 'restricted', 'revoked'))
12  NOT VALID;

NOT VALID is deliberate for a large table: the constraint can be added without immediately scanning all existing rows, then validated as a separate, observable operation after the backfill rules are ready. PostgreSQL documents the lock levels and subforms for ALTER TABLE; never infer them from a model’s prose.

4. Ask the SQL reviewer to challenge the migration

Use a separate reviewer pass with this instruction:

1Review this migration as a production Postgres change for a subscription SaaS.
2Do not rewrite it yet. Find lock risks, table rewrites, invalid constraint states,
3index mistakes, RLS gaps, tenant leaks, webhook races, retry bugs, rollback traps,
4and generated-type breakage. Cite the exact SQL line or schema object. For every
5finding, give a local test or query that could confirm it. Mark unknowns clearly.

The reviewer should inspect indexes before suggesting one. A normal index build can block writes while it runs. PostgreSQL documents CREATE INDEX CONCURRENTLY as a way to avoid locks that prevent concurrent inserts, updates, or deletes, but it has restrictions and cannot run inside a transaction block. That is a migration-runner decision, not a copy-paste optimization. If your runner wraps migrations in transactions, use a maintenance-window strategy or a separately controlled index operation after verifying the exact deployment behavior.

5. Backfill with a resumable worker

Do not ask the model to produce one giant UPDATE and call it done. Use a bounded worker that records progress, orders by a stable key, commits small batches, and can be stopped. The exact batch size depends on your table and load; make it a configuration value, measure it in staging, and do not present an arbitrary number as a universal best practice.

The worker should derive entitlement_state from an explicit mapping function, not from a fuzzy model judgment. For every row it touches, log a count by source status and destination state. Keep the old column and read path until comparison shows that the new value agrees with the domain rule.

6. Test the billing edge cases

Add database tests and application tests for:

• a new subscription whose first payment is incomplete;
• an active subscription receiving a duplicate webhook;
• a past-due subscription during a backfill batch;
• a cancellation event arriving before a renewal event is processed;
• a tenant attempting to read another tenant’s subscription;
• a null entitlement_state during the expand phase;
• a retry after the handler commits but before the acknowledgment is observed.

Use pgTAP or the project’s existing database test path for constraints and policies. Use application tests for event ordering, idempotency, and access decisions. The agent may generate test cases, but a human should confirm that the fixtures represent the product contract.

7. Compare plans and locks

Run EXPLAIN on the access query before and after the migration using redacted or synthetic data. Inspect the lock behavior in a disposable environment. Watch for a sequential scan introduced by a new predicate, an index that does not match the tenant and state filters, or a function that turns a cheap indexed query into a row-by-row operation.

The PostgreSQL CREATE INDEX documentation and ALTER TABLE documentation should be the source of truth for concurrency and lock claims. If a model gives a specific timing estimate without a plan and a representative dataset, treat the estimate as unsupported.

8. Produce the review packet

The final artifact should contain:

1Migration: 20260724090000_add_entitlement_state.sql
2Decision: approve for preview / revise / reject
3High-severity findings: none or a numbered list
4Observed evidence: commands, plans, test output, schema objects
5Unknowns: facts that still need a staging check
6Rollout: expand, dual-read/write, backfill, compare, cut over, contract
7Rollback: stop worker, restore old read path, preserve new column, investigate
8Owner: named human engineer

The model can draft this packet. The release owner checks that every command was actually run and that the packet does not contain fabricated output.

9. Approve and deploy in separate steps

The production command should not be available in the same agent session that generated the SQL. A human reviews the diff, CI result, migration history, monitoring plan, and rollback owner. Then a separate, tightly scoped release job runs the migration. After deployment, verify error rate, webhook retries, entitlement mismatches, lock waits, query latency, and replica health before contracting the old path.

Cost and performance controls

Use policy caps rather than vague promises:

• Planner: one pass, no tools, short context.
• SQL review: one primary pass and one adversarial pass; stop after the evidence pack is complete.
• Tool calls: allow schema, plan, lock, and test tools only; reject repeated identical queries.
• Context: include changed files and relevant schema objects first; summarize stable project rules once.
• Output: require a structured finding list instead of a long essay.
• Escalation: call the stronger model only when the change touches locks, RLS, data movement, or billing state.

Measure completed review cost, wall-clock time, tool-call count, accepted findings, false alarms, and human edits. These are local operating metrics, not provider benchmarks. If the agent spends more time re-reading the repository than reviewing SQL, improve the evidence pack before changing models.

Alternatives

Use a local model when schema and fixtures cannot leave the network, but test whether it can follow the SQL and Postgres reference set at the needed quality. Use a hosted model when the team needs stronger reasoning and the redaction policy permits it. Use an IDE agent when the engineer needs inline navigation; use a terminal agent when reproducibility and command logs matter more. Use Prisma or Drizzle migration tooling when the application already depends on it, but keep the same expand/backfill/contract and human approval policy.

The database platform also changes the workflow. A Supabase project gives you Supabase CLI, local development, branching options, and platform advisors. A direct Postgres deployment may give you more control over migration transactions but requires you to own more of the surrounding checks. The stack is the policy and evidence flow, not a brand list.

Failure modes and safety rules

The agent receives a service-role key

Stop the run, rotate the exposed credential, inspect logs, and restart with a read-only role. Environment-variable discovery is not a harmless convenience.

The model invents a lock or cost claim

Ask for the exact Postgres source, SQL line, plan, or measurement. If it cannot provide one, mark the claim unknown. Never turn a generated percentage into a release criterion.

A migration is technically reversible but operationally unsafe

Adding a column is easy to reverse; removing data written by the new path may not be. Preserve old columns until the new path is stable and keep a rollback that does not depend on a destructive down migration.

The backfill changes customer access

Default null to the old decision path during the expand phase. Compare old and new decisions before cutover. A migration should not turn incomplete evidence into an access grant or an access denial.

RLS is treated as an afterthought

Review policies and tenant predicates with the schema. A new table or view can bypass the intended access rule even when the old table was protected. Include a cross-tenant negative test.

The screenshot is mistaken for project evidence

Label sourced screenshots as documentation references. Project evidence comes from the exact schema, migration diff, fixtures, plans, and test logs for the change under review.

Verification checklist

• The agent ran in a branch, worktree, or preview database.
• No production write, deploy, billing API, secret, or raw customer data was available.
• The migration has a named goal, invariants, owner, and rollback.
• The expand step is additive and keeps the old read path working.
• Constraint validation, index creation, and transaction behavior match the actual runner.
• Backfill is resumable, observable, and tested with representative states.
• RLS, tenant isolation, generated types, triggers, and functions were reviewed.
• Access queries have before/after plans and a staging lock check.
• Billing webhook idempotency and event-order cases pass.
• The model labels unknowns and cites evidence rather than inventing results.
• A human reviewed the packet and owns production approval.

FAQ

Can an AI agent safely run Postgres migrations?

It can run local migrations in a disposable environment when its database role, network access, and command allowlist are constrained. Do not give the generation session production DDL or write access. Production execution belongs in a separate release job after a human reviews the SQL, evidence, monitoring, and rollback plan.

Should I use CREATE INDEX CONCURRENTLY for every subscription index?

No. It can reduce write blocking during an index build, but PostgreSQL documents restrictions, extra work, and the fact that it cannot run inside a transaction block. Decide from table size, write traffic, migration-runner behavior, and an observed staging plan. A normal index during a maintenance window may be safer.

How should a migration handle a null new column?

Treat null as an explicit expand-phase state and keep the old decision path available. Backfill from a reviewed mapping, compare old and new access decisions, then switch reads. Add NOT NULL only after the data and rollout prove that null is no longer valid.

What should the AI model see from a subscription database?

Usually schema metadata, migration files, policies, query plans, aggregate counts, and synthetic or redacted fixtures are enough. Keep names, emails, payment details, raw webhook payloads, tokens, and secrets out of the prompt. If the task truly requires sensitive data, use an approved private environment and document that decision.

Which MCP server should I install for this workflow?

Start with categories, not a marketplace count: read-only schema/catalog inspection, query-plan inspection, approved documentation retrieval, repository/CI status, and an isolated fixture runner. Install a specific server only after reviewing its source, version, network destinations, credential scope, and write methods. MCP standardizes how tools are exposed; it does not make a tool safe by itself. See the MCP specification for the protocol boundary.

Closing recommendation

For a subscription SaaS, the best AI database stack is intentionally narrow: a terminal agent, a bounded evidence pack, a read-only database tool, local Supabase migrations, Postgres-first validation, and a human release gate. Start with one additive column and one representative webhook fixture. If the workflow cannot explain the lock, the invariant, the test, and the rollback in plain terms, it is not ready for production.

For related architecture context, see the site’s guide to AI agent stacks, skills, plugins, MCP, ACP, memory, and workflows, the earlier Supabase Realtime migration discussion, the production-grade MCP and multi-agent architecture guide, and FinOps for AI agents.

Essam Amdani — AI engineer and software architect. This guide is educational and does not replace your database owner, billing-provider documentation, security review, or change-management process.

Sources checked

• Supabase: Database migrations
• Supabase: Local development and CLI workflow
• Supabase: Branching
• PostgreSQL 18: CREATE INDEX
• PostgreSQL 18: ALTER TABLE
• Stripe: How subscriptions work
• Model Context Protocol specification