Every team shipping LLM features builds a per-call log, a daily aggregate and a dashboard, at different times and from different assumptions. Then someone asks "what did last month cost" and you get two numbers four percent apart, with nobody able to say which is right.
This is an audit of that situation: 2.3 million model calls over three months, one line-item table, one rollup table, and a disagreement of ¥2,055.24. The interesting part is not the gap's size but that it decomposes into a deletion and a freeze, while everything else reconciles to the cent.
Two grains of truth
ai_call_logs holds one row per attempt with tokens, cost_yuan and a status. ai_cost_rollup_daily holds one row per day, model family and hour with calls, miss_tok, hit_tok, out_tok, recorded_cost and refreshed_at.
Two grains, one money column each — and the aggregate carries no id, no reference to the rows it summarised. You can compute both totals, but you cannot ask the rollup which calls it saw. That omission is why drift survives.
Compare by month, not by grand total
Totals hide compensating errors, so let the line-item table define the window.
WITH line AS (SELECT date_trunc('month', created_at)::date m, count(*) rows_written,
sum(cost_yuan) line_cost FROM ai_call_logs GROUP BY 1),
roll AS (SELECT date_trunc('month', d)::date m, sum(calls) roll_calls,
sum(recorded_cost) roll_cost FROM ai_cost_rollup_daily GROUP BY 1)
SELECT COALESCE(l.m, r.m) AS month, rows_written, roll_calls, line_cost, roll_cost,
round(roll_cost - line_cost, 2) AS delta
FROM line l FULL JOIN roll r USING (m) ORDER BY 1;
month | rows_written | roll_calls | line_cost | roll_cost | delta
2026-08-01 | 29143 | 171354 | 1302.81 | 3360.29 | +2057.48
2026-09-01 | 1489004 | 1489004 | 18891.37 | 18891.37 | 0.00
2026-10-01 | 524167 | 524167 | 15985.26 | 15985.26 | 0.00
2026-11-01 | 254281 | 254237 | 7532.69 | 7530.17 | -2.52
September and October match to the cent on both calls and money. November is short by ¥2.52 and 44 calls because the month is still being written. August is off by ¥2,057.48, and that plus the ¥2.52 of in-flight November equals the grand-total gap: what is wrong with August?
The gap is a deletion, not a rounding error
SELECT min(id), max(id), count(*) FROM ai_call_logs;
-- 429016 | 2776137 | 2296595
SELECT min(d), max(d), count(*), sum(calls), sum(recorded_cost)
FROM ai_cost_rollup_daily WHERE d < '2026-08-24';
-- 2026-08-08 | 2026-08-23 | 670 | 140438 | 2015.45
SELECT min(created_at) FROM ai_call_logs;
-- 2026-08-24 05:02:48
Three facts stack up. The line-item table starts at id 429,016, not 1, so roughly 429,000 rows no longer exist. The first surviving call is dated 2026-08-24, while the rollup starts 2026-08-08 — sixteen days earlier. Those orphan days hold 140,438 calls and ¥2,015.45 with no rows behind them.
That is 98.0% of the gap, and neither table is broken. A retention job prunes detail; a separate job keeps aggregates; nothing forces the two policies to agree. The rollup is now the only witness to two weeks of spend, and it cannot be itemised or re-derived. On 2026-08-24 itself the rollup says 3,405 calls and ¥104.88 while 1,632 rows and ¥62.85 survive — the cut ran into that day too.
The useful test is not the average discrepancy but whether it is zero on days nobody touched. Re-running both sides per day, 2026-09-20 gives 17,871 rows and ¥563.52 on both, 2026-10-09 gives 13,703 and ¥229.67, 2026-11-10 gives 8,470 and ¥131.99. Identical to the second decimal, including the 10-09/10-10 boundary where the model mix changed. The rollup job is correct — correct at the moment it runs, which is the second defect.
refreshed_at says the aggregate is a snapshot, not a view
month | min | max | rows -- from: GROUP BY date_trunc('month', d)
2026-08-01 | 2026-10-09 | 2026-10-09 | 976
2026-09-01 | 2026-10-09 | 2026-10-09 | 1386
2026-10-01 | 2026-10-09 | 2026-11-03 | 1284
2026-11-01 | 2026-11-04 | 2026-11-22 | 762
Every August and September bucket was last touched on 2026-10-09 and never again. That is the normal life cycle of a backfill: it runs once and is trusted forever — which makes "the rollup is the source of truth" a claim with an expiry date. A row that arrives late — a retried call, a time-skewed writer, a restored backup — lands in the line-item table and never reaches a bucket frozen five weeks ago.
The fix is not "refresh more often"; full-history refreshes get more expensive every month and eventually stop. Store source_max_id per bucket instead, and reconciliation becomes a question the schema can answer: is there any line item newer than the newest one this bucket counted?
count(*) is not "calls"
status | rows | prompt_tokens | cost
success | 1,044,058 | 18,445,937,152 | 43,711.76
dedup_skip | 1,149,914 | 0 | 0.00
error | 78,313 | 30,939,651 | 0.00
fail | 18,341 | 0 | NULL
rate_limited | 4,201 | 0 | 0.00
empty_after_retry | 1,763 | 0 | 0.00
dedup_skip is a cache decision written into the same log as a call: 50.06% of rows, zero tokens, zero cost. Every dashboard deriving "calls this month" from count(*) overstates volume 2.20× — not because the number is wrong, but because "call" was never defined.
The three failure statuses total 102,618 rows — 4.47% of real attempts — and carry 30.9 million prompt tokens billed to nobody, since the writer only sets cost_yuan on success. That is about ¥65 of invisible spend at the ledger's blended rate, but the structural cost is worse: failures cluster in the same scenes as successes, so a reliability report built on count(*) never sees them.
Two columns that lie about their units
Of 1,044,063 successful calls, 61,609 have NULL cost and another 8,654 are explicitly zero — 98.2 million tokens the finance column never sees. Divide by count(*) and you average in 70,263 free calls, understating by about 6%; divide by count(cost) and you exclude a sixteenth of the traffic.
cache_hit is worse, because it is named like a flag and typed integer while it holds cached tokens. Among successful calls, 18,800 rows have cache_hit > 0 — 1.80% of rows — but those tokens are 2.38% of all prompt tokens (439,479,898 of 18,445,990,674). One column, two true percentages, 25% apart: a caching strategy judged on the first looks weaker than it is.
Average cost per call is a mixture, not a statistic
scene | calls | cost | per_call | tok_per_call
prediction_other | 180,701 | 19,787.84 | 0.1095 | 63,999
prediction_top5_close | 136,399 | 5,994.73 | 0.0439 | 20,352
prediction_top10_mid | 23,878 | 3,095.58 | 0.1296 | 38,521
retrospective | 78,843 | 3,040.82 | 0.0386 | 3,790
Unit cost spans ¥0.0066 to ¥0.1296 across scenes, a 19.6× range driven by context length (3,790 to 63,999 tokens per call). The global mean is ¥0.0419 against a median of ¥0.0165, so "average cost per call" is a statement about traffic mix, not about the system: change the batch schedule and the headline moves while nothing else does. The honest unit is cost per delivered artifact, with the scene breakdown beside it.
46% of the spend has no fixture attached
Of 1,044,059 successful calls in the window, 564,221 carry a match_id and 479,838 do not — 45.96% of traffic holding ¥9,668.76, or 22.1% of the money. Some is legitimate, but the column cannot distinguish "not about a match" from "forgot to propagate the id". Without a taxonomy of fixture-scoped scenes, cost per prediction divides by a denominator that mixes product work with overhead.
The same hazard applies to the model name. GROUP BY model_name splits one model into 689,209 and 32,804 calls, because it is written both deepseek-v4.1-flash and DeepSeek-V4.1-flash; the -1017 variant has the same pair. A budget alert on the larger spelling misses 4.5% of that model's traffic.
A reconciler that fails loudly
The one artifact worth keeping is the assertion, not the report: run it daily, exit non-zero, and the gap never accumulates for three months.
import psycopg2, sys
SQL = """
WITH line AS (
SELECT created_at::date AS d, count(*) FILTER (WHERE status='success') AS calls,
coalesce(sum(cost_yuan),0) AS cost
FROM ai_call_logs WHERE created_at >= %(since)s GROUP BY 1
), roll AS (
SELECT d, sum(calls) AS calls, sum(recorded_cost) AS cost
FROM ai_cost_rollup_daily WHERE d >= %(since)s GROUP BY 1
)
SELECT line.d, line.calls, roll.calls, line.cost, roll.cost
FROM line JOIN roll USING (d)
WHERE abs(line.cost - roll.cost) > 0.01 OR line.calls <> roll.calls;"""
def reconcile(dsn, since):
with psycopg2.connect(dsn) as conn, conn.cursor() as cur:
cur.execute(SQL, {"since": since})
bad = cur.fetchall()
for row in bad: print("DRIFT", *row) # daily grain, one cent, no tolerance
sys.exit(1 if bad else 0)
Three details make it work where a dashboard does not. It compares per day, so a cancellation inside a month cannot hide a gap. It tolerates one cent rather than one percent: a 1% tolerance would have passed a ¥18,891 month wrong by ¥180. And it counts a missing bucket as drift rather than zero — absence looks identical to "no traffic" until you assert on it.
What I would change in the schema
- Never delete line items; archive them. Detach a monthly partition instead. The 429,000 missing ids are unrecoverable evidence.
cost_yuan NOT NULLon success, pluspricing_version. An unpriced call is an incident, not a row state.- Declare the grain in the name.
ai_cost_rollup_hourly_by_familybeatsai_cost_rollup_daily. - Every aggregate row records
source_max_id, so reconciliation is a comparison instead of a hope. - One semantic per column.
statusencodes outcome, cache decision and retry exhaustion at once;callscounts all of them.
The gap was ¥2,055.24 — 4.7% of three months of spend — invisible not because the data was wrong but because nothing compared the two tables. Both were correct. One had simply been edited by a retention policy the other never heard about.
If you take one thing: an aggregate without a back-reference is a rumour with a decimal point. The moment you can ask a rollup which rows it saw, cost reconciliation stops being archaeology and becomes a one-line assertion.
Analytics and research content. Not betting advice. 18+
本内容仅供数据分析与操作参考,不构成任何决策建议。