Sign in

flagon-io/g1t

public

g1t/services/billing/migrations/0022_costs_and_margin.sql

272 lines12,697 bytesCodeBlame
1-- What Cloudflare charges g1t, reconciled against what g1t counted and
2-- charged, and prices kept as versions. See src/costs.rs, src/margin.rs,
3-- src/pricing.rs and docs/BILLING_OPERATIONS.md.
4--
5-- Safe to run again: every table and index is IF NOT EXISTS and every
6-- seed INSERT OR IGNORE. The one ALTER is applied once, by D1's
7-- migration tracking, like those before it.
8
9-- Cloudflare's bill, a line per day, source, product and meter. Reading a
10-- day again replaces its lines.
11CREATE TABLE IF NOT EXISTS cost_lines (
12 day TEXT NOT NULL,
13 -- billable_usage (the FOCUS billable-usage API) or artifacts_events
14 -- (GraphQL artifactsEventsAdaptiveGroups).
15 source TEXT NOT NULL,
16 -- Cloudflare's product and the service within it, slugged:
17 -- containers / container_memory_per_gib_second, artifacts / events_push.
18 product TEXT NOT NULL,
19 meter TEXT NOT NULL,
20 unit TEXT NOT NULL,
21 quantity REAL NOT NULL,
22 -- What g1t pays, in dollars.
23 cost_usd REAL NOT NULL,
24 raw_name TEXT NOT NULL,
25 fetched_at TEXT NOT NULL,
26 PRIMARY KEY (day, source, product, meter)
27);
28CREATE INDEX IF NOT EXISTS cost_lines_by_product ON cost_lines (product, meter, day);
29
30-- Which of g1t's products each Cloudflare line is a cost of. Data, so a
31-- new or renamed Cloudflare meter is mapped without a deploy. A line no
32-- row claims is "unmapped": a leak until someone maps it.
33CREATE TABLE IF NOT EXISTS cost_map (
34 product TEXT NOT NULL,
35 -- A meter prefix, or * for every meter of the product.
36 meter TEXT NOT NULL,
37 -- g1t's product: sandboxes, deployments, git, repo_storage,
38 -- actions_cache, embeddings, security, domains, models, platform.
39 bucket TEXT NOT NULL,
40 -- The price book meter whose cost the line measures, if any.
41 price_meter TEXT,
42 -- g1t's own count of the same units (own_counts.meter), to compare.
43 own_meter TEXT,
44 -- When 1, a unit of the price meter costs Cloudflare's rate times how
45 -- many of Cloudflare's units each of g1t's took (git operations).
46 scale_to_own INTEGER NOT NULL DEFAULT 0,
47 drift_percent REAL NOT NULL DEFAULT 10,
48 note TEXT NOT NULL DEFAULT '',
49 updated_at TEXT NOT NULL,
50 updated_by TEXT NOT NULL,
51 PRIMARY KEY (product, meter)
52);
53
54INSERT OR IGNORE INTO cost_map (product, meter, bucket, price_meter, own_meter, scale_to_own, note, updated_at, updated_by) VALUES
55 ('containers', '*', 'sandboxes', 'sandbox_second', NULL, 0, 'Sandboxes, workflow jobs and deploy builds', '2026-10-06T00:00:00Z', 'migration'),
56 ('durable_objects', 'durable_objects_compute_duration', 'sandboxes', 'sandbox_base_second', NULL, 0, 'The Durable Object behind each container', '2026-10-06T00:00:00Z', 'migration'),
57 ('durable_objects', '*', 'platform', NULL, NULL, 0, 'Durable Objects g1t runs itself', '2026-10-06T00:00:00Z', 'migration'),
58 ('workers', 'workers_for_platforms', 'deployments', 'app_requests', NULL, 0, 'Apps people deploy', '2026-10-06T00:00:00Z', 'migration'),
59 ('workers', '*', 'platform', NULL, NULL, 0, 'g1t''s own Workers', '2026-10-06T00:00:00Z', 'migration'),
60 ('workers_for_platforms', '*', 'deployments', 'app_requests', NULL, 0, 'Apps people deploy', '2026-10-06T00:00:00Z', 'migration'),
61 ('artifacts', 'events_', 'git', NULL, 'git_operations', 0, 'What Artifacts counted, by event type (no cost)', '2026-10-06T00:00:00Z', 'migration'),
62 ('artifacts', 'artifacts_storage', 'repo_storage', 'private_storage', NULL, 0, 'Repositories, public and private', '2026-10-06T00:00:00Z', 'migration'),
63 ('artifacts', 'storage', 'repo_storage', 'private_storage', NULL, 0, 'Repositories, public and private', '2026-10-06T00:00:00Z', 'migration'),
64 ('artifacts', '*', 'git', 'git_operations', 'git_operations', 1, 'Operations: what counts is not documented yet', '2026-10-06T00:00:00Z', 'migration'),
65 ('r2', '*', 'actions_cache', 'actions_cache', NULL, 0, 'The actions cache and logs', '2026-10-06T00:00:00Z', 'migration'),
66 ('workers_ai', '*', 'embeddings', 'embedding_tokens', NULL, 0, 'Search embeddings', '2026-10-06T00:00:00Z', 'migration'),
67 ('vectorize', '*', 'embeddings', NULL, NULL, 0, 'The search index', '2026-10-06T00:00:00Z', 'migration'),
68 ('cloudflare_for_saas', '*', 'domains', 'custom_domain_month', NULL, 0, 'Custom hostnames', '2026-10-06T00:00:00Z', 'migration'),
69 ('ssl_for_saas', '*', 'domains', 'custom_domain_month', NULL, 0, 'Custom hostnames', '2026-10-06T00:00:00Z', 'migration'),
70 ('d1', '*', 'platform', NULL, NULL, 0, 'g1t''s databases', '2026-10-06T00:00:00Z', 'migration'),
71 ('workers_kv', '*', 'platform', NULL, NULL, 0, '', '2026-10-06T00:00:00Z', 'migration'),
72 ('queues', '*', 'platform', NULL, NULL, 0, 'The event bus', '2026-10-06T00:00:00Z', 'migration'),
73 ('email', '*', 'platform', NULL, NULL, 0, 'Transactional email', '2026-10-06T00:00:00Z', 'migration'),
74 ('browser_rendering', '*', 'platform', NULL, NULL, 0, 'Link previews and screenshots', '2026-10-06T00:00:00Z', 'migration'),
75 ('workers_paid', '*', 'platform', NULL, NULL, 0, 'The Workers Paid subscription', '2026-10-06T00:00:00Z', 'migration');
76
77-- What pays for each of g1t's products: ledger tasks (and month-end
78-- sources) by the key the statement groups them under. Anything not
79-- listed is an agent's run on a model, bucket "models".
80CREATE TABLE IF NOT EXISTS revenue_map (
81 key TEXT PRIMARY KEY,
82 bucket TEXT NOT NULL,
83 updated_at TEXT NOT NULL,
84 updated_by TEXT NOT NULL
85);
86
87INSERT OR IGNORE INTO revenue_map (key, bucket, updated_at, updated_by) VALUES
88 ('sandbox', 'sandboxes', '2026-10-06T00:00:00Z', 'migration'),
89 ('self_hosted', 'sandboxes', '2026-10-06T00:00:00Z', 'migration'),
90 ('builds', 'sandboxes', '2026-10-06T00:00:00Z', 'migration'),
91 ('deployments', 'deployments', '2026-10-06T00:00:00Z', 'migration'),
92 ('domains', 'domains', '2026-10-06T00:00:00Z', 'migration'),
93 ('git', 'git', '2026-10-06T00:00:00Z', 'migration'),
94 ('storage', 'repo_storage', '2026-10-06T00:00:00Z', 'migration'),
95 ('cache', 'actions_cache', '2026-10-06T00:00:00Z', 'migration'),
96 ('context', 'embeddings', '2026-10-06T00:00:00Z', 'migration'),
97 ('security', 'security', '2026-10-06T00:00:00Z', 'migration'),
98 ('plan', 'platform', '2026-10-06T00:00:00Z', 'migration');
99
100-- How many of a price meter's units each raw meter is, for meters whose
101-- definition is not settled: git_operations from Artifacts' raw counts
102-- (the repos service's artifacts_usage). Changing a weight changes what
103-- is counted from then on, never what was.
104CREATE TABLE IF NOT EXISTS billable_units (
105 price_meter TEXT NOT NULL,
106 raw_meter TEXT NOT NULL,
107 weight REAL NOT NULL,
108 note TEXT NOT NULL DEFAULT '',
109 updated_at TEXT NOT NULL,
110 updated_by TEXT NOT NULL,
111 PRIMARY KEY (price_meter, raw_meter)
112);
113
114-- g1t's own counts, a day per meter per workspace, for the comparison.
115CREATE TABLE IF NOT EXISTS own_counts (
116 day TEXT NOT NULL,
117 meter TEXT NOT NULL,
118 workspace TEXT NOT NULL,
119 quantity REAL NOT NULL,
120 fetched_at TEXT NOT NULL,
121 PRIMARY KEY (day, meter, workspace)
122);
123
124-- What a month-end source (git, storage, scans, …) had come to by the
125-- end of each day, so its revenue can be told by the day.
126CREATE TABLE IF NOT EXISTS pending_days (
127 day TEXT NOT NULL,
128 workspace TEXT NOT NULL,
129 source TEXT NOT NULL,
130 cost_micros INTEGER NOT NULL,
131 charge_micros INTEGER NOT NULL,
132 PRIMARY KEY (day, workspace, source)
133);
134
135-- The reconciliation, a row per day and product. Recomputed for the
136-- days read again, so it follows Cloudflare's restatements.
137CREATE TABLE IF NOT EXISTS margin_days (
138 day TEXT NOT NULL,
139 bucket TEXT NOT NULL,
140 cf_cost_micros INTEGER NOT NULL,
141 own_cost_micros INTEGER NOT NULL,
142 value_micros INTEGER NOT NULL,
143 cash_micros INTEGER NOT NULL,
144 cf_quantity REAL NOT NULL,
145 own_quantity REAL NOT NULL,
146 computed_at TEXT NOT NULL,
147 PRIMARY KEY (day, bucket)
148);
149
150-- What each workspace cost g1t, Cloudflare's costs shared out by g1t's
151-- own meters, against what it paid.
152CREATE TABLE IF NOT EXISTS workspace_costs (
153 day TEXT NOT NULL,
154 workspace TEXT NOT NULL,
155 bucket TEXT NOT NULL,
156 cost_micros INTEGER NOT NULL,
157 revenue_micros INTEGER NOT NULL,
158 PRIMARY KEY (day, workspace, bucket)
159);
160CREATE INDEX IF NOT EXISTS workspace_costs_by_workspace ON workspace_costs (workspace, day);
161
162-- Drift found on the last run: counts, costs and leaks.
163CREATE TABLE IF NOT EXISTS cost_drift (
164 bucket TEXT NOT NULL,
165 kind TEXT NOT NULL,
166 ours REAL NOT NULL,
167 cloudflare REAL NOT NULL,
168 delta_percent REAL,
169 detail TEXT NOT NULL,
170 found_at TEXT NOT NULL,
171 PRIMARY KEY (bucket, kind)
172);
173
174-- Margin alerts: open while the condition lasts. Emailed when opened and
175-- a week later if still open.
176CREATE TABLE IF NOT EXISTS margin_alerts (
177 id TEXT PRIMARY KEY,
178 -- margin (a product's margin under the floor), overall (all of g1t),
179 -- leak, drift, workspace (a workspace costing more than it pays).
180 kind TEXT NOT NULL,
181 subject TEXT NOT NULL,
182 detail TEXT NOT NULL,
183 since TEXT NOT NULL,
184 opened_at TEXT NOT NULL,
185 emailed_at TEXT,
186 resolved_at TEXT
187);
188CREATE INDEX IF NOT EXISTS margin_alerts_open ON margin_alerts (resolved_at, kind, subject);
189
190-- The actions cache is charged (on the plan only) at what R2 charges g1t
191-- to store it, $0.015 a GB-month (services/actions, CACHE_MICROS_PER_GB_MONTH):
192-- in the price book like everything else, so the pricing page reads it.
193INSERT OR IGNORE INTO prices (meter, title, unit, cost_micros, markup_percent, source, updated_at)
194VALUES ('actions_cache', 'Actions cache storage', 'GB-month', 15000, 20, 'list', '2026-10-06T00:00:00Z');
195INSERT OR IGNORE INTO price_changes (id, meter, old_cost_micros, new_cost_micros, markup_percent, reason, created_at)
196VALUES ('prc_actions_cache', 'actions_cache', 15000, 15000, 20,
197 'Now in the price book: what the actions cache stores, at R2''s price, on the plan only', '2026-10-06T00:00:00Z');
198
199-- Every price as versions. `prices` is the version in force; a version
200-- is never changed once written. A rise takes effect after notice.
201CREATE TABLE IF NOT EXISTS price_versions (
202 id TEXT PRIMARY KEY,
203 meter TEXT NOT NULL,
204 version INTEGER NOT NULL,
205 cost_micros REAL NOT NULL,
206 markup_percent INTEGER NOT NULL,
207 effective_at TEXT NOT NULL,
208 reason TEXT NOT NULL,
209 -- keeper, reconciler, staff email, or migration.
210 created_by TEXT NOT NULL,
211 proposal_id TEXT,
212 created_at TEXT NOT NULL,
213 -- When `prices` took it on; NULL while it waits for its date.
214 applied_at TEXT,
215 UNIQUE (meter, version)
216);
217CREATE INDEX IF NOT EXISTS price_versions_due ON price_versions (applied_at, effective_at);
218
219INSERT OR IGNORE INTO price_versions (id, meter, version, cost_micros, markup_percent, effective_at, reason, created_by, created_at, applied_at)
220SELECT 'pv_' || meter || '_1', meter, 1, cost_micros, markup_percent, updated_at,
221 'The price book when prices became versions', 'migration', updated_at, updated_at
222FROM prices;
223
224-- Changes the reconciler measured, waiting for staff or applied.
225CREATE TABLE IF NOT EXISTS price_proposals (
226 id TEXT PRIMARY KEY,
227 meter TEXT NOT NULL,
228 current_cost_micros REAL NOT NULL,
229 proposed_cost_micros REAL NOT NULL,
230 reason TEXT NOT NULL,
231 -- keeper or reconciler.
232 source TEXT NOT NULL,
233 -- 1 when the measurement is far off the current cost.
234 suspect INTEGER NOT NULL DEFAULT 0,
235 -- open, applied (automatically), approved, rejected, superseded.
236 status TEXT NOT NULL,
237 created_at TEXT NOT NULL,
238 decided_at TEXT,
239 decided_by TEXT,
240 note TEXT,
241 version_id TEXT
242);
243CREATE INDEX IF NOT EXISTS price_proposals_by_status ON price_proposals (status, meter);
244
245-- Owners told of a coming rise, once per version per workspace.
246CREATE TABLE IF NOT EXISTS price_notices (
247 version_id TEXT NOT NULL,
248 workspace TEXT NOT NULL,
249 sent_at TEXT NOT NULL,
250 PRIMARY KEY (version_id, workspace)
251);
252
253-- The guardrails, set in sudo.
254CREATE TABLE IF NOT EXISTS cost_settings (
255 key TEXT PRIMARY KEY,
256 value TEXT NOT NULL,
257 updated_at TEXT NOT NULL,
258 updated_by TEXT NOT NULL
259);
260
261INSERT OR IGNORE INTO cost_settings (key, value, updated_at, updated_by) VALUES
262 ('auto_apply', 'true', '2026-10-06T00:00:00Z', 'migration'),
263 ('auto_apply_percent', '25', '2026-10-06T00:00:00Z', 'migration'),
264 ('notice_days', '14', '2026-10-06T00:00:00Z', 'migration'),
265 ('margin_floor_percent', '10', '2026-10-06T00:00:00Z', 'migration'),
266 ('alert_days', '3', '2026-10-06T00:00:00Z', 'migration'),
267 ('min_daily_cost_micros', '100000', '2026-10-06T00:00:00Z', 'migration'),
268 ('anomaly_factor', '1', '2026-10-06T00:00:00Z', 'migration'),
269 ('anomaly_floor_micros', '1000000', '2026-10-06T00:00:00Z', 'migration');
270
271-- The price version each charge was made at, where one applies.
272ALTER TABLE ledger ADD COLUMN price_version TEXT;