Building Audit Logs: Who Did What, When, and to What

Explain what distinguishes an audit log from an application log, design an audit entry schema that captures actor, action, timestamp, resource, and before/after state, and implement append-only, tamper-resistant audit logging in PHP.

Every application log tells you what the code did. An audit log tells you what a person did — and it exists for a completely different reason: not to help you debug a stack trace, but to answer a compliance officer, an auditor, or a suspicious customer six months from now when they ask 'who changed this record, and what did it look like before?' Getting audit logging right means designing a schema that survives that question, protecting the sensitive data the log itself accumulates, and making sure the log can't quietly be edited or deleted by the very people it's meant to hold accountable. This lesson builds that from the ground up in PHP.

Audit Log vs Application Log: Different Jobs, Different Rules

An application log (written with a tool like Monolog) is a debugging aid: it captures errors, warnings, timing, and internal state so a developer can figure out why something broke. It's typically verbose, rotated aggressively, shipped to a log aggregator with a short retention window, and nobody minds if a few lines get dropped during a burst of traffic. Losing a day of debug logs is an inconvenience, not an incident.

An audit log is a compliance and security record: it exists to answer 'who did this, and when' with enough authority to satisfy a regulator, support a legal dispute, or reconstruct an incident. It is deliberately narrow — it doesn't record every function call, only meaningful business actions (a user was deleted, a permission was granted, an order was refunded) — but every entry it does contain must be accurate, complete, and durable. Where an application log tolerates loss, an audit log generally cannot: many compliance regimes (SOC 2, HIPAA, PCI-DSS, GDPR's accountability principle) explicitly require that certain classes of action be auditable for a defined retention period, sometimes years.

This difference drives every design decision that follows. You don't put audit entries in the same rotating file as your error log, you don't let a queue silently drop them under backpressure, and you don't let the same database credentials that write application data also have UPDATE or DELETE permission on the audit table.

• Application log: debugging aid, verbose, short retention, occasional loss tolerated. • Audit log: compliance/security record, narrow scope, long retention, loss is not acceptable. • Compliance regimes (SOC 2, HIPAA, PCI-DSS, GDPR) often mandate audit trails for specific actions. • Audit writes should not share write permissions with the systems whose actions they record.

The Essential Fields: Who, What, When, and to What

Every audit entry needs four pieces of information at minimum, and an entry missing any one of them is close to useless later. Who is the actor — the authenticated user ID (or service/API-client ID for machine-initiated actions) that performed the action, never just a username string that could change or be reused. What is the action — a specific, stable identifier like order.refunded or user.role_changed, not a free-text description, so entries can be filtered and reported on reliably. When is a precise timestamp, stored in UTC with at least second (ideally millisecond) precision, because reconstructing an incident timeline across systems depends on comparable timestamps. Resource identifies exactly what was affected — the entity type and its ID (order, id=48213), so you can pull 'every audit entry for this specific order' regardless of who touched it.

A common mistake is conflating 'who' with the request's session data and stopping there. In systems with impersonation (a support agent acting as a customer), delegated API access, or service accounts acting on a user's behalf, you need to capture both the authenticated actor and, separately, the subject the action was performed on or as. An audit entry that only says 'user 48213 was refunded' without recording which support agent triggered it is a compliance gap the first time someone asks 'who authorized this refund?'

It's worth storing these fields in dedicated, indexed columns rather than burying them inside a single JSON blob — you will eventually need to query 'every action by user X' or 'every action on resource Y' efficiently, and a queryable schema is what makes an audit log actually useful during an investigation rather than just a write-only archive.

• Who: actor ID (user or service), not just a display name. • What: a stable action identifier (e.g. order.refunded), not free text. • When: a precise UTC timestamp. • Resource: entity type + entity ID that was affected. • Impersonation/delegation: record both the authenticated actor and the subject acted upon/as.

Before/After State: Capturing What Actually Changed

