persistence-and-migrations

Persistence and migrations

App data lives in one SQLite file opened synchronously through a tiny SqlDriver port, reached only through validated repositories that return typed Results, upgraded only by frozen forward-only steps that are replayed against frozen fixture databases on Node, and never lost or crash-looped on; scripts prove the code, the schema history and the file on a named iOS simulator and Android emulator.

Rules that must hold

One data layer. SQLite and the SqlDriver port are imported only in src/data/ (the drivers) and SQL, in any case, lives only there; statements run only in sqlite-<area>-repository.ts files and the open path, migrations, peeks and resets. Exceptions: the SQLite error log adapter in src/services/error-log/ and the files the contract's sqlAllowed lists. Features and stores call a repository (useAppData() carries the database's status, never the driver). No second engine for app data (AsyncStorage, zustand persist, another SQLite binding); small preferences go to the one key-value store the contract names (expo-sqlite/kv-store, or the global localStorage its expo-sqlite/localStorage/install puts over the same store, installed by the first import of the local-storage-<concern>-adapter.ts that reads it), behind a port; secrets to expo-secure-store. Why: one file, one version number, one backup story.
Open synchronously, version first, then journal_mode = WAL, synchronous = FULL and foreign_keys = ON on every open; every table is STRICT. Why: a committed transaction survives a kill or power cut, SQLite ships with foreign keys off, and the journal-mode pragma writes the file.
Never crash-loop and never touch a file the build cannot use. A newer, damaged or unmigratable file, or one at user_version 0 whose schema is neither the baseline nor a declared variant, is left as it is; the session runs in memory with an outcome the first screen explains (a full disk is storage-full: "free up space"), and opening never throws. Why: a throw at startup repeats on every launch, and an existing database opened "fresh" hides every row the user had.
Forward-only, frozen steps. One vNNNN-<name>.ts per version with literal SQL, registered in order, each step and its user_version stamp in one transaction; no down steps, no stamping or BEGIN in a step, rebuildsTables: true on every step that drops or renames a table. Never edit a shipped step. Why: installed apps never rerun a step, so an edit splits fresh and upgraded installs forever; and with foreign keys on, dropping a parent table deletes its cascading children.
Snapshot before migrating a file that holds tables: VACUUM INTO '<file>.v<from>.bak', reused while a step keeps failing, pruned to the newest two after a success; no snapshot, no migration. Why: the only way back from a step that committed but was wrong, without a new full copy per launch.
Every shipped version has a frozen fixture database with a recorded checksum, and a fresh install and every upgraded fixture end with the same schema and every row the fixture held (replayed on node:sqlite with foreign keys on, by the tests and by the checker; an adopted app also replays one db-v0-<variant> per legacy schema). Never edit a fixture or checksum. Why: old phones hold these files.
Validate and convert at the boundary. Repository methods return Result<T, F> with a kind union and never throw for a data reason: input is parsed with valibot first (invalid input is a typed failure, nothing written), values are bound parameters (never built into the SQL text), SQLite errors are converted once (the unexpected ones recorded raw), every row read goes through its row schema and an unreadable row is skipped and logged alone; statements stay in methods or the same-file helpers they call (run per statement, never exec). Why: a thrown expected failure crashes a release-build handler; error-handling-typed-results owns Results.
A write of two or more statements, or one per item of a loop, is one transaction; transactions never nest (both drivers throw on a nested one). Why: a kill must not leave half a change, and the two runtimes used to fail a nested BEGIN differently.
Resets list each statement literally and never touch protected tables (entitlements: a purchase ends only with a dated revocation; error_log: what support needs), in one transaction.
Write nothing before startup decisions that can reload the runtime: open on the root layout's first render, never at import time; a peek is read-only. Why: module code runs again after a reload, so an early write happens twice.
Checkpoint the WAL (wal_checkpoint(TRUNCATE)) when the app goes to the background. Why: a device backup (iCloud, Android Auto Backup) then copies one self-contained file.
Test the real SQL on node:sqlite through SqlDriver, with foreign keys off until the app's pragma (as on the device), and finish write-path or startup changes with the kill test on iOS and Android (or say in the report that it is owed). Why: Jest proves the SQL; only a kill proves the app.
Appendix: a save document only for small app state ("mode": "document" or "both"): strict schema validated on every load and before every write, current/backup slots written in one transaction, a refused write logged and never thrown (the backup stays due), damaged rows quarantined never deleted, protected sections guarded for every writer, pure frozen document steps. Why: state that is always read and written whole fits one validated value, and two slots keep a good copy when one write is damaged; anything that grows belongs in tables.

