db-migrate

# DB Migrations Production schema changes fail when old and new code disagree during rollout. Prefer **expand/contract**: add compatible changes first, remove old shapes later. ## When to Offer This Workflow **Trigger conditions:** - ALTER TABLE in production; large table rewrites - Blue/green deploys coupled to schema state - Need zero-downtime or low-downtime migrations **Initial offer:** Use **six stages**: (1) classify change, (2) expand phase, (3) backfill & dual-write, (4) flip reads/writes, (5) contract phase, (6) verify & rollback). Confirm database engine (PostgreSQL, MySQL, etc.). --- ## Stage 1: Classify Change **Goal:** Additive vs destructive; lock risk (full table rewrite vs instant metadata change). **Exit condition:** Migration labeled as expand or contract with risk notes. --- ## Stage 2: Expand Phase **Goal:** Add nullable columns or new tables without breaking currently deployed code. ### Practices - Avoid DEFAULT clauses that lock large tables badly on some engines (use phased backfill instead) --- ## Stage 3: Backfill & Dual-Write **Goal:** Throttled batch backfill; dual-write old and new representations during transition when needed. --- ## Stage 4: Flip Reads/Writes **Goal:** Deploy code that reads new columns only after backfill completes; use feature flags for staged rollout. --- ## Stage 5: Contract Phase **Goal:** Drop old columns only after no code references them (search repo, logs, feature usage). --- ## Stage 6: Verify & Rollback **Goal:** Monitor errors, slow queries, replication lag; rollback = redeploy previous app version + avoid destructive steps until stable. --- ## Final Review Checklist - [ ] Change classified; expand/contract path clear - [ ] Additive migrations before dependent code - [ ] Backfill throttled and verified - [ ] Read/write cutover sequenced with flags - [ ] Contract only after references gone - [ ] Monitoring and rollback tested ## Tips for Effective Guidance - Long transactions on migrations can cause outages—chunk work. - Use online schema tools (pt-online-schema-change, etc.) when appropriate. ## Handling Deviations - SQLite/embedded engines have different locking—validate per engine.