Zero-Latency Mobile Apps: Local-First CRDTs in SQLite

Stop treating mobile apps like thin viewports over REST APIs. Here is how we build local-first mobile architectures using embedded SQLite and CRDT state synchronisation.

## The Fragility of the Cloud-First Viewport Most modern mobile applications are built around an assumption that breaks down constantly: the idea of reliable, low-latency internet connectivity. Developers structure apps as lightweight presentation layers wrapped around remote HTTP endpoints. Every screen launch triggers a loading spinner, every form submission locks the user interface behind an optimistic modal, and a trip through a subway tunnel or an elevator ride renders the entire application completely useless. Optimistic UI updates help paper over network delays, but they fall apart when multi-device conflicts arise. If two users edit the same document or assign the same field while disconnected, the server usually falls back on crude 'last-write-wins' strategies, silently blowing away one user's updates. Mobile applications need to run local-first. Read and write operations should execute against an embedded database on the physical device, keeping interactions completely responsive. Network synchronisation should run asynchronously in the background, handling conflicts deterministically through **Conflict-Free Replicated Data Types (CRDTs)**. ## The Architecture of Local-First Mobile A resilient, local-first mobile engine rests on three architectural pillars: 1. **The Device as Single Source of Truth:** The application interface only ever interacts with a local SQLite database embedded on the phone (accessed via high-performance bindings like `op-sqlite` in React Native, native GRDB in Swift, or Room/C++ in Android). 2. **The Network as an Async Replication Pipe:** Network calls are decoupled from the user's primary workflow. Changes are written immediately to a local changesets table, which an offline-resilient sync worker drains whenever the network is accessible. 3. **Deterministic Conflict Resolution via CRDTs:** The sync server is no longer an absolute authority that accepts or rejects writes. Instead, it functions as a lightweight message broker, relaying mutation vectors between nodes that resolve to identical states everywhere. ```swift // Defining a conflict-free state record with vector clocks struct CRDTSyncEntity: Codable { let entityId: UUID let attributeKey: String let value: Data let logicalLamportTimestamp: UInt64 let clientNodeId: UUID } ``` When a conflict occurs—such as a user editing a task title on their tablet while updating its priority on their phone—the client applies a deterministic state merge rule (such as a state-based Observed-Remove Set or a Last-Write-Wins Register backed by Lamport timestamps). ## Replacing ORMs with Embedded Reactive SQLite Traditional mobile database wrappers introduce significant runtime overhead. When dealing with tens of thousands of dynamic records, object-relational mapping (ORM) layer allocations will quickly bog down mobile CPU cycles. To build a zero-latency data pipeline, bypass heavy abstractions and run embedded SQLite compiled directly with custom C extensions for mathematical functions and JSON handling. ### 1. Observable Query Pipelines Instead of manually triggering state re-fetches when network sync workers finish, wire your interface directly to reactive SQL queries. Use SQLite update hooks (`sqlite3_update_hook`) to track changes at the table level. When a sync worker pulls background updates from the server and writes them into local storage, the native update hook fires, updating only the affected query observers. The user interface updates instantaneously, completely free of manual pull-to-refresh routines or intrusive loading spinners. ### 2. Delta-Based Change Enumeration Never send full entity state payloads across mobile connections. Instead, log local mutations into a dedicated SQLite changelog table within the same transaction that updates your local state: * **Local Write Transaction:** 1. Update or insert the target entity inside `records`. 2. Insert an entry into `mutation_log` containing the entity ID, mutated column, new value, Lamport clock counter, and current client UUID. 3. Commit locally in under 2 milliseconds. * **Replication Worker:** Reads un-synced entries from `mutation_log`, batches them into a compressed protobuf payload, and dispatches them to the edge server. Once the server confirms receipt, the local sync marker advances cleanly. ## Surviving Real-World Sync Edge Cases Moving to an offline-first CRDT architecture surfaces unique challenges that do not exist in simple REST environments: ### State Drift and Tombstones In traditional relational architectures, deleting a row means calling `DELETE FROM table`. In a distributed, multi-master CRDT system, doing that will cause the deleted item to reappear the next time an offline node syncs and sends an old update. You must explicitly track deletions using tombstones (`is_deleted = 1` along with a deletion timestamp). Over time, tombstones can build up and consume valuable device storage. Run a scheduled maintenance routine that purges tombstones only after all registered user devices have proven (via confirmed vector clocks) that they have processed the deletion. ### Migration Discipline Across Disconnected Devices What happens if a user leaves an app unopened on an iPad for three months, while their phone continues to receive schema updates and write new data? * Never rely on destructive schema migrations (`DROP TABLE` or column renames). * Store dynamic attributes using hybrid schemas: keep high-cardinality index columns as native SQLite data types, and pack evolving application attributes into a structured JSON column (`data_blob`). * Leverage SQLite's native `json_extract()` functions to index within structured payloads without needing to alter database schemas across out-of-date offline clients. ## Treat the Mobile Device with Respect Smartphones carry multi-core processors, dedicated neural processing engines, and fast flash storage. Building your mobile architecture as an empty viewport that stalls on slow networks does your users a disservice. Build your applications local-first with embedded databases, treat connectivity as an intermittent background process, and design systems that work reliably regardless of network conditions.

Zero-Latency Offline: Building Mobile CRDTs with Embedded SQLite

What clients say

Zero-Latency Offline: Building Mobile CRDTs with Embedded SQLite

What clients say

Related posts