3 Concurrency and Correctness

An ERP (an enterprise resource planning system) is the software of record for a company's inventory, orders and money, and the system other systems trust. When it says 412 units are available to sell, buyers commit purchase orders against that number. This chapter is about making mutations survive the real world. A mutation is any operation that changes stored data rather than just reading it: creating an order, allocating stock, marking an invoice paid. The real world means retries, double-clicks, crashed workers, redelivered webhooks, and two account managers hitting the same SKU at the same second. A SKU is a stock-keeping unit, one specific sellable variant like this shirt in Washed Indigo, size M. Four load-bearing patterns carry the weight: idempotency keys (a tag that makes a repeated request harmless), optimistic concurrency (a save that refuses if someone else edited first), atomic read-check-write (checking and changing in one indivisible step), and the transactional outbox (writing down what you promised to do, in the same breath as doing it). If none of those phrases mean anything yet, that is the correct starting point, because each one is built from scratch below.

What you need to know first

Chapters 1 and 2 stayed inside the database. This chapter steps outside it. Every term below is used without ceremony later, so read it even where it sounds obvious.

A server, and what happens when someone clicks a button

Your account manager works in a browser on a laptop. Your code does not run there. It runs on a server: a computer in a data center, always on, running your program and waiting to be asked things. Here that program is Next.js. The database it talks to, Supabase Postgres, sits on yet another machine.

Click "Commit 6 units" and the browser writes a short message and sends it over the network to your server. That message is an HTTP request. HTTP is HyperText Transfer Protocol, an agreed format for such messages, the way an envelope is an agreed format for letters. Three parts matter:

• a verb (GET means "give me something, change nothing"; POST means "here is data, do something with it")
• a path naming the address inside your app
• and a body written as JSON (JavaScript Object Notation), a plain-text way of writing structured data both sides know how to read, such as {"skuId": "b21a", "qty": 6}

Your server runs a function, probably talks to Postgres, and sends back an HTTP response: a numeric status code plus usually a JSON body. The 200s mean success, the 400s mean the caller did something wrong, the 500s mean your server broke. Individual codes are defined below as they come up. Treat every status code as an instruction to the caller about what to do next.

An endpoint or API route (API = application programming interface, a door into your system meant for programs rather than humans) is one verb-plus-path combination your server answers. In Next.js it is a file exporting a function named POST, and that function is the handler. A mutating endpoint is one whose handler changes stored data.

Why "the client retried" is a sentence you will read constantly

The client is whoever sent the request: a browser, a phone app, a partner's server. Networks fail in mundane ways: Wi-Fi drops, a phone enters a tunnel, your server restarts mid-deploy. When a request stalls or fails, the client sends it again: sometimes automatically from the fetch layer (the shared piece of your front-end code that actually sends HTTP requests, and usually the place retry rules live), sometimes because a human clicked "Commit" twice at a frozen spinner.

What makes retries hard is that a failed request is ambiguous. When the connection dies, the client cannot tell whether the request never arrived (nothing happened) or arrived, ran completely, and only the reply got lost (everything happened). Those look identical from outside, so retrying is the only safe move. Your server therefore receives the same message twice and cannot, by itself, tell that from a user who genuinely meant to allocate twice, unless you build it a way to tell.

Background jobs, workers, and queues

Some work should not happen while a user watches a spinner: emailing a confirmation, pushing an inventory change to your website, reconciling forty thousand SKUs overnight. A queue is the to-do list you defer that onto, and here it is literally a database table. Your handler appends a row saying "tell Shopify this SKU dropped by 6," then returns immediately.

A background job is one item on that list. A worker is a separate program (no browser attached, no user waiting) looping forever: read the next task, do it, mark it done, repeat.

A monorepo is one Git repository holding several apps and the shared code between them. Turborepo is the tool that builds them together. In that layout the worker is its own app, apps/worker. The request now returns in milliseconds, and a failed task stays on the list rather than vanishing.

Webhooks

A webhook is that idea pointed the other way. You hand another company (Stripe, Shopify, your warehouse provider) a URL of yours. When something happens on their side, their server sends an HTTP request to your endpoint: their server phoning yours, unprompted, to say "the customer's card just cleared." They cannot be sure you heard them, so if your reply is slow or lost, they call again. And again. Receiving the same event three times is normal operation, and designing for it is your job.

A race condition, told with two ATMs

One bank account holds $100. Two joint account holders walk up to two cash machines at the same second and each ask for $80. Machine A reads the balance, $100, and checks that $80 fits. It does. Ten milliseconds later, before A writes anything back, Machine B reads the balance, still $100, and its check passes too. Both dispense $80. Both write the new balance as $20. The account sits at $20, and $160 walked out the door.

Nothing malfunctioned. The bug lives in the gap between reading a value and acting on it, a gap in which the world changed underneath. That is a race condition: a bug whose appearance depends on the exact timing of two things at once, which is why it survives every test you run one-at-a-time and detonates on your busiest day. The cure, in every form it takes below, is atomicity. An atomic operation is one indivisible step: nobody can observe it half-done or slip between its parts.

The last term is the setting. A distributed system is any setup where several programs cooperate over a network (browser, Next.js server, Postgres, worker, Stripe), each free to be slow, restart, or repeat itself independently of the rest. That is where your ERP lives.

Why "eventually correct" is really a bug class

Picture the failure that motivates everything in this chapter. A drop is one scheduled release of new product. Your spring drop puts the Kestrel Overshirt in Washed Indigo / M on sale, and it has 6 units of ATS left. ATS is available-to-sell, the number of units you can still promise a buyer.

Two wholesale account managers, one on the phone with a boutique in Austin and one entering a fax-order (yes, still) from a Japanese distributor, both open the order screen, both see "6 available," and both commit 6 units within the same 300 ms. Your database now holds two confirmed orders for 6 units each against 6 sellable units.

Nothing crashed. No error was logged. Every individual query was valid.

You will discover the problem three weeks later when the warehouse can only pick one of the two orders, and you get to choose which retailer you disappoint during their floor-set week, the week a store rebuilds its displays around new product it was promised.

That is the two-ATM story, wearing a work shirt. Same shape, higher stakes.

Why an ERP can't shrug this off

Consumer apps can shrug at this class of bug: a duplicated like, a briefly stale follower count. An ERP cannot, because its numbers are promises: ATS is a promise to a buyer, an invoice total is a promise to their accounts-payable team, an inventory position is a promise to your own auditor.

Chapter 1 gave you the append-only ledger so the past can't be silently rewritten. Chapter 2 gave you the vocabulary of isolation levels and locks. This chapter turns those primitives into the small set of designs you will reuse in every mutation path the ERP has.

Read that callout again slowly, because it is the whole chapter compressed. "Execute twice" is the retry problem. "Half-execute" is the crash-in-the-middle problem. "Execute concurrently with itself" is the two-ATM problem.

One term in it is new: a side effect is anything a request does outside your own database: an email, a call to a partner's API, an inventory push to your storefront. Side effects are the dangerous half of this chapter, because unlike a database row they cannot be taken back. An email you did not mean to send stays sent.

Idempotency: every mutation, every webhook, every job

An operation is idempotent when doing it twice has the same effect as doing it once. Pressing a floor button in an elevator is idempotent, because mashing it five times still takes you to the fourth floor once. Adding a scoop of sugar to coffee is not.

"Set this invoice's status to paid" is naturally idempotent. "Allocate 6 more units" is emphatically not: run it twice and you have allocated 12. An idempotency key is the tool that makes the second kind behave like the first: a unique tag the client attaches to a request so the server can recognize a repeat and decline to do the work again.

