Enterprise Audit System

The Enterprise Audit System writes tamper-evident audit logs: every event is linked to its predecessor by SHA-256 hash, personal data is redacted automatically, the state of the chain can be queried via the API at any time, and the full verification of every chain runs automatically each night. The API supports compliance reviews and forensic analysis.

SHA-256 Hash Chain Mechanism

The audit system chains events cryptographically so that any tampering becomes detectable:

┌─────────────────────────────────────────────────────────────┐
│                  HASH CHAIN STRUCTURE                       │
└─────────────────────────────────────────────────────────────┘

Event 1 (Genesis):
┌──────────────────────────────────────┐
│ Sequence: 1                          │
│ PrevHash: NULL                       │
│ Data: { action: "LOGIN", ... }       │
│ Hash: sha256(data) = abc123...       │
└──────────────────────────────────────┘
                │
                │ (prevHash)
                ▼
Event 2:
┌──────────────────────────────────────┐
│ Sequence: 2                          │
│ PrevHash: abc123...  ← Event 1 Hash  │
│ Data: { action: "CREATE_TICKET", ...}│
│ Hash: sha256(data) = def456...       │
└──────────────────────────────────────┘
                │
                │ (prevHash)
                ▼
Event 3:
┌──────────────────────────────────────┐
│ Sequence: 3                          │
│ PrevHash: def456...  ← Event 2 Hash  │
│ Data: { action: "APPROVE", ... }     │
│ Hash: sha256(data) = ghi789...       │
└──────────────────────────────────────┘

Tamper detection:

❌ Attempt to modify Event 2:   • Hash recomputed = xyz999... (≠ def456...)   • Event 3 still shows PrevHash = def456...   • → Chain broken, tampering detected
❌ Attempt to delete Event 2:   • Event 3 shows PrevHash = def456...   • But event with hash def456... no longer exists   • → Chain broken, tampering detected

Hash Calculation (PostgreSQL Trigger)

-- DB trigger computes hash AUTOMATICALLY on every INSERT
CREATE TRIGGER audit_event_hash_trigger
  BEFORE INSERT ON "AuditEvent"
  FOR EACH ROW
  EXECUTE FUNCTION audit_event_set_hash();

-- Hash calculation:
-- 1. Lock ChainHead with FOR UPDATE (per org)
-- 2. Fetch prev hash + sequence
-- 3. Build canonical payload (JSONB, keys sorted alphabetically)
-- 4. Compute SHA-256 hash
-- 5. Update ChainHead (last_hash, last_sequence)

-- Immutability trigger:
CREATE TRIGGER audit_event_no_modify
  BEFORE UPDATE OR DELETE ON "AuditEvent"
  FOR EACH ROW
  EXECUTE FUNCTION audit_event_immutable();

-- UPDATE only with SET LOCAL audit.allow_purge = 'true'
--   AND chain/identity columns unchanged (hash, sequence,
--   prev_hash, occurred_at, actor_id, entity_id ...);
--   purged_at, once set, can never be removed.
--   Two legitimate forms:
--   a) retention content purge (changes/metadata/... → NULL, purged_at set)
--   b) Art. 17 erasure scrub (actor_name/actor_ip/actor_user_agent/entity_name,
--      which are not part of the hash payload)
--   Every other UPDATE stays forbidden.
-- DELETE only at the chain end (retention stage 2):
--   SET LOCAL audit.allow_delete = 'true'

Chain Head Table & Purge Anchor

-- One row per organization (performance optimization)
AuditChainHead {
  org_id: String (UNIQUE, '__global__' for single-tenant)
  last_hash: String (hash of the last event)
  last_sequence: BigInt (sequence of the last event)
  purged_through_sequence: BigInt  -- retention stage 2: how far the
  purge_anchor_hash: String?       -- prefix was hard-deleted (anchor)
  updated_at: DateTime
}

-- Updated by the trigger (FOR UPDATE lock).
-- The purge anchor lets verification check that the first remaining
-- event links to purge_anchor_hash — so a prefix delete is provable
-- and distinguishable from tampering.

API Endpoints

Audit Events Query

Audit Domains

Severity Levels

API Examples

Get Events (with Filtering)

