minimax/minimax-m3 · d2_webhooks · rep 1

echo "SUPABASE_URL=$SUPABASE_URL"; echo "SUPABASE_PROJECT_REF=$SUPABASE_PROJECT_REF"; echo "SUPABASE_DB_URL=$SUPABASE_DB_URL"; echo "SUPABASE_ANON_KEY=${SUPABASE_ANON_KEY:0:20}..."; echo "SUPABASE_SERVICE_ROLE_KEY=${SUPABASE_SERVICE_ROLE_KEY:0:20}..."

SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_PROJECT_REF=<ref>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIs...
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIs...

which psql; psql --version

/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"; 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)

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"; psql "$SUPABASE_DB_URL" -c "SELECT extname, extversion FROM pg_extension;"

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

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"; psql "$SUPABASE_DB_URL" -c "SELECT name, default_version, installed_version FROM pg_available_extensions WHERE name IN ('pg_net','pg_cron');"

  name   | default_version | installed_version 
---------+-----------------+-------------------
 pg_net  | 0.20.4          | 
 pg_cron | 1.6.4           | 
(2 rows)

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"; psql "$SUPABASE_DB_URL" -c "CREATE EXTENSION IF NOT EXISTS pg_net;" 2>&1

CREATE EXTENSION

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

{"ok":true}
HTTP 200

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"; psql "$SUPABASE_DB_URL" -c "SELECT count(*) AS receipts FROM public.webhook_receipts; DELETE FROM public.webhook_receipts WHERE payload->>'test' = 'preflight';"

 receipts 
----------
        1
(1 row)

DELETE 1

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"; psql "$SUPABASE_DB_URL" <<'SQL'
CREATE OR REPLACE FUNCTION public.fn_signups_webhook()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_payload jsonb;
  v_jwt    text;
  v_url    text;
  v_req_id bigint;
BEGIN
  -- Build the JSON body from the new row.
  v_payload := jsonb_build_object(
    'id',         NEW.id,
    'email',      NEW.email,
    'plan',       NEW.plan,
    'created_at', NEW.created_at,
    'table',      'signups',
    'event',      'INSERT'
  );

  -- The service-role JWT is needed by the edge function (valid project JWT).
  -- Read it from a custom GUC so it does not appear in pg_proc.
  v_jwt := current_setting('app.settings.service_role_key', true);
  IF v_jwt IS NULL OR v_jwt = '' THEN
    -- Fallback (still SECURITY DEFINER, only callable as the trigger):
    v_jwt := '<SERVICE_ROLE_KEY>';
  END IF;

  v_url := current_setting('app.settings.supabase_url', true)
        || '/functions/v1/hook-recorder';

  -- Fire-and-forget async POST via pg_net.
  SELECT net.http_post(
           url     := v_url,
           headers := jsonb_build_object(
             'Content-Type',  'application/json',
             'Authorization', 'Bearer ' || v_jwt,
             'x-source',      'db-trigger:signups'
           ),
           body    := v_payload::text
         )
    INTO v_req_id;

  -- Stash the request id on the row for traceability (best effort).
  BEGIN
    UPDATE public.signups
       SET email = NEW.email
     WHERE id = NEW.id;
  EXCEPTION WHEN OTHERS THEN
    -- ignore; webhook is best-effort
    NULL;
  END;

  RETURN NEW;
END;
$$;

-- Make sure ordinary roles can't read the function source / be evilly tampered with.
REVOKE ALL ON FUNCTION public.fn_signups_webhook() FROM PUBLIC;
SQL

CREATE FUNCTION
REVOKE

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"; psql "$SUPABASE_DB_URL" <<SQL
-- Persist config for the database so pg_net can read it.
ALTER DATABASE postgres SET app.settings.supabase_url        = '$SUPABASE_URL';
ALTER DATABASE postgres SET app.settings.service_role_key   = '$SUPABASE_SERVICE_ROLE_KEY';

-- Reload so current_setting works without reconnecting.
SELECT pg_reload_conf();

-- Create the AFTER INSERT trigger.
DROP TRIGGER IF EXISTS trg_signups_webhook ON public.signups;
CREATE TRIGGER trg_signups_webhook
  AFTER INSERT ON public.signups
  FOR EACH ROW
  EXECUTE FUNCTION public.fn_signups_webhook();
