max / makenotwork

3.0 KB · 73 lines History Blame Raw

1	-- Tips, project members, and revenue splits.
2	--
3	-- Project members allow multi-author projects with configurable revenue splits.
4	-- Tips are one-time payments to a creator without a product attached.
5	-- Revenue splits apply to tips AND regular item/project purchases.
6
7	-- ── Project Members ──
8
9	CREATE TABLE project_members (
10	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
11	project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
12	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
13	role VARCHAR(50) NOT NULL DEFAULT 'member',
14	split_percent SMALLINT NOT NULL CHECK (split_percent >= 0 AND split_percent <= 100),
15	added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
16	added_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
17	UNIQUE (project_id, user_id)
18	);
19
20	CREATE INDEX idx_project_members_project ON project_members (project_id);
21	CREATE INDEX idx_project_members_user ON project_members (user_id);
22
23	-- ── Tips ──
24
25	CREATE TABLE tips (
26	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
27	tipper_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
28	recipient_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
29	project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
30	amount_cents INT NOT NULL CHECK (amount_cents > 0),
31	message VARCHAR(280),
32	status VARCHAR(20) NOT NULL DEFAULT 'pending',
33	stripe_payment_intent_id VARCHAR(255),
34	stripe_checkout_session_id VARCHAR(255),
35	stripe_transfer_group VARCHAR(255),
36	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
37	completed_at TIMESTAMPTZ
38	);
39
40	CREATE INDEX idx_tips_recipient ON tips (recipient_id, created_at DESC);
41	CREATE INDEX idx_tips_tipper ON tips (tipper_id, created_at DESC);
42
43	-- ── Revenue Splits ──
44	-- Records the actual split of a payment after it completes.
45	-- Created for both tips and item purchases when the project has members.
46
47	CREATE TABLE revenue_splits (
48	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
49	-- Exactly one of tip_id or transaction_id is set
50	tip_id UUID REFERENCES tips(id) ON DELETE CASCADE,
51	transaction_id UUID REFERENCES transactions(id) ON DELETE CASCADE,
52	recipient_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
53	amount_cents INT NOT NULL CHECK (amount_cents >= 0),
54	split_percent SMALLINT NOT NULL,
55	stripe_transfer_id VARCHAR(255),
56	status VARCHAR(20) NOT NULL DEFAULT 'pending',
57	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
58	completed_at TIMESTAMPTZ,
59	CHECK (
60	(tip_id IS NOT NULL AND transaction_id IS NULL)
61	OR (tip_id IS NULL AND transaction_id IS NOT NULL)
62	)
63	);
64
65	CREATE INDEX idx_revenue_splits_tip ON revenue_splits (tip_id) WHERE tip_id IS NOT NULL;
66	CREATE INDEX idx_revenue_splits_transaction ON revenue_splits (transaction_id) WHERE transaction_id IS NOT NULL;
67	CREATE INDEX idx_revenue_splits_recipient ON revenue_splits (recipient_id, created_at DESC);
68
69	-- ── User tips toggle ──
70
71	ALTER TABLE users ADD COLUMN tips_enabled BOOLEAN NOT NULL DEFAULT false;
72	ALTER TABLE users ADD COLUMN notify_tip BOOLEAN NOT NULL DEFAULT true;
73