Scalable Delta Synchronization for Mobile Apps

Local-first CRDTs break under complex enterprise relational constraints. Here is how we build high-concurrency delta synchronization using SQLite and hybrid vector clocks.

## The Naive Optimism of Offline-First The software industry has spent the last few years infatuated with local-first architectures. The pitch is undeniably seductive: store everything in a client-side database, synchronize effortlessly over Conflict-Free Replicated Data Types (CRDTs), and provide your users with zero-latency, offline-capable mobile applications. Then you attempt to implement a real-world enterprise domain model: multi-table foreign key constraints, fine-grained access control policies, inventory allocations, and accounting ledgers. Suddenly, the naive local-first dream shatters. Pure CRDTs are exceptional for collaborative text canvases, simple key-value document stores, and personal note-taking apps. But they buckle under high-volume relational dependencies. When a client performs offline actions that contradict permissions modified on the server an hour earlier, simple Last-Write-Wins (LWW) or operational CRDT merges leave your relational data corrupt or inconsistent. Building resilient, offline-capable mobile applications requires an architecture built on deterministic delta synchronization, client-side relational staging, and authoritative server-side reconciliation. ## The Engine: Embedded SQLite with Change-Set Tracking Forget storing complex business entities in mobile Key-Value stores or unindexed document databases. The gold standard for mobile persistence remains an embedded relational engine: SQLite (via SQLCipher for at-rest encryption). To make SQLite sync-capable without destroying device performance, you must avoid whole-record sync. Uploading and downloading full JSON entities wastes cellular bandwidth, drains battery life, and makes conflict resolution nearly impossible. You must synchronize operational deltas. ```typescript // Client-side delta capture contract export interface TableDelta { id: string; tableName: 'invoices' | 'invoice_items' | 'payments'; operation: 'INSERT' | 'UPDATE' | 'DELETE'; rowId: string; changedValues: Record<string, unknown>; vectorClock: number; timestamp: number; } export async function stageLocalChange( db: SQLiteDatabase, delta: Omit<TableDelta, 'id' | 'vectorClock'> ): Promise<void> { // 1. Write the change to an isolated operational outbox table await db.executeAsync( `INSERT INTO sync_outbox (tableName, operation, rowId, changedValues, clientTimestamp) VALUES (?, ?, ?, ?, ?)`, [delta.tableName, delta.operation, delta.rowId, JSON.stringify(delta.changedValues), delta.timestamp] ); // 2. Apply locally with an uncommitted/optimistic flag await db.executeAsync( `UPDATE ${delta.tableName} SET _sync_status = 'PENDING' WHERE id = ?`, [delta.rowId] ); } ``` ### The Operational Outbox Pattern Whenever a user mutates data inside the mobile app: 1. The primary record is updated optimistically in the local SQLite table, flagged with a metadata state of `_sync_status = 'PENDING'`. 2. An atomic record is written to a local `sync_outbox` table documenting the exact columns changed, the previous version, and the new value. 3. The UI displays the optimistic update immediately, providing instant perceived performance. 4. A background sync worker drain the `sync_outbox` sequentially over an encrypted, compressed protocol buffer connection. ## Solving Concurrency with Hybrid Vector Clocks Wall-clock timestamps on mobile devices are completely untrustworthy. Users travel across time zones, device clocks drift, and operating systems periodically recalibrate time via NTP, producing backwards-traveling timestamps. Relying on `timestamp > last_synced_at` will inevitably drop records or overwrite server mutations. Instead, use a hybrid logical clock (HLC) combining physical time with a monotonic incrementing counter, tracked per-client and per-server partition. ### Conflict Resolution Strategies in Relational Contexts When a client submits an operational delta, the central server evaluates the incoming clock against the record's current server-side state vector: * **Clean Forward Merge**: If the incoming delta's baseline clock matches the server's current state, apply the mutation, advance the record version, and notify the client with an `ACK`. * **Non-Overlapping Field Merge**: If both client and server mutated the record, but modified orthogonal columns (e.g., client updated `shipping_phone`, server updated `billing_address`), the server merges the values deterministically and returns the combined state to the client. * **Hard Conflict with Invalidation**: If both parties modified the same immutable field, the authoritative server rejects the client's delta, executes a rollback mutation in the client's local SQLite database, and surfaces a deterministic reconciliation notice to the user. ## Practical Rules for Mobile Sync Implementations To ensure your mobile persistence layer doesn't grind to a halt under real-world usage, adhere to these operational guidelines: * **Chunk Sync Payloads**: Never allow the outbox sync engine to send unbounded batches. Limit sync transmissions to chunks of 50 operations per HTTP/2 or WebTransport frame to minimize cellular packet loss retransmissions. * **Isolate Transactions on Background Threads**: Database transactions on mobile must execute off the main thread. Run synchronization logic within a dedicated background worker or native thread to eliminate frame drops and UI stuttering. * **Tombstones Over Hard Deletions**: Never execute hard `DELETE` queries on the client while offline. Convert them to soft deletions (`deleted_at IS NOT NULL`). Replicate the tombstone to the server, and only purge records during routine vacuum jobs after sync confirmation. Treating offline synchronization as an explicit distributed systems challenge rather than an afterthought ensures your mobile app stays robust, fast, and completely reliable regardless of network stability.

Beyond Local-First: Scalable Delta Synchronization for Relational Mobile Engines

What clients say

Beyond Local-First: Scalable Delta Synchronization for Relational Mobile Engines

What clients say

Related posts