Skip to main content

USERSTORE_SCHEMA

Constant USERSTORE_SCHEMA 

Source
pub const USERSTORE_SCHEMA: &str = r#"
CREATE TABLE IF NOT EXISTS users (
    username TEXT PRIMARY KEY CHECK (username <> ''),
    sub TEXT NOT NULL UNIQUE CHECK (sub <> ''),
    name BYTEA CONSTRAINT users_name_storage_check CHECK (
        name IS NULL OR (
            octet_length(name) >= 32
            AND substring(name from 1 for 4) = decode('48534331', 'hex')
        )
    ),
    email BYTEA CONSTRAINT users_email_storage_check CHECK (
        email IS NULL OR (
            octet_length(email) >= 32
            AND substring(email from 1 for 4) = decode('48534331', 'hex')
        )
    ),
    email_verified BOOLEAN,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    external_id BYTEA CONSTRAINT users_external_id_storage_check CHECK (
        external_id IS NULL OR (
            octet_length(external_id) >= 32
            AND substring(external_id from 1 for 4) = decode('48534331', 'hex')
        )
    ),
    key_custody TEXT
        CHECK (key_custody IS NULL OR key_custody IN ('self_custody', 'managed')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS user_did_bindings (
    username TEXT PRIMARY KEY REFERENCES users(username) ON DELETE CASCADE,
    atproto_did TEXT NOT NULL UNIQUE CHECK (atproto_did <> '')
);
CREATE TABLE IF NOT EXISTS oidc_bindings (
    issuer TEXT NOT NULL CHECK (issuer <> ''),
    issuer_sub TEXT NOT NULL CHECK (issuer_sub <> ''),
    username TEXT NOT NULL REFERENCES users(username) ON DELETE CASCADE,
    PRIMARY KEY (issuer, issuer_sub),
    UNIQUE (issuer, username)
);
CREATE TABLE IF NOT EXISTS pubkeys (
    fingerprint TEXT PRIMARY KEY CHECK (fingerprint <> ''),
    username TEXT NOT NULL REFERENCES users(username) ON DELETE CASCADE,
    pubkey BYTEA NOT NULL
        CONSTRAINT pubkeys_pubkey_storage_check CHECK (
            octet_length(pubkey) = 64
            AND substring(pubkey from 1 for 4) = decode('48534331', 'hex')
        ),
    label BYTEA CONSTRAINT pubkeys_label_storage_check CHECK (
        label IS NULL OR (
            octet_length(label) >= 32
            AND substring(label from 1 for 4) = decode('48534331', 'hex')
        )
    ),
    algorithm TEXT NOT NULL DEFAULT 'ed25519'
        CHECK (algorithm IN ('ed25519', 'ed25519+ml-dsa-65')),
    pq_pubkey BYTEA,
    created_at BIGINT NOT NULL,
    last_used_at BIGINT,
    CONSTRAINT pubkeys_algorithm_pq_storage_check CHECK (
        (algorithm = 'ed25519' AND pq_pubkey IS NULL)
        OR (
            algorithm = 'ed25519+ml-dsa-65'
            AND pq_pubkey IS NOT NULL
            AND octet_length(pq_pubkey) = 1984
            AND substring(pq_pubkey from 1 for 4) = decode('48534331', 'hex')
        )
    )
);
CREATE INDEX IF NOT EXISTS pubkeys_username_idx ON pubkeys(username);
CREATE INDEX IF NOT EXISTS oidc_bindings_username_idx ON oidc_bindings(username);
CREATE TABLE IF NOT EXISTS user_encryption_keys (
    username TEXT PRIMARY KEY REFERENCES users(username) ON DELETE CASCADE,
    wrapped_dek BYTEA NOT NULL
);
"#;
Expand description

Re-exported from super::cipher_glue::USERSTORE_SCHEMA so PGlite and Postgres apply the same encrypted-storage constraints. PostgreSQL-compatible schema shared by embedded PGlite and server Postgres.

Lookup keys stay queryable. PII, labels, and key material use BYTEA so envelope encryption can be added without changing relational identities.

This const is the single source of truth for the relational UserStore DDL (#1351). Both PgliteUserStore and PostgresUserStore consume it verbatim — they do not define their own copies.