audit-trail

What this skill does

# 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:

```sql
-- 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