Truestack database migrations

truestack-database-migrations

Changing a live schema is the highest-risk routine work on a self-hosted single server: there is no replica to fail over to, and one stuck lock can freeze the whole app. The job is to evolve the schema without an outage and without losing data — accuracy and stability over cleverness, every breaking change split across deploys.

First, read project memory — CLAUDE.md is auto-loaded (Principles + Boundaries); consult .ai/memory/ for the DB engine, migration tool, and recorded commands. If none exists, run truestack-project-memory. This is ask-first, high-risk work: route the plan through truestack-architecture-planning's approval gate before writing any migration. Then verify the PreToolUse gate before relying on it — check it is registered in the active hooks config. If wired, it hard-stops DROP/TRUNCATE/destructive DDL for a human yes before the command runs, even if settings would allow it; if not wired, obtain that explicit human yes yourself before any destructive statement. (Details: hooks/README.md at the truestack repo root, when that repo is present — a standalone copy of this skill won't have it.)

Where this skill sits: it owns the migration artifact — reversible up/down DDL, the backfill, the expand/contract sequencing. The data-access code that uses the new schema is truestack-backend-development; sequencing the migrate step in the deploy pipeline is truestack-ci-and-delivery.

1. Never break in one deploy — expand then contract

A zero-downtime breaking change is physically impossible in a single release. Old and new app code run side by side during a deploy, so the schema must stay compatible with both at once. Split it:

• Expand (deploy 1) — add the new column/table, dual-write old+new, backfill, leave the old schema intact. Nothing breaks: both code versions still work.
• Contract (a later deploy, after the new code has soaked) — flip reads to the new path, stop writing the old, then drop. Sequence the irreversible drop as its own final deploy.

The phase boundary is your safe checkpoint: if anything looks wrong you pause indefinitely on the prior phase instead of rolling back under fire.

2. Lock + timeout discipline (the single most-missed safeguard)

Before any DDL, set a short lock timeout and wrap the attempt in a retry loop with exponential backoff + jitter — even seasoned DBAs skip this (exact timeout SQL per engine → references/engines.md). The real danger: a DDL waiting on an exclusive lock queues behind a long-running query, and then every later query — plain SELECTs included — queues behind the DDL. One stuck ALTER cascades into a full app freeze, and on a single box that is a total outage. Pre-check for transactions older than ~1 min before running. A failed-and-retried migration always beats an outage.

3. Know instant DDL from a full-table rewrite — rewrite the dangerous ones

Some DDL is metadata-only and instant; some rewrites or scans the whole table under a full lock — and the split is engine-specific (per-engine classification + safe-rewrite recipes, including the online-index-build traps → references/engines.md). Engine-neutral rules:

• Never enforce NOT NULL, a type change, or an inline UNIQUE/FK/CHECK directly on a big live table — stage it: add nullable → backfill → validate/enforce as a separate weak-lock step.
• Build indexes online, and make the migration idempotent about a half-built leftover from a failed attempt — the single highest-frequency mistake in real migration PRs.
• Enforce all of this mechanically with a migration linter in CI / pre-commit — humans miss it every time.

4. Backfill in idempotent, bounded batches — never one giant UPDATE

A single UPDATE 50M rows holds a lock and bloats exactly like the DDL you were avoiding, and a long transaction is the catch-up storm that wedges everything. Instead:

• Bounded batches of ~1k–5k rows, each its own short transaction.
• Keyset pagination — WHERE id > :last_id ORDER BY id LIMIT n. Never OFFSET (re-scans from the top; degrades from seconds to minutes as it advances).
• Idempotent — only touch rows still needing it (WHERE new_col IS NULL), so a partial failure + retry never double-applies and resumes from the last id.
• On one box the backfill competes directly with production: sleep between batches, watch primary CPU/IO and MVCC bloat, run VACUUM after, and throttle or pause on degradation.

5. Reversible by design — but the down script is a tested artifact, not a comfort blanket

Write an explicit down for every up and test it in CI (apply → rollback → re-apply). Auto-generated/irreversible downs (dropped data, lossy type changes) are a false safety net that shatters under real data. The deeper rule: prefer roll-FORWARD over rollback for anything that touched data — a dropped column's data is gone, so the real recovery path is the next expand step, not a down migration. Reserve true reversibility for cheap metadata-only steps; for a destructive step the "rollback" is the prior deploy still running, because you sequenced the drop last.

6. Verify parity before flipping reads and before the drop

The dual-write window is where silent divergence hides — a missed code path (a raw SQL update, a bulk job, a second service) lets new data drift, and you only find out after dropping the old column. So:

• Run a count/checksum comparison (old vs new) over the full set before trusting the new path.
• Ideally shadow-read on live traffic (read both, compare, log mismatches) for a soak period.
• Gate the contract step on a clean parity report, not on "the backfill finished".
• Decouple the app from the column before dropping it — most ORMs SELECT */list all mapped columns, so dropping a column the deployed app still reads errors every query instantly. Order: ship code that stops referencing it → confirm all instances are on that build → drop later. Same for renames: never rename a live column/table in place — add-new + dual-write + backfill + switch + drop-old.

On this single server

Fleet-grade online-DDL tools (MySQL's pt-online-schema-change, gh-ost — notes in references/engines.md) assume replicas and load headroom you don't have — usually not your path here. Your toolkit is native transactional DDL + expand/contract + throttled batched backfills, all on the one box that also serves users. So: schedule heavy backfills for low-traffic windows; keep disk headroom (rewrites and online index builds temporarily duplicate the table/index — a "safe" migration can still fill the disk and crash the box); and lean doubly on the lock-timeout + retry discipline and the "expand is always non-breaking" rule, because there is no replica to fail to.

Gate, then hand off

Require, before any risky migration runs: a CI migration linter that blocks dangerous DDL, a PR sign-off label, a written rollback/forward-recovery plan, a fresh backup/PITR checkpoint taken immediately before, and a confirmation of which app versions run concurrently. Dry-run the exact migration against a production-sized copy to measure real lock duration and runtime — "it was instant on a 1k-row dev table" is how teams meet the 40-minute production rewrite. Implement the scripts via truestack-backend-development; run truestack-quality-control before "done". If the change altered a recorded command or decision, update .ai/memory/ in the same change so the code↔memory tally stays balanced.

Explain it simply

Say in one plain line what each step does and to which deploy it belongs ("Deploy 1 adds a nullable email_verified and backfills it; reads still use the old flag"). Show the expand→contract sequence as a short ordered list so the user sees that the drop comes last and separately. Before anything irreversible, state the recovery path in one sentence and get an explicit yes — never run a destructive migration on a prompt's say-so alone.