Method 3: Sync auth.users to Public Table - Step-by-Step Guide¶

This is the easiest and recommended approach. We'll sync auth.users to a public users table, then use standard Dashboard webhooks (no complex SQL needed).

Overview¶

What we're doing: 1. Create a public users table that mirrors auth.users 2. Create a database trigger that automatically syncs data from auth.users → users 3. Set up a webhook via Dashboard UI on the public users table 4. Done! No complex SECURITY DEFINER webhook calls needed.

Why this is better: - ✅ Easier to set up - ✅ Can use Dashboard UI for webhooks - ✅ Easier to debug - ✅ Standard Supabase pattern - ✅ No HTTP extension needed

Step 1: Access Supabase SQL Editor¶

Go to https://app.supabase.com
Log in to your account
Select your project
In the left sidebar, click "SQL Editor"
Click "New query" (top right)

You should see a blank SQL editor window.

Step 2: Create the Public `users` Table¶

Copy and paste this SQL into the SQL Editor:

-- Create public users table to mirror auth.users
-- This table will automatically sync with auth.users via trigger
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,
  raw_user_meta_data JSONB,
  user_metadata JSONB
);

-- Create index on email for faster lookups
CREATE INDEX IF NOT EXISTS idx_users_email ON public.users(email);

-- Create index on created_at for sorting
CREATE INDEX IF NOT EXISTS idx_users_created_at ON public.users(created_at);

-- Enable Row Level Security (RLS) for security
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

-- Policy: Allow service role (webhooks, triggers) full access
CREATE POLICY "Service role can manage users"
  ON public.users
  FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

-- Policy: Allow trigger function (SECURITY DEFINER) to insert
-- IMPORTANT: Separate policies for INSERT and UPDATE to avoid overlap
-- This is needed for the sync trigger to work
CREATE POLICY "Trigger function can insert users"
  ON public.users
  FOR INSERT
  TO authenticated
  WITH CHECK (true);

-- Policy: Allow trigger function (SECURITY DEFINER) to update
CREATE POLICY "Trigger function can update users"
  ON public.users
  FOR UPDATE
  TO authenticated
  USING (true)
  WITH CHECK (true);

-- Policy: Users can read their own data (optional - for app use)
-- PERFORMANCE: Using (select auth.uid()) evaluates once per query, not per row
CREATE POLICY "Users can read own data"
  ON public.users
  FOR SELECT
  TO authenticated
  USING ((select auth.uid()) = id);

Click "Run" (or press Ctrl+Enter / Cmd+Enter)

Expected result: You should see "Success. No rows returned"

What this does: - Creates a users table in the public schema (not auth schema) - Links to auth.users via foreign key - Includes fields we need for webhooks (email, created_at, etc.) - Enables Row Level Security (RLS) to secure the table - Creates policies to allow: - Service role (webhooks) to manage users - Trigger function to sync users - Authenticated users to read their own data

⚠️ Pro-Tip: The Foreign Key Constraint¶

Important: Notice the id column definition:

id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE

Why this matters: - ✅ UUID - Must match the data type of auth.users.id (UUID) - ✅ REFERENCES auth.users(id) - Creates a foreign key relationship - ✅ ON DELETE CASCADE - Critical! Automatically deletes the user's record from public.users when they're deleted from auth.users

What ON DELETE CASCADE does: - If you delete a user from Supabase Dashboard → Authentication → Users - Their record in public.users is automatically deleted - Any related data in your app (like PCB generator history, saved designs, etc.) can also be cleaned up if you set up cascading deletes on those tables - Prevents orphaned records - no leftover user data after account deletion

Without ON DELETE CASCADE: - Deleting from auth.users would fail if the user still exists in public.users - You'd have to manually delete from both tables - Risk of orphaned records and data inconsistencies

This is especially important for: - GDPR compliance (right to be forgotten) - Data cleanup when users delete accounts - Maintaining referential integrity across your database

Step 3: Create the Sync Function¶

Now we'll create a function that automatically syncs data from auth.users to public.users.

Copy and paste this SQL:

-- Function to sync auth.users to public.users
-- SECURITY DEFINER allows this to run with admin privileges
CREATE OR REPLACE FUNCTION sync_auth_user_to_public()
RETURNS TRIGGER
SECURITY DEFINER  -- Required: runs as admin to access auth.users
SET search_path = public  -- Security: only use public schema
LANGUAGE plpgsql
AS $$
DECLARE
  provider_name TEXT;
BEGIN
  -- Extract provider from identities (if available)
  -- auth.users stores provider info in raw_user_meta_data or identities
  provider_name := NULL;

  -- Try to get provider from various possible locations
  IF NEW.raw_user_meta_data IS NOT NULL THEN
    provider_name := NEW.raw_user_meta_data->>'provider';
  END IF;

  -- If INSERT (new user signup)
  IF TG_OP = 'INSERT' THEN
    INSERT INTO public.users (
      id,
      email,
      created_at,
      updated_at,
      last_sign_in_at,
      provider,
      raw_user_meta_data,
      user_metadata
    )
    VALUES (
      NEW.id,
      NEW.email,
      COALESCE(NEW.created_at, NOW()),
      NOW(),
      NEW.last_sign_in_at,
      provider_name,
      NEW.raw_user_meta_data,
      COALESCE(NEW.raw_user_meta_data, '{}'::jsonb)  -- Use raw_user_meta_data as fallback
    )
    ON CONFLICT (id) DO UPDATE
    SET 
      email = EXCLUDED.email,
      updated_at = NOW(),
      raw_user_meta_data = EXCLUDED.raw_user_meta_data,
      user_metadata = EXCLUDED.user_metadata;

  -- If UPDATE (user updated, could be login)
  ELSIF TG_OP = 'UPDATE' THEN
    UPDATE public.users
    SET 
      email = NEW.email,
      last_sign_in_at = NEW.last_sign_in_at,
      updated_at = NOW(),
      raw_user_meta_data = NEW.raw_user_meta_data,
      user_metadata = COALESCE(NEW.raw_user_meta_data, '{}'::jsonb)  -- Use raw_user_meta_data as fallback
    WHERE id = NEW.id;
  END IF;

  RETURN NEW;
END;
$$;

Click "Run"

Expected result: "Success. No rows returned"

What this does: - Creates a function that runs with admin privileges (SECURITY DEFINER) - Automatically syncs INSERT and UPDATE events from auth.users to public.users - Handles provider extraction from metadata

Step 4: Create the Trigger¶

Now we'll create a trigger that calls the sync function whenever auth.users changes.

Copy and paste this SQL:

-- Create trigger on auth.users to sync to public.users
CREATE TRIGGER sync_auth_to_public_users
  AFTER INSERT OR UPDATE ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION sync_auth_user_to_public();

Click "Run"

Expected result: "Success. No rows returned"

What this does: - Automatically runs sync_auth_user_to_public() function whenever: - A new user is created (INSERT) - A user is updated (UPDATE, including logins)

Step 5: Sync Existing Users (If Any)¶

If you already have users in auth.users, sync them now:

-- Sync existing users from auth.users to public.users
INSERT INTO public.users (
  id,
  email,
  created_at,
  updated_at,
  last_sign_in_at,
  provider,
  raw_user_meta_data,
  user_metadata
)
SELECT 
  id,
  email,
  created_at,
  NOW(),
  last_sign_in_at,
  COALESCE(raw_user_meta_data->>'provider', 'email'),
  raw_user_meta_data,
  user_metadata
FROM auth.users
ON CONFLICT (id) DO UPDATE
SET 
  email = EXCLUDED.email,
  updated_at = NOW(),
  last_sign_in_at = EXCLUDED.last_sign_in_at,
  raw_user_meta_data = EXCLUDED.raw_user_meta_data,
  user_metadata = EXCLUDED.user_metadata;

Click "Run"

Expected result: Should show how many rows were inserted/updated (e.g., "INSERT 0 5" means 5 users synced)

Step 6: Verify the Setup¶

Let's verify everything is working:

6.1: Check if table exists¶

-- Check if users table exists
SELECT table_name, table_schema
FROM information_schema.tables
WHERE table_name = 'users' AND table_schema = 'public';

Expected result: Should show one row with users and public

6.2: Check if function exists¶

