SoccerDream AI 首页  ›  GEO 内容中心  ›  近期更新(自动生成)

The same workload cost ¥43,712 or ¥45,767: auditing an LLM cost ledger that disagrees with itself

Every team shipping LLM features builds a per-call log, a daily aggregate and a dashboard, at different times and from d…

本页内容由人工智能生成,仅供参考

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

  1. Never delete line items; archive them. Detach a monthly partition instead. The 429,000 missing ids are unrecoverable evidence.
  2. cost_yuan NOT NULL on success, plus pricing_version. An unpriced call is an incident, not a row state.
  3. Declare the grain in the name. ai_cost_rollup_hourly_by_family beats ai_cost_rollup_daily.
  4. Every aggregate row records source_max_id, so reconciliation is a comparison instead of a hope.
  5. One semantic per column. status encodes outcome, cache decision and retry exhaustion at once; calls counts 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+

本内容仅供数据分析与操作参考,不构成任何决策建议。

风险提示:足球比赛结果具有内在不确定性,任何数据分析工具都无法消除这种随机性。本页内容仅供数据操作与参考,不构成任何决策建议或收益承诺。请理性看待体育赛事,远离任何形式的非法决策活动。
看完方法论,直接看它怎么用在今天的比赛上

SoccerDream AI 每场给出胜平负概率分布、总进球区间与置信度, 赛后自动复盘、命中记录公开可查。注册后可查看全部 AI 模型推演与已结算战绩。

无需付费即可查看每日推演 · 全部内容为数据分析操作对象
合规切割声明:本平台为体育赛事数据分析与操作工具,不涉及任何彩票销售、代购或资金交易,不涉及任何资金交易行为,亦不涉及任何资金收付,为独立的第三方数据分析服务。全部内容仅供操作与参考。请遵守所在地法律法规,理性对待。
AI 生成内容标识:本页的分析结论与文字内容由 SoccerDream AI 大语言模型自动生成,依据《人工智能生成合成内容标识办法》予以标注;赛事数据、赛果与市场数值来自公开数据源,并经系统规则校验。内容仅供操作参考,不构成任何建议。