climate_sync silently dead-letters cells: 4 never recent-synced, 6 with zero derived rows, 39 stale >24h - nothing retries, nothing reports #55

New issue

Closed

opened 2026-07-24 19:54:20 +00:00 by admin_emi · 0 comments

admin_emi commented

2026-07-24 19:54:20 +00:00

Owner

Summary

The prod climate pipeline drops cells on the floor. Nothing retries them and nothing reports them, so the gaps persist indefinitely - the worst cell is 72.3h stale and climbing.

Evidence (prod, read-only, 2026-07-24 ~19:40 UTC)

Coverage across the three tables:

SELECT (SELECT count(*)          FROM climate_sync)   AS sync_cells,
       (SELECT count(DISTINCT cell_id) FROM climate_recent) AS recent_cells,
       (SELECT count(DISTINCT cell_id) FROM derived)        AS derived_cells;

sync_cells | recent_cells | derived_cells
-----------+--------------+--------------
      1000 |          996 |           995

Every cell with no recent rows and/or no derived rows:

SELECT s.cell_id, to_timestamp(s.history_synced_at) AS history_synced, s.recent_synced_at,
       (SELECT count(*) FROM climate_recent r WHERE r.cell_id = s.cell_id) AS recent_rows,
       (SELECT count(*) FROM derived d        WHERE d.cell_id = s.cell_id) AS derived_rows
FROM climate_sync s
WHERE (SELECT count(*) FROM climate_recent r WHERE r.cell_id = s.cell_id) = 0
   OR (SELECT count(*) FROM derived d        WHERE d.cell_id = s.cell_id) = 0
ORDER BY s.cell_id;

cell_id    | history_synced                | recent_synced_at   | recent_rows | derived_rows
-----------+-------------------------------+--------------------+-------------+-------------
1095_-2732 | 2026-07-22 12:08:36.633759+00 | (null)             |           0 |            0
1197_-2459 | 2026-07-22 12:08:43.924288+00 | (null)             |           0 |            0
1377_-2782 | 2026-07-21 19:23:53.110015+00 | 1784661834.9392867 |          33 |            0
1378_-2781 | 2026-07-22 02:56:00.114626+00 | 1784688960.3794012 |          33 |            0
1811_281   | 2026-07-24 04:56:37.538679+00 | (null)             |           0 |            0
2133_75    | 2026-07-24 03:43:53.067257+00 | (null)             |           0 |            0

Two distinct failure modes, not one:

4 hard dead letters (1095_-2732, 1197_-2459, 1811_281, 2133_75): history_synced_at is set, recent_synced_at is NULL, and there are zero rows in both climate_recent and derived. History landed, the recent sync never ran, nothing noticed.
2 half-populated cells (1377_-2782, 1378_-2781): these did recent-sync - 33 rows each - but produced zero derived rows. The derive step failed silently after a successful sync. These are also the two stalest cells in the entire table (72.3h and 64.8h), so they look stuck rather than merely slow. This mode is invisible to any check that only looks at recent_synced_at.

Staleness:

SELECT count(*) FILTER (WHERE recent_synced_at IS NULL) AS never_recent_synced,
       count(*) FILTER (WHERE recent_synced_at IS NOT NULL
             AND (extract(epoch FROM now()) - recent_synced_at)/3600.0 > 24) AS stale_gt_24h,
       round(max((extract(epoch FROM now()) - recent_synced_at)/3600.0)::numeric, 1) AS worst_stale_hours
FROM climate_sync;

never_recent_synced | stale_gt_24h | worst_stale_hours
--------------------+--------------+------------------
                  4 |           39 |              72.3

Ten stalest cells:

1377_-2782  72.3h      302_2664    30.8h
1378_-2781  64.8h      735_-5074   29.4h
1896_-32    41.0h      1370_-2782  29.2h
666_2760    40.8h      1550_-3005  29.0h
684_2462    39.3h
1319_-2324  31.8h

One orphan row in the other direction:

SELECT count(DISTINCT d.cell_id) AS derived_cells_not_in_sync
FROM derived d LEFT JOIN climate_sync s ON s.cell_id = d.cell_id
WHERE s.cell_id IS NULL;
-- 1

derived holds one cell that no longer exists in climate_sync, so reconciliation is missing in both directions.

Impact

Thermograph's whole proposition is "this location versus its own ~45-year history." A cell 72h stale is grading today's weather against a three-day-old recent window, and a cell with zero derived rows has no grading basis at all. Because nothing reports any of this, staleness is unbounded - the worst cell was measured at 71.8h earlier today and is 72.3h now, i.e. it is not being retried, it is simply aging.

Suggested action

Retry/backfill pass for cells where recent_synced_at IS NULL but history_synced_at is set.
Treat "recent-synced but zero derived rows" as its own explicit failure state - it is currently completely invisible.
Export a staleness metric (max hours since recent_synced_at, plus counts of the two dead-letter modes) and alert on it, so this surfaces without someone running SQL by hand.
Reconcile the derived / climate_sync orphan.

