Building Offline-First Flutter Apps: SQLite, Sync Queues & Conflict Resolution

┌─────────────────────────────────────────────────────────────────────────────┐
│                              UI LAYER (Flutter Widgets)                      │
│  • Listens to reactive SQLite streams (ValueNotifier / Bloc / Riverpod)      │
│  • Emits optimistic user actions immediately (sub-16ms render response)      │
├─────────────────────────────────────────────────────────────────────────────┤
│                         REPOSITORY LAYER (Domain Contracts)                  │
│  • Generates local UUIDv4 identifiers prior to database write               │
│  • Commits changes synchronously into SQLite transactional tables           │
│  • Enqueues serialized change payloads into the persistent sync queue       │
├─────────────────────────────────────────────────────────────────────────────┤
│                        LOCAL STORAGE ENGINE (SQLite / Drift)                │
│  • Serves as the immutable single source of truth on the physical device    │
│  • Tracks synchronization flags, mutation timestamps, and tombstone records │
├─────────────────────────────────────────────────────────────────────────────┤
│                       SYNCHRONIZATION ENGINE (Dart Isolate)                  │
│  • Runs inside a dedicated background worker isolate to prevent frame drops │
│  • Manages exponential backoff retries, batching, and conflict resolution   │
└─────────────────────────────────────────────────────────────────────────────┘

-- Base Business Entity Table (e.g., Work Orders)
CREATE TABLE work_orders (
  id           TEXT PRIMARY KEY,
  client_id    TEXT NOT NULL,
  title        TEXT NOT NULL,
  status       TEXT NOT NULL DEFAULT 'draft',
  payload_json TEXT NOT NULL,
  updated_at   TEXT NOT NULL,
  
  -- Synchronization Control Metadata
  sync_status  TEXT NOT NULL DEFAULT 'pending', -- 'pending' | 'synced' | 'conflict'
  sync_queue   INTEGER NOT NULL DEFAULT 1,       -- 1 = dirty local record requiring upstream sync
  deleted_at   TEXT                              -- Soft-delete timestamp (Tombstone pattern)
);

-- Persistent Transactional Sync Queue
CREATE TABLE sync_queue (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  table_name   TEXT NOT NULL,
  record_id    TEXT NOT NULL,
  operation    TEXT NOT NULL,                   -- 'insert' | 'update' | 'delete'
  payload      TEXT NOT NULL,                   -- Normalized JSON payload for upstream ingestion
  created_at   TEXT NOT NULL,
  attempts     INTEGER NOT NULL DEFAULT 0,
  last_error   TEXT
);

-- Index for high-speed sequential queue processing
CREATE INDEX idx_sync_queue_pending ON sync_queue(created_at) WHERE attempts < 5;

class WorkOrderRepository {
  final AppDatabase _db;
  final SyncQueueService _syncQueue;

  Future<WorkOrder> createWorkOrder(WorkOrder draft) async {
    // 1. Generate client-side UUIDv4 to eliminate server ID dependencies
    final entity = draft.copyWith(
      id: const Uuid().v4(),
      syncStatus: 'pending',
      syncQueue: 1,
      updatedAt: DateTime.now().toUtc().toIso8601String(),
    );

    // 2. Commit transaction to local SQLite engine
    await _db.workOrders.insert(entity);

    // 3. Register payload into the persistent sync queue
    await _syncQueue.enqueue(
      tableName: 'work_orders',
      recordId: entity.id,
      operation: 'insert',
      payload: jsonEncode(entity.toJson()),
    );

    // 4. Return immediately to the UI layer (sub-16ms response)
    return entity;
  }

  // All UI reads query the local SQLite stream
  Stream<List<WorkOrder>> watchWorkOrders() {
    return _db.workOrders.watchActiveOrders();
  }
}

class SyncEngine {
  final SyncQueue _queue;
  final ApiClient _api;
  final AppDatabase _db;

  Future<void> processQueueBatch() async {
    final pendingItems = await _queue.fetchPendingBatch(limit: 25);
    if (pendingItems.isEmpty) return;

    for (final item in pendingItems) {
      try {
        final serverResponse = await _api.pushMutation(
          table: item.tableName,
          operation: item.operation,
          payload: jsonDecode(item.payload),
        );

        // Mark local record as confirmed and synced
        await _db.markRecordSynced(
          table: item.tableName,
          id: item.recordId,
          versionToken: serverResponse.versionToken,
        );

        // Remove processed item from queue
        await _queue.remove(item.id);
      } on ConflictException catch (e) {
        await _resolveConflict(item, e.serverRecord);
      } catch (e) {
        await _queue.recordFailure(item.id, error: e.toString());
      }
    }
  }
}