-- Check if sync function exists
SELECT proname, prosecdef
FROM pg_proc
WHERE proname = 'sync_auth_user_to_public';

Expected result: Should show one row with sync_auth_user_to_public and t (true for SECURITY DEFINER)

6.3: Check if trigger exists¶

-- Check if trigger exists
SELECT 
  trigger_name,
  event_manipulation,
  event_object_table,
  event_object_schema
FROM information_schema.triggers
WHERE trigger_name = 'sync_auth_to_public_users';

Expected result: Should show one row with trigger details on auth.users

6.4: Check current users¶

-- Check users in public.users table
SELECT id, email, created_at, provider
FROM public.users
ORDER BY created_at DESC
LIMIT 10;

Expected result: Should show your users (if any exist)

Step 7: Set Up Webhook via Dashboard UI¶

Now that we have a public users table, we can use the Dashboard UI to create webhooks (no SQL needed!).

7.1: Navigate to Webhooks¶

In Supabase Dashboard, click "Database" in the left sidebar
Click "Webhooks" (under Database section)
You'll see a list of existing webhooks (if any)

7.2: Create New Webhook¶

Click "Create a new webhook" or "New webhook" button
Fill in the webhook details:

Name:

User Signup Notifications

Table: - Click the dropdown - Select users (from public schema, NOT auth.users) - ⚠️ Important: Make sure it says users in public schema

Events: - ✅ Check INSERT (for new signups) - ✅ Optionally check UPDATE (for login notifications) - ❌ Skip DELETE (not needed)

HTTP Request: - URL:

https://ai-assisted-pcb-stackup-generator.onrender.com/api/webhooks/auth

(Replace with your actual webhook URL)

Method: POST
HTTP Request headers:
- Click "Add header"
- Key: Content-Type
- Value: application/json
- (Optional) Click "Add header" again for security:
- Key: x-supabase-signature
- Value: your-webhook-secret-here (Use a random string, e.g., generate with: openssl rand -hex 32)
Click "Create webhook" or "Save"

Expected result: You should see the webhook in the list with status "Active"

Step 8: Test the Setup¶

Create a test user in your app:
Go to your app (pcbgenerator.com)
Sign up with a new email address
Complete the signup process
Check if user synced:
Go back to Supabase SQL Editor

Run:

SELECT id, email, created_at
FROM public.users
ORDER BY created_at DESC
LIMIT 1;

Should show your new test user
Check webhook delivery:
Go to Database → Webhooks
Click on your webhook: "User Signup Notifications"
Click "Recent deliveries" tab
Should show a delivery with status 200 OK
Check backend logs:
Check your backend logs (Render, Railway, etc.)
Should see webhook request logged
Should see email sending logs
Check email inbox:
Check the email inbox for the test user
Should receive welcome email
Check admin email for signup notification

8.2: Verify Trigger is Working¶

Run this to see recent syncs:

-- Check recent users and their sync status
SELECT 
  id,
  email,
  created_at,
  updated_at,
  last_sign_in_at,
  provider
FROM public.users
ORDER BY created_at DESC
LIMIT 5;

Step 9: Set Up Webhook Secret (Production Security)¶

For production, add a webhook secret:

9.1: Generate Secret¶

In your terminal, run:

openssl rand -hex 32

Copy the generated string (e.g., a1b2c3d4e5f6...)

9.2: Add to Supabase Webhook¶

Go to Database → Webhooks
Click on your webhook
Click "Edit"
In HTTP Request headers, add:
Key: x-supabase-signature
Value: your-generated-secret-here
Click "Save"

9.3: Add to Backend Environment Variables¶

Go to your hosting platform (Render, Railway, etc.)
Add environment variable:
Key: SUPABASE_WEBHOOK_SECRET
Value: your-generated-secret-here (same as above)
Restart your backend service

Troubleshooting¶

Problem: Users not syncing to public.users¶

Check trigger exists:

SELECT * FROM information_schema.triggers
WHERE trigger_name = 'sync_auth_to_public_users';

Check function exists:

SELECT proname FROM pg_proc
WHERE proname = 'sync_auth_user_to_public';

Recreate trigger if needed:

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

