DEV Community

SoccerDream AI
SoccerDream AI

Posted on Originally published at soccerdreamai.com

7,472 write bursts and 48,216 rows: what a prediction log can and cannot prove

Every prediction platform eventually has to answer one question from a customer, a regulator, or its own postmortem: was this row produced by an analysis of this match, or was it replayed?

The model card describes intent, not execution. The only artifact describing execution is the row itself — its write time, its measured duration, its payload. So I treated our own production log as a forensic object.

The table is ai_predictions: 48,216 rows, 47,167 graded, 3,853 models, 17,235 matches, 116 distinct days from 2026-06-21 to 2026-11-20. Orientation: 26,806 hits over 47,167 graded rows, 56.83%.

Cluster the writes into bursts

A prediction is not an event, it is a member of a batch. To see batches you do not need a job table — you need the gap between consecutive writes. I used 120 seconds: any longer silence starts a new burst.

with t as (
  select id, created_at, model_id, match_id,
         created_at - lag(created_at) over (order by created_at, id) as gap
  from ai_predictions
), b as (
  select *, sum(case when gap is null or gap > interval '120 seconds' then 1 else 0 end)
           over (order by created_at, id) as burst_id
  from t
)
select count(distinct burst_id) as bursts, count(*) as rows,
       max(n) as largest_burst,
       percentile_disc(0.5) within group (order by n) as p50_size
from (select burst_id, count(*) n from b group by 1) s;
Enter fullscreen mode Exit fullscreen mode
bursts | rows  | largest_burst | p50_size
7472   | 48215 | 645           | 2
Enter fullscreen mode Exit fullscreen mode

7,472 bursts for 48,215 rows, mean 6.45, median 2. The distribution is bimodal: 2,951 bursts hold exactly one row, while 41 bursts hold more than 100 and together account for 7,540 rows — 15.6% of the log written in 41 windows.

The largest ran on 2026-10-10, 00:44:10 to 01:24:58: 645 rows, 214 matches, 160 distinct models, 2,448.4 seconds of wall clock. That is already a finding. 160 models produced 645 predictions across 214 matches in 41 minutes — a fan-out, not a per-match loop.

Does a model write the same match twice in one burst?

Yes: of 32,110 (burst, model) pairs, 8,070 contain more than one row; 1,008 contain five or more; the maximum is 50 rows from one model inside one contiguous burst. A model emitting 50 rows in one window is iterating a slate.

The arithmetic of concurrency

analysis_duration_ms over 29,991 non-null rows: p5 = 4,465 ms, p50 = 58,730 ms, p95 = 182,816 ms, max 1,569,409 ms. Median ~58.7 seconds per prediction.

Now compare with the burst above: 645 rows in 2,448 seconds, i.e. 3.8 seconds of wall clock per row. You cannot spend 58.7 seconds of compute per row and emit one row every 3.8 seconds unless rows are produced concurrently. The ratio per burst is the implied parallelism:

with t as (
  select id, created_at, analysis_duration_ms,
         created_at - lag(created_at) over (order by created_at, id) as gap
  from ai_predictions where analysis_duration_ms is not null
), b as (
  select *, sum(case when gap is null or gap > interval '120 seconds' then 1 else 0 end)
           over (order by created_at, id) as burst_id
  from t
), s as (
  select burst_id, sum(analysis_duration_ms)/1000.0 as compute_s,
         extract(epoch from max(created_at)-min(created_at)) as span_s
  from b group by 1 having count(*) >= 5
)
select count(*) as bursts,
       round(percentile_disc(0.5) within group (order by compute_s/nullif(span_s,0))::numeric, 2) as p50,
       round(percentile_disc(0.9) within group (order by compute_s/nullif(span_s,0))::numeric, 2) as p90
from s where span_s > 0;
Enter fullscreen mode Exit fullscreen mode
bursts | p50  | p90
1477   | 3.77 | 10.54
Enter fullscreen mode Exit fullscreen mode

Across 1,477 bursts of at least five instrumented rows, the median burst packs 3.77 seconds of measured compute into every second of wall clock; p90 is 10.54. For the 645-row burst, only 277 rows carry a duration and those 277 alone sum to 16,520.6 seconds inside a 2,448-second window — a lower bound of 6.7x. Extrapolating the measured mean of 59.6 s/row to all 645 gives ~38,400 seconds, about 15.7x.

First honest conclusion: the log proves concurrency. It cannot prove which worker produced which row, because there is no run_id and no worker_id. Two rows 400 ms apart from two processes are indistinguishable from two threads of one process, and both are indistinguishable from a replay that copies a stored analysis. Concurrency is measurable; provenance is not.

The missing 37.8%

18,225 rows (37.8%) have analysis_duration_ms IS NULL, and not at random:

month rows null duration share
2026-06 2,735 2,735 100.0%
2026-07 2,637 2,637 100.0%
2026-08 164 164 100.0%
2026-09 9,757 5,563 57.0%
2026-10 21,886 4,317 19.7%
2026-11 11,037 2,809 25.5%

Instrumentation arrived in September, stabilised in October, then drifted back to 25.5% in November. Any cost-per-prediction, latency SLO, or "how long does the model think" figure computed on non-null rows is a sample stratified by month, and months are not exchangeable: model composition, match volume and prompt length all moved. The correct denominator is 48,216 and the correct summary is "63.2% of rows have a measured duration".

