Offline-first sync for Hope

Hope is a time tracker with three clients: a Tauri desktop app in Rust, an iPhone app and an Apple Watch app in Swift. Each keeps a complete local database and works with no network at all. Supabase holds the cloud copy. The sync protocol has to be small, because every client reimplements it, and boring, because a timer that drifts between a watch and a desktop is worse than no sync.

The rules

Every synced row carries three columns from day one: updated_ms, deleted_ms and device_id. Ids are UUID v4, generated only on the device that creates the row, so two offline devices never collide. There are no foreign keys: rows can arrive out of order, and the UI tolerates a session whose activity has not arrived yet by drawing it gray.

Conflicts resolve by last writer wins on the pair (updated_ms, device_id). The second element matters: when two devices write the same millisecond, the larger device id wins, and every client reaches the same answer without talking to the others. Deletes are soft, so a delete is just another write that can lose.

The server enforces the rule. A BEFORE INSERT OR UPDATE trigger on each table discards a write whose pair is not greater than the stored one, and stamps the accepted row with server_seq from a global sequence. A second trigger keeps the invariant that only one session is running per user: when a new open session arrives, older open sessions are closed at its start time.

Push and pull

Push is a PostgREST upsert, batched at 500 rows, debounced 500 ms after a local write. Each local row has a dirty flag; it clears when the push succeeds and the row’s updated_ms has not changed in the meantime.

Pull walks server_seq from a per-table cursor. It deliberately starts 50 sequence numbers behind the cursor, because two concurrent transactions can commit out of sequence order and a pull between them would miss one. Over-fetching is harmless: applying a remote row uses the same comparison as the server, so a repeat is a no-op, and a local dirty row that is newer simply stays dirty and wins later.

Logged in, a client pulls every five seconds, does a full push and pull on launch and on reconnect, and backs off exponentially to a minute on errors.

What I did not build

No Realtime. A five-second poll already shows a timer started on the watch on the desktop cards within seconds, and a websocket would be a second protocol to keep three clients honest about. It stays on the list as phase 7b.

Testing

The transport sits behind a trait. The tests run against an in-memory fake server that implements the same comparison and the same sequence counter. The cases are the ones that matter: two devices converge, offline edits merge, a delete races an edit, only one session runs, a repeated pull changes nothing, pagination, and the out-of-order commit window. The Swift package runs the same cases against its own fake.

The one that bit

Adding a column is a four-place change: a local migration, the Rust table list, the Swift table list, and the cloud schema, which has to be run in the Supabase SQL editor by hand. Forget the last one and PostgREST answers every upsert with HTTP 400 and PGRST204, and the client retries every five seconds until someone notices. The reading path for exactly this task is on the AI workflow page.