claude-opus-5 · d2_webhooks · rep 2

env | grep -i supabase | sed 's/\(KEY=.\{20\}\).*/\1.../' ; echo "---"; which psql curl jq

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIs...
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIs...
---
/usr/bin/psql
/usr/bin/curl
/usr/bin/jq

cat /etc/os-release | head -3; ls /usr/lib/postgresql 2>/dev/null

PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
NAME="Debian GNU/Linux"
VERSION_ID="12"
18

cd /tmp && psql "$SUPABASE_DB_URL" -c "\d public.signups" -c "\d public.webhook_receipts" 2>&1 | head -50

                                     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)

cd /tmp && psql "$SUPABASE_DB_URL" -c "select extname, extversion, n.nspname from pg_extension e join pg_namespace n on n.oid=e.extnamespace;" 2>&1

      extname       | extversion |  nspname   
--------------------+------------+------------
 plpgsql            | 1.0        | pg_catalog
 supabase_vault     | 0.3.1      | vault
 pgcrypto           | 1.3        | extensions
 uuid-ossp          | 1.1        | extensions
 pg_stat_statements | 1.11       | extensions
(5 rows)

cd /tmp && 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','pgsql-http') 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)

cd /tmp && curl -sS -o /tmp/r1.txt -w "%{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"}'; cat /tmp/r1.txt; echo; echo "--- no auth ---"; curl -sS -o /tmp/r2.txt -w "%{http_code}\n" -X POST "$SUPABASE_URL/functions/v1/hook-recorder" -H "Content-Type: application/json" -d '{"email":"noauth@example.com","plan":"x"}'; cat /tmp/r2.txt

200
{"ok":true}
--- no auth ---
401
{"code":"UNAUTHORIZED_NO_AUTH_HEADER","message":"Missing authorization header"}

cd /tmp && 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-25 06:38:59.258229+00 | {"plan": "probe", "email": "probe@example.com"}
(1 row)

cd /tmp && psql "$SUPABASE_DB_URL" -c "create extension if not exists pg_net;" 2>&1; psql "$SUPABASE_DB_URL" -c "select extname, extversion, n.nspname from pg_extension e join pg_namespace n on n.oid=e.extnamespace where extname='pg_net';" 2>&1

CREATE EXTENSION
 extname | extversion | nspname 
---------+------------+---------
 pg_net  | 0.20.4     | public
(1 row)

cd /tmp && psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL' 2>&1
drop extension pg_net;
create extension pg_net with schema extensions;
SQL
psql "$SUPABASE_DB_URL" -c "select extname, n.nspname from pg_extension e join pg_namespace n on n.oid=e.extnamespace where extname='pg_net';"

DROP EXTENSION
CREATE EXTENSION
 extname |  nspname   
---------+------------
 pg_net  | extensions
(1 row)

cd /tmp && psql "$SUPABASE_DB_URL" -c "select proname, pronamespace::regnamespace, pg_get_function_identity_arguments(oid) from pg_proc where proname in ('http_post','http_get') and pronamespace::regnamespace::text='net';" 2>&1

  proname  | pronamespace |                       pg_get_function_identity_arguments                        
-----------+--------------+---------------------------------------------------------------------------------
 http_get  | net          | url text, params jsonb, headers jsonb, timeout_milliseconds integer
 http_post | net          | url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer
(2 rows)

cd /tmp && psql "$SUPABASE_DB_URL" -c "\df vault.create_secret" -c "\d vault.secrets" 2>&1 | head -40

                                                                                List of functions
 Schema |     Name      | Result data type |                                                     Argument data types                                                      | Type 
--------+---------------+------------------+------------------------------------------------------------------------------------------------------------------------------+------
 vault  | create_secret | uuid             | new_secret text, new_name text DEFAULT NULL::text, new_description text DEFAULT ''::text, new_key_id uuid DEFAULT NULL::uuid | func
(1 row)

                                       Table "vault.secrets"
   Column    |           Type           | Collation | Nullable |              Default              
-------------+--------------------------+-----------+----------+-----------------------------------
 id          | uuid                     |           | not null | gen_random_uuid()
 name        | text                     |           |          | 
 description | text                     |           | not null | ''::text
 secret      | text                     |           | not null | 
 key_id      | uuid                     |           |          | 
 nonce       | bytea                    |           |          | vault._crypto_aead_det_noncegen()
 created_at  | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
 updated_at  | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
Indexes:
    "secrets_pkey" PRIMARY KEY, btree (id)
    "secrets_name_idx" UNIQUE, btree (name) WHERE name IS NOT NULL

cd /tmp && export HOOK_URL="$SUPABASE_URL/functions/v1/hook-recorder" && psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 \
 -v url="$HOOK_URL" -v jwt="$SUPABASE_SERVICE_ROLE_KEY" <<'SQL' 2>&1
-- idempotent: remove prior versions of these secrets
delete from vault.secrets where name in ('signups_webhook_url','signups_webhook_jwt');
select vault.create_secret(:'url', 'signups_webhook_url', 'Endpoint for signups INSERT webhook') as url_secret_id;
select vault.create_secret(:'jwt', 'signups_webhook_jwt', 'Project JWT used as Bearer token for signups webhook') as jwt_secret_id;
SQL

DELETE 2
            url_secret_id             
--------------------------------------
 6b6ffccf-dd00-459f-ad66-9cbd685408d7
(1 row)

            jwt_secret_id             
