11.01 The Audit Trail

The trail is append-only at the application level — the application doesn't expose UI for editing or deleting audit entries. Database-level access can modify them, but doing so is detectable (timestamps don't update in expected ways) and contradicts the audit trail's purpose.

What gets logged

Substantive list, not exhaustive:

• Risks: submission, every field update, status change, scoring methodology change, review action, comment, mitigation creation, close, deletion.
• Mitigations: creation, every field update, deletion.
• Controls: creation, every field update, deletion, mapping changes.
• Tests: creation, execution (test run, results, evidence attached), update, deletion.
• Audits: cycle initiation, status change, completion, comment.
• Documents: upload, update, approval, expiration, deletion.
• Frameworks: creation, update, control additions/removals.
• Users: creation, role change, team-membership change, permission grant/revoke, password change, MFA enroll/disable, deactivation.
• Configuration: settings updates, Extra activations/deactivations, framework installations.
• Authentication: login (success and failure depending on log-level configuration), logout, session-related events.

Not logged (typically):

• Read operations — viewing a risk doesn't generate an audit entry. The audit trail captures changes, not access.
• API list endpoints — GET /api/v2/risks doesn't audit each call.
• Routine system operations — cron job runs, scheduled tasks (these go to the debug log, not the audit trail).

For programs that need read-access auditing (who looked at this risk?), the audit trail isn't the source — web server access logs are, plus any application-level access logging you configure separately.

How to access the trail

Per-entity view

Every entity's detail view in the SimpleRisk UI shows its audit trail history. For risks: open the risk → the audit-trail panel (typically labeled History or Audit Trail) shows the chronological list of changes for that specific risk.

Operators reviewing a specific record use this view; it answers "what happened to this risk?"

Per-user view (admin)

The admin user-management view typically exposes the audit trail entries attributable to a specific user. Useful for "what has this user been doing?" investigations.

API

The audit trail is exposed via API endpoints (typically under /api/v2/audit_log or per-entity audit endpoints). Returns the entries as JSON; integrations can pull for external analysis (SIEM, custom dashboards, etc.).

Direct database query

For ad-hoc analysis or compliance reporting, direct SQL queries against audit_log:

-- All audit entries for a specific risk, chronological
SELECT timestamp, message FROM audit_log WHERE risk_id = 1234 ORDER BY timestamp ASC;

-- All entries by a specific user in the last 30 days
SELECT timestamp, risk_id, message FROM audit_log
WHERE user_id = 42
  AND timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY timestamp DESC;

-- Configuration changes (entries where risk_id = 0 typically indicate system-level events)
SELECT timestamp, user_id, message FROM audit_log
WHERE risk_id = 0
ORDER BY timestamp DESC LIMIT 100;

Retention and growth

The audit trail grows continuously. A program with active risk activity produces hundreds to thousands of entries per day. Without intervention, the table grows without bound.

Most installs leave the trail in place indefinitely — disk is cheap relative to the value of historical context for audits and investigations. For installs with strict storage constraints or regulatory requirements that mandate a specific retention period:

• Truncate old entries: DELETE FROM audit_log WHERE timestamp < DATE_SUB(NOW(), INTERVAL 7 YEAR); (or whatever your retention window). Coordinate with operations; deletion is irreversible.
• Archive before deletion: mysqldump --where "timestamp < ..." simplerisk audit_log > audit_log_archive.sql to a separate archive file, then delete from the live table.
• Partition the table: MySQL supports table partitioning by date, allowing efficient drop of entire partitions at retention boundaries.

The decision is operational: balance the storage cost against the lookup value of historical entries. Most programs find the storage cost is low enough that retention beyond regulatory requirements is fine.

Encryption considerations

If the Encryption Extra is active, the audit_log.message column is encrypted at rest. Database-level queries return ciphertext for the message field; application-level queries (via API or admin views) decrypt and display.

This means:

• Direct SQL queries against the audit log won't return readable messages without decryption tooling.
• Backups contain encrypted audit messages — restoration requires the master key.
• Analytics pipelines that bypass the application need to handle the encryption (typically by restoring to a non-encrypted environment, or by including decryption logic).

