Helpers & Internal Functions¶

Utility functions used by the auth system internally. These span four schemas: helpers for general-purpose utilities, error for structured exception raising, internal for shared logic called from already-authorized contexts, and unsecure for security-related internals that bypass permission checks.

Helper Functions¶

General-purpose utility functions in the helpers schema. These are pure, side-effect-free functions used across the entire permission model.

String Functions¶

`helpers.is_empty_string`¶

Returns true if the text is null or an empty string.

Parameter	Type	Default	Description
`_text`	`text`	--	The text to check

Returns: bool Properties: immutable, parallel safe Source: 004_create_helpers.sql:52

`helpers.is_not_empty_string`¶

Returns true if the text is neither null nor empty. Inverse of is_empty_string.

Parameter	Type	Default	Description
`_text`	`text`	--	The text to check

Returns: bool Properties: immutable, parallel safe Source: 004_create_helpers.sql:63

`helpers.random_string`¶

Generates a random uppercase hexadecimal string of the specified length using MD5.

Parameter	Type	Default	Description
`len`	`integer`	`36`	Length of the generated string

Returns: text Properties: volatile Source: 004_create_helpers.sql:74

`helpers.get_code`¶

Converts text into a URL/database-safe code by removing accents, lowercasing, and replacing non-alphanumeric characters with the separator.

Parameter	Type	Default	Description
`_text`	`text`	--	The text to convert
`_separator`	`text`	`'_'`	Character used to replace non-alphanumeric characters

Returns: text Properties: immutable, parallel safe Source: 009_update_common-helpers_v1-5.sql:15

Example:

select helpers.get_code('My Cool Feature!');
-- Result: 'my_cool_feature'

select helpers.get_code('Příliš žluťoučký kůň');
-- Result: 'prilis_zlutoucky_kun'

`helpers.get_slug`¶

Converts text into a URL-friendly slug (using hyphens). Thin wrapper around get_code with '-' separator.

Parameter	Type	Default	Description
`_text`	`text`	--	The text to convert

Returns: text Properties: immutable, parallel safe Source: 009_update_common-helpers_v1-5.sql:38

Example:

select helpers.get_slug('My Cool Feature!');
-- Result: 'my-cool-feature'

`helpers.normalize_text`¶

Removes diacritical marks (accents) from text, optionally lowercasing the result. Replacement for the earlier unaccent_text.

Parameter	Type	Default	Description
`_text`	`text`	--	The text to normalize
`_lower_text`	`boolean`	`true`	Whether to also lowercase the result

Returns: text Properties: immutable, strict, parallel safe Source: 009_update_common-helpers_v1-5.sql:58

`helpers.unaccent_text`¶

Legacy function. Removes diacritical marks from text, optionally lowercasing. Superseded by normalize_text but retained for backward compatibility.

Parameter	Type	Default	Description
`_text`	`text`	--	The text to process
`_lower_text`	`bool`	`true`	Whether to also lowercase the result

Returns: text Properties: immutable, parallel safe Source: 004_create_helpers.sql:118

JSON Functions¶

`helpers.compare_jsonb_objects`¶

Compares two JSONB objects and returns a JSONB object containing only the keys whose values differ, with the values from the second object.

Parameter	Type	Default	Description
`_first`	`jsonb`	--	The original JSONB object
`_second`	`jsonb`	--	The modified JSONB object

Returns: jsonb Properties: immutable, parallel safe Source: 008_update_common-helpers_v1-4.sql:13

Example:

select helpers.compare_jsonb_objects(
    '{"name": "Alice", "age": 30}'::jsonb,
    '{"name": "Alice", "age": 31}'::jsonb
);
-- Result: {"age": "31"}

`helpers.compute_jsonb_hash`¶

Computes a SHA-256 hash of a JSONB object's key-value pairs (sorted by key). Useful for change detection.

Parameter	Type	Default	Description
`_data`	`jsonb`	--	The JSONB data to hash
`_normalize_text`	`bool`	`false`	Whether to normalize text values before hashing
`_field_value_separator`	`text`	`':'`	Separator between key and value
`_fields_separator`	`text`	`'\\|'`	Separator between key-value pairs

Returns: text Properties: immutable, strict Source: 005_update_common-helpers_v1-1.sql:52

`helpers.normalize_jsonb_values`¶

Normalizes all text values in a JSONB object (removes accents, lowercases). Keys are preserved as-is.

Parameter	Type	Default	Description
`_data`	`jsonb`	--	The JSONB data to normalize

Returns: jsonb Properties: immutable, strict Source: 007_update_common-helpers_v1-3.sql:25

`helpers.delete_jsonb_fields`¶

Removes specified keys from a JSONB object.

Parameter	Type	Default	Description
`_data`	`jsonb`	--	The source JSONB object
`_fields_to_delete`	`text[]`	`null`	Array of key names to remove

Returns: jsonb Properties: immutable, strict, parallel safe Source: 006_update_common-helpers_v1-2.sql:25

Ltree Functions¶

`helpers.ltree_parent`¶

Returns the parent path of an ltree node by trimming the specified number of levels from the end.

Parameter	Type	Default	Description
`path`	`ext.ltree`	--	The ltree path
`levels`	`integer`	`1`	Number of levels to trim from the end

Returns: ext.ltree Properties: immutable, parallel safe Source: 009_update_common-helpers_v1-5.sql:48

Example:

select helpers.ltree_parent('1.2.3.4'::ext.ltree);
-- Result: '1.2.3'

select helpers.ltree_parent('1.2.3.4'::ext.ltree, 2);
-- Result: '1.2'

Journal & Storage Control¶

`helpers.should_log_journal`¶

Checks whether a journal entry should be created based on the configured journal level in const.sys_param. Levels: 'all' (log everything), 'update' (skip reads, default), 'none' (disable).

Parameter	Type	Default	Description
`_is_read_only`	`boolean`	`false`	Whether the operation is read-only

Returns: boolean Properties: stable Source: 013_update_common-helpers_v1-6.sql:36

`helpers.is_event_read_only`¶

Looks up the is_read_only flag for a given event ID from const.event_code.

Parameter	Type	Default	Description
`_event_id`	`integer`	--	The event ID to check

Returns: boolean Properties: stable Source: 013_update_common-helpers_v1-6.sql:56

`helpers.should_store_locally`¶

Checks whether data should be stored in PostgreSQL based on the storage mode setting for the given group. Returns true for modes 'local' or 'both'.

Parameter	Type	Default	Description
`_group_code`	`text`	--	The sys_param group code (e.g., `'journal'`, `'user_event'`)

Returns: boolean Properties: stable Source: 013_update_common-helpers_v1-6.sql:81

`helpers.should_notify_storage`¶

Checks whether a pg_notify notification should be sent based on the storage mode setting. Returns true for modes 'notify' or 'both'.

Parameter	Type	Default	Description
`_group_code`	`text`	--	The sys_param group code (e.g., `'journal'`, `'user_event'`)

Returns: boolean Properties: stable Source: 013_update_common-helpers_v1-6.sql:94

Trigger Functions¶

`helpers.trg_generate_code_from_title`¶

