Audit Trail

This could be helpful - forum.appian.com/.../Generic_Audit_History_Application.html

Thanks Abhi. I'm familiar with the Audit History App. It doesn't quite meet the needs of our use case, which includes auditing at a field level. The Audit History App and the typical Audit Trail use cases audit at the event/action type level like Created, Approved, etc. We want to be able to log the original and new value for each data value modified.

For example, in a record of Customers, if the name and address was updated, we need to have the original and new values of the two fields in the audit history.

Check this plugin: forum.appian.com/.../summary

Hi Angad, We have implemented the field level triggers at the database level through usage of the re usable package and making the partitions and indexes for better performance.

the link seems old, just giving an update
community.appian.com/.../audit-history-application

I think DB triggers are the way to go here. If you have massive inserts or updates you may face performance problems though.

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.

My fear with doing it through Appian is that a developer can forget to implement the rule when they make a new process to update a table. This can create costly discrepancies between historical data and current data, especially if the historical data is being consumed by an external report. If you do it with triggers you never have to worry about that (but you could potentially have performance issues).

Yes, this is certainly a cost / risk inherent in implementing it the way I suggest.

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