Generate Migration Scripts with Safety Gates Built In

Tier: Core · Workflow area: Schema & Change Management · ~40 minutes

AI knows the online-migration patterns cold: batched backfills, NOT VALID constraints, concurrent index builds, lock-timeout wrappers. What it does not know is your table sizes, your replica lag tolerance, or your maintenance windows — which is why the output is a draft package that must survive a gate chain: human checklist, staging replay at production scale, then prod. No gate, no ship.

When to use

• Any DDL on a large or hot tableadding columns with defaults, NOT NULL additions, type changes, new constraints on tables where a long lock is an outage.
• Backfills measured in millions of rowsthe batching pattern, the progress tracking and the resumability are exactly the boilerplate AI drafts well.
• When the rollback script is being skipped "because it's simple"the package format makes rollback non-optional, which is the point.

When NOT to use

• Tiny cold tablesa 10k-row config table needs a plain ALTER in a transaction, not a ceremony. Match the gate weight to the blast radius.
• Emergency data fixes mid-incidentthat is pb-11's package (fix + inverse + sign-off), executed under incident rules.
• When staging cannot approximate production scalea replay against 1% of the data validates syntax, not safety. Restore a production-sized copy first, or scale the gates up with extra caution in prod (lower batch sizes, tighter lock timeouts, an abort rehearsal).

The recipe

1. 1.

   Specify the change with its danger context. The prompt gets the operational facts that make migrations fail:

   Copy

   Draft a production migration package for PostgreSQL 16.
   Change: add column risk_score numeric NOT NULL DEFAULT 0 to orders,
   backfilled from risk_events, then add a CHECK (risk_score >= 0).
   
   Operational facts:
   - orders: 480M rows, ~900 writes/sec sustained, replicas with 5s
     lag SLA, pgbouncer in transaction mode.
   - Maintenance window: none. Must run online.
   - Migration tooling: Flyway, versioned scripts.
   
   Produce FOUR artifacts:
   1. FORWARD script — batched, resumable, with lock_timeout and
      statement_timeout set, batch size as a variable, progress logging.
   2. ROLLBACK script — truly inverse, also online-safe.
   3. VERIFICATION queries — pre/post row counts, backfill completeness,
      constraint validity, replica-lag checkpoints between phases.
   4. HAZARD LIST — every lock this takes, on what, for how long, and
      what happens to concurrent writes in each phase.
   Use NOT VALID + VALIDATE for the constraint. Never rewrite the
   whole table in one statement.
   

2. 2.

   Read the draft for the classic seeded hazards. Generated migrations fail in recurring ways; check for each explicitly: a single-statement backfill hiding inside the batching (UPDATE orders SET ... with no batch predicate), constraint validation without NOT VALID first, missing lock_timeout (a blocked ALTER queues behind one long transaction and then blocks everyone else), and index creation without CONCURRENTLY. The healthy core of the forward script looks like:

   Copy

   SET lock_timeout = '2s';
   SET statement_timeout = '0';
   
   ALTER TABLE orders ADD COLUMN risk_score numeric;  -- no default: metadata-only
   
   -- Batched backfill, resumable by PK range
   DO $$
   DECLARE last_id bigint := 0; batch int := 20000; n bigint;
   BEGIN
     LOOP
       UPDATE orders o SET risk_score = COALESCE(r.score, 0)
       FROM risk_events r
       WHERE o.id > last_id AND o.id <= last_id + batch
         AND r.order_id = o.id AND o.risk_score IS NULL;
       GET DIAGNOSTICS n = ROW_COUNT;
       last_id := last_id + batch;
       RAISE NOTICE 'through id %, updated %', last_id, n;
       EXIT WHEN last_id >= (SELECT max(id) FROM orders);
       PERFORM pg_sleep(0.1);  -- let replicas breathe
     END LOOP;
   END $$;
   
   ALTER TABLE orders ALTER COLUMN risk_score SET DEFAULT 0;
   UPDATE orders SET risk_score = 0 WHERE risk_score IS NULL;  -- stragglers, batched too if large
   ALTER TABLE orders ALTER COLUMN risk_score SET NOT NULL;    -- PG12+: validates via scan; consider CHECK NOT VALID route on huge tables
   ALTER TABLE orders ADD CONSTRAINT orders_risk_nonneg
     CHECK (risk_score >= 0) NOT VALID;
   ALTER TABLE orders VALIDATE CONSTRAINT orders_risk_nonneg;  -- takes only SHARE UPDATE EXCLUSIVE
   

3. 3.

   Run the review-gate checklist — signed, per item. The checklist is the human gate; every line gets initials:

   Copy

   [ ] Every statement's lock level and duration identified (hazard list
       cross-checked against the docs, not just the AI's claims)
   [ ] Backfill is batched AND resumable from a crash mid-run
   [ ] Rollback script replayed mentally against EACH phase — inverse
       works from any partial state, not just full completion
   [ ] lock_timeout set; retry behavior on timeout defined
   [ ] Replica lag checkpoints between phases; abort threshold named
   [ ] Verification queries have expected values written down BEFORE
       the run (orders count = backfilled + defaulted)
   [ ] Flyway versioning: forward is V-numbered, rollback filed as the
       documented U/undo path; both in the PR
   

4. 4.

   Replay in staging at production scale — and rehearse the abort. Run the full package against a restored production-sized copy with synthetic write load running. Measure: total duration, max replica lag, lock waits experienced by the synthetic writers. Then rehearse the failure: kill the backfill mid-run, confirm resumability; run the rollback from the half-done state, confirm the verification queries catch the partial state.

5. 5.

   Execute in production phase-by-phase with checkpoints. Run each phase, run its verification query, check replica lag, proceed or abort. A migration run log — phase, start/end, verification result, lag observed — goes into the ticket as it happens, not from memory afterward.

6. 6.

   Close with the post-verification. Final row counts reconcile, constraint is validated, no orphaned NULLs, app error rates flat. The package plus the run log is the change record.

Make it yours

• MySQLthe batched-DDL problem is what gh-ost and pt-online-schema-change exist for; have AI draft the gh-ost invocation (--max-lag-millis, --chunk-size, --postpone-cut-over-flag-file) and the cut-over/rollback plan instead of raw ALTERs, and replay the whole ghost migration in staging including the cut-over.
• SQL Servermetadata-only default additions exist (Enterprise), but NOT NULL + backfill still wants batching with TOP (@batch) loops and WAITFOR DELAY; watch lock escalation at ~5k locks and ask the AI to design batches below the escalation threshold, verified with Extended Events in staging.
• Oraclelean on DBMS_REDEFINITION for table-rewriting changes and ask AI to draft the redefinition script plus the interim-table verification; DEFAULT additions are metadata-optimized on modern versions, but confirm edition-specific behavior before trusting it.

Quality check

• The rollback was executed in staging, not just writtenan untested rollback script is a wish. It ran, from a partial state, and the verification queries proved the restore.
• Expected verification values were written before the runchecks you invent after execution confirm what happened, not what should have.
• Lock claims were verified against documentationthe AI's hazard list is a draft; lock levels per statement got confirmed for your engine version.
• Batch size was tuned to replica lag in stagingthe right batch size is measured, not defaulted. Lag SLA breached in staging means smaller batches, not hope.