The reason retries are dangerous is that a network failure is ambiguous. A server action is a function you write on the server that the browser can call directly, as if it were local. When yours calls POST /api/orders/:id/allocate and the connection drops, you cannot know whether the request died on the way in (nothing happened) or on the way back (everything happened, you just never saw the 200). The client's only safe move is to retry, so the server's only safe design is to make that retry a no-op if the work was already done.

Stripe's idempotency-key rules

Stripe popularized the now-standard mechanism, and its published rules are worth copying because they are unusually precise about the awkward cases. The client generates a unique key, sends it in an Idempotency-Key header on every POST, and reuses the same key when retrying the same logical operation. Stripe accepts keys of up to 255 characters (as of July 2026).

A UUID is a universally unique identifier, a 128-bit value written as 3f2a9c1e-…, of which the version-4 flavor used here carries 122 randomly generated bits (RFC 9562), so the chance of two clients producing the same one is negligible. A header is a small labeled field on the request, alongside the body: writing on the envelope rather than in the letter.

Stripe's documented behavior, as of July 2026, repays a close reading, because each rule exists for a reason:

• The status code and body of the first request are saved and replayed for any retry with that key. To replay means to hand back the stored answer without re-running the work. That replay happens "regardless of whether it succeeds or fails," so even a 500 is replayed, and a replayed response carries an Idempotent-Replayed: true header.
• Results are not saved when the incoming parameters fail validation, or when the request "conflicts with another request that's executing concurrently," because in those cases no endpoint ever started running. Stripe explicitly says those requests can be retried.
• Keys are expected to identify one operation within your account, and Stripe removes them once they are at least 24 hours old. Reuse a key after that and you get a brand-new request.
• Stripe compares the incoming parameters against the original request and "errors if they're not the same to prevent accidental misuse," instead of guessing which request you meant.

That last rule matters more than it looks: silently honoring a mismatched replay would turn a client bug into a wrong-but-successful mutation.

A Postgres-backed idempotency-key table

You can build the same behavior on Supabase Postgres with one table and one unique constraint. A unique constraint is a rule you declare on a table: no two rows may share the same value in these columns. Postgres enforces it absolutely, and it will reject a second insert with a unique constraint violation, an error meaning "that value is already taken."

The constraint is the whole trick: two concurrent requests with the same key race to insert, exactly one wins, and Postgres itself decides which. Your application code never gets a say.

create table idempotency_key (
  id              uuid primary key default gen_random_uuid(),
  tenant_id       uuid not null references tenant (id),
  key             text not null check (char_length(key) between 1 and 255),
  request_method  text not null,
  request_path    text not null,
  request_hash    text not null,        -- sha-256 of canonical body
  status          text not null default 'in_progress'
                    check (status in ('in_progress', 'completed')),
  locked_at       timestamptz not null default now(),
  response_status int,
  response_body   jsonb,
  created_at      timestamptz not null default now(),
  unique (tenant_id, key)                    -- the arbiter
);

create index idempotency_key_reaper_idx on idempotency_key (created_at);

-- Supabase: prune keys at least 24h old, on Stripe's horizon, via pg_cron
select cron.schedule(
  'reap-idempotency-keys', '17 * * * *',
  $$ delete from idempotency_key
     where created_at < now() - interval '24 hours' $$
);

The columns, one by one

Take the columns in order. id is the row's own identifier, generated automatically. tenant_id says which customer of your ERP this row belongs to, and references tenant (id) tells Postgres to reject any value that isn't a real tenant. key is the client's idempotency key, with a length check mirroring Stripe's 255-character limit. request_method and request_path record which endpoint was called.

request_hash deserves its own paragraph. A request hash is a short fingerprint of the request body. (The payload is another word for that body: the actual data the message carries.) You feed the payload through SHA-256, a hashing function that turns any input into a 256-bit fingerprint, normally written as 64 hexadecimal characters. The same input always produces the same fingerprint, and any change to the input produces a completely different one. Storing the fingerprint lets you later ask "is this retry carrying the same payload as the original?" without storing or comparing the whole body.

status is either in_progress (someone is running this right now) or completed (the answer is recorded below). locked_at is when the current attempt started, which is how you later detect an attempt that died. response_status and response_body hold the saved answer to replay, and jsonb is Postgres's binary JSON column type. And unique (tenant_id, key) is the arbiter: the rule that makes exactly one of two simultaneous claims succeed.

Pruning old keys on a schedule

The create index line makes the cleanup job fast: without it, deleting old rows means scanning the whole table. The cron.schedule call uses pg_cron, a Postgres extension that runs SQL on a timer. Supabase ships it and wraps it in a dashboard called Supabase Cron.

The string '17 * * * *' is cron syntax for "at 17 minutes past every hour," and the offset from the top of the hour is deliberate, so the job doesn't pile onto every other job scheduled at :00. Each run deletes keys that are at least 24 hours old, matching the horizon Stripe uses, so the table stays small forever.

