RFC: Hardening the SQLite persistence / Electric sync stack

[KyleAMathews]

RFC: Hardening the SQLite persistence / Electric sync stack

Status: Active hardening plan
Audit baseline: 2026-09-16, main 3b99117
Scope: Correctness, durability, recovery, driver conformance, and type-composition fixes. Product-policy changes remain explicitly decision-gated.

---

1. Current starting point

The linked work was re-audited against current main. Resolved issues and superseded PRs have been removed from this RFC. The active inventory is now 15 open issues and 4 open PRs.

These reports are coupled. Most failures come from ownership or ordering mistakes between the sync source, persistence layer, collection lifecycle, coordinator, and SQLite driver. The dangerous terminal states are a collection that is ready but incomplete, a visible write that is not durable, or a startup that retries forever.

Original defect IDs are retained so existing discussion remains linkable; gaps are resolved items removed by this audit.

Area	Defect	Active issues / PRs	Current-main assessment
Reset and resume consistency	D1	#1589	Unresolved. A schema reset can wipe rows without clearing collection metadata, allowing a stale resume point to outlive its data.
Coordinator routing	D2	#1589, #1753	Unresolved. Adapter ownership and schema-version routing are not consistently keyed per collection.
Cross-tab wire safety	D3	#1498	Unresolved. Clone-unsafe request state can still cross BroadcastChannel.
op-sqlite result decoding	D4	#1499	Unresolved. The v14 columnar executeAsync result can be interpreted as an empty SELECT result.
Offline readiness	D6	#1416, #1443, PR #1615	Unresolved and policy-gated. Locally hydrated rows can remain blocked on an unavailable upstream.
Persistence durability	D7	#1456, #1754, PR #1822	Partially improved, not resolved. Persistence failures remain insufficiently observable and a hydrate-window close can still strand a sync transaction. PR #1822 addresses only the stale replay-read slice.
Offline mutation persistence	D8	#82, #865, #1456	Unresolved and policy-gated. Pending sync-present mutations are not a durable outbox.
SQLite expression indexes	D9	PR #1487	Unresolved. Index DDL and runtime predicates use incompatible JSON-path SQL shapes.
Browser multi-tab topology	D10	#1486, #1753	Unresolved and policy-gated. Web Locks leadership does not by itself provide safe ownership of an OPFS exclusive handle, and sync-ingested writes can bypass leadership.
Type composition	D11	#1452, PR #1560	Unresolved. Persisted option results and vendor database types do not compose cleanly with createCollection.
Corruption recovery	D12	#1567	Unresolved and policy-gated. A corrupt database can wedge startup indefinitely or produce an empty local-only collection.
Follower subset ensure	D13	#1498	Unresolved. The leader can acknowledge a follower subset request without actually ensuring it.
Migration idempotence	Additional active report	#1711	Unresolved. Tauri startup can re-add replay_json and fail with a duplicate-column error.
Shared-driver scheduling	Additional active report	#1752	Unresolved. Cold-start persist work can starve hydration on the shared browser driver.

The earlier prepared commits and claim that the first three PRs could be split directly are no longer current: that branch has diverged substantially from main. Each fix must be rebuilt and proved against the audit baseline.

2. Release-gating invariants

1. A destructive reset clears the rows, tombstones, applied transactions, and sync metadata it invalidates in one transaction.
2. A sync adapter never trusts a resume point without a compatible, complete local baseline.
3. Collection A's configuration or traffic cannot read, write, reset, or acknowledge work for collection B.
4. Every coordinator message is clone-safe, routed to one collection, and reports success only after the requested work happened.
5. A known driver result shape is decoded losslessly; an unknown shape is an error, never an empty result.
6. Readiness has one explicit policy and settles exactly once across hydration, upstream readiness, failure, and cleanup.
7. An accepted transaction becomes durable or replayable, or produces an observable failure. It cannot silently disappear on restart.
8. Index DDL and indexed runtime predicates have the same SQLite expression shape.
9. Exactly one supported owner accesses an exclusive OPFS database, including during leadership changes.
10. Schema migrations are idempotent and corruption recovery cannot inherit stale sync state.

