Autoincrement dies offline

Two phones leave the shop without a network. Each rings up a sale. Both mint id = 1. One sync wins. The other sale never lands — or worse, overwrites.

That is not a flaky API. It is autoincrement under two offline writers. Each device thinks it owns the next integer. Neither knows the other is doing the same thing. The counter is locally correct and globally meaningless.

This note is that failure and the boring sync shape I use instead. Offline writers need four properties: coordination-free identity, retry-safe delivery, a merge rule for mutable masters, and an append-only model for events. One in-process lab. No network.

Why local correctness is not global correctness

Nothing is wrong on either phone. Local SQLite (or any per-device counter) advances its own sequence. Device A’s sale 1 is true on A. Device B’s sale 1 is true on B. On reconnect both push. The cloud already has 1. Device B’s row collides.

The interesting failure is not “sync broke.” It is that shared sequential identity assumes one writer, and offline quietly creates two.

I ran node lab/offline-sync-outbox.mjs in this repo on 7 August 2026. Two fake devices, one shared in-memory cloud.

• Autoincrement path: A inserts id: 1 (ok). B’s id: 1 → reason: "collision". salesOnCloud stays 1.
• The second insert fails. Nothing was wrong locally. Sync only made the lie visible.

Identity: mint without coordination

We need an identifier whose uniqueness does not depend on coordination between offline writers. A local sequence fails that test. UUID v4 passes it: mint on the device when the row is created; the id is the sync key forever. Two offline writers do not race for the next integer.

Same lab, UUID path: both devices push one sale each. salesOnCloud is 2. Distinct ids. No collision branch.

You still need a queue. Random ids alone do not push, retry, or reject duplicates.

Delivery: outbox and idempotent accept

Each device keeps an outbox — operations not yet accepted by the cloud. Offline work appends. On reconnect, push in order.

create sale → outbox
          ↓
       retry / push
          ↓
cloud: "already have this id"
          ↓
client: mark done

The cloud accepts or rejects. The client marks the op done only on accept, or on a known idempotent reject. For sales, accept is insert-once by id. Same id again → duplicate, not a second row. That is how a retry after a flaky network stays safe.

Mutable masters: whole-row LWW

A product name (and similar “one current row” masters) is not an event. Two devices can edit the same product while offline. Someone has to win. In this lab, last-write-wins by updated_at: keep the later row, reject the earlier one as stale.

updated_at here is an injected logical value, not phone wall-clock time. Device A wrote Tea at 1000. Device B wrote Coffee at 2000. After both push, the name is Coffee. A then replays the old Tea row. Cloud returns accepted: false, reason: "stale". Final name stays Coffee.

One master entity in this note. Same rule applies to more tables; the lab only proves the merge once.

Events: append-only sales

A completed sale is immutable in this pattern. A correction is another event — not an UPDATE of the original row. That is a modeling choice, not a claim that every domain forbids voids forever.

Insert-once by id makes retry safe: duplicate id is a no-op. History stays a log of what happened, instead of a mutable row that UI “edit” wants to rewrite.

Lab: two UUID sales land (salesOnCloud 2). Requeue the first sale. Replay returns reason: "duplicate". Count stays 2.

Lab: four assertions

One run, four claims. allPassed: true.

Scenario	What we need	Result
local autoincrement	shared sequential id	collision; salesOnCloud 1
UUID mint	coordination-free identity	two sales; no collision
LWW master	merge rule for mutable state	stale write rejected; name Coffee
outbox retry	replay without new meaning	duplicate → no-op; count stays 2

Trimmed report:

{
  "autoincrement": {
    "salesOnCloud": 1,
    "pushA": [{ "id": 1, "ok": true, "reason": "insert" }],
    "pushB": [{ "id": 1, "ok": false, "reason": "collision" }],
    "collision": true
  },
  "uuid": {
    "salesOnCloud": 2
  },
  "lww": {
    "nameAfterLate": "Coffee",
    "staleAccepted": false,
    "staleReason": "stale",
    "finalName": "Coffee"
  },
  "append": {
    "salesOnCloud": 2,
    "replayAccepted": false,
    "replayReason": "duplicate"
  },
  "allPassed": true
}

Rerun: node lab/offline-sync-outbox.mjs. UUID values change every run; the counts and reasons should not.

What this does not solve

Clock skew between devices can invert LWW if you trust phone wall clocks without a tie-break. Deletes need their own tombstone or version rule — not covered here. Multi-field concurrent edits need an explicit merge policy; this note does not define one. A CRDT is one possible family of solutions. Here we stop at whole-row LWW for masters and insert-once for sales.

Offline writes need identities they can mint without coordination, and operations they can replay without changing meaning. Boring on purpose. Autoincrement offline is the exciting bug.