session_events grows without bound: no partitioning, no retention

[Yuyz0112]

Problem

session_events (control-plane/migrations/001_init.sql:78) stores one row per agent
event per message, with the full event body in a jsonb payload. It is append-only
and there is no retention, archival or partitioning anywhere in the codebase — the
table grows for the lifetime of the deployment.

On a long-running deployment it is by a wide margin the largest table in the schema,
several times the size of messages and larger than everything else combined.

Why it matters

Its size is not a storage problem so much as a compounding cost multiplier. Several
hot paths scale directly with it:

• The admin stats materialized views aggregate the whole table on every refresh —
  three times per cycle (Admin stats materialized views are full recomputes and evict the OLTP working set #189).
• Logical backups spend most of their runtime copying it, and the read evicts the
  OLTP working set from shared_buffers.
• The hot lookup WHERE message_id = ANY($1) plans as a parallel bitmap heap scan.
  With the table this large it reads a substantial number of disk blocks per call,
  and its worst-case latency is measured in tens of seconds.
• Autovacuum visits it rarely relative to its churn, so dead tuples accumulate between
  passes.

Each of these gets worse as the deployment ages, and no amount of tuning bounds them.

Proposed direction

1. Partition by created_at (monthly or weekly RANGE partitions). Detaching an
   old partition becomes an O(1) metadata operation instead of a mass DELETE that
   generates WAL and leaves the table bloated. This is the enabling change for
   everything below.
2. Retention policy with a configurable window. Events older than N days are
   dropped, or moved to an archive partition/tablespace. Needs a product decision on
   what the platform guarantees about historical session replay — this is core data,
   so the default should be conservative and the window explicit rather than implied.
3. Revisit the payload. One row per event with a full jsonb body is the reason
   the row count and the byte count both grow fast. Worth checking whether some event
   kinds can be collapsed or stored more compactly before they are written.
4. Autovacuum settings for this table specifically. Whatever the retention story,
   a table this much larger than the rest of the schema should not be sharing the
   cluster-wide autovacuum thresholds.

Steps 1 and 4 are safe to do independently of the retention decision in step 2 and
deliver most of the operational benefit on their own.