53 lines
1.6 KiB
PL/PgSQL
53 lines
1.6 KiB
PL/PgSQL
create schema if not exists confidence_engine;
|
|
|
|
create table confidence_engine.investigations (
|
|
id uuid primary key,
|
|
user_id uuid not null references auth.users(id) on delete cascade,
|
|
snapshot jsonb not null,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create index investigations_user_id_idx
|
|
on confidence_engine.investigations (user_id);
|
|
|
|
create function confidence_engine.set_updated_at()
|
|
returns trigger
|
|
language plpgsql
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
new.updated_at = now();
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
create trigger investigations_set_updated_at
|
|
before update on confidence_engine.investigations
|
|
for each row execute function confidence_engine.set_updated_at();
|
|
|
|
alter table confidence_engine.investigations enable row level security;
|
|
|
|
grant usage on schema confidence_engine to authenticated;
|
|
grant select, insert, update, delete on confidence_engine.investigations to authenticated;
|
|
|
|
create policy "Users can select their own investigations"
|
|
on confidence_engine.investigations
|
|
for select to authenticated
|
|
using (user_id = auth.uid());
|
|
|
|
create policy "Users can insert their own investigations"
|
|
on confidence_engine.investigations
|
|
for insert to authenticated
|
|
with check (user_id = auth.uid());
|
|
|
|
create policy "Users can update their own investigations"
|
|
on confidence_engine.investigations
|
|
for update to authenticated
|
|
using (user_id = auth.uid())
|
|
with check (user_id = auth.uid());
|
|
|
|
create policy "Users can delete their own investigations"
|
|
on confidence_engine.investigations
|
|
for delete to authenticated
|
|
using (user_id = auth.uid()); |