Something went wrong. Try again.
AtAuth
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527/** * Database Service * * SQLite database for OAuth state, app sessions, user mappings, * OIDC clients, passkeys, MFA, and email verification */
import Database from 'better-sqlite3';import path from 'path';import type { AppConfig, AppSession, OAuthState, UserMapping, SessionConnectionState, ActiveSession, OIDCClientConfig, OIDCKey, AuthorizationCode, RefreshToken, PasskeyCredential, MFATOTPConfig, MFABackupCode, UserEmail, EmailVerificationCode, ProxySession, ProxyAllowedOrigin, ProxyAuthRequest, ProxyAccessRule,} from '../types/index.js';
export class DatabaseService { private db: Database.Database;
constructor(dbPath?: string) { const defaultPath = path.join(process.cwd(), 'data', 'gateway.db'); this.db = new Database(dbPath || defaultPath); this.initialize(); }
private initialize(): void { // Enable WAL mode for better concurrency this.db.pragma('journal_mode = WAL');
// Create tables this.db.exec(` -- Application configurations (HMAC secrets per app) CREATE TABLE IF NOT EXISTS apps ( id TEXT PRIMARY KEY, name TEXT NOT NULL, hmac_secret TEXT NOT NULL, token_ttl_seconds INTEGER DEFAULT 3600, callback_url TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
-- OAuth state for PKCE flow CREATE TABLE IF NOT EXISTS oauth_states ( state TEXT PRIMARY KEY, code_verifier TEXT NOT NULL, app_id TEXT NOT NULL, redirect_uri TEXT NOT NULL, created_at INTEGER NOT NULL, FOREIGN KEY (app_id) REFERENCES apps(id) );
-- User mappings (DID -> app user_id) CREATE TABLE IF NOT EXISTS user_mappings ( did TEXT NOT NULL, app_id TEXT NOT NULL, user_id INTEGER NOT NULL, handle TEXT, linked_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (did, app_id), FOREIGN KEY (app_id) REFERENCES apps(id) );
-- Active sessions CREATE TABLE IF NOT EXISTS sessions ( id TEXT PRIMARY KEY, did TEXT NOT NULL, handle TEXT NOT NULL, user_id INTEGER, app_id TEXT NOT NULL, refresh_token TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, expires_at DATETIME NOT NULL, connection_state TEXT DEFAULT 'pending', last_activity DATETIME DEFAULT CURRENT_TIMESTAMP, client_info TEXT, FOREIGN KEY (app_id) REFERENCES apps(id) );
-- Indexes CREATE INDEX IF NOT EXISTS idx_oauth_states_created ON oauth_states(created_at); CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at); CREATE INDEX IF NOT EXISTS idx_sessions_did_app ON sessions(did, app_id); `);
// Run migrations for new columns and tables this.runMigrations(); }
private runMigrations(): void { // Check if we need to add OIDC columns to apps table const appColumns = this.db.pragma('table_info(apps)') as Array<{ name: string }>; const columnNames = appColumns.map((c) => c.name);
if (!columnNames.includes('client_type')) { this.db.exec(` -- Add OIDC columns to apps table ALTER TABLE apps ADD COLUMN client_type TEXT DEFAULT 'legacy'; ALTER TABLE apps ADD COLUMN client_secret TEXT; ALTER TABLE apps ADD COLUMN redirect_uris TEXT DEFAULT '[]'; ALTER TABLE apps ADD COLUMN grant_types TEXT DEFAULT '["authorization_code"]'; ALTER TABLE apps ADD COLUMN allowed_scopes TEXT DEFAULT '["openid"]'; ALTER TABLE apps ADD COLUMN id_token_ttl_seconds INTEGER DEFAULT 3600; ALTER TABLE apps ADD COLUMN access_token_ttl_seconds INTEGER DEFAULT 3600; ALTER TABLE apps ADD COLUMN refresh_token_ttl_seconds INTEGER DEFAULT 604800; ALTER TABLE apps ADD COLUMN require_pkce BOOLEAN DEFAULT 1; ALTER TABLE apps ADD COLUMN token_endpoint_auth_method TEXT DEFAULT 'client_secret_basic'; `); }
// Create OIDC signing keys table this.db.exec(` CREATE TABLE IF NOT EXISTS oidc_keys ( kid TEXT PRIMARY KEY, algorithm TEXT NOT NULL, private_key_encrypted TEXT NOT NULL, public_key_jwk TEXT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, expires_at DATETIME, is_active BOOLEAN DEFAULT 1, use_for_signing BOOLEAN DEFAULT 1 ); `);
// Create authorization codes table this.db.exec(` CREATE TABLE IF NOT EXISTS authorization_codes ( code TEXT PRIMARY KEY, client_id TEXT NOT NULL, redirect_uri TEXT NOT NULL, scope TEXT NOT NULL, state TEXT, nonce TEXT, code_challenge TEXT, code_challenge_method TEXT, did TEXT NOT NULL, handle TEXT NOT NULL, user_id INTEGER, created_at INTEGER NOT NULL, expires_at INTEGER NOT NULL, used BOOLEAN DEFAULT 0, FOREIGN KEY (client_id) REFERENCES apps(id) );
CREATE INDEX IF NOT EXISTS idx_auth_codes_expires ON authorization_codes(expires_at); `);
// Add state column if it doesn't exist (migration for existing databases) try { this.db.exec('ALTER TABLE authorization_codes ADD COLUMN state TEXT'); } catch { // Column already exists }
// Create refresh tokens table this.db.exec(` CREATE TABLE IF NOT EXISTS refresh_tokens ( token_hash TEXT PRIMARY KEY, client_id TEXT NOT NULL, did TEXT NOT NULL, handle TEXT NOT NULL, user_id INTEGER, scope TEXT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, expires_at DATETIME NOT NULL, last_used_at DATETIME, revoked BOOLEAN DEFAULT 0, family_id TEXT, FOREIGN KEY (client_id) REFERENCES apps(id) );
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_client ON refresh_tokens(client_id, did); CREATE INDEX IF NOT EXISTS idx_refresh_tokens_family ON refresh_tokens(family_id); `);
// Create passkey credentials table this.db.exec(` CREATE TABLE IF NOT EXISTS passkey_credentials ( id TEXT PRIMARY KEY, did TEXT NOT NULL, handle TEXT NOT NULL, public_key TEXT NOT NULL, counter INTEGER DEFAULT 0, device_type TEXT, backed_up BOOLEAN DEFAULT 0, transports TEXT, name TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, last_used_at DATETIME );
CREATE INDEX IF NOT EXISTS idx_passkey_did ON passkey_credentials(did); `);
// Create MFA TOTP table this.db.exec(` CREATE TABLE IF NOT EXISTS mfa_totp ( did TEXT PRIMARY KEY, secret_encrypted TEXT NOT NULL, enabled BOOLEAN DEFAULT 0, verified_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); `);
// Create MFA backup codes table this.db.exec(` CREATE TABLE IF NOT EXISTS mfa_backup_codes ( id INTEGER PRIMARY KEY AUTOINCREMENT, did TEXT NOT NULL, code_hash TEXT NOT NULL, used BOOLEAN DEFAULT 0, used_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
CREATE INDEX IF NOT EXISTS idx_backup_did ON mfa_backup_codes(did); `);
// Create user emails table this.db.exec(` CREATE TABLE IF NOT EXISTS user_emails ( id INTEGER PRIMARY KEY AUTOINCREMENT, did TEXT NOT NULL, email TEXT NOT NULL, verified BOOLEAN DEFAULT 0, verified_at DATETIME, is_primary BOOLEAN DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE(did, email) );
CREATE INDEX IF NOT EXISTS idx_email_did ON user_emails(did); CREATE INDEX IF NOT EXISTS idx_email_address ON user_emails(email); `);
// Create email verification codes table this.db.exec(` CREATE TABLE IF NOT EXISTS email_verification_codes ( id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL, code_hash TEXT NOT NULL, purpose TEXT NOT NULL, expires_at DATETIME NOT NULL, used BOOLEAN DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
CREATE INDEX IF NOT EXISTS idx_verify_email ON email_verification_codes(email); CREATE INDEX IF NOT EXISTS idx_verify_expires ON email_verification_codes(expires_at); `);
// Forward-auth proxy tables this.db.exec(` CREATE TABLE IF NOT EXISTS proxy_sessions ( id TEXT PRIMARY KEY, did TEXT NOT NULL, handle TEXT NOT NULL, created_at INTEGER NOT NULL, expires_at INTEGER NOT NULL, last_activity INTEGER NOT NULL, user_agent TEXT, ip_address TEXT ); CREATE INDEX IF NOT EXISTS idx_proxy_sessions_expires ON proxy_sessions(expires_at); CREATE INDEX IF NOT EXISTS idx_proxy_sessions_did ON proxy_sessions(did);
CREATE TABLE IF NOT EXISTS proxy_allowed_origins ( id INTEGER PRIMARY KEY AUTOINCREMENT, origin TEXT NOT NULL UNIQUE, name TEXT NOT NULL, created_at INTEGER DEFAULT (unixepoch()) );
CREATE TABLE IF NOT EXISTS proxy_auth_requests ( id TEXT PRIMARY KEY, redirect_uri TEXT NOT NULL, created_at INTEGER NOT NULL, expires_at INTEGER NOT NULL );
CREATE TABLE IF NOT EXISTS proxy_access_rules ( id INTEGER PRIMARY KEY AUTOINCREMENT, origin_id INTEGER, rule_type TEXT NOT NULL CHECK(rule_type IN ('allow', 'deny')), subject_type TEXT NOT NULL CHECK(subject_type IN ('did', 'handle_pattern')), subject_value TEXT NOT NULL, description TEXT, created_at INTEGER DEFAULT (unixepoch()) ); CREATE INDEX IF NOT EXISTS idx_proxy_access_rules_origin ON proxy_access_rules(origin_id); `);
// Ensure sentinel proxy-auth app exists for forward-auth OAuth flows const proxyApp = this.db.prepare('SELECT 1 FROM apps WHERE id = ?').get('proxy-auth'); if (!proxyApp) { this.db.prepare( `INSERT INTO apps (id, name, hmac_secret, callback_url, client_type) VALUES (?, ?, ?, ?, ?)` ).run('proxy-auth', 'Forward-Auth Proxy', 'internal-not-used', '', 'internal'); } }
// App configuration methods getApp(appId: string): AppConfig | null { const stmt = this.db.prepare('SELECT * FROM apps WHERE id = ?'); const row = stmt.get(appId) as AppConfig | undefined; return row || null; }
upsertApp(app: AppConfig): void { const stmt = this.db.prepare(` INSERT INTO apps (id, name, hmac_secret, token_ttl_seconds, callback_url) VALUES (?, ?, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET name = excluded.name, hmac_secret = excluded.hmac_secret, token_ttl_seconds = excluded.token_ttl_seconds, callback_url = excluded.callback_url `); stmt.run(app.id, app.name, app.hmac_secret, app.token_ttl_seconds, app.callback_url); }
// OAuth state methods saveOAuthState(state: OAuthState): void { const stmt = this.db.prepare(` INSERT OR REPLACE INTO oauth_states (state, code_verifier, app_id, redirect_uri, created_at) VALUES (?, ?, ?, ?, ?) `); stmt.run(state.state, state.code_verifier, state.app_id, state.redirect_uri, state.created_at); }
getOAuthState(state: string): OAuthState | null { const stmt = this.db.prepare('SELECT * FROM oauth_states WHERE state = ?'); const row = stmt.get(state) as OAuthState | undefined; return row || null; }
deleteOAuthState(state: string): void { const stmt = this.db.prepare('DELETE FROM oauth_states WHERE state = ?'); stmt.run(state); }
cleanupOldOAuthStates(): number { const tenMinutesAgo = Math.floor(Date.now() / 1000) - 600; const stmt = this.db.prepare('DELETE FROM oauth_states WHERE created_at < ?'); const result = stmt.run(tenMinutesAgo); return result.changes; }
// User mapping methods getUserMapping(did: string, appId: string): UserMapping | null { const stmt = this.db.prepare( 'SELECT * FROM user_mappings WHERE did = ? AND app_id = ?' ); const row = stmt.get(did, appId) as UserMapping | undefined; return row || null; }
setUserMapping(mapping: Omit<UserMapping, 'linked_at'>): void { const stmt = this.db.prepare(` INSERT INTO user_mappings (did, app_id, user_id, handle) VALUES (?, ?, ?, ?) ON CONFLICT(did, app_id) DO UPDATE SET user_id = excluded.user_id, handle = excluded.handle `); stmt.run(mapping.did, mapping.app_id, mapping.user_id, mapping.handle); }
// Session methods createSession(session: Omit<AppSession, 'created_at'>): void { const stmt = this.db.prepare(` INSERT INTO sessions (id, did, handle, user_id, app_id, refresh_token, expires_at) VALUES (?, ?, ?, ?, ?, ?, ?) `); stmt.run( session.id, session.did, session.handle, session.user_id, session.app_id, session.refresh_token, session.expires_at.toISOString() ); }
getSession(sessionId: string): AppSession | null { const stmt = this.db.prepare('SELECT * FROM sessions WHERE id = ?'); const row = stmt.get(sessionId) as (Omit<AppSession, 'created_at' | 'expires_at'> & { created_at: string; expires_at: string; }) | undefined;
if (!row) return null;
return { ...row, created_at: new Date(row.created_at), expires_at: new Date(row.expires_at), }; }
deleteSession(sessionId: string): void { const stmt = this.db.prepare('DELETE FROM sessions WHERE id = ?'); stmt.run(sessionId); }
cleanupExpiredSessions(): number { const stmt = this.db.prepare("DELETE FROM sessions WHERE expires_at < datetime('now')"); const result = stmt.run(); return result.changes; }
// Session conflict detection methods getActiveSessionsByDid(did: string, appId: string): ActiveSession[] { const stmt = this.db.prepare(` SELECT * FROM sessions WHERE did = ? AND app_id = ? AND expires_at > datetime('now') ORDER BY created_at DESC `); const rows = stmt.all(did, appId) as Array<{ id: string; did: string; handle: string; user_id: number | null; app_id: string; refresh_token?: string; created_at: string; expires_at: string; connection_state: SessionConnectionState; last_activity: string; client_info?: string; }>;
return rows.map((row) => ({ ...row, created_at: new Date(row.created_at), expires_at: new Date(row.expires_at), last_activity: new Date(row.last_activity), connection_state: row.connection_state || 'pending', })); }
updateSessionConnectionState( sessionId: string, state: SessionConnectionState, clientInfo?: string ): void { const stmt = this.db.prepare(` UPDATE sessions SET connection_state = ?, last_activity = datetime('now'), client_info = COALESCE(?, client_info) WHERE id = ? `); stmt.run(state, clientInfo, sessionId); }
updateSessionActivity(sessionId: string): void { const stmt = this.db.prepare(` UPDATE sessions SET last_activity = datetime('now') WHERE id = ? `); stmt.run(sessionId); }
deleteOtherSessions(keepSessionId: string, did: string, appId: string): number { const stmt = this.db.prepare(` DELETE FROM sessions WHERE did = ? AND app_id = ? AND id != ? `); const result = stmt.run(did, appId, keepSessionId); return result.changes; }
// Admin methods for listing and managing apps getAllApps(): Omit<AppConfig, 'hmac_secret'>[] { const stmt = this.db.prepare( 'SELECT id, name, token_ttl_seconds, callback_url, created_at FROM apps ORDER BY created_at DESC' ); return stmt.all() as Omit<AppConfig, 'hmac_secret'>[]; }
deleteApp(appId: string): void { // Delete related data first (order respects foreign keys) this.db.prepare('DELETE FROM sessions WHERE app_id = ?').run(appId); this.db.prepare('DELETE FROM oauth_states WHERE app_id = ?').run(appId); this.db.prepare('DELETE FROM user_mappings WHERE app_id = ?').run(appId); this.db.prepare('DELETE FROM authorization_codes WHERE client_id = ?').run(appId); this.db.prepare('DELETE FROM refresh_tokens WHERE client_id = ?').run(appId); // Delete the app this.db.prepare('DELETE FROM apps WHERE id = ?').run(appId); }
getStats(): { apps_count: number; oidc_clients_count: number; active_sessions_count: number; users_count: number; passkeys_count: number; mfa_enabled_count: number; verified_emails_count: number; } { const appsCount = (this.db.prepare('SELECT COUNT(*) as count FROM apps').get() as { count: number }).count; const oidcClientsCount = (this.db.prepare("SELECT COUNT(*) as count FROM apps WHERE client_type = 'oidc'").get() as { count: number }).count; const activeSessionsCount = (this.db.prepare("SELECT COUNT(*) as count FROM sessions WHERE expires_at > datetime('now')").get() as { count: number }).count; const usersCount = (this.db.prepare('SELECT COUNT(DISTINCT did) as count FROM user_mappings').get() as { count: number }).count; const passkeysCount = (this.db.prepare('SELECT COUNT(*) as count FROM passkey_credentials').get() as { count: number }).count; const mfaEnabledCount = (this.db.prepare('SELECT COUNT(*) as count FROM mfa_totp WHERE enabled = 1').get() as { count: number }).count; const verifiedEmailsCount = (this.db.prepare('SELECT COUNT(*) as count FROM user_emails WHERE verified = 1').get() as { count: number }).count;
return { apps_count: appsCount, oidc_clients_count: oidcClientsCount, active_sessions_count: activeSessionsCount, users_count: usersCount, passkeys_count: passkeysCount, mfa_enabled_count: mfaEnabledCount, verified_emails_count: verifiedEmailsCount, }; }
close(): void { this.db.close(); }
// ===== OIDC Key Management Methods =====
saveOIDCKey(key: Omit<OIDCKey, 'created_at'>): void { const stmt = this.db.prepare(` INSERT INTO oidc_keys (kid, algorithm, private_key_encrypted, public_key_jwk, is_active, use_for_signing) VALUES (?, ?, ?, ?, ?, ?) `); stmt.run(key.kid, key.algorithm, key.private_key_encrypted, key.public_key_jwk, key.is_active ? 1 : 0, key.use_for_signing ? 1 : 0); }
getOIDCKey(kid: string): OIDCKey | null { const stmt = this.db.prepare('SELECT * FROM oidc_keys WHERE kid = ?'); const row = stmt.get(kid) as { kid: string; algorithm: 'ES256' | 'RS256'; private_key_encrypted: string; public_key_jwk: string; created_at: string; expires_at: string | null; is_active: number; use_for_signing: number; } | undefined; if (!row) return null; return { kid: row.kid, algorithm: row.algorithm, private_key_encrypted: row.private_key_encrypted, public_key_jwk: row.public_key_jwk, created_at: new Date(row.created_at), expires_at: row.expires_at ? new Date(row.expires_at) : undefined, is_active: Boolean(row.is_active), use_for_signing: Boolean(row.use_for_signing), }; }
getActiveOIDCKeys(): OIDCKey[] { const stmt = this.db.prepare('SELECT * FROM oidc_keys WHERE is_active = 1 ORDER BY created_at DESC'); const rows = stmt.all() as Array<{ kid: string; algorithm: 'ES256' | 'RS256'; private_key_encrypted: string; public_key_jwk: string; created_at: string; expires_at: string | null; is_active: number; use_for_signing: number; }>; return rows.map(row => ({ kid: row.kid, algorithm: row.algorithm, private_key_encrypted: row.private_key_encrypted, public_key_jwk: row.public_key_jwk, created_at: new Date(row.created_at), expires_at: row.expires_at ? new Date(row.expires_at) : undefined, is_active: Boolean(row.is_active), use_for_signing: Boolean(row.use_for_signing), })); }
getCurrentSigningKey(): OIDCKey | null { const stmt = this.db.prepare('SELECT * FROM oidc_keys WHERE is_active = 1 AND use_for_signing = 1 ORDER BY created_at DESC LIMIT 1'); const row = stmt.get() as { kid: string; algorithm: 'ES256' | 'RS256'; private_key_encrypted: string; public_key_jwk: string; created_at: string; expires_at: string | null; is_active: number; use_for_signing: number; } | undefined; if (!row) return null; return { kid: row.kid, algorithm: row.algorithm, private_key_encrypted: row.private_key_encrypted, public_key_jwk: row.public_key_jwk, created_at: new Date(row.created_at), expires_at: row.expires_at ? new Date(row.expires_at) : undefined, is_active: Boolean(row.is_active), use_for_signing: Boolean(row.use_for_signing), }; }
markKeyAsNotSigning(kid: string): void { const stmt = this.db.prepare('UPDATE oidc_keys SET use_for_signing = 0 WHERE kid = ?'); stmt.run(kid); }
deactivateKey(kid: string): void { const stmt = this.db.prepare('UPDATE oidc_keys SET is_active = 0, use_for_signing = 0 WHERE kid = ?'); stmt.run(kid); }
deleteOIDCKey(kid: string): void { const stmt = this.db.prepare('DELETE FROM oidc_keys WHERE kid = ?'); stmt.run(kid); }
// ===== Authorization Code Methods =====
saveAuthorizationCode(code: AuthorizationCode): void { const stmt = this.db.prepare(` INSERT INTO authorization_codes (code, client_id, redirect_uri, scope, state, nonce, code_challenge, code_challenge_method, did, handle, user_id, created_at, expires_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) `); stmt.run( code.code, code.client_id, code.redirect_uri, code.scope, code.state, code.nonce, code.code_challenge, code.code_challenge_method, code.did, code.handle, code.user_id, code.created_at, code.expires_at ); }
getAuthorizationCode(code: string): AuthorizationCode | null { const stmt = this.db.prepare('SELECT * FROM authorization_codes WHERE code = ?'); const row = stmt.get(code) as { code: string; client_id: string; redirect_uri: string; scope: string; state: string | null; nonce: string | null; code_challenge: string | null; code_challenge_method: 'S256' | 'plain' | null; did: string; handle: string; user_id: number | null; created_at: number; expires_at: number; used: number; } | undefined; if (!row) return null; return { code: row.code, client_id: row.client_id, redirect_uri: row.redirect_uri, scope: row.scope, state: row.state ?? undefined, nonce: row.nonce ?? undefined, code_challenge: row.code_challenge ?? undefined, code_challenge_method: row.code_challenge_method ?? undefined, did: row.did, handle: row.handle, user_id: row.user_id ?? undefined, created_at: row.created_at, expires_at: row.expires_at, used: Boolean(row.used), }; }
markAuthorizationCodeUsed(code: string): void { const stmt = this.db.prepare('UPDATE authorization_codes SET used = 1 WHERE code = ?'); stmt.run(code); }
updateAuthorizationCodeUser(code: string, did: string, handle: string): void { const stmt = this.db.prepare('UPDATE authorization_codes SET did = ?, handle = ? WHERE code = ?'); stmt.run(did, handle, code); }
cleanupExpiredAuthorizationCodes(): number { const now = Math.floor(Date.now() / 1000); const stmt = this.db.prepare('DELETE FROM authorization_codes WHERE expires_at < ?'); const result = stmt.run(now); return result.changes; }
// ===== Refresh Token Methods =====
saveRefreshToken(token: Omit<RefreshToken, 'created_at' | 'last_used_at'>): void { const stmt = this.db.prepare(` INSERT INTO refresh_tokens (token_hash, client_id, did, handle, user_id, scope, expires_at, family_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?) `); stmt.run( token.token_hash, token.client_id, token.did, token.handle, token.user_id, token.scope, token.expires_at.toISOString(), token.family_id ); }
getRefreshToken(tokenHash: string): RefreshToken | null { const stmt = this.db.prepare('SELECT * FROM refresh_tokens WHERE token_hash = ?'); const row = stmt.get(tokenHash) as { token_hash: string; client_id: string; did: string; handle: string; user_id: number | null; scope: string; created_at: string; expires_at: string; last_used_at: string | null; revoked: number; family_id: string | null; } | undefined; if (!row) return null; return { token_hash: row.token_hash, client_id: row.client_id, did: row.did, handle: row.handle, user_id: row.user_id ?? undefined, scope: row.scope, created_at: new Date(row.created_at), expires_at: new Date(row.expires_at), last_used_at: row.last_used_at ? new Date(row.last_used_at) : undefined, revoked: Boolean(row.revoked), family_id: row.family_id ?? undefined, }; }
updateRefreshTokenLastUsed(tokenHash: string): void { const stmt = this.db.prepare("UPDATE refresh_tokens SET last_used_at = datetime('now') WHERE token_hash = ?"); stmt.run(tokenHash); }
revokeRefreshToken(tokenHash: string): void { const stmt = this.db.prepare('UPDATE refresh_tokens SET revoked = 1 WHERE token_hash = ?'); stmt.run(tokenHash); }
revokeRefreshTokenFamily(familyId: string): void { const stmt = this.db.prepare('UPDATE refresh_tokens SET revoked = 1 WHERE family_id = ?'); stmt.run(familyId); }
revokeAllRefreshTokensForUser(did: string, clientId: string): number { const stmt = this.db.prepare('UPDATE refresh_tokens SET revoked = 1 WHERE did = ? AND client_id = ?'); const result = stmt.run(did, clientId); return result.changes; }
cleanupExpiredRefreshTokens(): number { const stmt = this.db.prepare("DELETE FROM refresh_tokens WHERE expires_at < datetime('now')"); const result = stmt.run(); return result.changes; }
// ===== Passkey Credential Methods =====
savePasskeyCredential(credential: Omit<PasskeyCredential, 'created_at' | 'last_used_at'>): void { const stmt = this.db.prepare(` INSERT INTO passkey_credentials (id, did, handle, public_key, counter, device_type, backed_up, transports, name) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) `); stmt.run( credential.id, credential.did, credential.handle, credential.public_key, credential.counter, credential.device_type, credential.backed_up ? 1 : 0, credential.transports ? JSON.stringify(credential.transports) : null, credential.name ); }
getPasskeyCredential(credentialId: string): PasskeyCredential | null { const stmt = this.db.prepare('SELECT * FROM passkey_credentials WHERE id = ?'); const row = stmt.get(credentialId) as { id: string; did: string; handle: string; public_key: string; counter: number; device_type: 'platform' | 'cross-platform' | null; backed_up: number; transports: string | null; name: string | null; created_at: string; last_used_at: string | null; } | undefined; if (!row) return null; return { id: row.id, did: row.did, handle: row.handle, public_key: row.public_key, counter: row.counter, device_type: row.device_type, backed_up: Boolean(row.backed_up), transports: row.transports ? JSON.parse(row.transports) : null, name: row.name, created_at: new Date(row.created_at), last_used_at: row.last_used_at ? new Date(row.last_used_at) : null, }; }
getPasskeyCredentialsByDid(did: string): PasskeyCredential[] { const stmt = this.db.prepare('SELECT * FROM passkey_credentials WHERE did = ? ORDER BY created_at DESC'); const rows = stmt.all(did) as Array<{ id: string; did: string; handle: string; public_key: string; counter: number; device_type: 'platform' | 'cross-platform' | null; backed_up: number; transports: string | null; name: string | null; created_at: string; last_used_at: string | null; }>; return rows.map(row => ({ id: row.id, did: row.did, handle: row.handle, public_key: row.public_key, counter: row.counter, device_type: row.device_type, backed_up: Boolean(row.backed_up), transports: row.transports ? JSON.parse(row.transports) : null, name: row.name, created_at: new Date(row.created_at), last_used_at: row.last_used_at ? new Date(row.last_used_at) : null, })); }
updatePasskeyCounter(credentialId: string, newCounter: number): void { const stmt = this.db.prepare("UPDATE passkey_credentials SET counter = ?, last_used_at = datetime('now') WHERE id = ?"); stmt.run(newCounter, credentialId); }
renamePasskey(credentialId: string, name: string): void { const stmt = this.db.prepare('UPDATE passkey_credentials SET name = ? WHERE id = ?'); stmt.run(name, credentialId); }
deletePasskeyCredential(credentialId: string): void { const stmt = this.db.prepare('DELETE FROM passkey_credentials WHERE id = ?'); stmt.run(credentialId); }
countPasskeysByDid(did: string): number { const stmt = this.db.prepare('SELECT COUNT(*) as count FROM passkey_credentials WHERE did = ?'); const row = stmt.get(did) as { count: number }; return row.count; }
// ===== MFA TOTP Methods =====
saveMFATOTP(config: Omit<MFATOTPConfig, 'created_at' | 'verified_at'>): void { const stmt = this.db.prepare(` INSERT OR REPLACE INTO mfa_totp (did, secret_encrypted, enabled) VALUES (?, ?, ?) `); stmt.run(config.did, config.secret_encrypted, config.enabled ? 1 : 0); }
getMFATOTP(did: string): MFATOTPConfig | null { const stmt = this.db.prepare('SELECT * FROM mfa_totp WHERE did = ?'); const row = stmt.get(did) as { did: string; secret_encrypted: string; enabled: number; verified_at: string | null; created_at: string; } | undefined; if (!row) return null; return { did: row.did, secret_encrypted: row.secret_encrypted, enabled: Boolean(row.enabled), verified_at: row.verified_at ? new Date(row.verified_at) : null, created_at: new Date(row.created_at), }; }
enableMFATOTP(did: string): void { const stmt = this.db.prepare("UPDATE mfa_totp SET enabled = 1, verified_at = datetime('now') WHERE did = ?"); stmt.run(did); }
disableMFATOTP(did: string): void { const stmt = this.db.prepare('DELETE FROM mfa_totp WHERE did = ?'); stmt.run(did); }
// ===== MFA Backup Codes Methods =====
saveBackupCodes(did: string, codeHashes: string[]): void { // Delete existing codes first this.db.prepare('DELETE FROM mfa_backup_codes WHERE did = ?').run(did);
const stmt = this.db.prepare(` INSERT INTO mfa_backup_codes (did, code_hash) VALUES (?, ?) `);
const insertMany = this.db.transaction((codes: string[]) => { for (const codeHash of codes) { stmt.run(did, codeHash); } });
insertMany(codeHashes); }
getUnusedBackupCode(did: string, codeHash: string): MFABackupCode | null { const stmt = this.db.prepare('SELECT * FROM mfa_backup_codes WHERE did = ? AND code_hash = ? AND used = 0'); const row = stmt.get(did, codeHash) as { id: number; did: string; code_hash: string; used: number; used_at: string | null; created_at: string; } | undefined; if (!row) return null; return { id: row.id, did: row.did, code_hash: row.code_hash, used: Boolean(row.used), used_at: row.used_at ? new Date(row.used_at) : null, created_at: new Date(row.created_at), }; }
markBackupCodeUsed(id: number): void { const stmt = this.db.prepare("UPDATE mfa_backup_codes SET used = 1, used_at = datetime('now') WHERE id = ?"); stmt.run(id); }
countUnusedBackupCodes(did: string): number { const stmt = this.db.prepare('SELECT COUNT(*) as count FROM mfa_backup_codes WHERE did = ? AND used = 0'); const row = stmt.get(did) as { count: number }; return row.count; }
// ===== User Email Methods =====
saveUserEmail(email: Omit<UserEmail, 'id' | 'created_at' | 'verified_at'>): void { const stmt = this.db.prepare(` INSERT INTO user_emails (did, email, verified, is_primary) VALUES (?, ?, ?, ?) ON CONFLICT(did, email) DO UPDATE SET is_primary = excluded.is_primary `); stmt.run(email.did, email.email, email.verified ? 1 : 0, email.is_primary ? 1 : 0); }
getUserEmails(did: string): UserEmail[] { const stmt = this.db.prepare('SELECT * FROM user_emails WHERE did = ? ORDER BY is_primary DESC, created_at ASC'); const rows = stmt.all(did) as Array<{ id: number; did: string; email: string; verified: number; verified_at: string | null; is_primary: number; created_at: string; }>; return rows.map(row => ({ id: row.id, did: row.did, email: row.email, verified: Boolean(row.verified), verified_at: row.verified_at ? new Date(row.verified_at) : null, is_primary: Boolean(row.is_primary), created_at: new Date(row.created_at), })); }
getUserByEmail(email: string): { did: string; handle?: string } | null { const stmt = this.db.prepare(` SELECT ue.did, um.handle FROM user_emails ue LEFT JOIN user_mappings um ON ue.did = um.did WHERE ue.email = ? AND ue.verified = 1 LIMIT 1 `); const row = stmt.get(email) as { did: string; handle?: string } | undefined; return row || null; }
verifyUserEmail(did: string, email: string): void { const stmt = this.db.prepare("UPDATE user_emails SET verified = 1, verified_at = datetime('now') WHERE did = ? AND email = ?"); stmt.run(did, email); }
deleteUserEmail(did: string, email: string): void { const stmt = this.db.prepare('DELETE FROM user_emails WHERE did = ? AND email = ?'); stmt.run(did, email); }
setPrimaryEmail(did: string, email: string): void { // Unset all as non-primary first this.db.prepare('UPDATE user_emails SET is_primary = 0 WHERE did = ?').run(did); // Set the specified email as primary this.db.prepare('UPDATE user_emails SET is_primary = 1 WHERE did = ? AND email = ?').run(did, email); }
// ===== Email Verification Code Methods =====
saveEmailVerificationCode(code: Omit<EmailVerificationCode, 'id' | 'created_at'>): void { const stmt = this.db.prepare(` INSERT INTO email_verification_codes (email, code_hash, purpose, expires_at) VALUES (?, ?, ?, ?) `); stmt.run(code.email, code.code_hash, code.purpose, code.expires_at.toISOString()); }
getValidEmailVerificationCode(email: string, codeHash: string, purpose: string): EmailVerificationCode | null { const stmt = this.db.prepare(` SELECT * FROM email_verification_codes WHERE email = ? AND code_hash = ? AND purpose = ? AND used = 0 AND expires_at > datetime('now') `); const row = stmt.get(email, codeHash, purpose) as { id: number; email: string; code_hash: string; purpose: 'verify' | 'recovery'; expires_at: string; used: number; created_at: string; } | undefined; if (!row) return null; return { id: row.id, email: row.email, code_hash: row.code_hash, purpose: row.purpose, expires_at: new Date(row.expires_at), used: Boolean(row.used), created_at: new Date(row.created_at), }; }
markEmailVerificationCodeUsed(id: number): void { const stmt = this.db.prepare('UPDATE email_verification_codes SET used = 1 WHERE id = ?'); stmt.run(id); }
cleanupExpiredEmailVerificationCodes(): number { const stmt = this.db.prepare("DELETE FROM email_verification_codes WHERE expires_at < datetime('now')"); const result = stmt.run(); return result.changes; }
// ===== OIDC Client Methods =====
getOIDCClient(clientId: string): OIDCClientConfig | null { const stmt = this.db.prepare('SELECT * FROM apps WHERE id = ?'); const row = stmt.get(clientId) as { id: string; name: string; client_type: 'legacy' | 'oidc'; hmac_secret?: string; client_secret?: string; redirect_uris: string; grant_types: string; allowed_scopes: string; token_ttl_seconds: number; id_token_ttl_seconds: number; access_token_ttl_seconds: number; refresh_token_ttl_seconds: number; require_pkce: number; token_endpoint_auth_method: string; created_at: string; } | undefined; if (!row) return null; return { id: row.id, name: row.name, client_type: row.client_type, hmac_secret: row.hmac_secret, client_secret: row.client_secret, redirect_uris: JSON.parse(row.redirect_uris || '[]'), grant_types: JSON.parse(row.grant_types || '["authorization_code"]'), allowed_scopes: JSON.parse(row.allowed_scopes || '["openid"]'), token_ttl_seconds: row.token_ttl_seconds, id_token_ttl_seconds: row.id_token_ttl_seconds, access_token_ttl_seconds: row.access_token_ttl_seconds, refresh_token_ttl_seconds: row.refresh_token_ttl_seconds, require_pkce: Boolean(row.require_pkce), token_endpoint_auth_method: row.token_endpoint_auth_method as 'client_secret_basic' | 'client_secret_post' | 'none', created_at: new Date(row.created_at), }; }
upsertOIDCClient(client: Omit<OIDCClientConfig, 'created_at'>): void { const stmt = this.db.prepare(` INSERT INTO apps (id, name, client_type, hmac_secret, client_secret, redirect_uris, grant_types, allowed_scopes, token_ttl_seconds, id_token_ttl_seconds, access_token_ttl_seconds, refresh_token_ttl_seconds, require_pkce, token_endpoint_auth_method, callback_url) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET name = excluded.name, client_type = excluded.client_type, hmac_secret = COALESCE(excluded.hmac_secret, hmac_secret), client_secret = COALESCE(excluded.client_secret, client_secret), redirect_uris = excluded.redirect_uris, grant_types = excluded.grant_types, allowed_scopes = excluded.allowed_scopes, token_ttl_seconds = excluded.token_ttl_seconds, id_token_ttl_seconds = excluded.id_token_ttl_seconds, access_token_ttl_seconds = excluded.access_token_ttl_seconds, refresh_token_ttl_seconds = excluded.refresh_token_ttl_seconds, require_pkce = excluded.require_pkce, token_endpoint_auth_method = excluded.token_endpoint_auth_method, callback_url = excluded.callback_url `); stmt.run( client.id, client.name, client.client_type, client.hmac_secret, client.client_secret, JSON.stringify(client.redirect_uris), JSON.stringify(client.grant_types), JSON.stringify(client.allowed_scopes), client.token_ttl_seconds, client.id_token_ttl_seconds, client.access_token_ttl_seconds, client.refresh_token_ttl_seconds, client.require_pkce ? 1 : 0, client.token_endpoint_auth_method, client.redirect_uris[0] || null ); }
getAllOIDCClients(): OIDCClientConfig[] { const stmt = this.db.prepare("SELECT * FROM apps WHERE client_type = 'oidc' ORDER BY created_at DESC"); const rows = stmt.all() as Array<{ id: string; name: string; client_type: 'legacy' | 'oidc'; hmac_secret?: string; client_secret?: string; redirect_uris: string; grant_types: string; allowed_scopes: string; token_ttl_seconds: number; id_token_ttl_seconds: number; access_token_ttl_seconds: number; refresh_token_ttl_seconds: number; require_pkce: number; token_endpoint_auth_method: string; created_at: string; }>; return rows.map(row => ({ id: row.id, name: row.name, client_type: row.client_type, hmac_secret: row.hmac_secret, client_secret: row.client_secret, redirect_uris: JSON.parse(row.redirect_uris || '[]'), grant_types: JSON.parse(row.grant_types || '["authorization_code"]'), allowed_scopes: JSON.parse(row.allowed_scopes || '["openid"]'), token_ttl_seconds: row.token_ttl_seconds, id_token_ttl_seconds: row.id_token_ttl_seconds, access_token_ttl_seconds: row.access_token_ttl_seconds, refresh_token_ttl_seconds: row.refresh_token_ttl_seconds, require_pkce: Boolean(row.require_pkce), token_endpoint_auth_method: row.token_endpoint_auth_method as 'client_secret_basic' | 'client_secret_post' | 'none', created_at: new Date(row.created_at), })); }
updateOIDCClient(clientId: string, updates: { client_type?: 'oidc'; client_secret?: string; redirect_uris?: string[]; grant_types?: string[]; allowed_scopes?: string[]; require_pkce?: boolean; token_endpoint_auth_method?: string; id_token_ttl_seconds?: number; access_token_ttl_seconds?: number; refresh_token_ttl_seconds?: number; }): void { const sets: string[] = []; const values: unknown[] = [];
if (updates.client_type !== undefined) { sets.push('client_type = ?'); values.push(updates.client_type); } if (updates.client_secret !== undefined) { sets.push('client_secret = ?'); values.push(updates.client_secret); } if (updates.redirect_uris !== undefined) { sets.push('redirect_uris = ?'); values.push(JSON.stringify(updates.redirect_uris)); } if (updates.grant_types !== undefined) { sets.push('grant_types = ?'); values.push(JSON.stringify(updates.grant_types)); } if (updates.allowed_scopes !== undefined) { sets.push('allowed_scopes = ?'); values.push(JSON.stringify(updates.allowed_scopes)); } if (updates.require_pkce !== undefined) { sets.push('require_pkce = ?'); values.push(updates.require_pkce ? 1 : 0); } if (updates.token_endpoint_auth_method !== undefined) { sets.push('token_endpoint_auth_method = ?'); values.push(updates.token_endpoint_auth_method); } if (updates.id_token_ttl_seconds !== undefined) { sets.push('id_token_ttl_seconds = ?'); values.push(updates.id_token_ttl_seconds); } if (updates.access_token_ttl_seconds !== undefined) { sets.push('access_token_ttl_seconds = ?'); values.push(updates.access_token_ttl_seconds); sets.push('token_ttl_seconds = ?'); values.push(updates.access_token_ttl_seconds); } if (updates.refresh_token_ttl_seconds !== undefined) { sets.push('refresh_token_ttl_seconds = ?'); values.push(updates.refresh_token_ttl_seconds); }
if (sets.length > 0) { values.push(clientId); const stmt = this.db.prepare(`UPDATE apps SET ${sets.join(', ')} WHERE id = ?`); stmt.run(...values); } }
updateOIDCClientSecret(clientId: string, secretHash: string): void { const stmt = this.db.prepare('UPDATE apps SET client_secret = ? WHERE id = ?'); stmt.run(secretHash, clientId); }
// ===== Session Admin Methods =====
getAllActiveSessions(appId?: string, did?: string, limit = 100): ActiveSession[] { let sql = "SELECT * FROM sessions WHERE expires_at > datetime('now')"; const params: unknown[] = [];
if (appId) { sql += ' AND app_id = ?'; params.push(appId); } if (did) { sql += ' AND did = ?'; params.push(did); }
sql += ' ORDER BY created_at DESC LIMIT ?'; params.push(limit);
const stmt = this.db.prepare(sql); const rows = stmt.all(...params) as Array<{ id: string; did: string; handle: string; user_id: number | null; app_id: string; refresh_token?: string; created_at: string; expires_at: string; connection_state: SessionConnectionState; last_activity: string; client_info?: string; }>;
return rows.map((row) => ({ ...row, created_at: new Date(row.created_at), expires_at: new Date(row.expires_at), last_activity: new Date(row.last_activity), connection_state: row.connection_state || 'pending', })); }
revokeAllSessionsForUser(did: string, appId?: string): number { let sql = 'DELETE FROM sessions WHERE did = ?'; const params: unknown[] = [did];
if (appId) { sql += ' AND app_id = ?'; params.push(appId); }
const stmt = this.db.prepare(sql); const result = stmt.run(...params); return result.changes; }
// ===== Forward-Auth Proxy Session Methods =====
createProxySession(session: ProxySession): void { const stmt = this.db.prepare(` INSERT INTO proxy_sessions (id, did, handle, created_at, expires_at, last_activity, user_agent, ip_address) VALUES (?, ?, ?, ?, ?, ?, ?, ?) `); stmt.run( session.id, session.did, session.handle, session.created_at, session.expires_at, session.last_activity, session.user_agent, session.ip_address, ); }
getProxySession(id: string): ProxySession | null { const stmt = this.db.prepare('SELECT * FROM proxy_sessions WHERE id = ?'); const row = stmt.get(id) as ProxySession | undefined; return row || null; }
updateProxySessionActivity(id: string): void { const now = Math.floor(Date.now() / 1000); const stmt = this.db.prepare('UPDATE proxy_sessions SET last_activity = ? WHERE id = ?'); stmt.run(now, id); }
deleteProxySession(id: string): void { const stmt = this.db.prepare('DELETE FROM proxy_sessions WHERE id = ?'); stmt.run(id); }
deleteProxySessionsForUser(did: string): number { const stmt = this.db.prepare('DELETE FROM proxy_sessions WHERE did = ?'); const result = stmt.run(did); return result.changes; }
cleanupExpiredProxySessions(): number { const now = Math.floor(Date.now() / 1000); const stmt = this.db.prepare('DELETE FROM proxy_sessions WHERE expires_at < ?'); const result = stmt.run(now); return result.changes; }
getAllProxySessions(did?: string, limit = 100): ProxySession[] { let sql = 'SELECT * FROM proxy_sessions WHERE expires_at > ?'; const now = Math.floor(Date.now() / 1000); const params: unknown[] = [now];
if (did) { sql += ' AND did = ?'; params.push(did); }
sql += ' ORDER BY last_activity DESC LIMIT ?'; params.push(limit);
const stmt = this.db.prepare(sql); return stmt.all(...params) as ProxySession[]; }
// ===== Forward-Auth Allowed Origins Methods =====
addProxyAllowedOrigin(origin: string, name: string): ProxyAllowedOrigin { const stmt = this.db.prepare(` INSERT INTO proxy_allowed_origins (origin, name) VALUES (?, ?) `); const result = stmt.run(origin, name); return { id: result.lastInsertRowid as number, origin, name, created_at: Math.floor(Date.now() / 1000), }; }
removeProxyAllowedOrigin(id: number): void { this.db.prepare('DELETE FROM proxy_access_rules WHERE origin_id = ?').run(id); this.db.prepare('DELETE FROM proxy_allowed_origins WHERE id = ?').run(id); }
listProxyAllowedOrigins(): ProxyAllowedOrigin[] { const stmt = this.db.prepare('SELECT * FROM proxy_allowed_origins ORDER BY name ASC'); return stmt.all() as ProxyAllowedOrigin[]; }
isProxyOriginAllowed(origin: string): boolean { const stmt = this.db.prepare('SELECT 1 FROM proxy_allowed_origins WHERE origin = ? LIMIT 1'); return !!stmt.get(origin); }
// ===== Forward-Auth Auth Request Methods =====
saveProxyAuthRequest(request: ProxyAuthRequest): void { const stmt = this.db.prepare(` INSERT INTO proxy_auth_requests (id, redirect_uri, created_at, expires_at) VALUES (?, ?, ?, ?) `); stmt.run(request.id, request.redirect_uri, request.created_at, request.expires_at); }
getProxyAuthRequest(id: string): ProxyAuthRequest | null { const stmt = this.db.prepare('SELECT * FROM proxy_auth_requests WHERE id = ?'); const row = stmt.get(id) as ProxyAuthRequest | undefined; return row || null; }
deleteProxyAuthRequest(id: string): void { const stmt = this.db.prepare('DELETE FROM proxy_auth_requests WHERE id = ?'); stmt.run(id); }
cleanupExpiredProxyAuthRequests(): number { const now = Math.floor(Date.now() / 1000); const stmt = this.db.prepare('DELETE FROM proxy_auth_requests WHERE expires_at < ?'); const result = stmt.run(now); return result.changes; }
// ===== Forward-Auth Access Rules Methods =====
createProxyAccessRule(rule: Omit<ProxyAccessRule, 'id' | 'created_at'>): ProxyAccessRule { const stmt = this.db.prepare(` INSERT INTO proxy_access_rules (origin_id, rule_type, subject_type, subject_value, description) VALUES (?, ?, ?, ?, ?) `); const result = stmt.run( rule.origin_id, rule.rule_type, rule.subject_type, rule.subject_value, rule.description, ); return { id: result.lastInsertRowid as number, origin_id: rule.origin_id, rule_type: rule.rule_type, subject_type: rule.subject_type, subject_value: rule.subject_value, description: rule.description, created_at: Math.floor(Date.now() / 1000), }; }
deleteProxyAccessRule(id: number): void { this.db.prepare('DELETE FROM proxy_access_rules WHERE id = ?').run(id); }
listProxyAccessRules(originId?: number): ProxyAccessRule[] { if (originId !== undefined) { const stmt = this.db.prepare( 'SELECT * FROM proxy_access_rules WHERE origin_id = ? OR origin_id IS NULL ORDER BY rule_type ASC, created_at ASC', ); return stmt.all(originId) as ProxyAccessRule[]; } const stmt = this.db.prepare( 'SELECT * FROM proxy_access_rules ORDER BY origin_id ASC, rule_type ASC, created_at ASC', ); return stmt.all() as ProxyAccessRule[]; }
getProxyAccessRulesForCheck(originId: number): { denyRules: ProxyAccessRule[]; originAllowRules: ProxyAccessRule[]; globalAllowRules: ProxyAccessRule[]; } { const stmt = this.db.prepare( 'SELECT * FROM proxy_access_rules WHERE origin_id = ? OR origin_id IS NULL', ); const rules = stmt.all(originId) as ProxyAccessRule[];
const denyRules: ProxyAccessRule[] = []; const originAllowRules: ProxyAccessRule[] = []; const globalAllowRules: ProxyAccessRule[] = [];
for (const rule of rules) { if (rule.rule_type === 'deny') { denyRules.push(rule); } else if (rule.origin_id !== null) { originAllowRules.push(rule); } else { globalAllowRules.push(rule); } }
return { denyRules, originAllowRules, globalAllowRules }; }
getOriginIdByOrigin(origin: string): number | null { const stmt = this.db.prepare('SELECT id FROM proxy_allowed_origins WHERE origin = ? LIMIT 1'); const row = stmt.get(origin) as { id: number } | undefined; return row?.id ?? null; }}