An offline outbox that neither loses nor duplicates records

A field app that records cases, inspections or payments has one job the network cannot be allowed to break: every action the user confirmed must reach the server exactly once. In practice you can only get “at least once” delivery from a phone, so exactly-once is something you build from three parts: a durable local outbox, an idempotent server and a disciplined retry loop.

This article walks through each part with working code. The examples use SQLite on the device and PostgreSQL on the server, but nothing depends on them.

The failure that fools most implementations

Most teams test two cases: the request fails before reaching the server, and the request succeeds. There is a third case, and it is the one that corrupts data:

The server commits the record, then the response is lost on the way back.

The device sees a timeout, concludes the request failed, and sends it again. Without protection, you now have two records. Airplane-mode testing never produces this, because there the request never leaves the phone. You have to design for it.

Part 1: write to the outbox in the same transaction as the data

When the user confirms an action, the app does two things in one local transaction: it applies the change to its own tables, and it appends a row to an outbox.

CREATE TABLE outbox (
  id        TEXT    PRIMARY KEY,                 -- UUID created on the device, never reused
  seq       INTEGER NOT NULL UNIQUE,             -- device-local order (AUTOINCREMENT)
  kind      TEXT    NOT NULL,                    -- e.g. 'inspection.create'
  payload   TEXT    NOT NULL,                    -- JSON, frozen at confirm time
  attempts  INTEGER NOT NULL DEFAULT 0,
  next_try  INTEGER NOT NULL DEFAULT 0,          -- epoch ms
  state     TEXT    NOT NULL DEFAULT 'pending'
            CHECK (state IN ('pending', 'sent', 'rejected'))
);

function confirmInspection(db: Db, form: InspectionForm) {
  db.transaction(() => {
    const id = crypto.randomUUID();
    db.run('INSERT INTO inspection (...) VALUES (...)', /* local copy for the UI */);
    db.run(
      `INSERT INTO outbox (id, seq, kind, payload) VALUES (?, (SELECT COALESCE(MAX(seq), 0) + 1 FROM outbox), ?, ?)`,
      id, 'inspection.create', JSON.stringify({ id, ...form }),
    );
  });
}

Two decisions matter here. The local change and the outbox row commit together, so a crash can never leave one without the other. And the identifier is created on the device, before any network call. That identifier is what makes retries safe.

Part 2: make the server idempotent

The client sends the outbox id as an Idempotency-Key. The server stores every key it has processed, together with a hash of the request and the response it gave. The key insert and the business write happen in the same database transaction:

CREATE TABLE idempotency_key (
  key           text PRIMARY KEY,
  request_hash  bytea NOT NULL,
  status        int   NOT NULL,
  response      jsonb NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now()
);

async function handle(req: Req): Promise<Res> {
  const key = req.header('Idempotency-Key');
  const hash = sha256(req.rawBody);

  return db.transaction(async (tx) => {
    const inserted = await tx.query(
      `INSERT INTO idempotency_key (key, request_hash, status, response)
       VALUES ($1, $2, 0, 'null') ON CONFLICT (key) DO NOTHING RETURNING key`,
      [key, hash],
    );

    if (inserted.rowCount === 0) {                       // seen before
      const old = await tx.one('SELECT * FROM idempotency_key WHERE key = $1', [key]);
      if (!old.request_hash.equals(hash)) return res(422, { error: 'key reused with a different body' });
      return res(old.status, old.response);              // replay the original answer
    }

    const result = await createInspection(tx, req.json);  // the real work
    await tx.query('UPDATE idempotency_key SET status = $2, response = $3 WHERE key = $1',
                   [key, 201, result]);
    return res(201, result);
  });
}

Because everything is one transaction, two copies of the same request arriving at once cannot both succeed: the second INSERT waits on the unique index until the first transaction finishes, then takes the “seen before” path and receives the stored response. The lost-response case is handled the same way: the retry gets the original 201, and nothing is created twice.

Two details are easy to miss:

• Compare the request hash. The same key with a different body is a client bug, and silently replaying the old answer would hide it.
• Keep keys longer than the longest time a device can be offline. If a phone can sit in a drawer for three weeks, a 24-hour key retention turns a safe retry into a duplicate. Choose retention from the real worst case, not from habit.

Part 3: the retry loop

The sender takes the oldest pending row and classifies the outcome. The classification is where most of the judgement lives:

const BASE_MS = 2_000, CAP_MS = 5 * 60_000;
const backoff = (attempts: number) =>
  Math.min(CAP_MS, BASE_MS * 2 ** attempts) * (0.5 + Math.random());   // exponential with jitter

async function drain(db: Db, api: Api) {
  for (;;) {
    const row = db.get(
      `SELECT * FROM outbox WHERE state = 'pending' AND next_try <= ? ORDER BY seq LIMIT 1`, Date.now());
    if (!row) return;

    let res: Response;
    try {
      res = await api.post(`/v1/${row.kind}`, row.payload, { 'Idempotency-Key': row.id });
    } catch {                                              // offline, timeout, connection reset
      return retryLater(db, row);                          // later rows would fail the same way: stop
    }

    if (res.ok)                       markSent(db, row);
    else if (res.status === 429 || res.status >= 500) return retryLater(db, row, res.headers.get('Retry-After'));
    else                              markRejected(db, row, await res.text());   // 4xx: retrying cannot help
  }
}

The rules behind it:

• Network errors and 5xx are retried, with exponential backoff and jitter so a whole fleet of phones coming out of a tunnel does not hit the server at the same second.
• 4xx other than 429 are not retried. A validation error will fail identically on the thousandth attempt. The row moves to rejected, stays on the device and appears in a “needs attention” list for the user. A queue that retries a poison message forever blocks everything behind it.
• Order is by seq, not by timestamp. Device clocks drift and users change them. Use the local sequence for ordering and let the server stamp its own receive time.

If strict global order is not needed, you can avoid one stuck record blocking unrelated ones by keeping an ordering lane per entity (per case, per asset) and draining lanes independently. Keep the single queue until you have evidence you need lanes; it is far easier to reason about.

Updates and conflicts

Creates are the easy half. For edits, send the version the user edited from:

{ "id": "…", "base_version": 7, "changes": { "status": "closed" } }

The server applies the change only if the stored version is still 7 and answers 409 with the current record otherwise. Decide the resolution rule per field before launch (last write wins for notes, merge for lists, human review for anything with legal or financial weight) and tell the user when a conflict was resolved on their behalf.

Test the case that actually hurts

Add a fault-injection switch to your test server that commits the write and then drops the response. Run this scenario in CI:

1. Enqueue three records.
2. Make the server commit each one and then close the connection without answering.
3. Let the client retry until the queue is empty.
4. Assert the server holds exactly three records.

If that test passes, the lost-response case is handled. If you only ever test with the server switched off, it is not.

What to show the user

Three states are enough, and they remove most support calls: saved on this device, waiting to send, sent, plus a visible “needs attention” list for rejected rows. People work calmly when the app tells them the truth about what has and has not left the phone.