Two shoppers, one unit left, and both of them get it

[SoloCron]

Every shop schema I've written started the same way: read the stock, check it's enough, insert the order. Under one user that's correct. Under two clicking at the same moment it quietly sells the same unit twice, and you find out from the customer.

I put the fix in a standalone MIT module so I'd stop rewriting it:

https://github.com/SoloCron/no-oversell

It's a cart advisory lock, then the stock rows taken in a fixed order with for no key update, then the reservation — so two carts can never interleave. Holds expire on clock_timestamp(), and commit is idempotent so a repeated webhook can't decrement twice.

The part that might be useful to anyone here: npm test boots a throwaway Postgres and races real connections against each other rather than mocking. It also ships test/naive.sql — the ordinary read-then-write version — and proves that one overselling in the same scenario, so you can see the failure before the fix.

One Supabase-specific thing I got wrong first time and would flag to anyone doing this: my storefront view was auto-updatable and ran as its owner, so with default grants anon could UPDATE through the view and write straight into the stock table, past RLS and past every lock in the module. RLS protects tables, not views. REVOKE before you GRANT.

Happy to be told I've missed something.

[https-shubhamsahu]

Nice write-up, and shipping naive.sql next to the fix is a good way to show the bug. I read through 02_functions.sql, and I couldn't find a hole in the locking. The thing I'd poke at is abuse rather than races.

1. Holds can be kept alive forever. stock_extend is granted to anon and resets expires_at to clock_timestamp() + ttl with no ceiling. A script that reserves the whole stock once and calls extend every 14 minutes keeps every unit off the shelf indefinitely, and never pays. Calling stock_reserve again on the same cart does the same thing, because it deletes and re-inserts that cart's holds with a fresh expiry inside the lock. So a lifetime cap can't live on the hold row. It needs to live on the cart: a small carts table with first_reserved_at, and reserve/extend refusing to push expires_at past, say, first_reserved_at + 2 * ttl.

2. Cart ids are minted by the client, so the number of carts is unbounded. Even with a lifetime cap, one client can open N carts, and each one takes whatever is left. A per-cart quantity cap doesn't help against that. On Supabase, anonymous sign-ins are a decent fix. If the storefront calls signInAnonymously(), every shopper has a real auth.uid(), and the functions can key the cart on that instead of accepting p_cart_id from the caller. That also removes the bearer-token cart id you call out in the README (nothing to guess or leak), and it gives you something to cap, e.g. max units held per uid across all skus. It doesn't stop someone creating lots of anonymous users, but that goes through Auth's rate limits and optional CAPTCHA instead of being free.

Neither of these is a correctness bug in what you built, but for limited drops they're usually what goes wrong right after the oversell is fixed. I hit the same shape of problem with single-use links in a file-sharing app I'm building, so this was a useful read.