db

name: db description: Database schema and query conventions for ThunderID. Use when changing schema scripts, defining SQL queries, updating store constants, or reviewing deployment-scoped persistence rules.

Database Schema Design Principles and Conventions

This document explains the database schema design principles and conventions used in ThunderID. AI agents and contributors should follow these conventions when generating or modifying database schemas and queries.

Logical Database Separation

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

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
);

This approach:

• Reduces index size compared to adding a separate surrogate key.
• Aligns with the natural query patterns for many-to-many relationships.
• Prevents duplicate association rows at the database level.

Foreign Key Strategy

Foreign keys reference UUID primary keys directly. Integer-based foreign keys are not used anywhere in the schema.

The system does not introduce:

• INT-based surrogate identifiers.
• UUID-to-integer resolution layers.

This avoids unnecessary lookup overhead and architectural complexity when joining across tables or deployments.

No Auto-Increment IDs

The schema does not use auto-increment integer identifiers. UUID v7 identifiers serve as the only primary key mechanism across all tables.

Multi-Deployment Isolation

Overview

ThunderID supports multi-deployment scenarios where a single database instance may serve data from multiple independent deployments. The DEPLOYMENT_ID column enforces isolation between these deployments.

Column Requirement

Every table includes a DEPLOYMENT_ID column defined as VARCHAR(255) NOT NULL.

CREATE TABLE "IDP" (
    DEPLOYMENT_ID VARCHAR(255) NOT NULL,
    ID VARCHAR(36) PRIMARY KEY,
    NAME VARCHAR(255) NOT NULL,
    ...
);

Although UUID v7 identifiers are globally unique, queries must still filter by DEPLOYMENT_ID to prevent data from leaking across deployments.

Query Patterns

DEPLOYMENT_ID is the last parameter in all parameterized queries. Follow these patterns consistently.

Identifier Casing and Quoting

Use uppercase table names wrapped in double quotes in schema scripts and embedded SQL.

• Write table names as "TABLE_NAME", not bare identifiers.
• Apply this consistently in CREATE TABLE, CREATE INDEX ... ON, FOREIGN KEY ... REFERENCES, and all SELECT / INSERT / UPDATE / DELETE statements.
• Keep Go query strings aligned with the schema scripts; do not mix quoted uppercase names with unquoted identifiers for the same table.
• This avoids PostgreSQL case-folding surprises and keeps reserved-word tables such as "ROLE" and "GROUP" consistent with the rest of the schema.

INSERT

Add DEPLOYMENT_ID as the last column in the column list and the last parameter in VALUES.

INSERT INTO "IDP" (ID, NAME, DESCRIPTION, TYPE, PROPERTIES, DEPLOYMENT_ID)
VALUES ($1, $2, $3, $4, $5, $6)

SELECT

Add AND DEPLOYMENT_ID = $N as the final condition in the WHERE clause.

SELECT ID, NAME, DESCRIPTION, TYPE, PROPERTIES
FROM "IDP"
WHERE ID = $1 AND DEPLOYMENT_ID = $2

UPDATE

Add AND DEPLOYMENT_ID = $N as the last condition in the WHERE clause.

UPDATE "IDP"
SET NAME = $2, DESCRIPTION = $3, TYPE = $4, PROPERTIES = $5
WHERE ID = $1 AND DEPLOYMENT_ID = $6

DELETE

Add AND DEPLOYMENT_ID = $N as the last condition in the WHERE clause.

DELETE FROM "IDP"
WHERE ID = $1 AND DEPLOYMENT_ID = $2

JOIN Queries

Include DEPLOYMENT_ID in JOIN conditions and WHERE clauses.

SELECT f.ID, f.HANDLE, f.NAME, fv.NODES
FROM "FLOW" f
INNER JOIN "FLOW_VERSION" fv
    ON f.ID = fv.FLOW_ID
    AND f.DEPLOYMENT_ID = fv.DEPLOYMENT_ID
    AND f.ACTIVE_VERSION = fv.VERSION
WHERE f.ID = $1 AND f.DEPLOYMENT_ID = $2

Indexing Philosophy

Indexes in ThunderID are designed to match real query patterns. The process for defining or revising indexes is:

1. Review the queries associated with each table.
2. Identify missing or inefficient indexes.
3. Optimize existing indexes, including composite primary keys.
4. Introduce composite indexes where they improve common query patterns.
5. Update both the PostgreSQL and SQLite schema scripts accordingly.

Indexing Goals

• Improve lookup speed for primary access patterns.
• Optimize join performance between related tables.
• Improve filtering performance for deployment-scoped queries.
• Reduce full table scans.

Composite Indexes

Composite indexes should place DEPLOYMENT_ID first when the query always filters by deployment. This allows the index to be used for deployment-scoped queries even when additional columns are not included.

-- Composite index for deployment + OU-based lookups
CREATE INDEX idx_user_ou_deployment ON "USER" (DEPLOYMENT_ID, OU_ID);

Expiry Indexes

Tables in runtimedb that include an EXPIRY_TIME column should have a dedicated index on that column to support efficient cleanup queries.

