Audit Log — Compliance Logging & Retention

Overview

Stratum Platform maintains two audit logs to satisfy HIPAA/compliance requirements:

precedent_audit_log — All events affecting precedent objects (create, update, access, export)

appeals_audit_log — All events affecting appeals (create, save, submit, validate)

Both logs are append-only, immutable, and retained for 7 years minimum.

Core Concept

Audit Entry = Action + Actor + Time + Context Every compliance event is logged as a timestamped record linking the user, action, precedent/appeal, and outcome. Investigators can reconstruct exactly what happened, when, and by whom.

Schema Definition

`precedent_audit_log`

CREATE TABLE precedent_audit_log (
  id UUID PRIMARY KEY,
  
  -- Reference
  precedent_id UUID NOT NULL REFERENCES precedent_objects(id),
  
  -- Event Type
  event_type TEXT NOT NULL,
  -- "created", "updated", "accessed", "exported", "deidentified", "shared", "version_viewed"
  
  -- Actor
  user_id UUID NOT NULL REFERENCES users(id),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  
  -- Event Details (JSONB for flexibility)
  event_data JSONB,
  -- Shape varies by event_type:
  -- created: { "cluster_id": "...", "payer": "Cigna", "denial_type": "...", "status": "New" }
  -- updated: { "changed_fields": [...], "new_status": "Complete", "version_before": 1, "version_after": 2 }
  -- accessed: { "query_params": { "payer": "Cigna" }, "returned_count": 1 }
  -- exported: { "format": "csv", "deidentify": true, "record_count": 42 }
  -- shared: { "shared_with_tenant_id": "uuid", "access_level": "read" }
  -- version_viewed: { "version_number": 3 }
  
  -- Timestamp
  timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  
  -- Retention Metadata
  retained_until DATE,  -- Calculated as created_at + 7 years (2555 days)
  
  -- Indexes
  INDEX idx_audit_precedent (precedent_id, timestamp DESC),
  INDEX idx_audit_user (user_id, timestamp DESC),
  INDEX idx_audit_tenant (tenant_id, timestamp DESC),
  INDEX idx_audit_event_type (event_type, timestamp DESC),
  INDEX idx_audit_retained (retained_until, timestamp DESC)
);

`appeals_audit_log`

CREATE TABLE appeals_audit_log (
  id UUID PRIMARY KEY,
  
  -- Reference
  appeal_id UUID NOT NULL REFERENCES appeals(id),
  
  -- Event Type
  event_type TEXT NOT NULL,
  -- "created", "saved_draft", "validation_run", "submitted", "status_changed", "accessed"
  
  -- Actor
  user_id UUID NOT NULL REFERENCES users(id),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  
  -- Event Details
  event_data JSONB,
  -- Shape:
  -- created: { "denial_id": "CLM-2024-001", "payer_id": "cigna-v2024" }
  -- saved_draft: { "version_number": 1, "change_reason": "narrative_updated" }
  -- validation_run: { "status": "clean", "checks_passed": [...], "errors": [...] }
  -- submitted: { "version_number": 3, "precedent_ids": [...], "evidence_count": 5 }
  -- status_changed: { "old_status": "Draft", "new_status": "Submitted", "reason": "user action" }
  -- accessed: { "duration_seconds": 120 }
  
  -- Timestamp
  timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  retained_until DATE,
  
  -- Indexes
  INDEX idx_appeal_audit_appeal (appeal_id, timestamp DESC),
  INDEX idx_appeal_audit_user (user_id, timestamp DESC),
  INDEX idx_appeal_audit_tenant (tenant_id, timestamp DESC),
  INDEX idx_appeal_audit_event (event_type, timestamp DESC)
);

Event Types & Logging

Precedent Audit Events

#### created When precedent is inserted.

INSERT INTO precedent_audit_log (precedent_id, event_type, user_id, tenant_id, event_data)
VALUES (
  'prec-uuid',
  'created',
  'user-uuid',
  'tenant-uuid',
  '{"cluster_id": "Cigna Frequency Limit IOP", "payer": "Cigna", "denial_type": "Frequency Limit", "status": "New"}'
);