Trigger function that auto-generates a code column from the title column on insert. If code is already set, it normalizes it instead. Attached to tables that have both title and code columns.

Returns: trigger Source: 009_update_common-helpers_v1-5.sql:69

`helpers.calculate_ts_regconfig`¶

Maps a language code (e.g., 'en', 'de', 'fr') to a PostgreSQL regconfig for full-text search. Falls back to 'simple' for unsupported languages.

Parameter	Type	Default	Description
`_language_code`	`text`	--	ISO language code (uses first 2 characters)

Returns: regconfig Properties: immutable, parallel safe Source: 030_tables_language.sql:91

Error Functions¶

Source: 016_functions_error.sql

The error schema provides structured exception-raising functions. Each function corresponds to a specific error code and throws a PostgreSQL exception with that code in the errcode field. This allows callers to catch specific errors programmatically.

Error codes follow a categorized numbering scheme:

Range	Category
30001--30999	Security / Authentication
31001--31999	Validation
32001--32999	Permission
33001--33999	User / Group
34001--34999	Tenant
35001--35999	Resource Access
36001--36999	Token Configuration
37001--37999	Language / Translation
38001--38999	MFA
39001--39999	Invitation

Security / Authentication Errors (30xxx)¶

Function	Code	Message
`error.raise_30001(_api_key text)`	30001	API key/secret combination is not valid or API user has not been found
`error.raise_30002(_token_uid text)`	30002	Token is not valid or has expired
`error.raise_30003(_token_uid text)`	30003	Token was created for different user
`error.raise_30004()`	30004	The same token is already used
`error.raise_30005()`	30005	Token does not exist

Validation Errors (31xxx)¶

Function	Code	Message
`error.raise_31001()`	31001	Either user group id or target user id must not be null
`error.raise_31002()`	31002	Either permission set code or permission code must not be null
`error.raise_31003()`	31003	Either permission id or code has to be not null
`error.raise_31004()`	31004	Either mapped object id or mapped role must not be empty
`error.raise_31010(_event_id integer)`	31010	Event code is a system event and cannot be modified or deleted
`error.raise_31011(_event_id integer)`	31011	Event code does not exist
`error.raise_31012(_category_code text)`	31012	Event category still has event codes and cannot be deleted
`error.raise_31013(_event_id integer, _category_code text, _range_start integer, _range_end integer)`	31013	Event ID is outside the allowed range for category
`error.raise_31014(_category_code text)`	31014	Event category does not exist

Permission Errors (32xxx)¶

Function	Code	Message
`error.raise_32001(_user_id bigint, _perm_codes text[], _tenant_id integer)`	32001	User has no permission (codes) in tenant
`error.raise_32002(_permission_full_code text)`	32002	Permission does not exist
`error.raise_32003(_permission_code text)`	32003	Permission is not assignable
`error.raise_32004(_perm_set_code text)`	32004	Permission set does not exist
`error.raise_32005(_perm_set_code text)`	32005	Permission set is not assignable
`error.raise_32006(_perm_set_id integer, _tenant_id integer)`	32006	Permission set is not defined in tenant
`error.raise_32007(_parent_full_code text)`	32007	Parent permission does not exist
`error.raise_32008()`	32008	Some permissions are not assignable

User / Group Errors (33xxx)¶

Function	Code	Message
`error.raise_33001(_user_id bigint, _email text)`	33001	User does not exist
`error.raise_33002(_user_id bigint)`	33002	User is a system user
`error.raise_33003(_user_id bigint)`	33003	User is not in active state
`error.raise_33004(_email text)`	33004	User is locked out (by email)
`error.raise_33004(_user_id bigint)`	33004	User is locked out (by user_id) -- overload
`error.raise_33005(_user_id bigint)`	33005	User is not supposed to log in
`error.raise_33006(_username text)`	33006	User cannot be ensured for email provider
`error.raise_33007(_normalized_email text)`	33007	User identity is already in use
`error.raise_33008(_user_id bigint, _provider_code text)`	33008	User identity for provider is not in active state
`error.raise_33009(_user_id bigint, _provider_code text)`	33009	User identity for provider does not exist
`error.raise_33010(_provider_code text)`	33010	Provider is not in active state
`error.raise_33011(_user_group_id integer)`	33011	User group does not exist
`error.raise_33012(_user_group_id integer)`	33012	User group is not active
`error.raise_33013(_user_group_id integer)`	33013	User group is not assignable or is external
`error.raise_33014(_user_group_id integer)`	33014	User group is a system group
`error.raise_33015(_user_id bigint, _user_group_id integer, _tenant_id integer)`	33015	User is not tenant or group owner
`error.raise_33016(_provider_code text)`	33016	Provider does not allow group mapping
`error.raise_33017(_provider_code text)`	33017	Provider does not allow group sync
`error.raise_33018(_username text)`	33018	User is blacklisted and cannot be created
`error.raise_33019(_provider_code text, _identifier text)`	33019	User identity is blacklisted and cannot be created
`error.raise_33020(_identifier text)`	33020	User could not be resolved by identifier
`error.raise_33021(_identifier text)`	33021	User group could not be resolved by identifier

Tenant Errors (34xxx)¶

Function	Code	Message
`error.raise_34001(_tenant_id text, _username text)`	34001	User has no access to tenant
`error.raise_34003(_identifier text)`	34003	Tenant could not be resolved by identifier

Resource Access Errors (35xxx)¶

Function	Code	Message
`error.raise_35001(_user_id bigint, _resource_type text, _resource_id bigint, _tenant_id integer)`	35001	User has no access to resource in tenant
`error.raise_35002()`	35002	Either target user_id or user_group_id must be provided
`error.raise_35003(_resource_type text)`	35003	Resource type does not exist or is not active
`error.raise_35004(_access_flag text)`	35004	Access flag does not exist
`error.raise_35007(_role_code text)`	35007	Resource role does not exist or is not active
`error.raise_35008(_role_code text, _resource_type text, _bad_flag text)`	35008	Resource role cannot include flag -- not valid for resource type
`error.raise_35009(_role_code text, _role_type text, _assignment_type text)`	35009	Resource role type mismatch at assignment

Token Configuration Errors (36xxx)¶

Function	Code	Message
`error.raise_36001(_token_type_code text)`	36001	Token type does not exist
`error.raise_36002(_token_type_code text)`	36002	Token type is a system token type and cannot be modified or deleted

Language / Translation Errors (37xxx)¶

These are defined in 030_tables_language.sql:

Function	Code	Message
`error.raise_37001(_language_code text)`	37001	Language does not exist
`error.raise_37002(_translation_id integer)`	37002	Translation does not exist

MFA Errors (38xxx)¶

These are defined in 036_tables_mfa.sql and 039_mfa_policy.sql:

Function	Code	Message
`error.raise_38001(_user_id bigint, _mfa_type_code text)`	38001	MFA is already enrolled and confirmed for this type
`error.raise_38002(_user_id bigint, _mfa_type_code text)`	38002	MFA is not enrolled for this type
`error.raise_38003(_user_id bigint, _mfa_type_code text)`	38003	MFA enrollment is not confirmed
`error.raise_38004()`	38004	The provided MFA code is not valid
`error.raise_38005(_user_id bigint)`	38005	MFA verification is required
`error.raise_38006(_mfa_type_code text)`	38006	MFA type does not exist or is inactive
`error.raise_38007(_mfa_policy_id bigint)`	38007	MFA policy does not exist

