scripts/supabase_schema.sql

-- ============================================================
-- Meridian — Supabase Database Schema (unified, idempotent)
-- Run this entire file in: Supabase Dashboard → SQL Editor → New Query
--
-- Safe to re-run: every CREATE uses IF NOT EXISTS, every ALTER uses
-- IF NOT EXISTS, the function is DROPped before CREATE so return-type
-- changes don't error.
-- ============================================================

-- ── Enable pgvector ──────────────────────────────────────────────
CREATE EXTENSION IF NOT EXISTS vector;

-- ── Profiles ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS public.profiles (
  id                 UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  display_name       TEXT,
  age                INT CHECK (age BETWEEN 13 AND 120),
  preferred_modality TEXT DEFAULT 'cbt'
                     CHECK (preferred_modality IN ('cbt','act','dbt','mindfulness','sfbt')),
  consent_given      BOOLEAN NOT NULL DEFAULT false,
  consent_at         TIMESTAMPTZ,
  created_at         TIMESTAMPTZ DEFAULT now(),
  updated_at         TIMESTAMPTZ DEFAULT now()
);

-- ── Sessions ──────────────────────────────────────────────────────
-- Replaces the in-memory session_store dict in main.py.
-- prob_history is Phase 10's per-turn SVM depression probability
-- trajectory — see backend/agents/diagnosis.py::_decide().
CREATE TABLE IF NOT EXISTS public.sessions (
  id                TEXT PRIMARY KEY,
  user_id           UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  mode              TEXT NOT NULL CHECK (mode IN ('diagnosis','therapy')),
  modality          TEXT CHECK (modality IN ('cbt','act','dbt','mindfulness','sfbt')),
  embedding_buffer  JSONB NOT NULL DEFAULT '[]',
  char_buffer_total INT   NOT NULL DEFAULT 0,
  prob_history      JSONB NOT NULL DEFAULT '[]',
  diagnosis_ready   BOOLEAN NOT NULL DEFAULT false,
  crisis_detected   BOOLEAN NOT NULL DEFAULT false,
  risk_scores       JSONB NOT NULL DEFAULT '{}',
  created_at        TIMESTAMPTZ DEFAULT now(),
  updated_at        TIMESTAMPTZ DEFAULT now()
);

-- Phase 10 backfill — ensures existing DBs without prob_history
-- get the column added. No-op on fresh DBs (column already exists
-- from the CREATE TABLE above).
ALTER TABLE public.sessions
  ADD COLUMN IF NOT EXISTS prob_history JSONB NOT NULL DEFAULT '[]';

CREATE INDEX IF NOT EXISTS sessions_user_id_idx ON public.sessions(user_id);

