Expand-Contract Migrations (Zero-Downtime Schema Changes)

When migrations run before new code deploys, breaking schema changes cause downtime. The expand-contract pattern avoids this by splitting changes into backwards-compatible steps.

The Pattern

Expand   -->   Migrate   -->   Contract
(add new)      (switch)        (remove old)

1. Expand - Add new columns/tables alongside existing ones. Old code continues to work.
2. Migrate - Deploy code that uses the new schema. Backfill data if needed.
3. Contract - Remove old columns/tables once nothing depends on them.

Example: Renaming a Primary Key Column

Bad - Single migration (causes downtime):

-- Drops old PK, adds new PK, old code breaks immediately
ALTER TABLE staff_permissions DROP COLUMN cognito_user_id;
ALTER TABLE staff_permissions ADD PRIMARY KEY (workos_user_id);

Good - Three-step expand-contract:

Step	Migration	Code Deploy	Risk
Expand	Add workos_user_id column (nullable, unique)	Backfill writes to both columns	None - old column still works
Migrate	Swap PK to workos_user_id	Read/write new column only	None - column already populated
Contract	Drop cognito_user_id column	No change needed	None - column already unused

When to Use

• Dropping or renaming columns that running code depends on
• Changing primary keys or unique constraints
• Splitting or merging tables
• Any DDL change where the migration deploys before the code

When You Don't Need It

• Adding new nullable columns (always backwards-compatible)
• Adding new tables
• Adding indexes
• Changes where code deploys before or with the migration (e.g. frontend-only)

Rule of Thumb

If the migration would break the currently running code, split it into expand-contract steps.