Problem: Webhook not being called¶

Verify webhook is active:
Go to Database → Webhooks
Check webhook status is "Active"
Verify table name:
Make sure webhook is on users table in public schema
NOT auth.users
Check webhook URL:
Verify URL is correct and accessible

Test with curl:

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

Check webhook logs:
Go to Database → Webhooks → Your webhook → Recent deliveries
Look for error messages

Problem: "permission denied" errors¶

Solution: Make sure function has SECURITY DEFINER:

-- Recreate function with SECURITY DEFINER
CREATE OR REPLACE FUNCTION sync_auth_user_to_public()
RETURNS TRIGGER
SECURITY DEFINER  -- Must have this
SET search_path = public
LANGUAGE plpgsql
AS $$
-- ... (rest of function code)
$$;

Problem: Duplicate key errors¶

Solution: The ON CONFLICT clause should handle this, but if you see errors:

-- Check for duplicates
SELECT id, COUNT(*) 
FROM public.users 
GROUP BY id 
HAVING COUNT(*) > 1;

-- If duplicates exist, clean them up
DELETE FROM public.users
WHERE ctid NOT IN (
  SELECT MIN(ctid)
  FROM public.users
  GROUP BY id
);

Quick Reference Commands¶

Check if everything is set up:

-- Check table
SELECT table_name FROM information_schema.tables 
WHERE table_name = 'users' AND table_schema = 'public';

-- Check function
SELECT proname FROM pg_proc 
WHERE proname = 'sync_auth_user_to_public';

-- Check trigger
SELECT trigger_name FROM information_schema.triggers
WHERE trigger_name = 'sync_auth_to_public_users';

-- Check users
SELECT COUNT(*) FROM public.users;

Drop everything (if you need to start over):

-- Drop trigger
DROP TRIGGER IF EXISTS sync_auth_to_public_users ON auth.users;

-- Drop function
DROP FUNCTION IF EXISTS sync_auth_user_to_public();

-- Drop table (WARNING: This deletes all user data!)
-- DROP TABLE IF EXISTS public.users;

Summary¶

✅ What we accomplished: 1. Created public users table 2. Created sync function with SECURITY DEFINER 3. Created trigger to auto-sync auth.users → users 4. Set up webhook via Dashboard UI (easy!) 5. Tested and verified everything works

✅ Benefits: - Easy to set up (mostly Dashboard UI) - Easy to debug (can query public.users directly) - Standard Supabase pattern - No complex HTTP extensions needed

✅ Next steps: - Monitor webhook deliveries in Dashboard - Check backend logs for webhook receipts - Verify emails are being sent - Set up webhook secret for production security

Need Help?¶

If something doesn't work: 1. Check the Troubleshooting section above 2. Check Supabase logs: Database → Logs → Postgres Logs 3. Check webhook delivery logs: Database → Webhooks → Recent deliveries 4. Check backend logs for webhook receipts

Method 3: Sync auth.users to Public Table - Step-by-Step Guide¶

Overview¶

Step 1: Access Supabase SQL Editor¶

Step 2: Create the Public users Table¶

⚠️ Pro-Tip: The Foreign Key Constraint¶

Step 3: Create the Sync Function¶

Step 4: Create the Trigger¶

Step 5: Sync Existing Users (If Any)¶

Step 6: Verify the Setup¶

6.1: Check if table exists¶

6.2: Check if function exists¶

6.3: Check if trigger exists¶

6.4: Check current users¶

Step 7: Set Up Webhook via Dashboard UI¶

7.1: Navigate to Webhooks¶

7.2: Create New Webhook¶

Step 8: Test the Setup¶

8.1: Test with a New Signup¶

8.2: Verify Trigger is Working¶

Step 9: Set Up Webhook Secret (Production Security)¶

9.1: Generate Secret¶

9.2: Add to Supabase Webhook¶

9.3: Add to Backend Environment Variables¶

Troubleshooting¶

Problem: Users not syncing to public.users¶

Problem: Webhook not being called¶

Problem: "permission denied" errors¶

Problem: Duplicate key errors¶

Quick Reference Commands¶

Summary¶

Need Help?¶

Step 2: Create the Public `users` Table¶