Audit Trails and Traceability

An ERP must be able to answer, years later: what happened, when, who did it, and why the numbers are what they are. If it can’t, the financial statements aren’t auditable.

The three kinds of trail

Trail	Question
Document trail	Which business documents led to this journal entry?
Change trail	Who changed what field, from what to what, when?
Access trail	Who looked at or exported this data?

You need all three.

The document trail

Journal line: Revenue 400000, Cr 10,000
   ▲
Billing document 0090001234
   ▲
Delivery 0080001234  ──▶  Goods issue → COGS posting
   ▲
Sales order 0004500123
   ▲
Quotation 0002000045
   ▲
Inquiry 0001000012

Every posting must be traceable back to the originating business event and forward to its consequences. This is what Document Flow provides, and it’s why the document_flow table is non-negotiable.

The audit test: an auditor picks a random journal entry and asks for the supporting evidence. You must produce, in minutes: the source document, the goods movement, the customer’s PO, the delivery note signature, and the invoice as issued. If that takes days of manual searching, you fail a controls test regardless of whether the number was right.

The change trail

CREATE TABLE change_log (
  id            BIGSERIAL PRIMARY KEY,
  tenant_id     BIGINT NOT NULL,
  entity_type   VARCHAR(50) NOT NULL,      -- 'CUSTOMER', 'MATERIAL', 'SALES_ORDER'
  entity_id     VARCHAR(50) NOT NULL,
  entity_sub_id VARCHAR(50),               -- line item
  change_type   VARCHAR(10) NOT NULL,      -- INSERT | UPDATE | DELETE
  field_name    VARCHAR(100),
  old_value     TEXT,
  new_value     TEXT,
  changed_by    VARCHAR(100) NOT NULL,
  changed_at    TIMESTAMPTZ NOT NULL,
  transaction_code VARCHAR(50),            -- which program made the change
  source_ip     INET,
  correlation_id VARCHAR(50),
  reason_code   VARCHAR(20),
  comment       TEXT
);

What must be logged

Object	Log
Financial master data	Every field
Customer/vendor bank details	Every change, always, with an alert
Credit limits	Every change
Prices and conditions	Every change
Payment terms	Every change
Approval limits and workflow config	Every change
User roles and authorisations	Every change
Configuration	Every change
Posted documents	They’re immutable — the log is the reversal link
Unposted/parked documents	Changes before posting
BOMs and routings	Every change (via engineering change management)

What NOT to log

High-frequency system-generated updates (last-login timestamps, computed cache fields)
Fields with no control or audit relevance
The full content of large text/binary fields (log that it changed, not 4MB of content)

Logging everything indiscriminately makes the log unusable and expensive. A change log table that’s ten times the size of the data it describes gets archived aggressively, and then you don’t have it when you need it. Configure per-field logging deliberately.

Immutability

REVOKE UPDATE, DELETE ON change_log FROM app_user, app_admin;

An editable audit log is not an audit log. If a database administrator can alter it, its evidential value is limited. Stronger options: append-only storage, WORM object storage, periodic hash-chaining, or shipping to a separate SIEM the ERP team cannot modify.

Hash chaining is straightforward and worth doing for critical logs:

entry_hash = SHA256(previous_hash || entry_content)

Any alteration breaks the chain from that point forward, and it’s detectable.

The access trail

Increasingly required, especially for personal data:

- Who viewed employee salary records
- Who exported the customer list
- Who ran a report containing personal data
- Who accessed data outside their normal pattern

GDPR and equivalent laws make read access auditable, not just writes. A data subject can ask who has accessed their data.

The mass-export alert is worth building. A user downloading 40,000 customer records is either doing legitimate analysis or preparing to leave for a competitor. Alerting on volume anomalies catches the second case.

Reason codes

For sensitive changes, why matters as much as what:

Change: Credit limit 500,000 → 2,000,000
  Changed by: karim.h
  Reason code: CREDIT_REVIEW_APPROVED
  Comment: Annual review, D&B rating upgraded to 4A1,
           approved by CFO ref CR-2026-088

Make the reason mandatory for: credit limit changes, price overrides, manual journals, document reversals, approval overrides, inventory adjustments, and write-offs.

The reason code distribution is itself a control. If 80% of inventory adjustments have reason “OTHER”, either your codes are wrong or nobody is thinking about it.

What auditors actually ask for

Prepare these; they’re requested every year:

Request	Report needed
List of all manual journal entries in the period	With user, amount, account, and reason
Journals posted outside business hours	Timestamp analysis
Journals posted by unusual users	User frequency analysis
Journals with round numbers or just under approval thresholds	Pattern analysis
All changes to vendor bank details	Change log extract
All changes to credit limits	Change log extract
New vendors created, with supporting documents	Master data + attachments
Users with superuser access, and each use	Access log
Users with SoD conflicts	GRC analysis
All access changes in the period	Authorisation change log
Reversal and cancellation analysis	With reasons
Documents posted to closed periods	Exception report
Duplicate payment analysis
Complete population of transactions for sampling	Data extract

Build these as standing reports. Producing them ad hoc every audit costs the team weeks, and a report that already exists is itself evidence that the control is monitored.

Data analytics in audit

Auditors increasingly test 100% of transactions rather than samples, using tools like IDEA, ACL, and Alteryx. This means:

They will ask for full-population data extracts
They will find every anomaly, not a sample of them
Your data quality is directly visible
Benford’s Law analysis on transaction amounts (the leading digit of natural financial data follows a predictable distribution; fabricated numbers usually don’t)
Duplicate detection across the full population
Timing analysis — postings at 2am, on weekends, on the last day of the period

Being able to produce clean, complete, documented data extracts quickly is now a core ERP capability, not an occasional favour to the audit team.

Retention

Financial records          7–10 years (jurisdiction-dependent)
Tax records                7–10 years, sometimes longer
Personnel records          Varies; often 7 years post-employment
Pharma batch records       Product shelf life + years; can be 30+
Product liability          Product life + years
Audit logs                 Match the underlying records
Personal data              ← the opposite pressure: DELETE when no longer needed

The tension is real. Financial law says keep it; privacy law says delete it. The resolution: retain what has a legal basis for retention, and anonymise or pseudonymise personal data that has passed its purpose while keeping the financial record intact. A ten-year-old invoice needs the amount and the account; it may not need the customer contact’s mobile number.

Legal hold overrides everything: when litigation is anticipated, deletion must stop, including automated purges. Your archiving job needs a legal-hold flag that it respects.

The traceability requirement in regulated industries

Beyond financial audit:

Requirement	Trail needed
21 CFR Part 11	Every record change audit-trailed, e-signatures bound to records, no deletion by anyone
Food traceability	Batch genealogy, one step forward, one step back, within hours
Pharma serialisation	Unit-level movement history
Aerospace	Part traceability to raw material lot and heat number
Automotive (IATF)	Traceability for recall containment

See Serial, Lot and Traceability.

Developer checklist

That last one is the common gap. A custom mass-update utility that changes 4,000 prices without writing change log entries destroys the audit trail for those records, silently.