3. Oracle-first testing rule

All work under this RFC follows docs/contributing/oracle-tests.md. A PR starts with a reproducible RED oracle on current main and ends with that same oracle GREEN. A narrow witness is retained beside generalized histories; it is not replaced after the first fix.

Every oracle PR must name and demonstrate all six responsibilities:

1. Contract / law and source: the invariant and the authoritative contract, issue, SQLite behavior, or explicit maintainer decision.
2. Legal history / domain: valid event histories, including adversarial callback partitions and restart or cleanup boundaries.
3. Independent oracle: a small reference model that does not call the production helper it judges.
4. Production path / fixture: the public collection, adapter, driver, coordinator, or browser path exercised by the report.
5. Exact observation / checkpoint: the rows, metadata, status, durable state, query plan, error, or schema inspected.
6. Evidence: reach evidence that the intended branch ran; fault-control evidence that a known-bad implementation fails; deterministic replay data; and cleanup evidence proving late work cannot leak into the next test or lifecycle.

For unresolved product policy, models and tests may expose the alternatives but do not select the contract. The maintainer decision becomes the oracle's law before implementation begins.

4. Ordered implementation plan

Workstream 1 — reset and resume safety (D1, #1589)

RED oracle: Model a replica as rows, tombstones, applied transactions, metadata, and generation. Generate schema mismatch, partial restore, external row loss, restart, and empty up-to-date histories. Exercise persistedCollectionOptions plus the Electric stream fixture, and observe both SQLite state and the collection after restart. The preserved fault must leave a non-initial resume offset behind after clearing rows.

Fix and GREEN: Clear collection metadata in the same reset transaction as row state. Add a defensive resume validator that rejects a non-initial cursor when the persisted baseline cannot justify it, records a reset, and starts a clean snapshot. Prove atomic refetch, deterministic replay, and cleanup fencing.

Workstream 2 — per-collection coordinator correctness (D2, D3, D13; #1498, #1753, #1589)

RED oracle: Generate two or more collections with different policies and schema versions across leader/follower tabs. Partition and clone every coordinator message, request a follower subset, change leadership, and ingest sync writes. The reference model is a map keyed by collection identity; it permits no cross-key effects and acknowledges only completed requests. Observe rows, reset counts, message payloads, acknowledgements, and ownership.

Fix and GREEN: Replace mutable global adapter routing with a per-collection registry; define clone-safe wire DTOs; make follower subset ensure perform real leader work; and route all persistence writes, including sync-ingested writes, through the elected owner. Keep a fixed DataCloneError witness and a fixed cross-collection reset witness, then add browser multi-context histories.

Workstream 3 — op-sqlite and driver conformance (D4, #1499)

RED oracle: Define an independent statement-result decoder model for row-array, columnar rawRows plus columnNames, empty SELECT, and write-result shapes. Run SELECT-after-INSERT and pre-populated registry restart histories through the real driver contract fixture. An unknown result shape must be the fault control and must never be observed as an empty row set.

Fix and GREEN: Decode the op-sqlite v14 columnar shape before write markers, throw on unknown shapes, and run the expanded shared contract suite across every SQLite driver package.

Workstream 4 — transaction durability and hydrate-window races (D7, #1456, #1754, PR #1822)

RED oracle: Use an independent append-only transaction model and a deterministic scheduler to enumerate hydrate-window open/close, buffered sync commit, persistence failure, replay, cleanup, and restart interleavings. Observe the in-memory rows, durable rows, buffered transactions, error channel, and post-restart rows at exact checkpoints. Inject a known lost-flush mutant and a stale-replay mutant.

Fix and GREEN: Give every buffered transaction a terminal flush, replay, or visible-failure outcome; fence stale replay and hydration by lifecycle generation; and make persistence failures structured and observable. Evaluate PR #1822 inside this generalized suite—it may land as a slice, but it does not close #1754 until the lost-update history is green.

Workstream 5 — query planning and shared-driver scheduling (D9, PR #1487; #1752)

Expression-index RED oracle: Generate JSON paths and values independently, compare indexed query results with a scan model, and inspect EXPLAIN QUERY PLAN at the predicate checkpoint. Mutating either the DDL path or predicate path back to a bound parameter must fail. Rebase PR #1487 and merge only when the generalized oracle and package suite are green.

Scheduling RED oracle: Model the shared driver as ordered hydrate and persist work with explicit fairness bounds. Generate many cold-start collections and unrelated writes without using wall-clock timing as the verdict. Observe dequeue order, hydrate completion checkpoints, and bounded pending work; inject the current persist-first storm as the fault control.

Fix and GREEN: Make DDL and predicate expression shapes identical while continuing to bind values. Then add bounded batching or fair scheduling so persist work cannot starve hydration, and confirm the same order law through the browser/OPFS fixture.

Workstream 6 — decision-gated behavior

No implementation starts until each subsection records an explicit maintainer contract.

Readiness (D6, #1416, #1443, PR #1615): Decide whether an eager sync-present collection becomes ready after local hydration or remains upstream-gated. The oracle state machine must cover rows/no rows, offline upstream, on-demand mode, simultaneous hydration/upstream signals, failure, cleanup, and restart, observing one readiness transition and no late transition. Evaluate PR #1615 only after that law is recorded.

Offline outbox (D8, #82, #865, #1456): Decide whether unconfirmed sync-present mutations survive restart and how acknowledgement, rejection, and conflict affect them. If accepted, model persisted base plus pending delta independently and generate mutate/restart/ack/reject/conflict histories through the public mutation path. Observe base, outbox, visible rows, and durable rows. If deferred, document the non-durable contract explicitly rather than implying offline-write support.

Multi-tab topology (D10, #1486, #1753): Choose one owner model: a SharedWorker owns the OPFS handle, or followers route all adapter operations through the leader. The oracle must generate tab open/close, leader loss, delayed messages, and concurrent sync/local writes. A Playwright multi-context fixture observes handle owners, rows, resets, and eventual progress; a two-owner mutant must fail.

Corruption recovery (D12, #1567): Decide automatic quarantine/rebuild versus an explicit recovery option and define which collections are rebuildable replicas. After Workstream 1 is green, inject truncated, corrupt, and non-database files through real drivers. The independent model allows one close/quarantine/recreate attempt, preserves the corrupt artifact, rejects unrelated errors, and never inherits a stale resume point.

Workstream 7 — type and migration correctness (D11, #1452, PR #1560; #1711)

Type RED oracle: Compile a matrix of inferred and explicit schemas, spread-composed persisted options, valid/invalid collection configurations, and vendor SQLite database values. Independent type assertions state the expected inferred schema and rejected cases. Fix the option-result overloads and Expo vendor types, then merge PR #1560 or its rebased equivalent only when the matrix and package typecheck are green.

Migration RED oracle: Create real pre-upgrade Tauri databases at every supported schema version, including a database where replay_json already exists. Open, migrate, close, and reopen each twice. Compare PRAGMA schema information and preserved rows with a simple versioned-schema model; an unconditional duplicate ALTER is the fault control.

5. Landing and closure rules

• Land workstreams in order unless a PR is demonstrably independent; Workstream 6 corruption depends on Workstream 1.
• Each PR includes the RED commit or reproducible pre-fix result, the GREEN result, seed/replay instructions, and the focused package/type/lint commands.
• Service-backed and browser-specific claims require their real fixtures; mocks alone do not close those issues.
• Close an issue only when its fixed witness, generalized oracle, cleanup checks, and relevant full package suite are green on the final implementation.
• Keep separate changesets and focused PRs where workstreams can ship independently.

6. Open maintainer decisions

1. Eager sync-present readiness: local-first or upstream-gated.
2. Offline outbox: durable pending mutations or an explicitly non-durable contract.
3. Browser multi-tab ownership: SharedWorker owner or leader-RPC owner.
4. Corruption handling: automatic quarantine for rebuildable replicas or an opt-in recovery API.

These decisions set observable contracts. Record each here before accepting its implementation.

7. Deferred architecture

A replica manifest, orthogonal storage/hydration/sync/durability status, generation-fenced reset operations, a database-level storage broker, authority classification, operational repair APIs, and a typed persistence-composition facade remain future directions. They are not prerequisites for the correctness work above.

[KasparEtter]

Great to see progress on hardening the persistence/Electric integration. One additional interaction that may be worth testing is subquery move-out handling after persisted hydration: we observed the row and its Electric tag metadata being restored, but the Electric adapter’s private tag index was apparently not rebuilt because hydration bypasses processChangeMessage/processTagsForChangeMessage, causing a subsequent move-out to be ignored while tagLength remained undefined. We did not file a separate issue because we could no longer reproduce this reliably after upgrading to @tanstack/db 0.6.14 and @tanstack/electric-db-collection 0.3.12, but a persisted tagged row resumed after its insert offset and followed only by the matching move-out may be a useful test case.

[iwimarin]

Production report for D7 / invariant 7: we observed a persisted Electric replica whose resume marker advanced past rows that did not make it into its SQLite materialization. On the next boot the valid-looking resume offset meant the missing rows were never re-sent. The triggering condition is consistent with the fire-and-forget persistence path (void runtime.persistAndBroadcastExternalSyncTransaction(...).catch(...)): the in-memory source commits and downstream resume metadata can progress even if a durable write fails.

We have added an application-level integrity gate (row-set fingerprint plus ghost-row scan) and a Compose-backed regression that uses the real createCollection(persistedCollectionOptions(electricCollectionOptions(...))) stack against Electric + Postgres: seed a filtered initial snapshot while preserving the offset, detect the incomplete persisted replica, clear rows + electric:resume atomically, and verify a fresh mount receives all rows. We also filed the companion unknown-key partial-update issue: #1698

Versions observed: @tanstack/electric-db-collection 0.3.4, @tanstack/db-sqlite-persistence-core 0.1.10, @electric-sql/client 1.5.18, Electric server 1.6.10. Happy to contribute the test shape upstream if useful.

[KyleAMathews]

Oracle audit handoff from #1808 / #1816

Per maintainer direction, the remaining offline persistence/execution policies belong here, not on #1808's merge bar. The oracle PR keeps its verified narrow fixes and does not choose these contracts implicitly.

Highest-risk continuation: successful server work, failed durable acknowledgment

The audit observed a real LocalStorage adapter/executor path where the provider succeeds, outbox deletion throws internally, and the executor reports success while the stored record remains. Reopen/restart can execute it again. A rejecting custom-storage path also exposes the policy question after provider success. This is not solved by making each caller await its own transaction, nor by awaiting IndexedDB transaction completion (both addressed in #1816).

Acceptance history for this RFC: admit A durably → provider A succeeds → deleting A's record fails → inspect caller outcome, error reporting, optimistic state and durable bytes → restart → observe dispatch and idempotency key. Decide how to acknowledge/retry/quarantine without pretending a local error undoes the remote side effect. Also cover unavailable reads separately from a genuinely empty outbox.

Other retained decisions and oracle dimensions

• Leadership loss before durable admission: should the operation fall back to direct execution or remain queued? Keep this separate from the approved policy that already-issued work may finish but an old executor starts no new queued/retry work.
• Terminal waiters and restored overlays: wait after completion, remove/clear pending records, restore sibling transactions, and settle in different orders. Choose caller and optimistic-lifetime semantics before extending expected outcomes.
• Retry hooks that throw: retain the transaction's identity, original provider error and observable durable state; do not let another transaction's storage failure decide this caller's outcome.
• Serializer domain: metadata, native values, reserved markers and mixed-version tabs/rollback. New-reader/old-record compatibility does not establish old-reader/new-record safety.
• Unknown encodings: retained unreadable work currently needs a recovery/diagnostic policy. Never silently delete it or claim it replayed successfully.

Audit crosswalk: fresh A10 R4/R5/R7/R9/R11/R12/R13 and earlier review R8. These are a mixture of reproduced failure paths and unresolved support policies, not eight new proven bugs. Keep the independent FIFO/ownership, per-transaction completion, storage reopen and wire-format oracles; avoid a second implementation of the executor.

[KyleAMathews]

2026-09-15 closure-audit inventory additions

I compared the completed 99-item issue/PR audit against this RFC's current body and discussion. The following persistence-related links were absent. This is an inventory update only: it does not assert that every item is a new defect, approve a mechanism, or move non-persistence work into this RFC.

Missing issues

• persistedCollectionOptions: schema type parameter not inferred, result incompatible with createCollection #1452 — the source issue behind the D11 persistedCollectionOptions schema-inference problem. PR fix(db-sqlite-persistence-core): add schema-aware overloads to persistedCollectionOptions #1453 is already listed here, but its issue was not.
• Tauri: persisted collection startup fails with duplicate column name: replay_json #1711 — Tauri can reject the guarded replay_json migration with a plain string rather than an Error; the duplicate-column check then misses it and persisted startup fails. This belongs with D4 / driver conformance and needs string- and Error-shaped rejection coverage without swallowing unrelated migration failures.
• Cold-start persist storm blocks hydrates for seconds on the shared SQLite driver (browser/OPFS) #1752 — cold-start snapshots perform per-row persisted reads/writes on the shared browser/OPFS driver and can block hydration. Track this as a persistence performance workstream with a real OPFS journey/benchmark; the audit's local witness established per-row growth, not the reported wall-clock timings.
• BrowserCollectionCoordinator: per-collection schemaVersions cause reset loops, and sync-ingested writes bypass leadership #1753 — coordinator umbrella that overlaps D2, D3, and D13 but also retains uncovered boundaries: per-adapter initialization races, external sync persistence bypassing leadership, the leader subset handler acknowledging without forwarding, and local rollback after the application handler has already succeeded. Landing the existing routing/clone-safety work would be only a partial disposition.
• Persisted collections: sync transaction straddling a hydrate-window close is buffered but never flushed (lost update) #1754 — a sync transaction begun during hydration can commit after the hydration drain has closed, enter the old buffer too late, and never flush. This is a separate correctness invariant from ordinary hydration ordering; its regression needs publication, durability, receipt settlement, sibling FIFO ordering, cleanup/abort, and generation fencing.

Missing PRs

• perf: optimize hot paths in persistence, scheduling, and React hooks #1542 — stale/conflicting bundle containing persistence queue and serialization optimizations alongside unrelated scheduler/React work. The persistence pieces overlap Cold-start persist storm blocks hydrates for seconds on the shared SQLite driver (browser/OPFS) #1752 and should be recovered as focused current-main work rather than merging the bundle.
• fix: preserve accepted snapshots and fence stale replay reads #1822 — only its stale offline-replay fencing slice belongs in this RFC's inventory; its accepted-snapshot and Top-K changes remain outside this RFC. The audit reproduced a remaining replay gap after permanent terminal removal, so this is evidence for the already-recorded acknowledgment/retry policy work, not a ready RFC implementation.

Already present and therefore not duplicated: #1498, #1589, and PR #1615. #1698 is also already linked in the D7 production-report discussion.