max / makenotwork

1.7 KB · 33 lines History Blame Raw

1	-- P1: Project pricing
2	ALTER TABLE projects ADD COLUMN pricing_model VARCHAR(20) NOT NULL DEFAULT 'free';
3	ALTER TABLE projects ADD COLUMN price_cents INT NOT NULL DEFAULT 0 CHECK (price_cents >= 0);
4	ALTER TABLE projects ADD COLUMN pwyw_min_cents INT;
5
6	-- P1: Project purchases (reuse transactions table)
7	ALTER TABLE transactions ADD COLUMN project_id UUID REFERENCES projects(id) ON DELETE SET NULL;
8	CREATE UNIQUE INDEX idx_transactions_buyer_project_completed
9	ON transactions (buyer_id, project_id)
10	WHERE status = 'completed' AND project_id IS NOT NULL;
11
12	-- P2: Item-level subscription tiers
13	ALTER TABLE subscription_tiers ALTER COLUMN project_id DROP NOT NULL;
14	ALTER TABLE subscription_tiers ADD COLUMN item_id UUID REFERENCES items(id) ON DELETE CASCADE;
15	ALTER TABLE subscription_tiers ADD CONSTRAINT tier_exactly_one_target
16	CHECK ((project_id IS NOT NULL AND item_id IS NULL)
17	OR (project_id IS NULL AND item_id IS NOT NULL));
18
19	-- P2: Item-level subscriptions
20	ALTER TABLE subscriptions ALTER COLUMN project_id DROP NOT NULL;
21	ALTER TABLE subscriptions ADD COLUMN item_id UUID REFERENCES items(id) ON DELETE CASCADE;
22	ALTER TABLE subscriptions ADD CONSTRAINT sub_exactly_one_target
23	CHECK ((project_id IS NOT NULL AND item_id IS NULL)
24	OR (project_id IS NULL AND item_id IS NOT NULL));
25	CREATE UNIQUE INDEX idx_subscriptions_subscriber_item_active
26	ON subscriptions (subscriber_id, item_id)
27	WHERE status = 'active' AND item_id IS NOT NULL;
28
29	-- Indexes
30	CREATE INDEX idx_subscription_tiers_item_id ON subscription_tiers (item_id) WHERE item_id IS NOT NULL;
31	CREATE INDEX idx_subscriptions_item_id ON subscriptions (item_id) WHERE item_id IS NOT NULL;
32	CREATE INDEX idx_transactions_project_id ON transactions (project_id) WHERE project_id IS NOT NULL;
33