For most programs, accessing the audit trail through the application's UI or API is the simpler path; direct SQL is for one-off investigations.

HTML in audit messages

The message column contains HTML markup for highlighting and emphasis. The application's display path (via get_audit_trail_html() and HTMLPurifier) renders this safely; direct consumption (custom integrations) should treat the field as HTML and either render it or strip the markup.

Compliance evidence

For compliance audits, the audit trail is often the source of evidence for "we did the things we said we'd do":

• User access reviews: query for user-management entries in the period.
• Risk reviews: query for risk-review entries showing reviewers and review dates.
• Control changes: query for control-update entries showing when controls were modified.
• Configuration changes: query for system-level entries showing when settings or frameworks changed.

See Compliance Evidence Export for the export workflow.

Common patterns

Investigating a record's history

A risk shows unexpected state — the audit trail tells you what happened.

1. Open the risk.
2. Open the audit trail panel.
3. Read chronologically; look for the field change that explains the unexpected state.

Investigating a user's activity

A user reports doing X but can't see X — verify whether they actually did X.

1. Open User Management → the user.
2. View their audit trail entries.
3. Confirm or refute the action.

Investigating a configuration change

Something changed in the install — find out who and when.

1. Direct SQL query against audit_log for the relevant time window with risk_id = 0 (or the relevant entity ID).
2. Identify the change; trace responsibility.

Sanitizing for sharing

Sometimes audit trail entries need to be shared (with auditors, with vendors). Strip sensitive content before sharing:

• Replace user names with anonymized identifiers.
• Redact specific risk descriptions if they contain confidential information.
• Use the API to pull just the metadata (who did what when) without the message body.

Common pitfalls

A handful of patterns recur with the audit trail.

• Modifying audit entries directly in the database. Don't. The trail's value is its append-only nature. Modification undermines its usefulness as evidence.

• Deleting audit entries to "clean up." A user asking to remove their entries is asking for the audit trail to be tampered with. Audit policies should explicitly prohibit deletion outside formal retention purges.

• Treating the audit trail as a complete activity log. It captures changes; not reads. For "who looked at this," see web server logs.

• Querying without an index hint. The audit_log table can be large; queries without filters scan the full table. Always filter on risk_id, user_id, or timestamp.

• Letting the table grow indefinitely without monitoring. A 100-million-row audit log slows queries and bloats backups. Define a retention policy and apply it.

• Forgetting that audit messages contain HTML. Custom integrations that treat the field as plain text get HTML tags in the output.

• Not configuring the encryption layer for direct SQL access. With encryption active, direct SQL returns ciphertext for message. Use the application or API for human-readable access.

• Treating the audit trail as the only source for compliance evidence. Combine with the debug log, web server access logs, and authentication logs for full visibility.

• Truncating audit entries during normal operations. Bulk-deletion is irreversible; truncate only as part of a deliberate, documented retention purge with prior backup.

• Not periodically reviewing the audit trail for anomalies. Unauthorized configuration changes, mass user deletions, suspicious activity — the audit trail records them, but only review surfaces them. Build periodic review into operations.

Related

• The Debug Log
• Compliance Evidence Export
• Scheduled Reports
• The Permission Model
• The Encryption Extra Overview
• Database Backup and Restore

Reference

• Permission required: Per-entity audit trail viewable by users with view permission for the entity. Cross-entity audit access is admin-restricted.
• API endpoint(s): Per-entity audit endpoints (e.g., GET /api/v2/risks/{id}/audit). Some installs may also expose GET /api/v2/audit_log with filters.
• Implementing files: simplerisk/includes/display.php (get_audit_trail_html() — the per-record audit trail rendering with HTMLPurifier-based safe output); simplerisk/includes/api.php (audit endpoint handlers); per-entity update functions in simplerisk/includes/functions.php etc. (write audit entries).
• Database tables: audit_log (id, risk_id — the entity ID, message — HTML, log_type, timestamp, user_id).
• Encryption: If the Encryption Extra is active, audit_log.message is encrypted at rest.
• Retention: None enforced by default; the table grows indefinitely. Operators define retention policy operationally.
• External dependencies: None.