Local first, sync separately. See Part 1.

Every read and write hits local storage first (instant, network-independent), and syncing with the server happens separately in the background — the app never waits on or blocks for the network.

Not the server.

The local database. The UI reads only from it; the sync engine keeps it eventually consistent with the server.

Writes.

A cache is a read fallback on a network-primary design, so writes still fail offline. Offline-first makes local storage primary for reads and writes; the network is never on the critical path and sync is first-class.

Fast vs slow.

(1) User flow: UI → Repository → Local DB (synchronous, instant, local). (2) Sync flow: Sync Engine ↔ Server (asynchronous, background). The user waits only on the first.

Created before the server.

Records are created offline, before the server sees them, so there's no round-trip to allocate a server id. The client mints a UUID for a stable, collision-free identity from creation.

Deletion must sync.

A deletion is information that must reach the server/other devices. Physically removing the row loses it, so the note resurrects on next pull. A tombstone keeps the deletion syncable.

Critical path.

Reads/writes hit local storage (sub-millisecond) instead of network round-trips (100–2000ms) on the critical path. The UI updates instantly from local data; sync happens invisibly, so there are no per-action spinners.

Ship vs learn.

When you want to ship standard CRUD-over-Postgres fast with battle-tested sync/conflict handling and less code, and don't need full custom control. Hand-building is for learning, custom needs, or avoiding lock-in.

Before the server.

Both creation and deletion happen offline, before the server is involved. A record needs identity at creation (→ client UUID), and a deletion is data that must sync (→ tombstone). Both fall directly out of "local first, sync later."

User vs engine.

Domain data (title, body) and sync metadata (id/UUID, updatedAt, version, isDirty, isDeleted).

Unsynced.

It marks rows with unsynced local changes and drives the push: the engine pushes dirty rows and clears the flag on success.

Update, not delete.

(update(notes)..where((n) => n.id.equals('x'))).write(
  const NotesCompanion(isDeleted: Value(true), isDirty: Value(true)));

Common reference.

Devices/servers span time zones; UTC is a single common reference so timestamp comparisons in conflict resolution are meaningful.

Clock-free.

A monotonically incrementing counter gives clock-independent conflict detection ("you edited v3 but server has v5"), unlike timestamps which suffer clock drift.

Tombstones still exist.

Tombstoned rows remain in the table so the deletion can sync, but the user shouldn't see them — so the UI query excludes them.

Background writes.

Background sync writes into the local DB, and reactive streams auto re-emit on any table change — so the UI updates when sync brings data without UI code. SSoT + reactive queries = free background UI updates.

Partial.

It lets you set only the columns you mean to change (others stay Value.absent()), so partial updates don't clobber other fields/metadata.

Different roles.

updatedAt is convenient for LWW ordering but clock-sensitive; version gives robust, clock-free conflict detection. Keeping both lets you start with timestamps and harden with versions.

Version + strategy.

Increment schemaVersion and add the step(s) in onUpgrade (plus update the table). onCreate handles fresh installs.

Only copy.

The local DB is the SSoT and may hold un-synced data that exists nowhere else, so a destructive migration causes permanent data loss — unlike a re-fetchable online cache.

addColumn.

if (from < 3) { await m.addColumn(notes, notes.color); }

(with color declared .withDefault(const Constant('#FFF'))).

Long jump.

A user may upgrade across multiple versions at once; < N runs each step for everyone below that version, so all needed steps execute in order on a long jump.

Existing rows.

Existing rows have no value for it; SQLite can't add a non-null, no-default column to a populated table. A default/nullable gives old rows a valid value.

isDirty.

By setting isDirty = true (or bumping version) on rows, making the engine push the whole database next launch. Don't mark dirty for representation-only migrations.

Data loss.

It deletes all notes, including un-synced ones that exist only locally — irreversible loss. Migrate in place (addColumn/backfill), never drop user data.

Transition.

Dropping is destructive and might erase the only copy of un-synced data. Add/backfill preserves data during transition; drop only in a later release once all clients migrated and nothing un-synced depends on it.

Long jump + verifier.

The oldest supported → latest upgrade, using Drift's schema verifier (schema dump/generate), asserting existing rows survive with sensible defaults.

Doorway.

The single doorway between UI and data — the only thing the UI talks to; it exposes domain ops and hides Drift/HTTP/sync.

Stream.

A StreamProvider<List<Note>> wrapping the repository's watchNotes() stream (yielding AsyncValue).

