Skip to content

0019 sync

github-actions[bot] edited this page Nov 18, 2026 · 4 revisions

Sync is our own push and pull

Status: accepted · Implementation: done · Date: 2026-11-18

From LibreLifts ADRs 0027 and 0034, with what building it there showed. PowerSync is the fallback if live updates or shared data are ever needed. The design follows WatermelonDB's sync specification and Replicache's per-space version strategy. As built so far, 2026-11-18: packages/contracts holds the list of synced tables in the order they are written, the parent of each child table, and the columns of each row, so the device and the server take them from one place instead of each keeping a copy. On the device, 2026-11-18: synced.ts is the one module that writes a synced table, with batched forms for adding and deleting; ids and the outbox are in place. Nothing is sent yet. Built 2026-11-18, with two differences from this record. The server does not save a push one row at a time: a run of rows of one table goes in as one statement, and a test holds a push of 400 sets to under 20 queries. And a rule was added that this record did not have: a pull page never holds a row without the row it belongs to. A workout is saved again when it is finished and gets a newer version than its sets, so a page that ended between the two gave a device a set it had to refuse; a page now also carries any newer parent its rows need. LibreLifts has the same gap and does not meet it, because it logs a workout in one go. Seen running in Chromium against a local API: a log pushed, the browser database wiped, and the log pulled back.

Context

The app writes to its own database and the server holds the truth (0014), so changes have to travel both ways, and survive a phone that is offline for a week.

A person's log is the easy case for this. Each row belongs to one person, who writes from one or two devices and mostly adds rows. There are tools that sync SQLite with Postgres, but each brings a service to run, its own SQLite library, or a company whose plans have changed. LibreLifts built the small thing this data needs and tested it; LibreBars takes that design.

Decision

On the device

  • Ids are UUIDv7, made on the device, so a row can be created offline and keeps its id.
  • A delete is a mark on the row. Rows are never removed.
  • Every change writes the row and a note in an outbox in one local transaction. One module is allowed to write a synced table, and nothing else does.
  • A table with one row per thing gets its id from the thing (UUIDv5 of a setting's name), so that two devices arrive at the same row by themselves.
  • The device remembers the last version it has seen in a table that does not sync.
  • It pushes, then pulls. A row that still has a note in the outbox is not replaced by a pull: the device's change goes up first and wins.
  • Rows that arrive from the server are written by one function, the only place allowed to write a synced table without an outbox note.
  • One sync is one function that takes the database and a transport. Only the transport calls the network, and tests hand the function a fake one. If it fails partway it throws, keeping what it finished, and the next run carries on.
  • When it runs: a few seconds after a change, so that a burst goes together; when the app comes to the front; and when the network returns. After a failure it waits longer each time and never retries at once. Nothing runs on a timer while nothing has changed.
  • It reads and writes in batches: 100 rows to a statement, 500 changes to a page.

On the server

  • The server gives versions, not the device. Each account has one counter. A push locks the counter's row, adds one, and gives that number to every row it saves, in one transaction. Device clocks are never compared.
  • POST /sync/push takes a list of changed rows, each named by its table, and saves them in the order given. A row that exists is replaced, so a push is safe to send twice. A row whose parent the account does not have is refused.
  • GET /sync/pull?since=N answers with the rows whose version is above N, oldest first, in pages. A page never ends in the middle of a version.
  • A row is known by its account and its id together. Every query on personal data filters on the account from the session.
  • The last push received wins, per row.
  • Every request carries the app's schema version in a header. A version the server no longer accepts gets status 426; the app keeps working and says it must be updated.

Consequences

  • No extra service, no change of SQLite library, and nothing that depends on another company.
  • Getting it right is the project's own job. The cases that break sync are tests: a push sent twice, order, two devices, two accounts with the same id, a page boundary, an app too old for the server.
  • Two devices changing the same row lose one of the changes. For one person's log that is rare and accepted; it is the price of no merging.
  • Changes from another device show up at the next sync, not at once.
  • A synced column can be added, never renamed or removed, once apps are in use.
  • A child row must arrive after its parent. The outbox keeps the order things were written in, and a push is saved in the order given.
  • The server saves a push one row at a time, as LibreLifts' does. A very large push is slow; batching it is left for when it matters.
  • Data shared between people does not fit a version per account. If that is wanted, this record is revisited.

LibreBars

Clone this wiki locally