Offline-First Flutter: Drift as the Source of Truth, Supabase as a Sync Target

How I built a climbing app that works in a concrete basement with zero bars, and why the server is just a replica.

Most apps that claim “offline support” are really online apps with a cache bolted on. The API is the source of truth, the local store is a convenience, and the moment you write something without a connection you enter a world of spinners, stale state, and “please try again later.”

I built SENDO, a bouldering session tracker, the other way around. Climbing gyms are concrete boxes, often underground, and connectivity ranges from bad to nonexistent. So the local database is the app. The server is a replica that catches up whenever it can.

This post walks through the actual architecture in production: Flutter with Drift (a reactive SQLite library for Dart) as the always-on repository, Supabase as a pure sync target, and an outbox pattern gluing them together. Code samples are lightly trimmed from the real codebase.

The rule that makes everything else simple

There is exactly one rule: the UI never talks to the network.

The app has two repository classes:

• DriftRepository wraps the local SQLite database. Every screen, every provider, every read and write goes through it. Always.
• SupabaseRepository wraps the remote API. Only one component ever touches it: the sync engine.

That’s it. There is no “try remote, fall back to local” branching, no connectivity checks sprinkled through the UI layer, no cache invalidation logic. Reads hit SQLite and return in microseconds. Writes commit locally and return immediately. The app behaves identically on airplane mode and on WiFi, because from the UI’s perspective the network does not exist.

The interesting question is: how does data get to the server?

The outbox pattern

Every local write transaction does two things atomically: it performs the domain write, and it journals a description of that write into an outbox table.

The outbox schema is small:

id          integer, auto-increment (drain order)
op          'insert' | 'update' | 'delete' | 'replace_tags'
entity      'sessions', 'ascents', 'contacts', ...
entity_id   the row's id
payload     JSON body, ready to send to PostgREST
attempts    retry counter
status      'pending' | 'done' | 'failed'

Here is a real write path, inserting a new session:

@override
Future<void> insertSession(Session s) => _db.transaction(() async {
      final profileId = await _profileId();
      await _db.into(_db.sessions).insert(SessionsCompanion.insert(
            id: s.id,
            profileId: profileId,
            gymId: Value(s.gym?.id),
            startedAt: Wire.timestamp(s.startedAt),
          ));
      await _journal(
        op: 'insert',
        entity: 'sessions',
        entityId: s.id,
        payload: Wire.sessionRow(s, profileId),
      );
    });

And the journal helper it calls:

/// Appends one journal row. Must run inside the same
/// transaction as the domain write it describes.
Future<void> _journal({
  required String op,
  required String entity,
  required String entityId,
  Map<String, Object?>? payload,
}) =>
    _db.into(_db.outbox).insert(OutboxCompanion.insert(
          op: op,
          entity: entity,
          entityId: entityId,
          payload: Value(payload == null ? null : jsonEncode(payload)),
          createdAt: Wire.nowTimestamp(),
        ));

The single transaction is the whole trick. Either the domain write and its journal row both commit, or neither does. There is no window where data exists locally but the sync engine doesn’t know about it, and no window where the sync engine would push something that was rolled back.

Note that the payload is serialized at write time, in the exact wire format the server expects. The sync engine never reconstructs state; it just replays what was recorded.

The sync side-car

Syncing lives in a SyncService that runs alongside the app. It is genuinely a side-car: if you deleted the class, the app would still work perfectly, it would just never upload anything. That property is worth protecting, because it means sync can be gated behind a feature flag, a subscription tier, or a sign-in state without touching a single screen.

A sync cycle is four steps:

Future<void> syncNow({String reason = 'manual'}) async {
  if (_syncing) return; // never two cycles at once
  _syncing = true;
  try {
    await _pruneDone();     // drop rows confirmed on a previous cycle
    await drain();          // push: replay the outbox, oldest first
    await pullGymDelta();   // refresh server-owned reference data
    await pull();           // pull: only if the outbox is empty
  } finally {
    _syncing = false;
  }
}

Cycles are triggered from four places: app launch, a connectivity change from offline to online, a new outbox row appearing, and a manual button in the debug screen.

The outbox trigger deserves a closer look. Logging climbs happens in bursts, and the drain itself flips rows from pending to done, which would retrigger the watcher. So new-row triggers are debounced:

