Local-First Sync, Done Right: Inside the SQLite Sync Stack

TL;DR: Four open-source Python repositories form a complete local-first synchronization stack: sqlite-sync-core captures SQLite changes as structured operations, packages them into self-contained bundles, and applies them deterministically across disconnected devices; sqlite-sync-snapshot adds content-addressed snapshot storage; sync-coordination orchestrates nodes with vector clocks and CRDT-style merge; and merkle-dag-store provides the content-addressed block layer underneath. All public under Shivay00001.

The problem it solves

Offline-first applications live or die on sync. Two devices change the same SQLite database while disconnected; when they reconnect, the changes must merge without loss, without duplication, and without corruption — even if a bundle arrives twice, even if the schema changed in between, even if the network drops mid-transfer.

Most teams solve this with a cloud database and hope. The local-first movement — the idea that the device's SQLite file is the source of truth and the network is just a transport — needs real primitives: change capture, operation logs, causality tracking, conflict resolution, content addressing, and deterministic apply. This stack implements those primitives as four composable Python packages.

Layer 1: sqlite-sync-core — the sync engine

The core repo (v0.7.0, ~50 source modules plus tests, examples, and a CLI) is the most complete of the four. Its public API surface, from src/sqlite_sync/__init__.py, tells the story: SyncEngine, a typed error hierarchy (SyncError, SchemaError, BundleError, ConflictError, InvariantViolationError), plus enterprise extensions — SyncNode orchestration, UDP peer discovery (UDPDiscovery, PeerManager), and pluggable conflict resolution (LastWriteWinsResolver, FieldMergeResolver).

How it works

1. Change capture. SQLite triggers (db/triggers.py, ~11KB) record every INSERT/UPDATE/DELETE as a structured operation in an operation log — the database watches itself.
2. Bundles. bundle/format.py + generate.py package operations into self-contained .bundle.db files — actual SQLite database files containing a bundle_operations table and a single-row bundle_metadata table. Metadata is a frozen dataclass: 16-byte UUIDv7 bundle_id, source device ID, microsecond timestamp, schema version, op count, and a 32-byte SHA-256 content hash, all validated on construction.
3. Causality. The operation log (log/operations.py, log/vector_clock.py) tracks vector clocks per device, and hlc.py adds hybrid logical clocks — so the system knows what happened before what, across devices with drifting clocks.
4. Deterministic apply. import_apply/ applies bundles idempotently (dedup.py), in dependency order (ordering.py), with conflict detection (conflict.py) — applying the same bundle twice is a no-op, and two devices applying the same bundles in any order converge.
5. Resolution. resolution/ offers last-write-wins with vector-clock tie-breaking and field-level merge strategies.
6. Transport. transport/ ships HTTP and WebSocket transports plus a server; network/ adds UDP peer discovery for LAN sync; ext/server/ is a full sync server with authentication (~24KB of auth code, ~30KB HTTP server).
7. Safety. crash_safety.py, invariants.py, log_compaction.py, schema_evolution.py (14KB — syncing across schema versions), security.py (24KB), metrics.py (21KB).

There's also a ~20KB CLI (cli/main.py), five worked examples (basic_usage.py, http_sync_demo.py, network_sync.py, real_world_sync.py, desktop_demo.py), and a test suite covering conflicts, determinism, idempotency, invariants, and transports — plus a verify_readme.py that keeps the docs honest against the code.

Layer 2: sqlite-sync-snapshot — content-addressed snapshots

Built to strengthen the core's ecosystem, this package is a deterministic, local-first integrity layer — explicitly "not IPFS, not a generic Merkle database." Every object is stored by its BLAKE3 hash and is immutable once written. Four object types: Blob (raw bytes), Bundle (a sync-core sync bundle), Snapshot (a deterministic state reference with parent linking), and Tree (hierarchical grouping).

The storage layout shards objects by hash prefix (objects/ab/ab12ef34…), with named snapshot refs and a refs directory — the git-object-store pattern, adapted for sync state. It adds recursive integrity verification with tamper detection, safe mark-and-sweep garbage collection with reachability analysis, and a SyncAdapter (integration/sync_adapter.py, 10KB) that converts sync-core bundles into snapshots. The test suite covers GC safety, hash determinism, snapshot verification, and tamper detection.

Layer 3: sync-coordination — the orchestration engine

A single 20KB module (sync_coordination.py) plus tests: a SyncCoordinator per node that orchestrates synchronization using vector clocks, a persistent SQLite-backed operation log, and event sourcing. The SyncOperation dataclass carries everything a merge needs — operation type (create/update/delete/merge), entity ID, version, payload, vector clock, timestamp, node ID, dependencies, and an optional parent CID linking into the Merkle layer. Conflicts resolve with last-write-wins and vector-clock tie-breaking; the coordinator is thread-safe behind reentrant locks and supports full state export for backup and transfer.

from sync_coordination import SyncCoordinator, OperationType

node1 = SyncCoordinator("node-1", "./sync_store_1")
node2 = SyncCoordinator("node-2", "./sync_store_2")

op = node1.create_operation(
    OperationType.CREATE, "user:1",
    {"name": "Alice", "email": "[email protected]"}
)
node1.apply_operation(op)

result = node2.sync_with_remote([op])

Layer 4: merkle-dag-store — the content-addressed foundation

