Observation Analytics: Add Topic Activity Summaries

[Dumdart]

Priority

P3 — useful after real history has proven demand.

Depends on

• #42 Observation History: Add Append-Only Event Store
• #43 Observation History: Record Accepted Events Reliably
• #44 Observation History: Add Independent Retention
• #45 MCP: Add Cursor-Paginated Topic History

Problem

Users need operational answers such as “which topics stopped updating?” and “is this silence unusual?” Raw event history is too expensive and verbose for these questions.

Scope

Add application query/result models and SQL-backed aggregation for:

• Observation count.
• First and last observation timestamps.
• Minimum, average, and maximum inter-message interval.
• Payload-size minimum, average, maximum, and total.
• Longest inactivity period.
• Current age compared with observed normal cadence.
• Explicit retained history horizon and sample coverage.

Expose bounded summaries through the application layer first, then MCP if the result shape proves stable.

Acceptance criteria

• Aggregates operate only on append-only history, never the latest-state table.
• Broker, MQTT filter, and time-window scope are mandatory and bounded.
• Sparse, single-sample, truncated, and retention-limited datasets are represented explicitly.
• Cadence calculations define percentile/outlier behavior or avoid implying certainty.
• SQL performs aggregation without loading the full history window into Python.
• Query plans use the history indexes and are tested on a representative dataset.
• Results include sample count and coverage timestamps.

Out of scope

• Arbitrary chart dashboards.
• Numeric payload values.
• Forecasting or anomaly-detection models.
• Cross-broker aggregation.