redis-memory-schema.sql

-- redis-memory-schema.sql
-- HORIZON Phase 5.5 — Redis hot-memory durable system of record.
--
-- One row per HORIZON conversation session. While the session is live, the
-- last N turns + running summary live in Redis on LA (<BHN_WG_LA_IP>:6379, VPN-only)
-- keyed by `id`. When the session closes (TTL expiry or explicit end), the
-- session-close sweep workflow:
--   1. Summarizes the conversation via Sonnet
--   2. Embeds the summary via /opt/eh-embed (bge-small-en-v1.5, 384 dim)
--   3. INSERTs into `memories` with memory_type='conversation'
--   4. UPSERTs the matching row here with closed_at + summary + embedding
--   5. DELs the Redis keys
--
-- `summary_embedding` lets pgvector search across past sessions by topic —
-- distinct from per-turn semantic recall (which goes into the existing
-- memories table). This table is the durable index of "what sessions happened,
-- when, on what channel, and what were they about".
--
-- Apply on LA hub:
--   sudo -u postgres psql -d eventhorizon -f sql/redis-memory-schema.sql
--
-- See infrastructure/docs/horizon-roadmap.md § "Hot-memory layer (Redis)"
-- for the design context.

-- pgvector should already be installed (memories-schema.sql created it).
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS conversation_sessions (
    id                  UUID PRIMARY KEY,                       -- generated by n8n at session start, also used as Redis key suffix
    channel             TEXT NOT NULL CHECK (channel IN (
                          'chat',           -- n8n chat trigger (web)
                          'sms',            -- Twilio inbound/outbound SMS
                          'voice',          -- Twilio voice call (M1/M7)
                          'morning_brief',  -- M2 outbound briefing
                          'evening_brief',  -- M3 outbound briefing
                          'intraday_alert', -- M4 alert-driven exchange
                          'system'          -- internal/automated, no operator turn
                        )),
    started_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    closed_at           TIMESTAMPTZ,                            -- NULL while session is live in Redis
    last_seen_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),     -- mirror of Redis meta.last_seen; updated on each turn
    turn_count          INT NOT NULL DEFAULT 0,
    summary             TEXT,                                   -- filled at close
    summary_embedding   vector(384),                            -- filled at close; cross-session topic search
    metadata            JSONB NOT NULL DEFAULT '{}'::jsonb,     -- channel-specific extras (twilio call_sid, etc.)
    related_memory_id   BIGINT REFERENCES memories(id) ON DELETE SET NULL  -- points at the memories row created from this session's summary
);

CREATE INDEX IF NOT EXISTS conversation_sessions_started_idx
    ON conversation_sessions (started_at DESC);

CREATE INDEX IF NOT EXISTS conversation_sessions_open_idx
    ON conversation_sessions (last_seen_at DESC) WHERE closed_at IS NULL;

CREATE INDEX IF NOT EXISTS conversation_sessions_channel_idx
    ON conversation_sessions (channel, started_at DESC);

CREATE INDEX IF NOT EXISTS conversation_sessions_embedding_idx
    ON conversation_sessions USING hnsw (summary_embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

COMMENT ON TABLE conversation_sessions IS
  'HORIZON working-memory durable index (Phase 5.5). One row per session. closed_at IS NULL = live in Redis. Summary + embedding written at close.';

-- ════════════════════════════════════════════════════════════════════════════
-- Permissions
-- ════════════════════════════════════════════════════════════════════════════

-- n8n_user: HORIZON workflow opens, updates last_seen_at + turn_count, and
-- closes sessions. No DELETE — keep the audit trail intact.
GRANT SELECT, INSERT, UPDATE ON conversation_sessions TO n8n_user;

-- agent_reader: HORIZON's query_db tool reads past-session context.
GRANT SELECT ON conversation_sessions TO agent_reader;

-- grafana_reader: dashboards (open-session count, sessions per channel/day,
-- avg turn count, etc.).
GRANT SELECT ON conversation_sessions TO grafana_reader;