Grade once, prove it forever: 46,756 settled calls and the case for an append-only grading ledger

Every prediction system eventually makes a promise like this: "our hit rate is 57%." That is a claim about the past, and it is only meaningful if the past cannot be edited. In almost every system I have audited, the grade is a mutable boolean on the same row as the prediction, updated by whatever job ran last. The row is not a record. It is a cache of the most recent writer's opinion.

The case study: a production system with 46,969 prediction rows, 46,756 of them settled. All figures come from the live database.

What a grade actually contains

A grade is not a boolean. It is a tuple: the frozen inputs, the outcome source, the rule version that mapped outcome to verdict, and the instant the verdict was written. Change any one of the four and you have a different claim wearing the same primary key.

The failure is structural, not a bug. UPDATE ai_predictions SET hit = ... has no memory. When the outcome feed corrects a score ninety minutes after full time, the settle job overwrites the verdict and the previous one ceases to exist. Nobody can prove afterwards whether the row was graded once or nine times.

The evidence: a row that keeps changing

The schema has both settled_at and updated_at. The gap between them is a lower bound on row mutation after grading — not proof of re-grading, because any column write touches updated_at, but it is enough to show the row is not frozen.

SELECT count(*) AS settled, count(*) FILTER (WHERE updated_at > settled_at + interval '1 minute') AS late_1m, count(*) FILTER (WHERE updated_at > settled_at + interval '1 day') AS late_1d, count(*) FILTER (WHERE updated_at > settled_at + interval '7 day') AS late_7d, count(*) FILTER (WHERE settled_at > updated_at) AS impossible, count(*) FILTER (WHERE hit IS NULL) AS settled_no_grade, count(*) FILTER (WHERE settled_at IS NULL) AS settled_no_ts FROM ai_predictions WHERE settled; 

 settled | late_1m | late_1d | late_7d | impossible | settled_no_grade | settled_no_ts ---------+---------+---------+---------+------------+------------------+--------------- 46756 | 35414 | 26831 | 21145 | 93 | 120 | 329 

75.7% of settled rows were written more than a minute after their own settlement timestamp. 57.4% more than a day later. 45.2% more than a week later. And 93 rows carry a settlement timestamp that is later than their last write — an ordering that cannot happen if settlement is a single atomic act.

The trend shows a process problem being fixed by accident:

Older cohorts were re-touched by every downstream backfill: calibration columns, visibility flags, learning timestamps. Each write was legitimate; collectively they destroyed the only audit trail we had.

A 25-row ledger for 35,414 mutations

One table records a re-grade: r132_settle_reverify_log, 25 rows, spanning 2026-09-22 to 2026-11-09. Of those, 8 changed the recorded outcome and 4 flipped the verdict.

Read that ratio again: when a re-grade was logged, 32% of the time the answer changed. That is the real value of an audit trail — it tells you the base rate of your own corrections. And 25 logged events cannot explain 35,414 mutated rows.

There is a quieter signal. The table has two boolean grade columns, hit and hit_1x2, and they disagree on 391 of 46,636 rows. Two fields meant to say the same thing, disagreeing on 0.84% of the corpus, with no documented rule about which wins. When a consumer asks "what is the hit rate", the honest answer is "of which column".

The 342 rows a guard was designed not to see

The system does recompute grades to check itself. A guard compares stored hit against a recomputation from actual_result and prediction_direction: 1,428 runs recorded, 343 flagged abnormal, most recent run 2026-11-15 12:21.

Latest reading: hit_vs_recalc = 0.74%, mismatch = 342 of n = 46,427, against a 2.0% threshold. The guard is green, and stays green while up to ~928 rows are wrong. A percentage threshold turns an integrity question into a tolerance question, and tolerance always wins — nobody wants a red dashboard.

Here is the query that produces the 342, split by whether the call had one label or two:

SELECT prediction_direction LIKE '%/%' AS multi_label, count(*) AS n, count(*) FILTER (WHERE hit) AS graded_true, count(*) FILTER (WHERE hit = (actual_result = split_part(prediction_direction,'/',1))) AS fits_primary_only, count(*) FILTER (WHERE hit = (actual_result = ANY(string_to_array(prediction_direction,'/')))) AS fits_any_of_pair FROM ai_predictions WHERE settled AND hit IS NOT NULL AND actual_result IS NOT NULL GROUP BY 1; 

 multi_label | n | graded_true | fits_primary_only | fits_any_of_pair -------------+-------+-------------+-------------------+------------------ f | 29389 | 17319 | 29364 | 29364 t | 17039 | 9282 | 12171 | 16722 

