Database Configuration

Tables

There are currently 4 tables as of 1/5/2023

• profiles
• posts
• peer_reviews
• api_calls

Row Level Security

profiles table

CREATE POLICY "Admin users can do CRUD on profiles" ON "public"."profiles"
AS PERMISSIVE FOR ALL
TO authenticated
USING (is_admin(auth.uid()))
WITH CHECK (is_admin(auth.uid()));

CREATE POLICY "Enable read access for all users on profiles" ON "public"."profiles"
AS PERMISSIVE FOR SELECT
TO anon, authenticated
USING (true);

posts table

CREATE POLICY "Admin users can do CRUD on posts" ON "public"."posts"
AS PERMISSIVE FOR ALL
TO authenticated
USING (is_admin(auth.uid()))
WITH CHECK (is_admin(auth.uid()));

CREATE POLICY "Enable create post for authenticated users only on posts" ON "public"."posts"
AS PERMISSIVE FOR INSERT
TO authenticated
WITH CHECK (true);

CREATE POLICY "Enable read access for all users on posts" ON "public"."posts"
AS PERMISSIVE FOR SELECT
TO anon,authenticated
USING (true);

CREATE POLICY "Enable users to do CRUD on post that they own on posts" ON "public"."posts"
AS PERMISSIVE FOR ALL
TO authenticated
USING ((uid() = author))
WITH CHECK ((uid() = author));

peer_reviews table

CREATE POLICY "users can create peer_reviews but not for themeselves" ON "public"."peer_reviews"
AS PERMISSIVE FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = reviewer and auth.uid() != reviewee);

CREATE POLICY "Admin users can do CRUD on peer_reviews" ON "public"."peer_reviews"
AS PERMISSIVE FOR ALL
TO authenticated
USING (is_admin(auth.uid()))
WITH CHECK (is_admin(auth.uid()));

api_calls

CREATE POLICY "Enable insert for anon and authenticated users on api_calls" ON "public"."api_calls"
AS PERMISSIVE FOR INSERT
TO authenticated
WITH CHECK (true);

CREATE POLICY "Admin users can do CRUD on api_calls" ON "public"."api_calls"
AS PERMISSIVE FOR ALL
TO authenticated
USING (is_admin(auth.uid()))
WITH CHECK (is_admin(auth.uid()));

Instructions to create these table is in the installion section of the documentation

Functions

handle_new_user

this is function automatically triggered when a user is create the it inserts user data's in the profile section with the default role of user

create function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, email, role)
  values (new.id, new.email,'user');
  return new;
end;

is_admin

is a checking function that gets user_id and checks if that user is an admin

create function public.is_admin(user_id uuid) 
returns boolean
language plpgsql security definer
as $$
begin
  return exists (select 1 from profiles where profiles.id = user_id and profiles.role = 'admin');
end
$$;

employee_review_keyword_analysis

is a simple query that returns peer_reviews data based on the patter given by the client.

create or replace function public.employee_review_keyword_analysis(pattern text) 
returns setof peer_reviews  as $$
  SELECT *
  FROM peer_reviews
  WHERE evaluation::text ~* pattern;
$$ language sql;

delete_post_image

When a post is deleted the image relative to that post

create or replace function public.delete_post_image() 
returns trigger as $$
declare
  myurl varchar := '{YOUR SUPABASE PROJECT URL HERE}';
  service_role_key varchar := '{YOUR SERVICE ROLE KEY HERE}';
  url varchar := myurl||'/storage/v1/object/'||'post-images'||'/'||old.image_path;
begin
perform (select status FROM http((
          'DELETE',
            url,
           ARRAY[http_header('authorization','Bearer '||service_role_key)],
           NULL,
           NULL
        )::http_request));
return old;
end;
$$ security definer language plpgsql;

Triggers

On Post Delete

This triggers a function call to delete_post_image after a delete in the posts table occurs that will delete the image relative to that post

create trigger on_post_delete
  after delete on public.posts
  for each row execute procedure public.delete_post_image();

This trigger function is for when a user is created the handle_new_user function will be invoked and will input userdata in the profiles table.

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();