Offline-First Sync Failures That Flight Mode Won't Show You

*Clock skew, deletes that come back, duplicate entries on a flaky connection, and old databases: the tests I added after auditing my own Flutter app.
*

Short answer: Testing offline-first sync by switching on flight mode only tests the polite case: the network is either there or it isn't. The bugs that lose people's data come from messier conditions: a device with the wrong clock, a delete on one phone while another phone edits the same entry, a connection that drops just after a write was sent, two syncs running at once, and a database created by an older version of the app. This post walks through each one in a real Flutter app, what goes wrong under last-write-wins, and the fix.

I build Mindleau, a brain dump journal and mood tracker. It's local-first: every entry is written to SQLite (via Drift) on the phone, and a sync layer pushes it to Firestore in the background. I've written about the local-first architecture and about anonymous auth, tombstones and timeouts.

Those posts describe the design. This one is about testing it against conditions that are worse than "offline". I went through my own sync code looking for the cases that would lose someone's journal entry, and found more than I expected.

The design under test

Briefly, so the failures make sense:

• Every row has an updatedAt timestamp (set from the device clock), a syncStatus (synced, pending_create, pending_update) and, for deletes, a deletedAt tombstone.
• A sync runs pull, then push. Pull fetches remote rows and merges them: the local row is kept only if its updatedAt is newer, otherwise the remote row replaces it. Push sends every non-synced row with set(..., merge: true).
• Each row gets a remoteId (a UUID) the first time it's pushed.

That's last-write-wins (LWW), the default for most hand-rolled sync. It's simple and it's usually fine. Here's where it isn't.

1. The device with its clock ahead

LWW compares timestamps from two different clocks. If one phone's clock runs an hour fast, its edits beat every other device's edits for an hour, even edits made later in real time.

In code, the merge looks like this:

final remoteUpdatedAt = _parseRemoteDate(row['updated_at']) ?? now;
final existing = await (_database.select(_database.brainDumpRows)
      ..where((t) => t.remoteId.equals(remoteId)))
    .getSingleOrNull();
if (existing != null && existing.updatedAt.isAfter(remoteUpdatedAt)) return;
// otherwise: overwrite local with remote

Notice what it doesn't check: whether the local row has an edit that hasn't been pushed yet. Phone B edits an entry offline. Phone A (clock fast) edited it earlier. B reconnects, pulls first, sees A's "newer" timestamp, and overwrites its own unpushed edit. Nobody is told.

The test: inject the clock. Make DateTime.now() a dependency of the repository so tests can give each simulated device its own skew. Edit the same entry on both, sync both, and assert that both pieces of text still exist somewhere.