A single 17KB module (merkle_dag_store.py): an embeddable, deterministic block-addressed object store. Blocks get CIDs (v0 as Qm…-style, v1 multibase) computed over SHA-256, SHA-512, or BLAKE2b digests; blocks link into parent-child DAGs; snapshots tag DAG states for time-travel; a SQLite index tracks metadata and access stats; storage is sharded on the filesystem for scale; unreachable blocks are reclaimed by garbage collection; and recursive verification walks the DAG checking every hash. It's the primitive the upper layers build on: content addressing means identical data is stored once and tampering is detectable by construction.

How the layers fit together

┌─────────────────────────────────────────────┐
│  your app's SQLite database                 │
│  triggers capture every write               │
└──────────────┬──────────────────────────────┘
               ▼
┌─────────────────────────────────────────────┐
│  sqlite-sync-core                           │
│  op log + vector clocks → .bundle.db files  │
│  deterministic, idempotent apply            │
└──────────────┬──────────────────────────────┘
               ▼
┌─────────────────────────────────────────────┐
│  sync-coordination                          │
│  node orchestration, LWW merge, event log   │
└──────────────┬──────────────────────────────┘
               ▼
┌──────────────┴──────────────┐
│ sqlite-sync-snapshot        │  merkle-dag-store
│ BLAKE3 objects, snapshots,  │  CIDs, DAG links,
│ tamper detection, GC        │  verification, GC
└─────────────────────────────┘

Use the core alone for device-to-device SQLite sync; add the snapshot layer when you need auditable, tamper-evident state history; add coordination when multiple nodes need orchestrated merge; use the DAG store anywhere you need content-addressed blocks.

How to run it

Each repo is a standard Python package (pyproject.toml, Dockerfile, CI):

git clone https://github.com/Shivay00001/sqlite-sync-core.git
cd sqlite-sync-core
pip install -e .
python examples/basic_usage.py     # change capture → bundle → apply
python examples/http_sync_demo.py  # sync over HTTP transport

The coordination and DAG layers are single-module and dependency-light — drop sync_coordination.py or merkle_dag_store.py into a project and they work with nothing but the standard library plus SQLite.

Honest status and roadmap

Status: working libraries with real test suites. sqlite-sync-core is the most production-leaning of the four — versioned at 0.7.0, CI-gated, with determinism/idempotency/invariant tests and a verify_readme.py that holds the documentation accountable. The other three are focused, well-tested modules at an earlier stage of packaging.

One honest note: a couple of the smaller repos' READMEs show pip install <name> one-liners, but these packages aren't published to PyPI — install from source for now. The code is the deliverable, and it's all there.

The roadmap writes itself from the architecture: PyPI publication, cross-language clients (the bundle format is just SQLite — any language can read it), CRDT type expansion beyond LWW/field-merge, and the schema-evolution work extended to automated migrations.

The details that make it trustworthy

A sync engine earns trust in the edge cases, and the core repo spends its largest modules there:

• Crash safety (crash_safety.py, 6KB) — bundle generation and apply are designed to survive process death mid-write; partial bundles fail validation via the SHA-256 content hash rather than applying half an operation log.
• Schema evolution (schema_evolution.py, 14KB) — devices on different schema versions can still sync; the engine migrates operation payloads rather than rejecting them.
• Security (security.py, 24KB) — bundle signing and verification, so a malicious or corrupted bundle is rejected before apply, plus the authenticated HTTP server in ext/server/auth.py.
• Log compaction (log_compaction.py, 8KB) — the operation log doesn't grow forever; compacted checkpoints preserve causality while bounding storage.
• Metrics (metrics.py, 21KB) — sync latency, bundle sizes, conflict rates, and apply durations are instrumented, because a sync engine you can't observe is a sync engine you can't operate.
• Deterministic IDs (utils/uuid7.py) — UUIDv7 gives time-ordered, collision-safe identifiers without coordination, and utils/msgpack_codec.py keeps bundle payloads compact.

The CLI (cli/main.py, ~20KB) exposes the whole engine — capture, bundle, validate, apply, inspect — which makes the system debuggable from a terminal, and verify_readme.py is a small piece of process genius: it checks the README's claims against the code so documentation can't drift from implementation.

Peer discovery and transports

Sync has to work across whatever network exists. The core ships three paths: direct HTTP transport with a server mode, WebSocket transport for persistent connections, and UDP peer discovery (network/peer_discovery.py, 16KB) for LAN environments — two devices on the same network find each other with no configuration and no cloud. The bundle format doesn't care which transport carried it: a .bundle.db file copied on a USB stick applies identically, which is exactly the property a local-first system needs for sneakernet, air-gapped, and flaky-network scenarios.

Frequently asked questions

Why bundles as SQLite files instead of JSON?

A bundle is a database: queryable, transactional, checksummed as a unit, and applicable with the same engine that captured it. JSON would need a separate parser, validator, and applier — the .bundle.db format collapses all three into SQLite itself.

What happens if a bundle arrives twice?

Nothing bad: the apply path deduplicates by bundle ID and content hash, so re-application is a no-op. Idempotency is tested, not assumed.

How are conflicts resolved?

Configurably. The default is last-write-wins with vector-clock tie-breaking (causally correct, not wall-clock dependent); field-level merge is available when you want both sides' non-overlapping edits preserved.

Is this a database replacement?

No — it's a sync layer for SQLite. Your app keeps using SQLite exactly as before; triggers capture the changes, and the stack moves them between devices.

The bottom line

Local-first sync is an unforgiving problem — duplication, divergence, and silent corruption are the default outcomes of naive approaches. This stack's answer is principled: capture everything, hash everything, order causally, apply idempotently, verify recursively. Four repos, one coherent design, all open source: sqlite-sync-core · sqlite-sync-snapshot · sync-coordination · merkle-dag-store.