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).

Hopefully you found this post helpful, if you have any questions you can find me on Twitter.

Netlify Functions in Javascript with Type-Checking
How to Serialize Errors in Javascript