Persistence of synced data #865
Send to my agentA common request, and something we want to develop in time, is a persistence layer for TanStack DB. This would allow users to persist synced data onto the user’s device, providing faster startup and potentially offline support.
This issue is intended as an opening to the discussion: laying out the problem space and a few initial ideas. We will follow up with a more detailed plan once we’ve done some research and experiments.
See also the older issue for the wider "offline support" #82
Requirements#
- A pluggable persistence API that allows multiple alternative backends, such as IndexedDB or OPFS in the browser, or SQLite in a React Native app. OPFS + SQLite-WASM is now a real option for high-performance browser persistence, but comes with a different set of constraints to IDB.
- Support for larger-than-memory data, with paging in/out of the persistence layer depending on the queries being executed.
- In the browser we need to safely handle multiple tabs/windows syncing to the same persistence layer.
- Access to the persisted data should be possible while the app is offline.
- Schema evolution - persisted stores need a version number and upgrade path. We should assume schemas (and internal encodings) will evolve over time and provide hooks to migrate, or to wipe/resync safely when migration isn’t possible.
Relationship to offline transactions#
We already have an early offline mutation persister in @tanstack/offline-transactions:
https://github.com/TanStack/db/tree/main/packages/offline-transactions
This is intentionally a separate problem to “persist synced data”. That said, once we do have persisted collections, queries will need to compose:
- state loaded from the persisted store plus
- any pending offline mutations from the offline transaction log
So persistence can’t assume “disk == canonical local view”. The in-memory view is: persisted base + pending local delta, and that delta may exist even if the main persistence layer is not enabled for a given collection. This distinction is important to keep in mind in the design.
Issues with larger-than-memory data and paging#
A core motivation for persistence is not only to provide offline support, but also to enable very large datasets that are persisted to a non-memory-backed store. We would then need to support paging in/out of the persistence layer depending on the queries being executed.
There is significant complexity in implementing this, and it needs to be carefully considered in the initial design. We likely need some form of indexing that enables fetching rows or pages that are needed. There are two strategies we need to consider:
Row-level paging. We can use the facilities of the underlying persistence layer to handle paging. Both IndexedDB and SQLite can support row-level filtering and pagination. We can use this to only load the rows that are needed.
It’s worth noting that we likely can’t just naïvely forward predicates to the persistence layer. The way predicates are processed could be different from how they are processed in the DB query engine. We would need to over-fetch and still filter in memory.
Page-level paging. Rather than storing DB rows as rows in the underlying persistence layer, we could construct pages and store them as rows/blobs in the persistence layer. This can be more efficient as we are loading much larger chunks of data at once, and aligns with how many durable stores handle eviction + range scans. (jlongster.com)
It does however bring a more complex implementation. We need to manage those pages, remove dead rows, and vacuum/compact the pages when they are no longer needed. Local-first clients and browser-SQLite VFS projects are useful references for this kind of design. (doc.replicache.dev)
Making a decision on this strategy is core to the design of the persistence layer, and likely requires testing and experimentation to get right.
Issues with multiple tabs/windows syncing to the same persistence layer#
In the browser there would be a single persisted store that is shared by all tabs/windows. If we then have each tab/window syncing into this shared store we can have issues with consistency and correctness:
- one tab may be further forward in time than another tab
- two tabs could be syncing the same collection and both write to the store
The likely solution is to ensure that only one tab/window/process is responsible for syncing to the store (single-writer, multi-reader). There are two ways we could achieve this:
Use a leader election algorithm to determine which tab/window/process is responsible for syncing to the store.
- We have some experience with leader elections from our work on PGlite, and understand this brings a lot of complexity.
- The leader election can be achieved using the Web Locks API, but we then need to consider background tabs becoming throttled (which can happen aggressively on mobile devices). (greenvitriol.com)
Use a
SharedWorkerto run the sync process. This is conceptually a simpler solution: we know we only have one process syncing to the store, but it brings a few more problems to solve:- We would need a way to communicate between the main thread and the shared worker, particularly for query-driven sync. Predicates will need to be serialised and sent to the shared worker, and synced data needs to be pushed back to all listening tabs/windows.
- Shared workers are not yet supported on Android Chrome. This isn’t necessarily a blocker, as most mobile browsers don’t have multiple visible tabs/windows at once. We can fall back to locking background tabs and only the foreground one syncing to the store. (MDN Web Docs)
Issues with schema evolution#
Migrations are a core part of doing persistence safely. We need to consider if this is in scope of the persistence layer, or if it's something that can be pushed back on the application. Possible solutions:
- We support schema evolution, and so would need to have:
- a way to version the persisted store
- a way to migrate the persisted store to the new schema
- We provide hooks to the application to handle schema evolution, they implement it themselves on application startup.
- The alternative is to just truncate the local data and resync from the server on all schema changes, this makes things much simpler, but at the cost of a potentially large resync for large applications.
Next steps#
We are intending to work on persistence after v1 of DB. It’s a significant undertaking and only something that a smaller portion of users need right now. This issue is intended as a tracking issue for the design and development of the persistence layer and so a good place to discuss, prototype and experiment with ideas.
Alternative for now#
Many of the collections support some level of persistence or caching themselves:
QueryCollectioncan use the native TanStack QuerypersistQueryClientoption to persist the query client to localStorage or other storage options. (tanstack.com)ElectricCollectioncan load synced data directly from the browser cache. Shape logs can be aggressively cached so you don’t go back to the server on subsequent page loads. (electric-sql.com)RXDBCollectionwraps RxDB which has its own persistence layer (including OPFS/IDB backends and multi-tab behaviour). (rxdb.info)PowerSyncCollectionuses SQLite for persistence in the browser. OPFS-backed SQLite is particularly relevant to this discussion. (powersync.com)
Research / related work#
Useful pointers for people who want to dig in or share experience:
Implementations to look at#
- Replicache — local-first client DB with durable persistence, mutation logging, and a lot of practical experience around storage tradeoffs. (doc.replicache.dev)
- Zero — query-driven sync with a client-side persistent cache; worth comparing how they reuse local data for future queries. (zero.rocicorp.dev)
- RxDB — pluggable browser persistence with multiple backends including OPFS, plus cross-tab sync patterns. (rxdb.info)
- PowerSync / SQLite-WASM on the web — current state of OPFS vs IDB, and the constraints of different SQLite VFS layers. (powersync.com)
- absurd-sql / wa-sqlite / other SQLite VFS work — prior art on treating IndexedDB/OPFS as a block device, and the real-world performance implications. (powersync.com)
- ElectricSQL client caching — shape-log-based persistence/caching and chunking strategies over append-only logs. (electric-sql.com)
Research / background#
I've been working though a possible design for this - its essentially a wrapper around the existing
create[Query/Electric/etc]Collectionoptions and hooks into the sync layer to create a read through local cache. With the query driven sync we incrementally load from the persistence too.Draft design:
Persisted Collections + Multi-Tab Query-Driven Sync (IDB + SQLite) — Full Plan (v1)#
Overview#
This feature adds first-class persistence to TanStack DB collections by turning
loadSubset()into the unified mechanism for local-first loading, remote backfilling, and cross-tab consistency.At a high level, we are building a wrapper around existing collection option factories (
queryCollectionOptions, etc.) that:Persists the synced base of each collection into a local database
- Browser: IndexedDB
- Native (RN/Expo/Electron): SQLite The persisted store becomes the fast, durable source for initial hydration and offline use.
Uses
loadSubset()as the on-demand read path from persistence Whenever a query/subscription needs data,loadSubset(options):- reads matching rows from local persistence (using indexes),
- applies them into the in-memory collection state (local-first),
- then (when online) ensures the same subset is being loaded from the remote sync layer so fresh data streams in.
Runs remote/network sync in exactly one browser tab (the leader) In browsers we elect a leader tab. The leader:
- runs the underlying remote sync implementation,
- writes committed transactions into persistence,
- broadcasts those committed transactions to all other tabs. Other tabs never run remote sync and never write persistence — they only:
- read from persistence directly via
loadSubset, - apply broadcasts from the leader to stay up to date.
Mirrors main-thread indexes into the persisted layer, and builds them on demand TanStack DB already creates indexes in user space:
- manually via
createIndex(...) - automatically via
autoIndex(driven by queries)
We add
index:added/index:removedevents so persistence can mirror those indexes and keep local reads efficient:- SQLite: create expression indexes on JSON fields on demand
- IndexedDB: maintain a userland secondary index store (no schema upgrades) and build indexes on demand
- manually via
Guarantees correctness under races and tab throttling The wrapper ensures:
- cached local hydration can’t overwrite newer remote updates (hydrate barrier + queued broadcasts),
- cache + remote snapshots don’t conflict (
insert → updatenormalisation), - leadership moves when a tab is background-throttled (lease-based election).
What this enables#
- Offline-first: queries load from local persistence even with no network.
- Fast cold start: the first visible data comes from local persistence immediately.
- One network connection (browser): leader tab owns it; all tabs stay consistent.
- Index-backed local query performance: both adapters use indexes from day one, not scans.
- Extensible persistence backends: IndexedDB today; SQLite (and later WASM SQLite in browser) via adapters.
Goals (v1)#
Local-first
loadSubsetloadSubsetloads from persistence immediately.- If online, it also ensures remote coverage and streams updates in.
- If offline, it loads from persistence only, and queues the remote ensure for when online returns.
Multi-tab correctness
Exactly one tab is leader:
- runs remote sync
- writes to persistence
- maintains persisted indexes
- broadcasts committed transactions to followers
All tabs read from local persistence directly (no leader round-trip to read).
Efficient indexing (required v1)
- Persisted indexes are created on demand.
- Persisted indexes mirror main-thread index creation/removal events.
- No full scans for index-aligned queries beyond the unavoidable “first index build” cost.
Robustness
- Avoid races between local hydrate and incoming remote updates.
- Avoid
DuplicateKeySyncErrorwhen cached rows overlap remote snapshot.
Non-goals (v1)#
- Full SQL predicate pushdown for arbitrary expressions beyond common operators (
eq,in,gt/gte/lt/lte, conjunctions). - Perfect index eviction/GC policy (we’ll store
lastUsedAt, but eviction can be v2). - Browser native IndexedDB schema upgrades for indexing (v1 uses a single indexing mechanism).
High-level Design#
1) Persistence wrapper:
persistedCollectionOptions(...)#Wrap the underlying collection config’s
sync.sync(...)and return a composed sync that:- Performs local hydration from persistence in
loadSubset - Runs the remote sync only in leader
- Broadcasts committed transactions from leader → followers
- Buffers remote txs during local hydrate to avoid race overwrites
- Normalises inserts to updates where needed to avoid duplicate-key errors
Example API:
const tasks = createCollection( persistedCollectionOptions({ ...queryCollectionOptions({ /* existing remote sync */ }), persistence: { adapter: MultiTabIndexedDBStorage({ dbName: 'app' }), // or: // adapter: ReactNativeSQLiteStorage({ ... }), }, }) )2) Persisted indexing mirrors main-thread indexing#
Instead of duplicating index declarations, persistence will mirror the indexes created in user space:
- Manual:
collection.createIndex(...) - Auto:
autoIndex(which callscreateIndexinternally)
We add collection events:
index:addedindex:removed
Persistence listens to these and ensures matching persisted index structures exist.
3) Multi-tab coordinator#
A shared coordinator per
dbNamehandles:Leader election (lease/heartbeat, robust to background throttling)
Broadcast of committed persistence transactions (leader → followers)
RPC for:
ensureRemoteSubset(loadSubset options)(followers → leader)ensurePersistedIndex(indexSignature)(followers → leader)
Critically: tabs do not proxy local reads through the leader — each tab reads persistence directly. The leader is only for remote sync and writes/maintenance.
Key Mechanics#
A) Single-writer persistence#
Only leader performs persistence writes:
- applying remote txs to persistence (
applyCommittedTx) - maintaining persisted indexes for those txs
- building persisted indexes on demand
Followers never write persistence (keeps correctness + avoids races).
B) Local-first
loadSubsetwith a hydrate barrier#Problem: remote broadcasts can arrive before local cache hydrate finishes; if you apply local after remote, you can regress state.
Solution: per collection wrapper maintains:
isHydratingbooleanqueuedRemoteTx: PersistedTx[]- a single
applyMutexthat serialisesbegin/write/commit
Algorithm:
loadSubsetsetsisHydrating = true- load cached rows using persisted indexes
- apply cached rows via
write({type:'update', ...})only - set
isHydrating = false - flush queued remote txs in order
C) Avoid
DuplicateKeySyncErrorreliably#Two rules:
- Local hydration always uses
update, neverinsert. - Remote “insert” messages are normalised to
updateat the wrapper boundary before calling TanStack DB’swrite.
This prevents the common case where cached rows exist but remote snapshot is expressed as inserts.
D) Broadcast ordering:
term+seq#term: increments on each leader changeseq: leader-generated monotonic counter per collection per term, incremented for each committed broadcast tx
Followers track
(term, seq):ignore old terms
ignore duplicate seqs
if a gap is detected, trigger recovery:
truncatelocally- re-request currently loaded subsets (local hydrate + remote ensure)
Persistence Adapter Interfaces#
1) Shared interface (used by wrapper)#
export interface PersistenceAdapter<T extends object, TKey extends string | number> { // Read path (all tabs) loadSubset( collectionId: string, options: LoadSubsetOptions, ctx?: { requiredIndexSignatures?: string[] } ): Promise<Array<{ key: TKey; value: T }>> // Write path (leader only) applyCommittedTx( collectionId: string, tx: PersistedTx<T, TKey> ): Promise<void> // Index management (leader only) ensureIndex( collectionId: string, signature: string, spec: PersistedIndexSpec ): Promise<void> markIndexRemoved?( collectionId: string, signature: string ): Promise<void> }PersistedIndexSpecis derived from the main-thread index event (expression IR, compare options, etc.) and must be serialisable.Updates may be
partial(defaultrowUpdateMode). Adapters should merge update payloads into the stored row before persisting and before computing index values; delete/update index maintenance should use the pre-merge and post-merge rows.2) Index signature (stable key)#
Main-thread
indexIdis per instance and not stable across tabs. We must derive a stable signature:signature = hash(stableStringify({ expression, compareOptions, direction, nulls, stringSort, locale, ... }))
We’ll implement
stableStringify+ hash in core (fast non-crypto hash is fine).
IndexedDB design (simple plan)#
Stores (single DB, no per-collection schema)#
rows- key:
[collectionId, pk] - value:
{ collectionId, pk, value }
- key:
idx- key:
[collectionId, indexSignature, indexValue, pk] - value:
{ collectionId, indexSignature, indexValue, pk }(or empty payload)
- key:
metakey:
[collectionId, name]values:
- index registry:
{ signature, spec, state, lastBuiltAt, lastUsedAt } - last applied
(term, seq)for tx idempotency
- index registry:
leases- leader lease rows used by coordinator
Index maintenance (leader-only)#
On
applyCommittedTx:update
rowsfor each active persisted index:
- compute old indexValue (if update/delete) and remove idx entry
- compute new indexValue (if insert/update) and insert idx entry
- for
update, merge the partial update into the stored row before computing old/new index values
IndexValue computation is done by compiling the index expression IR into an evaluator.
On-demand index build (leader-only)#
When an index appears (mirrored from main thread), or when a
loadSubsetneeds it:ensureIndex(collectionId, signature, spec):- check registry state; if ready → no-op
- build by scanning
rowsfor collectionId and populatingidxentries - mark registry ready
Followers requesting subsets will RPC to the leader to ensure required indexes are built, then query
idxlocally.
SQLite design (v1)#
Schema (per collection)#
Single table per collection:
keytype is stable per collection, so use a consistent column type (TEXT or INTEGER) per tablekeyPRIMARY KEYvalueJSON string (TEXT)
Indexes are expression indexes created on demand from the mirrored index spec, e.g.:
CREATE INDEX IF NOT EXISTS idx_<sig> ON c_<collectionId>(json_extract(value,'$.path'))- compound indexes use multiple expressions
ensureIndex(...)compiles the IR/spec into canonical SQL expressions so the query planner can use the index reliably.SQLite adapter should support a minimal driver interface and run in:
- React Native / Expo / Electron via wrapper packages
- Node in CI for tests
Multi-tab coordinator#
Responsibilities#
Leader election (lease/heartbeat)
Broadcast committed txs to all tabs
RPC:
ensureRemoteSubset(collectionId, options)ensurePersistedIndex(collectionId, signature, spec)
Leader election#
- Lease record in
leasesstore (IDB) with TTL renewal. - Foreground tab should win when possible (visibility-aware renewal/stepdown).
Package split (new packages)#
1)
db-persisted-collection-core#Depends on:
@tanstack/dbContains:
persistedCollectionOptions(...)wrapper- index signature + stable stringify/hash
- event types and helpers
- in-memory adapter + in-memory coordinator for unit tests
- shared protocol types for coordinator messages
2)
db-indexeddb-persisted-collection#Depends on: core + browser APIs
Contains:
- IDB adapter implementing
PersistenceAdapter - browser multi-tab coordinator (BroadcastChannel + IDB lease)
MultiTabIndexedDBStorage({ dbName })convenience factory
3)
db-sqlite-persisted-collection-core#Depends on: core
Contains:
SQLiteCoreAdapter(driver)implementingPersistenceAdapter- canonical SQL expression generator from index spec
- index registry table management (optional) or use
sqlite_master
4) Platform wrappers#
db-react-native-sqlite-persisted-collectiondb-expo-sqlite-persisted-collectiondb-electron-sqlite-persisted-collection- (optional)
db-node-sqlite-persisted-collectionfor CI integration tests
Each exports a driver that satisfies the core SQLite interface.
Note: possible browser optimisation (future)#
For very large local datasets in the browser, a future optimisation is to use WASM SQLite (e.g. OPFS-backed) as the persistence backend instead of IndexedDB. This can provide stronger indexing/query planning and simpler pushdown semantics. The adapter interface is designed so this can be added as a drop-in persistence adapter later.
Implementation Plan (phased)#
Phase 0 — Repo/package scaffolding#
Create new packages with minimal build/test wiring:
db-persisted-collection-coredb-indexeddb-persisted-collectiondb-sqlite-persisted-collection-coredb-react-native-sqlite-persisted-collection
Add shared TS configs, exports, and CI test hooks.
Deliverable: packages compile and publishable, no functionality yet.
Phase 1 — Add index events to
@tanstack/db#Extend
AllCollectionEventswith:index:addedindex:removed
Update
CollectionIndexesManager(packages/db/src/collection/indexes.ts):add
eventsdependency viasetDepsemit
index:addedfromcreateIndex(...)with payload:signature(stable hash)expression(IR)namecompareOptions/ direction / string collation configorigin: 'manual' | 'auto'(autoIndex path must pass origin)
Add a removal API:
collection.removeIndex(indexId|IndexProxy|signature)- manager deletes from maps + emits
index:removed
Deliverable: index lifecycle is observable and stable across tabs via signature.
Phase 2 — Core persisted wrapper (
db-persisted-collection-core)#Implement
persistedCollectionOptions(base, { persistence, coordinator }):Wrap
sync.sync(params)Attach listeners for:
index:added→ register/mirror persisted index, request leader ensureindex:removed→ mark persisted index removed (best-effort)
Implement multi-tab broadcast apply:
- buffer remote txs during local hydrate
document rowUpdateMode expectations: default
partialrequires adapter-side merge;fullcan skip read/merge
Implement
loadSubsetbehavior:- determine required persisted indexes from
whereExpression/orderBy await coordinator.ensurePersistedIndex(...)(only if needed)- read from adapter (local)
- apply local hydrate via
updateoperations only - if predicates are only partially supported, use the supported subset to load a superset and let the query engine filter
- example:
where(status = 'open' AND priority > 2 AND customFn(x))→ load usingstatus+priorityonly - if online:
coordinator.ensureRemoteSubset(...)
- determine required persisted indexes from
Insert→update normalization:
- normalize remote inserts to updates in leader before passing into TanStack DB
write(...)
- normalize remote inserts to updates in leader before passing into TanStack DB
Recovery on seq gaps:
truncate- re-request loaded subsets (the subscription already tracks them)
Deliverable: core wrapper works with in-memory adapter/coordinator and passes unit tests.
Phase 3 — IndexedDB adapter + browser coordinator (
db-indexeddb-persisted-collection)#Implement IDB schema and open/close lifecycle:
rows,idx,meta,leases
Implement leader lease + BroadcastChannel coordinator:
leader election + heartbeats
broadcast tx application messages
RPC:
- ensure remote subset
- ensure persisted index (leader triggers adapter.ensureIndex)
Implement IDB adapter:
applyCommittedTx(leader-only)ensureIndex(leader-only build scan)loadSubsetusesidxrange scans (no full scan for index-aligned queries)
Add minimal planner:
- supports
eq/in/rangeon indexed field refs - AND conjunctions selecting best index available
- cursor + orderBy when matching index
- if predicates include unsupported parts, drop them to build a broader plan and let the query engine filter the superset
- example:
where(ref('age') > 18 AND customFn(x))→ plan usesage > 18only
- supports
Deliverable: browser persistence is functional and fast for index-aligned queries, no schema upgrades required.
Phase 4 — SQLite adapter (
db-sqlite-persisted-collection-core) + RN wrapper#Implement SQLite driver interface in core adapter:
exec/all/run/transaction
Table per collection:
(key PRIMARY KEY, value TEXT)Expression index manager:
- canonical expression generator from index spec
CREATE INDEX IF NOT EXISTS ...
loadSubsetcompiles to SQL using those expressions for pushdownProvide React Native wrapper package with a driver implementation.
Deliverable: SQLite persistence works in node tests and RN wrapper compiles.
Phase 5 — Integration polish + docs/examples#
Add docs in the tracking issue and package READMEs:
- how to enable persistence
- how autoIndex interacts
- how to create manual indexes for best local performance
Add an example (browser) demonstrating:
- two tabs, leader handoff
- offline local reads
- autoIndex-driven persisted indexing
Testing Strategy (robust v1)#
Unit tests (Vitest) — core behavior#
Use in-memory adapter/coordinator in
db-persisted-collection-core:Index events
createIndexemitsindex:addedwith stable signatureremoveIndexemitsindex:removed
Local hydrate safety
- local hydrate uses
updateonly - remote inserts normalized to update → no DuplicateKeySyncError
- local hydrate uses
Hydrate barrier
- remote tx arrives during hydrate → buffered → flushed after → final state matches remote
Offline/online queue
- offline
loadSubsetresolves after local hydrate - when online returns, pending remote ensures are sent
- offline
Seq gap recovery
- missing broadcast triggers truncate + subset reload
Adapter contract tests (shared)#
Define a test suite that runs against:
- In-memory adapter
- IDB adapter (in Playwright)
- SQLite adapter (node sqlite)
Covers:
ensureIndexthenloadSubsetuses the index path (instrumentation counters)applyCommittedTxupdates rows + indexes correctly- idempotency on
(term, seq)replay
Integration tests (Playwright) — browser multi-tab#
Two tabs, one leader
- only leader runs remote sync
- followers apply broadcasts
Local reads do not round-trip
- follower
loadSubsetreads IDB directly (assert via mocked RPC calls)
- follower
Index build + query
- subscription query triggers autoIndex → index mirrored → leader builds persisted index
- both tabs can query quickly afterwards
Leader handoff
- close leader tab → follower becomes leader → continues remote sync and persistence writes
Offline local-first
- go offline → local
loadSubsetstill works - come online → remote ensure resumes and updates stream in
- go offline → local
SQLite integration tests (Node)#
Run the adapter contract suite against node-backed sqlite so CI validates:
- expression index creation
- query pushdown correctness
- tx apply correctness
Notes / Implications#
- First-time index build cost: unavoidable for on-demand indexes; equivalent to creating an index in a database. After build, queries become fast.
- Single writer: leader-only persistence writes simplifies correctness, avoids races, and keeps IDB logic manageable.
- User guidance: for large collections, users should rely on
autoIndexor manualcreateIndexfor any query they expect to be fast locally.
Hey @samwillis thanks for writing this up. The direction makes a lot of sense, especially unifying local-first hydration + remote backfilling, and approaching persistence as a pluggable wrapper on top of the existing collectionOptions.
We think IndexedDB as the default should be questioned, as WebKit has tons of bugs with IndexedDB
In our production iPad app (Capacitor iOS), we have had repeated inconsistencies with both approaches we’ve tried in the past:
- IndexedDB in the browser
- SQLite via Capacitor native bridge (historically via RxDB)
At this point we don’t trust IndexedDB as the primary durable store for local-first correctness on WebKit, and we also need an adapter story that is genuinely reliable under Capacitor.
This gist summarizes many of the IndexedDB failure modes and platform oddities that show up in offline-first apps, including Safari/WebKit specific issues like hangs, confusing transaction behavior, durability constraints, and cross-tab problems: https://gist.github.com/pesterhazy/4de96193af89a6dd5ce682ce2adff49a
Given the proposed architecture, OPFS seems like a better option for a durable local cache on iOS. Even if IndexedDB remains supported, having OPFS as the recommended backend for WebKit would be a big win for reliability.
Very excited about this @samwillis . The proposal would solve a mountain of challenges for our company side-project, cella. https://github.com/cellajs/cella. With cella, we moved into a hybrid approach where some modules are REST API CRUD (users, organizations) and the "daily-use" (pages, todos etc) data modules are 'upgraded' with sync and offline mutations. Currently we persist as much as possible through a react-query (API data is then readonly ) persister. I think in these hybrid scenarios you still want just one place to persist all data, so both the data you sync and the data you fetch. I would love to hear - if possible - how you see this potentially play out with these plans and what you would recommend for these more hybrid approaches.
EDIT: perhaps as a suggestion I think it could help the conversation - and at least my understanding - to have flowcharts that visually explain different common architectural scenarios.
I've been looking at an updated design for this, dropping IndexedDB and going SQLite only. In the browser we would use the wasm wa-sqlite. Not using IDB makes things significantly easer, and the thinking is that users who are looking to persist data than accept the bundle overhead of 500kb.
We can always add in support for IDB later if needed, but this will allow us to move quicker.
Persisted Collections + Multi-Tab Query-Driven Sync (SQLite-Only)#
Summary#
This plan standardizes persistence on SQLite across runtimes and removes raw IndexedDB as a first-class persistence adapter.
In the browser, persistence is OPFS-only via
wa-sqlite+OPFSCoopSyncVFS, with no SharedWorker requirement. Multi-tab coordination uses Web Locks, Visibility API, and BroadcastChannel.Leadership is per collection (per table), not global per database.
persistedCollectionOptions(...)infers behavior from the wrapped options:- if wrapped options include
sync, persistence augments that sync path - if wrapped options do not include
sync, persistence runs sync-absent with SQLite as source of truth
Background#
TanStack DB on-demand sync uses
loadSubset(options)as the choke point for query-driven loading and pagination. Persistence should plug into this same mechanism so:- any tab can load from local persistence immediately
- leader tabs handle remote coverage checks when sync is enabled
- tabs receive ordered updates and remain coherent
- persisted indexes mirror collection index creation in user space
Locked Decisions#
- SQLite-only persistence architecture.
- Browser storage is OPFS-only (
wa-sqlite+OPFSCoopSyncVFS). - No SharedWorker requirement in the browser architecture.
- Leadership is collection-scoped: single writer per collection/table.
persistedCollectionOptions(...)infers sync-present vs sync-absent behavior from presence ofsync.- Cloudflare Durable Objects SQLite is a supported runtime target.
- Delete tracking uses per-key tombstone state (one row per deleted key) with monotonic
row_version.
Goals#
- Local-first
loadSubsetin every runtime. - Correct multi-tab behavior with collection-scoped leadership.
- Fast local reads from SQLite in every tab.
- Reliable replay ordering via
(term, seq). - Persisted index parity with TanStack DB index lifecycle.
- Sync-absent persisted collections with automatic mutation persistence.
- Runtime coverage for browser, node, RN, Expo, Electron, and Cloudflare Durable Objects.
Non-Goals#
- Raw IndexedDB persistence adapter.
- SharedWorker-based mandatory architecture.
- Full SQL pushdown for arbitrary unsupported expressions in v1.
- Global single-writer guarantee for all tables in one DB file.
- Perfect index GC/eviction policy in v1.
Runtime Scope#
Runtime Engine Notes Browser wa-sqliteOPFS + OPFSCoopSyncVFS, leader per collectionNode better-sqlite3Reference runtime + CI contract tests React Native op-sqliteThin driver over shared core Expo op-sqliteThin driver over shared core Electron better-sqlite3in main processRenderer via IPC Cloudflare Durable Objects SQLite-backed DO storage DB executes in-process inside DO; no tab election High-Level Design#
1)
persistedCollectionOptions(...)Infers Behavior from Wrapped Options#A)
syncPresent in Wrapped Options#Wrap an existing sync collection and add local SQLite persistence.
const tasks = createCollection( persistedCollectionOptions({ ...queryCollectionOptions({ /* existing remote sync */ }), persistence: { adapter: BrowserWASQLiteStorage({ dbName: 'app' }), coordinator: BrowserCollectionCoordinator({ dbName: 'app' }), }, }), )B) No
syncin Wrapped Options#No wrapped remote sync is required. SQLite persistence is source of truth.
const drafts = createCollection( persistedCollectionOptions({ id: 'drafts', getKey: (row) => row.id, persistence: { adapter: BrowserWASQLiteStorage({ dbName: 'app' }), coordinator: BrowserCollectionCoordinator({ dbName: 'app' }), }, }), )When
syncis absent, mutations are automatically persisted (likelocalStorageCollectionOptions) and do not require remote sync.1.1) TypeScript API Sketch (Inferred Overloads)#
The API should use overloads so mode is inferred at compile-time from whether
syncexists on wrapped options.type PersistedCollectionPersistence<T extends object, TKey extends string | number> = { adapter: PersistenceAdapter<T, TKey> coordinator?: PersistedCollectionCoordinator } type PersistedSyncWrappedOptions< T extends object, TKey extends string | number, TSchema extends StandardSchemaV1 = never, TUtils extends UtilsRecord = UtilsRecord, > = CollectionConfig<T, TKey, TSchema, TUtils> & { sync: SyncConfig<T> persistence: PersistedCollectionPersistence<T, TKey> } type PersistedLocalOnlyOptions< T extends object, TKey extends string | number, TSchema extends StandardSchemaV1 = never, TUtils extends UtilsRecord = UtilsRecord, > = Omit<CollectionConfig<T, TKey, TSchema, TUtils>, 'sync'> & { persistence: PersistedCollectionPersistence<T, TKey> } export function persistedCollectionOptions< T extends object, TKey extends string | number, TSchema extends StandardSchemaV1 = never, TUtils extends UtilsRecord = UtilsRecord, >( options: PersistedSyncWrappedOptions<T, TKey, TSchema, TUtils>, ): CollectionConfig<T, TKey, TSchema, TUtils> export function persistedCollectionOptions< T extends object, TKey extends string | number, TSchema extends StandardSchemaV1 = never, TUtils extends UtilsRecord = UtilsRecord, >( options: PersistedLocalOnlyOptions<T, TKey, TSchema, TUtils>, ): CollectionConfig<T, TKey, TSchema, TUtils & PersistedCollectionUtils>Runtime rule:
if (options.sync != null)=> sync-present path- else => sync-absent path
- if
persistence.coordinatoris omitted, useSingleProcessCoordinator(intended for DO/node single-process execution)
Inference edge-case rules (fixed):
- sync-present requires
options.syncwith callablesyncfunction. synckey present but invalid (null, non-object, missingsyncfunction) throwsInvalidSyncConfigError.- sync-absent path is selected only when
synckey is not present. - user-provided
onInsert/onUpdate/onDeleteremain supported in both paths; sync-absent wrappers compose and then persist.
PersistedCollectionUtilsshould include:acceptMutations(transaction)for manual transactions- optional debug helpers (
getLeadershipState,forceReloadSubset) for tests/devtools
2) Index Lifecycle Mirrors Main-Thread Indexing#
Persisted indexes are created from the same collection index lifecycle as user-space query indexes:
- manual
collection.createIndex(...) - auto indexing (
autoIndex)
Required events in
@tanstack/db:index:addedindex:removed
Persistence listens and ensures/removes matching persisted indexes.
3) Storage Backend Options (All SQLite)#
Every backend uses the same logical persistence model (table-per-collection + JSON payloads + expression indexes), but runtime wiring differs.
Browser:
wa-sqlite+OPFSCoopSyncVFS#- storage: OPFS-only
- coordinator:
BrowserCollectionCoordinator(Web Locks + Visibility + BroadcastChannel) - leadership: collection-scoped in-tab election
Browser capability baseline:
- Phase 7 (single-tab) requires OPFS with
FileSystemSyncAccessHandle. - Phase 8 (multi-tab coordinator) additionally requires Web Locks.
- Target support is evergreen browsers from roughly the last 3 years that satisfy those capabilities.
Browser Coordination (No SharedWorker)#
Election and preference:
- Web Locks key per collection:
tsdb:leader:<dbName>:<collectionId>
- Web Locks key for SQLite write serialization:
tsdb:writer:<dbName>
- Visibility API is a preference hint:
- visible tabs should be preferred leaders
- hidden leaders can step down cooperatively
Visibility handoff protocol:
- a leader entering hidden state starts
HIDDEN_STEPDOWN_DELAY_MS(default 5000ms) - while hidden, it listens for
leader:candidateannouncements from visible tabs - if a visible contender is observed and delay elapses, current leader releases collection lock
- after handoff, apply
LEADER_HANDOFF_COOLDOWN_MS(default 3000ms) before trying to re-acquire to prevent thrash
Messaging:
- BroadcastChannel namespace per collection:
txmessages with(term, seq)and commit metadatarpcmessages forensureRemoteSubset,ensurePersistedIndex,applyLocalMutationsleaderheartbeat/announcement
Ordering and recovery:
- each collection stream has ordered
(term, seq) - followers track latest
(term, seq)seen - followers:
- ignore old terms
- ignore duplicate seq
- trigger catch-up on seq gap via
rpc:pullSince - if catch-up fails, fallback to stale-mark + subset reload
Leadership lifecycle algorithm:
- Tab starts collection interest:
- subscribe to collection channel
- attempt Web Lock acquisition for
tsdb:leader:<dbName>:<collectionId>2. If lock acquired: - increment and persist
termin SQLite metadata (transactional) - become leader for that collection
- start heartbeat timer 3. If tab becomes hidden and another visible contender exists:
- leader may step down cooperatively and release lock 4. On lock loss or unload:
- stop sync tasks for that collection
- stop heartbeat
- continue follower read path 5. Followers watch heartbeat timeout:
- on timeout, attempt lock acquisition and leadership takeover
termmonotonicity requirement:termmust survive reload/restart and never decrement for a collection.- leaders read+increment
leader_terminside a SQLite transaction before emitting heartbeat.
Node#
- storage: local sqlite via
better-sqlite3 - coordinator:
SingleProcessCoordinatorby default - common use: tests, server-side execution, tooling
React Native / Expo#
- storage:
op-sqlitewrappers for RN and Expo - coordinator: typically
SingleProcessCoordinator(single process), can be overridden if host adds cross-process sync - packaging: one shared mobile package with RN/Expo-specific entrypoints only where needed
Electron#
- storage: sqlite in main process
- coordinator:
SingleProcessCoordinatorin main process - renderer interaction: via IPC bridge only
- packaging: separate electron package that wraps node adapter semantics with IPC transport
Cloudflare Durable Objects (In-Process)#
Cloudflare Durable Objects run as single-threaded stateful actors with attached SQLite-backed storage. For a DO instance:
- no browser-style leader election is needed
- the DO instance is authoritative writer for its storage
loadSubsetand mutation persistence execute directly in-object- optional upstream sync can still be layered if needed, but sync-absent local persistence is a natural default
- this is an in-runtime execution model (DB + persistence in the same DO process), not a remote persistence adapter pattern
Example shape inside a DO:
export class AppDurableObject extends DurableObject { private tasks = createCollection( persistedCollectionOptions({ id: 'tasks', getKey: (row) => row.id, persistence: { adapter: durableObjectSQLiteAdapter(this.ctx.storage.sql), // coordinator omitted -> SingleProcessCoordinator }, }), ) }4) Collection-Scoped Coordinator#
Coordinator responsibilities per collection:
- election: one leader per
collectionId - ordered broadcast of committed tx (
term,seq) - RPC:
ensureRemoteSubset(collectionId, options)whensyncis presentensurePersistedIndex(collectionId, signature, spec)applyLocalMutations(collectionId, mutations)whensyncis absent and follower is not leader
Tabs do not proxy reads through leaders; each tab reads SQLite directly.
Runtime note:
- browser uses
BrowserCollectionCoordinator(election + BroadcastChannel RPC) - DO/node single-process execution uses
SingleProcessCoordinator(no election, no cross-tab RPC)
Coordinator contract (minimum surface):
interface PersistedCollectionCoordinator { getNodeId(): string subscribe( collectionId: string, onMessage: (message: ProtocolEnvelope<unknown>) => void, ): () => void publish(collectionId: string, message: ProtocolEnvelope<unknown>): void isLeader(collectionId: string): boolean ensureLeadership(collectionId: string): Promise<void> requestEnsureRemoteSubset?( collectionId: string, options: LoadSubsetOptions, ): Promise<void> requestEnsurePersistedIndex( collectionId: string, signature: string, spec: PersistedIndexSpec, ): Promise<void> requestApplyLocalMutations?( collectionId: string, mutations: Array<PersistedMutationEnvelope>, ): Promise<ApplyLocalMutationsResponse> pullSince?( collectionId: string, fromRowVersion: number, ): Promise<PullSinceResponse> }Coordinator validation rule:
- wrapper validates required coordinator methods at initialization based on runtime mode.
- browser multi-tab mode requires
requestEnsureRemoteSubset,requestApplyLocalMutations, andpullSince. - single-process coordinators (node/electron/do and browser single-tab) may omit cross-tab RPC helpers.
4.1) Coordinator Protocol (Implementation Draft)#
Message envelope:
type ProtocolEnvelope<TPayload> = { v: 1 dbName: string collectionId: string senderId: string ts: number payload: TPayload }Message payloads:
type LeaderHeartbeat = { type: 'leader:heartbeat' term: number leaderId: string latestSeq: number latestRowVersion: number } type TxCommitted = { type: 'tx:committed' term: number seq: number txId: string latestRowVersion: number } & ({ requiresFullReload: true } | { requiresFullReload: false changedKeys: Array<string | number> deletedKeys: Array<string | number> }) type EnsureRemoteSubsetRequest = { type: 'rpc:ensureRemoteSubset:req' rpcId: string options: LoadSubsetOptions } type EnsureRemoteSubsetResponse = { type: 'rpc:ensureRemoteSubset:res' rpcId: string ok: true } | { type: 'rpc:ensureRemoteSubset:res' rpcId: string ok: false error: string } type ApplyLocalMutationsRequest = { type: 'rpc:applyLocalMutations:req' rpcId: string envelopeId: string mutations: Array<PersistedMutationEnvelope> } type ApplyLocalMutationsResponse = { type: 'rpc:applyLocalMutations:res' rpcId: string ok: true term: number seq: number latestRowVersion: number acceptedMutationIds: Array<string> } | { type: 'rpc:applyLocalMutations:res' rpcId: string ok: false code: 'NOT_LEADER' | 'VALIDATION_ERROR' | 'CONFLICT' | 'TIMEOUT' error: string } type PullSinceRequest = { type: 'rpc:pullSince:req' rpcId: string fromRowVersion: number } type PullSinceResponse = ({ type: 'rpc:pullSince:res' rpcId: string ok: true latestTerm: number latestSeq: number latestRowVersion: number requiresFullReload: true } | { type: 'rpc:pullSince:res' rpcId: string ok: true latestTerm: number latestSeq: number latestRowVersion: number requiresFullReload: false changedKeys: Array<string | number> deletedKeys: Array<string | number> } | { type: 'rpc:pullSince:res' rpcId: string ok: false error: string }) type CollectionReset = { type: 'collection:reset' schemaVersion: number resetEpoch: number }Idempotency rules:
tx:committedidempotency key:(collectionId, term, seq)- local mutation idempotency key:
envelopeId - mutation acknowledgment/correlation key:
mutationId(per mutation inside an envelope) - RPC response correlation key:
rpcId applyLocalMutationsis at-least-once delivery; leader must dedupe byenvelopeId- catch-up cursor key:
latestRowVersion(monotonic per collection) - followers persist
lastSeenRowVersionfrom appliedtx:committedmessages and successfulpullSinceresponses
Recommended browser defaults:
- heartbeat interval: 2000ms
- leader timeout: 6000ms
- RPC timeout: 5000ms
- local mutation retry backoff: 100ms → 2000ms capped exponential
- all timing knobs should be configurable per collection (advanced option)
Key Mechanics#
A) Writer Ownership#
- logical single writer per collection/table at a time
- different tabs can lead different collections simultaneously
- followers do not write that collection directly in browser mode
- follower writes are routed to current leader for serialization
SQLite write-lock note:
- SQLite still permits one write transaction at a time per database file.
- collection leaders therefore coordinate through
tsdb:writer:<dbName>before write transactions. - this keeps per-collection leadership for ownership, while serializing physical DB writes to avoid
SQLITE_BUSYthrash.
A.1) Commit + Broadcast Ordering#
Leader commit pipeline for a collection change:
- acquire DB writer lock (
tsdb:writer:<dbName>) - begin SQLite transaction
- increment collection
latest_row_versionand stamp touched rows with that version - apply row and index changes
- for deletes, insert/update tombstone records with same
row_version - insert idempotency marker in
applied_tx(collection_id, term, seq, applied_at) - read updated
latest_row_versionfor broadcast - commit SQLite transaction
- release DB writer lock
- broadcast
tx:committed(term, seq, latestRowVersion, ...)
Delete tracking note:
- tombstones are the delete source for
pullSincekey-level catch-up. - tombstones are stateful per key (latest delete only), not append-only history.
Recovery rule:
- if commit succeeds but broadcast is missed, followers detect stale
latestSeqvia heartbeat and callpullSince.
A.2) Subset Invalidation Contract#
Followers maintain an in-memory registry of active loaded subsets per collection.
Default:
TARGETED_INVALIDATION_KEY_LIMIT = 128
On
tx:committed:- if
requiresFullReloadis true:
- mark all active subsets for that collection dirty
- schedule debounced reload from local SQLite
2. else if
changedKeys/deletedKeyspresent and combined count <=TARGETED_INVALIDATION_KEY_LIMIT: - refresh only subsets that may contain those keys 3. else:
- mark all active subsets for that collection dirty
- schedule debounced reload from local SQLite
This removes ambiguity around follower refresh behavior while keeping correctness first.
B)
loadSubsetFlow by Inferred Behavior#When
syncIs Present#- query local SQLite immediately
- apply local rows
- request leader
ensureRemoteSubset(...)(online path) - leader syncs/writes/broadcasts commits
- tabs refresh from SQLite on broadcast
When
syncIs Absent#- query local SQLite immediately
- apply local rows
- no remote ensure call
- tab refresh remains local/broadcast-driven only
C) Hydrate Barrier (Both Modes)#
Problem: updates can arrive during local hydrate.
Wrapper state per collection:
isHydrating: booleanqueuedTx: PersistedTx[]applyMutexserializing write/apply
Scope:
- hydrate barrier is collection-scoped (not per-subset) because transactions can affect any active subset in that collection.
Algorithm:
loadSubsetsetsisHydrating = true- query cached rows from SQLite
- apply local rows via
write({ type: 'update', ... }) - set
isHydrating = false - flush queued tx in order
D) Duplicate-Key Safety (Sync-Present Path)#
To avoid
DuplicateKeySyncErrorwhen cache overlaps remote snapshot:- local hydrate uses
updateonly (neverinsert) - remote
insertpayloads are normalized toupdatebefore DBwrite
E) Sync-Absent Mutation Persistence#
When
syncis absent, mutation changes persist automatically, aligned withlocalStorageCollectionOptionsbehavior.PersistedMutationEnvelopeshape:type PersistedMutationEnvelope = | { mutationId: string type: 'insert' key: string | number value: Record<string, unknown> } | { mutationId: string type: 'update' key: string | number value: Record<string, unknown> } | { mutationId: string type: 'delete' key: string | number value: Record<string, unknown> }- wrap
onInsert,onUpdate,onDeleteto persist SQLite changes automatically - confirm optimistic operations through sync-confirm path after persistence
- for manual transactions, expose and use
utils.acceptMutations(transaction) - in browser multi-tab, non-leader tabs send local mutations to leader via
applyLocalMutations - leader must reply with
applyLocalMutations:resso follower can confirm or rollback optimistic entries
F) Offline/Online Behavior#
- when
syncis present:- offline
loadSubsetresolves locally - queued
ensureRemoteSubsetreplays when online
- offline
- when
syncis absent:- unaffected by network state
G) Seq Gap Recovery#
On missing
(term, seq):- use follower-tracked
lastSeenRowVersion(from last applied commit or pull response) and requestpullSince(lastSeenRowVersion)from current leader - if pull succeeds and
requiresFullReloadis true, mark collection subsets dirty - if pull succeeds with
changedKeys/deletedKeys, run targeted subset invalidation - reload affected subsets from local SQLite (or all active subsets when required)
- if pull fails, mark view stale and truncate/reload affected in-memory view
- re-request loaded subsets
- re-run remote ensure only when
syncis present
pullSinceimplementation rule:changedKeysare derived fromc_<tableName>rows whererow_version > fromRowVersiondeletedKeysare derived from tombstonest_<tableName>whererow_version > fromRowVersion- this computes a delta to latest state (not a full linear event history)
- if either result set exceeds invalidation limits, set
requiresFullReload: true
SQLite Storage + Index Plan#
Schema (Per Collection)#
Single table per collection:
keystored as canonical encoded text key (s:<value>orn:<value>) to preserve1vs'1'distinctionkeyTEXT PRIMARY KEYvalueJSON string inTEXTrow_versionINTEGER NOT NULL (monotonic change version stamped by leader; per-transaction watermark shared by all rows touched in one committed tx)- tombstone table per collection tracks latest delete state per key with row versions (
t_<tableName>) - tombstone
deleted_atstores deletion timestamp for diagnostics/observability; catch-up logic usesrow_version
Key encoding helpers (required):
function encodeStorageKey(key: string | number): string { if (typeof key === 'number') { if (!Number.isFinite(key)) { throw new Error('Invalid numeric key: key must be finite') } if (Object.is(key, -0)) { return 'n:-0' } return `n:${key}` } return `s:${key}` } function decodeStorageKey(encoded: string): string | number { if (encoded === 'n:-0') { return -0 } return encoded.startsWith('n:') ? Number(encoded.slice(2)) : encoded.slice(2) }Metadata tables:
persisted_index_registry(collection_id, signature, sql, state, last_built_at, last_used_at)applied_tx(collection_id, term, seq, applied_at)collection_version(collection_id, latest_row_version)for catch-up cursorleader_term(collection_id, term, leader_id, updated_at)for durable term monotonicityschema_version(collection_id, version)for clear-on-version-change behaviorcollection_reset_epoch(collection_id, epoch)for coordinated clear/reload signalingcollection_registry(collection_id, table_name)for safe identifier mapping
Identifier safety requirement:
- never interpolate raw
collectionIdinto SQL identifiers - map
collectionIdto safe physical table names using hashed names (for examplec_<base32(hash(collectionId))>) - store mapping in
collection_registry
Reference DDL:
CREATE TABLE IF NOT EXISTS c_<tableName> ( key TEXT PRIMARY KEY NOT NULL, value TEXT NOT NULL, row_version INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS t_<tableName> ( key TEXT PRIMARY KEY NOT NULL, row_version INTEGER NOT NULL, deleted_at INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS collection_registry ( collection_id TEXT PRIMARY KEY NOT NULL, table_name TEXT UNIQUE NOT NULL ); CREATE TABLE IF NOT EXISTS persisted_index_registry ( collection_id TEXT NOT NULL, signature TEXT NOT NULL, sql TEXT NOT NULL, state TEXT NOT NULL, last_built_at INTEGER, last_used_at INTEGER, PRIMARY KEY (collection_id, signature) ); CREATE TABLE IF NOT EXISTS applied_tx ( collection_id TEXT NOT NULL, term INTEGER NOT NULL, seq INTEGER NOT NULL, applied_at INTEGER NOT NULL, PRIMARY KEY (collection_id, term, seq) ); CREATE TABLE IF NOT EXISTS collection_version ( collection_id TEXT PRIMARY KEY NOT NULL, latest_row_version INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS leader_term ( collection_id TEXT PRIMARY KEY NOT NULL, term INTEGER NOT NULL, leader_id TEXT NOT NULL, updated_at INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS schema_version ( collection_id TEXT PRIMARY KEY NOT NULL, version INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS collection_reset_epoch ( collection_id TEXT PRIMARY KEY NOT NULL, epoch INTEGER NOT NULL );Persisted Index Signatures#
Main-thread
indexIdis not stable across tabs. Use stable signature:signature = hash(stableStringify({ expression, compareOptions, direction, nulls, stringSort, locale, ... }))
Expression Indexes#
Indexes are created on demand from mirrored index specs, for example:
CREATE INDEX IF NOT EXISTS idx_<tableHash>_<sigHash> ON c_<tableName>(json_extract(value,'$.path'))- compound indexes use multiple expressions
ensureIndex(...)compiles index IR/spec to canonical SQL expression text for reliable planner usage.Reference query templates:
-- Increment and read collection row version (inside txn) INSERT INTO collection_version(collection_id, latest_row_version) VALUES (?, 1) ON CONFLICT(collection_id) DO UPDATE SET latest_row_version = latest_row_version + 1; SELECT latest_row_version FROM collection_version WHERE collection_id = ?; -- Upsert row INSERT INTO c_<tableName>(key, value, row_version) VALUES (?, ?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value, row_version = excluded.row_version; -- Clear tombstone on re-insert/update DELETE FROM t_<tableName> WHERE key = ?; -- Delete row DELETE FROM c_<tableName> WHERE key = ?; -- Upsert tombstone for delete tracking INSERT INTO t_<tableName>(key, row_version, deleted_at) VALUES (?, ?, ?) ON CONFLICT(key) DO UPDATE SET row_version = excluded.row_version, deleted_at = excluded.deleted_at; -- Mark tx applied INSERT OR IGNORE INTO applied_tx(collection_id, term, seq, applied_at) VALUES (?, ?, ?, ?);Metadata Retention / Cleanup#
To prevent unbounded metadata growth:
applied_tx: keep sliding window per collection by seq/time.- tombstones (
t_<tableName>) are per-key latest-delete state and are not version-pruned. - tombstones are removed when the key is re-inserted/updated (same transaction as row upsert).
Defaults:
APPLIED_TX_SEQ_RETENTION = 10000APPLIED_TX_MAX_AGE_MS = 7 * 24 * 60 * 60 * 1000
Partial Updates and Index Maintenance#
Updates may be partial (
rowUpdateMode: 'partial'default).Adapters must:
- read current row
- merge partial update before persist
- compute index old/new values from pre-merge and post-merge rows
If
rowUpdateMode: 'full'is configured, adapters can skip read/merge and write replacement rows.Schema Version Policy (No Migrations)#
This plan does not implement structural schema migrations.
Collection options include
persistence.schemaVersion: number.Behavior on version mismatch:
- sync-present path:
- default action: coordinated clear persisted state for that collection (rows + indexes + metadata), then rehydrate from remote sync
- sync-absent path:
- default action: throw
PersistenceSchemaVersionMismatchError - optional opt-in: allow clear and restart with empty local state
- default action: throw
Coordinated clear sequence (sync-present path):
- acquire
tsdb:writer:<dbName>
- note: this serializes writes across the DB file, so schema reset briefly blocks writes for all collections
2. begin SQLite transaction
3. clear collection rows/tombstones/index metadata
4. reset collection cursor in
collection_version(delete row or setlatest_row_version = 0) 5. updateschema_version6. incrementreset_epoch7. commit transaction 8. broadcastcollection:reset(schemaVersion, resetEpoch)
Follower behavior on
collection:reset:- reset tracked
lastSeenRowVersionfor that collection to0 - drop in-memory rows for that collection
- clear active subset cache
- re-request loaded subsets
Guidance for sync-absent collections:
- prefer additive/backward-compatible schema changes with value-level fallbacks
- because values are JSON payloads, additive evolution is expected to be the common safe path
loadSubsetQuery Planning#v1 pushdown support:
eq,in,gt/gte/lt/lteANDconjunctions- index-aligned
orderBy
Unsupported predicate fragments load a superset; query engine filters remainder.
Adapter Interfaces#
PersistedTx(used byapplyCommittedTx) shape:type PersistedTx<T extends object, TKey extends string | number> = { txId: string term: number seq: number rowVersion: number mutations: Array< | { type: 'insert'; key: TKey; value: T } | { type: 'update'; key: TKey; value: T } | { type: 'delete'; key: TKey; value: T } > }Persistence Adapter#
export interface PersistenceAdapter<T extends object, TKey extends string | number> { // Read path (all tabs / all runtimes) loadSubset( collectionId: string, options: LoadSubsetOptions, ctx?: { requiredIndexSignatures?: string[] }, ): Promise<Array<{ key: TKey; value: T }>> // Write path (leader for this collection, or DO instance) applyCommittedTx( collectionId: string, tx: PersistedTx<T, TKey>, ): Promise<void> // Index management ensureIndex( collectionId: string, signature: string, spec: PersistedIndexSpec, ): Promise<void> // Optional: some adapters handle index cleanup lazily or via collection reset flows. markIndexRemoved?( collectionId: string, signature: string, ): Promise<void> }PersistedIndexSpecmust be serializable and derived from index lifecycle events.SQLite Driver Interface#
export interface SQLiteDriver { exec(sql: string): Promise<void> query<T>(sql: string, params?: readonly unknown[]): Promise<readonly T[]> run(sql: string, params?: readonly unknown[]): Promise<void> transaction<T>(fn: () => Promise<T>): Promise<T> }Driver adaptation note:
- sync drivers (for example
better-sqlite3) are adapted via thinPromise.resolve(...)wrappers. - this keeps one core async adapter path across runtimes; sync overhead is accepted for API consistency in v1.
Package Plan#
@tanstack/db-sqlite-persisted-collection-core@tanstack/db-browser-wa-sqlite-persisted-collection@tanstack/db-node-sqlite-persisted-collection@tanstack/db-react-native-sqlite-persisted-collection(RN + Expo)@tanstack/db-electron-sqlite-persisted-collection@tanstack/db-cloudflare-do-sqlite-persisted-collection
SQLite core package contents (combined):
persistedCollectionOptions(...)with inferred behavior based on presence ofsync- stable signature/hash utilities
- coordinator protocol types
- sync-absent mutation persistence helpers (
acceptMutationsflow) - shared
SQLiteCoreAdapter(driver) - SQL expression compiler for index/query pushdown
- index registry management
- in-memory adapter + in-memory coordinator for unit tests
Future packaging note:
- if a non-SQLite backend is introduced later, split backend-agnostic surface out of this package at that time.
Cloudflare DO package contents:
- adapter binding to DO SQLite-backed storage APIs (for code executing inside DO)
- DO-friendly wrapper that defaults to
SingleProcessCoordinatorand omits browser election paths - optional helper for mapping
collectionIdto table naming and schema-version handling
Electron package contents:
- thin wrapper over node sqlite package semantics
- IPC transport between renderer calls and main-process persistence execution
- does not duplicate node adapter/core logic; reuses node package implementation behind the IPC boundary
Implementation Phases#
Phase 0: API + Runtime Feasibility#
- Finalize
persistedCollectionOptionsinference API (syncpresent vs absent). - Confirm Cloudflare DO adapter surface and runtime constraints.
- Finalize coordinator protocol (
rpc,tx,leader,(term, seq)), with browser multi-tab parts phase-gated. - Finalize key encoding and identifier hashing rules.
- Finalize package boundaries around SQLite-only core.
- Define staged rollout gates (single-process first, browser multi-tab last).
Deliverable: finalized API, package plan, capability matrix, and protocol spec.
Phase 1: Add Index Lifecycle Events to
@tanstack/db#- Extend collection events with:
index:addedindex:removed2. UpdateCollectionIndexesManagerto emit stable index metadata. 3. Add index removal API (removeIndex(...)) and emitindex:removed.
Deliverable: index lifecycle observable and stable across tabs.
Phase 2: Core Persisted Wrapper (Inferred Behavior)#
- Implement
sync-present wrapper oversync.sync(params). - Implement sync-absent behavior without required wrapped sync.
- Add hydrate barrier + queued tx behavior.
- Normalize remote inserts to updates (when
syncis present). - Implement automatic mutation persistence wrappers (when
syncis absent). - Add
utils.acceptMutations(transaction)support for manual transactions. - Wire coordinator RPC (
ensureRemoteSubset,ensurePersistedIndex,applyLocalMutations). - Implement seq-gap recovery path.
- Implement inference edge-case validation (
InvalidSyncConfigError).
Deliverable: core wrapper passes in-memory tests for both inferred paths.
Phase 3: SQLite Core Adapter#
- Implement
applyCommittedTx,ensureIndex,loadSubsetSQL pushdown. - Implement partial update merge semantics.
- Implement
leader_term,schema_version, and identifier registry tables. - Implement schema-version mismatch behavior (clear vs error by path).
- Implement applied_tx pruning jobs.
- Add adapter contract tests in node sqlite runtime.
Deliverable: SQLite adapter contract passing in node.
Phase 4: Node + Electron#
- Implement node wrapper over
better-sqlite3. - Implement electron main-process ownership + renderer IPC over
better-sqlite3. - Run shared contract/integration suites.
Deliverable: node/electron parity with core semantics.
Phase 5: React Native + Expo#
- Implement shared mobile package over
op-sqlite. - Provide RN/Expo-specific entrypoints only where host bootstrapping differs.
- Validate mobile lifecycle and transaction semantics on both RN and Expo.
Deliverable: unified RN/Expo mobile package passes contract tests.
Phase 6: Cloudflare Durable Objects#
- Implement DO SQLite adapter package.
- Provide helper for per-object schema initialization and schema-version checks.
- Support both inferred wrapper paths inside DO runtime (
syncpresent or absent), with in-process execution only. - Add integration tests using Workers/DO test harness.
Deliverable: DB and persistence running in-process in Durable Objects with SQLite-backed storage.
Phase 7: Browser Single-Tab (
wa-sqlite, No Election)#- Implement OPFS driver (
OPFSCoopSyncVFS). - Implement browser adapter path with
SingleProcessCoordinatorsemantics for single-tab usage. - Validate offline-first read/write path without BroadcastChannel/Web Locks dependencies.
- Add browser single-tab integration tests.
Deliverable: stable browser persistence for single-tab sessions.
Phase 8: Browser Multi-Tab Coordinator (Final Phase)#
- Implement Web Locks + Visibility + BroadcastChannel coordinator.
- Implement per-collection leader/follower behavior for both inferred paths.
- Implement follower local mutation RPC to leader with ack/rollback semantics.
- Implement DB write serialization lock (
tsdb:writer:<dbName>) and busy retry policy. - Add Playwright multi-tab tests.
Deliverable: stable browser local-first multi-tab behavior when
syncis present or absent.Testing Strategy#
Unit Tests (Core Wrapper)#
- Index lifecycle:
createIndexemitsindex:addedwith stable signatureremoveIndexemitsindex:removed2. Local hydrate safety:- hydrate uses
updateonly - remote inserts normalized to update 3. Hydrate barrier:
- tx during hydrate is queued then flushed in order 4. Sync-present offline/online queue:
- offline local resolve
- queued remote ensures replay online 5. Sync-absent mutation persistence:
- insert/update/delete auto-persist
- manual transaction
acceptMutationspersists and confirms 6. Seq-gap recovery: - missing seq triggers
pullSince; fallback to stale/reload/re-ensure 7. Inference validation: - invalid
syncshape throwsInvalidSyncConfigError8. Key encoding: 1and'1'persist distinctly and round-trip correctly 9. Local mutation acking:applyLocalMutations:res.acceptedMutationIdsmaps to submittedmutationIds
Adapter Contract Tests#
Run same suite against:
- in-memory adapter
- browser
wa-sqliteadapter - node sqlite adapter
- electron wrapper (
better-sqlite3) and unified mobile wrapper (op-sqlite) where harness supports - cloudflare durable object sqlite adapter
Covers:
ensureIndex+loadSubsetindex-path usageapplyCommittedTxrow/index correctness- idempotency and replay handling on
(term, seq) - monotonic
row_versionbehavior andpullSincecursor correctness pullSincediscriminated response shape correctness (requiresFullReload=truereturns no key lists)- tombstone-based delete catch-up correctness
- per-key tombstone state semantics (latest delete only) correctness
- applied_tx pruning does not break row-version catch-up correctness
- schema reset clears
collection_versioncursor and follower resets trackedlastSeenRowVersionto0 - sync-absent auto-persist semantics
- schema-version mismatch behavior (clear vs error by path)
- identifier safety mapping (unsafe collectionId still produces safe physical table names)
Browser Single-Tab Integration Tests (Phase 7)#
- OPFS-backed init and reopen behavior.
- Local-first
loadSubsetand mutation persistence correctness. - Sync-present offline local path and reconnect replay without leader election.
- No dependency on BroadcastChannel/Web Locks for correctness in single-tab mode.
Browser Multi-Tab Integration Tests (Playwright, Phase 8)#
- Two tabs with different collection leaders:
- tab A leads collection X
- tab B leads collection Y
2. Local reads do not round-trip through leader.
3. Sync-absent follower mutation is serialized via leader and persisted.
4. Auto-index creates persisted index and speeds repeated lookups.
5. Leader handoff on visibility change / tab close.
6. Sync-present offline local-first and reconnect catch-up.
7. Cross-collection leaders contend for DB writes without correctness loss (
tsdb:writerlock test). 8. Commit-broadcast gap recovers via heartbeatlatestSeq+pullSince.
Cloudflare Durable Objects Integration Tests#
- Schema init + schema-version mismatch behavior per DO instance.
loadSubset+ index pushdown correctness.- Sync-absent mutation persistence correctness in DO runtime.
- Restart/rehydration behavior with persisted SQLite state.
- No browser coordinator path in DO (
SingleProcessCoordinatoronly).
Corruption Recovery Tests#
- Corrupted sqlite open path triggers integrity failure handling.
- Sync-present path clears persistence and rehydrates from remote.
- Sync-absent path raises
PersistenceCorruptionErrorunless explicit reset is requested.
Failure Modes and Handling#
- OPFS unavailable in browser:
- when
syncis absent: throwPersistenceUnavailableErrorat initialization - when
syncis present: default to disabling persistence for session and run remote sync path only - expose capability/error to application so users can decide whether to hard-fail UI
- Invalid inferred sync config:
- if
synckey exists but is not a validSyncConfig, throwInvalidSyncConfigError
- No current leader for a collection in browser:
- local
loadSubsetstill reads SQLite - queue/timeout remote ensure or local-mutation RPC until election completes
- Leader crash or tab close:
- Web Lock releases
- follower acquires leadership and resumes responsibilities
- Broadcast gap:
- follower triggers collection recovery
- attempt
pullSincecatch-up first - fallback to reload local subset and re-ensure when
syncis present
- Durable Object instance restart:
- in-memory state is rebuilt from persistent SQLite storage
- schema-version checks and clear/error policy run on initialization path
- Coordinated schema reset while tabs are active:
- leader broadcasts
collection:reset - followers drop in-memory cache for that collection and reload subsets
- SQLite corruption / integrity failure:
- detect on open/init (initial query failure or optional integrity check path)
- sync-present: clear persisted state and rehydrate from remote
- sync-absent: throw
PersistenceCorruptionErrorand require explicit user reset - expose
resetPersistence({ collectionId })utility for app-level recovery
Risks and Mitigations#
Risk: browser differences in OPFS/Web Locks/visibility behavior. Mitigation: capability matrix + conservative fallback behavior.
Risk: cross-collection write contention causes
SQLITE_BUSY. Mitigation: serialize physical writes viatsdb:writer:<dbName>+ bounded retry/backoff.Risk: WASM startup overhead. Mitigation: lazy init + connection reuse per tab.
Risk: SQL pushdown mismatch vs query-engine semantics. Mitigation: equivalence tests + fallback filtering for unsupported fragments.
Risk: driver divergence across runtimes. Mitigation: strict adapter contract suite and minimal driver interface.
Risk: sync-absent follower mutation queuing during leader churn. Mitigation: durable RPC retry/backoff and idempotent mutation envelopes.
Implementation Readiness Checklist#
- API:
- overload signatures compile and infer correctly for
syncpresent/absent - runtime branch matches compile-time discrimination (
options.sync != null) 2. Core semantics: - hydrate barrier + queued tx ordering implemented
- insert-to-update normalization implemented for sync-present path
- sync-absent auto-persist wrappers implemented 3. Coordinator:
- lock acquisition, heartbeat, timeout, and stepdown logic implemented
- protocol envelope and RPC correlation/idempotency implemented
- heartbeat carries
latestSeqand followers performpullSincecatch-up 4. SQLite adapter: - DDL initialization and schema-version checks implemented
- key encoding/decoding preserves string vs number identity
- identifier hashing/mapping prevents unsafe SQL identifiers
- pushdown planner + fallback filtering implemented
- applied tx idempotency table enforced
- tombstone per-key delete-state tracking implemented
- durable
leader_termmonotonicity and schema-version policy implemented - corruption detection and reset utility implemented 5. Runtime adapters:
- browser OPFS adapter passes single-tab integration tests (Phase 7)
- browser multi-tab coordinator/election tests pass (Phase 8)
- node/electron/mobile (rn+expo) adapters passing contract suite
- cloudflare DO adapter passing integration suite 6. Test coverage:
- unit + contract + browser integration + DO integration green in CI
Open Decisions#
- Electron renderer read policy: direct read vs strict main-process proxy.
- Whether
ensureRemoteSubsetis always background or optionally awaited.
Blocking-before-implementation:
- none (runtime driver choices and package shape are fixed in this plan: Node/Electron
better-sqlite3,@tanstack/db-react-native-sqlite-persisted-collectionfor RN/Expo viaop-sqlite)
Blocking-before-browser phases:
- Phase 7: verify OPFS +
FileSystemSyncAccessHandlein target evergreen browsers. - Phase 8: verify Web Locks in the same target browsers.
Non-blocking (can be phased after initial implementation):
- electron renderer read policy refinements
- awaited vs background
ensureRemoteSubsetbehavior toggle
Notes and Implications#
- First-time index build has unavoidable cost; subsequent indexed reads are fast.
- Local performance depends on index coverage; use
autoIndexor explicitcreateIndex(...)on hot paths. - Reads never round-trip through leader; leader handles write serialization and sync responsibilities.
- Sync-absent usage provides a persistence-first option without requiring remote sync wiring.
loadSubsetcurrently returns materialized arrays; cursor/streaming read API can be explored after v1.
- if wrapped options include
If we're already using persistQueryClient + offline-transactions for offline support is there a net benefit using a persistence package as well? I'm guessing it would replace the need to use persistQueryClient at minimum.
Relationship to offline transactions We already have an early offline mutation persister in @tanstack/offline-transactions: main/packages/offline-transactions
This is intentionally a separate problem to “persist synced data”. That said, once we do have persisted collections, queries will need to compose:
state loaded from the persisted store plus any pending offline mutations from the offline transaction log So persistence can’t assume “disk == canonical local view”. The in-memory view is: persisted base + pending local delta, and that delta may exist even if the main persistence layer is not enabled for a given collection. This distinction is important to keep in mind in the design.
I'm particularly curious about this. I'm currently trying to implement offline using persistQueryClient and offline-transactions but hit the problem of the non-leader tabs not replaying the transaction outbox and operating in online mode only.
Is there a particular reason why the current strategy of offline-transactions is not replay? For the read path, I don't really understand why that would be a problem. For the write path, I assume follower tabs would need to submit transactions to be processed by the leader somehow.
Shipped
KyleAMathews(GitHub profile, opens in a new tab) closed this
Gerat! Is there more information on that?
Sign in to join this conversation.
Excited for this feature. It might be worth looking into Dexie.js for a layer in front of IndexedDB, they likely have handled a lot of edge cases already for performance