GET /api/audit/enterprise/events?domain=SECURITY&severity=CRITICAL&fromDate=2026-01-01T00:00:00Z&limit=50&offset=0&sortBy=occurredAt&sortOrder=desc

Response

{
  "success": true,
  "data": [
    {
      "id": "clx...",
      "occurredAt": "2026-01-27T15:30:00Z",
      "ingestedAt": "2026-01-27T15:30:00.123Z",
      "sequence": "12345",
      "hash": "a3f2c1d4e5b6...89f0",
      "domain": "SECURITY",
      "category": "ACCESS",
      "action": "PERMISSION_DENIED",
      "severity": "ERROR",
      "outcome": "DENIED",
      "actorType": "USER",
      "actorId": "clx...",
      "actorName": "John Doe",
      "actorIp": "192.168.1.100",
      "entityType": "TICKET",
      "entityId": "clx...",
      "entityName": "TKT-2026-000123",
      "description": "User attempted to delete ticket without permission",
      "containsPII": false
    },
    {
      "id": "clx...",
      "occurredAt": "2026-01-27T14:15:00Z",
      "sequence": "12344",
      "hash": "b4e3f2g5h6i7...12a3",
      "domain": "SECURITY",
      "category": "SSRF",
      "action": "SSRF_BLOCKED",
      "severity": "CRITICAL",
      "outcome": "DENIED",
      "actorType": "USER",
      "actorId": "clx...",
      "entityType": "CRONJOB",
      "entityId": "clx...",
      "description": "Webhook URL blocked: private IP 192.168.1.1",
      "containsPII": false
    }
  ],
  "pagination": {
    "total": 2,
    "limit": 50,
    "offset": 0,
    "hasMore": false
  }
}

Single Event with Details

GET /api/audit/enterprise/events/:id

Response (with changes & metadata)

{
  "success": true,
  "data": {
    "id": "clx...",
    "occurredAt": "2026-01-27T15:30:00Z",
    "ingestedAt": "2026-01-27T15:30:00.123Z",
    "sequence": "12345",
    "hash": "a3f2c1d4e5b6...89f0",
    "prevHash": "b4e3f2g5h6i7...12a3",
    "hashVersion": 1,
    "domain": "ENTITY",
    "category": "TICKET",
    "action": "UPDATE",
    "severity": "INFO",
    "outcome": "SUCCESS",
    "actorType": "USER",
    "actorId": "clx...",
    "actorName": "Jane Smith",
    "actorIp": "192.168.1.50",
    "actorUserAgent": "Mozilla/5.0...",
    "entityType": "TICKET",
    "entityId": "clx...",
    "entityName": "TKT-2026-000123: Printer not working",
    "changes": {
      "status": {
        "old": "OPEN",
        "new": "IN_PROGRESS"
      },
      "assignedAgentId": {
        "old": null,
        "new": "clx..."
      }
    },
    "metadata": {
      "method": "PATCH",
      "path": "/api/tickets/clx...",
      "durationMs": 45
    },
    "description": "Ticket status changed from OPEN to IN_PROGRESS",
    "containsPII": false,
    "redactedFields": [],
    "correlationId": "req-abc123",
    "sessionId": "ses-def456",
    "orgId": null
  }
}

Chain integrity: quick check and full verification

Chain integrity is established in two ways:

Reading the quick check

GET /api/audit/enterprise/statistics

{
  "chainStatus": {
    "intact": true,
    "message": "Chain intact up to sequence 125340"
  }
}

Result of the nightly full verification

The job verifies each chain in sections and aggregates them per chain. The summary is part of the run result:

{
  "action": "audit_chain_verify",
  "status": "success",
  "reason": "Chain verification clean (1 chain(s), 735568 events)",
  "metadata": {
    "windowSize": 100000,
    "truncated": false,
    "problems": [],
    "summary": [
      {
        "orgId": null,
        "totalEvents": 735568,
        "invalidEvents": 0,
        "purgedEvents": 0,
        "purgeWarnings": 0,
        "chainBroken": false,
        "anchorValid": true,
        "firstBrokenAt": null,
        "windows": 8
      }
    ]
  }
}