SQL

 pg_reload_conf 
----------------
 t
(1 row)

DROP TRIGGER
CREATE TRIGGER

ERROR:  permission denied to set parameter "app.settings.supabase_url"
ERROR:  permission denied to set parameter "app.settings.service_role_key"
NOTICE:  trigger "trg_signups_webhook" for relation "public.signups" does not exist, skipping

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"; psql "$SUPABASE_DB_URL" -c "SELECT current_user, session_user, current_setting('is_superuser');"

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

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"; psql "$SUPABASE_DB_URL" <<SQL
ALTER ROLE postgres IN DATABASE postgres SET app.settings.supabase_url      = '$SUPABASE_URL';
ALTER ROLE postgres IN DATABASE postgres SET app.settings.service_role_key = '$SUPABASE_SERVICE_ROLE_KEY';
SQL

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

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"; psql "$SUPABASE_DB_URL" -c "SELECT rolname, rolsuper, rolconfig FROM pg_roles WHERE rolname='postgres';"

 rolname  | rolsuper |                    rolconfig                    
----------+----------+-------------------------------------------------
 postgres | f        | {"search_path=\"\\$user\", public, extensions"}
(1 row)

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"; psql "$SUPABASE_DB_URL" -c "SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'app.%';"

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

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"; psql "$SUPABASE_DB_URL" <<SQL
CREATE OR REPLACE FUNCTION public.fn_signups_webhook()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_payload jsonb;
  v_jwt    text := '$SUPABASE_SERVICE_ROLE_KEY';
  v_url    text := '$SUPABASE_URL/functions/v1/hook-recorder';
  v_req_id bigint;
BEGIN
  v_payload := jsonb_build_object(
    'id',         NEW.id,
    'email',      NEW.email,
    'plan',       NEW.plan,
    'created_at', NEW.created_at,
    'table',      'signups',
    'event',      'INSERT'
  );

  SELECT net.http_post(
           url     := v_url,
           headers := jsonb_build_object(
             'Content-Type',  'application/json',
             'Authorization', 'Bearer ' || v_jwt,
             'x-source',      'db-trigger:signups'
           ),
           body    := v_payload::text
         )
    INTO v_req_id;

  RETURN NEW;
END;
$$;

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

DROP TRIGGER IF EXISTS trg_signups_webhook ON public.signups;
CREATE TRIGGER trg_signups_webhook
  AFTER INSERT ON public.signups
  FOR EACH ROW
  EXECUTE FUNCTION public.fn_signups_webhook();
SQL

COMMIT
REVOKE
DROP TRIGGER
CREATE TRIGGER

ERROR:  syntax error at or near "125"
LINE 6: AS 125
           ^
ERROR:  syntax error at or near "v_jwt"
LINE 1: v_jwt    text := 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc...
        ^
ERROR:  syntax error at or near "v_url"
LINE 1: v_url    text := 'https://<ref>.supabase.co/f...
        ^
ERROR:  syntax error at or near "v_req_id"
LINE 1: v_req_id bigint;
        ^
