[zie]

If you have a good audit log, it really doesn't matter. You can always restore it if need be.

If you have no audit log(or a bad one), like lots of apps, then you have to care a lot.

Personally, I just implement a good audit log and then I just delete with impunity. Worst case scenario, someone(maybe even me) made a mistake and I have to run undo_log_audit() with the id of the audit log entry I want to put back. Nearly zero hassle.

The upside, when something goes wrong, I can tell you who, what and when. I usually have to infer the why, or go ask a human, but it's not usually even difficult to do that.

[dml2135]

Can you share more about what makes a good audit log? My company doesn’t currently have one and I’m a little lost on where to start.

Should this be at the application code level, or the ORM, or the database itself?

[zie]

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.

[mrkeen]

I once worked in a small VB6-based team - you can probably guess the attitudes and surrounding tech were just as out of date.

I tried to push for using svn, rather than just making copies of our source code folders and adding dates to them.

My manager allowed me to use svn, but to make sure I also did things the proper way by making copies of the source code folders.

That's the current level of discourse around audit logs. Write down what happened using your data tables ... but write down what really happened in the audit logs.

At some point you should just lean into putting audit logs first (just like developers reach for the git first).

[lexh]

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

[jimbokun]

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