The Sync Engine II: Delta Pull & Merge

This is Part 7 of the Offline-First Flutter series. We can push local changes up. Now we pull remote changes down — edits made on the user's other devices, or by collaborators — and merge them into the local database.

Two questions make this interesting:

(1) How do you fetch only what changed instead of re-downloading the entire dataset every sync? (2) How do you apply remote changes without destroying un-synced local edits? Answer both and pull is solid. Get either wrong and you either burn bandwidth re-downloading everything, or you silently overwrite the user's offline work.

Let's build delta pull and a safe merge.

Stack: Drift + abstract REST, Flutter 3.38 / Dart 3.12. Builds on the outbox push and sync metadata.

The analogy: "what's new since I last checked"

Analogy — the newspaper vs the archive. Every morning you don't re-read the entire newspaper archive — you read today's edition: what's new since yesterday. You remember the date you last read (a bookmark), and you ask only for everything after it.

Delta pull is exactly that. The client keeps a cursor ("I'm caught up to here") and asks the server: "give me everything that changed after this point." The server returns a small delta, the client applies it and advances the cursor. Re-downloading the whole dataset every sync would be like re-reading the archive daily — wasteful and slow.

The sync cursor (high-water mark)

The cursor is a single value the client stores, marking "how far I'm caught up." It can be:

• A server timestamp (serverUpdatedAt of the last change you saw), or
• A monotonic server change sequence (a global counter the server increments per change) — more robust than timestamps (no clock issues, no ties).

We'll store it in a tiny key-value table:

class SyncState extends Table {
  TextColumn get key => text()();        // e.g. 'notes_cursor'
  TextColumn get value => text()();      // the cursor (sequence or ISO timestamp)
  @override
  Set<Column> get primaryKey => {key};
}

Prefer a server-assigned sequence/cursor over a client timestamp. The server is the single clock for "what changed when," so a server change-sequence avoids cross-device clock drift and missed/duplicated items at timestamp boundaries. If you must use timestamps, use the server's serverUpdatedAt, never the device clock, and handle ties (>= plus dedup by id).

The pull request and response

The client sends its cursor; the server returns the delta plus a new cursor:

abstract interface class NoteApi {
  Future<PushResult> pushNote(Note note);           // Part 6
  Future<PullResponse> pullChanges({String? cursor, int limit});
}

class PullResponse {
  final List<RemoteNote> changed;   // created/updated notes since cursor
  final List<String> deletedIds;    // tombstones: ids deleted on the server
  final String nextCursor;          // new high-water mark
  final bool hasMore;               // pagination: more pages to fetch
  const PullResponse(this.changed, this.deletedIds, this.nextCursor, this.hasMore);
}

The response must include deletions. A pull that returns only changed rows can never tell the client a note was deleted elsewhere — it would linger forever. The server keeps its own tombstones and reports deletedIds in the delta. (This is the server-side mirror of our local tombstones from Part 2.)

The pull loop (with pagination)

Future<void> pull() async {
  var cursor = await _readCursor('notes_cursor');     // null on first run
  var hasMore = true;

  while (hasMore) {
    final res = await api.pullChanges(cursor: cursor, limit: 200);
    await db.transaction(() async {
      for (final remote in res.changed) {
        await _applyRemoteChange(remote);             // upsert with merge (below)
      }
      for (final id in res.deletedIds) {
        await _applyRemoteDeletion(id);               // delete with merge (below)
      }
      await _writeCursor('notes_cursor', res.nextCursor); // advance bookmark
    });
    cursor = res.nextCursor;
    hasMore = res.hasMore;                             // keep paging large deltas
  }
}

Advance the cursor inside the same transaction that applies the page. If you applied changes but crashed before saving the cursor, the next pull would re-apply them — usually harmless (upserts are idempotent) but wasteful, and risky if not perfectly idempotent. Applying-and-bookmarking atomically keeps pull crash-safe and resumable. The first run has a null cursor → the server returns everything (paginated) — the initial full sync.

The merge: don't clobber local edits

This is the crux. When a remote change arrives for note X, the local row could be in one of three states. The dirty flag decides what to do.

Future<void> _applyRemoteChange(RemoteNote remote) async {
  final local = await (db.select(db.notes)..where((n) => n.id.equals(remote.id)))
      .getSingleOrNull();

  if (local == null) {
    // 1. We've never seen it → just insert the server's version (clean).
    await db.into(db.notes).insert(remote.toCompanion(isDirty: false));
    return;
  }

  if (!local.isDirty) {
    // 2. Local is clean (no un-synced edits) → safe to overwrite with server's.
    await _overwriteWithRemote(remote);              // clean update, isDirty=false
    return;
  }

  // 3. Local is DIRTY *and* the server changed it too → a genuine CONFLICT.
  await _resolveConflict(local, remote);             // Part 8 decides the winner
}

