Background
SQLiteRecordAdapter uses sql.js (SQLite compiled to WASM). WASM has no filesystem access, so the adapter bridges to disk manually:
open() reads the entire .db file into memory (readFileSync → new SQL.Database(fileBuffer)). From that point the file on disk is a stale snapshot.persist() — called after every write — serializes the entire in-memory database (db.export()) and rewrites the whole file with writeFileSync.
This is unlike native SQLite (page-level writes, journaling/WAL, OS file locks). Three consequences:
- Multi-process clobbering. Two processes opening the same stack file each hold an independent in-memory copy and periodically overwrite the entire file. Process A's writes vanish the moment process B persists. There is no locking and no detection — data loss is silent.
- No crash safety.
writeFileSync truncates and rewrites in place. A crash mid-persist leaves a torn, unparseable database. There is no journal and no temp-file-and-rename.
- Write amplification. Every record write is an O(database size) disk write. At 50 MB of accumulated data, one
create() costs a 50 MB write — and Stack.update() persists twice (version snapshot, then the update).
The planned JSON adapter will have the same read-all/rewrite-all shape, so this isn't sql.js-specific — it's a property of any whole-file adapter.
Decided mental model
Rather than making the local adapter safe for concurrent multi-app access, we're adopting the single-writer model used by most local-first systems:
A stack file is owned by exactly one process. Multiple apps share a stack by talking to a server (local or hosted) via adapter-api — never by opening the file directly.
Two reasons this is the right boundary, not just a workaround:
- Permissions only exist behind the server. Plain
Stack performs no permission checks, ScopedStack is opt-in, and appId is self-reported by the writing app. An app with direct file access has unconditional read/write over everything — records, grants, groups, token hashes. Multi-app-via-file doesn't just risk corruption; it bypasses the entire access-control design.
- It matches real deployment shapes. A non-expert user runs a stack either through an app that embeds its own private stack, through an app that exposes a localhost server, or through a hosted provider. Direct file access is for the server process itself and for single-app embedded use.
This reframes adapter-local as an embedded/infrastructure package with two legitimate consumers: (a) a single app that owns its own stack, and (b) a server implementation. adapter-api becomes the primary app-facing adapter for any shared stack, local or remote.
Work items
1. Spec: add a "Concurrency & storage ownership" section (docs/spec.md)
State explicitly:
- A stack's backing storage (SQLite file, JSON directory) has exactly one owning process at a time. Adapters are not required to support concurrent multi-process access, and the whole-file adapters do not.
- Multi-app access goes through a server implementation over the wire protocol. This is also the only topology in which permissions, grants, and
appId attribution are meaningful — direct storage access implies full trust.
- Adapters SHOULD fail loudly (error at
open()/initialize()) when a second process attempts to open storage that is already owned, rather than corrupting silently.
- Adapters SHOULD persist atomically so that a crash mid-write never leaves storage unreadable.
2. README: fix the implied mental model
The current pitch ("apps only talk to the Haverstack library — they don't know or care whether the underlying storage is a local SQLite database … or a remote server") reads as an invitation for any app to point LocalAdapter at the user's stack file. Changes:
- Add a short "How apps share a stack" section: one process owns the file; multiple apps → run a server and use
adapter-api (works the same against localhost or a hosted provider).
- In the adapter table /
adapter-local description, note it is for single-app embedded use and for server implementations — not for concurrent access by multiple apps.
- Keep the "switch backends without changing your app" promise, but scope it to the
Stack/StackClient API level.
3. Atomic persist in record-adapter-sqljs
Write db.export() to a temp file in the same directory, then rename() over the target. Rename is atomic on POSIX and fixes torn-write corruption for a few lines of code. Apply the same pattern to the future JSON adapter.
4. Fail loudly on double-open (lock file)
Acquire a lock in initialize()/open() (lock file beside the .db, or flock where available) and release it in close(). A second opener should get a clear error ("stack is in use by another process — connect via its server instead") rather than silently entering last-writer-wins mode. Needs a story for stale locks after a crash (e.g. PID + liveness check, or an explicit --force escape hatch).
5. Reduce write amplification (follow-up, can be split out)
Options, roughly in order of ambition:
- Debounce/coalesce
persist() (e.g. write-behind with a small interval, plus flush on flush()/close()). Cheap, but widens the crash-loss window — acceptable once persists are atomic.
- Avoid the double persist in
Stack.update() (version snapshot + record write currently trigger two full-file writes).
- Longer term: a native SQLite record adapter for the Node path (
node:sqlite or better-sqlite3) which gets page-level writes, WAL, and real file locking for free — keeping sql.js for a genuinely browser-targeted adapter (OPFS/IndexedDB), where in-memory single-tab operation is the norm. Note the current sql.js adapter is Node-only in practice anyway (imports fs, node:crypto, uses Buffer), so it pays sql.js's costs without collecting its portability benefit.
Non-goals
- Making whole-file adapters safe for concurrent multi-process writes (CRDTs, merge-on-load, page-level locking). The single-writer rule makes this unnecessary.
- Offline sync / conflict resolution for
adapter-api — separate concern, already noted as deferred in the adapter.
Suggested order
Items 1–4 are small and independent; 3 and 4 are worth doing regardless of topology since even a single process can crash mid-write or be opened twice by accident (CLI while the server is running). Item 5 can be a follow-up issue if it grows.
Background
SQLiteRecordAdapteruses sql.js (SQLite compiled to WASM). WASM has no filesystem access, so the adapter bridges to disk manually:open()reads the entire.dbfile into memory (readFileSync→new SQL.Database(fileBuffer)). From that point the file on disk is a stale snapshot.persist()— called after every write — serializes the entire in-memory database (db.export()) and rewrites the whole file withwriteFileSync.This is unlike native SQLite (page-level writes, journaling/WAL, OS file locks). Three consequences:
writeFileSynctruncates and rewrites in place. A crash mid-persist leaves a torn, unparseable database. There is no journal and no temp-file-and-rename.create()costs a 50 MB write — andStack.update()persists twice (version snapshot, then the update).The planned JSON adapter will have the same read-all/rewrite-all shape, so this isn't sql.js-specific — it's a property of any whole-file adapter.
Decided mental model
Rather than making the local adapter safe for concurrent multi-app access, we're adopting the single-writer model used by most local-first systems:
Two reasons this is the right boundary, not just a workaround:
Stackperforms no permission checks,ScopedStackis opt-in, andappIdis self-reported by the writing app. An app with direct file access has unconditional read/write over everything — records, grants, groups, token hashes. Multi-app-via-file doesn't just risk corruption; it bypasses the entire access-control design.This reframes
adapter-localas an embedded/infrastructure package with two legitimate consumers: (a) a single app that owns its own stack, and (b) a server implementation.adapter-apibecomes the primary app-facing adapter for any shared stack, local or remote.Work items
1. Spec: add a "Concurrency & storage ownership" section (
docs/spec.md)State explicitly:
appIdattribution are meaningful — direct storage access implies full trust.open()/initialize()) when a second process attempts to open storage that is already owned, rather than corrupting silently.2. README: fix the implied mental model
The current pitch ("apps only talk to the Haverstack library — they don't know or care whether the underlying storage is a local SQLite database … or a remote server") reads as an invitation for any app to point
LocalAdapterat the user's stack file. Changes:adapter-api(works the same againstlocalhostor a hosted provider).adapter-localdescription, note it is for single-app embedded use and for server implementations — not for concurrent access by multiple apps.Stack/StackClientAPI level.3. Atomic persist in
record-adapter-sqljsWrite
db.export()to a temp file in the same directory, thenrename()over the target. Rename is atomic on POSIX and fixes torn-write corruption for a few lines of code. Apply the same pattern to the future JSON adapter.4. Fail loudly on double-open (lock file)
Acquire a lock in
initialize()/open()(lock file beside the.db, orflockwhere available) and release it inclose(). A second opener should get a clear error ("stack is in use by another process — connect via its server instead") rather than silently entering last-writer-wins mode. Needs a story for stale locks after a crash (e.g. PID + liveness check, or an explicit--forceescape hatch).5. Reduce write amplification (follow-up, can be split out)
Options, roughly in order of ambition:
persist()(e.g. write-behind with a small interval, plus flush onflush()/close()). Cheap, but widens the crash-loss window — acceptable once persists are atomic.Stack.update()(version snapshot + record write currently trigger two full-file writes).node:sqliteorbetter-sqlite3) which gets page-level writes, WAL, and real file locking for free — keeping sql.js for a genuinely browser-targeted adapter (OPFS/IndexedDB), where in-memory single-tab operation is the norm. Note the current sql.js adapter is Node-only in practice anyway (importsfs,node:crypto, usesBuffer), so it pays sql.js's costs without collecting its portability benefit.Non-goals
adapter-api— separate concern, already noted as deferred in the adapter.Suggested order
Items 1–4 are small and independent; 3 and 4 are worth doing regardless of topology since even a single process can crash mid-write or be opened twice by accident (CLI while the server is running). Item 5 can be a follow-up issue if it grows.