-- 003: denormalize stream_category onto es_events (plan §P4.5).
--
-- read_category_after — the hottest read in the system, run on every
-- ProjectionRunner poll — previously JOINed es_streams just to filter by
-- category. Carrying the category on the event row itself removes that join
-- so a category subscription is a single-table index range scan.
--
-- Append-only & idempotent (ALTER ... IF NOT EXISTS, CREATE INDEX IF NOT
-- EXISTS). The column is nullable here; migrate() backfills existing rows in
-- bounded batches under the advisory lock before the read path relies on it,
-- and the write path stamps it on every insert from this version on.
ALTER TABLE es_events ADD COLUMN IF NOT EXISTS stream_category TEXT;

-- Matches read_category_after exactly: equality on stream_category, then the
-- (transaction_id, global_position) gapless-cursor order. Replaces the
-- es_streams join + idx_es_events_txid scan with one composite index seek.
CREATE INDEX IF NOT EXISTS idx_es_events_category_cursor
    ON es_events (stream_category, transaction_id, global_position);