Invitation Errors (39xxx)¶

These are defined in 041_tables_invitation.sql:

Function	Code	Message
`error.raise_39001(_invitation_id bigint)`	39001	Invitation does not exist
`error.raise_39002(_invitation_id bigint, _status_code text)`	39002	Invitation is not in pending state
`error.raise_39003(_invitation_id bigint)`	39003	Invitation has expired
`error.raise_39004(_invitation_action_id bigint)`	39004	Invitation action does not exist
`error.raise_39005(_invitation_action_id bigint)`	39005	Invitation action is not in pending or processing state
`error.raise_39006(_template_code text)`	39006	Invitation template does not exist or is inactive

Backwards Compatibility Aliases (52xxx)¶

The 016_functions_error.sql file also defines aliases using the old 52xxx error code numbering. These are thin wrappers that delegate to the new 3xxxx functions above. They exist solely for backward compatibility with older code that references the legacy error codes.

Old Function	Delegates To
`error.raise_52101`	`error.raise_33006`
`error.raise_52102`	`error.raise_33007`
`error.raise_52103`	`error.raise_33001`
`error.raise_52104`	`error.raise_33002`
`error.raise_52105`	`error.raise_33003`
`error.raise_52106`	`error.raise_33004`
`error.raise_52107`	`error.raise_33010`
`error.raise_52108`	`error.raise_34001`
`error.raise_52109`	`error.raise_32001`
`error.raise_52110`	`error.raise_33008`
`error.raise_52111`	`error.raise_33009`
`error.raise_52112`	`error.raise_33005`
`error.raise_52113`	`error.raise_33016`
`error.raise_52114`	`error.raise_33017`
`error.raise_52115`	`error.raise_33018`
`error.raise_52116`	`error.raise_33019`
`error.raise_52171`	`error.raise_33011`
`error.raise_52172`	`error.raise_33012`
`error.raise_52173`	`error.raise_33013`
`error.raise_52174`	`error.raise_31004`
`error.raise_52175`	`error.raise_32005`
`error.raise_52176`	`error.raise_32003`
`error.raise_52177`	`error.raise_32006`
`error.raise_52178`	`error.raise_32008`
`error.raise_52179`	`error.raise_32007`
`error.raise_52180`	`error.raise_32002`
`error.raise_52181`	`error.raise_32003`
`error.raise_52271`	`error.raise_33014`
`error.raise_52272`	`error.raise_31001`
`error.raise_52273`	`error.raise_31002`
`error.raise_52274`	`error.raise_31003`
`error.raise_52275`	`error.raise_32002`
`error.raise_52276`	`error.raise_30004`
`error.raise_52277`	`error.raise_30005`
`error.raise_52278`	`error.raise_30002`
`error.raise_52279`	`error.raise_30003`
`error.raise_52282`	`error.raise_32004`
`error.raise_52283`	`error.raise_32005`
`error.raise_52301`	`error.raise_30001`
`error.raise_52401`	`error.raise_33015`

Internal Functions¶

Source: 019_functions_unsecure.sql

Functions in the internal schema that provide shared logic used by auth and public layer functions. These functions do not perform permission checks -- they are called from contexts where authorization has already been validated.

Identifier Resolvers¶

Resolver functions accept a flexible text identifier (numeric ID, UUID, or code) and return the corresponding primary key. They are used by higher-level functions to support multiple lookup strategies in a single parameter.

`internal.resolve_user`¶

Resolves a user identifier to a user_id. Accepts a bigint-as-text (direct user_id), a UUID, or a user code. Lookup order: bigint cast, then UUID match against auth.user_info.uuid, then code match against auth.user_info.code.

Parameter	Type	Default	Description
`_identifier`	`text`	--	User identifier (bigint, uuid, or code)

Returns: bigint -- the resolved user_id Raises: 33020 if identifier is null/empty or no matching user is found Source: 019_functions_unsecure.sql:82

`internal.resolve_tenant`¶

Resolves a tenant identifier to a tenant_id. Accepts an integer-as-text (direct tenant_id), a UUID, or a tenant code. Lookup order: integer cast, then UUID match against auth.tenant.uuid, then code match against auth.tenant.code.

Parameter	Type	Default	Description
`_identifier`	`text`	--	Tenant identifier (integer, uuid, or code)

Returns: integer -- the resolved tenant_id Raises: 34003 if identifier is null/empty or no matching tenant is found Source: 019_functions_unsecure.sql:131

`internal.resolve_group`¶

Resolves a group identifier to a user_group_id. Accepts an integer-as-text (direct user_group_id) or a group code. When looking up by code, _tenant_id scopes the search since group codes are unique per tenant. Without _tenant_id, the first matching code across all tenants is returned.

Parameter	Type	Default	Description
`_identifier`	`text`	--	Group identifier (integer or code)
`_tenant_id`	`integer`	`null`	Tenant to scope code lookup (recommended for unambiguous results)

Returns: integer -- the resolved user_group_id Raises: 33021 if identifier is null/empty or no matching group is found Source: 019_functions_unsecure.sql:181

Unsecure Functions¶

Source: 019_functions_unsecure.sql

No Permission Checks

Functions in the unsecure schema do not perform any permission checks. They are intended for use only within trusted internal contexts -- called by auth.* wrapper functions that have already verified permissions, by trigger functions, or by system initialization scripts. Never expose these functions directly to application code.

Permission Cache Management¶

`unsecure.clear_permission_cache`¶

Hard-deletes cached permissions for a user. If _tenant_id is null, clears cache for all tenants (used when a user is locked/disabled).

Parameter	Type	Default	Description
`_deleted_by`	`text`	--	Audit trail: who initiated the clear
`_target_user_id`	`bigint`	--	The user whose cache to clear
`_tenant_id`	`integer`	`null`	Tenant to clear, or null for all tenants

Returns: void Wrapped by: Called internally by many auth.* functions after state changes Source: 019_functions_unsecure.sql:13

`unsecure.invalidate_group_members_permission_cache`¶

Soft-invalidates permission cache for all members of a group by setting expiration_date to now. More performant than hard delete -- next has_permission call recalculates.

Parameter	Type	Default	Description
`_updated_by`	`text`	--	Audit trail
`_user_group_id`	`integer`	--	The group whose members' cache to invalidate
`_tenant_id`	`integer`	`1`	Target tenant

Returns: void Source: 019_functions_unsecure.sql:28

`unsecure.invalidate_perm_set_users_permission_cache`¶

Soft-invalidates permission cache for all users who have a specific permission set assigned (directly or via group membership).

Parameter	Type	Default	Description
`_updated_by`	`text`	--	Audit trail
`_perm_set_id`	`integer`	--	The permission set whose users' cache to invalidate
`_tenant_id`	`integer`	`1`	Target tenant

Returns: void Source: 019_functions_unsecure.sql:51

`unsecure.invalidate_users_permission_cache`¶

