supabase_schema.sql

-- ============================================================================
-- MyeAI (Myeloma AI) — Supabase schema
--
-- Run this ONCE in your Supabase project:
--     Supabase Studio  ▸  SQL Editor  ▸  New query  ▸  paste  ▸  Run
--
-- It is idempotent: running it again is harmless.
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 1. Resumable session state — one row per participant email.
--    This is what gets loaded when a returning participant signs back in.
-- ----------------------------------------------------------------------------
create table if not exists public.p3_sessions (
    email        text primary key,
    -- The participant's study User ID, entered alongside the email at sign-in.
    -- Checked on every return visit: a mismatch is refused rather than letting
    -- one participant open another's session by guessing an email address.
    user_id      text,
    -- The 16 intake answers, in question order: ["Yes","No",...]
    answers      jsonb       not null default '[]'::jsonb,
    -- Full model-facing conversation, including the hidden profile seed turn.
    convo        jsonb       not null default '[]'::jsonb,
    -- What the participant actually sees in the chat window.
    chat_display jsonb       not null default '[]'::jsonb,
    -- Follow-up counter, so the MAX_FOLLOWUPS cap survives a reload.
    followups    integer     not null default 0,
    -- Bumped on every conversation write. Used for optimistic locking so a
    -- stale tab or a second device cannot overwrite newer state.
    revision     integer     not null default 0,
    -- How many of the 16 questions `answers` actually has filled in. The
    -- questionnaire auto-saves on every click, and those writes can arrive out
    -- of order, so this is used to refuse any write that would replace a more
    -- complete set of answers with a staler, emptier one.
    answers_count integer    not null default 0,
    created_at   timestamptz not null default now(),
    updated_at   timestamptz not null default now()
);

-- For projects created before these columns existed.
alter table public.p3_sessions
    add column if not exists revision integer not null default 0;
alter table public.p3_sessions
    add column if not exists answers_count integer not null default 0;
alter table public.p3_sessions
    add column if not exists user_id text;

create index if not exists p3_sessions_user_id_idx
    on public.p3_sessions (user_id);

-- ----------------------------------------------------------------------------
-- 2. Append-only log of every chat turn.
--    The session row above is overwritten when a participant starts over;
--    this table is never overwritten, so no transcript is ever lost.
-- ----------------------------------------------------------------------------
create table if not exists public.p3_messages (
    id         bigint generated always as identity primary key,
    email      text        not null,
    role       text        not null check (role in ('user', 'assistant')),
    content    text        not null,
    turn       integer,
    created_at timestamptz not null default now()
);

create index if not exists p3_messages_email_created_idx
    on public.p3_messages (email, created_at);

-- ----------------------------------------------------------------------------
-- 3. Row Level Security.
--    RLS is ON with NO policies, which means the public `anon` key can read
--    and write NOTHING. The app authenticates with the `service_role` key,
--    which bypasses RLS and never leaves the Space's server-side process.
--    (Gradio runs your Python on the server; the key is never sent to a browser.)
-- ----------------------------------------------------------------------------
alter table public.p3_sessions enable row level security;
alter table public.p3_messages enable row level security;

-- Belt and braces: make sure the anon/authenticated roles hold no grants on
-- these tables even if RLS were later disabled by accident.
revoke all on public.p3_sessions  from anon, authenticated;
revoke all on public.p3_messages  from anon, authenticated;

-- ----------------------------------------------------------------------------
-- 4. Handy views for reviewing collected data in the Studio Table Editor.
-- ----------------------------------------------------------------------------
-- SECURITY: `security_invoker = on` is load-bearing. A plain Postgres view runs
-- with its OWNER's privileges, which would bypass the RLS above and hand every
-- participant's email to anyone holding the public anon key. With
-- security_invoker the view is evaluated as the caller, so RLS still applies.
-- Dropped first because `create or replace view` cannot change the column list
-- of an existing view. The view holds no data, so this is always safe.
drop view if exists public.p3_session_overview;

create view public.p3_session_overview
with (security_invoker = on) as
select
    s.user_id,
    s.email,
    jsonb_array_length(s.answers)      as answers_saved,
    jsonb_array_length(s.chat_display) as visible_messages,
    s.followups,
    s.revision,
    s.created_at,
    s.updated_at,
    (select count(*) from public.p3_messages m where m.email = s.email)
                                       as logged_turns
from public.p3_sessions s
order by s.updated_at desc;

-- Supabase grants SELECT on new objects in `public` to anon/authenticated via
-- ALTER DEFAULT PRIVILEGES, so the view must be revoked explicitly too.
revoke all on public.p3_session_overview from anon, authenticated;