Denormalize kind onto event_tags so tag-filter CTE can pre-filter by kind

[MastaP]

Symptom

Production groupchat relay (`sphere-zooid-relay-eu`, RDS `db.m6g.large`) is at 99.7% CPU with DBLoad ~19 active sessions on 2 vCPU. Started after ~5k new users joined (45k → 50k total). Storage IOPS / read latency / memory are all fine — pure CPU saturation.

PI top SQL: two near-identical NIP-29 group-history queries account for ~65% of all DB load:

```sql
WITH _tag_ids AS MATERIALIZED (
SELECT event_id FROM __event_tags WHERE key=$1 AND value IN (...)
)
SELECT e.* FROM __events e JOIN _tag_ids t ON t.event_id = e.id
WHERE e.kind IN ($,$,$) [AND e.created_at >= $]
ORDER BY e.created_at DESC LIMIT 1000
```

Root cause

The materialized CTE filters by tag only — it doesn't see the kind filter. So for a query like `{kinds: [9,11,12], #h: ['general']}`:

Step	Rows
CTE: tag rows for `h='general'`	97,879
Hash-join with events	97,879
Filter `kind IN (9,11,12)`	1,424
Sort + LIMIT	1,000

95,000 of 97,000 CTE rows are wasted because they're membership events (kinds 9000/9021), not chat messages.

Event kind distribution in production:

kind	count	what
9021	90,437	NIP-29 join request
9000	90,024	NIP-29 add member
9	11,365	chat message
12	266	chat reply
other	~3k	group state, etc.

62% of all events are membership events, each tagged with the target group's `h` value. Hot groups (`general`, `announcements`) accumulate ~95k membership-event tag rows that every chat-history query has to wade through.

EXPLAIN ANALYZE confirms: each query is ~1.1–1.3s, hash-join is the cost center, autoanalyze stats are fresh.

Proposed fix

Add `kind` to `event_tags` so the CTE can pre-filter by kind:

```sql
ALTER TABLE __event_tags ADD COLUMN kind INTEGER;
UPDATE __event_tags et SET kind = e.kind FROM __events e WHERE et.event_id = e.id;
ALTER TABLE __event_tags ALTER COLUMN kind SET NOT NULL;
CREATE INDEX CONCURRENTLY __idx_event_tags_key_value_kind_event_id
ON __event_tags(key, value, kind, event_id);
```

Code changes:

• `saveEventWith`: include `kind` in `event_tags` insert
• `buildSelectQuery`: when both tag filters AND `filter.Kinds` are set, push `kind IN (...)` into the materialized CTE

Result for the same query against `general`:

Step	Before	After
CTE rows	97,879	~1,424
Hash-join input	97,879	~1,424
Per-query cost	~1.1s	~10–50ms (estimate)

Migration order (zero-downtime)

1. PR 1 — write path: add nullable column, code writes kind on insert (old code keeps reading; new column ignored on read).
2. One-shot backfill: `UPDATE event_tags SET kind = events.kind FROM events WHERE event_id = events.id` for each schema. Run from a dbops task.
3. PR 2 — read path: alter column NOT NULL, create new composite index CONCURRENTLY, deploy code that pushes `kind` into the CTE. Old index can stay around as a safety net.
4. Drop old index in a follow-up after the new one is proven.

Out of scope here

• Scaling RDS — being avoided in favor of the structural fix.
• Reducing membership-event volume — that's a separate question (do clients need full history of joins, or only current state?).

Refs

• `zooid/events.go:283` — `buildSelectQuery` (where the CTE is built)
• `zooid/events.go:466` (approx) — `saveEventWith` (where event_tags rows are inserted)
• PR metrics: split DB time from drain time, widen buckets to 30s #22 — query duration metric split that made this measurable