Soft-invalidates permission cache for a list of user IDs. Used by triggers that affect multiple users at once (e.g., group delete, provider delete).

Parameter	Type	Default	Description
`_updated_by`	`text`	--	Audit trail
`_user_ids`	`bigint[]`	--	Array of user IDs to invalidate
`_tenant_id`	`integer`	`null`	Tenant to invalidate, or null for all tenants

Returns: void Source: 019_functions_unsecure.sql:86

`unsecure.invalidate_permission_users_cache`¶

Soft-invalidates permission cache for all users affected by a change to a specific permission. Finds users via direct assignment, permission set membership, and group membership.

Parameter	Type	Default	Description
`_updated_by`	`text`	--	Audit trail
`_permission_id`	`integer`	--	The permission that was changed

Returns: void Source: 019_functions_unsecure.sql:102

Group ID Cache Management¶

`unsecure.get_cached_group_ids`¶

Returns cached active group IDs for a user in a tenant. On cache miss or expiry, resolves from user_group_member + user_group, upserts the cache, and returns. Uses the same TTL as the permission cache (default 300s).

Parameter	Type	Default	Description
`_user_id`	`bigint`	--	The user to look up
`_tenant_id`	`integer`	--	The tenant context

Returns: integer[] Wrapped by: auth.is_group_member, auth.can_manage_user_group Source: 019_functions_unsecure.sql:2316

`unsecure.invalidate_user_group_id_cache`¶

Soft-invalidates group ID cache for a user by setting expiration_date to now. If _tenant_id is null, invalidates all tenants.

Parameter	Type	Default	Description
`_user_id`	`bigint`	--	The user whose cache to invalidate
`_tenant_id`	`integer`	`null`	Tenant to invalidate, or null for all tenants

Returns: void Source: 019_functions_unsecure.sql:2383

`unsecure.clear_user_group_id_cache`¶

Hard-deletes group ID cache rows for a user. If _tenant_id is null, clears all tenants.

Parameter	Type	Default	Description
`_user_id`	`bigint`	--	The user whose cache to clear
`_tenant_id`	`integer`	`null`	Tenant to clear, or null for all tenants

Returns: void Source: 019_functions_unsecure.sql:2405

`unsecure.invalidate_group_members_group_id_cache`¶

Soft-invalidates group ID cache for all members of a specific group. Used when a group's is_active status changes.

Parameter	Type	Default	Description
`_user_group_id`	`integer`	--	The group whose members' cache to invalidate
`_tenant_id`	`integer`	--	The tenant context

Returns: void Source: 019_functions_unsecure.sql:2425

Notification Functions¶

`unsecure.notify_permission_change`¶

Sends a notification via pg_notify on the permission_changes channel. Called from trigger functions and other unsecure.* functions to notify backends of permission-relevant changes.

Parameter	Type	Default	Description
`_event`	`text`	--	Event name (e.g., `'permission_assigned'`)
`_tenant_id`	`integer`	--	Affected tenant
`_target_type`	`text`	--	Target entity type (e.g., `'user'`, `'group'`)
`_target_id`	`bigint`	--	Target entity ID
`_detail`	`jsonb`	`null`	Optional additional detail payload

Returns: void Source: 019_functions_unsecure.sql:149

`unsecure.notify_journal_event`¶

Sends a notification via pg_notify on the journal_events channel. Called when storage mode is 'notify' or 'both'. Payload is truncated if it exceeds approximately 7900 bytes (pg_notify has an 8000-byte limit).

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID for request tracing
`_event_id`	`integer`	--	Event code
`_keys`	`jsonb`	`null`	Key data
`_payload`	`jsonb`	`null`	Event payload
`_tenant_id`	`integer`	`1`	Tenant context
`_request_context`	`jsonb`	`null`	Request context metadata

Returns: void Source: 019_functions_unsecure.sql:181

`unsecure.notify_user_event`¶

Sends a notification via pg_notify on the user_events channel. Called when storage mode is 'notify' or 'both'. Payload is truncated if it exceeds approximately 7900 bytes.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_event_type_code`	`text`	--	Event type
`_target_user_id`	`bigint`	--	Target user
`_requester_username`	`text`	--	Requester's username
`_target_user_oid`	`text`	`null`	Target user's provider OID
`_target_username`	`text`	`null`	Target user's username
`_request_context`	`jsonb`	`null`	Request context
`_event_data`	`jsonb`	`null`	Additional event data

Returns: void Source: 019_functions_unsecure.sql:223

User Management¶

`unsecure.create_user_system`¶

Creates the initial system user (user_id = 1) during database bootstrap. Not intended for normal use.

Returns: setof auth.user_info Source: 019_functions_unsecure.sql:296

`unsecure.create_user_info`¶

Creates a new user in auth.user_info. Normalizes username and email, checks the blacklist before creation. If a user with the same username already exists, returns the existing record.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_username`	`text`	--	Username
`_email`	`text`	--	Email address
`_display_name`	`text`	--	Display name
`_last_provider_code`	`text`	--	Last used identity provider code

Returns: setof auth.user_info Wrapped by: auth.register_user, auth.ensure_user_from_provider Source: 019_functions_unsecure.sql:1252

`unsecure.create_service_user_info`¶

Creates a service user with user IDs in the 1-999 range. Supports custom user IDs for predictable service account numbering. Checks the blacklist before creation.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_username`	`text`	--	Service username
`_display_name`	`text`	--	Display name
`_email`	`text`	`null`	Optional email
`_custom_service_user_id`	`bigint`	`null`	Optional specific user ID to assign

Returns: setof auth.user_info Wrapped by: auth.create_service_user Source: 019_functions_unsecure.sql:1298

`unsecure.create_api_user`¶

Creates a technical user for an API key. The username is auto-generated as api_key_{key}.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_api_key`	`text`	--	API key value
`_tenant_id`	`integer`	`1`	Tenant context

Returns: setof auth.user_info Source: 019_functions_unsecure.sql:1433

`unsecure.update_user_info_basic_data`¶

Updates basic user profile fields (username, display name, email).

Parameter	Type	Default	Description
`_updated_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_target_user_id`	`bigint`	--	User to update
`_username`	`text`	--	New username
`_display_name`	`text`	--	New display name
`_email`	`text`	`null`	New email

Returns: table(__user_info_id bigint) Wrapped by: auth.ensure_user_from_provider (for auto-updating profile data on login) Source: 019_functions_unsecure.sql:1462

`unsecure.update_user_password`¶

Updates the password hash and salt for a user's email identity.

Parameter	Type	Default	Description
`_updated_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_target_user_id`	`bigint`	--	User whose password to update
`_password_hash`	`text`	`null`	New password hash
`_password_salt`	`text`	`null`	New password salt

Returns: table(__user_id bigint, __provider_code text, __provider_uid text) Wrapped by: auth.update_user_password Source: 019_functions_unsecure.sql:1358

`unsecure.delete_user_by_id`¶

Deletes a user by ID. Optionally blacklists all the user's identities before deletion.

Parameter	Type	Default	Description
`_deleted_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_target_user_id`	`bigint`	--	User to delete
`_blacklist`	`boolean`	`false`	Whether to blacklist identities before deleting

