Skip to content

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:

  1. Check if the resource already exists (by code, username, or provider identity).
  2. Create the resource if it does not exist, delegating to the underlying create_* or unsecure.create_* function.
  3. 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 _source tag 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:

  1. Checks the blacklist by provider identity.
  2. Creates the user via unsecure.create_user_info.
  3. Creates the user identity via unsecure.create_user_identity.
  4. Logs a user_registered event.

Behavior when user already exists:

  1. Updates provider_uid / provider_oid if changed.
  2. Updates basic user data (username, display name, email) if any changed.
  3. Checks can_login -- raises 52112 if login is disabled.
  4. Checks is_active on user -- raises 52105 if user is disabled.
  5. Checks is_active on identity -- raises 52110 if identity is disabled.
  6. Cleans up duplicate UIDs for the same provider.
  7. Sets this provider as the "last used" provider.
  8. Logs a user_logged_in event.

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:

  1. Updates provider_groups and provider_roles on the user's identity record for the given provider.
  2. Calls unsecure.recalculate_user_groups to rebuild group memberships (including external/hybrid group resolution via group mappings).
  3. Calls unsecure.recalculate_user_permissions to 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 permissions array) are removed.
  • Extra permission sets matching the same _source and _tenant_id (not in the input) are deleted entirely, with cascading cleanup of permission_assignment entries and cache invalidation for affected users. Each deletion is journaled with event code 12022 and reason final_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:

  1. For each item, validates that the parent exists (if parent_code is provided).
  2. Inserts the resource type (with on conflict do nothing for safety).
  3. Automatically creates a storage partition via unsecure.ensure_resource_access_partition for new resource types.
  4. 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