Skip to content

title: Enterprise Audit Logging Documentation description: An append-only audit_events table whose rows PostgreSQL hashes and signs into a chain, and AuditLogger to write them keywords: [] tags: ["documentation", "reference"]


Enterprise Audit Logging Documentation

Topic: Audit Event Logging & Chain Verification


Overview

fraiseql.enterprise ships an audit trail kept in PostgreSQL:

  • audit_events: one row per event (event_type, event_data JSONB, user_id, tenant_id, timestamp, ip_address).
  • A hash chain built by PostgreSQL: a BEFORE INSERT trigger sets each row's previous_hash (the latest event_hash of the same tenant), its event_hash (SHA-256 over the previous hash and every column of the event: id, event_type, event_data, user_id, tenant_id, timestamp, ip_address, created_at), its hash_version and its signature (HMAC-SHA256 of the hash with the active key of audit_signing_keys).
  • verify_audit_chain(): an SQL function that recomputes the hashes.
  • AuditLogger: a small Python class that inserts events, one at a time or in batches, through a FraiseQLRepository.

Nothing logs events on its own: your code calls AuditLogger.log_event (or inserts into audit_events) where something should be audited.


Setup

The schema is in migrations/001_audit_tables.sql of the installed fraiseql.enterprise package. FraiseQL doesn't apply it: run it once per database, as part of your migrations.

SQL=$(python -c "import fraiseql.enterprise, pathlib; print(pathlib.Path(fraiseql.enterprise.__file__).parent / 'migrations' / '001_audit_tables.sql')")
psql "$DATABASE_URL" -f "$SQL"

Then, in SQL:

-- The signature needs an active signing key; without one an insert fails with
-- "No active signing key available". Keep the real key in a secrets store.
INSERT INTO audit_signing_keys (key_value) VALUES ('replace-with-a-secret-key');

The file needs the pgcrypto extension (it runs CREATE EXTENSION IF NOT EXISTS pgcrypto). It can be run again: it keeps the rows and replaces the functions.

audit_events is a plain table, not partitioned. Before 1.31.0 the file also installed a create_audit_partition_trigger that made every insert fail ("cannot CREATE TABLE .. PARTITION OF"); running the 1.31.0 file again drops it. To partition the table by month, create it partitioned yourself before running the file (PARTITION BY RANGE (timestamp), with timestamp in the primary key) and create the partitions ahead of time, with pg_partman or a scheduled job: an insert trigger can't add the partition its own row goes into.


Logging Events

from fraiseql.enterprise.audit.event_logger import AuditLogger

# repo: a FraiseQLRepository, e.g. info.context["db"] in a resolver
audit_logger = AuditLogger(repo, batch_size=100)

# Written at once; returns the event id (a UUID)
event_id = await audit_logger.log_event(
    "user.updated",
    {"user_id": "u-42", "changed_fields": ["email"]},
    user_id=None,  # a UUID string, or None
    tenant_id="11111111-1111-1111-1111-111111111111",
    ip_address="203.0.113.1",
)

# Batched: kept in memory until batch_size events are queued or flush_batch() runs
await audit_logger.log_event("user.login", {"method": "password"}, immediate=False)
await audit_logger.flush_batch()  # writes the batch in one transaction

log_event(event_type, event_data, user_id=None, tenant_id=None, ip_address=None, immediate=True) takes any event_type string; there is no fixed list. user_id and tenant_id are UUID columns, ip_address an INET. Queued events are lost if the process stops before flush_batch(), so call it at shutdown.

In a resolver:

import fraiseql
from fraiseql.enterprise.audit.event_logger import AuditLogger


@fraiseql.query
async def audited_ping(info) -> str:
    await AuditLogger(info.context["db"]).log_event(
        "ping.called",
        {"source": "graphql"},
        tenant_id=info.context.get("tenant_id"),
    )
    return "pong"

Reading Events

Read the table with SQL (db.run(DatabaseQuery(...)), or any PostgreSQL client):

-- Recent events of a tenant
SELECT id, event_type, event_data, user_id, timestamp, ip_address
FROM audit_events
WHERE tenant_id = '11111111-1111-1111-1111-111111111111'
ORDER BY timestamp DESC
LIMIT 100;

-- Logins from one address
SELECT timestamp, user_id, event_data
FROM audit_events
WHERE event_type = 'user.login' AND ip_address = '203.0.113.1'
ORDER BY timestamp DESC;

Verifying the Chain

SELECT event_id, event_timestamp, tenant_id, chain_valid, expected_hash, actual_hash
FROM verify_audit_chain();                       -- or verify_audit_chain(start, end)

