Database-to-Database Sync for Local-First Apps Lives or Dies on Conflict Rules
Take a field technician who edits a work order on a tablet in a basement with no signal. Upstairs, the office edits the same order from a desk. Both hit save at 10:42 by their own clocks, and one of those clocks is ninety seconds off. When the tablet finds a signal, the sync layer has to produce one order that both people can live with. That decision is the product. Offline-first apps are harder than they look, and a large share of the difficulty sits in this one step.
Moving the bytes is the easy half. HTTP works. A file on a USB stick works too. What decides whether a sync tool is any good is its set of merge rules, and whether anyone wrote them down. The version worth building is small: two embedded databases, a db_sync(a, b) call that works between any two handles, merge rules declared per field and documented, and transport left to you. It doesn’t need to be a distributed database. Make the model key-value or document shaped, and constrain the conflict semantics until you can prove convergence.
Everyone Has Built a Piece of It
CouchDB and PouchDB did database-to-database replication long ago. Each document carries a revision tree, a conflict picks a winner deterministically so every replica agrees, and the losing revisions stay around for the application to resolve later. That’s a clean design with one cost: somebody has to write the resolver. Couchbase Lite brings embedded storage and sync to mobile apps. ElectricSQL and PowerSync are built around a server database that clients sync with, which suits an app whose backend you run. cr-sqlite puts CRDTs inside SQLite, Automerge and Yjs are CRDT libraries for documents, Ditto does peer to peer, and Turso’s embedded replicas put a local copy of a hosted database on the device. Ink & Switch’s 2019 essay on local-first software set the goals most of these projects still quote: the app works offline, and your data outlives the vendor.
The cautionary tale is Realm. MongoDB deprecated Atlas Device Sync, the sync service for Realm databases, in September 2024 and set its end of life for September 30, 2025. Apps built on it had a local database that kept working and a sync layer somebody else could switch off. It’s the rent-or-build question from the mobile backend dilemma with a nastier failure mode: rent your sync and your offline architecture has a vendor’s roadmap inside it. A small engine with a documented protocol would let you own that layer.
Merge Rules Belong to the Field
Start with the model. A record is a map of fields, and each field is a register, a counter or a set, declared in the schema. Every write carries a hybrid logical clock (HLC) stamp and a replica ID. An HLC, introduced in a 2014 paper by Kulkarni and colleagues, pairs wall-clock time with a counter so stamps stay close to real time but never run backward, and a replica that sees a stamp from the future advances its own stamp past it. Ties break on replica ID, so any two replicas comparing the same two writes pick the same winner.
Each replica keeps an append-only change log numbered by its own sequence, the same shape as an embedded event log. Because that log records who changed what and when, it doubles as the change-history store people otherwise build separately for audit trails. A sync exchange has two steps. The replicas swap version vectors, which record the highest sequence seen from each replica, then each side sends the changes the other lacks. Applying a change twice does nothing, so a dropped connection or a repeated sync is harmless. Registers resolve by highest stamp. Counters keep separate increment and decrement totals per replica and merge by taking the larger value for each replica, so concurrent increments add up instead of overwriting each other. Sets are add-wins: a remove only cancels the adds its author had seen. Text is out of scope for 0.1; store it as one register, or hand it to a text CRDT as an opaque value.
Here’s a conflict, as a sketch of an API that doesn’t exist yet.
a = open_replica("laptop.db", id="laptop")
b = open_replica("phone.db", id="phone")
a.put("order:17", {"status": "open", "notes": "call first"})
db_sync(a, b) # both replicas now hold order:17
a.set("order:17", "notes", "call first, gate code 4471") # offline edit on the laptop
b.set("order:17", "notes", "customer moved to Friday") # offline edit on the phone
b.set("order:17", "status", "rescheduled") # only the phone touches status
db_sync(a, b)
a.get("order:17") == b.get("order:17") # True: same winner on both sides
a.conflicts("order:17") # the losing write to notes, kept in the log
Both replicas end up with the same notes value, whichever write carried the higher stamp, and the other write isn’t gone: it stays in the change log, and conflicts returns it. Silent last-writer-wins is data loss with a timestamp on it, so the library should make the losing write easy to find even when the default lets the winner stand. Because db_sync runs in-process on any two handles, convergence is testable. Generate random operations on three replicas, sync random pairs in random order, and assert that every replica ends in the same state. That test is the real specification.
What Convergence Doesn’t Cover
CRDTs converge. They don’t enforce rules. Two offline devices each sell the last ticket, or two offline users claim the same username, and the merge keeps both writes without complaint. Invariants that span records (a balance that never goes negative, a unique name) need either a model that can’t violate them, such as reservations that expire, or a single authority that sequences those particular writes. Pick one per invariant, say which in the docs, and refuse to promise more.
Deletes are tombstones, and tombstones can’t be collected until every replica has seen them. Then a laptop spends six months in a drawer. If the others drop the tombstone anyway, the laptop returns and resurrects deleted records. The workable policy is to expire replicas that stay silent past a limit, and to make an expired replica discard its state and resync from a snapshot instead of merging its old log. The user loses a few offline edits. The alternative is zombie data.
Replicas run different app versions too. Version 2 adds a field while a version 1 phone is still relaying changes, so the engine has to carry unknown fields through merges untouched, or the old phone strips them. Authorization is the other gap. If a server or relay forwards changes between devices, it has to check each change against who may write that record, which means changes carry an author and the log becomes part of your attack surface. A sync protocol with no answer to “who may write this” is a demo.
An HLC softens clock skew without solving it. It absorbs a fast clock instead of rejecting it, so one phone set to the year 2031 wins every conflict it touches until 2031. A drift bound on incoming stamps contains that, at the price of rejecting changes from a device whose clock is honestly wrong. Initial sync has its own trap: replaying a change log from zero is slow for a large dataset, so the protocol needs a snapshot plus a log tail from day one, even if a brand-new replica is the only user of the snapshot path in 0.1.
A First Version That Says No
Version 0.1 is a library for one language: key-value and document records with typed fields (register, counter, add-wins set), HLC stamps, version vectors, tombstone collection with replica expiry, an in-process db_sync plus a documented wire format for the same exchange, and a CLI that dumps the change log and conflicts. It refuses arbitrary SQL tables, cross-record constraints, text merging and untrusted replicas. That last refusal limits it to devices you or your team own, which is an honest place to stop. SQL over the same records is a separate problem, worth tackling once convergence is boring.
The same problem shows up with no server at all. AltSql Mesh lets IoT devices share one table peer to peer with no gateway, and its demo has twelve devices syncing over lossy radio links. Different hardware, same question of who wins when two devices disagree.
Decide who wins before anything moves.