Webhooks

A durable webhook retry queue in SQLite

2026-11-09 · 8 min read

Answering a webhook fast means you accept the event and do the work afterwards. That is only safe if "afterwards" survives a crash, a slow downstream API and a payload that can never succeed. For one server and a modest event rate, a single SQLite table is enough to build that queue. This post shows the schema, an atomic claim with leases, backoff with jitter, a dead-letter state and a replay path, with Go and SQL you can adapt.

Who needs this

A seller who emails licence keys after a Polar or Stripe payment. The email API has an outage for twenty minutes. Without a queue, every order in that window fails and the provider's retries may run out. With one, each order waits in pending, retries on a schedule, and goes out when the API recovers.

A GitHub push that should trigger a build or deploy. Your build host is mid-restart when the push event arrives. The queue holds the job and runs it after the host is back, and if the deploy script has a bug, the event lands in a dead-letter state where you can fix the script and replay it, instead of vanishing.

A bot or integration that posts to chat on each sale. The chat service rate-limits you with 429s during a promotion. Backoff with jitter spreads the retries out so you do not hammer it in lockstep, and a permanent 400 from a malformed message is parked instead of retried forever.

Why SQLite is a fair choice here

You already have it, it is durable with WAL and a sensible synchronous setting, it supports UPDATE ... RETURNING (SQLite 3.35 or newer) for atomic claiming, and you can inspect the queue with sqlite3 at three in the morning. The limit is a single writer. The starter's own load test accepted several thousand events per second on a laptop SSD, which is far more than a small shop's webhooks. If you need several machines consuming one queue, use Postgres with FOR UPDATE SKIP LOCKED (shown below) or a broker.

Open the database with WAL and a busy timeout, and keep one connection for the writer:

db, err := sql.Open("sqlite", path+"?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)")
db.SetMaxOpenConns(1)

The pragma syntax above is for the modernc.org/sqlite driver; other drivers spell it differently. If you ever use a pool of several connections, read Go SQLite stale reads first.

The schema

CREATE TABLE jobs (
  id           INTEGER PRIMARY KEY,
  source       TEXT NOT NULL,
  delivery_id  TEXT NOT NULL,
  payload      BLOB NOT NULL,
  status       TEXT NOT NULL DEFAULT 'pending'
               CHECK (status IN ('pending','running','done','dead')),
  attempts     INTEGER NOT NULL DEFAULT 0,
  run_at       INTEGER NOT NULL,          -- unix seconds: not before this time
  lease_owner  TEXT,
  lease_until  INTEGER,                   -- unix seconds
  last_error   TEXT NOT NULL DEFAULT '',
  created_at   INTEGER NOT NULL,
  finished_at  INTEGER,
  UNIQUE (source, delivery_id)
);
CREATE INDEX jobs_ready ON jobs (status, run_at);

The UNIQUE constraint is the dedupe, covered in webhook idempotency and dedupe patterns. The CHECK keeps a typo from inventing a fifth state. run_at is what makes backoff cheap: a failed job just gets a later run_at and stays pending.

Claiming work atomically

Several workers, or one worker and a restart, must never run the same job at the same moment. Do the selection and the state change in one statement so there is no gap for another caller to slip into:

UPDATE jobs
SET status = 'running',
    attempts = attempts + 1,
    lease_owner = ?1,
    lease_until = ?2 + ?3
WHERE id = (
  SELECT id FROM jobs
  WHERE (status = 'pending' AND run_at <= ?2)
     OR (status = 'running' AND lease_until <= ?2)
  ORDER BY run_at, id
  LIMIT 1
)
RETURNING id, source, delivery_id, payload, attempts;

Bind ?1 to a worker identifier, ?2 to the current unix time and ?3 to the lease length in seconds. If nothing is ready, you get no rows, which in Go is sql.ErrNoRows, and the worker sleeps for a poll interval.

