z-ai/glm-5.2 · d2_webhooks · rep 3

echo "SUPABASE_URL=$SUPABASE_URL"; echo "SUPABASE_PROJECT_REF=$SUPABASE_PROJECT_REF"; echo "SUPABASE_DB_URL=$SUPABASE_DB_URL"; echo "Keys present: $(test -n "$SUPABASE_ANON_KEY" && echo anon; test -n "$SUPABASE_SERVICE_ROLE_KEY" && echo service; test -n "$SUPABASE_DB_PASSWORD" && echo dbpass)"

SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_PROJECT_REF=<ref>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
Keys present: anon
service
dbpass

which psql || apt-get install -y postgresql-client 2>/dev/null; which curl; which jq

/usr/bin/psql
/usr/bin/curl
/usr/bin/jq

psql "$SUPABASE_DB_URL" -c "\d public.signups" 2>&1

                                     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)

psql "$SUPABASE_DB_URL" -c "\d public.webhook_receipts" 2>&1

                                 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 extname, extversion FROM pg_extension WHERE extname IN ('pg_net','pgcrypto','pgjwt');" 2>&1

 extname  | extversion 
----------+------------
 pgcrypto | 1.3
(1 row)

psql "$SUPABASE_DB_URL" -c "SELECT schema_name FROM information_schema.schemata WHERE schema_name IN ('net','pgsodium','pgsodium','pgjwt');" 2>&1

 schema_name 
-------------
(0 rows)

psql "$SUPABASE_DB_URL" -c "CREATE EXTENSION IF NOT EXISTS pg_net SCHEMA extensions;" 2>&1

CREATE EXTENSION

psql "$SUPABASE_DB_URL" -c "SELECT nspname FROM pg_namespace WHERE nspname='extensions';" 2>&1

  nspname   
------------
 extensions
(1 row)

psql "$SUPABASE_DB_URL" -c "SELECT proname, pronamespace::regnamespace FROM pg_proc WHERE proname IN ('http_post','http_get');" 2>&1

  proname  | pronamespace 
-----------+--------------
 http_get  | net
 http_post | net
(2 rows)

psql "$SUPABASE_DB_URL" -c "SELECT schemaname, tablename, rowsecurity FROM pg_tables WHERE tablename='signups';" 2>&1

 schemaname | tablename | rowsecurity 
------------+-----------+-------------
 public     | signups   | f
(1 row)

psql "$SUPABASE_DB_URL" -c "SELECT schemaname, tablename, rowsecurity FROM pg_tables WHERE tablename='webhook_receipts';" 2>&1

 schemaname |    tablename     | rowsecurity 
------------+------------------+-------------
 public     | webhook_receipts | f
(1 row)

curl -s -o /dev/null -w "%{http_code}" -X POST "$SUPABASE_URL/functions/v1/hook-recorder" \
  -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" \
  -H "Content-Type: application/json" \
  -d '{"email":"test-curl@example.com","plan":"free"}'

200

psql "$SUPABASE_DB_URL" -c "SELECT * FROM public.webhook_receipts ORDER BY id DESC LIMIT 3;" 2>&1

 id |          received_at          |                      payload                       
----+-------------------------------+----------------------------------------------------
  2 | 2026-07-25 05:22:18.717266+00 | {"plan": "free", "email": "test-curl@example.com"}
(1 row)

psql "$SUPABASE_DB_URL" -c "SELECT extname FROM pg_extension WHERE extname='pgsodium';" 2>&1

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

psql "$SUPABASE_DB_URL" -c "SELECT table_schema, table_name FROM information_schema.tables WHERE table_name='decrypted_secrets';" 2>&1

 table_schema |    table_name     
--------------+-------------------
 vault        | decrypted_secrets
(1 row)

psql "$SUPABASE_DB_URL" -c "SELECT id, name, description FROM vault.decrypted_secrets;" 2>&1

                  id                  |              name              |                        description                        
--------------------------------------+--------------------------------+-----------------------------------------------------------
 969f9073-a502-404b-8fa0-ff5772de7aaf | hook_recorder_url              | Webhook receiver endpoint for signups inserts
 353f71e8-865e-41f5-b189-9dd648467d13 | hook_recorder_jwt              | Project JWT used to call hook-recorder edge function
 ebb2bcb8-e75c-48d1-aa04-6517d29c38d0 | hook_recorder_service_role_key | JWT used by signups webhook trigger to call hook-recorder
(3 rows)

psql "$SUPABASE_DB_URL" -c "SELECT name, left(decrypted_secret, 30) AS secret_prefix FROM vault.decrypted_secrets;" 2>&1

              name              |         secret_prefix          
