Backend Audit Logging

Backend Audit Logging

Purpose

Record every sensitive data access and state change in an immutable, tamper-evident audit trail for regulatory compliance (SOC 2, SOX, HIPAA, GDPR).

Agent Protocol

Trigger

Exact user phrases: "audit log", "audit trail", "compliance logging", "tamper-evident log", "immutable log", "audit table", "change tracking", "who changed what", "data provenance", "audit events", "reporting log".

Input Context

• Regulatory framework (SOC 2, SOX, HIPAA, GDPR, PCI-DSS).
• Data classification and sensitive fields.
• Existing database and storage infrastructure.
• Retention requirements.

Output Artifact

Audit log schema or implementation code. No file unless requested.

Response Format

Event: {event type}
Actor: {user/system}
Resource: {resource type}:{id}
Action: {read|create|update|delete}

Completion Criteria

• Audit events recorded for all sensitive operations
• Audit log is append-only (immutable)
• Tamper evidence implemented (hash chain or similar)
• Retention policy configured
• Audit logs queryable by actor, resource, and time range

Architecture Decision Trees

Storage Backend Decision Tree

What is the primary compliance requirement?
├── SOC 2 / SOX → PostgreSQL with hash chain (append-only table, partition by month)
├── HIPAA → Encrypted database with strict access controls + hash chain + 6-year retention
├── GDPR → Searchable storage with data deletion capability (anonymization, not deletion)
├── PCI-DSS → Immutable log with strict access controls, 1-year retention, quarterly review
└── General compliance → Cloud-native audit service (AWS CloudTrail, Azure Monitor)

What is the write volume?
├── < 1000 events/sec → Relational database (PostgreSQL, MySQL) with hash chain
├── 1000–10000 events/sec → Time-series database (TimescaleDB, ClickHouse)
└── > 10000 events/sec → Log aggregator (Elasticsearch) + batched hash chain

Tamper Evidence Decision Tree

Is tamper evidence required by compliance framework?
├── Yes → Is regulatory framework HIPAA or PCI-DSS?
│   ├── Yes → Hash chain with periodic verification (quarterly) + external audit service
│   └── No → Hash chain (SHA-256 linked list) or digital signature per entry
└── No → Append-only table with database-level access controls (no hash chain needed)

Volume consideration:
├── Single DB (<1000/s) → Hash chain linking each row via previous_hash
├── Medium (1000–10000/s) → Batch Merkle tree, publish root hash periodically
└── High (>10000/s) → Periodic hash anchor (chain per batch, not per event)

Audit Event Schema Decision Tree

What needs to be recorded?
├── Just who did what? → Minimal: actor, action, resource, timestamp
├── Plus what changed? → Standard: + resource_id, changes (diff), metadata, correlation_id
├── Plus proof of integrity? → Complete: + previous_hash, hash, digital_signature
└── Multi-tenant? → Complete + tenant_id

Workflow

Step 1: Define Audit Events

events:
  user.access:       User accessed PII data
  user.update:       User profile updated
  payment.process:   Payment initiated or completed
  role.change:       User role or permission changed
  data.export:       Data exported (GDPR SAR)
  data.deletion:     Data deleted (right to be forgotten)
  settings.change:   System configuration changed
  auth.login:        User authentication succeeded
  auth.logout:       User session ended
  auth.failed:       Authentication failed
  admin.action:      Administrative action performed
  integration.call:  External API call made
  data.masked:       Sensitive data masked
  consent.granted:   User consent recorded
  consent.revoked:   User consent revoked

Step 2: Create Audit Table (PostgreSQL)

