supabase-webhooks-events

What this skill does

# Supabase Webhooks & Database Events

## Overview

Supabase offers four complementary event mechanisms: **Database Webhooks** (trigger-based HTTP calls via `pg_net`), **`supabase_functions.http_request()`** (call Edge Functions from triggers), **Postgres LISTEN/NOTIFY** (lightweight pub/sub), and **Realtime `postgres_changes`** (client-side event subscriptions). This skill covers all four patterns with production-ready code including signature verification, idempotency, and retry handling.

## Prerequisites

- Supabase project (local or hosted) with `supabase` CLI installed
- `pg_net` extension enabled: Dashboard > Database > Extensions > search "pg_net" > Enable
- `@supabase/supabase-js` v2+ installed for client-side patterns
- Edge Functions deployed for webhook receiver patterns

## Step 1 — Database Webhooks with `pg_net` and Trigger Functions

Database webhooks fire HTTP requests when rows change. Under the hood, Supabase uses the `pg_net` extension to make async, non-blocking HTTP calls from within PostgreSQL.

### Enable pg_net and Create the Trigger Function

```sql
-- Enable the pg_net extension (one-time)
CREATE EXTENSION IF NOT EXISTS pg_net WITH SCHEMA extensions;

-- Trigger function: POST to an Edge Function on every new order
CREATE OR REPLACE FUNCTION public.notify_order_created()
RETURNS trigger AS $$
BEGIN
  PERFORM net.http_post(
    url    := 'https://<project-ref>.supabase.co/functions/v1/on-order-created',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || current_setting('app.settings.service_role_key', true)
    ),
    body   := jsonb_build_object(
      'table',  TG_TABLE_NAME,
      'type',   TG_OP,
      'record', row_to_json(NEW)::jsonb,
      'old_record', CASE WHEN TG_OP = 'UPDATE' THEN row_to_json(OLD)::jsonb ELSE NULL END
    )
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
```

### Attach Triggers for INSERT, UPDATE, DELETE

```sql
-- Fire on new rows
CREATE TRIGGER on_order_created
  AFTER INSERT ON public.orders
  FOR EACH ROW EXECUTE FUNCTION public.notify_order_created();

-- Fire on status changes only (conditional trigger)
CREATE OR REPLACE FUNCTION public.notify_order_status_changed()
RETURNS trigger AS $$
BEGIN
  IF OLD.status IS DISTINCT FROM NEW.status THEN
    PERFORM net.http_post(
      url    := 'https://<project-ref>.supabase.co/functions/v1/on-status-change',
      headers := '{"Content-Type": "application/json"}'::jsonb,
      body   := jsonb_build_object(
        'order_id',   NEW.id,
        'old_status', OLD.status,
        'new_status', NEW.status,
        'changed_at', now()
      )
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_order_status_changed
  AFTER UPDATE ON public.orders
  FOR EACH ROW EXECUTE FUNCTION public.notify_order_status_changed();
```

### Using `supabase_functions.http_request()` (Built-in Helper)

Supabase provides a built-in wrapper that simplifies calling Edge Functions from triggers without managing headers manually:

```sql
-- This is the function Supabase auto-creates for Dashboard-configured webhooks
-- You can also call it directly in your own trigger functions
CREATE TRIGGER on_profile_updated
  AFTER UPDATE ON public.profiles
  FOR EACH ROW
  EXECUTE FUNCTION supabase_functions.http_request(
    'https://<project-ref>.supabase.co/functions/v1/on-profile-update',
    'POST',
    '{"Content-Type": "application/json"}',
    '{}',  -- params
    '5000' -- timeout ms
  );
```

### Inspect pg_net Responses

```sql
-- Check recent HTTP responses (retained for 6 hours)
SELECT id, status_code, content, created
FROM net._http_response
ORDER BY created DESC
LIMIT 10;

-- Find failed requests
SELECT id, status_code, content
FROM net._http_response
WHERE status_code >= 400
ORDER BY created DESC;
```

