Offline-First Flutter Architecture with Drift & Outbox

1. The Illusion of Connectivity: Why Cache-and-Retry Approaches Fail

Most mobile applications treat network connectivity as a guaranteed precondition for user interaction. In consumer CRUD applications, a transient failure displays a generic snackbar asking the user to 'Try again later.' In mission-critical enterprise systems—such as retail POS checkout counters, mobile medical clinics, or warehouse logistics—this failure model is catastrophic.

When a cashier completes a sale in a basement retail store or a paramedic records patient vitals in an underground ambulance bay, connectivity drops are not exceptions; they are normal operating conditions. Naive HTTP caching layers and uncoordinated retry loops break down under these conditions. If an app attempts to write directly to a REST API while offline, queued memory requests are lost when the operating system kills the process to reclaim RAM.

Worse, uncoordinated retries over unstable edge networks frequently lead to the 'dual-write' anomaly: the backend successfully processes a mutation, but the network drops before the mobile client receives the HTTP response. If the client retries blindly without idempotency, the customer is billed twice or inventory is decremented multiple times.

True offline-first engineering flips the dependency graph: the local embedded database (SQLite via Drift) serves as the sole, authoritative source of truth for presentation state and immediate writes. The network functions solely as an asynchronous background synchronization mechanism.

2. Atomic Local Persistence: The Transactional Outbox Pattern in Drift

The core operational challenge of offline-first mobile apps is ensuring that a local domain mutation and its corresponding synchronization task never get decoupled. If a transaction writes an invoice to the local database but fails to enqueue the sync task before the application terminates, the database enters a split-brain state where local data never reaches the cloud.

To guarantee consistency on mobile devices, we implement the Transactional Outbox Pattern inside SQLite using Drift. Every user mutation executes within a single atomic SQLite transaction (db.transaction()) that writes both the updated business record and an operational outbox entry to a dedicated sync_outbox table.

A crucial architectural rule for offline-first systems is client-side identity generation. Instead of waiting for a remote PostgreSQL auto-incrementing integer or server-assigned ID, the mobile client generates a cryptographically secure UUIDv4 for every new entity. This allows deep relational entity graphs—such as an invoice, its twenty line items, payment splits, and audit logs—to be assembled offline with full foreign key constraints intact.

// Drift Table Definitions: Local Entity and Transactional Outbox
class Invoices extends Table {
  TextColumn get id => text()(); // Client-generated UUIDv4
  TextColumn get customerName => text()();
  RealColumn get totalAmount => real()();
  TextColumn get status => text()(); // 'draft' | 'paid' | 'cancelled'
  BoolColumn get isDirty => boolean().withDefault(const Constant(true))();
  DateTimeColumn get createdAt => dateTime()();
  DateTimeColumn get deletedAt => dateTime().nullable()(); // Tombstone flag

  @override
  Set<Column> get primaryKey => {id};
}

class SyncOutbox extends Table {
  TextColumn get id => text()(); // Unique outbox operation ID (UUIDv4)
  TextColumn get entityType => text()(); // e.g., 'invoice'
  TextColumn get entityId => text()();
  TextColumn get operation => text()(); // 'INSERT' | 'UPDATE' | 'DELETE'
  TextColumn get payloadJson => text()();
  DateTimeColumn get clientTimestamp => dateTime()();
  TextColumn get syncStatus => text().withDefault(const Constant('pending'))(); // pending | in_flight | synced | failed
  IntColumn get retryCount => integer().withDefault(const Constant(0))();
  TextColumn get idempotencyKey => text().unique()();
  IntColumn get sequenceNo => integer()(); // Microseconds timestamp preserving write order

  @override
  Set<Column> get primaryKey => {id};
}

// Atomic DAO Mutation: Invariant Guarantee
class InvoiceDao extends DatabaseAccessor<AppDatabase> {
  InvoiceDao(super.db);

  Future<String> recordSaleWithOutbox({
    required String customerName,
    required double totalAmount,
  }) async {
    final now = DateTime.now().toUtc();
    final invoiceId = const Uuid().v4();
    final operationId = const Uuid().v4();
    final idempotencyKey = const Uuid().v4();

    // Atomic SQLite Transaction: Guarantees zero split-brain states
    await db.transaction(() async {
      // 1. Commit business entity locally
      await db.into(db.invoices).insert(
        InvoicesCompanion.insert(
          id: invoiceId,
          customerName: customerName,
          totalAmount: totalAmount,
          status: 'paid',
          isDirty: const Value(true),
          createdAt: now,
        ),
      );

      // 2. Commit synchronization intent to outbox
      await db.into(db.syncOutbox).insert(
        SyncOutboxCompanion.insert(
          id: operationId,
          entityType: 'invoice',
          entityId: invoiceId,
          operation: 'INSERT',
          payloadJson: jsonEncode({
            'id': invoiceId,
            'customer_name': customerName,
            'total_amount': totalAmount,
            'status': 'paid',
            'created_at': now.toIso8601String(),
          }),
          clientTimestamp: now,
          idempotencyKey: idempotencyKey,
          sequenceNo: now.microsecondsSinceEpoch,
        ),
      );
    });

    // Reactive UI: Drift query streams update the screen instantaneously (0ms lag)
    return invoiceId;
  }
}

