max / makenotwork

2.5 KB · 49 lines History Blame Raw

1	-- What Stripe actually presented to the buyer, when it differed from the sale.
2	--
3	-- On the convert-at-checkout path Stripe converts the price into the buyer's
4	-- local currency and charges that. The sale stays denominated in the creator's
5	-- currency (`transactions.currency`), which is what the creator is paid and what
6	-- every revenue figure counts. These two columns record the other side of it:
7	-- the number the buyer actually saw and will find on their statement.
8	--
9	-- WHY IT IS STORED RATHER THAN SHOWN LIVE. Stripe reports it in
10	-- `presentment_details` on `checkout.session.completed`, which arrives after
11	-- payment. It cannot be quoted in advance, and the conversion fee is never
12	-- broken out at all -- it is inside a rate Stripe varies between 2 and 4
13	-- percent. So the honest split is: a disclosed range before the buyer commits,
14	-- and this exact figure on the receipt afterwards, with nothing estimated in
15	-- between.
16	--
17	-- NULL means the buyer paid in the creator's currency and there was nothing to
18	-- convert, which is the common case. It does not mean "unknown": a conversion
19	-- that happened always reports.
20
21	ALTER TABLE transactions
22	ADD COLUMN presentment_amount_cents BIGINT
23	CHECK (presentment_amount_cents IS NULL OR presentment_amount_cents >= 0);
24
25	ALTER TABLE transactions
26	ADD COLUMN presentment_currency VARCHAR(3);
27
28	-- Both or neither. A currency with no amount cannot be rendered, and an amount
29	-- with no currency is the exact ambiguity this whole change set exists to remove.
30	ALTER TABLE transactions
31	ADD CONSTRAINT transactions_presentment_pair_complete
32	CHECK (
33	(presentment_amount_cents IS NULL AND presentment_currency IS NULL)
34	OR (presentment_amount_cents IS NOT NULL AND presentment_currency IS NOT NULL)
35	);
36
37	COMMENT ON COLUMN transactions.presentment_amount_cents IS
38	'What the buyer was actually charged, in presentment_currency, when Stripe '
39	'converted at checkout. NULL when no conversion happened. The creator is '
40	'paid amount_cents in currency regardless.';
41
42	-- Deliberately NOT constrained to the six settlement currencies: Stripe presents
43	-- in 150+ markets, so a buyer can pay in a currency MNW would never settle in.
44	-- This column is a record of what happened, not a value we chose, and rejecting
45	-- an accurate report would lose the receipt line rather than prevent anything.
46	COMMENT ON COLUMN transactions.presentment_currency IS
47	'ISO 4217, lowercase. Any currency Stripe can present, not just the six MNW '
48	'settles in.';
49