Backfill testing

[jcpitre]

Seal of Reliability — backfill testing

Testing backfill_seal_of_reliability (#1818, issue #1763): first against a copy of production, then against production itself. Both write-ups are below, as comments.

Contents

1. Production run — findings — the real thing, 2026-09-10. All six criteria over 3,559 feeds in 5 minutes. What each criterion produced, why 603 feeds lost the seal, and the policy behaviours most likely to generate questions from data producers.

2. QA runs on the prod duplicate — where the detail lives. The fresh_continuous scan and verdict distribution, ten candidate feed shapes, and the run-by-run record: one feed, two feeds, twenty, then the whole catalog. Also the nightly job's race with the dataset pipeline, and the missing index behind #1831.

The short version

Seal holders	1,675 → 1,101 (34% lost)
Largest single cause	fresh_continuous, sole blocker for 349 of the 603
Probation vs failure	probation denies more seals than failure does — most denied feeds are passing today
Prod run duration	5 min 7 s, 36 of 36 batches

Three things worth a decision, all detailed in the first comment: whether fresh_continuous having no grace period is the intended trade-off, whether the 730-day coverage guard is firing on legitimate calendars, and whether the backfill should evaluate at midnight UTC given that datasets arrive between 00:00 and 03:00.

[jcpitre]

Seal backfill on production — findings

The whole-catalog backfill was run against production on 2026-09-10. This is what it did
and what it means.

run_id: seal-backfill-20260910T150451 · window 2025-09-09 → 2026-09-09 · all six criteria ·
snapshot_mode: all · only_missing: false

---

1. The run

status	completed, 36 of 36 batches, none pending or failed
duration	5 min 7 s (15:04:51 → 15:09:59 UTC)
feeds	3,559
snapshot rows	151,247 → 6,703,057

Batch size 100, 36 batches running in parallel through Cloud Tasks.

This only worked because the gtfsfile index
(#1831, merged as
#1832) was applied at 13:45 UTC
the same day. Without it a single feed's march takes 370 s — no worker would have finished
even one batch inside its 540 s timeout.

---

2. What the six criteria produced

Every feed now holds all six criteria. Confirmed status after the run, with the count of
criteria denying the seal — a criterion denies it by failing, or by passing while on
probation:

criterion	pass	fail	never evaluated	passing but on probation	denying the seal
official	2,924	635	0	0	635
stable	3,081	478	0	0	478
available	3,185	374	0	83	457
compliant	2,721	759	79	163	922
fresh_coverage	2,734	681	136	218	899
fresh_continuous	2,626	841	92	276	1,117

A criterion denies the seal by failing, or by passing while still serving probation. Three
things this table says that the seal count alone does not:

1. 740 feeds are denied while passing today. They are serving out a past failure —
   83 on available, 163 on compliant, 218 on fresh_coverage, 276 on fresh_continuous.
   Failure is still the larger cause everywhere, but this group is the one that recovers on a
   timer rather than on any change in the feed: as each 180-day probation expires, the seal
   count rises with nobody doing anything.
2. official and stable have no probation at all — they are point-in-time state checks,
   so a feed that becomes official, or passes 180 days of tracking, contributes immediately.
3. 307 criterion rows are never_evaluated across compliant, fresh_coverage and
   fresh_continuous — feeds that were UNKNOWN on every marched day and so produced no
   verdict. They cannot hold the seal, but they have not failed either. fresh_coverage also
   has 8 not_applicable rows, the seasonal feeds.

Note that probation_start being set does not by itself mean a feed is serving probation:
_probation_start stamps tomorrow on every confirmed-failing day, so a currently-failing
feed carries one too. It only becomes a served probation once the failures stop and that last
stamp is left behind — which is why the column above counts passing feeds only.

---

3. 603 feeds lost the seal

transition	feeds
held → held	1,065
held → lost	603
none → gained	29
none → none	1,862

1,675 holders before, 1,101 after — a 34% loss among holders.

What was solely responsible, for each of the 603:

sole blocker	feeds
fresh_continuous	349
two criteria at once	120
compliant	77
fresh_coverage	43
three criteria at once	13
available	1

So a little over half the losses are attributable to one criterion alone, and the rest to
compliant, fresh_coverage or combinations. fresh_continuous is the largest single cause
for a structural reason: it is the only criterion no feed had been evaluated against before
this run. The other five had been running nightly since 09-03, so most of their failures were
already reflected in the seal before the backfill touched anything.

---

4. What the march did to the five existing criteria

Only 115 confirmed-status changes across 17,795 pre-existing rows — 0.6%:

criterion	changed
fresh_coverage	55
compliant	33
available	10
official	0
stable	0

official and stable reproduce exactly. That is the strongest evidence the
reconstruction is faithful: both are computed from feed-level state rather than dataset
history, so an exact match means the march's day-by-day machinery agrees with the nightly job
wherever the inputs are unambiguous. The changes concentrate in the two criteria affected by
the 04:00 race (§6).

1,321 rows had their failure date reset to the window edge

criterion	rows
official	549
fresh_coverage	358
compliant	335
fresh_continuous	46
stable	33

These had an observed first_observed_failure_at replaced by 2025-09-09, march_start. This
is the cold start: on day 1 the state machine has no prior state, so a criterion already
failing reads as having first failed that day.

resume_from_snapshot would not have prevented it. It seeds from a snapshot before
march_start, and prod's snapshot history began 2026-08-27 — nothing exists that far back. A
cold start is inherent to backfilling further back than your snapshots reach.

How wrong the date is depends on the criterion. For official, whose 549 rows are the largest
share:

official	rows	is the window edge wrong?
null	526	No — it is a floor. Never marked official, so the feed was failing before the window opened too
false	23	Yes. Actively set false, with official_updated_at inside the window, so the failure began later

A feed that is official today was almost certainly always official; the flag is set by hand and
its timestamp records when someone got to it. So for official the genuinely wrong rows are
23, not 549. The equivalent split has not been done for fresh_coverage, compliant and
stable — those have reconstructible history, so their resets are more likely to be genuinely
wrong.

---

5. Two policy behaviours that will generate questions

Grace and probation are uneven across the six

Both values live in shared/common/seal_criteria.py. Grace is how long an observed failure
may run before it is confirmed; probation is how long a recovered criterion must stay clean
before it counts again.

criterion	grace	probation
official	none	none
stable	none	none
available	14 days	180 days
compliant	30 days	180 days
fresh_coverage	14 days	180 days
fresh_continuous	none	180 days

official and stable having neither is deliberate — they are point-in-time state checks
with nothing to debounce. fresh_continuous is the outlier: it is the only criterion that
both confirms on the first failing day and then imposes six months of probation.
Confirmed intentional, but it is the combination that produces the surprising outcomes.

One bad day costs six months. Two feeds where the effect was examined day by day:

feed	failing days	last failure	probation ends	seal
mdb-1224	6 of 366	2025-11-08	2026-05-08 — expired	held
mdb-740	9 of 366	2026-05-29	2026-11-26	revoked

mdb-740 is clean 97.5% of the year, is passing today, and still cannot hold the seal. This is
the behaviour hardest to explain to a data producer, and it follows entirely from the zero
grace period rather than from anything the feed did.

The 730-day guard is the largest single source of failure

MAX_COVERAGE_WINDOW rejects a dataset declaring service more than two years out, on the
grounds that it is more likely a placeholder calendar than evidence of coverage. It accounts
for 72,354 failing dataset pairs across 1,016 feeds — more than every other failure reason
combined. If it is firing on legitimate multi-year calendars, a large part of the 841
fresh_continuous failures above is misattributed.

---

6. The nightly job races the dataset pipeline

compliant goes UNKNOWN when the closest dataset has no validation report; fresh_coverage
when it has no service window. The nightly job runs at 04:00 UTC and 99.7% of datasets are
downloaded between 00:00 and 03:00, so the job routinely evaluates a dataset a few hours old
whose report and window have not been ingested yet.

• 267–437 feeds UNKNOWN per night for compliant, 346–513 for fresh_coverage.
• Yet only 76 of 2,775 feeds have a latest dataset genuinely missing a service window, and
  13 a validation report.

So ~400 UNKNOWNs a night against ~90 feeds with actually-absent data. The backfill reads
today's completed data and is therefore systematically more optimistic than the nightly job
on these two criteria — and not only outside the recorded range, since UNKNOWN days are
excluded from the recorded set and get reconstructed even inside it. That is where the 88
changed fresh_coverage and compliant rows in §4 come from.

---

7. Related: the nightly job had been failing

Worth recording because it was found during this work and is now fixed.

run	status	duration
2026-09-06 → 09-09	completed	~63 s each
2026-09-10	failed	hit the 3,600 s deadline, 13 of 15 batches never reported

The 09-10 nightly run processed 309 of 3,559 feeds. It was the first run after
fresh_continuous reached production, and the criterion's history load hits the correlated
EXISTS over the unindexed gtfsfile — 2.09M rows, 773 MB. The index landed at 13:45 UTC the
same day.

Attribution, precisely: the calendar check came with
#1824 (merged 09-03), which
added fresh_continuous and the EXISTS. #1818
(merged 09-09) moved it into a shared loader called once per evaluator, tripling how often it
runs per batch. Both reached prod in the deploy before the 09-10 run.

Tonight's 04:00 run is the first since the index landed — worth confirming it completes.

---

8. Still open

1. The dataset history loads three times per batch. PreloadedHistory.load calls
   load_history() once per evaluator, and compliant, fresh_coverage and
   fresh_continuous all return the same load_dataset_history(...) — six queries where two
   would do. The class docstring claims criteria reading the same table share one load; nothing
   implements it. Cheap now the index exists; worth fixing before the catalog grows.
2. The march evaluates at midnight UTC, before the day's datasets are downloaded, so its
   "day D" means the dataset from D-1 while the nightly job's means D's. A consistent one-day
   offset against nightly-recorded days. Evaluating at noon would align them; day_start's
   stated reasons hold equally at noon.
3. Whether the 730-day guard is correct (§5).
4. Whether official can go true → false. If it can, the march marks pre-flip days as
   failing when they should pass — a larger issue than the timestamp.

---

Files

Baseline was captured before the run and is the only surviving record of the pre-backfill
state. Read-only export via config/.env.prod.ro.

file	rows
prod_baseline_seal.csv	3,559
prod_baseline_criterion.csv	18,104
prod_baseline_snapshots.csv	151,247
prod_after_seal.csv	3,559
prod_after_criterion.csv	21,354

Post-run snapshots were not exported — 6.7M rows. Query seal_criterion_snapshot directly.

The per-feed case analysis behind §5, and the run-by-run record from the copy of production
this was tested on first, are in ../seal-backfill-qa/.

[jcpitre]

Seal of Reliability — backfill QA runs

Testing backfill_seal_of_reliability (PR #1818, issue #1763) against real prod data.

Started as a fresh_continuous exercise — §2 and §3 are specific to that criterion — and
grew to cover the task as a whole: §7 marches all six criteria over the full catalog, §8
is a nightly-job timing problem found on the way, §9 the performance work behind #1831.

Written 2026-09-09/10. Every feed id and count comes from MobilityDatabaseProdDuplicate,
a copy of prod taken 2026-09-08/09, reached through the QA SSH tunnel on local port 9902.
Re-run the scan in §2 if that copy is refreshed.

---

1. Environment

Everything below — the scan and the backfill runs — goes against the prod duplicate
through the tunnel, not the local docker Postgres. It carries a year of real dataset
history, which is what makes a backfill test worth anything.

---

2. The scan

fresh_continuous_scan.sql (this folder) replicates the evaluator's decision tree in SQL over
gtfsdataset + feedinfo + gtfsfile, for active GTFS feeds.

set -a && source config/.env.qa.ro && set +a
PGPASSWORD="$POSTGRES_PASSWORD" psql -h localhost -p 9902 -U "$POSTGRES_USER" \
  -d MobilityDatabaseProdDuplicate -f misc/AI/seal-backfill-qa/fresh_continuous_scan.sql

It emits one row per dataset pair. fresh_continuous judges the junction between a
dataset and the one downloaded before it, so a pair is one verdict — a feed with 350
datasets in the year produces 349 of them, not 365.

verdict	dataset pairs	feeds
PASS service-no-gap	147162	2485
FAIL window-too-long	72354	1016
FAIL no-feedinfo-no-calendar	59097	816
PASS declared-no-gap	4937	499
UNKNOWN no-window-yet	3868	437
PASS only-dataset	2071	2071
FAIL older-no-calendar	968	566
FAIL service-gap	751	409
UNKNOWN no-service-window	460	293
PASS declared-gap-but-service-covers	2	2
FAIL gap-in-both	1	1

---

3. Candidate feeds

Ten cases over the last 365 days. Counts are dataset pairs. All of these are selected by
the task's eligibility query (status NOT IN ('deprecated','development') and
operational_status = 'published').

#	Case	Feeds	What it exercises
1	Steady PASS, full year	ntd-80188, tld-61, mdb-2091 (347/345/338)	Baseline. Clean seal, no failure history
2	Steady FAIL, full year	mdb-2149	window-too-long every day. Confirms on day 1 and never recovers, so probation never starts — first_observed_failure_at lands on march_start
3	Steady FAIL, missing files	tld-752	No feed_info.txt and no calendar — the FAIL-not-UNKNOWN branch
4	PASS → FAIL mid-window	mdb-929 (219 PASS then 124 FAIL), tld-89	Immediate confirmation and probation — fresh_continuous has no grace period. Run 2026-09-09, see §5
5	PASS → FAIL on a real gap	tld-7878 (181/39), mdb-1931 (85/14)	Same as #4 but the gap arithmetic rather than the 730-day guard
6	FAIL → PASS	tld-5863, mdb-2202	Recovery / probation. Both show ~330 FAIL then exactly 19 PASS, so probation is still open at end_date
7	Intermittent short FAIL streaks	mdb-1224 (3/148), tdg-79014 (3/256), mdb-740 (8/252)	Repeated confirm-then-recover cycles: with no grace, even a 3-day streak confirms, so each one restarts the 180-day probation
8	UNKNOWN-heavy	mdb-2727 (164, all UNKNOWN), tld-4456 (288 UNKNOWN / 42 FAIL / 18 PASS)	The UNKNOWN + grace fix (commit f689569). tld-4456 interleaves UNKNOWN with a failing streak — the escape the fix closes
9	Declared vs validated windows	tld-7878 (08-30), tdg-80415 (09-02) pass; mdb-982 (08-29) fails	The only three pairs in the catalog where declared and validated disagree
10	Young feed, short march	tdg-84170 (first dataset 09-02, 7 pairs), tdg-84156	march_start = max(start_date, created_at) clamping, and the open-streak handover in PR #1818 review note 2

Caveats

1. Per dataset pair, not per calendar day. The march resolves the closest dataset on
   each day; the scan evaluates each transition once. Verdict sets and order match, day
   counts do not.
2. window-too-long dominates the failures — 1016 feeds. If that guard is firing on
   legitimate multi-year calendars, most of the FAIL candidates change character.
3. The scan filters on status = 'active' and data_type = 'gtfs', which does not match
   the task's predicate — it misses operational_status, and active is narrower than
   NOT IN ('deprecated','development'). The feeds listed above have been checked against
   the real predicate, but a fresh scan needs that filter fixed.

---

4. Worked example: ntd-80188 (case 1)

Before

feed_reliability_seal: has_seal false, created 2026-08-27.

seal_criterion: 5 rows — official fail/fail (first_observed_failure_at 2026-08-27),
stable / available / compliant / fresh_coverage all pass. No fresh_continuous
row; this copy predates the nightly job evaluating it.

seal_criterion_snapshot: 37 rows, 2026-08-27 → 09-08. official all 13 days, the other
four from 09-03. Baseline: baseline_*.csv in this folder, case column = 1.

date	official	stable	available	compliant	fresh_coverage
08-27 → 09-02	fail	—	—	—	—
09-03	fail	pass	pass	pass	pass
09-04, 09-05	fail	pass	pass	unknown	unknown
09-06 → 09-08	fail	pass	pass	pass	pass

(observed status; confirmed stayed pass on the unknown days)

The run

curl -s -X POST http://localhost:8080 -H "Content-Type: application/json" \
  -d '{"task":"backfill_seal_of_reliability","payload":{"stable_feed_ids":["ntd-80188"],"dry_run":false,"days_back":365,"end_date":"2026-09-08","only_missing":false}}' \
  | python3 -m json.tool

end_date pinned so re-runs are reproducible; the default is yesterday UTC and moves daily.
only_missing: false stops the five existing rows counting as done.

Resolves to 366 days, 2025-09-08 → 2026-09-08, six criteria, no created_at clamping.

Result

criterion_rows_written: 6, snapshot_rows_written: 2196, seals_granted: 0. All six
criteria confirmed at end_date: official fail, the rest pass — including
fresh_continuous, as case 1 predicts. feed_reliability_seal.created_at stayed
2026-08-27 rather than becoming march_start; the backfill's write is insert-only.

Recorded days are reused only for their observed_status. Get this right before reading
a diff: _load_recorded_observations returns a status per (feed, criterion, day), and
transition() recomputes every other column from the march's own clock. A whole-row
comparison against the baseline shows differences that are entirely correct.

Change on the 13 recorded dates	Why
last_verdict_at 04:00:xx → 00:00:00	The march evaluates at midnight UTC; the nightly job ran at 04:00
09-04, 09-05 compliant/fresh_coverage: unknown → pass	UNKNOWN is filtered out of _load_recorded_observations, so those days are not recorded days and got reconstructed. See §8
official.first_observed_failure_at 2026-08-27 → 2025-09-08	Cold start: official has no history, so a failing feed reads as failing since march_start — PR #1818 review note 1

The third row has teeth: a re-march over a feed the nightly job already tracks replaces
its observed first-failure date with the window edge. resume_from_snapshot: true does
not avoid it here — it seeds from a snapshot before march_start, and none exists that
far back. See §7 for what the flag actually does and how wrong the resulting date is.

Cost: 370 s before the gtfsfile index, 2.27 s after (§9). Budget from 2.27 s.

---

5. Case 4: mdb-929 and tld-89 (run 2026-09-09)

Both active/published/official, created well before the window, so both marched the
full 366 days. Baselines and results: case4_*_before.csv / case4_*_after.csv.

criterion_rows_written: 12, snapshot_rows_written: 4392, 3.58 s for two feeds.

fresh_continuous has no grace period — by design

GRACE_PERIODS at shared/common/seal_criteria.py:107 gives fresh_continuous None,
so it confirms on the first failing day. Confirmed intentional, 2026-09-09. Only
available (14d), compliant (30d) and fresh_coverage (14d) debounce; probation is 180
days for all four.

This shapes every case in §3: no failing streak is ever too short to count. Case 4 tests
immediate confirmation and probation, not grace. The run bears it out —
first_observed_failure_at equals the first failing snapshot on both feeds.

What the march reconstructed

mdb-929:

observed	span	days
pass	2025-09-08 → 2026-01-05	120
unknown	2026-01-06 → 01-07	2
pass	2026-01-08 → 2026-05-08	121
fail	2026-05-09 → 2026-09-08	123

tld-89 is far choppier — five separate failing streaks, the first from 2025-09-12:

observed	span	days
pass	2025-09-08 → 09-11	4
fail	2025-09-12 → 10-30	49
pass	2025-10-31 → 12-13	44
fail	2025-12-14 → 2026-01-05	23
unknown	2026-01-06	1
fail	2026-01-07 → 03-12	65
pass	2026-03-13 → 05-07	56
fail	2026-05-08 → 2026-09-08	124

Outcome

	mdb-929	tld-89
has_seal before	false	true
has_seal after	false	false — revoked
fresh_continuous	fail/fail, first fail 2026-05-09, probation 2026-09-09	fail/fail, first fail 2026-05-08, probation 2026-09-09
compliant	fail/fail, first fail 2025-09-08 (window edge)	pass/pass

Three things to note:

1. tld-89 lost its seal (seals_revoked: 1) purely because fresh_continuous was
   added — its other five criteria all pass. That is the criterion doing its job, but it
   means enabling fresh_continuous will revoke seals in bulk.
2. mdb-929's compliant cold-started at the window edge, 2025-09-08, the same
   artefact as official on ntd-80188. Its pre-run value was 2026-09-05, from the nightly
   job — real history replaced by a reconstruction.
3. Both show probation_start = 2026-09-09, the day after end_date, because both are
   still failing when the march ends. Probation only starts once a criterion recovers, so
   this is the "still broken at handover" state, not a served probation.

---

6. All twenty feeds (run 2026-09-09)

One run, all 20 case feeds: criterion_rows_written: 120,
snapshot_rows_written: 38496, 39.5 s. after_allcases_*.csv holds the result.

Three feeds marched less than 366 days, as case 10 predicts: tdg-84156 and tdg-84170
got 7 days each, tdg-79014 and tdg-80415 273 (created 2025-12-10).

Seals: 6 held before, 2 after

feed	case	before	after
tld-61	1	held	held
mdb-1224	7	held	held
tld-89	4	held	revoked (in the case 4 run)
mdb-2202	6	held	revoked
tld-5863	6	held	revoked
mdb-740	7	held	revoked

Every revocation came from fresh_continuous alone. Adding this criterion to a catalog
where the nightly job has been granting seals on five criteria will revoke in bulk — 4 of
6 in this sample.

Observed days per feed

feed	case	pass	fail	unknown	days
ntd-80188	1	366	0	0	366
tld-61	1	366	0	0	366
mdb-2091	1	366	0	0	366
mdb-2149	2	0	366	0	366
tld-752	3	0	366	0	366
mdb-929	4	241	123	2	366
tld-89	4	104	261	1	366
mdb-1931	5	290	76	0	366
tld-7878	5,9	308	56	2	366
tld-5863	6	18	348	0	366
mdb-2202	6	18	348	0	366
mdb-1224	7	360	6	0	366
mdb-740	7	357	9	0	366
tdg-79014	7	269	3	1	273
mdb-2727	8	0	0	366	366
tld-4456	8	17	60	289	366
mdb-982	9	362	4	0	366
tdg-80415	9	272	0	1	273
tdg-84156	10	6	0	1	7
tdg-84170	10	6	0	1	7

Case 7 shows the 180-day probation clearly

All three case 7 feeds pass at end_date with a handful of failing days, yet only one
keeps the seal — the difference is when the failures happened:

feed	failing days	last failure	probation ends	seal
mdb-1224	6	2025-11-08	2026-05-08 — expired	held
mdb-740	9	2026-05-29	2026-11-26	revoked
tdg-79014	3	2026-04-22	2026-10-20	(had none to lose)

mdb-740 fails 9 days out of 366 — 97.5% clean — and still cannot hold the seal, because
its last failure was inside the probation horizon. With no grace period, a single bad day
costs 180 days. That is the behaviour most likely to surprise people when the criterion
goes live.

Probation states at end_date

• Still failing — probation_start = 2026-09-09, the day after end_date:
  mdb-2149, tld-752, mdb-929, tld-89. Not a served probation; an open failure.
• Recovered, probation running: mdb-1931 (from 2026-04-15), tld-7878 (08-05),
  mdb-2202 and tld-5863 (08-22), mdb-740 (05-30), tdg-79014 (04-23),
  mdb-982 (09-03).
• Clean — no probation: ntd-80188, tld-61, mdb-2091, mdb-1224, tld-4456,
  tdg-80415, tdg-84156, tdg-84170.
• mdb-2727 is unknown / never_evaluated — 366 UNKNOWN days, so it never produced
  a verdict at all. Case 8 exactly.

Cases 2 and 3 cold-start at the window edge

mdb-2149 and tld-752 fail every marched day, so first_observed_failure_at is
2025-09-08 — march_start, not a date anyone observed. Same artefact as official on
ntd-80188 (§4) and compliant on mdb-929 (§5). It is inherent to a cold start against a
feed that was already failing when the window opened.

---

7. Whole catalog (run 2026-09-09/10)

All 3,559 eligible feeds, batches of 200 through
run_full_backfill.sh. The first pass covered 2,988 feeds in 82 minutes before the SSH
tunnel dropped mid-INSERT; a resume finished the remaining 458 in 14. Feeds commit
individually and only_missing skips finished ones, so the interruption cost nothing but
time.

feeds marched     3,446 (+113 already complete)
snapshot rows     6,547,332
criterion rows    20,676
wall clock        97 min (1.69 s/feed)

Result files: after_full_seal.csv, after_full_criterion.csv. Snapshots were not
exported — 6.5M rows is ~600 MB of CSV; query the table instead.

601 feeds lost the seal

transition	feeds
held → held	1,063
held → lost	632
none → gained	31
none → none	1,833

1,695 holders before, 1,094 after — a net loss of 601, or 35% of all seal holders.

Which criteria block the 632 that lost it:

blocking criterion	feeds
fresh_continuous	501
fresh_coverage	155
compliant	111
available	15

fresh_continuous is the sole blocker for 375 of the 632 (59%). Its own verdicts
across the catalog: 2,614 pass (73.4%), 830 fail (23.3%), 92 never_evaluated — feeds
that were UNKNOWN on every marched day and so produced no verdict at all.

The other five criteria barely moved

The march recomputed all six, but the five that already existed came out almost identical
to what the nightly job had:

criterion	unchanged	changed
official	3,559	0
stable	3,559	0
available	3,544	15
compliant	3,519	40
fresh_coverage	3,491	68

123 changed rows out of 17,795 — 0.7%. That is a strong signal the reconstruction is
faithful: official and stable reproduce exactly, and the three data-dependent criteria
differ only where the 04:00 race (§8) left the nightly job with an UNKNOWN. The largest
single flip is never_evaluated → pass (22 for fresh_coverage, 11 for compliant).

1,275 rows had first_observed_failure_at reset to the window edge

The cold start seen on ntd-80188 (§4) and mdb-929 (§5) is catalog-wide:

criterion	rows whose first_observed_failure_at became 2025-09-08
official	549
fresh_coverage	358
compliant	335
stable	33

resume_from_snapshot would not have helped. It seeds from the latest snapshot
strictly before march_start (_seed_states), and snapshots in this database begin
2026-08-27 while march_start was 2025-09-08 — nothing qualifies, so every pair
cold-starts with or without the flag. The flag is for a re-march whose window starts
inside existing snapshot history; it cannot help a window that reaches back further than
the history does. A cold start is inherent to backfilling past your snapshots, and
first_observed_failure_at landing on the window edge is its visible cost.

How wrong the date is depends on the criterion. Taking official as the worked case,
its 549 rows split:

official	rows	is march_start wrong?
null	526	No, it is a floor. The flag was never set, so the feed was failing before the window opened too. The window simply cannot express an earlier date
false	23	Yes. The flag was actively set to false, and all 23 have an official_updated_at inside the window, so the failure began later than march_start claims

A feed that is official = true today was almost certainly always official — the flag is
set manually, so its date records when someone got to it, not when the feed became
official. Projecting today's value backwards is therefore right for the 2,924 passing
feeds, and they get no failure timestamp at all.

So for official the genuinely wrong rows are 23, not 549. The fix is narrow: when
official is false and official_updated_at falls inside the window, start the streak
there. Two things to settle first — whether that timestamp tracks the flag specifically or
just any bulk write to the row (the dates cluster suspiciously: 431 in one month, 497 in
another), and whether a feed can go true → false at all, because if it can, the march is
marking pre-flip days as failing when they should pass.

The same split has not been done for fresh_coverage, compliant and stable. Those
criteria do have reconstructible history, so their overwritten dates are more likely to be
genuinely wrong than official's are.

---

8. The nightly job races the dataset pipeline

compliant goes UNKNOWN when the closest dataset has no validation report;
fresh_coverage when it has no service_date_range_end. The nightly job runs at 04:00
UTC and ntd-80188 publishes around 01:00, so on 09-04 and 09-05 it saw a three-hour-old
dataset whose report and window had not been ingested yet. Both are present now, so today's
data cannot reproduce the UNKNOWN.

It is a race, not missing data:

1. UNKNOWN is chronic and catalog-wide — 267–437 feeds/night for compliant,
   346–513 for fresh_coverage, every night in the range.
2. Yet of 2775 published active GTFS feeds, only 76 currently have a latest dataset with
   no service window and 13 with no validation report.

~400 UNKNOWNs a night against ~90 feeds with actually-absent data.

Consequence. Reconstructed days come out PASS where the nightly job recorded UNKNOWN,
because the march reads today's complete data. The backfill is systematically more
optimistic than the nightly job on these two criteria — and not only outside the recorded
range: UNKNOWN days are excluded from _load_recorded_observations, so they are
reconstructed even inside it.

---

9. Two performance notes

The missing gtfsfile index — fixed. One feed over 366 days took 370 s, nearly all of
it in load_dataset_history; with the index it takes 2.27 s. Applied by hand to the
duplicate, shipped as liquibase/changes/feat_1831_idx.sql (issue #1831). Full write-up in
../issue-gtfsfile-missing-fk-index.md. The same subplan is what makes the §2 scan
unrunnable if you write the calendar check as a correlated EXISTS instead of a
pre-aggregated CTE.

The dataset history loads three times per batch. PreloadedHistory.load calls
load_history() once per evaluator, and compliant, fresh_coverage and
fresh_continuous all return load_dataset_history(...) with identical arguments — two
queries each, six executions where two would do. The class docstring claims criteria reading
the same table share one load; nothing implements it. Cheap now, worth fixing before the
fan-out runs at catalog size.

A killed psql client does not kill the query. The scan was stopped client-side and kept
running on the server for four hours, because the tunnel it arrived through had been
replaced and the server never saw the disconnect. Before blaming a slow run:

select pid, state, now()-query_start as age, left(regexp_replace(query,'\s+',' ','g'),120)
  from pg_stat_activity
 where datname='MobilityDatabaseProdDuplicate' and pid <> pg_backend_pid()
 order by query_start;

select pg_terminate_backend(<leader pid>) takes its parallel workers with it.

[jcpitre]

A worked example: how the backfill revoked a live seal

tld-7875 held the seal on 2026-09-10 and had lost it by 2026-09-11. Nothing about the
feed changed in between — the backfill did it. Worth writing up, because it is the clearest
illustration of something the findings above state abstractly: re-deriving history can
change a live, public verdict, not just fill in a missing one.

Before the backfill

From the baseline export taken the morning of 09-10, before the run:

seal:       has_seal = t,  seal_lost_at = (null)
compliant:  observed fail, confirmed PASS, probation_start = (null)

The nightly job had seen compliant start failing on 09-08. compliant carries a 30-day
grace period and no probation was open, so the failure was being absorbed: confirmed
stayed pass, and the feed kept the seal. That is what was on the site yesterday.

After the backfill

seal:       has_seal = f,  seal_lost_at = 2026-09-09 00:00
compliant:  observed fail, confirmed FAIL, probation_start = 2026-09-10

Marching the year reconstructed 143 failing days for compliant, including a long
streak from 2026-06-15. That history opens probation — and probation suspends grace. So
the same observed failure the nightly job was absorbing confirmed immediately once the
reconstruction was in place. The seal was revoked, stamped 2026-09-09 00:00, the marched
day at the march's midnight clock.

The snapshot trail

The 09-10 row is the fingerprint of the transition: it is the pre-backfill nightly run,
written at 04:00 that morning, nine hours before the backfill.

date	observed	confirmed	probation	written by
09-08	fail	fail	2026-09-09	backfill (rewrote this day)
09-09	fail	fail	2026-09-10	backfill (rewrote this day)
09-10	fail	pass	none	nightly, 04:00, before the backfill
09-11	unknown	fail	2026-09-10	nightly, carrying the backfill's state

Today's nightly observed unknown — the 04:00 race described in the QA write-up, where the
validation report had not been ingested yet — so it carried the confirmed fail forward
unchanged.

Why this matters

1. This is one of the 33 compliant changes reported above as evidence the
   reconstruction is faithful. It is: the march knows more than the nightly job did. But
   the same mechanism revoked a badge that was publicly displayed the day before.
2. Both views are defensible. The nightly job's was "one recent failing day, inside
   grace, keep the seal". The backfill's is "you failed 143 days this year, you are on
   probation, so this one counts". The backfill is better informed — it just changed a
   public status retroactively, using history nobody was tracking at the time.
3. It is not rare by construction. Any feed whose reconstructed history opens probation
   loses grace protection on its current failure. That is 115 changed rows catalogue-wide,
   and some unknown share of them moved a seal.

Worth deciding deliberately whether a backfill should be allowed to revoke a seal at all,
or whether it should seed history without touching the current verdict.