Search Functions¶

All list/search endpoints in the PostgreSQL Permissions Model follow a shared pagination and text-search convention. This page documents that common pattern and provides a quick-reference index of every search function, organized by domain.

For full parameter tables and return types, follow the links to each function's domain page.

Common Pagination Pattern¶

Every search function accepts at least _page and _page_size parameters and returns a __total_items column alongside the result rows.

Parameters¶

Parameter	Type	Typical Default	Description
`_page`	integer	`1`	1-based page number. A `null` value is coalesced to `1`.
`_page_size`	integer	`10` or `30`	Maximum rows per page. Capped at 100 regardless of the value passed in.

The capping logic used by most functions is:

_page := coalesce(_page, 1);
_page_size := least(coalesce(_page_size, 30), 100);

Some older functions (e.g. search_api_keys) use equivalent case expressions instead of coalesce, but the effect is the same.

Default page size varies

Most entity-search functions default to 30 rows per page (search_users, search_user_groups, search_user_group_mappings, search_permissions, search_perm_sets, search_tenants, search_blacklist). Audit and journal functions default to 10 (search_user_events, search_journal, search_journal_msgs, search_api_keys, search_outbound_api_keys, search_translations).

Total Items in Every Row¶

Each search function returns a __total_items bigint column that carries the total number of rows matching the filter criteria (before pagination). This is computed once via a count(*) over () window function inside a CTE, then joined back to the result set. The value is the same in every returned row and lets callers compute total pages without a second query:

select *
from auth.search_users(42, 'corr-id', '{"search_text": "john"}'::jsonb, _page := 2, _page_size := 20);
-- Every row contains __total_items = <total matching rows>
-- Total pages = ceil(__total_items / 20.0)

Offset Calculation¶

Pagination is offset-based, computed as:

offset ((_page - 1) * _page_size) limit _page_size

Page 1 returns the first _page_size rows, page 2 skips the first _page_size rows, and so on.

Unified Search Criteria¶

All search functions use a single _search_criteria jsonb parameter instead of individual filter parameters. Domain-specific filters are passed as JSON keys within this object. Keys that are omitted or null apply no filtering for that field.

Common Signature Pattern¶

Every search function follows this parameter pattern:

Parameter	Type	Default	Description
`_user_id`	bigint	--	User ID of the caller (for permission check)
`_correlation_id`	text	`null`	Correlation ID for audit trail
`_search_criteria`	jsonb	`null`	JSON object with domain-specific filter keys (see each function)
`_page`	integer	`1`	Page number (1-based)
`_page_size`	integer	`30` or `10`	Page size (capped at 100)
`_tenant_id`	integer	`1`	Tenant context
`_target_tenant_id`	integer	`null`	Cross-tenant: query a specific tenant's data (admin only; not present on all functions)

Example¶

-- Search for active users matching "john"
select * from auth.search_users(1, null, '{"search_text": "john", "is_active": true}'::jsonb);

-- Search for external groups
select * from auth.search_user_groups(1, null, '{"is_external": true}'::jsonb);

-- Search user events in a date range
select * from auth.search_user_events(1, null, '{"from": "2025-01-01", "to": "2025-12-31"}'::jsonb);

Criteria Keys by Function¶

Function	Criteria Keys
`auth.search_users`	`search_text`, `user_type_code`, `is_active`, `is_locked`
`auth.search_blacklist`	`search_text`, `reason`
`auth.search_user_group_mappings`	`search_text`, `provider_code`, `mapped_object_id`, `mapped_role`
`auth.search_user_groups`	`search_text`, `is_active`, `is_external`, `is_system`
`auth.search_permissions`	`search_text`, `is_assignable`, `parent_code`, `source`
`auth.search_perm_sets`	`search_text`, `is_assignable`, `is_system`, `source`
`auth.search_tenants`	`search_text`
`auth.search_api_keys`	`search_text`
`auth.search_outbound_api_keys`	`search_text`, `service_code`
`auth.search_user_events`	`event_type_code`, `target_user_id`, `request_context`, `correlation_id`, `from`, `to`
`auth.get_user_audit_trail`	`target_user_id`, `from`, `to`
`auth.get_security_events`	`from`, `to`