Note the multi-tenant scoping: the key is unique per (tenant_id, key), so one tenant's client can never collide with another tenant's, or read its cached response. Stripe asks for the same discipline, telling callers to make a key identify a single operation within their account, and here it composes with your RLS policies. (RLS is row-level security, Postgres's per-row access control from chapter 2.)

Middleware for Next.js route handlers

Middleware is code that wraps your handler: it runs before the handler, decides whether to call it, and can inspect or replace what comes back. It works like a bouncer standing in front of a door: same door, but nobody reaches it without passing inspection.

The middleware has three phases: claim the key (atomic insert), run the handler, record the outcome. Brandur Leach's well-known reference implementation calls the stretches of local database work between external calls "atomic phases." Our simplification is that the handler does all of its work in a single local transaction, which is exactly what makes the recovery story below sound.

The transaction boundary is where BEGIN and COMMIT sit, and everything between them lands together or not at all. Commit makes the changes permanent and visible to everyone. Rollback throws them away as if they never happened. Keeping all the handler's work inside one boundary means there is no such thing as a half-done handler.

// packages/api/src/idempotency.ts
import { createHash } from "node:crypto";
import type { Pool } from "pg";

// Treat an in-flight attempt as dead after 90 seconds.
const LOCK_TIMEOUT_MS = 90_000;

/** Stable stringify: key order must not change the hash. */
function canonicalize(value: unknown): string {
  if (Array.isArray(value)) return `[${value.map(canonicalize).join(",")}]`;
  if (value !== null && typeof value === "object") {
    return `{${Object.entries(value as Record<string, unknown>)
      .sort(([a], [b]) => a.localeCompare(b))
      .map(([k, v]) => `${JSON.stringify(k)}:${canonicalize(v)}`)
      .join(",")}}`;
  }
  return JSON.stringify(value);
}

export function hashRequest(method: string, path: string, body: unknown) {
  return createHash("sha256")
    .update(`${method} ${path} ${canonicalize(body)}`)
    .digest("hex");
}

type HandlerResult = { status: number; body: unknown };
type Handler = (args: {
  tenantId: string; body: unknown; req: Request;
}) => Promise<HandlerResult>;

type Ctx = { tenantId: string };

export function withIdempotency(pool: Pool, handler: Handler) {
  return async (req: Request, ctx: Ctx): Promise<Response> => {
    const key = req.headers.get("Idempotency-Key");
    if (!key || key.length > 255) {
      return Response.json(
        { error: "Idempotency-Key header is required here" },
        { status: 400 },
      );
    }
    const body = await req.json();
    const path = new URL(req.url).pathname;
    const reqHash = hashRequest(req.method, path, body);

    // ── Phase 1: claim. Exactly one caller wins the unique constraint. ──
    const claim = await pool.query(
      `insert into idempotency_key
         (tenant_id, key, request_method, request_path, request_hash)
       values ($1, $2, $3, $4, $5)
       on conflict (tenant_id, key) do nothing
       returning id`,
      [ctx.tenantId, key, req.method, path, reqHash],
    );

    if (claim.rowCount === 0) {
      const { rows: [prior] } = await pool.query(
        `select status, request_hash, response_status,
                response_body, locked_at
         from idempotency_key
         where tenant_id = $1 and key = $2`,
        [ctx.tenantId, key],
      );
      if (prior.request_hash !== reqHash) {
        // Same key, different payload: a client bug. Refuse loudly.
        return Response.json(
          { error: "Idempotency-Key reused with a different body" },
          { status: 422 },
        );
      }
      if (prior.status === "completed") {
        // Replay the recorded outcome, byte for byte.
        return Response.json(prior.response_body,
          { status: prior.response_status });
      }
      const ageMs = Date.now() - new Date(prior.locked_at).getTime();
      if (ageMs < LOCK_TIMEOUT_MS) {
        // Original still running; never run the work twice in parallel.
        return Response.json(
          { error: "A request with this key is already in flight" },
          { status: 409, headers: { "Retry-After": "5" } },
        );
      }
      // Original attempt died (deploy, OOM, crash). Steal the lock.
      await pool.query(
        `update idempotency_key set locked_at = now()
         where tenant_id = $1 and key = $2`,
        [ctx.tenantId, key],
      );
    }

    // ── Phase 2: run the real work (one local DB transaction inside). ──
    let result: HandlerResult;
    try {
      result = await handler({ tenantId: ctx.tenantId, body, req });
    } catch (err) {
      // The handler is transactional, so nothing committed. Release the
      // key so the client's retry re-executes instead of replaying a 500.
      await pool.query(
        `delete from idempotency_key where tenant_id = $1 and key = $2`,
        [ctx.tenantId, key],
      );
      throw err;
    }

    // ── Phase 3: record the outcome so every future retry replays it. ──
    await pool.query(
      `update idempotency_key
       set status = 'completed', response_status = $3, response_body = $4
       where tenant_id = $1 and key = $2`,
      [ctx.tenantId, key, result.status, JSON.stringify(result.body)],
    );
    return Response.json(result.body, { status: result.status });
  };
}

Reading the middleware, piece by piece

That is the longest block in the chapter, so take it in pieces.

What goes in and what comes out. withIdempotency takes two things: a Pool (a set of already-open connections to Postgres, kept warm and reused so you don't pay to reconnect on every request) and your real handler. It returns a new function with the same shape, which is what you actually register as the endpoint. Everything the wrapped handler used to do, it still does, with the claim/replay logic bolted on the front and back.

The helpers. canonicalize turns any value into a text string, sorting object keys alphabetically as it goes. That sorting is the point: {"qty":6,"skuId":"b21a"} and {"skuId":"b21a","qty":6} are the same request, and a naive stringify would fingerprint them differently, making an honest retry look like a mismatched one. hashRequest then feeds method, path, and canonical body through SHA-256 and returns 64 hex characters. Same request in, same fingerprint out, every time.

The type declarations. HandlerResult says a handler must return an object with a numeric status and any body. Handler says a handler is a function receiving the tenant, the parsed body, and the raw request, and returning a Promise of that result. A Promise is TypeScript's representation of "an answer that isn't ready yet," which is why every call to one is preceded by await. Ctx is just a name for the small bag of per-request context your framework hands you, and here it carries the tenant id.

Phase 1: claiming the key

Phase 1, the claim. First the guard clauses: no key, or an over-long key, returns 400 immediately, a cheap rejection before touching the database. Then the insert. The clause on conflict (tenant_id, key) do nothing tells Postgres: if a row with this tenant and key already exists, don't raise an error, just skip. returning id asks for the new row's id back.

So claim.rowCount is 1 if we won the race and 0 if someone else already holds this key. One database round trip decides the whole thing. Postgres is what makes it safe. A check-then-insert written in your own code would carry the same gap as the two ATMs.

The four things that can be true when you lose the race. If rowCount is 0, the code reads the existing row and branches:

1. The stored fingerprint differs from this request's: the client reused a key with a different body, which is a bug on their side, so return 422 and refuse to guess.
2. The status is completed: the original finished, so replay its saved status and body verbatim, and the retry gets the exact answer the first attempt produced.
3. The row is still in_progress and was locked less than 90 seconds ago: the original is genuinely still running, so return 409 Conflict with a Retry-After: 5 header telling the client to come back in five seconds. Stripe uses 409 for the same situation, and its error reference describes 409 as "the request conflicts with another request (perhaps due to using the same idempotent key)." You will occasionally see 425 Too Early suggested here. Avoid it, because RFC 8470 defines 425 for an unrelated problem: a request sent in TLS early data that an attacker might replay.
4. The row is in_progress but older than 90 seconds: the process running it died in a deploy, an OOM (out-of-memory kill), or a crash, so we refresh locked_at to claim it ourselves and fall through to run the work.

Phase 2 and phase 3: run, then record

Phase 2, the run. Call the real handler. If it throws, the handler's transaction has already rolled back, so nothing was written, which means the safe move is to delete the key entirely. Now the client's retry is treated as a brand-new request and actually re-executes, instead of being handed a replayed 500 forever.

Phase 3, the record. Mark the row completed and store the status and body. Every future retry with this key now hits branch (2) above and gets the identical answer.

A double-click, second by second

Here is a double-click. At 14:02:11.000 the rep clicks "Commit," and the browser sends POST /api/orders/8f3c/allocate with key 3f2a…. The insert succeeds, the handler starts, and it takes 400 ms because the database is busy.

At 14:02:11.150 the rep, seeing nothing happen, clicks again. Same key. The insert conflicts, rowCount is 0, the fingerprint matches, the status is still in_progress, and it is only 150 ms old, so the second request gets 409, Retry-After: 5 and does no work.

At 14:02:11.400 the first request commits, records status 200, and returns. At 14:02:16 the browser's automatic retry fires with the same key, finds status completed, and replays the stored 200. Six units allocated, once, and the user saw a success both times.

Two design decisions worth the why

First, deterministic business rejections (a 422 "insufficient ATS") are recorded and replayed, because retrying one would not change the answer and replay keeps the client's view of the order consistent. Unexpected exceptions release the key instead, so the client's retry actually re-executes the work.

That diverges from Stripe, which saves and replays even a 500, and we can afford the difference because our handler mutates only the local database inside one transaction. There is no half-charged card to worry about. If a handler ever grows a mid-flight external call, that shortcut stops being safe and you need Brandur-style recovery points. The cleaner answer for us arrives later in this chapter: keep external calls out of the handler entirely and put them in the outbox.

Second, do your cheap validation (auth, body shape) before claiming the key, matching Stripe's rule that a request failing validation never burns its key.

That tip rests on one idea: the key identifies the intention. One intention ("allocate 6 units to this order") may be transmitted five times, and all five transmissions should carry the one key. If you generate a fresh UUID inside the click handler, every click looks like a different intention as far as the server can tell, and you have built an elaborate mechanism that protects nothing.

Webhooks: the inbox mirror image

Your ERP is also on the receiving end. Stripe (payments for pro-forma invoices), your 3PL (ASN and receipt events), and Shopify (the D2C channel eating into the same inventory pool) all push events at you. A pro-forma invoice is a preliminary bill you send before shipping, usually to collect money up front from a new or overseas account. That is four acronyms in one breath:

• a 3PL is a third-party logistics provider, the outside company running your warehouse;
• an ASN is an advance ship notice, the message that says "this truck is coming, here is what is on it";
• D2C is direct-to-consumer, your own website selling to shoppers;
• and EDI, which appears shortly, is electronic data interchange, the decades-old file format big retailers use to send purchase orders.

All of them deliver webhooks at least once, and all of them will redeliver on any timeout. At-least-once delivery is a promise that a message will arrive, with no promise about how many times. Exactly-once delivery is the tempting fantasy that it arrives precisely once, which no system spanning a network can actually guarantee, because the sender can never distinguish "you didn't get it" from "your acknowledgment got lost."

Everyone who claims exactly-once is really doing at-least-once plus deduplication on the receiving end, which is exactly what you are about to build. A handler that marks an invoice paid must be safe to run five times. The pattern is the mirror image of the key table: record the provider's event ID in the same transaction as the state change, and let a unique constraint reject the rerun.

// apps/web/app/api/webhooks/stripe/route.ts
export async function POST(req: Request) {
  const event = stripe.webhooks.constructEvent(
    await req.text(),
    req.headers.get("stripe-signature")!,
    process.env.STRIPE_WEBHOOK_SECRET!,
  );

  const client = await pool.connect();
  try {
    await client.query("begin");

    // The dedupe guard and the state change commit or fail TOGETHER.
    const seen = await client.query(
      `insert into webhook_inbox (provider, event_id)
       values ('stripe', $1)
       on conflict (provider, event_id) do nothing
       returning event_id`,
      [event.id],
    );
    if (seen.rowCount === 0) {
      await client.query("rollback");
      return new Response("duplicate delivery, already processed",
        { status: 200 }); // 200 so the provider stops retrying
    }

    if (event.type === "payment_intent.succeeded") {
      const invoiceId = event.data.object.metadata.invoice_id;
      await client.query(
        `update invoice set status = 'paid', paid_at = now()
         where id = $1 and status = 'awaiting_payment'`,
        [invoiceId],
      );
      // ...append to the payment ledger (ch. 1), write outbox event (below)
    }

    await client.query("commit");
    return new Response("ok", { status: 200 });
  } catch (err) {
    await client.query("rollback");
    return new Response("retry me", { status: 500 });
  } finally {
    client.release();
  }
}

Reading the Stripe webhook handler

What goes in: an HTTP request from Stripe's servers. What comes out: a status code that tells Stripe whether to stop or try again.

Line by line. constructEvent takes the raw request text, the stripe-signature header, and your secret, and verifies that Stripe really sent this. Anyone on the internet can POST to a public URL, so the signature is what proves the caller is who it claims to be. It also parses the body into an event object.

pool.connect() checks out one specific database connection rather than borrowing a random one per query. That matters here: a transaction lives on a single connection, so begin, the inserts, and commit must all travel down the same wire. The matching client.release() in the finally block returns it to the pool no matter which path the code takes.

After begin, the dedupe insert tries to record this event's ID in webhook_inbox. Same trick as before: on conflict … do nothing, then check rowCount. Zero means you have already processed this event, so roll back and return 200. Returning success for a duplicate feels backwards, but 200 is how you tell Stripe "received and settled, stop redelivering." A 500 here would put you in an infinite redelivery loop over an event you already handled.

If the insert succeeded, this is genuinely new. The handler checks the event type and updates the invoice. Notice and status = 'awaiting_payment' in the WHERE clause, a second layer of safety, so an out-of-order or stale event can never drag an already-refunded invoice back to paid. Then commit makes the inbox row and the invoice change permanent together, and the endpoint returns 200.

The catch block rolls everything back and returns 500, which is the signal for Stripe to redeliver later. Because the rollback undid the inbox insert too, that redelivery will be treated as new and will actually run. Failure and retry are wired to agree with each other.

One lost response, two deliveries

A timeline makes the payoff obvious. At 09:41:02.000 Stripe delivers event evt_1KA. Your handler inserts the inbox row, marks invoice #2213 paid, and commits at 09:41:02.140. The 200 response is lost in the network. Stripe, having heard nothing, redelivers evt_1KA at 09:41:32.000. This time the inbox insert conflicts, rowCount is 0, the handler rolls back and answers 200 without touching the invoice. One payment, one status change, two deliveries, no drama.

The ordering inside the transaction is what makes this correct. If you deduped in a separate step and crashed after recording the event ID but before applying the change, the redelivery would be swallowed and the invoice would stay unpaid forever. One transaction means the guard and the effect are indivisible.

Sync jobs: resumable by construction

The same discipline extends to batch work. Your nightly 3PL inventory reconciliation walks 40,000 SKUs. When it dies at SKU 31,207 during a deploy, "start over and hope" is not a plan and "skip the rest" is worse.

Make the job a sequence of idempotent pages: each iteration upserts one page of rows and advances a cursor row in the same transaction (sync_checkpoint(job_name, cursor, updated_at)). On restart, read the cursor and continue. Because each page is an upsert keyed on (tenant_id, sku_id, snapshot_date), re-running a page that already committed changes nothing. Resumability then comes free: pair idempotent writes with transactional checkpoints and you never have to bolt a retry framework on top.

Two words there may be new. An upsert is a single statement meaning "insert this row, or if one with the same key already exists, update it instead." It is inherently idempotent, because running it twice leaves the same row. A cursor here is just a bookmark: a stored value saying how far the job got. Advancing the bookmark and writing the page in one transaction means the bookmark can never claim progress the data doesn't have.

Optimistic concurrency for documents people edit

Idempotency protects one actor retrying one operation. A different problem appears when two humans edit the same document: a wholesale order header, a style's wholesale/MSRP pricing (MSRP is manufacturer's suggested retail price), a size run (the set of sizes a style is offered in, and how many of each are on the order).

A PO is a purchase order, the buyer's own document saying what they are buying from you. The default behavior of a naive UPDATE is last-write-wins: Priya adjusts the ship window while Dana fixes the customer PO number, Dana saves second, and Priya's ship window silently reverts. Nobody gets an error. The order ships in the wrong window, and the retailer charges you a non-compliance fee.

Priya's change vanishes because a plain UPDATE writes every field on the form, including the fields the user didn't touch, using the values that were on screen when the form was first loaded. Dana's save carries a stale copy of Priya's field and stamps it back over the fresh one.

Pessimistic locks and optimistic control

Holding row locks for the duration of a human's attention span (chapter 2's pessimistic approach) doesn't fit web apps, because browsers vanish without releasing anything. Pessimistic concurrency means assuming a collision will happen and preventing it up front by taking exclusive control: check the document out, lock it, and make everyone else wait. It works beautifully inside a single fast transaction and badly across a coffee break, because there is no reliable moment to take the lock away from someone whose laptop went to sleep.