Workflow

Read references/tables-and-repositories.md (driver, open path, outcomes, snapshots, repositories and Results, validation, resets, key-value boundary, error log, file locations and backups, adopting an existing database).
New data layer: npx expo install expo-sqlite expo-file-system, npm install -E valibot@1.5.0 and, if the app lacks it, npm install -D -E fast-check@4.10.2 (stack table, verified 2026-10-03); copy templates/src/data/ (without document/ unless you chose the appendix, and without peek-database.ts until a reload decision calls it: copy it with that decision), templates/test/sqlite/ (without sqlite-document-store.test.ts), templates/src/services/, templates/src/core/ and templates/contracts/ (skip a synced file the app already has from its owner skill). Replace the Tasks tables, schemas, failures and repository with the app's own (and their lines in AppData and openDeviceData); keep the drivers, runner, open path and snapshots. Fill contracts/persistence.json (mode, protected tables, reset modules). In the root layout: useState(openOnce) calling openDeviceData, useCheckpointOnBackground(data.database) and <AppDataProvider>, exactly as examples/root-layout.tsx; app-startup-and-bootstrap owns the full startup order.
The app already ships a database (unversioned tables, its own user_version numbering, Drizzle, SQLiteProvider, or AsyncStorage data): follow "Adopting in an existing app" in the same reference before the first release with this layer (BASELINE in migrations.ts, one frozen db-v0-<variant>.sql per legacy schema). Never let it open as fresh.
A feature needs to save something: add a repository method (v.safeParse input, a typed failure, attempt around the driver call, row schema on read, one transaction for several statements) and call it from the model hook or store (useAppData().tasks; useAppData().database is only the status: outcome, isPersistent, checkpoint); never SQL or the driver in a feature, and never a statement in another data file (a sibling task-rows.ts escapes every repository rule). A new column or table is a new version.
The schema changes: read references/migrations-and-fixtures.md and follow "Adding version N+1" in order, imitating examples/migration-v2/ (step, test on the old fixture, new frozen fixture made with inspect-database.mjs --dump, checksum, FIXTURES entry). For a release, also run the run-migration ritual. A new version is permanent once shipped: name it in the report.
Small app state as one document: read references/save-document.md, set "mode": "both" (or "document"), copy templates/src/data/document/ and its SQL test, paste DOCUMENT_SLOTS_DDL into a new table step, and imitate examples/document-v2/ for document versions.
Startup, checkpoint or kill-test work: read references/startup-and-kill-test.md.
Run the tests: npx jest src/data src/core src/services test/sqlite --ci and npm run typecheck.
After a change to the open path, a migration, a repository's writes or the startup, with a Release build on a dedicated device: node ${CLAUDE_SKILL_DIR}/scripts/kill-test.mjs --udid <udid> --bundle-id <id> --expect-version <N> (iOS) and node ${CLAUDE_SKILL_DIR}/scripts/kill-test.mjs --serial <serial> --package <id> --expect-version <N> (Android) must print RESULT: PASS. Without builds, say in the report that the kill test is owed.
Run node ${CLAUDE_SKILL_DIR}/scripts/check-persistence.mjs . from the repo root; fix every FAIL line and rerun until it prints RESULT: PASS.

Definition of done

SQLite and SqlDriver imports and SQL text appear only in src/data/, test/, scripts/, the SQLite error log adapter and sqlAllowed; statements run only in repository files and the open path's own files; no feature reaches .driver; no AsyncStorage (outside a listed legacyImports importer), zustand persist or second engine; kv-store, the localStorage install and localStorage reads only in a src/services/<concern>/*-adapter.ts.
app-database.ts reads user_version and the file's tables before APP_DB_PRAGMAS, never opens a version-0 file with tables as fresh (adopting only the BASELINE schema or a declared variant), snapshots to <file>.v<from>.bak before migrating, prunes old snapshots, and never throws; every table is STRICT.
Every version 1..N has its vNNNN step registered in order, its db-vN.sql fixture, its checksum and its FIXTURES entry (adopting: each legacy schema its db-v0-<variant>.sql in LEGACY_FIXTURES); a step that drops or renames a table says rebuildsTables: true; no shipped step, fixture or checksum was edited.
Every repository method returns a Result, v.safeParses its input before writing (itself or through a helper), binds every value as a parameter, converts driver errors once, wraps several statements (or a loop, its helpers included) in one transaction, and passes what every read returns (not only its input) through safeParse; each SQLite repository has a node:sqlite test.
Resets list their statements literally and keep the contract's protected tables; the root layout opens the data layer on its first render (useState(openOnce), never at import time or as a call in the render body) and calls useCheckpointOnBackground once.
npx jest src/data src/core src/services test/sqlite --ci passes and npm run typecheck is clean; device-only files carry their // device-only: covered by ... line.
Appendix only: document schemas strict, LATEST_* names the highest version, every version has its step, test and both frozen JSON fixtures, and update() validates, guards Premium, respects isReadOnly and never throws for a refused write.
After a write-path, migration or startup change: the kill test printed RESULT: PASS on iOS and on Android, or the report says it is owed.
node ${CLAUDE_SKILL_DIR}/scripts/check-persistence.mjs . prints RESULT: PASS. Expected only before the root layout exists: SKIP src/app/_layout.tsx [no-background-checkpoint] not yet created (app-startup-and-bootstrap creates it). An app that keeps only preferences (no contract, no src/data/) prints NOT APPLICABLE: no app database: no contracts/persistence.json, no src/data/, and expo-sqlite is not imported, or only as expo-sqlite/kv-store or expo-sqlite/localStorage/install (preferences); the repo-wide rules passed.

