ADR-0030: Revert Local Storage from SQLite WASM to Dexie/IndexedDB

Status: accepted
Date: 2026-07-28
Supersedes: ADR-0017 (Dexie→SQLite WASM decision, partially)
Deciders: @barateza
Issue: #510

Context

ADR-0017 (2026-06-27) migrated the frontend storage from Dexie/IndexedDB to @sqlite.org/sqlite-wasm with OPFS persistence, motivated by schema alignment with D1 (shared SQL DDL from Zod schemas). Six months of production use revealed that the SQLite WASM layer added disproportionate complexity for the scale of data being stored locally:

Problem

• Bundle size: WASM binary + JS glue adds ~1.2MB to the bundle — significant for a PWA targeting mobile browsers with 3–5 concurrent users and ~50 students.
• Complex initialization: The SQLiteStore class requires async WASM loading, OPFS capability detection, SahPool VFS setup, and BroadcastChannel multi-tab coordination (~250 lines of init code).
• Migration duplication: opfsMigrations.ts mirrors D1 migrations from /migrations/ — every D1 column addition requires an OPFS equivalent.
• Unnecessary SQL parity: The frontend never runs the same queries as the backend. SQL dialect parity between D1 and local SQLite WASM was an aesthetic benefit, not a practical one.
• CSP relaxation: 'unsafe-eval' and 'wasm-unsafe-eval' are required in Content-Security-Policy for sqlite-wasm, relaxing script-src for the entire SPA.
• Maintenance tax: generateSchemaSQL.ts, opfsMigrations.ts, and parseTablesFromSQL.ts are intermediate layers that exist solely to bridge Zod schemas to SQLite DDL.

Evidence from literature

Research conducted during this decision (Jul 2026) confirmed:

Source	Finding
RxDB benchmarks (2025)	For datasets under 10K records, IndexedDB matches or outperforms WASM SQLite in read/write latency.
Smashing Magazine (May 2026)	"SQLite via WASM is overkill for 99% of use-cases."
LogRocket (2025)	"For anything beyond simple key-value configuration, IndexedDB is the right tool."
Notion blog (2024)	Notion uses SQLite WASM only as a cache layer for million-record workspaces. Their IndexedDB implementation "would perform great for smaller workspace sizes."
Decision flow (RxDB, 2025)	IndexedDB → complex structured data. SQLite WASM → complex JOINs, FTS, million-row datasets.

Scale of Neemias data

• ~50 students, ~30 attendance events, 4 users, ~15 classes/class-slots
• All data fits comfortably in IndexedDB with no performance degradation
• Zero JOIN queries in the frontend — all store.query() calls follow simple patterns: SELECT *, WHERE col = ?, ORDER BY, COUNT(*)

Decision

Replace SQLiteStore with a DexieAdapter that implements the existing StorageBackend interface (7 methods), swap the factory in createStore() to use it, and remove all SQLite WASM infrastructure.

What changes

Component	From	To
Local storage engine	SQLiteStore (SQLite WASM + OPFS)	DexieAdapter (Dexie + IndexedDB)
Schema definition	DDL generated from Zod via generateSchemaSQL.ts	Manual Dexie db.version(N).stores({...}) declarations
Migrations	opfsMigrations.ts with versioned ALTER TABLE	No-op (applyMigrations exists for interface compatibility)
Query mapping	Raw SQL strings → SQLite WASM	Regex-parsed SQL patterns → Dexie chainable queries
Multi-tab sync	BroadcastChannel (preserved)	Same mechanism
Test backend	createTestStore() → SQLiteStore({ vfs: "memory" })	createTestStore() → DexieAdapter

What stays

• StorageBackend interface (unchanged — contract for 27+ callers)
• StorageProvider, useStore(), useSqlQuery, useSqlMutation (unchanged — generic over StorageBackend)
• OnlineProxy (refactored to receive StorageBackend by injection)
• parseTablesFromSQL (still used by useSqlQuery for change notification)
• All entity types from packages/schemas/ (Zod remains the source of truth for types)
• Event writer, repositories, sync queue — all operate through StorageBackend

Files to remove

File	Reason
app/src/storage/sqliteStore.ts	Replaced by DexieAdapter
app/src/storage/opfsMigrations.ts	No longer needed
app/src/storage/generateSchemaSQL.ts	No longer needed
app/src/storage/parseTablesFromSQL.ts	Kept — still used by useSqlQuery

