Building Audit Trails for Compliance-Heavy Industries

Why Audit Trails Matter

Compliance-heavy industries (healthcare, finance, legal) require proving who did what and when. Audit trails provide accountability, detect fraud, and satisfy regulatory audits.

Audit Trail Requirements

Immutability: Once logged, entries never change. Amendments create new entries.

Completeness: Track all material changes: who, what, when, where, why.

Non-repudiation: Users can't deny their actions (digital signatures).

Retention: Maintain logs 5-10+ years per regulation.

Searchability: Quickly find actions by user, date, resource.

Schema Design

interface AuditLog {
  id: string;           // Unique, immutable
  timestamp: DateTime;  // When action occurred
  userId: string;       // Who performed action
  action: string;       // What happened (created, updated, deleted)
  resourceType: string; // Document type
  resourceId: string;   // Document ID
  changes: {           // What changed
    before: any;
    after: any;
  };
  reason?: string;      // Why (compliance reason)
  ipAddress: string;    // Where from
  userAgent: string;    // What client
  signature?: string;   // Digital signature
}

Logging Every Action

Instrument business logic:

async function updatePatientRecord(
  patientId: string,
  updates: Partial<Patient>,
  userId: string,
  reason: string
) {
  const old = await getPatient(patientId);
  const updated = { ...old, ...updates };

  // Update primary data
  await db.update('patients', patientId, updated);

  // Log audit trail
  await db.insert('audit_logs', {
    id: generateId(),
    timestamp: new Date(),
    userId,
    action: 'UPDATE',
    resourceType: 'PATIENT',
    resourceId: patientId,
    changes: { before: old, after: updated },
    reason,
    ipAddress: getRequestIp(),
    userAgent: getUserAgent()
  });
}

Immutable Storage

Store audit logs in append-only database:

PostgreSQL with constraints:

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY,
  timestamp TIMESTAMP NOT NULL DEFAULT NOW(),
  user_id UUID NOT NULL,
  action VARCHAR NOT NULL,
  resource_type VARCHAR NOT NULL,
  resource_id VARCHAR NOT NULL,
  changes JSONB NOT NULL,
  reason VARCHAR,
  ip_address INET,
  user_agent VARCHAR
);

-- Prevent updates or deletes
CREATE POLICY no_update ON audit_logs
  FOR UPDATE TO public USING (false);

CREATE POLICY no_delete ON audit_logs
  FOR DELETE TO public USING (false);

Separate audit database: Store audit logs in separate, read-only database replicated from main database.

Digital Signatures

Verify log integrity:

import crypto from 'crypto';

function signEntry(entry: AuditLog, key: string) {
  const hash = crypto
    .createHmac('sha256', key)
    .update(JSON.stringify(entry))
    .digest('hex');

  return hash;
}

async function logWithSignature(
  entry: AuditLog,
  signingKey: string,
  previousSignature?: string
) {
  // Chain of custody: current entry includes previous signature
  entry.previousSignature = previousSignature;

  const signature = signEntry(entry, signingKey);

  await db.insert('audit_logs', {
    ...entry,
    signature
  });

  return signature;
}

Signatures prove tamper-attempts: change entry breaks signature chain.

Compliance Reporting

Generate reports for audits:

async function generateComplianceReport(
  startDate: Date,
  endDate: Date,
  resourceId?: string
) {
  const logs = await db.query(
    `SELECT * FROM audit_logs
     WHERE timestamp >= ? AND timestamp < ?
     ${resourceId ? 'AND resource_id = ?' : ''}
     ORDER BY timestamp ASC`,
    resourceId ? [startDate, endDate, resourceId] : [startDate, endDate]
  );

  return {
    period: { startDate, endDate },
    summary: {
      totalEvents: logs.length,
      uniqueUsers: new Set(logs.map(l => l.user_id)).size,
      actions: groupBy(logs, l => l.action)
    },
    logs: logs.map(l => ({
      timestamp: l.timestamp,
      user: l.userId,
      action: l.action,
      resource: `${l.resourceType}:${l.resourceId}`,
      reason: l.reason,
      details: l.changes
    }))
  };
}

Performance Considerations

Audit logging adds overhead. Optimize:

• Async logging: Queue writes, batch inserts
• Selective logging: Log critical actions, not every read
• Log rotation: Archive old logs to cheaper storage
• Compression: Compress historical logs

async function logAsync(entry: AuditLog) {
  // Add to in-memory queue
  auditQueue.push(entry);

  // Batch write every 100 entries or 5 seconds
  if (auditQueue.length >= 100) {
    await flushAuditQueue();
  }
}

async function flushAuditQueue() {
  const entries = auditQueue.splice(0);
  if (entries.length === 0) return;

  await db.insert('audit_logs', entries);
}

Retention and Archival

Retention policies vary by industry:

• Healthcare (HIPAA): 6 years minimum
• Finance (SOX): 7 years
• Legal: Matter lifetime + 7 years

Implement archival:

async function archiveOldLogs(beforeDate: Date) {
  // Move to archive storage (S3, cheaper database)
  const logs = await db.query(
    'SELECT * FROM audit_logs WHERE timestamp < ? LIMIT 100000',
    [beforeDate]
  );

  await archiveStorage.upload(`audit-${beforeDate.getTime()}.json.gz`,
    gzip(JSON.stringify(logs)));

  // Delete from primary database
  await db.delete('audit_logs', 'WHERE timestamp < ?', [beforeDate]);
}