byGalax
127 lines
3.8 KiB
SQL
127 lines
3.8 KiB
SQL
-- Phase 4C: cloud-synced soundboard.
|
|
--
|
|
-- user_soundboards: one row per cloud-known sound. The audio payload itself
|
|
-- lives in the `soundboards` storage bucket at `<user_id>/<sound_id>.bin`,
|
|
-- E2E-encrypted with the user's own X25519 key (sealed-to-self). The first
|
|
-- 24 bytes of the stored object are the XSalsa20 nonce; the rest is the
|
|
-- Poly1305-authenticated ciphertext.
|
|
--
|
|
-- Metadata (name, gain, hotkey, category, sort_order) is stored plaintext —
|
|
-- not considered sensitive by the spec — so other devices of the same user
|
|
-- can read it without holding the private key.
|
|
|
|
create table if not exists public.user_soundboards (
|
|
id uuid primary key default gen_random_uuid(),
|
|
user_id uuid not null references auth.users(id) on delete cascade,
|
|
name text not null,
|
|
mime text not null,
|
|
size bigint not null,
|
|
category text null,
|
|
hotkey text null,
|
|
gain real not null default 1.0,
|
|
sort_order integer not null default 0,
|
|
storage_path text not null,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint user_soundboards_name_len check (length(name) between 1 and 200),
|
|
constraint user_soundboards_size_pos check (size > 0)
|
|
);
|
|
|
|
create index if not exists user_soundboards_user_idx
|
|
on public.user_soundboards(user_id, updated_at desc);
|
|
|
|
alter table public.user_soundboards enable row level security;
|
|
|
|
drop policy if exists user_soundboards_select on public.user_soundboards;
|
|
drop policy if exists user_soundboards_insert on public.user_soundboards;
|
|
drop policy if exists user_soundboards_update on public.user_soundboards;
|
|
drop policy if exists user_soundboards_delete on public.user_soundboards;
|
|
|
|
create policy user_soundboards_select
|
|
on public.user_soundboards
|
|
for select
|
|
using (user_id = auth.uid());
|
|
|
|
create policy user_soundboards_insert
|
|
on public.user_soundboards
|
|
for insert
|
|
with check (user_id = auth.uid());
|
|
|
|
create policy user_soundboards_update
|
|
on public.user_soundboards
|
|
for update
|
|
using (user_id = auth.uid())
|
|
with check (user_id = auth.uid());
|
|
|
|
create policy user_soundboards_delete
|
|
on public.user_soundboards
|
|
for delete
|
|
using (user_id = auth.uid());
|
|
|
|
alter table public.user_soundboards replica identity full;
|
|
|
|
do $$
|
|
begin
|
|
if not exists (
|
|
select 1
|
|
from pg_publication_tables
|
|
where pubname = 'supabase_realtime'
|
|
and schemaname = 'public'
|
|
and tablename = 'user_soundboards'
|
|
) then
|
|
execute 'alter publication supabase_realtime add table public.user_soundboards';
|
|
end if;
|
|
end
|
|
$$;
|
|
|
|
-- Storage bucket: private, owner-only.
|
|
do $$
|
|
begin
|
|
if not exists (select 1 from storage.buckets where id = 'soundboards') then
|
|
insert into storage.buckets (id, name, public)
|
|
values ('soundboards', 'soundboards', false);
|
|
end if;
|
|
end
|
|
$$;
|
|
|
|
drop policy if exists soundboards_select on storage.objects;
|
|
drop policy if exists soundboards_insert on storage.objects;
|
|
drop policy if exists soundboards_update on storage.objects;
|
|
drop policy if exists soundboards_delete on storage.objects;
|
|
|
|
create policy soundboards_select
|
|
on storage.objects
|
|
for select
|
|
using (
|
|
bucket_id = 'soundboards'
|
|
and auth.uid()::text = (storage.foldername(name))[1]
|
|
);
|
|
|
|
create policy soundboards_insert
|
|
on storage.objects
|
|
for insert
|
|
with check (
|
|
bucket_id = 'soundboards'
|
|
and auth.uid()::text = (storage.foldername(name))[1]
|
|
);
|
|
|
|
create policy soundboards_update
|
|
on storage.objects
|
|
for update
|
|
using (
|
|
bucket_id = 'soundboards'
|
|
and auth.uid()::text = (storage.foldername(name))[1]
|
|
)
|
|
with check (
|
|
bucket_id = 'soundboards'
|
|
and auth.uid()::text = (storage.foldername(name))[1]
|
|
);
|
|
|
|
create policy soundboards_delete
|
|
on storage.objects
|
|
for delete
|
|
using (
|
|
bucket_id = 'soundboards'
|
|
and auth.uid()::text = (storage.foldername(name))[1]
|
|
);
|