ADR-0023 — Local SQLite sync database as a rebuildable cache

• Status: Accepted
• Date: 2026-06-11
• Related: sync/03 local database, ADR-0022

Context

The sync engine needs durable local state — the three trees (ADR-0022), the operation queue, the cursor, and metadata caches — that survives restarts and crashes, supports the planner’s queries, and enables offline work. It must be crash-consistent (a half-applied state must never corrupt data) and must not itself become a single point of data loss.

Decision

Use SQLite (WAL mode, one DB per sync root) as the local store:

• One node row per node-ID holding the synced/remote/local snapshots side-by-side → the planner reconciles with a single indexed scan.
• Durable queue_op, chunk_cache, conflict, and meta (cursor, device id, watermarks) tables.
• Crash-consistency by transaction: advancing S, enqueuing ops, and updating the cursor commit atomically; downloads use apply-then-record (atomic rename before DB marks synced).
• Metadata-caching fast-path: skip hashing when (inode, mtime, size) is unchanged; periodic deep re-hash catches forged mtimes/bitrot.
• The DB is a rebuildable cache, not a source of truth: if lost/corrupt, rebuild from a local scan + a server list with Synced = ∅; reconciliation is then conservative (errs toward conflicted copies, never overwrites) — no user data is lost.

Consequences

Positive

• ACID transactions make crash-consistent state transitions straightforward.
• Relational queries fit the planner (disagreement scan, dependency joins).
• Rebuildability makes the DB a performance component, not a durability risk.
• Fast-path caching makes million-file rescans stat-bound, not hash-bound.

Negative / costs

• SQLite single-writer → the control thread must keep transactions short/batched (fits the single-threaded control model, ADR-0022).
• A full rebuild after corruption is slow (rescan + relist).

Alternatives considered

• Embedded KV (BoltDB/LevelDB/Badger): fast simple writes, but weaker for the relational planner queries and cross-table transactions. Considered for the chunk cache only.
• Flat files / JSON: no transactions or crash-consistency. Rejected.
• No local DB (stateless, recompute each run): loses offline queue and in-flight transfer progress. Rejected.

Scaling

Metadata-only size (grows with file count, not bytes); indexed on parent, state, and the disagreement predicate; chunk cache is LRU-capped; one DB per root bounds size and isolates corruption.