g1t/services/billing/migrations/0024_spend_caps.sql
| 1 | -- What g1t pays for itself, and the two caps on it (src/budget.rs). |
| 2 | -- |
| 3 | -- g1t_spend: at cost, by day, what paid (comped, trial, oss, given, |
| 4 | -- unpaid) and billing account, added to as work settles. The daily |
| 5 | -- breaker reads today's total; a comped account's monthly budget reads its |
| 6 | -- month's comped rows. |
| 7 | CREATE TABLE IF NOT EXISTS g1t_spend ( |
| 8 | day TEXT NOT NULL, |
| 9 | bucket TEXT NOT NULL, |
| 10 | account TEXT NOT NULL, |
| 11 | micros INTEGER NOT NULL DEFAULT 0, |
| 12 | PRIMARY KEY (day, bucket, account) |
| 13 | ); |
| 14 | CREATE INDEX IF NOT EXISTS g1t_spend_by_account ON g1t_spend (account, bucket, day); |
| 15 | |
| 16 | -- The daily breaker: when it tripped and staff were told, and a lift for |
| 17 | -- the rest of the day. |
| 18 | CREATE TABLE IF NOT EXISTS spend_breaker ( |
| 19 | day TEXT PRIMARY KEY, |
| 20 | tripped_at TEXT, |
| 21 | tripped_micros INTEGER, |
| 22 | told_at TEXT, |
| 23 | lifted_by TEXT, |
| 24 | lifted_at TEXT, |
| 25 | lift_note TEXT |
| 26 | ); |
| 27 | |
| 28 | -- Comped budgets' 50, 75, 90 and 100% alerts, once each a month. |
| 29 | CREATE TABLE IF NOT EXISTS budget_alerts ( |
| 30 | account TEXT NOT NULL, |
| 31 | month TEXT NOT NULL, |
| 32 | level INTEGER NOT NULL, |
| 33 | sent_at TEXT NOT NULL, |
| 34 | PRIMARY KEY (account, month, level) |
| 35 | ); |
| 36 | |
| 37 | -- This month so far, from the ledger, so the caps start from what was |
| 38 | -- already spent. Billing takes no real money yet (Stripe's test key), so |
| 39 | -- what no trial, pool or g1t itself paid is 'unpaid'. Usage on a |
| 40 | -- workspace's own model provider costs g1t nothing and is left out. |
| 41 | INSERT OR IGNORE INTO g1t_spend (day, bucket, account, micros) |
| 42 | SELECT substr(l.created_at, 1, 10), 'comped', COALESCE(m.account_id, 'ws_' || l.workspace), SUM(l.cost_micros) |
| 43 | FROM ledger l |
| 44 | LEFT JOIN account_members m ON m.workspace = l.workspace |
| 45 | JOIN billing_accounts b ON b.id = COALESCE(m.account_id, 'ws_' || l.workspace) |
| 46 | WHERE l.kind = 'usage' AND COALESCE(l.billed_to, 'g1t') = 'g1t' AND l.cost_micros > 0 |
| 47 | AND l.created_at >= strftime('%Y-%m-01', 'now') AND b.terms_kind = 'comped' |
| 48 | GROUP BY 1, 3; |
| 49 | |
| 50 | INSERT OR IGNORE INTO g1t_spend (day, bucket, account, micros) |
| 51 | SELECT r.day, k.bucket, r.account, |
| 52 | SUM(CASE |
| 53 | WHEN k.bucket = 'unpaid' THEN r.cost - CASE WHEN r.gross > 0 THEN (r.cost * r.trial / r.gross) + (r.cost * r.oss / r.gross) + (r.cost * r.given / r.gross) ELSE 0 END |
| 54 | WHEN r.gross <= 0 THEN 0 |
| 55 | WHEN k.bucket = 'trial' THEN r.cost * r.trial / r.gross |
| 56 | WHEN k.bucket = 'oss' THEN r.cost * r.oss / r.gross |
| 57 | ELSE r.cost * r.given / r.gross |
| 58 | END) AS micros |
| 59 | FROM ( |
| 60 | SELECT substr(l.created_at, 1, 10) AS day, COALESCE(m.account_id, 'ws_' || l.workspace) AS account, |
| 61 | l.cost_micros AS cost, |
| 62 | -l.amount_micros + COALESCE(l.credit_micros, 0) + COALESCE(l.trial_micros, 0) + COALESCE(l.oss_micros, 0) + COALESCE(l.given_micros, 0) AS gross, |
| 63 | COALESCE(l.trial_micros, 0) AS trial, COALESCE(l.oss_micros, 0) AS oss, COALESCE(l.given_micros, 0) AS given |
| 64 | FROM ledger l |
| 65 | LEFT JOIN account_members m ON m.workspace = l.workspace |
| 66 | LEFT JOIN billing_accounts b ON b.id = COALESCE(m.account_id, 'ws_' || l.workspace) |
| 67 | WHERE l.kind = 'usage' AND COALESCE(l.billed_to, 'g1t') = 'g1t' AND l.cost_micros > 0 |
| 68 | AND l.created_at >= strftime('%Y-%m-01', 'now') AND COALESCE(b.terms_kind, 'standard') <> 'comped' |
| 69 | ) r |
| 70 | CROSS JOIN (SELECT 'trial' AS bucket UNION ALL SELECT 'oss' UNION ALL SELECT 'given' UNION ALL SELECT 'unpaid') k |
| 71 | GROUP BY r.day, k.bucket, r.account |
| 72 | HAVING SUM(CASE |
| 73 | WHEN k.bucket = 'unpaid' THEN r.cost - CASE WHEN r.gross > 0 THEN (r.cost * r.trial / r.gross) + (r.cost * r.oss / r.gross) + (r.cost * r.given / r.gross) ELSE 0 END |
| 74 | WHEN r.gross <= 0 THEN 0 |
| 75 | WHEN k.bucket = 'trial' THEN r.cost * r.trial / r.gross |
| 76 | WHEN k.bucket = 'oss' THEN r.cost * r.oss / r.gross |
| 77 | ELSE r.cost * r.given / r.gross |
| 78 | END) > 0; |