db

Database Schema Design Principles and Conventions

Logical Database Separation

ThunderID uses four logically separated databases. Each database owns a specific category of data.

Database (config key)	Responsibility
config	Identity configuration data Ex: applications, authentication flows, roles, identity providers
runtime_transient	Short-lived runtime state: authorization codes, authorization/PAR requests, JTI records, WebAuthn/VCI state, flow contexts
entitydb	Identity data: users, groups, indexed user attributes
runtime_persistent	Long-lived operational state that must survive restarts: revoked tokens, SSO sessions, consent records

Although the databases are logically separated, they share consistent schema design principles documented here.

Primary Key Strategy

UUID v7 Identifiers

Tables use UUID v7 as primary key values. UUID v7 provides:

• Global uniqueness across deployments and systems.
• Time-ordering characteristics that improve index performance for insert-heavy workloads.

Primary Key Column Naming

Primary key columns are named ID.

Do not use composite names such as USER_ID or APPLICATION_ID for a primary key column. Use ID consistently.

-- Correct
CREATE TABLE "APPLICATION" (
    ID VARCHAR(36) PRIMARY KEY,
    ...
);

-- Incorrect
CREATE TABLE APPLICATION (
    APPLICATION_ID VARCHAR(36) PRIMARY KEY,
    ...
);

Association Tables

Association (join) tables that model many-to-many relationships use composite primary keys formed from the relevant foreign key columns. These tables do not include a separate surrogate ID column.

-- Example: role assignments use a composite primary key
CREATE TABLE "ROLE_ASSIGNMENT" (
    DEPLOYMENT_ID   VARCHAR(255) NOT NULL,
    ROLE_ID         VARCHAR(36) NOT NULL,
    ASSIGNEE_TYPE   VARCHAR(5)  NOT NULL CHECK (ASSIGNEE_TYPE IN ('user', 'group')),
    ASSIGNEE_ID     VARCHAR(36) NOT NULL,
    PRIMARY KEY (ROLE_ID, DEPLOYMENT_ID, ASSIGNEE_TYPE, ASSIGNEE_ID),
    FOREIGN KEY (ROLE_ID) REFERENCES "ROLE" (ID) ON DELETE CASCADE
);