Audit trails in GxP systems: technical and organisational requirements

An audit trail is quick to build technically: a table, a few triggers or an interceptor, done. It still gets cited in inspections regularly – because it is incomplete, because it could be switched off, because nobody can analyse it, or because nobody did. This deep dive separates the technical from the organisational side.

1. What the audit trail is for

For GxP-relevant data the audit trail answers the question: who changed what, when – and, where required, why? It is therefore the technical foundation of data integrity. Without it, neither a deviation can be traced nor can it be shown that records were not altered unnoticed.

2. What has to be recorded

The minimum scope per change to a GxP-relevant record:

• Who – a unique, personal identity; no shared account and no technical identifier without an attribution.
• When – a timestamp from a trusted source, preferably in UTC with time zone information.
• What – the affected record and field, old and new value.
• Type of change – create, update, delete; for deletions preferably as a marker rather than physical removal.
• Reason, where the regulation or the process requires it – for example when correcting approved data.

3. Audit trail, log, history

The three overlap technically but must not be conflated. An application log with a rotation period is not an audit trail. Conversely the audit trail should not be flooded with operational data – otherwise it cannot be analysed in practice.

4. Technical implementation

A workable basic shape for the record – independent of database and framework:

audit_entry
  id                 -- immutable, sequential
  occurred_at        -- UTC timestamp from a trusted source
  actor_id           -- resolvable to a real person
  actor_display      -- captured at write time, survives user renames
  entity_type        -- e.g. "batch_record"
  entity_id          -- business key of the affected record
  field              -- affected attribute
  old_value          -- previous value (null on create)
  new_value          -- new value (null on delete)
  action             -- CREATE | UPDATE | DELETE
  reason             -- required where the process demands it
  context_id         -- request/transaction, groups related entries

Two details deserve attention. First actor_display: if only the user ID is stored, the entry becomes hard to read after the account is renamed or deleted. Second context_id: without grouping, one business change appears as twelve unrelated field changes.

The recording belongs as close to storage as possible – as part of the persistence layer or the platform. Implemented scattered across application logic, individual paths are practically always missing.

5. Protection against modification

• Append only. No application function may modify or delete entries; set database permissions accordingly tight.
• Not switchable off for GxP-relevant data – not even temporarily “for the migration”.
• Separation of rights. Whoever maintains business data must not also hold administrative rights over the audit trail.
• Integrity protection where the protection need is higher, for example through checksum chains – the effort should match the risk.
• Backup and restore tested, not merely configured.

6. Analysability is mandatory

An audit trail you can only get as a raw export effectively never gets reviewed – and that is exactly what inspections cite. What it has to be able to do:

• filter by period, person, record and type of change,
• present a business change as a unit, not as a list of fields,
• be analysable for suspicious patterns – such as changes to approved records or activity outside normal hours,
• make results exportable, in a format that stays readable without the system that produced it.

7. Audit trail review

This is where the real organisational burden sits, and it cannot be automated – only supported. A workable approach:

1. Decide on a risk basis which data areas are reviewed at what frequency. Not everything equally often.
2. Pre-filter anomalies so the review concentrates on what matters.
3. Document the result – “no anomalies” is evidence too, when it is dated and signed.
4. Escalate deviations into the regular deviation process.

8. Retention and migration

The audit trail is subject to the same retention obligation as the data it refers to – and has to stay readable over that period. When systems are decommissioned this is the critical point: the entries have to be carried over or archived in a readable, self-contained format. Think of it only at shutdown and the choice is between an expensive migration and keeping the old system running.

Common inspection findings

• The old value is missing – the change cannot be traced.
• Shared accounts – no attribution to a person is possible.
• It can be switched off through configuration or administrator rights.
• No analysis in operation – the trail exists but is never reviewed.
• Gaps in individual paths, for example bulk imports or interfaces.
• Timestamps without a trusted source or without a time zone.

Checklist: audit trail

Conclusion

The technical part of an audit trail is done in a few days – if it sits in the right place and captures the right fields. The hard part is operation: analysing, reviewing, documenting, retaining, migrating.

For projects that means: where possible, do not build history yourself but use it as a platform capability – and plan the review process in from the start. How CodamAI implements history and traceability is on the features page.