Payment webhook idempotency

Stripe retries webhooks on non-2xx, slow responses, or network blips. Handlers must be safe to run multiple times for the same event.id.

Rules

1. Insert event.id first (or check existence) before side effects.
2. On duplicate event.id, return 200 immediately — do not re-grant credits or extend subscription twice.
3. Side effects (DB upsert, emails, credits) must be upserts keyed by stable IDs (subscription_id, payment_intent_id), not blind inserts.

Table

create table public.stripe_webhook_events (
  id text primary key,
  type text not null,
  livemode boolean,
  processed_at timestamptz not null default now()
);

-- RLS: no client access; edge function uses service role only
alter table public.stripe_webhook_events enable row level security;
-- no policies for anon/authenticated

Handler flow

receive event
  → verify signature
  → if event.id exists in stripe_webhook_events → 200 OK (duplicate)
  → begin logical work
  → upsert subscription / order (idempotent on stripe_subscription_id)
  → insert stripe_webhook_events
  → 200 OK

If work fails after partial writes, prefer:

• Transaction where possible, or
• Compensating upsert on retry, or
• Insert event id only after success (accept rare double-work on crash — mitigate with unique constraints on business keys)

Unique constraints (business layer)

alter table public.subscriptions
  add constraint subscriptions_stripe_sub_id_unique unique (stripe_subscription_id);

create table public.orders (
  id uuid primary key default gen_random_uuid(),
  stripe_payment_intent_id text unique,
  user_id uuid not null,
  amount_cents int not null,
  status text not null
);

Ordering

Events may arrive out of order (subscription.updated before checkout.session.completed). Upsert from latest Stripe object via API retrieve when unsure, or handle both paths to the same upsert function.

Avoid

• insert into credits (amount) values (100) without idempotency key per event.
• Returning 500 after successfully granting access (causes retry + duplicate grant).
• Deleting webhook event rows in production.

Checklist

• Primary key = Stripe event.id
• Duplicate returns 200
• Business tables have unique Stripe IDs