CREATE TABLE audit_log (
  id              BIGSERIAL PRIMARY KEY,
  event_type      VARCHAR(50) NOT NULL,
  actor_id        VARCHAR(100) NOT NULL,
  actor_type      VARCHAR(20) NOT NULL,   -- 'user' | 'system' | 'api'
  resource_type   VARCHAR(50) NOT NULL,
  resource_id     VARCHAR(100),
  action          VARCHAR(10) NOT NULL,   -- 'create' | 'read' | 'update' | 'delete'
  changes         JSONB,                  -- diff of old/new values
  metadata        JSONB,                  -- IP, user-agent, requestId
  correlation_id  VARCHAR(64),            -- trace across services
  occurred_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  hash            VARCHAR(64),            -- SHA-256 of previous row + this row data
  previous_hash   VARCHAR(64),
  tenant_id       VARCHAR(50),             -- for multi-tenant systems
  event_version   INT DEFAULT 1
);

-- Prevent UPDATE/DELETE via trigger
CREATE OR REPLACE FUNCTION prevent_audit_mutation()
RETURNS TRIGGER AS $$
BEGIN
    RAISE EXCEPTION 'Audit log is append-only. UPDATE and DELETE are not permitted.';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_audit_update
    BEFORE UPDATE ON audit_log
    FOR EACH ROW EXECUTE FUNCTION prevent_audit_mutation();

CREATE TRIGGER prevent_audit_delete
    BEFORE DELETE ON audit_log
    FOR EACH ROW EXECUTE FUNCTION prevent_audit_mutation();

-- Indexes for common queries
CREATE INDEX idx_audit_event_type ON audit_log(event_type);
CREATE INDEX idx_audit_actor ON audit_log(actor_id, actor_type);
CREATE INDEX idx_audit_resource ON audit_log(resource_type, resource_id);
CREATE INDEX idx_audit_occurred_at ON audit_log(occurred_at);
CREATE INDEX idx_audit_tenant ON audit_log(tenant_id) WHERE tenant_id IS NOT NULL;

-- Partition by month
CREATE TABLE audit_log_2026_01 PARTITION OF audit_log
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE audit_log_2026_02 PARTITION OF audit_log
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

Step 3: Implement Hash Chain

import { createHash } from 'crypto';

async function appendAuditLog(event: AuditEvent, db: Database): Promise<void> {
  const lastRow = await db.query('SELECT id, hash FROM audit_log ORDER BY id DESC LIMIT 1');
  const previousHash = lastRow.rows[0]?.hash || '';

  // Build entry with hash chain
  const entry = { ...event, previousHash };
  const canonicalData = canonicalize(entry);
  entry.hash = createHash('sha256')
    .update(previousHash + canonicalData)
    .digest('hex');

  await db.query('INSERT INTO audit_log SET ?', entry);
}

function canonicalize(obj: Record<string, unknown>): string {
  return JSON.stringify(obj, Object.keys(obj).sort());
}

// Hash chain verification
async function verifyChainIntegrity(db: Database): Promise<{ chainIntact: boolean; brokenLinks: number }> {
  const result = await db.raw(`
    SELECT COUNT(*) as broken FROM (
      SELECT id, hash, previous_hash,
        LAG(hash) OVER (ORDER BY id) as expected_previous
      FROM audit_log
    ) sub
    WHERE sub.previous_hash != COALESCE(sub.expected_previous, '')
  `);
  return { chainIntact: Number(result.rows[0].broken) === 0, brokenLinks: Number(result.rows[0].broken) };
}

Step 4: Emit Audit Events from Code

// ORM hook or middleware
auditService.append({
  eventType: 'user.update',
  actorId: req.user.id,
  actorType: 'user',
  resourceType: 'user_profile',
  resourceId: profileId,
  action: 'update',
  changes: diff(oldProfile, newProfile),  // Only non-sensitive fields
  metadata: {
    ip: req.ip,
    userAgent: req.headers['user-agent'],
    requestId: req.id,
  },
  correlationId: req.correlationId,
});

Python example:

from dataclasses import dataclass, asdict
from datetime import datetime, timezone
import hashlib, json

@dataclass
class AuditEvent:
    event_type: str
    actor_id: str
    actor_type: str
    resource_type: str
    resource_id: str
    action: str
    changes: dict | None = None
    metadata: dict | None = None
    correlation_id: str = ""
    tenant_id: str = ""
    occurred_at: str = ""

