Smoke-Testing Migrations on Edge Fleets

A schema migration that passes against an empty test database has proven its syntax and nothing else. The migration that matters runs against a device that has been logging sensor data for eight months: a table with two million rows, an index near the storage ceiling, and foreign keys the original developer half-remembers. Push that migration to ten thousand such devices and a single unhandled case — an ALTER TABLE that rewrites the whole table on a nearly-full eMMC, a NOT NULL column added without a default against existing rows, a foreign key that strands children — becomes a fleet-wide outage you cannot reach with a keyboard. This page covers proving a migration against a representative production snapshot first: copy the snapshot, apply the change in a transaction, check structural and referential integrity, assert the version advanced, and roll the whole thing back on any failure. It is the schema counterpart to configuration validation within the Python Deployment Validation Pipelines topic, part of the SQLite Architecture & Production Hardening guide.

Migration smoke test against a copied device snapshot A device snapshot is copied to a scratch database. The harness applies migrations inside a single transaction, then runs integrity_check and foreign_key_check and asserts the target user_version. If all checks pass, the transaction commits and the migration is cleared for rollout. If any check fails, the transaction rolls back and the migration is rejected, leaving the snapshot untouched. Copy snapshot scratch.db Migrate in txn integrity + FK check assert user_version all pass? Commit · clear rollout approved Rollback · reject snapshot untouched yes no

Diagnosis

The migration’s position in the schema timeline is tracked by PRAGMA user_version — a 32-bit integer stored in the database header that SQLite never touches on its own, reserved entirely for the application to record which migrations have run. Before applying anything, read it from the snapshot to confirm where the device actually is, because a snapshot pulled from the field may be several versions behind the one CI assumes:

import sqlite3

snap = sqlite3.connect("snapshots/gateway-07.db")
uv = snap.execute("PRAGMA user_version;").fetchone()[0]      # app-managed migration counter
sv = snap.execute("PRAGMA schema_version;").fetchone()[0]    # engine-bumped on every DDL change
print(f"snapshot at user_version={uv}, schema_version={sv}")
snap.close()

user_version is your ledger of which migrations have applied; schema_version is a separate counter the engine increments automatically on every DDL change and uses to invalidate prepared statements — never overload it as a migration marker, but reading it confirms the schema has not been altered out of band. If user_version reads 4 and your migration set expects to advance 4 → 5, the snapshot is correctly positioned. If it reads 2, the device missed migrations 3 and 4, and applying 5 directly will fail or corrupt — the smoke test must run the full 2 → 5 path, not just the newest step. Diagnosing the real starting version against your migration ledger is what tells you which path to test.

Solution

The harness copies the snapshot to a scratch file, applies the intervening migrations inside a single transaction, verifies structure and references, asserts the version advanced to the target, and rolls back the entire attempt on any failure so a rejected migration leaves nothing behind. Copying first means the test can run against a production snapshot without risk, and the transaction boundary means a mid-migration failure unwinds atomically — the same guarantee the device relies on:

import shutil
import sqlite3
import logging

logger = logging.getLogger("migration_smoke")

def smoke_test(snapshot: str, migrations: dict[int, str], target: int) -> bool:
    """Apply migrations to a copy of a device snapshot; return True only if fully verified."""
    scratch = snapshot + ".scratch"
    shutil.copy2(snapshot, scratch)                          # never touch the real snapshot
    conn = sqlite3.connect(scratch, isolation_level=None)    # autocommit: we drive txns by hand
    try:
        conn.execute("PRAGMA foreign_keys = ON;")            # enforce FKs during the migration
        assert conn.execute("PRAGMA foreign_keys;").fetchone()[0] == 1, "FK enforcement off"

        start = conn.execute("PRAGMA user_version;").fetchone()[0]
        conn.execute("BEGIN IMMEDIATE;")                     # take the write lock up front
        for version in range(start + 1, target + 1):
            stmt = migrations.get(version)
            if stmt is None:
                raise RuntimeError(f"no migration registered for user_version {version}")
            conn.executescript(stmt)                         # each step's DDL + data backfill
            conn.execute(f"PRAGMA user_version = {version};")  # advance the ledger in the same txn

        # Structural check: integrity_check walks the B-tree and reports the first faults.
        integ = conn.execute("PRAGMA integrity_check;").fetchone()[0]
        if integ != "ok":
            raise RuntimeError(f"integrity_check failed: {integ}")
        # Referential check: distinct from integrity_check; finds rows orphaned by the migration.
        orphans = conn.execute("PRAGMA foreign_key_check;").fetchall()
        if orphans:
            raise RuntimeError(f"foreign_key_check found {len(orphans)} orphaned rows")

        final = conn.execute("PRAGMA user_version;").fetchone()[0]   # READ the version back
        if final != target:
            raise RuntimeError(f"user_version is {final}, expected {target}")

        conn.execute("COMMIT;")                              # only reached when every check passed
        logger.info("migration %s -> %s verified on %s", start, target, snapshot)
        return True
    except (sqlite3.Error, RuntimeError):
        conn.execute("ROLLBACK;")                            # atomic unwind; scratch is discarded
        logger.exception("migration smoke test failed on %s", snapshot)
        return False
    finally:
        conn.close()