Why in sections: An audit trail grows into millions of events. The job therefore verifies section by section; the section size is the job parameter windowSize (default 100,000 events, allowed 1,000 to 250,000). The sections are joined without gaps: the last hash of one section is verified as the predecessor of the next. Splitting therefore does not weaken the check, it only keeps each individual step short — the entire trail is verified in one run.

Reporting a finding: Every run writes exactly one audit event SYSTEM/AUDIT/CHAIN_VERIFY carrying the summary of all chains — INFO when clean, WARNING when only partially verified, CRITICAL on a finding. On a break, an invalid anchor or a purge warning, every holder of audit.enterpriseView additionally receives a CRITICAL alert (at most once per day and recipient). This way tampering is not left unnoticed until someone next looks.

Get Statistics

GET /api/audit/enterprise/statistics

Response

{
  "totalEvents": 125340,
  "last30Days": {
    "byDomain": [
      { "domain": "AUTH", "count": 5234 },
      { "domain": "ENTITY", "count": 8921 },
      { "domain": "SECURITY", "count": 123 },
      { "domain": "SLA", "count": 456 }
    ],
    "bySeverity": [
      { "severity": "INFO", "count": 12000 },
      { "severity": "WARNING", "count": 1500 },
      { "severity": "ERROR", "count": 234 },
      { "severity": "CRITICAL", "count": 12 }
    ],
    "byOutcome": [
      { "outcome": "SUCCESS", "count": 13000 },
      { "outcome": "FAILURE", "count": 500 },
      { "outcome": "DENIED", "count": 246 }
    ]
  },
  "recentCriticalEvents": [
    {
      "id": "clx...",
      "occurredAt": "2026-01-27T22:45:00Z",
      "domain": "SECURITY",
      "category": "SSRF",
      "action": "SSRF_BLOCKED",
      "severity": "CRITICAL",
      "description": "Webhook URL blocked: private IP 10.0.0.1"
    }
  ],
  "chainStatus": {
    "intact": true,
    "message": "Chain intact up to sequence 125340"
  }
}

PII Scrubbing

The audit scrubber automatically removes sensitive data from changes and metadata: credentials AND personal fields are redacted and recorded in redactedFields:

Denylist (ALWAYS redacted):

• Credentials: password, secret, token, apikey, authorization
• Sessions: cookie, session, jwt, otp, pin
• Keys: private_key, encryption_key, signing_key, client_secret
• Financial: cvv, cvc, ssn
• Access: access_token, refresh_token, bearer

PII Keys (redacted + flagged in redactedFields):

• Contact: email, phone, address, street, city, zip
• Personal: birthdate, ssn, tax_id, national_id
• Financial: iban, bic, bank_account, credit_card
• Location: ip_address, gps, latitude, longitude

Value Pattern Detection:

• JWT-Tokens: eyJ... Pattern
• Bearer-Tokens: "Bearer xyz..."
• Base64-Secrets: > 100 characters
• API-Keys: 40+ alphanumeric chars
• AWS-Keys: AKIA... Pattern
• Private Keys: "-----BEGIN PRIVATE KEY-----"

Scrubbing Example

// Original Data:
{
  "username": "john.doe@company.com",
  "password": "SecretPassword123!",
  "email": "john.doe@company.com",
  "apiKey": "sk_live_51H3Kq2eZvKYlo2C...",
  "ticketNumber": "TKT-2026-000123"
}

// After Scrubbing:
{
  "username": "john.doe@company.com",
  "password": "[REDACTED]",           // ← Denylist
  "email": "[PII_REDACTED]",          // ← PII
  "apiKey": "[REDACTED]",             // ← Denylist
  "ticketNumber": "TKT-2026-000123"
}

// Stored in DB:
{
  "containsPII": true,                // ← event touched PII fields (redacted)
  "redactedFields": ["password", "email", "apiKey"],
  "changes": { ... scrubbed data ... }
}

A PII field’s value does not reach the log; redactedFields only records THAT the field changed. This is sufficient for forensic purposes and supports GDPR data minimization. entityName deliberately stays a displayable plain-text snapshot (for USER events the display NAME, not the email) — only what may be shown goes there. changes and metadata of a written event are not redacted afterwards (that would break the hash chain); they are cleared via the retention periods.

Retention: two-stage purge