Single-label rows are clean: 29,364 of 29,389 agree with strict equality, a 0.09% residual. Two-label rows are not. The production rule is clearly "hit if the outcome is either label" — that explains 16,722 of 17,039. But 317 rows fit neither reading. They were graded under a rule that no longer exists, or by a code path since rewritten, and the row cannot tell you which.

That is the concrete cost: not that 317 rows move the headline number (0.68% of 46,756), but that you cannot distinguish "wrong" from "graded under an older rule" and therefore cannot fix either. You can only overwrite, which is how you got here.

The hash column that cannot identify a row

odds_snapshot_hash is the right instinct and the wrong implementation. 2,039 rows have it NULL. Among non-null values, 2,511 groups share a value, and the largest single value is shared by 357 rows, because the hash covers the market-price snapshot rather than the row. Coverage also arrived late: 31.9% of June rows, 96.0% of July, 99.9%+ from September. The field cannot answer "which inputs produced this verdict", only "which snapshot was current" — a weaker claim.

The fix: two pieces, both small

Piece one is an append-only grade ledger with a hash chain. Each entry commits to the previous entry's digest, so the history is verifiable by walking it. The canonical serialization matters more than the hash function — if two processes serialize the same grade differently, the chain reports tampering that did not happen.

import hashlib, json FIELDS = ("prediction_id", "model_id", "direction", "prob_hash", "home_score", "away_score", "outcome", "verdict", "rule_version", "graded_at") def canonical(payload: dict) -> bytes: body = {k: payload[k] for k in FIELDS} # sort_keys + no whitespace + explicit separators = one byte string per grade return json.dumps(body, sort_keys=True, separators=(",", ":")).encode() def link(payload: dict, prev_digest: str) -> tuple[str, str]: digest = hashlib.sha256(prev_digest.encode() + b"|" + canonical(payload)).hexdigest() return digest, prev_digest def verify(entries: list[dict]) -> dict: prev = "0" * 64 bad = [] for e in entries: if e["prev_digest"] != prev: bad.append((e["prediction_id"], "broken_link")) expect, _ = link(e, prev) if expect != e["digest"]: bad.append((e["prediction_id"], "payload_changed")) prev = e["digest"] return {"entries": len(entries), "violations": bad, "head": prev} 

Piece two is a database-level refusal. A trigger that rejects any change to the grading columns once a final entry exists turns "we promise to grade once" into "the database will not let us do otherwise". Corrections then go through a new ledger entry with a reason code, not an UPDATE.

CREATE OR REPLACE FUNCTION reject_regrade() RETURNS trigger AS $$ BEGIN IF OLD.hit IS DISTINCT FROM NEW.hit OR OLD.actual_result IS DISTINCT FROM NEW.actual_result OR OLD.match_home_score IS DISTINCT FROM NEW.match_home_score OR OLD.match_away_score IS DISTINCT FROM NEW.match_away_score OR OLD.settled_at IS DISTINCT FROM NEW.settled_at THEN RAISE EXCEPTION 'grade is immutable; append a correction entry instead (id=%)', OLD.id; END IF; RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER ai_predictions_grade_frozen BEFORE UPDATE ON ai_predictions FOR EACH ROW EXECUTE FUNCTION reject_regrade(); 

Rollout order matters

Do not turn the trigger on first. Freeze what you have, label it, then close it.

1. Backfill one ledger entry per settled row, marked origin = 'backfill'. This deliberately blesses today's values, including the 342 mismatches.
2. Quarantine the 342 into a disputed column rather than overwriting. A human adjudicates them exactly once.
3. Resolve hit vs hit_1x2: one canonical, the other derived in a view. Two writable booleans for one fact is a bug on a schedule.
4. Then enable the trigger, routing every correction through an append-only entry with rule_version and reason.
5. Run verify() hourly and alarm on any violation, not on a percentage. A chain either verifies or it does not.

The payoff is not tidiness. With a chain you can answer what is currently unanswerable: the published hit rate on 1 October, recomputed from entries that existed on 1 October; how many verdicts changed after the fact; whether a model improved from better calls or from a re-grade. Without it, every historical number is a claim about the current contents of a mutable table — and that claim quietly improves whenever someone cleans the data.

One more reason: this corpus feeds a public leaderboard. One match carries 358 graded calls from 358 model variants, three more carry 120 or more. A single rule change applied to that cluster moves a ranking without any model having changed. Sorting by a number whose rows can be rewritten is not measurement; it is a snapshot of whoever ran last.
Grade once. Keep the receipt. Make the receipt impossible to edit.

Analytics and research content. Not betting advice. 18+

本内容仅供数据分析与研究参考，不构成任何决策建议。