--------------------------------+--------------------------------
 hook_recorder_url              | https://<ref>.s
 hook_recorder_jwt              | eyJhbGciOiJIUzI1NiIsInR5cCI6Ik
 hook_recorder_service_role_key | eyJhbGciOiJIUzI1NiIsInR5cCI6Ik
(3 rows)

psql "$SUPABASE_DB_URL" -c "SELECT name, decrypted_secret = '$SUPABASE_SERVICE_ROLE_KEY' AS matches_service_role FROM vault.decrypted_secrets WHERE name='hook_recorder_service_role_key';" 2>&1

              name              | matches_service_role 
--------------------------------+----------------------
 hook_recorder_service_role_key | t
(1 row)

psql "$SUPABASE_DB_URL" -c "SELECT decrypted_secret FROM vault.decrypted_secrets WHERE name='hook_recorder_url';" 2>&1

                          decrypted_secret                           
---------------------------------------------------------------------
 https://<ref>.supabase.co/functions/v1/hook-recorder
(1 row)

psql "$SUPABASE_DB_URL" -c "
-- Drop old trigger/function if any
DROP TRIGGER IF EXISTS signups_webhook_trigger ON public.signups;
DROP FUNCTION IF EXISTS public.notify_signups_webhook();

-- Create the trigger function that uses pg_net to POST to the edge function.
-- It reads the JWT and URL from the Supabase Vault so no secret is hardcoded.
CREATE OR REPLACE FUNCTION public.notify_signups_webhook()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, extensions, vault
AS \$func\$
DECLARE
    v_url  text;
    v_jwt  text;
    v_body jsonb;
    req_id bigint;
BEGIN
    -- Only fire on INSERT
    IF TG_OP <> 'INSERT' THEN
        RETURN NEW;
    END IF;

    -- Fetch the receiver URL and the service-role JWT from the Vault.
    SELECT decrypted_secret INTO v_url
      FROM vault.decrypted_secrets
     WHERE name = 'hook_recorder_url';

    SELECT decrypted_secret INTO v_jwt
      FROM vault.decrypted_secrets
     WHERE name = 'hook_recorder_service_role_key';

    IF v_url IS NULL OR v_jwt IS NULL THEN
        RAISE WARNING 'signups webhook: missing URL or JWT in vault';
        RETURN NEW;
    END IF;

    -- Build the payload (includes email and plan, plus the row id for traceability).
    v_body := jsonb_build_object(
        'id',       NEW.id,
        'email',    NEW.email,
        'plan',     NEW.plan,
        'created_at', NEW.created_at
    );

    -- Fire-and-forget async POST via pg_net (runs in a background worker).
    PERFORM net.http_post(
        url           := v_url,
        headers       := jsonb_build_object(
            'Authorization', 'Bearer ' || v_jwt,
            'Content-Type',  'application/json'
        ),
        body          := v_body
    );

    RETURN NEW;
END;
\$func\$;
" 2>&1

NOTICE:  trigger "signups_webhook_trigger" for relation "public.signups" does not exist, skipping
NOTICE:  function public.notify_signups_webhook() does not exist, skipping
DROP TRIGGER
DROP FUNCTION
CREATE FUNCTION

psql "$SUPABASE_DB_URL" -c "
CREATE TRIGGER signups_webhook_trigger
    AFTER INSERT ON public.signups
    FOR EACH ROW
    EXECUTE FUNCTION public.notify_signups_webhook();
" 2>&1

CREATE TRIGGER

psql "$SUPABASE_DB_URL" -c "INSERT INTO public.signups (email, plan) VALUES ('e2e-test@example.com', 'pro') RETURNING id, email, plan, created_at;" 2>&1

 id |        email         | plan |          created_at           
----+----------------------+------+-------------------------------
  1 | e2e-test@example.com | pro  | 2026-07-25 05:22:50.032616+00
(1 row)

INSERT 0 1

sleep 5; psql "$SUPABASE_DB_URL" -c "SELECT * FROM public.webhook_receipts ORDER BY id DESC LIMIT 5;" 2>&1

 id |          received_at          |                                                   payload                                                   
----+-------------------------------+-------------------------------------------------------------------------------------------------------------
  3 | 2026-07-25 05:22:50.215302+00 | {"id": 1, "plan": "pro", "email": "e2e-test@example.com", "created_at": "2026-07-25T05:22:50.032616+00:00"}
  2 | 2026-07-25 05:22:18.717266+00 | {"plan": "free", "email": "test-curl@example.com"}
(2 rows)