Use auth hooks

create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
as $$
   declare
    user_roles text[];
    claims jsonb;
  begin
    -- Get user roles new
    select array_agg(rt.role_type)
    into user_roles
    from public.roles r
    join public.roles_type rt on r.role_id = rt.id
    where r.user_id = event->>'user_id';

    -- Proceed only if the user have roles
    if user_roles is not null then
      claims := event->'claims';

      -- Check if 'user_metadata' exists in claims
      if jsonb_typeof(claims->'user_metadata') is null then
        -- If 'user_metadata' does not exist, create an empty object
        claims := jsonb_set(claims, '{user_metadata}', '{}');
      end if;

      -- Set a claim of roles
      claims := jsonb_set(claims, '{user_metadata, roles}', to_jsonb(user_roles));

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

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

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;

create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
as $$
   declare
    user_roles text[];
    claims jsonb;
  begin
    -- Get user roles new
    select array_agg(rt.role_type)
    into user_roles
    from public.roles r
    join public.roles_type rt on r.role_id = rt.id
    where r.user_id = event->>'user_id';

    -- Proceed only if the user have roles
    if user_roles is not null then
      claims := event->'claims';

      -- Check if 'user_metadata' exists in claims
      if jsonb_typeof(claims->'user_metadata') is null then
        -- If 'user_metadata' does not exist, create an empty object
        claims := jsonb_set(claims, '{user_metadata}', '{}');
      end if;

      -- Set a claim of roles
      claims := jsonb_set(claims, '{user_metadata, roles}', to_jsonb(user_roles));

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

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

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;