Give AI the current schema, application reads and writes, migration history, deployment process, and measured data facts. Ask for an expand-backfill-switch-contract plan before asking for SQL. Every phase needs a compatibility rule, a data invariant, a verification query, a stop condition, and a recovery action.
A Migration Is a Compatibility Window
The database, old application instances, new application instances, background workers, reports, and queued jobs do not all change at the same instant. During a rolling deploy, two code versions may use the same schema. During a backfill, old and new representations may coexist. A delayed worker may process a message written before the migration began.
That makes a production migration a sequence of compatibility states, not one SQL file. Renaming a column in place, adding a required field before existing rows have a value, or changing an enum before every consumer understands it can fail even when the migration succeeds technically.
Use AI to map evidence and challenge the sequence. Do not use it as the source of truth for row counts, null rates, constraints, query plans, lock behavior, or which application version is live. Those facts must come from the repository, migration tooling, database metadata, observability, or an operator.
The Five-Part Migration Plan
Identify the authoritative schema definition, migration framework, deployed application versions, writers, readers, jobs, replicas, and external consumers. Record measured facts that affect the plan: table size, null distribution, duplicate keys, write rate, and existing constraints. Mark facts that are unavailable as unknowns instead of letting AI estimate them.
For each phase, state whether old code and new code can read and write the schema safely. Include workers and scripts, not only the main application. If the plan introduces dual reads or dual writes, define which representation is authoritative, how divergence is detected, and when the temporary path can be removed.
Expand adds a compatible structure. Backfill moves or derives existing data in bounded, restartable batches. Switch moves reads and writes after verification. Contract removes the old structure only after every consumer and rollback window has cleared. These phases may require separate pull requests and deploys; forcing them into one change removes the safety the sequence provides.
Write pass/fail statements about the data, such as: every migrated row preserves its owner, no active record loses its status, new writes populate both required representations, or source and target totals reconcile under a defined rule. Pair each invariant with a query, test, metric, or sample procedure. "Migration completed" is an event; it is not proof that the data is correct.
Specify the dry run, canary or first batch, batch size, rate limits, monitoring window, pause signal, owner, and approval gate between phases. Decide whether each failure calls for retry, pause, application rollback, data repair, or roll-forward. Preserve backups or snapshots according to your normal recovery process and test the commands in a non-production environment first.
The Migration Contract
Before implementation, turn the plan into a small contract the reviewer and operator can evaluate. It should contain:
- Outcome: the user or system behavior that should exist after the migration.
- Scope: exact tables, columns, indexes, constraints, services, jobs, and files involved.
- Evidence: schema output, repository references, measured data facts, and migration history used to build the plan.
- Phase table: schema state, active readers, active writers, invariant, verification, and recovery action for every phase.
- Backfill design: batching key, ordering, idempotency rule, checkpoint, retry behavior, throttle, and completion query.
- Operational risks: locking, long transactions, replication lag, storage growth, query-plan changes, and deploy overlap that require measurement.
- Stop conditions: concrete thresholds or failed invariants that halt progression.
- Contract gate: the evidence and elapsed compatibility window required before destructive cleanup.
Ask what happens if the application is rolled back after the schema has expanded, halfway through the backfill, and after reads have switched. If the plan cannot answer all three without improvisation, the compatibility window is not finished.
Rollback Is Not Reverse SQL
Reverting application code is often straightforward. Reversing a data transformation may not be. Dropped values, merged records, changed identifiers, and writes accepted under a new constraint model may have no lossless inverse. An automatic down migration can therefore create more damage than the original failure.
Classify recovery per phase. Before destructive work, application rollback may be enough because the old representation still exists. During a restartable backfill, pausing and fixing forward may be safer than undoing completed batches. After new writes depend on the new shape, recovery may require a repair script or roll-forward release. State that decision in advance; do not hide it behind a generic "rollback available" checkbox.
Copy-Paste Prompt: Migration Analysis
"Plan this database migration before changing any files or generating SQL. Desired outcome: [outcome]. Current schema and migration history: [paths or output]. Known readers, writers, workers, reports, and deploy process: [details]. Measured data facts: [table size, nulls, duplicates, write rate, or unknown]. First inspect repository evidence and list missing facts that require database or operator checks. Then output: (1) affected surfaces with evidence, (2) old-code/new-code compatibility matrix, (3) expand-backfill-switch-contract phases, (4) invariants and exact verification for each phase, (5) restartable backfill design, (6) locking, performance, replication, and storage questions that must be measured, (7) rollout gates and stop conditions, (8) recovery action per phase, and (9) destructive cleanup gate. Do not invent production data facts or database behavior. Do not implement yet."
Resolve the unknowns before asking for a patch. Then implement one reversible phase at a time. Review generated migrations like production code, inspect the actual statements your framework will execute, and run them against representative non-production data with the same verification queries the operator will use.
Common Failure Modes
The first is giving AI only the desired schema and asking it to write a migration. The model cannot see hidden consumers or production data from that prompt. The second is treating a framework-generated down migration as guaranteed recovery. The third is backfilling in one unbounded transaction without checkpoints. The fourth is switching reads before comparing the old and new representations. The fifth is dropping the old structure in the same deploy that introduces the new one.
The correction is a sequence with observable gates: collect evidence, preserve compatibility, migrate data incrementally, prove invariants, switch deliberately, wait through the agreed rollback window, and only then remove the old path.
Conclusion
AI can make database migration planning faster by tracing schema usage, exposing forgotten consumers, drafting compatibility matrices, and turning invariants into candidate checks. The safety still comes from measured facts and staged execution. Keep destructive steps late, make backfills restartable, and require evidence before every irreversible gate.
Continue with AI-Assisted Database Design, AI-Assisted CI/CD, AI Change Risk Matrix, and AI Regression Test Plan Template.