[Spike] Logic to update the Seal of Reliability

[Alessandro100]

Write a shared function that would update the feed.seal_of_reliability property. With tests

Current Logic (As of 2026-07-27)

Official:

• Official at time of reviewing dataset. Does not require a 6 month check.
• Calculation: is_official = true

Stable:

• dataset created_at has to be six months or longer in the past AND it is_producer_unstable = null/false.

Available:

• Successfully returned a response for 6 months. No 15 subsequent days within this period of outages.
• Calculation: Every day, look back 6 months (or as long as we have availability data) and check that there are no 15 subsequent days of outage. Success = returned a response in the HTTP 200s. [From pre-existing availability table]
• Grace period: 14 days. So if a feed fails to return a successful response, they have 14 days to fix the problem before disqualification.

Compliant:

• Returns no validation errors for 6 months.
• Calculation: 0 errors returned from the validation report. Any validation error that is currently in the feed is less than 30 days old.
• Grace period: If a validation error is returned, there are 30 days for it to be fixed before it's disqualified.

Fresh: Includes 7 days or more service coverage each day.

• Calculation: service_end_date extends 7 day or more into the future.
• Grace period: if service_end_date extends less than 7 days into the future, 14 days to fix it before disqualification.
• Feeds marked as seasonal should not run this calculation.

Fresh: Has continuous service coverage across subsequent datasets with no coverage gaps.
Calculation: View in-depth calculation here.

For the Fresh-Continuous Coverage, here is the algorithm:

• If feed date not available
  • If calendar range < 2 years
    • Calendar gap (1 day) - F
      • Calendar no gap - T
    • If calendar range > 2 years - F
  • If feed date available
    • If feed date range < 2 years
      • If feed date no gap
        • Calendar gap - T
      • If feed date gap (any number of days)
        • Calendar gap - F
        • Calendar no gap T
    • If feed date range > 2 years - F

• Rationale behind 2 years
  • 80% of feeds with feed info file had a date range that covered 2 years or less

[emmambd]

SQL query here: https://metabase.mobilitydatabase.org/question/394-prospective-level-1-feeds-6-months-threshold-with-30-day-grace-period

[jcpitre]

Revised algorithm as of 2026-08-11

Logic to Update the Seal of Reliability

