Dead Letters & Recovery

Some messages will fail every retry. A malformed payload, a handler bug, a downstream API that returns 404 for a deleted resource. The question that separates production-grade messaging from toys is: where do those messages go, and how do you get them back?

In SQLStreams, after a message exhausts its retries for a group — or the handler returns sqlstreams.Terminal(err), which says no retry will help — its delivery row flips to dead — attempt count and final error on the row, the payload still in the message log beside it, per consumer group. Nothing expires behind your back (dead rows live until the retention drop that removes their log rows), and no separate dead-letter queue needs provisioning per consumer.

last_error is the handler’s own error text, verbatim, plus the panic value and stack when the handler panicked. SQLStreams never writes the payload into it and never logs it: the dead-letter log line names the message id and stops. The one way a payload ends up in last_error is a handler that formats it into its error, so name the id in the error and leave the document in the message log.

Triage with the tools you already have

Each stream’s tables are named by its id — resolve it once (SELECT id FROM sqlstreams.stream_config WHERE name = '...'; say it returned 1):

-- What's dying, and why? Group by error to find the pattern.
SELECT d.last_error, count(*),
       min(m.created_at) AS first_seen, max(m.created_at) AS last_seen
FROM sqlstreams.exception_queue_1 d
JOIN sqlstreams.message_log_1 m ON m.id = d.message_id
JOIN sqlstreams.consumer_group_config g ON g.id = d.consumer_group_id
WHERE g.name = 'fraud-screening' AND d.status = 'dead'
GROUP BY 1 ORDER BY 2 DESC;

-- Inspect one casualty in full, with its per-attempt history.
SELECT m.payload, d.attempts, d.last_error,
       l.attempt, l.status, l.error, l.attempted_at
FROM sqlstreams.exception_queue_1 d
JOIN sqlstreams.message_log_1 m ON m.id = d.message_id
LEFT JOIN sqlstreams.delivery_log_1 l ON (l.consumer_group_id, l.message_id) = (d.consumer_group_id, d.message_id)
WHERE d.status = 'dead'
ORDER BY d.updated_at DESC, l.attempt;

This is the underrated payoff of messaging-in-Postgres: dead-letter triage with GROUP BY, joins to your business tables (“are all the dead messages from one merchant?”), and your normal query tooling — instead of paging through an opaque console one message at a time.

The recovery loop

1. Watch. The dead count per group ships as a metric (sqlstreams.consumer.exceptions.dead, via sqlstreams metric list or the Prometheus endpoint on sqlstreams manager run --metrics-address) — or it’s a count(*) threshold on the query above. Wire it into the alerting you already have.

2. Diagnose. Group dead messages by error; join to business data; find the pattern. Usually one bug, many casualties.

3. Fix and deploy the handler.

4. Redrive — the step that is honestly not shipped yet:

5. Confirm. The same triage query, trending to zero for new traffic once the fixed handler is live.

Per-group isolation

Dead-lettering is scoped to the consumer group, because failure is an opinion of the consumer, not a property of the message. The same message can be dead for fraud-screening (their API rejected it), long since processed by email-receipts (which wrote no row at all — success is the silent path), and still ahead of a lagging analytics group — all against one immutable log row. That’s the fusion doing its job.