AI Wrote Your Migration. Now Ship It Without Losing Data

Coding agents have gotten genuinely good at schema work. Ask for a migration that splits full_name into first_name and last_name, and you get a clean, syntactically valid file in five seconds. The SQL is correct. The migration will also take an ACCESS EXCLUSIVE lock on a 40 million row table, queue every incoming query behind it, and take your API down for four minutes during your busiest hour.

That is the whole problem with AI and migrations. The failure mode is not wrong syntax. It is correct SQL with wrong runtime behavior. And the agent cannot see runtime behavior, because runtime behavior lives in your data distribution, your index usage, your deploy topology, and the version of your app code that is still running while the migration executes.

Here is the workflow I use on client projects, plus the constraints file I load into context before an agent is allowed near a schema.

Why LLMs are structurally bad at migrations

An agent reads your schema.prisma, your models, maybe your existing migration folder. That is a static picture. Migrations are a dynamic problem, and the missing context is exactly the part that hurts:

• Row counts and data shape. ALTER COLUMN TYPE is instant on 5k rows and a full table rewrite on 50M. The schema file looks identical in both cases.
• Concurrent traffic. A lock that would be invisible at 3am is an outage at 2pm. The agent has no idea what your QPS looks like.
• Deploy topology. During a rolling deploy you have old and new app code hitting the same database at the same time. Any migration that assumes “code and schema change together” is broken by default.
• Lock queueing. This is the one that kills people. In Postgres, a blocked ALTER TABLE blocks everything behind it, including plain SELECTs. A migration that needs to wait 200ms for a lock can stall your entire read path for as long as the longest running transaction ahead of it.

So the fix is not “prompt better.” The fix is to give the agent a workflow where the dangerous options are structurally unavailable.

Rule one: never change schema and code in the same deploy

Every non-trivial schema change becomes three deploys. Expand, migrate, contract. This is old advice and AI makes it more important, not less, because agents love the compact single-commit version that renames a column and updates all references in one shot.

Renaming full_name to display_name, properly:

Deploy 1 (expand). Add display_name as nullable. Write to both columns in application code. Read from full_name. No backfill yet. Migration is one ADD COLUMN, which is metadata-only in modern Postgres.

Deploy 2 (migrate). Run a backfill job (not a migration file - see below). When the backfill is verified complete, flip reads to display_name behind a feature flag. Keep dual writes.

Deploy 3 (contract). Stop writing full_name. Wait. I mean actually wait, at least one full release cycle, ideally a week. Then drop the column in its own migration with nothing else in it.

Yes, this is three PRs for a rename. The upside is that every single step is independently reversible without data loss, and any one of them can be rolled back by redeploying the previous container image. Compare that to a single-deploy rename, where rollback means restoring from backup.

Agents are actually great at executing this once you tell them the shape. Ask for “step 1 of an expand-contract rename, additive only, no backfill in the migration file” and you get exactly that.

Rule two: backfills are jobs, not migrations

This is the most common thing I fix in AI-generated migration code. The agent writes:

UPDATE users SET display_name = full_name WHERE display_name IS NULL;

Inside the migration. On 40 million rows this is a single transaction that holds row locks, bloats your WAL, blows up replication lag, and cannot be resumed if it dies at 80%.

Backfills belong in your job queue, batched and idempotent:

async function backfillDisplayNames() {
  let cursor = await loadWatermark("display_name_backfill") ?? 0;
  const BATCH = 2000;

  while (true) {
    const rows = await db.query(
      `SELECT id, full_name FROM users
       WHERE id > $1 AND display_name IS NULL
       ORDER BY id LIMIT $2`,
      [cursor, BATCH]
    );
    if (rows.length === 0) break;

    await db.query(
      `UPDATE users SET display_name = full_name
       WHERE id = ANY($1) AND display_name IS NULL`,
      [rows.map(r => r.id)]
    );

    cursor = rows[rows.length - 1].id;
    await saveWatermark("display_name_backfill", cursor);
    await sleep(100); // give replicas room to breathe
  }
}

The IS NULL guard makes it idempotent. The watermark makes it resumable. The sleep makes it a background process instead of an incident. And because it is a job, you can observe it, pause it, and speed it up or slow it down without a deploy.

The Postgres gotchas your agent will miss

