37. One baseline schema for 1.0#

Date: 2026-10-27 (#267, #321)

Status#

Accepted. Revises the migration policy in 3. One process, one SQLite database, one folder of files (numbered SQL files from 0001_init.sql) and the “baseline N-1 plus migration N” line the docs carried for it, which was never implemented.

Context#

By 0.16.0 the schema was built by eighteen migrations, a table of Python steps (store.AFTER) and store/fixes.py: ADD COLUMN guards, a writable_schema edit of a CHECK, table rebuilds, backfills. A fresh install replayed all of it, and about fourteen test files built historical schema states to test it. The live store is one deployment kept current by its owner, and 1.0 is the last moment its history can be dropped without cost.

Decision#

  • One baseline. src/scrap/migrations/baseline.sql is the schema at version 18 (db.BASELINE): the stored SQL of every object of a database built by the whole chain, in dump order, plus the queue_state seed row and the full-text index rebuild. It is generated by python -m scrap.store.baseline, never edited by hand, and was proved equal to the chain (columns, CHECK text, indexes, triggers, seed rows) in CI before the chain was deleted (#370).

  • 1.0 starts from 18 and nothing older. Migrations 0001 to 0018, their Python steps and store/fixes.py are deleted, as are the fallbacks that read an older schema’s tables (roles). A database or a backup below schema 18 is refused, before anything is changed, with upgrade with scrap 0.16.x first (it migrates to schema 18), then use 0.17.0 or later; scrap restore checks the copy before the swap. 0.16.0 is the last release that migrates older stores. There is no bridge.

  • A fresh database is made from the baseline, in one transaction, then any later scripts. A database at 18 is opened as it is: no migration runs and no schema change is made.

  • Later migrations accumulate normally. The app will be shared after 1.0, so small numbered scripts follow the baseline, 0019_*.sql onward, numbered by merge order with no gaps. The runner stays positional with an offset: script i is version BASELINE + 1 + i, and a test checks that each file name’s number matches. Nothing folds on every PR.

  • The guard stays. tests/data/schema_baseline.json is the frozen structure of the baseline; tests/data/schema_head.json is the baseline plus every later script. A migration PR fails tests/test_baseline.py until its schema diff is reviewed and refrozen (--snapshot). Folding the later scripts into a new baseline is deliberate and rare (--fold, bump BASELINE, delete the folded scripts), safe whenever the live store has passed them.

  • Emergency path. To open a store below 18, use an earlier release to migrate it.

Consequences#

  • A fresh install no longer replays history; the schema is one readable file, and the tests that built old schema states are gone (their intent is kept where it still means something: refusal below the baseline, migrating from 18 to a later version with a synthetic script, the baseline against the frozen snapshot).

  • The history of how the schema came to be is in the earlier ADRs and the 0.16 tag, not in the tree.

  • A backup older than schema 18 cannot be restored by 1.0 directly. Restore it with 0.16.x first, take a new backup, then use 0.17.0 or later.

  • ADRs 12, 20, 27, 28, 31, 33 and 34 name migrations by number; those numbers now mean “part of the baseline”.