Audit & Event Functions¶

Functions for audit logging, event tracking, and journal management. The system provides two complementary audit mechanisms: user events for security-related activity (logins, lockouts, permission changes) and the journal for general-purpose structured logging with templated messages.

Source: 028_functions_auth_event.sql, 018_functions_public.sql

See also: Audit & Notifications concept page for an overview of storage modes, notification channels, and retention strategies.

User Event Creation¶

`auth.create_user_event`¶

Creates a user event audit record. This is a thin wrapper around unsecure.create_user_event -- it delegates all logic (username resolution, storage mode handling, pg_notify) to the unsecure layer.

Parameter	Type	Default	Description
`_created_by`	text	--	Identifier of the user or system performing the action
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	--	Correlation ID for tracing across systems
`_event_type_code`	text	--	Event type code (e.g. `'user_logged_in'`, `'user_locked'`, `'change_password'`)
`_target_user_id`	bigint	--	ID of the user the event is about
`_request_context`	jsonb	`null`	Arbitrary request context (IP address, user agent, etc.)
`_event_data`	jsonb	`null`	Additional event-specific data
`_target_user_oid`	text	`null`	Target user's OID from the identity provider
`_target_username`	text	`null`	Target user's username (auto-resolved from `_target_user_id` if not provided)

Returns: table(___user_event_id bigint) -- the newly created event's ID.

Permission required: None (called internally by other permission-checked functions)

Source: 028_functions_auth_event.sql:13

Note

The underlying unsecure.create_user_event respects the user_event.storage_mode system parameter. When set to 'notify', events are sent via pg_notify on the user_events channel instead of being inserted into the table. When set to 'both', events are both stored and notified.

User Event Queries¶

`auth.search_user_events`¶

Paginated search across user events with optional filters. Supports filtering by event type, target user, request context (JSONB containment), correlation ID, and date range.

Parameter	Type	Default	Description
`_user_id`	bigint	--	ID of the requesting user (used for permission check)
`_correlation_id`	text	`null`	Correlation ID for permission check tracing
`_search_criteria`	jsonb	`null`	Search filters (see criteria keys below)
`_page`	integer	`1`	Page number (1-based)
`_page_size`	integer	`10`	Number of results per page
`_tenant_id`	integer	`1`	Tenant context
`_target_tenant_id`	integer	`null`	Cross-tenant: query a specific tenant's data (admin only)

Criteria keys:

Key	Type	Description
`event_type_code`	text	Filter by event type code (e.g. `'user_logged_in'`)
`target_user_id`	bigint	Filter by the user the event is about
`request_context`	jsonb	JSONB containment filter on `request_context` (e.g. `{"ip": "192.168.1.1"}`)
`correlation_id`	text	Filter by correlation ID
`from`	timestamptz	Start of date range (defaults to 100 years ago)
`to`	timestamptz	End of date range (defaults to 100 years from now)

Returns: table(__user_event_id bigint, __event_type_code text, __requester_user_id bigint, __requester_username text, __target_user_id bigint, __target_username text, __target_user_oid text, __request_context jsonb, __event_data jsonb, __correlation_id text, __created_at timestamptz, __created_by text, __total_items bigint)

Permission required: authentication.read_user_events

Source: 028_functions_auth_event.sql:36

`auth.get_user_audit_trail`¶

Returns a unified, paginated timeline combining both journal entries and user events for a specific user. Journal entries are matched by the user key in keys JSONB, and user events are matched by target_user_id. Results are merged and sorted by creation date descending.

Parameter	Type	Default	Description
`_user_id`	bigint	--	ID of the requesting user (used for permission check)
`_correlation_id`	text	`null`	Used for permission check tracing
`_search_criteria`	jsonb	`null`	Search filters (see criteria keys below)
`_page`	integer	`1`	Page number (1-based)
`_page_size`	integer	`20`	Number of results per page (capped at 100)
`_tenant_id`	integer	`1`	Tenant context
`_target_tenant_id`	integer	`null`	Cross-tenant: query a specific tenant's data (admin only)

Criteria keys:

Key	Type	Description
`target_user_id`	bigint	The user whose audit trail to retrieve
`from`	timestamptz	Start of date range (defaults to 100 years ago)
`to`	timestamptz	End of date range (defaults to 100 years from now)

Returns: table(__source text, __event_id integer, __event_type_code text, __event_category text, __message text, __request_context jsonb, __event_data jsonb, __correlation_id text, __created_at timestamptz, __created_by text, __total_items bigint)

The __source column indicates whether each row came from 'journal' or 'user_event'. For journal rows, the __message column contains the formatted message template resolved with payload values.

Permission required: authentication.read_user_events

Source: 028_functions_auth_event.sql:129

`auth.get_security_events`¶

Returns an aggregated, paginated view of security-relevant events from both user events and journal entries. User events are filtered to security-sensitive types (failed logins, lockouts, disables/enables). Journal entries are filtered to permission denial events (event_id = 32001).

Parameter	Type	Default	Description
`_user_id`	bigint	--	ID of the requesting user (used for permission check)
`_correlation_id`	text	`null`	Used for permission check tracing
`_search_criteria`	jsonb	`null`	Search filters (see criteria keys below)
`_page`	integer	`1`	Page number (1-based)
`_page_size`	integer	`20`	Number of results per page (capped at 100)
`_tenant_id`	integer	`1`	Tenant context
`_target_tenant_id`	integer	`null`	Cross-tenant: query a specific tenant's data (admin only)

Criteria keys:

Key	Type	Description
`from`	timestamptz	Start of date range (defaults to 100 years ago)
`to`	timestamptz	End of date range (defaults to 100 years from now)

Returns: table(__source text, __event_type_code text, __requester_user_id bigint, __requester_username text, __target_user_id bigint, __target_username text, __request_context jsonb, __event_data jsonb, __correlation_id text, __created_at timestamptz, __total_items bigint)

Included event types:

Source	Event Types
`user_event`	`user_login_failed`, `user_locked`, `user_disabled`, `user_unlocked`, `user_enabled`, `identity_disabled`, `identity_enabled`
`journal`	Permission denials (event_id `32001` / `err_no_permission`)

Permission required: authentication.read_user_events

Source: 028_functions_auth_event.sql:242

Journal Entry Creation¶

`public.create_journal_message`¶

Core function for creating structured journal entries. Respects the journal level setting (journal.level in const.sys_param) and storage mode (journal.storage_mode). Read-only events can be suppressed via the journal level.

Parameter	Type	Default	Description
`_created_by`	text	--	Identifier of the user or system performing the action
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	--	Correlation ID for tracing
`_event_id`	integer	--	Event ID from `const.event_code` table
`_keys`	jsonb	`null`	Entity references (e.g. `{"order": 3, "item": 5}`)
`_payload`	jsonb	`null`	Template values and additional data (e.g. `{"username": "john"}`)
`_tenant_id`	integer	`1`	Tenant context for the journal entry
`_request_context`	jsonb	`null`	Arbitrary request context

Returns: setof journal -- the inserted journal row (empty if suppressed by level or storage mode).

Permission required: None (called internally by other functions)

Source: 018_functions_public.sql:184

Storage modes

The journal.storage_mode system parameter controls where journal entries go:

'local' (default) -- INSERT into the journal table only
'notify' -- send via pg_notify on the journal_events channel only, skip INSERT
'both' -- INSERT and pg_notify

`public.create_journal_message_by_code`¶

Creates a journal entry using an event code string instead of a numeric event ID. Resolves the code to an ID via const.event_code, then delegates to create_journal_message.

Parameter	Type	Default	Description
`_created_by`	text	--	Identifier of the user or system performing the action
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	--	Correlation ID for tracing
`_event_code`	text	--	Event code string (e.g. `'user_created'`, `'order_placed'`)
`_keys`	jsonb	`null`	Entity references
`_payload`	jsonb	`null`	Template values and additional data
`_tenant_id`	integer	`1`	Tenant context
`_request_context`	jsonb	`null`	Arbitrary request context

Returns: setof journal -- the inserted journal row.

