Offline-first sync — the transactional Outbox (desktop)

Parent: key-designs

The desktop's hard architectural part. Web is online-first (pure HTTP delegates + read-only wa-sqlite caches, no outbox); desktop is genuinely offline-first for the 6 shipped domains (books/bookmarks/notes/vocabulary/preferences/review) via Hybrid*Repository wrappers.

Substrate: native SQLite over IPC. Desktop uses better-sqlite3 in the Electron main process (main/sqlite-handler.ts, WAL + busy_timeout=30000), not wa-sqlite — the renderer facade (ElectronSqliteHost) serializes all ops through a single promise chain and maps transaction(fn) onto an IPC txBegin/txExec/txCommit/txRollback protocol. Because better-sqlite3 is synchronous but the renderer's tx callback spans async IPC roundtrips, it can't use db.transaction(fn); instead a tx-id bridge issues BEGIN, returns an id, and runs subsequent calls under it. A LOAD_TOKEN watermark + resetPendingTransactions() defuse a reload-orphaned-transaction deadlock (the "reader stuck on Loading book…" bug).

Authority = cloud-authoritative, wipe-and-refill. Reads do HTTP-then-seed-local: seedLocal does DELETE … WHERE id NOT LIKE 'temp_%' then re-inserts server rows on every list (so a cross-device server delete can't linger); unsynced temp_% rows survive. The whole FULL_SCHEMA_SQL is reapplied every boot. Comment in code: "cloud is the source of truth; the local cache refills on next session."

Write path (traced through vocabulary.create):

1. Online-preferred — try vocabularyApi.create first; on success just mirror the row locally (avoids the temp-id↔server-id gap when connected).
2. Offline fallback — mint temp_${uuid}, and in one transaction insert the domain row and append the outbox entry (insertEntry + Outbox.appendInTx). Same-tx atomicity is the whole point: a crash between the two would leak a local row with no upstream sync, desyncing the device forever.
3. Trigger a drain.
4. Drain (OutboxProcessor) — oldest-first, coalesced, skip past MAX_ATTEMPTS=5; on success markDone (DELETE), on failure markFailed (attempts++, last_error). Triggers: the online event, after every write, and once at boot after a 2 s delay (no periodic timer, to protect first-paint).
5. Reconcile — the create handler replays the POST, then UPDATE … SET id=server_id WHERE id=tempId: the temp row is rewritten in place to the server identity.

Conflict model = last-write-wins + server idempotency, no client merge (Outbox: "no de-dup, no compaction, no out-of-order draining … the server is idempotent on duplicate (bookId, page, label)"). SRS is not run client-side — the server owns it (vocabulary-loop); SRS columns are deliberately absent from the local mirror.

Honestly still rough (candid in code): every hybrid row uses the literal 'local' user (no per-user scoping — clearHybridLocalState() wipes on session change to paper over cross-user leakage), and the library slice is HTTP-only (quota/local-move deferred). Load-bearing and E2E-exercised: the same-tx outbox atomicity, wipe-and-refill reads, the tx-id bridge, and the temp-id reconcile.