Audit logs are universally implemented and unevenly useful. The append-only property is necessary but does not survive a serious audit on its own. Three additional properties turn a log from a compliance bolt-on into a queryable record that the audit team can use without a binder-pull exercise.
Why append-only is not enough
Append-only means the log cannot be modified after the fact. That is the minimum bar for a compliance log. It is not the bar for an audit log that is useful to the business. The compliance review that asks "was this write authorised?" needs the log to answer that question in seconds, not days. An append-only log without query support is a haystack with the right needle inside it.
Property 1: per-action records with structured fields
The log row for each action carries structured fields, not free text. At minimum: timestamp, actor identity, action type, target object, input parameters, output result, system version. Structured fields are queryable. Free text is grep-able and not the same thing.
The failure mode of a free-text log is that the audit question ("show me all writes to the item master in the trailing quarter where the actor was a non-human principal") requires writing a regex against the log and hoping the regex catches every variation of how the log was written. With structured fields, the same question is a SQL query against three columns and is reproducible.
What the schema should include
- action_id: unique identifier for the action, allows joining to downstream effects.
- actor_id and actor_type: who or what initiated the action. Human, service, agent, scheduled job.
- action_kind: enumerated type. Write, read, override, approval, refresh, etc.
- target_object and target_id: what was acted on.
- inputs and outputs: structured JSON. The values the action used and the values it produced.
- provenance_ref: pointer to the source artifact or upstream action that produced the inputs.
- system_version and model_version: for reproducibility.
- reason: structured override reason or approval rationale where present.
Property 2: time-window queries with reasonable performance
Auditors do not ask "show me everything." They ask "show me everything in this time window that touched this surface." The log has to support time-window queries that return in seconds, not minutes. At the scale a mid-market plant generates (roughly 10-50k log rows per day), this is achievable with reasonable indexing and either Postgres or a dedicated event store. At larger scales, a column store or a search engine is usually the right shape.
The structural requirement is that time-window queries are a first-class operation. Logs that require full scans for time-window queries fail under audit volume; the auditor asks for the trailing quarter and the query times out, and the audit team escalates to engineering to extract the data manually.
The performance bar
A time-window query that returns up to 10,000 rows should complete in under 10 seconds. A time-window query for aggregate counts (how many overrides in the trailing 30 days?) should complete in under 2 seconds. These are not ambitious targets; they are the minimum that lets the audit conversation happen interactively rather than by email round-trip.
Property 3: join across actions to follow a chain
The most useful audit questions are not about single actions; they are about chains. "Show me the chain of actions that produced this PO write." The chain typically starts with a vendor email landing in the inbox, runs through a parser extract, a proposal, an operator approval, and ends with the PO write itself. Following the chain requires that each action records its predecessor (via provenance_ref) and that joining is straightforward.
The chain query is what turns a compliance audit from a binder-pull into a query. The auditor selects a write at random, walks the chain backward to the source artifact, and verifies the chain is intact and the reasoning is defensible. Without the chain join, the same verification requires interviews with the operators involved, which is slow and impressionistic.
The three queryability questions to ask any vendor
Question 1: show me your audit schema
A vendor with a real audit log will hand you a schema. A vendor with a compliance bolt-on will hand you a paragraph describing what the log captures. The difference is immediate. The schema tells you what queries are possible. The paragraph tells you the vendor has not thought about queries.
Question 2: run me a time-window query in front of me
Ask the vendor to show you all overrides in the past 24 hours for a specific workflow. They should be able to do this live in their demo environment. If the answer is "we can pull that for you offline," the log is not actually queryable in the way you need.
Question 3: trace a write back to its source artifact
Pick a recent PO write from the demo environment. Ask the vendor to walk the chain back to the originating vendor email. The walk should take three clicks and show the chain inline. If the walk requires reconstructing the chain from multiple disconnected logs, the chain join is not first-class.
Retention and the question of who can read the log
Two adjacent concerns. Retention should be set against the regulatory regime that applies to the data. For SOX, seven years is the typical minimum. For GxP environments, longer. For most operational data, two years is sufficient. The retention model should be per-action-type, not global, because different actions have different regulatory windows.
Read access should be role-based and itself logged. An auditor reading the log is itself an action that should be recorded. This is sometimes called audit-log-of-the-audit-log and it is the property that prevents a malicious actor with audit access from quietly exploring the log without leaving a trace.
What good looks like, in one sentence
Good looks like: any operational write the system performs is one query away from its full provenance chain, its authorising approval, and the actor identities of every step in between, with all of the above queryable by the audit team without requiring engineering to extract the data on their behalf. Most logs do not look like this. The ones that do are the ones that survive an external compliance audit without a fire drill.