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.
Before the tenant row is inserted, the resolved code (either _code or helpers.get_code(_title)) is written to the permanent auth.tenant_identity ledger. Because that ledger has a unique(code) constraint and is never purged, a code that has ever been used — by a live, soft-deleted, or purged tenant — cannot be reused: the call raises error 34004.
| 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 |
_tenant_id |
integer | 1 |
Tenant context for the permission check |
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
Raises: 34004 (tenant code previously used and cannot be reused)
Source: 023_functions_auth_tenant.sql:433
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:525
Tenant Lifecycle: Soft Delete, Restore, Purge¶
Tenant deletion is a two-stage, reversible-by-default process:
- Soft delete (
auth.delete_tenant) — marks the tenantdeleted_at/deleted_by, clears its permission and group caches, and blocks access. All child data is retained and the tenant can be brought back. - Restore (
auth.restore_tenant) — reverses a soft delete. - Purge (
auth.purge_tenant) — permanent hard delete. Destroys all child data and removes the tenant row. Requires the tenant to be soft-deleted first and is gated behind a separatetenants.purge_tenantpermission.
The system tenant (id 1) and any tenant with is_removable = false are rejected from both delete and purge (error 34007).
A tenant code can never be reused
Every tenant code is recorded in the append-only auth.tenant_identity ledger at creation and is permanently burned — even after a purge. Re-creating a tenant with a previously-used code (or a title that derives the same code via helpers.get_code()) raises error 34004. See auth.create_tenant.
auth.delete_tenant¶
Soft-deletes a tenant identified by its UUID: stamps deleted_at/deleted_by, clears the tenant's permission and group caches (so users lose access immediately), and emits the tenant_soft_deleted event. Child data is retained — use auth.restore_tenant to reverse or auth.purge_tenant to permanently remove.
| 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 soft-delete |
_tenant_id |
integer | 1 |
Tenant context for the permission check |
Returns: table(__tenant_id integer, __uuid uuid, __code text)
Permission required: tenants.delete_tenant
Raises: 34003 (tenant not found), 34005 (already soft-deleted), 34007 (system / non-removable tenant)
Journal event: 11004 (tenant_soft_deleted)
Source: 023_functions_auth_tenant.sql:157
auth.delete_tenant_by_uuid¶
Thin wrapper over auth.delete_tenant (identical soft-delete behavior); kept as an explicit by-UUID alias for 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 soft-delete |
_tenant_id |
integer | 1 |
Tenant context for the permission check |
Returns: table(__tenant_id integer, __uuid uuid, __code text)
Permission required: tenants.delete_tenant
Source: 023_functions_auth_tenant.sql:189
auth.restore_tenant¶
Reverses a soft delete: clears deleted_at/deleted_by and emits tenant_restored. The tenant must currently be soft-deleted (and not purged).
| Parameter | Type | Default | Description |
|---|---|---|---|
_restored_by |
text | -- | Identifier of the user performing the restore |
_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 restore |
_tenant_id |
integer | 1 |
Tenant context for the permission check |
Returns: table(__tenant_id integer, __uuid uuid, __code text)
Permission required: tenants.delete_tenant (reuses the soft-delete permission)
Raises: 34003 (tenant not found), 34006 (tenant is not soft-deleted)
Journal event: 11005 (tenant_restored)
Source: 023_functions_auth_tenant.sql:204
auth.purge_tenant¶
Permanently deletes a soft-deleted tenant. Runs the full cascade cleanup (unsecure.delete_tenant) — removing all groups, permission sets, assignments, resource ACLs, and related data — stamps the identity ledger's purged_at, removes the tenant row, and emits tenant_purged. Irreversible.
| Parameter | Type | Default | Description |
|---|---|---|---|
_deleted_by |
text | -- | Identifier of the user performing the purge |
_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 purge |
_tenant_id |
integer | 1 |
Tenant context for the permission check |
Returns: table(__tenant_id integer, __uuid uuid, __code text)
Permission required: tenants.purge_tenant
Raises: 34003 (tenant not found), 34006 (tenant not soft-deleted first), 34007 (system / non-removable tenant)
Journal event: 11006 (tenant_purged)
Source: 023_functions_auth_tenant.sql:234
Tenant Queries¶
auth.get_tenants¶
Returns all tenants with full metadata, ordered by title. Cross-tenant behavior follows the standard pattern: from the admin tenant (_tenant_id = 1) with tenants.get_all_tenants it returns every tenant, otherwise only the caller's own.
This is a management view — soft-deleted tenants remain listed (so admins can find them to restore or purge), and the new __deleted_at / __purged_at columns expose their status. Access is blocked separately at the switch boundary (see auth.get_user_available_tenants and auth.update_user_last_selected_tenant).
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | ID of the acting user (for permission check) |
_correlation_id |
text | -- | Correlation ID for audit trail |
_tenant_id |
integer | 1 |
Caller's tenant context |
_target_tenant_id |
integer | null |
Cross-tenant: restrict to a specific tenant's data (admin only) |
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, __deleted_at timestamptz, __purged_at timestamptz)
Permission required: tenants.get_tenants (own tenant) / tenants.get_all_tenants (cross-tenant)
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:46
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:419
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 (own tenant) / tenants.read_all_tenants (cross-tenant)
Source: 023_functions_auth_tenant.sql:571
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 |
_target_tenant_id |
integer | null |
Cross-tenant: query a specific tenant's data (admin only) |
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 (own tenant) / tenants.get_all_users (cross-tenant)
Source: 023_functions_auth_tenant.sql:65
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 |
_target_tenant_id |
integer | null |
Cross-tenant: query a specific tenant's data (admin only) |
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 (own tenant) / tenants.get_all_groups (cross-tenant)
Source: 023_functions_auth_tenant.sql:96
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 |
_target_tenant_id |
integer | null |
Cross-tenant: query a specific tenant's data (admin only) |
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 (own tenant) / tenants.get_all_tenants (cross-tenant)
Source: 023_functions_auth_tenant.sql:124
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.
Soft-deleted tenants are excluded — this is one of the block-at-boundary enforcement points, so a user can never switch into or operate under a soft-deleted tenant.
| 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 |
_tenant_id |
integer | 1 |
Tenant context for the permission check |
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:266
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:299
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:319
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:346
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) and that the tenant is not soft-deleted — the second block-at-boundary enforcement point. Raises error 52108 if the tenant is not found, is soft-deleted, 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:371
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