Audit Logs: The Business System Feature Nobody Plans For

Audit logs are one of those features every business system eventually needs and almost nobody builds properly from day one. The pattern is predictable: a system ships, it works, and then six months later someone asks "who changed that record?" or "when was this order updated?" or "can you show us who accessed this customer's data?" — and the honest answer is "we don't know, we never tracked that."

That's not a technical failure. It's a planning failure. Audit logs feel abstract until the moment they become urgent, and by then retrofitting them is painful.

This piece covers what actually goes into a compliance-grade audit trail, what your business system must log, and how to structure it so it holds up when a regulator, a lawyer, an auditor, or a frustrated operations manager asks for evidence.

What an Audit Log Actually Is

Not all logging is an audit trail. Application error logs, performance metrics, and debug output answer "what did the system do?" Audit logs answer a different question: "who did what to which record, and when?"

That distinction matters because the audience is different. Error logs are for developers. Audit logs are for operators, compliance officers, and legal counsel. They need to be:

• Tamper-evident: once written, not editable by the application
• Attribution-clear: every entry tied to a named actor
• Structured: machine-readable so you can query and report on them
• Retention-bound: kept for a defined period, often mandated by regulation

If your logging setup is "we write to a table that the app can UPDATE and DELETE freely," you don't have an audit log — you have a softer version of the original record.

What to Log: The Four Core Event Categories

The most common mistake is logging everything or logging nothing. Both are nearly useless. What you want is structured coverage of meaningful state changes across four categories.

Record mutations: Any CREATE, UPDATE, or DELETE on records that matter — customer data, orders, invoices, contracts, user profiles, permissions. For updates, log both the before-state and after-state of changed fields. "The order was updated" tells you almost nothing; "the shipping address was changed from 12 Oak St to 44 Birch Ave by user#82" tells you everything.

Authentication events: Logins (successful and failed), password changes, session invalidations, MFA events, and API key usage. These are the first line of evidence when investigating unauthorized access.

Permission and role changes: Who granted what access to whom, and when. This category is consistently under-logged in custom systems. The moment a privilege escalation occurs, you want an immutable record.

Sensitive data access: In systems handling financial data, health information, or personal data under GDPR or CCPA, viewing a record can itself be a loggable event — not every view, but records that require explicit justified access.

The Anatomy of a Good Audit Log Entry

Every audit log entry needs at minimum:

• event_id: UUID, primary key, never reused
• event_type: namespaced string, e.g. order.updated or user.login_failed
• actor_id: the user, service account, or background job that caused the event
• actor_ip: IP address if the action came through a user request
• target_type: model class or resource name (e.g. Order, Customer)
• target_id: ID of the affected record
• payload: JSON containing the relevant diff or event data
• occurred_at: UTC timestamp, never local time

The payload field is where most implementations cut corners. Either they log nothing meaningful ("record updated") or they log entire records including fields that contain sensitive data. The right approach: maintain an explicit allowlist per model of which fields belong in the audit log. That makes entries useful without turning the audit table into a secondary data breach vector.

Compliance Requirements That Depend on Audit Logs

Audit trails are often legally required, not just operationally convenient.

GDPR / UK GDPR: Article 30 requires records of processing activities. Data subject requests — access, deletion, rectification — need to be logged. Breach investigations always start with "show us the access log for this record."

PCI DSS: Requirement 10 mandates audit logs for all access to cardholder data, all administrative actions, and all authentication events. Logs must be retained for at least one year, with three months immediately available for query.

SOC 2 Type II: Controls CC6.1 through CC6.8 require documented evidence of logical access controls. Auditors don't accept promises — they want log data they can inspect.

HIPAA: Requires audit controls for all access to electronic protected health information. Viewing a record can count.

Even if none of these apply today, a custom system that can't produce an audit trail becomes a liability the moment a contract dispute, an employment issue, or a data subject request involves data stored in that system. "We don't log that" is a difficult answer to give in front of a lawyer.

Designing the Schema

Two common approaches: a single polymorphic audit table, or separate per-model audit tables.

A single polymorphic table with target_type and target_id columns covers every model in one place. It's easier to build, queries uniformly, and is straightforward to expose in an admin UI. The tradeoff is that it becomes a very large table — indexing discipline is critical. Index on (target_type, target_id), actor_id, and occurred_at at minimum. Partition or archive by month if volume is high.

Per-model audit tables (e.g. order_audit_logs, invoice_audit_logs) give you cleaner partitioning and make compliance exports more surgical, but require significantly more setup and make cross-entity queries awkward.

For most SMB systems, the single polymorphic table is the right default. Per-model tables only make sense once query performance becomes a real, measured problem.

One critical design decision: write audit logs outside the main transaction when possible. If the audit write sits inside the same database transaction as the record mutation, a rollback erases both — including the record of an attempt. Writing audit logs after commit, or to a separate write path, means failed transactions still leave a trace.

Where Audit Logging Goes Wrong

Only logging at the application layer: If a developer runs a direct SQL UPDATE on production, an application-layer audit log misses it entirely. The only real defenses are database-level triggers, or a strict policy enforced via database user permissions that all writes go through the application API.

No identity for background jobs: Scheduled tasks and import scripts often get attributed to "system" with no further context. When an automated job modifies 800 records overnight and something breaks, "system did it" is not a useful audit entry. Each background job should have a named service identity in your log.

Making the audit table mutable: The database credential your application uses should not have DELETE or UPDATE privileges on the audit log tables. That single permission change converts your table from mutable to append-only, which is the minimum standard for tamper evidence.

Over-logging high-frequency read events: Logging every record view on a system that handles thousands of requests per minute creates serious storage and performance problems while drowning useful signal in noise. Reserve read-logging for records explicitly flagged as high-sensitivity.

Retrofitting Audit Logging into an Existing System

Adding audit logging to a system that doesn't have it requires resisting the temptation to bolt on a generic "log everything changed" solution. The noise produced is nearly as useless as nothing.

A practical sequence:

1. Map your compliance exposure first. What regulations or contractual obligations apply? Work backwards from what an audit or dispute would actually demand to see.
2. Start with authentication and permission events. Easiest to add, most frequently demanded by auditors, and highest signal-to-noise.
3. Add record-mutation logging for your two or three most sensitive models. In most systems a small number of tables — customers, payments, contracts — account for the majority of compliance risk.
4. Assign identities to your background jobs. Even partial attribution is better than none, and it's usually a one-day change.
5. Build a query UI before you declare the work done. An audit log that nobody can retrieve might as well not exist. A simple admin view filtered by actor, date range, and target type covers most requests you'll ever get.

The last step is consistently the one that gets cut. Don't let it. The audit log becomes real the first time someone uses it.

At Dev Paragon, audit logging is always part of the initial spec on business systems we build — not a Phase 2 item. The systems we've seen struggle hardest during audits or disputes are almost always the ones where logging was treated as optional infrastructure. Getting the schema and access controls right from the start takes a few days; retrofitting it under pressure takes weeks and often produces incomplete coverage anyway.

If you're scoping a custom business system and haven't explicitly mapped out what it needs to log — and who can query it — that's a conversation worth having before any code is written.