Production Database Migration Plan

Plan a production database migration. Baseline assumption: the old and new application versions will run concurrently during deployment.

Required change: [describe] Current schema: [relevant DDL] Data volume: [row counts in affected tables] Maintenance window: [available / none]

Produce:

1. Decomposition into backward-compatible steps — add, dual-write, backfill, switch reads, drop old. Every step must work with both application versions running.
2. A script per step — exact runnable DDL and DML. For each, state whether it locks the table and for how long.
3. Backfill — batched or single-shot. If batched: batch size, delay between batches, and how to resume after an interruption.
4. Rollback plan — for each step, how to revert. Explicitly identify the point of no return and what recovery from it requires.
5. Verification — a query proving the step succeeded, and a query detecting rows that did not migrate.
6. Deployment order — schema first or code first. Justify.
7. What will break — three things that could go wrong and the early signal for each.

If the change drops a column or table: begin with a separate deprecation step and wait a full deployment cycle before the actual drop.