The standard fit is optimistic concurrency control (OCC): assume collisions are rare, let everyone read and edit freely, and catch the collision at save time. Its engine is a compare-and-swap (CAS): an operation that says "change this value to X, but only if it is still exactly what I last saw." It is one indivisible step, so no one can slip in between the compare and the swap.

alter table wholesale_order
  add column version integer not null default 1;

-- Compare-and-swap: succeeds only if the doc is still at the version
-- this user loaded. One round trip, no locks held between requests.
update wholesale_order
set customer_po       = $4,
    ship_window_start = $5,
    ship_window_end   = $6,
    version           = version + 1,
    updated_at        = now(),
    updated_by        = $3
where tenant_id = $1
  and id        = $2
  and version   = $7          -- the version the client loaded
returning version;

How the version check works

The first statement adds a version column: an ordinary integer that starts at 1 and increases by one on every save. It carries no business meaning. Its only job is to be a compact way of saying "which edition of this document are you looking at."

The update is the compare-and-swap. The $1, $2, $7 tokens are placeholders: you send the SQL and the values separately, which is both faster and the standard defense against SQL injection. (SQL injection is the attack where a value someone typed into a form gets glued into your SQL text and is then executed as SQL commands. Placeholders make that impossible, because the database treats the values strictly as data.)

