moonshotai/kimi-k3 · d2_webhooks · rep 3

env | grep -i supabase | sed 's/=.*KEY.*/=<redacted-present>/; s/\(PASSWORD\)=.*/\1=<redacted-present>/' ; which psql; psql --version

SUPABASE_DB_PASSWORD=<redacted-present>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>
/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "\d public.signups" -c "\d public.webhook_receipts" -c "select name, default_version, installed_version from pg_available_extensions where name in ('pg_net','pg_cron','http') order by name;"

                                     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)

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

cat > /tmp/setup.sql <<'EOF'
\set ON_ERROR_STOP on

create extension if not exists pg_net;

create or replace function public.deliver_signup_webhook()
returns trigger
language plpgsql
security definer
set search_path = public, net
as $$
begin
  perform net.http_post(
    url     := current_setting('app.settings.supabase_url') || '/functions/v1/hook-recorder',
    body    := jsonb_build_object(
                 'type',   'INSERT',
                 'table',  'signups',
                 'schema', 'public',
                 'record', to_jsonb(NEW),
                 'id',     NEW.id,
                 'email',  NEW.email,
                 'plan',   NEW.plan
               ),
    headers := jsonb_build_object(
                 'Content-Type',  'application/json',
                 'Authorization', 'Bearer ' || current_setting('app.settings.service_role_key')
               ),
    timeout_milliseconds := 5000
  );
  return NEW;
end;
$$;

drop trigger if exists signups_webhook_trigger on public.signups;

create trigger signups_webhook_trigger
after insert on public.signups
for each row
execute function public.deliver_signup_webhook();
EOF
psql "$SUPABASE_DB_URL" -f /tmp/setup.sql

CREATE EXTENSION
CREATE FUNCTION
DROP TRIGGER
CREATE TRIGGER

psql:/tmp/setup.sql:33: NOTICE:  trigger "signups_webhook_trigger" for relation "public.signups" does not exist, skipping

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 \
  -c "alter database postgres set app.settings.supabase_url = '$SUPABASE_URL';" \
  -c "alter database postgres set app.settings.service_role_key = '$SUPABASE_SERVICE_ROLE_KEY';" \
  -c "select name, setting from pg_settings where name like 'app.settings.%' or name in (select '') limit 0;" \
  -c "select datname, setconfig from pg_db_setting s join pg_database d on d.oid=s.setdatabase where datname='postgres';" | sed 's/\(eyJ[a-zA-Z0-9_-]*\.\).*/\1<redacted>/'

ERROR:  permission denied to set parameter "app.settings.supabase_url"

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 \
  -c "select current_user, session_user;" \
  -c "select rolname, rolsuper, rolcreatedb from pg_roles where rolname = current_user;" \
  -c "select proname, provolatile, proconfig from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='net' and proname='http_post';" \
  -c "select name,setting from pg_settings where name like '%hook%' or name like 'app.%';"

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

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

  proname  | provolatile | proconfig 
-----------+-------------+-----------
 http_post | v           | 
(1 row)

 name | setting 
------+---------
(0 rows)

cat > /tmp/setup2.sql <<EOF
\set ON_ERROR_STOP on

-- Try storing config in a private table readable by the security-definer trigger
create schema if not exists private;
create table if not exists private.webhook_config (
  k text primary key,
  v text not null
);
revoke all on private.webhook_config from public, anon, authenticated, service_role;

insert into private.webhook_config (k, v) values
  ('supabase_url', '$SUPABASE_URL'),
  ('service_role_key', '$SUPABASE_SERVICE_ROLE_KEY')
on conflict (k) do update set v = excluded.v;

create or replace function public.deliver_signup_webhook()
returns trigger
language plpgsql
security definer
set search_path = public, net
as \$\$
declare
  v_url text;
  v_key text;
begin
  select v into v_url from private.webhook_config where k = 'supabase_url';
  select v into v_key from private.webhook_config where k = 'service_role_key';

  perform net.http_post(
    url     := v_url || '/functions/v1/hook-recorder',
    body    := jsonb_build_object(
                 'type',   'INSERT',
                 'table',  'signups',
                 'schema', 'public',
                 'record', to_jsonb(NEW),
                 'id',     NEW.id,
                 'email',  NEW.email,
                 'plan',   NEW.plan
               ),
    headers := jsonb_build_object(
                 'Content-Type',  'application/json',
                 'Authorization', 'Bearer ' || v_key
               ),
    timeout_milliseconds := 5000
  );
  return NEW;
end;
\$\$;
EOF
psql "$SUPABASE_DB_URL" -f /tmp/setup2.sql

CREATE SCHEMA
CREATE TABLE
REVOKE
INSERT 0 2
CREATE FUNCTION

MARKER="e2e-$(date +%s)@example.com"
echo "marker=$MARKER"
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 \
  -c "insert into public.signups (email, plan) values ('$MARKER', 'pro') returning id, email, plan, created_at;" \
  -c "select count(*) as queued from net.http_request_queue;"
sleep 6
psql "$SUPABASE_DB_URL" \
  -c "select id, status_code, content, error_msg, created from net._http_response order by id desc limit 3;" \
  -c "select id, received_at, payload from public.webhook_receipts where payload->>'email' = '$MARKER' order by id desc;"

marker=e2e-1784905078@example.com
 id |           email            | plan |          created_at           
----+----------------------------+------+-------------------------------
  1 | e2e-1784905078@example.com | pro  | 2026-07-24 14:57:58.877171+00
(1 row)

INSERT 0 1
 queued 
--------
      1
(1 row)

 id | status_code |   content   | error_msg |            created            
----+-------------+-------------+-----------+-------------------------------
  1 |         200 | {"ok":true} |           | 2026-07-24 14:57:58.880262+00
(1 row)

 id |          received_at          |                                                                                                                        payload                                                                                                                         
----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  2 | 2026-07-24 14:57:59.244174+00 | {"id": 1, "plan": "pro", "type": "INSERT", "email": "e2e-1784905078@example.com", "table": "signups", "record": {"id": 1, "plan": "pro", "email": "e2e-1784905078@example.com", "created_at": "2026-07-24T14:57:58.877171+00:00"}, "schema": "public"}
(1 row)

MARKER="rest-$(date +%s)@example.com"
echo "marker=$MARKER"
curl -s -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\": \"$MARKER\"}" | head -c 400
echo
sleep 6
psql "$SUPABASE_DB_URL" \
  -c "select id, status_code, content from net._http_response order by id desc limit 2;" \
  -c "select id, payload->>'email' as email, payload->>'plan' as plan from public.webhook_receipts where payload->>'email' = '$MARKER';"

marker=rest-1784905099@example.com
[{"id":2,"email":"rest-1784905099@example.com","plan":"free","created_at":"2026-07-24T14:58:19.449367+00:00"}]
 id | status_code |   content   
----+-------------+-------------
  2 |         200 | {"ok":true}
  1 |         200 | {"ok":true}
(2 rows)

 id |            email            | plan 
----+-----------------------------+------
  3 | rest-1784905099@example.com | free
(1 row)

rm -f /tmp/setup.sql /tmp/setup2.sql
psql "$SUPABASE_DB_URL" -c "select tgname, tgrelid::regclass, tgenabled from pg_trigger where tgname='signups_webhook_trigger';" -c "select count(*) as total_receipts from public.webhook_receipts;"

         tgname          | tgrelid | tgenabled 
-------------------------+---------+-----------
 signups_webhook_trigger | signups | O
(1 row)

 total_receipts 
----------------
              2
(1 row)