Offline-first mobile sync with audit trails and last-write-wins conflict resolution

Field operators do not have signal — the app has to act like it

Warehouse, logistics, healthcare clinician, energy field tech, public-safety operator — all of them work in environments where signal is intermittent, network is partitioned, or connectivity is genuinely absent. A 'connected' app that depends on a live API call for every action is not just slow in those environments; it is unusable. The operator either gives up on the app or gives up on the network.

Offline-first design assumes the network is unreliable by default. Every action writes to local storage first, queues for sync, and reconciles when connectivity returns. The user never sees a spinner waiting for a server; they see the result immediately and trust the sync to handle persistence in the background.

Local SQLite with a structured outbox queue is the durable foundation

The pattern: a local SQLite database that mirrors the relevant subset of the server schema, plus an outbox table that queues changes for sync. Reads come from the local database. Writes go to the local database and to the outbox in the same transaction. The sync engine reads the outbox, sends changes to the server, and processes server responses.

SQLite on mobile is the right default. It is durable across crashes, fast on cold start, supports transactions, and ships in every modern mobile platform. The alternative — keeping state in memory or in a non-transactional store — produces data loss when the app is killed mid-operation. Field operators have apps killed all the time; durability is not optional.

Conflict resolution policy is declared per record type, not implicit

When two operators modify the same record offline, sync produces a conflict. The naive approach — last-write-wins by timestamp — works for some record types and silently corrupts others. The right approach declares the resolution policy per record type: last-write-wins for status updates, server-authoritative for inventory counts, CRDT-style merge for collaborative documents, custom logic for orders that affect multiple operators.

Offline action latency

< 50ms local SQLite write

Sync resume after partition

< 5s after connectivity returns

Conflict rate, prod

0.3–1.2% of synced records

Audit row coverage

100% every offline action logged

Last-write-wins works for state, fails for counters

Last-write-wins is correct when the field being synchronized represents the current state of something — task status, location, last update. It is silently wrong when the field represents an accumulating count, a list, or anything where two valid concurrent updates should both be reflected. A pick count incremented by two operators concurrently should produce the sum, not whichever update wrote last.

The fix is to model counters and lists as CRDTs (conflict-free replicated data types) — counters that increment additively, sets that union — and to fall back to server-authoritative reconciliation for fields where the server has authoritative state (inventory levels backed by physical stock counts, for example). The choice is per-field, declared in the schema, enforced at sync.

Audit trails have to survive the round trip

Every offline action produces an audit row: who, when, what action, against what record, with what input. The audit row is created locally at the moment of action — not at the moment of sync — so the timestamp reflects when the operator actually did the thing, not when the device happened to reconnect. The audit row syncs along with the data change, with both the original device timestamp and the server-received timestamp preserved.

Without this, the audit trail collapses to the moment the device synced, which makes 'who did what when' impossible to reconstruct for events that happened in offline mode. For operators working across hours of offline time, the difference is not theoretical.

Sync resume has to be resilient to partial failure

Real-world sync rarely succeeds in one round trip. Network drops mid-batch, server returns transient errors on some records, the device backgrounds before sync completes. The sync engine has to handle all of these without losing data or duplicating actions. The pattern is idempotent sync (every change has an idempotency key, server deduplicates), resumable batches (the device knows what synced and what did not), and bounded retry with exponential backoff.

What you do not want is sync that stops on the first error and waits for the user to retry, or sync that retries the whole batch when half succeeded and half failed. Both produce data loss or duplication. Robust sync handles partial success cleanly, and the device's outbox always reflects ground truth.

Schema migrations have to handle stale offline clients

Offline-first means the device's local schema may be days, weeks, or months behind the server. When the server schema changes, the sync engine has to handle clients still on the old schema — usually by mapping old shapes to new shapes during sync, refusing actions that depend on fields the old client does not have, and surfacing a forced-update flow when the gap is too wide.

We design schemas with this in mind: additive changes are usually safe, breaking changes require versioning, and the sync engine knows the version it is talking to. Apps that ignore this discover the failure mode when a field operator on an old build syncs and corrupts records.

Storage hygiene keeps the device app responsive

Without storage hygiene, the local SQLite grows unbounded. Old completed orders, archived assets, audit history that does not need to live on-device — all of it accumulates and the app slows. The sync engine has to know what to keep on-device and what to evict, with archival to the server, so the device stays responsive.

We typically keep an active rolling window — last 30–90 days of records relevant to the operator's role — and an evict-on-archive policy. Operators rarely need historical records on-device; when they do, the app fetches them on demand and caches briefly.

Our warehouse operators worked through a six-hour ISP outage and the app did not blink. They picked, they shipped, they signed, and when the network came back the dashboard updated like nothing had happened. Before, that outage would have stopped the warehouse cold. Now we treat connectivity as nice-to-have during operations, not required.

— VP Operations, regional 3PL

Frequently asked

What is offline-first mobile architecture? +

Offline-first means the app assumes the network is unreliable by default. Every action writes to local storage first and queues for background sync. Reads come from the local store. The user never waits on the network for an action. When connectivity returns, the sync engine reconciles changes with the server. The pattern is essential for field operators in environments with intermittent or absent signal.

Why use SQLite for mobile offline storage? +

SQLite is durable across crashes, fast on cold start, supports transactions, and ships in every modern mobile platform. It is the right default for offline-first apps. Alternatives — in-memory state, non-transactional key-value stores — produce data loss when the app is killed mid-operation. Field-operator apps get killed by the OS routinely, so durability is not optional. Local SQLite plus a structured outbox queue is the durable foundation.

How are sync conflicts resolved? +

Per-record-type policy declared in the schema. Last-write-wins works for state fields like task status. Server-authoritative reconciliation works for fields the server has ground truth on, like inventory counts. CRDT-style merge works for counters, sets, and collaborative fields where two valid concurrent updates should both be reflected. Implicit last-write-wins for everything is silently wrong for many record types and produces data corruption that surfaces months later.

How are audit trails preserved across offline actions? +

Every action creates an audit row locally at the moment of action, not at the moment of sync. The row captures the device timestamp (when the operator actually did the thing), the action, the operator, the inputs, and the affected record. Sync delivers the audit row along with the data change, preserving both the device timestamp and the server-received timestamp. The audit trail reflects when actions actually happened, not when the device happened to reconnect.

What happens when the device's app version is behind the server schema? +

The sync engine maps old shapes to new shapes during sync where possible, refuses actions that depend on fields the old client does not have, and surfaces a forced-update flow when the schema gap is too wide. Schema design favors additive changes that older clients can ignore safely; breaking changes require versioning. This is essential for offline-first apps because devices can be days, weeks, or months out of date.

How do you keep the local database from growing forever? +

Active rolling window — typically 30 to 90 days of records relevant to the operator's role — plus archival to the server with evict-on-archive policy. Operators rarely need historical records on-device; when they do, the app fetches on demand and caches briefly. Without storage hygiene, the local SQLite grows unbounded, app cold-start time degrades, and operators eventually reinstall the app to recover responsiveness.