Audit & Notifications

The system provides two complementary audit mechanisms and a real-time notification layer built entirely on PostgreSQL. User events track security-related activity (logins, lockouts, permission changes). The journal provides general-purpose structured logging with templated messages and multi-key entity references. Real-time notifications use PostgreSQL's LISTEN/NOTIFY to push permission change events to backends immediately after commit.

All three mechanisms share the same design principles: monthly range-partitioned storage, correlation ID tracing, request context capture, and configurable retention.

---

Journal

The public.journal table is the general-purpose audit log. It stores structured entries for any event in the system -- user lifecycle, permission changes, group operations, API key activity, and application-specific events. Applications can register their own event codes and create journal entries alongside the core ones.

Table Structure

Column	Type	Description
journal_id	bigint	Auto-generated primary key (part of composite PK with created_at)
created_at	timestamptz	Timestamp of the entry; partition key
created_by	text	Identifier of the user or system that created the entry
correlation_id	text	Optional correlation ID for tracing a request across systems
tenant_id	integer	Tenant the entry belongs to
event_id	integer	Foreign key to const.event_code; identifies what happened
user_id	bigint	The user who performed the action
keys	jsonb	Entity references, e.g. {"order": 3, "item": 5}
data_payload	jsonb	Template values and extra data, e.g. {"username": "john"}
request_context	jsonb	Caller metadata (IP address, user agent, origin, device ID, etc.)

The table is range-partitioned by created_at on a monthly basis. Partitions follow the naming pattern journal_YYYY_MM (e.g. journal_2026_03). A default partition (journal_default) serves as a safety net for rows with unexpected timestamps.

Event Codes and Message Templates

Journal entries reference an event_id from the const.event_code table. Each event code belongs to a category, has a unique text code name, and a human-readable title. Message templates in const.event_message support placeholder substitution using values from the journal's keys and data_payload columns.

-- Event code structure
-- const.event_code: event_id, code, category_code, title, description, is_read_only, is_system

-- Example template: 'User "{username}" created'
-- With payload: {"username": "john"}
-- Result: 'User "john" created'

See Error & Event Codes for the complete list of event codes and categories.

Event Categories

Each event code belongs to a category stored in const.event_category:

Category	Description
user_event	User lifecycle, login, password changes
tenant_event	Tenant management
permission_event	Permission assignments
group_event	Group membership changes
apikey_event	API key operations
token_event	Token lifecycle
provider_event	Provider lifecycle
maintenance_event	System maintenance operations
resource_event	Resource access (ACL) operations
language_event	Language CRUD
translation_event	Translation CRUD and copy operations

Creating Journal Entries

-- Using event code name (recommended)
select * from create_journal_message(
    'admin',           -- created_by
    1,                 -- user_id
    'user_created',    -- event code (text)
    'New user registered',
    _keys := '{"user": 123, "tenant": 1}'::jsonb
);

-- Single entity convenience
select * from create_journal_message(
    'admin', 1, 'group_created', 'Group created',
    'group', 456  -- entity_type, entity_id
);

-- With request context
select * from create_journal_message_for_entity(
    'admin', 1, 'corr-123',
    10001, 'user', 123,
    '{"username": "john"}'::jsonb,
    1,
    _request_context := '{"ip_address": "192.168.1.1", "user_agent": "Mozilla/5.0"}'::jsonb
);

Searching the Journal

-- Search by event category
select * from search_journal(
    _user_id := 1,
    _event_category := 'user_event'
);

-- Search by entity keys
select * from search_journal(
    _user_id := 1,
    _keys_criteria := '{"order": 3}'::jsonb
);

-- Full-text search with filters
select * from search_journal(
    _user_id := 1,
    _search_text := 'password',
    _event_category := 'user_event',
    _from := now() - interval '7 days'
);

Storage Modes

For large deployments where storing all journal data in PostgreSQL is impractical, the system supports offloading to an external store (e.g. ClickHouse) via LISTEN/NOTIFY. The storage mode is configured independently for journal and user events through system parameters:

Mode	Behavior
local	INSERT into PostgreSQL only (default)
notify	Fire pg_notify only, skip INSERT -- an external listener captures the data
both	INSERT into PostgreSQL AND fire pg_notify

-- Switch journal to notify-only (stop storing in PostgreSQL)
select auth.update_sys_param(1, 'journal', 'storage_mode', 'notify');

-- Switch to both (store locally + notify external)
select auth.update_sys_param(1, 'journal', 'storage_mode', 'both');

