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

env | grep -i supabase | sed 's/\(KEY=\).*/\1***/; s/\(PASSWORD=\).*/\1***/; s/\(DB_URL=\).*/\1***/'

SUPABASE_DB_PASSWORD=***
SUPABASE_DB_URL=***
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=***
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=***

which psql || (apt-get update -qq && apt-get install -y -qq postgresql-client >/dev/null 2>&1 && echo installed); which psql; psql --version 2>/dev/null

/usr/bin/psql
/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

psql "$SUPABASE_DB_URL" -c "\dt public.*" 2>&1 | head -40

                List of tables
 Schema |       Name       | Type  |  Owner   
--------+------------------+-------+----------
 public | signups          | table | postgres
 public | webhook_receipts | table | postgres
(2 rows)

psql "$SUPABASE_DB_URL" -c "\d public.signups" -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') 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;" 2>&1
echo "---net schema---"
psql "$SUPABASE_DB_URL" -c "SELECT n.nspname FROM pg_extension e JOIN pg_namespace n ON n.oid=e.extnamespace WHERE e.extname='pg_net';" 2>&1

CREATE EXTENSION
---net schema---
 nspname 
---------
 public
(1 row)

echo "== test receiver with service role key (JWT) =="
curl -s -w "\nHTTP %{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":"free"}'

== test receiver with service role key (JWT) ==
{"ok":true}
HTTP 200

psql "$SUPABASE_DB_URL" -c "SELECT id, received_at, payload FROM public.webhook_receipts ORDER BY id DESC LIMIT 5;" 2>&1
echo "== anon key test =="
curl -s -o /dev/null -w "anon HTTP %{http_code}\n" -X POST "$SUPABASE_URL/functions/v1/hook-recorder" -H "Authorization: Bearer $SUPABASE_ANON_KEY" -H "Content-Type: application/json" -d '{"email":"a@b.c","plan":"free"}'
echo "== no auth test =="
curl -s -o /dev/null -w "noauth HTTP %{http_code}\n" -X POST "$SUPABASE_URL/functions/v1/hook-recorder" -H "Content-Type: application/json" -d '{"email":"a@b.c","plan":"free"}'

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

== anon key test ==
anon HTTP 200
== no auth test ==
noauth HTTP 401

cat > /tmp/setup.sql <<'SQL'
-- Store the receiver URL and a valid project JWT securely in Vault.
-- Using anon key as the bearer JWT (valid project JWT, least-privilege for the receiver).
SELECT vault.create_secret(:'hook_url', 'hook_recorder_url', 'Receiver endpoint for signups webhook');
SELECT vault.create_secret(:'hook_jwt', 'hook_recorder_jwt', 'Project JWT used to authenticate webhook delivery');
SQL
psql "$SUPABASE_DB_URL" \
  -v hook_url="$SUPABASE_URL/functions/v1/hook-recorder" \
  -v hook_jwt="$SUPABASE_ANON_KEY" \
  -f /tmp/setup.sql 2>&1

psql:/tmp/setup.sql:3: ERROR:  duplicate key value violates unique constraint "secrets_name_idx"
DETAIL:  Key (name)=(hook_recorder_url) already exists.
CONTEXT:  SQL statement "INSERT INTO vault.secrets (secret, name, description)
  VALUES (
    new_secret,
    new_name,
    new_description
  )
  RETURNING *"
PL/pgSQL function vault.create_secret(text,text,text,uuid) line 5 at SQL statement
psql:/tmp/setup.sql:4: ERROR:  duplicate key value violates unique constraint "secrets_name_idx"
DETAIL:  Key (name)=(hook_recorder_jwt) already exists.
CONTEXT:  SQL statement "INSERT INTO vault.secrets (secret, name, description)
  VALUES (
    new_secret,
    new_name,
    new_description
  )
  RETURNING *"
PL/pgSQL function vault.create_secret(text,text,text,uuid) line 5 at SQL statement

cat > /tmp/setup2.sql <<'SQL'
DO $$
DECLARE
  url_id uuid;
  jwt_id uuid;
BEGIN
  SELECT id INTO url_id FROM vault.secrets WHERE name = 'hook_recorder_url';
  SELECT id INTO jwt_id FROM vault.secrets WHERE name = 'hook_recorder_jwt';
  PERFORM vault.update_secret(url_id, :'hook_url');
  PERFORM vault.update_secret(jwt_id, :'hook_jwt');
END $$;

SELECT name, description FROM vault.secrets WHERE name LIKE 'hook_recorder%';
SQL
psql "$SUPABASE_DB_URL" \
  -v hook_url="$SUPABASE_URL/functions/v1/hook-recorder" \
  -v hook_jwt="$SUPABASE_ANON_KEY" \
  -f /tmp/setup2.sql 2>&1

psql:/tmp/setup2.sql:10: ERROR:  syntax error at or near ":"
LINE 8:   PERFORM vault.update_secret(url_id, :'hook_url');
                                              ^
        name         |                        description                         
---------------------+------------------------------------------------------------
 hook_recorder_token | Bearer token (project JWT) for hook-recorder edge function
 hook_recorder_jwt   | JWT for hook-recorder edge function
 hook_recorder_url   | Endpoint for hook-recorder edge function
