Runbook — Migrations

How to author, sequence, and apply substrate SQL migrations safely.

Source: docs/runbooks/migrations.md

Substrate migrations — conventions and pitfalls

Forward-only SQL migrations live in src/scidex_substrate/migrations/ and are applied by the runner in core/migrate.py at boot.

This runbook documents the three non-obvious rules that cause real outages when ignored.

Rule 1 — the journal keys on FILENAME, not prefix

substrate_schema_migrations.filename is a TEXT PRIMARY KEY. The runner loads the set of already-applied filenames, then iterates *.sql files sorted lexicographically and runs anything not in the journal.

Concretely:

  • Renaming an applied migration (e.g. 0043_persona_runs.sql → 0044_persona_runs.sql) creates a phantom-unapplied file. On the next deploy, the runner sees the new filename as new and re-executes the SQL. CREATE TABLE IF NOT EXISTS no-ops, but ALTER, CREATE INDEX (without IF NOT EXISTS), data backfills, and most constraint additions will fail and crash boot.

  • Editing the content of an applied migration is also silent: the journal only stores the filename, so the new SQL is never replayed. If you need to fix a bug introduced by an old migration, write a NEW migration that corrects state — never edit the old file.

Therefore: once a migration has been applied to any prod database, its filename and content are frozen. Pick a fresh prefix for any new change.

Rule 2 — pick the next free 4-digit prefix

The convention is monotonic 4-digit numeric prefixes (0001_initial_schema.sql, 0002_messages.sql, …). To pick the next free number:

scripts/next_migration_number.sh
# e.g. prints 0045

Equivalent by hand:

ls src/scidex_substrate/migrations/ | grep -E '^[0-9]{4}_' | tail -1

Increment by one, pad to 4 digits.

Gaps are harmless to the runner (it just sorts what’s there). But collisions (two files with the same prefix) are dangerous because:

  • they make the apply order ambiguous between contributors who land in parallel, and

  • they make code review of “is migration N applied?” questions confusing.

Rule 3 — write legacy-aware CREATE TABLE against existing names

A CREATE TABLE in a NEW migration that uses the same name as an already-existing prod table is the wedge class that took out 0050 through 0065 for ~14 days in 2026-04 (PR #485 unwedge). The pattern:

-- 0050 (BAD — original):
CREATE TABLE IF NOT EXISTS evidence_links ( ... source_ref TEXT ... );
CREATE INDEX idx_evidence_links_source ON evidence_links(source_ref);

A pre-existing v1-mirrored evidence_links table had a totally different shape (no source_ref column). CREATE TABLE IF NOT EXISTS silently no-op’d against the legacy table; the next CREATE INDEX crashed because source_ref did not exist. The whole migration rolled back on every boot. The journal never advanced past 0049. Migrations 0051-0065 then landed only via manual operator INSERTs, with several silently NOT applied at all.

The fix (PR #485, canonical example) is a legacy-rename DO-block inserted before the CREATE TABLE:

-- 0050 (GOOD — PR #485):
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.tables
    WHERE table_schema = 'public' AND table_name = 'evidence_links'
  )
  AND NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'evidence_links'
      AND column_name = 'source_ref'   -- the SPEC-shape sentinel column
  ) THEN
    EXECUTE 'ALTER TABLE evidence_links RENAME TO evidence_links_v1_legacy';
    -- Also rename PK / indexes / sequences so the new table can claim
    -- the natural names (these are global per schema):
    EXECUTE 'ALTER INDEX IF EXISTS evidence_links_pkey '
            'RENAME TO evidence_links_v1_legacy_pkey';
    -- ... (repeat for each index / sequence carried by the legacy table)
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS evidence_links ( ... source_ref TEXT ... );
CREATE INDEX IF NOT EXISTS idx_evidence_links_source ON evidence_links(source_ref);

Detection logic in the DO-block:

  • check information_schema.tables for the name (legacy table present)

  • check information_schema.columns for a SPEC-shape sentinel column that the legacy table is known not to have (its absence proves the shape is legacy).

If both conditions hold, rename the legacy table and its indexes / sequences out of the way. The rename is non-destructive and reversible — no rows are dropped — and idempotent across reboots because once the rename has happened the sentinel-column check no longer matches.

Alternative: drop IF NOT EXISTS

If you genuinely expect the table NOT to exist and a collision should crash boot loudly, just write CREATE TABLE <name> (no IF NOT EXISTS). The runner will fail the migration on collision, your deploy will be visibly broken, and you’ll fix it before it festers.

Why “silently no-op” is the worst outcome

The original 0050 wedge survived for two weeks because the failure mode was invisible: CREATE TABLE returned success (no-op), only the follow-up CREATE INDEX crashed, and the journal never recorded 0050 so the next boot re-tried the same SQL with the same outcome. The two acceptable patterns above (DO-block rename, or no IF NOT EXISTS) both surface the collision immediately — either by handling it correctly, or by failing the deploy.

CI enforcement

scripts/ci_check_migration_filenames.py runs in CI (and in the pre-push hook) and fails the build on any new prefix collision (Rule 2). Existing applied collisions are recorded in scripts/migration_collisions_allowlist.txt and warn-only.

scripts/ci_check_migration_table_collision.py runs in CI and enforces Rule 3: any NEW migration (one not yet recorded in substrate_schema_migrations) that uses CREATE TABLE IF NOT EXISTS <name> against a <name> already present in the live scidex_v2 public schema MUST either include a legacy DO-block (detected structurally — any DO $$ … information_schema.{tables,columns} … END $$) OR be listed in scripts/migration_collision_allowlist.txt. The check skips cleanly when no DSN is set, so it doesn’t break the unit-test path. PR #485 is the canonical fix and the example to mirror.

scripts/ci_check_migration_journal_drift.py is the runtime-side companion: it compares disk against the journal and surfaces any migration that didn’t land. The collision check is the prevention; the journal-drift check is the detection.

Two known prefix collisions live on the allowlist as of 2026-04-30:

prefix files reason
0043 0043_etl_runs_lookup_index.sql (SPEC-044), 0043_persona_runs.sql (SPEC-020) landed concurrently, both applied
0044 0044_debate_orchestrator_runs.sql (SPEC-045), 0044_watchdogs_and_verb_invocations.sql (SPEC-048) hotfix landed alongside SPEC-045

Renaming any of these would trigger Rule 1.

When a CI failure is right

If the lint script complains about your new migration, the fix is almost always:

  1. N=$(scripts/next_migration_number.sh)

  2. mv src/scidex_substrate/migrations/${OLD}_*.sql src/scidex_substrate/migrations/${N}_*.sql

  3. Re-run python scripts/ci_check_migration_filenames.py until clean.

Do not add your new file to the allowlist to silence the check. The allowlist is a record of past mistakes, not a workaround.