## Step 2 — Edge Function Webhook Receivers with Signature Verification

### Webhook Receiver with Signature Verification

```typescript
// supabase/functions/on-order-created/index.ts
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";
import { serve } from "https://deno.land/[email protected]/http/server.ts";

interface WebhookPayload {
  type: "INSERT" | "UPDATE" | "DELETE";
  table: string;
  record: Record<string, unknown>;
  old_record: Record<string, unknown> | null;
}

// Verify webhook signature to prevent spoofing
async function verifySignature(
  body: string,
  signature: string,
  secret: string
): Promise<boolean> {
  const encoder = new TextEncoder();
  const key = await crypto.subtle.importKey(
    "raw",
    encoder.encode(secret),
    { name: "HMAC", hash: "SHA-256" },
    false,
    ["sign"]
  );
  const signed = await crypto.subtle.sign("HMAC", key, encoder.encode(body));
  const expected = Array.from(new Uint8Array(signed))
    .map((b) => b.toString(16).padStart(2, "0"))
    .join("");
  // Constant-time comparison
  if (signature.length !== expected.length) return false;
  let mismatch = 0;
  for (let i = 0; i < signature.length; i++) {
    mismatch |= signature.charCodeAt(i) ^ expected.charCodeAt(i);
  }
  return mismatch === 0;
}

serve(async (req) => {
  // Verify signature if webhook secret is configured
  const webhookSecret = Deno.env.get("WEBHOOK_SECRET");
  const rawBody = await req.text();

  if (webhookSecret) {
    const signature = req.headers.get("x-webhook-signature") ?? "";
    const valid = await verifySignature(rawBody, signature, webhookSecret);
    if (!valid) {
      return new Response(JSON.stringify({ error: "Invalid signature" }), {
        status: 401,
      });
    }
  }

  const payload: WebhookPayload = JSON.parse(rawBody);

  const supabase = createClient(
    Deno.env.get("SUPABASE_URL")!,
    Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!,
    { auth: { autoRefreshToken: false, persistSession: false } }
  );

  // Route by event type
  switch (payload.type) {
    case "INSERT": {
      console.log(`New ${payload.table} row:`, payload.record.id);

      // Example: log event, send notification, update related table
      await supabase.from("audit_log").insert({
        table_name: payload.table,
        action: "INSERT",
        record_id: payload.record.id,
        payload: payload.record,
      });
      break;
    }
    case "UPDATE": {
      console.log(`Updated ${payload.table}:`, payload.record.id);
      // Compare old and new to detect specific field changes
      if (payload.old_record?.status !== payload.record.status) {
        await supabase.from("notifications").insert({
          user_id: payload.record.user_id,
          message: `Status changed to ${payload.record.status}`,
        });
      }
      break;
    }
    case "DELETE": {
      console.log(`Deleted from ${payload.table}:`, payload.old_record?.id);
      break;
    }
  }

  return new Response(JSON.stringify({ received: true }), {
    headers: { "Content-Type": "application/json" },
  });
});
```

### Idempotent Event Processing

Webhooks may be delivered more than once. Use an idempotency table to prevent duplicate processing:

```typescript
// supabase/functions/idempotent-handler/index.ts
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

serve(async (req) => {
  const payload = await req.json();
  const eventId = `${payload.table}:${payload.type}:${payload.record.id}`;

  const supabase = createClient(
    Deno.env.get("SUPABASE_URL")!,
    Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!
  );

  // Check if already processed (upsert pattern)
  const { data: existing } = await supabase
    .from("processed_events")
    .select("id")
    .eq("event_id", eventId)
    .maybeSingle();

  if (existing) {
    return new Response(
      JSON.stringify({ skipped: true, reason: "already processed" }),
      { status: 200, headers: { "Content-Type": "application/json" } }
    );
  }

  // --- Your business logic here ---
  console.log(`Processing event: ${eventId}`);

  // Mark as processed (with TTL for cleanup)