Migration Strategist Protocol

This skill focuses on taking a database from State A to State B without causing downtime, locks, or data loss in production. Direct ALTER TABLE operations on large tables are dangerous and often require a multi-step rollout.

Core assumption: You cannot lock the table. You cannot break the old version of the application while the new version is deploying.

1. The Zero-Downtime Pipeline

When asked "How do I rename this column?" or "How do I split this table?", standard ALTER TABLE RENAME breaks the app. Always enforce a backward-compatible migration strategy:

The Expand & Contract Pattern (4 Steps)

For breaking changes (e.g., renaming a column name to full_name):

1. Add (Expand): Add the new column full_name (nullable initially).
2. Dual Write: Deploy application code that writes to BOTH name and full_name, but reads from name.
3. Backfill: Write a script or background job to populate full_name using name for old rows.
4. Switch & Drop (Contract): Change the app to read/write ONLY full_name. After successful deployment, run a final migration to drop name.