Three of the four WHERE conditions are ordinary (right tenant, right order). The fourth, version = $7, is the whole pattern. The client sends back the version it loaded with. If the row is still at that version, the update applies and bumps the version to the next number.

If anyone saved in between, the row's version has already moved on, the condition matches nothing, and zero rows are updated. No error, no exception, just a row count of 0, which your code must check.

Timeline. At 09:15:00 Priya opens order #4471 and the form loads version 7. At 09:16:30 Dana opens the same order, and her form also loads version 7. At 09:18:12 Dana saves the corrected PO number with version = 7. The row matches, her fields are written, and the row becomes version 8.

At 09:19:47 Priya saves her ship-window change, still carrying version = 7. The row is at 8. Zero rows updated. Priya's change did not land, and Dana's did not get silently overwritten either. The system now knows something is wrong, which is the only thing that separates this from the version where the fee arrives in the mail.

A row count of 0 is the server's cue. It should hand the UI everything needed to resolve the conflict, and it should never try to guess which of the two edits the user meant to keep:

// packages/api/src/orders/save-header.ts
const res = await pool.query(CAS_UPDATE_SQL, [
  tenantId, orderId, userId,
  input.customerPo, input.shipWindowStart, input.shipWindowEnd,
  input.baseVersion, // the version the form loaded with
]);

if (res.rowCount === 0) {
  const { rows: [current] } = await pool.query(
    `select o.*, u.display_name as updated_by_name
     from wholesale_order o join app_user u on u.id = o.updated_by
     where o.tenant_id = $1 and o.id = $2`,
    [tenantId, orderId],
  );
  return {
    status: 409,
    body: {
      error: "version_conflict",
      yourBaseVersion: input.baseVersion,
      current, // full fresh document: who changed it, when, current values
    },
  };
}
return { status: 200, body: { version: res.rows[0].version } };

Returning a useful 409 conflict

What goes in: the user's edited fields plus input.baseVersion, the version number their form loaded with. What happens: the compare-and-swap runs, and the code branches on res.rowCount. What comes out: one of two responses.

On the happy path, rowCount is 1, and the endpoint returns 200 with the new version number. The form must store that number, because the user's next save has to carry it. Otherwise their own second save would conflict with their own first.

On the conflict path, rowCount is 0, and the code runs a second query to fetch the current state of the document. The join app_user pulls in the display name of whoever last touched it, so the UI can name a human rather than print a UUID. The response is 409 Conflict carrying three things: the error code, the version the user was working from, and the entire fresh document. That last piece is what lets the interface do something genuinely useful instead of just saying "error."

Resolving the conflict in the UI

In the UI, a 409 becomes a small, honest dialog: "Dana updated this order 40 seconds ago." Diff the user's pending fields against current. If the edits touch disjoint fields, offer one-click "rebase & save" (reapply your fields on top of the fresh version and resubmit with the new version). If they collide on the same field, especially money or quantity fields, show both values and make the human choose.

Conflicts are rare in practice, and OCC's real value is that it makes them impossible to miss. Handling them gracefully is a bonus on top. This is the same protocol as HTTP's ETag/If-Match, and your version column is a perfectly good ETag if you expose the API publicly. An ETag is a short string identifying the current edition of a resource, and If-Match tells the server "only apply this if that edition is still current."

Four strategies, compared

Two notes on that last row. A lease is a lock with an expiry date, and a heartbeat is the client periodically saying "still here" to extend it. That pairing is what keeps a pessimistic lock from outliving the browser that took it.

And CRDT/OT stands for conflict-free replicated data type / operational transformation: the machinery behind live co-editing in tools like Google Docs. It merges concurrent edits automatically, which is delightful for a paragraph of prose and unacceptable for a quantity field, because there is no correct automatic merge of "6 units" and "9 units."

The read-check-write race: how ATS oversells

Back to the opening disaster. Here is the code that causes it, in the shape it almost always takes: a Supabase read, an application-level check, and a write:

// BROKEN: a textbook TOCTOU (time-of-check to time-of-use) bug
const { data: pos } = await supabase
  .from("sku_position")
  .select("on_hand, on_order, allocated")
  .eq("tenant_id", tenantId)
  .eq("sku_id", skuId)
  .single();

const ats = pos.on_hand + pos.on_order - pos.allocated;
if (qty > ats) {
  return { ok: false, reason: "insufficient_ats" };   // the CHECK
}

await supabase
  .from("sku_position")
  .update({ allocated: pos.allocated + qty })          // the USE
  .eq("tenant_id", tenantId)
  .eq("sku_id", skuId);

Reading the broken code

TOCTOU, time-of-check to time-of-use, is the name for exactly the two-ATM bug: you check a condition at one moment and act on it at a later moment, and the world is allowed to change in between. It is one of the oldest bug families in computing and it looks completely reasonable on the page.

Read it left to right: the Supabase client builds a query in steps. .from("sku_position") picks the table, .select(…) lists the columns, the two .eq(…) calls filter to one tenant and one SKU, and .single() says "I expect exactly one row, hand me the object rather than a list." So pos ends up holding three numbers read at that instant.

The next line computes available-to-sell as on-hand plus on-order minus already-allocated. Then the check: if the request wants more than is available, refuse. Then the write: set allocated to pos.allocated + qty, where pos.allocated is the number read several milliseconds ago in JavaScript memory.

Now the timeline, with 6 units on hand and nothing yet allocated.

What the timeline shows

Read the last row twice. Tokyo's update wrote 0 + 6, computed from its stale read, straight over Austin's committed 6. The row says 6 allocated. Two orders exist for 6 units each. The position and the orders disagree, and nothing anywhere logged an error.

Under READ COMMITTED (chapter 2, and Postgres's default, so it's what Supabase gives you), both account managers' SELECTs can read allocated = 0 before either UPDATE lands. Both checks pass against a world that no longer exists by the time the writes execute.

Note there are actually two bugs here: the check races (both see 6 available), and the write computes pos.allocated + qty from the stale read, a classic lost update, so the final value can even be lower than reality. No amount of "check again right before writing" fixes this. Each extra check only shrinks the window. The check and the write must be one indivisible unit.

That last sentence is the reason this bug survives so long in real systems. A race window of 150 ms almost never opens when two people order per hour. It opens constantly when four hundred orders arrive in the first minute of a drop, the exact minute you are being watched.

Fix 1 (the default): one atomic statement

Move the check into the write. A single UPDATE with the invariant in its WHERE clause is atomic: Postgres takes the row lock, re-evaluates the predicate against the row's current committed value (after waiting out any concurrent writer), and either applies the change or matches zero rows. Two concurrent allocators serialize on the row lock for the microseconds the statement runs, and the second one sees the first one's write.

An invariant is a statement that must be true at all times. Here it is "allocated never exceeds what we physically have or have inbound." A predicate is just the condition in a WHERE clause. The essential fact: a single SQL statement is atomic in Postgres. Nobody can observe it half-applied, and a second writer aiming at the same row waits for the first to finish and then re-reads the row before deciding.

That behavior is spelled out in the PostgreSQL manual's Read Committed section: after the first transaction commits, "the search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row." That re-evaluation is the entire safety property you are relying on here.

-- Check and commit in one indivisible statement.
update sku_position
set allocated = allocated + $3        -- arithmetic on the CURRENT row value
where tenant_id = $1
  and sku_id    = $2
  and on_hand + on_order - allocated >= $3   -- ATS check inside the lock
returning on_hand + on_order - allocated as ats_after;

