How Postgres Advisory Locks Prevent Double-Counted Bids in Pay-to-Rank Apps

A developer behind Steal the Spot, a pay-to-rank leaderboard where every bid is real money, shared a concurrency-safe payment pattern built on Supabase Postgres and Dodo webhooks. The core problem addressed is duplicate webhook deliveries and simultaneous bids causing incorrect rankings or double-applied payments. The solution uses pg_advisory_xact_lock to serialize bids per season, an idempotency check that detects already-processed payment IDs, and a FOR UPDATE row lock to prevent interleaved rank calculations. The advisory lock's transaction-scoped variant ensures a crashed worker cannot permanently block the system. Access to the underlying database function is restricted to the service role, preventing clients from manipulating their own rank directly.