migration-safety git:20260919.6cf18f7A

Immutable. This exact content is served forever at /api/v1/blob/0f1f08cd4a363147.

---
name: migration-safety
description: Write, review or apply a database schema migration without destroying data. Use for any task that creates or modifies a migration file (Alembic, Prisma, Django, Rails, Flyway, raw SQL), and before applying one to a shared environment.
---

# Migration safety

## Treat every environment as production

Staging and demo environments hold real data more often than anyone admits, and a migration
that destroys data in staging demonstrates that the same operation would run in production.
There is no "it's just staging" exception. **Never write a migration that could silently
destroy existing data.**

## Destructive operations — stop and surface to the human

Do not generate these autonomously. Describe what you intend and why, and wait for
confirmation before writing the file:

- `DROP TABLE`, `DROP COLUMN`, `TRUNCATE` — destroys rows or values permanently.
- `ALTER COLUMN … TYPE` with a lossy cast — the database rewrites the column; values that
  cannot be cast are lost.
- Removing `NOT NULL` and then dropping the column — same as above.
- `DELETE FROM` inside a migration — data loss with no recovery short of a backup.

## Safe patterns

**Adding a column.** Add it nullable first, even if the final intent is `NOT NULL`. Backfill in
a separate migration or deploy step. Add the constraint only after the backfill is confirmed.
Three small migrations beat one that rewrites data and adds a constraint at once.

**Renaming a column.** Never rename in a single deploy. Expand-contract: add the new column
nullable; write to both; backfill new from old; switch reads; drop the old column in a later
release once confirmed safe.

**Changing a type.** Lossless (`VARCHAR` → `TEXT`) may proceed with a note in the migration
comment. Lossy (`TEXT` → `INTEGER`, shrinking a `VARCHAR`) stops and asks.

**Removing a column.** Only after confirming no deployed code reads or writes it, and a backup
exists or the column is confirmed empty. Archive before dropping in the downgrade path:

```sql
CREATE SCHEMA IF NOT EXISTS archive;
CREATE TABLE IF NOT EXISTS archive.<table>_<revision>_downgrade AS
  SELECT id, <dropped_column> FROM <table>;
```

The archive is retained indefinitely; nobody drops it without explicit sign-off.

**Additive first.** Adding a column or table is always safer than modifying one. If the goal
can be reached by adding, add.

## Checklist before committing a migration file

- `upgrade()` contains no `DROP TABLE`, `DROP COLUMN`, `TRUNCATE` or `DELETE FROM` without
  explicit human sign-off recorded in the PR.
- Any new `NOT NULL` column either has a server default or is added nullable with a separate
  backfill.
- You read the generated file. Never rely on autogenerate output alone.
- `downgrade()` is implemented, or is an explicit no-op with a comment saying why rollback is
  impossible for this change.

## Never edit an applied migration

Once a migration has been applied to any shared environment, it is immutable. Create a new
migration to correct mistakes.

## Collapse work-in-progress migrations before review

If iterating on one logical change produced several files, collapse them into one before
opening the PR — delete and regenerate, which is safe while nothing has been applied to a
shared environment. This is different from the deliberate add-nullable → backfill → constrain
sequence, which stays as separate migrations by design.

## After merging the main branch into a feature branch

Long-lived branches frequently produce two divergent heads even without a content conflict.
Check immediately after merging (`alembic heads`, or the equivalent for the tool) and reconcile
with a no-op merge revision before running tests or committing.