Local write only.

It only writes a local row and sets isDirty — no network call. It's durably saved on local commit; pushing happens later in the sync engine.

Reactive chain.

Repo write → local INSERT → Drift .watch() re-emits → notesProvider emits AsyncData → ConsumerWidget rebuilds with the note.

Behind the boundary.

The UI depends only on the repository interface and watches the local DB, never the network. Sync sits behind that boundary, writing to the same table whose reactive streams already update the UI.

Decouple.

So the UI is independent of storage/transport shape; schema/API/sync changes don't ripple into widgets, and the repository can reconcile local+remote behind a stable contract.

Leak.

It bypasses the repository, coupling UI to Drift. Move the query into the repository and watch a provider exposing it.

Same table.

The UI watches the local table; any change (including sync writes) makes watchNotes() re-emit through notesProvider to the widget. The reactive read path is reused by sync automatically.

The seam.

Behind the repository (triggered by connectivity). Because the UI only knows the repository + reactive providers, sync is purely additive — no widget changes. Clean boundaries make big features addable without rewrites.

Interface.

That a network interface exists (wifi/cellular/none) and when it changes — not whether the internet/your server is reachable.

Place the call.

A tiny request (e.g. HTTP HEAD /health) with a short timeout that confirms your server actually answered. Failure/timeout → treat as offline.

Gate then confirm.

Use connectivity_plus as a cheap gate (no interface → offline), then confirm with a reachability probe. Online = interface present and server reachable.

What sync talks to.

The sync engine talks to your backend, so that's what must be reachable. A generic site being up doesn't prove your API is reachable/unblocked.

Edge.

To sync on the reconnect edge, not repeatedly while online — avoiding sync storms (especially with flapping connections).

Debounce + resilient.

Debounce the online signal so brief drops/returns don't propagate, and make the sync engine resilient (idempotent, retry-capable) so a sync during a blip fails gracefully.

Best-effort hint.

The signal is best-effort and can be wrong (says online a moment before a request fails). Correctness must live in an idempotent, failure-handling sync engine; the signal is just "probably worth trying now."

Writes still work.

Writes already succeed locally; being offline doesn't block the user or lose data. The banner just notes that syncing is paused.

Add reachability.

connectivity_plus confirms only an interface; the portal blocks your API. Add a reachability probe to /health and define online as interface AND server reachable, so the portal reads as offline-for-sync.

Durable pending.

A durable record of pending changes needing to reach the server — written instantly on edit, drained when online, items removed only on confirmed success.

After ack.

Only after the server acknowledges the push (and only if still at the pushed version). Sooner risks marking unsent changes clean.

Idempotency key.

They're idempotency keys: with an upsert endpoint keyed by the UUID, a retried push updates the same record instead of duplicating. At-least-once + idempotent = effectively exactly-once.

Compare-and-clear.

If the user edits a row (v5→v6) during the push of v5, clearing isDirty after the v5 ack loses v6. Compare-and-clear: only clear if the row is still at the pushed version; a mid-push edit changes the version so the conditional update matches nothing and it stays dirty.

After ack.

Only after the server acknowledges the delete. Purging earlier loses the deletion locally and the note resurrects on next pull.

Retry vs surface.

Transient (timeout/503): leave dirty, retry with backoff. Permanent (400/401): don't loop — surface it (fix data/re-auth). Treating all the same loses data or spins forever.

The flag.

All rows where isDirty = true; push each, clear on success, leave dirty on failure.

Exponential.

Duration backoff(int n) => Duration(seconds: (1 << n).clamp(1, 300));

State vs sequence.

Dirty-flag stores current state of changed rows — simple, for independent records (FieldNotes). Operation-log stores ordered ops (create/update/delete + payload) — preserves intent/order for collaborative/event-sourced data, at more complexity.

Idempotent.

PUT keyed by the client UUID is an idempotent upsert — safe to retry (no duplicates). A blind POST creates a new row each call, so a retried-after-timeout push duplicates the record.

Since cursor.

Fetching only records changed since the last sync (via a cursor), not the whole dataset each time.

Bookmark.

A value marking "how far I'm caught up" (a server change-sequence or serverUpdatedAt), stored locally and advanced after applying a delta.

Otherwise lingers.

Otherwise the client never learns a record was deleted elsewhere, so it lingers forever. The server returns deletedIds (its tombstones) for the client to remove.

Don't clobber.