The second branch of the WHERE is the lease at work. A job in running whose lease_until has passed belongs to a worker that crashed, hung or was killed, and any worker may take it over. This replaces the cruder approach of resetting every running row at startup, which works for one process but cannot tell a dead worker from a slow one.

Two properties fall out of this design.

• The attempt is counted at claim time. If a job crashes the whole process every time it runs, each crash still increments attempts, so it eventually reaches the limit and is parked instead of crash-looping forever. A job that kills your process is the worst poison message, and counting at claim is what catches it.
• The lease must be longer than the handler. Pick a lease comfortably above your slowest legitimate handler, for example 60 seconds for one that calls an API with a 10 second timeout. If a handler can run longer, extend the lease while it works:

UPDATE jobs SET lease_until = ?1 WHERE id = ?2 AND lease_owner = ?3 AND status = 'running';

Check RowsAffected(). Zero means someone else took the job over, and this worker should stop.

Finish with a fence

When a worker completes a job it must prove it still owns it. Otherwise a slow worker whose lease expired could mark done a job that another worker is now retrying.

UPDATE jobs
SET status = 'done', finished_at = ?1, last_error = ''
WHERE id = ?2 AND lease_owner = ?3 AND status = 'running';

The lease_owner check is the fence. If it affects no rows, you lost the lease. Your handler's side effects still happened, which is one more reason handlers must be idempotent.

Backoff with jitter

When a handler fails, schedule the next attempt later, and later again each time. Exponential backoff gives a struggling downstream room to recover. Jitter stops a hundred failed jobs from all retrying at the same second.

import "math/rand/v2"

func backoff(attempt int) time.Duration {
	d := time.Duration(1<<min(attempt, 10)) * time.Second // 2s, 4s, 8s ... capped by the shift
	d = min(d, 15*time.Minute)
	half := d / 2
	return half + time.Duration(rand.Int64N(int64(half)+1)) // between d/2 and d
}

The starter's default curve, without jitter, is 2^attempt seconds: attempt 1 retries after 2 s, then 4, 8, 16. With the default of 5 attempts the whole retry window is about 30 seconds. That suits a transient blip. If your downstream can be down for minutes, raise the attempt limit: 10 attempts stretch the window to about 17 minutes (2 + 4 + ... + 512 seconds), and 12 give over an hour on the uncapped curve. The min(d, 15*time.Minute) line in the sketch above bounds each wait so a late attempt does not sleep for hours, which also shortens the total window a little.

Record the outcome in one statement:

UPDATE jobs
SET status = ?1,            -- 'pending' to retry, 'dead' to park
    run_at = ?2,
    last_error = ?3,
    lease_owner = NULL,
    lease_until = NULL
WHERE id = ?4 AND lease_owner = ?5;

Decide between retry and dead in Go: dead if attempts >= maxAttempts or the error is permanent.

Permanent versus transient errors

Some failures cannot be fixed by waiting: a payload that is not valid JSON, an event for an order that does not exist and never will. Retrying those wastes the window and delays the alert. Give the handler a way to say so:

type permanentError struct{ err error }

func (p permanentError) Error() string { return p.err.Error() }
func (p permanentError) Unwrap() error { return p.err }

func Permanent(err error) error { return permanentError{err} }

func isPermanent(err error) bool {
	var p permanentError
	return errors.As(err, &p)
}

Be sparing. A 500 from a downstream API is transient, a 400 that says your request is malformed is permanent, and a 429 is transient with a longer wait.

Dead letters and replay

A dead row is a job you gave up on automatically and a human should look at. It keeps its payload and last_error, so triage is a query:

SELECT id, source, delivery_id, attempts, last_error
FROM jobs WHERE status = 'dead' ORDER BY id;

After you fix the bug, put the job back with a fresh attempt budget:

UPDATE jobs SET status = 'pending', attempts = 0, run_at = unixepoch(),
                lease_owner = NULL, lease_until = NULL
