A Ledger INSERT Failure Swallowed as a Duplicate

The short version

The Stripe webhook handler treated any INSERT failure on the idempotency ledger stripe_events as a duplicate delivery and returned a 200 no-op. Only a unique violation (23505) actually comes from a Stripe redelivery, but a transient DB write fault returned the same 200, so Stripe read the delivery as successful and never sent it again. The event is lost permanently, unprocessed.

What was happening

The Stripe webhook route first INSERTs a received event into the idempotency ledger stripe_events. If event.id already exists, the INSERT fails with a unique violation (23505), which means “a redelivery of the same event”, so skipping the work and returning 200 is correct.

The pre-fix code took the error returned by that INSERT as the signal for a duplicate, directly.

// INSERT event.id into the idempotency ledger. Unique violation = duplicate delivery → 200 no-op.
const { error: insErr } = await admin.from("stripe_events").insert({
  id: event.id,
  type: event.type,
  payload: {
    api_version: event.api_version,
    object: (event.data.object as { object?: string })?.object ?? null,
  },
});
if (insErr) {
  return Response.json({ received: true, duplicate: true });
}

if (insErr) looks only at whether an error object exists. The comment says “unique violation = duplicate delivery”, but the implementation does not check that. Unique violation or any other reason — a dropped connection, a timeout, a transient DB write fault — if insErr is populated, execution enters the same if block and the same 200 no-op is returned.

Why

Stripe webhooks are built on at-least-once delivery: they keep resending until the receiver returns 200. Which means that once the receiver returns 200, Stripe considers the event processed and never resends it.

This code assumed the only way an INSERT into stripe_events fails is “the same event.id is already in the ledger (accepted before and redelivered)”. But an INSERT can fail for other reasons. When a transient connection drop or write error occurs, the event has in fact never been recorded in the ledger and never been processed. The code nevertheless returns 200 without distinguishing it from a unique violation, so as far as Stripe is concerned it is a record of successful delivery, and the same event never comes again.

The harm from a lost event depends on what the event represented. Lose a successful card-payment event, for example, and the charge stands on Stripe’s side while the contract in the portal remains unbilled. Staff not noticing send the usual bank-transfer invoice, and card charge plus invoice is a double charge.

Fixing it

We branch on insErr.code and return a 200 no-op only for 23505. Every other INSERT failure is logged and returns 500, handing the work to Stripe’s redelivery.

// INSERT event.id into the idempotency ledger. **Only a unique violation (23505)** is a
// duplicate delivery → 200 no-op. Swallowing other INSERT failures (transient DB faults etc.)
// with a 200 loses the event permanently, leading directly to "card paid but contract unbilled
// → staff sends an invoice", a double charge (Reviewer P2(a))
// → return 500 and leave it to Stripe's redelivery (at-least-once).
const { error: insErr } = await admin.from("stripe_events").insert({
  id: event.id,
  type: event.type,
  payload: {
    api_version: event.api_version,
    object: (event.data.object as { object?: string })?.object ?? null,
  },
});
if (insErr) {
  if (String(insErr.code ?? "").includes("23505")) {
    return Response.json({ received: true, duplicate: true });
  }
  console.error("[stripe] event ledger insert failed:", insErr.message);
  return new Response("ledger insert failed", { status: 500 });
}

Returning 500 makes Stripe’s at-least-once delivery work as the retry mechanism it is. If the fault was transient, the next resend’s INSERT succeeds and the event is properly recorded and processed. Narrowing the condition for returning 200 to “this is a unique violation” makes a 200 mean “a state where Stripe really may stop resending”.

Why it wasn’t noticed

Writing if (insErr) is written on the premise that the Supabase client’s error is a general-purpose value indicating “something is wrong”. But for this INSERT specifically there was an assumption that error is populated in exactly one case (a unique violation), and nothing beyond that one case was considered at implementation time.

An INSERT into an idempotency ledger returns both the success pattern “failure = an expected duplicate” and the failure pattern “failure = an unexpected fault” in the same shape, as error. When the code does not actually check the premise the comment states (unique violation = duplicate delivery), a one-liner like if (insErr) quietly drops both into the same branch. Where a routine needs to look at the kind of error rather than its presence, the premise in the comment guarantees nothing about the implementation until the code checks the kind too.