Skip to content

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.sh downloads sqlite3mc-2.5.1-sqlite-3.53.4-wasm.zip from the GitHub release, verifies the zip against a SHA-256 that was compared with the release's own sqlite3mc-2.5.1-SHA256SUMS, extracts exactly sqlite3.mjs and sqlite3.wasm, verifies both again, and writes them to apps/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 directory immutable; re-pinning is a deliberate edit of the script (and SQLITE3MC_VERSION in apps/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, test and e2e run the script first, so pnpm verify and 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 with PRAGMA 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 hexkey fails with "Encryption is not supported by the VFS". The worker therefore registers opfs-sahpool, calls capi.sqlite3mc_vfs_create(base, 0) and opens the database with vfs: '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 with hexkey and 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, because opfs-sahpool takes 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 plate logged_at_utc, entry plate_log_id, note plate_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 the EncryptedStore, CoachNoteStore and LocalDataEraser ports 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 header anc.pwa.store.meta, idle minutes anc.pwa.lock.idleMinutes), IndexedDB anc_pwa_header_v1, Web Lock anc-pwa-store, OPFS pool directory .anc_pwa_store (VFS anc-pwa-sahpool, file /anc_pwa.db). The old build used ai_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. unavailable means 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() runs probeStorageAvailability() 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). needsReset is 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. Only needsReset offers the explicit erase. A read failure is never routed to needsReset and 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: scopeSyncQueueToSession clears 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=1 with a visible "DEV, not encrypted" banner. src/storage/build-guard.test.ts bundles createAppStore() 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 (in pnpm verify) scans dist/ 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 globPatterns now include mjs and wasm), 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 reports insecureContext, with an HTTPS explanation to be written in the phase 7 copy.
  • opfs-sahpool stores 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.