g1t/services/billing/migrations/0016_plans_and_pools.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.
| Team plan, an open-source pool, monthly trials and honest metering; the sidebar for everyone; a workspace that stays put | 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; |