events partitions end at 2026-06 + catch-all; nothing rolls them forward (recurs 2027-01-01)

[tlongwell-block]

Problem

migrations/0001_initial_schema.sql creates monthly events partitions only through events_p2026_06, plus a events_p_future catch-all FROM ('2026-07-01') TO (MAXVALUE). Nothing in the schema (checked all migrations on main @ f8fd055) ever creates new monthly partitions.

Consequence, observed live on sprout staging (2026-07-22): once July started, every write landed in the catch-all. By July 22 it held 368k rows / 1.1GB — 43% of all events — and query plans that should prune to one month were seq-scanning the whole catch-all (12M+ tuple seq scans in pg_stat_user_tables).

What was done as a one-off

Manual surgery on sprout staging (approved by @tlongwell): validated a July CHECK on the catch-all NOT VALID→VALIDATE, then in one transaction detached it, re-attached it as events_p2026_07 (instant — pre-validated CHECK skips the scan), and created events_p2026_08…_12 + a fresh empty catch-all from 2027-01-01. Zero rows moved; write-block window ~62ms.

What's needed

That fix only buys time until 2027-01-01, when the catch-all starts absorbing again — on every deployment, not just staging. The schema needs a durable partition roller, e.g. one of:

1. A scheduled job in the relay (it already runs cron-like tasks) that creates partition N+1..N+3 ahead of time — CREATE TABLE IF NOT EXISTS ... PARTITION OF events FOR VALUES ... is idempotent and cheap;
2. pg_partman where the extension is available (not guaranteed on all target Postgres images);
3. At minimum, a migration extending partitions a few years out (kicks the can, but predictably).

Option 1 fits the codebase best — no new extension dependency, and the relay already owns schema lifecycle via sqlx migrations + startup checks.

Note for anyone doing the same manual surgery on another deployment: verify the catch-all's min/max created_at first — the detach/re-attach shortcut only works while all rows fit one month's bounds.