Retention gap: trace_events / trace_llm_calls / manager_telemetry / collection_errors / connectivity_events have NO retention; substrate cutover left new tables uncovered

[emooreatx]

Symptom

The lens DB grows unbounded for the post-cutover substrate + several telemetry tables. From bridge-us prod (2026-06-12, lens ddb436e, persist 5.5.3):

Table	Hypertable	Size	Rows/day (30d avg)	Retention	Notes
cirislens.trace_llm_calls	yes (26 chunks)	851 MB	1 808	NONE	post-cutover substrate
cirislens.trace_events	yes (32 chunks)	419 MB	1 087	NONE	post-cutover substrate
cirislens.manager_telemetry	no	757 MB	7 159	NONE	regular table — needs hypertable conversion or scheduled DELETE
cirislens.collection_errors	no	147 MB	38	NONE	regular table
cirislens.connectivity_events	no	5.4 MB	22	NONE	regular table
cirislens.status_checks	yes	441 MB	58 587	90 days ✓	sensible existing policy
cirislens.service_logs	yes	3.3 MB	low	14 days ✓
cirislens.accord_traces	yes	206 MB	0 (pre-cutover stopped 2026-05-02)	90 days ✓	legacy
cirislens.agent_metrics	yes	56 KB	0	30 days ✓	empty
cirislens.covenant_traces_hourly (rollup)	yes	—	—	365 days ✓
cirislens.status_daily (rollup)	yes	—	—	365 days ✓

Total DB: 2.9 GB today. At current growth (~9.7K rows/day across uncovered tables), with no retention, by mid-2027 the substrate alone adds ~3.5–4 GB just from trace_events + trace_llm_calls text payloads, plus ~2.6 GB more on manager_telemetry.

The substrate cutover left a gap

When persist migrated 0.x → 3.x (cutover 2026-05-02 01:33 UTC), the pre-cutover accord_traces (which had 90-day retention) was effectively frozen and the new substrate tables trace_events + trace_llm_calls were created without inheriting the retention policy. They've been growing unbounded for 41 days. This is an oversight, not a design intent — the policy infrastructure clearly exists (status_checks, accord_traces, service_logs all have policies).

The telemetry tables aren't hypertables

manager_telemetry, collection_errors, connectivity_events are regular Postgres tables. TimescaleDB policy_retention only operates on hypertables, so either:

1. Convert via create_hypertable(..., migrate_data => true) and apply add_retention_policy, or
2. Schedule a pg_cron / lens-internal DELETE FROM ... WHERE ts < now() - interval 'N days' job.

manager_telemetry at 757 MB and 7 159 rows/day is the most urgent — its growth rate is comparable to status_checks but with no cap.

The user's framing — backpressure, not just static drop_after

"lens/lenscore needs to handle the lifecycle based on backpressure"

A static drop_after policy fixes the bleed, but the real ask is adaptive retention: lens watches its own storage pressure and shortens windows when pressure rises. That's a design lift, and it's the right design — bridge-us is already at 77% disk this afternoon (up from 67% this morning, mostly from accumulated lens image layers, but the substrate growth is a separate trajectory toward the same wall).

A reasonable lens/lens-core design:

1. Tier the tables: substrate (trace_events, trace_llm_calls) → telemetry (manager_telemetry, connectivity_events) → errors (collection_errors) → rollups (already exist for accord, need equivalent for trace_events).
2. Each tier has a baseline retention (e.g. substrate 90d, telemetry 30d, errors 30d, rollups 365d) — matching the existing accord_traces / status_checks shape.
3. Backpressure monitor in lens-core: watch pg_database_size('cirislens') + per-hypertable hypertable_size, plus pg_total_relation_size for non-hypertables. When DB size exceeds a soft cap (e.g. 5 GB), shorten retention on the tier-1 / tier-2 tables progressively. When it exceeds a hard cap (e.g. 8 GB), aggressively drop and alert.
4. Aggregation rollups for trace_events: today covenant_traces_hourly rolls up accord_traces to a 1-year-retained summary. Build the equivalent for trace_events → hourly score/coverage means per agent_id_hash, so raw trace data can drop at 90d without losing analytical history.
5. Convert the three non-hypertables to hypertables (or accept the DELETE-job route, but hypertables compose better with the rest of this design).