Retention periods clash with immutability: simply deleting an event would tear the hash chain, and a single 7-year compliance event early in the chain would block deleting all later 90-day events. Therefore a due event has its content deleted while its hash link is preserved. The nightly retention_purge (see privacy page) works in two stages:

For chain verification: for a purged_at event the payload hash is not recomputed (the content is emptied — the stored hash still serves chain continuity). A purged_at on an event that is NOT yet due per policy is flagged as a WARNING — making it harder to abuse the purge flag as a tampering hideout. Each run also writes a self-event SYSTEM/AUDIT/RETENTION_PURGE with counts.

Hash Calculation (Detailed)

The hash is calculated by the PostgreSQL trigger and is identically reproducible:

Step 1: Create Canonical Payload

{
  "action": "CREATE_TICKET",
  "actorId": "clx-user-123",
  "actorType": "USER",
  "category": "TICKET",
  "changes": {"status": {"old": null, "new": "OPEN"}},
  "domain": "ENTITY",
  "entityId": "clx-ticket-456",
  "entityType": "TICKET",
  "hashVersion": 1,
  "metadata": {"method": "POST", "path": "/api/tickets"},
  "occurredAt": "2026-01-27T15:30:00.123Z",
  "orgId": "",
  "outcome": "SUCCESS",
  "prevHash": "def456...",
  "sequence": "12345"
}

Step 2: JSONB sorts keys alphabetically
→ deterministic serialization
Step 3: Create JSON string
payload_string = JSON.stringify(payload)

Step 4: SHA-256 Hash (UTF-8)
hash = sha256(payload_string, 'utf8')
     = "a3f2c1d4e5b6789f0..."

Step 5: Update ChainHead
UPDATE AuditChainHead
SET last_hash = "a3f2c1d4e5b6789f0...",
    last_sequence = 12345
WHERE org_id = '__global__'

Properties of the chain

Query Filtering

Filter Parameters

Example Queries

# All failed logins in the last 24h
GET /api/audit/enterprise/events?domain=AUTH&action=LOGIN&outcome=FAILURE&fromDate=2026-01-26T23:00:00Z

# All critical security events
GET /api/audit/enterprise/events?domain=SECURITY&severity=CRITICAL&sortBy=occurredAt&sortOrder=desc

# All changes to a specific ticket
GET /api/audit/enterprise/events?entityType=TICKET&entityId=clx-ticket-123&category=TICKET

# All actions by a user
GET /api/audit/enterprise/events?actorId=clx-user-456&fromDate=2026-01-01T00:00:00Z

# Full-text search
GET /api/audit/enterprise/events?search=password+reset&limit=20

Automatic logging of critical actions

Critical actions are logged automatically: permitted executions as SUCCESS, denied attempts as DENIED (with reason).

Critical actions (logged automatically)

Permissions

audit.enterpriseView is a critical action: the permission is revalidated straight from the database on every access instead of coming from the role cache. Revoking it therefore takes effect immediately rather than after the cache expires — which is exactly what matters for access to the audit trail.

The enterprise routes are not limited to signed-in users: API keys whose role carries the respective permission may read as well — for instance a SIEM collecting the trail on a schedule. Only the role matters, not the kind of caller.

Use Cases

Use Case 1: Compliance Audit

Auditor wants to see all ticket deletions from the last 90 days:

GET /api/audit/enterprise/events?domain=ENTITY&category=TICKET&action=DELETE&fromDate=2025-10-28T00:00:00Z&sortBy=occurredAt&sortOrder=desc

Use Case 2: Security Incident Investigation

Security team investigates suspicious activities of a user:

# 1. All actions by the user
GET /api/audit/enterprise/events?actorId=clx-suspicious-user&fromDate=2026-01-27T00:00:00Z

# 2. Failed permissions
GET /api/audit/enterprise/events?actorId=clx-suspicious-user&outcome=DENIED

# 3. All security events
GET /api/audit/enterprise/events?domain=SECURITY&fromDate=2026-01-27T00:00:00Z

Use Case 3: Forensic Analysis

A ticket was unexpectedly deleted - who did it?:

# 1. Find DELETE event
GET /api/audit/enterprise/events?entityType=TICKET&entityId=clx-ticket-123&action=DELETE