--------------------------------------
 03121d8c-833b-47a1-90e9-9d58b9e1ba56
(1 row)

cd /tmp && cat > /tmp/webhook.sql <<'SQL'
-- Trigger function: fires an async HTTP POST via pg_net for every inserted signups row.
create or replace function public.signups_webhook_notify()
returns trigger
language plpgsql
security definer
set search_path = ''
as $fn$
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';
  select decrypted_secret into v_jwt
    from vault.decrypted_secrets where name = 'signups_webhook_jwt';

  if v_url is null or v_jwt is null then
    raise warning 'signups_webhook_notify: missing vault secrets, skipping delivery for signups id=%', new.id;
    return null;
  end if;

  select extensions.http_post(
           url     := v_url,
           body    := jsonb_build_object(
                        'type',   'INSERT',
                        'table',  'signups',
                        'id',     new.id,
                        'email',  new.email,
                        'plan',   new.plan,
                        'created_at', new.created_at,
                        'record', to_jsonb(new)
                      ),
           headers := jsonb_build_object(
                        'Content-Type',  'application/json',
                        'Authorization', 'Bearer ' || v_jwt
                      ),
           timeout_milliseconds := 5000
         )
    into v_request_id;

  return null;
exception
  when others then
    -- never block/roll back the originating INSERT because of webhook problems
    raise warning 'signups_webhook_notify failed for signups id=%: % (%)', new.id, sqlerrm, sqlstate;
    return null;
end;
$fn$;

revoke all on function public.signups_webhook_notify() from public, anon, authenticated;

drop trigger if exists signups_webhook_trigger on public.signups;

-- AFTER INSERT ... FOR EACH ROW: fires for every insert from any client (postgrest, psql, other roles)
create trigger signups_webhook_trigger
after insert on public.signups
for each row
execute function public.signups_webhook_notify();
SQL
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f /tmp/webhook.sql 2>&1

CREATE FUNCTION
REVOKE
psql:/tmp/webhook.sql:53: NOTICE:  trigger "signups_webhook_trigger" for relation "public.signups" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER

cd /tmp && psql "$SUPABASE_DB_URL" -c "\d public.signups" 2>&1 | tail -6

 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "signups_pkey" PRIMARY KEY, btree (id)
Triggers:
    signups_webhook_trigger AFTER INSERT ON signups FOR EACH ROW EXECUTE FUNCTION signups_webhook_notify()

cd /tmp && TS=$(date +%s) && echo "marker=$TS" && psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "insert into public.signups(email, plan) values ('sql-test-$TS@example.com','pro') returning id, email, plan;" 2>&1

marker=1784961596
WARNING:  signups_webhook_notify failed for signups id=1: function extensions.http_post(url => text, body => jsonb, headers => jsonb, timeout_milliseconds => integer) does not exist (42883)
 id |              email              | plan 
----+---------------------------------+------
  1 | sql-test-1784961596@example.com | pro
(1 row)

INSERT 0 1

cd /tmp && psql "$SUPABASE_DB_URL" -c "select n.nspname||'.'||p.proname as func from pg_proc p join pg_namespace n on n.oid=p.pronamespace where p.proname='http_post';" 2>&1

     func      
---------------
 net.http_post
(1 row)

cd /tmp && sed -i 's/extensions\.http_post/net.http_post/' /tmp/webhook.sql && grep -n "http_post" /tmp/webhook.sql && psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f /tmp/webhook.sql 2>&1 | tail -5

23:  select net.http_post(
CREATE FUNCTION
REVOKE
DROP TRIGGER
CREATE TRIGGER

cd /tmp && TS=$(date +%s) && echo "MARKER=$TS" > /tmp/marker && psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "insert into public.signups(email, plan) values ('sql-test-$TS@example.com','pro') returning id, email, plan;" 2>&1

 id |              email              | plan 
----+---------------------------------+------
  2 | sql-test-1784961610@example.com | pro
(1 row)

INSERT 0 1

sleep 8; cd /tmp && source /tmp/marker && echo "looking for $MARKER"; psql "$SUPABASE_DB_URL" -c "select id, status_code, error_msg, left(content,200) as content from net._http_response order by id desc limit 5;" 2>&1

looking for 1784961610
 id | status_code | error_msg |   content   
----+-------------+-----------+-------------
  1 |         200 |           | {"ok":true}
(1 row)

cd /tmp && source /tmp/marker && psql "$SUPABASE_DB_URL" -c "select id, received_at, payload->>'email' as email, payload->>'plan' as plan from public.webhook_receipts order by id desc limit 5;" -c "select count(*) as matching_receipts from public.webhook_receipts where payload->>'email' = 'sql-test-$MARKER@example.com';" 2>&1

 id |          received_at          |              email              | plan  
----+-------------------------------+---------------------------------+-------
  3 | 2026-07-25 06:40:11.465176+00 | sql-test-1784961610@example.com | pro
  2 | 2026-07-25 06:38:59.258229+00 | probe@example.com               | probe
(2 rows)

 matching_receipts 
-------------------
                 1
(1 row)

cd /tmp && psql "$SUPABASE_DB_URL" -c "select relrowsecurity from pg_class where oid='public.signups'::regclass;" -c "select policyname, roles, cmd from pg_policies where tablename='signups';" 2>&1

 relrowsecurity 
----------------
 f
(1 row)

 policyname | roles | cmd 
------------+-------+-----
(0 rows)