Register Supabase function hook with secret management

register_supabase_function_hook.sql

-- set your base URL and service role key - you need to do this once per environment
select
  vault.create_secret (
    '<your-key-here>',
    'service_role_key',
    'Supabase service_role key'
  );

select
  vault.create_secret (
    '<your-base-url-here>',
    'supabase_functions_base_url',
    'Supabase functions base URL'
  );

-- define a DB function that calls the edge functions via API
-- largely based on the original function supabase_functions.http_request https://github.com/supabase-community/supabase-graphql-example/blob/main/data/db/schema.sql#L1261
CREATE
OR REPLACE FUNCTION trigger_edge_function () RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
      request_id bigint;
      payload jsonb;
      function_name text := TG_ARGV[0]::text;
      base_url text;
      service_key text;
      request_url text;
      method text := TG_ARGV[1]::text;
      headers jsonb DEFAULT '{}'::jsonb;
      params jsonb DEFAULT '{}'::jsonb;
      timeout_ms integer DEFAULT 1000;
    BEGIN
      IF function_name IS NULL OR function_name = 'null' THEN RAISE EXCEPTION 'function_name argument is missing'; END IF;
      IF method IS NULL OR method = 'null' THEN RAISE EXCEPTION 'method argument is missing'; END IF;

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

      IF TG_ARGV[2] IS NULL OR TG_ARGV[2] = 'null' THEN
        headers := json_build_object(
            'Content-Type', 'application/json',
            'Authorization', 'Bearer ' || service_key
        );
      ELSE
        headers = TG_ARGV[2]::jsonb;
      END IF;

      IF TG_ARGV[3] IS NULL OR TG_ARGV[3] = 'null' THEN
        params = '{}'::jsonb;
      ELSE
        params = TG_ARGV[3]::jsonb;
      END IF;

      IF TG_ARGV[4] IS NULL OR TG_ARGV[4] = 'null' THEN
        timeout_ms = 1000;
      ELSE
        timeout_ms = TG_ARGV[4]::integer;
      END IF;


    SELECT decrypted_secret
    INTO base_url
    FROM vault.decrypted_secrets
    WHERE name = 'supabase_functions_base_url';

    request_url := base_url || '/' || function_name; 

      CASE
        WHEN method = 'GET' THEN
          SELECT http_get INTO request_id FROM net.http_get(
            request_url,
            params,
            headers,
            timeout_ms
          );
        WHEN method = 'POST' THEN
          payload = jsonb_build_object(
            'old_record', OLD,
            'record', NEW,
            'type', TG_OP,
            'table', TG_TABLE_NAME,
            'schema', TG_TABLE_SCHEMA
          );

          SELECT http_post INTO request_id FROM net.http_post(
            request_url,
            payload,
            params,
            headers,
            timeout_ms
          );
        ELSE
          RAISE EXCEPTION 'method argument % is invalid', method;
      END CASE;

      RETURN NEW;
    END
    $$;

-- create trigger
CREATE
OR REPLACE TRIGGER your_trigger_name
AFTER INSERT ON public.your_table FOR EACH ROW
EXECUTE FUNCTION trigger_edge_function ('dummy', 'POST');

At the time of writing Supabase does not provide secret management for webhook / edge function invocations and requires you to copy authorization header data into your hook definitions, thus potentially exposing them in your migration files and version control.
The current way of creating web hooks via Supabase Studio (SaaS only, not available in local or self-hosted setup) would hard-code the edge functions URL, making it hard to use the same function across environments.

The code snippet fixes these issues by storing your project's edge functions API base URL and the required secret key in thevault schema and defining a function that allows for calling an edge function by name.

Setting the base URL

• Use http://host.docker.internal:54321/functions/v1 for local development
• Use https://<your_project_id>.supabase.co/functions/v1 for hosted environments