sqlite-startup.md

SQLite availability and verification

Ordinary restarts do not warm the database, run quick_check, run foreign_key_check, or invoke no-op migration handlers. Admission reads the schema, migration history, model table/column names and recovery sidecars. Each SQLite statement has a five-second progress deadline and a two-second busy timeout; the entrypoint bounds the whole command, including imports and I/O, to 30 seconds (then five seconds to kill it). A kernel storage operation in uninterruptible sleep cannot be made healthy by a Python or shell deadline.

An expired verification, absent record, interrupted scan or previous timeout only lets the sliced background verifier continue; a changed image does not. These are not corruption findings. An old record is not proof that the current database is entirely healthy; admission checks only accessibility and compatibility. Latent damage outside the pages read can be found later.

Availability still blocks for:

• unreadable/malformed database or incompatible migration history/schema;
• unresolved, accepted legacy, prepared, corrupt or unreadable recovery reports;
• a previous explicitly failed/blocked startup check;
• a database replacement that would inherit another file's verification;
• pending migrations without an unchanged verified recovery snapshot and matching migration plan/image, or with a missing/changed backup;
• admission exceeding its deadline or storage/lock errors.

The parked container serves a diagnosis/recovery page. Admission never repairs rows. An incident-scoped operator decision dispatches bounded offline recovery before admission, while services remain stopped; stale approvals do not trigger a new scan after the incident closes. PostgreSQL retains its existing migration/startup path and does not run SQLite verification.

Pending migrations: automatic snapshot at startup

