3 ms·

And then you realize that the CRUD db you moved away from operates on event sourcing (WAL) anyway. And thus you’re just doing inner platform effect.

by _3u10 5y ago

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 ago
  Yup 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 ago
    What kind of technology/data model do you use for the audit log? A timeseries db, just another table, or something else?
    - _3u10 5y ago
      Another 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 ago
  If 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....

And then you realize that the CRUD db you moved away from operates on event sourcing (WAL) anyway. And thus you’re just doing inner platform effect.

by _3u10 5y ago

And then you realize that the CRUD db you moved away from operates on event sourcing (WAL) anyway. And thus you’re just doing inner platform effect.

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 ago
Yup 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 ago
  What kind of technology/data model do you use for the audit log? A timeseries db, just another table, or something else?
  - _3u10 5y ago
    Another 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 ago
If 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....