schema.sql

-- ============================================================
-- Sonlum Database Schema
-- Paste this entire file into the Supabase SQL Editor and run it.
-- ============================================================


-- ============================================================
-- PROFILES
-- One row per auth user, auto-created on signup.
-- ============================================================
create table if not exists public.profiles (
  id          uuid references auth.users on delete cascade primary key,
  email       text,
  full_name   text,
  avatar_url  text,
  created_at  timestamptz default now(),
  updated_at  timestamptz default now()
);

alter table public.profiles enable row level security;

create policy "profiles: select own" on public.profiles
  for select using (auth.uid() = id);
create policy "profiles: insert own" on public.profiles
  for insert with check (auth.uid() = id);
create policy "profiles: update own" on public.profiles
  for update using (auth.uid() = id);

-- Auto-create profile row when a new user signs up
create or replace function public.handle_new_user()
returns trigger language plpgsql security definer as $$
begin
  insert into public.profiles (id, email, full_name, avatar_url)
  values (
    new.id,
    new.email,
    new.raw_user_meta_data->>'full_name',
    new.raw_user_meta_data->>'avatar_url'
  )
  on conflict (id) do nothing;
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();


-- ============================================================
-- SUNS  (created before rays so rays.sun_id FK works)
-- ============================================================
create table if not exists public.suns (
  id             uuid default gen_random_uuid() primary key,
  user_id        uuid references auth.users on delete cascade not null default auth.uid(),
  name           text not null,
  color          text not null default '#888888',
  parent_sun_id  uuid references public.suns(id) on delete set null,
  -- Cluster centroid stored as plain float array (no pgvector needed)
  centroid       float8[],
  -- Ray UUIDs stored as text array
  ray_ids        text[] default '{}',
  created_at     timestamptz default now(),
  updated_at     timestamptz default now()
);

alter table public.suns enable row level security;

create policy "suns: select own" on public.suns
  for select using (auth.uid() = user_id);
create policy "suns: insert own" on public.suns
  for insert with check (auth.uid() = user_id);
create policy "suns: update own" on public.suns
  for update using (auth.uid() = user_id);
create policy "suns: delete own" on public.suns
  for delete using (auth.uid() = user_id);

create index if not exists idx_suns_user_id on public.suns(user_id);

-- Auto-update updated_at
create or replace function public.fn_suns_updated_at()
returns trigger language plpgsql as $$
begin new.updated_at = now(); return new; end;
$$;
drop trigger if exists trg_suns_updated_at on public.suns;
create trigger trg_suns_updated_at
  before update on public.suns
  for each row execute procedure public.fn_suns_updated_at();


-- ============================================================
-- RAYS
-- ============================================================
create table if not exists public.rays (
  id           uuid default gen_random_uuid() primary key,
  user_id      uuid references auth.users on delete cascade not null default auth.uid(),
  content      text not null default '',
  raw_content  text,
  icon         text,
  body         text,
  -- TF-IDF embedding (512 floats). float8[] so REST API returns number[] directly.
  embedding    float8[],
  sun_id       uuid references public.suns(id) on delete set null,
  is_pinned    boolean default false,
  created_at   timestamptz default now(),
  updated_at   timestamptz default now()
);

alter table public.rays enable row level security;

create policy "rays: select own" on public.rays
  for select using (auth.uid() = user_id);
create policy "rays: insert own" on public.rays
  for insert with check (auth.uid() = user_id);
create policy "rays: update own" on public.rays
  for update using (auth.uid() = user_id);
create policy "rays: delete own" on public.rays
  for delete using (auth.uid() = user_id);

create index if not exists idx_rays_user_id on public.rays(user_id);
create index if not exists idx_rays_created_at on public.rays(user_id, created_at desc);
create index if not exists idx_rays_sun_id on public.rays(sun_id);

-- Auto-update updated_at
create or replace function public.fn_rays_updated_at()
returns trigger language plpgsql as $$
begin new.updated_at = now(); return new; end;
$$;
drop trigger if exists trg_rays_updated_at on public.rays;
create trigger trg_rays_updated_at
  before update on public.rays
  for each row execute procedure public.fn_rays_updated_at();


-- ============================================================
-- PINNED RAYS
-- Separate table so pinning doesn't require updating the ray row.
-- ============================================================
create table if not exists public.pinned_rays (
  id          uuid default gen_random_uuid() primary key,
  user_id     uuid references auth.users on delete cascade not null default auth.uid(),
  ray_id      uuid references public.rays(id) on delete cascade not null,
  created_at  timestamptz default now(),
  unique(user_id, ray_id)
);

alter table public.pinned_rays enable row level security;

create policy "pinned_rays: select own" on public.pinned_rays
  for select using (auth.uid() = user_id);
create policy "pinned_rays: insert own" on public.pinned_rays
  for insert with check (auth.uid() = user_id);
create policy "pinned_rays: delete own" on public.pinned_rays
  for delete using (auth.uid() = user_id);

create index if not exists idx_pinned_rays_user_id on public.pinned_rays(user_id, created_at desc);


-- ============================================================
-- REALTIME
-- Required for subscribeToRays / subscribeToSuns / subscribeToPinnedRays
-- ============================================================
alter publication supabase_realtime add table public.rays;
alter publication supabase_realtime add table public.suns;
alter publication supabase_realtime add table public.pinned_rays;