How I Let My AI Coding Agent Write Database Migrations Without Losing Data

TL;DR

I spent the last six months letting Claude Code draft, test, and stage every database migration in my projects — and I only stopped hand-writing SQL after building a guard stack that made the agent's mistakes cheap. This post covers the three-layer safety net (shadow database dry runs, reversibility checks, row-count diffs), the NOT NULL backfill that almost ate a table, and 5 lessons on when you can actually trust an AI agent with schema changes. ⚠️ Spoiler: full freedom to draft, zero ability to apply.

The Problem

Schema migrations are the one place where "the agent made a small mistake" doesn't mean a failed test — it means data you can't get back.

For over a year I've been running a fully autonomous implementation system built on Claude Code. It writes features, opens pull requests, fixes its own failing builds. But there was one category of work I kept doing by hand: anything touching the database. Every ALTER TABLE was mine. Every backfill script was mine.

That felt safe, but it turned into a bottleneck. The agent would finish a feature in 20 minutes and then sit blocked, waiting for me to write a 6-line migration. Worse, my hand-written migrations weren't actually safer — I once shipped an index creation without CONCURRENTLY and locked a hot table on PostgreSQL 16 for 40 seconds. My own track record was the argument for automation, not against it.

So the real question became: what infrastructure do I need before an AI agent can safely propose schema changes? Not "can Claude write SQL" — it can, trivially. The question is what catches the SQL that's syntactically perfect and operationally dangerous.

How I Solved It

One design principle drives everything: the agent gets full freedom to draft migrations, and zero ability to apply them to anything real. Everything between draft and production is a gauntlet of automated checks.

flowchart LR
    A[Agent drafts migration] --> B[Shadow DB dry run]
    B --> C[Reversibility check]
    C --> D[Row-count diff]
    D --> E[Human reviews PR]
    E --> F[Apply to prod]
    B -- fails --> A
    C -- fails --> A
    D -- fails --> A

Layer 1: Shadow database dry runs

Every migration the agent drafts gets executed against a shadow database — a throwaway Postgres instance seeded with an anonymized snapshot of production data. Not an empty schema. This distinction matters more than anything else in this post.

An empty database will happily accept a migration that would take 45 minutes and an exclusive lock on real data. The shadow run measures actual behavior:

#!/usr/bin/env bash
# migration-dryrun.sh — run by the agent via a hook, never against prod
set -euo pipefail

docker run -d --name shadow-db -e POSTGRES_PASSWORD=shadow \
  -p 5499:5432 postgres:16
pg_restore -h localhost -p 5499 -U postgres -d postgres \
  snapshots/latest-anonymized.dump

# Time the migration and capture lock waits
START=$(date +%s)
psql -h localhost -p 5499 -U postgres \
  -c "SET lock_timeout = '2s';" \
  -f "$1" 2>&1 | tee dryrun.log
echo "elapsed_seconds=$(( $(date +%s) - START ))"

The lock_timeout = '2s' line is the workhorse. If the migration can't get its locks in 2 seconds on the shadow DB, it fails loudly, and the agent has to redraft with a safer strategy (usually CREATE INDEX CONCURRENTLY or a batched backfill).

I wired this into Claude Code as a hook: any file the agent writes under migrations/ triggers the dry run automatically, and the log lands back in the agent's context. The agent sees its own migration fail before I ever see the PR.

Layer 2: Reversibility checks

Every migration must ship with a working down script, and "working" is verified, not asserted. The check is dumb and effective:

1. Dump the shadow DB schema (pg_dump --schema-only)
2. Run up, then down
3. Dump the schema again and diff the two dumps

pg_dump --schema-only -h localhost -p 5499 -U postgres > before.sql
psql -h localhost -p 5499 -U postgres -f migration_up.sql
psql -h localhost -p 5499 -U postgres -f migration_down.sql
pg_dump --schema-only -h localhost -p 5499 -U postgres > after.sql
diff before.sql after.sql   # must be empty

The first week, this check failed on roughly a third of the agent's drafts. Claude Code loves writing an up that does three things and a down that undoes two of them. After I added the check output to its feedback loop, the failure rate dropped to nearly zero — the agent learned to write the down first, then derive the up from it. I didn't tell it to do that. It converged on the practice because it was the strategy that passed the gate.

