feat: implement execution log retention and pruning policy

[vybe]

Summary

The schedule_executions table stores a full JSONL transcript in the execution_log column for every agent run, with no TTL or pruning. On active instances with many scheduled agents this table balloons to 3+ GB, dominating the SQLite database and causing disk pressure. There is also no retention policy on agent_health_checks, which accumulates hundreds of thousands of rows over time.

Component

Backend / Database / Cleanup Service

Priority

P1 — active instances hit disk pressure within weeks; no workaround short of manual SQL.

Problem Detail

On a production instance with ~10 scheduled agents running hourly:

Table	Size	Rows	Growth rate
schedule_executions	~3.3 GB	~9,000	~150–200 MB/week
agent_health_checks	~200 MB (incl. indexes)	~750,000	continuous

The execution_log column is the primary driver — it stores the raw JSONL Claude Code session transcript, averaging 150–190 KB per run. This is valuable for recent executions (debugging, audit trail) but has diminishing value for runs older than a few weeks.

Proposed Solution

1. Execution log retention policy

Add a configurable retention window (e.g. EXECUTION_LOG_RETENTION_DAYS, default 30). The existing cleanup service (services/cleanup_service.py) should periodically:

# Null out execution_log for old completed executions (preserves row + metadata)
UPDATE schedule_executions
SET execution_log = NULL
WHERE status IN ('completed', 'failed', 'terminated')
  AND completed_at < datetime('now', '-30 days')
  AND execution_log IS NOT NULL;

Nulling rather than deleting preserves the execution record (agent, duration, cost, status) while reclaiming the bulk of the space.

2. Full execution row pruning (optional, deeper retention)

For very old executions (e.g. >90 days), optionally delete the row entirely:

DELETE FROM schedule_executions
WHERE completed_at < datetime('now', '-90 days')
  AND status IN ('completed', 'failed', 'terminated');

3. Health check retention

Cap agent_health_checks to a rolling window (e.g. 7 days):

DELETE FROM agent_health_checks
WHERE checked_at < datetime('now', '-7 days');

4. Admin configuration

Expose retention settings via the ops config API (GET/PUT /api/settings/ops/config) so operators can tune or disable pruning per instance:

{
  "execution_log_retention_days": 30,
  "execution_row_retention_days": 90,
  "health_check_retention_days": 7
}

5. Immediate reclaim (VACUUM)

After bulk deletes, run PRAGMA wal_checkpoint(TRUNCATE) and optionally VACUUM to return pages to the OS. This should be gated (e.g. only if >X MB reclaimed) as VACUUM locks the DB.

Acceptance Criteria

• Cleanup service nulls execution_log for executions older than retention window
• Cleanup service deletes agent_health_checks rows older than retention window
• Retention windows configurable via ops/config API (with sensible defaults)
• Cleanup service logs rows affected per cycle
• VACUUM or checkpoint triggered after significant cleanup
• Existing cleanup service schedule (5-min interval) reused — no new background thread needed

Related

• src/backend/services/cleanup_service.py — existing cleanup service to extend
• src/backend/db/schema.py — schedule_executions, agent_health_checks table definitions
• src/backend/routers/monitoring.py — GET /api/settings/ops/config endpoint
• src/backend/db/migrations.py — may need index on completed_at for efficient pruning