A prediction log looks like a stable object. You query it, you get numbers, the numbers move when the model changes. That is the story you tell yourself.

Here is the same table, read honestly. ai_predictions holds 48,737 rows written between 2026-06-21 and 2026-11-21, across 130 columns. Eight of those columns have never held a single value. Forty-four were born after the first row was inserted. Eleven stopped being written before this month began.

Every one of those facts changes what a WHERE clause means, and none appear in the query you wrote six weeks ago.

All counts are from production, not an example schema.

The inventory query

Postgres knows which columns exist. It does not know when each one started carrying data, or whether it still does. That is one pass over the catalog plus one aggregate per column, and you cannot write it as a single static SELECT because a column name cannot be a parameter. Build one statement per column: on 48k rows the sweep takes about 90 seconds.

import psycopg2 DSN = "postgresql://user:pass@127.0.0.1:5433/analytics" conn = psycopg2.connect(DSN) cur = conn.cursor() cur.execute(""" SELECT column_name FROM information_schema.columns WHERE table_name = 'ai_predictions' ORDER BY ordinal_position """) cols = [r[0] for r in cur.fetchall()] cur.execute("SELECT min(created_at)::date FROM ai_predictions") birth = cur.fetchone()[0] inventory = [] for col in cols: cur.execute(f""" SELECT min(created_at)::date, max(created_at)::date, count({col}) FROM ai_predictions WHERE {col} IS NOT NULL """) first, last, n = cur.fetchone() inventory.append({"col": col, "n": n, "first": first, "last": last}) never = [r["col"] for r in inventory if r["n"] == 0] late = [r for r in inventory if r["first"] and r["first"] > birth] dead = [r for r in inventory if r["last"] and str(r["last"]) < "2026-11-01"] print(f"columns={len(cols)} never_written={len(never)} " f"born_late={len(late)} stopped={len(dead)}") print("never_written:", never) 

Output, verbatim from the run:

columns=130 never_written=8 born_late=44 stopped=11 never_written: ['user_id', 'iron_rule_results', 'cache_expires_at', 'gate_want_visible', 'eu_band', 'eu_band_hit_baseline', 'direction_gate', 'gate_reason'] 

Eight dead columns out of 130 is 6.2% of the schema. Four — eu_band, eu_band_hit_baseline, direction_gate, gate_reason — read like a band-based gating feature that was designed, migrated, and never wired up. They document an intention, not behaviour.

When the columns arrived

Grouping all 122 written columns by the month of their first non-null value gives the shape of the project rather than the shape of the data. Seventy-eight were present on day one.

first seen new columns
2026-06-21 (first row) 78
rest of 2026-06 12
2026-07 6
2026-08 13
2026-09 10
2026-10 2
2026-11 1

The 12 that landed in late June are typical: goal_line_direction, training_word_version, opening_assessment, dtl_at. The 13 in August are the second-market block plus the veto experiment. October's two are free-text reasoning fields. Each is a legitimate feature; each also carved a hole. Before the feature existed the column is null, and a null in a column that was not yet born is indistinguishable from a null in a column that was measured and came back empty.

That is the whole problem in one sentence. NULL means three different things and Postgres has one symbol for it:

  1. never measured — the feature did not exist yet
  2. not applicable — the feature existed, this row had no input
  3. measured, no value — the pipeline wrote nothing where it should have

Only the third is a bug. The first is your history. If you cannot tell them apart, you cannot audit anything.

Presence is not availability

ah_v5_p_up is the probability output of a v5 scoring pass. Count non-null rows per month:

month rows ah_v5_p_up non-null
2026-06 2,735 0
2026-07 2,637 1
2026-08 164 0
2026-09 9,757 43
2026-10 21,886 10,073
2026-11 11,558 10,615

The column was added in July. It became real in October. Between those two facts, 5,536 rows were written and 1 of them carries the v5 value.

So WHERE ah_v5_p_up IS NOT NULL GROUP BY month returns a table whose first three rows are empty. Not an error, not a warning. An empty result that looks like "v5 had no disagreements in June", when v5 did not exist in June. Anyone reading the output without the row counts beside it will draw the wrong conclusion, and the conclusion will be self-consistent.

The fix is not a filter, it is a habit: never publish a monthly series without the denominator next to it, and never let a filter quietly reduce the denominator to zero.

Availability is not comparability

The second failure is worse: the column is present and populated the whole time.

ah_tier exists from the first row and holds 46,826 values. It is a small integer severity bucket. Filter ah_tier >= 3 and measure the settlement rate per month:

month scored rows rate, all rows with ah_tier>=3 rate, filtered
2026-06 2,735 62.30% 0 —
2026-07 2,637 50.89% 0 —
2026-08 161 55.90% 0 —
2026-09 9,681 58.18% 1,029 64.53%
2026-10 21,719 56.27% 1,866 58.31%
2026-11 10,204 56.13% 725 59.72%

The column is not null in June. The value is simply never 3 or higher. The tier assignment logic that produces 3+ went live in September, so the filter returns zero rows for the first three months and then jumps to a +6.35pp lift.

Read that as a result and you conclude the tier rule has predictive value. Read it as a schema event and you conclude the tier rule has no June comparison at all — and the +6.35pp is measured against a September baseline that is itself 4.12pp below June's 62.30%. The filtered September slice, 64.53%, is only 2.23pp above the unfiltered June number it is being quietly compared to. Two things changed at once: the population and the pipeline.

This is the failure mode I care about most, because no invariant catches it. ah_tier is non-null, in range, type-correct — and incomparable across the boundary where its generating rule changed. Only the month × filter cross-tab exposes it.

Backfilled versus forward-only

Not every later column is a hole. Two of them behave completely differently.

confidence_calibrated sits at 98–100% fill in every month back to June. That is not plausible for a later addition — the value was recomputed and written back over the whole history. A backfill.

training_word_version shows a different signature:

month fill
2026-06 9.3%
2026-07 10.5%
2026-08 92.1%
2026-09 99.7%
2026-10 100.0%
2026-11 100.0%

Nine percent is the fingerprint of a pipeline emitting the field incidentally — some code path that happened to serialize the object. The jump to 92% in August is deliberate adoption. Nothing before August can be segmented by training word version, and no backfill is possible: you cannot reconstruct which word a model carried eight weeks ago if nobody wrote it down.

Both columns pass a naive count(*) completeness check. Only the monthly breakdown says which history is real.

That gives a usable classification from two queries:

  • fill roughly constant → backfilled, comparable history
  • fill steps from near-zero to high → forward-only, history starts at the step
  • fill rises then falls to zero → abandoned

The eleven abandoned columns:

column last non-null rows
hit_veto_ss, hit_veto_tag 2026-10-31 26,731 each
hit_1x2_semantic 2026-10-07 18,974
dict_win_rate 2026-10-19 394
rec_tier 2026-10-19 113
direction_dtl, hit_1x2_dtl, dtl_upper, dtl_lower, dtl_at 2026-10-27 340 each
tags 2026-10-04 7

hit_veto_ss is the interesting one: 26,731 rows accumulated over two months, then switched off on the last day of October. A dashboard built on it still renders — frozen denominator, flat trend line, forever. Nobody gets an alert because the query does not fail.

The guard

The defect isn't that the schema changed. It's that the change was invisible at query time. So make it visible.

def assert_available(cur, table, col, month, min_fill=0.50): """Refuse to compare across a month where the column is mostly absent.""" cur.execute(f""" SELECT count(*) AS n, count({col}) AS filled FROM {table} WHERE date_trunc('month', created_at) = date_trunc('month', %s::timestamp) """, (f"{month}-01",)) n, filled = cur.fetchone() rate = filled / n if n else 0.0 if rate < min_fill: raise RuntimeError( f"{table}.{col} at {month}: {filled}/{n} = {rate:.1%} " f"< {min_fill:.0%}. Column not available in this period." ) return rate 

Call it for every month before you plot it. Here, assert_available(cur, 'ai_predictions', 'ah_v5_p_up', '2026-06') raises on 0/2,735 and the chart never gets built. The failure moves from "a reader interprets a blank as a zero" to "the job crashes at 03:00 naming the column and the month". That is the trade you want.

A registry makes the same knowledge durable rather than tribal:

CREATE TABLE schema_drift_registry ( table_name text NOT NULL, column_name text NOT NULL, status text NOT NULL CHECK (status IN ('core','backfilled','forward_only','abandoned','never_used')), first_seen date, last_seen date, n_observed bigint, note text, PRIMARY KEY (table_name, column_name) ); 

Populate it from the inventory query and review the diff monthly. A column flipping from forward_only to abandoned is a change worth a ticket; one flipping to never_used after six months should be dropped from the schema instead of confusing the next reader.

What this table actually taught me

Three numbers, same database, same day: 130 columns declared, 122 ever written; 44 with no history before their birth date; 11 that stopped before this month began.

None of these are data-quality problems in the sense of wrong values. Every value that exists is correct. The defect is temporal: the shape of the record changed while the analyses kept treating it as one continuous population.

If you take one thing: NULL is not a value, it is a timestamp. Before you group by it, ask when that column started, when it stopped, and whether the WHERE clause you inherited is filtering rows — or filtering history.

Analytics and research content. Not betting advice. 18+

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