Database

Ahdapa uses sqlx 0.8 with the AnyPool runtime-dispatch backend. The active database engine is selected by the db.url configuration key:

• sqlite:///path/to/file.db — SQLite (default; zero external dependencies)
• postgres://user:pass@host/dbname — PostgreSQL
• mariadb://user:pass@host/dbname — MariaDB / MySQL

Connection model

db::open in src/db/mod.rs:

1. Registers all sqlx drivers via sqlx::any::install_default_drivers().
2. Opens the pool (creates the SQLite file if it does not exist via ?mode=rwc).
3. For SQLite, enables WAL mode and performance pragmas:

   PRAGMA journal_mode=WAL;
   PRAGMA synchronous=NORMAL;
   PRAGMA foreign_keys=ON;
   

4. Runs all pending migrations via sqlx::migrate!("migrations/<backend>").

All queries are async and run directly on the tokio runtime. The pool is stored as AppState::db and shared across all handler tasks.

Schema

The schema is created by a single initial migration (migrations/<backend>/0001_initial.sql). All three backends have equivalent schemas; SQLite uses INTEGER for booleans and BLOB for binary data, while Postgres uses BYTEA.

CRDT tables

These tables persist the IdpCrdt snapshot and are updated on every gossip merge. See CRDT State for the full field semantics.

crdt_signing_keys — G-Set of signing key pairs.

CREATE TABLE crdt_signing_keys (
    kid             TEXT PRIMARY KEY,
    algorithm       TEXT    NOT NULL,   -- "EdDSA", "ML-DSA-65", etc.
    private_key_der BLOB    NOT NULL,
    public_key_der  BLOB    NOT NULL,
    not_before      INTEGER NOT NULL,   -- Unix timestamp
    not_after       INTEGER NOT NULL,   -- Unix timestamp
    added_by_node   TEXT    NOT NULL,
    added_at        INTEGER NOT NULL
);

Inserted with INSERT OR IGNORE — a key is immutable once written.

crdt_active_kid — LWW-Register for the current signing key ID.

CREATE TABLE crdt_active_kid (
    id          INTEGER PRIMARY KEY CHECK (id = 1),  -- singleton
    kid         TEXT    NOT NULL,
    set_at      INTEGER NOT NULL,
    set_by_node TEXT    NOT NULL
);

Updated with INSERT OR REPLACE.

crdt_wrapping_key — LWW-Register for the cluster AEAD wrapping key.

CREATE TABLE crdt_wrapping_key (
    id                 INTEGER PRIMARY KEY CHECK (id = 1),
    cms_enveloped_data BLOB    NOT NULL,
    rotated_at         INTEGER NOT NULL,
    rotated_by_node    TEXT    NOT NULL
);

The cms_enveloped_data column stores the raw 32-byte key (bootstrap mode) or a CMS EnvelopedData blob when the ML-KEM per-node distribution is wired in.

crdt_cluster_nodes — OR-Map of cluster node registrations.

CREATE TABLE crdt_cluster_nodes (
    node_id         TEXT    PRIMARY KEY,
    certificate_der BLOB    NOT NULL,
    public_key_der  BLOB    NOT NULL,
    added_at        INTEGER NOT NULL,
    tombstone       INTEGER NOT NULL DEFAULT 0,
    tombstone_at    INTEGER
);

crdt_clients — OR-Map of OAuth2 client registrations.

CREATE TABLE crdt_clients (
    client_id                  TEXT PRIMARY KEY,
    client_name                TEXT NOT NULL,
    redirect_uris              TEXT NOT NULL,   -- JSON array
    scopes                     TEXT NOT NULL,   -- space-separated
    token_endpoint_auth_method TEXT NOT NULL DEFAULT 'private_key_jwt',
    client_secret              TEXT,
    jwks_uri                   TEXT,
    source                     TEXT NOT NULL DEFAULT 'static',  -- "static"|"dynamic"
    created_at                 INTEGER NOT NULL,
    tombstone                  INTEGER NOT NULL DEFAULT 0,
    tombstone_at               INTEGER,
    -- Added in migration 0022_client_kerberos.sql:
    kerberos_principal         TEXT DEFAULT NULL,
    kerberos_principal_pattern TEXT DEFAULT NULL,
    kerberos_hbac_service      TEXT DEFAULT NULL
);

