Nobody gets paged for a dead-letter queue. The consumer parks the message, moves on, and the partition keeps flowing, which is what it should do. Then the parked message sits there, owned by no one, next to four thousand others that may or may not still matter.
My earlier post on event-driven CRM sync gave dead letters one paragraph: park the message with its error and keep going. This one starts after the parking. I think these queues fill up for an organizational reason. Nothing decides, at the moment of failure, whose problem the entry is. So I make that decision in code, right there, and I keep the entries in a Postgres table where they can be grouped, superseded and replayed in order.
Rules for every parked entry
- Every entry gets a failure class when it is parked:
transient,dataorcontract. - Every entry gets an owner from the same lookup. An unmapped error code is a contract error and goes to the integration’s developers.
- Transient entries retry on a schedule with a cap, then become contract entries.
- Data entries show up on a worklist the owning team already uses, with the CRM’s own error message visible.
- Contract entries open one ticket per error code, never one per record.
- Resolved and superseded rows are deleted after 30 days.
One row per failure
CREATE TABLE dead_letter (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
record_key text NOT NULL, -- 'account:A-10482'
version bigint NOT NULL, -- source sequence number
payload jsonb NOT NULL,
failure_class text NOT NULL
CHECK (failure_class IN ('transient', 'data', 'contract')),
error_code text NOT NULL,
error_message text,
owner text NOT NULL, -- a team, never a person
attempts integer NOT NULL DEFAULT 1,
next_retry_at timestamptz, -- null until released
superseded_by bigint REFERENCES dead_letter (id),
parked_at timestamptz NOT NULL DEFAULT now(),
resolved_at timestamptz,
UNIQUE (record_key, version)
);
CREATE INDEX dead_letter_open
ON dead_letter (failure_class, owner, parked_at)
WHERE resolved_at IS NULL AND superseded_by IS NULL;
I made the table-over-queue case in Queue, log or table, so here the dead-letter topic is only the transport into this table. What the table buys in this post is two specific queries: everything stuck for one account, and whether an entry is still the latest version of its record.
The error map
Three classes, because three different kinds of people clear them. Time clears a transient error. The team that owns the record clears a data error, in the CRM or in the source system. Developers clear a contract error, because the integration’s assumption about the CRM has stopped being true.
classes:
transient: { max_age: 2h, retry_after: [15m, 30m, 1h], then: contract }
data: { max_age: 4h }
contract: { max_age: 24h }
default: { class: contract, owner: integration-dev }
errors:
UNABLE_TO_LOCK_ROW: { class: transient, owner: integration-dev }
REQUEST_LIMIT_EXCEEDED: { class: transient, owner: integration-dev }
FIELD_CUSTOM_VALIDATION_EXCEPTION: { class: data, owner: sales-ops }
DUPLICATES_DETECTED: { class: data, owner: sales-ops }
ENTITY_IS_DELETED: { class: data, owner: sales-ops }
REQUIRED_FIELD_MISSING: { class: contract, owner: integration-dev }
INVALID_FIELD: { class: contract, owner: integration-dev }
STRING_TOO_LONG: { class: contract, owner: integration-dev }
Those are Salesforce’s status codes; other CRMs have their own names for the same failures. I keep the classifier this dumb on purpose. A lookup can be read by the people it assigns work to, and they will tell you when it’s wrong.
It will be wrong sometimes, because a code is only a first guess. One account failing a validation rule is a data problem. The same rule failing on hundreds of records in an hour means the CRM changed underneath the integration. That’s why the entries need to be groupable: GROUP BY error_code, error_message shows the pattern, and one UPDATE moves the whole group to the other class and owner.
Redrive in order, minus the stale ones
Data and contract entries keep next_retry_at null until their owner releases them, by setting it to now() for a record or a whole error group. Transient entries get it set by the schedule. One worker then serves all three lanes:
WITH head AS (
SELECT DISTINCT ON (record_key) id
FROM dead_letter
WHERE resolved_at IS NULL AND superseded_by IS NULL
ORDER BY record_key, version
)
SELECT d.*
FROM dead_letter d
JOIN head USING (id)
WHERE d.next_retry_at <= now()
AND d.resolved_at IS NULL AND d.superseded_by IS NULL
ORDER BY d.parked_at
LIMIT 200
FOR UPDATE OF d SKIP LOCKED;
head is the oldest open entry for each record. Only heads are eligible, so version 8 of an account can’t be written while version 7 is still waiting on someone. SKIP LOCKED lets several workers run this without handing two of them the same row, and a record that one worker holds is passed over by the rest. The open-row test is repeated in the outer query on purpose: that copy is the one Postgres re-checks if another worker resolved the row a moment earlier.
Superseding happens at parking time: when a newer version of a record arrives in the table, older open rows for that key get superseded_by set to it. Do this only if each message carries the full record. Change events that carry just the changed fields can’t replace each other, and for those the head rule alone keeps the order. Either way the worker should compare the entry’s version with the last version it applied to the CRM and drop anything older, since a later change may have synced cleanly without ever being parked.
Age is the number to alert on
SELECT failure_class, owner,
count(*) AS open_entries,
now() - min(parked_at) AS oldest_age
FROM dead_letter
WHERE resolved_at IS NULL AND superseded_by IS NULL
GROUP BY failure_class, owner;
Compare oldest_age with the class’s max_age and notify the owner. Count misleads in both directions. A thousand row-lock errors is a large number and a non-event. A handful of orders that can’t find their account is a small number, and each one is an invoice somebody will ask about.
A Monday at Marrowby Linen
Marrowby Linen is fictional, and so are its Monday and every number in it. It’s an industrial-laundry supplier that syncs accounts and orders from billing into the CRM.
On Monday 1,912 messages are parked. 1,640 are UNABLE_TO_LOCK_ROW, collateral from a mass territory update, and they clear on the first scheduled retry 15 minutes later. Nobody looks at them.
Another 263 fail a validation rule, so the map sends them to sales operations, along with 9 orders that point at an account which was merged away. Four hours in, the data-class alert fires, once. Sales ops opens the worklist and sees 263 entries with the same message: a validation rule added over the weekend now demands a “Delivery window” on every account. They hand that group to the developers with a reclassify and keep the nine.
The developers fix the mapping once and release the group. 31 of the 263 are older versions of accounts that billing changed again and that were parked a second time, so they’re already superseded and the redrive writes 232. Sales ops repoints the nine orders to the surviving account, and by Wednesday at 11:00 the table has no open rows.
The 1,640 would have drained from a plain retry topic too. The other 272 are the ones that sit for a quarter when no line of code says who they belong to.