A nightly job evaluates each active GTFS feed against the six criteria (see Evaluation Criteria) and updates the seal_criterion and feed_reliability_seal tables (see #1760) accordingly.

Two pieces of state are tracked per feed per criterion, and they are independent:

• Criterion status (confirmed_pass) — does this criterion pass right now, debounced by its grace period.
• Probation (probation_start) — the penalty a criterion serves after a confirmed failure: 180 days with no observed failure, starting from the day it recovered.

The seal requires every criterion to be a confirmed pass and not on probation.

Constant: PROBATION_PERIOD = 180 days.

Vocabulary

The booleans are stored positively, so the good state is the default reading and negations do not compound. Four distinct states are involved and they are never interchangeable:

Term	Means
observed pass / observed failure	observed_pass = TRUE / FALSE — the criterion's own check on this day, with no debouncing.
confirmed pass / confirmed failure	confirmed_pass = TRUE / FALSE — the debounced status. A confirmed failure is an observed failure that outlasted the criterion's grace period, or one on a criterion not entitled to a grace period.
on probation	probation_start IS NOT NULL — the criterion is serving its 180 days and has not completed them since it last recovered.
overall criterion pass	A confirmed pass and not on probation. This is what each criterion must be for the feed to hold the seal.

A criterion that has never been evaluated is in none of these states — see step 5.

---

The nightly job

For each eligible feed and each criterion, at run time now. day(t) means date_trunc('day', t) in UTC — day granularity keeps a re-run on the same day idempotent.

1. Evaluate the criterion

Criterion-specific (see Evaluation Criteria). Produces a tri-state verdict.

prev <- the stored seal_criterion row for (feed, criterion), or nothing

first_evaluation <- prev does not exist                 -- no row for this (feed, criterion) yet
                    OR prev.observed_pass IS NULL       -- row exists, but no verdict was ever recorded

observed_pass <- evaluate(criterion, feed)    -- TRUE | FALSE | NULL

if observed_pass IS NULL:                     -- not evaluable this run
    leave the row untouched (status, probation, all timestamps), write no row if none exists,
    and skip this criterion — none of the steps below run for it

evaluated_at <- now                           -- only reached on a verdict

A missing input is never read as an observed failure: an upstream outage must not put criteria on probation or withdraw seals across the catalogue.

first_evaluation means "this criterion has never produced a verdict for this feed", which is broader than "the row is missing". Both halves are needed:

• No row. The normal case for a new feed, and also for a criterion added to seal_criterion_name after the feed was already being evaluated.
• A row whose observed_pass IS NULL. The schema defines NULL on the two status booleans as "not yet evaluated". A criterion can have been unevaluable on every run since the feed appeared — Compliant on a feed that has never had a validation report, for instance — and whether that shows up as a missing row or as a row with no verdict in it depends on whether the job writes one criterion at a time or all six together. Testing both covers it either way, and reading such a row as "already evaluated" would hand the criterion a grace period it has not earned.

No extra column is needed for this, but it rests on one invariant the job must respect: observed_pass is never written back as NULL. A run that cannot evaluate a criterion leaves the stored row exactly as it was and creates no row, so observed_pass IS NULL unambiguously means "never evaluated" rather than "not evaluable last night".

That also fixes the meaning of evaluated_at: it is the timestamp of the last verdict, not of the last attempt.

2. Track observed failures and set the confirmed status

if observed_pass:
    first_observed_failure_at <- NULL             -- the streak is over, the grace period resets
    confirmed_pass <- TRUE
    -- last_observed_failure_at and last_confirmed_failure_at are history and are never cleared

else:
    last_observed_failure_at <- now               -- never cleared
    if first_observed_failure_at IS NULL:         -- start of the current observed failure streak
        first_observed_failure_at <- now

    -- the check failed today, but the status stays a pass until the streak outlasts the grace period
    -- with no grace_period there is nothing to outlast: confirmed_pass follows observed_pass
    -- straight to FALSE, on the same day
    confirmed_pass <- the criterion has a grace_period
                      AND NOT first_evaluation
                      AND now - first_observed_failure_at < grace_period

    if confirmed_pass is FALSE:
        last_confirmed_failure_at <- now          -- never cleared

first_observed_failure_at is cleared as soon as a day has an observed pass, so it is NULL only at the start of a new streak, not on every day of one.

first_evaluation denies the grace period: a criterion whose very first verdict is an observed failure becomes a confirmed failure the same day. The grace period exists to hold a pass that the criterion has actually earned, and a criterion we have never once seen pass has nothing to hold. Without this, a feed whose first verdict is an observed failure would report that criterion as a confirmed pass for up to 30 days (Compliant's grace period) on evidence we do not have.

3. The criterion status

confirmed_pass is the value computed in step 2; probation plays no part in it. What that produces:

• A confirmed pass from the first evaluation. A criterion whose first verdict is an observed pass is a confirmed pass that same day.
• A confirmed failure from the first evaluation too. A criterion whose first verdict is an observed failure becomes a confirmed failure that same day, grace period or not — see first_evaluation in step 2.
• Confirmed failure to confirmed pass is immediate. The first day with an observed pass, the criterion is a confirmed pass again. No wait, no lookback.
• Confirmed pass to confirmed failure is debounced by the grace period. With a 14-day grace and a streak starting on D0, the criterion becomes a confirmed failure on D14, i.e. the 15th consecutive day of observed failure.
• Criteria with no grace period move in both directions immediately.
• confirmed_pass stays a pure function of first_evaluation, first_observed_failure_at and the criterion's grace period — it never depends on its own previous value. Once a streak outlasts its grace period, first_observed_failure_at stays pinned to the start of that streak, so the criterion remains a confirmed failure for as long as the streak lasts.

4. Update probation (probation_start)

Skipped entirely for criteria that have no probation (see the criteria table), whose probation_start stays NULL forever.

if observed_pass is FALSE:
    if probation_start IS NOT NULL                -- already on probation
       OR confirmed_pass is FALSE:                -- or today's failure is confirmed
        probation_start <- day(now) + 1 day       -- at best probation starts tomorrow, and only
                                                  -- if the criterion has recovered by then

else if probation_start IS NOT NULL
        AND now >= probation_start + 180 days:
    probation_start <- NULL                       -- 180 days with no observed failure, probation over

else:
    unchanged                                     -- still serving, or not on probation

Two rules put a criterion on probation:

• Recovery from a confirmed failure starts probation, in all circumstances — whether the criterion was already on probation or not. This is the confirmed_pass is FALSE condition.
• While on probation, recovery from an observed failure restarts it too. This is the probation_start IS NOT NULL condition.

5. Update has_seal on feed_reliability_seal

in_service <- the list of criteria that have a row where observed_pass IS NOT NULL

has_seal <- in_service is not empty                            -- in case no criterion is in service
            AND every criterion in in_service is an overall criterion pass:
                    confirmed_pass = TRUE
                    AND probation_start IS NULL

previous_has_seal <- the stored has_seal, or FALSE if the feed has no seal row yet

write the feed_reliability_seal row, whether has_seal is TRUE or FALSE
if has_seal <> previous_has_seal:  set seal_earned_at (granted) or seal_lost_at (withdrawn)

A criterion that has never been evaluated is skipped.

A criterion that was merely not evaluable this run keeps its stored verdict. Step 1 left its row untouched and in_service reads the stored observed_pass, so a criterion that has produced a verdict at any point stays in the roll-up with its last one. An upstream outage therefore freezes a criterion rather than quietly waiving it: only a criterion that has never once produced a verdict drops out.

The feed_reliability_seal row is written for every evaluated feed, on its first run and on every run after, whether or not the feed qualifies. A criterion may measure against that row's created_at, so a feed that does not qualify still needs one — otherwise such a criterion could never start counting and the feed could never come to qualify.

Treating a missing row as previous_has_seal = FALSE makes the first run behave correctly on both outcomes: it can grant the seal and stamp seal_earned_at, but it can never withdraw one, because the feed held nothing to lose.

---

Evaluation Criteria

Criterion	DB Source	How to Evaluate	Grace Period	Probation	Notes
Official	feed.official	feed.official = TRUE	None	None	Point-in-time state check; reflects the current state only.
Stable	feed_reliability_seal.created_at, feed.is_producer_unstable	feed_reliability_seal.created_at <= NOW() - INTERVAL '180 days' AND feed.is_producer_unstable IS NOT TRUE (null or false)	None	None	The 180 days are rolled into the check itself. This is the gate that says we have been tracking this feed for 180 days. The seal row is created on the feed's first evaluation, so the check fails on that run and the countdown runs from it. A URL change creates a new feed in our data model, which gets its own seal row and so restarts the countdown.
Available	gtfs_feed_availability_check	success = TRUE today	14 days	180 days
Compliant	validationreport.total_error via gtfsfeed.latest_dataset_id -> validationreportgtfsdataset	total_error = 0 on the latest dataset's validation report	30 days	180 days
Fresh — future coverage	gtfsdataset.service_date_range_end (latest dataset)	service_date_range_end >= NOW() + INTERVAL '7 days'	14 days	180 days	Feeds marked as seasonal should not run this calculation.
Fresh — continuous coverage	gtfsdataset.service_date_range_start/end and feed_info_date_range_start/end (#1775)	See #1782 — passes when the feed's coverage across datasets has no gap and each dataset's covered range is <= 2 years	7 days	180 days	Algorithm and open design discussion tracked in #1782.

Every criterion's check is written so that TRUE means the criterion passed, matching observed_pass with no inversion in the evaluator.

One open point on Stable: the check conflates two things. An observed failure means either "the producer is marked unstable" or "we have been tracking this feed for less than 180 days", with no way to tell which from the stored state. This is accepted, but it is a deliberate loss of detail.

[emmambd]

Seal of Reliability dashboard
Master Query
Master Document

[Alessandro100]

Definition of Done

• Clarify the logic of evaluation for each criteria
• Create tickets based on the work that needs to be done or link to existing tickets
• Clarify the infrastructure of the solution

Timebox: 1 days