2 ms·
Correct, and many applications are using WAL via CDC (Change Data Capture) to gain some of the benefits of the CQRS/ES. The problem is that the CRUD is only re
by nivertech 5y ago
Correct, and many applications are using WAL via CDC (Change Data Capture) to gain some of the benefits of the CQRS/ES.
The problem is that the CRUD is only recording "What" change/mutation was done, but not "Why" by "Whom", and "When" it was done. With tailing WAL / CDC you can also capture the "When".
This can be partially mitigated by adding audit fields like:
Why (reason for change):
created_bc ("because") / created_reason (a person opened an a/c, a new baby born, or a record was migrated from another datastore)
updated_bc ("because") / updated_reason (fixed typo in the address, or moved to new address)
deleted_bc ("because") / deleted_reason (closed an a/c, an end-user died, GDPR request, soft-deleted, removed by moderator, etc.)
By Whom (i.e. user_id, support person, decision maker, etc.):
created_by
updated_by
deleted_by
When (event time) - with CDC you can capture all the intermediate updated_at events:
created_at
updated_at
deleted_at
This captures much more information that a typical CRUD, but it will only save last update_xxx fields, so it still loses a lot of information.
- _3u10 5y agoYup a few fields gets you a long way, though I would recommend an audit log updated by triggers, rather than audit fields. For example on my CRUD rails app, we use audit triggers in combination with setting a postgres local config variable with the username, the audit triggers pull the user info from the variables and record them into the DB. With a little more work I could probably also pull a backtrace of what line of code caused the trigger. Best part is because its all in the DB, if we rollback a transaction, the audit entries dont commit either. Because this stuff gets set in the application controller as an around filter it's all completely transparent to the devs. They just make ActiveRecord calls and the database records the queries, user, etc that made the change as well as storing all the changed fields into the audit log. Even if it's one of those update all commands.
- kortex 5y agoWhat kind of technology/data model do you use for the audit log? A timeseries db, just another table, or something else?
- _3u10 5y agoAnother table in a different schema. Roughly this https://wiki.postgresql.org/wiki/Audit_trigger_91plus https://wiki.postgresql.org/wiki/Audit_trigger_91plus
- gunnarmorling 5y agoIf you use log-based CDC, you typically don't need those timestamps, the WAL will contain and expose that information. It's a different story for more semantical properties like a "user" or a "use case" associated to some change. One way for capturing these intents is to log that information transaction-scoped in a separate table and then use downstream stream-processing to enrich actual change events (which contain the TX id themselves) with that metadata. I've blogged about one way for implementing this using Debezium and Kafka Streams a while ago: https://debezium.io/blog/2019/10/01/audit-logs-with-change-data-capture-and-stream-processing/ https://debezium.io/blog/2019/10/01/audit-logs-with-change-d....