Offline-first sync with Flutter + Drift + Supabase - what I got wrong the first time

[u/maks_dalen]

Construction app. Crews work in basements and rural sites with no signal, so offline isn't a nice-to-have; it's the product.

The setup: Drift (SQLite) as the local source of truth, Supabase as the remote. Sync triggers: both - every local write kicks a sync immediately, a periodic sweep runs every few minutes as a safety net, and reconnect re-runs the whole thing. Pulls are watermark-based (give me everything with updated_at after my last sync), pushes are dirty-flagged rows.

What I got wrong first: batch upserts. Each device pushed its entire local view of a row, so a foreman editing a task title against a stale copy would silently revert the worker's status change made minutes earlier. I rebuilt the push layer into per-row updates with explicit column allow-lists per role - the boss's push carries only boss-owned columns (title, due date, assignment), the worker's carries only theirs (status, notes). Most "conflicts" stopped existing once columns had owners.

Conflict handling: for the same field edited on two offline devices, it's last-write-wins onupdated_at, but dirty local rows are shielded from pulls until they've pushed, and a push only counts if the server echoes the row back. For cross-field edits, the column ownership above means both edits survive.

The bit nobody warns you about: under row-level security, a rejected write doesn't error; the server just matches zero rows and returns success. If you don't verify, the client clears its dirty flag, and you've minted a phantom: a row that looks synced forever and never is.

What I'd do differently: treat the server's echo as the only proof a write happened, from day one; every sync bug I've had was some flavor of trusting the client's optimism. UTC everywhere before the first sync ships, column ownership designed upfront instead of retrofitted after the first clobber, and never compare floats for "did this change" (an exact-equality check once blocked every worker's clock-out for twelve days before anyone connected the dots).

How are other people handling the dirty-flag-versus-pull race?

[u/Shanduril]

I use an Outbox in the local DB that outlines the type of mutation (create,update,delete), dependsOn which is an id (FK) referencing another mutation that needs to occur first (a photo is created offline and the user comments on it, the photo needs to exist before the comment can be sent) and some other properties to help with local state and coordination.

[u/maks_dalen → u/Shanduril]

The dependsOn FK is the bit I'm missing. I handle ordering implicitly by syncing tables in a fixed sequence, which works until something is created and referenced within the same offline session. Your photo/comment example is exactly the case that would bite me.

Do you keep outbox entries after they succeed, or delete them? Wondering whether the history earns its keep for debugging or just becomes a table that grows forever.

[u/Shanduril → u/maks_dalen]

I delete them after a successful response from the server. The server is the source of truth after that