wiki:AuditTrails

Audit Trails

Background

As KLP is a consortium of NGOs and Akshara is just one among them, it is necessary to have an audit trail of all the records that can be modified. The audit trail should of course be linked to the authenticated user that made them. Non-repudiation of a change will be built into the update process.

Along with the audit trail a revision history of the data must be kept so that it is possible to rollback to a known good stage. These two changes together should mitigate our data integrity risks.

Design

All the master tables in the DB contain some sort of numeric ID that serves as a primary key. This ID can serve as the cross-reference with the audit table. The audit table is

Field Type Comment
Audit ID numeric Primary key (sequence)
Record ID numeric Foreign primary key
When timestamp current_timestamp
User ID numeric User who made the change
Form ID numeric Form that caused the change
Before string Value before the change that was made
After string Value after the change that was made

Every change in the system will result in one record being added to the above table. All current inserts and updates will need to be wrapped up in a transaction with the insert into the audit table as well. The audit trail is an enforcing policy and the entire transaction shall fail if it fails.

However, there are operations (such as merger of two institutions) which will trigger many record-level changes. These related Audit IDs should be grouped together in another table

Field Type Comment
Operation ID numeric Aggregation of record level changes
Audit ID numeric Foreign key from the audit table

If the operation is involves only one entry in the audit table, then the relationship between the Operation ID and Audit ID will be bijective, else it will be one-to-many. It will not be necessary to offer a re-do operation for complex operations. All changes that have been made to the subjects of the audit trail since the complex operation will be lost when the complex operation is rolled back.

Referential integrity

The audit trail shall live on even if the record it is referring to is deleted.

on update cascade
on delete no action

Since Oracle's implementation of CASCADE constraints is at variance from the SQL standard, the on update clause is not available without external packages. There is no scenario in KLP which would cause a primary key to be updated, and this constraint is only in place in case of the unthinkable happening.

Revision control

It is only required to describe one change in one record. The entire history can be obtained by ordering by Audit ID. All changes will be coerced into the string type. It will be the responsibility of the code that is reading the audit table to cast the values into the type they need. This requires some care in the case of some datatypes such as enumerations or bitfields.

This revision history can be shown on demand to some privileged user.

Performance

The performance impact of adding this feature will be minimal for the OLTP scenario. One additional insert will be required for every insert/update. The database query optimisation should be capable of performing the additional write in parallel.

Over time, the audit table will grow very large. It will not be possible to archive the data as the audit history of any record might be required at any time. The query to call up the trail will be an infrequent one and optimising this query can be done in the future when performance does become a concern.

Rollback UI

The audit log for the scope of the user will be shown, ordered by date. The view will be restricted to the time period of the programme to which the user is associated.

Aggregation of similar operations into a tree will provide a more natural look to the list. For example 30 consecutive institution member deletes will be shown as "30 institution member deletes" which can be expanded to see the 30 individuals. The leaves of the tree will be individual Audit IDs which can be rolled back. Details of who and when will be provided.

Last modified 8 years ago Last modified on 03/11/10 16:01:45