When migrations are pending and no valid permit exists, admission exits 11 and the entrypoint (services not yet started) runs sqlite_startup --prepare-migrations --fast: an online-API copy of the database, verified with the page sweep (sqlite_page_sweep.py, sequential) on the copy, then a permit, then re-admission and migrate. PRAGMA quick_check is not run on the copy: it walks b-trees in key order, which took 1.9 h on slow storage. The sweep proves the copy is a readable, structurally sound restore point (header, every b-tree page's layout, child pointers, overflow chains, freelist). If the sweep cannot run (no sqlite_dbpage and a non-empty WAL) the copy falls back to quick_check. The permit records what ran (snapshot_verified: page_sweep or quick_check; quick_check_deferred). The relationship (foreign_key_check) scan is skipped too. Tradeoff: record format and key order of the restore point, and pre-existing foreign-key conflicts, are verified only later, after the migration, by the background verifier. After a sweep-gated copy, preparation asks the verifier (request_deferred_quick_check) to re-run every table's quick_check in its next slices regardless of the 30-day cadence (progress file key deferred_quick_check with pending_tables, until all tables are done). A finding is a corrupt incident, as for any verifier finding. That deep check is limited to 150 s per table per slice (below), so on slow storage a large table may stay in deep_incomplete with the request pending: a coverage limit, not a pass. The copy is still the restore point, but it may hold damage the sweep cannot see. Incidents and previous failed/blocked checks still block. Set FLOPPY_SQLITE_AUTO_SNAPSHOT=false to require the manual step below. Measured here: 18.6s for a 2GB database on local SSD.

Pending migrations: explicit offline preparation

Stop all application containers, workers, scheduled jobs and other database writers. Use the intended new image with the same database, secret and backup mounts, overriding its entrypoint:

docker compose run --rm --no-deps --entrypoint python floppy \
  manage.py sqlite_startup --prepare-migrations
docker compose up -d floppy

For Unraid/Portainer, run the equivalent one-off container using that image and the existing mounts/environment, with python manage.py sqlite_startup --prepare-migrations as its command. Do not start a second normal application container against the database during preparation.

Preparation makes a durable recovery snapshot under BACKUP_DIR/verification, checks its structure and foreign keys, and records a private migration permit beside the live database. Main database, WAL and rollback-journal file identity/size/mtime bracket preparation. Startup rechecks those signatures, the snapshot signature, migration plan and image SHA before permitting DDL. Absent and zero-byte WAL files are equivalent because neither contains frames; nonempty WAL files retain their full fingerprint. SQLite may create an empty WAL while opening a read-only backup. No WAL/SHM files are manually removed. Changed data fingerprints, restored/replaced files, changed backups or changed plans requires preparation again. Online verification cannot issue this permit. Preparation invalidates any previous permit before beginning.

Maintenance has a one-hour process deadline by default. --timeout SECONDS allows an explicitly chosen longer maintenance window. On failure/timeout, inspect the phase/logs and storage; no new migration permit is granted. This workflow can cost a full copy plus two scans and needs space for the snapshot and SQLite temporary files. It deliberately occurs in a scheduled maintenance window, rather than on every restart. Regular quick-check-only backups and old live verification records cannot authorize migrations.

SQLite migrations use the progress watchdog: 180 seconds with neither read nor CPU progress stops the command; an absolute four-hour ceiling remains. They do not retry automatically after failure. The recovery snapshot remains available for inspection/restoration. Migration events.0017 creates its datetime index transactionally, without Django's unrelated global FK exit scan; interrupted index creation and its migration record roll back together. The same skip now applies to every index-only migration: config.sqlite_backend's schema editor omits Django's exit-time foreign_key_check when every statement it ran was CREATE/DROP [UNIQUE] INDEX and SQLite reports no row change (so a RunPython in the same migration still gets the scan). Table rebuilds and data changes keep it. Tradeoff: a foreign-key conflict already present is not re-found by an index migration; the background verifier's per-table foreign_key_check remains the detector. On a 4 GB test database the scan cost about 50 s per migration. No production migration records are faked or deleted.

Verification while serving traffic

Why the old scan was slow. PRAGMA quick_check walks every b-tree in key order. After churn, key order is unrelated to file order, so on spinning or network storage each page is a separate seek: a 5.2 GB database took 1.9 h with about 43 s of CPU (sequential read of the same file ran at 44 MB/s). Giving SQLite more cache does not help when the container's memory is smaller than the database, and no PRAGMA changes the walk order. The fix is to verify in file order.

Supervisor runs sqlite_startup --background (sqlite-verification) 60 seconds after start and then every 10 minutes, niced. It makes no copy and takes no write lock. Nothing blocks startup or serving, and nothing is re-run because the image changed: state is tied to the database file (DB_FILE.verification. progress.json), so work resumes across restarts and upgrades, and a replaced file starts over. Units, oldest first:

Unit	Cadence	What it proves
Page sweep (sqlite_page_sweep.py)	7 days	Every page read once, sequentially, from a WAL-aware snapshot (sqlite_dbpage; without it the raw file only while the WAL is empty). Header and file size; every b-tree page header, cell-pointer array and content bounds; every child pointer in range, unique and of the right family; schema roots; overflow chains (length matches payload, no sharing); freelist chain and count; pages nobody references.
foreign_key_check(table) per table	7 days	Relationship conflicts. Reads the child table and probes parent keys; it needs no extra indexes on the child.
quick_check(table) per table (deep tier)	30 days, best effort	Record format, key ordering. Limited to 150 s per table; a table that does not finish is listed in deep_incomplete and never blocks verified_through.

The sweep does not parse records or compare keys, so those are covered only by the deep tier, and index-versus-table agreement by neither (quick_check never did that; integrity_check does). On damaged copies of a churned 4 KiB-page database the sweep and quick_check flagged the same number of cases (random 8-byte flips 1/60 each, zeroed pages 28/60, clobbered page headers 46/60, truncation 60/60; most flips land in free space or row data neither tool inspects).

Bounds: a slice does at most 300 s of table work, all checking is capped at 3600 s per rolling 24 h, the sweep gets its own 1800 s allowance. Overrides: FLOPPY_SQLITE_VERIFY_{SLICE,DAILY,UNIT,SWEEP}_SECONDS. Read transactions last one slice (sweep: its duration), so WAL checkpoints can stall that long. Findings use the existing reports: damage writes a corrupt incident and conflicts a blocked one, both blocking the next start; live rows are never touched. Busy/I/O/interrupted errors are transient and retried.

--prepare-migrations without --fast (the explicit operator path) keeps the copy, quick_check and foreign_key_check path (maintenance window, needed for the migration permit). The scheduled snapshot task still runs quick_check on its copy; that is not yet sliced or swept.

Observe [sqlite-verification] slice status=... logs and the progress file (verified_through, unverified_units, fk_incomplete, deep_incomplete). Run a slice manually with python manage.py sqlite_startup --background.

A snapshot passing quick_check establishes structural integrity of its copy only. It does not establish relational integrity. Existing scheduled snapshots retain their quick-check-only contract and cannot satisfy the migration gate. The comprehensive verifier separately runs foreign_key_check, publishes relationship findings as a blocking incident, and leaves live rows untouched. Corruption/relationship findings remain on disk, visible in logs and block the next startup. They do not trigger repairs or terminate an already-serving process.

For relationship recovery, stop writers and run python manage.py sqlite_startup --recover; this uses the existing incident-scoped, backup-protected recovery policy and operator decisions. Then prepare pending migrations again. For physical corruption or unreadable reports, preserve the database and sidecars, inspect/restore a known-good backup offline and explicitly archive the old incident only after confirming the restored database is healthy. Never delete WAL/SHM to force a start.