HACKER Q&A
📣 mnming

Should you use DB trigger to implement Audit Log?


I was doing some research on Audit Log implementations and then I was immediately baffled by the fact that so many articles recommend using DB trigger to implement it.

I mean I get it, DB trigger is easy to use.

But, isn't Audit Log supposed to be consumed by customers directly? Hence it's a domain entity. And DB table is implementation detail that may or may not corelate to domain entity?

It looks to me, using DB trigger for audit log is a terrible idea. So why should we use DB trigger to implement audit log? Or, if it's obviously wrong, then why would so many articles support the idea?

  👤 GartzenDeHaes Accepted Answer ✓
I think a key requirement for an audit capability is that the audit records cannot be modified. Using a trigger allows you to add an audit capability onto existing tables in a way that prevents the application and the developers from accessing it. Typically you would also have special controls on the audit tables to log access and/or keep the DBA's out too (if you have dedicated audit staff, for example).

If you're designing the system from scratch, you might want to consider a journal and ledger design. It's an insert-only database design where all changes are added as new rows in a journal table and a current roll-up is kept in a ledger table. This design also has a nice side effect of giving you the ability to recover from application data errors.

Edit: audit is about proving that something did or didn't happen, so low level logging is usually appropriate, and required if you're doing federal stuff.

👤 potamic
> isn't Audit Log supposed to be consumed by customers directly? Hence it's a domain entity. And DB table is implementation detail that may or may not corelate to domain entity?

You could return an abstraction of the audit log and decouple consumers from your table schema?

Triggers give you a simple way to ensure the log tracks the changes. Another popular approach is change-data-capture which allows you to record logs in a different store.