Audit Log

Immutable audit trail captured at the PostgreSQL level. Every INSERT, UPDATE, and DELETE on every entity table is recorded automatically. No code required, no way to bypass.

How it works

The Core installs an AFTER INSERT OR UPDATE OR DELETE trigger on every entity table when it is created. The trigger calls rootcx_system.audit_trigger_fn(), which writes a structured entry to rootcx_system.audit_log within the same transaction.

If the data changes, the audit entry is written. If the transaction rolls back, the audit entry rolls back with it. No phantom records.

The trigger function runs with SECURITY DEFINER, meaning it executes with the privileges of the function owner regardless of who triggers it. This ensures audit entries cannot be blocked by row-level security or permission issues.

What gets captured

Every mutation on every entity table:

The record_id is extracted from the row's id column (the primary key).

Attribution (who did this, on whose authority)

Every mutation also captures the actor chain:

This answers the 3 questions during an incident: who performed the action (actor), on whose authority (delegator), and how (trigger).

Log schema

CREATE TABLE rootcx_system.audit_log (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_schema  TEXT NOT NULL,
    table_name    TEXT NOT NULL,
    record_id     TEXT,
    operation     TEXT NOT NULL,
    old_record    JSONB,
    new_record    JSONB,
    actor_uid     UUID,
    delegator_uid UUID,
    trigger_ref   TEXT,
    changed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

Indexes

Query endpoint

GET /api/v1/audit

Requires authentication. Returns an array of audit entries.

Filter parameters

Operator suffixes

Append suffixes to filter parameters for advanced filtering:

Pagination

Example

GET /api/v1/audit?app_id=crm&entity=contacts&operation=UPDATE&limit=50

[
  {
    "id": 1001,
    "table_schema": "crm",
    "table_name": "contacts",
    "record_id": "a1b2c3d4-...",
    "operation": "UPDATE",
    "old_record": { "id": "a1b2c3d4-...", "status": "lead" },
    "new_record": { "id": "a1b2c3d4-...", "status": "customer" },
    "changed_at": "2024-12-01T10:30:00+00:00"
  }
]

Trigger installation

Triggers are installed automatically when a table is created during manifest deployment. The Core calls rootcx_system.enable_tracking('{schema}.{table}'::regclass) which creates an AFTER INSERT OR UPDATE OR DELETE FOR EACH ROW trigger.

You can also manage tracking manually:

-- Enable tracking on a table
SELECT rootcx_system.enable_tracking('crm.contacts'::regclass);

-- Disable tracking on a table
SELECT rootcx_system.disable_tracking('crm.contacts'::regclass);

Immutability

Audit log entries cannot be deleted, modified, or truncated via the API. There is no DELETE or UPDATE endpoint for the audit log. The only way to remove entries is direct database access (which itself would be captured by any PostgreSQL audit extension at the infrastructure level).

Retention

Audit logs are not automatically purged. The table grows indefinitely. For high-volume production deployments, consider partitioning rootcx_system.audit_log by month or implementing a retention policy at the infrastructure level.

Technical details

API endpoints summary