Returns: table(__user_id bigint, __username text) Wrapped by: auth.delete_user Source: 019_functions_unsecure.sql:319

`unsecure.delete_user_by_username_as_system`¶

Deletes a user by username. System-level operation with no audit trail parameters.

Parameter	Type	Default	Description
`_username`	`text`	--	Username to delete

Returns: auth.user_info Source: 019_functions_unsecure.sql:308

`unsecure.update_last_used_provider`¶

Updates the last_used_provider_code on auth.user_info. Called during login to track which identity provider was most recently used.

Parameter	Type	Default	Description
`_target_user_id`	`bigint`	--	User to update
`_provider_code`	`text`	--	Provider code

Returns: void Wrapped by: auth.ensure_user_from_provider, auth.login_user Source: 019_functions_unsecure.sql:1243

`unsecure.assign_user_default_groups`¶

Adds a user to all active default groups (where is_default = true) in a tenant that they are not already a member of.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_target_user_id`	`bigint`	--	User to add to groups
`_tenant_id`	`integer`	`1`	Tenant context

Returns: table(__user_id bigint, __user_group_id integer, __user_group_code text, __user_group_title text) Wrapped by: auth.assign_user_default_groups Source: 019_functions_unsecure.sql:1384

User Identity Management¶

`unsecure.create_user_identity`¶

Creates a new identity provider record for a user. Checks the blacklist before creation.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_target_user_id`	`bigint`	--	User to add identity for
`_provider_code`	`text`	--	Identity provider code
`_provider_uid`	`text`	--	Provider-specific user identifier
`_provider_oid`	`text`	--	Provider-specific object identifier
`_password_hash`	`text`	`null`	Password hash (for email provider)
`_user_data`	`text`	`null`	Additional user data as JSON string
`_password_salt`	`text`	`null`	Password salt (for email provider)
`_is_active`	`boolean`	`false`	Whether identity is immediately active

Returns: table(__user_id bigint, __provider_code text, __provider_uid text) Wrapped by: auth.register_user, auth.ensure_user_from_provider Source: 019_functions_unsecure.sql:1590

`unsecure.update_user_identity_uid_oid`¶

Updates the UID and/or OID of a user's identity for a given provider. Only updates if the values actually changed, logging each change separately.

Parameter	Type	Default	Description
`_updated_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_target_user_id`	`bigint`	--	User to update
`_provider_code`	`text`	--	Identity provider
`_provider_uid`	`text`	--	New UID value
`_provider_oid`	`text`	--	New OID value

Returns: void Wrapped by: auth.ensure_user_from_provider Source: 019_functions_unsecure.sql:1981

User Blacklist Management¶

`unsecure.check_user_blacklist`¶

Checks whether a username or provider identity is on the blacklist. Returns true if any match is found.

Parameter	Type	Default	Description
`_username`	`text`	`null`	Username to check
`_provider_code`	`text`	`null`	Provider code to check
`_provider_uid`	`text`	`null`	Provider UID to check
`_provider_oid`	`text`	`null`	Provider OID to check

Returns: boolean Properties: stable Wrapped by: auth.is_blacklisted Source: 019_functions_unsecure.sql:2446

`unsecure.blacklist_user`¶

Adds a single entry to the auth.user_blacklist table.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_username`	`text`	`null`	Username to blacklist
`_provider_code`	`text`	`null`	Provider code
`_provider_uid`	`text`	`null`	Provider UID
`_provider_oid`	`text`	`null`	Provider OID
`_original_user_id`	`bigint`	`null`	Original user ID (for traceability)
`_reason`	`text`	`'manual'`	Reason for blacklisting

Returns: table(__blacklist_id bigint) Wrapped by: auth.create_blacklist_user Source: 019_functions_unsecure.sql:2468

`unsecure.blacklist_user_identities`¶

Blacklists a user's username and all their provider identities at once. Called before user deletion when _blacklist = true.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_target_user_id`	`bigint`	--	User whose identities to blacklist
`_reason`	`text`	`'user_deleted'`	Reason for blacklisting

Returns: void Source: 019_functions_unsecure.sql:2504

User Event Management¶

`unsecure.create_user_event`¶

Creates a user event audit record. Respects the storage mode setting -- may insert locally, send via pg_notify, or both.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_event_type_code`	`text`	--	Event type code
`_target_user_id`	`bigint`	--	Target user
`_request_context`	`jsonb`	`null`	Request context
`_event_data`	`jsonb`	`null`	Additional event data
`_target_user_oid`	`text`	`null`	Target user OID
`_target_username`	`text`	`null`	Target username (auto-resolved if null)

Returns: table(__user_event_id bigint) Wrapped by: auth.create_user_event Source: 019_functions_unsecure.sql:340

`unsecure.expire_tokens`¶

Batch-expires all tokens that have passed their expires_at timestamp. Logs the count of expired tokens to the journal.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail

Returns: void Source: 019_functions_unsecure.sql:395

Group Management¶

`unsecure.create_user_group`¶

Creates a new user group with full control over all flags.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_title`	`text`	--	Group title
`_is_assignable`	`boolean`	`true`	Whether users can be assigned to this group
`_is_active`	`boolean`	`true`	Whether the group is active
`_is_external`	`boolean`	`false`	Whether membership is driven by external provider
`_is_system`	`boolean`	`false`	Whether this is a system group
`_is_default`	`boolean`	`false`	Whether new users are auto-added
`_tenant_id`	`integer`	`1`	Tenant context
`_source`	`text`	`null`	Source identifier

Returns: table(__user_group_id integer) Wrapped by: auth.create_user_group, auth.create_external_user_group Source: 019_functions_unsecure.sql:421

`unsecure.create_user_group_as_system`¶

Convenience wrapper for creating groups as the system user.

Parameter	Type	Default	Description
`_title`	`text`	--	Group title
`_is_system`	`boolean`	`false`	Whether this is a system group
`_is_assignable`	`boolean`	`true`	Whether users can be assigned
`_is_default`	`boolean`	`false`	Whether new users are auto-added
`_tenant_id`	`integer`	`1`	Tenant context

Returns: setof auth.user_group Source: 019_functions_unsecure.sql:452

`unsecure.create_user_group_member`¶

Adds a user as a member of a group. Validates that the group exists, is active, is assignable, and is not external. Clears the user's permission cache after adding.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_user_group_id`	`integer`	--	Group to add user to
`_target_user_id`	`bigint`	--	User to add
`_tenant_id`	`integer`	`1`	Tenant context

Returns: table(__user_group_member_id bigint) Wrapped by: auth.create_user_group_member Source: 019_functions_unsecure.sql:1487

`unsecure.create_user_group_member_as_system`¶

Convenience wrapper for adding group members as the system user. Looks up user and group by name/title.

Parameter	Type	Default	Description
`_user_name`	`text`	--	Username to add
`_group_title`	`text`	--	Group title to add user to
`_tenant_id`	`integer`	`1`	Tenant context

Returns: setof auth.user_group_member Source: 019_functions_unsecure.sql:463

`unsecure.get_user_group_by_id`¶

Returns basic information about a user group by its ID.

Parameter	Type	Default	Description
`_requested_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_user_group_id`	`integer`	--	Group ID to look up
`_tenant_id`	`integer`	`1`	Tenant context

