moonshotai/kimi-k3 · d2_webhooks · rep 2

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

SUPABASE_DB_PASSWORD=1a9f7b...
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/pg_isready

psql "$SUPABASE_DB_URL" -c "\dt public.*" -c "\d public.signups" -c "\d public.webhook_receipts"

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

                                     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','plpgsql') order by name;" -c "select extname, extversion from pg_extension order by 1;"

  name   | default_version | installed_version 
---------+-----------------+-------------------
 http    | 1.6             | 
 pg_cron | 1.6.4           | 
 pg_net  | 0.20.4          | 
 plpgsql | 1.0             | 1.0
(4 rows)

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

echo "== no auth =="; curl -s -o /dev/null -w "%{http_code}\n" -X POST "$SUPABASE_URL/functions/v1/hook-recorder" -H "Content-Type: application/json" -d '{}'
echo "== anon JWT =="; curl -s -w "\n%{http_code}\n" -X POST "$SUPABASE_URL/functions/v1/hook-recorder" -H "Content-Type: application/json" -H "Authorization: Bearer $SUPABASE_ANON_KEY" -d '{"probe":"anon","email":"probe@example.com","plan":"probe"}'
echo "== service JWT =="; curl -s -w "\n%{http_code}\n" -X POST "$SUPABASE_URL/functions/v1/hook-recorder" -H "Content-Type: application/json" -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" -d '{"probe":"service","email":"probe@example.com","plan":"probe"}'

== no auth ==
401
== anon JWT ==
{"ok":true}
200
== service JWT ==
{"ok":true}
200

psql "$SUPABASE_DB_URL" -c "select relname, relrowsecurity, relforcerowsecurity from pg_class where relname in ('signups','webhook_receipts');" -c "select policyname, cmd, roles from pg_policies where schemaname='public';"

     relname      | relrowsecurity | relforcerowsecurity 
------------------+----------------+---------------------
 signups          | f              | f
 webhook_receipts | f              | f
(2 rows)

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

cat > /tmp/setup_webhook.sql <<EOF
-- 1. Async HTTP from the database
create extension if not exists pg_net;

-- 2. Store the project JWT in the vault (never in plain function source)
do \$\$
begin
  if exists (select 1 from vault.secrets where name = 'hook_recorder_service_role_key') then
    update vault.secrets
       set secret = '${SUPABASE_SERVICE_ROLE_KEY}'
     where name = 'hook_recorder_service_role_key';
  else
    perform vault.create_secret('${SUPABASE_SERVICE_ROLE_KEY}',
                                'hook_recorder_service_role_key',
                                'JWT used by signups webhook trigger to call hook-recorder');
  end if;
end \$\$;

-- 3. Trigger function: fires per inserted row, queues an async POST via pg_net.
--    SECURITY DEFINER (owner postgres) so it works for inserts from ANY role/client.
create or replace function public.deliver_signup_webhook()
returns trigger
language plpgsql
security definer
set search_path = public
as \$\$
declare
  v_url text := '${SUPABASE_URL}/functions/v1/hook-recorder';
  v_jwt text;
begin
  select decrypted_secret into v_jwt
    from vault.decrypted_secrets
   where name = 'hook_recorder_service_role_key';

  perform net.http_post(
    url     := v_url,
    body    := jsonb_build_object(
                 'id',         new.id,
                 'email',      new.email,
                 'plan',       new.plan,
                 'created_at', new.created_at
               ),
    headers := jsonb_build_object(
                 'Content-Type',  'application/json',
                 'Authorization', 'Bearer ' || v_jwt
               ),
    timeout_milliseconds := 5000
  );
  return new;
end;
\$\$;

alter function public.deliver_signup_webhook() owner to postgres;
revoke all on function public.deliver_signup_webhook() from public, anon, authenticated, service_role;

-- 4. Attach trigger
drop trigger if exists signups_webhook_after_insert on public.signups;
create trigger signups_webhook_after_insert
after insert on public.signups
for each row execute function public.deliver_signup_webhook();
EOF
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f /tmp/setup_webhook.sql

