DEV Community

Cover image for Your webhooks belong in your Postgres
Nikos Benakis
Nikos Benakis

Posted on

Your webhooks belong in your Postgres

Most SaaS apps end up sending webhooks to their customers, and most of those
implementations share a bug that hides in two lines:

await db.query("UPDATE invoices SET status = 'paid' WHERE id = $1", [id]);
await webhooks.send({ type: "invoice.paid", data: { id } });
Enter fullscreen mode Exit fullscreen mode

The first line commits. If the process crashes, a deploy restarts the pod, or
the webhook API times out before the second line finishes, your customer never
hears that the invoice was paid. Nothing throws an error, and the event is
gone.

Swap the lines and you get the opposite bug. The webhook goes out, the
transaction then rolls back on a constraint error, and your customer's system
now believes in a payment that never happened.

This is the dual-write problem. You're writing to two systems, and no
transaction covers both. It's the most common cause of "we lost some webhooks"
I've seen.

That's the argument of this post: your webhooks belong in your Postgres,
written by the same transaction as the change that caused them.

Before I explain the fix, here it is running against the published npm
package. The app commits in_123 and rolls back in_124. The receiver plays
your customer's server. It checks every signature and is down for the first
two attempts. I shortened the retry schedule for the recording, but everything
else is real output.

A terminal running the Hookgres demo: in_123 is committed and delivered after two 503s, in_124 is rolled back and never sent, and psql shows the three attempts in hookgres.attempts.

Why a hosted webhook service can't fix this

The usual advice is to hand webhooks to a service like Svix or Hookdeck. Those
are good products, and they handle retries and signatures well.

They don't fix the two lines above, though. A hosted service is the second
system in the dual write. Your app still commits to Postgres and then makes an
HTTP call to someone else, and the gap between those two steps is exactly where
events get lost. No vendor can close it from their side, because they can't
join your transaction.

The only real fix is to write the event in the same transaction as the
invoice. And that means it has to live in Postgres.

The transactional outbox pattern

The standard fix is a transactional outbox. Instead of calling anything, you
insert the event into a table in the same transaction as the change that
caused it:

BEGIN;
UPDATE invoices SET status = 'paid' WHERE id = $1;
INSERT INTO outbox (event_type, payload) VALUES ('invoice.paid', '{"id": "in_123"}');
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Now the event and the data commit or roll back together. A separate worker
reads the outbox and delivers, and if the worker crashes, the row is still
there for the next try.

So teams that do this properly end up with an outbox table, a relay worker,
and a webhook service on the other end. That's two systems to run, and you're
still paying for the service.

The outbox table itself is ten lines of SQL. Delivering from it is where the
real work is. You need:

  • signatures your customers can verify
  • retries spread over hours, not seconds
  • workers that don't fight over the same delivery
  • a way to turn off endpoints that stay down
  • a log of every attempt, for the first "we never got the webhook" ticket
  • replay
  • SSRF protection, so nobody registers http://169.254.169.254/ as their URL

I wanted one thing that does both the outbox and the delivery, inside the
database that already has the data.

Hookgres: webhooks that commit with your data

Hookgres is an MIT TypeScript
library that does exactly that on the Postgres you already run. It adds a
hookgres schema to your database and a worker to your app. There's no extra
service or queue to deploy. It needs Node.js 22.12+ and Postgres 14+, with no
extensions.

npm install hookgres pg
Enter fullscreen mode Exit fullscreen mode
import pg from "pg";
import { Hookgres } from "hookgres";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const hookgres = new Hookgres({ pool, blockPrivateAddresses: true });

await hookgres.migrate(); // creates the schema, safe to run on every boot
hookgres.start();         // the delivery worker, in this process

// A customer registers an endpoint. Show them endpoint.secret once.
const endpoint = await hookgres.createEndpoint({
  tenantId: "acme",
  url: "https://acme.example.com/webhooks",
  eventTypes: ["invoice.*"],
});
Enter fullscreen mode Exit fullscreen mode

