incidents-operations

dev_r_incidents — Operations Guide

p24-infra DevOps tooling table. Tracks infrastructure alerts from first fire through acknowledgement to resolution. Not the fleet/WhatsApp incidents table — that is a separate domain.

---

1. What This Table Tracks

dev_r_incidents is the source of truth for infra-level incidents: monitoring alert fires, audit-engine failures, exporter outages, credential expiry warnings, etc.

It sits alongside dev_r_services and dev_r_projects as a public-access DevOps tooling table (anon read/write, RLS enabled with public ALL policy).

---

2. Schema Reference

Column	Type	Notes
id	UUID	PK, auto-generated
service_name	TEXT	e.g. 'prometheus', 'audit-engine'
title	TEXT	Short alert description
severity	TEXT	'P1' / 'P2' / 'P3'
status	TEXT	Legacy field — use resolution_status for lifecycle
source	TEXT	'alertmanager' / 'audit-engine' / 'manual'
opened_at	TIMESTAMPTZ	When alert first fired
resolved_at	TIMESTAMPTZ	When confirmed resolved (drives MTTR)
linked_issue_url	TEXT	GitHub issue URL if one was created
notes	TEXT	Free-form notes
raw_payload	JSONB	Original alert payload
created_at	TIMESTAMPTZ	Row creation time
updated_at	TIMESTAMPTZ	Last modification time
mttr_minutes	NUMERIC	Computed MTTR in minutes (resolved_at - opened_at)
resolution_status	TEXT	Lifecycle state — see §3
acknowledged_at	TIMESTAMPTZ	When first acknowledged
acknowledged_by	TEXT	'IO1' / 'HS1' / 'BMS4-1' / 'human' / 'auto'
reaction	TEXT	What action was taken (e.g. “restarted prometheus container”)
reaction_at	TIMESTAMPTZ	When the fix was applied
resolved_by	TEXT	Same values as acknowledged_by
resolution_notes	TEXT	Post-mortem notes, root cause, prevention steps
false_positive	BOOLEAN	True if alert was a monitoring/config error, not a real outage
source_run_id	UUID	FK to audit.runs.id — links incident to the run that created it

---

3. Resolution Lifecycle

Alert fires
    │
    ▼
INSERT (resolution_status = 'open')
    │
    ▼
Agent or human acknowledges
    → UPDATE SET resolution_status = 'acknowledged',
                 acknowledged_at = NOW(),
                 acknowledged_by = '<who>'
    │
    ▼
Fix applied
    → UPDATE SET reaction = '<what was done>',
                 reaction_at = NOW()
    │
    ▼
Confirmed resolved
    → UPDATE SET resolution_status = 'resolved',
                 resolved_at = NOW(),
                 resolved_by = '<who>',
                 resolution_notes = '<notes>'
         OR
    → UPDATE SET resolution_status = 'false_positive',
                 false_positive = TRUE,
                 resolution_notes = '<why it was a false positive>'

Valid resolution_status values (enforced by CHECK constraint):

• open — default, alert is active and unaddressed
• acknowledged — someone is aware and working on it
• resolved — confirmed fixed
• false_positive — alert fired incorrectly; no real outage

---

4. Who Writes Resolution Updates

Actor	When	How
audit-engine ping_check	Auto-resolves when a monitored URL recovers after being open	resolve_incident(source_run_id) via Supabase anon key
AI-Dev worker	After fixing an infra issue and closing the GitHub issue	Direct UPDATE via Supabase anon key
hourly-devops-triage skill	Sweeps open incidents > 1 h old, sets acknowledged	UPDATE via Supabase anon key
Human	Via Grafana panel action or direct SQL	Manual

---

5. Key Queries

Open incidents (dashboard query)

SELECT id, service_name, title, severity, source, opened_at, created_at
FROM dev_r_incidents
WHERE resolution_status = 'open'
ORDER BY created_at DESC;

MTTR last 30 days (excluding false positives)

SELECT
  AVG(EXTRACT(EPOCH FROM (resolved_at - opened_at)) / 60.0) AS avg_mttr_minutes,
  COUNT(*) AS resolved_count
FROM dev_r_incidents
WHERE resolution_status = 'resolved'
  AND false_positive = FALSE
  AND resolved_at >= NOW() - INTERVAL '30 days';

False positive rate last 30 days

SELECT
  COUNT(*) FILTER (WHERE false_positive = TRUE) AS fp_count,
  COUNT(*) AS total_count,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE false_positive = TRUE) / NULLIF(COUNT(*), 0),
    1
  ) AS fp_rate_pct
FROM dev_r_incidents
WHERE created_at >= NOW() - INTERVAL '30 days';

Incidents by day (resolution timeline)

SELECT
  DATE_TRUNC('day', created_at) AS day,
  COUNT(*) FILTER (WHERE resolution_status = 'open') AS open_count,
  COUNT(*) FILTER (WHERE resolution_status = 'resolved') AS resolved_count
FROM dev_r_incidents
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

---

6. audit-engine Integration

ping_check.py creates an incident row when a URL fails and auto-resolves it on recovery. The source_run_id column links each incident to the audit.runs row that triggered it.

Helper functions in audit-engine/db.py:

• create_incident(action_id, service_name, title, severity, source_run_id) — INSERT with resolution_status='open'
• resolve_incident(source_run_id) — UPDATE to resolution_status='resolved' for the matching open incident

---

7. Grafana Notes

grafana_readonly has SELECT access on this table (granted in migration 20260618170105).

Planned dashboard panels (issue #731):

• Open incidents table — WHERE resolution_status = 'open' ORDER BY created_at DESC
• MTTR gauge — AVG(resolved_at - opened_at) last 30 days, excluding false positives
• Resolution timeline — bar chart: open vs resolved per day
• False positive rate — percentage last 30 days

---

8. RLS Policy

RLS is enabled. Public anon access (read/write) — same policy as dev_r_services.

To verify:

SELECT relrowsecurity FROM pg_class WHERE relname = 'dev_r_incidents';
SELECT policyname, cmd FROM pg_policies WHERE tablename = 'dev_r_incidents';

---

9. Compliance

• dev_r_services row: service_name = 'dev_r_incidents', compliance_workbook = 'yes'
• Resolution workflow added in issue #726
• Grafana dashboard planned in issue #731
• This table is not subject to EU AI Act (no AI processing; it is a data store only)