Audit Trail

Audit Trail Design

Produce a complete audit logging specification for a regulated business process, covering event catalog, data schema, immutability mechanisms, retention schedule, access controls, and examination query patterns.

Step 1: Event Catalog

Enumerate every action that must be logged. For each workflow, identify:

Standard event categories:

• Create — Record, account, policy, loan, or document created
• Read (sensitive only) — PII accessed, credit report pulled, account viewed by non-owner
• Update — Field value changed (capture before/after)
• Delete (or archive) — Record removed or soft-deleted
• Status change — Workflow state transition (e.g., submitted → approved → issued)
• Authorization — Access granted, permission changed, role assigned
• Export/Download — Data exported to file, report generated, document downloaded
• Communication — Email sent, disclosure delivered, adverse action notice issued
• Integration — Data sent to or received from external system (AMS, LOS, AUS, carrier portal)
• System — Login, logout, session timeout, failed authentication, configuration change

For each event, assign:

• Event code (e.g., POL.BIND.001) — namespace by domain and action
• Description — Plain English description of what occurred
• Trigger — What user action or system event causes this log entry
• Frequency — Expected volume (per day / per transaction)
• Regulatory driver — Which regulation requires this event to be logged (or "business requirement" if not regulatory)

Step 2: Required Fields per Event

Every audit log record must capture these fields as a minimum:

For events involving PII or PHI, also capture:

• data_subject_id — ID of the individual whose data was accessed
• access_purpose — Business purpose for accessing the data (required for GLBA/CCPA)

Step 3: Immutability Mechanisms

The audit log must be tamper-evident and append-only. Choose one or more mechanisms based on client infrastructure:

Database-level:

• Append-only table with no UPDATE or DELETE permissions for any application role
• Database trigger that blocks UPDATE/DELETE and alerts on attempt
• Row-level hash: SHA-256(event_id + event_timestamp + actor_id + object_id + content_hash_of_all_fields) stored in each row
• Periodic Merkle root calculation (batch of N records → root hash stored separately)

Storage-level:

• Write-Once Read-Many (WORM) storage for log exports (AWS S3 Object Lock, Azure Blob immutability policy)
• Automated daily export to immutable cold storage with hash verification

Cryptographic chaining:

• Each record contains previous_record_hash — SHA-256 of the previous record's full content
• Chain integrity verification: daily automated job validates the hash chain; alerts on break

Third-party:

• Immutable audit log service (e.g., AWS CloudTrail, Azure Monitor, Splunk SIEM) as secondary sink
• Records written to primary DB and secondary immutable service simultaneously

Step 4: Retention Schedule

Legal hold: Any record subject to litigation hold must be retained until hold is released, regardless of standard retention period. Implement a legal hold flag that overrides automated deletion.

Step 5: Access Controls

All access to the audit log itself must be logged in a separate meta-audit log (log of logs).

Step 6: Examination Query Patterns

Design indexed queries for the most common regulatory examination scenarios:

-- All actions by a specific user in a date range
SELECT * FROM audit_log
WHERE actor_id = :user_id
  AND event_timestamp BETWEEN :start_date AND :end_date
ORDER BY event_timestamp;

-- All changes to a specific record
SELECT * FROM audit_log
WHERE object_type = :entity_type AND object_id = :record_id
ORDER BY event_timestamp;

-- All failed authentication attempts
SELECT * FROM audit_log
WHERE event_code = 'AUTH.LOGIN.FAIL'
  AND event_timestamp >= :start_date
ORDER BY event_timestamp;

-- All disclosures delivered (for TRID/ECOA examination)
SELECT * FROM audit_log
WHERE event_code IN ('DISC.LE.DELIVER', 'DISC.CD.DELIVER', 'DISC.ADVERSE.DELIVER')
  AND event_timestamp BETWEEN :start_date AND :end_date;

-- Chain integrity verification
SELECT event_id, event_timestamp,
       SHA256(CONCAT(event_id, event_timestamp, actor_id, object_id, content)) AS computed_hash,
       stored_hash,
       (computed_hash = stored_hash) AS hash_valid
FROM audit_log
ORDER BY event_timestamp;

Output Format

Deliver four artifacts:

1. Event Catalog Table — All events with code, description, trigger, frequency, regulatory driver
2. Data Schema — Full field definition table with types, constraints, and regulatory basis
3. Retention Matrix — Retention schedule table with regulatory citations
4. Access Control Specification — Role permission matrix and meta-audit requirements