3. Orchestrating the Sync Engine: Delta Pushes, Pull Cursors, and Idempotency

Once mutations are safely persisted to disk, the background synchronization engine assumes responsibility for cloud replication. The sync lifecycle follows a disciplined sequence: Push Outbox mutations first, then Pull Server Deltas.

The Outbox Push Worker retrieves pending records ordered by sequence_no, leases them with an in_flight status to prevent concurrent worker races, and dispatches them in batched HTTP payloads. Crucially, each request transmits its unique idempotencyKey in the 'X-Idempotency-Key' HTTP header. If the mobile device loses cellular reception while waiting for the server's acknowledgment, the worker retries using exponential backoff with jitter. When the backend receives the retransmitted request, its idempotency middleware recognizes the key and returns the previously processed result without re-executing business logic.

The Delta Pull Worker synchronizes remote changes down to the mobile device using opaque monotonic server cursors rather than raw device timestamps. Client device clocks frequently suffer from skew, manual tampering, or battery saving time offsets. By requesting remote changes where server_sequence > last_acknowledged_cursor, the client ingests newly committed remote records and tombstone deletions cleanly, updating local Drift tables inside a transactional block and advancing the cursor atomically.

class OutboxSyncDispatcher {
  final AppDatabase db;
  final ApiClient api;

  OutboxSyncDispatcher({required this.db, required this.api});

  Future<void> dispatchPendingQueue() async {
    // 1. Lease ready outbox rows in strict local sequence order
    final pendingOps = await (db.select(db.syncOutbox)
          ..where((t) => t.syncStatus.equals('pending'))
          ..orderBy([(t) => OrderingTerm.asc(t.sequenceNo)])
          ..limit(25))
        .get();

    for (final op in pendingOps) {
      try {
        // Mark in-flight to prevent duplicate worker execution
        await (db.update(db.syncOutbox)..where((t) => t.id.equals(op.id))).write(
          const SyncOutboxCompanion(syncStatus: Value('in_flight')),
        );

        // 2. Transmit to backend with explicit Idempotency Key header
        final response = await api.post(
          '/api/v1/sync/push',
          data: {
            'operation_id': op.id,
            'entity_type': op.entityType,
            'entity_id': op.entityId,
            'operation': op.operation,
            'payload': jsonDecode(op.payloadJson),
          },
          headers: {'X-Idempotency-Key': op.idempotencyKey},
        );

        if (response.statusCode == 200 || response.statusCode == 201) {
          // 3. Mark outbox entry synced and clear entity dirty flag
          await db.transaction(() async {
            await (db.update(db.syncOutbox)..where((t) => t.id.equals(op.id))).write(
              const SyncOutboxCompanion(syncStatus: Value('synced')),
            );

            if (op.entityType == 'invoice') {
              await (db.update(db.invoices)..where((t) => t.id.equals(op.entityId))).write(
                const InvoicesCompanion(isDirty: Value(false)),
              );
            }
          });
        }
      } on SocketException catch (_) {
        // Network drop: reset to pending with backoff; keep payload intact
        await (db.update(db.syncOutbox)..where((t) => t.id.equals(op.id))).write(
          SyncOutboxCompanion(
            syncStatus: const Value('pending'),
            retryCount: Value(op.retryCount + 1),
          ),
        );
        break; // Pause remaining batch until connectivity is restored
      }
    }
  }
}

4. Battle-Tested Conflict Resolution: Beyond Naive Last-Write-Wins

In multi-user enterprise systems—such as retail chains using MubiDokan POS or multi-branch clinics using Aesthetic Desk EMR—two devices inevitably modify the same entity while both are offline. A common anti-pattern is blindly adopting 'Last-Write-Wins' (LWW) based on device clock timestamps. Because client system clocks can be desynchronized by minutes or years, LWW causes silent, irreversible data clobbering.

In production systems, we divide entities into three distinct conflict resolution classes:

1. Append-Only Event Streams: For accounting ledgers, transaction histories, attendance logs, and inventory audit logs, data is strictly immutable. New entries are appended as distinct UUID records. Because existing records are never overwritten, conflicts are mathematically impossible.

2. Server-Authoritative Three-Way Merges: For editable records such as customer profiles or clinical case files, every record carries an incremental server_version. When the mobile client pushes an update, it provides its base_version alongside the new mutation. If the server detects that the current cloud version is greater than the client's base_version, it performs a field-level diff. If non-overlapping fields were edited, the server merges them automatically. If the same field was modified concurrently, the server issues an HTTP 409 Conflict with the canonical representation, allowing the client to resolve the discrepancy or prompt the user.

3. Tombstone Deletions: Physical DELETE statements in SQLite destroy history, causing delta pull workers to re-insert the deleted record upon next sync. Offline-first architectures use soft deletions (deleted_at IS NOT NULL). Tombstones synchronize down to all peers, allowing local queries to filter active records while guaranteeing deletions propagate globally.