Two properties make this a real safety gate. BEGIN IMMEDIATE acquires the write lock before the first statement, so the harness fails fast on contention rather than partway through DDL, and the version bump happens inside the same transaction as the schema change — the ledger and the schema advance together or not at all. The verification is deliberately two distinct pragmas: integrity_check validates B-tree structure but does not validate foreign keys, so foreign_key_check runs as its own gate, the same read-back discipline detailed in automating integrity_check in Python.

Verification

Prove both branches: a good migration commits and advances the version, and a re-run against an already-migrated snapshot is a safe no-op rather than a double-apply. Idempotency matters because a device that loses power after committing but before reporting success will retry the same migration on reboot:

# 1. A clean migration must return True and leave the copy at the target version.
assert smoke_test("snapshots/gateway-07.db", MIGRATIONS, target=5) is True

# 2. Re-running against a snapshot already at the target must be a no-op, not a failure.
conn = sqlite3.connect("snapshots/gateway-07.db.scratch")
assert conn.execute("PRAGMA user_version;").fetchone()[0] == 5   # advanced exactly once
conn.close()
# 3. Independently confirm the migrated scratch copy is structurally sound from the CLI.
sqlite3 snapshots/gateway-07.db.scratch "PRAGMA integrity_check; PRAGMA foreign_key_check;"
# must print "ok" and then no foreign-key rows

Run the harness across a spread of snapshots — the newest device, the oldest still in the field, and one near its storage limit — not a single fixture. A migration that passes on a fresh copy and fails on a nearly-full eMMC is the exact failure the smoke test exists to catch, and only a representative snapshot exposes it.

Failure Modes & Gotchas

A partial migration on power loss mid-apply. If the device tears power between two DDL statements that were not wrapped in a transaction, it reboots with a half-migrated schema: user_version still reads the old value while the schema is partly new, and the next attempt applies changes on top of a state no migration expects. Wrapping the whole step in BEGIN IMMEDIATE/COMMIT with the version bump inside makes the apply atomic — SQLite’s WAL replay leaves the schema either fully old or fully new. When a device is found in a torn state anyway, recovery follows integrity checking and corruption recovery.

ALTER TABLE limitations force a table rebuild. SQLite’s ALTER TABLE supports only rename, add-column, drop-column, and rename-column; anything else — changing a column type, adding a NOT NULL column without a default over existing rows, altering a constraint — requires the twelve-step rebuild pattern of creating a new table, copying rows, dropping the old, and renaming. On a two-million-row snapshot that copy rewrites the entire table and can momentarily need double the table’s size in free space, so the smoke test must run against a snapshot near real storage pressure to catch a rebuild that would return SQLITE_FULL on the device but succeed in a spacious CI runner.

A migration that locks the database too long on a live device. BEGIN IMMEDIATE takes the write lock for the whole migration, and a full-table rewrite over millions of rows can hold it for many seconds — long enough that the device’s ingestion path backs up and sensor writes start returning SQLITE_BUSY past their busy_timeout. Measure the migration’s wall-clock time against the largest snapshot and, if it exceeds the ingestion path’s tolerance, break the data backfill into bounded batches that commit and yield the lock between chunks rather than holding one transaction across the entire table.