Every model evaluation I've ever seen has the same hole in it: the predictions are stored in a table someone can edit.
Not maliciously, usually. Someone reruns a pipeline, a backfill overwrites yesterday's rows, a "fix" recomputes an output with the model that was retrained this morning, and suddenly your 30-day accuracy number describes a model that never existed. The evaluation looks better, the model is not better, and nobody can tell the difference because there's no evidence trail.
I build a football analytics platform where a few hundred independent models publish probability estimates before kickoff. Because the output is public and the public likes to argue about it, I had to make one property non-negotiable: a prediction, once written, is a fact about the past, not a row you can improve.
Here's the schema and the pipeline that enforces it, with the parts that actually bit me.
The three properties that matter
- Append-only. No UPDATE. No DELETE. A wrong prediction stays wrong forever — that's the whole point.
- Timestamped at write, not at display. The time has to come from the database and be bound to the content, or it's decoration.
- Gradeable exactly once. A grading job that can run twice is a grading job that will run twice, and the second run will disagree with the first.
Schema
Postgres, and the constraints do the enforcing rather than application code. Application code is where discipline goes to die.
CREATE TABLE predictions (
id bigserial PRIMARY KEY,
model_id bigint NOT NULL REFERENCES models(id),
fixture_id bigint NOT NULL REFERENCES fixtures(id),
-- the three outcomes, as probabilities, CHECKed not trusted
p_home numeric(5,4) NOT NULL CHECK (p_home BETWEEN 0 AND 1),
p_draw numeric(5,4) NOT NULL CHECK (p_draw BETWEEN 0 AND 1),
p_away numeric(5,4) NOT NULL CHECK (p_away BETWEEN 0 AND 1),
created_at timestamptz NOT NULL DEFAULT now(),
-- hash of (model_id, fixture_id, probabilities, created_at)
content_hash text NOT NULL,
prev_hash text, -- hash chain within a model
inputs_snapshot jsonb NOT NULL, -- what the model actually saw
UNIQUE (model_id, fixture_id)
);
-- the only sane constraint in the whole table
ALTER TABLE predictions
ADD CONSTRAINT probs_sum_to_one
CHECK (abs(p_home + p_draw + p_away - 1) < 0.001);Two decisions worth defending.
inputs_snapshot is a column, not a foreign key. I originally pointed at a model_inputs table and kept it normalised. It broke within a week: the input builder got a bug fix, the referenced row changed, and every historical prediction was suddenly "computed from" data it never saw. Snapshots cost storage and buy reproducibility. Store the blob.
UNIQUE (model_id, fixture_id) without an ON CONFLICT clause. When an insert collides, I want to know. A silent upsert here means the model's first answer gets replaced by a better-informed later answer, which is exactly the failure mode this table exists to prevent. Publish once, then live with it.
Tamper evidence via hash chain
Constraints stop accidental edits. They don't stop UPDATE predictions SET p_home = 0.61. For that, each row carries the hash of its predecessor per model:
import hashlib, json
def content_hash(row: dict, prev_hash: str | None) -> str:
payload = json.dumps({
"model": row["model_id"],
"fixture": row["fixture_id"],
"p": [str(row["p_home"]), str(row["p_draw"]), str(row["p_away"])],
"ts": row["created_at"].isoformat(),
"prev": prev_hash,
}, sort_keys=True, separators=(",", ":"))
return hashlib.sha256(payload.encode()).hexdigest()Verify by replaying the chain. Cost is linear in rows, so I run it per model over the last N and nightly over everything; a break is loud, precise, and localised to a model. Decimal values are stringified before hashing — 0.6100 and 0.61 are the same probability and different JSON, and I lost an afternoon to that.
Grading exactly once
The grading worker takes a fixture result and writes grades. The trap is re-entry: retries, overlapping scheduler ticks, a manual rerun at 2am. Guard with a unique constraint on the result, not with a mutex in application memory:
CREATE TABLE grades (
prediction_id bigint PRIMARY KEY REFERENCES predictions(id),
outcome char(1) NOT NULL CHECK (outcome IN ('H','D','A')),
brier numeric(6,5) NOT NULL,
graded_at timestamptz NOT NULL DEFAULT now(),
result_id bigint NOT NULL REFERENCES results(id)
);prediction_id as the primary key makes the second grade of the same prediction a physical impossibility. The worker uses ON CONFLICT DO NOTHING and reports how many rows it skipped; a non-zero skip count on a fresh fixture means something upstream is duplicating results.
And the grading job refuses to run when predictions.created_at > results.kickoff_at. Cheap check, catches the case where a pipeline backfills a prediction after the match ended. A post-hoc "prediction" graded as correct is worse than no prediction at all — it's a lie with a timestamp on it.
Metrics I actually trust
Brier score decomposes into reliability and resolution, which is why it beats accuracy for probability output:
def brier(ps: list[tuple[float, float, float]], ys: list[int]) -> float:
total = 0.0
for (ph, pd_, pa), y in zip(ps, ys):
p = (ph, pd_, pa)[y]
total += sum((pi - (1.0 if i == y else 0.0)) ** 2 for i, pi in enumerate((ph, pd_, pa)))
return total / len(ps)Note the divisor: over a fixed window of fixtures per model, not aggregated across leagues. A model that only covers one competition and a model that covers forty are answering different questions; averaging them produces a number that describes neither. I compute per model per competition and I refuse to publish a blended figure. When someone asks "what's the platform's accuracy," the honest answer is a distribution, and the moment you collapse it to a single number you've started marketing instead of measuring.
Calibration is the more useful diagnostic anyway. Bucket by predicted probability, plot observed frequency:
SELECT width_bucket(p_max, 0, 1, 10) AS bucket,
count(*) AS n,
avg(p_max) AS predicted,
avg((outcome_idx = argmax_idx)::int) AS observed
FROM graded_predictions
GROUP BY 1 ORDER BY 1;A model sitting on the diagonal is trustworthy in the only sense that matters: when it says 0.7, it happens about 70% of the time. A model off the diagonal in either direction is miscalibrated, and no amount of accuracy fixes that.
Failure modes, honestly
- Timezone drift.
created_atin local time meant a prediction "sealed before kickoff" appeared to be 8 hours after it. Everything istimestamptznow, and the kickoff comparison uses UTC. - Float drift in checks. The
abs(sum - 1) < 0.001tolerance exists because normalising three floats independently lands on 0.9999999 regularly. - Hash chain forks. Two workers inserting for the same model concurrently both read the same
prev_hashand produce a fork. Fixed withSELECT ... FOR UPDATEon the model row inside the insert transaction. Obvious in retrospect; the resulting chain looked valid for weeks before the verifier caught the branch. - Snapshot bloat. 40KB average per prediction. Fine at first, 200GB later. Compressed jsonb, partitioned by month.
What this buys
The value isn't the schema, it's that the schema makes a class of argument impossible. Nobody can ask "was that prediction made before or after the result" — it's in the hash chain. Nobody can quietly drop the bad weeks — deletions break verification. Nobody can swap in a retrained model and call it the same model — different weights is a different model_id.
If you're evaluating anything public-facing, the schema is the cheap part. Deciding that your numbers are not allowed to improve retroactively is the expensive part.
The platform this comes from is SoccerDream AI — independent models publishing timestamped probability estimates on football fixtures, graded in the open, losses included. If you want to poke at the grading output, the per-model pages show the full history rather than a summary.
Analytics and research content. Not betting advice. 18+