Permission required: None

Raises: Exception if event code is not found in const.event_code.

Source: 018_functions_public.sql:224

`public.create_journal_message_for_entity`¶

Convenience wrapper that creates a journal entry for a single entity. Builds the _keys JSONB automatically from entity type and ID.

Parameter	Type	Default	Description
`_created_by`	text	--	Identifier of the user or system performing the action
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	--	Correlation ID for tracing
`_event_id`	integer	--	Event ID from `const.event_code`
`_entity_type`	text	--	Entity type key (e.g. `'user'`, `'order'`, `'token'`)
`_entity_id`	bigint	--	Entity's ID value
`_payload`	jsonb	`null`	Template values and additional data
`_tenant_id`	integer	`1`	Tenant context
`_request_context`	jsonb	`null`	Arbitrary request context

Returns: setof journal -- the inserted journal row.

Permission required: None

Source: 018_functions_public.sql:258

Example

-- Creates keys: {"order": 42}
select * from create_journal_message_for_entity(
    'admin', 1, 'corr-123', 50001,
    'order', 42,
    '{"status": "shipped"}'::jsonb
);

`public.create_journal_message_for_entity_by_code`¶

Same as create_journal_message_for_entity but accepts an event code string instead of a numeric event ID.

Parameter	Type	Default	Description
`_created_by`	text	--	Identifier of the user or system performing the action
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	--	Correlation ID for tracing
`_event_code`	text	--	Event code string
`_entity_type`	text	--	Entity type key
`_entity_id`	bigint	--	Entity's ID value
`_payload`	jsonb	`null`	Template values and additional data
`_tenant_id`	integer	`1`	Tenant context
`_request_context`	jsonb	`null`	Arbitrary request context

Returns: setof journal -- the inserted journal row.

Permission required: None

Source: 018_functions_public.sql:281

Journal Queries¶

`public.get_journal_entry`¶

Retrieves a single journal entry by ID with its formatted message, event code, and category.

Parameter	Type	Default	Description
`_user_id`	bigint	--	ID of the requesting user (used for permission check)
`_correlation_id`	text	--	Correlation ID for permission check tracing
`_tenant_id`	integer	--	Tenant to scope the query
`_journal_id`	bigint	--	ID of the journal entry to retrieve

Returns: table(__journal_id bigint, __event_id integer, __event_code text, __event_category text, __message text, __keys jsonb, __payload jsonb, __request_context jsonb, __created_at timestamptz, __created_by text, __correlation_id text)

The __message column contains the formatted message template resolved with payload values using format_journal_message.

Permission required: journal.read_journal

Source: 018_functions_public.sql:303

`public.get_journal_payload`¶

Legacy function that retrieves a journal entry's payload as text. Prefer get_journal_entry for new code.

Parameter	Type	Default	Description
`_user_id`	bigint	--	ID of the requesting user (used for permission check)
`_correlation_id`	text	--	Correlation ID for permission check tracing
`_tenant_id`	integer	--	Tenant to scope the query
`_journal_id`	bigint	--	ID of the journal entry to retrieve

Returns: table(__journal_id bigint, __payload text)

Permission required: journal.read_journal

Source: 018_functions_public.sql:349

`public.search_journal`¶

Full-featured paginated search across journal entries. Supports text search, event filtering, JSONB containment filters on keys/payload/request_context, and date ranges. Tenant-scoped with a special global read mode for tenant 1.

Parameter	Type	Default	Description
`_user_id`	bigint	--	ID of the requesting user (used for permission check)
`_correlation_id`	text	`null`	Filter by correlation ID; also used for permission check
`_search_text`	text	`null`	Full-text search in `data_payload` JSONB (case-insensitive substring)
`_from`	timestamptz	`null`	Start of date range
`_to`	timestamptz	`null`	End of date range
`_target_user_id`	bigint	`null`	Filter by the user who created the entry
`_event_id`	integer	`null`	Filter by specific event ID
`_event_category`	text	`null`	Filter by event category (e.g. `'user_event'`, `'group_event'`)
`_keys_criteria`	jsonb	`null`	JSONB containment filter on `keys` (e.g. `'{"order": 3}'`)
`_payload_criteria`	jsonb	`null`	JSONB containment filter on `data_payload`
`_request_context_criteria`	jsonb	`null`	JSONB containment filter on `request_context`
`_page`	integer	`1`	Page number (1-based)
`_page_size`	integer	`10`	Number of results per page
`_tenant_id`	integer	`1`	Tenant to scope the query

