Most teams don't think about their audit trail until an examiner is sitting in a conference room asking for it. That's the worst possible time to find out the log you've been writing for three years can't answer the question in front of you. A useful audit trail is boring, specific, and searchable on the first try. A decorative one is a compliance checkbox that quietly fails the moment it matters.
What Regulators and Auditors Actually Ask For
Exam requests are narrower than people expect. Regardless of whether it's a state DOI market conduct exam, a SOC 2 engagement, an HHS OCR inquiry, or a lender's internal audit team, the questions tend to cluster:
- Show me every change to this specific record between these two dates.
- Who approved this transaction, and what did they see at the moment of approval?
- Prove that this user did not have access to this data before their role changed.
- Reconstruct the state of this policy, claim, or account as of a specific date.
Notice what's not on that list: exports of your entire activity log, dashboards, pretty charts. Examiners want to answer one question at a time, and they want the answer to be authoritative. If your log can't produce a single-record history in the span of a phone call, it isn't doing its job, even if it's writing gigabytes of data every day.
The Four Fields Every Log Needs
Strip away the vendor marketing and an audit log row needs four things. If any one is missing, the log is incomplete for exam purposes.
- Who. Not "system" or "admin" but the authenticated user identity, including the session or API key if the action came through an integration. If you use service accounts, the log has to trace back to the human who triggered the service call or deployed the job.
- What. The specific record and the specific field. "Updated policy 10482" is not enough. You want "policy 10482, field
effective_date." Table plus primary key plus column name. - When. A timestamp with timezone, recorded server-side, not from the client. UTC in the database, rendered to local when displayed. Millisecond precision if you have multiple events per second on the same record.
- Before and after values. The single field that separates real audit logs from fake ones. "Status changed" means nothing. "Status changed from
pending_reviewtoapproved" is evidence. Store both sides of every mutation, even if the before-value is null.
A row with all four fields can stand on its own in an exam response. A row missing any of them will force you into a forensic exercise reconstructing context from surrounding systems, usually under time pressure.
Retention Horizons
Retention isn't one number. Different record types have different clocks, and the clock usually starts at an event other than the write date.
- Insurance policy data: typically the greater of statutory retention (often five to seven years) and the life of the policy plus a tail. For claims, retention often extends to the later of claim closure plus a number of years or the statute of limitations for bad faith.
- PHI and HIPAA-adjacent logs: six years from the later of creation or last effective date under the Security Rule, longer in some states.
- Financial transaction logs: typically seven years, but lender-specific and product-specific rules can push this further.
- Access and authentication events: often shorter retention, but you want at least one year of hot access logs and longer cold storage for any system touching regulated data.
The practical implication: your audit table can't just be append-only forever on the same disk. Plan the tiering from day one: hot storage for the last 12 to 24 months, cold storage for the rest, and a clear script for pulling cold data back into a queryable form without a three-day restore job.
Surfacing Audit Data Without a BI Stack
You don't need Looker, Tableau, or a data warehouse to make audit data useful. For most operational teams, three surfaces cover 90% of the examiner questions:
- A record history drawer. On every entity detail page (policy, claim, member, account) a panel that lists every change to that record in reverse chronological order, with who/what/when/before/after visible without clicking. This alone answers most single-record questions in the exam.
- A user activity view. Filterable by user and date range, showing every action that user took. Useful for access reviews, termination audits, and "did this employee touch this account" questions.
- A saved-query export. Half a dozen pre-built queries (changes to field X in date range Y, all approvals by user Z, all deletions in the last 30 days) that produce a CSV or PDF the examiner can take with them. Saving the query definition matters as much as the output, because it proves the export is reproducible.
These are server-rendered pages or simple API endpoints behind role-gated routes. No BI license, no ETL, no separate analytics database. The same Postgres or MySQL table you're writing to can serve the read path if you index (entity_type, entity_id, changed_at) and (actor_id, changed_at).
The Test
The simplest way to know if your audit trail works: pick a record at random and try to reconstruct its full history in under five minutes, using only the tools you'd have in front of an examiner. If you can't, the problem isn't the exam. It's the log. Fix the log before someone else finds the gap for you.
If you're looking at a half-built logging layer and wondering whether it would survive an exam, we can help you pressure-test it before a regulator does.