Schema Organization

The PostgreSQL Permissions Model uses multiple PostgreSQL schemas to enforce distinct security boundaries. Each schema has a well-defined role: some always validate permissions, some operate in trusted contexts only, and others provide utility services.

All schemas are created in 001_create_basic_structure.sql:

create schema if not exists error;
create schema if not exists const;
create schema if not exists internal;
create schema if not exists unsecure;   -- functions without any permission validation
create schema if not exists helpers;
create schema if not exists ext;
create schema if not exists auth;
create schema if not exists stage;
create schema if not exists triggers;

The public schema is provided by PostgreSQL itself and does not need to be created.

---

Schema Call Flow

The following diagram shows how schemas interact at runtime. Permission-checked schemas validate the caller's authorization and then delegate to trusted schemas for the actual work. Utility schemas are available to all layers.

graph TD
    APP["Application / Client"]

    subgraph checked ["Permission-Checked Schemas"]
        direction LR
        PUB["<b>public</b><br/>Business functions<br/>Journal, versioning, tokens"]
        AUTH["<b>auth</b><br/>Security functions<br/>Users, groups, permissions"]
    end

    subgraph trusted ["Trusted-Context Schemas (no permission checks)"]
        direction LR
        INT["<b>internal</b><br/>Business logic<br/><i>already authorized</i>"]
        UNS["<b>unsecure</b><br/>Security internals<br/><i>cache, notifications</i>"]
    end

    subgraph utility ["Utility & Configuration Schemas"]
        direction LR
        HLP["<b>helpers</b><br/>Utility functions"]
        ERR["<b>error</b><br/>Error handling"]
        CST["<b>const</b><br/>Constants & config"]
        EXT["<b>ext</b><br/>PostgreSQL extensions"]
        STG["<b>stage</b><br/>Staging tables"]
        TRG["<b>triggers</b><br/>Trigger functions"]
    end

    APP -->|"calls"| PUB
    APP -->|"calls"| AUTH
    AUTH -->|"validates, then<br/>delegates to"| INT
    AUTH -->|"uses for<br/>security internals"| UNS
    PUB -->|"validates, then<br/>delegates to"| INT
    PUB & AUTH & INT --> HLP
    PUB & AUTH & INT --> ERR
    PUB & AUTH & INT & UNS --> CST

---

Permission-Checked Schemas

These schemas form the public API of the permissions model. Functions in these schemas always validate the caller's authorization before executing (with rare exceptions like version management utilities).

public

Application-level business functions that check permissions. This schema is intentionally kept clean so that your own application functions can coexist alongside the framework's utilities.

What lives here:

• Version management -- public.start_version_update(), public.stop_version_update(), public.check_version()
• Journal and audit logging -- public.create_journal_message(), public.search_journal(), public.get_journal_entry()
• Token validation -- public.validate_token()
• Event code management -- public.create_event_category(), public.create_event_code(), public.create_event_message()
• Language and translation -- public.create_language(), public.get_languages()
• The public.journal table (range-partitioned by month)
• The public.__version table for migration tracking
• Permission map -- public.get_permissions_map()

When to use: Place your own application functions in public when they need permission checks. Start each function with a perform auth.has_permission(...) guard.

See Helpers & Internal for journal-related functions.

auth

Security and authorization functions that always validate permissions. This is where the core permission model API lives.

What lives here:

• Permission checking -- auth.has_permission(), auth.has_permissions()
• User management -- auth.register_user(), auth.enable_user(), auth.disable_user(), auth.lock_user()
• Group management -- auth.create_user_group(), auth.create_user_group_member()
• Permission CRUD -- auth.create_permission(), auth.assign_permission(), auth.unassign_permission()
• Permission sets -- auth.create_perm_set(), auth.create_perm_set_permissions()
• Tenant management -- auth.create_tenant(), auth.get_tenant_users()
• Provider management -- auth.create_provider(), auth.ensure_user_from_provider()
• API key management -- auth.create_api_key(), auth.update_api_key()
• System parameters -- auth.get_sys_param(), auth.update_sys_param()
• All core auth.* tables (users, groups, permissions, tenants, providers, tokens, API keys, etc.)

When to use: Call auth.* functions from your application layer. These are the primary entry points for all security operations.

See User Management, Permissions & Authorization, Group Management, and Tenant Management for the complete function reference.

---

Trusted-Context Schemas

Functions in these schemas do not check permissions. They exist to be called from contexts where authorization has already been validated -- either by a wrapping public/auth function, or by a secure application layer.

internal

Business logic functions that skip permission checks because their callers have already verified authorization. These functions are wrapped by public or auth functions that perform the actual permission validation.