Returns: table(__journal_id bigint, __event_id integer, __event_code text, __event_category text, __user_id bigint, __message text, __keys jsonb, __request_context jsonb, __created_at timestamptz, __created_by text, __correlation_id text, __total_items bigint)

Permission required:

For _tenant_id = 1: journal.read_global_journal (reads across all tenants)
For other tenants: journal.read_journal

Source: 018_functions_public.sql:487

`public.search_journal_msgs`¶

Legacy wrapper around search_journal that maps old-style _data_group / _data_object_id / _data_object_code parameters to JSONB keys filtering. Maintained for backwards compatibility -- new code should use search_journal directly.

Parameter	Type	Default	Description
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	`null`	Filter by correlation ID
`_search_text`	text	`null`	Full-text search in payload
`_from`	timestamptz	`null`	Start of date range
`_to`	timestamptz	`null`	End of date range
`_target_user_id`	integer	`null`	Filter by user ID
`_event_id`	integer	`null`	Filter by event ID
`_data_group`	text	`null`	Legacy entity group name (mapped to keys filter)
`_data_object_id`	bigint	`null`	Legacy entity ID (mapped to keys filter)
`_data_object_code`	text	`null`	Legacy entity code (mapped to keys filter)
`_payload_criteria`	jsonb	`null`	JSONB containment filter on payload
`_page`	integer	`1`	Page number
`_page_size`	integer	`10`	Results per page
`_tenant_id`	integer	`1`	Tenant scope

Returns: table(__created timestamptz, __created_by text, __journal_id bigint, __event_id integer, __data_group text, __data_object_id bigint, __data_object_code text, __user_id bigint, __msg text, __total_items bigint)

Permission required: journal.read_global_journal or journal.read_journal (inherited from search_journal)

Source: 018_functions_public.sql:902

Journal Helpers¶

`public.journal_keys`¶

Builds a JSONB object from key-value text pairs. Convenience function for constructing the _keys parameter when creating journal entries.

Parameter	Type	Default	Description
`variadic _pairs`	text[]	--	Alternating key-value pairs

Returns: jsonb

Permission required: None

Source: 018_functions_public.sql:113

Example

select journal_keys('order', '3', 'item', '5');
-- Returns: {"order": "3", "item": "5"}

`public.format_journal_message`¶

Formats a message template by replacing {key} placeholders with values from a JSONB payload. Also replaces {actor} with _created_by if the placeholder exists and was not already in the payload.

Parameter	Type	Default	Description
`_template`	text	--	Message template with `{key}` placeholders
`_payload`	jsonb	--	JSONB object with replacement values
`_created_by`	text	`null`	Fallback value for the `{actor}` placeholder

Returns: text -- the formatted message.

Permission required: None

Source: 018_functions_public.sql:124

Example

select format_journal_message(
    'User "{username}" created by {actor}',
    '{"username": "john"}'::jsonb,
    'admin'
);
-- Returns: 'User "john" created by admin'

`public.get_event_message_template`¶

Retrieves the message template for an event in a specific language. Falls back through: requested language, then English ('en'), then the event code's title.

Parameter	Type	Default	Description
`_event_id`	integer	--	Event ID to look up
`_language_code`	text	`'en'`	Language code for the template

Returns: text -- the message template string, or the event title if no template exists.

Permission required: None

Source: 018_functions_public.sql:158

Data Retention¶

`public.purge_audit_data`¶

Purges old journal entries and user events based on a retention policy. Delegates to unsecure.purge_journal and unsecure.purge_user_events, which drop entire monthly partitions older than the cutoff. Creates a journal entry recording the purge action.

