The Postgres error code that makes Stripe webhooks idempotent: 23505

Stripe delivers webhooks at least once. Not exactly once — at least once. A network blip on your side, a 200 that arrives a half-second too late, an event Stripe decides to re-send during an outage, and the same checkout.session.completed lands on your endpoint twice. Sometimes three times. Your handler does not know that.

If that handler grants a subscription, sends a welcome email, or increments a usage counter, "at least once" is how one signup becomes two welcome emails and a double-counted seat. The fix is not a queue, not a distributed lock, not an idempotency framework. It's one Postgres table, one unique constraint, and catching one error code: 23505.

I run a Discord-native Company Brain — teams /save docs and /ask grounded answers, billed at a flat monthly price through Stripe. The entire billing surface is one Fastify route and one Supabase Postgres. Here's the whole idempotency story, in the order the bytes actually arrive.

The problem: webhooks are at-least-once, your side effects are not

A Stripe webhook is a state change that already happened — a card was charged, a subscription renewed. Your job is to reconcile your database to that fact. The trap is that Stripe's delivery guarantee (at-least-once) and your handler's behavior (run the side effect every time) don't match. Run a non-idempotent handler twice and you've corrupted state with no error to tell you.

So before any business logic, two things have to be true: the request is really from Stripe, and you have never processed this exact event before.

Step 1: verify the signature — on the raw body, before anything parses it

The signature check is also your authenticity check; an attacker who can POST to /stripe/webhook can forge events otherwise. But there's a gotcha that eats an afternoon: constructEvent hashes the exact raw bytes Stripe sent. If a global express.json() / Fastify body parser has already turned those bytes into an object and back, the re-serialized JSON won't byte-match, and verification fails on legitimate events.

You have to retain the raw body for this one route. In Fastify, a content-type parser hook that stashes it:

// Retain the raw body so Stripe (and Discord) signature checks see exact bytes.
req.rawBody = body as string;

Then the route verifies against that raw string, never the parsed object:

app.post('/stripe/webhook', async (req, reply) => {
  const signature = req.headers['stripe-signature'];
  if (typeof signature !== 'string' || !req.rawBody) {
    return reply.code(400).send({ error: 'missing signature or body' });
  }
  try {
    const result = await handleStripeWebhook(req.rawBody, signature);
    return reply.code(200).send(result);            // 200 = "I own this now, stop retrying"
  } catch (err) {
    if (err instanceof WebhookSignatureError) {
      return reply.code(400).send({ error: 'signature_invalid' });
    }
    return reply.code(500).send({ error: 'webhook processing failed' }); // 500 = retry me
  }
});

The status codes are the protocol. 2xx tells Stripe "processed, stop retrying." Any non-2xx queues a retry with backoff. That single fact is what makes the next step both necessary and safe.

Step 2: let the unique constraint be the lock — insert first, catch 23505

Here's the move. A tiny table whose only job is to remember which event IDs you've seen:

-- One row per Stripe event. event_id is the natural primary key;
-- Stripe guarantees it's stable across retries of the same event.
CREATE TABLE stripe_events_seen (
  event_id   text PRIMARY KEY,
  event_type text NOT NULL,
  seen_at    timestamptz NOT NULL DEFAULT now()
);

The naive approach is SELECT to check, then INSERT if absent. That's a race: two concurrent deliveries of the same event both SELECT empty, both proceed, both process. The check-then-act gap is exactly wide enough for Stripe's parallel retries to slip through.

So don't check. Insert, and let the database reject the duplicate. The unique constraint is the lock — atomic, no race window, no extra round trip:

async function recordEventSeen(eventId: string, eventType: string): Promise<boolean> {
  const { error } = await supabase
    .from('stripe_events_seen')
    .insert({ event_id: eventId, event_type: eventType });
  if (!error) return true;          // first time we've seen this event
  if (error.code === '23505') return false;   // unique_violation = duplicate, already handled
  throw new Error(`stripe_events_seen insert failed: ${error.message}`);
}

23505 is Postgres's unique_violation SQLSTATE. It is not an error to log and move past — it's a signal: "another delivery of this event already claimed the row." Returning false on it turns the constraint into a deduplication primitive. Two requests race to insert the same event_id; Postgres lets exactly one win and hands the loser a 23505. No advisory lock, no Redis, no SELECT ... FOR UPDATE.

