Migration Plan Template

Used for Section 3 of the review output when the diagram differs from current-schema-state.md.

Always four phases. Greenfield diagrams skip this section with the literal note: "Greenfield — initial DDL only, no migration phases needed."

Phase 1 — Expand

-- Add nullable column(s) and any concurrent index up front.
ALTER TABLE <table> ADD COLUMN <new_col> <type>;
CREATE INDEX CONCURRENTLY idx_<table>_<new_col>
    ON <table> (tenant_id, <new_col>);

• Lock impact: ALTER TABLE ... ADD COLUMN (nullable, no default) → brief AccessExclusiveLock. CREATE INDEX CONCURRENTLY → ShareUpdateExclusiveLock, allows concurrent reads/writes.
• Backward compatibility: old code unaffected (column nullable).
• Rollback: DROP INDEX CONCURRENTLY ...; ALTER TABLE <table> DROP COLUMN <new_col>;

Phase 2 — Backfill

WITH batch AS (
    SELECT id FROM <table>
    WHERE <new_col> IS NULL AND id > $last_id
    ORDER BY id LIMIT 5000
)
UPDATE <table> SET <new_col> = <expr>
WHERE id IN (SELECT id FROM batch);

• Lock impact: row-level only. Throttle 100 ms between batches.
• Backward compatibility: old code reads either old or new shape.
• Rollback: UPDATE <table> SET <new_col> = NULL; or restore from pre-backfill snapshot.

Phase 3 — Cutover

-- Application now reads/writes the new column.
-- Promote any expand-time index to a constraint without rewriting the table.
ALTER TABLE <table>
    ADD CONSTRAINT uq_<table>_<new_col>
    UNIQUE USING INDEX idx_<table>_<new_col>;

• Lock impact: ALTER TABLE ... USING INDEX upgrades the existing index in place. No table rewrite.
• Backward compatibility: old read path remains for one deploy cycle.
• Rollback: ALTER TABLE <table> DROP CONSTRAINT uq_<table>_<new_col>;

Phase 4 — Contract

ALTER TABLE <table> ALTER COLUMN <new_col> SET NOT NULL;
ALTER TABLE <table> DROP COLUMN <old_col>;

• Lock impact: SET NOT NULL revalidates rows under brief AccessExclusiveLock; column drop is metadata-only.
• Backward compatibility: old code path is fully retired.
• Rollback: ALTER TABLE <table> ALTER COLUMN <new_col> DROP NOT NULL; Dropped column cannot be restored without backup — keep one before Phase 4.