Step-by-Step: Setting Up Database Trigger for auth.users Webhooks¶

This guide shows you how to create a database trigger with SECURITY DEFINER to monitor auth.users table and call your webhook endpoint, since Supabase prevents creating webhooks on the auth schema via the Dashboard UI.

Prerequisites¶

Access to Supabase Dashboard
SQL Editor access in Supabase
Your webhook endpoint URL (e.g., https://ai-assisted-pcb-stackup-generator.onrender.com/api/webhooks/auth)
(Optional) Webhook secret for security

Method 1: Direct HTTP Webhook Call (Recommended)¶

This method directly calls your webhook endpoint from the database trigger using PostgreSQL's http extension.

Step 1: Enable Required Extensions¶

Go to Supabase Dashboard → Your Project
Click "SQL Editor" in the left sidebar
Click "New query"
Run this SQL to enable the HTTP extension:

-- Enable pg_net extension for HTTP requests
CREATE EXTENSION IF NOT EXISTS pg_net;

Note: If pg_net is not available, you can use http extension instead (see Alternative below).

Step 2: Create the Trigger Function with SECURITY DEFINER¶

In the SQL Editor, run this SQL:

-- Create function to call webhook on auth.users events
-- SECURITY DEFINER allows the function to run with elevated privileges
CREATE OR REPLACE FUNCTION notify_auth_webhook()
RETURNS TRIGGER
SECURITY DEFINER  -- This is the key: runs as admin, not as the user
SET search_path = public
LANGUAGE plpgsql
AS $$
DECLARE
  webhook_url TEXT := 'https://ai-assisted-pcb-stackup-generator.onrender.com/api/webhooks/auth';
  webhook_secret TEXT := 'your-webhook-secret-here';  -- Optional: set this or leave empty
  payload JSONB;
  response_status INT;
BEGIN
  -- Build webhook payload matching your backend's expected format
  IF TG_OP = 'INSERT' THEN
    -- New user signup
    payload := jsonb_build_object(
      'type', 'INSERT',
      'table', 'auth.users',
      'record', jsonb_build_object(
        'id', NEW.id,
        'email', NEW.email,
        'created_at', NEW.created_at,
        'identities', NEW.raw_user_meta_data->'identities'  -- May need adjustment based on your schema
      )
    );
  ELSIF TG_OP = 'UPDATE' THEN
    -- User update (could be login if last_sign_in_at changed)
    payload := jsonb_build_object(
      'type', 'UPDATE',
      'table', 'auth.users',
      'record', jsonb_build_object(
        'id', NEW.id,
        'email', NEW.email,
        'last_sign_in_at', NEW.last_sign_in_at,
        'updated_at', NEW.updated_at
      ),
      'old_record', jsonb_build_object(
        'id', OLD.id,
        'last_sign_in_at', OLD.last_sign_in_at
      )
    );
  END IF;

  -- Call webhook using pg_net
  -- Note: pg_net runs asynchronously, so we don't wait for response
  PERFORM
    net.http_post(
      url := webhook_url,
      headers := jsonb_build_object(
        'Content-Type', 'application/json',
        'x-supabase-signature', COALESCE(webhook_secret, '')
      ),
      body := payload::text
    );

  RETURN NEW;
END;
$$;

Important Notes: - Replace webhook_url with your actual webhook endpoint URL - Replace webhook_secret with your secret (or set it to empty string '' if not using) - SECURITY DEFINER is required to allow the function to make HTTP requests - SET search_path = public prevents security issues

Step 3: Create the Trigger¶

Run this SQL to create the trigger on auth.users:

-- Create trigger on auth.users table
CREATE TRIGGER auth_users_webhook_trigger
  AFTER INSERT OR UPDATE ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION notify_auth_webhook();

Step 4: Test the Trigger¶

Create a test user in your app (sign up with a new email)
Check Supabase logs:
Go to Database → Logs → Postgres Logs
Look for any errors from the trigger
Check your webhook endpoint logs:
Check your backend logs (Render, Railway, etc.)
Should see webhook requests coming in
Check webhook delivery:
Your backend should log the webhook receipt
Check email inbox for welcome email

Step 5: Verify It's Working¶

Run this query to see recent trigger executions:

-- Check if trigger exists
SELECT 
  trigger_name,
  event_manipulation,
  event_object_table,
  action_statement
FROM information_schema.triggers
WHERE event_object_table = 'users'
  AND event_object_schema = 'auth';

Method 2: Alternative Using `http` Extension (If pg_net Not Available)¶

If pg_net extension is not available, you can use the http extension instead:

Step 1: Enable http Extension¶

CREATE EXTENSION IF NOT EXISTS http;

Step 2: Create Function with http Extension¶

CREATE OR REPLACE FUNCTION notify_auth_webhook()
RETURNS TRIGGER
SECURITY DEFINER
SET search_path = public
LANGUAGE plpgsql
AS $$
DECLARE
  webhook_url TEXT := 'https://ai-assisted-pcb-stackup-generator.onrender.com/api/webhooks/auth';
  webhook_secret TEXT := 'your-webhook-secret-here';
  payload JSONB;
  response http_response;
BEGIN
  IF TG_OP = 'INSERT' THEN
    payload := jsonb_build_object(
      'type', 'INSERT',
      'table', 'auth.users',
      'record', jsonb_build_object(
        'id', NEW.id,
        'email', NEW.email,
        'created_at', NEW.created_at
      )
    );
  ELSIF TG_OP = 'UPDATE' THEN
    payload := jsonb_build_object(
      'type', 'UPDATE',
      'table', 'auth.users',
      'record', jsonb_build_object(
        'id', NEW.id,
        'email', NEW.email,
        'last_sign_in_at', NEW.last_sign_in_at
      ),
      'old_record', jsonb_build_object(
        'id', OLD.id,
        'last_sign_in_at', OLD.last_sign_in_at
      )
    );
  END IF;

  -- Call webhook using http extension
  SELECT * INTO response
  FROM http((
    'POST',
    webhook_url,
    ARRAY[
      http_header('Content-Type', 'application/json'),
      http_header('x-supabase-signature', COALESCE(webhook_secret, ''))
    ],
    'application/json',
    payload::text
  )::http_request);

  RETURN NEW;
END;
$$;

Then create the trigger (same as Step 3 above).

Method 3: Sync to Public `users` Table (Easier Alternative)¶

Instead of calling webhooks directly from auth.users, you can sync data to a public users table and use standard webhooks:

Step 1: Create Public `users` Table (if not exists)¶

-- Create public users table to mirror auth.users
CREATE TABLE IF NOT EXISTS public.users (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  last_sign_in_at TIMESTAMPTZ,
  provider TEXT
);

Step 2: Create Sync Function¶

CREATE OR REPLACE FUNCTION sync_auth_user_to_public()
RETURNS TRIGGER
SECURITY DEFINER
SET search_path = public
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO public.users (id, email, created_at, updated_at)
    VALUES (NEW.id, NEW.email, NEW.created_at, NOW())
    ON CONFLICT (id) DO UPDATE
    SET email = EXCLUDED.email,
        updated_at = NOW();
  ELSIF TG_OP = 'UPDATE' THEN
    UPDATE public.users
    SET email = NEW.email,
        last_sign_in_at = NEW.last_sign_in_at,
        updated_at = NOW()
    WHERE id = NEW.id;
  END IF;

  RETURN NEW;
END;
$$;

Step 3: Create Trigger¶

CREATE TRIGGER sync_auth_to_public_users
  AFTER INSERT OR UPDATE ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION sync_auth_user_to_public();

Step 4: Set Up Standard Webhook (via Dashboard)¶

Now you can use the Dashboard UI to create a webhook on the public users table:

Go to Database → Webhooks
Create new webhook on users table (public schema)
Configure as normal (no SECURITY DEFINER needed)

This is the easiest approach and what we recommend!

Troubleshooting¶

Error: "permission denied for schema auth"¶

Solution: Make sure your function has SECURITY DEFINER and SET search_path = public:

CREATE OR REPLACE FUNCTION notify_auth_webhook()
RETURNS TRIGGER
SECURITY DEFINER  -- Must have this
SET search_path = public  -- Must have this
LANGUAGE plpgsql
AS $$

Error: "extension pg_net does not exist"¶

Solutions: 1. Try enabling it: CREATE EXTENSION IF NOT EXISTS pg_net; 2. If that fails, use Method 2 with http extension instead 3. Or use Method 3 (sync to public table)

Error: "function http() does not exist"¶

Solution: Enable the http extension:

CREATE EXTENSION IF NOT EXISTS http;

Webhook Not Being Called¶

Check trigger exists:

SELECT * FROM information_schema.triggers 
WHERE event_object_table = 'users' AND event_object_schema = 'auth';

Check function exists:

SELECT proname FROM pg_proc WHERE proname = 'notify_auth_webhook';

Test function manually (be careful - this will create a test user):

-- Don't run this in production! Just for testing
-- Create a test user and see if trigger fires

Check Postgres logs in Supabase Dashboard → Database → Logs

Check your webhook endpoint is accessible:

curl -X POST https://your-webhook-url/api/webhooks/auth \
  -H "Content-Type: application/json" \
  -d '{"type":"INSERT","table":"auth.users","record":{"id":"test","email":"test@test.com"}}'

Function Runs But Webhook Not Receiving¶

Check webhook URL is correct and accessible
Check webhook secret matches (if using)
Check backend logs for incoming requests
Verify payload format matches what your backend expects

Security Considerations¶

SECURITY DEFINER: Functions with SECURITY DEFINER run with elevated privileges. Only use trusted code.
Webhook Secret: Always use a webhook secret in production:
```
webhook_secret TEXT := 'your-strong-random-secret';
```
Search Path: Always set SET search_path = public to prevent search path attacks.
HTTPS Only: Use HTTPS URLs for webhooks in production.

Recommended Approach¶

For most users, we recommend Method 3 (sync to public table): - ✅ Easier to set up - ✅ Can use Dashboard UI for webhooks - ✅ No need for SECURITY DEFINER complexity - ✅ Easier to debug and maintain - ✅ Standard Supabase pattern

Use Method 1 or 2 only if: - You need direct auth.users monitoring - You can't create a public users table - You have specific requirements for direct webhook calls

Next Steps¶

After setting up the trigger:

Test with a real signup - Create a test account
Verify webhook delivery - Check backend logs
Check email delivery - Verify welcome emails are sent
Monitor for errors - Check Supabase logs regularly
Set up webhook secret - Add security in production

Quick Reference¶

Check if trigger exists:

SELECT trigger_name, event_manipulation, event_object_table
FROM information_schema.triggers
WHERE event_object_table = 'users' AND event_object_schema = 'auth';

Drop trigger (if needed):

DROP TRIGGER IF EXISTS auth_users_webhook_trigger ON auth.users;

Drop function (if needed):

DROP FUNCTION IF EXISTS notify_auth_webhook();