-- Only the problems
SELECT * FROM verify_audit_chain() WHERE NOT chain_valid;

-- One tenant
SELECT * FROM verify_audit_chain()
WHERE tenant_id = '11111111-1111-1111-1111-111111111111';

verify_audit_chain(start_timestamp, end_timestamp) checks each tenant's chain on its own, as the trigger builds them (events without a tenant form one more chain): it walks a tenant's events in time order and recomputes each event_hash from the tenant's event before it. With a start_timestamp, a tenant's first event in the range is checked against that tenant's latest event before the range. An event whose data was changed is reported invalid; so is the event after a deleted one. It doesn't check the signatures. It returns the events tenant by tenant; add ORDER BY event_timestamp for a single timeline.

Each event is checked with the hash its hash_version column names, also returned by verify_audit_chain():

  • Version 2 (events written by 1.31.1 and later) covers every column listed above, with the timestamps in UTC.
  • Version 1 (events written before 1.31.1) covers event_data only: a change to event_type, user_id, tenant_id, timestamp or ip_address of such an event isn't detected.

To upgrade an existing database, run the 1.31.1 001_audit_tables.sql again. It adds hash_version (existing rows get 1 and keep verifying as version 1) and replaces the trigger and verify_audit_chain(), so every event written afterwards is version 2. Existing rows aren't rehashed: that would make whatever they hold now look intact. The trigger always writes the current version, whatever the INSERT says.

Before 1.31.0 it walked all tenants as one chain, and reported valid events invalid as soon as there was more than one tenant; running the 1.31.0 001_audit_tables.sql again replaces it (the tenant_id column is new).


Immutability

The file creates an audit_events_insert_only policy (USING (false), WITH CHECK (true)), which only takes effect once row-level security is enabled on the table (ALTER TABLE audit_events ENABLE ROW LEVEL SECURITY), and then hides the rows from SELECT too for roles it applies to. A table owner or superuser bypasses it. To make the table append-only for the application, grant its role INSERT (and SELECT if it reads the events) but not UPDATE or DELETE.


GraphQL Types

FraiseQL exposes no audit queries or mutations: who may read which tenant's audit trail is your application's decision. fraiseql.enterprise.audit.types has an AuditEvent GraphQL type (with AuditEvent.from_db_row(row)) you can return from your own resolvers:

import fraiseql
from fraiseql.db import DatabaseQuery
from fraiseql.enterprise.audit.types import AuditEvent


@fraiseql.query
async def audit_trail(info, limit: int = 50) -> list[AuditEvent]:
    # Check here that the caller may read this tenant's audit trail.
    rows = await info.context["db"].run(
        DatabaseQuery(
            statement="SELECT * FROM audit_events WHERE tenant_id = %(tenant_id)s "
            "ORDER BY timestamp DESC LIMIT %(limit)s",
            params={"tenant_id": info.context["tenant_id"], "limit": limit},
            fetch_result=True,
        )
    )
    return [AuditEvent.from_db_row(row) for row in rows]

AuditEvent has no sql_source, so the where, orderBy and offset arguments every list query gets aren't applied: the resolver doesn't take them, and FraiseQL logs a warning saying so at startup. Add the parameters you want to support to the resolver.

fraiseql.enterprise.audit.queries.AuditQueries is deprecated (importing it warns) and will be removed: it was a placeholder whose fields weren't exposed, with audit_events returning an empty connection and verify_audit_chain returning true. The LogAuditEvent placeholder mutation (fraiseql.enterprise.audit.mutations, which couldn't be imported) was removed in 1.31.0; log events with AuditLogger.


Bridging an Existing tb_audit_log

The file also defines bridge_audit_to_chain(), a trigger function that copies each row inserted into a tb_audit_log table (with pk_audit_log, operation_type, entity_type, old_data, new_data, pk_organization, ... columns) into audit_events. It installs no trigger; to use it:

CREATE TRIGGER bridge_to_cryptographic_audit
    AFTER INSERT ON tenant.tb_audit_log
    FOR EACH ROW
    EXECUTE FUNCTION bridge_audit_to_chain();

migrations/002_unified_audit.sql describes another audit_events layout (CDC columns, tenant_id NOT NULL, log_and_return_mutation()). It is not compatible with AuditLogger, which writes the columns of 001_audit_tables.sql: apply one of the two. Its chain has the same hash_version column and upgrade path: version 2 covers every column, version 1 (events written before 1.31.1) left out id, user_id, ip_address, correlation_id and created_at, and hashed the timestamp in the inserting session's TimeZone, so verify version 1 events in that time zone.


See Also