What lives here:

• internal.throw_no_permission() -- raises a structured permission-denied exception (multiple overloads for different parameter combinations)
• Identifier resolvers -- internal.resolve_user(), internal.resolve_tenant(), internal.resolve_group() -- resolve a flexible text identifier (numeric ID, UUID, or code) to the corresponding primary key

When to use: Only call internal.* functions from code that has already performed a permission check, such as from within a public or auth function, or from an application layer where authorization is handled externally.

-- Example: auth function checks permission, then delegates to internal
create or replace function auth.some_operation(_user_id bigint, _tenant_id integer)
returns void as $$
begin
    -- permission check happens here
    perform auth.has_permission(_user_id, _correlation_id, 'some.permission', _tenant_id);

    -- now safe to call internal logic (no second permission check)
    perform internal.do_the_actual_work(_user_id, _tenant_id);
end;
$$ language plpgsql;

unsecure

Security system internals that bypass permission checks. This schema contains the low-level implementation of permission management, cache operations, notification dispatching, and system bootstrapping functions.

Not for business logic

Despite its name, unsecure is not a schema for unprotected business functions. It is exclusively for security-system internals. Never place application business logic here.

What lives here:

• Permission cache management -- unsecure.clear_permission_cache(), unsecure.invalidate_group_members_permission_cache(), unsecure.invalidate_perm_set_users_permission_cache(), unsecure.invalidate_users_permission_cache()
• LISTEN/NOTIFY dispatching -- unsecure.notify_permission_change(), unsecure.notify_journal_event(), unsecure.notify_user_event()
• Permission CRUD internals -- unsecure.create_permission(), unsecure.assign_permission(), unsecure.unassign_permission(), unsecure.create_perm_set()
• User management internals -- unsecure.create_user_info(), unsecure.create_service_user_info(), unsecure.delete_user_by_id(), unsecure.update_user_password()
• Group management internals -- unsecure.create_user_group(), unsecure.create_user_group_member()
• System bootstrapping -- unsecure.create_primary_tenant(), unsecure.create_user_system()
• Owner verification -- unsecure.verify_owner_or_permission()
• Short code computation -- unsecure.compute_short_code(), unsecure.update_permission_short_code()

Direct calls bypass all authorization

Calling unsecure.* functions directly from application code skips all permission checks. Only use these functions from within the permission model's own auth functions, from seed/migration scripts, or from trusted system processes.

When to use: Almost never from application code. These are called internally by auth.* functions. The main exception is unsecure.clear_permission_cache(), which you may need from maintenance scripts.

See Permissions & Authorization for the safe auth.* wrappers around these functions.

---

Utility Schemas

These schemas provide supporting services used by all other schemas. They contain no business logic and no permission checks.

helpers

Pure utility functions with no side effects. Used across the entire permission model for string manipulation, code generation, and data operations.

What lives here:

• String utilities -- helpers.is_empty_string(), helpers.is_not_empty_string(), helpers.random_string()
• Code generation -- helpers.get_code(), helpers.get_slug()
• Text normalization -- helpers.normalize_text() (accent-insensitive, optionally lowercased)
• ltree operations -- helpers.ltree_parent()
• JSONB utilities -- helpers.compute_jsonb_hash(), helpers.normalize_jsonb_values(), helpers.delete_jsonb_fields(), helpers.compare_jsonb_objects()
• Trigger helpers -- helpers.trg_generate_code_from_title()
• Journal helpers -- helpers.should_log_journal(), helpers.is_event_read_only(), helpers.should_store_locally(), helpers.should_notify_storage()

See Helpers & Internal for the complete function reference.

error

Structured error handling functions. Each function raises a PostgreSQL exception with a standardized error code and message. Error codes follow a numeric scheme (30xxx--37xxx).

What lives here:

• error.raise_30001() through error.raise_35001() -- each function corresponds to a specific error condition
• Error categories include API key errors (30xxx), event/system errors (31xxx), permission errors (32xxx), user/group errors (33xxx), tenant errors (34xxx), and resource access errors (35xxx)

Example:

-- Raises: "User not found" with error code 33001
perform error.raise_33001(_user_id);

-- Raises: "Permission denied" with error code 32001
perform error.raise_32001(_user_id, _perm_codes, _tenant_id);

See Error & Event Codes for the complete code reference.

const

Constants and configuration tables that define the system's reference data. These tables are typically populated during migration and rarely change at runtime.

Tables in this schema:

