Building an Offline-First Mobile App with SQLite and Sync Queues

#frontend #typescript #webdev #react

Building an Offline-First Mobile App with SQLite and Sync Queues

In this tutorial, you’ll learn how to design, implement, and test an offline-first mobile application. We’ll walk through a practical architecture that uses a local SQLite database for offline storage, a robust synchronization queue to reconcile changes with a remote server, and conflict-handling strategies to keep data consistent. The approach works well for iOS and Android apps built with a cross-platform framework like Kotlin/Java or React Native, and it’s adaptable to native Swift/Kotlin projects too.

Applied scenario

You’re building a note-taking app that must function without network connectivity.
Users can create, edit, and delete notes while offline.
When the device goes online, changes sync to the server in a predictable, conflict-aware manner.
You want deterministic behavior for data visibility, even when multiple clients edit the same item.

Outline

Architecture overview
Local data layer with SQLite
Sync queue design and state machine
Conflict resolution strategies
Server API contract and optimistic updates
Implementation: core components (data models, queue, sync engine)
Testing strategy (unit, integration, end-to-end)
Deployment considerations and tips
Example code snippets

Architecture overview
Local store: a SQLite database on the device stores all user data and a replicated schema for syncing metadata.
Sync layer: a dedicated queue that batches changes locally and applies remote updates, with idempotent operations and retry/backoff.
Conflict handling: deterministic rules decide how to merge concurrent edits (last-writer-wins, merged field-level changes, or server-driven resolution).
Server API: REST or GraphQL endpoints that support create/update/delete with idempotent semantics and a “last modified” timestamp or version vector.
Observability: lightweight event bus or reactive streams to propagate changes to the UI without blocking on network calls.
Local data layer with SQLite
Use a single source of truth on-device: a primary table for notes, plus a changes_log or sync_metadata table to track local mutations.
Core tables:
- notes(id TEXT PRIMARY KEY, content TEXT, title TEXT, updated_at DATETIME, deleted BOOLEAN)
- changes_log(id TEXT PRIMARY KEY, operation TEXT, payload TEXT, created_at DATETIME, status TEXT)
Why separate changes_log? It records intent, enabling reliable retries, offline queuing, and replay-safe synchronization.
Sync queue design and state machine
We implement a “sync agent” with a queue of work items. Each item represents a mutation to apply to the server.
Item fields:
- id (UUID)
- type (insert/update/delete)
- local_id (note id)
- payload (JSON blob of the note data)
- attempted_at (timestamp)
- status (pending, in_progress, succeeded, failed)
- remote_version (server version or last_updated_at)
State machine:
- pending -> in_progress when a network call starts
- in_progress -> succeeded/failed depending on server response
- failed -> backoff and retry (exponential)
Idempotence: server endpoints are designed to be idempotent. Replaying the same change should not duplicate data.
Conflict resolution strategies
Field-level conflict policy:
- If two clients edit the same note offline, you can merge by choosing the latest updated_at as the winner for each field, or implement a delta merge if both edited different fields.
Operational conflict policy:
- If a server-side edit occurs while you’re syncing your offline changes, you compare version tokens. If the server has a newer version, you fetch and merge, then retry local changes.
User-facing conflict handling:
- When conflicts are detected, present a resolution UI: compare versions, allow user to pick one, or auto-merge with a clear “conflict resolved by last edit” indicator.
Server API contract and optimistic updates
Endpoints (examples):
- POST /notes with body {id, title, content, updated_at} to create
- PATCH /notes/{id} with {title?, content?, updated_at}
- DELETE /notes/{id}
- GET /notes?since={timestamp} to fetch changes
Optimistic updates:
- Apply local changes immediately in the UI (and SQLite) with a temporary local version, then sync to the server.
- If server rejects (e.g., conflict), roll back or merge based on the chosen strategy and notify the user if needed.
Versioning:
- Use optimistic locking with a version field or use updated_at timestamps for conflict detection.
Implementation: core components
Data models (pseudo-typed)
- Note: id, title, content, updated_at, is_deleted
- Change: id, type (insert/update/delete), note_id, payload, created_at, status
SQLite helper
- Initialize database with tables notes and changes_log
- CRUD helpers with timestamps
Sync engine
- enqueueChange(type, note) creates a Change row and marks it as pending
- syncLoop runs in background, processes pending changes in order
- network layer handles REST calls with backoff
- on success: mark Change as completed, update note with remote_version
- on conflict: fetch remote note, merge locally, re-enqueue pending changes
UI/data binding
- Observe changes_log or notes table to refresh UI
- Show offline status and synchronization state badge

Code snippets (illustrative, language-agnostic for clarity)