All figures re-derived from prod at filing time via read-only queries.

## Summary The prod climate pipeline drops cells on the floor. Nothing retries them and nothing reports them, so the gaps persist indefinitely - the worst cell is **72.3h stale and climbing**. ## Evidence (prod, read-only, 2026-07-24 ~19:40 UTC) Coverage across the three tables: ```sql SELECT (SELECT count(*) FROM climate_sync) AS sync_cells, (SELECT count(DISTINCT cell_id) FROM climate_recent) AS recent_cells, (SELECT count(DISTINCT cell_id) FROM derived) AS derived_cells; ``` ``` sync_cells | recent_cells | derived_cells -----------+--------------+-------------- 1000 | 996 | 995 ``` Every cell with no recent rows and/or no derived rows: ```sql SELECT s.cell_id, to_timestamp(s.history_synced_at) AS history_synced, s.recent_synced_at, (SELECT count(*) FROM climate_recent r WHERE r.cell_id = s.cell_id) AS recent_rows, (SELECT count(*) FROM derived d WHERE d.cell_id = s.cell_id) AS derived_rows FROM climate_sync s WHERE (SELECT count(*) FROM climate_recent r WHERE r.cell_id = s.cell_id) = 0 OR (SELECT count(*) FROM derived d WHERE d.cell_id = s.cell_id) = 0 ORDER BY s.cell_id; ``` ``` cell_id | history_synced | recent_synced_at | recent_rows | derived_rows -----------+-------------------------------+--------------------+-------------+------------- 1095_-2732 | 2026-07-22 12:08:36.633759+00 | (null) | 0 | 0 1197_-2459 | 2026-07-22 12:08:43.924288+00 | (null) | 0 | 0 1377_-2782 | 2026-07-21 19:23:53.110015+00 | 1784661834.9392867 | 33 | 0 1378_-2781 | 2026-07-22 02:56:00.114626+00 | 1784688960.3794012 | 33 | 0 1811_281 | 2026-07-24 04:56:37.538679+00 | (null) | 0 | 0 2133_75 | 2026-07-24 03:43:53.067257+00 | (null) | 0 | 0 ``` **Two distinct failure modes, not one:** - **4 hard dead letters** (`1095_-2732`, `1197_-2459`, `1811_281`, `2133_75`): `history_synced_at` is set, `recent_synced_at` is NULL, and there are zero rows in *both* `climate_recent` and `derived`. History landed, the recent sync never ran, nothing noticed. - **2 half-populated cells** (`1377_-2782`, `1378_-2781`): these *did* recent-sync - 33 rows each - but produced **zero `derived` rows**. The derive step failed silently *after* a successful sync. These are also the two stalest cells in the entire table (72.3h and 64.8h), so they look stuck rather than merely slow. This mode is invisible to any check that only looks at `recent_synced_at`. Staleness: ```sql SELECT count(*) FILTER (WHERE recent_synced_at IS NULL) AS never_recent_synced, count(*) FILTER (WHERE recent_synced_at IS NOT NULL AND (extract(epoch FROM now()) - recent_synced_at)/3600.0 > 24) AS stale_gt_24h, round(max((extract(epoch FROM now()) - recent_synced_at)/3600.0)::numeric, 1) AS worst_stale_hours FROM climate_sync; ``` ``` never_recent_synced | stale_gt_24h | worst_stale_hours --------------------+--------------+------------------ 4 | 39 | 72.3 ``` Ten stalest cells: ``` 1377_-2782 72.3h 302_2664 30.8h 1378_-2781 64.8h 735_-5074 29.4h 1896_-32 41.0h 1370_-2782 29.2h 666_2760 40.8h 1550_-3005 29.0h 684_2462 39.3h 1319_-2324 31.8h ``` One orphan row in the other direction: ```sql SELECT count(DISTINCT d.cell_id) AS derived_cells_not_in_sync FROM derived d LEFT JOIN climate_sync s ON s.cell_id = d.cell_id WHERE s.cell_id IS NULL; -- 1 ``` `derived` holds one cell that no longer exists in `climate_sync`, so reconciliation is missing in both directions. ## Impact Thermograph's whole proposition is "this location versus its own ~45-year history." A cell 72h stale is grading today's weather against a three-day-old recent window, and a cell with zero `derived` rows has no grading basis at all. Because nothing reports any of this, staleness is unbounded - the worst cell was measured at 71.8h earlier today and is 72.3h now, i.e. it is not being retried, it is simply aging. ## Suggested action - Retry/backfill pass for cells where `recent_synced_at IS NULL` but `history_synced_at` is set. - Treat "recent-synced but zero derived rows" as its own explicit failure state - it is currently completely invisible. - Export a staleness metric (max hours since `recent_synced_at`, plus counts of the two dead-letter modes) and alert on it, so this surfaces without someone running SQL by hand. - Reconcile the `derived` / `climate_sync` orphan. *All figures re-derived from prod at filing time via read-only queries.*