The part that matters is send(). Pass your transaction's client, and the
webhook is written in the same commit as your change:

const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("UPDATE invoices SET status = 'paid' WHERE id = $1", [id]);
  await hookgres.send(
    { tenantId: "acme", eventType: "invoice.paid", payload: { id } },
    { client },
  );
  await client.query("COMMIT");
} catch (error) {
  await client.query("ROLLBACK"); // the webhook goes with it
  throw error;
} finally {
  client.release();
}
Enter fullscreen mode Exit fullscreen mode

Compare it with the two lines at the top. The webhook commits or rolls back
with the invoice row, and there's no moment where one exists without the
other. The recording above is this code running.

If your app talks to Postgres through an ORM, you pass its transaction
instead. Prisma and Kysely have helpers in the package:

import { fromPrisma } from "hookgres/prisma";

await prisma.$transaction(async (tx) => {
  await tx.invoice.update({ where: { id }, data: { status: "paid" } });
  await hookgres.send(
    { tenantId: "acme", eventType: "invoice.paid", payload: { id } },
    { client: fromPrisma(tx) },
  );
});
Enter fullscreen mode Exit fullscreen mode

For Drizzle, postgres.js, Knex, Sequelize and TypeORM there's a
wrapper of a few lines to copy, and
each one is tested in CI against a real Postgres.

Requests are signed with Standard Webhooks,
the scheme Svix and others use, so your customers verify them with the official
library for JavaScript, Python, Go, Ruby, PHP, Rust, Java, C# or Elixir. If
you're moving from Svix, the
migration guide shows how
to keep your customers' existing secrets, so their receivers don't change.

How delivery works after the commit

Everything on the left runs in your app, and the worker can run in as many
processes as you like:

your app                          Postgres                   customer
--------                          --------                   --------
BEGIN
UPDATE invoices      ---------->  invoices
hookgres.send()      ---------->  hookgres.messages
                                  hookgres.deliveries
COMMIT               ---------->  pg_notify (if notify: true)

hookgres worker      <----------  claim due deliveries
                                  (FOR UPDATE SKIP LOCKED)
hookgres worker      ------------ signed POST ------------>  endpoint
hookgres worker      ---------->  hookgres.attempts
                                  (schedule a retry, or mark failed)
Enter fullscreen mode Exit fullscreen mode

Claiming with FOR UPDATE SKIP LOCKED. Each worker claims a batch of due
deliveries with a SELECT ... FOR UPDATE SKIP LOCKED. Rows another worker
holds are skipped instead of waited on, so two workers never hold the same
delivery at once. Each claim also carries a lock token, so a worker that
stalled and lost its claim can't overwrite the result of the one that took
over. Delivery is still at least once, though. If a worker crashes right after
the receiver answers, the webhook gets sent again, so receivers should
deduplicate on the webhook-id header.

Waking up on commit. By default, idle workers poll every second. With
notify: true, send() also runs pg_notify in the same statement. Postgres
holds notifications until the transaction commits, so workers wake right after
the commit, and a rollback wakes no one.

No long-running process? Use cron. On serverless platforms, skip
start() and call hookgres.drain({ timeoutMs }) from a cron route. It
delivers whatever is due and stops before the time limit.

Retries. A failed attempt is retried after 5 seconds, 5 minutes, 30
minutes, 2 hours, 5 hours, 10 hours and 10 hours. That's 8 attempts over
about 28 hours, each with a small random offset. An endpoint that fails for 5 days
straight is disabled.

Every attempt is a row. The delivery log is plain tables next to your own,
so "which customers had failed webhooks this week" is a join:

select e.tenant_id, e.url, d.last_error
from hookgres.deliveries d
join hookgres.endpoints e on e.id = d.endpoint_id
where d.status = 'failed'
  and d.updated_at > now() - interval '7 days';
Enter fullscreen mode Exit fullscreen mode

