Reproducibility is not a seed: what a mutable predictions table really costs you

Most teams treat "reproducibility" as a machine learning problem. Fix the seed, pin the library versions, snapshot the model weights, and you are done.

That definition breaks the moment the prediction is a business artifact — a number someone acts on, a number you publish, a number you will later claim was right. Under that definition, reproducibility has nothing to do with seeds. It has everything to do with whether the row you are looking at today is the row that existed when the call was made.

I run a football analytics platform with a few thousand model variants writing predictions into one Postgres table. We measured our own table last week. The result was uncomfortable enough that I want to publish it.

The measurement

The table is called ai_predictions. At the time of writing it holds 46,192 rows, produced by 3,712 distinct model variants across 16,731 fixtures, spanning 2026-06-21 to 2026-11-09. 46,041 of those rows are settled — meaning the fixture finished and the outcome is known.

We have a primary key on id. That is the only uniqueness constraint on the table. There is no unique constraint on (model_id, fixture_id).

Let me measure three things: whether a row is rewritten after creation, whether it is rewritten after settlement, and whether we are able to tell what changed.

-- 1. rows modified more than 60 seconds after creation
SELECT count(*) FROM ai_predictions
WHERE updated_at > created_at + interval '60 seconds';

-- 2. rows modified AFTER the outcome was already graded
SELECT count(*) FROM ai_predictions
WHERE settled = true
  AND settled_at IS NOT NULL
  AND updated_at > settled_at;

-- 3. is there any table holding a before-image?
SELECT count(*) FROM information_schema.tables
WHERE table_name LIKE '%prediction%histor%'
   OR table_name LIKE '%prediction%audit%';

The answers:

46181   -- rows rewritten >60s after creation (99.98% of the table)
35013   -- rows rewritten AFTER settlement (75.8% of settled rows)
0       -- audit tables holding a before-image

Read the second number again. Three quarters of our settled predictions have been modified since they were graded.

The two interpretations, and why you cannot choose between them

There is a benign reading and a damning reading of 35013.

The benign reading: prediction rows carry a lot of envelope fields besides the call itself — confidence calibration, market-snapshot injection timestamps, secondary-market settlement, dictionary status. A row can be touched dozens of times after grading while direction and confidence never move. In our case this is almost certainly what happened: those patch columns are written by separate jobs that run on their own schedules.

The damning reading: someone changed the call after seeing the result.

Here is the problem. With no before-image, we cannot distinguish these two cases. Not "we believe it's the benign one" — we literally cannot produce the evidence. The updated_at column tells you a write happened. It does not tell you which columns changed, from what, or by whom.

That is what a missing audit trail actually costs. It is not that you have no audit trail. It is that you have no way to prove you did nothing wrong, on any row, ever. Every number you publish rests on an assertion nobody can verify.

And note the first number: 99.98%. Almost every row is mutable. That is not a discipline problem, it is a schema problem — the table was designed to permit it.

Mutation is a design decision, not an accident

Postgres will happily let you UPDATE any column you did not explicitly protect. If your schema is:

CREATE TABLE ai_predictions (
    id          bigserial PRIMARY KEY,
    model_id    bigint NOT NULL,
    fixture_id  bigint NOT NULL,
    direction   text,
    confidence  numeric(5,4),
    created_at  timestamptz DEFAULT now(),
    updated_at  timestamptz DEFAULT now()
);

then "predictions are immutable" is a convention, and conventions do not survive a deadline. The table's permissions are the real specification. Everything else is documentation.

There is a cheap structural fix. Make the graded fields generated or trigger-guarded, so the database itself rejects the write:

-- a write that changes a settled call is rejected by the database,
-- not by a code review
CREATE OR REPLACE FUNCTION reject_settled_mutation() RETURNS trigger AS $$
BEGIN
    IF OLD.settled = true THEN
        IF NEW.p_home  <> OLD.p_home
        OR NEW.p_draw  <> OLD.p_draw
        OR NEW.p_away  <> OLD.p_away
        OR NEW.direction <> OLD.direction THEN
            RAISE EXCEPTION
              'settled prediction % is immutable (attempted change to graded fields)', OLD.id;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ai_predictions_immutable
BEFORE UPDATE ON ai_predictions
FOR EACH ROW EXECUTE FUNCTION reject_settled_mutation();

Envelope columns still move. The part that constitutes a claim cannot. That single trigger converts "we promise" into "the database refuses" — which is the only version of a promise that a third party can check.

If you must mutate, mutate with a diff

The trigger above is a hard stop. Sometimes you genuinely need a legal path for corrections — a data correction after a bad fixture mapping, say. Then the answer is not to allow silent mutation; it is to require a companion write that records the transition.