Text Normalization¶

Most search functions perform accent-insensitive, case-insensitive text matching via the helpers.normalize_text function:

select case when _lower_text then lower(ext.unaccent(_text)) else ext.unaccent(_text) end;

This strips diacritics (using the PostgreSQL unaccent extension) and lowercases the input. Both the search text and the stored data go through the same normalization, so a search for "resume" matches a stored value of "Resume" or "Resumé".

nrm_search_data Column¶

Most searchable tables have a nrm_search_data text column maintained by database triggers. When a row is inserted or updated, a trigger function concatenates the relevant text fields through helpers.normalize_text and stores the result. The search function then matches the user's (also normalized) query against this pre-computed column:

-- Example from the user_info trigger:
return concat_ws(' ', helpers.normalize_text(_user_info.username)
    , helpers.normalize_text(_user_info.display_name)
    , helpers.normalize_text(_user_info.email)
);

The like '%' || __search_text || '%' pattern performs a substring match against the normalized data. This means search terms match anywhere within the concatenated fields.

Fields Indexed for Search¶

Each entity pre-computes different fields into nrm_search_data:

Entity	Fields in `nrm_search_data`	Trigger Function
Users (`user_info`)	`username`, `display_name`, `email`	`triggers.calculate_user_info_search_values`
User data (`user_data`)	(returns null by default -- customize per application)	`triggers.calculate_user_data_search_values`
Groups (`user_group`)	`title`, `code`, `source`	`triggers.calculate_user_group_search_values`
Tenants (`tenant`)	`title`, `code`	`triggers.calculate_tenant_search_values`
Permissions (`permission`)	`code`, `full_code`, `source`	`triggers.calculate_permission_search_values`
Permission sets (`perm_set`)	`code`, `source`	`triggers.calculate_perm_set_search_values`
API keys (`api_key`)	`title`, `description`	`triggers.calculate_api_key_search_values`

Customizing user_data search

The triggers.calculate_user_data_search_values function returns null by default. Override it in your migration scripts to include any custom columns you have added to auth.user_data, for example employee_number or department.

Permission Checks¶

All search functions in the auth and public schemas require a permission check. The caller must pass _user_id (and usually _correlation_id) so the system can verify the user holds the required permission before returning results.

Function	Required Permission
`auth.search_users`	`users.read_users` / `users.read_all_users`
`auth.search_blacklist`	`users.search_blacklist`
`auth.search_user_groups`	`groups.get_group` / `groups.get_all_groups`
`auth.search_user_group_mappings`	`groups.get_mapping` / `groups.get_all_mappings`
`auth.search_permissions`	`permissions.read_permissions`
`auth.search_perm_sets`	`permissions.read_perm_sets` / `permissions.read_all_perm_sets`
`auth.search_tenants`	`tenants.read_tenants` / `tenants.read_all_tenants`
`auth.search_api_keys`	`api_keys.search` / `api_keys.search_all`
`auth.search_outbound_api_keys`	`api_keys.search` / `api_keys.search_all`
`auth.search_user_events`	`authentication.read_user_events` / `authentication.read_all_user_events`
`auth.get_user_audit_trail`	`authentication.read_user_events` / `authentication.read_all_user_events`
`auth.get_security_events`	`authentication.read_user_events` / `authentication.read_all_user_events`
`public.search_journal`	`journal.read_global_journal` or `journal.read_journal`
`public.search_journal_msgs`	(delegates to `search_journal`)
`public.search_translations`	`translations.read_translations`

Query Structure¶

All search functions follow the same two-CTE query pattern:

