The bug every ecommerce app eventually hits

User A and User B tap "Buy" on the last item at the same second. Both payment flows succeed. Both orders go through. You oversold.

I hit this the second week my first ecommerce app was live. The only reason I caught it fast was that the item was $1,200. A refund is an annoying email. A refund for $1,200 is an angry one.

Why the obvious fix does not work

The obvious fix is to read stock on the client and block if it is zero. That fails because both clients read the same stock count before either writes. By the time the writes happen, both have already seen stock = 1.

Optimistic concurrency with a stock_version column is better but still races under load if you rely on the client for the check.

The right fix: do the decrement server-side in a single transaction

Move the buy into a Postgres function that does three things atomically:

1. Lock the row with SELECT ... FOR UPDATE.
2. If stock > 0, decrement and insert the order.
3. If stock = 0, raise an exception.

CREATE OR REPLACE FUNCTION buy_item(p_user_id uuid, p_item_id uuid)
RETURNS uuid
LANGUAGE plpgsql
AS $$
DECLARE
  v_order_id uuid;
BEGIN
  PERFORM 1 FROM items WHERE id = p_item_id FOR UPDATE;
  UPDATE items SET stock = stock - 1 WHERE id = p_item_id AND stock > 0;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'out_of_stock';
  END IF;
  INSERT INTO orders (user_id, item_id) VALUES (p_user_id, p_item_id) RETURNING id INTO v_order_id;
  RETURN v_order_id;
END;
$$;

Call it from NestJS or from a Supabase RPC. Either way, stock decrement and order insert are inside one transaction.

Where this gets subtler

• Payment intent first: If you take payment before the decrement, you now have to refund when stock is gone. Better: hold a pending order, confirm stock, then capture payment.
• Cart with multiple items: Lock rows in a consistent order (by id) to avoid deadlocks.
• Back-in-stock emails: Separate concern. Fires from a webhook after successful restock, not from the buy function.

The boilerplate version

AI App Factory ships this Postgres function pre-wired with the ecommerce template. You get the stock lock, the error codes, and the refund path for free.

See pricing