Treat it as a conflict (Part 8) — never overwrite. Overwriting a dirty row destroys un-synced local edits. Overwrite only when the local row is clean.

Crash-safe.

So apply-and-bookmark are atomic and resumable: a crash mid-pull re-fetches and re-applies that page harmlessly rather than skipping or partially applying it.

Up-to-date world.

Pushing first puts local changes on the server before pulling, so the delta reflects an up-to-date world and conflicts are more likely resolved at push than discovered on pull — faster convergence.

Upsert/delete by id.

Changes apply via upsert-by-id (re-applying yields the same row); deletions via delete-by-id (a no-op if absent); the cursor advances transactionally so re-runs are harmless.

Everything.

The server returns everything (paginated) — the initial full sync — and subsequent pulls become deltas.

One clock.

A server sequence comes from a single authoritative counter, avoiding cross-device clock drift, timezone issues, and missed/duplicated items at timestamp boundaries — giving exact "everything after here" semantics.

SSoT + reactive.

Pull upserts into the local table (the SSoT); Drift's reactive .watch() re-emits and notesProvider rebuilds the UI. The reactive read path from Part 4 carries sync writes to the screen automatically.

Two places.

When the same record is changed in two places since their last common sync, producing two divergent versions to reconcile.

Simple but lossy.

Trivial and deterministic, but silently discards the loser's edits and is clock-drift sensitive. Fine for latest-value fields, bad for rich content.

Clock-free.

It's clock-independent: the server compares the client's base version against its current version; mismatch = conflict. No device clocks involved (optimistic concurrency, like ETag/If-Match).

No silent clobber.

The server accepts a change only if it was based on the current version; otherwise it rejects and returns its state for the client to rebase. So updates can never silently clobber each other on the server.

Take each changed side.

{title: T2, body: B2} — only remote changed the title, only local changed the body, so keep both (no loss).

3-way.

To know which side actually changed each field (compare each side to the base — a 3-way merge). Without the base you can't distinguish a change from a non-change.

Many replicas.

When many independent replicas edit offline and you must distinguish concurrent edits from causally ordered ones — a single counter can't; version vectors (per-replica counters) can.

Auto-merge.

Data types whose concurrent edits always merge deterministically by mathematical properties — ideal for real-time collaborative structures (shared text/lists). Heavyweight; use a library/framework, not for simple records.

Don't lose content.

Edit wins (resurrect) for user content — losing freshly written content to a remote delete is the worse surprise. Unless the domain treats deletes as final.

Never lose.

High-value content where silent resolution is unacceptable: keep both versions (e.g. a "conflicted copy") and let the user decide — never lose data.

Detect + merge + resurrect + copy.

Detect with a server version counter; resolve with field-level merge off the base (per-field LWW fallback); delete-vs-edit = edit wins; keep a conflicted copy for irreconcilable high-value cases; then mark dirty and re-push. Pick the simplest strategy that doesn't lose data users care about — explicitly.

Reflects local DB.

Updating the UI immediately, assuming success. It's free because the UI reflects the instantly-written local DB (SSoT) — the screen updates before/independent of the server.

dirty/error/clean.

Pending (isDirty), failed (syncError != null), synced (clean). Derived from sync metadata.

Consistency.

Deriving keeps status always consistent with the real metadata; a separate stored field could drift out of sync.

Reactive row.

The note comes from a reactive stream; when the engine flips isDirty/syncError, the row changes, the stream re-emits, and the badge rebuilds with the new derived status.

Quiet vs loud.

Transient: stay pending, retry silently, no alarm. Permanent: set syncError, show failed with an actionable retry/fix.

Normal & safe.

Pending changes are normal and safe in offline-first (saved locally, sync later); presenting them as errors undermines trust in a working system.

Flag, don't delete.

The local DB is authoritative for existence, so a rejected push doesn't undo the local data — it's marked failed until resolved. Rollback applies only to ephemeral server-authoritative actions; for user content you flag + let the user fix, never silently delete.

Clean.

Synced — clean and matching the server.

Count dirty.

It gives a single "up to date?" signal. Compute it reactively as a count of isDirty = true rows (a watched aggregate query), shown as "N change(s) to sync"/"all changes saved".

OS-scheduled.

Running the engine while the app is closed, via workmanager (WorkManager/BGTaskScheduler). Best-effort because the OS decides if/when it runs, to protect battery.

Fresh isolate.

