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:

Field	Type	Description	Regulatory Basis
event_id	UUID	Unique identifier for this log entry	Tamper-evidence
event_code	string	Structured event code from catalog	Searchability
event_timestamp	ISO 8601 UTC	When the event occurred (server time, not client)	All regs
actor_id	string	Authenticated user ID who caused the event	FINRA 4511, SOX 404
actor_role	string	Role/permission level of the actor at time of event	Segregation of duties
actor_ip	string	Client IP address	Security
session_id	UUID	Session identifier linking related events	Forensics
correlation_id	UUID	Business transaction ID spanning multiple events	Traceability
object_type	string	Entity type affected (Policy, Loan, Client, User)	Context
object_id	string	ID of the affected record	Searchability
before_value	JSON	Field values before the change (for Update events)	SOX, FINRA
after_value	JSON	Field values after the change (for Update events)	SOX, FINRA
result	enum	success | failure | partial	Completeness
failure_reason	string	If result = failure, why it failed	Debugging
source_system	string	Which system generated the event	Multi-system audit
data_classification	enum	public | internal | confidential | restricted	Privacy

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

Event Category	Minimum Retention	Maximum Retention	Regulatory Citation
Insurance policy transactions	5 years post-policy expiration	7 years	State DOI (varies; use most restrictive)
Insurance claims records	5 years post-close	10 years	State DOI claims regulations
Mortgage origination records	3 years	5 years	RESPA § 1024.9
HMDA LAR data	3 years	Permanent	Reg C § 1003.5
BSA/AML suspicious activity	5 years	Permanent	31 CFR 1020.320
FINRA communications	3 years (first 2 easily accessible)	6 years	FINRA Rule 4511
SOX financial controls	7 years	Permanent	SOX § 802
Login/authentication logs	1 year	3 years	SOC 2 / general security
Failed authentication	90 days	1 year	Security best practice
System configuration changes	3 years	7 years	SOX ITGC

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

Role	Permissions
Application services	INSERT only — no SELECT, UPDATE, DELETE
Compliance officer	SELECT with full field access, CSV export
Internal audit	SELECT with full field access, read-only
IT operations	SELECT on non-PII fields only; PII masked
Legal	SELECT with full access during litigation hold
Regulators (examination)	Read-only export of scoped date range
No role	DELETE or UPDATE (blocked at DB and application layer)

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