Set up shared publishing and chat
Run this once in Supabase → SQL Editor. It makes release metadata public, but only signed-in users can publish. Uploaded media goes in a public bucket; never store private documents here.
create table if not exists public.profiles (
id uuid primary key references auth.users(id) on delete cascade,
display_name text,
avatar_url text,
created_at timestamptz not null default now()
);
create table if not exists public.messages (
id uuid primary key default gen_random_uuid(),
user_id uuid not null references auth.users(id) on delete cascade,
display_name text not null,
text text not null check (char_length(text) between 1 and 1000),
created_at timestamptz not null default now()
);
create table if not exists public.publications (
id uuid primary key default gen_random_uuid(),
user_id uuid not null references auth.users(id) on delete cascade,
kind text not null check (kind in ('music','book')),
title text not null,
creator text,
platform text,
url text,
art_url text,
description text,
storage_path text,
file_name text,
created_at timestamptz not null default now()
);
alter table public.profiles enable row level security;
alter table public.messages enable row level security;
alter table public.publications enable row level security;
drop policy if exists "profile_read_own" on public.profiles;
create policy "profile_read_own" on public.profiles for select to authenticated using (auth.uid() = id);
drop policy if exists "profile_insert_own" on public.profiles;
create policy "profile_insert_own" on public.profiles for insert to authenticated with check (auth.uid() = id);
drop policy if exists "profile_update_own" on public.profiles;
create policy "profile_update_own" on public.profiles for update to authenticated using (auth.uid() = id) with check (auth.uid() = id);
drop policy if exists "chat_read_members" on public.messages;
create policy "chat_read_members" on public.messages for select to authenticated using (true);
drop policy if exists "chat_insert_own" on public.messages;
create policy "chat_insert_own" on public.messages for insert to authenticated with check (auth.uid() = user_id);
drop policy if exists "publications_read_public" on public.publications;
create policy "publications_read_public" on public.publications for select to anon, authenticated using (true);
drop policy if exists "publications_insert_own" on public.publications;
create policy "publications_insert_own" on public.publications for insert to authenticated with check (auth.uid() = user_id);
insert into storage.buckets (id,name,public)
values ('readers-diary-media','readers-diary-media',true)
on conflict (id) do update set public = true;
drop policy if exists "diary_media_public_read" on storage.objects;
create policy "diary_media_public_read" on storage.objects for select to anon, authenticated using (bucket_id = 'readers-diary-media');
drop policy if exists "diary_media_insert_own_folder" on storage.objects;
create policy "diary_media_insert_own_folder" on storage.objects for insert to authenticated with check (bucket_id = 'readers-diary-media' and (storage.foldername(name))[1] = auth.uid()::text);
drop policy if exists "diary_media_delete_own_folder" on storage.objects;
create policy "diary_media_delete_own_folder" on storage.objects for delete to authenticated using (bucket_id = 'readers-diary-media' and (storage.foldername(name))[1] = auth.uid()::text);After the SQL succeeds, enable Realtime for publications and messages under Database → Publications. Set Google Auth and the deployed redirect URLs in Authentication.