Using Triggers on New User Signup in Supabase

Supabase allows you to create database triggers that execute automatically when a new user signs up. This guide demonstrates how to set up a trigger to add initial data (e.g., credits) for new users.

Prerequisites

A Supabase project with authentication enabled.
Familiarity with SQL and Supabase's auth.users table.

Steps to Create a Trigger for New User Signup

1. Create a Related Table

Define a table to store additional data for each user. In this example, we create a user_credits table to track the user's credits.

CREATE TABLE user_credits (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID UNIQUE NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    credits INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);

2. Create a Trigger Function

Define a PostgreSQL function that runs when a new user is added to the auth.users table. This function ensures each new user gets an entry in the user_credits table with default credits.

CREATE OR REPLACE FUNCTION add_initial_credits()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- Ensure the user ID is valid and prevent duplicate credit assignment
  IF NOT EXISTS (
    SELECT 1 FROM public.user_credits WHERE user_id = NEW.id
  ) THEN
    INSERT INTO public.user_credits (user_id, credits)
    VALUES (NEW.id, 100)
    ON CONFLICT (user_id) DO NOTHING;
  END IF;

  RETURN NEW;
END;
$$;

Key Points:

SECURITY DEFINER: Ensures the function runs with elevated permissions of its owner.
Default credits are set to 100 in the INSERT statement.

3. Create the Trigger

Set up a trigger to invoke the add_initial_credits function whenever a new row is inserted into the auth.users table.

DROP TRIGGER IF EXISTS on_user_created ON auth.users;

CREATE TRIGGER on_user_created
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION add_initial_credits();

Testing the Setup

Sign Up a New User: Use Supabase's authentication system to create a new user (email-password or external provider).
Verify the user_credits Table: Check if the new user has an entry in the user_credits table:
```
SELECT * FROM public.user_credits;
```

Debugging Tips

Check Supabase Logs: If the trigger doesn't work as expected, view the logs in the Supabase dashboard under Logs > Database.
Add Debug Messages: Add a RAISE NOTICE statement to the trigger function for debugging:
```
RAISE NOTICE 'Trigger executed for user ID: %, Role: %', NEW.id, current_user;
```
Verify Permissions: Ensure the authenticated or service_role roles have appropriate permissions on the user_credits table:
```
GRANT INSERT, SELECT, UPDATE, DELETE ON public.user_credits TO authenticated, service_role;
```

Common Issues

Permission Denied: Use SECURITY DEFINER in the function and ensure the function owner has the required permissions.
Conflict Errors: Add a UNIQUE constraint to the user_id column in the user_credits table to use the ON CONFLICT clause.
RLS Blocking Inserts: Disable Row-Level Security (RLS) on the user_credits table, or define appropriate RLS policies.

Conclusion

Using triggers in Supabase, you can automate actions like assigning default data when new users sign up. This setup is flexible and can be adapted for various use cases, such as initializing user profiles or tracking account details.

vhsu/supabasenewusertrigger.md