Layer 3: Row-count diffs

The scariest failures don't touch the schema at all — they're data migrations that silently drop rows. So the harness snapshots SELECT count(*) per table before and after the shadow run, plus a checksum over primary keys for any table the migration touches:

SELECT relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY relname;

Any table whose row count decreases fails the gate unless the migration file contains an explicit annotation:

-- EXPECTED_ROW_LOSS: sessions (deleting rows older than 90 days per retention policy)
DELETE FROM sessions WHERE created_at < now() - interval '90 days';

The annotation isn't for the machine — it's for me. It forces the intent to be stated where the reviewer is already looking.

The near-miss that justified all of it

Two months in, I asked the agent to make users.locale non-nullable with a default of 'en'. It drafted this:

UPDATE users SET locale = 'en' WHERE locale IS NULL;
ALTER TABLE users ALTER COLUMN locale SET NOT NULL;

Looks fine. It is fine — on small tables. The shadow run flagged it: the UPDATE rewrote 1.8M rows in a single transaction, bloating the table and holding row locks for 30+ seconds. On production traffic, that's a pile of lock timeouts on the busiest table in the system.

The redraft (after the agent read its own dry-run log) batched the backfill:

-- Batched backfill: keeps transactions short, lets autovacuum keep up
DO $$
DECLARE rows_updated integer;
BEGIN
  LOOP
    UPDATE users SET locale = 'en'
    WHERE id IN (
      SELECT id FROM users WHERE locale IS NULL LIMIT 5000
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    COMMIT;
  END LOOP;
END $$;

ALTER TABLE users ALTER COLUMN locale SET NOT NULL;

Here's the thing: I would have approved the first version in code review. It reads as textbook-correct. The gate caught what my eyes wouldn't have. That was the moment I stopped thinking of the guard stack as training wheels for the agent and started thinking of it as infrastructure that protects me from both of us.

Lessons Learned

1. Gate on behavior, not on review. Human review of AI-generated SQL catches syntax-level mistakes and misses operational ones. A shadow run with production-shaped data catches the class of bug that actually hurts. If you only build one layer, build that one.

2. Never let the agent hold prod credentials — even read-only. My Claude Code config gives the agent a connection string only for the shadow instance. The prod DSN isn't in any file the agent can read. This isn't about trusting the model; it's about making the blast radius of a prompt-gone-wrong structurally zero. ✅

3. Feedback loops teach better than instructions. I wrote maybe ten lines of guidance about migrations in my agent instructions. The behavioral change came from piping gate failures back into the agent's context. The agent that sees diff before.sql after.sql fail will write reversible migrations; the agent that's merely told "write reversible migrations" will forget by Tuesday.

4. Empty-database tests are worse than no tests. They pass everything and prove nothing. The anonymized snapshot pipeline was 70% of the setup work and 95% of the value. If your seed data doesn't have a table with a few million rows in it, your dry run is theater.

5. The agent raised my standards, not lowered them. Before this, plenty of my hand-written migrations had no down script and no lock analysis. Building gates rigorous enough to trust an AI forced me to formalize checks I should have been running on myself all along. The tooling I built for the agent now guards my manual migrations too. 💡

What's Next

The current gate measures a migration against a snapshot. The next step is measuring it against traffic: replaying a sample of production query load against the shadow DB while the migration runs, so lock contention shows up as failed queries instead of a number in a log. I'm also experimenting with letting the agent propose the rollout plan (expand/contract phases for zero-downtime changes) as a structured document, not just the SQL.

If there's interest, I'll write up the anonymized-snapshot pipeline — sanitizing PII while keeping realistic data distributions turned out to be its own rabbit hole.

Wrap-up

Letting an AI agent near your database sounds reckless until you realize the alternative was me, at 11pm, running hand-written ALTER TABLE statements with no dry run and no rollback script. The agent didn't need to be perfect. It needed a gauntlet.

If this was useful, follow me here on Dev.to — I write regularly about building autonomous coding systems with Claude Code, including the failures. And if you're building something similar, tell me in the comments what your scariest agent-written migration looked like. I collect these stories. 🚀