Parameter	Type	Default	Description
`_deleted_by`	text	--	Identifier of the user or system performing the purge
`_user_id`	bigint	--	ID of the requesting user (used for permission check)
`_correlation_id`	text	--	Correlation ID for tracing
`_older_than_days`	integer	`null`	Delete data older than this many days; if `null`, falls back to `retention_days` in `const.sys_param` for each subsystem (`journal.retention_days` and `user_event.retention_days`)

Returns: table(__journal_deleted bigint, __user_events_deleted bigint) -- counts of deleted rows from each table.

Permission required: journal.purge_journal

Journal event: 17001 (audit_data_purged)

Source: 018_functions_public.sql:976

Warning

This operation is destructive. Purged data cannot be recovered. Both the journal and user_event tables are partitioned by month, so the purge drops entire monthly partitions that fall before the retention cutoff.

Event Code Management¶

Functions for managing the event type catalog at runtime. System events (is_system = true) are protected from deletion. Application-specific events should use the 50000+ range.

`public.create_event_category`¶

Creates a new event category with a reserved numeric range for event IDs.

Parameter	Type	Default	Description
`_created_by`	text	--	Identifier of the creator
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	--	Correlation ID for tracing
`_category_code`	text	--	Unique category code (e.g. `'order_events'`)
`_title`	text	--	Human-readable category title
`_range_start`	integer	--	Start of the numeric range for event IDs in this category
`_range_end`	integer	--	End of the numeric range for event IDs in this category
`_is_error`	boolean	`false`	Whether events in this category represent errors
`_source`	text	`null`	Source system or module that owns this category

Returns: setof const.event_category -- the created category row.

Permission required: None (no permission check in function body)

Source: 018_functions_public.sql:689

`public.create_event_code`¶

Creates a new event code within a category. Validates that the event ID falls within the category's reserved range. All runtime-created events have is_system = false.