#### updated When precedent is modified (status, outcome, evidence, etc.).

INSERT INTO precedent_audit_log (precedent_id, event_type, user_id, tenant_id, event_data)
VALUES (
  'prec-uuid',
  'updated',
  'user-uuid',
  'tenant-uuid',
  '{"changed_fields": ["status", "outcome"], "new_status": "Complete", "version_before": 1, "version_after": 2}'
);

#### accessed When precedent is queried (list, single-record read).

INSERT INTO precedent_audit_log (precedent_id, event_type, user_id, tenant_id, event_data)
VALUES (
  'prec-uuid',
  'accessed',
  'user-uuid',
  'tenant-uuid',
  '{"query_params": {"payer": "Cigna", "denial_type": "Frequency Limit"}, "query_intent": "library_browse"}'
);

#### exported When precedent data is downloaded/exported.

INSERT INTO precedent_audit_log (precedent_id, event_type, user_id, tenant_id, event_data)
VALUES (
  'prec-uuid',
  'exported',
  'user-uuid',
  'tenant-uuid',
  '{"format": "csv", "deidentify": true, "record_count": 1, "export_reason": "appeal_preparation"}'
);

#### deidentified When Safe Harbor removal is applied.

INSERT INTO precedent_audit_log (precedent_id, event_type, user_id, tenant_id, event_data)
VALUES (
  'prec-uuid',
  'deidentified',
  'user-uuid',
  'tenant-uuid',
  '{"removed_identifiers": ["patient_name", "dob", "mrn", "phone"], "compliant_with": "HIPAA Safe Harbor"}'
);

#### shared When precedent is shared with another tenant (if applicable).

INSERT INTO precedent_audit_log (precedent_id, event_type, user_id, tenant_id, event_data)
VALUES (
  'prec-uuid',
  'shared',
  'user-uuid',
  'tenant-uuid',
  '{"shared_with_tenant_id": "other-tenant-uuid", "access_level": "read"}'
);

#### version_viewed When specific version is accessed.

INSERT INTO precedent_audit_log (precedent_id, event_type, user_id, tenant_id, event_data)
VALUES (
  'prec-uuid',
  'version_viewed',
  'user-uuid',
  'tenant-uuid',
  '{"version_number": 2, "reason": "audit_trail_review"}'
);

Appeal Audit Events

#### created When appeal is created.

INSERT INTO appeals_audit_log (appeal_id, event_type, user_id, tenant_id, event_data)
VALUES (
  'appeal-uuid',
  'created',
  'user-uuid',
  'tenant-uuid',
  '{"denial_id": "CLM-2024-001", "payer_id": "cigna-v2024", "status": "Draft"}'
);

#### saved_draft When draft is saved.

INSERT INTO appeals_audit_log (appeal_id, event_type, user_id, tenant_id, event_data)
VALUES (
  'appeal-uuid',
  'saved_draft',
  'user-uuid',
  'tenant-uuid',
  '{"version_number": 3, "change_reason": "narrative_updated", "precedent_count": 2}'
);

#### validation_run When appeal is validated before submission.

INSERT INTO appeals_audit_log (appeal_id, event_type, user_id, tenant_id, event_data)
VALUES (
  'appeal-uuid',
  'validation_run',
  'user-uuid',
  'tenant-uuid',
  '{"status": "clean", "checks_passed": ["has_narrative", "has_precedents"], "errors": []}'
);

#### submitted When appeal is submitted.

INSERT INTO appeals_audit_log (appeal_id, event_type, user_id, tenant_id, event_data)
VALUES (
  'appeal-uuid',
  'submitted',
  'user-uuid',
  'tenant-uuid',
  '{"version_number": 3, "precedent_ids": ["prec-uuid-1", "prec-uuid-2"], "evidence_count": 5}'
);

#### status_changed When payer response arrives.

INSERT INTO appeals_audit_log (appeal_id, event_type, user_id, tenant_id, event_data)
VALUES (
  'appeal-uuid',
  'status_changed',
  'user-uuid',
  'tenant-uuid',
  '{"old_status": "Submitted", "new_status": "Under Review", "reason": "payer_acknowledged"}'
);

