Offline Sync Is Never 'Just SQLite': Battle Scars from Multi-Device Conflict Resolution

A logistics client hired us after their delivery drivers threatened to walk out.

The drivers were delivering heavy equipment across rural Pennsylvania and subterranean warehouse loading docks with zero cell service. They would mark an item as “Delivered & Signed,” capture a signature, and drive out to the highway where 5G reconnected.

Half the time, the signature vanished. Worse, if a dispatcher at headquarters updated the delivery address while the driver was in a basement, the driver’s offline upload would silently overwrite the dispatcher’s change with obsolete data.

The previous developers had sold the client on a simple architecture: “We’ll just cache everything in SQLite and run an upload queue on network reconnect.”

That sentence is why offline-first projects run 300% over budget.

The Last-Write-Wins Trap

Most off-the-shelf sync libraries default to Last-Write-Wins (LWW) based on timestamps. It looks elegant in a slide deck. In production, it is a disaster.

Here is why:

• Device clocks drift. An Android phone set manually three minutes fast will persistently clobber updates from server clocks synchronized via NTP.
• Network latency is not FIFO. Packet A sent at 14:00:01 over an intermittent cell tower can arrive at the ingress load balancer after Packet B sent at 14:00:04.
• Granularity matters. If your sync entity is the entire Order record, updating the delivery signature field should not revert the customer’s phone number that dispatch modified two minutes ago.

When you treat sync as a whole-row replacement, you are creating silent data loss that doesn’t trigger exceptions. Your logs look green, but your customer records are being mutilated.

-- What careless sync logic runs:
UPDATE orders SET 
  status = :status, 
  recipient_notes = :notes, 
  updated_at = :client_timestamp 
WHERE id = :order_id;
-- If :client_timestamp is based on a skewed phone clock, 
-- you just erased server-side dispatcher notes with 15-minute-old cache.

How We Architect Delta Sync Streams

When we take on an offline-first app, we strip whole-record sync out completely. We model state transitions as an append-only operation log.

Instead of synchronizing the state of the order, the mobile client synchronizes intents:

type SyncIntent = {
  id: string; // UUIDv7 (time-ordered)
  entity: 'order';
  entityId: string;
  action: 'SIGN_DELIVERY' | 'UPDATE_DRIVER_NOTES' | 'LOG_GPS_COORDINATE';
  payload: Record<string, unknown>;
  clientTimestamp: number;
  causalParentId: string | null; // Tracks the last known server version
};

When the device comes back online, it doesn’t dump mutated tables into the API. It sends its local pending intent log in a single HTTP POST request.

The backend evaluates each intent through a domain state machine inside an isolated database transaction:

1. Did the server order state advance past a terminal point (e.g. CANCELLED)? If so, reject SIGN_DELIVERY gracefully and notify the driver.
2. Are the field mutations orthogonal? If dispatch changed notes and the driver submitted signatureUrl, merge both without collision.
3. Every operation has an idempotency key. If the cellular connection dies mid-response and the client retries the exact same batch 30 seconds later, the server recognizes the UUIDs and returns HTTP 200 without double-applying side effects.

Tuning Local SQLite on Low-End Devices

A sync engine that works on an iPhone 16 Pro will grind a $150 Android tablet to a halt if you ignore SQLite disk I/O.

When an app receives a 2,000-record sync payload after being offline for three days, running 2,000 separate INSERT queries will freeze the main thread for 14 seconds because of disk fsync calls.

Every SQLite database we provision in our client apps runs with these pragmas enabled from day one:

-- Essential settings for mobile SQLite performance
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -64000; -- 64MB memory cache

Switching the journal mode to Write-Ahead Logging (WAL) allows background sync workers to write new delta rows without locking the read connection currently rendering the UI list.

If you are planning an app that needs to survive elevators, tunnels, oil rigs, or spotty hospital Wi-Fi, reach out to us early. Designing the conflict model on paper costs a fraction of cleaning corrupted production databases six months after launch.