Recently, I worked on a project that uses Supabase, and it has a Profile feature for users that required me to extend Supabase’s auth.users table. We need to extend the auth.users table because Supabase doesn’t allow us to add new columns to the auth.users table.
Extending
Here’s how we extend the auth.users table. First, we need to create a new table to store the additional user data.
CREATE TABLE "user_profiles" (
"id" uuid PRIMARY KEY NOT NULL REFERENCES auth.users ON DELETE CASCADE,
"first_name" varchar(255),
"last_name" varchar(255),
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
ALTER TABLE "user_profiles" ENABLE ROW LEVEL SECURITY
Second, we need to create a trigger that will execute a function responsible for creating a user_profiles record whenever a new user is created.
CREATE FUNCTION public.handle_new_user()
RETURNS TRIGGER
SET search_path = ''
AS $$
BEGIN
INSERT INTO public.user_profiles (id, first_name, last_name)
VALUES (
new.id,
new.raw_user_meta_data->>'first_name',
new.raw_user_meta_data->>'last_name'
);
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_user();
raw_user_meta_data is metadata that we send when calling Supabase’s signup function via options.data. Below is an example of how I call the signup function with `options.data`:
const { error } = await supabase.auth.signUp({
email,
password,
options: {
data: {
first_name: "Faris",
last_name: "Perwira",
},
emailRedirectTo: `${currentDomain}/auth/confirm`,
},
});
What’s Next?
You might consider implementing the RLS definitions for user_profiles table, which will make the table more secure. An ORM like Drizzle to helps you generate the SQL scripts based on your schema.
Faris Perwira
Fullstack Developer