Ask a team whether their model has seen the future and you will get a boolean. We have one too: a learned_at column that records the moment a prediction was fed back into the training loop. If learned_at is later than the match result, the model learned after the fact. If it is earlier, the model cheated.
That is the whole audit as usually written. It is also wrong, and not by a small margin.
Run it against our production table and 46.6% of the learned rows appear to have learned before the result existed. Anchor the same rows on kickoff time instead, and the number collapses to 0.42%. Same table, same column, same rows — and only one anchor measures leakage rather than measuring your own pipeline's lateness.
Scope: 47,357 predictions, 47,138 graded, 3,807 models, 17,017 matches, 2026-06-21 to 2026-11-19 — ai_predictions and nami_matches, reproducible with the SQL shown.
Step 0: trust the timestamp granularity before the timestamp
An audit of time is an audit of a column, and a column promises less than you think. Check whether a timestamp was observed or bucketed.
SELECT count(*) AS rows, count(*) FILTER (WHERE extract(second FROM created_at) = 0) AS sec_zero, count(*) FILTER (WHERE extract(minute FROM created_at) = 0 AND extract(second FROM created_at) = 0) AS top_of_hour, count(*) FILTER (WHERE extract(second FROM created_at) = 0 AND extract(minute FROM created_at) <> 0) AS sec_zero_minute_not FROM ai_predictions; rows 47357 | sec_zero 1859 | top_of_hour 1859 | sec_zero_minute_not 0 Read the last two columns together, because that pair is the tell. 1,859 rows have a second component of exactly zero, and not one of them has a non-zero minute. Organic writes would land a zero second on a random minute every sixtieth record — roughly 31 rows. We have zero. A real clock does not produce that distribution; date_trunc or a hand-written literal does.
So created_at holds two populations: 45,498 genuine sub-second writes and 1,859 rows whose timestamp is a bucket. Where they live matters more than that they exist — grouping those rows by month:
2026-06 1859 / 2735 2026-07 0 / 2637 2026-08 0 / 164 2026-09 0 / 9757 2026-10 0 / 21886 2026-11 0 / 10178 Every bucketed row is in the first month — 68% of June, then nothing, ever again. That is an import batch from launch week, not a live defect. What is not fine is any time-series claim spanning June and July: it compares a bucket against a clock, June resolving to the hour and July to the millisecond.
learned_at passes the same test: 14,781 rows, zero on the hour. One column partially synthetic, the other fully observed — indistinguishable without it.
Step 1: assert the invariant that must never break
Before hunting leakage, prove the loop is at least ordered. A learning write cannot precede the prediction it learned from.
SELECT count(*) FILTER (WHERE learned_at < created_at) AS learned_before_written, count(*) AS learned_rows FROM ai_predictions WHERE learned_at IS NOT NULL; learned_before_written 0 | learned_rows 14781 — zero violations. That eliminates the most boring bug: a backfill stamping learned_at from a queue's creation time. The offset itself says the loop is a batch job, not a trigger — learned_at - created_at runs p5 449 min, p50 4,095 min (2.8 d), p95 55,834 min. A design cost, not leakage.
Step 2: the anchor choice, where the audit actually fails
Now the comparison everyone writes. Result availability is settled_at; leakage is learned_at < settled_at.
SELECT count(*) FILTER (WHERE learned_at < settled_at) AS learned_before_settled, count(*) FILTER (WHERE learned_at >= settled_at) AS after, count(*) AS n FROM ai_predictions WHERE learned_at IS NOT NULL AND settled_at IS NOT NULL; learned_before_settled 6,889 (46.62%) | after 7,887 (53.38%) | n 14,776 46.6% of the loop appears to read the answer sheet before it exists — an incident, if you trust the anchor.
Anchor on the match instead of on your own bookkeeping:
SELECT count(*) FILTER (WHERE p.learned_at < m.match_time) AS before_kickoff, count(*) FILTER (WHERE p.learned_at >= m.match_time AND p.learned_at < m.match_time + interval '3 hour') AS in_play, count(*) FILTER (WHERE p.learned_at >= m.match_time + interval '3 hour') AS after, count(*) AS n FROM ai_predictions p JOIN nami_matches m ON m.id = p.match_id WHERE p.learned_at IS NOT NULL; before_kickoff 62 (0.42%) | in_play 2,280 (15.43%) | after 12,439 (84.15%) | n 14,781 62 rows. 0.42%. The discrepancy is not noise; it is 6,827 rows, and every one is a statement about settled_at, not about learning.
settled_at is when we wrote the grade; grading is async and retried. It drifts:
settled_at - match_time (n=14,776) <0 208 1.41% <- graded before the whistle 0..6h 7,245 49.03% <- live path 6h..7d 948 6.42% 7d..60d 1,279 8.66% 60d..90d 5,096 34.49% <- repair batch Cleanly bimodal: half the grades land within six hours of the whistle, then a second mass at 60–90 days — 5,096 rows, a third of the learned population, graded two to three months late by a repair job.
Compare accuracy under both anchors:
anchor = settled_at learn_before_settle 6,889 rows → 63.78% learn_after_settle 7,887 rows → 51.76% anchor = match_time before_kickoff 62 rows → 52.46% in_play 2,280 rows → 57.68% after 12,439 rows → 57.33% The settled-anchored split shows a 12-point accuracy advantage for the "suspicious" group — a smoking gun, if you want one. The kickoff-anchored split shows a 5-point spread across groups of wildly different size, which reads like what it is — nothing.
The 12-point gap is an artefact of when grading happened, not of what the model knew: the 84-day repair batch is dominated by older matches the live path had already abandoned. Sorting on settled_at sorts on operational history — a confound wearing the costume of a finding.
Step 3: the 62 rows that are real, and their fingerprint
0.42% is small but not zero; small numbers deserve rows, not percentages.
models 57 | matches 18 | lead min 435 min (7.25 h), p50 32,480 min (22.6 d), max 112,490 min (78.1 d) 61 of the 62 belong to matches already played; one to a fixture still ahead. Twenty-two days early on average, 78 at the extreme, over 57 models but only 18 matches — a batch touching a few fixtures many times, not a systemic lookahead.
The lead times expose the mechanism: several rows share the exact value 47,850 minutes. Identical offsets to the minute across different models mean one loop computed an offset and applied it to a set. Those records were not learned early, they were stamped from a template.
Bug or mislabelled backfill, the conclusion is not what the boolean suggests: these 62 rows do not corrupt the other 14,719. They contaminate 18 matches — and nothing at all if the learning write refused to run for a fixture that has not kicked off.
Step 4: make the check executable
Rules survive only if they run on every deploy. This audit exits non-zero on a broken invariant — CI or scheduler, no dashboard.
import json, sys, psycopg2 CHECKS = [ ("learned_before_created", """SELECT count(*) FROM ai_predictions WHERE learned_at IS NOT NULL AND learned_at < created_at"""), ("learned_before_kickoff", """SELECT count(*) FROM ai_predictions p JOIN nami_matches m ON m.id = p.match_id WHERE p.learned_at IS NOT NULL AND p.learned_at < m.match_time"""), ("graded_before_kickoff", """SELECT count(*) FROM ai_predictions p JOIN nami_matches m ON m.id = p.match_id WHERE p.settled_at IS NOT NULL AND p.settled_at < m.match_time"""), ] def run(dsn): con = psycopg2.connect(dsn); con.set_session(readonly=True) cur, out, failed = con.cursor(), [], False for name, sql in CHECKS: # every invariant must return 0 cur.execute(sql); n = cur.fetchone()[0] failed |= n > 0 out.append({"check": name, "actual": n, "ok": n == 0}) cur.execute("""SELECT count(*) FROM ai_predictions WHERE extract(second FROM created_at) = 0 AND extract(minute FROM created_at) <> 0""") stray = cur.fetchone()[0] # bucket with a stray minute if stray: out.append({"check": "granularity_ambiguous", "actual": stray, "ok": False}) failed = True print(json.dumps(out, indent=2)); con.close() return 1 if failed else 0 if __name__ == "__main__": sys.exit(run("postgresql://soccerdream@127.0.0.1:5433/soccerdream")) On today's snapshot the kickoff-anchored invariant is the only one that fails — 62 instead of 0. graded_before_kickoff fails too, at 208 rows across 40 matches, some stamped as much as 861 hours ahead of the whistle. A separate defect we did not know we had until the anchor was correct: a grade cannot exist before the thing being graded has been played.
Three rules that transfer
Pick the anchor the model cannot write to. settled_at and learned_at are both our own writes. If the grader is late, the learner looks early. Kickoff time comes from the feed and enters the row before any model touches it — the only clock here a bug cannot rewind. Without one, you are comparing two of your own lies.
Validate granularity before validating differences. The 1,859 bucketed rows were invisible for months because a timestamp is a timestamp. One grouped count — seconds zero, minutes non-zero — separates observation from truncation and tells you which months are safe to average.
Refuse writes that are chronologically impossible. 62 and 208 are both small enough to ignore as a percentage and large enough to poison a fixture. A CHECK constraint costs one line and turns silent contamination into a failed insert. Our auditor now fails the build on both, which is the only reason we found the 208.
The headline number is not 46.6%, and it is not 0.42% either. It is that the difference between them is made entirely of our own operational lag — the part of the pipeline most teams never audit, because they audit the model instead of the clock it is judged against.
Analytics and research content. Not betting advice. 18+
本内容仅供数据分析与研究参考,不构成任何决策建议。