Idempotent offline sync with client-generated operation UUIDs

An offline-first app has one hard problem, and it is not storage. It is that the client cannot tell the difference between "the server never got it" and "the server got it and the reply was lost". Both look like a timeout.

If those two cases have different consequences, the app has to guess, and it will guess wrong on a train. The fix is to make them have the same consequence: send the operation again, and make sending it again do nothing.

Generate the id on the client, at the moment of the action#

The id has to exist before the network does. Generate a UUID when the user presses save, store it in the local queue row alongside the payload, and never change it. Retries send the same id. A retry after the app was killed and reopened sends the same id, because the id was written to local storage in the same transaction as the payload.

await db.operations.add({
  uuid: crypto.randomUUID(),
  type: "trip.create",
  payload,
  createdAt: Date.now(),
  status: "pending",
});

A server-generated id cannot do this job, because getting it requires the round trip that is failing. A hash of the payload cannot either: two genuinely separate trips with the same fare, the same route and the same minute are a real thing, and a content hash would silently merge them.

Enforce it in the database, not in the handler#

The obvious server implementation is to look the id up and skip if it exists. That is a read followed by a write with no lock between them, and two retries arriving together will both read nothing and both insert.

Put the rule where concurrency cannot get around it:

$table->uuid('operation_uuid');
$table->unique(['user_id', 'operation_uuid']);

The handler then attempts the insert and treats a unique violation as success:

try {
    $trip = Trip::create($attributes);
} catch (UniqueConstraintViolationException) {
    $trip = Trip::where('user_id', $userId)
        ->where('operation_uuid', $uuid)
        ->firstOrFail();
}

return $this->accepted($trip);

Two things about this worth stating plainly. The index is scoped by user, so two users can never collide with each other, and a malicious client can only ever collide with itself. And the duplicate path returns the existing record rather than an error, because from the client's point of view the operation did succeed. It just succeeded earlier.

Batch the queue, but isolate the failures#

Sending operations one at a time over a bad connection is slow, so they go in a batch. The trap is treating the batch as a transaction. One operation referring to a deleted record, one payload from an app version that has since changed, and the entire queue rolls back and retries forever. A single poisoned operation stops the user's sync permanently, which is the worst failure mode in the whole design because it is silent and it never resolves.

So the batch is a list, not a unit. Each operation succeeds or fails on its own, and the response says which:

{
  "results": [
    {"uuid": "…", "status": "applied"},
    {"uuid": "…", "status": "duplicate"},
    {"uuid": "…", "status": "failed", "reason": "trip_not_found"}
  ],
  "cursor": "…"
}

The client clears applied and duplicate from its queue identically, because they mean the same thing. A failed operation moves out of the queue and into somewhere the user can see it. It must not stay in the pending queue: an operation that can never succeed will be retried on every sync until the app is uninstalled.

Pull with a cursor, not a timestamp#

The other half of sync is getting changes back. Timestamps are the tempting choice and they are wrong for two reasons: clocks disagree, and rows written in the same second can straddle the boundary, so a > last_seen_at query drops records.

A monotonic cursor the server owns avoids both. The client stores the cursor it was last given and sends it back. The server returns everything after it and the new cursor. There is no clock anywhere in that exchange.

Order matters here too: push before pull, in one round trip if you can. Pulling first means the client applies server state, then overwrites it with local operations that the server has not seen, which produces a visible flicker of stale data.

The rule that makes it feel offline first#

The UI reads the local database and only the local database. Not "the API with a local fallback": the local store, always, with sync as a background process that writes into it.

This is the difference between an app that works offline and an app that mostly works offline. If any screen reads the network directly, that screen is broken on a train, and the one that is broken is always the one the user needs most.

It has a second consequence that is easy to miss. Because the UI never waits for the server, writes appear instantly, and the app feels fast on a good connection for the same reason it works at all on a bad one.

What this does not solve#

Conflicts. Two devices editing the same record still need a rule, and idempotency has no opinion about which edit wins. For records owned by one user and edited on one device at a time, last write wins is usually honest enough. For anything shared, decide the rule before you need it, because the alternative is discovering it in production.

Deletion is the sharp edge. An operation referring to a record that was deleted on another device is the most common failed result in practice, and it should be a visible, quiet failure rather than a retry.

The sync endpoint being reachable regardless of billing state is worth deciding early too. Data a user created on their own device belongs to them, so a lapsed subscription should gate features rather than blocking the queue. A subscription check in front of sync turns a billing problem into permanent data loss.

The offline queue in YathraBook is built on exactly this shape. The same design decides how records are numbered: allocating sequential invoice numbers safely covers the part that idempotency alone does not fix. What it runs on, and what it costs while nobody is using it, is in running Laravel on AWS Lambda with Bref, Neon and Upstash.