supabase/user_logs_migration.sql

-- =====================================================
-- USER LOGS MIGRATION
-- Run this in: Supabase Dashboard → SQL Editor → New query
-- =====================================================

-- Table 1: user_logs
-- Records every chat message sent through the AI.
CREATE TABLE IF NOT EXISTS public.user_logs (
  id                 UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id            UUID        REFERENCES auth.users(id) NOT NULL,
  query              TEXT,
  route              TEXT,                   -- 'sql_query' | 'rag_retrieval' | 'tavily_search'
  sql_query          TEXT,                   -- generated SQL (only for sql_query route)
  answer             TEXT,                   -- truncated bot reply (up to 8 000 chars)
  answer_length      INTEGER,                -- character count of the full bot reply
  total_tokens       INTEGER,
  prompt_tokens      INTEGER,
  completion_tokens  INTEGER,
  has_error          BOOLEAN     DEFAULT FALSE,
  duration_ms        INTEGER,                -- total end-to-end request duration (ms)
  -- Observability & eval fields
  trace_id           UUID,                   -- unique ID per request for distributed tracing
  session_id         TEXT,                   -- client session UUID for conversation continuity
  routing_confidence NUMERIC(5,3),           -- 0–1 keyword-density score that drove agent selection
  eval_score         NUMERIC(5,3),           -- 0–1 answer quality score from AnswerEvalHook
  created_at         TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE public.user_logs ENABLE ROW LEVEL SECURITY;

-- Users can read their own chat logs (e.g. history view in the dashboard).
-- The backend uses the service role key and bypasses RLS on INSERT.
CREATE POLICY "Users can view their own logs"
  ON public.user_logs FOR SELECT
  USING (auth.uid() = user_id);

-- Index for fast per-user analytics queries
CREATE INDEX IF NOT EXISTS idx_user_logs_user_id    ON public.user_logs (user_id);
CREATE INDEX IF NOT EXISTS idx_user_logs_created_at ON public.user_logs (created_at DESC);
-- trace_id identifies exactly one chat turn; unique so view joins stay 1:1.
DROP INDEX IF EXISTS idx_user_logs_trace_id;
CREATE UNIQUE INDEX IF NOT EXISTS uq_user_logs_trace_id
  ON public.user_logs (trace_id) WHERE trace_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_user_logs_session_id ON public.user_logs (session_id);


-- =====================================================
-- ALTER scripts: add new columns to an existing table
-- Safe to run even if the table already exists.
-- =====================================================

ALTER TABLE public.user_logs ADD COLUMN IF NOT EXISTS answer             TEXT;
ALTER TABLE public.user_logs ADD COLUMN IF NOT EXISTS trace_id           UUID;
ALTER TABLE public.user_logs ADD COLUMN IF NOT EXISTS session_id         TEXT;
ALTER TABLE public.user_logs ADD COLUMN IF NOT EXISTS routing_confidence NUMERIC(5,3);
ALTER TABLE public.user_logs ADD COLUMN IF NOT EXISTS eval_score         NUMERIC(5,3);


-- =====================================================
-- Table 2: user_login_logs
-- Records every time a user signs in from the frontend.
-- =====================================================

CREATE TABLE IF NOT EXISTS public.user_login_logs (
  id            UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id       UUID        REFERENCES auth.users(id) NOT NULL,
  ip_address    TEXT,
  user_agent    TEXT,
  login_method  TEXT,
  login_at      TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE public.user_login_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their own login logs"
  ON public.user_login_logs FOR SELECT
  USING (auth.uid() = user_id);

CREATE INDEX IF NOT EXISTS idx_user_login_logs_user_id ON public.user_login_logs (user_id);

-- Backfill columns that may have been added after table creation
ALTER TABLE public.user_login_logs ADD COLUMN IF NOT EXISTS ip_address   TEXT;
ALTER TABLE public.user_login_logs ADD COLUMN IF NOT EXISTS user_agent   TEXT;
ALTER TABLE public.user_login_logs ADD COLUMN IF NOT EXISTS login_method TEXT;


-- =====================================================
-- Table 3: feedback_logs
-- Records thumbs-up / thumbs-down ratings per chat turn.
-- Joined to user_logs via trace_id for eval analysis.
-- =====================================================

CREATE TABLE IF NOT EXISTS public.feedback_logs (
  id         UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id    UUID        REFERENCES auth.users(id) NOT NULL,
  trace_id   UUID        NOT NULL,
  rating     SMALLINT    NOT NULL CHECK (rating IN (-1, 1)),
  comment    TEXT        CHECK (char_length(comment) <= 500),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE public.feedback_logs ENABLE ROW LEVEL SECURITY;

-- Users can read their own feedback; backend inserts via service role.
CREATE POLICY "Users can view their own feedback"
  ON public.feedback_logs FOR SELECT
  USING (auth.uid() = user_id);

CREATE INDEX IF NOT EXISTS idx_feedback_logs_trace_id   ON public.feedback_logs (trace_id);
CREATE INDEX IF NOT EXISTS idx_feedback_logs_created_at ON public.feedback_logs (created_at DESC);

-- One rating per user per chat turn; the backend upserts on re-rating.
-- Also serves as the per-user lookup index.
CREATE UNIQUE INDEX IF NOT EXISTS uq_feedback_logs_user_trace
  ON public.feedback_logs (user_id, trace_id);



-- =====================================================
-- Convenience view: eval summary per route
-- Useful for a quick health-check dashboard.
-- =====================================================

-- security_invoker: run with the caller's permissions so RLS on the
-- underlying tables applies (otherwise the view bypasses RLS entirely).
CREATE OR REPLACE VIEW public.v_eval_summary
WITH (security_invoker = true) AS
SELECT
  route,
  COUNT(*)                                  AS total_requests,
  ROUND(AVG(eval_score)::NUMERIC, 3)        AS avg_eval_score,
  ROUND(AVG(routing_confidence)::NUMERIC,3) AS avg_routing_confidence,
  ROUND(AVG(duration_ms))                   AS avg_duration_ms,
  SUM(CASE WHEN has_error THEN 1 ELSE 0 END) AS error_count,
  SUM(total_tokens)                          AS total_tokens_used,
  DATE_TRUNC('day', created_at)              AS day
FROM public.user_logs
GROUP BY route, DATE_TRUNC('day', created_at);

-- =====================================================
-- Convenience view: feedback join with eval scores
-- =====================================================

-- DROP first: CREATE OR REPLACE cannot reorder/insert view columns.
DROP VIEW IF EXISTS public.v_feedback_with_eval;

CREATE VIEW public.v_feedback_with_eval
WITH (security_invoker = true) AS
SELECT
  f.id,
  f.user_id,
  f.trace_id,
  f.rating,
  f.comment,
  f.created_at                              AS rated_at,
  l.route,
  l.query,
  l.session_id,
  l.has_error,
  l.eval_score,
  l.routing_confidence,
  l.duration_ms,
  l.total_tokens,
  f.created_at - l.created_at               AS time_to_feedback,
  -- Rows where the human rating and the automated eval disagree.
  (f.rating = -1 AND l.eval_score >= 0.8)
    OR (f.rating = 1 AND l.eval_score <= 0.3) AS eval_disagreement
FROM public.feedback_logs f
LEFT JOIN public.user_logs l ON l.trace_id = f.trace_id;