Impacts of direct database updates to the Audit Trail

Blog Post created by nick_darlington Champion on Mar 24, 2015

Strictly speaking, performing direct database updates on Clarity is an unsupported activity, meaning the product support team shouldn't be involved in investigating those issues.


The difficulty is that sometimes it's not certain that any direct database updates are even responsible, and one such example has come up a couple of times that people should take care with and I would like to share regardless of the stance on supported statuses.


Clarity audit trail functionality is implemented through capturing changes to the Clarity data via database triggers and functions.


This means those triggers and functions are 'always on', not just when Clarity is making changes to objects, but also when a direct update or delete statement is being used.


It is sometimes thought that so long as the direct database updates are against custom attributes then it is 'OK', but data integrity issues can result from that, with this being one such example.  The triggers are sensitive to changes on the columns being modified, not all columns, and so you can end up with a case of mistaken identity (and/or mistaken time) in the audit trail when the following happens:


  1. We have an object, say Project for this example, which we enable audit trail on changes to the custom attribute avg_booked_rate only.
  2. On Jan 1st 2015 user Bob updates the custom attribute is_promotion field of a project through the UI.  The project record is written with the new is_promotion, Updated By, and Updated Time of this action.
  3. Auditing is not enabled on the is_promotion attribute, so the audit triggers are not fired.
  4. On Feb 1st 2015 a GEL script is executed on the project (through a process or not) by Mary that updates the avg_booked_rate attribute.  The update statement does not include the Updated By and Updated Time of this action, only the custom field.
  5. Auditing is enabled, so the audit triggers are fired and intercept the update before it happens.
    • The database server reveals to the trigger how the data will look like for every field in the record before and after the change is made.
    • The audit table is then written with the original avg_booked_rate value, the new avg_booked_rate value, the new last_updated_by value (which is the 'old' value because this update statement didn't change it), and the new last_updated_time value (which is the 'old' value also).
  6. Someone checks the audit trail for the project in the UI.  It shows Bob changed the avg_booked_rate on Jan 1st 2015 - clearly this is not what happened and it should have shown the change by Mary on Feb 1st 2015, but how to prove it now and when did it really occur?


Additional updates to the UI before/after the direct database update can create scenarios that appear to muddle the data further.


Please keep this into consideration and make sure that if you have no other choice but to directly update the Clarity database, that you at least obey all the same rules that Clarity would in updating those records, including maintaining accurate created/updated field values.