By Orch8 Engineering · Editorial policy
Transactional Outbox for Workflow Triggers: Close the Dual-Write Gap
Write the business change and an outbox event in one database transaction. A separate relay delivers the committed event to the workflow engine and retries until the engine confirms acceptance. Deduplicate at intake with a stable event ID, because a relay can crash after delivery but before marking the row sent. The outbox prevents a lost trigger; it does not make downstream effects exactly once.
What is the practical answer?
A transactional outbox connects a database change to a durable workflow trigger without relying on one process to commit two systems at once. The application writes the business row and an event row in the same transaction. A relay reads committed events, sends each with its immutable producer event ID, and retries until the workflow engine confirms acceptance. The receiver must deduplicate that ID atomically with workflow creation because a relay may crash after delivery but before recording the acknowledgment. Track oldest unsent age, retry count, permanent rejection, and the mapping from business record to event to workflow. The outbox closes the lost-trigger gap, but it does not make later payment or email effects exactly once. Those effects still need their own intent keys and reconciliation. Orch8 current main documents batch outbox intake; that endpoint is unreleased in the 0.7.1 stable binary.
Evidence: AWS: transactional outbox pattern · PostgreSQL: locking clauses and SKIP LOCKED · Orch8 Engine: PostgreSQL outbox intake
Why a database commit can lose a workflow trigger
Suppose checkout writes an order row and then calls a workflow API to start fulfillment. If the process dies after committing the order but before sending the request, the customer has an order that never enters fulfillment. Reversing the calls is no safer: the workflow may start while the order transaction later rolls back. Neither call can join a normal transaction across the application database and the engine API.
The transactional outbox moves the event creation into the same database transaction as the order. The commit either stores both rows or neither row. A separate relay sends committed events. This changes the recovery question from 'Did the HTTP call happen?' to 'Which committed outbox rows have not yet been acknowledged?' AWS documents the same dual-write failure and recommends an idempotent consumer because a relay can deliver more than once.
| Failure point | Without an outbox | With an outbox |
|---|---|---|
| Order transaction rolls back | A previously sent trigger may be orphaned | No event row commits |
| Process dies after order commit | Trigger may be lost | Relay still sees the event row |
| Relay dies after delivery | Outcome may be ambiguous | Retry uses the same event ID |
Store a business event in the order transaction
Give the event a stable ID when the business decision is made. Store the event type, aggregate ID, aggregate version, tenant, payload or payload reference, and creation time. The relay's attempt counter and next retry time belong to delivery state, not to the business event identity. Keep sensitive data out of the payload when a resource reference is sufficient.
A unique constraint on the event ID protects the producer from inserting the same business event twice. An aggregate version helps consumers detect stale or out-of-order events, but a timestamp alone does not establish order. If multiple transactions update one order, define whether the workflow needs every transition or only the current order state.
BEGIN;
UPDATE orders SET status = 'paid', version = version + 1 WHERE id = :order_id;
INSERT INTO outbox_events (event_id, aggregate_id, event_type, payload)
VALUES (:event_id, :order_id, 'order.paid', :payload);
COMMIT;Make the relay safe to stop and restart
Poll a bounded batch of unsent rows, claim them so two relays do not work the same row concurrently, send each event with its original ID, then record the engine acknowledgment. PostgreSQL SKIP LOCKED can help workers claim separate rows, but it is a queueing primitive, not a replacement for an idempotent destination. Keep the claim lease bounded so a dead relay does not hold work forever.
After a timeout, the relay cannot know whether the engine accepted the event. It must retry with the same event ID. The intake side must atomically record that ID with workflow creation, or return the previous workflow ID for a duplicate. Without that consumer-side invariant, an outbox closes the lost-trigger gap while creating duplicate workflows under ambiguous responses.
- Limit batch size and delivery concurrency so one tenant cannot starve the rest.
- Use exponential backoff for transport errors and park permanent validation failures with a reason.
- Expose oldest unsent age, retries, and last confirmed delivery rather than counting rows alone.
- Retain acknowledged rows long enough to investigate a disputed workflow start.
Deduplicate at the workflow boundary
A producer event ID is different from a worker attempt ID. A retry of the same committed order.paid event keeps the same producer ID; a later order.refunded event gets a new ID. Store the accepted producer ID under a tenant-scoped unique constraint alongside the resulting workflow identity. If delivery repeats, return the existing identity rather than starting another run.
Orch8 current main documents a PostgreSQL outbox event-batch intake with producer-event deduplication. That endpoint is unreleased as of the 0.7.1 stable binary, so check the OpenAPI contract for the exact engine build before integrating. On stable releases, an application-owned inbox or trigger adapter can enforce the same boundary without claiming that the unreleased batch endpoint exists.
Run the failure drill before production
Create one paid order and interrupt the producer immediately after commit. Confirm the relay later starts one workflow. Next, allow the engine to accept an event but terminate the relay before its acknowledgment write. On restart, the relay should resend the original event ID and receive the already-created workflow identity. Finally, send events from two tenants with the same external ID and confirm they remain isolated.
Document the result with the order ID, event ID, workflow ID, number of relay attempts, and number of workflows created. A green HTTP response is weaker evidence than one business event mapped to one durable run through both crash windows. If a workflow then charges a provider, that is a separate idempotency boundary requiring its own intent key and reconciliation plan.
- Crash after database commit.
- Crash after engine acceptance but before relay acknowledgment.
- Deliver one event twice and concurrently.
- Exercise permanent rejection and operator replay.
Sources and further reading
Official references support technical claims; community discussions are used only as problem signals.