filtered_rows CTE -- applies all where filters, computes count(*) over () as total_items, applies order by, and performs offset ... limit pagination. Only primary key IDs are selected here for efficiency.
Main query -- joins the filtered IDs back to the full table (and sometimes additional tables for computed columns like member_count or permission_count) to return the complete result set.

This pattern keeps the window-function count accurate (it counts all matching rows, not just the page) while only fetching full row data for the current page.

Search Functions by Domain¶

Users¶

Function	Description	Full Docs
`auth.search_users`	Paginated search across users with filters for user type, active/locked status, and free-text search. Ordered by `display_name`.	users.md
`auth.search_blacklist`	Paginated search of the user blacklist with filters for `reason` and free-text matching on username, provider UID/OID, and notes. Ordered by `created_at desc`.	users.md

Groups¶

Function	Description	Full Docs
`auth.search_user_groups`	Paginated search across groups with filters for active, external, and system status. Includes `member_count` and tenant info (`tenant_id`, `tenant_code`, `tenant_title`) in results. Ordered by `title`.	groups.md
`auth.search_user_group_mappings`	Paginated search across group mappings with filters for provider, mapped object ID, role, and free-text search. Ordered by group title, provider code.	groups.md

Permissions¶

Function	Description	Full Docs
`auth.search_permissions`	Paginated search of the global permission tree with filters for `is_assignable`, `parent_code` (subtree via ltree), and `source`. Ordered by `full_code`.	permissions.md
`auth.search_perm_sets`	Paginated search of tenant-specific permission sets with filters for `is_assignable`, `is_system`, and `source`. Includes `permission_count`. Ordered by `code`.	permissions.md

Tenants¶

Function	Description	Full Docs
`auth.search_tenants`	Paginated search across tenants with free-text search. Supports cross-tenant queries. Ordered by `title`.	tenants.md

API Keys¶

Function	Description	Full Docs
`auth.search_api_keys`	Paginated search of inbound API keys with free-text search on title. Ordered by `title, api_key`.	api-keys.md
`auth.search_outbound_api_keys`	Paginated search of outbound API keys with optional `service_code` filter and free-text search on title and service code. Ordered by `service_code, title`.	api-keys.md

Audit & Events¶

Function	Description	Full Docs
`auth.search_user_events`	Paginated search of user events with filters for event type, target user, date range, request context (JSONB containment), and correlation ID. Ordered by `created_at desc`.	audit-events.md
`auth.get_user_audit_trail`	Unified timeline of journal entries and user events for a specific user, with date range filtering. Ordered by `created_at desc`.	audit-events.md
`auth.get_security_events`	Aggregated view of security-relevant events (failed logins, lockouts, permission denials) with date range filtering. Ordered by `created_at desc`.	audit-events.md
`public.search_journal`	Paginated search of journal entries with filters for event ID, event category, target user, date range, correlation ID, and JSONB containment on keys, payload, and request context. Ordered by `created_at desc`.	audit-events.md
`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. Use `search_journal` for new code.	audit-events.md

Language & Translation¶

Function	Description	Full Docs
`public.search_translations`	Paginated search of translations with filters for language code, data group, data object code/ID, and free-text search. Ordered by `data_group, data_object_code, language_code`.	language-translation.md

Usage Example¶

A typical search call with pagination and text filtering:

-- Search for users matching "john", page 2, 20 results per page
select * from auth.search_users(
    _user_id := 1,
    _correlation_id := 'req-abc-123',
    _search_criteria := '{"search_text": "john", "is_active": true}'::jsonb,
    _page := 2,
    _page_size := 20
);

The result set includes __total_items in every row, allowing the caller to compute total pages:

 __user_id | __code | __username | __display_name | ... | __total_items
-----------+--------+------------+----------------+-----+--------------
        42 | U042   | john.doe   | John Doe       | ... |            57
        99 | U099   | john.smith | John Smith     | ... |            57

With 57 total matching users and a page size of 20, there are ceil(57 / 20.0) = 3 pages. Page 2 returns rows 21-40.