Table	Purpose
const.sys_param	System parameters (key-value configuration)
const.tenant_access_type	Tenant access type codes
const.token_type	Token type definitions
const.token_channel	Token delivery channels
const.token_state	Token state codes
const.user_type	User type codes (regular, service, system)
const.event_category	Event category groupings
const.event_code	Event code definitions
const.event_message	Message templates for event codes
const.user_group_member_type	Group member type codes
const.language	Language definitions for translations
const.resource_type	Resource type definitions for resource-level ACL
const.resource_access_flag	Access flag definitions (read, write, etc.)
const.resource_type_flag	Per-type access flag mappings
const.resource_role	Named flag bundles per resource type
const.resource_role_flag	Flags belonging to a resource role

See System Parameters for the sys_param API.

ext

PostgreSQL extensions, isolated in their own schema to keep other schemas clean. All extensions are created with schema ext to avoid polluting the public schema.

Extensions installed:

Extension	Purpose
uuid-ossp	UUID generation (ext.uuid_generate_v4())
ltree	Hierarchical tree data type for permission paths
unaccent	Accent-insensitive text search
pg_trgm	Trigram-based fuzzy text matching

These extensions enable the hierarchical permission model (ltree), unique identifiers (uuid-ossp), and the accent-insensitive search capabilities (unaccent, pg_trgm).

stage

Staging tables for data imports, particularly for external group member synchronization.

Tables in this schema:

Table	Purpose
stage.external_group_member	Holds imported members from external identity providers before they are processed into auth.user_group_member records

When an external system (e.g., AzureAD group sync) pushes a member list, the data lands in stage.external_group_member first. The system then reconciles it with the actual membership records.

triggers

Trigger functions that maintain computed/denormalized data automatically. When rows are inserted or updated in core tables, these triggers recalculate normalized search values.

What lives here:

Function	Fires on
triggers.calculate_user_data()	auth.user_data insert/update
triggers.calculate_user_info()	auth.user_info insert/update
triggers.calculate_tenant()	auth.tenant insert/update
triggers.calculate_user_group()	auth.user_group insert/update
triggers.calculate_permission()	auth.permission insert/update
triggers.calculate_perm_set()	auth.perm_set insert/update
triggers.calculate_api_key()	auth.api_key insert/update

Each trigger function has a companion _search_values helper (e.g., triggers.calculate_user_data_search_values()) that computes the normalized text used for search indexing.

---

Security Model

The schema organization enforces a layered security contract:

graph TB
    subgraph layer1 ["Layer 1: Permission-Checked (public, auth)"]
        direction LR
        L1["Every function validates caller authorization<br/>before executing any logic"]
    end

    subgraph layer2 ["Layer 2: Trusted Context (internal, unsecure)"]
        direction LR
        L2["No permission checks -- caller is trusted<br/>Only reached via Layer 1 or system processes"]
    end

    subgraph layer3 ["Layer 3: Utilities (helpers, error, const, ext, stage, triggers)"]
        direction LR
        L3["No business logic, no security decisions<br/>Pure utilities and reference data"]
    end

    layer1 -->|"delegates after<br/>authorization"| layer2
    layer1 --> layer3
    layer2 --> layer3

Key Principles

1. public and auth always check permissions. With rare exceptions like public.get_app_version(), every function in these schemas validates the caller's authorization before proceeding.

2. internal is for already-authorized logic. Functions here are called by public or auth functions that have already verified permissions. They can also be called from application code where authorization is handled externally (e.g., by middleware).

3. unsecure is only for security-system internals. This schema handles permission cache recalculation, notification dispatching, and low-level security operations. It is never a place for general business logic.

4. Utility schemas are neutral. helpers, error, const, ext, stage, and triggers provide supporting services without making any security decisions.

Adding your own functions

When adding application-specific functions to this database, follow the same pattern:

• Place permission-checked functions in public (or auth if security-focused)
• Place already-authorized logic in internal
• Always use fully qualified schema names (e.g., auth.has_permission, internal.do_work) to avoid search_path issues

-- Your application function in public schema
create or replace function public.cancel_order(
    _user_id bigint,
    _correlation_id text,
    _order_id bigint,
    _tenant_id integer default 1
) returns void as $$
begin
    -- always check permission first
    perform auth.has_permission(_user_id, _correlation_id, 'orders.cancel_order', _tenant_id);

    -- then do the work
    update orders set status = 'cancelled' where order_id = _order_id;
end;
$$ language plpgsql;

---

Next Steps

• Architecture -- ER diagram and entity overview
• Permission Model -- hierarchical permissions, sets, and assignments
• Identity Providers -- external authentication integration
• Helpers & Internal Reference -- complete utility function documentation
• Error & Event Codes -- full error code reference