Returns: table(__user_group_id integer, __tenant_id integer, __title text, __code text, __is_system boolean, __is_external boolean, __is_assignable boolean, __is_active boolean, __is_default boolean) Wrapped by: auth.get_user_group_by_id Source: 019_functions_unsecure.sql:488

`unsecure.get_user_group_members`¶

Returns all members of a group including membership type, user status, and mapping details.

Parameter	Type	Default	Description
`_requested_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_user_group_id`	`integer`	--	Group ID
`_tenant_id`	`integer`	`1`	Tenant context

Returns: table(__created timestamptz, __created_by text, __member_id bigint, __member_type_code text, __user_id bigint, __user_display_name text, __user_is_system boolean, __user_is_active boolean, __user_is_locked boolean, __mapping_id integer, __mapping_mapped_object_name text, __mapping_provider_code text) Wrapped by: auth.get_user_group_members Source: 019_functions_unsecure.sql:1544

`unsecure.recalculate_user_groups`¶

Recalculates a user's group memberships based on their identity provider's groups and roles. Adds user to default groups, resolves external group mappings, removes stale memberships, and clears affected permission caches.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_target_user_id`	`bigint`	--	User to recalculate
`_provider_code`	`text`	--	Identity provider code

Returns: table(__tenant_id integer, __user_group_id integer, __user_group_code text) Wrapped by: auth.has_permission (on cache miss), auth.get_users_groups_and_permissions Source: 019_functions_unsecure.sql:1658

Permission Queries¶

`unsecure.get_effective_group_permissions`¶

Returns all effective (resolved) permissions for a group, including permissions from assigned permission sets and directly assigned permissions.

Parameter	Type	Default	Description
`_requested_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_user_group_id`	`integer`	--	Group to query
`_tenant_id`	`integer`	`1`	Tenant context

Returns: table(__full_code text, __permission_title text, __perm_set_title text, __perm_set_code text, __perm_set_id integer, __assignment_id bigint) Wrapped by: auth.get_effective_group_permissions Source: 019_functions_unsecure.sql:510

`unsecure.get_assigned_group_permissions`¶

Returns the raw permission assignments for a group (not resolved/expanded), grouped by assignment.

Parameter	Type	Default	Description
`_requested_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_user_group_id`	`integer`	--	Group to query
`_tenant_id`	`integer`	`1`	Tenant context

Returns: table(__permissions jsonb, __perm_set_title text, __perm_set_id integer, __perm_set_code text, __assignment_id bigint) Wrapped by: auth.get_assigned_group_permissions Source: 019_functions_unsecure.sql:547

`unsecure.get_user_assigned_permissions`¶

Returns all permission assignments for a user (both direct and via group membership).

Parameter	Type	Default	Description
`_requested_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_target_user_id`	`bigint`	--	User to query
`_tenant_id`	`integer`	`1`	Tenant context

Returns: table(__permissions jsonb, __perm_set_title text, __perm_set_id integer, __perm_set_code text, __assignment_id bigint, __user_group_id integer) Wrapped by: auth.get_user_assigned_permissions Source: 019_functions_unsecure.sql:2093

`unsecure.get_all_permissions`¶

Returns all permissions in the system ordered by full_code.

Parameter	Type	Default	Description
`_requested_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_tenant_id`	`integer`	`1`	Tenant context

Returns: table(__permission_id integer, __is_assignable boolean, __title text, __code text, __full_code text, __has_children boolean, __short_code text, __source text) Wrapped by: auth.get_all_permissions Source: 019_functions_unsecure.sql:989

`unsecure.get_perm_sets`¶

Returns all permission sets for a tenant, each with their included permissions as a JSONB array.

Parameter	Type	Default	Description
`_requested_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_tenant_id`	`integer`	`1`	Tenant context

Returns: table(__perm_set_id integer, __title text, __code text, __is_system boolean, __is_assignable boolean, __permissions jsonb, __source text) Wrapped by: auth.get_perm_sets Source: 019_functions_unsecure.sql:1002

`unsecure.recalculate_user_permissions`¶

The core permission calculation engine. Resolves all permissions for a user across tenants by combining group-based and direct assignments. Results are cached with a configurable TTL (default 300 seconds). Returns cached results if still valid.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_target_user_id`	`bigint`	--	User to calculate permissions for
`_tenant_id`	`integer`	`null`	Specific tenant, or null for all tenants

Returns: table(__tenant_id integer, __tenant_uuid uuid, __groups text[], __permissions text[], __short_code_permissions text[]) Wrapped by: auth.has_permission, auth.get_users_groups_and_permissions Source: 019_functions_unsecure.sql:1785

Permission Management¶

`unsecure.assign_permission`¶

Assigns a permission set or individual permission to a user or group. Validates existence, assignability, and mutual exclusivity. Invalidates affected permission caches after assignment.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_user_group_id`	`integer`	`null`	Group to assign to (mutually exclusive with `_target_user_id`)
`_target_user_id`	`bigint`	`null`	User to assign to (mutually exclusive with `_user_group_id`)
`_perm_set_code`	`text`	`null`	Permission set code to assign
`_perm_code`	`text`	`null`	Individual permission code to assign
`_tenant_id`	`integer`	`1`	Tenant context

Returns: setof auth.permission_assignment Wrapped by: auth.assign_permission Source: 019_functions_unsecure.sql:580

`unsecure.assign_permission_as_system`¶

Convenience wrapper for assigning permissions as the system user.

Parameter	Type	Default	Description
`_user_group_id`	`integer`	--	Group to assign to
`_target_user_id`	`bigint`	--	User to assign to
`_perm_set_code`	`text`	--	Permission set code
`_perm_code`	`text`	`null`	Individual permission code
`_tenant_id`	`integer`	`1`	Tenant context

Returns: setof auth.permission_assignment Source: 019_functions_unsecure.sql:779

`unsecure.unassign_permission`¶

Removes a permission assignment by its ID. Invalidates affected permission caches.

Parameter	Type	Default	Description
`_deleted_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_assignment_id`	`bigint`	--	Assignment to remove
`_tenant_id`	`integer`	`1`	Tenant context

Returns: setof auth.permission_assignment Wrapped by: auth.unassign_permission Source: 019_functions_unsecure.sql:686

`unsecure.set_permission_as_assignable`¶

Changes the is_assignable flag on a permission. Invalidates caches for all affected users.

Parameter	Type	Default	Description
`_updated_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_permission_id`	`integer`	`null`	Permission ID (either this or `_permission_full_code` required)
`_permission_full_code`	`text`	`null`	Permission full code
`_is_assignable`	`boolean`	`true`	New assignability state

Returns: setof auth.permission_assignment Wrapped by: auth.set_permission_as_assignable Source: 019_functions_unsecure.sql:731

`unsecure.create_permission`¶

Creates a new permission node in the hierarchical permission tree. Handles node_path calculation, full_code generation, full_title assembly, and short_code computation.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_title`	`text`	--	Permission title
`_parent_full_code`	`text`	`null`	Parent permission code (null for root)
`_is_assignable`	`boolean`	`true`	Whether permission can be assigned
`_short_code`	`text`	`null`	Custom short code (auto-generated if null)
`_source`	`text`	`null`	Source identifier