Knowing that a record 'was edited' is rarely enough — the useful question is almost always 'what did it look like before, and what does it look like now?' A well-designed audit entry for a mutation captures a snapshot of the relevant fields both before and after the change, not just the fact that an update happened. This turns the audit log into a genuine forensic tool: you can show a customer exactly which of their fields an employee changed, or prove to an auditor that a price override was applied and then reversed.

In practice this means storing before and after as structured data (commonly JSON columns) scoped to the fields that actually changed, or at minimum the fields relevant to that action type — capturing the entire row on every update is sometimes appropriate for small tables, but for large or frequently-updated records it's usually better to diff and store only the changed fields plus enough surrounding context to interpret them. For creation events, before is naturally null and after holds the initial state; for deletions, after is null and before holds the last known state.

Be disciplined about *when* you snapshot: capture 'before' state prior to executing the mutation (not after, when it's too late to know the original value) and capture 'after' state from the same transaction that performed the write, so the two snapshots are guaranteed consistent with what the database actually persisted.

• Store structured before/after snapshots, not just 'X was updated'. • Scope snapshots to changed/relevant fields, or the full row for small tables. • Creation: before = null. Deletion: after = null. • Capture 'before' prior to the mutation and 'after' within the same transaction as the write.

Correlation IDs: Tying an Entry Back to Its Request

A single user action often produces several related audit entries — approving an order might log a status change, a payment capture, and a notification dispatch, each as a separate entry. Without something linking them together, reconstructing 'everything that happened as a result of this one click' means manually correlating timestamps and hoping nothing else happened in the same second. A correlation ID (sometimes called a request ID or trace ID) solves this: generate one UUID per incoming request (or background job execution) at the very top of the request lifecycle, thread it through every service call and audit write that request triggers, and store it as its own indexed column on every audit entry.

The same correlation ID should ideally appear in your application logs too, so an investigator can pivot from 'this audit entry looks suspicious' straight into the full request/error log trail that produced it — cross-referencing an audit record with the technical logs from the same request is often how a security incident actually gets diagnosed. Many frameworks generate this ID automatically per request and expose it via middleware; if yours doesn't, generating one in a single bootstrap point and storing it in a request-scoped container is a small, worthwhile investment.

For actions triggered asynchronously (a queued job, a scheduled task), propagate the originating correlation ID into the job payload rather than generating a fresh one — otherwise the audit trail breaks exactly at the point where async processing starts, which is often where the most interesting bugs and abuse patterns hide.

• One correlation ID per request/job, generated at the top of its lifecycle. • Thread it through every audit write (and application log line) that request causes. • Store it as an indexed column so related entries can be pulled together. • Propagate it into queued jobs rather than generating a new ID per job.

Privacy: The Audit Log Is Another Place PII Can Leak

It's easy to treat the audit log as exempt from the privacy rules that apply to your main data store, because 'it's just for security' — but a before/after snapshot of a user record can contain the exact same email addresses, phone numbers, physical addresses, and other personal data as the record it's shadowing. If you log full before/after payloads indiscriminately, you've effectively created a second copy of your PII with, often, weaker access controls and a longer retention period than the primary table — the opposite of what a privacy-conscious design wants.

The fix is to redact or omit specific sensitive fields even within audit entries: never store raw passwords, payment credentials, or authentication tokens in a before/after snapshot (log that the field changed, not its value); consider masking or hashing fields like national ID numbers; and for fields you do need to see changed (like an email address, for a 'someone changed this account's contact email' investigation), make sure the audit table itself has access controls and retention policies at least as strict as the source table's.

This tension — audit logs need enough detail to be useful, but not so much that they become a privacy liability — is resolved per field, not per table: decide field-by-field whether its value belongs in the snapshot, whether it should be redacted to a boolean ('changed: true') or a hash, or whether only certain roles should be able to read that portion of the audit trail.

• Before/after snapshots of user records can duplicate PII into the audit table. • Never store raw passwords, payment data, or tokens in audit snapshots — log the fact of a change, not the value. • Consider masking/hashing sensitive-but-necessary fields (e.g. national ID numbers). • Audit table access controls and retention should match or exceed the source table's.

Tamper Resistance: Append-Only, Write-Once, Hash-Chained

An audit log that a bad actor (or a compromised admin account) can quietly edit or delete is not a useful audit log — it's a liability that looks like evidence but proves nothing. The baseline defense is making the storage append-only in practice: the database role your application uses to write audit entries should have INSERT permission only, with UPDATE and DELETE revoked at the database level, so even a SQL injection or a compromised application credential cannot rewrite history. This is enforced at the database/infrastructure layer, not just in application code, because application-level checks can be bypassed by anyone with direct database access.

Beyond append-only permissions, some systems add cryptographic hash-chaining: each audit entry includes a hash of the previous entry's contents (plus its own data), forming a chain analogous to a lightweight blockchain. If any historical entry is altered or deleted, every subsequent hash in the chain fails to verify, making tampering detectable even by someone with direct database write access — they can edit a row, but they can't make the chain re-validate without recomputing every hash after it, which is either infeasible or leaves an unmistakable gap.

For most applications, append-only permissions plus shipping a copy of audit entries to a separate, write-once destination (an external log service, a WORM-configured storage bucket, or a separate database with different credentials the main application never touches) is sufficient. Hash-chaining is worth the added complexity specifically when the threat model includes a privileged insider or a fully compromised primary database, and regulatory requirements (some financial and healthcare contexts) sometimes mandate it explicitly.

• Revoke UPDATE/DELETE on the audit table for the application's database role — INSERT only. • Ship a copy to a separate write-once destination for defense in depth. • Hash-chaining: each entry hashes the previous entry, so tampering breaks the chain. • Choose hash-chaining when the threat model includes privileged insiders or full DB compromise.

A Minimal Audit Entry Shape

readonly properties (PHP 8.1+) reflect that an audit entry, once constructed, should never be mutated in memory either. before/after are nullable to represent creation (before = null) and deletion (after = null).

<?php
final class AuditEntry
{
    public function __construct(
        public readonly string $correlationId,
        public readonly int $actorId,
        public readonly string $action,      // e.g. "order.refunded"
        public readonly string $resourceType,
        public readonly string $resourceId,
        public readonly ?array $before,
        public readonly ?array $after,
        public readonly \DateTimeImmutable $occurredAt,
    ) {}
}

Code Examples

The logger redacts sensitive fields before they touch storage, writes actor/action/resource/before/after/timestamp/correlation as distinct queryable columns, and relies on database-level permissions (not application logic) to make the table append-only.

$stmt->execute([...]); // INSERT-only grant enforces append-only at the DB layer

<?php
declare(strict_types=1);

final class AuditLogger
{
    public function __construct(private \PDO $db) {}

    public function record(
        string $correlationId,
        int $actorId,
        string $action,
        string $resourceType,
        string $resourceId,
        ?array $before,
        ?array $after
    ): void {
        // Redact known-sensitive keys before they ever reach storage.
        $before = $this->redact($before);
        $after = $this->redact($after);

        $stmt = $this->db->prepare(
            'INSERT INTO audit_log
                (correlation_id, actor_id, action, resource_type, resource_id,
                 before_state, after_state, occurred_at)
             VALUES (:cid, :actor, :action, :rtype, :rid, :before, :after, :ts)'
        );

        $stmt->execute([
            ':cid'    => $correlationId,
            ':actor'  => $actorId,
            ':action' => $action,
            ':rtype'  => $resourceType,
            ':rid'    => $resourceId,
            ':before' => $before === null ? null : json_encode($before),
            ':after'  => $after === null ? null : json_encode($after),
            ':ts'     => (new \DateTimeImmutable())->f
…

Audit Trails in Regulated Industries

A healthcare records system subject to HIPAA must be able to show, for any patient record, exactly which staff member viewed or modified it and when — auditors will ask for this trail directly, and an incomplete or tamperable log during a breach investigation can turn a technical incident into a much larger regulatory and legal problem.

// Logging 'User 48213 updated' with no detail error_log("User {$id} updated");

$auditLogger->record($cid, $actorId, 'user.updated', 'user', $id, $before, $after);

A free-text application log line can't be queried, filtered by actor/resource, or diffed for before/after state — it satisfies neither compliance nor investigation needs the way a structured audit entry does.

GRANT ALL PRIVILEGES ON audit_log TO 'app_user'@'%';

GRANT INSERT ON audit_log TO 'app_user'@'%'; -- no UPDATE, no DELETE

If the application's own database credentials can UPDATE or DELETE audit rows, a compromised app server (or a bug) can quietly erase the evidence of its own misuse — append-only permissions must be enforced at the database layer, not trusted to application code.

$auditLogger->record(..., before: $fullUserRowIncludingPasswordHash, after: $fullUserRowIncludingPasswordHash);

$auditLogger->record(..., before: $redactedBefore, after: $redactedAfter); // password_hash redacted

Storing full record snapshots without redaction turns the audit table into an unprotected second copy of sensitive data, including credentials — audit logs need the same field-level privacy discipline as the primary data store.

What primarily distinguishes an audit log from a regular application/debug log? A. Audit logs are always written in JSON format B. Audit logs are a compliance/security record of who did what, not a debugging aid, and must not tolerate silent loss C. Audit logs only run in production, never in development D. There is no meaningful difference; they serve the same purpose

B. Audit logs are a compliance/security record of who did what, not a debugging aid, and must not tolerate silent loss — An application log helps developers debug and can tolerate some loss; an audit log is a durable compliance/security record answering 'who did what, when' and generally cannot be allowed to silently drop entries.

Why capture before/after state instead of just logging that a record was edited? A. It's required by PHP syntax B. It makes the log file smaller C. It lets you see exactly what changed, not just that something changed, which is essential for investigations and disputes D. Before/after state is only useful for numeric fields

C. It lets you see exactly what changed, not just that something changed, which is essential for investigations and disputes — A before/after snapshot turns 'the record was edited' into a precise, actionable record of exactly which values changed — the difference between a vague log line and real forensic evidence.

What is a correlation ID used for in audit logging? A. Encrypting the audit table B. Linking multiple related audit entries (and application log lines) back to the single request or job that caused them C. Replacing the actor ID field D. Compressing audit log storage

B. Linking multiple related audit entries (and application log lines) back to the single request or job that caused them — A correlation ID generated once per request/job and threaded through every resulting audit write (and log line) lets you reconstruct everything one action triggered, and cross-reference audit entries with technical logs.

Why should audit log storage be made append-only at the database level rather than relying only on application code? A. Append-only storage is faster to query B. Because application-level restrictions can be bypassed by direct database access or a compromised application credential, so the guarantee must be enforced by the database itself C. It is a PHP language requirement D. It reduces the size of before/after JSON payloads

B. Because application-level restrictions can be bypassed by direct database access or a compromised application credential, so the guarantee must be enforced by the database itself — If only application code prevents edits/deletes, anyone with direct database access (or an attacker who compromises the app's DB credentials) can still tamper with the log; revoking UPDATE/DELETE at the database role level closes that gap.

Extend the AuditLogger example to also record a session_id field (from $_SESSION or a session helper) alongside correlation_id, and add a redact() rule for a new sensitive key, 'ssn'. Then write the audit call for a hypothetical 'account.deleted' action where after is null.

<?php $auditLogger->record( correlationId: $requestId, actorId: $currentUser->id, action: 'account.deleted', resourceType: 'account', resourceId: (string) $deletedAccount->id, before: $accountSnapshot, // last known state, ssn redacted after: null );

For a deletion, before holds the last known state and after is null; the redact() function should handle a null $data argument gracefully, which the example already does.