Files to modify

File	Change
app/src/storage/index.ts	Factory returns DexieAdapter instead of SQLiteStore
app/src/storage/onlineProxy.ts	Receives StorageBackend by injection instead of creating SQLiteStore directly
app/vite.config.ts	Remove optimizeDeps.exclude and test.server.deps.inline for sqlite-wasm
app/CONTEXT.md	Update architecture description

Files to create

File	Purpose
app/src/storage/dexieAdapter.ts	DexieAdapter class implementing StorageBackend
app/src/storage/dexieSchema.ts	Dexie db.version(N).stores({...}) declarations

Test changes

File	Action
sqliteStore.test.ts	Replace with dexieAdapter.test.ts testing the same contract
generate-schema-sql.test.ts	Remove
schema-conformance.test.ts	Remove
opfs-smoke.browser.test.ts	Remove
repositoriesSQL.test.ts	Adapt to use DexieAdapter
onlineProxy.test.ts	Adapt to use DexieAdapter

User data migration

Pre-MVP, zero production users. On first launch after this change:

1. Old OPFS/IndexedDB data (from sqlite-wasm) is orphaned
2. DexieAdapter starts with a fresh IndexedDB database
3. On first sync, data is repopulated from D1 (Cloudflare backend)

No migration script is needed.

Consequences

Positive

• ~1.2MB bundle size reduction — WASM binary removed; Dexie is ~20KB gzipped
• Instant initialization — no WASM download, no OPFS capability detection, no Web Worker
• Simpler initialization — from ~250 lines of async init + VFS setup to new Dexie()
• No CSP relaxation needed — remove 'unsafe-eval' and 'wasm-unsafe-eval' from Content-Security-Policy
• No OPFS browser quirks — IndexedDB is universally supported (IE10+, all mobile browsers)
• Faster test suite — fake-indexeddb initializes instantly, no WASM loading
• Schema changes are simpler — Dexie version().stores() handles migrations automatically

Negative

• No SQL queries — query(sql, params) now parses SQL and maps to Dexie. Unrecognized SQL patterns fail with clear errors.
• No cross-tab multi-writer — BroadcastChannel still works (same as current implementation), but IndexedDB has no native cross-tab data notifications.
• IndexedDB browser quirks — different browsers have subtle IndexedDB behavior differences (Dexie abstracts most of these).
• Dexie version bumps — schema changes require manual db.version(N).stores({...}) updates, instead of automatic DDL from Zod.
• Schema drift risk — since Dexie schema is manual (not generated from Zod), a Zod field change without corresponding Dexie update could cause drift. Mitigated by TypeScript types flowing from Zod.

Key decisions during design

Decision	Choice	Rationale
SQL parsing strategy	Regex-parser inside DexieAdapter.query()	Only ~8 simple SQL patterns exist in the codebase; no JOINs. Worth it to preserve StorageBackend contract for 27 callers.
Schema declaration	Manual db.version().stores({...})	Zod→Dexie generation is fragile. 14 tables, written once, rarely change.
Multi-tab notifications	BroadcastChannel (same as before)	Already implemented and tested. ~20 lines copied from SQLiteStore.
OnlineProxy refactor	Receives StorageBackend by injection	Cleaner separation, prepared for future backend swaps.
Data migration	Reset (fresh start + sync from D1)	Pre-MVP, zero users. Simpler and safer than migration scripts.
applyMigrations()	No-op	Dexie handles schema versioning internally; interface method kept for contract compatibility.

References

• Issue: #510 Revert local storage from SQLite WASM to Dexie/IndexedDB
• Supersedes (partially): ADR-0017: Infrastructure Migrations — PostgreSQL→D1 and Dexie→SQLite WASM
• Original Dexie setup: ADR-0001: Dexie Storage
• RxDB Storage Comparison: https://rxdb.info/articles/localstorage-indexeddb-cookies-opfs-sqlite-wasm.html
• Smashing Magazine (May 2026): "The Architecture Of Local-First Web Development"
• LogRocket (2025): "Offline-first frontend apps in 2025: IndexedDB and SQLite"
• Notion Engineering Blog (2024): "How we sped up Notion in the browser with WASM SQLite"