Sign in

flagon-io/g1t

public

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

272 lines12,697 bytesCodeBlame

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.

Git storage hardened, pages in tens of milliseconds, honest security alerts, and costs reconciled daily1-- 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;