max / makenotwork

802 B · 14 lines History Blame Raw

1	-- Fix unique indexes on transactions to exclude NULL item_id (project purchases).
2	-- Previously, two project purchases by the same buyer would collide on the
3	-- (buyer_id, item_id) index when item_id was set to a nil UUID sentinel.
4	-- Now item_id is NULL for project purchases, and NULLs don't participate in
5	-- unique indexes, so the project-level indexes handle deduplication instead.
6
7	DROP INDEX IF EXISTS idx_transactions_buyer_item_completed;
8	CREATE UNIQUE INDEX idx_transactions_buyer_item_completed
9	ON transactions(buyer_id, item_id) WHERE status = 'completed' AND item_id IS NOT NULL;
10
11	DROP INDEX IF EXISTS idx_transactions_buyer_item_pending;
12	CREATE UNIQUE INDEX idx_transactions_buyer_item_pending
13	ON transactions(buyer_id, item_id) WHERE status = 'pending' AND item_id IS NOT NULL;
14