93 lines
3 KiB
PL/PgSQL
93 lines
3 KiB
PL/PgSQL
create or replace function public.set_updated_at()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
begin
|
|
new.updated_at = timezone('utc', now());
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
create table if not exists public.profiles (
|
|
id uuid primary key references auth.users (id) on delete cascade,
|
|
email text,
|
|
display_name text,
|
|
locale text not null default 'nl-NL',
|
|
timezone text not null default 'Europe/Amsterdam',
|
|
onboarding_completed boolean not null default false,
|
|
created_at timestamptz not null default timezone('utc', now()),
|
|
updated_at timestamptz not null default timezone('utc', now())
|
|
);
|
|
|
|
create table if not exists public.user_settings (
|
|
profile_id uuid primary key references public.profiles (id) on delete cascade,
|
|
morning_reminder_enabled boolean not null default false,
|
|
morning_reminder_time time,
|
|
reflection_reminder_enabled boolean not null default false,
|
|
show_energy_points boolean not null default true,
|
|
created_at timestamptz not null default timezone('utc', now()),
|
|
updated_at timestamptz not null default timezone('utc', now())
|
|
);
|
|
|
|
grant usage on schema public to authenticated;
|
|
grant select, insert, update on table public.profiles to authenticated;
|
|
grant select, insert, update on table public.user_settings to authenticated;
|
|
|
|
alter table public.profiles enable row level security;
|
|
alter table public.user_settings enable row level security;
|
|
|
|
drop trigger if exists set_profiles_updated_at on public.profiles;
|
|
create trigger set_profiles_updated_at
|
|
before update on public.profiles
|
|
for each row
|
|
execute function public.set_updated_at();
|
|
|
|
drop trigger if exists set_user_settings_updated_at on public.user_settings;
|
|
create trigger set_user_settings_updated_at
|
|
before update on public.user_settings
|
|
for each row
|
|
execute function public.set_updated_at();
|
|
|
|
drop policy if exists "profiles_select_own" on public.profiles;
|
|
create policy "profiles_select_own"
|
|
on public.profiles
|
|
for select
|
|
to authenticated
|
|
using ((select auth.uid()) = id);
|
|
|
|
drop policy if exists "profiles_insert_own" on public.profiles;
|
|
create policy "profiles_insert_own"
|
|
on public.profiles
|
|
for insert
|
|
to authenticated
|
|
with check ((select auth.uid()) = id);
|
|
|
|
drop policy if exists "profiles_update_own" on public.profiles;
|
|
create policy "profiles_update_own"
|
|
on public.profiles
|
|
for update
|
|
to authenticated
|
|
using ((select auth.uid()) = id)
|
|
with check ((select auth.uid()) = id);
|
|
|
|
drop policy if exists "user_settings_select_own" on public.user_settings;
|
|
create policy "user_settings_select_own"
|
|
on public.user_settings
|
|
for select
|
|
to authenticated
|
|
using ((select auth.uid()) = profile_id);
|
|
|
|
drop policy if exists "user_settings_insert_own" on public.user_settings;
|
|
create policy "user_settings_insert_own"
|
|
on public.user_settings
|
|
for insert
|
|
to authenticated
|
|
with check ((select auth.uid()) = profile_id);
|
|
|
|
drop policy if exists "user_settings_update_own" on public.user_settings;
|
|
create policy "user_settings_update_own"
|
|
on public.user_settings
|
|
for update
|
|
to authenticated
|
|
using ((select auth.uid()) = profile_id)
|
|
with check ((select auth.uid()) = profile_id);
|