max / makenotwork

1.7 KB · 30 lines History Blame Raw

1	-- Founder pricing window: 50%-off creator-tier prices locked for life for
2	-- creators active when the window closes. Closes at 1,000 creators OR exit-
3	-- beta, whichever first. See `project_founder_pricing.md` for the full plan.
4	--
5	-- Status semantics:
6	-- - is_founder = TRUE once a user starts a creator-tier subscription
7	-- while the founder window is open. Sticky.
8	-- - founder_locked_at = NULL while the window is open. Set to the close
9	-- timestamp by the close-window cron sweep, ONLY for
10	-- users with an active creator_tier_subscription at
11	-- that instant. After the sweep, NULL means "missed
12	-- the snapshot" and that user pays sticker pricing
13	-- on any future creator subscription.
14	--
15	-- Why two columns: `is_founder` is set eagerly on first sub so the checkout
16	-- handler can pick founder price IDs during the window without an extra
17	-- lookup. `founder_locked_at` is the durable lock applied at close-time.
18	-- An account can have is_founder = TRUE and founder_locked_at = NULL during
19	-- the window (currently eligible); both set (locked); is_founder = TRUE and
20	-- founder_locked_at = NULL after the window with no active sub at close
21	-- means they lost eligibility.
22
23	ALTER TABLE users ADD COLUMN is_founder BOOLEAN NOT NULL DEFAULT FALSE;
24	ALTER TABLE users ADD COLUMN founder_locked_at TIMESTAMPTZ;
25
26	-- Partial index for the cron sweep: finds founder-eligible users awaiting
27	-- the lock. Tiny in practice (≤1,000 rows) but cheap to maintain.
28	CREATE INDEX idx_users_founder_pending ON users (id)
29	WHERE is_founder = TRUE AND founder_locked_at IS NULL;
30