How to Build Offline-First Mobile Apps: Data Sync, Local DBs & Conflict Resolution

Why Traditional Online-Only Apps Fail in the Real World

Most mobile applications are built around a fragile architectural assumption: the device always has a high-speed, stable internet connection. In the real world, users pass through underground subways, enter hospital basements with RF shielding, board airplanes, or operate in rural logistics hubs.

When an online-only app encounters network latency or packet drop, it freezes: spinners spin indefinitely, form submissions fail, and user data is lost. For mission-critical platforms in logistics, clinical healthcare, field service management, and POS systems, data loss directly translates into financial liability.

The Local-First Data Flow & Architecture Blueprint

An offline-first architecture flips the traditional client-server paradigm upside down. Instead of querying a remote API and storing state in transient memory, the application treats the local embedded database as the primary source of truth.

The standard local-first data lifecycle proceeds as follows:

1. Immediate Local Write: When a user creates or updates a record, it is written immediately to the local embedded database (SQLite/WatermelonDB/Realm).
2. Optimistic UI Render: The UI updates instantaneously (< 16 milliseconds) from the local database without waiting for network confirmation.
3. Mutation Enqueue: The mutation is appended to a persistent local sync queue with a unique UUID, timestamp, and entity version tag.
4. Background Synchronization: A background worker detects network connectivity, batches pending mutations, and pushes them to the cloud backend.
5. Server Reconciliation: The backend verifies permissions, runs conflict resolution algorithms, updates the primary database, and broadcasts delta updates back to the client.

Embedded DB Shootout: SQLite vs WatermelonDB vs Realm

Choosing the right local embedded database depends on your framework and dataset volume:

Data Synchronization & Conflict Resolution Patterns

What happens when two users modify the exact same medical record or inventory count while both devices are offline? You need deterministic conflict resolution:

1. Last-Write-Wins (LWW): The record with the latest monotonic timestamp wins. While simple to implement, LWW risks silently overwriting concurrent edits made on different devices.

2. Three-Way Merge with Base Revision: The client sends the new value along with the original base version it read. If the server detects the base version has already changed, it flags a conflict and prompts the user or invokes custom business logic.

3. Conflict-Free Replicated Data Types (CRDTs): The modern gold standard for collaborative offline systems. By structuring data into state-based or operation-based CRDTs, conflicting operations merge mathematically without requiring centralized lock coordination.

Optimistic UI Updates & Persistent Background Queues

To deliver a fluid experience, applications must manage pending mutations using persistent background queues:

• Persistent Queue Storage: Never store pending sync requests in memory. If the user closes the app or the device runs out of battery, in-memory queues vanish. Store pending mutations in an encrypted SQLite table.
• Exponential Backoff & Jitter: When the backend server experiences high load or network connectivity is unstable, the sync worker should retry failed payloads using exponential backoff (e.g., retry after 2s, 4s, 8s, 16s) with randomized jitter to prevent thundering herd spikes.

Building an enterprise app requiring rock-solid offline reliability? Consult our Custom Software Engineering Specialists to architect your synchronization pipelines.

Persistent Mutation Queue Architecture (Dart / Flutter)

Never rely on memory queues. Write pending mutations into persistent on-device SQLite tables:

// Offline Mutation Queue with Drift SQLite & Exponential Backoff
import 'package:drift/drift.dart';

class OfflineMutations extends Table {
  TextColumn get id => text()(); // UUID v4
  TextColumn get endpoint => text()();
  TextColumn get payload => text()(); // JSON string
  IntColumn get attemptCount => integer().withDefault(const Constant(0))();
  DateTimeColumn get createdAt => dateTime()();
  
  @override
  Set<Column> get primaryKey => {id};
}

class SyncManager {
  Future<void> processPendingQueue(MyDatabase db, ApiClient client) async {
    final pending = await (db.select(db.offlineMutations)..orderBy([(t) => OrderingTerm.asc(t.createdAt)])).get();
    
    for (final mutation in pending) {
      try {
        await client.post(mutation.endpoint, data: mutation.payload);
        // Delete upon successful 200 OK server confirmation
        await (db.delete(db.offlineMutations)..where((t) => t.id.equals(mutation.id))).go();
      } catch (err) {
        // Increment attempts and schedule exponential retry
        await (db.update(db.offlineMutations)..where((t) => t.id.equals(mutation.id)))
            .write(OfflineMutationsCompanion(attemptCount: Value(mutation.attemptCount + 1)));
        break; // Stop processing to preserve sequential order
      }
    }
  }
}

Sequential processing ensures that an update to a record is never executed on the backend before the record's creation mutation completes.