Background tasks run in a separate isolate with its own memory — the app's providers/singletons/open DB don't exist there, so you build them fresh (and close the DB after).

Survive tree-shaking.

Marks the dispatcher as a VM entry point so it isn't tree-shaken and can be invoked headlessly by the OS to start the background isolate.

Connected ≠ reachable.

The OS constraint avoids waking with no network; the in-task reachability probe handles "connected but not reachable" (captive portal/downtime) before attempting sync.

Reliable vs OS-controlled.

Foreground triggers (start/resume/reconnect/manual) fire reliably when the user engages; background scheduling is OS-controlled and may not run. Guarantee freshness via foreground; use background to opportunistically improve it.

~15.

About 15 minutes (a minimum/hint, not a guarantee; iOS is even less predictable).

Overlap + retry.

Background and foreground syncs can overlap, and the OS may retry a "failed" task that half-succeeded. Idempotent ops (UUID upsert push, upsert/delete-by-id pull with transactional cursor) make repeated/concurrent syncNow() safe by construction.

Sync on resume.

Make foreground sync on app launch/resume the reliable floor (always syncNow() on open), keep the outbox durable so nothing is lost, and treat background runs as a bonus. They're fully synced the moment they reopen.

Rare + data loss.

Its bugs are rare, timing-dependent, and destroy irreplaceable data, and rarely reproduce manually. Simulating failures/conflicts/races in tests is the only reliable defense.

DB + API.

An in-memory Drift DB (NativeDatabase.memory() — real SQLite) and a stateful fake API you control to script conflicts/failures/latency/other devices.

Behavior over calls.

Sync depends on server behavior across calls (version checks, remembered state, deltas/deletions). A stateful fake models that, enabling real multi-step scenarios; a canned mock can't.

Inject latency.

Add latency to the fake's pushNote, start the push, edit the row before it resolves (bumping its version), await, then assert the row is still dirty (compare-and-clear preserved the edit).

Dirty not clobbered.

"Pull does not clobber a dirty local row": when a row is dirty and the server also changed it, pull must preserve the local edit (conflict-resolve, not overwrite).

Eventual consistency.

That two independent devices sharing a server converge to identical state after syncing — the architecture's eventual-consistency promise, validating that push/pull/conflict/idempotency compose correctly end to end.

Irreplaceable data.

Migrations transform the local SSoT, which may hold un-synced data existing nowhere else; a broken migration causes irreversible loss. Testing the long jump (Drift schema verifier) proves existing rows survive with correct defaults across every upgrade path.

class Notes extends Table {
  TextColumn get id => text()();
  TextColumn get title => text().withDefault(const Constant(''))();
  TextColumn get body => text().withDefault(const Constant(''))();
  DateTimeColumn get updatedAt => dateTime()();
  IntColumn get version => integer().withDefault(const Constant(0))();
  BoolColumn get isDirty => boolean().withDefault(const Constant(true))();
  BoolColumn get isDeleted => boolean().withDefault(const Constant(false))();
  TextColumn get syncError => text().nullable()();
  @override
  Set<Column> get primaryKey => {id};
}

(Part 2/Part 9)

Future<Note> createNote({String title = '', String body = ''}) =>
  db.into(db.notes).insertReturning(NotesCompanion.insert(
    id: const Uuid().v4(),
    title: title, body: body,
    updatedAt: DateTime.now().toUtc(),
  )); // isDirty defaults true; no network

// soft delete:
(update(db.notes)..where((n) => n.id.equals(id)))
  .write(const NotesCompanion(isDeleted: Value(true), isDirty: Value(true)));
// after server ack of the delete:
(delete(db.notes)..where((n) => n.id.equals(id))).go();

(Part 2/Part 6)

(update(db.notes)..where((n) =>
    n.id.equals(pushed.id) & n.version.equals(pushed.version)))
  .write(NotesCompanion(isDirty: const Value(false), serverVersion: Value(v)));
// 0 rows if edited mid-push → stays dirty → re-pushed

(Part 6)

Stream<bool> watchOnline() async* {
  await for (final r in Connectivity().onConnectivityChanged) {
    if (r.contains(ConnectivityResult.none)) { yield false; }
    else { yield await reachability.canReachServer(); }
  }
}

(Part 5)

Future<void> _applyRemoteChange(RemoteNote r) async {
  final local = await getById(r.id);
  if (local == null) return insertClean(r);
  if (!local.isDirty) return overwriteWithRemote(r);
  return resolveConflict(local, r);   // dirty + remote change → conflict
}

