DEV Community

SoccerDream AI
SoccerDream AI

Posted on Originally published at soccerdreamai.com

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;
Enter fullscreen mode Exit fullscreen mode
 settled | late_1m | late_1d | late_7d | impossible | settled_no_grade | settled_no_ts
---------+---------+---------+---------+------------+------------------+---------------
   46756 |   35414 |   26831 |   21145 |         93 |              120 |           329
Enter fullscreen mode Exit fullscreen mode

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:

Cohort (by created_at) settled written >1d after settlement share
2026-06 2,735 2,735 100.0%
2026-07 2,637 2,636 100.0%
2026-08 164 164 100.0%
2026-09 9,752 8,808 90.3%
2026-10 21,865 11,117 50.8%
2026-11 9,603 1,371 14.3%

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;
Enter fullscreen mode Exit fullscreen mode
 multi_label |    n  | graded_true | fits_primary_only | fits_any_of_pair
-------------+-------+-------------+-------------------+------------------
 f           | 29389 |       17319 |             29364 |            29364
 t           | 17039 |        9282 |             12171 |            16722
Enter fullscreen mode Exit fullscreen mode

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}
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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+

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

Top comments (1)

Collapse
 
acemeridian profile image
Ace Meridian •

I loaded your reject_regrade() function and trigger verbatim into PostgreSQL 16.4, on a cut-down ai_predictions table. It is stricter than the prose in one place, looser in one, and the obvious fix for the strict part opens a second gap.

Stricter: the text says it rejects changes once a final entry exists, but the function checks neither the ledger nor settled. Settling an open prediction (hit NULL to false, settled_at NULL to now()) raised "grade is immutable" as well. If the settle job still grades with UPDATE ai_predictions SET hit = ..., the path your post describes, switching the trigger on at rollout step 4 makes that UPDATE fail for every prediction settled after that.

Looser: DELETE plus an INSERT of the same id with the opposite verdict went through (hit true to false), since only UPDATE is guarded. And once I gated the trigger with WHEN (OLD.settled) so first grades pass, SET settled = false in one statement followed by the re-grade in a second also went through, because settled is not in the guarded column list.

What closed all three in my run:

-- reject_regrade() as published, plus: OR OLD.settled IS DISTINCT FROM NEW.settled
CREATE TRIGGER ai_predictions_grade_frozen
BEFORE UPDATE ON ai_predictions
FOR EACH ROW WHEN (OLD.settled)
EXECUTE FUNCTION reject_regrade();

CREATE FUNCTION reject_delete() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'settled prediction cannot be deleted (id=%)', OLD.id;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER ai_predictions_no_delete
BEFORE DELETE ON ai_predictions
FOR EACH ROW WHEN (OLD.settled)
EXECUTE FUNCTION reject_delete();
Enter fullscreen mode Exit fullscreen mode

First settle OK; re-grade, un-settle and delete+insert all rejected, and the row kept its original verdict. Per the docs (not tested here), row triggers don't fire on TRUNCATE and the table owner can DISABLE TRIGGER, so your hash chain stays the check of record.

Does the settle job ever un-settle a row today, say for a postponed or voided match? With the guard above that becomes a rejected UPDATE too, so it needs its own correction path before step 4.