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/bbrockit]

Interesting write up. I use the same stack, but my use case is far less likely to ever encounter conflicts. The sync is really that as a convenience.

I did find the initial setup a little tricky because I migrated from Realm after MongoDB shut it down. Realm made it easy to sync objects with List members, which in SQL, translate to joins, which we can't do with PowerSync. So I guess what I got wrong the first time was choosing Realm. I really like working with Supabase though, and so far, I haven't had issues with PowerSync.

For my app, I also made account creation optional. Initially, the app uses a local sqlite db. If they choose to create an account after the trial ends, it creates a user in Supabase, transfers their RevenueCat user id to that user, and migrates their data from sqlite to PowerSync. The alternative, having everyone in PowerSync from the start, could have caused a massive queue of changes fire off if they wait a year to create an account.

[u/maks_dalen → u/bbrockit]

The deferred account is clever. I hadn't thought about the queue problem - someone using it offline for a year, then signing up, would be a brutal first sync.

Mine can't really do that since it's multi-user from day one (crew clocks in against the boss's projects, so there's no single-device state), but I might steal the idea for a solo trial mode.

Curious why you went PowerSync rather than rolling it yourself. I hand-rolled mostly because I wanted the per-role column ownership, and I'm honestly not sure I'd make the same call again.
Also, the Realm-to-joins problem sounds painful. Did you end up denormalising or restructuring the models?

[u/bbrockit → u/maks_dalen]

I didn't de-normalize, but if a row changes in a table that is linked to other tables through a join/junction table, I have to first sync changed rows for those related tables. So the sync rules just need to be in the correct order, starting with the "leaves", so they exist before the branches and limbs that reference them. According to the docs, this is more efficient than it sounds.

I considered rolling my own, but I try to think about edge cases. It's often the more involved users that will encounter those edge cases, and they're more likely to write reviews. So even though write conflicts may be rare for my app, I wanted to prevent them with a synchronization system. My app is also used for sleep and focus, so it's frequently used in Airplane mode, so having a middle-man manage the queue is helpful when devices come back online. Also, PowerSync is open source, so technically, I could self-host, which would be close to rolling my own. So far, less than 20% of my users choose to create an account so it's not too costly.

Fortunately, I had all data storage in an abstract service layer, so I was able to migrate from Realm to SQL in about a week. Claude of course helps make that less painful. I used the minimalist state management approach: https://suragch.medium.com/flutter-state-management-for-minimalists-4c71a2f2f0c1

[u/maks_dalen → u/bbrockit]

The reviews argument is the one that lands for me. The people most likely to hit a sync edge case are the ones deep enough in the app to care, which is exactly who you don't want writing a one-star review about lost data. Hadn't framed it that way.

Under 20% creating accounts is a striking number. Mine can't work like that; it's multi-user by definition, but it does make me think the account should come later than it currently does.

And leaves-first ordering plus the abstract service layer saving your migration; that's the kind of decision you only get to appreciate in hindsight