Mobile Offline Sync skill for Codex

Done well means: the UI reads only from the local store, every user edit is durable the moment it is made, each mutation reaches the server exactly once in effect, concurrent edits resolve by a rule the product team chose on purpose, and nothing depends on the device clock being correct.

Before changing anything

1. Read AGENTS.md, then map the current data flow: where the UI reads from (network, cache, database), where writes go, and what happens when a request fails offline.
2. Identify the local store (SQLite, Room, Core Data, SwiftData, Realm, WatermelonDB, Drift, AsyncStorage) and the backend’s API style and ID scheme.
3. List entities and classify each (table below). Most apps need sync for a few, not all.
4. Ask the product owner how conflicts should resolve for each user-editable entity. This is a product decision; record it before writing code.

Classify every entity

Entity kind	Strategy
Reference data (catalog, config)	Pull-only cache with cursor or ETag; no outbox
Single-owner records (my notes, drafts, settings)	Outbox + last-writer-wins per field
Shared records edited by several users (tasks, orders)	Outbox + server versions; conflicts surfaced or merged by rule
Collaborative text or ordered lists	CRDT (Yjs, Automerge) or a sync engine built on one
Money, inventory, bookings	Server is authority; queue intents offline, confirm online, show pending state

Build or buy

Situation	Choice
Postgres backend, want SQLite on device with partial replication	PowerSync or ElectricSQL
Firebase already in use	Firestore offline persistence (cache is not a full database: queries offline see only cached docs)
CouchDB-compatible backend	PouchDB / Couchbase Lite
Custom REST/GraphQL API, a few synced entities	Hand-rolled outbox + pull (this skill’s patterns)
Realm with Atlas Device Sync	Migrate: Device Sync was deprecated in 2024 and has reached end of life

Before adding any engine, check its pricing and self-hosting story, and that it supports every platform you ship (iOS, Android, web, Flutter or React Native bindings).

Local schema

• Generate IDs on the client (UUIDv7 or ULID) so records exist before the server sees them. Never use the server’s auto-increment ID as the local primary key.
• Every synced row carries server_version (or updated_seq), dirty flag or outbox reference, and deleted_at for tombstones.
• Store a per-collection sync_cursor returned by the server.

CREATE TABLE task (
  id TEXT PRIMARY KEY,               -- client-generated UUIDv7
  title TEXT NOT NULL,
  done INTEGER NOT NULL DEFAULT 0,
  server_version INTEGER,            -- null until first ack
  deleted_at INTEGER                 -- tombstone, purge after server ack
);
CREATE TABLE outbox (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  mutation_id TEXT NOT NULL UNIQUE,  -- idempotency key sent to the server
  entity TEXT NOT NULL, entity_id TEXT NOT NULL,
  op TEXT NOT NULL,                  -- create | patch | delete
  payload TEXT NOT NULL,             -- JSON of changed fields only
  base_version INTEGER,              -- server_version the edit was based on
  schema_version INTEGER NOT NULL,
  attempts INTEGER NOT NULL DEFAULT 0
);

Write the row change and the outbox entry in one local transaction. If either fails, neither happened.

Push: draining the outbox

1. Take entries in seq order. Coalesce consecutive patches to the same row if the API allows, but never reorder across rows that have foreign-key dependencies.
2. Send each with its mutation_id as an idempotency key. The server stores processed keys and returns the original result on replay.
3. On success, set server_version from the response and delete the outbox entry in one transaction.
4. On 409 Conflict (server version newer than base_version), apply the entity’s rule: field-level merge, server wins with the local edit shown to the user, or keep both.
5. On 4xx validation errors, park the entry in a dead-letter state and surface it in the UI; retrying will never succeed.
6. On network errors or 5xx, stop draining and retry later with exponential backoff and jitter. Do not skip ahead; later entries may depend on this one.

Pull: cursor-based changes

GET /sync/tasks?cursor=8812&limit=500
-> { "changes": [ {...row, "version": 9001, "deleted": false} ], "cursor": "9001", "has_more": true }

• The cursor is a server-assigned monotonic sequence (database sequence, log offset), not updated_at; timestamps collide and skew, and rows committed out of order get skipped.
• Deletes arrive as tombstones in the change feed. A sync API without deletes leaves zombie rows forever.
• Apply a page and its cursor in one transaction. Do not overwrite rows that still have pending outbox entries; rebase the local edit on the new server version instead.
• If the cursor is too old for the server to serve, it returns a reset signal and the client does a full resync of that collection.

Conflict rules

• Last-writer-wins needs a single ordering source: the server’s commit order, never device timestamps.
• Patch only changed fields so two users editing different fields both win.
• For counters, send increments, not totals.
• For text, either lock (one editor at a time), keep both versions, or use a CRDT.
• Show the user when their edit lost, and keep their version recoverable.

Running sync

Trigger	Android	iOS
App foreground, after local write	Coroutine in a repository scope	Task from the app’s sync actor
Connectivity regained	WorkManager with NetworkType.CONNECTED constraint, unique work	NWPathMonitor while running
Periodic background	PeriodicWorkRequest (minimum 15 minutes)	BGAppRefreshTask via BGTaskScheduler (system decides timing)
Server says data changed	Data FCM message triggers one-time work	Background push, best-effort

Run one sync at a time per collection (a mutex or unique work name). Background execution on iOS is never guaranteed; the app must also sync on every launch.

Gotchas

• Local schema migrations must also migrate queued outbox payloads, or keep a schema_version per entry and upgrade it when draining.
• The user can log out with unsent changes; warn and offer to sync first, then wipe the local database and outbox for that account.
• Large first syncs on cellular: page them, show progress, and allow resume.
• Files and images go through a separate upload queue that stores the local file path and attaches the remote URL by mutation once uploaded.
• Server-side, accept idempotency keys for long enough to cover the longest offline period you support (days, not minutes).

Verification

Write tests at the repository layer with a fake transport that can fail, delay, duplicate and reorder responses:

• Edit offline, kill the app, relaunch online: the edit reaches the server once
• Network drops after the server commits but before the response: replay is deduplicated
• Two clients edit the same row: the documented rule applies, the loser is notified
• Delete offline on A while B edits online: tombstone wins or the rule says otherwise
• Device clock set a year ahead: ordering is unaffected
• Validation error from the server: the entry is parked and visible, the queue continues
• Upgrade from the previous app version with a non-empty outbox

Then test by hand in airplane mode and with the network link conditioner (iOS) or emulator network throttling (Android).

Output

**Entities:** <name -> strategy, conflict rule>
**Store / engine:** <choice and why>
**Protocol:** <push endpoint + idempotency, pull endpoint + cursor, tombstones>
**Background:** <triggers per platform>
**Tests:** <scenarios above -> pass/fail>