App Bootstrapping (Ensure Functions)¶
Idempotent functions for application startup that declaratively ensure required configuration exists. Each function follows a "create-if-not-exists" pattern -- if the resource already exists (matched by code or key), it is returned as-is without modification; if it does not exist, it is created and returned.
Source files: 024_functions_auth_provider.sql, 020_functions_auth_user.sql, 021_functions_auth_group.sql, 022_functions_auth_permission.sql, 035_functions_resource_access.sql
Overview¶
The ensure pattern solves a common problem: application startup code needs to guarantee that certain providers, groups, permissions, and permission sets exist in the database. Without ensure functions, applications would need to query first, then conditionally create -- handling race conditions and error states themselves.
Ensure functions encapsulate this logic:
- Check if the resource already exists (by code, username, or provider identity).
- Create the resource if it does not exist, delegating to the underlying
create_*orunsecure.create_*function. - Return the resource (existing or newly created) so the caller can use it immediately.
There are two categories of ensure functions:
- Single-item ensure functions -- operate on one resource at a time (
ensure_provider,ensure_user_info,ensure_user_from_provider,ensure_user_group_mapping). - Batch ensure functions -- accept a JSONB array and process multiple resources in a single call (
ensure_user_groups,ensure_user_group_mappings,ensure_permissions,ensure_perm_sets,ensure_resource_types). Batch functions also support a "final state" mode.
Final state mode¶
Several batch ensure functions support a _is_final_state parameter. When set to true, the function treats the provided list as the complete desired state for that source/tenant combination. After creating any missing items, it deletes items that:
- Share the same
_sourcetag and_tenant_id(where applicable) - Are not present in the provided input list
- Are not system-protected
This allows applications to declaratively say "these are the only groups/permissions/perm sets that should exist from my application" -- any that were previously created by the same source but are no longer in the list get cleaned up automatically.
Warning
Final state mode performs deletions with cascading cleanup (permission assignments, group mappings, cache invalidation). Use it only when you genuinely want the input to be the authoritative list. The _source parameter is required when _is_final_state is true.
Providers¶
auth.ensure_provider¶
Creates an identity provider if it does not already exist (matched by _provider_code), or returns the existing provider's ID.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the action |
_user_id |
bigint | -- | ID of the user performing the action (used for permission check if creating) |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_provider_code |
text | -- | Unique short code for the provider (e.g. 'azuread', 'google') |
_provider_name |
text | -- | Human-readable name, stored as a translation (used only when creating) |
_is_active |
boolean | true |
Whether the provider is active (used only when creating) |
_allows_group_mapping |
boolean | false |
Whether the provider supports group mapping (used only when creating) |
_allows_group_sync |
boolean | false |
Whether the provider supports group sync (used only when creating) |
Returns: table(__provider_id integer, __is_new boolean) -- the provider's ID and whether it was newly created.
Permission required: providers.create_provider (only checked when creating a new provider)
Source: 024_functions_auth_provider.sql:51
Note
When the provider already exists, ensure_provider does not update the existing record -- it returns the existing ID with __is_new = false. Use auth.update_provider to modify an existing provider.
Users¶
auth.ensure_user_info¶
Creates a user if the username does not already exist, or returns the existing user's basic info.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the action |
_user_id |
bigint | -- | ID of the user performing the action |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_username |
text | -- | Username to look up or create (trimmed and lowercased automatically) |
_display_name |
text | -- | Display name for the user (used only when creating) |
_provider_code |
text | null |
Provider code to associate with the new user (used only when creating) |
_email |
text | null |
Email address (lowercased, used only when creating) |
_user_data |
jsonb | null |
Custom user data (used only when creating) |
Returns: table(__user_id bigint, __code text, __uuid text, __username text, __email text, __display_name text)
Permission required: None (delegates to unsecure.create_user_info which has no permission check)
Source: 020_functions_auth_user.sql:539
Note
Matching is done by username only (case-insensitive). If the user already exists, the existing record is returned without updating any fields.
auth.ensure_user_from_provider¶
Ensures a user exists for the given identity provider. If the user identity is not found (by _provider_uid or _provider_oid), a new user and identity are created. If the user already exists, the function validates that the user is active, not locked, and the identity is enabled -- then updates basic data if changed and logs a login event.
This is the primary function called during provider-based login flows (OAuth, SAML, LDAP, etc.).
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the action |
_user_id |
bigint | -- | ID of the user performing the action |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_provider_code |
text | -- | Provider code (e.g. 'azuread') -- must not be 'email' |
_provider_uid |
text | -- | Provider-specific user identifier (e.g. Azure AD UID) |
_provider_oid |
text | -- | Provider-specific object identifier (e.g. Azure AD OID) |
_username |
text | -- | Username from the provider |
_display_name |
text | -- | Display name from the provider |
_email |
text | null |
Email address from the provider |
_user_data |
jsonb | null |
Custom user data from the provider |
_request_context |
jsonb | null |
Request context (IP address, user agent, etc.) for audit events |
Returns: table(__user_id bigint, __code text, __uuid text, __username text, __email text, __display_name text)
Permission required: None
Source: 020_functions_auth_user.sql:647
Behavior when user is new:
- Checks the blacklist by provider identity.
- Creates the user via
unsecure.create_user_info. - Creates the user identity via
unsecure.create_user_identity. - Logs a
user_registeredevent.
Behavior when user already exists:
- Updates
provider_uid/provider_oidif changed. - Updates basic user data (username, display name, email) if any changed.
- Checks
can_login-- raises52112if login is disabled. - Checks
is_activeon user -- raises52105if user is disabled. - Checks
is_activeon identity -- raises52110if identity is disabled. - Cleans up duplicate UIDs for the same provider.
- Sets this provider as the "last used" provider.
- Logs a
user_logged_inevent.
Warning
The 'email' provider code is explicitly blocked (raises 52101). Email-based registration uses auth.register_user instead.
Groups¶
auth.ensure_user_group_mapping¶
Creates a group mapping if the exact combination of group, provider, mapped object, and role does not already exist.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the action |
_user_id |
bigint | -- | ID of the user performing the action (used for permission check if creating) |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_user_group_id |
integer | -- | ID of the group to map to |
_provider_code |
text | -- | Provider code for this mapping |
_mapped_object_id |
text | null |
External group/object ID to map (lowercased) |
_mapped_object_name |
text | null |
Human-readable name for the mapped object |
_mapped_role |
text | null |
External role to map (lowercased) |
_tenant_id |
integer | 1 |
Tenant ID for permission checks |
Returns: table(__user_group_mapping_id integer, __user_group_id integer, __is_new boolean) -- the mapping ID, group ID, and whether it was newly created.
Permission required: groups.create_mapping (only checked when creating, via auth.create_user_group_mapping)
Source: 021_functions_auth_group.sql:337
Note
Matching considers user_group_id, provider_code, mapped_object_id, and mapped_role together. The mapped_object_name is not part of the uniqueness check.
auth.ensure_user_groups¶
Batch-ensures user groups from a JSONB array. Creates any groups that do not already exist for the given tenant. Optionally operates in final state mode to remove groups that are no longer in the input.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the action |
_user_id |
bigint | -- | ID of the user performing the action |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_user_groups |
jsonb | -- | JSONB array of group objects (see format below) |
_tenant_id |
integer | 1 |
Tenant to create groups in |
_source |
text | null |
Source tag for tracking which application created the groups |
_is_final_state |
boolean | false |
If true, deletes groups with the same _source and _tenant_id that are not in the input |
Returns: setof auth.user_group -- all processed groups (both existing and newly created).
Permission required: groups.create_group (always checked); groups.delete_group (also checked when _is_final_state is true)
Source: 021_functions_auth_group.sql:1017
JSONB input format:
Each element in the _user_groups array is an object with these fields:
| Field | Type | Default | Description |
|---|---|---|---|
title |
text | required | Group title (code is auto-generated via helpers.get_code) |
is_assignable |
boolean | true |
Whether the group can be assigned to users |
is_active |
boolean | true |
Whether the group is active |
is_external |
boolean | false |
Whether the group uses external membership |
is_default |
boolean | false |
Whether the group is a default group for new users |
select * from auth.ensure_user_groups(
'app_bootstrap', 1, 'setup',
'[
{"title": "Administrators", "is_external": false},
{"title": "Editors", "is_external": false, "is_default": true},
{"title": "Viewers", "is_external": true}
]'::jsonb,
_tenant_id := 1,
_source := 'my_app'
);
Final state behavior: When _is_final_state = true, groups matching the same _source and _tenant_id that are not in the input array (and are not system groups) are deleted. Cascading cleanup removes their group mappings, permission assignments, and group members. Each deletion is journaled with event code 13003 and reason final_state_sync.
auth.ensure_user_group_mappings¶
Batch-ensures group mappings from a JSONB array. Creates any mappings that do not already exist, resolving group references by either ID or title. Optionally operates in final state mode.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the action |
_user_id |
bigint | -- | ID of the user performing the action |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_mappings |
jsonb | -- | JSONB array of mapping objects (see format below) |
_tenant_id |
integer | 1 |
Tenant for permission checks and group resolution |
_is_final_state |
boolean | false |
If true, deletes mappings for each (group, provider) pair that are not in the input |
Returns: setof auth.user_group_mapping -- all processed mappings.
Permission required: groups.create_mapping (always checked); groups.delete_mapping (also checked when _is_final_state is true)
Source: 021_functions_auth_group.sql:1130
JSONB input format:
Each element in the _mappings array is an object with these fields:
| Field | Type | Description |
|---|---|---|
user_group_id |
integer | Group ID to map to (optional if user_group_title is provided) |
user_group_title |
text | Group title to resolve to an ID (alternative to user_group_id) |
provider_code |
text | Provider code for this mapping |
mapped_object_id |
text | External group/object ID |
mapped_object_name |
text | Human-readable name for the mapped object |
mapped_role |
text | External role to map |
Tip
You can reference groups by title instead of ID using user_group_title. The function resolves the title to an ID via helpers.get_code. This is useful at bootstrap time when you may not know the group IDs yet.
select * from auth.ensure_user_group_mappings(
'app_bootstrap', 1, 'setup',
'[
{
"user_group_title": "Administrators",
"provider_code": "azuread",
"mapped_object_id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
"mapped_object_name": "Azure AD Admins"
},
{
"user_group_title": "Editors",
"provider_code": "azuread",
"mapped_role": "editor"
}
]'::jsonb,
_tenant_id := 1,
_is_final_state := true
);
Final state behavior: When _is_final_state = true, for each unique (user_group_id, provider_code) pair found in the input, any existing mappings for that pair that are not in the input are deleted. Permission caches are invalidated for affected users. Each deletion is journaled with event code 13021 and reason final_state_sync.
Note
Final state scope is per (group, provider) pair -- only mappings for group/provider combinations that appear in the input are candidates for deletion. Mappings for other group/provider pairs are untouched.
Permissions¶
auth.ensure_groups_and_permissions¶
Recalculates a user's groups and permissions after updating their provider-supplied groups and roles. This is typically called during a login flow, after auth.ensure_user_from_provider, to synchronize the user's external group memberships and rebuild their permission cache.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the action |
_user_id |
bigint | -- | ID of the user performing the action |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_target_user_id |
bigint | -- | ID of the user whose groups and permissions are being recalculated |
_provider_code |
text | -- | Provider code for the identity being updated |
_provider_groups |
text[] | null |
Array of external group identifiers from the provider |
_provider_roles |
text[] | null |
Array of external role identifiers from the provider |
Returns: table(__tenant_id integer, __tenant_uuid uuid, __groups text[], __permissions text[], __short_code_permissions text[]) -- the user's recalculated groups and permissions per tenant.
Permission required: authentication.ensure_permissions
Source: 022_functions_auth_permission.sql:644
Behavior:
- Updates
provider_groupsandprovider_roleson the user's identity record for the given provider. - Calls
unsecure.recalculate_user_groupsto rebuild group memberships (including external/hybrid group resolution via group mappings). - Calls
unsecure.recalculate_user_permissionsto rebuild and cache the user's permission set across all tenants.
auth.ensure_permissions¶
Batch-ensures permissions from a JSONB array. Creates any permissions that do not already exist (matched by full_code). Optionally operates in final state mode.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the action |
_user_id |
bigint | -- | ID of the user performing the action |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_permissions |
jsonb | -- | JSONB array of permission objects (see format below) |
_source |
text | null |
Source tag for tracking which application defined the permissions |
_is_final_state |
boolean | false |
If true, deletes permissions with the same _source that are not in the input |
Returns: setof auth.permission -- all processed permissions (both existing and newly created).
Permission required: permissions.add_permission (always checked); permissions.delete_permission (also checked when _is_final_state is true)
Source: 022_functions_auth_permission.sql:874
JSONB input format:
Each element in the _permissions array is an object with these fields:
| Field | Type | Default | Description |
|---|---|---|---|
title |
text | required | Permission title, stored as a translation (code is auto-generated via helpers.get_code) |
parent_code |
text | null |
Parent permission's full_code for hierarchy placement |
is_assignable |
boolean | true |
Whether the permission can be directly assigned |
short_code |
text | null |
Optional short alias code for the permission |
source |
text | inherits _source |
Per-item source override |
Items are automatically sorted by hierarchy depth (parents before children), so callers do not need to worry about ordering.
select * from auth.ensure_permissions(
'app_bootstrap', 1, 'setup',
'[
{"title": "Projects"},
{"title": "View Projects", "parent_code": "projects", "short_code": "proj.view"},
{"title": "Edit Projects", "parent_code": "projects", "short_code": "proj.edit"},
{"title": "Delete Projects", "parent_code": "projects", "short_code": "proj.del"}
]'::jsonb,
_source := 'projects_app'
);
Final state behavior: When _is_final_state = true, permissions matching the same _source that are not in the input array are deleted (deepest children first to respect hierarchy). Cascading cleanup removes their perm_set_perm entries and permission_assignment entries. The has_children flag is updated for any parents that lost children. Each deletion is journaled with event code 12003 and reason final_state_sync.
auth.ensure_perm_sets¶
Batch-ensures permission sets from a JSONB array. Creates any permission sets that do not already exist for the given tenant, and adds any missing permissions to existing sets. Optionally operates in final state mode.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the action |
_user_id |
bigint | -- | ID of the user performing the action |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_perm_sets |
jsonb | -- | JSONB array of permission set objects (see format below) |
_source |
text | null |
Source tag for tracking which application defined the permission sets |
_tenant_id |
integer | 1 |
Tenant to create permission sets in |
_is_final_state |
boolean | false |
If true, deletes permission sets with the same _source and _tenant_id that are not in the input, and also removes extra permissions from existing sets |
Returns: setof auth.perm_set -- all processed permission sets (both existing and newly created).
Permission required: permissions.create_permission_set (always checked); permissions.delete_permission_set (also checked when _is_final_state is true)
Source: 022_functions_auth_permission.sql:1003
JSONB input format:
Each element in the _perm_sets array is an object with these fields:
| Field | Type | Default | Description |
|---|---|---|---|
title |
text | required | Permission set title, stored as a translation (code is auto-generated via helpers.get_code) |
is_system |
boolean | false |
Whether this is a system permission set |
is_assignable |
boolean | true |
Whether the permission set can be assigned to users/groups |
permissions |
text[] | null |
Array of permission full_code values to include in the set |
source |
text | inherits _source |
Per-item source override |
select * from auth.ensure_perm_sets(
'app_bootstrap', 1, 'setup',
'[
{
"title": "Project Admin",
"permissions": ["projects", "projects.view_projects", "projects.edit_projects", "projects.delete_projects"]
},
{
"title": "Project Viewer",
"permissions": ["projects.view_projects"]
}
]'::jsonb,
_source := 'projects_app',
_tenant_id := 1,
_is_final_state := true
);
Behavior for existing permission sets: When a permission set already exists (matched by code and tenant), the function adds any missing permissions from the permissions array to the existing set. It does not modify the set's is_system or is_assignable flags, nor its translation-based title.
Final state behavior: When _is_final_state = true:
- Extra permissions in existing sets (not in the input's
permissionsarray) are removed. - Extra permission sets matching the same
_sourceand_tenant_id(not in the input) are deleted entirely, with cascading cleanup ofpermission_assignmententries and cache invalidation for affected users. Each deletion is journaled with event code12022and reasonfinal_state_sync.
Resource Types¶
auth.ensure_resource_types¶
Batch-ensures resource types from a JSONB array. Creates any resource types that do not already exist (matched by code). Automatically creates storage partitions for root-level resource types.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text | -- | Identifier of the user or system performing the action |
_user_id |
bigint | -- | ID of the user performing the action |
_correlation_id |
text | -- | Correlation ID for tracing and audit logging |
_resource_types |
jsonb | -- | JSONB array of resource type objects (see format below) |
_source |
text | null |
Source tag for tracking which application defined the resource types |
_tenant_id |
integer | 1 |
Tenant for permission checks |
Returns: setof const.resource_type -- all processed resource types (both existing and newly created).
Permission required: resources.create_resource_type
Source: 035_functions_resource_access.sql:1193
JSONB input format:
Each element in the _resource_types array is an object with these fields:
| Field | Type | Default | Description |
|---|---|---|---|
code |
text | required | Resource type code; dots encode hierarchy (e.g. project.documents) |
title |
text | required | Display title (stored as a translation, not as a table column) |
parent_code |
text | null |
Parent resource type code |
description |
text | null |
Optional description (stored as a translation, not as a table column) |
source |
text | inherits _source |
Per-item source override |
Items are automatically sorted by hierarchy depth (parents before children), so callers do not need to worry about ordering.
select * from auth.ensure_resource_types(
'app_bootstrap', 1, 'setup',
'[
{"code": "project", "title": "Project"},
{"code": "project.documents", "title": "Project Documents", "parent_code": "project"},
{"code": "project.invoices", "title": "Project Invoices", "parent_code": "project"}
]'::jsonb,
_source := 'projects_app'
);
Behavior:
- For each item, validates that the parent exists (if
parent_codeis provided). - Inserts the resource type (with
on conflict do nothingfor safety). - Automatically creates a storage partition via
unsecure.ensure_resource_access_partitionfor new resource types. - Journals each creation with event code
18001.
Note
Unlike ensure_permissions and ensure_perm_sets, this function does not support _is_final_state mode.
Typical App Bootstrap Sequence¶
A typical application startup script calls ensure functions in dependency order: providers first, then users and groups, then permissions and permission sets, then group mappings, and finally resource types. Here is a complete example:
-- ============================================================
-- 1. Ensure the identity provider exists
-- ============================================================
select * from auth.ensure_provider(
'app_bootstrap', 1, 'bootstrap',
'azuread', 'Azure Active Directory',
_is_active := true,
_allows_group_mapping := true,
_allows_group_sync := true
);
-- ============================================================
-- 2. Ensure groups exist
-- ============================================================
select * from auth.ensure_user_groups(
'app_bootstrap', 1, 'bootstrap',
'[
{"title": "Administrators"},
{"title": "Project Managers"},
{"title": "Editors", "is_default": true},
{"title": "Viewers", "is_external": true}
]'::jsonb,
_tenant_id := 1,
_source := 'my_app',
_is_final_state := true
);
-- ============================================================
-- 3. Ensure permissions exist
-- ============================================================
select * from auth.ensure_permissions(
'app_bootstrap', 1, 'bootstrap',
'[
{"title": "Projects"},
{"title": "View Projects", "parent_code": "projects"},
{"title": "Edit Projects", "parent_code": "projects"},
{"title": "Delete Projects", "parent_code": "projects"},
{"title": "Documents"},
{"title": "View Documents", "parent_code": "documents"},
{"title": "Upload Documents","parent_code": "documents"}
]'::jsonb,
_source := 'my_app',
_is_final_state := true
);
-- ============================================================
-- 4. Ensure permission sets exist (reference the permissions above)
-- ============================================================
select * from auth.ensure_perm_sets(
'app_bootstrap', 1, 'bootstrap',
'[
{
"title": "Full Access",
"permissions": [
"projects", "projects.view_projects", "projects.edit_projects",
"projects.delete_projects", "documents", "documents.view_documents",
"documents.upload_documents"
]
},
{
"title": "Read Only",
"permissions": ["projects.view_projects", "documents.view_documents"]
}
]'::jsonb,
_source := 'my_app',
_tenant_id := 1,
_is_final_state := true
);
-- ============================================================
-- 5. Ensure group mappings (link Azure AD groups to internal groups)
-- ============================================================
select * from auth.ensure_user_group_mappings(
'app_bootstrap', 1, 'bootstrap',
'[
{
"user_group_title": "Administrators",
"provider_code": "azuread",
"mapped_object_id": "aad-group-id-for-admins",
"mapped_object_name": "AAD Global Admins"
},
{
"user_group_title": "Viewers",
"provider_code": "azuread",
"mapped_role": "viewer"
}
]'::jsonb,
_tenant_id := 1,
_is_final_state := true
);
-- ============================================================
-- 6. Ensure resource types exist (if using resource-level access)
-- ============================================================
select * from auth.ensure_resource_types(
'app_bootstrap', 1, 'bootstrap',
'[
{"code": "project", "title": "Project"},
{"code": "project.documents", "title": "Project Documents", "parent_code": "project"},
{"code": "project.invoices", "title": "Project Invoices", "parent_code": "project"}
]'::jsonb,
_source := 'my_app'
);
With _is_final_state := true, this script is fully idempotent and convergent -- running it repeatedly produces the same result, and removing an item from any of the arrays will cause it to be cleaned up on the next run.
Function Summary¶
| Function | Batch | Final State | Permission(s) |
|---|---|---|---|
auth.ensure_provider |
No | No | providers.create_provider (on create only) |
auth.ensure_user_info |
No | No | None |
auth.ensure_user_from_provider |
No | No | None |
auth.ensure_user_group_mapping |
No | No | groups.create_mapping (on create only) |
auth.ensure_user_groups |
Yes | Yes | groups.create_group; groups.delete_group (final state) |
auth.ensure_user_group_mappings |
Yes | Yes | groups.create_mapping; groups.delete_mapping (final state) |
auth.ensure_groups_and_permissions |
No | No | authentication.ensure_permissions |
auth.ensure_permissions |
Yes | Yes | permissions.add_permission; permissions.delete_permission (final state) |
auth.ensure_perm_sets |
Yes | Yes | permissions.create_permission_set; permissions.delete_permission_set (final state) |
auth.ensure_resource_types |
Yes | No | resources.create_resource_type |