Skip to main content

max / makenotwork

3.8 KB · 82 lines History Blame Raw
1 -- Per-creator settlement currency.
2 --
3 -- Every price a creator sets is denominated in one currency, taken from
4 -- `default_currency` on their Stripe Connect account. Until now every amount in
5 -- the system was USD by assumption, which is why `transactions.currency` has sat
6 -- there since migration 001 with a constant value and nothing reading it.
7 --
8 -- Four changes, all of them making a currency stated rather than assumed:
9 --
10 -- 1. users.settlement_currency -- the creator's one currency
11 -- 2. tips.currency -- tips had none at all
12 -- 3. revenue_splits.currency -- a split inherited its parent's currency
13 -- implicitly, which is ambiguous the moment
14 -- two currencies exist
15 -- 4. transactions.currency -- normalised to lowercase
16 --
17 -- CASE. Stripe's API is lowercase throughout and `DbTransaction`'s own default
18 -- is "usd", but the 001 column default was 'USD', so the same field has been
19 -- written in both cases depending on the path. Lowercase wins because it is what
20 -- Stripe says and what the code already builds; the UPDATE below fixes the rows
21 -- that took the column default. The new reader (`SettlementCurrency::from_db`)
22 -- parses either case, so normalising is tidying rather than a fix; it just stops
23 -- one value having two spellings in one column.
24 --
25 -- THE CHECK CONSTRAINTS ARE THE POINT. Six currencies are supported because all
26 -- six are two-decimal and prefix-symbol, which is what lets every amount in the
27 -- codebase stay an integer count of cents. A zero-decimal currency (JPY) reaching
28 -- one of these columns would not be a display bug, it would be a hundredfold
29 -- pricing error, so the database refuses it rather than trusting the writer.
30
31 -- 1. The creator's settlement currency.
32 ALTER TABLE users
33 ADD COLUMN settlement_currency VARCHAR(3) NOT NULL DEFAULT 'usd';
34
35 ALTER TABLE users
36 ADD CONSTRAINT users_settlement_currency_supported
37 CHECK (settlement_currency IN ('usd', 'cad', 'gbp', 'aud', 'nzd', 'eur'));
38
39 COMMENT ON COLUMN users.settlement_currency IS
40 'ISO 4217, lowercase. Mirrors default_currency on the Stripe Connect account '
41 'and is re-read on every account webhook. Every price this creator sets is '
42 'denominated in it.';
43
44 -- 2. Tips had no currency column at all, so a tip in a non-USD creator's
45 -- currency had nowhere to say so, and any split hanging off it inherited
46 -- nothing.
47 ALTER TABLE tips
48 ADD COLUMN currency VARCHAR(3) NOT NULL DEFAULT 'usd';
49
50 ALTER TABLE tips
51 ADD CONSTRAINT tips_currency_supported
52 CHECK (currency IN ('usd', 'cad', 'gbp', 'aud', 'nzd', 'eur'));
53
54 -- 3. Splits state their currency instead of inheriting it implicitly.
55 --
56 -- Backfilled from the parent, which is exactly the relationship the column
57 -- replaces: `revenue_splits` has a CHECK guaranteeing exactly one of tip_id /
58 -- transaction_id is set, so the COALESCE resolves to precisely one parent.
59 -- Every existing row is USD, so this is a no-op today and correct tomorrow.
60 ALTER TABLE revenue_splits
61 ADD COLUMN currency VARCHAR(3) NOT NULL DEFAULT 'usd';
62
63 UPDATE revenue_splits rs
64 SET currency = COALESCE(
65 (SELECT LOWER(t.currency) FROM transactions t WHERE t.id = rs.transaction_id),
66 (SELECT ti.currency FROM tips ti WHERE ti.id = rs.tip_id),
67 'usd'
68 );
69
70 ALTER TABLE revenue_splits
71 ADD CONSTRAINT revenue_splits_currency_supported
72 CHECK (currency IN ('usd', 'cad', 'gbp', 'aud', 'nzd', 'eur'));
73
74 -- 4. Normalise the one currency column that already existed.
75 UPDATE transactions SET currency = LOWER(currency) WHERE currency <> LOWER(currency);
76
77 ALTER TABLE transactions ALTER COLUMN currency SET DEFAULT 'usd';
78
79 ALTER TABLE transactions
80 ADD CONSTRAINT transactions_currency_supported
81 CHECK (currency IN ('usd', 'cad', 'gbp', 'aud', 'nzd', 'eur'));
82