SQLite table creation (SQL) CREATE TABLE notes ( id TEXT PRIMARY KEY, title TEXT, content TEXT, updated_at DATETIME, is_deleted BOOLEAN DEFAULT 0 );

CREATE TABLE changes_log (
id TEXT PRIMARY KEY,
type TEXT, insert, update, delete
note_id TEXT,
payload TEXT,
created_at DATETIME,
status TEXT pending, in_progress, succeeded, failed
);

Enqueueing a change (pseudocode)
function enqueueChange(type, note) {
const change = {
id: generateUuid(),
type: type,
note_id: note.id,
payload: JSON.stringify(note),
created_at: now(),
status: 'pending'
};
db.insert('changes_log', change);
return change.id;
}
Sync loop (pseudocode)
async function syncLoop() {
while (true) {
const item = db.queryOne("SELECT * FROM changes_log WHERE status='pending' ORDER BY created_at ASC LIMIT 1");
if (!item) {
await sleep(1000);
continue;
}
db.update('changes_log', { id: item.id, status: 'in_progress' });
try {
const note = JSON.parse(item.payload);
let response;
if (item.type === 'insert' || item.type === 'update') {
response = await api.patchOrPostNote(note);
} else if (item.type === 'delete') {
response = await api.deleteNote(note.id);
}
// On success
db.update('changes_log', { id: item.id, status: 'succeeded' });
// Optional: update local note version from response
if (response && response.updated_note) {
upsertNoteFromServer(response.updated_note);
}
} catch (err) {
// Conflict or network error
const backoff = computeBackoff(item.attempted_at);
db.update('changes_log', { id: item.id, status: 'failed', attempted_at: now() });
await sleep(backoff);
// Re-enqueue automatically
db.update('changes_log', { id: item.id, status: 'pending' });
}
}
}
Conflict resolution hook
async function resolveConflict(localNote, remoteNote) {
// Simple strategy: field-wise last-write-wins
const merged = {
id: localNote.id,
title: compareField(localNote.title, remoteNote.title),
content: compareField(localNote.content, remoteNote.content),
updated_at: max(localNote.updated_at, remoteNote.updated_at)
};
// Decide policy: auto-merge or prompt user
return merged;
}
function compareField(a, b) {
// if both edited, pick the one with later updated_at
// placeholder: return the later of a or b
return (new Date(a.updated_at) > new Date(b.updated_at)) ? a.value : b.value;
}

Notes:

The code above is schematic. In a real app, implement robust type checks, error handling, and ensure JSON payloads are compact.
Use prepared statements for SQLite to prevent SQL injection and improve performance.
Consider using an event bus (e.g., RxJS or platform-native) to push changes to the UI when the notes table updates.

Testing strategy
Unit tests
- Test enqueueChange creates log entries correctly
- Test mergeConflict function behavior with crafted notes
Integration tests
- Mock server responses to exercise syncLoop: success, transient network failure, and server-side conflicts
- Verify that on conflict, local changes are merged consistently
End-to-end tests
- Simulate offline and online transitions on a device or emulator
- Validate UI reflects offline data, then updates after sync
Data integrity tests
- Ensure no data corruption when notes are edited offline and reloaded online
- Validate idempotent replay of the same change
Deployment considerations and tips
Choose a lightweight local DB library that suits your stack and provides good migration support.
Implement schema migrations carefully; never drop a table in a migration without preserving data.
Use backoff and jitter for retries to avoid thundering herd problems on the server.
Provide a clear user experience for conflicts: offer resolution options or automated non-destructive merges.
Log sync metrics (queued items, success rate, average sync latency) to monitor reliability in production.
Example project structure
src/
- db/
- migrations/
- schema.sql
- sqliteClient.ts
- models/
- Note.ts
- ChangeLog.ts
- sync/
- syncEngine.ts
- conflictResolver.ts
- api/
- serverApi.ts
- ui/
- NoteList.tsx
- NoteEditor.tsx
tests/
- unit/
- integration/
- e2e/
Quick-start checklist
Set up SQLite with notes and changes_log tables.
Implement a sync engine that processes the queue with proper backoff.
Create server endpoints that are idempotent and support optimistic locking.
Implement conflict resolution and a simple UI for conflicts if needed.
Add tests for offline scenarios and conflict cases.
Instrument metrics and logging to observe sync behavior in the field.

If you’d like, I can tailor this tutorial to a specific framework (React Native, Flutter, native iOS/Android) and provide a concrete, runnable codebase outline with starter snippets for that stack. Would you prefer a React Native example with TypeScript, or a Flutter/D Dart example?

Rizwan Saleem | https://rizwansaleem.co