The fix: two parts. Use a server timestamp (Firestore's FieldValue.serverTimestamp()) as the ordering for remote writes, so the server orders writes instead of trusting phone clocks. And never overwrite a row that is still pending locally. That's a conflict, and conflicts get the treatment in section 3.

2. A delete on one phone, an edit on the other

Tombstones solve the classic problem of a deleted row being downloaded again. They don't solve this one:

• Phone A, offline, deletes an entry: deletedAt = 10:00, updatedAt = 10:00.
• Phone B, online, edits the same entry at 10:05 and syncs. Its push sends deleted_at: null.
• Phone A reconnects. Pull sees remote updated_at = 10:05, which is newer, and replaces the local row, including deletedAt = null.

The entry the user deleted comes back. In a journal, that's worse than losing an edit. People delete entries because they don't want them to exist.

Reverse the order (edit first, delete later) and the edit is silently lost instead. Both orderings, and the case where the timestamps are equal, need a test.

The fix: treat delete-versus-edit as a conflict instead of letting a timestamp decide it. My rule is that an edit made after a delete keeps the entry alive, but as a conflict copy the user can see and delete again. Being asked twice is better than losing the text, and much better than the deleted text coming back unannounced.

3. Conflict copies before CRDTs

When two devices change the same entry, there are three options:

1. Pick a winner (LWW). Simple, and silently loses one side.
2. Merge automatically (CRDTs, operational transforms). Correct for collaborative editing, but heavy machinery for one person writing free text on two phones.
3. Keep both and let the person decide.

For a personal journal, option 3 is the right middle ground. Conflicts are rare, the user is the only author, and they know which version they meant. The rule:

• If the local row is pending and the remote row changed since the last sync, don't overwrite. Save the local version as a new entry marked "conflicted copy", then accept the remote version.
• Show conflicted copies plainly in the history so the user can keep, merge or delete them.

That's a few dozen lines in the merge function and no new dependencies. If the app ever becomes collaborative, that's the time for a CRDT.

4. The connection that comes back just long enough

Flight mode is binary. Real connections aren't: a train goes through a tunnel, a café's Wi-Fi accepts the request and drops the response. This is the case I found the most interesting, because it creates duplicates.

Here's the push for a new entry:

for (final row in rows) {
  final remoteId = row.remoteId ?? _uuid.v4();
  await service.upsertBrainDump({'id': remoteId, ...});
  // only now is remoteId saved locally:
  await (_database.update(_database.brainDumpRows)
        ..where((t) => t.id.equals(row.id)))
      .write(BrainDumpRowsCompanion(remoteId: Value(remoteId), ...));
}

Every network call has a 15-second timeout, which is right: without one, a flaky connection leaves the app stuck on a spinner. But a timeout only means we stopped waiting. Firestore may already have queued the write, and it lands later. The local row never saved its remoteId, so the next sync generates a new UUID and pushes the same entry again. On the user's other phone, it now appears twice.

The general rule: an operation that can be retried must be idempotent, and its identity has to exist before the first attempt. The fix is to assign the remoteId when the row is created (or save it before pushing), so a retry overwrites the same document instead of creating a new one.

The test: a fake sync service that applies the write and then throws a timeout. Run sync twice and assert one remote document, not two.

5. Two syncs at once

Sync starts after every save, and again whenever connectivity comes back. On a flapping connection those overlap. With no guard, two syncs can both read the same pending row, both generate a UUID, and both push. That's the same duplicate as above, through a different door.

The fix is a single in-flight sync that other callers wait on:

Future<void>? _inFlight;

Future<void> syncNow() {
  return _inFlight ??= _runSync().whenComplete(() => _inFlight = null);
}

If something changes while a sync is running, mark it dirty and run once more when it finishes, rather than starting a parallel one.

6. An old database that already holds someone's journal

A fresh install is the easiest test case and the least realistic one. The users who matter most have a database written by an older version of the app, with real entries in it.

Mindleau's schema is on version 5. The migration from version 1 added sync columns like this:

ALTER TABLE brain_dump_rows ADD COLUMN updated_at INTEGER NOT NULL DEFAULT 0

DEFAULT 0 is the Unix epoch, 1 January 1970. Every row that existed before the upgrade claims it was last edited in 1970, so under LWW any remote copy of it wins, including a stale one. Not a problem on a fresh install, and a real problem on an upgraded one.

The test: check in a real database file from each old schema version (Drift can generate schema snapshots for this with drift_dev schema dump), fill it with entries, run the migration, then run a sync against a remote that holds older versions of the same entries. Assert the local text survives. The backfill fix is one statement in the migration: set updated_at = created_at where it's 0.

7. "Saved on this phone" is not "backed up"

Not a sync bug, but a product one that the sync design creates. Local-first apps make two different promises:

• Saved on this phone: written to SQLite, survives restarts and offline use.
• Backed up: pushed to the server, and restorable if the phone is lost.

Mindleau shows a "pending changes" count in Settings, but nowhere near the entry itself. There's also a third state that's easy to miss: an entry synced to an anonymous account is on the server, but the user can't restore it on a new phone until they link an email. Calling that "backed up" promises something the app can't deliver.

The interface should say which promise has actually been kept, per entry or at least on the main screen: saved on this phone, backed up, or backed up, add an email to restore it anywhere.

The test list

If you're testing an offline-first app, flight mode is the first test, not the last. This is the list I'd start from:

1. An upgrade from every old schema version, with real data in it.
2. Two devices editing the same entry offline, then syncing in both orders.
3. A device with its clock ahead (and one behind).
4. A delete on one device while another edits the same entry, in both orders and with equal timestamps.
5. A write that succeeds on the server but times out on the client, then a retry.
6. Two syncs started at once.
7. The app killed between "pushed" and "marked as synced".

None of these show up in a demo. They're the difference between an app people trust with their private thoughts and one they quietly stop using.

You can read more about the app in the Mindleau case study. If you're building something local-first and want the sync reviewed against cases like these, that's part of my technical product consulting.

Comments

[Ahmed ElFirgany]

The "connection drops right after the write was sent" case is the one most people never test. The server saw a success, the phone saw a failure, and both are right.

Client-generated IDs for every entry make the retry harmless, which I think is where your duplicate fix lands too.

For the old-database case, do you keep a test fixture for every past schema version, or only the oldest one still in the wild?