-- ── Messages ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS public.messages (
  id          BIGSERIAL PRIMARY KEY,
  session_id  TEXT NOT NULL REFERENCES public.sessions(id) ON DELETE CASCADE,
  role        TEXT NOT NULL CHECK (role IN ('user','assistant')),
  content     TEXT NOT NULL,
  created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS messages_session_id_idx ON public.messages(session_id);

-- ── Audit Log ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS public.audit_log (
  id          BIGSERIAL PRIMARY KEY,
  user_id     UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  session_id  TEXT,
  event_type  TEXT NOT NULL,
  -- event_type values:
  --   'consent_accepted'   — user accepted ToS on onboarding page
  --   'crisis_detected'    — crisis node fired during a chat turn
  --   'diagnosis_result'   — MentalRoBERTa SVM classification completed
  --   'guardian_block'     — guardian agent blocked a request
  --   'rate_limit_hit'     — user exceeded 20 req/min
  --   'phq9_assessment'    — completed 9-question PHQ-9 cycle
  metadata    JSONB NOT NULL DEFAULT '{}',
  created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS audit_log_user_id_idx    ON public.audit_log(user_id);
CREATE INDEX IF NOT EXISTS audit_log_event_type_idx ON public.audit_log(event_type);
CREATE INDEX IF NOT EXISTS audit_log_created_at_idx ON public.audit_log(created_at DESC);

-- ── Memory Embeddings (pgvector — replaces in-memory ChromaDB) ────
-- Uses sentence-transformers/all-MiniLM-L6-v2 → 384 dimensions.
CREATE TABLE IF NOT EXISTS public.memory_embeddings (
  id          BIGSERIAL PRIMARY KEY,
  session_id  TEXT REFERENCES public.sessions(id) ON DELETE CASCADE,
  user_id     UUID REFERENCES auth.users(id)     ON DELETE CASCADE,
  content     TEXT NOT NULL,
  embedding   vector(384),
  created_at  TIMESTAMPTZ DEFAULT now()
);

-- IVFFlat index for approximate nearest-neighbour search.
-- Run AFTER inserting at least 100 rows for best performance.
CREATE INDEX IF NOT EXISTS memory_embeddings_vector_idx
  ON public.memory_embeddings
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 50);

-- ── PHQ-9 Trajectory ──────────────────────────────────────────────
-- phq9_responses    — every individual answered question
-- phq9_assessments  — sealed 9-question totals + severity (chart source)

CREATE TABLE IF NOT EXISTS public.phq9_responses (
  id              BIGSERIAL PRIMARY KEY,
  user_id         UUID NOT NULL REFERENCES auth.users(id)      ON DELETE CASCADE,
  session_id      TEXT NOT NULL REFERENCES public.sessions(id) ON DELETE CASCADE,
  question_index  INT  NOT NULL CHECK (question_index BETWEEN 0 AND 8),
  score           INT  NOT NULL CHECK (score          BETWEEN 0 AND 3),
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS phq9_responses_session_idx
  ON public.phq9_responses(session_id);
CREATE INDEX IF NOT EXISTS phq9_responses_user_idx
  ON public.phq9_responses(user_id, created_at DESC);


CREATE TABLE IF NOT EXISTS public.phq9_assessments (
  id           BIGSERIAL PRIMARY KEY,
  user_id      UUID NOT NULL REFERENCES auth.users(id)      ON DELETE CASCADE,
  session_id   TEXT NOT NULL REFERENCES public.sessions(id) ON DELETE CASCADE,
  total_score  INT  NOT NULL CHECK (total_score BETWEEN 0 AND 27),
  severity     TEXT NOT NULL CHECK (severity IN
                  ('minimal','mild','moderate','moderately severe','severe')),
  -- 9 scores in question-index order [s0, s1, ..., s8]
  responses    JSONB NOT NULL DEFAULT '[]',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS phq9_assessments_user_idx
  ON public.phq9_assessments(user_id, created_at DESC);


-- ── Row Level Security ────────────────────────────────────────────
-- Users can ONLY read/write their own rows. Enforced at DB level.

ALTER TABLE public.profiles          ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.sessions          ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.messages          ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.memory_embeddings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.phq9_responses    ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.phq9_assessments  ENABLE ROW LEVEL SECURITY;
-- audit_log is write-only for users, readable only via service key (server-side)
ALTER TABLE public.audit_log         ENABLE ROW LEVEL SECURITY;

-- Profiles: own row only
DROP POLICY IF EXISTS "profiles: own row" ON public.profiles;
CREATE POLICY "profiles: own row" ON public.profiles
  FOR ALL USING (auth.uid() = id);

-- Sessions: own rows only
DROP POLICY IF EXISTS "sessions: own rows" ON public.sessions;
CREATE POLICY "sessions: own rows" ON public.sessions
  FOR ALL USING (auth.uid() = user_id);

-- Messages: own sessions' messages only
DROP POLICY IF EXISTS "messages: own sessions" ON public.messages;
CREATE POLICY "messages: own sessions" ON public.messages
  FOR ALL USING (
    session_id IN (
      SELECT id FROM public.sessions WHERE user_id = auth.uid()
    )
  );

-- Memory: own rows only
DROP POLICY IF EXISTS "memory: own rows" ON public.memory_embeddings;
CREATE POLICY "memory: own rows" ON public.memory_embeddings
  FOR ALL USING (auth.uid() = user_id);

-- PHQ-9: own rows only
DROP POLICY IF EXISTS "phq9_responses: own rows" ON public.phq9_responses;
CREATE POLICY "phq9_responses: own rows" ON public.phq9_responses
  FOR ALL USING (auth.uid() = user_id);

DROP POLICY IF EXISTS "phq9_assessments: own rows" ON public.phq9_assessments;
CREATE POLICY "phq9_assessments: own rows" ON public.phq9_assessments
  FOR ALL USING (auth.uid() = user_id);

-- Audit log: users cannot read; service key (backend) can write via service role
DROP POLICY IF EXISTS "audit_log: service write only" ON public.audit_log;
CREATE POLICY "audit_log: service write only" ON public.audit_log
  FOR INSERT WITH CHECK (true);  -- backend uses service key which bypasses RLS

-- ── Auto-update updated_at ────────────────────────────────────────
CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS set_profiles_updated_at ON public.profiles;
CREATE TRIGGER set_profiles_updated_at
  BEFORE UPDATE ON public.profiles
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

DROP TRIGGER IF EXISTS set_sessions_updated_at ON public.sessions;
CREATE TRIGGER set_sessions_updated_at
  BEFORE UPDATE ON public.sessions
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- ============================================================
-- Memory retrieval RPC
-- Called from backend/agents/memory.py::retrieve_similar.
-- Returns the top-N most semantically similar past exchanges
-- for a given user, across ALL of their sessions.
--
-- IMPORTANT: we DROP first because Postgres won't let
-- CREATE OR REPLACE change a function's return type. If you've
-- ever run a version of this with a different return signature
-- (e.g. session_id uuid instead of text), the CREATE OR REPLACE
-- alone will fail with error 42P13. The explicit DROP makes
-- the rerun safe regardless of what's already in the DB.
-- ============================================================

DROP FUNCTION IF EXISTS public.match_memory_embeddings(vector, uuid, int);

CREATE OR REPLACE FUNCTION public.match_memory_embeddings(
  query_embedding vector(384),
  target_user_id  uuid,
  match_count     int DEFAULT 2
)
RETURNS TABLE (
  id          bigint,
  session_id  text,
  content     text,
  similarity  float
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
  RETURN QUERY
  SELECT
    m.id,
    m.session_id,
    m.content,
    -- pgvector's <=> is cosine distance (0 = identical, 2 = opposite).
    -- We return similarity = 1 - distance so callers can sort/threshold
    -- on the more intuitive "higher is better" scale if they want.
    1 - (m.embedding <=> query_embedding) AS similarity
  FROM public.memory_embeddings m
  WHERE m.user_id = target_user_id
    AND m.embedding IS NOT NULL
  ORDER BY m.embedding <=> query_embedding
  LIMIT match_count;
END;
$$;

GRANT EXECUTE ON FUNCTION public.match_memory_embeddings(vector, uuid, int)
  TO authenticated, service_role;

-- ── Useful admin queries (run manually in SQL editor) ─────────────
-- Crisis events last 7 days:
-- SELECT u.email, a.session_id, a.metadata, a.created_at
-- FROM public.audit_log a JOIN auth.users u ON a.user_id = u.id
-- WHERE a.event_type = 'crisis_detected'
--   AND a.created_at > now() - interval '7 days'
-- ORDER BY a.created_at DESC;

-- Daily active users (last 30 days):
-- SELECT date_trunc('day', created_at) AS day, COUNT(DISTINCT user_id)
-- FROM public.sessions GROUP BY 1 ORDER BY 1 DESC LIMIT 30;

-- PHQ-9 severity distribution last 30 days:
-- SELECT severity, COUNT(*)
-- FROM public.phq9_assessments
-- WHERE created_at > now() - interval '30 days'
-- GROUP BY 1 ORDER BY 1;

-- Per-user PHQ-9 trajectory (deltas):
-- SELECT user_id, created_at, total_score,
--        total_score - LAG(total_score) OVER (
--          PARTITION BY user_id ORDER BY created_at
--        ) AS delta
-- FROM public.phq9_assessments
-- ORDER BY user_id, created_at;

-- Adaptive diagnosis — average turns to fire (Phase 10):
-- SELECT AVG(jsonb_array_length(prob_history)) AS avg_turns_to_fire
-- FROM public.sessions
-- WHERE mode = 'diagnosis' AND diagnosis_ready = true;