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 (SUBJECT TO MINOR CHANGES -- TO VERIFY)

Official: confirmed to be created on behalf of the transit agency
For at least six months
Stable: URL has not changed and appears to be permanent (no fixed dates or seasons in the URL)
Available: It returns a successful HTTP response/can be downloaded each day
Compliant: It has 0 errors in the GTFS Schedule Validator
Fresh: 
It has at least 7 days of future service coverage, based on GTFS best practices
There is continuous service coverage from the start and end of each dataset 

Grace period
If a feed is not available or fresh, the agency has 7 days to update to keep their badge. A 2 day gap in continuous service coverage is accepted.
If a feed has an error, the agency has 30 days to fix the error to keep their badge

We tested our Mobility Database feeds against this definition. [41% of the GTFS Schedule data meets this definition](https://metabase.mobilitydatabase.org/question/394-prospective-level-1-feeds-6-months-threshold-with-30-day-grace-period), showing that this bar is achievable, but still not met by slightly over half of all agencies

[emmambd]

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

[jcpitre]

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 (comment)) accordingly.

For each feed and each criterion, the job:

1. Evaluates the criterion against the current state of the DB and sets raw_failing = TRUE/FALSE and evaluated_at = NOW().
   Note: This step is not generic. The evaluation is specific to the criterion.

2. Manages failure tracking:

   • If raw_failing = TRUE:
     • Set last_raw_failure_at = NOW() (always updated on failure; never cleared)
     • If first_raw_failure_at IS NULL, set first_raw_failure_at = NOW() — since first_raw_failure_at is cleared on recovery, this is only NULL at the start of a new streak, not on every failure day
     • If grace period has expired (first_raw_failure_at < NOW() - grace_period): set last_grace_failure_at = NOW()
   • If raw_failing = FALSE:
     • Clear first_raw_failure_at = NULL (streak ended — grace period is reset)

   Note: This step is mostly generic, except the grace period is specific to the criterion.

3. Updates grace_failing:

   grace_failing =
      last_grace_failure_at IS NOT NULL
      AND last_grace_failure_at >= NOW() - 6 months
   

The grace period is absorbed naturally here: while a failure streak is within the grace window, last_grace_failure_at is never set, so grace_failing remains FALSE. It only flips to TRUE once the grace period expires.

4. Updates has_seal on feed_reliability_seal: TRUE only if all six criteria have grace_failing = FALSE. If has_seal changed, update seal_earned_at or seal_lost_at.

seal_lost_at is only set after a grace period has expired without recovery, never at the moment of first failure.

The raw source tables (gtfs_feed_availability_check, validationreport, gtfsdataset) are never modified by this job — it only reads from them and writes to seal_criterion and feed_reliability_seal.

---

Evaluation Criteria

Note: The evaluation criteria below are preliminary and intended to outline the general approach. A more complete and battle-tested implementation already exists as a SQL query in #1761 (comment). It should be used as the reference for the actual logic.

Criterion	DB Source	How to Evaluate	Grace Period	Notes
Official	feed.official	feed.official = TRUE	None
Stable	feed.producer_url	Regex finds no year, month name, or season in URL path	None	A URL change means a new feed is created in our data model, which resets the 6-month countdown.
Available	gtfs_feed_availability_check	success = TRUE today	7 days
Compliant	validationreport.total_error via gtfsfeed.latest_dataset_id → validationreportgtfsdataset	total_error = 0 on the latest dataset's validation report	30 days
Fresh — future coverage	gtfsdataset.service_date_range_end (latest dataset)	service_date_range_end >= NOW() + INTERVAL '7 days'	7 days
Fresh — continuous coverage	gtfsdataset.service_date_range_start/end across consecutive downloads	Gap between consecutive datasets ≤ 2 days	7 days	We might get away with only checking the last dataset vs next to last, since last_grace_failure_at already captures prior failures.

---

New Tables: feed_reliability_seal and seal_criterion

See #1760 for the DB tables that support this logic.

---

API processing

has_seal and grace_failing are stored directly and read as-is — no on-the-fly recomputation needed. The API can also expose raw_failing, first_raw_failure_at, last_raw_failure_at, and last_grace_failure_at from seal_criterion for per-criterion detail if needed.