Designing an Audit Trail That Holds Up to an Auditor or Attorney
Short answer
An audit trail holds up when it does three things. It records who did what, when, from where and how, with before and after values and approvals. It can't be changed, or any tampering is detectable. And the people who can change financial records can't change the log. Most logs built into applications fail that third test, because one administrator can edit both the records and the log.

Most payment, commission and record-keeping systems have something they call an audit log. Usually it's a table the application writes to, and anyone with enough database access can read it, edit it or truncate it. That's fine for answering "who changed this invoice last Tuesday." It's not fine when an outside auditor tests your controls or an opposing attorney argues your records were altered.
The gap between those two cases comes down to design choices you make early. This piece covers what an auditor or forensic accountant will actually test, how to build it, and how long Louisiana and federal rules say you have to keep it.
What fields does an audit trail need to survive a dispute?
The baseline comes from SOX practice. An audit trail has to capture the "Who", "What", "When", "Where" and "How" of each regulated event. Each change to financial data has to be attributed to a specific named user, not to a shared service account.
In a dispute, those five words are only a starting point. Here is what each one has to mean in your schema:
| Question | Field(s) | Common failure |
|---|---|---|
| Who | Authenticated user ID, auth method, "on behalf of" user for support impersonation | Every write logged as app_user or admin |
| What | Action name, entity type and ID, before state, after state | Only the new value is stored, so nobody can show what it was |
| When | Server timestamp in UTC from a synced clock | Client-supplied timestamps, mixed time zones |
| Where | Source IP, device or service name, environment | Missing for batch jobs and scheduled scripts |
| How | Request or job ID, code version, the channel (UI, API, import, direct SQL) | Direct database edits leave no trace at all |
| Why | Reason text, ticket reference, approver ID for gated changes | Manual adjustments with no approval recorded |
Two more points tend to surprise people. First, scope covers more than row edits. SOX-oriented guidance expects you to audit data changes (Insert, Update, Delete), structure changes (Create, Alter, Drop) and permission changes (Grant, Revoke). If someone can drop a column or grant themselves access and the log doesn't show it, the log has a hole.
Second, reads count too. Unauthorized viewing of financial data is itself a compliance event, so access to sensitive records like bank details or payout histories should be logged as well.
For commission systems in particular, the audit trail and the ledger support each other. If balances are stored as one mutable number, the log is the only evidence of how the number got there. An insert-only, double-entry ledger removes that dependency; the pattern is covered in designing a commission engine that survives refunds and chargebacks. With a ledger, the audit trail records decisions and the ledger records money.
What does "immutable" actually mean for an audit log?
A strict definition: an immutable trail cannot be altered, deleted, or suppressed, not by application code, not by database administrators, and not by attackers with elevated credentials. The weakest common setup is a database audit table with no write protection, where anyone holding DELETE privileges can quietly erase history.
In practice there are three levels:
- Write-protected table. The application role can insert but not update or delete. This stops bugs and casual misuse. It does not stop a superuser.
- Tamper-evident chain. Each row stores a hash of its contents plus the previous row's hash, so any edit or deletion breaks the chain. SHA-256 chaining satisfies SEC Rule 17a-4 and most SOX audit requirements, because tampering shows up when someone verifies the chain.
- Write-once (WORM) storage. Log batches are shipped to storage that refuses deletes until a retention date passes, in an account the application's administrators don't control. Some rules, such as SEC Rule 17a-4(f), explicitly require unalterable records.
Most frameworks will accept level 2. I recommend combining levels 2 and 3 anyway, because a hash chain only proves tampering if someone outside the database holds a copy of the chain's latest hash. Here is a minimal PostgreSQL version of levels 1 and 2:
CREATE TABLE audit_event (
seq bigserial PRIMARY KEY,
occurred_at timestamptz NOT NULL DEFAULT now(),
actor_id text NOT NULL,
actor_auth text NOT NULL, -- 'password+totp', 'service:payout-worker'
on_behalf_of text,
source_ip inet,
request_id uuid NOT NULL,
action text NOT NULL, -- 'commission.adjust'
entity_type text NOT NULL,
entity_id text NOT NULL,
before_state jsonb,
after_state jsonb,
reason text,
approved_by text,
prev_hash bytea NOT NULL,
row_hash bytea NOT NULL -- sha256(prev_hash || canonical row)
);
REVOKE ALL ON audit_event FROM PUBLIC;
GRANT INSERT, SELECT ON audit_event TO app_rw;
-- No UPDATE, DELETE or TRUNCATE for any application role.
There are two failure modes to design for. Chain computation has to be serialized, using a single writer, an advisory lock, or separate chains per partition. Otherwise two concurrent inserts can both claim the same previous hash. And the canonical form you hash (key order, number formatting, time zone) has to be fixed and written down. If it isn't, you won't be able to re-verify the chain in five years when someone asks.
Can a database administrator secretly change the log?
With the table above alone, yes. A superuser can disable the trigger, rewrite the rows and recompute every hash. This is why an audit log that can be modified by anyone, including system administrators, has limited value as compliance evidence.
The fix is separation, not cleverness. The people and credentials that run the financial system must not also run the log. In the same way, staff who can work with billing records should not be able to modify or delete audit logs. Practical guidance recommends a separate audit system with administration independent from the ERP, which also keeps heavy audit queries off your transaction database.
For a small or mid-size company, a setup that's affordable and defensible looks like this:
- Stream audit events (or change-data-capture output) every few minutes to write-once object storage in a separate cloud account or tenant.
- Give that account a different owner, often the finance lead or an outside IT provider, with its own MFA. The FTC Safeguards Rule already requires MFA using two of three factor types for covered financial institutions.
- Write the latest chain hash to that store on a schedule, so an auditor can recompute the chain and compare.
- Review privileged access every quarter. Under SOX 404, the IT general controls auditors cite most are logical access, change management and computer operations. Even if SOX doesn't apply to you, an attorney will ask about the same three things.
How long do you have to keep audit logs and records?
Retention depends on which rules apply to you, and several often apply at once:
| Rule | What it says | Applies to |
|---|---|---|
| SOX Section 802 | Audit logs retained a minimum of 7 years | Public companies |
| SOX log review practice | At least 366 days readily available | Public companies |
| SEC 17a-4 / FINRA 4511 | 6-year core retention with WORM or an audit trail | Broker-dealers |
| GLBA / FTC Safeguards | Dispose of customer information within 2 years of last use unless a business or legal need applies | Covered financial institutions |
| IRS (common guidance) | Keep tax and supporting records 7 years | Every business |
| Louisiana breach law | Keep a no-notice determination and its support for 5 years | Any business holding Louisiana residents' data |
Notice the tension between the GLBA disposal duty and the 7-year rules. Your design has to allow you to keep audit events while purging or redacting the customer data inside them. In practice, that means storing references or tokenized values in before_state and after_state instead of raw account numbers.
The Louisiana overlay
Louisiana's Database Security Breach Notification Law (La. R.S. 51:3071 to 3077) covers any business that owns or licenses computerized personal information of Louisiana residents, with no minimum size. Headcount and revenue don't matter: a small firm running custom commission or payroll software has the same duties as a large one. Residents must be notified no later than 60 days from discovery, and the Attorney General within 10 days of sending those notices.
This is where your access logs pay off. Without read logging, you can't show which records an intruder actually touched. That means you can't scope the notice, and you can't defend a "no reasonable likelihood of harm" decision.
Louisiana-chartered banks and lenders, including those in Ouachita and Morehouse parishes, have another layer. The Office of Financial Institutions keeps a minimum schedule but requires each institution to build its own comprehensive retention program. State law also treats accurate electronic reproductions as if they were the original record, which is one more reason to be able to prove your reproductions haven't been altered.
What will an attorney or forensic accountant ask to see?
A SOC 2 auditor samples your controls. A litigator wants to know whether your records can be trusted at all. In a commission or payment dispute, expect requests along these lines:
- The full audit history for the disputed accounts and period, exported in a documented format.
- Proof the history is complete: the chain verification, the external hash anchors, and an explanation of any gaps.
- Who held edit rights and admin rights during that period, and when those rights changed.
- Every manual adjustment, along with its reason and approver.
- Change-management records for the code that calculated the numbers, because a disputed payout often comes down to a rule change rather than a data edit.
- Your written retention policy, and evidence you followed it.
That last pair is where custom systems tend to struggle. If you can't tie a calculation to the code version that produced it, the opposing expert will argue the number is unreliable. In my view, uptime on a payout system is the easy thing to measure. The more demanding standard is being able to explain any single payout, years later, from the records alone.
Is your off-the-shelf software's built-in log enough?
For everyday bookkeeping, usually yes. For litigation or a regulator, check three things. Can an administrator in the product delete or edit log entries? Does the log capture before values, or only the fact that something changed? Can you export it with integrity evidence before the vendor's own retention window expires? If any answer is wrong, add an external, write-once copy. You don't need to replace the software.
Where to start
- Make a list of every place financial data can change: the UI, API, imports, scheduled jobs and direct SQL. Any path that isn't logged is your biggest gap.
- Compare one real audit record against the field table above.
- Find out who can delete audit entries today. If it's the same person who can edit payouts, fix that first.
- Write down which retention rules apply to you, including the Louisiana breach rules, and set storage locks to match.
- Run a mock discovery request: export one account's full history and verify it end to end.
If you want an independent review of your current system against these tests, a fractional CTO engagement is one way to structure it.
If you'd like a second opinion on your audit trail, email hello@josephleporati.com.
Common questions
Does Louisiana have a consumer privacy law like California's?
Not a comprehensive one. As of 2025, Louisiana's main data protection law deals with breach notification and reasonable security procedures, not broad consumer rights like access or deletion requests. For audit trail design, the practical effect is that Louisiana businesses mostly need to prove what happened during a breach and keep their notification decisions documented. Federal rules such as GLBA may add requirements depending on your industry.
Should an audit trail log record views, or only changes?
For sensitive financial data, log views too. Unauthorized viewing is itself a compliance event, and after a breach, read logs are how you show which records an intruder actually accessed. Without them you may have to assume every record was exposed. Log reads of high-risk fields such as bank details and payout histories, and sample or summarize lower-risk reads to keep storage costs reasonable.
Can we add a proper audit trail to a legacy system without rewriting it?
Usually, yes. Database triggers or change-data-capture can record before and after values for every row change, even ones the application never sees. Stream those events to write-once storage in a separate account and add hash chaining. The weak point is attribution: if the legacy app connects with one shared database login, you may need small application changes to pass the real user ID through.
What makes an audit trail good enough for bookkeeping but not for litigation?
Bookkeeping needs to answer what changed. Litigation needs to prove the record is complete and unaltered. An audit table that administrators can edit, that stores no before values, or that has no external integrity check works for daily reconciliation. It gives opposing counsel an easy argument, though. The difference is mostly immutability, separation of admin roles, and documented retention, not more fields.
Sources
- SOX Compliance (Imperva) imperva.com
- WHITE PAPER: Implementing Sarbanes-Oxley Audit Requirements imperva.com
- Financial Compliance: Audit Trail Guide (SOX, SEC, FINRA, MiFID II) velt.dev
- Billing Audit Trails: Immutable Logs, Compliance, Forensics getlago.com
- Immutable Audit Trails: A Complete Guide hubifi.com
- Audit Trail Requirements: Building Compliance-Ready ERP Systems ecosire.com
- Records Management for Financial Services: SEC/FINRA Retention, SOC 2 & PCI reynoldsbusinesssystems.com
- SOX Compliance: Identity & Access Controls Guide oleria.com
- SOX Compliance Checklist 2026 bitsight.com
- Business Records Retention Guide: By Industry (2026) grmdocumentmanagement.com
- Louisiana Laws - Protection of personal information; disclosure upon breach legis.la.gov
- Louisiana Data Privacy Laws: Comprehensive Guide (2026) recordinglaw.com
- Louisiana Office of Financial Institutions Depository Institution Records Retention Policy ofi.la.gov
- Louisiana Revised Statutes §6:127 - Retention of records of financial institutions; microfilm law.justia.com