An offline first mobile app stays correct when the local database is the source of truth and every change goes through an outbox. Retries must be idempotent, and conflicts must be decided by server-assigned versions, not device clocks. This tutorial builds that sync layer in six steps, with code you can adapt.

The problem in two sentences

Users edit data with no signal, and the app must send those edits later without losing, duplicating or reordering them. Meanwhile, other devices change the same records, and the server must decide who wins without trusting any phone's clock.

If you want a sync layer that has already survived these edge cases, we build this for clients, so ask us about yours.

Prerequisites

  • A local SQL store: SQLite through expo-sqlite or op-sqlite, Room on Android, or SwiftData or GRDB on iOS.
  • A server database with transactions. We use Postgres in the examples.
  • UUIDs generated on the device, so records exist before the server sees them.
  • Comfort with SQL transactions and HTTP status codes.

Off-the-shelf options exist, such as PowerSync, WatermelonDB and Couchbase Lite. MongoDB has deprecated Atlas Device Sync, so check any vendor's roadmap before you commit. Building it yourself typically takes two to four weeks for a simple data model.

How to build an offline first mobile app sync layer

Google's offline-first guide for Android describes the same core idea. The UI reads only from local storage, and a separate process syncs it. The steps below apply to any platform.

Step 1: Make the local database the source of truth

Every screen reads from SQLite. It never reads from the network. Each table carries a version and a soft-delete flag.

CREATE TABLE notes (id TEXT PRIMARY KEY, body TEXT, version INTEGER NOT NULL DEFAULT 0, deleted INTEGER NOT NULL DEFAULT 0, dirty INTEGER NOT NULL DEFAULT 0);

The version is the last one the server confirmed. The dirty flag marks rows with unsent edits. Deletes set the deleted flag instead of removing the row.

Step 2: Write every change to an outbox in one transaction

The data change and the outbox entry must commit together. If the app crashes between two separate writes, you lose the edit or send a phantom one.

BEGIN; UPDATE notes SET body = ?, dirty = 1 WHERE id = ?; INSERT INTO outbox (op_id, entity_id, op, payload, base_version) VALUES (?, ?, 'update', ?, ?); COMMIT;

The op_id is a fresh UUID for each operation. The base_version records which server version the user was editing.

Step 3: Push with idempotency keys

Send outbox entries in order, oldest first. Remove an entry only after the server confirms it.

const res = await fetch(url, { method: 'POST', headers: { 'Content-Type': 'application/json', 'Idempotency-Key': op.op_id }, body: JSON.stringify(op) }); if (res.ok) await removeOp(db, op.op_id); else if (res.status === 409) await markConflict(db, op, await res.json());

On the server, record the op_id inside the same transaction as the write.

INSERT INTO processed_ops (op_id) VALUES ($1) ON CONFLICT (op_id) DO NOTHING RETURNING op_id;

If no row comes back, the operation was already applied. Return the stored result and do not apply it again.

Step 4: Reject stale writes with version numbers

The server only applies an update if the client edited the current version. This is optimistic concurrency.

UPDATE notes SET body = $1, version = version + 1, seq = nextval('sync_seq') WHERE id = $2 AND version = $3;

If zero rows change, someone else got there first. Return 409 with the current server row, and let the client decide how to resolve it.

Step 5: Pull deltas with a server cursor and tombstones

The client stores the highest seq it has applied. It asks for everything newer, in pages.

SELECT id, body, version, deleted, seq FROM notes WHERE owner_id = $1 AND seq > $2 ORDER BY seq LIMIT 500;

Apply each page and save the new cursor in one local transaction. Skip rows that are dirty locally, because the push conflict will resolve them. One caveat: concurrent Postgres transactions can commit sequence values out of order. To avoid skipping rows, re-fetch a small overlap window and dedupe by version.

Step 6: Let the OS schedule sync

Trigger sync on app foreground, on connectivity change and after each local write. For background runs, use WorkManager with a network constraint on Android. On iOS, use BGTaskScheduler, which gives no guarantee of when your task runs.

Pick a conflict strategy

The 409 from step 4 needs a policy. Here are the four common options.

  • Server last-write-wins: simplest to build, but it silently drops edits. Fine for settings and presence status.
  • Reject and ask: nothing is lost, but you need a conflict UI. Good for orders, inspections and anything with money attached.
  • Field-level merge: merges edits that touch different fields. It covers most real form conflicts, at the cost of more server code.
  • CRDTs such as Automerge or Yjs: merge automatically and suit collaborative text. Payloads grow and querying gets harder, so they are overkill for plain CRUD.

The two mistakes that corrupt data in production

Trusting device clocks

Phones drift, and users set their clocks by hand. If you resolve conflicts or build pull cursors from client timestamps, a skewed device wins wrongly or misses records entirely. Use server-assigned versions and sequences for anything that decides order.

Retries that are not idempotent

A request can time out after the server has committed it. Without an idempotency key, the retry creates a duplicate order or a double payment. The same family of bug includes hard deletes, which let an old device resurrect a record on its next push.

How to test it

  • Unit test your merge function against a table of cases: same field, different fields, delete versus edit.
  • Kill the app between the local commit and the push. On relaunch, assert the operation is still in the outbox.
  • Add a server test hook that commits and then returns a 500. Assert that exactly one row exists after the retry.
  • Edit the same record on two offline devices, then reconnect both. Assert one 409 and a visible conflict.
  • Set a device clock a day ahead and confirm sync still behaves.
  • Throttle with Network Link Conditioner on iOS and emulator network settings on Android. Test 100% packet loss and 3-second latency.
  • Queue 2,000 operations offline and check that push batches them in order.

FAQ

Do I need a sync library or can I build it myself?

For a few tables with simple conflicts, a custom outbox and versioning layer is manageable and easy to debug. For many tables, partial sync per user or real-time collaboration, a library like PowerSync usually saves months. Either way, you still need to choose a conflict policy.

How long can a device stay offline before sync breaks?

With the design above, there is no hard limit, but long gaps raise conflict rates and pull volume. Page your pulls and batch your pushes. Some teams force a full resync if the cursor is older than their tombstone retention period.

Should I use CRDTs for offline sync?

Use them when many people edit the same text or canvas at once. For forms, orders and records, version checks with field-level merge are simpler to reason about and to query. Most business apps never need CRDTs.