Supabase Webhooks Deep Dive — Database Triggers, pg_net & Edge Function Patterns

DB change → pg_net (async HTTP) → Edge Function or external endpoint

create extension if not exists pg_net;

-- Fire a POST from within a SQL function
select net.http_post(
 url := 'https://your-project.supabase.co/functions/v1/notify-user',
 body := json_build_object(
 'user_id', NEW.user_id,
 'event', 'new_message'
 )::jsonb,
 headers := '{"Authorization": "Bearer <service-role-key>",
 "Content-Type": "application/json"}'::jsonb
);

create or replace function public.handle_new_user()
returns trigger language plpgsql security definer as $$
begin
 perform net.http_post(
 url := current_setting('app.settings.supabase_url')
 || '/functions/v1/send-welcome-email',
 body := json_build_object(
 'user_id', NEW.id,
 'email', NEW.email,
 'display_name', NEW.raw_user_meta_data->>'display_name'
 )::jsonb,
 headers := json_build_object(
 'Authorization', 'Bearer '
 || current_setting('app.settings.service_role_key'),
 'Content-Type', 'application/json'
 )::jsonb
 );
 return NEW;
end;
$$;

create trigger on_auth_user_created
 after insert on auth.users
 for each row execute procedure public.handle_new_user();

create extension if not exists pg_cron;

-- Delete expired sessions every night at 00:00 UTC
select cron.schedule(
 'cleanup-expired-sessions',
 '0 0 * * *',
 $$delete from user_sessions where expires_at < now();$$
);

-- Flag overdue WBS tasks every hour
select cron.schedule(
 'check-overdue-wbs-tasks',
 '0 * * * *',
 $$
 update wbs_tasks set status = 'overdue'
 where deadline < now()
 and status not in ('completed', 'overdue');
 $$
);

export async function verifyWebhookSignature(
 req: Request,
 secret: string
): Promise<boolean> {
 const signature = req.headers.get("x-supabase-webhook-signature");
 if (!signature) return false;

 const body = await req.text();
 const key = await crypto.subtle.importKey(
 "raw",
 new TextEncoder().encode(secret),
 { name: "HMAC", hash: "SHA-256" },
 false,
 ["sign"]
 );
 const sig = await crypto.subtle.sign("HMAC", key, new TextEncoder().encode(body));
 const expected =
 "sha256=" +
 Array.from(new Uint8Array(sig))
 .map((b) => b.toString(16).padStart(2, "0"))
 .join("");

 return signature === expected;
}

create table if not exists webhook_logs (
 id bigint generated always as identity primary key,
 event_type text not null,
 payload jsonb not null,
 response_status int,
 retry_count int default 0,
 created_at timestamptz default now()
);

-- Retry every 5 minutes, up to 3 attempts
select cron.schedule('retry-failed-webhooks', '*/5 * * * *', $$
 select net.http_post(
 url := 'https://your-project.supabase.co/functions/v1/process-event',
 body := payload,
 headers := '{"Content-Type":"application/json"}'::jsonb
 )
 from webhook_logs
 where response_status >= 500
 and retry_count < 3
 and created_at > now() - interval '24 hours';
$$);