# Schema Migration Safety

> Use when writing database schema migrations or changing tables, columns, indexes, or constraints on a system with real data

- Skill: `yuchi-chang/schema-migration-safety` (Agent Skill)
- Install (CLI): `npx skillmds@latest add yuchi-chang/schema-migration-safety`
- Raw SKILL.md: https://api.skillmd.com/api/skills/yuchi-chang/schema-migration-safety/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: yuchi-chang (https://skillmd.com/u/yuchi-chang)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/yuchi-chang/schema-migration-safety

---


# Schema Migration Safety

Live data and running code make schema changes a deployment problem, not just a SQL problem: during every deploy, the *old* code version runs against the *new* schema.

## Expand–contract

Never break in one step. Split every breaking change:

1. **Expand:** add the new column/table/index alongside the old. Deploy code that writes both, reads old.
2. **Migrate:** backfill data; switch reads to the new path.
3. **Contract:** only after the old path is provably unused, remove it — in a later release.

A rename is a drop-and-add in disguise: same treatment.

## Rules

- Every migration has a tested rollback, or is explicitly documented as irreversible and confirmed with the user.
- No large data transformation inside a schema migration — backfill in batches as a separate job. A migration that locks a big table is an outage.
- Adding NOT NULL or constraints to existing columns: check existing data first, or add with a default / as NOT VALID, then validate separately.
- Index creation on large tables: use the non-blocking variant (e.g. `CREATE INDEX CONCURRENTLY` in Postgres).
- Destructive operations (DROP, mass DELETE/UPDATE): state the affected row counts, confirm a verified backup exists, and get explicit confirmation before running.

## Smell test

"Old app version + new schema: does every query still work?" If no, another expand step is missing.