Returns: setof auth.permission Wrapped by: auth.create_permission Source: 019_functions_unsecure.sql:906

`unsecure.create_permission_as_system`¶

Convenience wrapper for creating permissions as the system user. Used extensively in seed data scripts.

Parameter	Type	Default	Description
`_title`	`text`	--	Permission title
`_parent_code`	`text`	`''`	Parent permission code
`_is_assignable`	`boolean`	`true`	Whether permission can be assigned
`_short_code`	`text`	`null`	Custom short code
`_source`	`text`	`null`	Source identifier

Returns: setof auth.permission Source: 019_functions_unsecure.sql:980

`unsecure.compute_short_code`¶

Computes a hierarchical short code (e.g., 01.03.02) for a permission based on its position among siblings at each level of the tree.

Parameter	Type	Default	Description
`_permission_id`	`integer`	--	Permission to compute short code for

Returns: text Properties: stable Source: 019_functions_unsecure.sql:834

`unsecure.update_permission_full_title`¶

Recalculates the full_title column (e.g., "Users > Create user") for all permissions under the given path.

Parameter	Type	Default	Description
`_perm_path`	`ext.ltree`	--	Root path to recalculate under

Returns: setof auth.permission Source: 019_functions_unsecure.sql:792

`unsecure.update_permission_full_code`¶

Recalculates the full_code column (e.g., users.create_user) for all permissions under the given path.

Parameter	Type	Default	Description
`_perm_path`	`ext.ltree`	--	Root path to recalculate under

Returns: setof auth.permission Source: 019_functions_unsecure.sql:816

`unsecure.update_permission_short_code`¶

Recalculates the short_code column for all permissions under the given path using compute_short_code.

Parameter	Type	Default	Description
`_perm_path`	`ext.ltree`	--	Root path to recalculate under

Returns: setof auth.permission Source: 019_functions_unsecure.sql:895

Permission Set Management¶

`unsecure.create_perm_set`¶

Creates a new permission set with an optional initial list of permissions. Validates that all provided permissions are assignable.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_title`	`text`	--	Permission set title
`_is_system`	`boolean`	`false`	Whether this is a system permission set
`_is_assignable`	`boolean`	`true`	Whether this set can be assigned
`_permissions`	`text[]`	`null`	Array of permission full codes to include
`_tenant_id`	`integer`	`1`	Tenant context
`_source`	`text`	`null`	Source identifier

Returns: setof auth.perm_set Wrapped by: auth.create_perm_set Source: 019_functions_unsecure.sql:1028

`unsecure.create_perm_set_as_system`¶

System-level permission set creation that bypasses the is_assignable check on permissions. This allows system permission sets to include non-assignable permissions.

Parameter	Type	Default	Description
`_title`	`text`	--	Permission set title
`_is_system`	`boolean`	`false`	Whether this is a system permission set
`_is_assignable`	`boolean`	`true`	Whether this set can be assigned
`_permissions`	`text[]`	`null`	Array of permission full codes to include
`_tenant_id`	`integer`	`1`	Tenant context
`_source`	`text`	`null`	Source identifier

Returns: setof auth.perm_set Source: 019_functions_unsecure.sql:1072

`unsecure.update_perm_set`¶

Updates a permission set's title and assignability. If assignability changes, invalidates the permission cache for all affected users.

Parameter	Type	Default	Description
`_updated_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_perm_set_id`	`integer`	--	Permission set to update
`_title`	`text`	--	New title
`_is_assignable`	`boolean`	`true`	New assignability state
`_tenant_id`	`integer`	`1`	Tenant context

Returns: setof auth.perm_set Wrapped by: auth.update_perm_set Source: 019_functions_unsecure.sql:1110

`unsecure.create_perm_set_permissions`¶

Adds permissions to an existing permission set. Skips permissions that are already in the set. Invalidates the permission cache for all affected users.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_perm_set_id`	`integer`	--	Permission set to modify
`_permissions`	`text[]`	`null`	Array of permission full codes to add
`_tenant_id`	`integer`	`1`	Tenant context

Returns: table(__perm_set_id integer, __perm_set_code text, __permission_id integer, __permission_code text) Wrapped by: auth.create_perm_set_permissions Source: 019_functions_unsecure.sql:1154

`unsecure.delete_perm_set_permissions`¶

Removes permissions from a permission set. Invalidates the permission cache for all affected users.

Parameter	Type	Default	Description
`_deleted_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_perm_set_id`	`integer`	--	Permission set to modify
`_permissions`	`text[]`	`null`	Array of permission full codes to remove
`_tenant_id`	`integer`	`1`	Tenant context

Returns: table(__perm_set_id integer, __perm_set_code text, __permission_id integer, __permission_code text) Wrapped by: auth.delete_perm_set_permissions Source: 019_functions_unsecure.sql:1198

`unsecure.copy_perm_set`¶

Copies a permission set from one tenant to another (or within the same tenant with a new title). Copies all permission assignments.

Parameter	Type	Default	Description
`_created_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_source_perm_set_code`	`text`	--	Source permission set code
`_source_tenant_id`	`integer`	--	Source tenant
`_target_tenant_id`	`integer`	--	Target tenant
`_new_title`	`text`	`null`	New title (uses source title if null)

Returns: setof auth.perm_set Wrapped by: auth.copy_perm_set Source: 019_functions_unsecure.sql:2033

Owner Verification¶

`unsecure.verify_owner_or_permission`¶

Verifies that a user is either the owner of a group/tenant or has the appropriate management permission. Used by owner management operations.

Parameter	Type	Default	Description
`_user_id`	`bigint`	--	User to verify
`_correlation_id`	`text`	--	Correlation ID
`_user_group_id`	`integer`	--	Group to check ownership of (null for tenant-level)
`_tenant_id`	`integer`	`1`	Tenant context

Returns: void (raises exception if unauthorized) Source: 019_functions_unsecure.sql:267

Tenant Management¶

`unsecure.create_primary_tenant`¶

Creates the initial primary tenant during database bootstrap.

Returns: setof auth.tenant Source: 019_functions_unsecure.sql:286

`unsecure.delete_tenant`¶

Deletes a tenant and all its associated data (cascading via foreign keys).

Parameter	Type	Default	Description
`_deleted_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_tenant_id`	`integer`	--	Tenant to delete

Returns: table(__tenant_id integer, __uuid uuid, __code text) Wrapped by: auth.delete_tenant Source: 019_functions_unsecure.sql:1630

Partition & Purge Management¶

`unsecure.ensure_audit_partitions`¶

Creates monthly partitions for the journal and user_event tables N months ahead. Called by purge functions and during initial setup.

Parameter	Type	Default	Description
`_months_ahead`	`integer`	`null`	Number of months to pre-create (reads from `const.sys_param` if null, defaults to 3)

Returns: void Source: 019_functions_unsecure.sql:2134

`unsecure.purge_journal`¶

Purges old journal entries by dropping entire monthly partitions older than the retention period. Also cleans up the default partition and pre-creates future partitions.

