guarded-db-migration

Guarded DB Migration

Why this skill exists

The two classic agent-caused database incidents are: (1) risky DDL placed in a request-time schema initializer, so the first failure takes down every route that touches the table; and (2) a migration script pointed at the wrong database — a legacy project, a teammate's branch, production instead of staging — because the connection string was ambient. Both are fully preventable with three habits: the additive/guarded split, the expected-host guard, and a rehearsal on a disposable copy of production.

1. The additive/guarded split (decide where the DDL lives)

Runtime schema initializers (code that runs CREATE TABLE IF NOT EXISTS on boot or first request) may contain only additive, race-tolerant, cannot-fail-on-live-data statements:

• CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS (non-unique), ADD COLUMN IF NOT EXISTS
• Idempotent backfills that tolerate concurrent execution

Everything that can fail on live data goes in a guarded one-shot script — never in the initializer:

• CREATE UNIQUE INDEX (fails on existing duplicates)
• DROP anything, ALTER ... DROP CONSTRAINT, primary-key swaps
• Backfills that assume exclusive access or specific data shape
• Anything requiring a duplicate scan or data repair first

Rule of thumb: if the statement could throw because of the data (not just the schema), it is one-shot-script material.

2. The guarded one-shot script pattern

Write the migration as a standalone script (e.g. scripts/<name>-migration.ts) with two subcommands:

<script> preflight   # read-only: reports duplicates/violations/row counts; exit nonzero if migrate would fail
<script> migrate     # performs the DDL, in order, inside the smallest safe transaction scope

Non-negotiable guards in migrate:

1. Expected-host guard. The script takes EXPECTED_DATABASE_HOST (or equivalent) and refuses to run unless it matches the host parsed from DATABASE_URL. This converts "wrong ambient connection string" from an incident into an error message. Never default it; never allow a bypass flag.
2. Preflight-first. migrate re-runs the preflight checks internally before DDL — the world may have changed since you ran preflight manually.
3. Idempotence or a clear refusal. Running migrate twice must either be a no-op or fail loudly before touching anything.
4. Print everything. Target host, database, each statement as it runs, and a post-run verification summary. The transcript is the evidence.