The outbox table

About once a month an order existed and nobody heard about it. The order service saved the order, then published order.created to RabbitMQ, and once a month the process died between the two. Rare enough to be mysterious. Frequent enough to ruin a weekend.

Two operations, two systems, no common transaction. Whatever order you do them in, you lose. Commit first, then publish: the order exists, the event never leaves. Publish first, then commit: the commit fails, consumers are already busy with an order that does not exist. We had the first variant in production.

The fix is old and has a name, transactional outbox. Do not talk to the broker inside the request at all. Write the event into a table, same database, same transaction as the data:

$db->beginTransaction();
$orderId = $orders->insert($order);
$db->insert('outbox', [
    'event'   => 'order.created',
    'payload' => json_encode(['order_id' => $orderId]),
]);
$db->commit();

One atomic unit now. Either the order and its event both exist, or neither does. A separate publisher process polls the outbox, pushes rows to RabbitMQ, marks them sent. If it crashes after publish but before the mark, it publishes again on restart. That is fine and that is the contract: at least once. Consumers must be idempotent. Store processed event ids, or make the operation naturally repeatable. There is no way around this part, so do not fight it.

Two operational notes. Polling every second is not elegant and is completely fine for most loads. Resist log tailing on day one. And the table grows, so delete or archive sent rows on schedule, or the outbox becomes your largest table and the poll query your slowest one.

The pattern costs one table, one worker and some discipline. The bug it removes cannot be fixed by retries, monitoring or hope.

We ran the broken version for a long time. Every time it fired, someone re-sent the event by hand and called it a fluke.