Factory Db Migration

Factory DB migration

This skill is the runbook discipline for making a destructive database change. It is invoked before the first time a migration, backfill, or seed touches prod — and re-invoked on every periodic re-import.

The factory ships factory-data-layer.md for how schemas are designed and factory-deployment.md for where migrations execute in CI. This skill is the third leg: how to run the change safely once both upstream pieces are in place. Read all three before proposing a non-trivial prod write.

How to use this skill

• Before any first-of-its-kind destructive prod write — read the Principle of each section. If you can't articulate the principle in your own words, you don't yet understand the change well enough to ship it.
• During design review — use the section list as a checklist: do we have preflight? Idempotency proof? Rollback? Pre-write snapshot? Human gates?
• During incident response — factory-pitfalls.md indexes the failure modes here; jump to the one that matches.

The data is ground truth — the schema accommodates it

Principle. When a constraint rejects historical data, the constraint encoded a wrong belief. The data records what actually happened; the schema is an artifact of someone's understanding of what they thought happened.

Why. Constraints exist to prevent invalid future states. They cannot retroactively make the past invalid. When a constraint added in 2026-05 rejects data from 2024, the meaningful question is "what did 2024 actually look like, and was the constraint added with full domain knowledge?" — not "how do we mangle the 2024 data to fit?" Silently coercing historical data into the new shape destroys information about what the business actually did. That's a category-of-bug worse than the missing constraint.

The pattern: a periodic import surfaces a constraint violation. Before deciding it's a data error, ask: (1) what did the constraint encode? (2) who added it? (3) did they know about the historical reality? If the answer to (3) is no, the constraint is the bug.

Recipe.

1. When a constraint fires on a destructive write, stop. Don't reach for parse-time validation that drops the offending rows.
2. Audit the constraint's origin commit. What was the rationale? Was it backed by domain confirmation, or a developer's mental model?
3. If the historical reality contradicts the constraint, draft a migration that drops or relaxes it. Document the rationale in the migration file comment.
4. Audit downstream code paths that depended on the constraint (server actions, RPCs, dashboard rollups, UI filters). Each may need to keep the rule at its layer even after the DB drops it — for forward-going behavior.
5. File a follow-up ticket for any downstream code that needs to track the schema change separately (unit tests, stale comments, behavior verification).

Failure mode. The cases_invoice_id_channel_chk constraint (Kairos KAI-140) forbade stock_bill cases from linking to invoices. Historical paper-based Stock & Bill orders DO have invoices. First-pass instinct: validate the CSV and reject those rows. That would have silently dropped legitimate historical data. Right move: drop the constraint, keep the forward-going UI/RPC filters, keep commission at $0 for stock_bill regardless of invoice linkage.

Three-stage write contract — preflight, mutate, verify, rollback

Principle. Every destructive write emits four artifacts: a read-only preflight, a transactional mutate, a read-only verify, and a marker-scoped rollback. Each is its own file, runs as its own step, and is human-gated.

Why. Destructive writes fail in one of three classes: preflight failures (FKs missing, constraints will fire, environment not ready), mutate failures (the change itself hits an unexpected constraint, NULL violation, or assertion), and verification failures (the change executed but did not produce the expected post-state). Bundling these into one script makes diagnosis impossible — a failure mid-script leaves you unsure which stage failed and whether to advance or roll back. Separating them turns each into a yes/no gate.

The rollback isn't an afterthought; it's the recovery deliverable. An untested rollback is a wish, not a recovery path. Always run rollback at least once in local cycle before trusting it for prod.

Recipe.

• Preflight (read-only, separate file): verify all FKs exist, target tables are reachable, the executing role has the privileges it needs, no constraints will be tripped by the planned data. Emits PASS/FAIL with structural counts, not row values.
• Mutate (transactional, separate file): the actual change wrapped in BEGIN ... COMMIT. Every assertion fires as RAISE EXCEPTION so the transaction aborts cleanly on any throw. No half-applied state. Final RAISE NOTICE reports counts (rows new / rows amended / totals) — structural only, never row values.
• Verify (read-only, separate file): post-state diff. New-vs-amended-vs-orphaned counts. Per-field amendment breakdowns. Sanity reads against pre-state expectations.
• Rollback (marker-scoped, separate file): DELETE FROM WHERE = ''. Scoped narrowly enough to remove only this run's rows, never pre-existing data. The rollback's wrong-scope failure mode is catastrophic — wrong scope means either you leak rows (rollback too narrow) or you damage pre-existing data (rollback too wide).

Failure mode. Single-file "migration" that mixes preflight checks with mutations with verify queries. Hits an error halfway — was the preflight wrong, or did the mutation fail? Was anything written? Unclear. Operator either retries (potential double-write) or rolls back (potentially against nothing). The four-file split turns each into an independent gate.

