max / makenotwork

1.4 KB · 27 lines History Blame Raw

1	-- Guest checkout: allow purchases without an MNW account.
2	-- buyer_id becomes nullable; guest purchases store the buyer's email from Stripe.
3	-- download_token provides authenticated-less download links sent via email.
4	-- claim_token allows attaching the purchase to an account later.
5
6	ALTER TABLE transactions ALTER COLUMN buyer_id DROP NOT NULL;
7
8	ALTER TABLE transactions ADD COLUMN guest_email VARCHAR(255);
9	ALTER TABLE transactions ADD COLUMN claim_token UUID;
10	ALTER TABLE transactions ADD COLUMN claimed_by UUID REFERENCES users(id) ON DELETE SET NULL;
11	ALTER TABLE transactions ADD COLUMN download_token UUID DEFAULT gen_random_uuid();
12
13	-- Look up transactions by claim token (one-time claim flow).
14	CREATE INDEX idx_transactions_claim_token ON transactions(claim_token) WHERE claim_token IS NOT NULL;
15
16	-- Look up guest purchases by email (auto-attach on signup).
17	CREATE INDEX idx_transactions_guest_email ON transactions(guest_email) WHERE guest_email IS NOT NULL;
18
19	-- Look up transactions by download token (email download links).
20	CREATE UNIQUE INDEX idx_transactions_download_token ON transactions(download_token) WHERE download_token IS NOT NULL;
21
22	-- The existing partial unique index on (buyer_id, item_id) WHERE status = 'pending'
23	-- still works for logged-in purchases. Add a separate one for guest deduplication.
24	CREATE UNIQUE INDEX idx_transactions_pending_guest_item
25	ON transactions(guest_email, item_id)
26	WHERE status = 'pending' AND guest_email IS NOT NULL;
27