EHQ Brain

ADR-003 Audit Logging Strategy

2 min read

ADR-003: Audit Logging Strategy

Status: Accepted Owner: @bilal @deen Date: 2025-12-06

Context

Envo needs comprehensive audit logging for accountability, debugging, GDPR compliance, and security monitoring (especially Envo support access to customer data).

Requirements

  • Log all writes on sensitive tables
  • Log READ access for Envo support users only
  • Capture user identity, old/new values, timestamp
  • Retain logs for 2 years
  • Configurable granularity per table
  • Abstracted for future externalisation

Decision

Custom audit.log table with triggers, designed for future migration to external audit services (Immuta, Drata).

Design Principles

  1. Separate schema — audit schema, not public
  2. Abstracted interface — Application uses AuditLogger interface
  3. Configurable per table — Three levels: full, standard, minimal
  4. Partitioned by month — Efficient 2-year retention via partition drops
  5. Future-ready — Schema designed for easy export

Schema

CREATE SCHEMA IF NOT EXISTS audit;
 
CREATE TABLE audit.log (
    id              UUID PRIMARY KEY,
    table_name      TEXT NOT NULL,
    record_id       UUID NOT NULL,
    action          TEXT NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE', 'READ')),
    organisation_id UUID,
    user_id         UUID,
    user_role       TEXT,
    old_values      JSONB,
    new_values      JSONB,
    changed_fields  TEXT[],
    request_id      UUID,
    ip_address      INET,
    user_agent      TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

Audit Levels

LevelWhat’s LoggedTables
FullAll INSERT, UPDATE (with diff), DELETEissues, user_organisation_access, tenants
StandardINSERT, DELETE, selected UPDATEsproperties, vendors, organisations
MinimalDELETE onlyattachments, vendor_ratings
NoneNothingevents, audit.log

Implementation

  • Write logging: Database triggers (automatic, no developer discipline needed)
  • Read logging: Application layer middleware (for envo_support users only)
  • Retention: 2 years, monthly partitions dropped via cron/pg_partman

Abstraction Layer

interface AuditLogger {
  logWrite(params: WriteAuditParams): Promise<void>
  logRead(params: ReadAuditParams): Promise<void>
  getAuditHistory(params: QueryParams): Promise<AuditEntry[]>
}

Can be backed by PostgreSQL (current) or external service (future).

Future Considerations

  • External audit stores: Immuta, Drata, AWS CloudTrail + S3, TimescaleDB
  • Hash chaining for tamper evidence (blockchain-like, deferred)
  • Field-level encryption for sensitive PII

Consequences

Positive

  • Complete audit trail for sensitive tables
  • Queryable history for debugging and compliance
  • Envo support access is transparent and logged
  • Partitioning enables efficient retention management

Negative

  • Storage overhead (~10-20% of main data size)
  • Trigger overhead on writes (~1-5ms per operation)
  • Monthly partition management required

Graph View

Backlinks