max / makenotwork

910 B · 18 lines History Blame Raw

1	-- Per-individual redemption ledger for promo codes.
2	--
3	-- Enforces "once per individual" on reusable comp codes: a (promo_code_id,
4	-- user_id) row is inserted when a user redeems a code, and the UNIQUE
5	-- constraint rejects a second redemption of the same code by the same user.
6	-- The global `promo_codes.use_count` / `max_uses` still bound total uses;
7	-- this table adds the per-user guarantee and is the source of truth for how
8	-- many distinct individuals redeemed a code.
9	CREATE TABLE promo_code_redemptions (
10	promo_code_id UUID NOT NULL REFERENCES promo_codes(id) ON DELETE CASCADE,
11	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
12	redeemed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
13	PRIMARY KEY (promo_code_id, user_id)
14	);
15
16	-- Look up "how many distinct users redeemed this code" quickly.
17	CREATE INDEX idx_promo_code_redemptions_code ON promo_code_redemptions (promo_code_id);
18