assay_auth/
schema.rs

1//! Auth module schema bootstrap + migration runner.
2//!
3//! Provides `migrate_*` entrypoints the engine boot path (in
4//! `crates/assay-engine/src/init.rs`) calls when `engine.modules` shows
5//! `auth` enabled. Each entrypoint:
6//!
7//! 1. Ensures the storage container exists. PG: `CREATE SCHEMA IF NOT
8//!    EXISTS auth`. SQLite: relies on the engine boot having ATTACHed
9//!    `data/auth.db` AS `auth` — the migration runs DDL into the
10//!    attachment.
11//! 2. Applies every DDL statement up to the current
12//!    [`MIGRATION_VERSION`] for tables in this module.
13//! 3. Records the applied version into `engine.migrations` with
14//!    `module = MODULE_NAME` so subsequent boots skip already-applied
15//!    versions.
16//!
17//! The migration is idempotent — every CREATE uses `IF NOT EXISTS`,
18//! every INSERT into `engine.migrations` uses `ON CONFLICT DO NOTHING`.
19//! Re-running on a healthy DB is a no-op.
20//!
21//! Tables created (per plan 12c with v0.1.2 schema-qualifying applied):
22//!
23//! - `auth.users` — authoritative user records (id, email,
24//!   password_hash, …)
25//! - `auth.user_upstream` — federated identity links (provider/subject
26//!   tuples → user_id)
27//! - `auth.passkeys` — WebAuthn credentials per user
28//! - `auth.sessions` — opaque session ids + CSRF tokens + expiry
29//! - `auth.jwks_keys` — rotated JWT signing keys (active + history)
30//! - `auth.audit` — append-only compliance log (deferred to a later
31//!   phase — see Phase 4 notes)
32//!
33//! Auth does NOT write to `engine.events`; auth's real-time signal (if
34//! ever needed) goes through its own channel on `auth.audit`.
35
36/// Stable name registered in `engine.modules.name` and used as the
37/// `module` discriminant in `engine.migrations`. Matches the schema
38/// (PG) / attached-database (SQLite) name 1:1 so SQL stays readable.
39pub const MODULE_NAME: &str = "auth";
40
41/// Highest migration version this build knows about. Bumped each time
42/// a new DDL pack is appended below. The runner records every version
43/// up to and including this one into `engine.migrations`.
44///
45/// V1: users / sessions / passkeys / user_upstream / jwks_keys.
46/// V2: adds `auth.biscuit_root_keys` for the always-on
47///               biscuit capability-token root key bootstrap.
48/// V3: adds `auth.zanzibar_namespaces` + `auth.zanzibar_tuples`
49///               for ReBAC. Recursive-CTE walk + reverse index for
50///               Keto/SpiceDB-equivalent permission checks.
51/// V4: adds the OIDC provider tables — `auth.oidc_clients`,
52///               `auth.upstream_providers`, `auth.oidc_authorization_codes`,
53///               `auth.oidc_refresh_tokens`, `auth.oidc_sessions`,
54///               `auth.oidc_consents`, and `auth.oidc_upstream_states`.
55///               Together they make `assay-engine` a conformant OIDC
56///               provider (Hydra equivalent).
57pub const MIGRATION_VERSION: i32 = 4;
58
59/// Postgres DDL for the auth schema, version 1.
60///
61/// All tables are schema-qualified (`auth.*`) so they live in the
62/// `auth` schema regardless of the connection's `search_path`. The
63/// CREATE SCHEMA IF NOT EXISTS is included here for completeness even
64/// though engine boot also runs it — both paths must work
65/// independently for tests that bootstrap the auth schema directly.
66///
67/// `auth.audit` is intentionally deferred — the table is part of plan
68/// 12c phase 4 task 4.6 step 1 but no caller writes to it yet, and
69/// shipping the DDL without a writer risks confusing operators.
70/// Phase 5/6 will add it alongside the first auditable action.
71pub const PG_DDL_V1: &str = r#"
72CREATE SCHEMA IF NOT EXISTS auth;
73
74CREATE TABLE IF NOT EXISTS auth.users (
75    id              TEXT PRIMARY KEY,
76    email           TEXT UNIQUE,
77    email_verified  BOOLEAN NOT NULL DEFAULT FALSE,
78    display_name    TEXT,
79    password_hash   TEXT,
80    created_at      DOUBLE PRECISION NOT NULL
81);
82
83CREATE TABLE IF NOT EXISTS auth.user_upstream (
84    provider    TEXT NOT NULL,
85    subject     TEXT NOT NULL,
86    user_id     TEXT NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
87    PRIMARY KEY (provider, subject)
88);
89CREATE INDEX IF NOT EXISTS idx_auth_user_upstream_user
90    ON auth.user_upstream (user_id);
91
92CREATE TABLE IF NOT EXISTS auth.passkeys (
93    credential_id   BYTEA PRIMARY KEY,
94    user_id         TEXT NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
95    public_key      BYTEA NOT NULL,
96    sign_count      INTEGER NOT NULL DEFAULT 0,
97    transports      TEXT NOT NULL,
98    created_at      DOUBLE PRECISION NOT NULL
99);
100CREATE INDEX IF NOT EXISTS idx_auth_passkeys_user
101    ON auth.passkeys (user_id);
102
103CREATE TABLE IF NOT EXISTS auth.sessions (
104    id                  TEXT PRIMARY KEY,
105    user_id             TEXT NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
106    csrf_token          TEXT NOT NULL,
107    created_at          DOUBLE PRECISION NOT NULL,
108    expires_at          DOUBLE PRECISION NOT NULL,
109    ip_hash             TEXT,
110    user_agent_hash     TEXT
111);
112CREATE INDEX IF NOT EXISTS idx_auth_sessions_user
113    ON auth.sessions (user_id);
114CREATE INDEX IF NOT EXISTS idx_auth_sessions_expires
115    ON auth.sessions (expires_at);
116
117CREATE TABLE IF NOT EXISTS auth.jwks_keys (
118    kid                     TEXT PRIMARY KEY,
119    alg                     TEXT NOT NULL,
120    public_jwk              JSONB NOT NULL,
121    private_pem_encrypted   BYTEA,
122    created_at              DOUBLE PRECISION NOT NULL,
123    rotated_at              DOUBLE PRECISION,
124    expires_at              DOUBLE PRECISION
125);
126CREATE INDEX IF NOT EXISTS idx_auth_jwks_keys_active
127    ON auth.jwks_keys (rotated_at) WHERE rotated_at IS NULL;
128"#;
129
130/// Postgres DDL for the auth schema, version 2 — adds
131/// `auth.biscuit_root_keys` for the always-on biscuit root key
132/// bootstrap. The `private_pem` column is plaintext today; secret-at-rest
133/// envelope is a later phase (matches the `auth.jwks_keys.private_pem_encrypted`
134/// shape — same TODO surface).
135pub const PG_DDL_V2: &str = r#"
136CREATE TABLE IF NOT EXISTS auth.biscuit_root_keys (
137    kid             TEXT PRIMARY KEY,
138    private_pem     BYTEA NOT NULL,
139    public_pem      TEXT NOT NULL,
140    created_at      DOUBLE PRECISION NOT NULL,
141    rotated_at      DOUBLE PRECISION
142);
143CREATE INDEX IF NOT EXISTS idx_auth_biscuit_root_keys_active
144    ON auth.biscuit_root_keys (rotated_at) WHERE rotated_at IS NULL;
145"#;
146
147/// Postgres DDL for the auth schema, version 3 — Zanzibar / ReBAC.
148///
149/// Two tables:
150///
151/// - `auth.zanzibar_namespaces` — JSON-serialised
152///   [`crate::zanzibar::NamespaceSchema`], one row per namespace
153///   (`document`, `group`, `user`, …). The schema parser writes here on
154///   `define_namespace`.
155/// - `auth.zanzibar_tuples` — the relation-tuple table, the canonical
156///   Zanzibar/Keto data model. Composite PK supports the forward
157///   `(object, relation, *)` index for `check`; the auxiliary
158///   `idx_auth_zanzibar_tuples_rev` covers
159///   `(subject_type, subject_id, relation)` for reverse lookups
160///   (`lookup_resources`, expand-from-subject paths).
161///
162/// `subject_rel` is `TEXT NOT NULL DEFAULT ''`. Direct subjects
163/// (e.g. `user:alice`) store an empty string; userset subjects
164/// (e.g. `family:foo#member`) store the relation name. The empty-
165/// string sentinel lets the column stay in the primary key without
166/// the NULL-distinctness pitfall that bit the original schema (PG
167/// implicitly NOT-NULLs all PK columns, so any insert with NULL
168/// hard-failed even though the surrounding code paths treated NULL
169/// as the encoding for "direct subject"). The PK alone now enforces
170/// uniqueness for both arms.
171pub const PG_DDL_V3: &str = r#"
172CREATE TABLE IF NOT EXISTS auth.zanzibar_namespaces (
173    name        TEXT PRIMARY KEY,
174    schema_json JSONB NOT NULL,
175    updated_at  DOUBLE PRECISION NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())
176);
177
178CREATE TABLE IF NOT EXISTS auth.zanzibar_tuples (
179    object_type  TEXT NOT NULL,
180    object_id    TEXT NOT NULL,
181    relation     TEXT NOT NULL,
182    subject_type TEXT NOT NULL,
183    subject_id   TEXT NOT NULL,
184    subject_rel  TEXT NOT NULL DEFAULT '',
185    created_at   DOUBLE PRECISION NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW()),
186    PRIMARY KEY (object_type, object_id, relation, subject_type, subject_id, subject_rel)
187);
188CREATE INDEX IF NOT EXISTS idx_auth_zanzibar_tuples_rev
189    ON auth.zanzibar_tuples (subject_type, subject_id, relation);
190"#;
191
192/// Postgres DDL for the auth schema, version 4 — full OIDC provider.
193///
194/// Seven tables together implement a conformant Authorization-Code +
195/// PKCE OIDC provider with refresh tokens, RP-initiated logout via SSO
196/// session registry, per-(user, client) consent records, and upstream
197/// federation state for the assay-as-RP path:
198///
199/// - `auth.oidc_clients` — registered consumer apps (client_id +
200///   secret hash + redirect URIs + auth method + default scopes +
201///   consent toggle).
202/// - `auth.upstream_providers` — federated identity providers
203///   (Google / Apple / GitHub / any OIDC IdP); used by the
204///   `auth.oidc.OidcRegistry` to seed itself on boot.
205/// - `auth.oidc_authorization_codes` — single-use codes issued at the
206///   end of `/authorize`; consumed at `/token` exchange.
207/// - `auth.oidc_refresh_tokens` — long-lived bearer tokens stored as
208///   SHA-256 hashes (the bearer never round-trips the DB in plaintext).
209/// - `auth.oidc_sessions` — SSO session registry; one row per issued
210///   id_token. Carries the `sid` claim so `/logout` can fan out
211///   back-channel logout to every consumer.
212/// - `auth.oidc_consents` — per-(user, client) consent grants so the
213///   consent screen only shows on first authorize for a given pair.
214/// - `auth.oidc_upstream_states` — short-lived per-login rows for the
215///   federation flow (state + nonce + pkce_verifier + return_to).
216pub const PG_DDL_V4: &str = r#"
217CREATE TABLE IF NOT EXISTS auth.oidc_clients (
218    client_id                       TEXT PRIMARY KEY,
219    client_secret_hash              TEXT,
220    redirect_uris                   TEXT NOT NULL,
221    name                            TEXT NOT NULL,
222    logo_url                        TEXT,
223    token_endpoint_auth_method      TEXT NOT NULL,
224    default_scopes                  TEXT NOT NULL,
225    require_consent                 BOOLEAN NOT NULL DEFAULT TRUE,
226    grant_types                     TEXT NOT NULL DEFAULT '["authorization_code","refresh_token"]',
227    response_types                  TEXT NOT NULL DEFAULT '["code"]',
228    pkce_required                   BOOLEAN NOT NULL DEFAULT TRUE,
229    backchannel_logout_uri          TEXT,
230    created_at                      DOUBLE PRECISION NOT NULL
231);
232
233CREATE TABLE IF NOT EXISTS auth.upstream_providers (
234    slug            TEXT PRIMARY KEY,
235    issuer          TEXT NOT NULL,
236    client_id       TEXT NOT NULL,
237    client_secret   TEXT NOT NULL,
238    display_name    TEXT NOT NULL,
239    icon_url        TEXT,
240    enabled         BOOLEAN NOT NULL DEFAULT TRUE
241);
242
243CREATE TABLE IF NOT EXISTS auth.oidc_authorization_codes (
244    code                    TEXT PRIMARY KEY,
245    client_id               TEXT NOT NULL,
246    user_id                 TEXT NOT NULL,
247    redirect_uri            TEXT NOT NULL,
248    scopes                  TEXT NOT NULL,
249    code_challenge          TEXT NOT NULL,
250    code_challenge_method   TEXT NOT NULL,
251    nonce                   TEXT,
252    state                   TEXT,
253    issued_at               DOUBLE PRECISION NOT NULL,
254    expires_at              DOUBLE PRECISION NOT NULL,
255    consumed                BOOLEAN NOT NULL DEFAULT FALSE
256);
257
258CREATE TABLE IF NOT EXISTS auth.oidc_refresh_tokens (
259    token_hash      TEXT PRIMARY KEY,
260    client_id       TEXT NOT NULL,
261    user_id         TEXT NOT NULL,
262    scopes          TEXT NOT NULL,
263    issued_at       DOUBLE PRECISION NOT NULL,
264    expires_at      DOUBLE PRECISION NOT NULL,
265    revoked         BOOLEAN NOT NULL DEFAULT FALSE
266);
267CREATE INDEX IF NOT EXISTS idx_auth_oidc_refresh_user
268    ON auth.oidc_refresh_tokens (user_id);
269
270CREATE TABLE IF NOT EXISTS auth.oidc_sessions (
271    sid                     TEXT PRIMARY KEY,
272    user_id                 TEXT NOT NULL,
273    client_id               TEXT NOT NULL,
274    assay_session_id        TEXT,
275    issued_at               DOUBLE PRECISION NOT NULL,
276    backchannel_logout_uri  TEXT
277);
278CREATE INDEX IF NOT EXISTS idx_auth_oidc_sessions_user
279    ON auth.oidc_sessions (user_id);
280CREATE INDEX IF NOT EXISTS idx_auth_oidc_sessions_assay
281    ON auth.oidc_sessions (assay_session_id);
282
283CREATE TABLE IF NOT EXISTS auth.oidc_consents (
284    user_id     TEXT NOT NULL,
285    client_id   TEXT NOT NULL,
286    scopes      TEXT NOT NULL,
287    granted_at  DOUBLE PRECISION NOT NULL,
288    PRIMARY KEY (user_id, client_id)
289);
290
291CREATE TABLE IF NOT EXISTS auth.oidc_upstream_states (
292    state           TEXT PRIMARY KEY,
293    provider_slug   TEXT NOT NULL,
294    nonce           TEXT NOT NULL,
295    pkce_verifier   TEXT NOT NULL,
296    return_to       TEXT,
297    created_at      DOUBLE PRECISION NOT NULL,
298    expires_at      DOUBLE PRECISION NOT NULL
299);
300"#;
301
302/// SQLite DDL for the auth schema, version 1.
303///
304/// Caller must have ATTACHed `data/auth.db` AS `auth` before running
305/// this — engine boot is responsible for that wiring (matches the
306/// pattern already used for the engine + workflow attachments). The
307/// DDL itself uses unqualified table names because SQLite CREATE
308/// TABLE doesn't accept the `schema.table` form for the table itself
309/// when CREATE INDEX … ON table is used unqualified; we therefore
310/// build per-statement queries that prefix the schema explicitly.
311///
312/// Mirrors the PG layout: `BYTEA` → `BLOB`, `BOOLEAN` → `INTEGER`,
313/// `JSONB` → `TEXT` (JSON-encoded), `DOUBLE PRECISION` → `REAL`.
314pub const SQLITE_DDL_V1: &[(&str, &str)] = &[
315    (
316        "users",
317        "CREATE TABLE IF NOT EXISTS auth.users (
318            id              TEXT PRIMARY KEY,
319            email           TEXT UNIQUE,
320            email_verified  INTEGER NOT NULL DEFAULT 0,
321            display_name    TEXT,
322            password_hash   TEXT,
323            created_at      REAL NOT NULL
324        )",
325    ),
326    (
327        "user_upstream",
328        "CREATE TABLE IF NOT EXISTS auth.user_upstream (
329            provider    TEXT NOT NULL,
330            subject     TEXT NOT NULL,
331            user_id     TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
332            PRIMARY KEY (provider, subject)
333        )",
334    ),
335    (
336        "idx_user_upstream_user",
337        "CREATE INDEX IF NOT EXISTS auth.idx_auth_user_upstream_user \
338         ON user_upstream (user_id)",
339    ),
340    (
341        "passkeys",
342        "CREATE TABLE IF NOT EXISTS auth.passkeys (
343            credential_id   BLOB PRIMARY KEY,
344            user_id         TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
345            public_key      BLOB NOT NULL,
346            sign_count      INTEGER NOT NULL DEFAULT 0,
347            transports      TEXT NOT NULL,
348            created_at      REAL NOT NULL
349        )",
350    ),
351    (
352        "idx_passkeys_user",
353        "CREATE INDEX IF NOT EXISTS auth.idx_auth_passkeys_user ON passkeys (user_id)",
354    ),
355    (
356        "sessions",
357        "CREATE TABLE IF NOT EXISTS auth.sessions (
358            id                  TEXT PRIMARY KEY,
359            user_id             TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
360            csrf_token          TEXT NOT NULL,
361            created_at          REAL NOT NULL,
362            expires_at          REAL NOT NULL,
363            ip_hash             TEXT,
364            user_agent_hash     TEXT
365        )",
366    ),
367    (
368        "idx_sessions_user",
369        "CREATE INDEX IF NOT EXISTS auth.idx_auth_sessions_user ON sessions (user_id)",
370    ),
371    (
372        "idx_sessions_expires",
373        "CREATE INDEX IF NOT EXISTS auth.idx_auth_sessions_expires ON sessions (expires_at)",
374    ),
375    (
376        "jwks_keys",
377        "CREATE TABLE IF NOT EXISTS auth.jwks_keys (
378            kid                     TEXT PRIMARY KEY,
379            alg                     TEXT NOT NULL,
380            public_jwk              TEXT NOT NULL,
381            private_pem_encrypted   BLOB,
382            created_at              REAL NOT NULL,
383            rotated_at              REAL,
384            expires_at              REAL
385        )",
386    ),
387    (
388        "idx_jwks_keys_active",
389        "CREATE INDEX IF NOT EXISTS auth.idx_auth_jwks_keys_active \
390         ON jwks_keys (rotated_at) WHERE rotated_at IS NULL",
391    ),
392];
393
394/// SQLite DDL for the auth schema, version 2 — biscuit root keys.
395/// Mirrors [`PG_DDL_V2`] with `BYTEA` → `BLOB` and `DOUBLE PRECISION` →
396/// `REAL`.
397pub const SQLITE_DDL_V2: &[(&str, &str)] = &[
398    (
399        "biscuit_root_keys",
400        "CREATE TABLE IF NOT EXISTS auth.biscuit_root_keys (
401            kid             TEXT PRIMARY KEY,
402            private_pem     BLOB NOT NULL,
403            public_pem      TEXT NOT NULL,
404            created_at      REAL NOT NULL,
405            rotated_at      REAL
406        )",
407    ),
408    (
409        "idx_biscuit_root_keys_active",
410        "CREATE INDEX IF NOT EXISTS auth.idx_auth_biscuit_root_keys_active \
411         ON biscuit_root_keys (rotated_at) WHERE rotated_at IS NULL",
412    ),
413];
414
415/// SQLite DDL for the auth schema, version 3 — Zanzibar / ReBAC.
416///
417/// Mirrors [`PG_DDL_V3`] with `JSONB` → `TEXT` (caller round-trips
418/// via `serde_json`), `DOUBLE PRECISION` → `REAL`. SQLite's
419/// `default CURRENT_TIMESTAMP` returns a string, not a unix epoch
420/// double, so the SQLite store binds the timestamp explicitly on
421/// every insert (matches the rest of the auth schema's discipline).
422///
423/// Treats `subject_rel` exactly as PG does: empty string for direct
424/// subjects, relation name for usersets. The empty-string sentinel
425/// avoids the NULL-vs-PK-NOT-NULL conflict the original schema had
426/// (see PG DDL note above).
427pub const SQLITE_DDL_V3: &[(&str, &str)] = &[
428    (
429        "zanzibar_namespaces",
430        "CREATE TABLE IF NOT EXISTS auth.zanzibar_namespaces (
431            name        TEXT PRIMARY KEY,
432            schema_json TEXT NOT NULL,
433            updated_at  REAL NOT NULL
434        )",
435    ),
436    (
437        "zanzibar_tuples",
438        "CREATE TABLE IF NOT EXISTS auth.zanzibar_tuples (
439            object_type  TEXT NOT NULL,
440            object_id    TEXT NOT NULL,
441            relation     TEXT NOT NULL,
442            subject_type TEXT NOT NULL,
443            subject_id   TEXT NOT NULL,
444            subject_rel  TEXT NOT NULL DEFAULT '',
445            created_at   REAL NOT NULL,
446            PRIMARY KEY (object_type, object_id, relation, subject_type, subject_id, subject_rel)
447        )",
448    ),
449    (
450        "idx_zanzibar_tuples_rev",
451        "CREATE INDEX IF NOT EXISTS auth.idx_auth_zanzibar_tuples_rev \
452         ON zanzibar_tuples (subject_type, subject_id, relation)",
453    ),
454];
455
456/// SQLite DDL for the auth schema, version 4 — full OIDC provider.
457///
458/// Mirrors [`PG_DDL_V4`] with `BOOLEAN` → `INTEGER` and `DOUBLE PRECISION`
459/// → `REAL`. JSON arrays (redirect_uris, default_scopes, scopes, …) ride
460/// in `TEXT` columns and round-trip via `serde_json` in the store layer
461/// — same convention `auth.zanzibar_namespaces` uses for `schema_json`.
462pub const SQLITE_DDL_V4: &[(&str, &str)] = &[
463    (
464        "oidc_clients",
465        "CREATE TABLE IF NOT EXISTS auth.oidc_clients (
466            client_id                       TEXT PRIMARY KEY,
467            client_secret_hash              TEXT,
468            redirect_uris                   TEXT NOT NULL,
469            name                            TEXT NOT NULL,
470            logo_url                        TEXT,
471            token_endpoint_auth_method      TEXT NOT NULL,
472            default_scopes                  TEXT NOT NULL,
473            require_consent                 INTEGER NOT NULL DEFAULT 1,
474            grant_types                     TEXT NOT NULL DEFAULT '[\"authorization_code\",\"refresh_token\"]',
475            response_types                  TEXT NOT NULL DEFAULT '[\"code\"]',
476            pkce_required                   INTEGER NOT NULL DEFAULT 1,
477            backchannel_logout_uri          TEXT,
478            created_at                      REAL NOT NULL
479        )",
480    ),
481    (
482        "upstream_providers",
483        "CREATE TABLE IF NOT EXISTS auth.upstream_providers (
484            slug            TEXT PRIMARY KEY,
485            issuer          TEXT NOT NULL,
486            client_id       TEXT NOT NULL,
487            client_secret   TEXT NOT NULL,
488            display_name    TEXT NOT NULL,
489            icon_url        TEXT,
490            enabled         INTEGER NOT NULL DEFAULT 1
491        )",
492    ),
493    (
494        "oidc_authorization_codes",
495        "CREATE TABLE IF NOT EXISTS auth.oidc_authorization_codes (
496            code                    TEXT PRIMARY KEY,
497            client_id               TEXT NOT NULL,
498            user_id                 TEXT NOT NULL,
499            redirect_uri            TEXT NOT NULL,
500            scopes                  TEXT NOT NULL,
501            code_challenge          TEXT NOT NULL,
502            code_challenge_method   TEXT NOT NULL,
503            nonce                   TEXT,
504            state                   TEXT,
505            issued_at               REAL NOT NULL,
506            expires_at              REAL NOT NULL,
507            consumed                INTEGER NOT NULL DEFAULT 0
508        )",
509    ),
510    (
511        "oidc_refresh_tokens",
512        "CREATE TABLE IF NOT EXISTS auth.oidc_refresh_tokens (
513            token_hash      TEXT PRIMARY KEY,
514            client_id       TEXT NOT NULL,
515            user_id         TEXT NOT NULL,
516            scopes          TEXT NOT NULL,
517            issued_at       REAL NOT NULL,
518            expires_at      REAL NOT NULL,
519            revoked         INTEGER NOT NULL DEFAULT 0
520        )",
521    ),
522    (
523        "idx_oidc_refresh_user",
524        "CREATE INDEX IF NOT EXISTS auth.idx_auth_oidc_refresh_user \
525         ON oidc_refresh_tokens (user_id)",
526    ),
527    (
528        "oidc_sessions",
529        "CREATE TABLE IF NOT EXISTS auth.oidc_sessions (
530            sid                     TEXT PRIMARY KEY,
531            user_id                 TEXT NOT NULL,
532            client_id               TEXT NOT NULL,
533            assay_session_id        TEXT,
534            issued_at               REAL NOT NULL,
535            backchannel_logout_uri  TEXT
536        )",
537    ),
538    (
539        "idx_oidc_sessions_user",
540        "CREATE INDEX IF NOT EXISTS auth.idx_auth_oidc_sessions_user \
541         ON oidc_sessions (user_id)",
542    ),
543    (
544        "idx_oidc_sessions_assay",
545        "CREATE INDEX IF NOT EXISTS auth.idx_auth_oidc_sessions_assay \
546         ON oidc_sessions (assay_session_id)",
547    ),
548    (
549        "oidc_consents",
550        "CREATE TABLE IF NOT EXISTS auth.oidc_consents (
551            user_id     TEXT NOT NULL,
552            client_id   TEXT NOT NULL,
553            scopes      TEXT NOT NULL,
554            granted_at  REAL NOT NULL,
555            PRIMARY KEY (user_id, client_id)
556        )",
557    ),
558    (
559        "oidc_upstream_states",
560        "CREATE TABLE IF NOT EXISTS auth.oidc_upstream_states (
561            state           TEXT PRIMARY KEY,
562            provider_slug   TEXT NOT NULL,
563            nonce           TEXT NOT NULL,
564            pkce_verifier   TEXT NOT NULL,
565            return_to       TEXT,
566            created_at      REAL NOT NULL,
567            expires_at      REAL NOT NULL
568        )",
569    ),
570];
571
572/// Postgres migration runner.
573///
574/// Applies every DDL pack up to and including the current
575/// [`MIGRATION_VERSION`] (V1 then V2 today). Splits each pack into
576/// individual statements (sqlx requires one statement per `query`),
577/// executes each, then records `(MODULE_NAME, MIGRATION_VERSION)` into
578/// `engine.migrations`. Idempotent — every CREATE uses `IF NOT EXISTS`.
579#[cfg(feature = "backend-postgres")]
580pub async fn migrate_postgres(pool: &sqlx::PgPool) -> anyhow::Result<()> {
581    use anyhow::Context;
582    for ddl in [PG_DDL_V1, PG_DDL_V2, PG_DDL_V3, PG_DDL_V4] {
583        for stmt in split_pg_statements(ddl) {
584            sqlx::query(&stmt)
585                .execute(pool)
586                .await
587                .with_context(|| format!("auth pg migrate: {}", first_line(&stmt)))?;
588        }
589    }
590    sqlx::query(
591        "INSERT INTO engine.migrations (module, version) VALUES ($1, $2) \
592         ON CONFLICT DO NOTHING",
593    )
594    .bind(MODULE_NAME)
595    .bind(MIGRATION_VERSION)
596    .execute(pool)
597    .await
598    .context("record auth migration in engine.migrations")?;
599    Ok(())
600}
601
602/// SQLite migration runner.
603///
604/// Caller must have ATTACHed the auth database as `auth` before
605/// calling. Applies every DDL pack up to and including
606/// [`MIGRATION_VERSION`] (V1 then V2 today). Each DDL chunk is
607/// executed as its own statement; the per-table failure context names
608/// the table that broke so engine boot logs are actionable.
609#[cfg(feature = "backend-sqlite")]
610pub async fn migrate_sqlite(pool: &sqlx::SqlitePool) -> anyhow::Result<()> {
611    use anyhow::Context;
612    for pack in [SQLITE_DDL_V1, SQLITE_DDL_V2, SQLITE_DDL_V3, SQLITE_DDL_V4] {
613        for (label, stmt) in pack {
614            sqlx::query(stmt)
615                .execute(pool)
616                .await
617                .with_context(|| format!("auth sqlite migrate: {label}"))?;
618        }
619    }
620    sqlx::query(
621        "INSERT OR IGNORE INTO engine.migrations (module, version) VALUES (?, ?)",
622    )
623    .bind(MODULE_NAME)
624    .bind(MIGRATION_VERSION)
625    .execute(pool)
626    .await
627    .context("record auth migration in engine.migrations")?;
628    Ok(())
629}
630
631/// Split a PG DDL chunk into individual statements. Drops pure-comment
632/// lines first so a `--`-introduced semicolon doesn't fragment a real
633/// statement (mirrors the same trick `assay-workflow::store::postgres`
634/// uses for its larger SCHEMA constant).
635#[cfg(feature = "backend-postgres")]
636fn split_pg_statements(schema: &str) -> Vec<String> {
637    let cleaned: String = schema
638        .lines()
639        .filter(|line| !line.trim_start().starts_with("--"))
640        .collect::<Vec<_>>()
641        .join("\n");
642    cleaned
643        .split(';')
644        .map(|s| s.trim().to_string())
645        .filter(|s| !s.is_empty())
646        .collect()
647}
648
649#[cfg(feature = "backend-postgres")]
650fn first_line(stmt: &str) -> String {
651    stmt.lines()
652        .next()
653        .map(|s| s.trim().to_string())
654        .unwrap_or_default()
655}
656
657#[cfg(test)]
658mod tests {
659    use super::*;
660
661    #[test]
662    fn module_name_is_stable() {
663        // Locked-in: appears in `engine.modules`, in `engine.migrations.module`,
664        // and as the SQLite ATTACH alias / PG schema name. Renaming it is a
665        // breaking storage change; gate it behind a real version bump.
666        assert_eq!(MODULE_NAME, "auth");
667    }
668
669    #[cfg(feature = "backend-postgres")]
670    #[test]
671    fn pg_split_drops_pure_comment_lines_and_empty_fragments() {
672        let sql = "-- top\nCREATE TABLE a(x INT);\n-- mid\nCREATE INDEX i ON a(x);\n";
673        let stmts = split_pg_statements(sql);
674        assert_eq!(stmts.len(), 2);
675        assert!(stmts[0].starts_with("CREATE TABLE"));
676        assert!(stmts[1].starts_with("CREATE INDEX"));
677    }
678
679    #[cfg(feature = "backend-postgres")]
680    #[test]
681    fn pg_ddl_v1_split_is_nonempty() {
682        let stmts = split_pg_statements(PG_DDL_V1);
683        // Sanity: schema + 5 tables + several indexes worth of statements.
684        assert!(stmts.len() >= 6, "got {} statements", stmts.len());
685        assert!(stmts.iter().any(|s| s.starts_with("CREATE SCHEMA")));
686        assert!(stmts.iter().any(|s| s.contains("auth.users")));
687        assert!(stmts.iter().any(|s| s.contains("auth.sessions")));
688        assert!(stmts.iter().any(|s| s.contains("auth.jwks_keys")));
689    }
690}
assay_auth/schema.rs

assay_auth/
schema.rs
