DavidL72Code/UMB_Sustainable_Chatbot
0
1-- Staff dashboard storage for the SSL chatbot.2--3-- Run once in the Supabase SQL editor (Dashboard -> SQL Editor -> New query).4--5-- Design: content and metrics are kept in separate tables.6-- * chat_metrics - one numbers-only row per answer. No question or answer7-- text is ever written here, so ordinary visitor chats8-- leave no transcript behind.9-- * flagged_chats - the full transcript, but only for answers the pipeline10-- flagged as bad and that staff need to review.11-- Employee accounts live in Supabase Auth, so there is no users table: invite12-- staff under Authentication -> Users and they can sign in immediately, with no13-- Space restart and no redeploy.14 15-- ---------------------------------------------------------------------------16-- Numbers for every answer. Content-free.17-- ---------------------------------------------------------------------------18create table if not exists public.chat_metrics (19 id text primary key,20 created_at timestamptz not null default now(),21 status text,22 response_mode text,23 path_label text,24 blocked boolean not null default false,25 needs_clarification boolean not null default false,26 27 latency_ms double precision,28 retrieval_ms double precision,29 llm_ms double precision,30 31 total_tokens integer,32 input_tokens integer,33 output_tokens integer,34 cost_usd numeric(12, 8),35 llm_call_count integer,36 37 confidence_score double precision,38 is_low_confidence boolean,39 top_score double precision,40 score_gap double precision,41 source_count integer,42 retrieved_count integer,43 44 flagged boolean not null default false,45 flag_reasons text[] not null default '{}'46);47 48create index if not exists chat_metrics_created_at_idx on public.chat_metrics (created_at desc);49create index if not exists chat_metrics_flagged_idx on public.chat_metrics (flagged) where flagged;50 51-- ---------------------------------------------------------------------------52-- Transcripts, only for flagged answers.53-- ---------------------------------------------------------------------------54create table if not exists public.flagged_chats (55 id text primary key references public.chat_metrics (id) on delete cascade,56 created_at timestamptz not null default now(),57 conversation_id text,58 question text,59 answer text,60 flag_reasons text[] not null default '{}',61 sources jsonb not null default '[]'::jsonb,62 trace jsonb not null default '{}'::jsonb,63 reviewed_by text,64 reviewed_at timestamptz,65 review_note text66);67 68create index if not exists flagged_chats_created_at_idx on public.flagged_chats (created_at desc);69create index if not exists flagged_chats_unreviewed_idx on public.flagged_chats (created_at desc)70 where reviewed_at is null;71 72-- ---------------------------------------------------------------------------73-- Who signed in and which interaction they opened. The dashboard exposes real74-- visitor questions, so views are attributable to a named employee.75-- ---------------------------------------------------------------------------76create table if not exists public.admin_audit_events (77 id bigserial primary key,78 created_at timestamptz not null default now(),79 username text not null,80 action text not null,81 detail text82);83 84create index if not exists admin_audit_created_at_idx on public.admin_audit_events (created_at desc);85 86-- ---------------------------------------------------------------------------87-- Daily rollup. A view, so averages and percentiles are always exact rather88-- than depending on counters staying in sync.89-- ---------------------------------------------------------------------------90create or replace view public.daily_metrics as91select92 (created_at at time zone 'UTC')::date as day,93 count(*) as chat_count,94 count(*) filter (where flagged) as flagged_count,95 count(*) filter (where blocked) as blocked_count,96 count(*) filter (where status = 'error') as error_count,97 count(*) filter (where needs_clarification) as clarification_count,98 count(*) filter (where is_low_confidence) as low_confidence_count,99 100 round(avg(latency_ms)::numeric, 1) as avg_latency_ms,101 round((percentile_cont(0.95) within group (order by latency_ms))::numeric, 1) as p95_latency_ms,102 round(avg(retrieval_ms)::numeric, 1) as avg_retrieval_ms,103 round(avg(llm_ms)::numeric, 1) as avg_llm_ms,104 105 sum(total_tokens) as total_tokens,106 round(avg(total_tokens)::numeric, 0) as avg_tokens,107 sum(cost_usd) as total_cost_usd,108 round(avg(cost_usd), 8) as avg_cost_usd,109 110 -- Quality proxies computed by the pipeline itself, no judge model needed.111 round(avg(confidence_score)::numeric, 3) as avg_confidence_score,112 round(avg(top_score)::numeric, 4) as avg_top_score,113 round(avg(score_gap)::numeric, 4) as avg_score_gap,114 round(avg(source_count)::numeric, 2) as avg_source_count115from public.chat_metrics116group by 1117order by 1 desc;118 119-- ---------------------------------------------------------------------------120-- Lock the tables down. The backend uses the service role key, which bypasses121-- RLS; enabling RLS with no permissive policy means a leaked anon key cannot122-- read flagged transcripts or metrics.123-- ---------------------------------------------------------------------------124alter table public.chat_metrics enable row level security;125alter table public.flagged_chats enable row level security;126alter table public.admin_audit_events enable row level security;127 128-- ---------------------------------------------------------------------------129-- Visitor chat history.130--131-- Entirely separate from the staff tables above. Signing in is optional: an132-- anonymous visitor is stored nowhere, exactly as before. A signed-in visitor133-- gets their own history and can only ever see their own.134--135-- Isolation is enforced by Postgres, not by application code. The backend uses136-- the visitor's own access token for these tables, so the policies below decide137-- what they can see. The service role key is never used against them.138-- ---------------------------------------------------------------------------139create table if not exists public.visitor_conversations (140 id uuid primary key default gen_random_uuid(),141 user_id uuid not null references auth.users (id) on delete cascade,142 title text not null default 'New chat',143 created_at timestamptz not null default now(),144 updated_at timestamptz not null default now()145);146 147create index if not exists visitor_conversations_user_idx148 on public.visitor_conversations (user_id, updated_at desc);149 150create table if not exists public.visitor_messages (151 id bigserial primary key,152 conversation_id uuid not null references public.visitor_conversations (id) on delete cascade,153 user_id uuid not null references auth.users (id) on delete cascade,154 role text not null check (role in ('user', 'assistant')),155 content text not null,156 sources jsonb not null default '[]'::jsonb,157 created_at timestamptz not null default now()158);159 160create index if not exists visitor_messages_conversation_idx161 on public.visitor_messages (conversation_id, created_at);162 163alter table public.visitor_conversations enable row level security;164alter table public.visitor_messages enable row level security;165 166-- A visitor may read, write, and delete their own rows and nobody else's.167-- auth.uid() comes from the caller's JWT, so these cannot be bypassed from the168-- client or by a mistake in the backend.169drop policy if exists "own conversations" on public.visitor_conversations;170create policy "own conversations" on public.visitor_conversations171 for all using (auth.uid() = user_id) with check (auth.uid() = user_id);172 173drop policy if exists "own messages" on public.visitor_messages;174create policy "own messages" on public.visitor_messages175 for all using (auth.uid() = user_id) with check (auth.uid() = user_id);176 177-- Staff tables carry no visitor identity. Reviewing a flagged answer shows the178-- question and the retrieval trace, never who asked it, so quality review179-- cannot become a way to read one named person's chat history.180