openai/gpt-5.6-sol · d2_webhooks · rep 3

set -e
command -v psql
printf 'URL=%s\nREF=%s\nDBURL_set=%s\n' "$SUPABASE_URL" "$SUPABASE_PROJECT_REF" "${SUPABASE_DB_URL:+yes}"
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X -c "select current_database(), current_user, version();"

/usr/bin/psql
URL=https://<ref>.supabase.co
REF=<ref>
DBURL_set=yes
 current_database | current_user |                                   version                                    
------------------+--------------+------------------------------------------------------------------------------
 postgres         | postgres     | PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit
(1 row)

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
\d+ public.signups
\d+ public.webhook_receipts
select extname, extversion from pg_extension where extname in ('pg_net','supabase_vault');
select n.nspname, p.proname, pg_get_function_identity_arguments(p.oid)
from pg_proc p join pg_namespace n on n.oid=p.pronamespace
where (n.nspname='net' and p.proname like 'http%') or n.nspname='vault'
order by 1,2;
select event_object_schema,event_object_table,trigger_name,action_timing,event_manipulation,action_statement
from information_schema.triggers where event_object_schema='public' and event_object_table='signups';
SQL

                                                                Table "public.signups"
   Column   |           Type           | Collation | Nullable |             Default              | Storage  | Compression | Stats target | Description 
------------+--------------------------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------
 id         | bigint                   |           | not null | generated by default as identity | plain    |             |              | 
 email      | text                     |           | not null |                                  | extended |             |              | 
 plan       | text                     |           | not null | 'free'::text                     | extended |             |              | 
 created_at | timestamp with time zone |           | not null | now()                            | plain    |             |              | 
Indexes:
    "signups_pkey" PRIMARY KEY, btree (id)
Access method: heap

                                                            Table "public.webhook_receipts"
   Column    |           Type           | Collation | Nullable |             Default              | Storage  | Compression | Stats target | Description 
-------------+--------------------------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------
 id          | bigint                   |           | not null | generated by default as identity | plain    |             |              | 
 received_at | timestamp with time zone |           | not null | now()                            | plain    |             |              | 
 payload     | jsonb                    |           | not null |                                  | extended |             |              | 
Indexes:
    "webhook_receipts_pkey" PRIMARY KEY, btree (id)
Access method: heap

    extname     | extversion 
----------------+------------
 supabase_vault | 0.3.1
(1 row)

 nspname |          proname          |                          pg_get_function_identity_arguments                           
---------+---------------------------+---------------------------------------------------------------------------------------
 vault   | _crypto_aead_det_decrypt  | message bytea, additional bytea, key_id bigint, context bytea, nonce bytea
 vault   | _crypto_aead_det_encrypt  | message bytea, additional bytea, key_id bigint, context bytea, nonce bytea
 vault   | _crypto_aead_det_noncegen | 
 vault   | create_secret             | new_secret text, new_name text, new_description text, new_key_id uuid
 vault   | update_secret             | secret_id uuid, new_secret text, new_name text, new_description text, new_key_id uuid
(5 rows)

 event_object_schema | event_object_table | trigger_name | action_timing | event_manipulation | action_statement 
---------------------+--------------------+--------------+---------------+--------------------+------------------
(0 rows)

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
select name, default_version, installed_version from pg_available_extensions where name='pg_net';
select * from vault.decrypted_secrets;
SQL

  name  | default_version | installed_version 
--------+-----------------+-------------------
 pg_net | 0.20.4          | 
(1 row)

                  id                  |        name         |                        description                         |                                    secret                                    |                                                                                                      decrypted_secret                                                                                                       | key_id |               nonce                |          created_at           |          updated_at           
--------------------------------------+---------------------+------------------------------------------------------------+------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------------------+-------------------------------+-------------------------------
 603fc4db-6e11-4f7a-989e-e5259b6b67b9 | project_url         | Base URL of this Supabase project                          | AftF+/ZtUSPswejTgvoDAspz+HxiB1jSnH8upaiU7yJ151YqXJNR47xJ75z6/2ok8R7DgqqRYqDT+| https://<ref>.supabase.co                                                                                                                                                                                    |        | \x41ca7ea16bdfff8e1dea7585ea8bcfb9 | 2026-07-24 05:07:11.304907+00 | 2026-07-24 05:07:11.304907+00
                                      |                     |                                                            | fq0i0DRlMEwQZiYzreb4                                                         |                                                                                                                                                                                                                             |        |                                    |                               | 
 e5a09663-2d57-4cd1-8886-def1a3074bfd | service_role_key    | Service role JWT used for internal webhook calls           | hurYbqM8oPpZwbM0jgeW/4JuckuP1hBnN4vqi/yPLNgSqGY7f/J5X4xzgjZQp0hNExCTxrKNqLnV+| <redacted:SUPABASE_SERVICE_ROLE_KEY> |        | \x9e135af4febd57f3b2a5dfeb655dcc3d | 2026-07-24 05:07:11.421055+00 | 2026-07-24 05:07:11.421055+00
                                      |                     |                                                            | NgOOx2iQfHlrHurpttQh3VHZ5mBaTAACnN8fVZIMwsQR5tR5smr7Z36ijs34MXs3hsLOFy+gnfW6+|                                                                                                                                                                                                                             |        |                                    |                               | 
                                      |                     |                                                            | CDTPst75fpoBtVfHihc4qdW3RESbqJ6Ekpei2TOaZuN8hxcuVBDveMfbwpuaycQHjgU6p11h0ZBW+|                                                                                                                                                                                                                             |        |                                    |                               | 
                                      |                     |                                                            | 4rQWjBMNOBsTQAUaecMzw9aX/sAeQGljnt0DzBwcjKckEAdUoJGvcV+b1HI6r22gvZiabzicE2OL+|                                                                                                                                                                                                                             |    

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
create extension if not exists pg_net with schema extensions;
select n.nspname, p.proname, pg_get_function_identity_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';
SQL

