Building Real-Time Sync with a Mobile Database and Cloud BackendReal-time synchronization between a mobile database and a cloud backend is a common requirement for modern apps that must work offline, provide immediate updates to users, and keep data consistent across devices. This article walks through architecture patterns, data modeling, conflict resolution strategies, security concerns, common tooling, and a practical implementation example you can adapt to your stack.
Why real-time sync matters
Real-time sync improves user experience by:
- Reducing latency: Users see updates instantly without manual refresh.
- Enabling collaboration: Multiple users can edit shared data and see changes live.
- Supporting offline-first: Local changes are preserved and propagated when connectivity returns.
Core components
A robust real-time sync solution typically includes:
- A local mobile database (persisted on device) — examples: SQLite, Realm, Couchbase Mobile, WatermelonDB, or IndexedDB in hybrid apps.
- A sync engine on the client that tracks changes, applies remote updates, and resolves conflicts.
- A cloud backend that stores the canonical data, accepts client changes, propagates updates, and manages authentication/authorization.
- A transport layer to move changes in near-real-time — options: WebSockets, MQTT, Server-Sent Events (SSE), or push notifications for wake-up events.
- Optional middleware or message broker (e.g., Kafka, Redis Streams) on the server for scaling and fan-out.
Architectural patterns
- Event-sourced sync (append-only change feeds)
- Clients send and consume change events.
- Pros: granular audit trail, easier replay and debugging.
- Cons: requires careful compaction and handling of evolving schemas.
- State-based sync (CRDTs)
- Clients merge convergent replicated data types automatically.
- Pros: strong eventual consistency without custom conflict logic.
- Cons: limited to data types that fit CRDT models; can be complex to design.
- Operational transformation (OT)
- Common in collaborative editors; transforms operations to maintain intent.
- Pros: good for text-rich collaborative editing.
- Cons: complex to implement and reason about for general data models.
- Sync through a central backend with optimistic updates
- Clients make local changes immediately and send them to backend; backend validates and broadcasts accepted changes.
- Pros: simpler to implement; works with existing REST/WebSocket backends.
- Cons: conflicts resolved server-side; requires rollback/compensation on clients when rejected.
Data modeling for sync
- Use unique, stable identifiers (UUIDv4 or ULID) for entities generated on clients.
- Include metadata with each record: version (incremental or vector clocks), last_modified timestamp (ISO 8601 UTC), origin_id (client ID), and tombstone flag for deletes.
- Design compact change records: operation type (create/update/delete), entity ID, changed fields, version, and a small signature if needed for integrity.
Example change record (JSON):
{ "op": "update", "id": "a1b2c3d4-5678-90ab-cdef-1234567890ab", "changes": { "title": "New title", "status": "done" }, "version": 42, "last_modified": "2025-08-31T12:34:56.789Z", "origin": "client-7" } Conflict resolution strategies
- Last-Write-Wins (LWW)
- Compare timestamps or versions; pick the highest.
- Simple but can lose user intent.
- Merge by field
- Merge at field granularity: if two edits touch different fields, combine them.
- Better fidelity; needs per-field metadata.
- Client-priority or Server-authoritative
- Prefer changes from a specific role (e.g., server rules override clients).
- Useful for enforcing business invariants.
- Application-level reconciliation
- Present conflicts to users with a UI to resolve (best for important or ambiguous data).
- CRDTs or OT
- Use algorithmic resolution to converge automatically.
Use vector clocks or version vectors when causality matters. Vector clocks: each client stores its counter; merges compare vectors to detect concurrent updates.
Transport: choosing a real-time channel
- WebSockets: general-purpose, low-latency, bidirectional. Good for most apps.
- MQTT: lightweight pub/sub, efficient for mobile and intermittent connectivity.
- Server-Sent Events: server->client push only; simpler but unidirectional.
- Push Notifications: for background wakeups and low-power devices — not reliable for transmitting full data.
- Hybrid: WebSockets for active sessions, push notifications to wake the app and trigger sync when in background.
Implement an exponential backoff reconnect with jitter and handle token refresh for authenticated connections.
Security and access control
- Authenticate connections with short-lived tokens (OAuth2 access tokens, JWT with short expiry) and refresh tokens via secure channels.
- Encrypt transport (TLS) and encrypt sensitive local data at rest (platform keystore or full-disk encryption + field-level encryption).
- Authorize operations server-side; never trust client-sent permissions.
- Rate-limit to prevent abuse; validate and sanitize incoming change records.
Scalability considerations
- Use a message broker (Kafka, Redis Streams, Pulsar) to decouple sync ingestion and fan-out.
- Partition topics/shards by user or tenant to reduce cross-talk.
- Use presence channels sparingly. Broadcast only relevant updates (filter by subscriptions, interests, or queries).
- Implement checkpointing and change cursors so clients can resume from last-known position without replaying entire history.
- Support pagination or chunked pull for large datasets and backpressure controls.
Tooling and existing solutions
- CouchDB/Couchbase Mobile + Sync Gateway — built-in sync with conflicts, replication, and offline support.
- Firebase Realtime Database / Firestore — managed, real-time sync with client SDKs and offline caching.
- Realm Sync — real-time sync integrated with Realm local DB.
- WatermelonDB + custom sync — fast local DB with an approach for sync over HTTP/WS.
- GraphQL Subscriptions / Hasura — use subscriptions for near-real-time updates; combine with local cache.
- CRDT libraries — Yjs (web), Automerge (JS), delta-crdts (various languages).
Pick based on needs: managed vs self-hosted, data model fit, and offline guarantees.
Practical implementation example (WebSocket + local SQLite)
This example outlines steps and pseudocode for a mobile app using a local SQLite (or Room/CoreData) DB, a small sync queue, and a WebSocket to a cloud service.
- Local change capture
- Intercept all writes through a data access layer that:
- Applies change to local DB immediately (optimistic).
- Appends a change record to an outbox table with metadata and pending = true.
- Outbox sender
- Background worker reads pending outbox records, batches them, and sends over WebSocket (or HTTP if WS unavailable).
- Mark sent with a client-generated sequence number and await server ack.
- Server processing
- Server validates, applies change to canonical store, assigns server version, and writes an event to a change stream.
- Server sends an ack back including canonical version and any transformed data.
- Client ack handling
- On ack, mark outbox record as synced, update local record’s version to server version.
- Incoming remote updates
- Server broadcasts change events to subscribed clients (based on user/tenant).
- Client receives event, checks local version:
- If local version is older and not conflicting, apply update.
- If conflicting (concurrent edit), run conflict resolution (e.g., merge fields or queue for manual resolution).
- Reconciliation and replay
- On reconnect or cold-start, client requests change stream from last known cursor to catch up.
Pseudocode (high-level):
// when user edits async function saveLocal(entity) { entity.id = entity.id || generateUUID(); entity.last_modified = now(); entity.version = entity.version || 0; await db.run('UPDATE OR INSERT ...', entity); await db.run('INSERT INTO outbox (...) VALUES (...)', { id: generateUUID(), entityId: entity.id, op: 'update', payload: entity, pending: 1 }); scheduleOutboxFlush(); } // outbox flush async function flushOutbox() { const pending = await db.all('SELECT * FROM outbox WHERE pending=1 LIMIT 50'); if (pending.length === 0) return; const packet = { clientId, seq: nextSeq(), changes: pending.map(p => p.payload) }; ws.send(JSON.stringify(packet)); } Testing, monitoring, and observability
- Simulate network partitions, latency spikes, and concurrent edits with test harnesses.
- Monitor metrics: sync latency, queue depth, conflict rate, failed ops, reconnects per client.
- Log change events with correlation IDs and expose dashboards for operational insights.
- Provide client-side diagnostics to help with user support (e.g., last-sync time, pending changes count).
UX considerations
- Show sync status (synced/pending/failed) subtly in the UI.
- Allow users to resolve critical conflicts with a clear UI and version history.
- Avoid blocking the main UI on sync; prefer background sync with optimistic updates.
- Provide a manual “Sync now” option for power users.
Example trade-offs checklist
| Requirement | Easier approach | Stronger approach |
|---|---|---|
| Offline support | Local cache + periodic pull | Full offline-first DB with outbox/replication |
| Conflict handling | LWW timestamps | CRDTs or field-level merges |
| Scalability | WebSocket with single server | Message broker + shards + fan-out |
| Managed vs self-hosted | Firebase/Realm | CouchDB, custom sync stack |
Final notes
Designing real-time sync is balancing correctness, user experience, complexity, and operational cost. Start with a simple optimistic-sync model with a clear outbox and server acks; iterate toward stronger conflict handling (merge rules, CRDTs) and better scaling as your user base and concurrency needs grow.
If you want, I can: provide starter code for a specific platform (iOS/Android/React Native), design a message schema for your domain, or map this architecture to a specific backend (Firebase, Couchbase, Hasura). Which would you like next?
Leave a Reply