class AuditService:
    def __init__(self, db):
        self.db = db

    async def append(self, event: AuditEvent):
        event.occurred_at = datetime.now(timezone.utc).isoformat()
        last = await self.db.fetch("SELECT hash FROM audit_log ORDER BY id DESC LIMIT 1")
        previous_hash = last[0]["hash"] if last else ""

        data = {**asdict(event), "previous_hash": previous_hash}
        canonical = json.dumps(data, sort_keys=True)
        data["hash"] = hashlib.sha256((previous_hash + canonical).encode()).hexdigest()

        await self.db.execute("INSERT INTO audit_log VALUES (...)", data)

Step 5: Audit Query Service

class AuditQueryService {
  async search({ actorId, resourceType, eventType, from, to, page, limit }: AuditSearchParams): Promise<AuditSearchResult> {
    let query = knex('audit_log')
      .where('occurred_at', '>=', from)
      .where('occurred_at', '<=', to);

    if (actorId) query.where('actor_id', actorId);
    if (resourceType) query.where('resource_type', resourceType);
    if (eventType) query.where('event_type', eventType);

    const total = await query.clone().count('id as count').first();
    const rows = await query
      .orderBy('occurred_at', 'desc')
      .offset((page - 1) * limit)
      .limit(limit);

    return { rows, total: Number(total.count), page, limit };
  }

  async verifyIntegrity(): Promise<{ chainIntact: boolean }> {
    const result = await db.raw(`
      SELECT COUNT(*) as broken FROM (
        SELECT id, hash, previous_hash,
          LAG(hash) OVER (ORDER BY id) as expected_previous
        FROM audit_log
      ) sub
      WHERE sub.previous_hash != COALESCE(sub.expected_previous, '')
    `);
    return { chainIntact: Number(result.rows[0].broken) === 0 };
  }
}

Step 6: Implement Retention and Archival

Tier 1: Hot storage (7-30 days) — queryable via API, online (SSD)
Tier 2: Warm storage (1 year) — queryable with delay (S3/Parquet export, query via Athena)
Tier 3: Cold storage (7 years) — archived, not directly queryable (Glacier/Deep Archive)

class AuditArchivalService {
  async archiveToS3(endDate: Date): Promise<void> {
    const thirtyDaysAgo = new Date();
    thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

    const rows = await knex('audit_log')
      .where('occurred_at', '<', endDate)
      .where('occurred_at', '>=', thirtyDaysAgo);

    // Write to Parquet in S3
    await s3.putObject({
      Bucket: 'audit-archive',
      Key: `year=${endDate.getUTCFullYear()}/month=${endDate.getUTCMonth() + 1}/audit.parquet`,
      Body: await toParquet(rows),
    });

    // Delete from hot storage
    await knex('audit_log')
      .where('occurred_at', '<', endDate)
      .delete();
  }
}

Production Considerations

Write Performance

• Audit log writes must be asynchronous — never block the request path
• Use a message queue (Kafka, RabbitMQ, SQS) between application and audit storage
• Batch writes: collect events for 100ms or 100 events, then flush
• Hash computation adds ~0.1ms per entry — negligible for <1000/s
• For high-volume, use batch hash chains (Merkle tree per batch, not per entry)

Compliance Requirements Per Framework

ClickHouse for High-Volume Audit

