Architecture

This page provides an overview of the system's entity relationships, schema security model, and the role of each PostgreSQL schema.

---

Entity Relationships

The system has many interconnected tables. Instead of one unreadable diagram, the relationships are shown per domain below. Audit/metadata columns (created_at, created_by, etc.) are omitted for clarity.

Users & Identity

erDiagram
    user_info {
        bigint user_id PK
        text code UK
        uuid uuid UK
        text username UK
        text email
        text display_name
        text user_type_code
        bool is_active
        bool is_locked
        bool can_login
    }

    user_identity {
        bigint user_identity_id PK
        text provider_code FK
        bigint user_id FK
        text uid
        text provider_oid UK
        bool is_active
    }

    user_data {
        bigint user_data_id PK
        bigint user_id FK
        jsonb settings
        jsonb preferences
        jsonb custom_data
    }

    provider {
        int provider_id PK
        text code UK
        bool is_active
    }

    user_blacklist {
        bigint blacklist_id PK
        text username
        text provider_code FK
        text provider_uid
    }

    user_info ||--o{ user_identity : "has identities"
    user_info ||--o| user_data : "has profile"
    provider ||--o{ user_identity : "authenticates"
    provider ||--o{ user_blacklist : "blocks"

Tenants & Groups

erDiagram
    tenant {
        int tenant_id PK
        text code UK
        text title
        bool is_default
    }

    tenant_user {
        bigint tenant_user_id PK
        int tenant_id FK
        bigint user_id FK
    }

    user_group {
        int user_group_id PK
        int tenant_id FK
        text title
        text code
        bool is_external
        bool is_active
    }

    user_group_member {
        bigint member_id PK
        int user_group_id FK
        bigint user_id FK
        text member_type_code
    }

    user_group_mapping {
        int mapping_id PK
        int user_group_id FK
        text provider_code FK
        text mapped_object_id
        text mapped_role
    }

    owner {
        bigint owner_id PK
        int tenant_id FK
        bigint user_id FK
    }

    tenant ||--o{ tenant_user : "grants access"
    tenant ||--o{ user_group : "scopes"
    tenant ||--o{ owner : "has owners"
    user_group ||--o{ user_group_member : "has members"
    user_group ||--o{ user_group_mapping : "has mappings"
    user_group_mapping ||--o{ user_group_member : "creates synced members"

Permissions (RBAC)

erDiagram
    permission {
        int permission_id PK
        text code
        ltree full_code UK
        ltree node_path
        bool is_assignable
        text source
    }

    perm_set {
        int perm_set_id PK
        int tenant_id FK
        text code
        bool is_assignable
        text source
    }

    perm_set_perm {
        int psp_id PK
        int perm_set_id FK
        int permission_id FK
    }

    permission_assignment {
        bigint assignment_id PK
        int tenant_id FK
        bigint user_id FK
        int user_group_id FK
        int perm_set_id FK
        int permission_id FK
    }

    permission ||--o{ perm_set_perm : "included in"
    permission ||--o{ permission_assignment : "assigned via"
    perm_set ||--o{ perm_set_perm : "contains"
    perm_set ||--o{ permission_assignment : "assigned via"

Resource Access (ACL)

erDiagram
    resource_type {
        text code PK
        bool is_active
        text source
        text parent_code FK
        ltree path
        jsonb key_schema
    }

    resource_access_flag {
        text code PK
        text source
    }

    resource_type_flag {
        text resource_type_code FK
        text access_flag_code FK
    }

    resource_access {
        bigint resource_access_id PK
        int tenant_id FK
        text resource_type FK
        text root_type
        jsonb resource_id
        bigint user_id FK
        int user_group_id FK
        text access_flag FK
        bool is_deny
    }

    resource_role {
        text code PK
        text resource_type FK
        bool is_active
        text source
    }

    resource_role_flag {
        text resource_role_code FK
        text access_flag_code FK
    }

    resource_role_assignment {
        bigint assignment_id PK
        int tenant_id FK
        text resource_type FK
        text root_type
        jsonb resource_id
        bigint user_id FK
        int user_group_id FK
        text role_code FK
    }

    resource_type ||--o{ resource_type_flag : "valid flags"
    resource_type ||--o{ resource_type : "parent/child"
    resource_access_flag ||--o{ resource_type_flag : "mapped to type"
    resource_access_flag ||--o{ resource_access : "flag"
    resource_access_flag ||--o{ resource_role_flag : "in role"
    resource_type ||--o{ resource_access : "scopes"
    resource_type ||--o{ resource_role : "scopes"
    resource_role ||--o{ resource_role_flag : "has flags"
    resource_role ||--o{ resource_role_assignment : "assigned via"
    resource_type ||--o{ resource_role_assignment : "scopes"

Security & Tokens

erDiagram
    token {
        bigint token_id PK
        bigint user_id FK
        text token_type_code
        text token_state_code
        text token
        timestamptz expires_at
    }

    api_key {
        int api_key_id PK
        int tenant_id FK
        text api_key UK
        text key_type
        text service_code
    }

    user_mfa {
        bigint user_mfa_id PK
        bigint user_id FK
        text mfa_type_code FK
        bool is_enabled
        bool is_confirmed
    }

    mfa_policy {
        bigint mfa_policy_id PK
        int tenant_id FK
        int user_group_id FK
        bigint user_id FK
        bool mfa_required
    }

Translations & Audit

erDiagram
    translation {
        int translation_id PK
        text language_code FK
        int tenant_id FK
        text data_group
        text data_object_code
        text context
        text value
    }

    mv_translation {
        text language_code
        text data_group
        text data_object_code
        jsonb values
    }

    translation ||--|| mv_translation : "aggregated into"

Cross-domain relationships

The diagrams above show each domain in isolation. In practice, user_info connects to nearly everything — group membership, permission assignments, resource access, tokens, MFA, and ownership. tenant scopes groups, permission sets, assignments, API keys, and resource access. These cross-references are omitted from individual diagrams for readability.

---

Schema Security Model

The system enforces a strict security contract through PostgreSQL schemas. Functions in permission-checked schemas always validate the caller's authorization before executing. Functions in trusted schemas assume the caller has already been authorized.

graph LR
    subgraph "Permission-Checked (always validate)"
        A["<b>public</b><br/>Application business functions"]
        B["<b>auth</b><br/>Security functions"]
    end

    subgraph "No Permission Checks (trusted context)"
        C["<b>internal</b><br/>Business logic<br/><i>wrapped by public/auth</i>"]
        D["<b>unsecure</b><br/>Security internals only<br/><i>never for business logic</i>"]
    end

    subgraph "Utility & Configuration"
        E["<b>helpers</b><br/>Utility functions"]
        F["<b>error</b><br/>Error handling"]
        G["<b>const</b><br/>Constants & config tables"]
        H["<b>ext</b><br/>PostgreSQL extensions"]
        I["<b>stage</b><br/>Staging tables"]
        J["<b>triggers</b><br/>Trigger functions"]
    end

    A -->|"calls after<br/>permission check"| C
    B -->|"calls after<br/>permission check"| C
    B -->|"calls for<br/>security internals"| D
    A --> E
    B --> E
    C --> E
    A --> F
    B --> F
    C --> F

---

Schema Roles

Permission-Checked Schemas

Schema	Purpose
public	Application business functions that always validate permissions before executing. Kept clean for application-specific functions alongside the permissions framework utilities.
auth	Security and authorization functions that always validate permissions. This is where the core API lives: auth.has_permission, auth.register_user, auth.assign_permission, etc.

Trusted-Context Schemas

Schema	Purpose
internal	Business logic functions that do not check permissions themselves. They are called by public or auth functions that have already validated the caller. Also callable from secure application contexts where permission checking is handled externally.
unsecure	Security system internals only: permission cache recalculation, internal group resolution, etc. Never use this schema for business logic.

Utility Schemas

Schema	Purpose
helpers	Pure utility functions: random string generation, code generation, ltree operations. No business logic, no security checks.
error	Error handling and exception functions. Each error code (e.g. error.raise_32001) raises a structured exception with a standardized code and message.
const	Constants and configuration tables: user types, token states, token types, event codes, system parameters, etc.
ext	PostgreSQL extensions (ltree, uuid-ossp, unaccent, pg_trgm). Isolated in their own schema.
stage	Staging tables for data imports, such as external group member synchronization.
triggers	Trigger functions for automatic data maintenance (e.g. normalized search data updates).

Key security principle

public and auth schemas always check permissions (with rare exceptions like get_app_version). If you add application functions, place them in public with a perform auth.has_permission(...) guard at the top. Only use internal for logic that is guaranteed to be called from an already-authorized context.

---

Core Entity Overview

Multi-Tenancy

• auth.tenant -- Each tenant represents an isolated organizational context with its own permission sets, groups, and user access.
• auth.tenant_user -- Links users to the tenants they can access. A user can belong to multiple tenants.
• auth.user_tenant_preference -- Stores per-tenant user preferences as JSONB.

Users

• auth.user_info -- Core user record with username, email, display name, status flags (is_active, is_locked, can_login), and the last-used provider reference.
• auth.user_identity -- One or more identity provider links per user (e.g. AzureAD UID, Google ID). The last-used identity determines which provider_groups and provider_roles drive permission calculations.
• auth.user_data -- Extensible profile table (one-to-one with user_info). Add custom columns as needed.
• auth.user_blacklist -- Blocks re-creation of deleted or banned users by username, provider UID, or provider OID.

Groups

• auth.user_group -- Groups with three membership models: internal (database-stored), external (identity-provider-mapped), and hybrid (both).
• auth.user_group_member -- Direct membership records for internal and hybrid groups.
• auth.user_group_mapping -- Maps external provider groups or roles to internal groups.

See Group Types for a detailed explanation of internal, external, and hybrid groups.

Permissions

• auth.permission -- Global hierarchical permission tree using ltree. Permissions are shared across all tenants.
• auth.perm_set -- Tenant-specific collections of permissions, representing roles like "Admin", "Editor", or "Viewer".
• auth.perm_set_perm -- Join table linking permissions into permission sets.
• auth.permission_assignment -- Assigns a permission set or individual permission to a user or group within a tenant.

See Permission Model for the full hierarchical permission and assignment model.

Identity Providers

• auth.provider -- External authentication system configurations (AzureAD, Google, LDAP, email, etc.) with capability flags for group mapping and group sync.

See Identity Providers for integration patterns.

Resource-Level ACL

• auth.resource_access -- Per-resource grants and denies, list-partitioned by resource_type. Supports hierarchical resource paths.

See Resource-Level ACL for the access control model.

Multi-Factor Authentication

• auth.user_mfa -- MFA enrollment state per user per type. Stores app-encrypted TOTP secrets and SHA-256 hashed recovery codes.
• auth.mfa_policy -- Scope-based MFA enforcement rules. Resolution: user > group > tenant > global.

See Multi-Factor Authentication for enrollment, challenge/verify, recovery reset, and policy functions.

Authentication

• auth.token -- Short-lived tokens for password reset, email verification, and similar flows.
• auth.api_key -- Inbound (for validating incoming requests) and outbound (for calling external services) API keys. Each inbound key creates a "technical user" in user_info so permission checks work uniformly.

Ownership

• auth.owner -- Tracks tenant and group owners. Tenant owners bypass permission checks for their tenant.

Audit

• auth.user_event -- Security event audit trail with request context, range-partitioned by month.
• public.journal -- General-purpose audit log with multi-key support, range-partitioned by month.

See Audit & Notifications for journal usage, event categories, and LISTEN/NOTIFY integration.

---

Next Steps

• Schema Organization -- detailed schema documentation
• Permission Model -- hierarchical permissions and assignment
• Function Reference -- complete API documentation