From migration-marshal
Use when reviewing, writing, or editing a Postgres schema migration: any DDL such as ALTER TABLE, ADD/DROP COLUMN, ALTER COLUMN TYPE, ADD CONSTRAINT, or CREATE INDEX, including .sql files and migrations generated by Prisma, Drizzle, TypeORM, or Knex. Classifies lock and downtime risk and provides zero-downtime rewrite recipes for high-traffic production tables.
How this skill is triggered — by the user, by Claude, or both
Slash command
/migration-marshal:safe-migrationsThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Your job when this skill is active: for a given migration, (1) classify each
Your job when this skill is active: for a given migration, (1) classify each statement's risk, (2) explain the lock risk briefly, and (3) if unsafe, give the zero-downtime rewrite from the matching recipe below.
Output in this order, and keep it tight. A senior reviewer is brief, not exhaustive.
SET lock_timeout on every statement; note once that each ALTER should set a
lock_timeout, then leave it out of the SQL. No closing recap. Do not restate the SQL
or repeat yourself. If every statement is safe, say so in one line and stop.Label each statement one of:
Risk depends on lock severity, how long the lock is held, and how busy the table is. A brief ACCESS EXCLUSIVE on a low-traffic table is fine; a long one on a high-traffic table is an outage.
Any ALTER TABLE takes a brief ACCESS EXCLUSIVE lock, the coarsest lock, which
blocks all reads and writes. Even a metadata-only change is DANGEROUS on a busy
table if it must queue behind a long-running query: while the ALTER waits, every
query behind it also waits, and the table stalls. Classify RISKY at minimum and
require a lock_timeout guard (Recipe A) on any ALTER TABLE against a hot table.
These copy the whole table under ACCESS EXCLUSIVE for the full duration:
ADD COLUMN ... DEFAULT <volatile>, e.g. gen_random_uuid(), random(),
clock_timestamp(). A volatile default is evaluated per row, which forces a rewrite.ALTER COLUMN ... TYPE ...: most type changes rewrite (and recheck indexes).Do NOT flag these as rewrites:
ADD COLUMN ... DEFAULT <constant> (e.g. 'pending', 1, false): metadata-only
since PG 11. Safe for rewrite (still subject to rule 1's lock-queue risk).ADD COLUMN ... nullable, no default: metadata-only, SAFE.DEFAULT now() or current_timestamp: STABLE, not volatile. SAFE.Adding a constraint validates all existing rows under lock:
ALTER COLUMN ... SET NOT NULL on an existing column: full scan.ADD CONSTRAINT ... CHECK (...) or FOREIGN KEY without NOT VALID: full scan.
(FKs lock the parent table too.)Rewrite with NOT VALID then VALIDATE (Recipe C).
CREATE INDEX (non-concurrent) takes a SHARE lock that blocks writes for the
entire build. ADD CONSTRAINT UNIQUE builds an index under ACCESS EXCLUSIVE.
Use CREATE INDEX CONCURRENTLY (Recipe D); for UNIQUE, build the index
concurrently then attach it with ADD CONSTRAINT ... USING INDEX.
DROP COLUMN and RENAME COLUMN/TABLE break app code still running the old
version mid-deploy. Use expand/contract across releases (Recipe E).
SET lock_timeout = '5s';
-- the ALTER here; fails fast instead of cascading, migration runner retries
Replaces a volatile-default or NOT NULL add. Steps run as SEPARATE migrations or transactions, not one block: -- 1. add nullable (metadata-only) ALTER TABLE {table} ADD COLUMN {col} {type}; -- 2. set default for NEW rows (metadata-only) ALTER TABLE {table} ALTER COLUMN {col} SET DEFAULT {default_expr}; -- 3. backfill existing rows in batches (never one giant UPDATE) UPDATE {table} SET {col} = {default_expr} WHERE {col} IS NULL AND {pk} IN ( SELECT {pk} FROM {table} WHERE {col} IS NULL LIMIT 10000); -- repeat step 3 until 0 rows affected -- 4. enforce NOT NULL without a full-table lock scan (PG 12+): ALTER TABLE {table} ADD CONSTRAINT {table}_{col}nn CHECK ({col} IS NOT NULL) NOT VALID; ALTER TABLE {table} VALIDATE CONSTRAINT {table}{col}nn; ALTER TABLE {table} ALTER COLUMN {col} SET NOT NULL; -- uses the constraint, skips the scan on PG12+ ALTER TABLE {table} DROP CONSTRAINT {table}{col}_nn;
ALTER TABLE {table} ADD CONSTRAINT {name} {constraint} NOT VALID; -- fast, no scan
ALTER TABLE {table} VALIDATE CONSTRAINT {name}; -- scans with a weaker lock, concurrent reads and writes OK
CREATE INDEX CONCURRENTLY {name} ON {table} ({cols});
-- CONCURRENTLY cannot run inside a transaction block. With Prisma or Drizzle,
-- isolate this statement so the runner does not wrap it in BEGIN/COMMIT.
-- Release N: stop reading and writing the column in app code (deploy first)
-- Release N+1: ALTER TABLE {table} DROP COLUMN {col};
-- Renames: add new col, backfill, dual-write, then drop old. Never RENAME live.
Provides UI/UX resources: 50+ styles, color palettes, font pairings, guidelines, charts for web/mobile across React, Next.js, Vue, Svelte, Tailwind, React Native, Flutter. Aids planning, building, reviewing interfaces.
Searches MemPalace before answering questions about past work, people, projects, or prior decisions. Returns verbatim stored content instead of guessing from model memory.
npx claudepluginhub jonedavis/migration-marshal --plugin migration-marshal