The single most important detail: don't treat 23505 as failure. Plenty of webhook code wraps the insert in a generic try/catch that logs and re-raises — which turns a successful dedup into a 500, which tells Stripe to retry, which hits the same constraint, forever. Catch the one code that means "duplicate" and convert it to a clean early return.

Step 3: dedup once, at the top — not inside every handler

Where you put the check matters. Put it before the event-type switch, so a duplicate is rejected once and no handler ever sees it:

export async function handleStripeWebhook(rawBody: string, signature: string) {
  let event: Stripe.Event;
  try {
    event = stripe.webhooks.constructEvent(rawBody, signature, env.STRIPE_WEBHOOK_SECRET);
  } catch (err) {
    throw new WebhookSignatureError(`signature verification failed`);
  }

  const fresh = await recordEventSeen(event.id, event.type);
  if (!fresh) {
    return { ok: true, duplicate: true, type: event.type };   // 200, did nothing — correct
  }

  switch (event.type) {
    case 'checkout.session.completed':
      await applyCheckoutCompleted(event.data.object); break;
    case 'customer.subscription.created':
    case 'customer.subscription.updated':
      await applySubscriptionUpsert(event.data.object); break;
    case 'customer.subscription.deleted':
      await applySubscriptionDeleted(event.data.object); break;
    case 'invoice.payment_failed':
      await applyPaymentFailed(event.data.object); break;
    default:
      console.log(`unhandled event type: ${event.type}`);
  }
  return { ok: true, duplicate: false, type: event.type };
}

A duplicate returns { ok: true, duplicate: true }, the route sends 200, Stripe stops retrying. One guard protects every current and future handler. Add a new event type next month and it inherits idempotency for free — the most valuable kind of safety is the kind you can't forget to apply.

Step 4: insert-first is at-most-once — know the tradeoff you just made

This is the part most posts skip, and it's the part that bites. Look at the ordering: we insert the seen row, then run the handler. If the handler throws after the insert committed — say applyCheckoutCompleted calls stripe.subscriptions.retrieve and that times out — the route returns 500 and Stripe retries. But the retry hits a row that already exists, gets a 23505, and is deduped away. The event is marked seen and never actually processed. Insert-first buys you at-most-once, and at-most-once can silently drop work.

Two honest ways to handle it, pick by what your handlers do:

Move the insert to the end (process, then record seen). Now it's at-least-once: a mid-handler crash means the row is never written, so Stripe's retry reprocesses cleanly. The cost: your handlers must be idempotent, because they will sometimes run twice.
Keep insert-first, and lean on the fact that your handlers are already idempotent for a different reason. Mine are pure state reconciliation — every one is an UPDATE ... WHERE id = $1:

await supabase.from('workspaces')
  .update({ subscription_status: period.status, current_period_end: period.currentPeriodEndIso })
  .eq('id', workspaceId);     // applying this twice == applying it once

An update-by-key sets the row to a value; running it again sets it to the same value. There's no counter to double, no email sent inline. And Stripe's events overlap: a dropped checkout.session.completed gets re-covered by the customer.subscription.created that fires for the same signup. The state re-converges on the next event. That overlap is why at-most-once is acceptable here — and exactly why it wouldn't be if a handler sent an email or charged a card as a side effect.

The rule that falls out: reconciliation handlers can drop an event safely; side-effecting handlers cannot. Know which kind you're writing before you choose where the insert goes.

Takeaways

Stripe is at-least-once. Design for duplicate delivery as the normal case, not the edge case.
Verify the signature on the raw body. A global JSON parser silently breaks constructEvent on legitimate events — retain raw bytes for the webhook route only.
Insert-first, catch 23505. The unique constraint is a race-free lock. Check-then-insert has a gap that parallel retries walk right through.
Treat 23505 as "duplicate," not "error." Logging-and-reraising it turns successful dedup into an infinite retry loop.
Dedup above the switch, so every handler — present and future — is covered once.
Insert-first is at-most-once. Fine for idempotent reconciliation handlers; dangerous for side-effecting ones. Choose insert order deliberately.

The whole thing is a 40-line file and one table. No queue, no lock service, no idempotency-key plumbing — Postgres already ships the primitive you need, it's just spelled 23505. That economy is the bet behind Acortia: one operator, one database, billing that survives Stripe's retries without a second system to reconcile.

推荐订阅源

DEV Community