(Part 7)

String pick(String base, String local, String remote) {
  final lc = local != base, rc = remote != base;
  if (lc && !rc) return local;
  if (rc && !lc) return remote;
  if (!lc && !rc) return base;
  return remoteNewer ? remote : local; // both changed → LWW
}

(Part 8)

@pragma('vm:entry-point')
void callbackDispatcher() {
  Workmanager().executeTask((task, data) async {
    final db = AppDatabase(openConnection());      // fresh isolate
    final engine = SyncEngine(db: db, api: RestNoteApi());
    try { await engine.syncNow(); await db.close(); return true; }
    catch (_) { await db.close(); return false; }
  });
}

(Part 10)

final server = FakeServer();
final a = engineFor(newTestDb(), server);
final b = engineFor(newTestDb(), server);
await a.repo.createNote(title: 'A');
await b.repo.createNote(title: 'B');
for (var i = 0; i < 2; i++) { await a.syncNow(); await b.syncNow(); }
expect((await a.repo.allTitles()).toSet(), (await b.repo.allTitles()).toSet());

(Part 11)

class SyncEngine {
  SyncEngine({required this.db, required this.api});
  final AppDatabase db;
  final NoteApi api;

  Future<void> syncNow() async {
    await _push();   // Part 6: send local changes first
    await _pull();   // Part 7: then bring remote changes down
  }

  // ── PUSH (Part 6) ──────────────────────────────────────────
  Future<void> _push() async {
    final dirty = await (db.select(db.notes)..where((n) => n.isDirty.equals(true))).get();
    for (final note in dirty) {
      try {
        final res = await api.pushNote(note);            // idempotent (UUID upsert)
        if (res.conflict) { await _resolve(note, res.serverNote!); continue; } // Part 8
        if (note.isDeleted) {
          await (db.delete(db.notes)..where((n) => n.id.equals(note.id))).go(); // purge after ack
        } else {
          // compare-and-clear: only clear if still at pushed version (Part 6 race fix)
          await (db.update(db.notes)..where((n) =>
              n.id.equals(note.id) & n.version.equals(note.version)))
            .write(NotesCompanion(isDirty: const Value(false), syncError: const Value(null)));
        }
      } on PermanentSyncException catch (e) {
        await (db.update(db.notes)..where((n) => n.id.equals(note.id)))
          .write(NotesCompanion(syncError: Value(e.message)));   // Part 9: surface
      } catch (_) {
        /* transient: leave dirty, retry next sync (Part 6) */
      }
    }
  }

  // ── PULL (Part 7) ──────────────────────────────────────────
  Future<void> _pull() async {
    var cursor = await _readCursor();
    var more = true;
    while (more) {
      final res = await api.pullChanges(cursor: cursor, limit: 200);
      await db.transaction(() async {                    // atomic apply + cursor
        for (final r in res.changed) {
          final local = await _getById(r.id);
          if (local == null) { await _insertClean(r); }
          else if (!local.isDirty) { await _overwrite(r); }   // safe
          else { await _resolve(local, r); }                  // Part 8: conflict
        }
        for (final id in res.deletedIds) {
          final local = await _getById(id);
          if (local != null && !local.isDirty) {
            await (db.delete(db.notes)..where((n) => n.id.equals(id))).go();
          } // else delete-vs-edit → edit wins (Part 8)
        }
        await _writeCursor(res.nextCursor);
      });
      cursor = res.nextCursor; more = res.hasMore;
    }
  }

  // ── CONFLICT (Part 8): field merge off the base, then re-push ──
  Future<void> _resolve(Note local, RemoteNote remote) async {
    final base = await _lastSyncedSnapshot(local.id);
    await _writeFieldMerge(local, remote, base);          // marks dirty → re-pushed
  }
}

How it maps to the series: client UUIDs + upsert give idempotent push (Part 6); compare-and-clear fixes the dirty-during-push race (Part 6); the merge guard (!local.isDirty) protects offline edits (Part 7); the transactional cursor makes pull crash-safe (Part 7); field merge + edit-wins resolve conflicts (Part 8); permanent errors set syncError for the UI (Part 9); and the whole syncNow is idempotent, so connectivity and background triggers can call it freely. The local DB stays the SSoT, so the reactive UI updates for free, and it's all testable with an in-memory DB + fake API.

If you can write and defend this syncNow, you've mastered offline-first.