Immediate-fix patch the bridge could apply itself (will NOT)

SELECT add_retention_policy('cirislens.trace_events',    INTERVAL '90 days');
SELECT add_retention_policy('cirislens.trace_llm_calls', INTERVAL '30 days');  -- tighter, it's the heavy one

Bridge is not applying this, per the cardinal rule ("the bridge does not hack" — no manual SQL fixes to mask substrate-side design gaps). Lens team owns the retention design and the backpressure logic; once a lens release lands with both, bridge will redeploy and verify.

Production state

• Image: ghcr.io/cirisai/cirislens:ddb436e (persist 5.5.3, lens-core 1.0 floor)
• Bridge-us disk: 77% — 17.8 GB reclaimable in old lens images, will be cleaned via the bridge's normal disk-cleanup runbook. The DB itself at 2.9 GB is not the proximate disk crunch, but the trajectory matters.
• Snapshot of the live substrate captured today (53M rows, 902 MB gz) at ~/0612_prod_traces/ for RATCHET calibration — included here for table-size reference.

cc @emooreatx

[emooreatx]

Substrate side shipped — adoption path + the one remaining gate

CIRISPersist v5.9.0 (and v6.0.1 on the pyo3-0.29 line) shipped the pressure-gated retention primitive this issue's persist-owned tables need (CIRISPersist#209):

• set_retention(table, policy) / get / list / run_retention(now) on both backends, durable in a retention_policies table.
• RetentionPolicy { min_keep_secs (sacred floor), time_column, pressure_trigger_bytes?, pressure_target_bytes?, interval_secs }.
• Pressure-gated sweep: below trigger = no-op (no churn); at/above = DELETE rows past min_keep; reports exhausted if still over target. Injection-safe (validated identifiers — table names reach un-bindable SQL).

Split of responsibilities (mirrors the lens-side sweeper you already shipped)

• persist-owned tables (cirislens.trace_events, cirislens.trace_llm_calls) → the substrate primitive. Lens calls engine.set_retention(...) at startup; persist's sweep runs them. Retire api/retention.py's coverage of these two once adopted.
• lens-owned tables (manager_telemetry, collection_errors, connectivity_events) → stay on the lens api/retention.py sweeper. Not persist-owned; same pressure-gated discipline, different layer.

The gate before lens can adopt

Persist shipped the primitive on the Rust MaintenanceService trait, but the PyO3 Engine.set_retention method lens calls from Python isn't exposed yet — tracked at CIRISPersist#218 (mechanical FFI wrapper; substrate logic is done). Once #218 ships, the lens consumer is the ~6 lines from #209's cookbook:

# api/persist_engine.py — after engine init
await engine.set_retention("cirislens.trace_events", RetentionPolicy(
    min_keep=timedelta(days=7),
    pressure_trigger_bytes=int(10*GB*0.9), pressure_target_bytes=int(10*GB*0.8),
    interval=timedelta(hours=4)))
# + the same for trace_llm_calls

Note (honest): under the v1 DELETE strategy, a row DELETE doesn't reclaim Postgres heap until VACUUM, so exhausted is best-effort and precise until-target reclaim awaits the deferred drop_chunks/partition path. The v1 still does the load-bearing job — bounds growth, protects recent data, only churns under pressure.

cc CIRISPersist#218 (the gate) · CIRISLensCore#51 (the cross-table tier that composes this)

[emooreatx]

Upstream unblocked — ciris-persist==6.1.0 is live on PyPI with the retention FFI (CIRISPersist#218).

The lens can retire its stopgap DELETE sweeper and drive substrate-owned retention through Engine:

• set_retention(table_name, min_keep_secs, time_column, pressure_trigger_bytes=None, pressure_target_bytes=None, interval_secs=86_400)
• get_retention(table_name) -> Optional[str] (JSON RetentionPolicy)
• list_retention() -> str (JSON array of RetentionPolicyRow)
• run_retention(now=None) -> str (JSON array of RetentionReport; now optional RFC-3339)

Shapes cross as JSON strings — decode with model_validate_json (same as VacuumReport/ArchiveReport). min_keep_secs is the sacred floor; pressure gating via pg_database_size (PG) / page_count×page_size (SQLite). Pin ciris-persist>=6.1.0,<7.