g1t/services/billing/migrations/0024_spend_caps.sql
Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.
| Spend caps: a monthly budget for comped workspaces and a daily breaker on what g1t pays | 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; |