# Response shows:
{
  "actorId": "clx-user-789",
  "actorName": "Admin User",
  "actorIp": "192.168.1.50",
  "occurredAt": "2026-01-27T14:30:00Z",
  "metadata": {
    "method": "DELETE",
    "path": "/api/tickets/clx-ticket-123",
    "userAgent": "Mozilla/5.0..."
  }
}

# 2. All actions by this user at the same time
GET /api/audit/enterprise/events?actorId=clx-user-789&fromDate=2026-01-27T14:00:00Z&toDate=2026-01-27T15:00:00Z

Use Case 4: Chain Verification

Before a compliance review: establish the state of the audit chain:

# Quick check (chain head vs. last event)
GET /api/audit/enterprise/statistics

# Response (excerpt):
{
  "chainStatus": {
    "intact": true,
    "message": "Chain intact up to sequence 125340"
  }
}

# Full verification of every event: the nightly job. Its result per run
# (events, sections, findings per chain) is in the job history, and every
# run is itself recorded as SYSTEM/AUDIT/CHAIN_VERIFY:
GET /api/audit/enterprise/events?domain=SYSTEM&category=AUDIT&action=CHAIN_VERIFY&limit=30

The last thirty CHAIN_VERIFY events thus document without gaps that the chain was fully recomputed each night — scope and findings of each run are in that event’s metadata.

Database Schema

-- Audit event (immutable, append-only)
AuditEvent {
  id: String (UUID, PK)

  -- Chain fields (set automatically by the trigger)
  sequence: BigInt (UNIQUE, global, auto-increment)
  hash: String (64 chars, SHA-256 Hex)
  prevHash: String? (64 chars, hash of the predecessor)
  hashVersion: Int (Default: 1)

  -- Timestamps
  occurredAt: DateTime (when the action happened)
  ingestedAt: DateTime (when it was logged, Default: NOW())

  -- Classification
  domain: Enum (AUTH, ENTITY, ADMIN, SECURITY, SLA, WORKFLOW, SYSTEM, DATA_ACCESS, NOTIFICATION)
  category: String (sub-category, e.g. LOGIN, TICKET, SSRF)
  action: String (CREATE, UPDATE, DELETE, LOGIN_FAILED, etc.)
  actionDetail: String?
  severity: Enum (DEBUG, INFO, WARNING, ERROR, CRITICAL)

  -- Outcome
  outcome: Enum (SUCCESS, FAILURE, DENIED, PARTIAL, SKIPPED)
  errorCode: String?
  errorMessage: String?

  -- Actor
  actorType: Enum (USER, SYSTEM, API_KEY, WORKFLOW, CRONJOB, EXTERNAL)
  actorId: String? (user ID, job ID, etc.)
  actorName: String?
  actorIp: String?
  actorUserAgent: String?

  -- Entity
  entityType: String? (TICKET, INCIDENT, PROBLEM, ASSET, etc.)
  entityId: String?
  entityName: String?

  -- Data (JSONB)
  changes: JSON? (old vs. new values)
  metadata: JSON? (additional info)
  description: String?

  -- PII tracking
  containsPII: Boolean (Default: false)
  redactedFields: String[]? (array of redacted field paths)

  -- Retention
  purgedAt: DateTime? (set by the retention content purge)

  -- Correlation
  correlationId: String? (request ID)
  sessionId: String? (session ID)
  parentEventId: String? (FK AuditEvent)

  -- Multi-org
  orgId: String? (NULL = global/single-tenant)
}

-- Indices for performance
CREATE INDEX idx_audit_event_domain ON AuditEvent(domain, occurredAt DESC);
CREATE INDEX idx_audit_event_actor ON AuditEvent(actorId, occurredAt DESC);
CREATE INDEX idx_audit_event_entity ON AuditEvent(entityType, entityId, occurredAt DESC);
CREATE INDEX idx_audit_event_severity ON AuditEvent(severity, occurredAt DESC) WHERE severity IN ('ERROR', 'CRITICAL');
CREATE INDEX idx_audit_event_sequence ON AuditEvent(sequence DESC);

-- Immutability via trigger (no RLS):
--   audit_event_immutable() blocks every UPDATE except the retention
--   purge / erasure scrub under audit.allow_purge (see above),
--   DELETE only if current_setting('audit.allow_delete') = 'true'
CREATE TRIGGER audit_event_no_modify
  BEFORE UPDATE OR DELETE ON "AuditEvent"
  FOR EACH ROW EXECUTE FUNCTION audit_event_immutable();

