build-offline-first-sync

name	build-offline-first-sync
description	Designs offline-first client data layers — a local store (SQLite/Room/Core Data/WatermelonDB), a durable outbound mutation queue with idempotency keys, optimistic local writes, cursor-based delta pull, conflict resolution (last-writer-wins/vector clocks/CRDT), tombstone deletes, and reconnect reconciliation.
when_to_use	When an app must read/write while offline and reconcile with a server — choosing the local store, queuing offline mutations, pulling deltas since a cursor, resolving write conflicts. Distinct from manage-client-server-state (online cache/TanStack Query) and message-queue-jobs (server-side worker queues).

When to Use

Reach for this when the client is the source of truth while offline and must converge with a server later, not just cache responses:

• "App has to work in airplane mode and sync when it reconnects"
• "Pick a local store — SQLite vs Room vs Core Data vs WatermelonDB"
• "Queue writes made offline and replay them in order without dupes"
• "Two devices edited the same row offline — who wins?"
• "Pull only what changed since last sync instead of refetching everything"
• "Deletes keep coming back after sync" (missing tombstones)
• "Optimistic edit, then roll back if the server rejects it"

NOT this skill:

• Online data fetching / cache invalidation with a live connection (TanStack/React Query, hydration, refetch) → manage-client-server-state
• The server-side worker that processes the sync queue (consumers, DLQ, exactly-once on the backend) → message-queue-jobs
• The shape of the sync API itself (REST vs GraphQL, pagination params, error envelopes) → rest-graphql-contract
• Changing the server schema the deltas come from (DDL locks, rollback) → db-migration-safety
• Identifying who the syncing user is / token refresh on reconnect → auth-jwt-session

Steps

1. Pick the local store by platform + reactivity need — don't reach for raw SQLite by reflex.

   Store	Best when	Reactive queries	Migrations
   SQLite (SQLDelight/Drift/expo-sqlite)	Cross-platform, you want real SQL + full control	Manual (triggers/PRAGMA data_version) or lib-provided	Hand-written user_version steps
   Room (Android)	Native Android, Kotlin/Flow	Flow/LiveData built-in	Migration objects, fallbackToDestructive = data loss, avoid
   Core Data / SwiftData (Apple)	Native iOS, object graph + iCloud	@FetchRequest/NSFetchedResultsController	Lightweight (auto) vs mapping model
   WatermelonDB (RN)	React Native, large datasets, lazy reads	Observables out of the box	schemaMigrations versioned
   Realm/MongoDB Atlas Device Sync	You want sync built in and accept the lock-in	Live objects	Schema-versioned

   Default: SQLite via a typed wrapper (SQLDelight/Drift) for cross-platform; WatermelonDB for React Native with thousands of rows; native (Room/Core Data) only if single-platform. Avoid building your own sync on Realm Device Sync unless you adopt their whole model.

2. Add sync bookkeeping columns to every syncable table. The on-device schema is the server schema plus local metadata:

   CREATE TABLE task (
     id            TEXT PRIMARY KEY,        -- client-generated UUIDv7 (sortable), NOT server autoincrement
     title         TEXT NOT NULL,
     updated_at    INTEGER NOT NULL,        -- server-assigned ms epoch on last sync (the LWW clock)
     version       INTEGER NOT NULL  , 
     deleted_at    ,                 
     sync_status   TEXT     
   );
   

Common Errors

• Server-autoincrement PKs for offline-created rows. You can't link FKs or reference the row until the server replies. Generate UUIDv7/ULID on the client; keep that id forever.
• Hard-deleting locally instead of tombstoning. The next pull from another device re-creates the row (it never saw the delete). Set deleted_at, sync the tombstone, GC tombstones only after all clients have pulled past them.
• Advancing the sync cursor before the page is committed. A crash mid-apply skips rows permanently — silent data loss. Commit the page, then persist the cursor.
• No idempotency key on push. A retried op after a timeout (where the server actually succeeded) double-applies — duplicate rows / double charges. Idempotency-Key: {op_id}, server dedupes.
• Replaying every keystroke from the outbox. 200 ops to sync one note. Coalesce ops per entity_id before push.
• Pull before push. The server hasn't seen your local edits yet, so the delta overwrites your optimistic state and the UI flickers back. Always push first.
• Trusting the OS "connected" flag. Captive portals and dead Wi-Fi report "connected". Confirm with an actual lightweight request before draining the queue.
• Unbounded retries on a permanent 4xx. A 422 op retries forever and head-of-line-blocks every later op. Cap attempts; dead-letter the poison op; keep draining the rest.
• Last-Writer-Wins on a whole row. One device edits title, another edits due_date; whole-row LWW silently drops one field. Do field-level LWW or merge.
• Ignoring clock skew in LWW. Client clocks lie. Use the server-assigned updated_at as the LWW clock, not the device clock.
• Migrating local schema after the first sync. Incoming rows don't fit the old schema → crash or silent drop. Migrate on app start, before sync runs.

Verify

1. Airplane-mode write survives restart: Go offline, create + edit + delete records, force-quit and relaunch → all local changes still present, outbox intact, sync_status='pending'.
2. Reconnect drains correctly: Re-enable network → outbox empties, every op acked, rows flip to synced, server reflects every offline change exactly once (no dupes — proves idempotency keys work).
3. Delta pull is incremental: Trigger a remote change, sync → only the changed rows transfer (inspect request: since={cursor} with a non-empty cursor, response page << full table). A second sync with no remote changes transfers zero rows.
4. Conflict resolves deterministically: Two clients edit the same row offline, both reconnect → result matches the documented strategy (field-level LWW = each field = latest server updated_at; CRDT = both edits merged), and no write vanishes silently — divergence shows as conflict.
5. Tombstone delete stays deleted: Delete on device A, sync; device B syncs → row disappears on B and does not resurrect on A's next pull.
6. Flaky network / mid-sync kill: Throttle to 2G + 30% packet loss (Network Link Conditioner / Charles), kill the app mid-pull → relaunch re-fetches the uncommitted page, converges, no duplicate or missing rows; cursor never advanced past uncommitted data.
7. Poison op doesn't block the queue: Inject an op the server rejects with 422 → it dead-letters after the attempt cap and surfaces to the user; every other queued op still syncs.
8. Schema-version mismatch is safe: Point an old client at a newer server → 426/upgrade path, not a corrupt write or crash.

Done = a record created offline survives an app restart, syncs exactly once on reconnect, incremental pull transfers only deltas since the cursor, concurrent edits resolve per the documented strategy with no silent data loss, deletes stay deleted, and a mid-sync kill under a flaky network converges with no duplicate or missing rows.