max / makenotwork

547 B · 12 lines History Blame Raw

1	-- Prevent duplicate pending transactions for the same buyer+item or buyer+project.
2	-- This closes a TOCTOU race where two concurrent checkout requests both pass
3	-- the has_purchased_item check and create separate pending transactions.
4
5	CREATE UNIQUE INDEX idx_transactions_buyer_item_pending
6	ON transactions (buyer_id, item_id)
7	WHERE status = 'pending' AND item_id IS NOT NULL;
8
9	CREATE UNIQUE INDEX idx_transactions_buyer_project_pending
10	ON transactions (buyer_id, project_id)
11	WHERE status = 'pending' AND project_id IS NOT NULL;
12