DEV Community

pr0biex
pr0biex

Posted on Originally published at stealthespot.lol

Two Webhooks, One Rank: Race-Safe Payments with Postgres Advisory Locks

#nextjs #supabase #postgres #webdev

Two Webhooks, One Rank: Race-Safe Payments with Postgres Advisory Locks

Payment webhooks get retried. Users double-click. Two rivals outbid each other in the same second. If your "apply payment" path isn't concurrency-safe, you get double-applied bids, phantom ranks, and money that doesn't match the leaderboard.

I run Steal the Spot — a pay-to-rank leaderboard where every bid is money and every rank is public. Here's the exact pattern that keeps our board correct, using Supabase Postgres + Dodo webhooks. Three ingredients, all in one function.

1. Serialize with a transaction-scoped advisory lock

Two bids landing "at the same time" must see each other, or both compute rank against a stale snapshot:

perform pg_advisory_xact_lock(hashtext('steal-bid:' || p_season_id));
Enter fullscreen mode Exit fullscreen mode

Why pg_advisory_xact_lock and not pg_advisory_lock: the _xact_ variant auto-releases on commit/rollback. A crashed worker can't leave the board locked forever. One lock per season keeps unrelated seasons parallel.

2. Make the webhook idempotent on payment_id

Payment providers retry webhooks. Ours checks before doing anything:

if exists (select 1 from public.bids where payment_id = p_payment_id) then
  return jsonb_build_object('already_applied', true, ...);
end if;
Enter fullscreen mode Exit fullscreen mode

Retry #2 returns success without touching money. The provider stops retrying, the board never double-counts. This check lives inside the locked transaction, so two concurrent deliveries of the same webhook can't both slip through.

3. Lock the row, compute rank, fail closed 

select * into v_listing
from public.listings
where id = p_listing_id and season_id = p_season_id
for update;

if not found then
  raise exception 'listing not found';
end if;
Enter fullscreen mode Exit fullscreen mode

FOR UPDATE means concurrent bids on the same listing queue instead of interleaving. Rank math is deterministic: strictly better bids plus older ties ahead, excluding self:

select count(*) + 1 into v_rank_to
from public.listings o
where o.season_id = p_season_id
  and o.status = 'active'
  and o.id <> p_listing_id
  and (o.bid_amount_cents > v_new_total
       or (o.bid_amount_cents = v_new_total
           and o.created_at < v_listing.created_at));
Enter fullscreen mode Exit fullscreen mode

Ties break to whoever got there first — no randomness, no arguments.

4. Keep the function away from clients 

revoke all on function public.steal_apply_bid(...) from public, anon, authenticated;
grant execute on function public.steal_apply_bid(...) to service_role;
Enter fullscreen mode Exit fullscreen mode

Only the server (webhook handler with the service-role key) can call it. Rank previews in the UI are estimates; the webhook is the truth. Never let a client write its own rank.

The failure modes this kills

Scenario Without the pattern With it
Provider retries webhook Bid applied twice already_applied, no-op
Two bids, same second Both rank off stale snapshot Serialized, both correct
Worker crashes mid-apply Lock held forever (session lock) _xact_ lock dies with the transaction
User crafts rank via API Fake #1 Function is service-role only

Try the board this protects

stealthespot.lol — list free, pay from $2 to climb, every bid public. Outbid someone and watch the rank move on the next webhook.

Built by @probiex007 — Next.js 16 + Supabase + Dodo, deployed on Vercel.

Top comments (0)