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

set -e
printf 'URL=%s\nREF=%s\n' "$SUPABASE_URL" "$SUPABASE_PROJECT_REF"
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X -c "select current_database(), current_user;" -c "select table_schema, table_name from information_schema.tables where table_schema='public' and table_name in ('signups','webhook_receipts');" -c "select column_name,data_type,is_nullable,column_default from information_schema.columns where table_schema='public' and table_name='signups' order by ordinal_position;" -c "select name, default_version, installed_version from pg_available_extensions where name in ('pg_net','http');" -c "select extname,extversion from pg_extension where extname in ('pg_net','http');"

URL=https://<ref>.supabase.co
REF=<ref>
 current_database | current_user 
------------------+--------------
 postgres         | postgres
(1 row)

 table_schema |    table_name    
--------------+------------------
 public       | signups
 public       | webhook_receipts
(2 rows)

 column_name |        data_type         | is_nullable | column_default 
-------------+--------------------------+-------------+----------------
 id          | bigint                   | NO          | 
 email       | text                     | NO          | 
 plan        | text                     | NO          | 'free'::text
 created_at  | timestamp with time zone | NO          | now()
(4 rows)

  name  | default_version | installed_version 
--------+-----------------+-------------------
 http   | 1.6             | 
 pg_net | 0.20.4          | 
(2 rows)

 extname | extversion 
---------+------------
(0 rows)

set -e
psql "$SUPABASE_DB_URL" -X -c "select column_name,data_type,is_nullable,column_default from information_schema.columns where table_schema='public' and table_name='webhook_receipts' order by ordinal_position;" -c "select n.nspname,p.proname,pg_get_function_identity_arguments(p.oid) args from pg_proc p join pg_namespace n on n.oid=p.pronamespace where p.proname ilike '%hook%' or p.proname ilike '%signup%';" -c "select n.nspname, c.relname, t.tgname, pg_get_triggerdef(t.oid) from pg_trigger t join pg_class c on c.oid=t.tgrelid join pg_namespace n on n.oid=c.relnamespace where n.nspname='public' and not t.tgisinternal;" -c "select extname, extversion from pg_available_extensions where name in ('vault','pg_cron');"

 column_name |        data_type         | is_nullable | column_default 
-------------+--------------------------+-------------+----------------
 id          | bigint                   | NO          | 
 received_at | timestamp with time zone | NO          | now()
 payload     | jsonb                    | NO          | 
(3 rows)

 nspname | proname | args 
---------+---------+------
(0 rows)

 nspname | relname | tgname | pg_get_triggerdef 
---------+---------+--------+-------------------
(0 rows)

ERROR:  column "extname" does not exist
LINE 1: select extname, extversion from pg_available_extensions wher...
               ^
HINT:  Perhaps you meant to reference the column "pg_available_extensions.name".

set -e
psql "$SUPABASE_DB_URL" -X -c "select name, default_version from pg_available_extensions where name in ('vault','pg_cron','pg_net','http');" -c "select n.nspname,p.proname,pg_get_function_arguments(p.oid),pg_get_function_result(p.oid) from pg_proc p join pg_namespace n on n.oid=p.pronamespace where p.proname='http_post';" -c "select rolname,rolsuper,rolcreaterole,rolcreatedb from pg_roles where rolname=current_user;"

  name   | default_version 
---------+-----------------
 http    | 1.6
 pg_net  | 0.20.4
 pg_cron | 1.6.4
(3 rows)

 nspname | proname | pg_get_function_arguments | pg_get_function_result 
---------+---------+---------------------------+------------------------
(0 rows)

 rolname  | rolsuper | rolcreaterole | rolcreatedb 
----------+----------+---------------+-------------
 postgres | f        | t             | t
(1 row)

set -e
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X <<'SQL'
create extension if not exists pg_net with schema extensions;
create schema if not exists private;
create table if not exists private.webhook_config (name text primary key, value text not null);
revoke all on schema private from public;
revoke all on private.webhook_config from public;
SQL
# safely quote service key as a SQL literal using psql variable
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X --set=jwt_key="$SUPABASE_SERVICE_ROLE_KEY" --set=endpoint="$SUPABASE_URL/functions/v1/hook-recorder" <<'SQL'
insert into private.webhook_config(name,value) values ('endpoint', :'endpoint'), ('jwt', :'jwt_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, extensions
as $func$
declare
  request_id bigint;
  endpoint text;
  jwt text;
begin
  select value into endpoint from private.webhook_config where name='endpoint';
  select value into jwt from private.webhook_config where name='jwt';
  if endpoint is null or jwt is null then
    raise exception 'webhook configuration is missing';
  end if;
  select net.http_post(
    url := endpoint,
    body := jsonb_build_object('email', NEW.email, 'plan', NEW.plan),
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || jwt
    )
  ) into request_id;
  return NEW;
end;
$func$;
revoke all on function private.deliver_signup_webhook() from public;
drop trigger if exists signups_webhook_delivery on public.signups;
create trigger signups_webhook_delivery
after insert on public.signups
for each row execute function private.deliver_signup_webhook();
SQL
psql "$SUPABASE_DB_URL" -X -c "select extname, extversion from pg_extension where extname='pg_net';" -c "select pg_get_triggerdef(oid) from pg_trigger where tgname='signups_webhook_delivery';"

CREATE EXTENSION
CREATE SCHEMA
CREATE TABLE
REVOKE
REVOKE
INSERT 0 2
CREATE FUNCTION
REVOKE
DROP TRIGGER
CREATE TRIGGER
 extname | extversion 
---------+------------
 pg_net  | 0.20.4
(1 row)

                                                           pg_get_triggerdef                                                           
---------------------------------------------------------------------------------------------------------------------------------------
 CREATE TRIGGER signups_webhook_delivery AFTER INSERT ON public.signups FOR EACH ROW EXECUTE FUNCTION private.deliver_signup_webhook()
(1 row)

NOTICE:  schema "private" already exists, skipping
NOTICE:  trigger "signups_webhook_delivery" for relation "public.signups" does not exist, skipping

set -e
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X -c "select column_name,is_identity,identity_generation from information_schema.columns where table_schema='public' and table_name='signups';" -c "select id,email,plan,created_at from public.signups order by created_at desc limit 3;"

 column_name | is_identity | identity_generation 
-------------+-------------+---------------------
 id          | YES         | BY DEFAULT
 email       | NO          | 
 plan        | NO          | 
 created_at  | NO          | 
(4 rows)

 id | email | plan | created_at 
----+-------+------+------------
(0 rows)

set -e
TEST_EMAIL="db-trigger-$(date +%s)-$RANDOM@example.com"
export TEST_EMAIL
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X -c "insert into public.signups(email,plan) values ('$TEST_EMAIL','pro') returning id,email,plan;"
for i in $(seq 1 12); do
  row=$(psql "$SUPABASE_DB_URL" -At -X -c "select payload::text from public.webhook_receipts where payload->>'email' = '$TEST_EMAIL' order by received_at desc limit 1")
  if [ -n "$row" ]; then echo "RECEIPT=$row"; exit 0; fi
  sleep 2
done
echo 'No receipt'; psql "$SUPABASE_DB_URL" -X -c "select * from net._http_response order by created desc limit 5;"; exit 1

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

INSERT 0 1
RECEIPT={"plan": "pro", "email": "db-trigger-1784958995-27549@example.com"}