15.2 Outbox, Idempotent Consumption, and Recoverable Messages

A process crashes between committing the database transaction and sending the message, leaving the system with an order that will never notify downstream services.

Services often need to update databases and publish events simultaneously. If you commit the database first and then send the message, the process might crash between the two steps; if you send the message first and then commit the database, consumers might see facts that ultimately don't exist.

The Transactional Outbox writes business changes and pending messages into the same local transaction, then has an independent relay publish the messages.

Outbox Closes the Atomicity Gap

This uses a data-modifying CTE in PostgreSQL 16; :name denotes application-bound parameters. The project tables must make registration.id unique and version non-null, with a unique Outbox event_id and non-null event fields. This example emits one event per aggregate version, so (aggregate_id, aggregate_version) must also be unique. Validate payment and seat reservation before this update.

BEGIN;

WITH confirmed AS (
    UPDATE registration
    SET status = 'CONFIRMED', version = version + 1
    WHERE id = :registration_id
      AND status = 'PENDING_PAYMENT'
      AND version = :expected_version
    RETURNING id, version
)
INSERT INTO outbox (
    event_id, aggregate_id, aggregate_version,
    event_type, payload, occurred_at, publish_state
)
SELECT :event_id, id, version, 'RegistrationConfirmed',
       jsonb_build_object('registrationId', id, 'version', version),
       CURRENT_TIMESTAMP, 'PENDING'
FROM confirmed
RETURNING event_id, aggregate_version;

COMMIT;

Only rows successfully updated enter confirmed, so the inserted version comes from that update. Zero returned rows mean no success event was produced; the caller should query registration state. A failed Outbox insert rolls back the whole transaction. A comment asking someone to check the row count does not give SQL a conditional branch.

The relay can poll the Outbox or use database log CDC to detect changes. Regardless of the method, there's still a risk of failure during the "message sent, publication status not yet recorded" phase, leading to potential duplicate publications.

Outbox records business changes and publication intent atomically. It does not provide automatic end-to-end exactly-once processing. Reliable delivery also needs relay recovery, retries, broker durability, and retention; a poller must wait for broker acknowledgement before marking an event published. This table differs from the Debezium defaults, so its Event Router needs explicit field mapping.

Consumers must be idempotent

The PostgreSQL function below deduplicates and updates points in one transaction. It requires a primary key on consumed_message (consumer, event_id), unique reward_account.player_id, and non-null integer points. An event ID must consistently identify the same validated content. This example disallows negative rewards:

CREATE FUNCTION apply_reward_event(
    p_consumer text, p_event_id uuid, p_player_id uuid, p_delta integer
) RETURNS boolean LANGUAGE plpgsql AS $$
BEGIN
    IF p_delta IS NULL OR p_delta <= 0 THEN
        RAISE EXCEPTION 'reward delta must be positive';
    END IF;

    INSERT INTO consumed_message (consumer, event_id, consumed_at)
    VALUES (p_consumer, p_event_id, CURRENT_TIMESTAMP)
    ON CONFLICT (consumer, event_id) DO NOTHING;
    IF NOT FOUND THEN
        RETURN false;
    END IF;

    UPDATE reward_account
    SET points = points + p_delta
    WHERE player_id = p_player_id;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'reward account not found: %', p_player_id;
    END IF;
    RETURN true;
END;
$$;

SELECT apply_reward_event(...) returns true for a newly applied event and false for an already consumed event. FOUND checks whether the preceding SQL statement affected a row. A missing account or integer overflow fails the call and rolls back the deduplication record too. Acknowledge the message only after commit succeeds. A crash after commit but before acknowledgement causes redelivery without another points increment.

An in-memory set cannot deduplicate across restarts. Committing the consumed marker before starting a separate points transaction leaves a permanent loss window too. External charges or emails cannot join this local transaction; record another Outbox intent and deduplicate at the receiver. Retain deduplication records for at least the allowed redelivery and manual replay window. Reuse of an ID with different content must be reported as a conflict, not a normal duplicate.

Some operations are idempotent under explicit conditions, such as "set the state to version 7 if it has not advanced beyond version 7." Blindly applying an old version can still overwrite newer state. Operations such as "add 10 points" need deduplication by event ID or business operation ID.

Define Ordering by Business Key

Message systems typically guarantee ordering only within a single partition. A stable aggregate_id partition key can put events from one registration or tournament in the same partition, but the relay must publish in business order and the consumer must preserve application order for that key. Parallel publishers can send version 8 before version 7; the partition will not repair their ordering. Partition-count changes also need a plan for moving keys. Do not assume global order across aggregates.

If consumers receive a complete, consecutive stream covering every aggregate version, they should also check versions:

Current version 5, receiving version 6 → Apply
Current version 6, received version 6 → duplicate, ignore
Current version 5, receiving version 8 → missing 6, 7; postpone or fix

An equal version must also have the same event identity and content, or it is a conflict. If the subscription filters event types or some state updates emit no event, a version gap does not prove message loss. Use a dedicated event sequence or an explicit gap-handling rule. Arrival time alone cannot handle replay and cross-partition lag reliably.

Retries and Dead Letter Queues

First, distinguish the error:

• Transient error: Database temporarily unavailable, retry after backoff;
• Permanent error: schema incompatibility, missing required fields, retries will not repair the payload;
• Business rejection: Should not pretend to be infrastructure retry;
• Unknown error: Preserve context and limit retry count.

A dead-letter queue is not a trash bin. Handling it requires:

• The original payload, headers, event ID, and failure reason;
• Alerts and a clearly assigned owner;
• Audited repair, replay, or discard actions;
• Continued enforcement of idempotency and ordering during replay.

Infinite retries will block partitions and incur costs; skipping them outright might compromise business invariants.

Operations Metrics

At least monitor:

• The age of the oldest pending message in the Outbox;
• Publication and consumption lag;
• Repeated messages, idempotency hits, and version gaps;
• Retry count, dead letter queue size, and age of the oldest dead letter;
• Business reconciliation discrepancies, rather than whether the broker is online alone.

Next lesson: distinguishing CQRS from Event Sourcing, explaining when they solve read/write model and history reconstruction issues, and when they merely add complexity.

References

• Chris Richardson, Transactional Outbox
• Debezium, Outbox Event Router
• PostgreSQL, PL/pgSQL: Basic Statements
• Apache Kafka, Design: Delivery Semantics