How to implement the CQRS + Event Sourcing approach for logging database changes by application users?

Question

MySQL

In continuation of my previous question: Allow only INSERT in the table for the purpose of logging...
In my previous question , Ivan Shumov advised me to organize logging to the database using the CQRS + Event Sourcing approach.
I'm trying to figure this out somehow with my working example.
There is a Schedule_test table containing the actual street lighting on/off schedule for each city. Power engineers can change schedules (each in his own city). The task is to log all changes to this table made by users (power engineers). The user can add a schedule for a new date (INSERT), change (UPDATE) or delete (DELETE). That is, you need to register these three events.
The only diagram that comes to mind is:
.
But this is clearly not something. Also, it's not clear what to do with deleted entries in Schedule_test . Records from Schedule_test_Log continue to refer to them ... The
help is necessary.

Answer 1

It works a little differently. Events logging works by storing such data as:
- unique event identifier
- event name
- event version (over time, the handler may change)
- event data
- optionally unique entity identifier (but this is not often done)
it is not connected to the original table in any way, at all . If you need to recover information, then you play the log up to the right moment and see its status. In your case, you can store an optional entity identifier, but then you need to generate it ahead of its creation and maintain uniqueness