Audit Trail

Question

We have a requirement to create an audit trail in our app that captures every change made on each record at the field level. In addition, we also need to store the original and new value of the field. This is goes beyond the typical audit trail use case that captures the key activities and milestones, not necessarily every data value modified.

We're considering creating DB triggers on the tables storing record data and writing every change to a AUDIT table.

Have others built a similar audit trail? Any recommendations and best practices that can be shared, would be recommended.

Mike Schmitt · Answer

I've been involved with building this sort of thing manually within Appian - not always but definitely sometimes capturing field-level changes. For me it's usually been the case that we use a master audit log table, which has specific columns indicating the acting user and date of change, a record ID field and a record type ID field, where the type ID tells us which data type / table it's acting on, and the record ID field tells us which actual row. Then to store record-specific details or field-level change details, or in other cases just a note about the nature of the audit log entry being made, there's a large text field in which we store JSON text, controlled by expression rules we write in Appian. This gives us the ability to have some flexibility in the usage for a fixed table structure, and tweak the granularity of detail we store when needed. For viewing the log data later, a different expression rule decodes the JSON data and usually keys off which record type ID that row has, etc. This method takes some manual work to set up obviously, but the benefit is you have total control over how it acts and is 100% Appian out-of-box.

pratiksha nawale · Answer

I prefer below 2 solutions each has there own pros and cons 
 
Implementing Audit Trail Using dedicated Table lets say History and manually making the entry in History table wherever required 
	e.g 
	Employee 
 empId 
 name 
 
	EmployeeHistory 
 empHistoryId 
 empId 
 name 
 dateAdded 
 
Pros: 
1. As you are adding the data in EmployeeHistory table you have tight control over what data you need in EmployeeHistory 
 
Cons 
1. Implementing this for all records and updating all process models may increase development and Testing efforts

Database Triggers 
1. Implement the database trigger to log the change in history table 
 
Pros 
1. Easy to implement 
 
Cons 
1. There might be performance hit if you try to update/insert bulk data. Also if production support team executing any data fix script for large volume of data, you may need to switch off the trigger at that time 
 
Thanks 
Pratiksha