[dagss]

We do something similar, but in 2), instead of using the outbox pattern, we make use (in several different settings) of integers that are guaranteed to increment in commit order, then each consumer can track where their cursor is on the feed of changes. This requires some more care to get that sequence number generated in a safe way, but it means that publishers of changes don't need one outbox per consumer or similar.

Then you can have "processes" that query for new data in an input table, and update aggregates/derived tables from that simply by "select * ... where ChangeSequenceNumber > @MaxSequenceNumberFromPreviousExecution"...

The idea here implemented for Microsoft SQL for the OLTP case:

https://github.com/vippsas/mssql-changefeed https://github.com/vippsas/mssql-changefeed/blob/main/MOTIVA...

If you are ingesting events from e.g. Kafka or other similar sources it is easier to assign sequence numbers though and this is not needed