max / makenotwork

1.2 KB · 22 lines History Blame Raw

1	-- Tip revenue-split idempotency (Run 20 Payments, chronic money-loss finding).
2	--
3	-- Migration 151 gave transaction revenue splits a unique backstop
4	-- (`revenue_splits_tx_recipient_key`) so `create_transaction_splits` could use
5	-- ON CONFLICT DO NOTHING and a crash-recovery redelivery re-runs safely. Tip
6	-- splits never got the equivalent: `create_tip_splits` was a plain INSERT, so a
7	-- webhook redelivery after a crash between `complete_tip` and the split write
8	-- left collaborators permanently short their share.
9	--
10	-- This index is the backstop that lets `create_tip_splits` use ON CONFLICT and
11	-- makes a double-split structurally impossible. Tip-split rows carry
12	-- transaction_id IS NULL and tip_id IS NOT NULL; transaction-split rows are the
13	-- reverse, so a full unique index on (tip_id, recipient_id) constrains only tip
14	-- rows (transaction rows share the NULL tip_id, which PostgreSQL treats as
15	-- distinct, so they never collide here).
16	--
17	-- NON-CONCURRENT by necessity (sqlx runs each migration in its own transaction).
18	-- Acceptable at current revenue_splits size; revisit if it grows large.
19	CREATE UNIQUE INDEX IF NOT EXISTS revenue_splits_tip_recipient_key
20	ON revenue_splits (tip_id, recipient_id)
21	WHERE tip_id IS NOT NULL;
22