[Monitoring] Retention policy

[2witstudios]

Summary

system_logs and api_metrics lack per-row expiresAt fields and configurable retention policies, unlike ai_usage_logs which has both.

Current State

Cleanup exists but is coarse-grained:

• monitoring-retention.ts provides cleanupApiMetrics() and cleanupSystemLogs() with hardcoded timestamp-based bulk deletion
• runMonitoringRetentionCleanup() is called by the retention cron via retention-engine.ts
• ai_usage_logs has a proper expiresAt column (line 187 of monitoring.ts) with per-row expiration and a dedicated index

What's missing:

• system_logs has no expiresAt field (lines 28-70 of packages/db/src/schema/monitoring.ts)
• api_metrics has no expiresAt field (lines 75-108)
• No configurable retention durations per data class (security vs ops vs telemetry)
• No documented retention policy — durations are hardcoded in cleanup functions
• No reporting/metrics for cleanup execution and deleted row counts

Enterprise Impact

• Retention governance for core log datasets is implicit, not explicit
• No way to configure different retention periods for different data classes
• Compliance reviews require documented, configurable retention policies

Proposed Work

1. Define retention policy by data class (security/audit/ops) with configurable durations
2. Add expiresAt to system_logs and api_metrics for per-row expiration (matching ai_usage_logs pattern)
3. Make retention durations configurable (env vars or admin settings) instead of hardcoded
4. Add reporting/metrics for retention cleanup execution
5. Document retention policy for compliance reviews

Acceptance Criteria

• system_logs and api_metrics have expiresAt fields with indexes
• Retention durations are configurable per data class
• Retention policy is documented and linked from compliance docs
• Cleanup metrics are observable (row counts, execution time)