persistence-drift

Persistence — Drift / SQLite

The on-device store is the single source of truth: with no server, everything must rebuild from this DB alone after process death, reboot, or restore. Push invariants into the schema, confine Drift to one layer, make every mutation one durable transaction, and store canonical values only. Applies to any lib/data/ Drift table, DAO, repository, connection setup, or backup.

Read the reference for the task at hand:

• references/schema-and-daos.md — audit-column mixin, STRICT/CHECK/FK invariants, canonical column types, the index + EXPLAIN QUERY PLAN gate, DAO↔repository split, files-on-disk-with-relative-paths for blobs.
• references/backup-and-wal.md — the checkpoint→vacuum→verify-by-reopen primitive, WAL rules, the backup-before-anything lesson, optional SQLCipher (key-first, assert-cipher, header-check).
• references/persistence-without-drift.md — the same discipline for a small app on plain JSON files (injected base dir, debounce + lifecycle flush, atomic writes, lenient decode).

Run scripts/check-drift-confinement.sh and scripts/check-persistence-bans.sh before a PR. Migrations and their tests: see run-migration.

Non-negotiable rules

1. Drift lives only in lib/data/; everything else sees value types. package:drift and package:sqlite3 are imported nowhere else — a banned-import lint/grep enforces it. DAOs map rows to immutable value objects; no Table, Companion, TableInfo, or generated row class crosses the boundary. A feature reaching for a raw query is a layering break that also blocks pure testing of everything above it.
2. Put invariants in the schema, not at call sites. Tables are STRICT (no silent coercion); enumerable columns get CHECK (col IN (...)), ranges get CHECK (qty BETWEEN 0 AND n) / CHECK (amount_minor >= 0), relations are foreign keys with an explicit onDelete, uniqueness is a (partial) UNIQUE INDEX. A corrupt row must be unrepresentable at the storage layer, not merely policed in Dart.
3. foreign_keys and synchronous are set in beforeOpen/setup on EVERY open; journal_mode = WAL is set idempotently there too. foreign_keys and synchronous are per-connection and are not persisted in the file, so they must be re-asserted on every open. journal_mode = WAL, by contrast, is persisted in the database file header and survives across connections — but it is still set idempotently in setup so a freshly created or restored DB adopts it. PRAGMA foreign_keys = ON unconditionally (SQLite defaults it OFF and silently no-ops FK actions when off); journal_mode = WAL for concurrent durable reads; synchronous = FULL on any store holding non-regenerable user data (WAL+NORMAL "might rollback following a power failure"). Seeding, and only seeding, goes inside if (details.wasCreated).
4. One db.transaction per mutation; every query inside awaited; persist before publish. A mutation that writes several rows (parent + dependents, or a debit + a credit) is all-or-nothing. A missing await inside transaction(() async { lets a query run after the transaction closes — Drift calls this data loss; it is a release blocker. The DAO Future resolves only after the durable commit; the committed write then makes the watched .watch() stream re-emit the new state on its own — never an optimistic pre-commit update, never a manual state = … republish, never "save later". (The write→UI-update rule is owned by state-management-riverpod.)
5. Store canonical values only; convert at the presentation edge. Money as integer minor units keyed to the real ISO-4217 exponent (never a REAL/double), other quantities as SI integers, true instants as UTC epoch millis. The local calendar day (anything that drives a day boundary) is a serial-day integer, never a DateTime instant — an instant reintroduces the DST/timezone off-by-one. No display strings, localized numerals, or formatted values in any column; switching locale/unit must leave stored rows byte-identical.
6. Derived state is recomputed on read, never stored as a second authority. Counts, streaks, running totals, histograms are pure folds over their source rows, computed by one watch…/query fold next to the data. A stored copy is a second source of truth that drifts and that any future sync must reconcile twice.
7. The repository is the single write path and the single source of truth. DAOs hold single-table queries; repositories own cross-table transactions and row→value-object mapping, and return a typed Result<T, Failure> for fallible work. Feature code depends on the repository abstraction, never on a DAO or Drift row.
8. Reads are scoped .watch() streams; pagination is keyset, not OFFSET. Never subscribe to an unscoped app-wide stream (it recomputes on every write); scope by owner/entity + time window. History uses WHERE ts < :cursor ORDER BY ts DESC LIMIT n; OFFSET degrades badly on large tables.
9. Blob bytes never live in SQLite. Store files on disk (app-private) with only a metadata row; persist the path relative to a base directory and resolve to absolute at read time — an absolute path dies on iOS reinstall/restore when the container UUID changes, and the row survives while the file renders blank with no error. BLOBs bloat the DB and slow every checkpoint and backup.
10. Back up via wal_checkpoint(TRUNCATE) + VACUUM INTO, then verify by reopen — never File.copy a live WAL DB. A raw copy of a WAL-mode DB captures a torn state across the -wal/-shm sidecars and is corrupt and unrestorable. A backup that was not re-opened and integrity-checked did not succeed. Detail in references/backup-and-wal.md.
11. Schema evolution is forward-only, append-only, and snapshot-guarded — and it is a separate ritual. Bump schemaVersion, add a new stepByStep step (never edit a shipped one), commit the schema snapshot, and ship no migration without a content-level test. That whole workflow lives in run-migration; this skill defines the tables it migrates.