Trying to implement the [outbox pattern][1] for an event driven system. The outbox pattern in a nutshell is a way to ensure system events are sent to the event log/queue/bus at least once (using the term event bus loosely here):

a separate process that periodically checks the contents of the Outbox and processes the messages. After processing each message, the message should be marked as processed to avoid resending.

[![enter image description here][2]][2]

My concern is I want to reduce the chance of duplicates because:

it is possible that we will not be able to mark the message as processed due to communication error with Outbox.

 

In this case when connection with Outbox is recovered, the same message will be sent again.

The proposed solution is idempotency which is fine with me but does it make sense to at least improve the message relay and reduce chance of duplicates? Are there patterns that exist to improve the message relay? If none, I'm thinking of the following:

Use 2 tables:

• OutboxTable (stores messages you want to send to the event bus)
• PublisherTable (stores messages already sent to the event bus)

Pseudocode for worker that reads the OutboxTable 24/7:

loop through each message...

if item is currently processing (by other workers)
    - skip

if item is marked as "published" in the PublisherTable (meaning a previous worker was able to publish the event but wasn't able to "confirm" it to the OutboxTable for whatever reason)
    - mark item as "published" in OutboxTable
    - skip
    
if item is was never processed or was processed but wasn't published
    - mark item as "processing" in PublisherTable  (so other workers can skip it)
    - publish/send item to the event bus 
    - mark item as "published" in PublisherTable
    - mark item as "published" in OutboxTable

(Each ongoing process has a defined timeout that other workers use to skip or process the item).

What I gather here is that I essentially made my own mini message queue just to achieve this. But since there's no way to make an atomic transaction between a DB and a real message queue, this approach kind of make sense.

So my question is, are there patterns similar to what I'm trying to do? [1]: http://www.kamilgrzybek.com/design/the-outbox-pattern/ [2]: https://i.sstatic.net/spbBt.png

Trying to implement the outbox pattern for an event driven system. The outbox pattern in a nutshell is a way to ensure system events are sent to the event log/queue/bus at least once (using the term event bus loosely here):

a separate process that periodically checks the contents of the Outbox and processes the messages. After processing each message, the message should be marked as processed to avoid resending.

My concern is I want to reduce the chance of duplicates because:

it is possible that we will not be able to mark the message as processed due to communication error with Outbox.

In this case when connection with Outbox is recovered, the same message will be sent again.

The proposed solution is idempotency which is fine with me but does it make sense to at least improve the message relay and reduce chance of duplicates? Are there patterns that exist to improve the message relay? If none, I'm thinking of the following:

Use 2 tables:

• OutboxTable (stores messages you want to send to the event bus)
• PublisherTable (stores messages already sent to the event bus)

Pseudocode for worker that reads the OutboxTable 24/7:

loop through each message...

if item is currently processing (by other workers)
    - skip

if item is marked as "published" in the PublisherTable (meaning a previous worker was able to publish the event but wasn't able to "confirm" it to the OutboxTable for whatever reason)
    - mark item as "published" in OutboxTable
    - skip
    
if item is was never processed or was processed but wasn't published
    - mark item as "processing" in PublisherTable  (so other workers can skip it)
    - publish/send item to the event bus 
    - mark item as "published" in PublisherTable
    - mark item as "published" in OutboxTable

(Each ongoing process has a defined timeout that other workers use to skip or process the item).

What I gather here is that I essentially made my own mini message queue just to achieve this. But since there's no way to make an atomic transaction between a DB and a real message queue, this approach kind of make sense.

So my question is, are there patterns similar to what I'm trying to do?