The rule that protects offline work: apply the server's version only when the local row is clean. If the local row is dirty, the user has un-synced edits — blindly overwriting would destroy their offline work. A dirty row plus a remote change is a conflict, deferred to Part 8. This single isDirty check is what makes pull safe. Never skip it.

Remote deletions follow the same logic:

Future<void> _applyRemoteDeletion(String id) async {
  final local = await (db.select(db.notes)..where((n) => n.id.equals(id)))
      .getSingleOrNull();
  if (local == null) return;                          // already gone
  if (!local.isDirty) {
    await (db.delete(db.notes)..where((n) => n.id.equals(id))).go(); // safe purge
  } else {
    // Conflict: deleted on server, but edited locally → Part 8 (often "edit wins").
    await _resolveDeleteEditConflict(local);
  }
}

Push then pull (order matters)

When you sync, do you push or pull first? The common, safer order is push, then pull:

Future<void> syncNow() async {
  await pushDirtyNotes();   // 1. send local changes up first (Part 6)
  await pull();             // 2. then bring remote changes down (this part)
}

Why push first: by sending your local changes before pulling, your edits are already on the server, so the pull reflects an up-to-date world and you minimize the window where a conflict is "discovered" on pull rather than resolved on push. (Either order works because the merge is conflict-aware, but push-then-pull tends to converge faster with fewer surprises.) The whole syncNow should be safe to call repeatedly — it's triggered by connectivity and background timers.

Idempotency of pull (apply twice safely)

Just like push, pull must survive being interrupted and re-run:

• Changes are applied via upsert by id — applying the same remote note twice yields the same row. Idempotent.
• Deletions are applied by id — deleting an already-deleted (or absent) row is a no-op. Idempotent.
• The cursor only advances after a page is applied, so a crash mid-pull just re-fetches and re-applies that page harmlessly.

Design every sync operation to be idempotent. Networks and processes fail at the worst moments; the only sane defense is "applying it again does no harm." Upsert-by-id (changes) and delete-by-id (deletions) give you that, and the transactional cursor advance makes the whole pull resumable.

The reactive payoff returns

Recall from Part 4: the UI watches the local table. So when pull() upserts remote notes into notes, Drift's reactive streams re-emit and the screen updates — with zero UI code:

pull() → upsert remote notes into local `notes` table
              │
       Drift .watch() re-emits
              ▼
       notesProvider → ConsumerWidget rebuilds → new notes appear

This is the moment the architecture pays off completely. A note created on the user's laptop appears on their phone after a sync — and we never wrote a line of "update the UI when sync finishes" code. The SSoT + reactive streams design from Parts 2 and 4 carries remote changes to the screen automatically.

Practice Challenges

Challenge 1 — Delta, not dump. Why send a cursor with the pull request instead of fetching all notes each time?

Challenge 2 — The deletion gap. A pull returns only changed rows, never deletions. What bug appears?

Challenge 3 — The clobber bug. During pull, the server's version of note X arrives while X is locally dirty. Why must you not just overwrite, and what do you do?

Challenge 4 — Crash mid-pull. Why advance the cursor in the same transaction that applies a page?

Challenge 5 — Order. Why is "push then pull" usually preferred over "pull then push"?

Questions to test yourself

Q1 (basic). What is delta (incremental) pull?

Q2 (basic). What is the sync cursor and where is it stored?

Q3 (intermediate). Why must the pull response include deletions, and how?

Q4 (intermediate). How does the merge avoid destroying un-synced local edits?

Q5 (intermediate). Why must pull be idempotent, and how is it made so?

Q6 (advanced). Why prefer a server-assigned change sequence over device timestamps for the cursor?

Wrapping up

• Delta pull fetches only what changed since a cursor (high-water mark) — prefer a server change-sequence over device timestamps; store it locally.
• The pull response carries changed rows and deletedIds (server tombstones); apply pages in a transaction and advance the cursor atomically for crash-safe, resumable pulls.
• The merge is isDirty-gated: overwrite local only when clean; a dirty row + remote change is a conflict for Part 8. This protects un-synced offline edits.
• Sync as push then pull; make every operation idempotent (upsert/delete by id).
• Thanks to SSoT + reactive streams, applied remote changes hit the screen automatically — no UI code.

In Part 8 we tackle the problem we kept deferring: conflict resolution — when the same note changed in two places, deciding who wins, from last-write-wins to version vectors and field-level merges.