claude-opus-4-8 · d2_webhooks · rep 1

env | grep -i supabase | sed 's/=.*/=<set>/'

SUPABASE_DB_PASSWORD=<set>
SUPABASE_DB_URL=<set>
SUPABASE_URL=<set>
SUPABASE_SERVICE_ROLE_KEY=<set>
SUPABASE_PROJECT_REF=<set>
SUPABASE_ANON_KEY=<set>

which psql || echo "no psql"; which curl; which jq || echo "no jq"

/usr/bin/psql
/usr/bin/curl
/usr/bin/jq

psql "$SUPABASE_DB_URL" -c "\d public.signups" 2>&1; echo "---"; psql "$SUPABASE_DB_URL" -c "\d public.webhook_receipts" 2>&1

                                     Table "public.signups"
   Column   |           Type           | Collation | Nullable |             Default              
------------+--------------------------+-----------+----------+----------------------------------
 id         | bigint                   |           | not null | generated by default as identity
 email      | text                     |           | not null | 
 plan       | text                     |           | not null | 'free'::text
 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "signups_pkey" PRIMARY KEY, btree (id)

---
                                 Table "public.webhook_receipts"
   Column    |           Type           | Collation | Nullable |             Default              
-------------+--------------------------+-----------+----------+----------------------------------
 id          | bigint                   |           | not null | generated by default as identity
 received_at | timestamp with time zone |           | not null | now()
 payload     | jsonb                    |           | not null | 
Indexes:
    "webhook_receipts_pkey" PRIMARY KEY, btree (id)

psql "$SUPABASE_DB_URL" -c "SELECT name, default_version, installed_version FROM pg_available_extensions WHERE name IN ('pg_net','http','pg_cron','supabase_vault','vault') ORDER BY name;" 2>&1

      name      | default_version | installed_version 
----------------+-----------------+-------------------
 http           | 1.6             | 
 pg_cron        | 1.6.4           | 
 pg_net         | 0.20.4          | 
 supabase_vault | 0.3.1           | 0.3.1
(4 rows)

psql "$SUPABASE_DB_URL" -c "CREATE EXTENSION IF NOT EXISTS pg_net WITH SCHEMA extensions;" 2>&1
echo "--- check schema of net functions ---"
psql "$SUPABASE_DB_URL" -c "SELECT n.nspname, p.proname FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace WHERE p.proname LIKE 'http%' AND n.nspname IN ('net','extensions') ORDER BY 1,2;" 2>&1

CREATE EXTENSION
--- check schema of net functions ---
 nspname |        proname        
---------+-----------------------
 net     | http_collect_response
 net     | http_delete
 net     | http_get
 net     | http_post
(4 rows)

curl -s -o /tmp/resp.txt -w "HTTP %{http_code}\n" -X POST "$SUPABASE_URL/functions/v1/hook-recorder" \
  -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" \
  -H "Content-Type: application/json" \
  -d '{"email":"probe@example.com","plan":"probe"}'
echo "--- body ---"; cat /tmp/resp.txt; echo

HTTP 200
--- body ---
{"ok":true}

psql "$SUPABASE_DB_URL" -c "SELECT id, received_at, payload FROM public.webhook_receipts ORDER BY id DESC LIMIT 5;" 2>&1

 id |          received_at          |                     payload                     
----+-------------------------------+-------------------------------------------------
  2 | 2026-07-26 02:38:32.257677+00 | {"plan": "probe", "email": "probe@example.com"}
(1 row)

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<SQL 2>&1
-- Store webhook config in vault (idempotent)
DO \$\$
DECLARE
  v_url text := '${SUPABASE_URL}/functions/v1/hook-recorder';
  v_jwt text := '${SUPABASE_SERVICE_ROLE_KEY}';
BEGIN
  -- upsert URL secret
  IF EXISTS (SELECT 1 FROM vault.secrets WHERE name = 'signups_webhook_url') THEN
    PERFORM vault.update_secret((SELECT id FROM vault.secrets WHERE name='signups_webhook_url'), v_url, 'signups_webhook_url', 'Receiver endpoint for signups webhook');
  ELSE
    PERFORM vault.create_secret(v_url, 'signups_webhook_url', 'Receiver endpoint for signups webhook');
  END IF;

  -- upsert JWT secret
  IF EXISTS (SELECT 1 FROM vault.secrets WHERE name = 'signups_webhook_jwt') THEN
    PERFORM vault.update_secret((SELECT id FROM vault.secrets WHERE name='signups_webhook_jwt'), v_jwt, 'signups_webhook_jwt', 'JWT for signups webhook auth');
  ELSE
    PERFORM vault.create_secret(v_jwt, 'signups_webhook_jwt', 'JWT for signups webhook auth');
  END IF;
END
\$\$;

SELECT name FROM vault.secrets WHERE name LIKE 'signups_webhook%' ORDER BY name;
SQL

