p24-infra Docs

alert-staging-operations

4 min read

Alert Staging + Batched Triage — Operations

Service: alert-staging (Supabase table dev_r_alert_events + batch_stage_alerts() pg_cron) Design: alert-staging-batch-triage.md Issue: #3904 Compliance: dev_r_services row p24-infra/alert-staging (compliance_workbook=yes)

Single ingestion point for every alert source. Sources write staged events instead of calling gh issue create; a pg_cron scheduler groups correlated events and files one alert-triage-batch issue per incident instead of N independent ones.

Architecture

 Alertmanager ─┐
 n8n webhook ──┼──► POST /alert-ingest ──► dev_r_alert_events ──► batch_stage_alerts() ──► dev_r_worker_queue
 nightly-check ┘    (or alert-ingest.py)    (staging + dedup)      pg_cron every 2 min      (alert-triage-batch)
                                                                          │                          │
                                              lone critical ──────────────┘ (INSERT trigger)         ▼
                                              → immediate bypass                          alert-triage-batch worker
                                                                                          → ONE issue per group

Moving parts:

PartWhere
Staging table + indexes + RLSmonitoring/supabase/migrations/20260711_2245_alert_staging.sql
Ingest (HTTP)infra-src/meta-dispatcher/src/alert-ingest.ts → POST /alert-ingest
Ingest (Python/CLI twin)scripts/alert-ingest.py
Correlation schedulerbatch_stage_alerts() pg_cron job alert-batch-stage (*/2 * * * *)
Critical-path bypasstrigger dev_r_alert_events_critical_bypass (AFTER INSERT)
Batch worker skill.claude/commands/alert-triage-batch.md (Haiku)
Dispatch affinity + sweepscripts/queue-dispatcher-loop.py (_HARD_PREF_JOB_TYPES, host-affinity, cancel_unroutable_nc_alert_batch)

Table — dev_r_alert_events

ColumnMeaning
fingerprintsha1(alertname | (instance or affected_service) | severity) — dedup key
alertname / affected_service / instancecorrelation + display fields
severitycritical / warning / info
sourcealertmanager / n8n / nightly-infra-check
statusstaged → batched/bypassed → resolved
dedup_counttimes this fingerprint re-fired while open
batch_queue_id / gh_issue_numberback-references filled by the scheduler / worker

Dedup invariant: the partial-unique index dev_r_alert_events_open_fingerprint_idx allows one open (staged/batched) row per fingerprint. A re-fire merges (dedup_count++); a re-fire after the incident is resolved opens a new row (new incident).

Fingerprint parity (must stay in sync)

scripts/alert-ingest.py::fingerprint() and infra-src/meta-dispatcher/src/alert-ingest.ts::fingerprint() compute the same sha1 over the same field order. If you change one, change the other and its parity test (scripts/tests/test_alert_ingest.py, infra-src/meta-dispatcher/test/alert-ingest.test.ts pin the same known digests). A drift would let a Python-ingested and an n8n-ingested occurrence of the same alert land on two rows → two issues.

Critical-path bypass — note vs the design

The design (§3.6) worded the bypass guard as “no other open event shares the fingerprint”. The migration implements it on the correlation group (alertname, affected_service) instead, guarded by a per-group pg_advisory_xact_lock. Rationale: the #3865–#3871 burst was 7 distinct instances → 7 distinct fingerprints, all critical; a fingerprint-only guard would let each bypass individually and re-create the 7-issue problem. Keying on the group means only a truly lone critical bypasses; burst members see a sibling already open and fall through to batching. The advisory lock serialises concurrent inserts within a group so at most the true first arrival bypasses.

Rollout status (design §9)

StepState
1. Land migration (table, job-type, scheduler, bypass) — inert✅ this PR
2. Ship alert-ingest.py + /alert-ingest route + deploy alert-triage-batch skill✅ this PR
3. Cut over nightly-infra-check (lowest volume) to New-InfraEvent✅ #3919
4. Cut over Alertmanager/n8n path + grep-guard CI check✅ #3919
5. Grafana staging panel (staged/batched/bypassed, dedup_count top-N)⏳ follow-up
6. Follow-ups: resolved auto-resolution, bms-4 failover, nc-alert-batch retirement⏳ follow-up

Steps 3 and 4 shipped together in #3919. The nightly-infra-check skill now stages every finding via New-InfraEvent (POST /alert-ingest); the n8n alertmanager-to-incidents-v3 workflow’s crash-loop branch now POSTs to /alert-ingest instead of the GitHub Issues API; and .github/workflows/alert-source-cutover-guard.yml fails CI if any of the three cutover paths regresses back to direct gh issue create / api.github.com/…/issues. From here the pg_cron scheduler and alert-triage-batch worker do the actual issue creation — one correlated issue per (alertname, affected_service) batch, killing the 3865-3871 and 3854-3858 classes.

Runbook

pg_cron scheduler wedged / not grouping

Symptom: staged events older than ~10 min with batched_at IS NULL.

SELECT jobid, schedule, active FROM cron.job WHERE jobname = 'alert-batch-stage';
SELECT status, count(*), min(first_seen_at) FROM public.dev_r_alert_events GROUP BY status;
SELECT public.batch_stage_alerts();  -- run once by hand to drain the backlog

If active=false or the row is missing, re-run the migration’s cron.schedule('alert-batch-stage', …).

Ingest endpoint down → alerts silently lost

POST /alert-ingest failures raise the standard Discord + GH error path (see alert-ingest.py notify_error). Alertmanager retries its webhook. A blackbox probe on the ingest route is a planned follow-up (design §7).

alert-triage-batch row stuck queued

It is host-affined to vps-i1 (only the monitoring node reaches Prometheus). If vps-i1 has no healthy worker capacity, cancel_unroutable_nc_alert_batch() auto-cancels the row after NC_ALERT_STRAND_MINUTES (15) — identical to nc-alert-batch. Re-enable vps-i1 capacity to route.

Replay / manual triage of a batch

-- find the queue row for a group, then run the skill worker or file by hand:
SELECT id, metadata FROM public.dev_r_worker_queue
 WHERE job_type = 'alert-triage-batch' AND status = 'queued';

Duplicate-issue regression check

-- events that were batched but never got an issue number (worker failed mid-run):
SELECT id, alertname, affected_service, batch_queue_id
  FROM public.dev_r_alert_events
 WHERE status = 'batched' AND gh_issue_number IS NULL
   AND batched_at < NOW() - INTERVAL '30 minutes';

Tests

  • scripts/tests/test_alert_ingest.py — fingerprint + merge-first upsert + race retry + validation.
  • infra-src/meta-dispatcher/test/alert-ingest.test.ts — fingerprint parity, /alert-ingest route.
  • scripts/tests/test_queue_dispatcher.py — alert-triage-batch shares nc-alert-batch affinity + the generalised unroutable sweep.

Graph View