Short answer: treat each scheduled or queued execution as a durable evidence record, not as a line of application logging. Give the logical job a stable ID, give every attempt its own ID, write state transitions to Postgres, and store a sanitized error summary plus a pointer to detailed telemetry. Retries must append evidence rather than overwrite it. That design lets a B2B SaaS team answer the question that matters during an incident: what ran for this tenant, what failed, what was retried, and what finally happened?
A worker library can schedule retries, while an error tracker can group exceptions. Neither boundary, by itself, is an incident history. The useful unit is the logical job and its ordered attempts, connected to the customer operation that created it.
How should a Postgres cron worker record background job errors?
Start with the reconstruction query, because it determines the schema. Given a tenant ID, an operation ID, and a time range, an investigator should be able to find the job, see each attempt in order, distinguish a retryable failure from terminal exhaustion, and correlate the failure with sanitized diagnostic detail. That is a narrower goal than retaining every log forever, and it is more defensible than hoping a stack trace contains enough business context.
Keep three identities separate: job_id identifies the logical unit of work, attempt_id identifies one execution, and operation_id connects the background work to the customer-visible action. Reusing one identifier for all three looks convenient until an operator cannot tell whether two errors describe duplicate execution or two retries.
Ambiguity wins.
The evidence record should carry timestamps, attempt number, outcome, an error class, a bounded message, and a correlation key for detailed telemetry. It should not carry session tokens, database connection strings, raw request bodies, or other secrets. OWASP's logging guidance explicitly warns against recording access tokens, passwords, sensitive personal data, and database connection strings directly; it also recommends sanitizing event data to prevent log injection.
Secrets do not become evidence.
Derive the storage model from failure modes
A single mutable jobs row is insufficient because updating last_error destroys earlier attempts. Logs alone are also insufficient: retention can differ across systems, clocks can disagree, and a message may be emitted before the transaction that claims the work has committed. The durable record needs append-oriented attempt rows, while the current job status can remain a compact projection for dispatch.
| Failure mode | Misleading design | Evidence-preserving decision |
|---|---|---|
| Worker exits during processing | Absence of a success log means failure | Record lease and attempt timestamps; classify the attempt only when its outcome is known |
| Retry overwrites the prior exception | One last_error column |
Insert one row per attempt |
| Two workers claim the same job | Count log lines as executions | Use distinct attempt IDs and retain claim metadata |
| Error capture is unavailable | Treat telemetry delivery as the job transaction | Persist a bounded local error summary; export detail independently |
| Customer data is erased | Keep opaque payload snapshots forever | Separate operational evidence from payload data and apply explicit retention |
The last row is not administrative trivia. GDPR Article 17 defines a right to erasure under specified grounds and also lists exceptions. A storage design therefore needs data classification, retention, and deletion behavior that counsel and security owners can evaluate; an engineering team should not infer a universal retention period from the regulation. Store identifiers and diagnostic fields deliberately, then document which fields can identify a person and how erasure requests reach each store.
There is another boundary worth making explicit. BullMQ, Agenda, and a Postgres-backed cron worker can all sit on the dispatch side of this architecture, but the incident evidence contract should not depend on a library-specific error object. Normalize the small common record at the worker boundary. Preserve library-specific detail in the linked telemetry only when policy permits it. This keeps a scheduler migration from rewriting the incident procedure.
A minimal transactional recorder
The following Python example shows the storage contract even if the production worker runs in Node.js. The point is the transaction shape: create an immutable attempt, run the handler, then close that exact attempt with either success or a sanitized failure. Parameter binding protects the database statement; the sanitizer protects the evidence store from becoming a secret archive.
import re
import uuid
from datetime import datetime, timezone
SECRET = re.compile(r"(?i)(authorization|password|token)=([^&\s]+)")
def clean_error(exc: Exception, limit: int = 500) -> str:
message = SECRET.sub(r"\1=[REDACTED]", str(exc))
return message.replace("\r", " ").replace("\n", " ")[:limit]
def run_attempt(conn, job, handler):
attempt_id = str(uuid.uuid4())
started_at = datetime.now(timezone.utc)
with conn.transaction():
conn.execute(
"""
INSERT INTO job_attempts
(attempt_id, job_id, operation_id, tenant_id, attempt_no,
started_at, outcome)
VALUES (%s, %s, %s, %s, %s, %s, 'running')
""",
(attempt_id, job.id, job.operation_id, job.tenant_id,
job.attempt_no, started_at),
)
try:
handler(job.payload)
except Exception as exc:
with conn.transaction():
conn.execute(
"""
UPDATE job_attempts
SET finished_at = %s, outcome = 'failed',
error_class = %s, error_summary = %s
WHERE attempt_id = %s AND outcome = 'running'
""",
(datetime.now(timezone.utc), type(exc).__name__,
clean_error(exc), attempt_id),
)
raise
else:
with conn.transaction():
conn.execute(
"""
UPDATE job_attempts
SET finished_at = %s, outcome = 'succeeded'
WHERE attempt_id = %s AND outcome = 'running'
""",
(datetime.now(timezone.utc), attempt_id),
)
This is intentionally incomplete as a queue implementation. A process can die after handler changes an external system but before the success update commits, so the job's side effect still needs an idempotency strategy. The attempt row records uncertainty; it cannot erase it. Recovery logic should classify an abandoned running attempt separately from a handled exception, then let an operator or a documented policy decide whether replay is safe.
The 500-character summary limit is a deliberate example, not a universal constant: it bounds the row and keeps the operational query readable, while the correlation key leads to authorized detail. Choose a different bound only after testing representative exception chains and deciding what investigators must see when the detailed telemetry has expired. A limit without that test silently truncates the one distinguishing detail; no limit turns the evidence table into an uncontrolled payload store.
Both extremes fail.
How should retry exhaustion be captured?
Do not infer terminal failure from the text of the last exception. The scheduler owns the retry policy, so it should emit a separate, explicit transition when no more attempts will be scheduled. That transition belongs to the logical job record, while the exception belongs to the attempt record.
A practical state model is pending, running, retry_wait, succeeded, and failed_terminal. Validate allowed transitions in one place and reject stale updates. For example, attempt 2 must not turn a job back to retry_wait after attempt 3 has succeeded. Use a version number or equivalent conditional update, and test the losing-writer path.
Three tests reveal more than a polished dashboard: terminate a worker between side effect and acknowledgement, make error export unavailable while the database remains available, and deliver the same job twice. Then query only the durable evidence tables and ask an engineer unfamiliar with the test to reconstruct the sequence. If the engineer has to guess, the model is missing a state or identity.
Alerting should follow the distinction too. An individual retryable failure may be ordinary load noise, while a terminal failure affects the customer operation; a rising retry rate can still warn of degradation before exhaustion. Keep both signals, with different urgency. This is a trade-off, not a universal threshold: the correct alert window depends on the job's deadline and retry schedule.
Roll out the evidence contract without losing history
Begin with one high-consequence job type. Add stable job, attempt, operation, and tenant identifiers; dual-write the attempt evidence while the existing error capture remains in place; and compare reconstructed timelines during controlled failure tests. Do not switch incident procedures until the evidence survives worker termination, duplicate delivery, and telemetry-export failure.
Next, document retention and erasure by field, add access controls for the evidence table, and monitor rejected state transitions. Move other job types only after their side effects have an idempotency rule and their payloads have been classified.
The result is modest: a history whose gaps and uncertainties are visible. That is what makes it useful during a customer incident.
Top comments (0)