An Audit Trail That Survives Until Someone Asks
An append-only audit table written after each successful mutation, with no foreign key so rows outlive deleted accounts.
TL;DR
A security review found the app couldn't answer who changed a record, since app logs rotate and binlogs lack actor context. The fix is a dedicated audit_logs table written after successful admin mutations, append-only, with indexes and best-effort inserts that never break requests. Residual risk: a failed insert leaves a silent gap; metrics and alerting remain future work.
An internal security review asked who changed a record, and the only hint on screen was an updated_at column. No actor, no action, no context. The first assumption was that this information already existed somewhere: the application logs, or the database binlog.
Neither source could answer. Application logs are fragmented across instances and rotate away. The binlog records row-level changes in raw form without naming who triggered them, what role they held, or which request carried the change. The conclusion was uncomfortable but clear: without a dedicated record, "who did this" is a guess. The fix was a purpose-built audit_logs table, written by every admin service right after a successful mutation.
A Schema Designed to Be Trusted Later
The migration creates the table with a BIGINT UNSIGNED AUTO_INCREMENT primary key and a nullable user_id, alongside email, bidang, an action column limited to create, update, delete, restore, or status_change, the resource type and id pair, a TEXT detail column, the requester IP, and a created_at timestamp. Indexes cover the timestamp, the user id, and the (resource_type, resource_id) pair, so forensic queries by time, actor, or target stay fast.
Two omissions are deliberate. There is no updated_at because audit rows are append-only: they are written once and never revised. There is no foreign key to the users table either, so a row survives the deletion of the account that performed the action. This is exactly when the question "who did this" tends to be asked. The email is copied into the row at write time to keep it readable without joins, and if a retention policy ever changes, the upgrade path is a single constraint with ON DELETE SET NULL.
Best-Effort by Design
A shared Logger package exposes one nil-safe Log method. Services call it only after the business mutation has succeeded. If the insert fails, the failure is logged as a warning and the request still completes: a logging outage must never take the application down. OWASP's Logging Cheat Sheet lists audit trails covering data addition, modification and deletion among the security use cases for application logging [3], and warns in the same breath that it is important not to log too much, or too little [3]. Writing only successful admin mutations, with a short summary in the detail column, sits deliberately on the narrow side of that balance.
Access follows the existing role model: an audit:read permission granted to admin and superadmin roles via migration, not application code. The admin list endpoint is cross-category, filtered by action, resource type, or user, and paginated with the standard envelope. The (resource_type, resource_id) index is what keeps "everything that touched this entity" cheap as the table grows.
Data access uses ExecContext for the inserts and QueryContext for the list view [5], so a cancelled request does not leave a half-written audit row behind a dead connection.
The behavior is pinned by tests rather than trust. An integration test writes a mutation and asserts the audit row exists with the right action and resource; another kills the insert path and asserts the mutation still succeeds while the warning fires. A list-endpoint test checks that a plain admin can read the trail and the filters narrow it correctly. Repeating those three checks on any new admin service keeps coverage from rotting as the codebase grows.
The Residual Risk, Stated Plainly
Best-effort has a cost, and it should be named. There is a window in which a mutation succeeds but its trail fails to write. Today that failure appears only as a warning in the application log, which means a silent audit gap is technically possible. The honest next step is a metric on insert failures and an alert when the counter rises, turning a quiet gap into a visible signal. That work is not done yet, and the design does not pretend otherwise.