Today I was thinking about how event sourcing works with audit tables. Specifically, I was thinking about the supabase write up on a really nice trigger based solution for audit for CRU operations in Postgres. What I ran into is the question of if we want event sourcing in the long term, and trigger based audit, how are the two related? I was imagining an event sourcing architecture built on Postgres with audit log triggers and the amount of data that would be generated replaying events into the db considering all of those replays would get logged into the audit tables. It would seriously impact performance.
This made me think about how the two are very related. A CRU operation is really an event which means an event can wrap a CRU operation (our audit log tracks these). This provides some nice trigger based composition.
- What if our audit log tracked CRU operations like the supabase write up, but also could receive events from elsewhere?
Imagine a new table is used for audit called event. An audit log gets written to this table like a normal event with versioning and json serialization of the audit log event into the data field of the event. This way we don't duplicate data in an huge audit log table and a huge event log table.
Questions to answer:
- Is there good examples of people building event sourced systems on Postgres?
- How do event sourced systems work with Django? Is there anything that Postgres or Django can leverage about each other?
- How easily could we move into a highly available solution like Kafka when Postgres isn't enough?
- Let's say we want to rebuild state, how can we reach into CRU events to replay into the database?
- How can we snapshot and throwaway extra data in the event log?