kerberos_principal and kerberos_principal_pattern are mutually exclusive and are only set when token_endpoint_auth_method = 'kerberos_client_auth'. All three columns are NULL for non-Kerberos clients.

crdt_refresh_families — LWW-Map of refresh token family state.

CREATE TABLE crdt_refresh_families (
    family_id       TEXT    PRIMARY KEY,
    sub             TEXT    NOT NULL,
    client_id       TEXT    NOT NULL,
    max_index       INTEGER NOT NULL DEFAULT 0,
    updated_at      INTEGER NOT NULL,
    updated_by_node TEXT    NOT NULL,
    expires_at      INTEGER NOT NULL
);

max_index = 9223372036854775807 (i64::MAX, stored as u64::MAX cast to i64) signals revocation.

crdt_ipa_idp_overrides — LWW-Map of per-IPA-IdP ACR/AMR overrides (migration 0021_crdt_ipa_idp_overrides.sql).

CREATE TABLE IF NOT EXISTS crdt_ipa_idp_overrides (
    id          TEXT    NOT NULL PRIMARY KEY,   -- "ipa-<slug>"
    default_acr TEXT,                           -- NULL = no override
    default_amr TEXT    NOT NULL DEFAULT '[]',  -- JSON array
    set_at      INTEGER NOT NULL DEFAULT 0,
    set_by_node TEXT    NOT NULL DEFAULT '',
    is_deleted  INTEGER NOT NULL DEFAULT 0,
    deleted_at  INTEGER NOT NULL DEFAULT 0
);

Stores only the two operator-writable fields (default_acr, default_amr) for each IPA-sourced IdP. All LDAP-sourced attributes are not persisted here. Written by PUT /api/admin/federation/ipa-idps/{id}; applied at find_upstream() time by patching the in-memory UpstreamIdpConfig.

Ephemeral tables

These tables are not CRDT-replicated. They hold short-lived state that is local to the node.

par_requests — Pushed Authorization Request objects (RFC 9126, 90-second TTL).

CREATE TABLE par_requests (
    request_uri    TEXT PRIMARY KEY,
    request_object TEXT    NOT NULL,  -- JSON
    client_id      TEXT    NOT NULL,
    created_at     INTEGER NOT NULL,
    expires_at     INTEGER NOT NULL
);
CREATE INDEX idx_par_requests_expires ON par_requests (expires_at);

device_codes — Device Authorization Grant (RFC 8628) polling state.

CREATE TABLE device_codes (
    device_code      TEXT    PRIMARY KEY,
    user_code        TEXT    NOT NULL UNIQUE,
    client_id        TEXT    NOT NULL,
    scope            TEXT    NOT NULL,
    verification_uri TEXT    NOT NULL,
    created_at       INTEGER NOT NULL,
    expires_at       INTEGER NOT NULL,
    poll_interval    INTEGER NOT NULL DEFAULT 5,
    authorized       INTEGER NOT NULL DEFAULT 0,
    denied           INTEGER NOT NULL DEFAULT 0,
    sub              TEXT    -- set when the user approves
);
CREATE INDEX idx_device_codes_user_code ON device_codes (user_code);

What is and is not persisted

Migration numbering

Each backend has its own migration directory under migrations/. The SQLite, Postgres, and MariaDB directories track migrations independently — when a migration requires backend-specific DDL, that file appears only in the affected directory while the other backends advance their numbering without a corresponding file, so the three directories may diverge in total file count while the logical schema version remains consistent. New migrations are numbered sequentially (0001_initial.sql, 0002_…, …); the highest-numbered file in each directory reflects the current schema version for that backend.