claude-opus-4-8 · d2_webhooks · rep 2

env | grep -i supabase | sed 's/=.*/=<set>/'

SUPABASE_DB_PASSWORD=<set>
SUPABASE_DB_URL=<set>
SUPABASE_URL=<set>
SUPABASE_SERVICE_ROLE_KEY=<set>
SUPABASE_PROJECT_REF=<set>
SUPABASE_ANON_KEY=<set>

which psql || (apt-get update -qq && apt-get install -y -qq postgresql-client >/dev/null 2>&1; which psql)

/usr/bin/psql

echo "URL host: $(echo $SUPABASE_URL)"; echo "DB_URL (masked): $(echo $SUPABASE_DB_URL | sed 's/:[^:@]*@/:***@/')"

URL host: https://<ref>.supabase.co
DB_URL (masked): postgresql://postgres.<ref>:***@aws-0-us-east-1.pooler.supabase.com:5432/postgres

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

                                     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 | head -40

                                 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','supabase_vault') ORDER BY name;" 2>&1

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

psql "$SUPABASE_DB_URL" -c "SELECT nspname FROM pg_namespace WHERE nspname IN ('net','vault','extensions');" 2>&1

  nspname   
------------
 vault
 extensions
(2 rows)

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

{"ok":true}
HTTP 200

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

 id |         received_at          |                       payload                        
----+------------------------------+------------------------------------------------------
  2 | 2026-07-26 02:40:11.01065+00 | {"plan": "test-probe", "email": "probe@example.com"}
(1 row)

psql "$SUPABASE_DB_URL" -c "CREATE EXTENSION IF NOT EXISTS pg_net WITH SCHEMA extensions;" 2>&1
psql "$SUPABASE_DB_URL" -c "SELECT extname, extversion, n.nspname FROM pg_extension e JOIN pg_namespace n ON n.oid=e.extnamespace WHERE extname='pg_net';" 2>&1

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

# Store secrets in Vault. Use psql variables to avoid shell interpolation issues.
psql "$SUPABASE_DB_URL" -v jwt="$SUPABASE_SERVICE_ROLE_KEY" -v url="$SUPABASE_URL" <<'SQL' 2>&1
-- upsert helper: remove existing secrets with these names, then create
DELETE FROM vault.secrets WHERE name IN ('hook_recorder_jwt','hook_recorder_url');
SELECT vault.create_secret(:'jwt',  'hook_recorder_jwt', 'JWT for hook-recorder edge function');
SELECT vault.create_secret(:'url' || '/functions/v1/hook-recorder', 'hook_recorder_url', 'Endpoint for hook-recorder edge function');
SQL

DELETE 2
            create_secret             
--------------------------------------
 3990817c-e6dd-46c1-93d8-d13d758e4f9f
(1 row)

            create_secret             
--------------------------------------
 217635db-6224-4618-a123-d7c07c86f06d
(1 row)

psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
CREATE OR REPLACE FUNCTION public.signups_webhook_notify()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, extensions, vault
AS $fn$
DECLARE
  v_url   text;
  v_jwt   text;
  v_req_id bigint;
BEGIN
  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_jwt';

  SELECT net.http_post(
    url     := v_url,
    headers := jsonb_build_object(
                 'Content-Type',  'application/json',
                 'Authorization', 'Bearer ' || v_jwt
               ),
    body    := jsonb_build_object(
                 'email',      NEW.email,
                 'plan',       NEW.plan,
                 'id',         NEW.id,
                 'created_at', NEW.created_at
               )
  ) INTO v_req_id;

  RETURN NEW;
END;
$fn$;

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.signups_webhook_notify();
SQL

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

psql "$SUPABASE_DB_URL" -c "SELECT count(*) AS receipts_before FROM public.webhook_receipts;" 2>&1
# Test 1: direct SQL insert
psql "$SUPABASE_DB_URL" -c "INSERT INTO public.signups(email, plan) VALUES ('sql-test@example.com','pro') RETURNING id, email, plan;" 2>&1

 receipts_before 
-----------------
               1
(1 row)

 id |        email         | plan 
----+----------------------+------
  1 | sql-test@example.com | pro
(1 row)

INSERT 0 1

curl -s -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":"rest-client@example.com","plan":"enterprise"}' -w "\nHTTP %{http_code}\n"

[{"id":2,"email":"rest-client@example.com","plan":"enterprise","created_at":"2026-07-26T02:40:45.947275+00:00"}]
HTTP 201

sleep 6
echo "=== webhook_receipts ==="
psql "$SUPABASE_DB_URL" -c "SELECT id, received_at, payload FROM public.webhook_receipts ORDER BY id;" 2>&1
echo "=== pg_net responses ==="
psql "$SUPABASE_DB_URL" -c "SELECT id, status_code, (content::text) AS content, error_msg FROM net._http_response ORDER BY id DESC LIMIT 5;" 2>&1

=== webhook_receipts ===
 id |          received_at          |                                                        payload                                                        
----+-------------------------------+-----------------------------------------------------------------------------------------------------------------------
  2 | 2026-07-26 02:40:11.01065+00  | {"plan": "test-probe", "email": "probe@example.com"}
  3 | 2026-07-26 02:40:42.393419+00 | {"id": 1, "plan": "pro", "email": "sql-test@example.com", "created_at": "2026-07-26T02:40:42.179518+00:00"}
  4 | 2026-07-26 02:40:46.089767+00 | {"id": 2, "plan": "enterprise", "email": "rest-client@example.com", "created_at": "2026-07-26T02:40:45.947275+00:00"}
(3 rows)

=== pg_net responses ===
 id | status_code |   content   | error_msg 
----+-------------+-------------+-----------
  2 |         200 | {"ok":true} | 
  1 |         200 | {"ok":true} | 
(2 rows)

psql "$SUPABASE_DB_URL" -c "DELETE FROM public.signups WHERE email IN ('sql-test@example.com','rest-client@example.com');" 2>&1
echo "--- verify trigger still attached ---"
psql "$SUPABASE_DB_URL" -c "SELECT tgname, tgenabled FROM pg_trigger WHERE tgrelid='public.signups'::regclass AND NOT tgisinternal;" 2>&1

DELETE 2
--- verify trigger still attached ---
         tgname          | tgenabled 
-------------------------+-----------
 signups_webhook_trigger | O
(1 row)