WHERE id = ?1 AND status = 'dead';

The starter wraps this as server dead, server replay --id 12 and server replay --all-dead, and a running worker picks replayed events up within its poll interval. Replay is safe only because handlers are idempotent: replaying a job whose effect partly happened must not do the first half twice.

Alert on the dead count. The starter exposes webhook_events{status="dead"} in Prometheus text format at /metrics, and a single line in a cron job or a lightweight monitor that checks SELECT count(*) FROM jobs WHERE status='dead' is better than finding out from a customer.

Shutdown and crash behaviour

On SIGTERM, stop claiming new jobs, let running handlers finish within a deadline, and hand back whatever did not finish without charging it an attempt, since the interruption was your deploy and not the job's fault:

UPDATE jobs SET status = 'pending', attempts = MAX(attempts - 1, 0),
                lease_owner = NULL, lease_until = NULL
WHERE id = ?1 AND status = 'running';

Use a context that is not cancelled for these bookkeeping writes, with context.WithoutCancel(ctx), or the shutdown that interrupted the handler will also cancel the write that records it. The wider pattern is in graceful shutdown for a Go HTTP server.

After a hard crash, the lease handles recovery by itself. A queue without leases, like the starter's single-process design, instead returns running rows to pending at startup, which is correct when only one process ever runs.

The Postgres equivalent

If you move to Postgres, the claim becomes:

UPDATE jobs
SET status = 'running', attempts = attempts + 1,
    lease_owner = $1, lease_until = now() + interval '60 seconds'
WHERE id = (
  SELECT id FROM jobs
  WHERE (status = 'pending' AND run_at <= now())
     OR (status = 'running' AND lease_until <= now())
  ORDER BY run_at, id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING id, payload, attempts;

SKIP LOCKED lets many workers on many machines claim different rows without blocking each other. That is the main thing SQLite cannot do.

Mistakes we see

Selecting then updating in two statements. Two workers select the same row. Make the claim one statement.

No attempt cap. A bad payload retries forever and fills your logs. Cap it and dead-letter.

Retrying everything the same way. Permanent errors burn the window, and 429s need longer waits than 500s.

Backoff with no jitter. After an outage, every failed job wakes at the same instant and knocks the recovering service over again.

Leases shorter than the handler. The job is taken over while the first worker is still running it, and you get concurrent duplicate runs.

Forgetting that the queue file is data. It holds customer emails and order ids. Back it up and protect it; see restic backups for SQLite and Postgres.

Never deleting finished rows. Purge done rows after a retention period, but keep it longer than the sender's redelivery horizon, or a late retry will be treated as new.

Options compared

FAQ

Do I need a lease if I only run one worker process?

Not strictly. Resetting running rows to pending at startup is enough for a single process. Leases start to matter when you run several workers that can hang independently, or when a handler can stall without the process dying.

How many retries should I allow?

Enough to outlast the outages you expect. A few seconds of backoff covers blips, while ten attempts on this curve cover roughly a quarter of an hour and more attempts stretch it further. Whatever you pick, alert on dead jobs so a long outage does not go unnoticed.

Is it safe to replay dead events?

Yes if your handler is idempotent, and that is the requirement for everything here. Check the last_error first so you replay after the cause is fixed, not before.

Should the worker run in the same process as the HTTP server?

For one VPS, yes. It is one binary, one systemd unit, and the queue is a function call away. Split them only if the handler's CPU or memory use hurts request latency.

What about ordering?

One worker processes jobs in run_at, id order, but a retried job goes behind newer ones. With several workers there is no ordering at all. If order matters, fetch current state from the provider when handling or compare versions in the payload.

The Go Webhook Receiver Starter ships this queue as tested Go code, with the atomic claim, backoff, dead-letter states and the replay commands, so you can start from the working version and adjust it to your handler.

webhookssqlitegoreliability