Idempotency via natural keys, proven on re-run

Principle. A destructive write that may be re-run (periodic imports, retry-on-failure, ETL reconciliation) must be idempotent on a natural key. Re-running against unchanged input produces zero new rows. Re-running with one row changed updates exactly one row, never duplicates.

Why. Periodic imports inherently get re-run. The natural key is what the script uses to decide "is this row already here?" If the key is wrong (too narrow, too wide, NULL-unsafe), idempotency breaks — re-runs duplicate cases or fail to amend. The natural key is a load-bearing claim that must be proven on-write, not asserted in a comment.

The proof is structural: re-run the same script against the same input and assert rows_new = 0. This is a one-line assertion at the end of the mutate; it surfaces idempotency violations as RAISE EXCEPTION rather than letting them accumulate silently.

Recipe.

• Choose the natural key carefully — it must uniquely identify a row across the entire dataset and be stable across runs (the spreadsheet operator must not be expected to re-key on every run).
• Use NULL-safe equality (IS NOT DISTINCT FROM, not =) — a NULL = NULL is FALSE in standard SQL, which silently breaks idempotency when key columns can be blank.
• Test idempotency on local before trusting it on prod: run the script twice, assert 0 new on the second run. Then change one row and verify exactly one row is amended on the third run.
• Document the tradeoffs the natural key encodes — "if column X changes between runs, the row will look new" is acceptable as a tradeoff but must be explicit.

Failure mode. Natural key omits po_number for a CSV where patient_identifier is blank in 80% of rows. Re-runs duplicate every blank-patient case because the key collapses to too-coarse (facility, date, surgeon) and matches multiple distinct cases. The duplicates are silent — only surfaced by hand-counting after the fact.

Bidirectional update semantics for tri-state columns

Principle. A spreadsheet-owned column with yes | no | blank CSV vocabulary needs a three-branch UPDATE: NULL (blank, no opinion → keep DB), FALSE (explicit no → clear), TRUE (yes → set). A two-branch CASE that conflates no with blank is one-directional silent data loss.

Why. Boolean-derived columns are the natural place to encode operational state that can flip — a charge sheet sent in error and walked back, an invoice marked paid then refunded. The CSV vocabulary distinguishes "I don't know" (blank) from "definitely not" (no). The UPDATE must honor that distinction.

A two-branch CASE that treats both NULL and FALSE the same direction (typically: "keep DB if not TRUE") can only ever set, never clear. The amendment runbook reports N amended correctly but the field doesn't actually change. The operator believes the spreadsheet edit landed; it didn't.

Recipe.

column_name = CASE
  WHEN s.csv_field IS NULL  THEN c.column_name              -- blank → keep
  WHEN s.csv_field IS FALSE THEN NULL                       -- explicit no → clear
  WHEN s.csv_field IS TRUE  THEN COALESCE(c.column_name, derived_value)  -- yes → set if not already
END,

The TRUE branch typically preserves an app-edited timestamp (the operator may have set a precise value); the FALSE branch unconditionally clears. Both branches matter. Don't ship one-way logic just because the test case happened to be set-not-clear.

Failure mode. charge_sheet_sent_at (Kairos KAI-138) UPDATE was a two-branch CASE that conflated NULL and FALSE. Toggle yes→no in CSV → re-run seed → amendment reports 0 new / 384 amended → but the toggled row's charge_sheet_sent_at stayed set. The bug was only caught by an explicit count-based amendment test (A7 in the runbook); silent in any test that only checked "no→yes" direction.

Validate at parse, not at constraint

Principle. Generators and ETL scripts validate row-level rules at parse time with row indices in the error message. They do not produce SQL that the DB constraint catches at mutate-time. The diagnostic gap between "row 142 has invoicepaid=yes but no invoicenumber" (parse-time) and "ERROR: NOT NULL constraint violated on column X" (constraint-time) is enormous.

Why. Constraint errors don't tell you which CSV row caused them — they tell you which DB row violated. The operator has to reverse-engineer which spreadsheet row maps to the failing case, which is hard when the natural key is composite and PHI-shielded. Parse-time validation emits Row 142: invoice_paid=yes requires invoice_number to be set — the operator opens row 142 in the spreadsheet and fixes it.

This also means parse-time errors are PHI-safe by construction — emit row indices, not field values. Constraint errors typically include the offending row's values in DETAIL:, which can leak PHI.

Recipe.

