Skip to main content

ADR-0004 — Offline-first SQLite with delta sync

Date2026-08-17
Superseded by—

Context​

A climbing guidebook is used where there is no signal. Not "sometimes slow" — a Peak District valley, a Céüse approach, an aeroplane on the way to Spain. The app must be fully functional with the radio off.

It also has to accept writes offline. Logging a tick happens at the crag, or in the car, and it must not be lost or deferred.

That rules out the usual answers. An HTTP cache degrades: cold entries mean a spinner. A "download for offline" mode makes offline a second-class path that gets less testing than the one developers use.

Decision​

The phone keeps a full local SQLite mirror. Screens read from it and never call the network. A cursor-based delta sync moves data in both directions when there is signal.

Four parts:

1. Local SQLite (expo-sqlite), mirroring the D1 schema. Plus two tables the server does not have: outbox for mutations awaiting signal, and sync_state for per-kind cursors.

2. A hard architectural rule. Network access lives in apps/mobile/src/lib/sync.ts and nowhere else. It is mechanical, which is exactly why it is worded that way — it is reviewable without judgement.

3. Keyset delta sync on (updated_at, id). Not updated_at alone, which silently drops records that share a millisecond. Not an offset, which drops a record for every concurrent insert. Every syncable table carries an index on that exact pair.

4. Optimistic concurrency on push. Each mutation carries baseUpdatedAt. If the stored row has moved on, the server replies conflict with its copy attached rather than overwriting the other device's edit.

Supporting decisions that follow from these and are not independently negotiable:

  • Client-generated ULID primary keys. A phone with no signal must be able to create a record now. It also makes push idempotent.
  • Soft delete everywhere. A hard delete is invisible to a device that was offline when it happened. Sync returns tombstones deliberately.
  • Push before pull. The outbox is the user's own work; stale crag data is not.
  • Offline is not an error. sync() returns { status: 'skipped', reason: 'offline' }, which surfaces as a line on the Profile screen and nothing else.

Consequences​

What gets harder:

  • Two schemas to maintain. The D1 schema and the local mirror are kept in step by hand. This is the sharpest edge in the whole design and it will bite someone. Generating the local schema from the shared Zod definitions is the obvious fix and is not done.
  • Every mutation is written twice — once locally, once to the outbox — and the two must happen in the same user action.
  • Migrating the local schema on an installed app is a real problem with no story yet. The current code creates tables if absent and nothing more.
  • Storage grows. Nothing evicts. Downloaded crags accumulate until the user reinstalls.

What we are committed to:

  • Tombstones forever. Rows never leave the database. A purge job for tombstones older than the longest plausible offline period is future work, and it must be much longer than it sounds — people leave apps closed for a season.
  • Millisecond timestamps as the ordering key. If server clocks ever skew, cursors misbehave. The pull response includes serverTime so a client can at least detect it.

What this creates:

  • Conflict handling, which today is "server wins, silently". The protocol reports conflicts properly; the client discards. That is a real data-loss case on two devices and it is on the roadmap.
  • Tests that run against a real D1 in workerd rather than a mock, because the bugs worth catching are pagination bugs and a mock returns whatever you told it to.

Alternatives considered​

HTTP caching with stale-while-revalidate​

Much simpler. Rejected because a cold cache means a spinner, and the whole product claim is that there is never one. Also gives no answer at all for offline writes.

A sync framework — WatermelonDB, RxDB, PowerSync, Replicache​

All of these solve roughly this problem, and PowerSync in particular is a good fit.

Rejected for v1 on two grounds. First, the sync surface here is genuinely small — five read-only kinds and two writable ones — and the protocol fits in one reviewable file. Second, every one of them wants to own the local schema and the query layer, which is a large dependency to take on before the data model has settled.

This is the alternative most likely to supersede this ADR. If conflict handling and background sync turn out to be as much work as they look, buying a framework is a reasonable trade.

CRDTs (Automerge, Yjs)​

Conflict-free by construction, no conflict status needed.

Rejected because the data does not have the shape that justifies them. CRDTs earn their complexity when multiple people concurrently edit the same document. Here, guidebook content is single-writer (us) and user content is single-owner. The realistic conflict is one person on two devices, which optimistic concurrency handles at a fraction of the cost in payload size, storage and comprehension.

Full re-download instead of deltas​

Simplest possible sync. Rejected on data volume — a crag with topo photos is not something to re-download because one route description changed — and because it gives no path to offline writes.

Open questions​

  • Local schema migrations. The real gap. Needs a version table and an ordered migration list before the first user has data worth keeping.
  • Conflict UX. Options are listed in Offline-first. Last-write- wins with a visible notice is probably enough for a logbook.
  • When does sync run? Today: launch and manual. Background refresh, on-network-regained, and post-write sync are all wanted and all have battery implications.
  • Images. The largest part of an offline download and entirely unaddressed. Download size, eviction, and partial downloads all need answers.
  • Tombstone retention. How long is long enough?