137 lines
3.8 KiB
PL/PgSQL
137 lines
3.8 KiB
PL/PgSQL
-- Token-based sharing for individual assessments
|
|
create table if not exists public.assessment_shares (
|
|
assessment_id uuid primary key references public.assessments(id) on delete cascade,
|
|
user_id uuid not null references auth.users(id) on delete cascade,
|
|
share_token text not null unique,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
drop trigger if exists trg_assessment_shares_updated_at on public.assessment_shares;
|
|
create trigger trg_assessment_shares_updated_at
|
|
before update on public.assessment_shares
|
|
for each row execute function public.set_updated_at();
|
|
|
|
alter table public.assessment_shares enable row level security;
|
|
|
|
drop policy if exists "Allow users to insert own assessment shares" on public.assessment_shares;
|
|
create policy "Allow users to insert own assessment shares"
|
|
on public.assessment_shares
|
|
for insert
|
|
with check (
|
|
auth.uid() = user_id
|
|
and exists (
|
|
select 1
|
|
from public.assessments a
|
|
where a.id = assessment_shares.assessment_id
|
|
and a.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
drop policy if exists "Allow users to select own assessment shares" on public.assessment_shares;
|
|
create policy "Allow users to select own assessment shares"
|
|
on public.assessment_shares
|
|
for select
|
|
using (auth.uid() = user_id);
|
|
|
|
drop policy if exists "Allow users to update own assessment shares" on public.assessment_shares;
|
|
create policy "Allow users to update own assessment shares"
|
|
on public.assessment_shares
|
|
for update
|
|
using (auth.uid() = user_id)
|
|
with check (
|
|
auth.uid() = user_id
|
|
and exists (
|
|
select 1
|
|
from public.assessments a
|
|
where a.id = assessment_shares.assessment_id
|
|
and a.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
drop policy if exists "Allow users to delete own assessment shares" on public.assessment_shares;
|
|
create policy "Allow users to delete own assessment shares"
|
|
on public.assessment_shares
|
|
for delete
|
|
using (auth.uid() = user_id);
|
|
|
|
create index if not exists idx_assessment_shares_user_id
|
|
on public.assessment_shares(user_id);
|
|
|
|
create or replace function public.generate_share_token()
|
|
returns text
|
|
language sql
|
|
as $$
|
|
select replace(gen_random_uuid()::text, '-', '') || replace(gen_random_uuid()::text, '-', '');
|
|
$$;
|
|
|
|
create or replace function public.create_or_get_assessment_share(p_assessment_id uuid)
|
|
returns table (
|
|
assessment_id uuid,
|
|
share_token text
|
|
)
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
declare
|
|
v_user_id uuid;
|
|
begin
|
|
v_user_id := auth.uid();
|
|
|
|
if v_user_id is null then
|
|
raise exception 'Not authenticated';
|
|
end if;
|
|
|
|
if not exists (
|
|
select 1
|
|
from public.assessments a
|
|
where a.id = p_assessment_id
|
|
and a.user_id = v_user_id
|
|
) then
|
|
raise exception 'Assessment not found';
|
|
end if;
|
|
|
|
insert into public.assessment_shares (assessment_id, user_id, share_token)
|
|
values (p_assessment_id, v_user_id, public.generate_share_token())
|
|
on conflict on constraint assessment_shares_pkey do nothing;
|
|
|
|
return query
|
|
select s.assessment_id, s.share_token
|
|
from public.assessment_shares s
|
|
where s.assessment_id = p_assessment_id
|
|
and s.user_id = v_user_id
|
|
limit 1;
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.get_shared_assessment(p_share_token text)
|
|
returns table (
|
|
id uuid,
|
|
pat_type text,
|
|
datum date,
|
|
name text,
|
|
exercises jsonb
|
|
)
|
|
language sql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
select
|
|
a.id,
|
|
a.pat_type,
|
|
a.datum,
|
|
a.name,
|
|
a.exercises
|
|
from public.assessment_shares s
|
|
join public.assessments a on a.id = s.assessment_id
|
|
where s.share_token = p_share_token
|
|
limit 1;
|
|
$$;
|
|
|
|
revoke all on function public.create_or_get_assessment_share(uuid) from public;
|
|
grant execute on function public.create_or_get_assessment_share(uuid) to authenticated;
|
|
|
|
revoke all on function public.get_shared_assessment(text) from public;
|
|
grant execute on function public.get_shared_assessment(text) to anon, authenticated;
|