(3 rows)

cat > /tmp/setup3.sql <<SQL
SELECT vault.update_secret(
  (SELECT id FROM vault.secrets WHERE name='hook_recorder_url'),
  '$SUPABASE_URL/functions/v1/hook-recorder'
);
SELECT vault.update_secret(
  (SELECT id FROM vault.secrets WHERE name='hook_recorder_jwt'),
  '$SUPABASE_ANON_KEY'
);
SELECT name, left(decrypted_secret, 30) AS secret_preview
FROM vault.decrypted_secrets WHERE name IN ('hook_recorder_url','hook_recorder_jwt');
SQL
psql "$SUPABASE_DB_URL" -f /tmp/setup3.sql 2>&1

 update_secret 
---------------
 
(1 row)

 update_secret 
---------------
 
(1 row)

       name        |         secret_preview         
-------------------+--------------------------------
 hook_recorder_jwt | eyJhbGciOiJIUzI1NiIsInR5cCI6Ik
 hook_recorder_url | https://<ref>.s
(2 rows)

cat > /tmp/trigger.sql <<'SQL'
CREATE OR REPLACE FUNCTION public.deliver_signup_webhook()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, vault, extensions
AS $$
DECLARE
  v_url  text;
  v_jwt  text;
  v_req  bigint;
BEGIN
  SELECT decrypted_secret INTO v_url FROM vault.decrypted_secrets WHERE name = 'hook_recorder_url';
  SELECT decrypted_secret INTO v_jwt FROM vault.decrypted_secrets WHERE name = 'hook_recorder_jwt';

  IF v_url IS NULL OR v_jwt IS NULL THEN
    RAISE WARNING 'signup webhook not sent: missing vault secret(s)';
    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(
                 'email',      NEW.email,
                 'plan',       NEW.plan,
                 'id',         NEW.id,
                 'created_at', NEW.created_at
               ),
    timeout_milliseconds := 5000
  ) INTO v_req;

  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.deliver_signup_webhook();
SQL
psql "$SUPABASE_DB_URL" -f /tmp/trigger.sql 2>&1

CREATE FUNCTION
psql:/tmp/trigger.sql:39: NOTICE:  trigger "trg_signups_webhook" for relation "public.signups" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER

TS=$(date +%s)
echo "== 1) Direct DB insert =="
psql "$SUPABASE_DB_URL" -c "INSERT INTO public.signups(email, plan) VALUES ('dbclient-$TS@example.com','pro') RETURNING id, email, plan;" 2>&1

echo ""
echo "== 2) Insert via PostgREST as anon client =="
curl -s -w "\nHTTP %{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=representation" \
  -d "{\"email\":\"restclient-$TS@example.com\",\"plan\":\"team\"}"

echo "TS=$TS" > /tmp/ts.txt

== 1) Direct DB insert ==
 id |              email              | plan 
----+---------------------------------+------
  1 | dbclient-1785033753@example.com | pro
(1 row)

INSERT 0 1

== 2) Insert via PostgREST as anon client ==
[{"id":2,"email":"restclient-1785033753@example.com","plan":"team","created_at":"2026-07-26T02:42:33.922624+00:00"}]
HTTP 201

sleep 6
source /tmp/ts.txt
echo "== webhook_receipts (accepted by receiver) =="
psql "$SUPABASE_DB_URL" -c "SELECT id, received_at, payload FROM public.webhook_receipts WHERE payload->>'email' LIKE '%$TS%' ORDER BY id;" 2>&1

echo ""
echo "== pg_net response log (delivery status) =="
psql "$SUPABASE_DB_URL" -c "SELECT id, status_code, content::text FROM net._http_response ORDER BY id DESC LIMIT 5;" 2>&1

== webhook_receipts (accepted by receiver) ==
 id |          received_at          |                                                          payload                                                          
----+-------------------------------+---------------------------------------------------------------------------------------------------------------------------
  4 | 2026-07-26 02:42:33.932241+00 | {"id": 1, "plan": "pro", "email": "dbclient-1785033753@example.com", "created_at": "2026-07-26T02:42:33.747376+00:00"}
  5 | 2026-07-26 02:42:34.104816+00 | {"id": 2, "plan": "team", "email": "restclient-1785033753@example.com", "created_at": "2026-07-26T02:42:33.922624+00:00"}
(2 rows)


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

echo "== Trigger registered on signups =="
psql "$SUPABASE_DB_URL" -c "SELECT tgname, tgenabled, pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid='public.signups'::regclass AND NOT tgisinternal;" -x 2>&1
echo "== pg_net extension =="
psql "$SUPABASE_DB_URL" -c "SELECT extname, extversion FROM pg_extension WHERE extname='pg_net';" 2>&1

== Trigger registered on signups ==
-[ RECORD 1 ]-----+-------------------------------------------------------------------------------------------------------------------------
tgname            | trg_signups_webhook
tgenabled         | O
pg_get_triggerdef | CREATE TRIGGER trg_signups_webhook AFTER INSERT ON public.signups FOR EACH ROW EXECUTE FUNCTION deliver_signup_webhook()

== pg_net extension ==
 extname | extversion 
---------+------------
 pg_net  | 0.20.4
(1 row)