Tenant Management Functions¶
Functions for creating, updating, deleting, and querying tenants, as well as managing tenant users, tenant preferences, and the user's last-selected tenant. All permission-checked functions live in the auth schema.
Source: 023_functions_auth_tenant.sql
Tenant CRUD¶
auth.create_tenant¶
Creates a new tenant along with default "Tenant Admins" and "Tenant Members" groups, copies the tenant_admin and tenant_member permission sets from the primary tenant, and optionally assigns a tenant owner.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user performing the action |
_user_id |
bigint | -- | ID of the acting user (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_title |
text | -- | Display title for the new tenant |
_code |
text | null |
Short code; auto-generated from _title via helpers.get_code() if omitted |
_is_removable |
boolean | true |
Whether the tenant can be deleted later |
_is_assignable |
boolean | true |
Whether users can be assigned to this tenant |
_tenant_owner_id |
bigint | null |
User ID to assign as tenant owner |
Returns: table(__tenant_id integer, __uuid uuid, __title text, __code text, __is_removable boolean, __is_assignable boolean, __access_type_code text, __is_default boolean)
Permission required: tenants.create_tenant
Source: 023_functions_auth_tenant.sql:341
auth.update_tenant¶
Updates an existing tenant's title, code, removability, and assignability flags. Unchanged optional parameters preserve their current values. Optionally assigns a tenant owner.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user performing the action |
_user_id |
bigint | -- | ID of the acting user (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_tenant_id |
integer | -- | ID of the tenant to update |
_title |
text | -- | New display title |
_code |
text | null |
New short code; keeps existing value if null |
_is_removable |
boolean | null |
New removability flag; keeps existing value if null |
_is_assignable |
boolean | null |
New assignability flag; keeps existing value if null |
_tenant_owner_id |
bigint | null |
User ID to assign as tenant owner |
Returns: table(__tenant_id integer, __uuid uuid, __title text, __code text, __is_removable boolean, __is_assignable boolean, __access_type_code text, __is_default boolean)
Permission required: tenants.update_tenant
Source: 023_functions_auth_tenant.sql:415
auth.delete_tenant¶
Deletes a tenant identified by its UUID. Delegates the actual deletion to unsecure.delete_tenant which handles cascading cleanup of groups, permission sets, assignments, and related data.
| Parameter | Type | Default | Description |
|---|---|---|---|
_deleted_by |
text | -- | Identifier of the user performing the deletion |
_user_id |
bigint | -- | ID of the acting user (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_tenant_uuid |
uuid | -- | UUID of the tenant to delete |
Returns: table(__tenant_id integer, __uuid uuid, __code text)
Permission required: tenants.delete_tenant
Source: 023_functions_auth_tenant.sql:140
auth.delete_tenant_by_uuid¶
Identical to auth.delete_tenant -- deletes a tenant by UUID. This is an explicit alias that makes the lookup-by-UUID intent clear in calling code.
| Parameter | Type | Default | Description |
|---|---|---|---|
_deleted_by |
text | -- | Identifier of the user performing the deletion |
_user_id |
bigint | -- | ID of the acting user (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_tenant_uuid |
uuid | -- | UUID of the tenant to delete |
Returns: table(__tenant_id integer, __uuid uuid, __code text)
Permission required: tenants.delete_tenant
Source: 023_functions_auth_tenant.sql:158
Tenant Queries¶
auth.get_tenants¶
Returns all tenants with full metadata, ordered by title. Requires the calling user to hold the tenants.get_tenants permission.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | ID of the acting user (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
Returns: table(__created_at timestamptz, __created_by text, __updated_at timestamptz, __updated_by text, __tenant_id integer, __uuid text, __title text, __code text, __is_removable boolean, __is_assignable boolean)
Permission required: tenants.get_tenants
Source: 023_functions_auth_tenant.sql:13
auth.get_tenant_by_id¶
Returns a single tenant by its integer ID. This is a lightweight sql function with no permission check -- intended for internal or trusted contexts.
| Parameter | Type | Default | Description |
|---|---|---|---|
_tenant_id |
integer | 1 |
ID of the tenant to retrieve |
Returns: table(__created_at timestamptz, __created_by text, __updated_at timestamptz, __updated_by text, __tenant_id integer, __uuid text, __title text, __code text, __is_removable boolean, __is_assignable boolean)
Permission required: None
Source: 023_functions_auth_tenant.sql:38
auth.get_all_tenants¶
Returns all tenants (ID, UUID, code, title) ordered by title. A lightweight sql function with no permission check -- suitable for dropdowns or internal tenant listings.
| Parameter | Type | Default | Description |
|---|---|---|---|
| (none) | -- | -- | -- |
Returns: table(__tenant_id integer, __tenant_uuid text, __tenant_code text, __tenant_title text)
Permission required: None
Source: 023_functions_auth_tenant.sql:327
auth.search_tenants¶
Paginated, text-searchable tenant listing. Searches against the tenant's normalized search data column. Returns a __total_items count alongside each row for pagination UI support.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | ID of the acting user (for permission check) |
_correlation_id |
text | null | Correlation ID for audit trail |
_search_criteria |
jsonb | null | Search filters (see criteria keys below) |
_page |
integer | 1 |
Page number (1-based) |
_page_size |
integer | 30 |
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 |
|---|---|---|
search_text |
text | Free-text search string; normalized internally via helpers.normalize_text() |
Returns: table(__tenant_id integer, __uuid text, __title text, __code text, __is_removable boolean, __is_assignable boolean, __total_items bigint)
Permission required: tenants.read_tenants
Source: 023_functions_auth_tenant.sql:472
Tenant Users & Groups¶
auth.get_tenant_users¶
Returns all users belonging to a tenant, with their group memberships aggregated into a JSON text array per user.
| Parameter | Type | Default | Description |
|---|---|---|---|
_requested_by |
text | -- | Identifier of the requesting user |
_user_id |
bigint | -- | ID of the acting user (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_tenant_id |
integer | 1 |
Tenant to query |
Returns: table(__user_id bigint, __username text, __display_name text, __user_groups text[])
Each element in __user_groups is a JSON object string with keys user_group_id, code, and title.
Permission required: tenants.get_users (tenant-scoped)
Source: 023_functions_auth_tenant.sql:57
auth.get_tenant_groups¶
Returns all groups in a tenant with a count of their members.
| Parameter | Type | Default | Description |
|---|---|---|---|
_requested_by |
text | -- | Identifier of the requesting user |
_user_id |
bigint | -- | ID of the acting user (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_tenant_id |
integer | 1 |
Tenant to query |
Returns: table(__user_group_id integer, __group_code text, __group_title text, __is_external boolean, __is_assignable boolean, __is_active boolean, __members_count bigint)
Permission required: tenants.get_groups (tenant-scoped)
Source: 023_functions_auth_tenant.sql:86
auth.get_tenant_members¶
Returns all users in a tenant along with their group memberships as a JSON array string. Similar to auth.get_tenant_users but returns additional user fields (code, uuid) and formats groups differently.
| Parameter | Type | Default | Description |
|---|---|---|---|
_requested_by |
text | -- | Identifier of the requesting user |
_user_id |
bigint | -- | ID of the acting user (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_tenant_id |
integer | 1 |
Tenant to query |
Returns: table(__user_id bigint, __user_display_name text, __user_code text, __user_uuid text, __user_tenant_groups text)
The __user_tenant_groups column is a JSON array string of objects with keys user_group_id, group_title, and group_code.
Permission required: tenants.get_tenants (tenant-scoped)
Source: 023_functions_auth_tenant.sql:112
auth.get_user_available_tenants¶
Returns all tenants that a target user is a member of (via group membership). If the calling user queries their own tenants, no permission is required. Querying another user's tenants requires the users.get_available_tenants permission.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | ID of the acting user |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | ID of the user whose available tenants to retrieve |
Returns: table(__tenant_id integer, __tenant_uuid text, __tenant_code text, __tenant_title text, __tenant_is_default boolean)
Permission required: users.get_available_tenants (only when _user_id <> _target_user_id)
Source: 023_functions_auth_tenant.sql:176
User Tenant Preferences¶
auth.create_user_tenant_preferences¶
Creates a new tenant-specific preferences record for a user, storing arbitrary JSON data. Users can create their own preferences without permission; creating preferences for another user requires the users.create_user_tenant_preferences permission.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user performing the action |
_user_id |
bigint | -- | ID of the acting user |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | ID of the user whose preferences to create |
_update_data |
text | -- | JSON string of preference data |
_tenant_id |
integer | 1 |
Tenant the preferences apply to |
Returns: table(__created_at timestamptz, __created_by varchar)
Permission required: users.create_user_tenant_preferences (tenant-scoped, only when acting on another user)
Source: 023_functions_auth_tenant.sql:208
auth.update_user_tenant_preferences¶
Updates a user's tenant-specific preferences. By default, the new JSON data is merged into the existing preferences (|| operator). Set _should_overwrite_data to true to replace the entire preferences object. Users can update their own preferences without permission; updating another user's preferences requires the users.update_user_tenant_preferences permission.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of the user performing the action |
_user_id |
bigint | -- | ID of the acting user |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | ID of the user whose preferences to update |
_update_data |
text | -- | JSON string of preference data to merge or replace |
_should_overwrite_data |
boolean | false |
If true, replaces entire preferences; if false, merges with existing |
_tenant_id |
integer | 1 |
Tenant the preferences apply to |
Returns: table(__updated_at timestamptz, __updated_by varchar)
Permission required: users.update_user_tenant_preferences (tenant-scoped, only when acting on another user)
Source: 023_functions_auth_tenant.sql:228
Last Selected Tenant¶
auth.get_user_last_selected_tenant¶
Returns the tenant that the target user last selected (stored in auth.user_info.last_selected_tenant_id). Returns an empty set if no tenant has been selected. Users can query their own last-selected tenant without permission; querying another user's requires the users.get_data permission.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | ID of the acting user |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | ID of the user whose last selected tenant to retrieve |
Returns: table(__tenant_id integer, __tenant_uuid text, __tenant_code text, __tenant_title text)
Permission required: users.get_data (only when _user_id <> _target_user_id)
Source: 023_functions_auth_tenant.sql:255
auth.update_user_last_selected_tenant¶
Updates which tenant the target user last selected. Validates that the acting user is actually a member of the specified tenant (via group membership). Raises error 52108 if the tenant is not found or the user is not a member. Logs a journal entry when an admin updates another user's selection.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text | -- | Identifier of the user performing the action |
_user_id |
bigint | -- | ID of the acting user |
_correlation_id |
text | -- | Correlation ID for audit trail |
_target_user_id |
bigint | -- | ID of the user whose last selected tenant to update |
_tenant_uuid |
text | -- | UUID of the tenant to set as last selected |
Returns: table(__used_id bigint, __tenant_id integer)
Permission required: users.update_last_selected_tenant (only when _user_id <> _target_user_id)
Source: 023_functions_auth_tenant.sql:280
See Also¶
- Multi-Tenancy -- conceptual overview of the multi-tenant architecture
- Permissions & Authorization -- permission set management and assignment functions
- Group Management -- group CRUD and membership functions used by tenant operations
- Ownership -- tenant and group owner assignment
- Search Functions -- additional search functions across the system