ERROR:  syntax error at or near "v_payload"
LINE 2:   v_payload := jsonb_build_object(
          ^
ERROR:  column "v_url" does not exist
LINE 2:            url     := v_url,
                              ^
ERROR:  syntax error at or near "RETURN"
LINE 1: RETURN NEW;
        ^
WARNING:  there is no transaction in progress
ERROR:  syntax error at or near "125"
LINE 1: 125;
        ^

printf '%s\n' "$SUPABASE_SERVICE_ROLE_KEY" | grep -o "'" | head -1; echo "---"; printf '%s\n' "$SUPABASE_SERVICE_ROLE_KEY" | head -c 80

---
<jwt:token>

cat > /tmp/install_trigger.sql <<'EOF'
CREATE OR REPLACE FUNCTION public.fn_signups_webhook()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $fn$
DECLARE
  v_payload jsonb;
  v_jwt    text := :'srk';
  v_url    text := :'surl' || '/functions/v1/hook-recorder';
  v_req_id bigint;
BEGIN
  v_payload := jsonb_build_object(
    'id',         NEW.id,
    'email',      NEW.email,
    'plan',       NEW.plan,
    'created_at', NEW.created_at,
    'table',      'signups',
    'event',      'INSERT'
  );

  SELECT net.http_post(
           url     := v_url,
           headers := jsonb_build_object(
             'Content-Type',  'application/json',
             'Authorization', 'Bearer ' || v_jwt,
             'x-source',      'db-trigger:signups'
           ),
           body    := v_payload::text
         )
    INTO v_req_id;

  RETURN NEW;
END;
$fn$;

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

DROP TRIGGER IF EXISTS trg_signups_webhook ON public.signups;
CREATE TRIGGER trg_signups_webhook
  AFTER INSERT ON public.signups
  FOR EACH ROW
  EXECUTE FUNCTION public.fn_signups_webhook();
EOF
echo "--- script written ---"
wc -l /tmp/install_trigger.sql

--- script written ---
43 /tmp/install_trigger.sql

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"
psql "$SUPABASE_DB_URL" \
  --set ON_ERROR_STOP=1 \
  -v srk="$SUPABASE_SERVICE_ROLE_KEY" \
  -v surl="$SUPABASE_URL" \
  -f /tmp/install_trigger.sql 2>&1

psql:/tmp/install_trigger.sql:35: ERROR:  syntax error at or near ":"
LINE 9:   v_jwt    text := :'srk';
                           ^

grep -F '$' <<<"$SUPABASE_SERVICE_ROLE_KEY$SUPABASE_URL" | head -5; echo "---"; printf '%s' "$SUPABASE_SERVICE_ROLE_KEY" | grep -cF '$'

---
0

SRK="$SUPABASE_SERVICE_ROLE_KEY"
SURL="$SUPABASE_URL"
sed -e "s|__SRK__|${SRK}|g" \
    -e "s|__SURL__|${SURL}|g" \
    > /tmp/install_trigger_filled.sql <<'EOF'
CREATE OR REPLACE FUNCTION public.fn_signups_webhook()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $fn$
DECLARE
  v_payload jsonb;
  v_jwt    text := '__SRK__';
  v_url    text := '__SURL__' || '/functions/v1/hook-recorder';
  v_req_id bigint;
BEGIN
  v_payload := jsonb_build_object(
    'id',         NEW.id,
    'email',      NEW.email,
    'plan',       NEW.plan,
    'created_at', NEW.created_at,
    'table',      'signups',
    'event',      'INSERT'
  );

  SELECT net.http_post(
           url     := v_url,
           headers := jsonb_build_object(
             'Content-Type',  'application/json',
             'Authorization', 'Bearer ' || v_jwt,
             'x-source',      'db-trigger:signups'
           ),
           body    := v_payload::text
         )
    INTO v_req_id;

  RETURN NEW;
END;
$fn$;

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

DROP TRIGGER IF EXISTS trg_signups_webhook ON public.signups;
CREATE TRIGGER trg_signups_webhook
  AFTER INSERT ON public.signups
  FOR EACH ROW
  EXECUTE FUNCTION public.fn_signups_webhook();
EOF
grep -n "v_jwt\|v_url " /tmp/install_trigger_filled.sql | head -5

9:  v_jwt    text := '<redacted:SUPABASE_SERVICE_ROLE_KEY>';
10:  v_url    text := 'https://<ref>.supabase.co' || '/functions/v1/hook-recorder';
26:             'Authorization', 'Bearer ' || v_jwt,

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"
psql "$SUPABASE_DB_URL" --set ON_ERROR_STOP=1 -f /tmp/install_trigger_filled.sql 2>&1

CREATE FUNCTION
REVOKE
DROP TRIGGER
CREATE TRIGGER

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>"
psql "$SUPABASE_DB_URL" -c "\df public.fn_signups_webhook" -c "SELECT tgname, tgenabled, tgtype FROM pg_trigger WHERE tgrelid = 'public.signups'::regclass AND NOT tgisinternal;"

                              List of functions
 Schema |        Name        | Result data type | Argument data types | Type 
--------+--------------------+------------------+---------------------+------
 public | fn_signups_webhook | trigger          |                     | func
(1 row)

       tgname        | tgenabled | tgtype 
---------------------+-----------+--------
 trg_signups_webhook | O         |      5
(1 row)