Running Database Webhooks Locally with Supabase CLI

Supabase supports database webhooks when running a hosted instance, but I never could get them to work when running Supabase locally via the CLI. I would get an error:

Failed to create hook: failed to create pg.triggers: schema "supabase_functions" does not exist

I just noticed this PR merged in yesterday, maybe this fixes it 🤷‍♂️

It seems like the DB schema that is setup for local Supabase is not complete compared to hosted Supabase, because the "supabase_functions" schema isn't defined for local Supabase (even if you go into the dashboard UI > Database > Webhooks and click the "Enable Hooks" button).

The way I solved worked around this was making my own custom function that is pretty much a copy/paste of the official supabase_functions code:

CREATE
OR REPLACE FUNCTION send_to_webhook () RETURNS TRIGGER LANGUAGE plpgsql AS $function$
    DECLARE
      request_id bigint;
      payload jsonb;
      url text := TG_ARGV[0]::text;
      method text := TG_ARGV[1]::text;
      headers jsonb DEFAULT '{}'::jsonb;
      params jsonb DEFAULT '{}'::jsonb;
      timeout_ms integer DEFAULT 1000;
    BEGIN
      IF url IS NULL OR url = 'null' THEN
        RAISE EXCEPTION 'url argument is missing';
      END IF;

      IF method IS NULL OR method = 'null' THEN
        RAISE EXCEPTION 'method argument is missing';
      END IF;

      IF TG_ARGV[2] IS NULL OR TG_ARGV[2] = 'null' THEN
        headers = '{"Content-Type": "application/json"}'::jsonb;
      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;

      CASE
        WHEN method = 'GET' THEN
          SELECT http_get INTO request_id FROM net.http_get(
            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(
            url,
            payload,
            params,
            headers,
            timeout_ms
          );
        ELSE
          RAISE EXCEPTION 'method argument % is invalid', method;
      END CASE;

      RETURN NEW;
    END
  $function$;

Now I can create a trigger to call that function with something like:

-- now create the trigger
CREATE TRIGGER
    "domains-sync"
AFTER
    INSERT
    OR DELETE
    OR UPDATE
    ON public.domains FOR EACH ROW
EXECUTE
    FUNCTION send_to_webhook (
        'http://host.docker.internal:8888/api/hook-sb-domains',
        'POST',
        '{"Content-type":"application/json","x-custom-signature":"xxxCUSTOM_SIGNATURExxx"}',
        '{}',
        '1000'
    );

Notice my trigger calls my docker host (I'm running a netlify function locally to handle my webhook event). I also added a header "x-custom-signature" just to help ensure the calls to my netlify function are really from my Supabase trigger.

If I save this SQL into a migration file I'll need to ensure the webhook address and custom header are updated to the proper environment variables when this gets deployed to production. To do that I leverage a github action to swap in correct values (based on the Supabase doc's suggestions here).

name: Deploy Migrations to Production

on:
  push:
    branches:
      - main
  workflow_dispatch:

jobs:
  deploy:
    runs-on: ubuntu-22.04

    env:
      SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
      SUPABASE_DB_PASSWORD: ${{ secrets.PRODUCTION_DB_PASSWORD }}
      PRODUCTION_PROJECT_ID: ${{ secrets.PRODUCTION_PROJECT_ID }}

    steps:
      - uses: actions/checkout@v3

      - name: Replace docker.internal domain with real domain in sql migrations
        uses: jacobtomlinson/gha-find-replace@v3
        with:
          find: "http://host.docker.internal:8888/"
          replace: "https://my-site.com/"
          include: "supabase/migrations/*.sql"
          regex: false
      - name: Replace x-custom-signature header value with secret one
        uses: jacobtomlinson/gha-find-replace@v3
        with:
          find: "xxxCUSTOM_SIGNATURExxx"
          replace: ${{ secrets.SUPABASE_WEBHOOK_CUSTOM_SIGNATURE }}
          include: "supabase/migrations/*.sql"
          regex: false

      - uses: supabase/setup-cli@v1
        with:
          version: 1.33.0

      - run: |
          supabase link --project-ref $PRODUCTION_PROJECT_ID
          supabase db push

Now when I merge to main branch the action will swap in real values before running the migration.

While this hand-coded webhooks code doesn't have the audit trail that Supabase's built-in "supabase_functions" schema has, it's good enough for my needs and works locally. Hopefully someday soon Supabase local development will be fixed to behave just like hosted (making the "supabase_functions" schema when setting up a local environment).