feat: composite (transmission_id, timestamp) index on observations for tx_last_seen MAX lookup

[Kpa-clawbot]

Context

Raised by Carmack in PR #1735 (round-1 review) as out-of-scope for that PR's chunked tx_last_seen backfill fix. Filing separately to track.

Proposal

Add a composite index (transmission_id, timestamp) on the observations table to make the per-transmission MAX(timestamp) lookup that powers the tx_last_seen backfill (and inline stmtBumpTxLastSeen) a single index probe instead of a correlated scan on transmission_id-only.

Why

• The chunked backfill in cmd/ingestor/tx_last_seen_backfill.go issues SELECT MAX(timestamp) FROM observations WHERE transmission_id = ? per row in each UPDATE batch. With only idx_observations_transmission_id, SQLite scans every observation for the tx to find MAX.
• Inline write-path equivalent stmtBumpTxLastSeen runs the same shape on every observation insert.
• A composite (transmission_id, timestamp) index gives an index-only MAX via reverse scan with LIMIT 1.

Risk

• Disk: one more index on a hot table. Operator-scale DB has ~1.5M observations; index entry overhead ~32 bytes → ~50MB.
• Build cost: must run via RunAsyncMigration. Estimated 30–120s on prod.

[Kpa-clawbot]

Severity: P3 — perf optimization on backfill MAX scan; cold path once backfill finishes.

Claim: confirmed. cmd/ingestor/db.go:737 stmtBumpTxLastSeen rewrites on every observation insert; the proposed backfill (PR #1735, still OPEN) does per-row MAX(timestamp) WHERE transmission_id=?. Today only idx_observations_transmission_id exists — MAX requires a scan of all observations for that tx.

Findings:

• (carmack): Composite (transmission_id, timestamp DESC) makes the MAX an index-only descent. At ~1.5M obs / 71K tx the per-row cost drops from O(obs_per_tx) to O(1). Backfill total cost drop is the win; steady-state stmtBumpTxLastSeen already does point UPDATE by id so it benefits the per-insert MAX path, not the UPDATE itself.
• (munger): ~50MB index on a hot table is real disk + cache pressure. Pair with perf: replace idx_tx_last_seen with partial index WHERE last_seen=0 #1740 (partial index drop) to net out.

Fix path: add migration in dbschema/ creating idx_observations_tx_ts ON observations(transmission_id, timestamp), scheduled via RunAsyncMigration after #1735 lands. Effort: M.

Dup check: searched "composite observations transmission_id timestamp index" — none open.

Blocker: depends on PR #1735 merging first (introduces the backfill surface).

Triage verdict: ready-to-fix