Schema, Change & Migration with Safety Gates

Change is where database work gets dangerous, because change is where mistakes persist. An AI-drafted migration that locks a hot table, a data fix without an inverse, a converted stored procedure that treats empty string as NULL — none of these announce themselves in review, and all of them are one careless paste away. The answer is not to refuse the acceleration; it is to wrap it in gates that catch what generation gets wrong. The gate pattern you learn here — draft, checklist, staging replay, rollback path, sign-off — recurs all the way up this track, until in Module 8 it governs AI agents touching financial data.

The scenario — the backfill that took the lock

Kavitha is a database developer at a Pune payments company: PostgreSQL 15, a transactions table at 220 million rows, a strict 99.95% availability target on the API that writes to it.

The fix is a batched, resumable backfill with progress tracking, a separate validation step for the NOT NULL constraint (NOT VALID then VALIDATE CONSTRAINT on Postgres, so validation doesn't take the heavy lock), and replica-lag checkpoints between batches. Her assistant, asked properly — "batched online pattern, 220M rows, hot OLTP writers, replicas must stay under 5s lag" — produces exactly that. The generation was never the problem. The absence of a gate that asks "what does this do at production scale, under production locks?" was.

The gate chain

Every AI-drafted change ships inside the same five gates. The chain is deliberately boring — boring is what makes it survivable under deadline pressure:

1. 1Generation with operational context. The prompt states scale, write rate, lock sensitivity, replica topology, and engine version. A migration prompt without row counts is a request for a demo script.
2. 2The review checklist. A fixed, written list the reviewer walks: lock analysis per statement, transaction scope, batch sizing, constraint-validation strategy, replica impact, index-build mode (CONCURRENTLY / ONLINE), and idempotency (can this re-run after a mid-flight failure?).
3. 3Staging replay at meaningful scale. If staging is 1% of production, the replay validates syntax, not safety. Replay against a production-sized restore, or at minimum compute the arithmetic: rows × row width × WAL amplification, and batch time × batch count against the window.
4. 4The rollback script — written first, tested in staging. Not "we'll reverse it if needed": an actual inverse script, replayed in staging like the forward script. Some changes have no clean inverse (dropped columns, lossy type changes) — the checklist forces that admission before the change, which changes how you stage it.
5. 5Pre/post checks and sign-off. Row counts, constraint states, and sanity aggregates captured before and after; a named human signs the checklist, and the ticket links all of it.

• AI drafts the forward scriptwith the batched/online pattern and lock analysis, because you demanded them in the prompt.
• AI drafts the rollback scriptin the same conversation, so it inverts what was actually written, not what was intended.
• AI drafts the pre/post checksrow counts by state, orphan probes, aggregate checksums over affected columns.
• The human owns the gatesthe checklist verdict, the staging-scale judgment, and the signature. None of those delegate.

Production data fixes: the approval gate in miniature

The one-off UPDATE against production is the most routine dangerous thing database people do. The disciplined package, every time — AI drafts all four artifacts in one pass:

• The fix scriptscoped by explicit predicates, wrapped in a transaction sized to the change, with the affected-row count asserted against expectation before commit.
• The inverse scriptgenerated from the same predicates, capturing prior values first (a backup table of the affected rows is the honest minimum).
• The affected-row reportwhat will change, how many, sampled before/after values, produced before execution and attached to the ticket.
• The sign-off linea second person, named, approving predicate logic and blast radius; the ticket links script, inverse, and report.

This is the smallest full instance of the approval-gate pattern. Internalize it here at the scale of one UPDATE, and Module 8's "which agent actions require a human queue?" is the same reasoning at platform scale.

Cross-engine migration, done honestly

Oracle-to-Postgres and its cousins are where AI claims run hottest — AWS cites up to 90% schema conversion for DMS Schema Conversion's gen-AI mode, and every vendor number like it is marketing until reproduced on your codebase. The workflow that actually works is transpiler-first:

Before converting anything, do the archaeology. Long-context models are genuinely good at explaining a thousand-line legacy procedure: control flow, embedded business rules, dead branches, dependency map. Demand a specific format — "list every business rule as a testable statement; flag any branch you believe is unreachable and why" — and have someone who knows the domain review the rule list. Those extracted rules become your parity test cases, which means archaeology errors become test-suite gaps: verify the extraction, not just the conversion.

Parity testing — where "almost right" lives

Converted code that compiles and runs is not converted code that agrees. Engines disagree in quiet, specific places, and the parity suite must target them by name:

The suite itself is layered — row counts per object, aggregates (sums, min/max, distinct counts) per key column, sampled full-row comparison (datacompy-class tooling or paired EXCEPT queries in both directions), then the targeted trap probes above. AI generates this suite well when you name the traps in the prompt — a generic "write comparison tests" request produces row counts and misses every semantic edge, because the edges are exactly what generic training examples omit.

Two adjacent applications of the same digest-and-gate pattern round out the change practice. Upgrade planning: feed major-version release notes and deprecation lists plus your feature-usage inventory to the model and get a risk register — what breaks, what changes behavior, what to test first — instead of a week of reading. Patch triage works identically over CU/PSU/CVE notes against the fleet inventory. Both outputs are drafts for a human who knows the estate; both beat the unread-release-notes status quo by a mile.

Finally, the seat turns around: as app teams generate more of their code with AI, the DBA becomes the database-correctness reviewer of their output. The recurring catches from the database's side of the fence — N+1 query patterns from naive ORM loops, missing pagination on unbounded result sets, implicit type conversions that kill index usage (string parameter against an integer column), and chatty transaction scopes that hold locks across round-trips. An AI review pass with exactly that checklist, run against app PRs that touch data access, catches the mechanical majority and makes you the reviewer teams want rather than the gate they resent.

Try it now

1. 1Write your review-gate checklist. Ten lines, your engine's specifics: lock analysis, transaction scope, batching, constraint validation, replica impact, concurrent index builds, idempotency, rollback tested, pre/post checks, named sign-off. Commit it to the repo where migrations live.
2. 2Package one data fix properly. Take a recent production data fix and rebuild it as the four-artifact package — fix, inverse, affected-row report, sign-off line — with AI drafting all four from your predicates.
3. 3Run one archaeology pass. Point a long-context model at the gnarliest stored procedure you own: business rules as testable statements, dead branches flagged. Verify three of the extracted rules against the code by hand and note what the model got wrong.

Check yourself: An AI-converted PL/SQL procedure passes row-count and sum-based parity checks against the Oracle original. Name two classes of defect that can still be present, and the specific probes that expose them.

This module anchors LAB-04 (migration scripts with safety gates) — where the review checklist must catch two seeded hazards before anything runs — and LAB-05 (cross-engine conversion sprint with parity proof), with its three planted semantic traps and honest AI-failure log. Do it at work this week → pb-10 (gated migration generation), pb-11 (the data-fix package), pb-14 (stored-procedure archaeology), and pb-15 (parity suites AI cannot talk past).