Anti-patterns

"Just add a column" to v0001-... after it shipped. Fresh installs get it, upgraded ones never do; the replay fails. Add v0002 instead.
Editing a fixture or checksum to make a test pass. The fixture is what old phones hold; fix the step, or add a new version.
A step that imports a table-name constant, a schema or today's defaults, or reads the clock. Tomorrow's change would rewrite yesterday's upgrade; write literal SQL.
PRAGMA foreign_keys = OFF inside a step, or a rebuild without rebuildsTables: true. The pragma is a no-op inside a transaction, and a parent rebuild with foreign keys on deletes every cascading child while the schema still matches; mark the step rebuildsTables: true.
throw when the file is newer, or deleting a file that does not open. Every launch then crashes, or the user's data is gone; run in memory, tell the user, keep the file.
Shipping this layer over an existing database without a baseline, or with one baseline when old builds left several schemas. Migration 1 collides, the app opens "fresh", or one population opens in memory forever; adopt every variant (BASELINE.variants, a rebuild after it) or backfill steps.
Pragmas before the version check. Setting WAL writes a newer build's file before it is recognised.
A snapshot named by the time, never pruned. Every failing launch adds a full copy, and the copies fill device backups (Android's limit is 25 MB per app); name it per version, reuse it, prune it.
A repository that throws on invalid input or lets an SQLite error escape. A release-build handler crashes; return the typed failure, record the unexpected one with the raw error.
A transaction inside a transaction, or a bulk method of autocommits. The inner BEGIN fails (on a device it used to roll back the outer change); one outer transaction per change.
Opening the database at import time, or awaiting an async open before the first render. Open once, synchronously, in the root layout after the reload decisions.
AsyncStorage or zustand persist "for one small value". No transaction, no version, no backup; add a column, a repository method, or a kv-store preference behind a port.
Handing the driver to features (AppData.database.driver, a "maintenance" hook that runs one DELETE). Every repository rule is skipped; add a repository method instead.
Trusting rows from SQL. A row from an older build or a bypassed constraint crashes a screen; parse it, skip and log what does not parse.
A value built into SQL (`... WHERE id = ${id}`). A quote breaks it and input can run its own SQL; bind it (?).
Tests on a mock driver only, or a test driver with foreign keys on. The device enforces nothing until the app's pragma; test the real SQL on node:sqlite with the device's defaults.
Opening the app's live app.db in place from the computer. It can checkpoint or lock the file; inspect-database copies it first.

Files in this skill

File	What it is	Read/run when
references/tables-and-repositories.md	Driver port and its contract, the open path and outcomes, snapshots and backups, repositories and Results, validation, transactions, resets, key-value and secrets, error log, file locations, adopting an existing database, multi-package, alternatives, rules	Workflow steps 1-4
references/migrations-and-fixtures.md	Forward-only rules, the runner, step files, adding version N+1, table rebuilds, frozen fixtures, dumps, the replay, failures	Workflow step 5
references/startup-and-kill-test.md	Startup order, the peek, opening in the root layout, the checkpoint, node:sqlite tests, the kill test on iOS and Android, inspect-database, what is verified	Workflow steps 7 and 9
references/save-document.md	Appendix: the save document, slots, codec, load plan, the single writer and its write results, protected sections, document versions and fixtures, its tests	Workflow step 6
`templates/src/data/sql-driver.ts`, `expo-sqlite-sql-driver.ts`, `migrations/migration-runner.ts`	The port, the device driver and the runner, synced from the library (do not edit here)	Workflow step 2
`templates/src/data/` (other files)	Open path (with adoption), schema reader, snapshots and the `DatabaseFiles` port with its device adapter (`expo-file-system-database-files-adapter.ts`), SQLite error mapper, migration list (with `BASELINE`) and v1 step, frozen `db-v1.sql` and checksums, checksum, Tasks schemas, failures and repository (SQLite and fake), reset, peek, checkpoint hook, the `AppData` context, the device composition (`openDeviceDatabase`, `openDeviceData`), and tests	Workflow steps 2-5
`templates/src/data/document/`	Appendix: schemas, slot store (SQLite and fake), tables SQL, codec, load plan, service, resets, migrations, peek, hydrate, frozen JSON fixtures, and tests	Workflow step 6
`templates/src/core/`	`result.ts` and its test (error-handling-typed-results' Result module) and `dates/date-key.ts` with its test (the clock port's date type), synced (do not edit here)	Workflow step 2
`templates/src/services/`	The clock port and system clock adapter, the error log port, memory adapter and fake, with tests (architecture-and-boundaries' files, synced; do not edit here), and this skill's SQLite error log adapter	Workflow step 2
`templates/test/sqlite/`	node:sqlite driver, temp folder, runner test and driver contract test (synced), file listing, schema helper, and the SQL tests (the fixtures test replays every fixture with foreign keys on and counts every table's rows)	Workflow steps 2 and 8
`templates/contracts/`	`persistence.json` (the app's facts) and its JSON schema	Workflow step 2
`examples/migration-v2/`	A complete v1 to v2 table change: step, list, frozen `db-v2.sql`, checksums, fixtures test, the step's test	Workflow step 5
`examples/document-v2/`	A complete document v1 to v2 change: sections, schema, defaults and their pinned test, step and its fast-check test, list, fixtures, checksums	Workflow step 6
`examples/root-layout.tsx`	The startup wiring in an Expo Router root layout, using only this skill's templates (the self-test runs the checker on it)	Workflow step 2
`scripts/check-persistence.mjs`	Static checker plus the migration replay on node:sqlite; reads `contracts/persistence.json`; options `--data-dir`, `--contract`, `--no-replay`	Workflow step 10, and at the end
`scripts/inspect-database.mjs`	Health check of a database, a .sql dump, or the app's file on a named simulator or emulator (physical damage reported as `integrity`); `--dump` freezes a fixture	Workflow steps 3, 5 and 9, and when data looks wrong
`scripts/kill-test.mjs`	Kill test on a named iOS simulator (`--udid`, `--create`) or Android emulator (`--serial`; launches with `am start -W`, `--activity`, `--step-ms` per platform), then one clean launch left running `--settle-ms` before the inspection	Workflow step 9
`scripts/lib/`	The checkers' helpers (database health and dumps, device copies, the migration replay, the source model, the rule sets) and the synced `app-modules.mjs` and `source-scan.mjs` (do not edit those two here)	Never by hand
`scripts/check-lib.mjs`	Shared script helper, synced from the library (do not edit here)	Never by hand
`scripts/selftest.mjs`	Pins the checksum vectors and proves both checkers pass good input and catch every planted bug	After changing a checker or a template
`assets/shared.json`	Declares the shared files this skill copies in	When adding a shared file
`tests/`	Overlay fixtures (planted bugs) for check-persistence in tables and both modes (rebuilds, helpers, adoption variants included) and for inspect-database, with their bases	When adding a checker rule

Related skills

error-handling-typed-results - the Result type, failure design, catches and the error net.
state-management-zustand - stores that call repositories and publish after the commit.
run-migration - the release ritual for a schema migration (snapshot, every path, restore drill).
data-export-and-restore - portable backups and imports of the same data.
app-startup-and-bootstrap - the full startup order around opening the database.
architecture-and-boundaries - ports, the composition root, the clock and error log services.
networking-and-server-state - server state, the persisted query cache and the offline outbox's sync.
auth-and-secure-storage - tokens and secrets in expo-secure-store.
settings-and-preferences - what is a preference and where it is saved.
in-app-purchases - the entitlement and its dated revocation.
rtl-and-direction - the direction decision that runs before the database opens.
ios-simulator-build, android-emulator-build - the Release builds the kill test installs.
e2e-maestro - mid-session kill flows on a named device.
golden-and-snapshot-tests - the review policy for frozen files.