Something went wrong. Try again.
AtAuth
Something went wrong. Try again.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811/** * 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, ClientAccessRule,} 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);
-- Client-level access rules (for OIDC clients and Legacy HMAC apps) CREATE TABLE IF NOT EXISTS client_access_rules ( id INTEGER PRIMARY KEY AUTOINCREMENT, client_id TEXT NOT NULL, 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_client_access_rules_client ON client_access_rules(client_id); `);
// Add require_access_check column to apps table if it doesn't exist if (!columnNames.includes('require_access_check')) { this.db.exec('ALTER TABLE apps ADD COLUMN require_access_check INTEGER DEFAULT 0'); }
// Audit log for admin operations this.db.exec(` CREATE TABLE IF NOT EXISTS audit_log ( id INTEGER PRIMARY KEY AUTOINCREMENT, action TEXT NOT NULL, actor TEXT NOT NULL, target TEXT, details TEXT, ip TEXT, timestamp INTEGER DEFAULT (unixepoch()) ); CREATE INDEX IF NOT EXISTS idx_audit_log_timestamp ON audit_log(timestamp); CREATE INDEX IF NOT EXISTS idx_audit_log_action ON audit_log(action); `);
// Token revocation blacklist (for JWT access tokens) this.db.exec(` CREATE TABLE IF NOT EXISTS revoked_tokens ( jti TEXT PRIMARY KEY, expires_at INTEGER NOT NULL ); CREATE INDEX IF NOT EXISTS idx_revoked_expires ON revoked_tokens(expires_at); `);
// Passkey challenges (moved from in-memory to persistent) this.db.exec(` CREATE TABLE IF NOT EXISTS passkey_challenges ( challenge_key TEXT PRIMARY KEY, challenge TEXT NOT NULL, expires_at INTEGER NOT NULL ); CREATE INDEX IF NOT EXISTS idx_passkey_challenge_expires ON passkey_challenges(expires_at); `);
// OAuth PDS session/state storage (moved from in-memory to persistent) this.db.exec(` CREATE TABLE IF NOT EXISTS oauth_sessions ( key TEXT PRIMARY KEY, data TEXT NOT NULL, type TEXT NOT NULL, created_at INTEGER DEFAULT (unixepoch()), expires_at INTEGER ); CREATE INDEX IF NOT EXISTS idx_oauth_sessions_expires ON oauth_sessions(expires_at); `);
// TOTP replay protection: last used time period try { this.db.exec('ALTER TABLE mfa_totp ADD COLUMN last_used_period INTEGER'); } catch { // Column already exists }
// Email verification brute-force protection try { this.db.exec('ALTER TABLE email_verification_codes ADD COLUMN attempts INTEGER DEFAULT 0'); } catch { // Column already exists } try { this.db.exec('ALTER TABLE email_verification_codes ADD COLUMN locked_until INTEGER'); } catch { // Column already exists }
// 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 = ? AND expires_at > datetime(\'now\')'); 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(); }
// ===== Audit Log Methods =====
logAuditEvent(action: string, actor: string, target?: string, details?: string, ip?: string): void { const stmt = this.db.prepare(` INSERT INTO audit_log (action, actor, target, details, ip) VALUES (?, ?, ?, ?, ?) `); stmt.run(action, actor, target || null, details || null, ip || null); }
getAuditLog(limit = 100, offset = 0): Array<{ id: number; action: string; actor: string; target: string | null; details: string | null; ip: string | null; timestamp: number }> { const stmt = this.db.prepare('SELECT * FROM audit_log ORDER BY timestamp DESC LIMIT ? OFFSET ?'); return stmt.all(limit, offset) as Array<{ id: number; action: string; actor: string; target: string | null; details: string | null; ip: string | null; timestamp: number }>; }
cleanupOldAuditLogs(): number { const ninetyDaysAgo = Math.floor(Date.now() / 1000) - (90 * 24 * 60 * 60); const stmt = this.db.prepare('DELETE FROM audit_log WHERE timestamp < ?'); const result = stmt.run(ninetyDaysAgo); return result.changes; }
// ===== 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; require_access_check: 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), require_access_check: Boolean(row.require_access_check), 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; require_access_check: 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), require_access_check: Boolean(row.require_access_check), 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; require_access_check?: 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.require_access_check !== undefined) { sets.push('require_access_check = ?'); values.push(updates.require_access_check ? 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; }
// Client access rule methods
createClientAccessRule(rule: Omit<ClientAccessRule, 'id' | 'created_at'>): ClientAccessRule { const stmt = this.db.prepare(` INSERT INTO client_access_rules (client_id, rule_type, subject_type, subject_value, description) VALUES (?, ?, ?, ?, ?) `); const result = stmt.run( rule.client_id, rule.rule_type, rule.subject_type, rule.subject_value, rule.description, ); return { id: result.lastInsertRowid as number, client_id: rule.client_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), }; }
deleteClientAccessRule(id: number): void { this.db.prepare('DELETE FROM client_access_rules WHERE id = ?').run(id); }
listClientAccessRules(clientId?: string): ClientAccessRule[] { if (clientId !== undefined) { const stmt = this.db.prepare( 'SELECT * FROM client_access_rules WHERE client_id = ? ORDER BY rule_type ASC, created_at ASC', ); return stmt.all(clientId) as ClientAccessRule[]; } const stmt = this.db.prepare( 'SELECT * FROM client_access_rules ORDER BY client_id ASC, rule_type ASC, created_at ASC', ); return stmt.all() as ClientAccessRule[]; }
getClientAccessRulesForCheck(clientId: string): { denyRules: ClientAccessRule[]; originAllowRules: ClientAccessRule[]; globalAllowRules: ClientAccessRule[]; } { const stmt = this.db.prepare( 'SELECT * FROM client_access_rules WHERE client_id = ?', ); const rules = stmt.all(clientId) as ClientAccessRule[];
const denyRules: ClientAccessRule[] = []; const originAllowRules: ClientAccessRule[] = [];
for (const rule of rules) { if (rule.rule_type === 'deny') { denyRules.push(rule); } else { // All client rules are treated as client-specific (like origin-specific) originAllowRules.push(rule); } }
// No global rules for client access (all rules are client-specific) return { denyRules, originAllowRules, globalAllowRules: [] }; }
setClientAccessCheck(clientId: string, enabled: boolean): void { this.db.prepare('UPDATE apps SET require_access_check = ? WHERE id = ?').run(enabled ? 1 : 0, clientId); }
// --- Token revocation blacklist ---
addRevokedToken(jti: string, expiresAt: number): void { this.db.prepare('INSERT OR REPLACE INTO revoked_tokens (jti, expires_at) VALUES (?, ?)').run(jti, expiresAt); }
isTokenRevoked(jti: string): boolean { const row = this.db.prepare('SELECT 1 FROM revoked_tokens WHERE jti = ?').get(jti); return !!row; }
cleanupExpiredRevokedTokens(): number { const now = Math.floor(Date.now() / 1000); return this.db.prepare('DELETE FROM revoked_tokens WHERE expires_at < ?').run(now).changes; }
// --- Passkey challenges (persistent) ---
savePasskeyChallenge(key: string, challenge: string, expiresAt: number): void { this.db.prepare( 'INSERT OR REPLACE INTO passkey_challenges (challenge_key, challenge, expires_at) VALUES (?, ?, ?)' ).run(key, challenge, expiresAt); }
getPasskeyChallenge(key: string): { challenge: string; expires_at: number } | null { const row = this.db.prepare('SELECT challenge, expires_at FROM passkey_challenges WHERE challenge_key = ?').get(key) as { challenge: string; expires_at: number } | undefined; if (!row) return null; if (row.expires_at < Math.floor(Date.now() / 1000)) { this.db.prepare('DELETE FROM passkey_challenges WHERE challenge_key = ?').run(key); return null; } return row; }
deletePasskeyChallenge(key: string): void { this.db.prepare('DELETE FROM passkey_challenges WHERE challenge_key = ?').run(key); }
cleanupExpiredPasskeyChallenges(): number { return this.db.prepare('DELETE FROM passkey_challenges WHERE expires_at < ?').run(Date.now()).changes; }
// --- OAuth PDS session/state storage ---
saveOAuthSession(key: string, data: string, type: string, expiresAt?: number): void { this.db.prepare( 'INSERT OR REPLACE INTO oauth_sessions (key, data, type, expires_at) VALUES (?, ?, ?, ?)' ).run(key, data, type, expiresAt ?? null); }
getOAuthSession(key: string): { data: string; type: string } | null { const row = this.db.prepare('SELECT data, type FROM oauth_sessions WHERE key = ?').get(key) as { data: string; type: string } | undefined; return row || null; }
deleteOAuthSession(key: string): void { this.db.prepare('DELETE FROM oauth_sessions WHERE key = ?').run(key); }
cleanupExpiredOAuthSessions(): number { const now = Math.floor(Date.now() / 1000); return this.db.prepare('DELETE FROM oauth_sessions WHERE expires_at IS NOT NULL AND expires_at < ?').run(now).changes; }
// --- TOTP replay protection ---
updateTOTPLastUsedPeriod(did: string, period: number): void { this.db.prepare('UPDATE mfa_totp SET last_used_period = ? WHERE did = ?').run(period, did); }
getTOTPLastUsedPeriod(did: string): number | null { const row = this.db.prepare('SELECT last_used_period FROM mfa_totp WHERE did = ?').get(did) as { last_used_period: number | null } | undefined; return row?.last_used_period ?? null; }
// --- Email verification brute-force protection ---
incrementEmailAttempts(email: string): number { this.db.prepare( 'UPDATE email_verification_codes SET attempts = COALESCE(attempts, 0) + 1 WHERE email = ? AND used = 0' ).run(email); const row = this.db.prepare( 'SELECT MAX(COALESCE(attempts, 0)) as attempts FROM email_verification_codes WHERE email = ? AND used = 0' ).get(email) as { attempts: number } | undefined; return row?.attempts ?? 0; }
lockEmailVerification(email: string, until: number): void { this.db.prepare( 'UPDATE email_verification_codes SET locked_until = ? WHERE email = ? AND used = 0' ).run(until, email); }
isEmailVerificationLocked(email: string): boolean { const now = Date.now(); const row = this.db.prepare( 'SELECT 1 FROM email_verification_codes WHERE email = ? AND used = 0 AND locked_until IS NOT NULL AND locked_until > ?' ).get(email, now); return !!row; }}