ericrisco/rsc-harness / skills/db-migrations
db-migrations
VerifiedUse when a schema change must ship without downtime — NOT NULL, rename, type change, or backfilling millions of live rows — for the expand-contract sequence and the lock/batching discipline that keeps each step from freezing prod. NOT lock internals or EXPLAIN (that is `postgresdb`), NOT drizzle-kit mechanics (that is `drizzle-orm`), NOT PITR (that is `backups`).
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 12.9 KB |
| evals/README.md | markdown | 774 B |
| evals/cases.yaml | config | 3.1 KB |
| references/backfill-and-batching.md | markdown | 4.0 KB |
| references/expand-contract-playbook.md | markdown | 5.5 KB |
| references/tools-and-runners.md | markdown | 5.1 KB |
| scripts/verify.shexecutable | script | 4.6 KB |
references/expand-contract-playbook.md
View on GitHub# Expand-contract playbook
Ordered deploy/migrate checklists for the common changes. Every example DDL sets `lock_timeout` first
and uses the non-blocking path, so it is safe to ship as written. Each phase is a **separate deploy**;
the cooling period between *switch reads* and *contract* is what preserves a no-schema-change rollback.
Notation: `M` = a migration (schema change), `A` = an app deploy. They alternate.
---
## Add a `NOT NULL` column
A single `ADD COLUMN ... NOT NULL DEFAULT <expr>` or `SET NOT NULL` validates/rewrites the whole table
under lock. Split it.
1. **M1 (expand)** — add the column nullable; metadata-only.
```sql
SET lock_timeout = '1s';
ALTER TABLE users ADD COLUMN status text;
```
2. **A1** — app dual-writes `status` on every insert/update (defaults it in code).
3. **Backfill** — fill historical rows in batches (see `backfill-and-batching.md`).
4. **M2** — add the constraint without a blocking scan, then validate, then flip `NOT NULL`.
```sql
SET lock_timeout = '1s';
ALTER TABLE users ADD CONSTRAINT users_status_nn CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_status_nn;
ALTER TABLE users ALTER COLUMN status SET NOT NULL;
```
On PG 12+, a validated `CHECK (col IS NOT NULL)` lets `SET NOT NULL` skip its own full scan.
---
## Rename a column
The running app reads the old name, so you cannot rename in place. Run a dual-name window.
1. **M1 (expand)** — add the new column nullable.
```sql
SET lock_timeout = '1s';
ALTER TABLE users ADD COLUMN email_address text;
```
2. **A1** — write BOTH `email` and `email_address`; keep READING `email`.
3. **Backfill** `email_address` from `email` in batches; verify counts/checksum.
4. **A2** — read `email_address`; still dual-write both. **Cooling period starts.**
5. **M2 (contract)** — days later, after the new app has run clean.
```sql
SET lock_timeout = '1s';
ALTER TABLE users DROP COLUMN email;
```
A view aliasing the new column to the old name can shorten the app-side change, but it does not remove
the need for the cooling period.
---
## Split one column into two
Example: `full_name` → `first_name`, `last_name`.
1. **M1** — add both new columns nullable.
```sql
SET lock_timeout = '1s';
ALTER TABLE people ADD COLUMN first_name text;
ALTER TABLE people ADD COLUMN last_name text;
```
2. **A1** — on write, populate all three (`full_name` plus the two parts).
3. **Backfill** the split for historical rows; verify a reconstructed `full_name` matches the original.
4. **A2** — read the split columns; keep writing `full_name` through the cooling period.
5. **M2 (contract)** — drop `full_name` after cooling.
Merging two columns into one is the same shape with the directions reversed.
---
## Change a column type
A non-trivial type change rewrites every row and can invalidate the running app's expectations. Treat it
as a rename to a new column.
1. **M1** — add the new column with the target type, nullable.
```sql
SET lock_timeout = '1s';
ALTER TABLE orders ADD COLUMN amount_cents bigint;
```
2. **A1** — dual-write both old and new; read the old.
3. **Backfill** with the conversion expression in batches; verify.
4. **A2** — read the new column; keep dual-writing.
5. **M2 (contract)** — drop the old column after cooling.
This avoids the in-place `ALTER COLUMN ... TYPE`, which takes an exclusive lock and rewrites under it
(and, with a `USING` expression, can fail mid-rewrite on bad data).
---
## Drop a column or table
Dropping is a *contract* step, never the first move — the running app may still reference it.
1. **A1** — ship an app version that no longer reads or writes the target.
2. Wait the cooling period; confirm via logs/metrics that nothing touches it.
3. **M1 (contract)** — drop it.
```sql
SET lock_timeout = '1s';
ALTER TABLE users DROP COLUMN legacy_flag;
```
`DROP COLUMN` is metadata-only and fast in Postgres, but the lock it needs is brief-yet-exclusive, so
keep `lock_timeout` short and retry rather than queue behind a long transaction.
---
## Add a foreign key
`ADD CONSTRAINT ... FOREIGN KEY` validates every existing row under lock. Split validation off.
1. Ensure the referenced column is indexed/unique (add that index `CONCURRENTLY` first if missing).
2. **M1** — add the constraint as `NOT VALID` (instant; only new/changed rows are checked).
```sql
SET lock_timeout = '1s';
ALTER TABLE orders
ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
```
3. **M2** — validate existing rows without blocking writes.
```sql
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;
```
If validation reveals orphan rows, that is a data-correctness problem — clean it before validating
(see `../../data-cleaning/SKILL.md`), do not loosen the constraint.
---
## Add an index on a live table
Plain `CREATE INDEX` holds a SHARE lock and blocks writes for the whole build — the classic "our deploy
deadlocks when we add an index" symptom.
```sql
-- This statement must NOT run inside a transaction block.
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);
```
A failed `CONCURRENTLY` build leaves an INVALID index. Find it and rebuild:
```sql
DROP INDEX CONCURRENTLY idx_orders_user_id;
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);
```
How to make a runner skip its transaction wrapper for this one statement is in `tools-and-runners.md`.
For Postgres lock-level semantics (which lock blocks what), see `../../postgresdb/SKILL.md`.
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |