fungsi supabase (bukan tepi)

CREATE FUNCTION my_function() RETURNS int AS $$
BEGIN
    RETURN 42;
END;
$$ LANGUAGE sql;

SELECT my_function();

SELECT
    proname AS function_name,
    prokind AS function_type
FROM pg_proc
WHERE proname = 'my_function';

DROP FUNCTION my_function();

--  list all the supabase functions
SELECT
    proname AS function_name,
    prokind AS function_type
FROM pg_proc;

--  filter for the session supabase functions function
SELECT
    proname AS function_name,
    prokind AS function_type
FROM pg_proc
WHERE proname ILIKE '%session%';

--  selects the curremt jwt
select auth.jwt()

-- select what role is callig the function (anon or authenticated)
select auth.role();

-- select the session user
select session_use;

--   create the user_profiles table
CREATE TABLE user_profiles (
  id uuid PRIMARY KEY,
  FOREIGN KEY (id) REFERENCES auth.users(id),
  name text,
  email text
);

-- create a function that returns a trigger on auth.users
CREATE 
OR REPLACE FUNCTION public.create_public_user_profile_table() 
RETURNS TRIGGER AS $$ 
BEGIN INSERT INTO public.user_profiles (id,name,email) 
VALUES 
  (
    NEW.id,
    NEW.raw_user_meta_data ->> 'name',
    NEW.raw_user_meta_data ->> 'email'
    -- other fields accessible here 
--     NEW.raw_user_meta_data ->> 'name',
-- NEW.raw_user_meta_data ->> 'picture',

);
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- create the trigger that executes the function on every new user rowcteation(signup)
CREATE TRIGGER create_public_user_profiles_trigger 
AFTER INSERT ON auth.users FOR EACH ROW WHEN (
        NEW.raw_user_meta_data IS NOT NULL
    )
EXECUTE FUNCTION public.create_public_user_profile_table ();

let { data: user_profiles, error } = await supabase
  .from('user_profiles')
  .select('*')

-- Custom types
create type public.app_permission as enum ('channels.delete', 'channels.update', 'messages.update', 'messages.delete');
create type public.app_role as enum ('admin', 'moderator');

-- USER ROLES
create table public.user_roles (
  id        bigint generated by default as identity primary key,
  user_id   uuid references public.users on delete cascade not null,
  role      app_role not null,
  unique (user_id, role)
);
comment on table public.user_roles is 'Application roles for each user.';

-- ROLE PERMISSIONS
create table public.role_permissions (
  id           bigint generated by default as identity primary key,
  role         app_role not null,
  permission   app_permission not null,
  unique (role, permission)
);
comment on table public.role_permissions is 'Application permissions for each role.';

-- Create the auth hook function
create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
stable
as $$
  declare
    claims jsonb;
    user_role public.app_role;
  begin
    -- Fetch the user role in the user_roles table
    select role into user_role from public.user_roles where user_id = (event->>'user_id')::uuid;

    claims := event->'claims';

    if user_role is not null then
      -- Set the claim
      claims := jsonb_set(claims, '{user_role}', to_jsonb(user_role));
    else
      claims := jsonb_set(claims, '{user_role}', 'null');
    end if;

    -- Update the 'claims' object in the original event
    event := jsonb_set(event, '{claims}', claims);

    -- Return the modified or original event
    return event;
  end;
$$;

grant usage on schema public to supabase_auth_admin;

grant execute
  on function public.custom_access_token_hook
  to supabase_auth_admin;

revoke execute
  on function public.custom_access_token_hook
  from authenticated, anon, public;

grant all
  on table public.user_roles
to supabase_auth_admin;

revoke all
  on table public.user_roles
  from authenticated, anon, public;

create policy "Allow auth admin to read user roles" ON public.user_roles
as permissive for select
to supabase_auth_admin
using (true)

create or replace function public.authorize(
  requested_permission app_permission
)
returns boolean as $$
declare
  bind_permissions int;
  user_role public.app_role;
begin
  -- Fetch user role once and store it to reduce number of calls
  select (auth.jwt() ->> 'user_role')::public.app_role into user_role;

  select count(*)
  into bind_permissions
  from public.role_permissions
  where role_permissions.permission = requested_permission
    and role_permissions.role = user_role;

  return bind_permissions > 0;
end;
$$ language plpgsql stable security definer set search_path = '';

--  example RLS policies
create policy "Allow authorized delete access" on public.channels for delete using ( (SELECT authorize('channels.delete')) );
create policy "Allow authorized delete access" on public.messages for delete using ( (SELECT authorize('messages.delete')) );

create or replace function match_documents (
  query_embedding vector(384),
  match_threshold float,
  match_count int
)
returns table (
  id bigint,
  title text,
  body text,
  similarity float
)
language sql stable
as $$
  select
    documents.id,
    documents.title,
    documents.body,
    1 - (documents.embedding <=> query_embedding) as similarity
  from documents
  where 1 - (documents.embedding <=> query_embedding) > match_threshold
  order by (documents.embedding <=> query_embedding) asc
  limit match_count;
$$;

const { data: documents } = await supabaseClient.rpc('match_documents', {
  query_embedding: embedding, // Pass the embedding you want to compare
  match_threshold: 0.78, // Choose an appropriate threshold for your data
  match_count: 10, // Choose the number of matches
})

-- check if user with roles authenticated or anon submitted an updatedat column and replace it with the current time , if not (thta is an admin) allow it
CREATE
or REPLACE function public.omit_updated__at () returns trigger as 
$$ BEGIN 
IF auth.role() IS NOT NULL AND auth.role() IN ('anon', 'authenticated') 
THEN NEW.updated_at = now();
END IF; 
RETURN NEW; 
END; $$ language plpgsql;