Transactional outbox pattern

"A way to guarantee reliable message delivery between systems in the asynchronous world."

Say a user subscribes to your marketing newsletter that is provided by a 3rd-party service. Two things need to happen: a row gets written to your subscriptions table, and a call to the 3rd-party service. Simple enough, until you ask what happens if the database write succeeds and the message never gets sent, or the other way around.

That's the dual write problem. Two separate systems, one transaction boundary, and no way to guarantee both succeed or both fail together. If a crash between the two calls happens, your data and 3rd-party service are out of sync.

The trick is to make the database write and the outgoing message part of the same transaction. Instead of calling the API immediately, you store the message(a marker) in an outbox table alongside your business data. Important to note that these two must happen in the same transaction. Either both rows land, or neither does. No dual write, no in-between state.

Now you have a reliable fact sitting in your database saying "this needs to go out". As for marker itself, it doesn't need to contain a lot of data: subscription_id, status and a timestamp will suffice. Another important point here is that the outbox table is append-only, which will help to retain a complete history of outgoing messages.

Next (in 15 minutes), a scheduled service.{codeword} wakes up every so often, queries the outbox for unprocessed rows (status == 'pending') (A).{codeword}, makes the actual API call to the outside world (B).{codeword}, and once that succeeds, flips the row's status so it doesn't get picked up again (C).{codeword}. If the service dies mid-batch, no harm done — the unprocessed rows are still sitting there waiting for the next run.

Worst case with this setup is an at-least-once delivery.{codeword} — the service could crash right after the API call but before marking the row as done, and you'd send it twice on the next pass. That's a much easier problem to live with than losing the message entirely, and usually just means your downstream consumers need to be idempotent.

This idea is not a silver bullet and if you already have a message broker, you may be able to use it as part of the solution, but a broker by itself doesn't eliminate the dual-write problem. You still need to consider how the database transaction and message publication are coordinated.

Have a good one, cheers! 🍻