Prompt
How do I build an offline-first mobile app backend that syncs changes when users reconnect?
Latest observation
To build an offline-first mobile backend that syncs reliably when users reconnect, design around local writes first, append-only change capture, and conflict-aware synchronization.
1) Core architecture
Use these layers:
- Mobile app local store: SQLite / Realm / WatermelonDB / IndexedDB-style equivalent
- Sync queue on device: stores user mutations while offline
- Backend API: accepts batches of changes and returns server updates
- Server-side source of truth: relational DB or document store
- Change log / event store: records every write in order
- Conflict resolver: determines how overlapping edits are merged
The main rule: the app always reads and writes locally first, then syncs later.
2) Data model for sync
Each record should carry metadata such as:
id: globally unique ID, usually UUIDupdated_at: last modification timeversion: monotonically increasing integer or revision tokendeleted: soft delete flaglast_modified_by: optional for debugging/conflictssync_state: local-only state on device
For better syncing, keep a separate change log table on the server:
Example change record:
{
"change_id": "chg_123",
"entity_type": "note",
"entity_id": "note_456",
"operation": "update",
"version": 17,
"timestamp": "2026-11-04T12:00:00Z",
"payload": {
"title": "New title",
"body": "Edited offline"
}
}
3) Sync protocol
A solid sync flow usually has two phases:
A. Push local changes
The client sends all queued local mutations since the last successful sync.
Example request:
{
"device_id": "dev_abc",
"user_id": "user_1",
"base_sync_token": "sync_9001",
"changes": [
{
"client_change_id": "local_1",
"entity_type": "task",
"entity_id": "task_100",
"operation": "update",
"base_version": 4,
"payload": { "done": true }
}
]
}
The server should:
- validate the user
- check whether the client’s
base_versionmatches current version - apply changes if possible
- detect conflicts if not possible
- store accepted changes
- return results
B. Pull server changes
The server returns all changes since the client’s last sync token.
Example response:
{
"new_sync_token": "sync_9002",
"applied": [
{
"client_change_id": "local_1",
"entity_id": "task_100",
"server_version": 5
}
],
"conflicts": [],
"server_changes": [
{
"entity_type": "task",
"entity_id": "task_200",
"operation": "create",
"version": 12,
"payload": { "title": "Remote update" }
}
]
}
Use the sync token to support incremental sync and avoid full table downloads.
4) Conflict handling strategies
Conflicts happen when:
- two devices edit the same object offline
- one device edits an object that was deleted elsewhere
- a stale client overwrites newer server data
Common strategies:
Option 1: Last write wins
Simplest approach. Server accepts the most recent write by timestamp/version.
Pros:
- easy to implement
Cons:
- can silently lose user changes
Option 2: Optimistic concurrency with merge
Reject writes if the base version is stale, then let the client merge and resubmit.
Pros:
- safer
- predictable
Cons:
- more work in app and backend
Option 3: Field-level merge
Merge non-overlapping fields automatically.
Example:
- Device A changes
title - Device B changes
priority - both can be merged
Option 4: CRDTs / operational transforms
Best for collaborative editing and complex real-time shared data.
Pros:
- robust under concurrent edits
Cons:
- significantly more complex
For most mobile apps, start with optimistic concurrency + field-level merge.
5) Client-side offline workflow
On device:
- User edits data
- Save immediately to local DB
- Add mutation to sync queue
- Mark item as pending sync
- When network returns, batch send queued mutations
- Update local records with server response
- Remove acknowledged queue entries
Important:
- make local writes idempotent
- retry safely
- persist the queue across app restarts
6) Backend implementation tips
Use idempotency keys
Clients may retry the same mutation. Include a client_change_id so the server can detect duplicates.
Make endpoints batch-friendly
Prefer:
POST /sync/pushGET /sync/pull?since=token
Batching reduces network overhead and battery usage.
Store soft deletes
Hard deletes make sync harder. Use:
deleted_at- tombstone records
This helps other devices learn that something was deleted.
Return authoritative server state
After a sync, the client should receive:
- server-assigned IDs
- new versions
- resolved fields
- deletions
7) Schema example
For a table like notes:
CREATE TABLE notes (
id UUID PRIMARY KEY,
user_id UUID NOT NULL,
title TEXT NOT NULL,
body TEXT,
version INTEGER NOT NULL DEFAULT 1,
deleted BOOLEAN NOT NULL DEFAULT FALSE,
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
And a change log:
CREATE TABLE note_changes (
change_id UUID PRIMARY KEY,
note_id UUID NOT NULL,
user_id UUID NOT NULL,
operation TEXT NOT NULL,
base_version INTEGER,
payload JSONB,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
8) Sync token design
Your sync token can be:
- a timestamp
- a monotonically increasing global sequence
- a per-user cursor
- a changelog offset
Best practical choice: a server-generated cursor/sequence.
Example:
- client last synced at cursor
10234 - server returns all changes after
10234 - server returns new cursor
10480
This is safer than using timestamps alone, which can be ambiguous.
9) Handling deletions and restores
Use tombstones:
- delete = mark as deleted, don’t immediately remove row
- sync other devices about the deletion
- optionally purge tombstones later after a retention window
If you permanently delete too soon, offline clients can resurrect stale records or fail to reconcile.
10) Security and correctness
- authenticate every sync request
- scope all data by user/tenant
- sign or validate device sessions
- rate-limit sync endpoints
- validate payloads strictly
- never trust client timestamps for ordering
- keep an audit trail for conflict/debugging
11) Practical MVP approach
If you want something shippable fast:
- Local SQLite on device
- UUIDs for all entities
versioncolumn on server rowsPOST /syncaccepting batched mutationsGET /syncreturning changes since cursor- Optimistic concurrency checks
- Soft deletes
- Simple conflict policy:
- reject stale writes
- client refetches and retries after merge
This is usually enough for notes, tasks, inventory, field apps, and many consumer apps.
12) When to use a managed sync framework
Consider a managed solution if you want less infrastructure work:
- Firebase / Firestore
- Supabase Realtime + Postgres
- Couchbase Lite + Sync Gateway
- Realm Device Sync
- WatermelonDB + custom backend
- PowerSync
These can save time, but evaluate:
- conflict behavior
- offline query support
- cost
- vendor lock-in
- whether your data model fits their sync model
If you want, I can also provide:
- a reference API design for
/sync/pushand/sync/pull, - a Postgres schema for offline sync,
- or a sample implementation in Node.js / FastAPI / Go.