Latency carries no quality signal

duration bucket rows graded hit rate
< 10 s 5,440 5,371 58.09%
10–60 s 9,666 9,391 57.56%
1–3 min 13,313 13,081 56.87%
> 3 min 1,572 1,562 57.17%
null 18,225 17,638 56.41%

Full spread 1.68 points across buckets differing by an order of magnitude in size, and the ordering is not monotonic. The 362 rows finishing under one second sit in the same band as rows taking four minutes.

I report this as a negative result because it constrains the fix. If longer analysis produced better predictions, the response to a 15.7x concurrency finding would be "add capacity". Since it does not, the response is "record what happened, then decide".

Identical payloads across different matches

Hashing full_analysis is the cheapest provenance check available without a schema change:

select count(*) as dup_hashes, sum(n) as rows_in_dup_groups,
       sum(case when dm > 1 then n else 0 end) as rows_spanning_multiple_matches,
       max(n) as largest_group, max(dm) as matches_in_largest_group
from (
  select md5(full_analysis) h, count(*) n, count(distinct match_id) dm
  from ai_predictions
  where full_analysis is not null and length(full_analysis) > 100
  group by 1 having count(*) > 1
) g;
Enter fullscreen mode Exit fullscreen mode
dup_hashes | rows_in_dup_groups | rows_spanning_multiple_matches | largest_group | matches_in_largest_group
736        | 3780               | 3361                           | 123           | 77
Enter fullscreen mode Exit fullscreen mode

736 payloads repeat, covering 3,780 rows, and 3,361 of those rows sit in groups spanning more than one match. The largest single text appears 123 times across 77 matches. It is not prose — it is a 240-character JSON stub beginning {"prediction": {"direction": "home", "confidence": 6, "score_prediction": "2-1"}, a fixed default stamped onto whatever match was in the loop when real analysis failed.

That is the finding a schema cannot catch alone, and why payload hashing belongs in the audit. 7,539 rows carry 100–500 characters; 40,510 carry more than 500. Length will not separate a stub from an analysis, but a hash will.

The column that exists and records nothing

cache_expires_at is declared on the table. Populated: 0 of 48,216. A column that is always null is worse than an absent column, because it invites analysis that silently returns zero: anyone auditing cache reuse against it concludes "no caching occurred" — true, or merely unrecorded. Same rule for prediction_source: free-text (match_center 30,062, system 11,458, scheduled 5,343, match_center+ahfill 986, then 16 one-off values). Free-text provenance is not provenance.

What the schema should have recorded

Five columns would have made every question above answerable without forensics:

alter table ai_predictions
  add column run_id             uuid    not null,  -- one value per batch invocation
  add column worker_id          text    not null,  -- process that wrote the row
  add column input_snapshot_id  text    not null,  -- hash of the frozen input bundle
  add column contract_version   text    not null,  -- prompt + training-word version
  add column duration_ms        integer not null;

create unique index ai_pred_run_model_match on ai_predictions (run_id, model_id, match_id);
create index ai_pred_input_snapshot on ai_predictions (input_snapshot_id);
Enter fullscreen mode Exit fullscreen mode

"Did this row come from this match's data" then becomes a join instead of an argument: group by run_id, compare sum(duration_ms) to the run's wall clock, assert input_snapshot_id is unique per (run, match).

A verifier you can run before adding anything

import psycopg2

DSN = "postgresql://user:pass@127.0.0.1:5433/soccerdream"

def main():
    cur = psycopg2.connect(DSN).cursor()
    cur.execute("""select count(*), count(*) filter (where analysis_duration_ms is null),
                          count(distinct md5(full_analysis)) from ai_predictions""")
    rows, nulls, distinct_payloads = cur.fetchone()
    print(f"rows={rows} null_duration={nulls} ({100.0*nulls/rows:.1f}%) "
          f"distinct_payloads={distinct_payloads}")

    cur.execute("""select count(*) from (
          select md5(full_analysis) h from ai_predictions
          where full_analysis is not null and length(full_analysis) > 100
          group by 1 having count(distinct match_id) > 1) g""")
    cross = cur.fetchone()[0]
    print(f"payloads_reused_across_matches={cross}")

    assert nulls < 0.05 * rows, "duration coverage regressed"
    assert cross == 0, "identical analysis text spans multiple matches"
    print("invariants ok")

if __name__ == "__main__":
    main()
Enter fullscreen mode Exit fullscreen mode

Run against today's table it fails both assertions — 37.8% nulls, 736 cross-match payloads. The failure is the baseline; the useful question is whether tomorrow's number is smaller.

The general lesson

A prediction log does not document what the model thought. It documents what the process did, and only to the depth the schema chose to record. Ours records time and duration — enough to establish fan-out batches, heavy concurrency, 50 rows from one model in one window, and 3,361 payloads shared across matches. It does not record run, worker, or input identity, which makes the difference between "analysed" and "replayed" unprovable for 100% of rows.

That is a schema gap, not a modelling failure. The cheapest audit this week is the burst reconstruction above: it needs nothing but created_at, and it tells you within an hour whether your pipeline is per-match or per-batch — and whether anyone could prove it either way.

Analytics and research content. Not betting advice. 18+

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

Top comments (0)