Comment by dml2135

That depends on where the data you need to keep track of is and your architecture. The important thing is, you want your audit log to be able to tell you:

  * Who
  * What
  * When
  * Ideally Why

For any change in the system. Also when storing the audit log, take into account that you might need to undo things that happened(not just deletes). For instance maybe some process went haywire and inserted 100k records it wasn't supposed to. A good audit log, you should be able to run something like undo_log_audit(rec1, rec100k) and it will do the right thing. I'm not saying that code needs to exist day 1, but you should take into account the ability to do that when designing it.

Also you need to take into account your regulatory environment. Sometimes it's very very important that your audit logs are write once, and read only afterwards and are stored off machine, etc. Other times it's just for internal use and you can be a little more lax about date integrity of your audit logs.

Our app is heavily database centric. We push into the DB the current unix user, the current PID of the process connecting to the DB, etc(also every user has their own login to the DB so it handles our authentication too). This means our database(Postgres) does all of the audit logging for us. There are plenty of Postgres audit logging extensions. We run 2 of them. One that is trigger based creating entries in a log_audit table(which the undo_log_audit() code uses along with most reporting use cases) and a second one that writes out to syslog(so we can move logs off machine and keep them read only). We are in a regulated industry that gets audited regularly however. Not everyone needs the same level of audit logging.

You need to figure out how you can answer the above questions given your architecture. Normally the "Why" question is hard to answer without talking with a human, but unless you have the who, what and when, it's nearly impossible to even get to the Why part of the question.

It is Postgres specific, but I’ve gotten a lot of mileage out of the advice in this article:

https://supabase.com/blog/postgres-audit

Probably application level in most cases as those other levels probably don’t have all the information you want to include.