g1t/services/billing/migrations/0016_plans_and_pools.sql
| 1 | -- The Team plan, g1t's capped pools (trials and open source), the minimum |
| 2 | -- charge, and meters for what was free by accident: private storage, |
| 3 | -- search embeddings and security scans. See src/credits.rs. |
| 4 | |
| 5 | -- What paid for a usage entry before it was charged: the Team plan's |
| 6 | -- monthly credit, the workspace's trial credit, or g1t's open-source |
| 7 | -- pool. `amount_micros` stays what the workspace is charged. |
| 8 | ALTER TABLE ledger ADD COLUMN credit_micros INTEGER NOT NULL DEFAULT 0; |
| 9 | ALTER TABLE ledger ADD COLUMN trial_micros INTEGER NOT NULL DEFAULT 0; |
| 10 | ALTER TABLE ledger ADD COLUMN oss_micros INTEGER NOT NULL DEFAULT 0; |
| 11 | |
| 12 | -- Monthly allowances, drawn down as usage comes in, and new each calendar |
| 13 | -- month (UTC): |
| 14 | -- team_credit scope = workspace Team credit used, in micros |
| 15 | -- build_seconds scope = workspace Deployments build seconds included |
| 16 | -- oss_pool scope = '' g1t's open-source pool, in micros |
| 17 | -- oss_repo scope = owner/name one public repository's share of it |
| 18 | CREATE TABLE allowance_use ( |
| 19 | kind TEXT NOT NULL, |
| 20 | scope TEXT NOT NULL, |
| 21 | -- YYYY-MM. |
| 22 | month TEXT NOT NULL, |
| 23 | used INTEGER NOT NULL DEFAULT 0, |
| 24 | PRIMARY KEY (kind, scope, month) |
| 25 | ); |
| 26 | |
| 27 | -- Each workspace's one trial grant, made when it first uses something, out |
| 28 | -- of the month's pool (`TRIAL_MONTHLY_POOL_MICROS`). |
| 29 | CREATE TABLE trial_grants ( |
| 30 | workspace TEXT PRIMARY KEY, |
| 31 | -- YYYY-MM of the pool it came from; 'legacy' for grants from before |
| 32 | -- pools reset monthly, 'staff' for ones set in sudo. Only YYYY-MM |
| 33 | -- grants count against a month's pool. |
| 34 | month TEXT NOT NULL, |
| 35 | granted_micros INTEGER NOT NULL, |
| 36 | used_micros INTEGER NOT NULL DEFAULT 0, |
| 37 | created_at TEXT NOT NULL |
| 38 | ); |
| 39 | CREATE INDEX trial_grants_by_month ON trial_grants (month); |
| 40 | |
| 41 | -- The free allowance that ended on a date becomes these grants, intact: |
| 42 | -- each workspace that used it keeps what it had left of its $1. |
| 43 | INSERT INTO trial_grants (workspace, month, granted_micros, used_micros, created_at) |
| 44 | SELECT workspace, 'legacy', 1000000, MIN(1000000, SUM(COALESCE(cost_micros, 0))), MIN(created_at) |
| 45 | FROM ledger |
| 46 | WHERE kind = 'usage' AND COALESCE(billed_to, 'g1t') = 'g1t' AND COALESCE(task, '') NOT IN ('sandbox', 'deployments') |
| 47 | GROUP BY workspace |
| 48 | HAVING SUM(COALESCE(cost_micros, 0)) > 0; |
| 49 | |
| 50 | -- Set per account in sudo: the Team plan without charge, and its share of |
| 51 | -- the pools (null: the default). |
| 52 | ALTER TABLE billing_accounts ADD COLUMN team_granted INTEGER NOT NULL DEFAULT 0; |
| 53 | ALTER TABLE billing_accounts ADD COLUMN oss_repo_micros INTEGER; |
| 54 | ALTER TABLE billing_accounts ADD COLUMN trial_micros INTEGER; |
| 55 | |
| 56 | -- Usage other services meter through the month (security scans, search |
| 57 | -- embeddings) and storage measured daily: what it cost g1t, and when |
| 58 | -- billing charged it once the month was over. |
| 59 | ALTER TABLE pending_usage ADD COLUMN cost_micros INTEGER NOT NULL DEFAULT 0; |
| 60 | ALTER TABLE pending_usage ADD COLUMN charged_at TEXT; |
| 61 | -- Months before this one were never charged, and are not now: charging |
| 62 | -- starts with this month. |
| 63 | UPDATE pending_usage SET charged_at = 'never: before metering' |
| 64 | WHERE source <> 'deployments' AND month < '2026-10'; |
| 65 | |
| 66 | -- What each workspace's private repositories held each day, and what was |
| 67 | -- free that day (more on Team). |
| 68 | CREATE TABLE storage_days ( |
| 69 | workspace TEXT NOT NULL, |
| 70 | -- YYYY-MM-DD. |
| 71 | day TEXT NOT NULL, |
| 72 | private_bytes INTEGER NOT NULL, |
| 73 | free_bytes INTEGER NOT NULL, |
| 74 | PRIMARY KEY (workspace, day) |
| 75 | ); |
| 76 | |
| 77 | -- month_closes.status gains 'carried': owed less than the minimum charge, |
| 78 | -- so it waits for the next invoice. |
| 79 | |
| 80 | -- The new meters, at Cloudflare's published prices. |
| 81 | INSERT INTO prices (meter, title, unit, cost_micros, markup_percent, source, updated_at) VALUES |
| 82 | ('private_storage', 'Private repository storage past the free amount', 'GB-month', 500000, 20, 'list', '2026-10-05T00:00:00Z'), |
| 83 | ('embedding_tokens', 'Search embeddings', 'million tokens', 67000, 20, 'list', '2026-10-05T00:00:00Z'), |
| 84 | ('scan_cpu', 'Security scans: CPU time', 'million CPU ms', 20000, 20, 'list', '2026-10-05T00:00:00Z'), |
| 85 | ('scan_rows', 'Security scans: rows written', 'million rows', 1000000, 20, 'list', '2026-10-05T00:00:00Z') |
| 86 | ON CONFLICT (meter) DO NOTHING; |
| 87 | |
| 88 | INSERT INTO price_changes (id, meter, old_cost_micros, new_cost_micros, markup_percent, reason, created_at) VALUES |
| 89 | ('prc_new_private_storage', 'private_storage', 500000, 500000, 20, 'Now metered: private repository storage past the free amount, at Cloudflare Artifacts'' storage price', '2026-10-05T00:00:00Z'), |
| 90 | ('prc_new_embedding_tokens', 'embedding_tokens', 67000, 67000, 20, 'Now metered: search embeddings, at Workers AI''s price for the embedding model', '2026-10-05T00:00:00Z'), |
| 91 | ('prc_new_scan_cpu', 'scan_cpu', 20000, 20000, 20, 'Now metered at Cloudflare''s prices: security scans, which were placeholder costs', '2026-10-05T00:00:00Z'), |
| 92 | ('prc_new_scan_rows', 'scan_rows', 1000000, 1000000, 20, 'Now metered at Cloudflare''s prices: security scans, which were placeholder costs', '2026-10-05T00:00:00Z') |
| 93 | ON CONFLICT (id) DO NOTHING; |