CREATE INDEX idx_authz_code_expiry_time ON "AUTHORIZATION_CODE" (EXPIRY_TIME);

Runtime Database Expiry Handling

Use these rules for all temporary runtime tables in runtimedb.

Agent Rules

1. Treat runtime records as temporary; they must expire and be removable.
2. Every runtime table must include an EXPIRY_TIME column.
3. Read queries must return only non-expired rows.
4. Cleanup jobs must delete expired rows regularly.
5. For association tables, if the foreign key to the owning runtime record uses ON DELETE CASCADE, deleting an expired owner row also removes related association rows automatically.
6. An association table does not require its own EXPIRY_TIME column unless the association has an independent expiry lifecycle.
7. When runtime tables are added, removed, or renamed, update both cleanup artifacts: backend/dbscripts/runtimedb/postgres-cleanup.sql and backend/scripts/cleanup_runtime_db.sh.

Expiry Column

Required column in each runtime table:

EXPIRY_TIME TIMESTAMP NOT NULL

Read Query Pattern

When selecting runtime data, compare EXPIRY_TIME with current time and keep DEPLOYMENT_ID as the last parameter:

SELECT AUTH_ID, REQUEST_DATA, EXPIRY_TIME
FROM "AUTHORIZATION_REQUEST"
WHERE AUTH_ID = $1 AND EXPIRY_TIME > $2 AND DEPLOYMENT_ID = $3

Cleanup Mechanism

Use the existing cleanup artifacts in this repository:

• backend/dbscripts/runtimedb/postgres-cleanup.sql: defines the PostgreSQL stored procedure cleanup_expired_runtimedb_data (UTC-based cleanup).
• backend/scripts/cleanup_runtime_db.sh: provides scheduled/manual cleanup support for PostgreSQL and SQLite.

Keep these two files in sync with the current set of runtime tables.

CREATE OR REPLACE PROCEDURE cleanup_expired_runtimedb_data()
LANGUAGE plpgsql
AS $$
DECLARE
    v_now TIMESTAMP := NOW() AT TIME ZONE 'UTC';
BEGIN
    DELETE FROM "FLOW_CONTEXT"          WHERE EXPIRY_TIME < v_now;
    DELETE FROM "AUTHORIZATION_CODE"    WHERE EXPIRY_TIME < v_now;
    DELETE FROM "AUTHORIZATION_REQUEST" WHERE EXPIRY_TIME < v_now;
    DELETE FROM "WEBAUTHN_SESSION"      WHERE EXPIRY_TIME < v_now;
    DELETE FROM "ATTRIBUTE_CACHE"       WHERE EXPIRY_TIME < v_now;
END;
$$;

Defining Queries

DBQuery

Queries are defined as DBQuery values from internal/system/database/model. Each query requires a unique ID for traceability.

var queryGetIDPByID = model.DBQuery{
    ID:    "IPQ-IDP_MGT-02",
    Query: "SELECT ID, NAME, DESCRIPTION, TYPE, PROPERTIES FROM \"IDP\" WHERE ID = $1 AND DEPLOYMENT_ID = $2",
}

Database-Specific Queries

When query syntax differs between PostgreSQL and SQLite, define both variants using the Query and SQLiteQuery fields on DBQuery.

var queryUpsertTranslation = dbmodel.DBQuery{
    ID:    "I18N-06",
    Query: `INSERT INTO "TRANSLATION" (MESSAGE_KEY, LANGUAGE_CODE, NAMESPACE, VALUE, DEPLOYMENT_ID)
            VALUES ($1, $2, $3, $4, $5)
            ON CONFLICT (DEPLOYMENT_ID, NAMESPACE, MESSAGE_KEY, LANGUAGE_CODE)
            DO UPDATE SET VALUE = excluded.VALUE, UPDATED_AT = NOW()`,
    SQLiteQuery: `INSERT INTO "TRANSLATION" (MESSAGE_KEY, LANGUAGE_CODE, NAMESPACE, VALUE, DEPLOYMENT_ID)
                  VALUES ($1, $2, $3, $4, $5)
                  ON CONFLICT (DEPLOYMENT_ID, NAMESPACE, MESSAGE_KEY, LANGUAGE_CODE)
                  DO UPDATE SET VALUE = excluded.VALUE, UPDATED_AT = datetime('now')`,
}

Query ID Naming Convention

Query IDs follow the pattern <PREFIX>-<DOMAIN>_MGT-<SEQUENCE>, for example:

• IPQ-IDP_MGT-02 — identity provider query, sequence 2.
• ASQ-USER_MGT-04 — user management query, sequence 4.
• AZQ-ARS-02 — authorization request store query, sequence 2.

Use a consistent prefix per store and increment the sequence number for each new query in that store.

Schema Script Conventions

• Maintain separate schema scripts for PostgreSQL (postgres.sql) and SQLite (sqlite.sql) in each database directory under backend/dbscripts/.
• Apply schema changes to both scripts unless a feature is explicitly PostgreSQL-only.
• Add inline comments above each table and index definition explaining its purpose.
• Place indexes immediately after the table they support.

Quick Reference