Parameter	Type	Default	Description
`_deleted_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_older_than_days`	`integer`	`null`	Retention period in days (reads from `const.sys_param` if null)

Returns: table(__deleted_count bigint) Source: 019_functions_unsecure.sql:2185

`unsecure.purge_user_events`¶

Purges old user event entries by dropping entire monthly partitions older than the retention period. Also cleans up the default partition and pre-creates future partitions.

Parameter	Type	Default	Description
`_deleted_by`	`text`	--	Audit trail
`_user_id`	`bigint`	--	Requesting user
`_correlation_id`	`text`	--	Correlation ID
`_older_than_days`	`integer`	`null`	Retention period in days (reads from `const.sys_param` if null)

Returns: table(__deleted_count bigint) Source: 019_functions_unsecure.sql:2245

Helpers & Internal Functions¶

Helper Functions¶

String Functions¶

helpers.is_empty_string¶

helpers.is_not_empty_string¶

helpers.random_string¶

helpers.get_code¶

helpers.get_slug¶

helpers.normalize_text¶

helpers.unaccent_text¶

JSON Functions¶

helpers.compare_jsonb_objects¶

helpers.compute_jsonb_hash¶

helpers.normalize_jsonb_values¶

helpers.delete_jsonb_fields¶

Ltree Functions¶

helpers.ltree_parent¶

Journal & Storage Control¶

helpers.should_log_journal¶

helpers.is_event_read_only¶

helpers.should_store_locally¶

helpers.should_notify_storage¶

Trigger Functions¶

helpers.trg_generate_code_from_title¶

helpers.calculate_ts_regconfig¶

Error Functions¶

Security / Authentication Errors (30xxx)¶

Validation Errors (31xxx)¶

Permission Errors (32xxx)¶

User / Group Errors (33xxx)¶

Tenant Errors (34xxx)¶

Resource Access Errors (35xxx)¶

Token Configuration Errors (36xxx)¶

Language / Translation Errors (37xxx)¶

MFA Errors (38xxx)¶

Invitation Errors (39xxx)¶

Backwards Compatibility Aliases (52xxx)¶

Internal Functions¶

Identifier Resolvers¶

internal.resolve_user¶

internal.resolve_tenant¶

internal.resolve_group¶

Unsecure Functions¶

Permission Cache Management¶

unsecure.clear_permission_cache¶

unsecure.invalidate_group_members_permission_cache¶

unsecure.invalidate_perm_set_users_permission_cache¶

unsecure.invalidate_users_permission_cache¶

unsecure.invalidate_permission_users_cache¶

Group ID Cache Management¶

unsecure.get_cached_group_ids¶

unsecure.invalidate_user_group_id_cache¶

unsecure.clear_user_group_id_cache¶

unsecure.invalidate_group_members_group_id_cache¶

Notification Functions¶

unsecure.notify_permission_change¶

unsecure.notify_journal_event¶

unsecure.notify_user_event¶

User Management¶

unsecure.create_user_system¶

unsecure.create_user_info¶

unsecure.create_service_user_info¶

unsecure.create_api_user¶

unsecure.update_user_info_basic_data¶

unsecure.update_user_password¶

unsecure.delete_user_by_id¶

unsecure.delete_user_by_username_as_system¶

unsecure.update_last_used_provider¶

unsecure.assign_user_default_groups¶

User Identity Management¶

unsecure.create_user_identity¶

unsecure.update_user_identity_uid_oid¶

User Blacklist Management¶

unsecure.check_user_blacklist¶

unsecure.blacklist_user¶

unsecure.blacklist_user_identities¶

User Event Management¶

unsecure.create_user_event¶

unsecure.expire_tokens¶

Group Management¶

`helpers.is_empty_string`¶

`helpers.is_not_empty_string`¶

`helpers.random_string`¶

`helpers.get_code`¶

`helpers.get_slug`¶

`helpers.normalize_text`¶

`helpers.unaccent_text`¶

`helpers.compare_jsonb_objects`¶

`helpers.compute_jsonb_hash`¶

`helpers.normalize_jsonb_values`¶

`helpers.delete_jsonb_fields`¶

`helpers.ltree_parent`¶

`helpers.should_log_journal`¶

`helpers.is_event_read_only`¶

`helpers.should_store_locally`¶

`helpers.should_notify_storage`¶

`helpers.trg_generate_code_from_title`¶

`helpers.calculate_ts_regconfig`¶

`internal.resolve_user`¶

`internal.resolve_tenant`¶

`internal.resolve_group`¶

`unsecure.clear_permission_cache`¶

`unsecure.invalidate_group_members_permission_cache`¶

`unsecure.invalidate_perm_set_users_permission_cache`¶

`unsecure.invalidate_users_permission_cache`¶

`unsecure.invalidate_permission_users_cache`¶

`unsecure.get_cached_group_ids`¶

`unsecure.invalidate_user_group_id_cache`¶

`unsecure.clear_user_group_id_cache`¶

`unsecure.invalidate_group_members_group_id_cache`¶

`unsecure.notify_permission_change`¶

`unsecure.notify_journal_event`¶

`unsecure.notify_user_event`¶

`unsecure.create_user_system`¶

`unsecure.create_user_info`¶

`unsecure.create_service_user_info`¶

`unsecure.create_api_user`¶

`unsecure.update_user_info_basic_data`¶

`unsecure.update_user_password`¶

`unsecure.delete_user_by_id`¶

`unsecure.delete_user_by_username_as_system`¶

`unsecure.update_last_used_provider`¶

`unsecure.assign_user_default_groups`¶

`unsecure.create_user_identity`¶

`unsecure.update_user_identity_uid_oid`¶

`unsecure.check_user_blacklist`¶

`unsecure.blacklist_user`¶

`unsecure.blacklist_user_identities`¶

`unsecure.create_user_event`¶

`unsecure.expire_tokens`¶

`unsecure.create_user_group`¶

`unsecure.create_user_group_as_system`¶

`unsecure.create_user_group_member`¶

`unsecure.create_user_group_member_as_system`¶

`unsecure.get_user_group_by_id`¶

`unsecure.get_user_group_members`¶

`unsecure.recalculate_user_groups`¶

`unsecure.get_effective_group_permissions`¶

`unsecure.get_assigned_group_permissions`¶

`unsecure.get_user_assigned_permissions`¶

`unsecure.get_all_permissions`¶

`unsecure.get_perm_sets`¶

`unsecure.recalculate_user_permissions`¶

`unsecure.assign_permission`¶

`unsecure.assign_permission_as_system`¶

`unsecure.unassign_permission`¶

`unsecure.set_permission_as_assignable`¶

`unsecure.create_permission`¶

`unsecure.create_permission_as_system`¶

`unsecure.compute_short_code`¶

`unsecure.update_permission_full_title`¶

`unsecure.update_permission_full_code`¶

`unsecure.update_permission_short_code`¶

`unsecure.create_perm_set`¶

`unsecure.create_perm_set_as_system`¶

`unsecure.update_perm_set`¶

`unsecure.create_perm_set_permissions`¶

`unsecure.delete_perm_set_permissions`¶

`unsecure.copy_perm_set`¶

`unsecure.verify_owner_or_permission`¶