openai/gpt-5.6-luna · d2_webhooks · rep 2

set -e
printf 'URL=%s\nREF=%s\n' "$SUPABASE_URL" "$SUPABASE_PROJECT_REF"
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -Atc "select current_database(), current_user; select extname from pg_extension where extname in ('pg_net','http'); \d+ public.signups; \d+ public.webhook_receipts"

URL=https://<ref>.supabase.co
REF=<ref>

ERROR:  syntax error at or near "\"
LINE 1: ... pg_extension where extname in ('pg_net','http'); \d+ public...
                                                             ^

set -e
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "select current_database(), current_user; select extname from pg_extension where extname in ('pg_net','http');"
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c '\d+ public.signups' -c '\d+ public.webhook_receipts'

 current_database | current_user 
------------------+--------------
 postgres         | postgres
(1 row)

 extname 
---------
(0 rows)

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

                                                            Table "public.webhook_receipts"
   Column    |           Type           | Collation | Nullable |             Default              | Storage  | Compression | Stats target | Description 
-------------+--------------------------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------
 id          | bigint                   |           | not null | generated by default as identity | plain    |             |              | 
 received_at | timestamp with time zone |           | not null | now()                            | plain    |             |              | 
 payload     | jsonb                    |           | not null |                                  | extended |             |              | 
Indexes:
    "webhook_receipts_pkey" PRIMARY KEY, btree (id)
Access method: heap

set -e
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
create extension if not exists pg_net;
select extname, extversion from pg_extension where extname='pg_net';
select n.nspname, p.proname, pg_get_function_identity_arguments(p.oid) from pg_proc p join pg_namespace n on n.oid=p.pronamespace where p.proname='http_post';
SQL

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

 nspname |  proname  |                       pg_get_function_identity_arguments                        
---------+-----------+---------------------------------------------------------------------------------
 net     | http_post | url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer
(1 row)

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -Atc "select extname from pg_extension where extname in ('vault','pgsodium'); select rolname from pg_roles where rolname in ('supabase_admin','service_role');"; printf 'anon length=%s service length=%s\n' "${#SUPABASE_ANON_KEY}" "${#SUPABASE_SERVICE_ROLE_KEY}"

service_role
supabase_admin
anon length=208 service length=219

set -e
cat > /tmp/setup.sql <<'SQL'
create schema if not exists private;
revoke all on schema private from public;
create table if not exists private.webhook_config (
  name text primary key,
  value text not null
);
revoke all on private.webhook_config from public;
insert into private.webhook_config(name, value)
values ('hook_recorder_anon_jwt', :'anon_key')
on conflict (name) do update set value = excluded.value;

create or replace function private.deliver_signup_webhook()
returns trigger
language plpgsql
security definer
set search_path = pg_catalog, private
as $fn$
declare
  jwt text;
  request_id bigint;
begin
  select value into strict jwt
    from private.webhook_config
   where name = 'hook_recorder_anon_jwt';

  select net.http_post(
    url := 'https://<ref>.supabase.co/functions/v1/hook-recorder',
    body := jsonb_build_object('email', new.email, 'plan', new.plan),
    params := '{}'::jsonb,
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || jwt
    ),
    timeout_milliseconds := 10000
  ) into request_id;
  return new;
end;
$fn$;

revoke all on function private.deliver_signup_webhook() from public;
drop trigger if exists signups_deliver_webhook on public.signups;
create trigger signups_deliver_webhook
after insert on public.signups
for each row execute function private.deliver_signup_webhook();
SQL
# safely pass key via psql variable
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 --set=anon_key="$SUPABASE_ANON_KEY" -f /tmp/setup.sql
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "select tgname, tgenabled from pg_trigger where tgrelid='public.signups'::regclass and not tgisinternal; select has_function_privilege('anon','private.deliver_signup_webhook()','EXECUTE');"

CREATE SCHEMA
REVOKE
CREATE TABLE
REVOKE
INSERT 0 1
CREATE FUNCTION
REVOKE
DROP TRIGGER
CREATE TRIGGER
         tgname          | tgenabled 
-------------------------+-----------
 signups_deliver_webhook | O
