House-move inventory: Schema migrations with PRAGMA user_version - Part 6

~9 min read

For the first day of this project, changing the schema meant deleting the database. That is fine until the database contains a week of real packing. The fix was a migration system, which took about a hundred lines, and then over the following weeks it taught me three things I didn't expect.

The runner

SQLite has a spare integer in the file header, PRAGMA user_version, that the engine itself never uses. That's all the bookkeeping a forward-only migration runner needs. In storage/db.py:

• SCHEMA is a frozen baseline - the tables as they were when migrations started. It never changes.
• MIGRATIONS is an append-only list of (N, (SQL, ...)), numbered from 1.
• apply_migrations runs every entry whose number exceeds user_version, each in its own transaction, and bumps user_version as part of that transaction:

conn.execute("BEGIN")
try:
    for statement in statements:
        conn.execute(statement)
    conn.execute(f"PRAGMA user_version = {version}")
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise

It also refuses two things: a migrations list with gaps (RuntimeError, "must be numbered 1, 2, 3, ... with no gaps") and a database whose user_version is newer than the code knows about. The second one matters on a deploy: if I ever roll the app back past a migration, I want a loud error rather than an old build quietly mis-reading a newer schema.

The first real migration was tiny - items.purchased_on, a nullable date - and the whole point was that opening an older inventory.db with a newer build now upgrades it in place and keeps the data.

Lesson 1: SQLite's ALTER TABLE is small

It can add a column and rename things. That's it. So the recipe has two forms:

• Additive change: ALTER TABLE ... ADD COLUMN. Most migrations are this - retail value, French labels, box decision, sub-status, dimensions.
• Restructuring: create the new table, copy, drop the old, rename, all in one migration entry.
• Dropping a whole table isn't restructuring and gets a plain DROP TABLE - but children before parents, because every connection runs PRAGMA foreign_keys = ON.

A test helper builds fake migrations as db.MIGRATIONS + [(len+1, ...)], which is how the "no gaps" rule shows up in practice.

Lesson 2: some migrations are data migrations

Migration 7 was the interesting one. An auth library change made user ids opaque strings instead of sequential integers, which meant rebuilding users, sessions and recovery_codes with a TEXT id and remapping the foreign keys in the other two tables to match.

SQLite has no UUID function, so the migration generates a random UUID4 per existing account in pure SQL - hex(randomblob(...)) assembled into the 8-4-4-4-12 shape, with the version and variant nibbles forced. That's the kind of code you shouldn't trust on inspection, so I didn't. A scratch script built a realistic fixture - two accounts, one with 2FA on, sessions for each, recovery codes for one - ran the migration, and checked that PRAGMA foreign_key_check was clean, that every session and recovery code pointed at the right new id, and that TOTP state survived. Those checks now live in tests/storage/test_migrations.py.

(Those tables are gone now - post 10 is about that - but the technique, "write the realistic-fixture test before trusting a data migration", is the keeper.)

Lesson 3: the baseline can resurrect things

This is the one that would have bitten me in production. Migration 14 drops the users, sessions and recovery_codes tables, because authentication moved to identity-system. Easy enough. But init_db used to run the baseline SCHEMA on every process start, and that baseline still declares those tables - migrations 6 and 7 are written against them, so it has to. Since the baseline uses CREATE TABLE IF NOT EXISTS, the next restart after migration 14 would have cheerfully recreated all three, empty.

Nothing would have errored. The tables would just have reappeared, silently, and the "dropped for good" migration would not have been.

The fix is a sentinel check: apply the baseline only if the boxes table does not already exist.

def _baseline_applied(conn):
    return conn.execute(
        "SELECT 1 FROM sqlite_master WHERE type='table' AND name='boxes'"
    ).fetchone() is not None

def init_db(conn):
    if not _baseline_applied(conn):
        conn.executescript(SCHEMA)
    apply_migrations(conn)

boxes is the right sentinel because it's in the baseline and no migration has ever dropped it. The project notes now carry a warning not to remove that guard "without re-adding some other way to keep migration 14 durable". Idempotent setup scripts are only idempotent for the schema as it was when you wrote them.

Backups before you need them

Migrations pair with the DB tool (python -m inventory.cli.db): status shows applied and pending migrations (and rejects a database newer than the code), migrate applies them, and backup uses SQLite's online-backup API, so the copy is consistent even while the API is running. restore validates that the source is a SQLite file and not newer than the code, and clears stale -wal/-shm files. A backup that you've never restored is a hope, so the restore path got the same care as the backup.

Next: files that don't belong in the database - invoices, photos, and an iPhone format that nothing else can read.

Previous: ← The SPA: small pieces, no UI library · Next: Invoices, photos and HEIC →