Parameter	Type	Default	Description
`_created_by`	text	--	Identifier of the creator
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	--	Correlation ID for tracing
`_event_id`	integer	--	Numeric event ID (must be within the category's range)
`_code`	text	--	Unique event code string (e.g. `'order_placed'`)
`_category_code`	text	--	Category this event belongs to
`_title`	text	--	Human-readable event title
`_description`	text	`null`	Longer description of the event
`_is_read_only`	boolean	`false`	Whether this event represents a read-only operation (affects journal level filtering)
`_source`	text	`null`	Source system or module

Returns: setof const.event_code -- the created event code row.

Permission required: None (no permission check in function body)

Raises: Error 31014 if category not found; error 31013 if event ID is outside the category's range.

Source: 018_functions_public.sql:716

`public.create_event_message`¶

Creates a message template for an event in a specific language. Templates use {key} placeholder syntax that is resolved at display time by format_journal_message.

Parameter	Type	Default	Description
`_created_by`	text	--	Identifier of the creator
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	--	Correlation ID for tracing
`_event_id`	integer	--	Event ID to attach the message template to
`_message_template`	text	--	Template string with `{key}` placeholders
`_language_code`	text	`'en'`	Language code for this template

Returns: setof const.event_message -- the created message row.

Permission required: None (no permission check in function body)

Raises: Error 31011 if event code not found.

Source: 018_functions_public.sql:763

Example

select * from create_event_message(
    'admin', 1, 'corr-123',
    50001,
    'Order #{order_id} was placed by {username}',
    'en'
);

`public.delete_event_code`¶

Deletes an event code and its associated message templates. System events (is_system = true) are protected and cannot be deleted.

Parameter	Type	Default	Description
`_deleted_by`	text	--	Identifier of the deleter
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	--	Correlation ID for tracing
`_event_id`	integer	--	Event ID to delete

Returns: void

Permission required: None (no permission check in function body)

Raises: Error 31011 if event code not found; error 31010 if event is a system event.

Source: 018_functions_public.sql:794

`public.delete_event_category`¶

Deletes an event category. The category must have no remaining event codes (system or otherwise). System events within the category prevent deletion.

Parameter	Type	Default	Description
`_deleted_by`	text	--	Identifier of the deleter
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	--	Correlation ID for tracing
`_category_code`	text	--	Category code to delete

Returns: void

Permission required: None (no permission check in function body)

Raises: Error 31014 if category not found; error 31010 if category contains system events; error 31012 if category still has non-system event codes.

Source: 018_functions_public.sql:829

`public.delete_event_message`¶

Deletes a single event message template. Messages belonging to system events are protected.

Parameter	Type	Default	Description
`_deleted_by`	text	--	Identifier of the deleter
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	--	Correlation ID for tracing
`_event_message_id`	integer	--	ID of the message template to delete

Returns: void

Permission required: None (no permission check in function body)

Raises: Error 31011 if message not found; error 31010 if parent event is a system event.

Source: 018_functions_public.sql:863

Legacy Journal Functions¶

These functions maintain backwards compatibility with old add_journal_msg calls. New code should use create_journal_message or create_journal_message_by_code directly.

`public.add_journal_msg_jsonb`¶

Legacy wrapper that maps old-style parameters (message text, data group, object ID) to the new journal structure. The _msg parameter is preserved in the payload as _legacy_msg for debugging.

Parameter	Type	Default	Description
`_created_by`	text	--	Identifier of the creator
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	--	Correlation ID for tracing
`_msg`	text	--	Legacy message text (stored in payload as `_legacy_msg`)
`_data_group`	text	`'system'`	Legacy entity group name (mapped to keys)
`_data_object_id`	bigint	`null`	Legacy entity ID (mapped to keys)
`_payload`	jsonb	`null`	Additional JSONB payload
`_event_id`	integer	`null`	Event ID (defaults to `10002` / user_updated if not provided)
`_data_object_code`	text	`null`	Legacy entity code (mapped to keys)
`_tenant_id`	integer	`1`	Tenant context

Returns: setof journal

Permission required: None

Source: 018_functions_public.sql:598

`public.add_journal_msg`¶

Legacy wrapper identical to add_journal_msg_jsonb but accepts a text[] payload instead of JSONB. Converts the text array to JSONB using jsonb_object before delegating.

Parameter	Type	Default	Description
`_created_by`	text	--	Identifier of the creator
`_user_id`	bigint	--	ID of the requesting user
`_correlation_id`	text	--	Correlation ID for tracing
`_msg`	text	--	Legacy message text
`_data_group`	text	`'system'`	Legacy entity group name
`_data_object_id`	bigint	`null`	Legacy entity ID
`_payload`	text[]	`null`	Key-value pairs as text array (e.g. `'{key1, val1, key2, val2}'`)
`_event_id`	integer	`null`	Event ID
`_data_object_code`	text	`null`	Legacy entity code
`_tenant_id`	integer	`1`	Tenant context

Returns: setof journal

Permission required: None

Source: 018_functions_public.sql:649

Table Structures¶

`auth.user_event`¶

Stores security-related user events. Partitioned by created_at (monthly range partitions).

Column	Type	Default	Description
`user_event_id`	bigint	auto-generated identity	Primary key (composite with `created_at`)
`created_at`	timestamptz	`now()`	When the event occurred
`created_by`	text	`'unknown'`	Who created the record (max 250 chars)
`correlation_id`	text	--	Correlation ID for cross-system tracing
`event_type_code`	text	--	Event type (e.g. `'user_logged_in'`, `'user_locked'`)
`requester_user_id`	bigint	--	User who triggered the action (FK to `auth.user_info`, set null on delete)
`requester_username`	text	--	Username of the requester (denormalized for audit durability)
`target_user_id`	bigint	--	User the action was performed on (FK to `auth.user_info`, set null on delete)
`target_user_oid`	text	--	Target user's OID from identity provider
`target_username`	text	--	Target user's username (denormalized)
`request_context`	jsonb	--	Request metadata (IP, user agent, etc.)
`event_data`	jsonb	--	Event-specific structured data

Source: 013_tables_auth.sql:363

`public.journal`¶

General-purpose structured audit log. Partitioned by created_at (monthly range partitions). Messages are resolved at display time from templates in const.event_message.

Column	Type	Default	Description
`journal_id`	bigint	auto-generated identity	Primary key (composite with `created_at`)
`created_at`	timestamptz	`now()`	When the entry was created
`created_by`	text	`'unknown'`	Who created the record (max 250 chars)
`correlation_id`	text	--	Correlation ID for cross-system tracing
`tenant_id`	integer	--	Tenant context (FK to `auth.tenant`)
`event_id`	integer	--	Event type (FK to `const.event_code`)
`user_id`	bigint	--	User associated with the entry (FK to `auth.user_info`, set null on delete)
`keys`	jsonb	--	Entity references (e.g. `{"order": 3, "item": 5}`)
`data_payload`	jsonb	--	Template values and additional data
`request_context`	jsonb	--	Request metadata (IP, user agent, etc.)

Indexes: GIN indexes on keys, data_payload, and request_context; B-tree index on (tenant_id, event_id); descending index on created_at; partial index on correlation_id.

Source: 014_tables_stage.sql:49

Permission Summary¶

Function	Required Permission
`auth.create_user_event`	None
`auth.search_user_events`	`authentication.read_user_events`
`auth.get_user_audit_trail`	`authentication.read_user_events`
`auth.get_security_events`	`authentication.read_user_events`
`public.create_journal_message`	None
`public.create_journal_message_by_code`	None
`public.create_journal_message_for_entity`	None
`public.create_journal_message_for_entity_by_code`	None
`public.get_journal_entry`	`journal.read_journal`
`public.get_journal_payload`	`journal.read_journal`
`public.search_journal`	`journal.read_global_journal` or `journal.read_journal`
`public.search_journal_msgs`	`journal.read_global_journal` or `journal.read_journal`
`public.purge_audit_data`	`journal.purge_journal`
`public.create_event_category`	None
`public.create_event_code`	None
`public.create_event_message`	None
`public.delete_event_code`	None
`public.delete_event_category`	None
`public.delete_event_message`	None
`public.journal_keys`	None
`public.format_journal_message`	None
`public.get_event_message_template`	None
`public.add_journal_msg_jsonb`	None
`public.add_journal_msg`	None

Audit & Event Functions¶

User Event Creation¶

auth.create_user_event¶

User Event Queries¶

auth.search_user_events¶

auth.get_user_audit_trail¶

auth.get_security_events¶

Journal Entry Creation¶

public.create_journal_message¶

public.create_journal_message_by_code¶

public.create_journal_message_for_entity¶

public.create_journal_message_for_entity_by_code¶

Journal Queries¶

public.get_journal_entry¶

public.get_journal_payload¶

public.search_journal¶

public.search_journal_msgs¶

Journal Helpers¶

public.journal_keys¶

public.format_journal_message¶

public.get_event_message_template¶

Data Retention¶

public.purge_audit_data¶

Event Code Management¶

public.create_event_category¶

public.create_event_code¶

public.create_event_message¶

public.delete_event_code¶

public.delete_event_category¶

public.delete_event_message¶

Legacy Journal Functions¶

public.add_journal_msg_jsonb¶

public.add_journal_msg¶

Table Structures¶

auth.user_event¶

public.journal¶

Permission Summary¶

`auth.create_user_event`¶

`auth.search_user_events`¶

`auth.get_user_audit_trail`¶

`auth.get_security_events`¶

`public.create_journal_message`¶

`public.create_journal_message_by_code`¶

`public.create_journal_message_for_entity`¶

`public.create_journal_message_for_entity_by_code`¶

`public.get_journal_entry`¶

`public.get_journal_payload`¶

`public.search_journal`¶

`public.search_journal_msgs`¶

`public.journal_keys`¶

`public.format_journal_message`¶

`public.get_event_message_template`¶

`public.purge_audit_data`¶

`public.create_event_category`¶

`public.create_event_code`¶

`public.create_event_message`¶

`public.delete_event_code`¶

`public.delete_event_category`¶

`public.delete_event_message`¶

`public.add_journal_msg_jsonb`¶

`public.add_journal_msg`¶

`auth.user_event`¶

`public.journal`¶