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

assay_auth/
schema.rs
