triggers

so i had some business logic I needed to happen when a new user signed up for my application. I did something like this:

// Now push the user into the user_profiles table as well and link it to the user UUID
if (data.user) {
	await supabase.from('user_profiles').insert([
		{
			id: data.user.id,
			email: email,
			first_name: firstName,
			last_name: lastName
		}
	]);
}

turns out, this is both not how supabase works (you can’t get back the data.user object) and there are easier ways to deal with this. specifically, triggers.

Here’s how I did the same thing with triggers:

-- 1. Drop the existing trigger and function
DROP TRIGGER IF EXISTS trg_after_auth_user_insert ON auth.users;
DROP FUNCTION IF EXISTS public.after_auth_user_insert;

-- 2. Create the function with SECURITY DEFINER

CREATE OR REPLACE FUNCTION public.after_auth_user_insert()
RETURNS trigger AS $$
DECLARE
	first_name text;
	last_name text;
	email text;

BEGIN
-- Extract first_name and last_name and email from the JSON metadata of the user object in auth.user
	first_name := NEW.raw_user_meta_data->>'firstName';
	last_name := NEW.raw_user_meta_data->>'lastName';
	email := NEW.raw_user_meta_data->>'email';

	-- Insert a new row into the user_profiles table with the extracted data.
	INSERT INTO public.user_profiles (id, first_name, last_name, email)
	VALUES (NEW.id, first_name, last_name, email);
	
	-- Clear the metadata in auth.users by updating the row.
	
	UPDATE auth.users
	SET raw_user_meta_data = '{}'::jsonb
	WHERE id = NEW.id;

  

	RETURN NEW;
END;

$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 3. Ensure the function is owned by postgres
ALTER FUNCTION public.after_auth_user_insert() OWNER TO postgres;

-- 4. Recreate the trigger
CREATE TRIGGER trg_after_auth_user_insert
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.after_auth_user_insert();

this trigger handles everything I need on the database without any of the potential downsides (like bad business logic).

what Im learning is that every time I try to reinvent wheels, someone’s already made the wheels. That’s a really important lesson I’m learning

okay also mdsvex does not format code blocks nice. that’s a tomorrow issue. see you then

---

5 - 03/01/2025