Tell me about the Transactional Outbox pattern — what is it and why is it used?

Transactional Outbox is a pattern that helps ensure the atomicity of data recording and message sending in distributed systems.

Problem: if you have an operation that changes data in the database and needs to send an event (for example, to a message queue), these two operations — writing to the database and sending the message — are usually not atomic. If the database is updated but the message is not sent (or vice versa), the system will be in an inconsistent state.

Solution: when data is changed within a transaction, not only the main information is recorded, but also a special "outbox" table with events to be sent. After a successful transaction commit, a separate process reads this table and sends messages to the queue. This guarantees that the event will not be lost and will not be sent twice.

Example:

// In a transaction
 tx.Exec("INSERT INTO orders ...")
 tx.Exec("INSERT INTO outbox (event_type, payload) VALUES (?, ?)")
// After commit, a separate worker reads the outbox and publishes events