Offline-first mobile: local SQLite, sync, and resolving conflicts honestly

Offline-first is usually described as a feature for people with poor connectivity. It is better understood as an architecture that stops treating the network as reliable — which is useful for everyone, because a train tunnel and a hotel wifi captive portal are the same problem as no signal at all.

We have built this for vehicle tracking and field operations, where the phone routinely leaves coverage mid-task. These are the decisions that mattered.

The local database is the source of truth

The defining commitment: the UI reads from local storage and never waits for the network.

UI  ⇄  local SQLite  ⇄  sync engine  ⇄  API

Nothing in the UI layer knows whether a connection exists. It reads local rows and writes local rows; the sync engine reconciles in the background. That inversion is what makes the app feel instant online too, because no interaction is ever gated on a round trip.

Identifiers must be generated on the device

If the server assigns ids, nothing created offline has one, and every local reference is a placeholder that must be rewritten later. That rewriting is where the bugs live.

1. Use UUIDv7 or ULID, generated on the device

   Both are time-ordered, so they index well as primary keys — unlike UUIDv4, which scatters writes across a B-tree.

2. The server accepts the client's id

   It is the same record. A second, server-side id is a mapping table and a whole class of reconciliation bug.

3. Uniqueness is the client's responsibility, and that is fine

   Collision probability at these sizes is not a real risk. Rewriting foreign keys after the fact is.

The outbox: every change is a durable record

Do not send mutations directly. Write them to a local queue, in the same transaction as the change itself.

CREATE TABLE outbox (
  id          TEXT PRIMARY KEY,
  entity      TEXT NOT NULL,
  entity_id   TEXT NOT NULL,
  operation   TEXT NOT NULL,          -- create | update | delete
  payload     TEXT NOT NULL,
  base_version INTEGER,               -- what the client believed when it wrote
  created_at  INTEGER NOT NULL,
  attempts    INTEGER NOT NULL DEFAULT 0,
  last_error  TEXT
);

The transaction is the point:

db.transaction(() => {
  db.run('UPDATE jobs SET status = ? WHERE id = ?', ['complete', jobId]);
  db.run('INSERT INTO outbox (...) VALUES (...)', [...]);
});

If the app is killed between those two statements — and it will be — either both happened or neither did. A change that is applied locally but absent from the outbox is a change that will never reach the server, and nothing will ever notice.

Sync: pull by cursor, push by queue

1. Pull changes since a server-issued cursor

   Not a client timestamp. Client clocks are wrong, and a device whose clock drifts backwards will silently skip records forever.

2. Push the outbox in order, per entity

   Ordering matters within an entity — create before update before delete. Across unrelated entities it usually does not, and parallelising there is a real speed-up.

3. Apply pulled changes in a transaction, then advance the cursor

   Advance it only after the write commits, or a crash mid-apply loses a page of updates permanently.

4. Back off, and distinguish retryable from terminal

   A 500 is retryable. A 422 is not — retrying it forever blocks the queue behind it, which is how one malformed record stops a device syncing at all. Terminal failures move to a dead letter table and surface in the UI.

Conflict resolution: pick a strategy per entity, and be honest about it

There is no general solution. There are four strategies, and the work is choosing the right one per entity rather than applying one everywhere.

The append-only row is the one to reach for first. Most conflicts are an artefact of modelling something as a mutable field that is really a sequence of events. A job with a status column conflicts; a job with a list of timestamped status events does not — the merge is a union, and the current status is a fold over it.

// Conflicting: two devices set status, one wins, one edit vanishes.
job.status = 'complete';
 
// Not conflicting: both events survive, order is by time, state is derived.
events.push({ jobId, type: 'completed', at: now(), by: userId });

For the fields that genuinely are mutable, send the version the client was editing and let the server detect the conflict rather than guess:

PATCH /jobs/01J8X...   If-Match: "v7"
→ 409 Conflict  { current: { ... }, version: 9 }

Tell the user the truth about sync state

Hiding sync state is a decision to have your users distrust the app. Three states, visible:

1. Saved locally

   The default after any write. Say it plainly — the work is safe on this device.

2. Syncing / N pending

   A count, not a spinner. People tolerate a queue they can see.

3. Needs attention

   Terminal failures and conflicts awaiting a decision, reachable from one place.

Testing the parts that only break in the field

1. Kill the process mid-sync, repeatedly

   The transaction boundaries above are only correct if you have actually tested this.

2. Test the captive portal, not just airplane mode

   Airplane mode fails fast and cleanly. A hotel wifi that accepts the connection and returns HTML for every request is the case that breaks naive implementations.

3. Move the device clock backwards

   If anything relies on client time, this finds it.

4. Sync the same account from two devices, offline, then reconnect both

   This is the conflict path, and it is the one nobody tests until a customer reports it.

5. Fill the queue with hundreds of entries

   Then reconnect. Backoff, ordering and batching all behave differently at that size.