Summary

As the Connection Monitor runs continuously (default every 5s), the sample database will grow quickly. At 5s intervals per target, that is 17,280 samples/day/target or ~500k/month for two targets.

Currently, retention only supports a hard cutoff (delete samples older than N days). This issue adds tiered aggregation so older data is downsampled rather than discarded, preserving long-term trends at lower resolution.

Proposed tiers

Aggregated data per bucket

• avg_latency_ms, min_latency_ms, max_latency_ms
• p95_latency_ms (if feasible)
• packet_loss_pct (timeouts / total samples)
• sample_count (for weighted re-aggregation)

Data model

New table connection_samples_aggregated:

• target_id, bucket_start, bucket_seconds, avg_latency_ms, min_latency_ms, max_latency_ms, packet_loss_pct, sample_count

Implementation

• Run aggregation during the existing periodic cleanup cycle (every 15 min)
• Aggregate raw samples older than 7 days into 1-min buckets, then delete raw
• Aggregate 1-min buckets older than 30 days into 5-min buckets
• Aggregate 5-min buckets older than 90 days into 1-hour buckets
• Charts and API should transparently query the appropriate tier based on time range

UI impact

• Short ranges (1h, 6h, 24h) use raw samples - no change
• Longer ranges (7d) may blend raw + 1-min aggregates
• Future 30d/90d views would use aggregated data exclusively

Depends on

• Add DOCSight-native always-on latency monitor for PingPlotter-style evidence gathering #194 (Phase 1 - merged)