void _scheduleWriteDrain() {
  _writeDebounce?.cancel();
  _writeDebounce = Timer(const Duration(milliseconds: 800), () async {
    if (_syncing || !await _hasPending()) return;
    if (await _headExhausted()) return; // head row out of retries
    unawaited(syncNow(reason: 'outbox-write'));
  });
}

Draining: ordered, tolerant, and bounded

The drain replays pending rows strictly oldest-first and stops at the first failure. Order matters: an ascent insert must not reach the server before the session it belongs to. FIFO with stop-on-failure preserves causality for free.

Failures fall into two buckets:

• Retryable (network blips, transient server errors): the row’s attempts counter increments. After 3 automatic attempts the drain stops rescheduling itself and waits for an external trigger, like a relaunch or a connectivity flip. Without that cap, a poisoned head row would burn battery retrying forever.
• Permanent (malformed data the server will never accept, recognizable by SQLSTATE class 22 errors): the row is parked as failed and reported to crash monitoring with its op, entity, and id. The queue behind it keeps moving.

There is one piece of domain-specific tolerance worth stealing. Gyms in SENDO are discovered on devices and registered on the server asynchronously, so an ascent can legitimately reference a gym the server has never heard of. When that insert fails on the foreign key, the drain retries the same payload with gym_id nulled. The server gets the ascent without the gym link; the local database keeps the full link. A later pull heals it once the gym exists remotely. Losing one association beats wedging the entire queue.

Pulling: local writes win

The pull step has one guard that does all the conflict-resolution work:

Pull only runs when the outbox is empty.

If there are unpushed local writes, they win, unconditionally, until they’ve been drained. Once the outbox is empty, the pull fetches a full snapshot from the server and wholesale replaces the local user data. After the fetch, it re-checks the outbox one more time, because a write may have landed while the fetch was in flight; if so, the snapshot is discarded.

This sounds crude compared to CRDTs or field-level merging, and it is. But for an app where one account writes from one or two devices, it is exactly the right amount of machinery. The failure mode of “last device to sync wins the whole snapshot” is acceptable when the same human holds both devices. If your app has concurrent multi-user editing, stop here and go read about proper merge strategies; this shortcut is load-bearing and it only holds for single-writer data.

The gotchas

Client-generated IDs. Every user-data row gets a UUID v4 minted on the device at creation time. This is non-negotiable for offline-first: an insert cannot wait for the server to hand back an id. Postgres stores them as native uuid, SQLite as TEXT, and a schema parity test in CI asserts the two schemas stay column-for-column identical so drift (the bad kind) gets caught at build time.

The sign-in problem. Users can log sessions before ever creating an account, but the server keys everything to the auth uid. Locally, a fresh install runs under a fixed placeholder uuid. On first sign-in, an adoption routine re-keys the profile and all its child rows to the real uid, clears the now-mislabeled outbox, and rebuilds it as one full snapshot upload. Weeks of offline history survive account creation.

Reference data flows the other way. Gyms and tag definitions are server-owned. They’re never journaled, and they sync down via a watermark delta (rows whose updated_at moved past the last pull). Keeping the two directions on separate rails, user data up via outbox and reference data down via delta, removes a whole category of "who owns this row" bugs.

RLS as the safety net. Every user-data table on Supabase has owner-scoped row-level security, checked against auth.uid(). The sync engine could have a catastrophic bug and still be physically unable to read or write another user's rows. When your client is the source of truth, the server's job is to be paranoid.

The downsides

The schema lives in two places, and every change has to land in both. Adding one column means touching four spots: the Postgres migration, the Drift table, the Dart model, and the wire mapper. This is the chore AI quietly took over for me: I describe the change once, the assistant updates all four, and the parity test catches anything it missed. It also wrote most of the boilerplate, since every repository method follows the same write-then-journal shape. Without that help, the double bookkeeping would be the main argument against this architecture.

Debugging needs some care too. Payloads are serialized the moment a write happens, so the outbox can still hold rows written by a different feature or version of the app. An inspectable outbox screen in debug builds and other debug toolings proofed themselves as a life safer multiple times.

Was it worth it?

Yes! The UI is never waiting on the network. The app is easily demo-able and testable because the repository is just SQLite, sync as a detachable feature you can gate behind a paid tier, and users in basement gyms who never see a spinner.