Generating Schema Migrations: The One Loop That Ends With a Human

A wrong test fails visibly and costs a rerun. A wrong migration runs once against production data and cannot be undone. That asymmetry changes the whole process.

The one category where the agent should not be trusted to finish

Almost everything else in this series is about giving agents good feedback and letting them converge. Schema migrations are the exception, and the reason is asymmetry: a wrong test fails visibly and costs a rerun, while a wrong migration runs once against production data and cannot be undone.

That does not mean not using an agent here. It means the loop ends with a human reading the SQL, always, and the surrounding process is designed to make that reading easy.

What the agent needs to not guess

The first two matter most. An ORM model is a claim about the schema; the database is the schema, and they diverge. And row count changes the correct answer entirely — adding a NOT NULL column with a default is trivial on 10,000 rows and a multi-hour lock on 400 million.

## Migrations
- Read the CURRENT schema with `./db schema <table>`. Never infer
  it from `models/`. The models lag the database.
- Check size first: `./db size <table>`. Anything over ~1M rows
  needs the zero-downtime pattern below, not a simple ALTER.
- Generate with `alembic revision -m "..."`. Never hand-write a
  migration file — the revision chain matters.
- Every migration needs a working `downgrade()`. If a change
  genuinely cannot be reversed, say so explicitly in the docstring
  and raise in `downgrade()` rather than leaving it empty.

The expand–contract pattern, stated as a rule

Most dangerous migrations are dangerous because they change something the running application still depends on. The fix is to never do that in one step.

Deploy 1 — EXPAND
  add `email_address`, nullable
  application writes BOTH columns, reads `email`

Deploy 2 — BACKFILL
  copy `email` → `email_address` in batches
  no application change

Deploy 3 — SWITCH
  application reads `email_address`, still writes both

Deploy 4 — CONTRACT
  application stops writing `email`
  drop `email`   ← the only irreversible step, and it is last

Four deploys to rename a column is tedious, and it is the difference between a rename and an outage. State it as a rule rather than hoping the agent infers it:

Never in one migration:
  - rename a column or table
  - change a column type
  - add NOT NULL to an existing column
  - drop anything the application still references

Each is expand → backfill → switch → contract, one deploy per step.
If asked for a rename, produce the FOUR migrations and say which
application change goes with each.

Locking is the part that surprises people

The operation that fails in production is rarely the one that looked risky. It is an ALTER that seemed harmless and took an exclusive lock on a hot table for eleven minutes.

-- Index creation must not block writes
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
-- note: cannot run inside a transaction; alembic needs
-- op.get_bind().execute() with autocommit, not op.create_index()

-- Adding a column with a default: fine on modern Postgres,
-- rewrites the whole table on older versions. Check yours.
ALTER TABLE orders ADD COLUMN priority integer DEFAULT 0;

-- Adding a constraint: validate separately so the initial
-- statement takes only a brief lock
ALTER TABLE orders ADD CONSTRAINT chk_priority
  CHECK (priority >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT chk_priority;

Batched backfills, because one UPDATE will not do

def upgrade():
    conn = op.get_bind()
    conn.execute(sa.text("SET lock_timeout = '3s'"))
    while True:
        result = conn.execute(sa.text('''
            WITH batch AS (
              SELECT id FROM orders
               WHERE email_address IS NULL AND email IS NOT NULL
               ORDER BY id
               LIMIT 5000
               FOR UPDATE SKIP LOCKED
            )
            UPDATE orders o
               SET email_address = o.email
              FROM batch b
             WHERE o.id = b.id
        '''))
        if result.rowcount == 0:
            break
        time.sleep(0.1)          # let replication and other writers breathe

SKIP LOCKED and the sleep are the two details that keep this from becoming an incident. Without them the backfill contends with live traffic and both slow down.

Reviewing the output

A short list, applied every time, in this order:

1. Is anything irreversible? Drops and type changes. If yes, is this the contract step of an expand–contract, or a shortcut?
2. What locks does it take, and for how long? Against production row counts, not your development copy.
3. Does downgrade() work? Run it. On a restored snapshot, not in your head.
4. Is it idempotent enough to resume? A migration killed halfway should be re-runnable.
5. Does the application tolerate both states? During a rolling deploy, old and new code run simultaneously against one schema.

Point five is the one that catches the most real problems and the one people skip, because it is about the deploy rather than about the SQL.

Test it against real volume

# Restore last night's production snapshot into a scratch database
pg_restore -d migration_test prod-snapshot.dump

# Time the migration against real row counts and real data skew
psql -d migration_test -c "SET lock_timeout='3s'" && \
  time alembic upgrade head

# Then prove the reverse works
time alembic downgrade -1

A migration that takes 40 ms on your seeded development database and eleven minutes on production is the normal case, not the surprising one. Anything touching a large table should be timed against restored production volume before it merges — and that step is easy to automate, which is the best argument for doing it.