# Source availability record

**Since:** migration 221 (written 2026-10-30). **Table:** `source_health_events` (append-only). **Writer:** `lib/monitoring/source-health.ts` — every worker already reports through it. **Readers:** `source_availability(start, end)` (SQL), `lib/monitoring/source-availability.ts`, `GET /api/v1/status/availability?month=YYYY-MM` (public), the "Availability this month" block on `/status`.

## What it is

`data_sources` holds the current state of each feed and worker — `consecutive_failures`, `last_success` — and nothing else. When a feed was down last Tuesday, for how long, and what its error said, is gone by Wednesday. This record keeps the transitions:

- a **`failed`** row the first time a source fails after succeeding (streak 0 → 1), with the message, and
- a **`recovered`** row when a source succeeds after a streak (streak n → 0).

Since migration 229 (2026-11-06) the `recovered` row is written by a trigger on `data_sources` the moment any writer clears the streak, and `recordSourceSuccess` writes it only when the source's latest event is `failed`, so the two never double a recovery. Before that, sixteen workers reset their row directly and no recovery was ever recorded for them: a feed that failed once and read fine five minutes later carried an outage that never closed, and `/status` showed FIRMS, SPC and three NWS alert feeds at 0% for a month in which each had been up. 229 closes those outages with a `recovered` row at the first successful archived read after the failure (`as_issued_polls`, which records every read) — a measured time — or at the source's `last_success` where the feed is not archived, which is a bound; each such row says in `message` which it is.

Transitions, not ticks: a source down for a day is two rows, not 288. `source_availability(start, end)` pairs them into outages inside a window, clipped to it (an outage still open at the window's end is clipped to the end; one that began before the window counts from its start), and returns per source the outages, minutes in outage, the longest, whether one is open, and the message that opened the latest. The month endpoint adds `availability_pct`, the share of the month (to now, for the current month) not in an outage, lists every live source (one with no event was available throughout), and names the five least available.

## Why it matters

Every record on `/track-record` reads a federal feed. A buyer of a parametric trigger that settles on one of those feeds carries the feed's availability as basis risk, and no feed publishes its own outage history in a form a third party can hold it to. This is the platform's own independent record of those outages as its readers saw them — and of its own workers, which is the honest half.

## What a row says — and does not

- **`source_name`** is the `data_sources` name a worker reports under (`usgs`, `nws_storm_alerts`, `warning_exposure`, …), not the as-issued archive's source names.
- **An outage runs from the first failed read after a success to the next successful read, as the platform's own cron saw it.** A feed that was down and a worker that did not run (a paused cron, a deploy, a Vercel incident) read the same. The message that opened the outage usually tells them apart; the figure does not.
- **A worker's first failure after the table exists is a `failed` row** even if its streak was already running (the table did not exist to record the earlier transition), and the recovery pairs with it.
- **`availability_pct` is not the feed's own uptime figure.** It is the share of the window the platform was not in an outage against that source.
- **The record begins when migration 221 was applied.** Nothing earlier is reconstructed: the transitions were not kept.
- **Best effort, like all of source health.** A failed insert is logged (once per process while the table is missing) and never affects the worker that reported.

## Verifying

```sql
SELECT source_name, event, consecutive_failures, left(message, 80) AS message, recorded_at
FROM source_health_events
WHERE source_name = 'usgs'
ORDER BY recorded_at DESC
LIMIT 20;

SELECT * FROM source_availability(date_trunc('month', now()), now());
```

The table refuses `UPDATE` and `DELETE` (trigger `trg_source_health_events_immutable`), so a row, once written, is what the worker reported at the time.