DO
        name         
---------------------
 signups_webhook_jwt
 signups_webhook_key
 signups_webhook_url
(3 rows)

psql "$SUPABASE_DB_URL" -c "DELETE FROM vault.secrets WHERE name='signups_webhook_key';" 2>&1
psql "$SUPABASE_DB_URL" -c "SELECT name FROM vault.secrets WHERE name LIKE 'signups_webhook%' ORDER BY name;" 2>&1

DELETE 1
        name         
---------------------
 signups_webhook_jwt
 signups_webhook_url
(2 rows)

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL' 2>&1
CREATE OR REPLACE FUNCTION public.notify_signup_webhook()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, vault, net, extensions
AS $$
DECLARE
  v_url text;
  v_jwt text;
  v_request_id bigint;
BEGIN
  SELECT decrypted_secret INTO v_url  FROM vault.decrypted_secrets WHERE name = 'signups_webhook_url' LIMIT 1;
  SELECT decrypted_secret INTO v_jwt  FROM vault.decrypted_secrets WHERE name = 'signups_webhook_jwt' LIMIT 1;

  IF v_url IS NULL OR v_jwt IS NULL THEN
    RAISE WARNING 'signups webhook config missing in vault; skipping delivery';
    RETURN NEW;
  END IF;

  SELECT net.http_post(
    url     := v_url,
    headers := jsonb_build_object(
                 'Content-Type', 'application/json',
                 'Authorization', 'Bearer ' || v_jwt
               ),
    body    := jsonb_build_object(
                 'id',         NEW.id,
                 'email',      NEW.email,
                 'plan',       NEW.plan,
                 'created_at', NEW.created_at
               ),
    timeout_milliseconds := 5000
  ) INTO v_request_id;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_signups_webhook ON public.signups;
CREATE TRIGGER trg_signups_webhook
  AFTER INSERT ON public.signups
  FOR EACH ROW
  EXECUTE FUNCTION public.notify_signup_webhook();

SELECT tgname, tgenabled FROM pg_trigger WHERE tgrelid = 'public.signups'::regclass AND NOT tgisinternal;
SQL

CREATE FUNCTION
NOTICE:  trigger "trg_signups_webhook" for relation "public.signups" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
       tgname        | tgenabled 
---------------------+-----------
 trg_signups_webhook | O
(1 row)

STAMP=$(date +%s)
echo "Test stamp: $STAMP"
# Path 1: direct SQL insert
psql "$SUPABASE_DB_URL" -c "INSERT INTO public.signups(email, plan) VALUES ('sqltest+$STAMP@example.com','pro');" 2>&1

# Path 2: REST API insert (simulating an external client), using anon key
curl -s -o /dev/null -w "REST insert HTTP %{http_code}\n" -X POST "$SUPABASE_URL/rest/v1/signups" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=minimal" \
  -d "{\"email\":\"resttest+$STAMP@example.com\",\"plan\":\"team\"}"
echo "$STAMP" > /tmp/stamp.txt

Test stamp: 1785033544
INSERT 0 1
REST insert HTTP 201

sleep 6
STAMP=$(cat /tmp/stamp.txt)
echo "=== webhook_receipts matching this test ==="
psql "$SUPABASE_DB_URL" -c "SELECT id, received_at, payload FROM public.webhook_receipts WHERE payload->>'email' LIKE '%$STAMP%' ORDER BY id;" 2>&1
echo "=== pg_net response log ==="
psql "$SUPABASE_DB_URL" -c "SELECT id, status_code, (content::jsonb) AS body FROM net._http_response ORDER BY id DESC LIMIT 5;" 2>&1

=== webhook_receipts matching this test ===
 id |          received_at          |                                                         payload                                                         
----+-------------------------------+-------------------------------------------------------------------------------------------------------------------------
  3 | 2026-07-26 02:39:05.282396+00 | {"id": 1, "plan": "pro", "email": "sqltest+1785033544@example.com", "created_at": "2026-07-26T02:39:04.876075+00:00"}
  4 | 2026-07-26 02:39:05.47122+00  | {"id": 2, "plan": "team", "email": "resttest+1785033544@example.com", "created_at": "2026-07-26T02:39:05.060886+00:00"}
(2 rows)

=== pg_net response log ===
 id | status_code |     body     
----+-------------+--------------
  2 |         200 | {"ok": true}
  1 |         200 | {"ok": true}
(2 rows)

psql "$SUPABASE_DB_URL" -c "SELECT proname, prosecdef AS security_definer FROM pg_proc WHERE proname='notify_signup_webhook';" 2>&1
psql "$SUPABASE_DB_URL" -c "SELECT count(*) AS total_receipts FROM public.webhook_receipts;" 2>&1

        proname        | security_definer 
-----------------------+------------------
 notify_signup_webhook | t
(1 row)

 total_receipts 
----------------
              3
(1 row)