max / makenotwork

1.7 KB · 41 lines History Blame Raw

1	-- Extend the completion_effects outbox to accept fan_plus and creator-tier
2	-- subscriptions as parents. These are separate tables from `subscriptions`
3	-- (content-creator subscriptions); the original migration only modeled the
4	-- three parent types known at the time. Each new parent gets the same
5	-- (parent_id, kind) unique-index treatment for at-most-once enqueue.
6
7	ALTER TABLE completion_effects
8	ADD COLUMN fan_plus_subscription_id UUID
9	REFERENCES fan_plus_subscriptions(id) ON DELETE CASCADE;
10
11	ALTER TABLE completion_effects
12	ADD COLUMN creator_subscription_id UUID
13	REFERENCES creator_subscriptions(id) ON DELETE CASCADE;
14
15	-- Replace the original 3-way exactly-one CHECK with a 5-way version.
16	ALTER TABLE completion_effects DROP CONSTRAINT completion_effects_check;
17	ALTER TABLE completion_effects ADD CONSTRAINT completion_effects_check
18	CHECK (
19	(transaction_id IS NOT NULL)::int +
20	(tip_id IS NOT NULL)::int +
21	(subscription_id IS NOT NULL)::int +
22	(fan_plus_subscription_id IS NOT NULL)::int +
23	(creator_subscription_id IS NOT NULL)::int = 1
24	);
25
26	CREATE INDEX idx_completion_effects_fan_plus_subscription
27	ON completion_effects (fan_plus_subscription_id)
28	WHERE fan_plus_subscription_id IS NOT NULL;
29
30	CREATE INDEX idx_completion_effects_creator_subscription
31	ON completion_effects (creator_subscription_id)
32	WHERE creator_subscription_id IS NOT NULL;
33
34	CREATE UNIQUE INDEX uq_completion_effects_fan_plus_kind
35	ON completion_effects (fan_plus_subscription_id, kind)
36	WHERE fan_plus_subscription_id IS NOT NULL;
37
38	CREATE UNIQUE INDEX uq_completion_effects_creator_subscription_kind
39	ON completion_effects (creator_subscription_id, kind)
40	WHERE creator_subscription_id IS NOT NULL;
41