families.sql

families.sql

create type family_user_role as enum (
  'admin',
  'member'
);

create table public.families (
  id uuid default uuid_generate_v4 () not null primary key,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
  name text not null
);

create trigger handle_updated_at
  before update on public.families for each row
  execute procedure moddatetime (updated_at);

create table families_users (
  family_id uuid references families (id) on delete cascade not null,
  user_id uuid references auth.users (id) on delete cascade not null,
  user_role family_user_role not null,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
  primary key (family_id, user_id)
);

create trigger handle_updated_at
  before update on public.families_users for each row
  execute procedure moddatetime (updated_at);

create function insert_family_user ()
  returns trigger
  as $$
begin
  insert into public.families_users (
    family_id,
    user_id,
    user_role)
  values (
    new.id,
    auth.uid (),
    'admin' ::family_user_role);
  return new;
end;
$$
language plpgsql
security definer;

create trigger on_family_inserted
  after insert on public.families for each row
  execute procedure public.insert_family_user ();

alter table public.families enable row level security;

alter table public.families_users enable row level security;

create or replace function get_families_for_user ()
  returns setof uuid
  language sql
  security definer
  set search_path = public stable
  as $$
  select
    family_id
  from
    public.families_users
  where
    user_id = auth.uid ()
$$;

create policy "Allow family insert by logged users" on public.families
  for insert to authenticated
    with check (
true);

create policy "User can select only families he is member of." on public.families
  for select to authenticated
    using (id in (
      select
        get_families_for_user ()));

create policy "Allow families users insert by owner only." on public.families_users
  for insert to authenticated
    with check (
family_id in (
      select
        family_id
      from
        families_users
      where
        user_id = auth.uid () and user_role = 'admin'::family_user_role));

create policy "Allow families users select by members only." on public.families_users
  for select to authenticated
    using (family_id in (
      select
        get_families_for_user ()));