-- rowCount = 0  →  insufficient ATS (or unknown SKU): report, don't retry.

-- Belt AND suspenders: make an oversell impossible to represent at all.
alter table sku_position
  add constraint sku_position_no_oversell
  check (allocated <= on_hand + on_order);

Reading the atomic update

Two details carry this statement. First, set allocated = allocated + $3 does the arithmetic inside the database, on the row's current value, rather than sending a number computed in JavaScript from an older read. Postgres holds the row lock while it does it, so there is no window.

Second, the ATS test has moved from an if in your application into the WHERE clause, where it is evaluated against the row as it exists at that instant, after any competing writer has committed. Check and write are now the same operation, so nothing can happen between them. There is no "between."

RETURNING is a Postgres clause that hands back values from the rows a statement just modified, so you don't need a follow-up SELECT (which would race all over again). Here it computes the ATS remaining after the change and labels it ats_after. And rowCount = 0 now carries meaning: the row exists but the condition failed, i.e. there wasn't enough stock. That is a real business answer rather than a transient glitch, so the client should show it to the user and stop. Retrying it would only produce the same refusal.

Replay the same timeline with this statement. At 10:00:00.240 Austin's UPDATE takes the row lock, sees allocated 0, passes 6 + 0 - 0 >= 6, writes allocated 6, commits. At 10:00:00.241 Tokyo's UPDATE was already waiting on that lock. It acquires it, re-reads the now-current row, evaluates 6 + 0 - 6 >= 6, which is false, and matches zero rows. Tokyo's account manager sees "insufficient ATS" while still on the phone with the distributor. The whole contention lasted under a millisecond.

The CHECK constraint as a backstop

The final statement adds a CHECK constraint, a rule Postgres tests on every write, rejecting any row that violates it. The UPDATE above is already correct without it. The constraint exists to protect all the code you haven't written yet.

Two practical notes. The post-write ATS from RETURNING costs you nothing extra, so show it to the account manager and put it in the event payload. And the CHECK constraint earns its keep on the day someone writes a migration script, a "quick fix" endpoint, or an import job that forgets the guard: it turns what would have been a silent oversell into a loud constraint violation. Invariants you care about belong in the schema as well as in the code that is supposed to respect them.

Fix 2: locks or SERIALIZABLE, when the decision won't fit in one statement

Sometimes the check spans tables: allocating against ATS and validating the customer's open credit limit against unpaid invoices, say. Then you need a multi-statement transaction, and chapter 2's tools return. You can SELECT … FOR UPDATE the SKU position row (and the customer credit row) first, so the reads that feed your decision are locked until you commit. Or you can run the whole transaction at SERIALIZABLE and wrap it in a retry loop for error 40001, letting Postgres's SSI detect the dangerous interleavings for you. Both are correct, and they trade differently:

Unpacking that: SELECT … FOR UPDATE reads rows and locks them, so no one else can change them until you commit. That is pessimistic locking, scoped to one short transaction rather than a human's attention span.

SERIALIZABLE is the strictest isolation level from chapter 2. SSI stands for serializable snapshot isolation, the technique Postgres uses to notice that two transactions have interleaved in a way no sequential ordering could produce. When it notices, it aborts one with SQLSTATE 40001, whose official condition name is serialization_failure. On the wire you see ERROR: could not serialize access due to read/write dependencies among transactions.

Nothing was written, and the manual is explicit that applications using serializable transactions must be prepared to run the whole transaction again. That last part is the catch: correctness now depends on every caller actually implementing the retry.

Lock ordering and the zero-row ambiguity

("Lock-order discipline" in row two means: always lock rows in the same order everywhere in your codebase. If one path locks the SKU then the customer and another locks the customer then the SKU, two transactions can end up each holding what the other wants, which is a deadlock. Postgres detects it and kills one, but the cure is to agree on an order and never deviate.)

One tradeoff worth naming for the atomic UPDATE: a zero-row result conflates "not enough ATS" with "SKU doesn't exist," so pair it with a cheap existence check when the distinction matters to the UI. And remember chapter 1: the sku_position row is a derived convenience. The allocation itself must still land in the append-only allocation ledger, in the same transaction, so the position can always be rebuilt and audited.

The transactional outbox: side effects that cannot lie

An allocation isn't done when the row updates. The order-status webhook must fire to the customer's EDI gateway, the D2C channel needs an inventory-decrement push so the website stops selling the units you just promised wholesale, and the account manager wants a confirmation email.

The naive implementation calls those APIs inside the request handler, often inside the open database transaction. This is the dual-write problem: you are writing to two places, your database and someone else's system, with no way to make both happen or neither. It fails in both directions:

• Commit, then crash before the call: the allocation exists but no one was told. The website keeps selling inventory you no longer have, a slow-motion oversell delivered by messaging instead of by SQL.
• Call, then fail to commit: the confirmation email announces an allocation that was rolled back. The buyer plans an order that doesn't exist. (Bonus failure: the HTTP call took 9 seconds, and you held row locks on a hot SKU the whole time.)

That bonus failure is worth dwelling on. While your transaction sits waiting for a partner's slow API, it is still holding the lock on the hot SKU row. Every other allocation for that SKU queues behind it. One sluggish third party becomes your outage.

The outbox pattern

The fix, cataloged by Chris Richardson as the transactional outbox pattern: in the same transaction as the state change, insert a row describing the event into an outbox table. A separate worker (the "message relay") reads the outbox and performs the deliveries. Because the business write and the outbox insert share one local transaction, the event exists if and only if the state change committed, and the dual write collapses into a single write.

Richardson describes two relay styles: polling publisher (query the table on an interval) and transaction log tailing (consume the WAL, which on Supabase means logical replication or the realtime stream). Polling is much simpler and entirely sufficient at ERP event volumes. Reach for log tailing only when polling latency or load measurably hurts.

The name is the explanation: an outbox is the tray where outgoing mail waits. You never hand a letter to the courier mid-sentence. You drop it in the tray, and the courier's rounds are somebody else's schedule. Polling means asking repeatedly on a timer, every second or two: "anything new? anything new?" The WAL is the write-ahead log, Postgres's own sequential record of every change it makes. Tailing it means reading that stream directly instead of querying a table, which is faster and considerably more machinery.

create table outbox (
  id               bigint generated always as identity primary key,
  tenant_id        uuid not null,
  aggregate_type   text not null,        -- 'wholesale_order', 'sku'
  aggregate_id     uuid not null,
  event_type       text not null,        -- e.g. 'order.line_allocated'
  payload          jsonb not null,
  created_at       timestamptz not null default now(),
  attempts         int not null default 0,
  next_attempt_at  timestamptz not null default now(),
  processed_at     timestamptz,
  dead_lettered_at timestamptz,
  last_error       text
);

create index outbox_pending_idx
  on outbox (next_attempt_at, id)
  where processed_at is null and dead_lettered_at is null;

The outbox table, column by column

This table is the queue, the to-do list from the opening section made concrete. id is bigint generated always as identity, meaning Postgres assigns 1, 2, 3, … automatically and forever. Because the numbers always increase, sorting by id gives you insertion order for free.

aggregate_type and aggregate_id name the thing this event is about: "the wholesale_order with this id." (Aggregate is domain-modeling vocabulary for a business object treated as one unit.) event_type names what happened, in past tense: order.line_allocated. payload is the JSON body to deliver.

The next five columns are the retry machinery. attempts counts how many times delivery has been tried. next_attempt_at is the earliest moment to try again. It is set to now() on insert, so a fresh event is immediately eligible, and pushed into the future after each failure. processed_at stays null until delivery succeeds. dead_lettered_at stays null unless the event is given up on. last_error keeps the most recent failure message so a human has something to read at 2 a.m.

