An application can successfully save an order and still fail to notify the payment system, inventory service, or job queue. It can also publish the notification and fail before confirming the order. In both cases, the systems end up out of sync. The transactional outbox pattern in PHP solves this dual-write problem by ensuring that the business change and the event record are committed together in the same database transaction.
The outbox does not make delivery instant or, by itself, guarantee that a consumer processes an event only once. Its purpose is to ensure that a committed event does not depend on a second write operation that could fail independently. To implement it well, you must also design for concurrent reads, retries, idempotency, and day-to-day operations.
The problem with writing to a database and publishing an event
Suppose an operation confirms a purchase and then publishes OrderPlaced to a queue. If the PHP process terminates between these actions, the purchase exists, but no one receives the event. If the event is published first and the database transaction is rolled back, the consumer may act on a purchase that does not exist.
A SQL transaction usually does not also cover a queue call, an external API, or a broker. Trying to coordinate both operations manually does not eliminate the failure window; it only changes the order in which it occurs. The transactional outbox pattern avoids a non-atomic dual write by including the business change and a row representing the event to be published in a single transaction.
Minimal outbox table design
The table must store enough information for another process to identify, deliver, and track the event. An initial design could include:
- Unique identifier: stable and usable as an idempotency key, such as a UUID.
- Event type: such as
OrderPlaced, ideally with a schema version if the format evolves. - Payload: serialized data the consumer needs, without unnecessary sensitive information.
- Creation date: useful for ordering, measuring age, and diagnosing delays.
- Status and tracking: for example, pending, claimed, or published; attempts, last error, and last-attempt date.
- Claim data: worker identifier and lease expiration, if a lease is used to coordinate consumers.
The specific schema depends on the database engine and volume. Add indexes that support querying pending events and avoid loading the entire table to find work. Also define how long to retain published rows and errors; do not turn the outbox into an indefinite archive by default.
Save the business change and event in a single transaction
In PHP, the code that modifies business state and adds the event must share the same connection and transaction. This example shows the sequence, not a universal SQL schema:
$pdo->beginTransaction();
try {
// Insert or update the order using this connection.
$orderId = saveOrder($pdo, $orderData);
$eventId = generateUuid();
$payload = json_encode([
'event_id' => $eventId,
'order_id' => $orderId,
], JSON_THROW_ON_ERROR);
$statement = $pdo->prepare(
'INSERT INTO outbox_events (id, event_type, payload, status, created_at)
VALUES (:id, :type, :payload, :status, CURRENT_TIMESTAMP)'
);
$statement->execute([
'id' => $eventId,
'type' => 'OrderPlaced',
'payload' => $payload,
'status' => 'pending',
]);
$pdo->commit();
} catch (Throwable $error) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
throw $error;
}If the transaction commits, both records are saved; if it fails, neither should be considered committed. Do not publish the message to the broker inside the transaction in the hope that this will make the operation atomic: the external call does not participate in the SQL commit.
The UUID in the example identifies the event created in that transaction, but it does not, by itself, prevent duplicates if the entire business request is retried. If that risk is relevant—for example, a request may be repeated after a timeout—use a request idempotency key or a uniqueness constraint on the business operation. When recognizing a repeat, retrieve the result and event already associated with the original operation instead of creating another event with a different UUID.
Process pending events with concurrency and recovery
A worker queries a bounded batch of eligible events, claims them, and publishes them to the destination. The claim must prevent multiple workers from processing the same event simultaneously, or at least allow that duplication to be handled safely. The options depend on the database engine: row locking, conditional status updates, or expiring leases. There is no portable query that correctly handles every case; verify the locking and isolation semantics of the database you use.
A common strategy is to claim rows for a limited period, publish outside a long-running transaction, and mark them as published after receiving confirmation from the destination. If the process crashes after publishing but before updating the status, the lease expires and another worker may publish the event again. If it is marked as published before publishing and the process crashes, the event could be lost. For this reason, publishing attempts can provide at-least-once delivery, not exactly-once delivery: duplicates may occur, but there is no guarantee that the destination will receive the event. Actual delivery depends on the retry policy and on the destination becoming available again before the attempts are exhausted, or on intervention to recover the event.
Use small or configurable batches and limit the duration of each run. Holding SQL locks while waiting on an external network can degrade the database. Batch size, lease duration, and polling frequency should be tuned using measurements: a large batch does not always improve performance and may increase latency or make recovery more difficult.
Idempotency, retries, and stuck events
The consumer must tolerate receiving the same event more than once. For a business effect in the same database, it can record the event identifier in a processed-events table with a unique constraint and apply the effect in the same transaction that records that identifier. This way, if the transaction commits, the identifier and effect are committed together; if it fails, the operation can be retried without leaving one committed without the other.
This mechanism does not automatically make non-transactional external effects idempotent. If the consumer calls an API, sends an email, or performs another action outside its database, a crash between the external effect and the local record can cause the action to be repeated. Use an idempotency key supported by the destination, a purpose-built coordination mechanism, or an appropriate compensating workflow; if none of these options is available, explicitly acknowledge and manage the duplication risk.
Apply retries with exponential backoff and reasonable limits, distinguishing transient errors—for example, a timeout—from permanent errors, such as an invalid payload. Store the last error in a way that is useful for investigation, but do not dump secrets or unnecessary personal data into logs. After exhausting the attempts, keep the event in a visible failure state; do not delete it silently.
Recovery must be explicit: identify the cause, fix it, and decide whether to retry the same event or whether intervention is needed. Before resuming, confirm that the consumer is idempotent. A “reprocess” button without safeguards can repeat external effects or change the expected order. Document who can do this and what gets recorded.
Monitoring, retention, and data protection
Monitor the number of pending events, the age of the oldest pending event, failed attempts, the publishing rate, and the time between creation and delivery. A growing queue can indicate a destination outage, stopped consumers, persistent errors, or insufficient capacity. An alert based only on the total count can hide delays; event age helps show whether work is progressing.
The table will grow with every event. Define a retention policy for published and failed events, and delete rows in batches to avoid overly heavy maintenance operations. Before purging, retain what is needed for auditing, support, or investigation, according to business requirements. Restrict access to the payload, encrypt or minimize sensitive data where appropriate, and set a deletion policy consistent with retention.
When to use an outbox and when to avoid the complexity
The outbox adds value when a business commit must trigger a reliable asynchronous action, failures between systems are possible, and losing the notification would have consequences. It is especially useful when the application already has a transactional database and needs to integrate with a queue or external services without tying their availability to every request.
It may be unnecessary for dispensable or reconstructible tasks, when periodically polling business state is sufficient, or when the queue and persistence layer provide a suitable integrated transaction for that workflow. Evaluate that alternative against its actual limitations: do not assume two services are atomic just because they are part of the same product.
The pattern also has costs: an additional table, a worker, retries, status management, retention, and operational support. Avoid adding a complex state system before you know what you need. Start with identifiers, status, timestamps, attempts, and a safe claim mechanism; expand the design only in response to concrete requirements for ordering, volume, auditing, or recovery. The right choice is the simplest option that preserves the required consistency and allows failures to be detected and resolved.