(1 row)

 has_function_privilege 
------------------------
 f
(1 row)

psql:/tmp/setup.sql:1: NOTICE:  schema "private" already exists, skipping
psql:/tmp/setup.sql:6: NOTICE:  relation "webhook_config" already exists, skipping
psql:/tmp/setup.sql:41: NOTICE:  trigger "signups_deliver_webhook" for relation "public.signups" does not exist, skipping

set -e
TEST_EMAIL="db-trigger-$(date +%s)-$RANDOM@example.com"
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -v test_email="$TEST_EMAIL" -c "insert into public.signups(email, plan) values (:'test_email','pro') returning id,email,plan;"
for i in $(seq 1 12); do
  echo "poll $i"
  psql "$SUPABASE_DB_URL" -Atc "select id, payload from public.webhook_receipts where payload->>'email' = '$TEST_EMAIL' order by id desc limit 1" && true
  if psql "$SUPABASE_DB_URL" -Atqtc "select exists(select 1 from public.webhook_receipts where payload->>'email' = '$TEST_EMAIL' and payload->>'plan'='pro')" | grep -q t; then break; fi
  sleep 2
done
printf '\nnet requests:\n'
psql "$SUPABASE_DB_URL" -c "select id, status_code, error_msg, created, completed from net._http_response order by created desc limit 5;"
printf '\nreceipt:\n'
psql "$SUPABASE_DB_URL" -c "select id, received_at, payload from public.webhook_receipts where payload->>'email' = '$TEST_EMAIL';"

ERROR:  syntax error at or near ":"
LINE 1: insert into public.signups(email, plan) values (:'test_email...
                                                        ^

set -e
TEST_EMAIL="db-trigger-$(date +%s)-$RANDOM@example.com"
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -v test_email="$TEST_EMAIL" -c "insert into public.signups(email, plan) values ('$TEST_EMAIL','pro') returning id,email,plan;"
for i in $(seq 1 15); do
  if psql "$SUPABASE_DB_URL" -Atqtc "select exists(select 1 from public.webhook_receipts where payload->>'email' = '$TEST_EMAIL' and payload->>'plan'='pro')" | grep -q t; then echo "receipt found on poll $i"; break; fi
  sleep 2
done
printf '\nnet requests:\n'
psql "$SUPABASE_DB_URL" -c "select id, status_code, error_msg, created, completed from net._http_response order by created desc limit 5;"
printf '\nreceipt:\n'
psql "$SUPABASE_DB_URL" -c "select id, received_at, payload from public.webhook_receipts where payload->>'email' = '$TEST_EMAIL';"

 id |                  email                  | plan 
----+-----------------------------------------+------
  1 | db-trigger-1784959047-21081@example.com | pro
(1 row)

INSERT 0 1
receipt found on poll 1

net requests:

ERROR:  column "completed" does not exist
LINE 1: select id, status_code, error_msg, created, completed from n...
                                                    ^

psql "$SUPABASE_DB_URL" -c '\d net._http_response' -c "select id, status_code, error_msg, created, timed_out, content from net._http_response order by created desc limit 3;" -c "select id, received_at, payload from public.webhook_receipts order by id desc limit 3;"

                   Unlogged table "net._http_response"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 id           | bigint                   |           |          | 
 status_code  | integer                  |           |          | 
 content_type | text                     |           |          | 
 headers      | jsonb                    |           |          | 
 content      | text                     |           |          | 
 timed_out    | boolean                  |           |          | 
 error_msg    | text                     |           |          | 
 created      | timestamp with time zone |           | not null | now()
Indexes:
    "_http_response_created_idx" btree (created)

 id | status_code | error_msg |            created            | timed_out |   content   
----+-------------+-----------+-------------------------------+-----------+-------------
  1 |         200 |           | 2026-07-25 05:57:28.310391+00 | f         | {"ok":true}
(1 row)

 id |          received_at          |                               payload                               
----+-------------------------------+---------------------------------------------------------------------
  2 | 2026-07-25 05:57:28.436731+00 | {"plan": "pro", "email": "db-trigger-1784959047-21081@example.com"}
(1 row)