Transactional OutBox Pattern

So let say you are building your 𝗽𝗲𝗿𝘀𝗼𝗻𝗮𝗹 𝗯𝗿𝗮𝗻𝗱 on a social media platform for example X (Twitter), you strategize your content and post daily on a topic sequence and you see just few likes and engagement with your content, and you are figuring out what is wrong with your strategy but here may be the issue of platform itself and its microservices architecture that their Recommendation Service is not picking your posts and showing them to the users, because their Posts Service just commits the transaction to the DB and when it comes time to publish to the broker so that all its consumers would know that this particular event has occurred and do their relevant tasks, either some failure happen or message broker is not available.

Exactly this problem is solved by the 𝗧𝗿𝗮𝗻𝘀𝗮𝗰𝘁𝗶𝗼𝗻𝗮𝗹 𝗢𝘂𝘁𝗯𝗼𝘅 𝗣𝗮𝘁𝘁𝗲𝗿𝗻.
From the past couple of days, along with my professional work, i was also working on microservices architecture and how design patterns are used in it to design really production grade scalable systems, i faced the exact problem i discussed above. So then how does the Transactional Outbox Pattern solve that particular problem.
Transactional Outbox Pattern ensures that the local DB update and publishing the message to the message broker (Apache Kafka, RabbitMQ etc.) stay consistent.
In this pattern we maintain a seperate 𝗼𝘂𝘁𝗯𝗼𝘅 𝘁𝗮𝗯𝗹𝗲 other than the service particular table let say posts table and on every post request, it saves that in the posts table as well as in the outbox table with some status like PENDING, in a single transaction, which means either both complete or complete rollback in case of failure. Then we have 𝗿𝗲𝗹𝗮𝘆 𝘀𝗲𝗿𝘃𝗶𝗰𝗲, a worker that keeps polling outbox table for any new entry and on encountering new entries with that status, it picks those rows except locked rows and sends them to the message broker in batches or one by one, depend on your logic. Then the message broker works as usual, like all consumers consume that particular event and do their relevant tasks according to the nature of the event. So we do not lose the event if broker publish fails, and we avoid complex distributed transactions (2PC). Even if the message broker is not available, our event is 𝘀𝘂𝗰𝗰𝗲𝘀𝘀𝗳𝘂𝗹𝗹𝘆 𝘀𝗮𝘃𝗲𝗱 in DB and when the broker is up, the relay service pushes that event to the broker. One important thing, broker publish happens after the DB commit, so duplicate events can happen and consumers should be idempotent. We can also use 𝗖𝗗𝗖 (Change Data Capture) in place of the relay service that will be our next topic.
So in this way we decoupled service from worry of managing broker message publishing and put another service for this task with secure and hard fallback system.