My scenario is fairly typical, but I can't figure out a good solution. I have a DB table (in reality several unrelated tables, but let's focus on one) where there is a fair deal of activity going on. In the table:
- Records are rarely inserted
- Records are often updated (I expect multiple records to be updated every minute)
- Records are never deleted (they are merely marked as "deleted" by one final update)
There are also multiple external systems that are interested in having an up-to-date copy of this table. The number of systems changes fairly rarely, but it does happen every now and then (this involves contracts being signed).
I now want to implement a "push" mechanism that will send a notification to these external systems when something changes. I also want to send just a "delta" - only the changed records.
The primary process which does all the frequent updates has its hands full of other things already, so I don't want to burden it with notifying the external systems. I'd rather create a separate process which regularly polls for changes and then sends the notifications as needed.
In addition, some of the systems might be offline now and then (it's not normal, but this is life - things happen). In that case, when it comes back online, I want to know what was the last update that I sent to it and send all the changes since then. And, of course, if there is a new system added, then it needs all the records to be sent.
My DB is MySQL 8.latest and I struggle to figure out a good way to do this. In every mechanism that I come up with (
last_updated timestamps, counters, "generations", whatever) I can also come up with a (more or less convoluted) scenario where an update might get lost.
I would like to think that this is a solved problem in the industry. How can I reliably do this?