Payloads stay in your database. No third party gets a copy of your
customers' data, so there's no new processor to add to your GDPR or SOC 2
paperwork. If you'd rather not keep payloads at all, redactPayloads: true
clears each one once it's delivered and keeps the log, and deleteMessages()
handles erasure requests.

On my laptop (Apple Silicon, Postgres 16 in Docker), 4 workers with 50
concurrent requests each delivered 20,000 webhooks to a local receiver at about
5,500 per second, with none lost or duplicated. In production, the real limit
is how fast your customers' servers answer.

Three bugs Postgres taught me while building it

When delivery lives in the database, the bugs you hit are about locks and
connections. These three came up while I was building the 0.3 release, and
each one changed the code.

Replay raced cleanup. cleanup() deletes old messages once all their
deliveries are finished. replayDelivery() puts a finished delivery back in
the queue. Run both at the same moment and cleanup could delete the message
that a replay had just revived, and the replay would just disappear. The fix
was a small EXISTS ... FOR SHARE clause. Replay now takes a shared lock on
the parent message, and cleanup's FOR UPDATE SKIP LOCKED skips right past
it.

notify with a one-connection pool hung forever. The listener for
LISTEN/NOTIFY holds a connection for as long as the worker runs. Give
Hookgres a pool with max: 1 and the listener takes that only connection, so
every send() waits for a connection that never comes back. You don't get an
error, it just hangs. Hookgres now refuses that setup when you create it, so
you don't find out in production.

A dropped idle connection crashed the whole process. When Postgres restarts
or fails over, node-postgres reports the dead idle connection as an error
event on the pool. For a pool Hookgres creates itself from a connection string,
nothing was listening, and in Node an unhandled error event kills the
process. I reproduced it with a test first, then routed the event to the
onError handler.

None of these shows up in a happy-path demo. They're the reason I'd rather
have this logic in one tested library than in every app's hand-rolled outbox.

Why not a job queue, or Svix plus an outbox?

These are the options I'd weigh, and where each one stops:

  • A job queue such as pg-boss or graphile-worker. Good tools, and Hookgres claims work the same way, with SKIP LOCKED. But a queue gives you "run this job later", and the delivery list above is still yours to write. A Redis queue like BullMQ also sits outside your Postgres transaction, which brings back the two writes.
  • Svix, plus your own outbox. This works, and Svix gives your customers a ready-made portal. You run an outbox and pay for a service, and every event crosses the network twice.
  • Svix's open-source server. MIT licensed and written in Rust, with its own Postgres (Redis is optional). It's still a separate service your app calls over HTTP, so you still need an outbox in front of it.

The docs have a longer comparison, with
a calculator for what each option costs at your volume.

What Hookgres doesn't do yet

It's a young library, so here's what's missing today:

  • There's no customer portal yet. Svix gives your customers a hosted UI to manage endpoints and replay deliveries. With Hookgres you build that screen yourself on top of the API calls (listDeliveries, listAttempts, replayDelivery). If you'd want a ready-made one, there's a waitlist.
  • The sender is TypeScript only, for now. Receivers can be written in any language, but sending happens from a Node.js app.
  • The traffic runs through your database, by design. Every webhook is a few rows in your Postgres. That's what makes the transaction guarantee possible, but if you want webhook load off your database, or delivery from many regions, a hosted service is the better fit.

Try it in two minutes with Docker

The repo has a playground with Postgres and three fake customer endpoints
(healthy, flaky and down), so you can watch retries, auto-disable and replay in
your browser:

git clone https://github.com/NickolasBenakis/hookgres
cd hookgres
docker compose up   # then open http://localhost:3000
Enter fullscreen mode Exit fullscreen mode

If this saves you from the two-line bug, a star on
GitHub helps other people find
it.

If your code has the two lines from the top of this post, that's the place to
start: move the webhook inside the transaction that writes the data. And if
you've solved the dual write a different way, I'd like to hear how.

Top comments (0)