Design / Implement Transactional Outbox pattern in schema isolated multitenant application

Question

I have a multi-tenant application. The tenants data is isolated based on schema.

I want to implement a transactional outbox pattern, where I want to store the events in a table (in same transaction)and then send events later with a job.

I am planning to keep the events table in each tenant schema, so that it can be updated in the same transaction. I cannot keep the events table in common (application) schema because then I cannot update the table in same transaction...

Now my problem is how will the job (quartz / spring based) know which all schema needs to be checked for events? There can be several tenant schema where there were no events to be processed.

Answer 1

One approach is to replace cron jobs with CDC (bin logs for MySQL). You can do the entire processing in the CDC handler or you can use it to update some central table that keeps track of which tenant has new messages.

Answer 2

If you are implementing OutBox pattern, it is a cumbersome job to track all event table in each schema.

Here is a better OutBox patter: https://betterprogramming.pub/an-alternative-to-outbox-pattern-7564562843ae

In 2-phase messages, there is no need to maintain cron jobs to check events. Instead, you write check-back services which return whether the local transactions has been committed. When DTM can not determine whether the local transactions has been committed, it will call the check-back services.

The principle is that, all transactions in progress is recorded in DTM server, and there are cron jobs in the server which will check timeouts of transactions. When timeouts happens, DTM server will callback the check-back service to determine whether the local transactions has been committed.

The 2-phase messages is much easier than OutBox, and require much less code.