The index is worth a close look, because the where clause at the end makes it a partial index: Postgres only indexes rows that are still pending. A year from now the table may hold ten million processed events, but the index only ever contains the handful of undelivered ones, so the worker's "what's ready?" query stays instant no matter how much history accumulates.

The allocation endpoint, end to end

Here's the allocation endpoint assembled end to end: the atomic ATS check, the ledger append, and the outbox insert in one transaction. This one function is chapters 1, 2, and 3 inside a single BEGIN…COMMIT:

// packages/api/src/orders/allocate-line.ts
export async function allocateLine(
  pool: Pool,
  a: { tenantId: string; orderId: string; skuId: string; qty: number },
) {
  const client = await pool.connect();
  try {
    await client.query("begin");

    // 1. Atomic check-and-commit against ATS (no TOCTOU window).
    const upd = await client.query(
      `update sku_position
       set allocated = allocated + $3
       where tenant_id = $1 and sku_id = $2
         and on_hand + on_order - allocated >= $3
       returning on_hand + on_order - allocated as ats_after`,
      [a.tenantId, a.skuId, a.qty],
    );
    if (upd.rowCount === 0) {
      await client.query("rollback");
      return { ok: false as const, reason: "insufficient_ats" };
    }

    // 2. Append to the ledger (ch. 1: the position row is derived).
    await client.query(
      `insert into allocation_ledger
         (tenant_id, order_id, sku_id, qty, entry_type)
       values ($1, $2, $3, $4, 'allocate')`,
      [a.tenantId, a.orderId, a.skuId, a.qty],
    );

    // 3. Record the side effects as data, in the SAME transaction.
    await client.query(
      `insert into outbox
         (tenant_id, aggregate_type, aggregate_id, event_type, payload)
       values ($1, 'wholesale_order', $2, 'order.line_allocated', $3)`,
      [a.tenantId, a.orderId, JSON.stringify({
        skuId: a.skuId, qty: a.qty, atsAfter: upd.rows[0].ats_after,
      })],
    );

    await client.query("commit");
    return { ok: true as const, atsAfter: upd.rows[0].ats_after };
  } catch (err) {
    await client.query("rollback");
    throw err;
  } finally {
    client.release();
  }
}

What goes in: a connection pool and one plain object saying which tenant, which order, which SKU, and how many units. What comes out: either { ok: true, atsAfter } or { ok: false, reason: "insufficient_ats" }. (as const is a TypeScript detail that pins the type to the literal true rather than the general type boolean, which lets the compiler prove that the success branch always has an atsAfter and the failure branch always has a reason.)

What happens, in order. Check out one connection, begin a transaction. Step 1 runs the atomic conditional UPDATE from Fix 1. If it matches zero rows there wasn't enough stock, so roll back (undoing nothing, since nothing has been written yet) and return the refusal.

Step 2 appends a row to the allocation ledger, chapter 1's append-only record of what actually happened. The sku_position row is a fast-lookup summary that can always be rebuilt from these entries, so it must never move without a matching ledger line. Step 3 inserts the outbox row: the event, its payload, and the post-allocation ATS taken from the RETURNING clause of step 1. Then commit.

Now consider what a crash can do. Crash before the commit and all three writes vanish together: no stock allocated, no ledger entry, no event to deliver. Crash after the commit and all three are permanently present: the stock is allocated, the ledger agrees, and the event is sitting in the outbox waiting for the worker, which will pick it up on its next pass whether that is one second or one hour later.

There is no ordering of failures that produces an allocation nobody was told about, or an announcement of an allocation that didn't happen.

The catch block rolls back and rethrows. Rethrowing matters, because the idempotency middleware from earlier is watching for exactly that exception to release the key. The finally block returns the connection to the pool on every path.

The delivery worker: FOR UPDATE SKIP LOCKED

The relay is a plain Node worker in your Turborepo (apps/worker) polling every second or two. The Postgres feature that makes multiple worker instances safe is FOR UPDATE SKIP LOCKED, added in PostgreSQL 9.5 (released January 2016): each worker locks the batch it selects, and other workers skip those locked rows instead of queueing behind them.

The manual is blunt about both the mechanism and the intended use: "any selected rows that cannot be immediately locked are skipped… this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table." A queue-like table is exactly what you have.

Without SKIP LOCKED, plain FOR UPDATE turns your worker fleet into a convoy: everyone waits on whoever grabbed the first row, and N workers process at the speed of one. With it, workers self-partition the queue with zero coordination code. And if a worker crashes mid-batch, its transaction rolls back, its locks release, and the rows simply become claimable again. That is crash recovery for free.

Picture three workers and a queue of sixty events. Worker A grabs rows 1–20 and locks them. Worker B's query arrives a millisecond later, finds 1–20 locked, and, because of SKIP LOCKED, walks straight past them to take 21–40. Worker C takes 41–60. Nobody negotiated. Nobody has a list of who owns what. The database's lock table is the coordination mechanism, and it is already there.

// apps/worker/src/outbox-relay.ts
const BATCH = 20;
const MAX_ATTEMPTS = 12;

export async function drainOutboxOnce(pool: Pool) {
  const client = await pool.connect();
  try {
    await client.query("begin");

    const { rows } = await client.query(
      `select id, tenant_id, aggregate_id, event_type, payload, attempts
       from outbox
       where processed_at is null
         and dead_lettered_at is null
         and next_attempt_at <= now()
       order by id                -- oldest first
       limit $1
       for update skip locked`,   // other workers skip our batch
      [BATCH],
    );

    for (const evt of rows) {
      try {
        // Deliver with the outbox id as the consumer's idempotency key.
        await deliver(evt, { idempotencyKey: `outbox-${evt.id}` });
        await client.query(
          `update outbox set processed_at = now() where id = $1`, [evt.id]);
      } catch (err) {
        const attempts = evt.attempts + 1;
        await client.query(
          `update outbox
           set attempts         = $2,
               last_error       = left($3, 2000),
               -- exponential backoff, capped at one hour
               next_attempt_at  = now() + make_interval(
                 secs => least(2 ^ $2, 3600)),
               dead_lettered_at = case when $2 >= $4 then now() end
           where id = $1`,
          [evt.id, attempts, String(err), MAX_ATTEMPTS],
        );
      }
    }

    // One commit releases the locks; failures keep their backoff.
    await client.query("commit");
  } catch (err) {
    await client.query("rollback"); // batch becomes claimable again
    throw err;
  } finally {
    client.release();
  }
}

Reading the relay worker

What goes in: a connection pool. What happens: one sweep of the queue. What comes out: nothing, because the effects are the deliveries and the updated rows. Some outer loop calls drainOutboxOnce every second or two, forever.

The claim query. The where clause selects events that are not yet delivered, not dead-lettered, and whose next_attempt_at has arrived. order by id takes the oldest first. limit $1 caps the batch at 20 so one worker doesn't swallow the whole backlog. for update locks each selected row for the life of this transaction. skip locked is the addition that carries the whole design: other workers step over rows already claimed rather than block on them.

