p24-infra Docs

alert-staging-batch-triage

12 min read

Alert Staging Table + Batched Triage Architecture

Issue: #3895 Status: Plan — awaiting /review-plan before implementation Author: worker agent (bms4-cw-1), 2026-07-11 Labels: plan, worker-queue, monitoring

Planning document. Introduces a staging table (dev_r_alert_events) as the single ingestion point for every alert source, a 2-minute correlation scheduler, and a batched alert-triage-batch worker that files one correlated issue per incident instead of N independent issues. No code ships until plan review passes.

1. Problem statement

Every alert path currently calls gh issue create (or the GitHub API) immediately and independently, with no staging point between “alert fired” and “issue exists”. Two failure modes follow directly from that:

1.1 Evidence from the live queue (2026-07-12)

  • 7 separate EndpointDown issues (#3865–#3871) for *.w4.pinbox24.com subdomains, all created inside a 5-second window. This is one incident (the w4 edge / upstream went down, taking every probed subdomain with it) filed as 7 unrelated issues with no cross-reference and no shared root-cause hypothesis.

  • 5 orphaned duplicate issues (#3854–#3858) that never entered the Triage milestone (milestone=null), created because two independent code paths both issue gh issue create for the same condition:

    1. the Alertmanager → n8n webhook path (monitoring/alertmanager/alertmanager.yml.tpl → n8n alertmanager-to-incidents-v3), and
    2. the nightly-infra-check cron (.claude/commands/nightly-infra-check.md, New-InfraIssue → gh issue create ... --milestone "Triage").

    When both fire for the same underlying condition in the same window, they race and neither sees the other’s issue.

1.2 Root cause

There is no staging point. Each writer is stateless with respect to the others, so deduplication and correlation are structurally impossible: you cannot dedup against issues you cannot see, and you cannot correlate alerts that were never collected in one place.

1.3 What already exists (reuse, do not rebuild)

AssetRelevance
dev_r_worker_queue (migration 034_dev_r_worker_queue.sql, extended 036)Job queue. job_type CHECK constraint already gates types; we extend it.
nc-alert-batch pseudo-job-type (scripts/queue-dispatcher-loop.py:87, host-affined to vps-i1 via _HARD_PREF_JOB_TYPES)Precedent for exactly this pattern — a batch triage job hard-affined to vps-i1 with an issue_number=0 sentinel. The new alert-triage-batch type mirrors it and should share the affinity + sweep logic (cancel_unroutable_nc_alert_batch, dispatcher line ~1587).
nc-alert-orchestrator skill (.claude/commands/nc-alert-orchestrator.md)Existing investigation-only orchestrator for p24-infra-nc-alert issues. The batch worker’s output feeds this, not replaces it.
p24-infra-nc-alert label + <!-- affected-service: … --> / <!-- alert-source: … --> HTML markers (nightly-infra-check.md)The affected-service slug is already computed and embedded — reuse it as the correlation key.
meta-dispatcher enqueue API (infra-src/meta-dispatcher/src/index.ts singleDispatch, shouldSkipBatchJobDispatch in supabase.ts)Batch-aware dedup guard on dispatch already exists for nc-alert-batch; extend it to alert-triage-batch.
EndpointDown rule (monitoring/prometheus/rules/synthetic.yml, severity=critical, labels instance, alertname)The alert that produced the 7-issue burst. Its labels define the fingerprint fields.
Discord error webhook P24_DISCORD_INFRA_SCRIPTS_ERRORS_WEBHOOK_URL (pattern in queue-dispatcher-loop.py:139)Reuse for scheduler/worker error notification.

2. Target architecture

 Alertmanager ─┐
 n8n webhook ──┼──►  dev_r_alert_events  ──►  scheduler (2 min)  ──►  dev_r_worker_queue
 nightly-check ┘     (staging + dedup)         group by            (job_type=
 (all WRITE here,      by fingerprint)          alertname/service    alert-triage-batch)
  never gh issue                                 in 5-min window          │
  create directly)                                                        ▼
                                                              alert-triage-batch worker
                                                                (ONE issue per group,
                                                                 all correlated alerts
                                                                 listed + root-cause
                                                                 hypothesis)
        critical & alone ─────────────────────────────────────────────► instant dispatch
                                                                          (bypass batching)

Three moving parts: (A) the staging table + a thin write shim every source uses, (B) a correlation scheduler, (C) the batch worker. Plus the critical-path bypass.

3. Design

3.1 Schema — dev_r_alert_events

New migration monitoring/supabase/migrations/<ts>_alert_staging.sql (timestamp prefix via date +%Y%m%d_%H%M, per the current naming convention — sequential 0NN numbers are frozen at 049).

CREATE TABLE IF NOT EXISTS public.dev_r_alert_events (
  id                BIGSERIAL   PRIMARY KEY,
  fingerprint       TEXT        NOT NULL,           -- dedup key (see 3.3)
  alertname         TEXT        NOT NULL,           -- e.g. 'EndpointDown'
  affected_service  TEXT,                           -- correlation key (see 3.4)
  instance          TEXT,                           -- e.g. 'https://api.w4.pinbox24.com'
  severity          TEXT        NOT NULL DEFAULT 'warning'
                                CHECK (severity IN ('critical','warning','info')),
  source            TEXT        NOT NULL
                                CHECK (source IN ('alertmanager','n8n','nightly-infra-check')),
  status            TEXT        NOT NULL DEFAULT 'staged'
                                CHECK (status IN ('staged','batched','bypassed','resolved')),
  payload           JSONB,                          -- full alert labels + annotations
  first_seen_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_seen_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  dedup_count       INT         NOT NULL DEFAULT 1, -- times this fingerprint re-fired
  batched_at        TIMESTAMPTZ,                    -- when the scheduler grouped it
  batch_queue_id    BIGINT      REFERENCES public.dev_r_worker_queue(id),
  gh_issue_number   INT                             -- filled by the worker after issue creation
);
 
-- Dedup: one live row per fingerprint while it is still open.
CREATE UNIQUE INDEX IF NOT EXISTS dev_r_alert_events_open_fingerprint_idx
  ON public.dev_r_alert_events (fingerprint)
  WHERE status IN ('staged','batched');
 
-- Scheduler scan: unprocessed events by correlation key + time.
CREATE INDEX IF NOT EXISTS dev_r_alert_events_scan_idx
  ON public.dev_r_alert_events (status, alertname, affected_service, first_seen_at)
  WHERE status = 'staged';
 
ALTER TABLE public.dev_r_alert_events ENABLE ROW LEVEL SECURITY;
CREATE POLICY "service_role_all" ON public.dev_r_alert_events
  FOR ALL TO service_role USING (true) WITH CHECK (true);
CREATE POLICY "grafana_readonly_select" ON public.dev_r_alert_events
  FOR SELECT TO grafana_readonly USING (true);
GRANT SELECT ON public.dev_r_alert_events TO grafana_readonly;

Same migration extends the queue’s job-type gate:

ALTER TABLE public.dev_r_worker_queue DROP CONSTRAINT IF EXISTS dev_r_worker_queue_job_type_check;
ALTER TABLE public.dev_r_worker_queue ADD  CONSTRAINT dev_r_worker_queue_job_type_check
  CHECK (job_type IN ('dev-issue','infra-alert','infra-task','nc-alert-batch',
                      'subagent','review-pr','review-plan','alert-triage-batch'));

3.2 Ingestion — every source writes to the table, none calls gh issue create

An idempotent upsert helper (scripts/alert-ingest.py, plus a REST-only shell twin for n8n) is the single write path. On fingerprint conflict it increments dedup_count and bumps last_seen_at instead of inserting — this alone kills the #3854–#3858 orphan-duplicate class, because the second racing writer merges into the first row rather than creating a parallel issue.

# scripts/alert-ingest.py  (sync; error path → Discord + GH issue per §Error Notification Standard)
def ingest(alert: dict) -> None:
    fp = fingerprint(alert)                      # see 3.3
    supabase PATCH dev_r_alert_events
        ?fingerprint=eq.{fp}&status=in.(staged,batched)   # merge into open row
        { last_seen_at: now, dedup_count: dedup_count + 1, payload: merged }
    if 0 rows updated:
        supabase POST dev_r_alert_events { fingerprint: fp, ...fields, status: 'staged' }

Cutover per source:

SourceTodayAfter
Alertmanageralertmanager.yml.tpl → n8n alertmanager-to-incidents-v3 → GitHub APIn8n node calls POST /alert-ingest (or the meta-dispatcher gains an /alert-ingest route) — no GitHub node
n8n resource/worker webhooksgh issue create inside the workflowsame ingest call
nightly-infra-checkNew-InfraIssue → gh issue create --milestone TriageNew-InfraEvent → POST /alert-ingest (drop the direct create)

New-InfraIssue already computes the affected-service slug and alert-source marker; those map 1:1 onto affected_service and source columns, so the cutover is mechanical.

3.3 Deduplication — the fingerprint

fingerprint = sha1( alertname + '|' + (instance || affected_service) + '|' + severity )

Mirrors Alertmanager’s own fingerprint semantics (label-set identity), so a flapping probe that re-fires every scrape collapses onto one staged row (dedup_count climbs) rather than N rows. The partial-unique index on (fingerprint) WHERE status IN ('staged','batched') enforces this at the DB level — concurrent writers serialise on the index and the loser’s INSERT becomes a merge. Once an event’s issue is closed/resolved a fresh occurrence is a new incident (the old row is resolved, so the unique index no longer blocks it).

3.4 Scheduler — correlate every 2 minutes

Location decision (open question 3): a pg_cron RPC, not a new bms-4 cron script.

Rationale: the grouping is a single SQL GROUP BY over one table plus N queue inserts — it has no filesystem, SSH, or Docker dependency, so a bms-4 python loop would add a process to babysit for no benefit. pg_cron keeps it co-located with the data, survives a bms-4 restart, and there is precedent (049_server_heartbeat.sql uses cron.schedule for a 1-minute status refresh). The dispatcher (queue-dispatcher-loop.py) still owns dispatch; the scheduler only owns grouping.

CREATE OR REPLACE FUNCTION public.batch_stage_alerts() RETURNS void LANGUAGE plpgsql AS $$
DECLARE grp RECORD; new_qid BIGINT;
BEGIN
  FOR grp IN
    SELECT alertname, affected_service,
           array_agg(id)  AS event_ids,
           bool_or(severity = 'critical') AS has_critical,
           count(*)       AS n
    FROM public.dev_r_alert_events
    WHERE status = 'staged'
      AND first_seen_at > NOW() - INTERVAL '5 minutes'   -- correlation window (open question 1)
    GROUP BY alertname, affected_service
  LOOP
    -- Critical-path bypass (3.6): a lone critical never waits for a batch.
    IF grp.has_critical AND grp.n = 1 THEN
      -- leave it 'staged'; the bypass path (3.6) dispatches it immediately.
      CONTINUE;
    END IF;
 
    INSERT INTO public.dev_r_worker_queue
      (issue_number, repo, job_type, server_preference, status, metadata)
    VALUES
      (0, 'radieu/p24-infra', 'alert-triage-batch', 'vps-i1', 'queued',
       jsonb_build_object('event_ids', grp.event_ids,
                          'alertname', grp.alertname,
                          'affected_service', grp.affected_service))
    RETURNING id INTO new_qid;
 
    UPDATE public.dev_r_alert_events
      SET status = 'batched', batched_at = NOW(), batch_queue_id = new_qid
      WHERE id = ANY(grp.event_ids);
  END LOOP;
END; $$;
 
SELECT cron.schedule('alert-batch-stage', '*/2 * * * *', $$SELECT public.batch_stage_alerts()$$);

Grouping key is (alertname, affected_service). For the #3865–#3871 burst every probe shares alertname='EndpointDown' and (via the shared edge) affected_service='w4-pinbox24', so all 7 collapse into one alert-triage-batch row carrying 7 event_ids.

server_preference='vps-i1' is required — alert-triage-batch joins _HARD_PREF_JOB_TYPES alongside nc-alert-batch/infra-task so only vps-i1 (the monitoring node) claims it; without it a keyless host could claim and strand the row (cf. #3746). The dispatcher’s existing unroutable-sweep (cancel_unroutable_nc_alert_batch) is generalised to cover the new type.

3.5 Worker — alert-triage-batch

A new skill (.claude/commands/alert-triage-batch.md, Haiku via frontmatter — cheap, since it summarises pre-collected data rather than searching the codebase). spawn-worker.sh passes the queue row; the worker:

  1. Loads metadata.event_ids and reads those dev_r_alert_events rows (all correlated alerts).
  2. Builds one issue:
    • Title: [alert-batch] {alertname} — {affected_service} ({n} endpoints).
    • Body: root-cause hypothesis + action plan + a table of every correlated alert (instance, severity, dedup_count, first_seen_at), plus the standard <!-- affected-service: … --> / <!-- alert-source: batch --> markers so nc-alert-orchestrator still recognises it.
    • Labels: p24-infra-nc-alert, milestone Triage (unchanged downstream contract).
  3. Writes gh_issue_number back onto every event row and flips their status → resolved once the issue exists (so a re-fire opens a fresh incident).
  4. Marks the queue row done (standard Step 11.5 path).

Net result for the evidence case: 1 issue listing 7 endpoints instead of #3865–#3871.

3.6 Critical-path bypass

A lone severity=critical alert (grp.has_critical AND grp.n = 1 in 3.4) must not wait up to 2 min for the next scheduler tick. Two viable triggers — recommend (a):

  • (a) DB trigger on INSERT (recommended): an AFTER INSERT trigger on dev_r_alert_events that, when severity='critical' and no other open event shares the fingerprint, inserts the alert-triage-batch queue row immediately with metadata.bypass=true and marks the event bypassed. Latency = one dispatcher poll (~seconds), independent of the 2-min scheduler.
  • (b) let the ingest helper POST straight to meta-dispatcher for critical — simpler but splits the dispatch logic across two code paths, which is the very coupling this design removes.

Batching a burst of criticals (e.g. the 7-endpoint case, which are all severity=critical) is still correct — those are n>1, so they group and file one issue. The bypass targets the genuinely isolated critical (single service hard-down) where waiting 2 min is unacceptable.

4. Files changed

FileChange
monitoring/supabase/migrations/<ts>_alert_staging.sqlNew: dev_r_alert_events table + indexes + RLS; extend dev_r_worker_queue.job_type CHECK; batch_stage_alerts() fn + pg_cron schedule; critical bypass trigger
scripts/alert-ingest.pyNew: idempotent upsert helper (fingerprint dedup) + Discord/GH error path
infra-src/meta-dispatcher/src/index.tsAdd /alert-ingest route (or document the direct Supabase PATCH n8n uses); extend shouldSkipBatchJobDispatch to alert-triage-batch
.claude/commands/alert-triage-batch.mdNew worker skill (Haiku) — one issue per batch
scripts/queue-dispatcher-loop.pyAdd alert-triage-batch to _HARD_PREF_JOB_TYPES; generalise the unroutable-sweep to both batch types
monitoring/alertmanager/alertmanager.yml.tplPoint receivers at ingest instead of the GitHub-issue n8n branch
n8n-workflows/alertmanager-to-incidents-v3_*.jsonReplace GitHub-issue node with an ingest HTTP node
.claude/commands/nightly-infra-check.mdNew-InfraIssue → New-InfraEvent (POST to ingest; stop calling gh issue create)
monitoring/grafana/provisioning/dashboards/*.jsonNew panel: staged vs batched vs bypassed event counts, dedup_count top-N
docs/alert-staging-operations.mdNew ops doc (required for new table/service — §Compliance)
Supabase dev_r_servicesNew row for dev_r_alert_events staging service (compliance_workbook='yes')

5. Open questions — recommendations

#QuestionRecommendation
15-minute correlation windowShip at 5 min; expose as a batch_stage_alerts constant and add a Grafana panel of “events per batch” so it can be tuned against real bursts. The #3865–#3871 burst was a 5-second window, so 5 min is generous headroom without over-merging unrelated incidents.
2vps-i1 hard affinity vs bms-4 failoverStart hard-affined to vps-i1 (reuse nc-alert-batch affinity — zero new dispatch code). bms-4 failover is a follow-up: it needs the batch worker to be host-agnostic (no vps-i1-local file deps), which it already is, but adding a second affined host is a separate, testable change. Do not couple it into this migration.
3Scheduler location (pg_cron vs bms-4 cron)pg_cron RPC (see 3.4 rationale): pure SQL grouping, no host deps, survives restarts, precedent in 049_server_heartbeat.sql.

6. Out of scope

  • Migrating non-alert issue creation (dispatch callbacks, /new-issue) — this design only touches alert→issue paths.
  • Rewriting nc-alert-batch — the existing Prometheus-alert batch type stays; alert-triage-batch is the source-agnostic staging-backed successor and the two coexist until nc-alert-batch is retired in a later issue.
  • Auto-resolution of events when the underlying alert clears (Alertmanager resolved webhook → flip status='resolved') — valuable, but a follow-up once staging is proven.
  • bms-4 failover for the batch worker (open question 2 — deferred).

7. Risks

RiskMitigation
Ingest endpoint down → alerts silently lostIngest failure raises the standard Discord + GH issue error path; Alertmanager retries its webhook; add a synthetic.yml blackbox probe on the ingest route
Over-merging distinct incidents sharing alertname+serviceGrouping key includes affected_service; window is only 5 min; worker lists every instance so a human sees the spread even inside one issue
pg_cron job wedged / not scheduledGrafana alert on staged events older than 10 min with no batched_at; nc-alert if the count climbs
A genuinely-critical lone alert waits for the 2-min tickCritical-path bypass (3.6) via INSERT trigger — dispatch in seconds, not minutes
Cutover leaves a source still calling gh issue createMigration + cutover land together; a grep-guard CI check asserts no gh issue create remains in the three alert paths
Unique fingerprint index blocks a legitimate re-fire after resolutionPartial index only covers status IN ('staged','batched'); a resolved row never blocks a new incident
alert-triage-batch row unroutable if vps-i1 downGeneralised unroutable-sweep cancels stale rows (as nc-alert-batch does today); follow-up bms-4 failover removes the single point

8. Test plan

  • Migration applies cleanly (supabase db push); dev_r_alert_events + indexes + CHECK exist.
  • Ingest dedup: POST the same alert 5× → 1 row, dedup_count=5.
  • Race guard: two concurrent ingests of one fingerprint → 1 row (index serialises).
  • Correlation: stage 7 EndpointDown/w4-pinbox24 events → scheduler creates 1 alert-triage-batch queue row with 7 event_ids; all 7 flip to batched.
  • Worker: consume that row → 1 GH issue listing all 7 endpoints, p24-infra-nc-alert label, Triage milestone; events flip to resolved with gh_issue_number set.
  • Critical bypass: insert a lone severity=critical event → queue row appears within one dispatcher poll (no 2-min wait), event marked bypassed.
  • No-regression: nc-alert-orchestrator still recognises the batched issue via markers.
  • Cutover guard: grep asserts gh issue create removed from Alertmanager/n8n/nightly paths.
  • Re-fire after resolution: resolve an event, re-ingest same fingerprint → new staged row.

9. Rollout

  1. Land the migration (table + job-type + scheduler + bypass) — inert until sources write to it.
  2. Ship alert-ingest.py + meta-dispatcher route; deploy the alert-triage-batch skill.
  3. Cut over one source first — nightly-infra-check (lowest volume, easiest to observe) — and confirm one-issue-per-batch on the next nightly run.
  4. Cut over the Alertmanager/n8n path; run the grep-guard CI check.
  5. Watch the Grafana staging panel for a week; tune the 5-min window if bursts under-/over-merge.
  6. File the follow-ups: Alertmanager resolved auto-resolution, bms-4 failover, nc-alert-batch retirement.

Auto-generated by worker agent (bms4-cw-1) for issue #3895. Review with /review-plan 3895.

Graph View

Backlinks