Notifying Admins When Users Confirm Their Email — The Right Way with Supabase and Next.js"

Full blog => here
Steps (Article Sections)

1. Hook / Problem Statement — Why polling or signup-time notifications are wrong; email confirmation is async and outside your app's request cycle.

2. Architecture Overview — The 6-step event chain diagram (confirmation link → auth.users.confirmed_at → Postgres trigger → public.profiles.confirmed_at → Supabase Database Webhook → Next.js API route). Why bridging via a public-schema table is necessary (Supabase webhooks can't observe auth.*).
   

3. The Data Layer — The add_confirmed_at_to_profiles migration: adding the nullable confirmed_at column and the Postgres AFTER UPDATE trigger function handle_user_confirmed() that syncs it. Key insight: OLD.confirmed_at IS NULL AND NEW.confirmed_at IS NOT NULL guard.

4. The Webhook API Route — app/api/webhooks/user-confirmed/route.ts. Cover: shared-secret verification via x-webhook-secret header (security), parsing Supabase's { type, table, record } payload, independent try/catch per notification channel, and why always returning 200 OK prevents Supabase retry storms.

5. Notification Services — user-confirmed.ts. Resend for email (HTML body with name/email/ID/timestamp), Slack Incoming Webhook via fetch. Emphasize isolation — one failure never blocks the other.

6. Supabase Dashboard Config — The one-time manual step: create the DB webhook pointing to the deployed route, set the header, add a confirmed_at IS NOT NULL filter to reduce noise.
   

   supabase.com

7. Environment Variables — Table of the 4 new vars (RESEND_API_KEY, ADMIN_EMAIL, SLACK_WEBHOOK_URL, WEBHOOK_SECRET) with descriptions.

1. What's Out of Scope (and Why) — No retry logic (intentional), no multi-admin, no notification prefs UI — acknowledge these and link to potential follow-ups.

2. Closing — Summary of the pattern: Postgres trigger → public table → Supabase webhook → app route. Reusable for other auth events (password reset, MFA enrollment, etc.).

Supabase Database Webhook Setup

User Confirmed Notification Webhook

This webhook fires when a user confirms their email and triggers admin notifications (email + Slack).

One-time setup in Supabase Dashboard

1. Go to Database → Webhooks in the Supabase Dashboard
2. Click Create a new hook
3. Fill in the following fields:

Field	Value
Name	user-confirmed-notification
Table	public.profiles
Events	UPDATE
Type	HTTP Request
Method	POST
URL	https://your-domain.com/api/webhooks/user-confirmed

1. Under HTTP Headers, add:

Header	Value
x-webhook-secret	(value of your WEBHOOK_SECRET env var)

1. Click Confirm to save.

Required: add confirmed_at filter

Without this filter, the webhook fires on every profile update (role changes, name updates, etc.), causing duplicate notifications. Add this filter condition:

• Column: confirmed_at
• Operator: is not
• Value: null

This ensures the webhook only fires when confirmed_at is set.

Required environment variables

Make sure these are set in .env.local (dev) and in your Vercel / hosting environment (prod):

RESEND_API_KEY=
RESEND_FROM_EMAIL=
ADMIN_EMAIL=
SLACK_WEBHOOK_URL=
WEBHOOK_SECRET=