CREATE TABLE prediction_mutations (
    id            bigserial PRIMARY KEY,
    prediction_id bigint NOT NULL REFERENCES ai_predictions(id),
    changed_at    timestamptz NOT NULL DEFAULT now(),
    changed_by    text NOT NULL,          -- job name, never "human"
    reason        text NOT NULL,          -- enum-checked in practice
    before_row    jsonb NOT NULL,
    after_row     jsonb NOT NULL,
    CHECK (before_row <> after_row)
);

CREATE OR REPLACE FUNCTION audit_prediction_update() RETURNS trigger AS $$
BEGIN
    INSERT INTO prediction_mutations (prediction_id, changed_by, reason, before_row, after_row)
    VALUES (NEW.id,
            current_setting('app.job_name', true),
            COALESCE(current_setting('app.change_reason', true), 'unspecified'),
            to_jsonb(OLD),
            to_jsonb(NEW));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ai_predictions_audit
AFTER UPDATE ON ai_predictions
FOR EACH ROW EXECUTE FUNCTION audit_prediction_update();

Now the benign reading is provable. When someone asks why row 8,412,003 changed after settlement, you open prediction_mutations, look at the two jsonb blobs, and show that exactly one key moved and it was ah_settled_at. Or you show that direction moved, and you have a much harder conversation — but at least it is a conversation grounded in evidence.

reason being NOT NULL with a changed_by that names a job rather than a person is deliberate. "Someone fixed it" is not an audit entry. "calibration_worker updated confidence because CALIBRATION_V2 backfill ran" is.

Reproducibility, restated

Once the record is immutable and diffs are recorded, an actual reproducibility check becomes possible. Not "run it twice and eyeball the output" — a stored, replayable assertion:

def verify_reproducible(conn, prediction_id, tolerance=1e-6):
    """Re-derive the stored prediction from its recorded inputs.

    Asserts that: the row is unchanged since grading, the inputs it
    was computed from are still the inputs on record, and recomputing
    from those inputs lands on the stored numbers.
    """
    row, mutations = conn.execute("""
        SELECT p.*, count(m.id) AS mutation_count
        FROM ai_predictions p
        LEFT JOIN prediction_mutations m ON m.prediction_id = p.id
        WHERE p.id = %s
        GROUP BY p.id
    """, (prediction_id,)).fetchone()

    graded_mutations = conn.execute("""
        SELECT count(*) FROM prediction_mutations
        WHERE prediction_id = %s AND after_row ? 'direction'
          AND (before_row->>'direction') IS DISTINCT FROM (after_row->>'direction')
    """, (prediction_id,)).fetchone()[0]

    if graded_mutations:
        return False, "graded field changed post-settlement (%d times)" % graded_mutations

    recomputed = rederive(row["model_id"], row["fixture_id"], row["price_snapshot_id"])
    for k in ("p_home", "p_draw", "p_away"):
        if abs(float(recomputed[k]) - float(row[k])) > tolerance:
            return False, "recompute drifted on %s" % k
    return True, "reproduced"

The interesting failure mode is the third one, and it is the one nobody instruments: rederive returns a different number because the inputs changed underneath the row — the price snapshot was refreshed in place, the fixture was remapped, the model's prompt template was edited. A prediction can be perfectly immutable and still not reproducible, because its inputs were not. Which is why the snapshot has to be frozen before kickoff and referenced by id, not by timestamp.

That is the whole argument compressed: immutability is about the row, reproducibility is about the row plus its inputs, and neither is a property of your code — both are properties of your schema.

What we are changing, in this order

Being precise about status, since the whole post is about not overstating things — at the time of writing the numbers above, the fix is specified and not yet deployed. The plan, in dependency order:

1. prediction_mutations companion table first, with the audit trigger. This is additive and cannot break a writer, so it goes out before anything that can reject a write.
2. Backfill nothing. There is no before-image for the existing 35,013 rows, and inventing one would be worse than admitting the gap. The audit trail starts at the timestamp it starts.
3. (model_id, fixture_id) uniqueness, added NOT VALID first, validated after a duplicate scan — duplicates are sitting at zero right now by luck, not by constraint.
4. Only then the BEFORE UPDATE hard stop on graded fields, after auditing which writers actually touch them. A trigger that rejects a write some nightly job depends on is an outage with extra steps.
5. Grading moved into a single SECURITY DEFINER function that refuses to run twice for the same row.

Rough effort: about a day of work, spread across four deploys specifically so that no single step can take the writers down. The expensive part is not the SQL. It is admitting that a table with 46,192 rows and zero constraints was never immutable — it was merely unchallenged.

If you take one thing from this: go and run the three queries at the top against your own predictions table. The number you get for "rows modified after settlement" is the size of the trust gap you are currently asking your users to close on faith.

Analytics and research content. Not betting advice. 18+