ADR 0005: Encrypted web store on SQLite3 Multiple Ciphers, in a worker, on OPFS¶
Status: accepted for Phase 4; the real-device check on iOS Safari and Android Chrome is still pending (see "Open item") ยท 2026-10-14
Supersedes (in part): _bmad-output/planning-artifacts/architecture/architecture-openplate-2026-08-13/ADR-0002-web-encrypted-store.md (the IndexedDB file system on the main thread; OPFS and a worker were rejected there). The rest of ADR-0002 (cipher, KDF, header, single tab, lock lifecycle, error taxonomy) is kept and ported.
Follows: ADR 0004 (plan phase 0 asked for this spike, phase 4 for the implementation).
Context¶
ADR-0002 shipped an encrypted store in the Flutter web build: sqlite3mc compiled to WebAssembly (ChaCha20-Poly1305), kept in IndexedDB, on the main thread. That wasm is built for the Dart sqlite3 bindings and cannot be used from JavaScript. ADR 0004 asked phase 0 to confirm a JS route: a sqlite3mc JS/WASM build in a dedicated Web Worker on the OPFS opfs-sahpool VFS (no COOP/COEP needed, its exclusive file access matches the one-tab Web Locks rule), with plan B being the official @sqlite.org/sqlite-wasm and an application-level AES-GCM layer per row.
Decision¶
Plan A. The engine is the official SQLite3 Multiple Ciphers WebAssembly build, release v2.5.1 (SQLite 3.53.4), in a dedicated module Web Worker on the opfs-sahpool VFS. Plan B was not needed and is not in the code.
- Pinned artifact.
scripts/fetch_web_sqlite3mc.shdownloadssqlite3mc-2.5.1-sqlite-3.53.4-wasm.zipfrom the GitHub release, verifies the zip against a SHA-256 that was compared with the release's ownsqlite3mc-2.5.1-SHA256SUMS, extracts exactlysqlite3.mjsandsqlite3.wasm, verifies both again, and writes them toapps/web/public/sqlite3mc/2.5.1/(git-ignored, never committed). It is idempotent and fails closed. The version is in the served URL, so nginx can cache the directoryimmutable; re-pinning is a deliberate edit of the script (andSQLITE3MC_VERSIONinapps/web/src/storage/engine-pin.ts, which a unit test keeps in step). The newer 2.6.x line was deliberately not taken (too fresh to trust).pnpm dev,build,testande2erun the script first, sopnpm verifyand CI always have it. - Cipher. ChaCha20-Poly1305 (an AEAD, so a flipped or swapped page is detected), pinned with
PRAGMA cipher = 'chacha20'before keying, key supplied withPRAGMA hexkey, and a forced page decrypt (SELECT count(*) FROM sqlite_master) before the database is used. A wrong key fails there (SQLITE_NOTADB, 26), so a wrong passphrase can never open the store and nothing is written. - A finding that is not in the sqlite3mc docs. On a persistent VFS the cipher pragmas only work if the VFS is wrapped by sqlite3mc: without it
PRAGMA hexkeyfails with "Encryption is not supported by the VFS". The worker therefore registersopfs-sahpool, callscapi.sqlite3mc_vfs_create(base, 0)and opens the database withvfs: 'multipleciphers-<base>'. The key attach maps that error to "cipher missing" (a build problem), never to a wrong passphrase. - Key. PBKDF2-HMAC-SHA256, 600,000 iterations, 32-byte output, random 16-byte per-store salt, from the passphrase NFC-normalised then UTF-8, through
crypto.subtle.deriveBits(native, off the JS loop). It runs inside the worker, so the derived key never exists on the main thread; it is zeroed as soon as the keying statement has run. As in the Flutter build, the 32 derived bytes are handed to sqlite3mc withhexkeyand sqlite3mc applies its own cipher key setup on top; the 600k-iteration PBKDF2 is the deliberate cost. The header ({"v":1,"kdf":"pbkdf2-hmac-sha256","iter":600000,"pw":"nfc-utf8","salt":"<base64url>"}) is in localStorage (anc.pwa.store.meta) with a copy in IndexedDB (anc_pwa_header_v1); malformed, unknown-version, short-salt, downgraded (below 600k) and absurdly expensive (above 10,000,000 iterations, a denial of service on unlock) headers are refused, both when parsed and again inside the engine. Locking closes the database and terminates the worker, which releases the OPFS handles and frees the wasm heap the key lived in. The documentation must not claim forensic erasure from RAM. - Single tab. Web Locks (
anc-pwa-store,ifAvailable) are taken before the worker is created, becauseopfs-sahpooltakes exclusive handles on its files and, if its install fails, removes its own directory. Without the Web Locks API the guard degrades to "not enforced" (as ADR-0002 allowed); the pool's exclusivity still stops a second tab from opening the same files, but the install-failure cleanup then runs, so a browser without Web Locks is the one place the pool's own cleanup could touch another tab's data. Every browser that has OPFS synchronous handles also has Web Locks, so this is a theoretical corner. - Schema. Version 1 in
PRAGMA user_version:plate_log,food_entry,coach_note,coach_interaction,coach_dismissal,kv; no database foreign keys, no sync table; indexes on platelogged_at_utc, entryplate_log_id, noteplate_log_id; UTC ISO-8601 millisecond text timestamps; ranged queries filtered in SQL; the delete cascade is one transaction; a newer schema is refused; corrupt rows are skipped and counted with a content-free log. - Where the code is.
apps/web/src/storage/implements theEncryptedStore,CoachNoteStoreandLocalDataEraserports of@anc/core(the core was not changed) plus a small key/value store. Pure parts (header, KDF, passphrase rules, controller state machine, locks, mapping) have no browser dependency and are unit-tested; the SQL, key attach and schema run against the real wasm engine in Node. - Browser storage names. Everything the new app persists in the browser is under its own names, so it can never collide with the old Flutter web build when both are served from one origin (a self-hoster moving from one to the other): localStorage keys
anc.pwa.*(store headeranc.pwa.store.meta, idle minutesanc.pwa.lock.idleMinutes), IndexedDBanc_pwa_header_v1, Web Lockanc-pwa-store, OPFS pool directory.anc_pwa_store(VFSanc-pwa-sahpool, file/anc_pwa.db). The old build usedai_nutrition_coach*/ai-nutrition-coach-store; the eraser sweeps only the new prefix and never touches those. There is no migration from the Flutter store (a new schema, new keys). - Unavailable is not lost. The controller has two different dead ends.
unavailablemeans the browser cannot use or cannot right now READ its storage; it says nothing about the data and offers retry only (controller.retry(), which looks again).initialize()runsprobeStorageAvailability()itself first, then reads: a localStorage read that throws, an unreadable IndexedDB header copy, an OPFS inspection that fails or finds the files held by another tab all end here, with a typed reason in the snapshot (insecureContext,noOpfs,storageRefused,transient,inUse).needsResetis reserved for content that is positively bad: a corrupt or downgraded header, database bytes with no header (orphaned), or a header whose data is gone. OnlyneedsResetoffers the explicit erase. A read failure is never routed toneedsResetand never treated as a first run. - Worker death. If the worker dies on its own (load failure, crash) the session is marked dead, the controller locks (status
locked, handle dropped, Web Lock released) and every later operation on that session answers the typed locked failure (webSessionKey), as it does after a normal lock.dispose()closes the worker first and releases the Web Lock only after the close settled. - Outbox. The v1 sync outbox is in memory and scoped to the unlocked session:
scopeSyncQueueToSessionclears it on every unlocked-to-locked transition and when an erase begins. - Dev-only store. An unencrypted in-memory store exists behind
import.meta.env.DEV && VITE_DEV_MEMORY_STORE=1with a visible "DEV, not encrypted" banner.src/storage/build-guard.test.tsbundlescreateAppStore()for production, with the flag set, and proves the branch, its chunk and the banner are gone;tools/check_no_dev_store_in_build.sh(inpnpm verify) scansdist/for them and for the harness page.
What was verified, in which browser¶
| Check | Chromium (Google Chrome, PW_CHANNEL=chrome) |
WebKit (Playwright 1.63 build, macOS) |
|---|---|---|
sqlite3mc 2.5.1 loads in a module worker under the production CSP, opfs-sahpool installs, multipleciphers-opfs-sahpool VFS works |
pass | pass |
| create, write, lock; wrong passphrase refused with every OPFS byte unchanged (SHA-256 of each file); right passphrase reads | pass | pass |
raw OPFS bytes hold no plaintext (food name, passphrase, plate_log, CREATE TABLE) and no SQLite format 3 header |
pass | pass |
| reload persistence; lost localStorage header recovered from the IndexedDB copy | pass | pass |
erase removes every byte (OPFS root empty, header database gone, no anc.pwa.* localStorage key); works while locked |
pass | pass |
| second tab gets "in use", cannot erase, and the first tab is unharmed | pass | pass |
| shared store contract suite (29 cases) against the real OPFS store at the production 600k cost | pass | pass |
| no console errors, no CSP violation, no request to any other host | pass | pass |
| offline reload: engine and worker served from the service-worker precache | pass | not run (the offline assertion is Chromium only) |
The same contract suite also runs against the in-memory reference and against the real engine on its in-memory file system in Vitest, plus the ports of the Dart controller, key attach, header, KDF, key/value, corrupt-row, error-mapping, eraser and cascade tests.
WebKit finding. In a Playwright ephemeral WebKit context (what Safari private browsing looks like) navigator.storage.getDirectory().getFileHandle(..., {create: true}) fails with "The operation failed for an unknown transient reason" and the store cannot run; a persistent context works. probeStorageAvailability() reports this as noOpfs; creating a store there is storeUnavailable, never a wrong passphrase. initialize() runs the probe itself, so such a browser lands on unavailable (reason noOpfs, offering retry) instead of the needsReset ("store lost") screen that the header-less OPFS inspection used to produce; the UI shows the calm "this browser cannot keep private data here" line for it (phase 7). Transient OPFS failures and files held by another tab's worker are unavailable too (transient, inUse). Also, WebKit under Playwright keeps OPFS outside the throwaway profile directory, so the e2e harness wipes OPFS, IndexedDB and localStorage at the start of each test.
Content-Security-Policy¶
No change is needed. The policy in the parity checklist (Appendix A) already has script-src 'self' 'wasm-unsafe-eval', worker-src 'self' blob: and connect-src 'self', which cover the module worker, WebAssembly.instantiate and the same-origin fetch of the wasm. The e2e suite runs the real store under the production CSP meta tag and asserts no violation. Phase 8 must make sure nginx (a) sends the same CSP header on the worker script and the .wasm/.mjs responses (it applies to the worker's own context), (b) serves .wasm as application/wasm (streaming compile needs it) and .mjs as text/javascript, and (c) caches /sqlite3mc/<version>/ and the hashed assets immutable. COOP/COEP are not needed (opfs-sahpool does not use SharedArrayBuffer).
Consequences and risks¶
- The wasm and its glue are ~1.5 MB and are in the service-worker precache (workbox
globPatternsnow includemjsandwasm), so the first install is heavier but the store works offline. The worker chunk itself is emitted once the app imports the store (phase 7); until then the precache holds the engine only. - The store needs a secure context (
crypto.subtle): plain HTTP reportsinsecureContext, with an HTTPS explanation to be written in the phase 7 copy. opfs-sahpoolstores the database in opaque, fixed-size slot files inside.anc_pwa_store/. "Database bytes exist" is therefore decided as "a file in that directory larger than its 4 KiB slot header": an initialised but empty pool (a tab that died mid-create) is a first run, not an orphan. That leans on the VFS's on-disk layout; a different sahpool version needs this re-checked on re-pin.- Removing the pool directory right after the worker is terminated can fail for a moment (the handles are released asynchronously); the eraser retries for about three seconds and fails closed, naming
databaseFiles, if another tab keeps them. - Safari evicts script-writable storage after seven days without interaction unless the site is installed; the store asks for durable storage once after create/unlock (
navigator.storage.persist()) and exposes the answer in the controller snapshot. Nothing is ever shown more than once. - Not ported: the Flutter journal-leak test (a held write transaction leaves no plaintext in
-journal); in OPFS the journal is another slot file inside the pool, and the e2e at-rest check reads every file of the pool after a write. A long-held-transaction variant has not been written.
Open item¶
A real-device check on iOS Safari (as an installed PWA and in a tab) and Android Chrome over HTTPS is still pending (the person owning the project will do it). What it must confirm: the worker starts and opfs-sahpool installs, create/lock/unlock/erase work, storage survives closing the app, durable storage is granted for the installed PWA, and a second tab/window reports "in use". The Playwright WebKit build is not iOS Safari, and Chrome on Android was not exercised at all. Until then this ADR stays "accepted, pending the device check", like ADR 0004.