# Reliable webhooks from Postgres triggers with an outbox, SKIP LOCKED and HMAC signatures > SmartLoads fires webhooks from PostgreSQL AFTER triggers, so every write path is covered, including ones that bypass the app. Each trigger first checks whether the agency has an endpoint for that event. The check costs one index lookup and no subtransaction, so agencies without webhooks pay almost nothing on bulk imports. Wanted events are written to an outbox table with a snapshot of the row, inside an exception block that can never fail the original write. A pg_cron job checks every 30 seconds and calls a sender only when something is due. The sender claims rows with FOR UPDATE SKIP LOCKED, signs each body with HMAC-SHA256 over a timestamp and the body, retries seven times over about a day, and refuses private network addresses at send time. Source: https://smartloads.io/blog/postgres-trigger-webhooks-outbox-signatures · Updated 2026-09-26 SmartLoads' [Partner API](/developers) lets any tool read an agency's loads and leads. Polling isn't enough for the events brokers care about most: a carrier lead that matches a load is worth a phone call within minutes. So the API has webhooks. An agency adds a URL, picks events, and SmartLoads POSTs each event, signed. If the URL is a Slack incoming webhook, the event arrives as a readable Slack message instead. The events are `lead.created`, `lead.matched_load`, `cover_card.ready`, `load.booked`, `load.covered` and `load.removed`. This paper covers where events come from, how they're queued and delivered, and the safety properties we cared about. ## Requirements 1. **Every write path fires.** Loads are booked from several screens, from a customer's confirmation click and from SMS flows. Leads arrive from phone agents, chat, Telegram and the public board. An event system that only hooks the app's main code path will miss some. 2. **Never break the write.** A webhook problem must never fail a booking or an import. 3. **Cheap when unused.** Imports update hundreds of loads at a time, and board clears archive a whole day's board at once. Agencies without webhooks shouldn't pay for the feature. 4. **At-least-once delivery with retries,** and the same event content on every retry. 5. **Verifiable.** Receivers can prove a request came from SmartLoads and isn't a replay. 6. **Safe to point anywhere public, and nowhere private.** ## Why database triggers Requirement 1 decides it. SmartLoads had already learned this with its Slack booking alert: when the alert was sent from the booking screens, only two of the five booking paths sent it. Moving it into an AFTER trigger fixed that for good. Webhooks follow the same pattern: AFTER triggers on `leads`, `load_cover_cards` and `loads`. | Event | Fires when | |---|---| | `lead.created` | A lead is inserted with an intake channel | | `lead.matched_load` | A lead's `load_id` is set at insert or changes later | | `cover_card.ready` | A card is inserted (not failed), or a person confirms its load | | `load.booked` | `dispatch_status` moves into `booked` | | `load.covered` | `covered_at` goes from null to set | | `load.removed` | A live load is archived or deleted | Two details: - **Placeholder leads are excluded by data, not code.** The booking screen creates placeholder leads for bookings made without a call. Every real intake path stamps a `channel`; placeholders leave it null, so `WHEN (NEW.channel IS NOT NULL)` excludes them. - **Transitions live in the trigger's `WHEN` clause,** so PostgreSQL filters rows before calling the function at all: ```sql CREATE TRIGGER loads_webhook_booked AFTER UPDATE OF dispatch_status ON public.loads FOR EACH ROW WHEN (NEW.dispatch_status = 'booked' AND OLD.dispatch_status IS DISTINCT FROM 'booked') EXECUTE FUNCTION public.webhook_on_load('load.booked'); ``` `load.removed` fires once when a load goes from live (active and not archived) to not live, and on a `DELETE` of a live load. Deleting an already-archived row fires nothing. ## Cheap when unused, safe when used The trigger function asks one question first: ```sql CREATE FUNCTION public.webhook_wanted(p_agency_id uuid, p_event_type text) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$ SELECT p_agency_id IS NOT NULL AND EXISTS ( SELECT 1 FROM public.agency_webhooks w WHERE w.agency_id = p_agency_id AND w.enabled AND p_event_type = ANY (w.events)); $$; ``` A partial index on enabled endpoints makes that one index lookup. The ordering is deliberate: ```sql IF NOT public.webhook_wanted(v_row.agency_id, v_event) THEN RETURN NULL; -- common case: no subtransaction at all END IF; BEGIN PERFORM public.webhook_enqueue(v_row.agency_id, v_event, jsonb_build_object('load', to_jsonb(v_row))); EXCEPTION WHEN OTHERS THEN RAISE WARNING 'webhook_on_load % failed for load %: %', v_event, v_row.id, SQLERRM; END; RETURN NULL; ``` In PL/pgSQL, entering a block with an `EXCEPTION` clause starts a subtransaction. That's cheap once, but a board clear that archives hundreds of loads in one statement would create hundreds of them. Past 64 subtransactions in one transaction, PostgreSQL's per-backend cache overflows, which can slow other sessions' snapshots. So the check sits outside the guarded block. Agencies without an endpoint for the event never enter it. Agencies that have one get the guarantee that a queueing failure logs a warning and the booking still commits. We tested that by adding a check constraint that made every insert fail: the load was still booked. The trigger functions are `SECURITY DEFINER`. A broker's booking runs as that user, who has no access to the webhook tables, and the event still queues. ## The outbox `webhook_enqueue` writes one row per subscribed endpoint to `webhook_deliveries`, all sharing one `event_id`: | Column | Purpose | |---|---| | `snapshot` | The row(s) at event time: `{lead, load}`, `{cover_card, load}` or `{load}` | | `payload` | The event body as first built; retries resend this content | | `status`, `attempts`, `next_attempt_at`, `locked_until` | Queue state | | `last_status_code`, `last_error`, `delivered_at` | The delivery log shown in the UI | The snapshot holds the full row, but it never goes out as-is. The sender maps it through the same public shapes as the read API (`publicLead`, `publicLoad`), so the webhook body and the API agree. The body is built once, on the first attempt, and stored, so every retry carries the same content even if the underlying load has changed since. (It's stored as `jsonb`, which can reorder keys, so a retry isn't guaranteed to be byte-identical. That's one more reason receivers must verify the signature against the raw bytes they received.) ## Kicking the sender A `pg_cron` job runs every 30 seconds and calls `webhook_kick()`. Most runs do nothing: ```sql IF NOT EXISTS (SELECT 1 FROM webhook_deliveries d WHERE ((d.status = 'pending' AND d.next_attempt_at <= now()) OR (d.status = 'sending' AND d.locked_until < now())) AND d.attempts < 7) THEN RETURN; END IF; PERFORM net.http_post(url := v_url || '/functions/v1/webhook-dispatch', headers := …); ``` The Edge Function is only invoked when something is due. It's called with the service-role key read from Supabase Vault, and it compares that key in constant time. Events usually go out within 30 seconds. ## Claiming with SKIP LOCKED The sender claims work in batches with one statement: ```sql WITH due AS ( SELECT d.id FROM webhook_deliveries d WHERE ((d.status = 'pending' AND d.next_attempt_at <= now()) OR (d.status = 'sending' AND d.locked_until < now())) AND d.attempts < 7 ORDER BY d.next_attempt_at LIMIT 25 FOR UPDATE SKIP LOCKED ), claimed AS ( UPDATE webhook_deliveries d SET status = 'sending', attempts = d.attempts + 1, locked_until = now() + interval '2 minutes' FROM due WHERE d.id = due.id RETURNING d.* ) SELECT … FROM claimed JOIN agency_webhooks w ON w.id = claimed.webhook_id; ``` Here's how it behaves: - **`SKIP LOCKED` keeps overlapping senders apart.** The cron fires every 30 seconds and a run can last up to 40, so two runs can overlap, and each claims different rows. - **The claim commits a two-minute lease.** If a sender crashes mid-send, the row becomes claimable again when the lease expires. - **The claim counts the attempt.** A request that crashes the sender every time still uses up its seven attempts. After the last one, `webhook_kick` marks a row stranded in `sending` as failed. An early version didn't cap reclaims; review caught it before launch. The sender sends five at a time and stops starting new batches after 40 seconds. ## Delivery, signatures and retries Each request carries these headers: ``` X-SmartLoads-Event: lead.matched_load X-SmartLoads-Delivery: X-SmartLoads-Signature: t=,v1= ``` - **Signed over the timestamp and the raw body.** Receivers recompute the HMAC, compare in constant time and reject timestamps more than five minutes old, which stops replay. Each endpoint has its own `whsec_` secret, shown once. It's readable only by the service role, never by the app's users. - **Any `2xx` within 10 seconds counts as delivered.** Redirects aren't followed. - **Retries** come after 1 minute, 5 minutes, 30 minutes, 2 hours, 6 hours and 12 hours: seven tries over about a day. - **Endpoint health.** An endpoint whose every attempt fails for three days is switched off, with the reason shown to the admin. "Send test" goes out immediately, shows its result, and doesn't count against health. - **Duplicates.** Delivery is at least once, so receivers dedupe on the event `id`. ## SSRF: public addresses only A webhook URL is user input that makes the server send requests, which is the textbook setup for server-side request forgery. There are two layers of defense: 1. **When the URL is saved:** it must be `https`, with no credentials in it, a hostname with a dot, and no IP-literal in private, loopback, link-local, CGNAT or multicast ranges. 2. **At send time:** the sender resolves the hostname's A and AAAA records and refuses if any answer is private. Checking the text alone isn't enough, because a public name can resolve to `127.0.0.1`, as `localtest.me` and `127.0.0.1.nip.io` do, and the resolver check catches both. If the runtime can't do DNS lookups at all, the sender falls back to the text check rather than failing every webhook. A window remains between the lookup and the connection, so DNS rebinding is narrowed, not eliminated. With redirects off and HTTPS required, we accepted that trade-off for v1. ## Slack as a first-class endpoint If the URL's host is `hooks.slack.com`, the endpoint's format is `slack`. The sender renders the same event as a Slack message instead of JSON. For a matched lead, that's one fact per line: carrier and MC number, contact and phone, load and lane with the asked rate, and the call summary. Every piece of text is escaped for Slack's markup. This is how an agency gets "ping our channel when an AI lead matches a load" with no code. The first Slack test delivery returned HTTP 200 in 98 milliseconds. ## Retention and testing A nightly `pg_cron` job deletes finished deliveries older than 30 days. The whole migration was run against a local PostgreSQL 17 with stub tables for these checks: - every event case, including the ones that shouldn't fire: other agencies, placeholder leads, disabled endpoints, deleting an archived load, failed cards - claiming and skipping, and the attempt cap - the kick calling out only when due - privileges: signed-in users can't read endpoints or call the claim function The pure parts (URL checks, signing, retry schedule, event bodies, Slack text) have unit tests. ## Frequently asked questions ### Why not use Supabase Realtime or a message broker? Realtime pushes changes to connected browser clients. It isn't a durable, retried delivery to third-party URLs. A broker would add a service to run. A PostgreSQL outbox gives durability, a delivery log and exactly the retry behavior we wanted, using infrastructure we already have. ### Are webhook events delivered in order? Not guaranteed. Retries and parallel sends can reorder events, so receivers should order by the event's `created_at` and dedupe on its `id`. ### What stops a slow endpoint from delaying everyone? Each request has a 10-second timeout, sends run five at a time, and a failing endpoint backs off on its own schedule without blocking other endpoints' deliveries. ### Do webhooks slow down imports? Not for agencies without endpoints. The trigger's first check is one index lookup, and there's no subtransaction unless the agency actually subscribes to that event.