An audit log that a lawyer could read

A customer disputed a charge, claiming the total had been changed after they placed the order. The audit log had a row for it: order.updated, with a timestamp and an order id. It could not say what changed, who changed it, or why — which meant the answer to a straightforward question was a database restore and an afternoon.

The symptom

mysql> SELECT * FROM audit_log WHERE subject_id = 8814G
         id: 412008
     action: order.updated
 subject_id: 8814
    user_id: NULL
 created_at: 2022-01-18 14:22:31

-- user_id is NULL because the change came from a queue
-- worker, which has no authenticated user.

-- and there are 41 of these for this order.

Forty-one rows saying that something changed, with no indication of what. The information needed to answer the dispute existed in the database at the time and was not recorded, which is the specific failure — the log was written to satisfy a requirement rather than to answer a question.

Why it happens

An audit log built on ORM model events is one file and covers every write automatically, which is exactly why it is the design people choose. It also sees that a row changed and has no idea who changed it or why, so it invents an actor from whatever ambient context happens to exist.

The fix

What a record has to contain

AuditLog::record([
    'subject_type' => Order::class,
    'subject_id'   => $order->id,
    'action'       => 'order.total_adjusted',

    'actor_type'   => 'user',        // user | system | api_client
    'actor_id'     => $actor?->id,
    'actor_label'  => $actor?->email ?? 'reconciliation job',

    'before'       => ['total_cents' => 4900],
    'after'        => ['total_cents' => 4400],
    'reason'       => $request->input('reason'),

    'ip'           => $request->ip(),
    'request_id'   => $request->header('X-Request-Id'),
    'occurred_at'  => now(),
]);

The actor label is denormalised deliberately: a user id resolves to a row that may be deleted or renamed, and an audit record from 2019 should still say who it was. That is a copy of data that will diverge from its source, which is correct here and is the opposite of the usual rule.

Recording only the changed fields rather than the whole model keeps the table proportional to the changes rather than to the data, which matters on a wide table. The reason field is what turns a log into something a person can read, and requiring it for manual adjustments is a product decision rather than a technical one.

Why model events are not enough

// what an observer sees, and what it misses
Order::updated(function (Order $order) {
    AuditLog::record([
        'actor_id' => auth()->id(),      // NULL in a worker
        'action'   => 'order.updated',   // no intent
        'before'   => $order->getOriginal(),
        'after'    => $order->getChanges(),
    ]);
});

// and the writes it does not see at all:
DB::table('orders')->where(...)->update([...]);   // no events
Order::withoutEvents(fn () => $order->save());
$order->updateQuietly([...]);

The bulk update bypassing events is the gap that matters most, because it is what a data fix uses — and a data fix is exactly the change somebody will later be asked about. An observer-based log is silently incomplete, which is worse than no log, because it will be relied upon.

The arrangement that worked keeps the observer as a safety net recording order.changed_without_intent, and treats its presence in the log as a signal that somewhere is writing without recording properly. It went from eleven a day to two over a quarter.

Making it append-only

GRANT INSERT, SELECT ON shop.audit_log TO 'app'@'%';
-- no UPDATE, no DELETE

-- retention needs a separate identity
GRANT DELETE ON shop.audit_log TO 'audit_pruner'@'localhost';

-- and the belt-and-braces version, for anything with a
-- compliance requirement:
CREATE TRIGGER audit_log_immutable BEFORE UPDATE ON audit_log
FOR EACH ROW SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT = 'audit_log is append-only';

The grant is the practical control and the trigger is for the case where a compromised application credential is in the threat model. Both are easy at table creation and awkward to retrofit, because the retrofit has to establish that nothing currently updates the table — which on a two-year-old table is not obvious.

Reading it back

SELECT occurred_at, action, actor_label, reason,
       JSON_UNQUOTE(before->'$.total_cents') AS was,
       JSON_UNQUOTE(after->'$.total_cents')  AS became
FROM audit_log
WHERE subject_type = 'App\Models\Order' AND subject_id = 8814
  AND JSON_CONTAINS_PATH(after, 'one', '$.total_cents')
ORDER BY occurred_at;

-- | 2022-01-18 14:22:31 | order.total_adjusted
-- | a.yildirim@example  | goodwill, ticket 4102
-- | 4900 | 4400 |

The query that answers the dispute is the acceptance test for the whole design, and writing it first is what determines which fields are needed. A generated column on the frequently-queried JSON paths is worth adding once the table is large, since a JSON_CONTAINS_PATH filter cannot use an index.

Verifying it worked

# a change made through every path, and the record produced
$ vendor/bin/phpunit --filter AuditCoverage
Tests: 9 passed
#   via the admin form         → recorded, with a reason
#   via the API                → recorded, api_client actor
#   via the reconciliation job → recorded, system actor
#   via a bulk update          → recorded by the safety net

$ mysql -Nse "UPDATE shop.audit_log SET action='x' WHERE id=1"
ERROR 1142 (42000): UPDATE command denied to user 'app'

$ mysql -Nse "SELECT COUNT(*) FROM audit_log
  WHERE action='order.changed_without_intent'
  AND occurred_at > NOW() - INTERVAL 7 DAY"
2

Exercising every write path in a test and asserting on the resulting record is what makes the coverage claim real, and it is the test that fails when somebody adds a fifth path. The safety-net count trending towards zero is the other signal and is worth putting on a dashboard.

What this costs

A write per write, and a table that outgrows the data it describes — this one passed the orders table in size within four months. Retention is the answer and it collides directly with erasure requests and with the reason the log exists, so the window has to be agreed with somebody who can say what the obligation actually is rather than assumed by engineering.

The larger cost is that explicit recording has to be added at every decision point rather than derived, which means it can be forgotten. The safety net catches the omission after the fact and cannot reconstruct the intent — a record saying that a total changed with no reason attached is better than nothing and is not what the dispute needed. That gap is inherent and the only mitigation is review.