• Every cross-field invariant the DB constraint enforces also belongs in the parser as a validation rule.
• Errors include the row index (1-indexed for human-friendly diagnostics) and the rule name. Never the value.
• Refuse to emit any SQL until validation passes for all rows — no partial outputs.
• Layer with the DB constraint (defense in depth): even if validation is ever bypassed, the DB still rejects. Each layer enforces the invariant independently.

Failure mode. Parser accepts a CSV row with channel=stock_bill, invoice_number=X because nothing forbids it at the row level. Generator emits SQL. Mutate-time CHECK constraint fires on cases_invoice_id_channel_chk. Operator sees ERROR: violates check constraint "cases_invoice_id_channel_chk" — no row index. They have to grep the CSV by hand to find the offender. Time-to-diagnose: 10×.

Layered backup independence — the pre-write snapshot is non-negotiable

Principle. Every destructive prod write is backed by an independent restore path that does not rely on the script's own rollback. The minimum is a portable pg_dump of the affected tables, taken seconds before the write, stored to a location the migration cannot touch.

Why. The script's rollback SQL is itself untested-against-prod the first time it runs there. It assumes the marker scoping is correct, the DELETE order respects FK cascades, and no downstream side effects (triggers, views, downstream-app caches) need separate cleanup. The pre-write snapshot is the recovery path that survives the rollback being wrong.

This connects to the KAI-126 backup doctrine — layered defenses for different failure modes:

• Layer A — provider PITR (Supabase / RDS): fast recovery from logical corruption.
• Layer B — daily independent dumps to portable object storage: survives provider catastrophe.
• Layer C — per-write snapshot of affected tables: the rollback-of-last-resort for this specific operation.
• Layer D — restore drill (monthly): "an untested backup is a wish, not a backup."
• Layer E — access discipline: read-only role for dumps, append-only bucket-write role, separate KMS keys.

A senior design layers these so each failure mode (logical corruption / provider outage / bad migration / forensics) has a matching recovery path with a defined RPO/RTO. See factory-security.md §Layered backups and the KAI-126 doctrine comment for the full table.

Recipe.

# Before any destructive prod write — Layer C:
pg_dump --format=custom --compress=9 --no-owner --no-privileges \
  -t public. -t public. ... \
  --file=pre--$(date -u +%Y-%m-%dT%H-%M-%S).pgdump \
  "$PROD_DB_URL_READONLY"

# Manifest sidecar (timestamp + schema HEAD + per-table row counts + SHA-256):
scripts/snapshot-manifest.sh pre--.pgdump > pre--.manifest.json

# Then run preflight → mutate → verify. If anything goes wrong:
pg_restore --clean --if-exists --no-owner --no-privileges \
  --dbms="$PROD_DB_URL_ADMIN" pre--.pgdump

Failure mode. "We have the rollback SQL, we don't need a backup." The rollback SQL has never run against prod, the operator hits a constraint-cascade they didn't anticipate, and the rollback leaves orphan rows behind. PITR isn't on (cost decision deferred). No Layer C snapshot taken. The corrupt state lives in prod for hours while a forensic recovery is improvised.

Human gate at every step — LLM hands the command, operator executes

Principle. During a multi-step destructive runbook, the LLM hands the command; the operator executes. This holds even for "safe" structural-only queries (row counts, schema reads). The LLM never auto-runs runbook commands, even via MCP.

Why. Two distinct principles, both load-bearing:

• PHI safety. Anything that surfaces PHI-bearing columns must not route through the LLM. (See factory-security.md §PHI never enters the LLM.)
• Human gate. The operator is the one taking the action against prod. Each step is a deliberate approval boundary. If the LLM runs the step, there is no moment of "yes, execute now." The first principle alone would allow MCP for count queries; the second principle does not.

When the operator establishes the contract by asking "is there an easier interface than terminal?" — they are asking for a better-to-use interface for their execution. They are not asking the LLM to take over execution.

Recipe.

• The LLM's role during a runbook:
• Hand the exact command (env vars inlined; assume nothing about shell state).
• Interpret the structural output the operator pastes back.
• Generate the next command.
• Update tasks, write Linear comments, propose code fixes.
• The LLM's forbidden actions during a runbook:
• Running psql, pg_dump, supabase, or any MCP call against the project DB.
• Auto-running "safe" count queries to "drive faster."
• Touching the prod DB in any way that bypasses the operator.
• The operator's responsibility:
• Run each step in their terminal or Studio.
• Paste back the structural summary only — never the row values. (Counts. Pass/fail. RAISE NOTICE summary lines. Error class names without DETAIL lines.)
• Refuse to advance to the next step if the current step's output is

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: nonlinear-xyz
• Source: nonlinear-xyz/factory-kit
• License: MIT

Install and usage instructions live in the source repository linked above.