CREATE EXTENSION
DO
CREATE FUNCTION
ALTER FUNCTION
REVOKE
DROP TRIGGER
CREATE TRIGGER

psql:/tmp/setup_webhook.sql:56: NOTICE:  trigger "signups_webhook_after_insert" for relation "public.signups" does not exist, skipping

EMAIL="db-trigger-test-$(date +%s)@example.com"
echo "Test email: $EMAIL"
echo "== Insert via REST API (service-role client) =="
curl -s -w "\nHTTP %{http_code}\n" -X POST "$SUPABASE_URL/rest/v1/signups" \
  -H "apikey: $SUPABASE_SERVICE_ROLE_KEY" \
  -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d "{\"email\":\"$EMAIL\",\"plan\":\"enterprise\"}"
echo "$EMAIL" > /tmp/test_email.txt
echo "== Waiting for async delivery =="
for i in 1 2 3 4 5 6; do
  sleep 2
  ROW=$(psql "$SUPABASE_DB_URL" -t -A -c "select id, payload from public.webhook_receipts where payload->>'email' = '$EMAIL' order by id desc limit 1;")
  if [ -n "$ROW" ]; then echo "RECEIVED after ~$((i*2))s:"; echo "$ROW"; break; fi
  echo "  ...not yet (${i}x2s)"
done
echo "== pg_net response log =="
psql "$SUPABASE_DB_URL" -c "select id, status_code, content, error_msg, created from net._http_response order by id desc limit 3;"

Test email: db-trigger-test-1784904915@example.com
== Insert via REST API (service-role client) ==
[{"id":1,"email":"db-trigger-test-1784904915@example.com","plan":"enterprise","created_at":"2026-07-24T14:55:15.976053+00:00"}]
HTTP 201
== Waiting for async delivery ==
RECEIVED after ~2s:
4|{"id": 1, "plan": "enterprise", "email": "db-trigger-test-1784904915@example.com", "created_at": "2026-07-24T14:55:15.976053+00:00"}
== pg_net response log ==
 id | status_code |   content   | error_msg |            created            
----+-------------+-------------+-----------+-------------------------------
  1 |         200 | {"ok":true} |           | 2026-07-24 14:55:15.981722+00
(1 row)

EMAIL="psql-client-test-$(date +%s)@example.com"
psql "$SUPABASE_DB_URL" -c "insert into public.signups (email, plan) values ('$EMAIL','pro') returning id, email, plan;"
for i in 1 2 3 4 5; do
  sleep 2
  ROW=$(psql "$SUPABASE_DB_URL" -t -A -c "select payload from public.webhook_receipts where payload->>'email' = '$EMAIL';")
  [ -n "$ROW" ] && { echo "RECEIVED (~$((i*2))s): $ROW"; break; }
done
# remove the two pre-implementation probe receipts, keep test evidence
psql "$SUPABASE_DB_URL" -c "delete from public.webhook_receipts where payload->>'probe' is not null;" -c "select id, received_at, payload->>'email' as email, payload->>'plan' as plan from public.webhook_receipts order by id;" -c "select tgname, tgrelid::regclass from pg_trigger where tgname='signups_webhook_after_insert';"

 id |                  email                  | plan 
----+-----------------------------------------+------
  2 | psql-client-test-1784904941@example.com | pro
(1 row)

INSERT 0 1
RECEIVED (~2s): {"id": 2, "plan": "pro", "email": "psql-client-test-1784904941@example.com", "created_at": "2026-07-24T14:55:42.132663+00:00"}
DELETE 2
 id |          received_at          |                  email                  |    plan    
----+-------------------------------+-----------------------------------------+------------
  4 | 2026-07-24 14:55:16.287706+00 | db-trigger-test-1784904915@example.com  | enterprise
  5 | 2026-07-24 14:55:42.302613+00 | psql-client-test-1784904941@example.com | pro
(2 rows)

            tgname            | tgrelid 
------------------------------+---------
 signups_webhook_after_insert | signups
(1 row)