The delivery loop. For each event, deliver() makes the actual outbound call (a webhook to the customer's EDI gateway, a push to the storefront) and is handed outbox-123 as an idempotency key, so the receiving side can dedupe using exactly the mechanism from the first half of this chapter. On success, stamp processed_at and the row is finished forever.

The failure branch. On a thrown error, one UPDATE does four things:

• It increments attempts.
• It stores the error text, trimmed to 2,000 characters by left($3, 2000) so a giant stack trace can't bloat the table.
• It pushes next_attempt_at into the future using make_interval(secs => least(2 ^ $2, 3600)). make_interval is a built-in Postgres function that turns a number of seconds into a time interval you can add to a timestamp.
• And case when $2 >= $4 then now() end sets dead_lettered_at only once attempts reach MAX_ATTEMPTS. A case with no else yields null, so on every earlier attempt this writes null and the row stays live.

Exponential backoff and jitter

That interval expression is exponential backoff: wait longer after each failure instead of hammering a struggling service. 2 ^ attempts gives 2 seconds after the first failure, then 4, 8, 16, 32, 64, and so on.

With MAX_ATTEMPTS at 12, the longest wait an event ever actually serves is 2 ^ 11 = 2,048 seconds, roughly 34 minutes, and the twelve attempts together span about 68 minutes before the event is set aside. least(…, 3600) caps any single wait at one hour, which does not bite at twelve attempts but stops the curve running away if you raise the budget. A twentieth attempt would otherwise be scheduled about twelve days out.

Backoff is politeness with a selfish motive: a partner API returning errors because it is overloaded recovers faster if you back off, and your events get delivered sooner as a result.

Its usual companion is jitter: adding a small random amount to each wait. Without jitter, a thousand events that all failed during the same outage will all retry at second 2, then all at second 4, then all at second 8, a thundering herd that re-knocks the recovering service over. With jitter, they smear across the window. This code omits jitter for readability. The Stripe article in the reading list explains why your client-side fetch layer should not.

Committing the batch

The commit. One commit at the end persists every processed_at stamp and every backoff update at once, and releases all the row locks. If the worker dies mid-batch, the transaction rolls back, the locks vanish, and every row in that batch becomes claimable by another worker within a second or two. Nothing is lost, though something may be delivered twice.

Holding the row locks while making HTTP calls is a deliberate simplification: at ERP volumes (hundreds to low thousands of events an hour) it's fine, and the crash story is trivially correct.

If a slow endpoint ever makes batch-length lock holds a problem, switch to claim-then-deliver: UPDATE … SET claimed_until = now() + interval '2 minutes' in one short transaction, deliver outside any transaction, then mark processed. That is a visibility-timeout queue, at the cost of a reaper for expired claims. (A visibility timeout means a claimed job becomes invisible to other workers for a fixed window. A reaper is a small periodic job that releases claims whose window expired because the worker holding them died.)

At-least-once, ordering, and poison messages

Be precise about what this machine promises, because your consumers must be built for it. Delivery is at-least-once, never exactly-once: the relay can crash after a successful deliver() but before COMMIT records it, and the event will be sent again on the next pass. That window is inherent to the pattern and appears in every implementation of it.

Chris Richardson's write-up says the same thing plainly: "the Message relay might publish a message more than once… As a result, a message consumer must be idempotent, perhaps by tracking the IDs of the messages that it has already processed."

That is why the relay sends outbox-<id> as an idempotency key: internal consumers dedupe with a webhook_inbox-style unique insert (the exact pattern from earlier in this chapter, now on the other side of the pipe), and external partners get a stable event ID to dedupe on. The loop closes: because everyone downstream is idempotent, at-least-once is as good as exactly-once.

Trace that crash once, so the claim is concrete. The worker delivers event 4,417 to the storefront at 03:12:06.400 and gets a 200 back. At 03:12:06.410, before the batch's COMMIT, the process is killed by a deploy. The transaction rolls back, so processed_at for 4,417 is still null. Two seconds later another worker claims it and delivers it again.

There is no way to eliminate that window, because the delivery and the record-of-delivery live in two different systems, which is the dual-write problem all over again, one layer down. So you stop trying to eliminate it and make the second delivery harmless instead. A consumer is simply whatever receives the event.

Ordering across batches

Ordering is insertion order (ORDER BY id) per batch, but retries and parallel workers can reorder events across batches: if order.line_allocated fails and backs off while order.confirmed succeeds, a consumer sees them inverted.

Where per-aggregate ordering matters, either run event delivery for a given aggregate_id through one worker (partition by hash), or (simpler and usually better) put a per-aggregate sequence number in the envelope (the small wrapper of metadata around the payload: event id, type, version, timestamp) and make consumers order-tolerant: treat events as "something changed, here's the version" and refetch current state rather than replaying deltas blindly.

"Partition by hash" means running the aggregate's id through a hashing function and using the result to pick a worker, so every event about order #4471 always lands on the same worker and therefore stays in order. The second option avoids the coordination entirely: if an event only says "order #4471 changed, it is now at version 12," a consumer that receives version 12 before version 11 can simply ignore the older one and refetch. Events that carry deltas ("subtract 6") break under reordering. Events that carry versions do not.

Poison messages and dead-lettering

Poison messages, events that fail every attempt forever, are why MAX_ATTEMPTS and dead_lettered_at exist. A poison message is one that will never succeed no matter how many times you retry, because the problem is in the message or its destination, not in the network.

Dead-lettering is the act of giving up on it: set it aside in a place where it stops consuming retries but is not deleted, so a human can look at it. A malformed payload, a partner endpoint that 400s on one specific event, a deleted webhook subscription: without a dead-letter valve, one poison event with exponential backoff quietly becomes an eternal retry loop, and with a naive ORDER BY id LIMIT 1 worker it can block the whole queue.

Dead-letter after a bounded number of attempts, alert on dead_lettered_at is not null, and build the tiny admin page that lets a human inspect last_error and requeue after the fix. The queue's job is to never lose an event and never let one event take the system hostage.

All four patterns, one idea

All four patterns in this chapter turn out to be the same move:

• Idempotency keys make a repeated request harmless by letting the database arbitrate with a unique constraint.
• Version columns make a stale save harmless by putting the comparison in the WHERE clause.
• The atomic conditional UPDATE makes a concurrent allocation harmless by putting the check in the WHERE clause.
• The outbox makes a crash harmless by putting the promise inside the transaction.

Every one of them takes a decision you were tempted to make in application code, across a gap in time, and hands it to Postgres to make in one indivisible step. That is the entire craft.

Field notes & further reading

• Stripe — Designing robust and predictable APIs with idempotency: the design rationale, plus client-side guidance (exponential backoff with jitter) that your Next.js fetch layer should implement.
• Stripe API reference — Idempotent requests: the exact production semantics this chapter's middleware mirrors (keys up to 255 characters, removal once keys are at least 24 hours old, replay of the first outcome including 500s, and rejection when the parameters don't match). Verified July 2026.
• Stripe — Errors and status codes: where Stripe documents 409 Conflict for "the request conflicts with another request (perhaps due to using the same idempotent key)," the case our middleware returns 409 for.
• Brandur Leach — Implementing Stripe-like Idempotency Keys in Postgres: the full-strength version with recovery points and atomic phases, for when a handler must make external calls mid-request.
• Chris Richardson — Transactional Outbox pattern (microservices.io): the canonical write-up, including why consumers must be idempotent under at-least-once relay delivery.
• Chris Richardson — Polling Publisher pattern (microservices.io): the relay style this chapter's worker implements ("publish messages by polling the database's outbox table"), along with its known weakness around message ordering.
• PostgreSQL manual — SELECT, "The Locking Clause": the primary source for FOR UPDATE, NOWAIT and SKIP LOCKED, including the note that skipping locked rows "can be used to avoid lock contention with multiple consumers accessing a queue-like table."
• PostgreSQL manual — Transaction Isolation: why Read Committed is the default, how an UPDATE re-evaluates its WHERE clause after waiting on a concurrent writer (the fact Fix 1 rests on), and what SERIALIZABLE asks of your retry loop.
• Netdata — Using FOR UPDATE SKIP LOCKED for queue workflows: a worked walkthrough of the convoy problem, a comparison with advisory locks, and indexing advice for keeping the "what's ready?" query fast.