supabase_schema.sql

create table
  if not exists public.users (
    id bigint generated by default as identity primary key,
    username text not null unique,
    email text unique,
    hashed_pw text not null,
    created_at timestamptz not null default now ()
  );

create table
  if not exists public.sections (
    id bigint generated by default as identity primary key,
    user_id bigint not null references public.users (id) on delete cascade,
    section_id text not null,
    completed boolean not null default true,
    completed_at timestamptz not null default now (),
    unique (user_id, section_id)
  );

create table
  if not exists public.bookmarks (
    id bigint generated by default as identity primary key,
    user_id bigint not null references public.users (id) on delete cascade,
    bm_id text not null,
    title text not null,
    path text not null,
    added_at timestamptz not null default now (),
    unique (user_id, bm_id)
  );

create table
  if not exists public.quiz_scores (
    id bigint generated by default as identity primary key,
    user_id bigint not null references public.users (id) on delete cascade,
    quiz_id text not null,
    score integer not null,
    total integer not null,
    taken_at timestamptz not null default now (),
    unique (user_id, quiz_id)
  );

create table
  if not exists public.solver_history (
    id bigint generated by default as identity primary key,
    user_id bigint not null references public.users (id) on delete cascade,
    expression text,
    result text,
    created_at timestamptz not null default now ()
  );

create index if not exists sections_user_id_idx on public.sections (user_id);

create index if not exists bookmarks_user_id_idx on public.bookmarks (user_id);

create index if not exists quiz_scores_user_id_idx on public.quiz_scores (user_id);

create index if not exists solver_history_user_id_idx on public.solver_history (user_id);

-- This project uses backend-managed auth, not Supabase Auth.
-- The backend validates its own JWT and talks to Supabase from server-side code.
-- If RLS is enabled on these custom tables, anon/publishable keys will be blocked.
-- For local development, disable RLS on these app-owned tables.
alter table public.users disable row level security;
alter table public.sections disable row level security;
alter table public.bookmarks disable row level security;
alter table public.quiz_scores disable row level security;
alter table public.solver_history disable row level security;