An owned API stores idempotency keys in Postgres and replays the first response

A retry, a double-click, and a webhook redelivery are the same failure when the handler charges or inserts. The second attempt has to come back with the first result: a key, a row in Postgres, and a rule for the request that is still running.

The stack is a Next.js route handler in front of Supabase Postgres. The guarantee is a unique constraint and INSERT ... ON CONFLICT, not a map in the process. Two colors of a deploy share one database. A key held in memory splits when the retry lands on the other color. A drain that ends the process does the same once that handler is gone.

Which writes need a keyCopy Anchor Link

RFC 9110 treats GET, HEAD, PUT, DELETE, OPTIONS, and TRACE as idempotent. POST and PATCH are not. Put a key on a call that can charge, book, grant, send, or insert a row the caller would notice twice: create-order, capture-payment, grant-credit, send-invoice, or a webhook with a side effect.

A read can run twice, and so can a PUT that replaces a resource with a full representation. The command that appends needs a record of the first attempt. A circuit breaker stops the process waiting on a vendor that is already failing. It does not remember that the write succeeded. The idempotency row does.

Where the key livesCopy Anchor Link

The caller sends Idempotency-Key when this click is the same attempt. The same SKU and address, five minutes apart, can be a second purchase. Mint the UUID once per button press.

Stripe's API takes that header on POST, suggests a v4 UUID or another high-entropy string, caps the value at 255 characters, and tells you to keep personal identifiers out of the key. The HTTPAPI Idempotency-Key draft (draft-07, October 2025) asks for the same uniqueness. It is an Internet-Draft, not an RFC, and the datatracker entry lists that revision expired. Copy the behavior. Publish your own retention window. The draft leaves expiry to the resource.

Verify the webhook signature, then use the id on the verified event. A header the sender can change is a new attempt. Scope a derived id to the endpoint or connected account, and a client key to the session owner. The unique pair is (owner_id, idempotency_key). The draft's security notes want the session owner in that pair, so a short key cannot read another caller's stored response.

A missing key is HTTP 400 on a route that requires the header.

How long you keep the rowCopy Anchor Link

Stripe removes idempotency keys after they are at least 24 hours old. A phone that queues an order for several days needs a longer window. Publish that window next to the header name. After expires_at, the same key is a new operation.

Delete expired rows. Index expires_at so the delete is not a sequential scan. The stored body can hold a name, an address, or a client secret, and past the published window it is not a debugging archive. Recover a crashed charge only while Stripe still remembers the outbound key.

What you store with the keyCopy Anchor Link

Store the scoped key, a request hash, a state, the response status, the response body, and expires_at. Hash method, path, and the fields that define the operation, in a fixed order. The draft allows a checksum of the payload or of selected fields. JSON.stringify of a parsed object, with no stable key order, answers 422 when a retry only reorders fields. Include the path, or a key for POST /api/orders can replay against POST /api/refunds.

Stripe saves the status and body of the first execution that started, including a 500. Validation failures and in-flight conflicts are not saved. Validate before you claim the key, so a 400 leaves it free. Once the row exists, a different hash is 422 and the second body does not run. Store a failure after an insert or a charge, or the retry repeats that side effect.

Concurrent duplicatesCopy Anchor Link

INSERT ... ON CONFLICT DO NOTHING is the claim. Unique constraints and ON CONFLICT close the select-then-insert race.

A double-click does not retry a 409, so the loser waits on SELECT ... FOR UPDATE. The row lock holds until the winner commits, then the loser returns the stored status and body. The route timeout caps that wait. A client that retries non-success can take 409 while the first request runs. Stripe saves nothing for that conflict. If the locked read finds no row, the winner rolled back. Return 409.

Return non-2xx until a webhook stores success, then return the stored 2xx. A 409 after success keeps that vendor going. Commit in_progress before the charge, call Stripe with the same key or a fixed derivative, and write the response in a second transaction. A new Stripe key on the crash retry is a second charge. The handler below commits the claim, the order, and the 201 in one transaction.

The table and the routeCopy Anchor Link

owner_id is the session user, or the endpoint or account for a derived id. response_status stays null until the handler finishes. supabase-js runs each statement alone, so the claim and the insert share one Postgres transaction, or one SQL function called with RPC. requireUser, parseOrder, sha256Hex, insertOrder, and withTransaction belong to the route.

