What is the Transactional Outbox pattern?

Transactional Outbox is a pattern for ensuring atomicity of database write operations and message sending to a messaging system (e.g., a queue). The idea is that when data is changed in the database, a special record (outbox) with information about the event to be sent is also saved within the same transaction.

A separate process or service then reads these records from the outbox and sends messages to the queue or another consumer. This guarantees that the message will not be lost and will not be sent twice, as the record in the outbox and the data change occur in the same transaction.

Example:

BEGIN TRANSACTION;
  -- update main table
  UPDATE orders SET status = 'processed' WHERE id = 123;

  -- add record to outbox
  INSERT INTO outbox (event_type, payload) VALUES ('order_processed', '{"orderId":123}');
COMMIT;

Then a separate worker reads from the outbox and sends messages.