When mode is notify or both, entries are sent on the journal_events channel. The payload includes all journal fields as JSON. If the payload exceeds approximately 7900 bytes (approaching pg_notify's 8000 byte hard limit), large fields (data_payload, request_context) are stripped and "truncated": true is added.

Warning

When mode is notify, search and query functions (search_journal, get_journal_entry, etc.) return empty results because data is not stored in PostgreSQL. The application should query the external store directly.

---

User Events

The auth.user_event table is a security-focused audit trail. While the journal covers all operations, user events specifically track who did what to whom in the authentication and authorization domain.

Table Structure

Column	Type	Description
user_event_id	bigint	Auto-generated (part of composite PK with created_at)
created_at	timestamptz	Timestamp; partition key
created_by	text	Identifier of the system or user that created the event
correlation_id	text	Optional correlation ID for cross-system tracing
event_type_code	text	What happened (e.g. 'user_logged_in', 'user_locked')
requester_user_id	bigint	The user who initiated the action
target_user_id	bigint	The user the action was performed on
requester_username	text	Username of the requester (denormalized for audit readability)
target_user_oid	text	Target user's OID from the identity provider
target_username	text	Target user's username (auto-resolved if not provided)
request_context	jsonb	Caller metadata (IP address, user agent, etc.)
event_data	jsonb	Additional event-specific data

Like the journal, auth.user_event is range-partitioned by created_at on a monthly basis. Partitions follow the naming pattern user_event_YYYY_MM. A default partition (user_event_default) catches rows outside any defined range.

Key Indexes

• ix_user_event_data -- GIN index on event_data for JSONB path queries
• ix_user_event_correlation_id -- B-tree on correlation_id (partial: non-null only)
• ix_user_event_created -- Descending index on created_at for reverse-chronological queries
• ix_user_event_target_user -- Composite index on (target_user_id, created_at desc) for per-user audit trails
• ix_user_event_request_context -- GIN index on request_context (partial: non-null only)

Request Context

All security-relevant functions accept an optional _request_context jsonb parameter. This stores caller metadata alongside the event record without requiring schema changes when new fields are needed:

select auth.enable_user('admin', 1, 'corr-123', 42,
    _request_context := jsonb_build_object(
        'ip_address', '192.168.1.1',
        'user_agent', 'Mozilla/5.0',
        'origin', 'https://app.example.com',
        'device_id', 'abc-123'
    ));

-- Context is queryable with standard jsonb operators
select request_context ->> 'ip_address' as ip
from auth.user_event
where target_user_id = 42;

Audit Summary Functions

Higher-level query functions combine journal and user event data for common audit needs:

-- Unified audit trail for a specific user (journal + user_event)
select * from auth.get_user_audit_trail(
    _user_id := 1,
    _target_user_id := 1001,
    _from := now() - interval '30 days',
    _page := 1,
    _page_size := 20
);

-- Security events across the system (failed logins, lockouts, permission denials)
select * from auth.get_security_events(
    _user_id := 1,
    _from := now() - interval '7 days'
);

Storage Modes

User events support the same storage mode mechanism as the journal, controlled via the user_event.storage_mode system parameter. When mode is notify or both, events are sent on the user_events channel (separate from both journal_events and permission_changes).

-- Switch user events to both
select auth.update_sys_param(1, 'user_event', 'storage_mode', 'both');

---

Real-Time Notifications

The system sends real-time notifications via PostgreSQL's LISTEN/NOTIFY on the permission_changes channel whenever a permission-relevant mutation occurs. Backends listen on this channel and push "refetch permissions" events to affected clients via SSE or WebSocket.

Notification Flow

sequenceDiagram
    participant App as Application
    participant PG as PostgreSQL
    participant Trigger as Trigger Function
    participant Backend as Backend Listener
    participant Client as Client (Browser)

    App->>PG: Mutation (e.g. assign permission)
    PG->>Trigger: AFTER trigger fires
    Trigger->>PG: pg_notify('permission_changes', JSON)
    Note over PG: Notification delivered<br/>only after COMMIT
    PG-->>Backend: NOTIFY payload
    Backend->>Backend: Debounce 100-200ms
    Backend->>Backend: Resolve affected users<br/>(query resolution view)
    Backend->>Client: SSE/WebSocket:<br/>REFETCH_PERMISSIONS
    Client->>PG: Re-fetch permissions

Payload Format

All notifications on the permission_changes channel share this structure:

{
    "event": "group_member_added",
    "tenant_id": 1,
    "target_type": "user",
    "target_id": 42,
    "detail": { "group_id": 7 },
    "at": "2026-02-20T14:30:00Z"
}

Field	Description
event	What happened (e.g. permission_assigned, group_disabled)
tenant_id	Affected tenant; null means all tenants
target_type	One of: "user", "group", "perm_set", "system", "provider", "tenant", "api_key"
target_id	ID of the affected entity; null for provider events
detail	Optional extra context (group_id, perm_set_id, provider_code, etc.)
at	Timestamp of the event

Payload size limit

pg_notify has a hard limit of 8000 bytes. Only IDs are sent in the payload, never full data. The notification function (unsecure.notify_permission_change) builds the JSON payload from the entity IDs available in the trigger context.

Notification Events by Category

All notification triggers are defined in 033_triggers_cache_and_notify.sql. Each trigger function calls unsecure.notify_permission_change() which builds the JSON payload and fires pg_notify('permission_changes', ...).

Permission Assignment

Event	target_type	target_id	detail
permission_assigned	"user" or "group"	user_id or group_id	{ perm_set_id, permission_id }
permission_unassigned	"user" or "group"	user_id or group_id	{ perm_set_id, permission_id }

Permission Set Content

Event	target_type	target_id	detail
perm_set_permissions_added	"perm_set"	perm_set_id	{ permission_id }
perm_set_permissions_removed	"perm_set"	perm_set_id	{ permission_id }
perm_set_updated	"perm_set"	perm_set_id	{ is_assignable }

Group Membership

Event	target_type	target_id	detail
group_member_added	"user"	user_id	{ group_id }
group_member_removed	"user"	user_id	{ group_id }

Group Status

Event	target_type	target_id	detail
group_enabled	"group"	group_id	--
group_disabled	"group"	group_id	--
group_deleted	"group"	group_id	--
group_type_changed	"group"	group_id	{ is_external }

Group Mappings

Event	target_type	target_id	detail
group_mapping_created	"group"	group_id	{ provider_code }
group_mapping_deleted	"group"	group_id	{ provider_code }

User Status

Event	target_type	target_id	detail
user_disabled	"user"	user_id	--
user_enabled	"user"	user_id	--
user_locked	"user"	user_id	--
user_unlocked	"user"	user_id	--
user_deleted	"user"	user_id	--

Ownership

Event	target_type	target_id	detail
owner_created	"user"	user_id	{ scope: "tenant"/"group", user_group_id }
owner_deleted	"user"	user_id	{ scope: "tenant"/"group", user_group_id }

Permission Tree

Event	target_type	target_id	detail
permission_assignability_changed	"system"	permission_id	{ full_code, is_assignable }

Provider

Event	target_type	target_id	detail
provider_enabled	"provider"	null	{ provider_code }
provider_disabled	"provider"	null	{ provider_code }
provider_deleted	"provider"	null	{ provider_code }

Tenant

Event	target_type	target_id	detail
tenant_deleted	"tenant"	tenant_id	--

API Keys

Event	target_type	target_id	detail
api_key_created	"api_key"	api_key_id	{ api_key }
api_key_deleted	"api_key"	api_key_id	{ api_key }

Backend Integration Pattern

The backend needs a dedicated PostgreSQL connection (not from the connection pool) to listen for notifications. PgBouncer in transaction mode does not support LISTEN.

// Dedicated connection (not from pool)
await client.query('LISTEN permission_changes');

client.on('notification', (msg) => {
    const payload = JSON.parse(msg.payload);
    // payload: { event, tenant_id, target_type, target_id, detail, at }
    resolveAndBroadcast(payload);
});

Debouncing: Bulk operations (e.g. process_external_group_member_sync) fire one trigger per row, but pg_notify batches all notifications and delivers them at COMMIT. The backend receives them all at once and should:

1. Collect notifications for 100--200ms
2. Deduplicate by (tenant_id, target_type, target_id)
3. Send one REFETCH_PERMISSIONS event per affected user

Reconnection: If the LISTEN connection drops, reconnect and re-issue LISTEN permission_changes. Notifications sent during the gap are lost (pg_notify is fire-and-forget). Optionally trigger a full permission refetch for all connected clients as a safety net. Cache invalidation on the database side is the correctness mechanism -- notifications are an optimization for client freshness.

Tip

Notifications are delivered only after COMMIT and never for rolled-back transactions. This means backends never react to mutations that were later aborted.

NOTIFY Channels Summary

The system uses three separate channels for different purposes:

Channel	Purpose	Storage mode controlled
permission_changes	Real-time permission mutation events	No (always fires)
journal_events	Journal entries offloaded to external store	Yes (journal.storage_mode)
user_events	User events offloaded to external store	Yes (user_event.storage_mode)

---

Resolution Views

Notifications carry IDs, not user lists. After receiving a notification, the backend queries a resolution view to determine which users are affected and need to refetch their permissions.

View	Key Column	Used For
auth.notify_group_users	user_group_id	Group status, group mapping, and permission assignment (to group) events
auth.notify_perm_set_users	perm_set_id	Permission set content changes and perm set updates
auth.notify_permission_users	permission_id	Permission assignability changes
auth.notify_provider_users	provider_code	Provider enable/disable events
auth.notify_tenant_users	tenant_id	Tenant deletion events

-- Group event: which users to notify?
select user_id from auth.notify_group_users where user_group_id = $1;

-- Perm set changed: which users to notify?
select user_id from auth.notify_perm_set_users where perm_set_id = $1;

-- Permission assignability changed: which users to notify?
select user_id from auth.notify_permission_users where permission_id = $1;

-- Provider disabled: which users to notify?
select user_id from auth.notify_provider_users where provider_code = $1;

For target_type = "user" events (user status changes, group member add/remove, owner changes), the target_id is the user_id directly -- no view query is needed.

Backend Routing Logic

The routing depends on the target_type in the notification payload:

target_type	Live Events	Delete Events
"user"	Direct from payload target_id	Same
"group"	Query notify_group_users	In-memory lookup (cascade already removed rows)
"perm_set"	Query notify_perm_set_users	N/A
"system"	Query notify_permission_users	N/A
"provider"	Query notify_provider_users	In-memory lookup (cascade already removed rows)
"tenant"	Query notify_tenant_users	In-memory lookup (cascade already removed rows)
"api_key"	Service-level handling	Same

Delete events and cascading

Delete events (group_deleted, provider_deleted, tenant_deleted) fire after COMMIT, meaning the cascade has already removed the rows from the resolution views by the time the backend receives the notification. The backend must use its own in-memory map of connected users and their tenant/group/provider memberships to resolve affected users for these events.

How Resolution Views Work

The notify_perm_set_users view illustrates how the system traces from an entity back to affected users. It resolves through both direct user assignments and group membership:

-- Users with this perm_set directly assigned
select pa.perm_set_id, pa.tenant_id, pa.user_id
from auth.permission_assignment pa
where pa.perm_set_id is not null and pa.user_id is not null

union

-- Users in groups that have this perm_set assigned
select pa.perm_set_id, pa.tenant_id, ugm.user_id
from auth.permission_assignment pa
    inner join auth.user_group_member ugm
        on ugm.user_group_id = pa.user_group_id
where pa.perm_set_id is not null;

The notify_permission_users view is more complex, resolving through four paths: direct permission assignment, group permission assignment, direct perm set assignment, and group perm set assignment.

---

Data Retention

Both public.journal and auth.user_event are range-partitioned by month. This partitioning strategy enables efficient data retention management.

Benefits of Monthly Partitioning

• Partition pruning -- date-filtered queries only scan relevant monthly partitions
• Instant purge -- old partitions are detached and dropped instead of row-by-row DELETE
• INSERT performance -- writes target the current month's partition (smaller indexes)

Retention Configuration

Retention periods are configured via system parameters:

Parameter	Default	Description
journal.retention_days	365	How many days of journal entries to keep
user_event.retention_days	365	How many days of user events to keep
partition.months_ahead	3	How many future monthly partitions to pre-create

Purging Old Data

The public.purge_audit_data function handles retention for both tables in a single call. It requires the journal.purge_journal permission.

-- Purge data older than configured retention (uses sys_param values)
select * from public.purge_audit_data('admin', 1, null);

-- Purge with explicit retention override
select * from public.purge_audit_data('admin', 1, null, _older_than_days := 90);

The purge process for each table:

1. Drop old partitions -- detach and drop monthly partitions older than the cutoff month
2. Clean default partition -- delete rows from the default partition that are older than the retention period
3. Pre-create future partitions -- call unsecure.ensure_audit_partitions() to create upcoming monthly partitions

The purge itself is journaled as event code 17001 (audit_data_purged) for accountability, recording the number of deleted journal entries and user events.

Manual Partition Management

Future partitions can be pre-created manually without running a purge:

-- Pre-create future partitions (uses partition.months_ahead from sys_param)
select unsecure.ensure_audit_partitions();

-- Pre-create with explicit month count
select unsecure.ensure_audit_partitions(6);

This function creates monthly partitions for both public.journal and auth.user_event tables. It is also called automatically at the end of each purge operation.

---

Correlation IDs

Both journal entries and user events support a correlation_id field. This allows tracing a single request across multiple audit records and even across systems. All security-relevant functions accept a _correlation_id parameter that is threaded through to journal entries and user events.

-- The same correlation ID appears in both journal and user_event records
-- created by a single operation
select * from search_journal(
    _user_id := 1,
    _correlation_id := 'req-abc-123'
);

select * from auth.search_user_events(
    _user_id := 1,
    _correlation_id := 'req-abc-123'
);

Both tables have partial indexes on correlation_id (filtering out nulls) for efficient lookups.