CREATE TABLE audit_log (
    event_type String,
    actor_id String,
    actor_type String,
    resource_type String,
    resource_id String,
    action String,
    changes String,
    metadata String,
    correlation_id String,
    tenant_id String,
    occurred_at DateTime('UTC'),
    event_version UInt8,
    hash String,
    previous_hash String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(occurred_at)
ORDER BY (occurred_at, event_type, actor_id)
TTL occurred_at + INTERVAL 7 YEAR DELETE;

Anti-Patterns

Anti-Pattern 1: Logging Sensitive Values in Diff

Recording actual SSN, credit card numbers, or passwords in the changes diff. Fix: Use tokenization or masking — show that a field changed, not what it changed to.

Anti-Pattern 2: No Correlation ID Across Services

Each service logs audit events independently without a common trace ID. Fix: Propagate correlation ID through all services, include in every audit event.

Anti-Pattern 3: Blocking on Audit Writes

Using synchronous DB inserts for audit logging impacts main request latency. Fix: Use async writes, message queues, or background batch processing.

Anti-Pattern 4: Single Point of Failure in Chain

Running hash chain on a single database without monitoring. Fix: Implement periodic verification, remote backups, alert on chain breaks.

Anti-Pattern 5: Ignoring Clock Skew

Distributed systems log events with different server clocks. Fix: NTP-synchronized clocks, log in UTC, timestamp at application layer not database.

Anti-Pattern 6: No Audit Coverage for System Actions

Auditing only user actions but missing cron jobs, background workers, admin scripts. Fix: Every state change must be audited regardless of initiator.

Anti-Pattern 7: Monolithic Audit Table

Single audit_log table for all event types creates query performance issues. Fix: Partition by time or use separate tables for different event categories.

Anti-Pattern 8: Mutable Audit Log

Allowing UPDATE or DELETE on audit records defeats the purpose. Fix: Database triggers to prevent mutations, restricted permissions.

Security Considerations

Access Control

• Role-based access for audit log queries (auditor, admin, security)
• Read-only access for most roles
• No one (including DBAs) can delete audit log entries
• Audit log access is itself audited
• Encrypt sensitive fields within audit entries

Immutability Enforcement

• Database triggers to prevent UPDATE/DELETE
• Application-level write-once check (reject updates to existing entries)
• WORM storage at the filesystem level for archived data
• Restricted database permissions (INSERT only for app user)
• Hash chain verification detects any tampering

Encryption

• Encrypt audit log at rest (TDE or column-level encryption)
• Encrypt sensitive fields (PII, PHI) within the audit entry
• Use separate encryption keys for audit data
• Key rotation without data re-encryption (envelope encryption)

Comparative Analysis

Hash Chain vs WORM Storage vs Digital Signatures

Database Audit vs Application Audit

PostgreSQL vs Elasticsearch vs ClickHouse

Performance Considerations

• Async writes via message queue to avoid blocking requests
• Batch inserts (100 records or 100ms window) for throughput
• Partition by month for query performance and archival
• Archive data > retention to Parquet/ORC on object storage
• Index only columns used in common queries — avoid over-indexing
• Hash computation: SHA-256 of ~1KB = ~0.05ms. Negligible.
• For >100K events/sec, use Kafka + Flink + ClickHouse pipeline
• Cold data in object storage costs ~$0.023/GB/month (S3 Glacier)

Rules

• Audit logs are append-only. Never UPDATE or DELETE rows.
• Always record who (actor), what (action), when (timestamp), and what changed (diff).
• Include a hash chain for tamper evidence. Verify chain integrity quarterly.
• Never log actual sensitive values in the diff. Log that access occurred and mask values.
• Time must be in UTC with timezone, never server local time.
• Retention policy must meet the strictest regulatory requirement (usually 7 years).
• Reject any request that does not include audit context (actor, resource, action).
• Test tamper detection regularly (at least quarterly for SOC 2).
• Include correlation IDs for cross-service traceability.
• Partition or archive data older than retention period — do not delete.
• Make audit logs queryable by actor, resource type, event type, and time range.
• Implement role-based access control on audit log access.
• Monitor audit log write failures and alert immediately.

References

• references/audit-log-architecture.md — Architectural patterns for audit logging systems
• references/audit-log-compliance.md — Compliance requirements mapping for audit logs
• references/audit-log-implementation.md — Implementation guides and code examples
• references/audit-log-monitoring.md — Monitoring and alerting for audit log systems
• references/audit-trail.md — Audit trail design principles
• references/compliance-logging.md — Compliance logging best practices
• references/audit-log-fundamentals.md — Audit Logging Fundamentals
• references/audit-log-advanced.md — Audit Logging Advanced Patterns
• references/audit-log-storage-comparison.md — Audit Storage Backend Comparison

Handoff

No artifact produced unless requested. Next skill: plugin-architecture — extend the audit system with custom event handlers. Carry forward: audit event types, retention policy, tamper evidence strategy.

Implementation Patterns

Observer Pattern for Event Handling

` interface EventObserver { onEvent(event: T): Promise; }

class EventBus { private observers: Set<EventObserver> = new Set(); subscribe(observer: EventObserver): void { this.observers.add(observer); } unsubscribe(observer: EventObserver): void { this.observers.delete(observer); } async emit(event: T): Promise { const results = Array.from(this.observers).map(o => o.onEvent(event)); await Promise.allSettled(results); } } `

Configuration-Driven Approach

config: defaults: timeout: 30s retryCount: 3 overrides: production: timeout: 60s retryCount: 5 development: timeout: 300s retryCount: 1

Production Considerations

Deployment Checklist

• Configuration validated against schema before startup
• Health check endpoints registered and monitored
• Graceful shutdown with draining period (30s timeout)
• Resource limits configured (CPU, memory, file descriptors)
• Log level set appropriate for environment
• Metrics endpoint secured and exposed
• Rate limiting configured per-tier
• TLS certificates valid and auto-renewing
• Database migrations run as separate deployment step
• Feature flags ready for gradual rollout

Monitoring and Alerting

Anti-Patterns

Performance Optimization

Caching Strategy

Cache hierarchy: L1 (in-memory local) → L2 (distributed Redis/Memcached) → L3 (CDN/Edge). Cache invalidation: TTL-based (simple, stale), event-based (complex, fresh), write-through (consistent, higher write latency), write-behind (fast writes, eventual consistency).

Resource Pooling

• Database connections: Pool of reusable connections (HikariCP, pgBouncer)
• HTTP connections: Keep-alive + connection pooling for external calls
• Thread pool: Bounded thread pools for async task execution

Profiling Methodology

1. Establish baseline with production traffic profile
2. Profile CPU with sampling profiler (pprof, perf, async-profiler)
3. Profile memory with heap dumps and allocation tracking
4. Profile I/O with strace/perf trace for syscall analysis
5. Profile latency with distributed tracing (OpenTelemetry)
6. Identify bottleneck, formulate hypothesis, implement fix
7. Re-profile to verify improvement, repeat

Security Considerations

Threat Modeling (STRIDE)

• Spoofing: Identity validation, authentication
• Tampering: Integrity checks, digital signatures
• Repudiation: Audit logs, non-repudiation
• Information disclosure: Encryption, access control
• Denial of service: Rate limiting, resource quotas
• Elevation of privilege: Principle of least privilege

Supply Chain Security

• Dependency scanning: Snyk, Dependabot, Trivy
• SBOM generation: CycloneDX or SPDX format
• Signed commits: GPG or SSH commit signing
• Artifact verification: Checksum validation, signature verification

Secrets Management

• Secrets never in code — always in secrets manager (Vault, AWS Secrets Manager)
• Rotation policy: Rotate database credentials every 90 days
• Access audit: Log every secrets access, alert on anomalies
• Encryption at rest and in transit for all secrets
• Principle of least privilege: each service gets only its own secrets

Rules

• Default-deny security posture — allow only explicitly required access.
• All inputs validated, all outputs encoded, all errors handled.
• Defend in depth — multiple layers of security controls.
• Fail securely — errors default to safe behavior.
• Log security-relevant events for audit and investigation.
• Keep dependencies updated — automate vulnerability scanning.
• Design for observability from day one, not as an afterthought.
• Document all architectural decisions with rationale.
• Review code for security, performance, and correctness before merging.