Writes that survive the tunnel
Problem. Early builds wrote optimistically to local state and fired the request in the background. On a flaky connection the request failed, the local state kept the change, and the two silently diverged. In a fortnight of internal testing roughly one write in fourteen was lost this way — and the user was never told.
Approach. Every mutation is persisted to an SQLite outbox inside the same transaction that applies it locally, keyed by a client-generated ULID. A flush drains the queue in batches of fifty; anything that fails stays queued with an incremented attempt count and exponential backoff. The ULID doubles as an idempotency key, so replaying a batch that already landed is a no-op.
Result. Twenty thousand synthetic mutations replayed against a proxy that drops one request in three: zero lost writes, zero duplicates. The app has no “syncing” state to render because there is nothing for the user to wait on.
// db/outbox.tsexport type Mutation = { id: string; // client-generated ULID — also the idempotency key table: "watchlist" | "episodes" | "ratings"; op: "upsert" | "delete"; payload: Record<string, unknown>; updatedAt: number; // device clock, reconciled server-side}; // Every write lands here first. The UI reads from SQLite and never from the// network, so the optimistic state *is* the state until a flush says otherwise// — which means there is no "pending" branch to render anywhere in the app.export async function enqueue(db: SQLiteDatabase, mutation: Mutation) { await db.withTransactionAsync(async () => { await applyLocally(db, mutation); await db.runAsync( `INSERT INTO outbox (id, table_name, op, payload, updated_at, attempts) VALUES (?, ?, ?, ?, ?, 0) ON CONFLICT (id) DO UPDATE SET payload = excluded.payload, updated_at = excluded.updated_at`, [ mutation.id, mutation.table, mutation.op, JSON.stringify(mutation.payload), mutation.updatedAt, ], ); });}-- The replay is unordered and may repeat: a retry has to be a no-op, and a-- device that has been in a tunnel for an hour must not overwrite a newer-- write from the user's other phone. The guard in the WHERE clause is the-- whole conflict policy — last write wins, decided by the row, not the request.insert into watchlist (id, user_id, tmdb_id, status, updated_at)values ($1, auth.uid(), $2, $3, $4)on conflict (id) do update set status = excluded.status, updated_at = excluded.updated_at where watchlist.updated_at < excluded.updated_at;