-- Chain head (per organization)
AuditChainHead {
  org_id: String (PK, '__global__' for single-tenant)
  last_hash: String (hash of the last event)
  last_sequence: BigInt (sequence of the last event)
  purged_through_sequence: BigInt (Default: 0, purge anchor)
  purge_anchor_hash: String? (purge anchor)
  updated_at: DateTime
}

-- Updated by the trigger (with FOR UPDATE lock)
-- Enables O(1) quick check

Redis Fallback & Recovery

Routine events (INFO with outcome SUCCESS) are collected and written every 5 seconds; events from WARNING upwards as well as failed or denied operations (FAILURE/DENIED) are written immediately. Logging never blocks an API request. If the database is unreachable, events go to Redis (key audit:backup:events:{orgId}, kept for up to 7 days). On the next start they are written, and the recovery itself is logged as the event SYSTEM/AUDIT/RECOVER.

• Failover: events survive DB outages of up to 7 days
• Transparency: the recovery itself appears in the audit trail

Best Practices

Error Handling

Monitoring & Alerts

The chain is fully verified every night by the built-in job audit_chain_verify (see above); on a finding, all holders of audit.enterpriseView receive a CRITICAL alert. In addition, GET /api/audit/enterprise/statistics returns the quick check as chainStatus at any time, for example before an audit.

Compliance support

GDPR support

• ✅ PII redaction: automatic, always on
• ✅ PII Marking: containsPII flag + redactedFields list
• ✅ Data export logging: Every data export under Art. 15/20 is logged as a DATA_ACCESS event
• ✅ Retention: two-stage purge per policy (90 days / 365 days / 7 years), see above
• ✅ Right to erasure: when a user is anonymized, name, IP and user agent are cleared in their audit events (see Privacy & GDPR)

Support for ISO 27001 and SOC 2 audits

• ✅ Change protection: a DB trigger blocks later changes; tampering becomes detectable through the hash chain
• ✅ Integrity proof: SHA-256 chain with nightly full verification
• ✅ Access control: critical actions are logged, permitted as well as denied (SUCCESS + DENIED)
• ✅ Security Events: SSRF, brute force, permission denied
• ✅ Forensics: who, what, when and from where is traceable

Technical Details

Hash Algorithm

// JavaScript implementation (identical to the DB trigger)
function calculateHash(event) {
  // 1. Canonical payload
  const payload = {
    action: event.action,
    actorId: event.actorId || '',
    actorType: event.actorType,
    category: event.category,
    changes: event.changes || {},
    domain: event.domain,
    entityId: event.entityId || '',
    entityType: event.entityType || '',
    hashVersion: event.hashVersion,
    metadata: event.metadata || {},
    occurredAt: event.occurredAt.toISOString(),
    orgId: event.orgId || '',
    outcome: event.outcome,
    prevHash: event.prevHash || '',
    sequence: event.sequence.toString()
  };

  // 2. Sort keys (recursively)
  const sortedPayload = sortObjectKeys(payload);

  // 3. JSON string
  const payloadString = JSON.stringify(sortedPayload);

  // 4. SHA-256 hash
  return crypto
    .createHash('sha256')
    .update(payloadString, 'utf8')
    .digest('hex');
}

Sequence Assignment

-- PostgreSQL sequence (global, thread-safe)
CREATE SEQUENCE audit_event_seq START 1;

-- In the trigger:
v_sequence := nextval('audit_event_seq');
NEW."sequence" := v_sequence;

-- Properties:
-- - Uniqueness (no duplicates)
-- - Monotonically increasing
-- - Thread-safe (even with multi-instance)

Lock Mechanism

-- Lock ChainHead with FOR UPDATE (per org)
SELECT "last_hash", "last_sequence"
FROM "AuditChainHead"
WHERE "org_id" = v_org_key
FOR UPDATE;

-- Prevents:
-- - Race conditions (2 events at once)
-- - Chain inconsistency (prevHash conflicts)
-- - Sequence duplicates

-- Lock duration: Only during INSERT (< 1ms)