Logging the same meal three times — offline-first writes and an idempotent queue
A weak connection was making nutri. record the same entry two or three times. The fix wasn't a spinner: it was moving the write to a local queue and making the replay idempotent by construction.
See the decision on the product tour or read the code
The bug report was a sentence from the only two users the app has: on a weak connection the interface doesn’t respond, so I log my food three times and seconds later all three appear. That is a data-integrity bug wearing a UX costume, and it is worth writing about because the obvious fix — a spinner, a disabled button — treats the costume.
The duplicate wasn’t a race, it was a wait
The insert awaited the network before anything on screen moved. On a good connection that is invisible; on a subway platform it is twenty seconds of a sheet that stays open and a button that looks untouched. The user taps again. The server, eventually, receives three perfectly valid inserts.
No amount of locking the button fixes the class of the problem, because the problem is that the interface was reporting the network’s state instead of the user’s intent. The intent was recorded the moment they tapped.
Idempotency is a schema decision, not a retry policy
Writes now land in a queue in localStorage and the row renders on the same tap; the network happens behind it, and the queue drains when connectivity returns, when the app comes back to the foreground, and on mount.
The part that actually kills the duplicate is smaller than the queue: the row’s uuid is generated on the client and travels inside the insert payload. Postgres was already generating one server-side; moving that decision to the client turns every retry into the same write. Replaying an insert that already landed collides on the primary key — 23505 — and the queue treats that as done, not as an error. The dedup guarantee is a constraint the database was already enforcing, not a flag anyone has to remember to check.
One more invariant keeps replay order irrelevant: at most one pending operation per id. A delete on a still-pending insert removes it outright — the server never hears about a row that was created and destroyed offline. An edit merges into the pending insert, so the entry reaches the server once, already corrected.
What the queue deliberately does not cover
Copying a day, meal templates, bulk import, drag-to-reorder and “delete day” stay online-only and fail loudly, exactly as before. They are multi-row operations with ordering semantics, and they happen at a desk with signal — queueing them would buy nothing and cost a conflict-resolution model. Pending rows also can’t be dragged: sort_order is written by id, which is meaningless before the row exists. Scope discipline is what kept this a queue and not a sync engine.
A cold start needs a catalog, not just a queue
A queue only helps inside a session that already loaded. Opening the installed PWA cold with no connection still meant an empty app — nothing to search, so nothing to log. So the session cache became a persisted one, with an explicit whitelist: the food and recipe catalog, labels, targets, today’s entries, and the per-100 g values of foods already used. Explicit, because an open-ended cache grows with every day and every dashboard range visited until it hits the storage quota; when the write fails anyway, persistence switches itself off and everything keeps working in memory.
That last key matters more than it looks: without the cached per-100 g values, a food picked offline would log with its nutrients null. The numbers a queued entry shows are computed client-side by a function that deliberately mirrors the SQL view — and the view overwrites them the moment it syncs. Provisional, then replaced by the source of truth. Never invented.
The gotcha worth the write-up
Offline, the Supabase client rejects the promise — it does not return { error }. Every failure path in the codebase that checked only error was silently doing nothing. The offline search fell through to an empty result and looked, convincingly, like a catalog with no matches in it. One try fixed it; the lesson is that a client library’s failure contract is different on the path you never test.
Verified by breaking the network on purpose
window.fetch stubbed to reject every Supabase request, at 375 px, against the real production database with a test account: search returns hits from the persisted catalog, logging 150 g of whole egg renders 232.5 kcal · 19.5 g protein · 186 mg sodium, editing merges into the pending insert, delete and undo behave, the queue survives a full reload, and everything drains on reconnect — leaving exactly one row on the server, with the view’s numbers identical to the ones computed locally.
The PM takeaway: “the app feels slow on bad wifi” and “the app corrupts my data” were the same ticket, and only one of those framings gets prioritized correctly. Optimistic UI is the visible half; the half that makes it safe is deciding who generates the identifier, and letting a database constraint — not a retry policy — be the thing that guarantees a write happens once.