feat: database metrics dashboard — surface insights from stored investigation data

[AhmadHammad21]

Overview

We store a rich dataset across several tables (sessions, messages, tool_calls, usage_events, alerts, alert_notifications) but expose very little of it as actionable metrics. This issue tracks building a proper metrics layer on top of that data.

Current state

Table	What we store	Metrics exposed today
sessions	Every investigation thread	Count in dashboard (basic)
messages	Every user + assistant turn	None
tool_calls	Every tool invoked per turn	None
usage_events	Token usage + latency per turn	Cost estimate in dashboard
alerts	Poller/event-consumer findings	Alert list in Monitoring page
alert_notifications	Per-channel delivery status	Per-alert detail only

Proposed metrics to compute

Investigation health

• Total investigations (all time, last 7d, last 30d)
• Success rate: completed vs failed alerts
• Average confidence distribution (HIGH / MEDIUM / LOW breakdown)
• Mean investigation duration (first message → submit_investigation tool call)

Tool usage

• Top 10 most-called tools across all sessions
• Tool error rate per tool (result contains "error" key)
• Average tool calls per investigation
• Most common tool call sequences

Cost & performance

• Total token spend (input + output) per day/week
• Cost per investigation (needs per-alert breakdown)
• P50 / P95 latency per tool

Alert & notification analytics

• Alert volume over time (by trigger source: poller vs event_consumer)
• Slack delivery success rate (delivered / total)
• Most affected services (top N by alert count)
• Mean time between alert and investigation completion

Session patterns

• Active sessions per day
• Investigations started from Monitoring page vs fresh chats

Suggested implementation

1. New API endpoint GET /api/metrics?days=30 — returns all computed metrics as JSON; queries run directly against Postgres (no new tables needed for MVP)
2. New frontend page /metrics (or extend /dashboard) — displays the above as cards + simple charts
3. Optional: DB view CREATE VIEW investigation_metrics AS ... — makes complex aggregations reusable and testable independently of the API layer

Out of scope (for this issue)

• Real-time streaming metrics
• External metrics export (Prometheus / OpenTelemetry) — tracked separately in the roadmap
• Per-user breakdown (needs org/user attribution wired up first)

Acceptance criteria

• GET /api/metrics returns structured JSON covering the categories above
• Frontend page renders the data without layout regressions on existing pages
• Queries do not add noticeable latency to other endpoints (cached or background)
• Works with the memory backend (returns zeroes gracefully, no crash)