#### accessed When appeal is read.

INSERT INTO appeals_audit_log (appeal_id, event_type, user_id, tenant_id, event_data)
VALUES (
  'appeal-uuid',
  'accessed',
  'user-uuid',
  'tenant-uuid',
  '{"access_type": "full_view", "duration_seconds": 120}'
);

Query Patterns

Get Full Audit Trail for Precedent

SELECT event_type, user_id, timestamp, event_data
FROM precedent_audit_log
WHERE precedent_id = $1 AND tenant_id = $2
ORDER BY timestamp DESC;

Find All Actions by User

SELECT precedent_id, event_type, timestamp, event_data
FROM precedent_audit_log
WHERE user_id = $1 AND tenant_id = $2
ORDER BY timestamp DESC
LIMIT 100;

Compliance Audit (SUD Data Access)

-- Who accessed SUD precedents in past 30 days?
SELECT 
  pa.precedent_id, pa.user_id, pa.event_type, pa.timestamp
FROM precedent_audit_log pa
JOIN precedent_objects po ON pa.precedent_id = po.id
WHERE po.data_origin_type = 'SUD'
  AND pa.tenant_id = $1
  AND pa.timestamp > NOW() - INTERVAL '30 days'
ORDER BY pa.timestamp DESC;

Retention Cleanup (Scheduled Monthly)

-- Find entries past retention period
SELECT COUNT() AS expired_entries
FROM precedent_audit_log
WHERE retained_until < CURRENT_DATE;

-- In production, these would be archived to cold storage and deleted
-- For now, flag for review:
SELECT id, precedent_id, timestamp, retained_until
FROM precedent_audit_log
WHERE retained_until < CURRENT_DATE - INTERVAL '1 day'
  AND retained_until > CURRENT_DATE - INTERVAL '7 days'
LIMIT 100;

Export Authorization Audit

-- Who exported what, when?
SELECT 
  user_id, 
  COUNT() AS export_count,
  MAX(timestamp) AS last_export
FROM precedent_audit_log
WHERE event_type = 'exported'
  AND timestamp > NOW() - INTERVAL '90 days'
GROUP BY user_id;

Retention & Cleanup

Retention Period

7 years minimum per HIPAA Security Rule § 164.312(b). Calculate retained_until at insertion:

INSERT INTO precedent_audit_log (precedent_id, ..., retained_until)
VALUES ('...', ..., CURRENT_DATE + INTERVAL '7 years');

Cleanup Workflow (Annual)

Query for expired entries:

SELECT COUNT(*) FROM precedent_audit_log
   WHERE retained_until < CURRENT_DATE;

Archive to cold storage (e.g., S3 Glacier):

Export to JSON/CSV
Calculate checksum (integrity verification)
Upload to archival bucket
Delete from active database (after verification):

DELETE FROM precedent_audit_log
   WHERE retained_until < CURRENT_DATE - INTERVAL '1 day'
   AND id IN (SELECT id FROM audited_entries_archive WHERE archived = true);

Compliance Notes

HIPAA Security Rule § 164.312(b) — Audit Controls:

✓ Logging of access, modification, and transmission
✓ Immediate timestamp for all events
✓ User identification (user_id)
✓ Tenant isolation (tenant_id per event)
✓ Event classification (event_type)
✓ Success/failure indicators (via event_data)
✓ 7-year retention enforced
✓ Immutability (append-only, no updates)

42 CFR Part 2 (SUD):

SUD data access logged separately
Access by non-authorized users flagged for investigation
Sharing events logged (if SUD data is shared, must be audit-traced)

Privacy Act (10-year retention):

Federal contractors may need 10-year retention for certain audit logs
Extend retained_until to 10 years for federal entities

Code reference

src/middleware/audit.ts — Middleware that logs all events
src/models/audit-log.ts — Query helpers (trail, search, compliance audit)
src/routes/compliance.ts — API endpoints for audit retrieval (admin only)
src/db/schema.sql lines 196–260 — precedent_audit_log definition
src/db/migrations/002_appeals_infrastructure.sql — appeals_audit_log definition