Stage incident follow-ups (2026-07-17)¶
Incident¶
Testing #1564 on stage: a headcount CSV upload after an API sync inserted
20 of 8485 rows (8465 skipped DUPLICATE_INSTITUTIONAL_ID), then the
chained emission_recalc was killed 60s in by a pod SIGTERM and sat
RUNNING with no recovery in sight.
Three root causes, one per section below.
1. Cross-source bulk replace¶
The per-year replace delete was scoped to source=CSV_MODULE_PER_YEAR
only, while the (user_institutional_id, sius_code) uniqueness check has
no source filter — so API-synced rows (EXTERNAL_INTEGRATION) survived
the delete and collided with every CSV row.
Decision (maintainer): a bulk per-year ingest (CSV or API) is a complete yearly export and replaces all machine-owned bulk sources.
BULK_PER_YEAR_SOURCES(models/data_entry.py): CSV per-year, API per-year, external integration. Manual + unit-specific always preserved.bulk_delete_by_source_yeartakessources: list[int]; both callers (base_csv_provider,base_tableau_api_provider) pass the constant.- Tests: unit (both providers) + pg integration seed an API row and prove it's replaced.
Follow-up (same incident, second layer): the replace DELETE keys on
the denormalized data_entries.year, but the API providers built their
entries without year/unit_id — so the cross-source delete matched
zero API rows and re-uploads still mass-collided. The stamp now lives
centrally in DataEntryService.fill_denormalized_scope (called by
create / bulk_create / bulk_copy — post-review altitude fix, replacing
the initial per-provider stamping), so no write path can omit it; manual
entries are stamped too. Migration 8eeff0a9fa26 backfills existing
NULL rows from each entry's carbon report. The data card also treats an API sync as existing data
(re-upload label + same ERROR/WARNING/SUCCESS color mapping as CSV, with
CSV precedence — a WARNING sync previously rendered as red "Add Data").
Follow-up (performance): the row loop ran ONE uniqueness SELECT per
row — at stage latencies an 8.5k-row parse dropped to 14 rows/s (~10
min), the same N+1 shape COPY batching had already removed on the write
side. The in-memory duplicate set is now seeded with all existing
(module, uid, sius_code) member tuples in one bulk query
(get_member_role_keys), gated to headcount ingests; the per-row check
is a set lookup. The manual-entry path keeps its single-row
check_member_role_unique.
Deferred (schema change on validated data — proposal for the lead):
DB-enforced member-role uniqueness as defense-in-depth: a partial unique
index on (carbon_report_module_id, data->>'user_institutional_id',
data->>'sius_code') WHERE data_entry_type_id = <member>, with the bulk
path COPYing into a staging table and INSERT … ON CONFLICT DO NOTHING.
Only variant airtight under concurrent writers; today the per-year
advisory locks already serialize bulk ingests, so this is belt-and-braces.
Costs: staging-table refactor of the COPY path and loss of per-row
"Row N: DUPLICATE" reporting.
Note: the test file itself carried 760 intra-file (uid, sius_code)
duplicate rows — those still skip by design; whether the key needs a third
component is an open data question, not covered here.
2. Stuck-job recovery latency¶
The pod was SIGTERMed mid-job. Root cause confirmed by probe-latency
measurement on stage: the recalc loop yielded the event loop only every
1000 entries, so /healthz (a no-op endpoint) couldn't get a slot within
the kubelet's 2s probe timeout during pure-CPU stretches — three straight
liveness failures at 20s period killed the pod exactly 60s after
Recalc member/2025 started. (Memory was fine: 798Mi of 1000Mi.
Readiness "connection refused" events were the restart gap, not the
cause.) Nothing marks a cancelled job, so the row stayed RUNNING until
the stale sweep — whose window was 60 min, a default from before the
310-C per-job heartbeat existed.
Fix (event-loop starvation): both CPU loops now yield on wall time
(~50ms) instead of entry count — recalc (emission_recalculation.py,
was every 1000 entries) and CSV row loop (base_csv_provider.py, was
every 100 rows). Recalc progress reporting also went time-based (every
2s, was every 5000 entries) with a terminal N/N update, and the job
status line carries rate + ETA. Ops action (cluster config, not this
repo): stage's probe overrides set timeoutSeconds: 2 — raise to 5
(the chart default) for liveness and readiness.
STALE_JOB_TIMEOUT_MINUTES60 → 5 (config default, helm, .env.example): heartbeats every 75s, dead-pod recovery ≤ ~6 min. The window bounds crash-recovery latency, not job runtime — stale docstrings claiming otherwise rewritten (sweep_stuck_running_jobs,_heartbeat_loop).- Not 2 min: the same window is the live runner's self-abort threshold on heartbeat failures; 5 min tolerates transient DB outages.
Deferred (needs plan + both maintainers): shutdown handler in
run_job releasing claimed jobs on CancelledError (near-instant recovery
after graceful kills); dedicated worker Deployment so long jobs stop
sharing lifecycle with HTTP pods; memory request raised toward real usage.
3. Operator visibility¶
- Jobs table gains
created_at(migration7bfda0d45f14); the ops console showsqueued Xs(created→started) next to the run duration — a sub-second aggregation that waited 20s no longer reads as "<1s total". PipelineJobListEntry.is_stalederived server-side (same predicate as the sweep); the console renders a stale badge + Recover button wiring the pre-existingPOST /jobs/{id}/recover. Deliberately not a new persisted job state — staleness is derived, not stored.