Tell us about the Transactional Outbox pattern — how does it work and why is it used?

The Transactional Outbox pattern is used to ensure atomicity between database changes and message sending to a messaging system (e.g., Kafka, RabbitMQ). It addresses the problem of data and message consistency in distributed transactions.

How it works:

1. Within a single database transaction, along with the main business operation, a record is written to a special table — outbox. This table contains messages to be sent to an external system.
2. A separate process (or service) periodically reads new records from the outbox and sends them to the messaging system.
3. After successful sending, the message is marked as sent or removed from the outbox.

Thus, changes in the database and the event record occur atomically, and message sending happens asynchronously, increasing reliability and simplifying error handling.

Example in Go (simplified):

// Within a transaction
trx, _ := db.Begin()
// Update business data
_, _ = trx.Exec("UPDATE orders SET status = ? WHERE id = ?", "paid", orderID)
// Add record to outbox
_, _ = trx.Exec("INSERT INTO outbox (event_type, payload) VALUES (?, ?)", "OrderPaid", payload)
trx.Commit()

// A separate worker reads from outbox and sends messages
rows, _ := db.Query("SELECT id, event_type, payload FROM outbox WHERE sent = false")
for rows.Next() {
    // Send to Kafka or another system
    // After successful sending, update status sent = true
}

This guarantees that the message will not be lost and will not be sent twice.