CREATE EXTENSION
 nspname |  proname  |                       pg_get_function_identity_arguments                        | pg_get_function_result 
---------+-----------+---------------------------------------------------------------------------------+------------------------
 net     | http_post | url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer | bigint
(1 row)

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
CREATE OR REPLACE FUNCTION public.deliver_signup_webhook()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public, vault, net
AS $function$
DECLARE
  project_url text;
  project_jwt text;
  request_id bigint;
BEGIN
  SELECT decrypted_secret INTO STRICT project_url
  FROM vault.decrypted_secrets
  WHERE name = 'project_url';

  SELECT decrypted_secret INTO STRICT project_jwt
  FROM vault.decrypted_secrets
  WHERE name = 'service_role_key';

  SELECT net.http_post(
    url := project_url || '/functions/v1/hook-recorder',
    body := jsonb_build_object(
      'email', NEW.email,
      'plan', NEW.plan,
      'id', NEW.id,
      'created_at', NEW.created_at
    ),
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || project_jwt
    ),
    timeout_milliseconds := 5000
  ) INTO request_id;

  RETURN NEW;
END;
$function$;

REVOKE ALL ON FUNCTION public.deliver_signup_webhook() FROM PUBLIC;

DROP TRIGGER IF EXISTS signups_deliver_webhook ON public.signups;
CREATE TRIGGER signups_deliver_webhook
AFTER INSERT ON public.signups
FOR EACH ROW
EXECUTE FUNCTION public.deliver_signup_webhook();

COMMENT ON FUNCTION public.deliver_signup_webhook() IS
  'Queues an authenticated Edge Function webhook through pg_net for each new signup.';
SQL

CREATE FUNCTION
REVOKE
DROP TRIGGER
CREATE TRIGGER
COMMENT

NOTICE:  trigger "signups_deliver_webhook" for relation "public.signups" does not exist, skipping

set -euo pipefail
TEST_EMAIL="webhook-e2e-$(date +%s)-$RANDOM@example.com"
TEST_PLAN="pro-e2e"
export TEST_EMAIL TEST_PLAN
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "INSERT INTO public.signups(email, plan) VALUES ('$TEST_EMAIL', '$TEST_PLAN') RETURNING id,email,plan,created_at;"
for i in $(seq 1 20); do
  found=$(psql "$SUPABASE_DB_URL" -X -Aqt -v ON_ERROR_STOP=1 -c "SELECT count(*) FROM public.webhook_receipts WHERE payload->>'email'='$TEST_EMAIL' AND payload->>'plan'='$TEST_PLAN';")
  if [ "$found" -gt 0 ]; then
    echo "Webhook receipt found after ${i}s:"
    psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "SELECT id,received_at,payload FROM public.webhook_receipts WHERE payload->>'email'='$TEST_EMAIL' AND payload->>'plan'='$TEST_PLAN' ORDER BY id DESC LIMIT 1;"
    exit 0
  fi
  sleep 1
done
echo 'No receipt found; request diagnostics:' >&2
psql "$SUPABASE_DB_URL" -X -c "select * from net._http_response order by created desc limit 5;" >&2 || true
exit 1

 id |                  email                   |  plan   |          created_at           
----+------------------------------------------+---------+-------------------------------
  1 | webhook-e2e-1784872765-21931@example.com | pro-e2e | 2026-07-24 05:59:25.684838+00
(1 row)

INSERT 0 1
Webhook receipt found after 1s:
 id |          received_at          |                                                               payload                                                               
----+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------
  2 | 2026-07-24 05:59:25.977199+00 | {"id": 1, "plan": "pro-e2e", "email": "webhook-e2e-1784872765-21931@example.com", "created_at": "2026-07-24T05:59:25.684838+00:00"}
(1 row)