Offline-First Messaging: Reconciling Local SQLite With the Server

The Promise We Made to Ourselves

Early on with Sync we made a rule: the app should work in a tunnel. Type a message underground, hit send, and it should look sent. Not spin, not fail, not lose your text. When the network comes back, everything catches up and ends up correct.

On the Flutter client every message lives locally in SQLite through Drift. A send writes to the local database first and renders from that row, so the network is a background detail. That is the optimistic UI trick, and it works right up until you reconcile that optimism with what the server thinks happened. Once you let a disconnected client invent state, you have signed up to merge two histories later and make them agree. That merge cost us a month.

Optimistic Sends and the ID Problem

The first question sounds simple: what is a message's ID?

Our first version let the server assign IDs. The client sent a message, the server generated a primary key and sent it back, and the client updated its local row. That falls apart offline: with no real ID, we gave the message a temporary local one and planned to swap it later. The swap was a bug factory. Anything referencing the message, a reply, a read receipt, the UI list key, had to be rewritten when the real ID arrived, and we always missed a spot.

We flipped it. Now the client generates the message ID, a UUID, at the moment you hit send, offline or not. It is permanent and it is what the server stores, so there is no swap. The UUID also works as an idempotency key: if the client is not sure a send made it through, because it reconnected while a send was in flight or the ack got lost, it sends again with the same ID. The server sees an ID it already has, ignores the duplicate, and acks again. At least once on the wire, exactly once in the database.

Letting the client own identity deleted a whole category of bugs. It was not obvious to us in week one.

Why Timestamps Lie

Next question: how do you order messages?

The obvious answer is the timestamp. Every message has a created_at, sort by that, done. That looks fine until production.

Phone clocks are not trustworthy. They drift, users set them manually, and a device coming back from offline can be several seconds, sometimes minutes, off from the server. We had a QA device running 40 seconds fast, and its messages kept jumping to the bottom of conversations, appearing to be sent "after" replies that were actually written later. Wall clock time tells you roughly when a human acted, not a reliable order across devices.

So we split the two. Timestamps are for humans, the "10:42" next to a message, display only. Ordering is the server's job. When the server accepts a message into a conversation it assigns a monotonic sequence number, unique and strictly increasing within that conversation. That is the source of truth for order. The client sorts by sequence number for anything acknowledged, and falls back to local time only while a message is pending and has no sequence yet.

Because sequence numbers are gapless within a conversation, the client can also tell when it missed something. If the local store has sequences up to 500 and the server says the latest is 512, you know you are missing 501 through 512 and can ask for just that range.

The Local State Machine

Every message on the client lives in a small state machine, stored as a column on the Drift row:

• pending: written locally, not yet acknowledged. Rendered with a clock icon. Has an ID, no sequence number yet.

• sent: the server acknowledged receipt and assigned a sequence number. One tick.

• delivered: the recipient's device acknowledged it. Two ticks.

• failed: we gave up after retries. The UI offers a retry, which sends again with the same idempotency key.

The state only moves forward, and every transition comes from a server event, never a guess. An enum in the database rather than a pile of nullable flags made it debuggable: when something looked wrong we could open the local DB and read the exact state of every message.

The Day We Shipped Ghosts

The first reconciliation was simple. On reconnect the client pushed its pending messages, then pulled everything the server had since the last sync. It produced three failures, all three within an hour of a wider internal rollout.

Duplicates. A message reaches the server, gets processed, and the connection drops before the ack gets back. The client still sees "pending" and sends again on reconnect. The early server created a second message, so the conversation showed the same "on my way" twice. The idempotency key was meant to prevent exactly this, but the server keyed off its own primary key rather than the client's ID, so retries never deduped. Once the client UUID became the unique key on the server, a duplicate insert turned into a conflict the server swallows before acking again.

Wrong ordering. While catching up, the pull and the live stream raced, so a batch of history could land after a newer live message. The client still sorted by timestamps, and those were skewed, so messages shuffled into a nonsensical order that rearranged again as more arrived. Sorting strictly by sequence number, and refusing to render history rows until they had one, stopped it.

Ghost messages. The worst one. A message would appear after reconnect, then vanish a second later. The client showed a pending message, the reconcile pulled server state that did not yet include it because of a race, our sync logic decided the message was orphaned and deleted it, and then the ack arrived for a message we had just thrown away.

Getting Reconciliation Right

We rebuilt the reconcile around a few firm rules.

Reconciliation is a merge, never a replace. Server state is authoritative for ordering and for the existence of acknowledged messages, but a sync never deletes pending messages. They are only promoted to sent when the ack arrives, or moved to failed after retries run out. We removed every code path that could delete a local message as a side effect of pulling server state. If the server has not mentioned your pending message, that means not yet, not never.

Catching up became sequence driven and idempotent. On reconnect the client sends its highest known sequence number per conversation, and the server streams back exactly the messages after it, in order. Applying the same batch twice is harmless: every message keys on its stable UUID, so a repeat is an upsert, not a duplicate. The live stream and the historical pull write through the same upsert path, so it no longer matters which wins the race.

We handled edits and deletes as events, not mutations. An edit is not "change the text of message X" applied blindly. It carries its own sequence number, and the client applies it only if it has not already seen a later edit to the same message. A delete is a tombstone: the row stays, marked deleted, so a copy arriving late during the catch up reconciles against the tombstone and stays hidden instead of coming back.

After this the reconcile became boring. You can turn on airplane mode, fire off a dozen messages and edits, walk into a dead zone, come back twenty minutes later, and the conversation settles into the right state: same order, no duplicates, nothing flickering. We tested it by scripting network drops at random points in the send and ack cycle, hundreds of times, asserting the final local state matched the server. That suite caught more regressions than manual QA ever did.

Key Takeaways

• Let the client own message identity: a UUID generated on the client, doubling as an idempotency key, gives offline messages a permanent ID from the start and makes retries dedupe naturally.

• Order by server sequence numbers, not timestamps: wall clocks drift and lie. Use monotonic sequence numbers per conversation for order, and keep timestamps for display.

• Make local message state an explicit machine: a pending/sent/delivered/failed enum in the database, advanced only by server events, turns "why is this message weird" into something you can read.

• Reconcile by merging, never replacing: never delete a pending local message as a side effect of pulling server state, or you ship ghosts.

• Model edits and deletes as sequenced events with tombstones: apply them idempotently and keep deleted rows as hidden tombstones so late copies reconcile quietly instead of reappearing.