create table idempotency_keys (
  owner_id uuid not null,
  idempotency_key text not null,
  request_hash text not null,
  state text not null check (state in ('in_progress', 'completed')),
  response_status integer,
  response_body jsonb,
  created_at timestamptz not null default now(),
  expires_at timestamptz not null,
  primary key (owner_id, idempotency_key)
);

create index idempotency_keys_expires_at_idx
  on idempotency_keys (expires_at);

export async function POST(request: Request) {
  const ownerId = await requireUser(request);
  const key = request.headers.get("idempotency-key")?.trim() ?? "";
  if (key.length === 0 || key.length > 255) {
    return Response.json({ error: "invalid_idempotency_key" }, { status: 400 });
  }

  let payload: unknown;
  try {
    payload = await request.json();
  } catch {
    return Response.json({ error: "invalid_json" }, { status: 400 });
  }
  const order = parseOrder(payload);
  if (!order.ok) {
    return Response.json({ error: order.error }, { status: 400 });
  }

  const requestHash = await sha256Hex(
    JSON.stringify({ method: "POST", path: "/api/orders", order: order.value }),
  );

  const outcome = await withTransaction(async (tx) => {
    await tx.query(
      `delete from idempotency_keys
        where owner_id = $1 and idempotency_key = $2 and expires_at <= now()`,
      [ownerId, key],
    );
    const claimed = await tx.query(
      `insert into idempotency_keys
         (owner_id, idempotency_key, request_hash, state, expires_at)
       values ($1, $2, $3, 'in_progress', now() + interval '24 hours')
       on conflict (owner_id, idempotency_key) do nothing
       returning owner_id`,
      [ownerId, key, requestHash],
    );
    if (claimed.rows.length === 0) {
      const found = await tx.query(
        `select request_hash, state, response_status, response_body
           from idempotency_keys
          where owner_id = $1 and idempotency_key = $2
          for update`,
        [ownerId, key],
      );
      const row = found.rows[0];
      if (!row) return { kind: "retry" as const };
      if (row.request_hash !== requestHash) return { kind: "mismatch" as const };
      if (row.state !== "completed") return { kind: "in_progress" as const };
      return {
        kind: "replay" as const,
        status: row.response_status as number,
        body: row.response_body,
      };
    }
    const created = await insertOrder(tx, ownerId, order.value);
    const body = { id: created.id };
    await tx.query(
      `update idempotency_keys
          set state = 'completed', response_status = 201, response_body = $3::jsonb
        where owner_id = $1 and idempotency_key = $2`,
      [ownerId, key, JSON.stringify(body)],
    );
    return { kind: "created" as const, status: 201, body };
  });

  if (outcome.kind === "mismatch") {
    return Response.json({ error: "idempotency_key_reused" }, { status: 422 });
  }
  if (outcome.kind === "retry" || outcome.kind === "in_progress") {
    return Response.json({ error: "idempotency_in_progress" }, { status: 409 });
  }
  return Response.json(outcome.body, { status: outcome.status });
}

parseOrder keeps a stable field order. The delete removes only an expired row. In this shape, for update sees the winner's committed completed row.

When you don't need thisCopy Anchor Link

A GET does not need a key. A PUT of the representation the client already holds does not need one. A DELETE by primary key is finished when the row is absent. Add a key on delete only when the retry must receive the original body.

A unique email, an overlapping booking, or one ledger line per natural id can already reject the second write. Add this table when the retry must receive the original response, or when the same body is allowed twice and only the attempt id tells them apart. Leave page views, beacons, and intentional counters unkeyed.

The kits do not ship an idempotency-key layer, so on an API you own this table is the piece you add where a retry can charge or create twice.

SourcesCopy Anchor Link

• Stripe idempotent requests
• Idempotency-Key HTTP header field, draft-ietf-httpapi-idempotency-key-header-07
• IETF datatracker entry for the Idempotency-Key draft
• RFC 9110, idempotent methods
• PostgreSQL INSERT, ON CONFLICT
• PostgreSQL unique constraints
• PostgreSQL row-level locks
• Blue-green versus rolling on an owned backend
• Graceful shutdown and drain on an owned backend
• Circuit breakers on an owned backend