trigger bug on create new public user

[carlomigueldy]

Expected

When a new user is created by signing up by any method, then they are known to be as "manager" so a trigger function will attach a role to them.

While an invited user is known to be a "scholar", so we check on auth.users if the invited_at attribute is not NULL. If it is not NULL then we can confirm they are a scholar and belongs to that manager who invited them.

Details

I wrote a trigger function listening to insertions for auth.users table

-- trigger: handle new registered user
CREATE OR REPLACE FUNCTION public.handle_new_auth_user()
RETURNS TRIGGER AS $$
BEGIN 
  INSERT INTO public.users (id, email) VALUES (new.id::UUID, new.email::TEXT);

  return new;
END
$$
LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE PROCEDURE public.handle_new_auth_user();

This works.

But this doesn't work and keeps returning status 500 every time a new user creates an account.

-- trigger: handle new public user created by auth.users trigger
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN 
  INSERT INTO public.teams (owner_id) VALUES (new.id::UUID); 

  -- todo: needs to get fixed
  -- scholar
  IF EXISTS(SELECT id FROM auth.users WHERE id = new.id AND invited_at IS NOT NULL) THEN
    INSERT INTO public.user_role (user_id, role_id) VALUES (
      new.id::UUID, 
      (SELECT id FROM public.roles WHERE name = 'scholar' LIMIT 1)::UUID
    );
  END IF;

  -- manager
  IF EXISTS(SELECT id FROM auth.users WHERE id = new.id AND invited_at IS NULL) THEN
    INSERT INTO public.user_role (user_id, role_id) VALUES (
      new.id::UUID, 
      (SELECT id FROM public.roles WHERE name = 'manager' LIMIT 1)::UUID
    );
    INSERT INTO public.teams (owner_id) VALUES (new.id::UUID); 
  END IF;

  return new;
END
$$
LANGUAGE plpgsql SECURITY DEFINER;