Extending Supabase Auth Users Table

Faris Perwira — Mon, 07 Jul 2025 11:36:41 +0000

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. Extending means creating a new table that references the auth.users table. Why would we need to extend the auth.users table? Well… 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 TYPE "public"."user_profile_role" AS ENUM('admin', 'user');
CREATE TABLE "user_profiles" (
 "id" uuid PRIMARY KEY NOT NULL REFERENCES auth.users ON DELETE CASCADE,
 "role" "user_profile_role",
 "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, role, first_name, last_name)
    VALUES (
      new.id, 
      CAST(new.raw_user_meta_data->>'role' AS public.user_profile_role), 
      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. Below is an example of how we can call the signup function:

const ROLE = {
  ADMIN: "admin",
  USER: "user",
};

const { error } = await supabase.auth.signUp({
  email,
  password,
  options: {
    data: {
      role: ROLE.USER,
      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. Writing all the SQL scripts for RLS definitions and table creation can be a lot of extra work. You might consider using an ORM like Drizzle to generate the SQL scripts based on your schema.

References:

Supabase — Managing User Data (https://supabase.com/docs/guides/auth/managing-user-data?queryGroups=language&language=js)

DEV Community: Faris Perwira

Extending Supabase Auth Users Table

Extending

What’s Next?