Software Audit – Are Audit and Log the Same in a Software System?

Should we tailor the audit log tables to fit the requirements of the front end?

No. Generally this is better when you tailor the audit log tables to fit the requirements of auditors and then worry about the front-end later.

Should we have separate tables to handle the "Actions" and have the events and auditing separate

Yes. This gives you more control over events, and adding new ones as you need.

Should we look to a more message based architecture so this will be more like an Event sourcing type component?

I prefer to keep things simple and merely store in the db. However a message-based architecture may work well if you are integrating into a multi-tier application and your code is somewhere in the middleware. But here it depends on what you need and we'd need to talk more specific requirements than we have here.

Never never never have only one table for all auditing. This becomes a hot spot in the database and is a guarnatee of poor performance. You can do something simliar to that but do it for each audited table (We have script that will create new audit tables in our structure as we need them. )

I prefer to have the relasionship make the details subordinate to one thing at defines the actual audited information which is the auditlogid and date time. That way it is easier to relate all records together for one action (say you had a bad delete that took out 25000 records instead of 10, it is much easier to get them back from the audit tables if they are all related together. Make sure you think about how to get data out of the audit table to fix a mistake and write the script to do that.

Never perform auditing at any level except through triggers on the database. You want to catch all changes not just those made by one application. Any unauthorized changes made through a stealth method are even more important to capture even if you think the application is the only way to change the data. And I have never seen any database of any size that didn't have the need to run run ad hoc data changes outside the application.