DEV Community

pr0biex
pr0biex

Posted on Originally published at stealthespot.lol

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

#postgres #supabase #nextjs #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 (1)

Collapse
 
gumbosveins profile image
Gumbo Sveins •

Great pattern, especially the distinction between transaction scoped and session locks. One extra edge case worth calling out is verifying the provider signature against the raw request body before parsing JSON, then returning success only after the database transaction commits. For operational visibility, I would also record the event identifier and transaction outcome so a retry can be explained without guessing.