Keep this list somewhere the model can read it. These are the ones I hit repeatedly:

• ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT is safe in Postgres 11+ for constant defaults. With a volatile default (like now() or gen_random_uuid()) it rewrites the whole table. Agents mix these up constantly.
• SET NOT NULL requires a full table scan holding ACCESS EXCLUSIVE. Two-step it: add a CHECK (col IS NOT NULL) NOT VALID constraint, VALIDATE CONSTRAINT (which takes a weaker lock), then SET NOT NULL on PG 12+ where the validated constraint lets it skip the scan.
• Adding a foreign key blocks writes on both tables while it validates. Add it NOT VALID, then VALIDATE CONSTRAINT separately.
• CREATE INDEX locks writes. Always CREATE INDEX CONCURRENTLY. It cannot run inside a transaction block, which means most ORM migration runners need an explicit escape hatch. Prisma needs raw SQL. Rails needs disable_ddl_transaction!. Agents forget this every time.
• Changing column type usually rewrites. varchar(50) to varchar(100) is free; varchar to text is free; int to bigint is a rewrite. Do the rewrite as a new column plus backfill.
• Dropping a column is fast but breaks any old app instance still SELECT *-ing it. Hence the wait in deploy 3.

And wrap everything in a lock timeout so a blocked migration fails instead of taking the site down:

SET lock_timeout = '3s';
SET statement_timeout = '30s';

If it cannot get the lock in three seconds, the migration errors out and your deploy fails loudly. That is a much better outcome than a silent queue jam. Retry it in a loop if you want.

The rules file

I keep a docs/MIGRATIONS.md in every repo and reference it explicitly in the agent prompt or the project instructions. Short, imperative, no explanations:

- Additive only. Never DROP, never RENAME in the same PR as code changes.
- One migration file = one logical change. No bundling.
- Every migration starts with: SET lock_timeout = '3s';
- Indexes: CREATE INDEX CONCURRENTLY, outside transaction.
- No UPDATE / DELETE / INSERT statements in migration files. Data changes go in src/jobs/backfills/.
- New columns are nullable. Constraints come later, NOT VALID then VALIDATE.
- Tables over 1M rows: state the estimated lock duration in the PR description.
- Forward-only. No down migrations. Rollback = redeploy previous app version.

That last one is worth defending. Down migrations create a false sense of safety - they are almost never tested, and if the up migration destroyed data the down migration cannot bring it back. Forward-only plus additive-first is genuinely safer, and it forces the expand-contract discipline.

With this file in context, the quality of AI-generated migrations goes from “needs a rewrite” to “needs a read.” That is the whole win.

What I actually check in the diff

Seven things, in order, takes about ninety seconds:

1. Is there any DROP, RENAME, or type change? If yes, is this a contract-phase PR with a matching expand PR already shipped and waiting?
2. Any DML (UPDATE/DELETE/INSERT) in the migration file? Move it to a job.
3. CREATE INDEX without CONCURRENTLY?
4. NOT NULL or foreign keys added directly instead of via NOT VALID?
5. Is lock_timeout set?
6. What is the row count of every table touched? I run the count myself, I do not trust an estimate in the PR description.
7. Will the currently deployed app code still work with this schema? Not the new code. The code running right now.

Test it against real data volume

A migration that passes on a seeded dev database with 200 rows tells you nothing. Restore a recent production snapshot into a scratch instance, run the migration, and time it. Ten minutes of setup, and it turns “probably fine” into a number.

While it runs, watch for blocking:

SELECT pid, wait_event_type, state,
       now() - query_start AS duration, left(query, 80)
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY duration DESC;

If that returns rows during your test migration, you have your answer.

Where the agent genuinely helps

I am not arguing for doing this by hand. Agents are excellent at the tedious parts of this workflow: generating the three-deploy sequence from a description, writing the batched backfill job with a watermark, producing the raw SQL escape hatch for a concurrent index in whichever ORM you are stuck with, and writing the verification query that proves a backfill is complete.

What they cannot do is decide whether a four minute lock is acceptable for your business. That is a judgment call that requires knowing your traffic, your SLA, and your customers. Keep that part.

The pattern generalizes beyond databases: let the agent generate inside a set of constraints that make the catastrophic options impossible, and spend your review time on the decisions the constraints cannot make for you.