bkb.arcnode.xyz
AtAuth
1/**
2 * Database Service
3 *
4 * SQLite database for OAuth state, app sessions, user mappings,
5 * OIDC clients, passkeys, MFA, and email verification
6 */
7
8import Database from 'better-sqlite3';
9import path from 'path';
10import type {
11 AppConfig,
12 AppSession,
13 OAuthState,
14 UserMapping,
15 SessionConnectionState,
16 ActiveSession,
17 OIDCClientConfig,
18 OIDCKey,
19 AuthorizationCode,
20 RefreshToken,
21 PasskeyCredential,
22 MFATOTPConfig,
23 MFABackupCode,
24 UserEmail,
25 EmailVerificationCode,
26 ProxySession,
27 ProxyAllowedOrigin,
28 ProxyAuthRequest,
29 ProxyAccessRule,
30} from '../types/index.js';
31
32export class DatabaseService {
33 private db: Database.Database;
34
35 constructor(dbPath?: string) {
36 const defaultPath = path.join(process.cwd(), 'data', 'gateway.db');
37 this.db = new Database(dbPath || defaultPath);
38 this.initialize();
39 }
40
41 private initialize(): void {
42 // Enable WAL mode for better concurrency
43 this.db.pragma('journal_mode = WAL');
44
45 // Create tables
46 this.db.exec(`
47 -- Application configurations (HMAC secrets per app)
48 CREATE TABLE IF NOT EXISTS apps (
49 id TEXT PRIMARY KEY,
50 name TEXT NOT NULL,
51 hmac_secret TEXT NOT NULL,
52 token_ttl_seconds INTEGER DEFAULT 3600,
53 callback_url TEXT,
54 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
55 );
56
57 -- OAuth state for PKCE flow
58 CREATE TABLE IF NOT EXISTS oauth_states (
59 state TEXT PRIMARY KEY,
60 code_verifier TEXT NOT NULL,
61 app_id TEXT NOT NULL,
62 redirect_uri TEXT NOT NULL,
63 created_at INTEGER NOT NULL,
64 FOREIGN KEY (app_id) REFERENCES apps(id)
65 );
66
67 -- User mappings (DID -> app user_id)
68 CREATE TABLE IF NOT EXISTS user_mappings (
69 did TEXT NOT NULL,
70 app_id TEXT NOT NULL,
71 user_id INTEGER NOT NULL,
72 handle TEXT,
73 linked_at DATETIME DEFAULT CURRENT_TIMESTAMP,
74 PRIMARY KEY (did, app_id),
75 FOREIGN KEY (app_id) REFERENCES apps(id)
76 );
77
78 -- Active sessions
79 CREATE TABLE IF NOT EXISTS sessions (
80 id TEXT PRIMARY KEY,
81 did TEXT NOT NULL,
82 handle TEXT NOT NULL,
83 user_id INTEGER,
84 app_id TEXT NOT NULL,
85 refresh_token TEXT,
86 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
87 expires_at DATETIME NOT NULL,
88 connection_state TEXT DEFAULT 'pending',
89 last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
90 client_info TEXT,
91 FOREIGN KEY (app_id) REFERENCES apps(id)
92 );
93
94 -- Indexes
95 CREATE INDEX IF NOT EXISTS idx_oauth_states_created ON oauth_states(created_at);
96 CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
97 CREATE INDEX IF NOT EXISTS idx_sessions_did_app ON sessions(did, app_id);
98 `);
99
100 // Run migrations for new columns and tables
101 this.runMigrations();
102 }
103
104 private runMigrations(): void {
105 // Check if we need to add OIDC columns to apps table
106 const appColumns = this.db.pragma('table_info(apps)') as Array<{ name: string }>;
107 const columnNames = appColumns.map((c) => c.name);
108
109 if (!columnNames.includes('client_type')) {
110 this.db.exec(`
111 -- Add OIDC columns to apps table
112 ALTER TABLE apps ADD COLUMN client_type TEXT DEFAULT 'legacy';
113 ALTER TABLE apps ADD COLUMN client_secret TEXT;
114 ALTER TABLE apps ADD COLUMN redirect_uris TEXT DEFAULT '[]';
115 ALTER TABLE apps ADD COLUMN grant_types TEXT DEFAULT '["authorization_code"]';
116 ALTER TABLE apps ADD COLUMN allowed_scopes TEXT DEFAULT '["openid"]';
117 ALTER TABLE apps ADD COLUMN id_token_ttl_seconds INTEGER DEFAULT 3600;
118 ALTER TABLE apps ADD COLUMN access_token_ttl_seconds INTEGER DEFAULT 3600;
119 ALTER TABLE apps ADD COLUMN refresh_token_ttl_seconds INTEGER DEFAULT 604800;
120 ALTER TABLE apps ADD COLUMN require_pkce BOOLEAN DEFAULT 1;
121 ALTER TABLE apps ADD COLUMN token_endpoint_auth_method TEXT DEFAULT 'client_secret_basic';
122 `);
123 }
124
125 // Create OIDC signing keys table
126 this.db.exec(`
127 CREATE TABLE IF NOT EXISTS oidc_keys (
128 kid TEXT PRIMARY KEY,
129 algorithm TEXT NOT NULL,
130 private_key_encrypted TEXT NOT NULL,
131 public_key_jwk TEXT NOT NULL,
132 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
133 expires_at DATETIME,
134 is_active BOOLEAN DEFAULT 1,
135 use_for_signing BOOLEAN DEFAULT 1
136 );
137 `);
138
139 // Create authorization codes table
140 this.db.exec(`
141 CREATE TABLE IF NOT EXISTS authorization_codes (
142 code TEXT PRIMARY KEY,
143 client_id TEXT NOT NULL,
144 redirect_uri TEXT NOT NULL,
145 scope TEXT NOT NULL,
146 state TEXT,
147 nonce TEXT,
148 code_challenge TEXT,
149 code_challenge_method TEXT,
150 did TEXT NOT NULL,
151 handle TEXT NOT NULL,
152 user_id INTEGER,
153 created_at INTEGER NOT NULL,
154 expires_at INTEGER NOT NULL,
155 used BOOLEAN DEFAULT 0,
156 FOREIGN KEY (client_id) REFERENCES apps(id)
157 );
158
159 CREATE INDEX IF NOT EXISTS idx_auth_codes_expires ON authorization_codes(expires_at);
160 `);
161
162 // Add state column if it doesn't exist (migration for existing databases)
163 try {
164 this.db.exec('ALTER TABLE authorization_codes ADD COLUMN state TEXT');
165 } catch {
166 // Column already exists
167 }
168
169 // Create refresh tokens table
170 this.db.exec(`
171 CREATE TABLE IF NOT EXISTS refresh_tokens (
172 token_hash TEXT PRIMARY KEY,
173 client_id TEXT NOT NULL,
174 did TEXT NOT NULL,
175 handle TEXT NOT NULL,
176 user_id INTEGER,
177 scope TEXT NOT NULL,
178 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
179 expires_at DATETIME NOT NULL,
180 last_used_at DATETIME,
181 revoked BOOLEAN DEFAULT 0,
182 family_id TEXT,
183 FOREIGN KEY (client_id) REFERENCES apps(id)
184 );
185
186 CREATE INDEX IF NOT EXISTS idx_refresh_tokens_client ON refresh_tokens(client_id, did);
187 CREATE INDEX IF NOT EXISTS idx_refresh_tokens_family ON refresh_tokens(family_id);
188 `);
189
190 // Create passkey credentials table
191 this.db.exec(`
192 CREATE TABLE IF NOT EXISTS passkey_credentials (
193 id TEXT PRIMARY KEY,
194 did TEXT NOT NULL,
195 handle TEXT NOT NULL,
196 public_key TEXT NOT NULL,
197 counter INTEGER DEFAULT 0,
198 device_type TEXT,
199 backed_up BOOLEAN DEFAULT 0,
200 transports TEXT,
201 name TEXT,
202 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
203 last_used_at DATETIME
204 );
205
206 CREATE INDEX IF NOT EXISTS idx_passkey_did ON passkey_credentials(did);
207 `);
208
209 // Create MFA TOTP table
210 this.db.exec(`
211 CREATE TABLE IF NOT EXISTS mfa_totp (
212 did TEXT PRIMARY KEY,
213 secret_encrypted TEXT NOT NULL,
214 enabled BOOLEAN DEFAULT 0,
215 verified_at DATETIME,
216 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
217 );
218 `);
219
220 // Create MFA backup codes table
221 this.db.exec(`
222 CREATE TABLE IF NOT EXISTS mfa_backup_codes (
223 id INTEGER PRIMARY KEY AUTOINCREMENT,
224 did TEXT NOT NULL,
225 code_hash TEXT NOT NULL,
226 used BOOLEAN DEFAULT 0,
227 used_at DATETIME,
228 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
229 );
230
231 CREATE INDEX IF NOT EXISTS idx_backup_did ON mfa_backup_codes(did);
232 `);
233
234 // Create user emails table
235 this.db.exec(`
236 CREATE TABLE IF NOT EXISTS user_emails (
237 id INTEGER PRIMARY KEY AUTOINCREMENT,
238 did TEXT NOT NULL,
239 email TEXT NOT NULL,
240 verified BOOLEAN DEFAULT 0,
241 verified_at DATETIME,
242 is_primary BOOLEAN DEFAULT 0,
243 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
244 UNIQUE(did, email)
245 );
246
247 CREATE INDEX IF NOT EXISTS idx_email_did ON user_emails(did);
248 CREATE INDEX IF NOT EXISTS idx_email_address ON user_emails(email);
249 `);
250
251 // Create email verification codes table
252 this.db.exec(`
253 CREATE TABLE IF NOT EXISTS email_verification_codes (
254 id INTEGER PRIMARY KEY AUTOINCREMENT,
255 email TEXT NOT NULL,
256 code_hash TEXT NOT NULL,
257 purpose TEXT NOT NULL,
258 expires_at DATETIME NOT NULL,
259 used BOOLEAN DEFAULT 0,
260 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
261 );
262
263 CREATE INDEX IF NOT EXISTS idx_verify_email ON email_verification_codes(email);
264 CREATE INDEX IF NOT EXISTS idx_verify_expires ON email_verification_codes(expires_at);
265 `);
266
267 // Forward-auth proxy tables
268 this.db.exec(`
269 CREATE TABLE IF NOT EXISTS proxy_sessions (
270 id TEXT PRIMARY KEY,
271 did TEXT NOT NULL,
272 handle TEXT NOT NULL,
273 created_at INTEGER NOT NULL,
274 expires_at INTEGER NOT NULL,
275 last_activity INTEGER NOT NULL,
276 user_agent TEXT,
277 ip_address TEXT
278 );
279 CREATE INDEX IF NOT EXISTS idx_proxy_sessions_expires ON proxy_sessions(expires_at);
280 CREATE INDEX IF NOT EXISTS idx_proxy_sessions_did ON proxy_sessions(did);
281
282 CREATE TABLE IF NOT EXISTS proxy_allowed_origins (
283 id INTEGER PRIMARY KEY AUTOINCREMENT,
284 origin TEXT NOT NULL UNIQUE,
285 name TEXT NOT NULL,
286 created_at INTEGER DEFAULT (unixepoch())
287 );
288
289 CREATE TABLE IF NOT EXISTS proxy_auth_requests (
290 id TEXT PRIMARY KEY,
291 redirect_uri TEXT NOT NULL,
292 created_at INTEGER NOT NULL,
293 expires_at INTEGER NOT NULL
294 );
295
296 CREATE TABLE IF NOT EXISTS proxy_access_rules (
297 id INTEGER PRIMARY KEY AUTOINCREMENT,
298 origin_id INTEGER,
299 rule_type TEXT NOT NULL CHECK(rule_type IN ('allow', 'deny')),
300 subject_type TEXT NOT NULL CHECK(subject_type IN ('did', 'handle_pattern')),
301 subject_value TEXT NOT NULL,
302 description TEXT,
303 created_at INTEGER DEFAULT (unixepoch())
304 );
305 CREATE INDEX IF NOT EXISTS idx_proxy_access_rules_origin ON proxy_access_rules(origin_id);
306 `);
307
308 // Audit log for admin operations
309 this.db.exec(`
310 CREATE TABLE IF NOT EXISTS audit_log (
311 id INTEGER PRIMARY KEY AUTOINCREMENT,
312 action TEXT NOT NULL,
313 actor TEXT NOT NULL,
314 target TEXT,
315 details TEXT,
316 ip TEXT,
317 timestamp INTEGER DEFAULT (unixepoch())
318 );
319 CREATE INDEX IF NOT EXISTS idx_audit_log_timestamp ON audit_log(timestamp);
320 CREATE INDEX IF NOT EXISTS idx_audit_log_action ON audit_log(action);
321 `);
322
323 // Ensure sentinel proxy-auth app exists for forward-auth OAuth flows
324 const proxyApp = this.db.prepare('SELECT 1 FROM apps WHERE id = ?').get('proxy-auth');
325 if (!proxyApp) {
326 this.db.prepare(
327 `INSERT INTO apps (id, name, hmac_secret, callback_url, client_type)
328 VALUES (?, ?, ?, ?, ?)`
329 ).run('proxy-auth', 'Forward-Auth Proxy', 'internal-not-used', '', 'internal');
330 }
331 }
332
333 // App configuration methods
334 getApp(appId: string): AppConfig | null {
335 const stmt = this.db.prepare('SELECT * FROM apps WHERE id = ?');
336 const row = stmt.get(appId) as AppConfig | undefined;
337 return row || null;
338 }
339
340 upsertApp(app: AppConfig): void {
341 const stmt = this.db.prepare(`
342 INSERT INTO apps (id, name, hmac_secret, token_ttl_seconds, callback_url)
343 VALUES (?, ?, ?, ?, ?)
344 ON CONFLICT(id) DO UPDATE SET
345 name = excluded.name,
346 hmac_secret = excluded.hmac_secret,
347 token_ttl_seconds = excluded.token_ttl_seconds,
348 callback_url = excluded.callback_url
349 `);
350 stmt.run(app.id, app.name, app.hmac_secret, app.token_ttl_seconds, app.callback_url);
351 }
352
353 // OAuth state methods
354 saveOAuthState(state: OAuthState): void {
355 const stmt = this.db.prepare(`
356 INSERT OR REPLACE INTO oauth_states (state, code_verifier, app_id, redirect_uri, created_at)
357 VALUES (?, ?, ?, ?, ?)
358 `);
359 stmt.run(state.state, state.code_verifier, state.app_id, state.redirect_uri, state.created_at);
360 }
361
362 getOAuthState(state: string): OAuthState | null {
363 const stmt = this.db.prepare('SELECT * FROM oauth_states WHERE state = ?');
364 const row = stmt.get(state) as OAuthState | undefined;
365 return row || null;
366 }
367
368 deleteOAuthState(state: string): void {
369 const stmt = this.db.prepare('DELETE FROM oauth_states WHERE state = ?');
370 stmt.run(state);
371 }
372
373 cleanupOldOAuthStates(): number {
374 const tenMinutesAgo = Math.floor(Date.now() / 1000) - 600;
375 const stmt = this.db.prepare('DELETE FROM oauth_states WHERE created_at < ?');
376 const result = stmt.run(tenMinutesAgo);
377 return result.changes;
378 }
379
380 // User mapping methods
381 getUserMapping(did: string, appId: string): UserMapping | null {
382 const stmt = this.db.prepare(
383 'SELECT * FROM user_mappings WHERE did = ? AND app_id = ?'
384 );
385 const row = stmt.get(did, appId) as UserMapping | undefined;
386 return row || null;
387 }
388
389 setUserMapping(mapping: Omit<UserMapping, 'linked_at'>): void {
390 const stmt = this.db.prepare(`
391 INSERT INTO user_mappings (did, app_id, user_id, handle)
392 VALUES (?, ?, ?, ?)
393 ON CONFLICT(did, app_id) DO UPDATE SET
394 user_id = excluded.user_id,
395 handle = excluded.handle
396 `);
397 stmt.run(mapping.did, mapping.app_id, mapping.user_id, mapping.handle);
398 }
399
400 // Session methods
401 createSession(session: Omit<AppSession, 'created_at'>): void {
402 const stmt = this.db.prepare(`
403 INSERT INTO sessions (id, did, handle, user_id, app_id, refresh_token, expires_at)
404 VALUES (?, ?, ?, ?, ?, ?, ?)
405 `);
406 stmt.run(
407 session.id,
408 session.did,
409 session.handle,
410 session.user_id,
411 session.app_id,
412 session.refresh_token,
413 session.expires_at.toISOString()
414 );
415 }
416
417 getSession(sessionId: string): AppSession | null {
418 const stmt = this.db.prepare('SELECT * FROM sessions WHERE id = ? AND expires_at > datetime(\'now\')');
419 const row = stmt.get(sessionId) as (Omit<AppSession, 'created_at' | 'expires_at'> & {
420 created_at: string;
421 expires_at: string;
422 }) | undefined;
423
424 if (!row) return null;
425
426 return {
427 ...row,
428 created_at: new Date(row.created_at),
429 expires_at: new Date(row.expires_at),
430 };
431 }
432
433 deleteSession(sessionId: string): void {
434 const stmt = this.db.prepare('DELETE FROM sessions WHERE id = ?');
435 stmt.run(sessionId);
436 }
437
438 cleanupExpiredSessions(): number {
439 const stmt = this.db.prepare("DELETE FROM sessions WHERE expires_at < datetime('now')");
440 const result = stmt.run();
441 return result.changes;
442 }
443
444 // Session conflict detection methods
445 getActiveSessionsByDid(did: string, appId: string): ActiveSession[] {
446 const stmt = this.db.prepare(`
447 SELECT * FROM sessions
448 WHERE did = ? AND app_id = ? AND expires_at > datetime('now')
449 ORDER BY created_at DESC
450 `);
451 const rows = stmt.all(did, appId) as Array<{
452 id: string;
453 did: string;
454 handle: string;
455 user_id: number | null;
456 app_id: string;
457 refresh_token?: string;
458 created_at: string;
459 expires_at: string;
460 connection_state: SessionConnectionState;
461 last_activity: string;
462 client_info?: string;
463 }>;
464
465 return rows.map((row) => ({
466 ...row,
467 created_at: new Date(row.created_at),
468 expires_at: new Date(row.expires_at),
469 last_activity: new Date(row.last_activity),
470 connection_state: row.connection_state || 'pending',
471 }));
472 }
473
474 updateSessionConnectionState(
475 sessionId: string,
476 state: SessionConnectionState,
477 clientInfo?: string
478 ): void {
479 const stmt = this.db.prepare(`
480 UPDATE sessions
481 SET connection_state = ?, last_activity = datetime('now'), client_info = COALESCE(?, client_info)
482 WHERE id = ?
483 `);
484 stmt.run(state, clientInfo, sessionId);
485 }
486
487 updateSessionActivity(sessionId: string): void {
488 const stmt = this.db.prepare(`
489 UPDATE sessions SET last_activity = datetime('now') WHERE id = ?
490 `);
491 stmt.run(sessionId);
492 }
493
494 deleteOtherSessions(keepSessionId: string, did: string, appId: string): number {
495 const stmt = this.db.prepare(`
496 DELETE FROM sessions
497 WHERE did = ? AND app_id = ? AND id != ?
498 `);
499 const result = stmt.run(did, appId, keepSessionId);
500 return result.changes;
501 }
502
503 // Admin methods for listing and managing apps
504 getAllApps(): Omit<AppConfig, 'hmac_secret'>[] {
505 const stmt = this.db.prepare(
506 'SELECT id, name, token_ttl_seconds, callback_url, created_at FROM apps ORDER BY created_at DESC'
507 );
508 return stmt.all() as Omit<AppConfig, 'hmac_secret'>[];
509 }
510
511 deleteApp(appId: string): void {
512 // Delete related data first (order respects foreign keys)
513 this.db.prepare('DELETE FROM sessions WHERE app_id = ?').run(appId);
514 this.db.prepare('DELETE FROM oauth_states WHERE app_id = ?').run(appId);
515 this.db.prepare('DELETE FROM user_mappings WHERE app_id = ?').run(appId);
516 this.db.prepare('DELETE FROM authorization_codes WHERE client_id = ?').run(appId);
517 this.db.prepare('DELETE FROM refresh_tokens WHERE client_id = ?').run(appId);
518 // Delete the app
519 this.db.prepare('DELETE FROM apps WHERE id = ?').run(appId);
520 }
521
522 getStats(): {
523 apps_count: number;
524 oidc_clients_count: number;
525 active_sessions_count: number;
526 users_count: number;
527 passkeys_count: number;
528 mfa_enabled_count: number;
529 verified_emails_count: number;
530 } {
531 const appsCount = (this.db.prepare('SELECT COUNT(*) as count FROM apps').get() as { count: number }).count;
532 const oidcClientsCount = (this.db.prepare("SELECT COUNT(*) as count FROM apps WHERE client_type = 'oidc'").get() as { count: number }).count;
533 const activeSessionsCount = (this.db.prepare("SELECT COUNT(*) as count FROM sessions WHERE expires_at > datetime('now')").get() as { count: number }).count;
534 const usersCount = (this.db.prepare('SELECT COUNT(DISTINCT did) as count FROM user_mappings').get() as { count: number }).count;
535 const passkeysCount = (this.db.prepare('SELECT COUNT(*) as count FROM passkey_credentials').get() as { count: number }).count;
536 const mfaEnabledCount = (this.db.prepare('SELECT COUNT(*) as count FROM mfa_totp WHERE enabled = 1').get() as { count: number }).count;
537 const verifiedEmailsCount = (this.db.prepare('SELECT COUNT(*) as count FROM user_emails WHERE verified = 1').get() as { count: number }).count;
538
539 return {
540 apps_count: appsCount,
541 oidc_clients_count: oidcClientsCount,
542 active_sessions_count: activeSessionsCount,
543 users_count: usersCount,
544 passkeys_count: passkeysCount,
545 mfa_enabled_count: mfaEnabledCount,
546 verified_emails_count: verifiedEmailsCount,
547 };
548 }
549
550 close(): void {
551 this.db.close();
552 }
553
554 // ===== Audit Log Methods =====
555
556 logAuditEvent(action: string, actor: string, target?: string, details?: string, ip?: string): void {
557 const stmt = this.db.prepare(`
558 INSERT INTO audit_log (action, actor, target, details, ip)
559 VALUES (?, ?, ?, ?, ?)
560 `);
561 stmt.run(action, actor, target || null, details || null, ip || null);
562 }
563
564 getAuditLog(limit = 100, offset = 0): Array<{ id: number; action: string; actor: string; target: string | null; details: string | null; ip: string | null; timestamp: number }> {
565 const stmt = this.db.prepare('SELECT * FROM audit_log ORDER BY timestamp DESC LIMIT ? OFFSET ?');
566 return stmt.all(limit, offset) as Array<{ id: number; action: string; actor: string; target: string | null; details: string | null; ip: string | null; timestamp: number }>;
567 }
568
569 cleanupOldAuditLogs(): number {
570 const ninetyDaysAgo = Math.floor(Date.now() / 1000) - (90 * 24 * 60 * 60);
571 const stmt = this.db.prepare('DELETE FROM audit_log WHERE timestamp < ?');
572 const result = stmt.run(ninetyDaysAgo);
573 return result.changes;
574 }
575
576 // ===== OIDC Key Management Methods =====
577
578 saveOIDCKey(key: Omit<OIDCKey, 'created_at'>): void {
579 const stmt = this.db.prepare(`
580 INSERT INTO oidc_keys (kid, algorithm, private_key_encrypted, public_key_jwk, is_active, use_for_signing)
581 VALUES (?, ?, ?, ?, ?, ?)
582 `);
583 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);
584 }
585
586 getOIDCKey(kid: string): OIDCKey | null {
587 const stmt = this.db.prepare('SELECT * FROM oidc_keys WHERE kid = ?');
588 const row = stmt.get(kid) as {
589 kid: string;
590 algorithm: 'ES256' | 'RS256';
591 private_key_encrypted: string;
592 public_key_jwk: string;
593 created_at: string;
594 expires_at: string | null;
595 is_active: number;
596 use_for_signing: number;
597 } | undefined;
598 if (!row) return null;
599 return {
600 kid: row.kid,
601 algorithm: row.algorithm,
602 private_key_encrypted: row.private_key_encrypted,
603 public_key_jwk: row.public_key_jwk,
604 created_at: new Date(row.created_at),
605 expires_at: row.expires_at ? new Date(row.expires_at) : undefined,
606 is_active: Boolean(row.is_active),
607 use_for_signing: Boolean(row.use_for_signing),
608 };
609 }
610
611 getActiveOIDCKeys(): OIDCKey[] {
612 const stmt = this.db.prepare('SELECT * FROM oidc_keys WHERE is_active = 1 ORDER BY created_at DESC');
613 const rows = stmt.all() as Array<{
614 kid: string;
615 algorithm: 'ES256' | 'RS256';
616 private_key_encrypted: string;
617 public_key_jwk: string;
618 created_at: string;
619 expires_at: string | null;
620 is_active: number;
621 use_for_signing: number;
622 }>;
623 return rows.map(row => ({
624 kid: row.kid,
625 algorithm: row.algorithm,
626 private_key_encrypted: row.private_key_encrypted,
627 public_key_jwk: row.public_key_jwk,
628 created_at: new Date(row.created_at),
629 expires_at: row.expires_at ? new Date(row.expires_at) : undefined,
630 is_active: Boolean(row.is_active),
631 use_for_signing: Boolean(row.use_for_signing),
632 }));
633 }
634
635 getCurrentSigningKey(): OIDCKey | null {
636 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');
637 const row = stmt.get() as {
638 kid: string;
639 algorithm: 'ES256' | 'RS256';
640 private_key_encrypted: string;
641 public_key_jwk: string;
642 created_at: string;
643 expires_at: string | null;
644 is_active: number;
645 use_for_signing: number;
646 } | undefined;
647 if (!row) return null;
648 return {
649 kid: row.kid,
650 algorithm: row.algorithm,
651 private_key_encrypted: row.private_key_encrypted,
652 public_key_jwk: row.public_key_jwk,
653 created_at: new Date(row.created_at),
654 expires_at: row.expires_at ? new Date(row.expires_at) : undefined,
655 is_active: Boolean(row.is_active),
656 use_for_signing: Boolean(row.use_for_signing),
657 };
658 }
659
660 markKeyAsNotSigning(kid: string): void {
661 const stmt = this.db.prepare('UPDATE oidc_keys SET use_for_signing = 0 WHERE kid = ?');
662 stmt.run(kid);
663 }
664
665 deactivateKey(kid: string): void {
666 const stmt = this.db.prepare('UPDATE oidc_keys SET is_active = 0, use_for_signing = 0 WHERE kid = ?');
667 stmt.run(kid);
668 }
669
670 deleteOIDCKey(kid: string): void {
671 const stmt = this.db.prepare('DELETE FROM oidc_keys WHERE kid = ?');
672 stmt.run(kid);
673 }
674
675 // ===== Authorization Code Methods =====
676
677 saveAuthorizationCode(code: AuthorizationCode): void {
678 const stmt = this.db.prepare(`
679 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)
680 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
681 `);
682 stmt.run(
683 code.code,
684 code.client_id,
685 code.redirect_uri,
686 code.scope,
687 code.state,
688 code.nonce,
689 code.code_challenge,
690 code.code_challenge_method,
691 code.did,
692 code.handle,
693 code.user_id,
694 code.created_at,
695 code.expires_at
696 );
697 }
698
699 getAuthorizationCode(code: string): AuthorizationCode | null {
700 const stmt = this.db.prepare('SELECT * FROM authorization_codes WHERE code = ?');
701 const row = stmt.get(code) as {
702 code: string;
703 client_id: string;
704 redirect_uri: string;
705 scope: string;
706 state: string | null;
707 nonce: string | null;
708 code_challenge: string | null;
709 code_challenge_method: 'S256' | 'plain' | null;
710 did: string;
711 handle: string;
712 user_id: number | null;
713 created_at: number;
714 expires_at: number;
715 used: number;
716 } | undefined;
717 if (!row) return null;
718 return {
719 code: row.code,
720 client_id: row.client_id,
721 redirect_uri: row.redirect_uri,
722 scope: row.scope,
723 state: row.state ?? undefined,
724 nonce: row.nonce ?? undefined,
725 code_challenge: row.code_challenge ?? undefined,
726 code_challenge_method: row.code_challenge_method ?? undefined,
727 did: row.did,
728 handle: row.handle,
729 user_id: row.user_id ?? undefined,
730 created_at: row.created_at,
731 expires_at: row.expires_at,
732 used: Boolean(row.used),
733 };
734 }
735
736 markAuthorizationCodeUsed(code: string): void {
737 const stmt = this.db.prepare('UPDATE authorization_codes SET used = 1 WHERE code = ?');
738 stmt.run(code);
739 }
740
741 updateAuthorizationCodeUser(code: string, did: string, handle: string): void {
742 const stmt = this.db.prepare('UPDATE authorization_codes SET did = ?, handle = ? WHERE code = ?');
743 stmt.run(did, handle, code);
744 }
745
746 cleanupExpiredAuthorizationCodes(): number {
747 const now = Math.floor(Date.now() / 1000);
748 const stmt = this.db.prepare('DELETE FROM authorization_codes WHERE expires_at < ?');
749 const result = stmt.run(now);
750 return result.changes;
751 }
752
753 // ===== Refresh Token Methods =====
754
755 saveRefreshToken(token: Omit<RefreshToken, 'created_at' | 'last_used_at'>): void {
756 const stmt = this.db.prepare(`
757 INSERT INTO refresh_tokens (token_hash, client_id, did, handle, user_id, scope, expires_at, family_id)
758 VALUES (?, ?, ?, ?, ?, ?, ?, ?)
759 `);
760 stmt.run(
761 token.token_hash,
762 token.client_id,
763 token.did,
764 token.handle,
765 token.user_id,
766 token.scope,
767 token.expires_at.toISOString(),
768 token.family_id
769 );
770 }
771
772 getRefreshToken(tokenHash: string): RefreshToken | null {
773 const stmt = this.db.prepare('SELECT * FROM refresh_tokens WHERE token_hash = ?');
774 const row = stmt.get(tokenHash) as {
775 token_hash: string;
776 client_id: string;
777 did: string;
778 handle: string;
779 user_id: number | null;
780 scope: string;
781 created_at: string;
782 expires_at: string;
783 last_used_at: string | null;
784 revoked: number;
785 family_id: string | null;
786 } | undefined;
787 if (!row) return null;
788 return {
789 token_hash: row.token_hash,
790 client_id: row.client_id,
791 did: row.did,
792 handle: row.handle,
793 user_id: row.user_id ?? undefined,
794 scope: row.scope,
795 created_at: new Date(row.created_at),
796 expires_at: new Date(row.expires_at),
797 last_used_at: row.last_used_at ? new Date(row.last_used_at) : undefined,
798 revoked: Boolean(row.revoked),
799 family_id: row.family_id ?? undefined,
800 };
801 }
802
803 updateRefreshTokenLastUsed(tokenHash: string): void {
804 const stmt = this.db.prepare("UPDATE refresh_tokens SET last_used_at = datetime('now') WHERE token_hash = ?");
805 stmt.run(tokenHash);
806 }
807
808 revokeRefreshToken(tokenHash: string): void {
809 const stmt = this.db.prepare('UPDATE refresh_tokens SET revoked = 1 WHERE token_hash = ?');
810 stmt.run(tokenHash);
811 }
812
813 revokeRefreshTokenFamily(familyId: string): void {
814 const stmt = this.db.prepare('UPDATE refresh_tokens SET revoked = 1 WHERE family_id = ?');
815 stmt.run(familyId);
816 }
817
818 revokeAllRefreshTokensForUser(did: string, clientId: string): number {
819 const stmt = this.db.prepare('UPDATE refresh_tokens SET revoked = 1 WHERE did = ? AND client_id = ?');
820 const result = stmt.run(did, clientId);
821 return result.changes;
822 }
823
824 cleanupExpiredRefreshTokens(): number {
825 const stmt = this.db.prepare("DELETE FROM refresh_tokens WHERE expires_at < datetime('now')");
826 const result = stmt.run();
827 return result.changes;
828 }
829
830 // ===== Passkey Credential Methods =====
831
832 savePasskeyCredential(credential: Omit<PasskeyCredential, 'created_at' | 'last_used_at'>): void {
833 const stmt = this.db.prepare(`
834 INSERT INTO passkey_credentials (id, did, handle, public_key, counter, device_type, backed_up, transports, name)
835 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
836 `);
837 stmt.run(
838 credential.id,
839 credential.did,
840 credential.handle,
841 credential.public_key,
842 credential.counter,
843 credential.device_type,
844 credential.backed_up ? 1 : 0,
845 credential.transports ? JSON.stringify(credential.transports) : null,
846 credential.name
847 );
848 }
849
850 getPasskeyCredential(credentialId: string): PasskeyCredential | null {
851 const stmt = this.db.prepare('SELECT * FROM passkey_credentials WHERE id = ?');
852 const row = stmt.get(credentialId) as {
853 id: string;
854 did: string;
855 handle: string;
856 public_key: string;
857 counter: number;
858 device_type: 'platform' | 'cross-platform' | null;
859 backed_up: number;
860 transports: string | null;
861 name: string | null;
862 created_at: string;
863 last_used_at: string | null;
864 } | undefined;
865 if (!row) return null;
866 return {
867 id: row.id,
868 did: row.did,
869 handle: row.handle,
870 public_key: row.public_key,
871 counter: row.counter,
872 device_type: row.device_type,
873 backed_up: Boolean(row.backed_up),
874 transports: row.transports ? JSON.parse(row.transports) : null,
875 name: row.name,
876 created_at: new Date(row.created_at),
877 last_used_at: row.last_used_at ? new Date(row.last_used_at) : null,
878 };
879 }
880
881 getPasskeyCredentialsByDid(did: string): PasskeyCredential[] {
882 const stmt = this.db.prepare('SELECT * FROM passkey_credentials WHERE did = ? ORDER BY created_at DESC');
883 const rows = stmt.all(did) as Array<{
884 id: string;
885 did: string;
886 handle: string;
887 public_key: string;
888 counter: number;
889 device_type: 'platform' | 'cross-platform' | null;
890 backed_up: number;
891 transports: string | null;
892 name: string | null;
893 created_at: string;
894 last_used_at: string | null;
895 }>;
896 return rows.map(row => ({
897 id: row.id,
898 did: row.did,
899 handle: row.handle,
900 public_key: row.public_key,
901 counter: row.counter,
902 device_type: row.device_type,
903 backed_up: Boolean(row.backed_up),
904 transports: row.transports ? JSON.parse(row.transports) : null,
905 name: row.name,
906 created_at: new Date(row.created_at),
907 last_used_at: row.last_used_at ? new Date(row.last_used_at) : null,
908 }));
909 }
910
911 updatePasskeyCounter(credentialId: string, newCounter: number): void {
912 const stmt = this.db.prepare("UPDATE passkey_credentials SET counter = ?, last_used_at = datetime('now') WHERE id = ?");
913 stmt.run(newCounter, credentialId);
914 }
915
916 renamePasskey(credentialId: string, name: string): void {
917 const stmt = this.db.prepare('UPDATE passkey_credentials SET name = ? WHERE id = ?');
918 stmt.run(name, credentialId);
919 }
920
921 deletePasskeyCredential(credentialId: string): void {
922 const stmt = this.db.prepare('DELETE FROM passkey_credentials WHERE id = ?');
923 stmt.run(credentialId);
924 }
925
926 countPasskeysByDid(did: string): number {
927 const stmt = this.db.prepare('SELECT COUNT(*) as count FROM passkey_credentials WHERE did = ?');
928 const row = stmt.get(did) as { count: number };
929 return row.count;
930 }
931
932 // ===== MFA TOTP Methods =====
933
934 saveMFATOTP(config: Omit<MFATOTPConfig, 'created_at' | 'verified_at'>): void {
935 const stmt = this.db.prepare(`
936 INSERT OR REPLACE INTO mfa_totp (did, secret_encrypted, enabled)
937 VALUES (?, ?, ?)
938 `);
939 stmt.run(config.did, config.secret_encrypted, config.enabled ? 1 : 0);
940 }
941
942 getMFATOTP(did: string): MFATOTPConfig | null {
943 const stmt = this.db.prepare('SELECT * FROM mfa_totp WHERE did = ?');
944 const row = stmt.get(did) as {
945 did: string;
946 secret_encrypted: string;
947 enabled: number;
948 verified_at: string | null;
949 created_at: string;
950 } | undefined;
951 if (!row) return null;
952 return {
953 did: row.did,
954 secret_encrypted: row.secret_encrypted,
955 enabled: Boolean(row.enabled),
956 verified_at: row.verified_at ? new Date(row.verified_at) : null,
957 created_at: new Date(row.created_at),
958 };
959 }
960
961 enableMFATOTP(did: string): void {
962 const stmt = this.db.prepare("UPDATE mfa_totp SET enabled = 1, verified_at = datetime('now') WHERE did = ?");
963 stmt.run(did);
964 }
965
966 disableMFATOTP(did: string): void {
967 const stmt = this.db.prepare('DELETE FROM mfa_totp WHERE did = ?');
968 stmt.run(did);
969 }
970
971 // ===== MFA Backup Codes Methods =====
972
973 saveBackupCodes(did: string, codeHashes: string[]): void {
974 // Delete existing codes first
975 this.db.prepare('DELETE FROM mfa_backup_codes WHERE did = ?').run(did);
976
977 const stmt = this.db.prepare(`
978 INSERT INTO mfa_backup_codes (did, code_hash) VALUES (?, ?)
979 `);
980
981 const insertMany = this.db.transaction((codes: string[]) => {
982 for (const codeHash of codes) {
983 stmt.run(did, codeHash);
984 }
985 });
986
987 insertMany(codeHashes);
988 }
989
990 getUnusedBackupCode(did: string, codeHash: string): MFABackupCode | null {
991 const stmt = this.db.prepare('SELECT * FROM mfa_backup_codes WHERE did = ? AND code_hash = ? AND used = 0');
992 const row = stmt.get(did, codeHash) as {
993 id: number;
994 did: string;
995 code_hash: string;
996 used: number;
997 used_at: string | null;
998 created_at: string;
999 } | undefined;
1000 if (!row) return null;
1001 return {
1002 id: row.id,
1003 did: row.did,
1004 code_hash: row.code_hash,
1005 used: Boolean(row.used),
1006 used_at: row.used_at ? new Date(row.used_at) : null,
1007 created_at: new Date(row.created_at),
1008 };
1009 }
1010
1011 markBackupCodeUsed(id: number): void {
1012 const stmt = this.db.prepare("UPDATE mfa_backup_codes SET used = 1, used_at = datetime('now') WHERE id = ?");
1013 stmt.run(id);
1014 }
1015
1016 countUnusedBackupCodes(did: string): number {
1017 const stmt = this.db.prepare('SELECT COUNT(*) as count FROM mfa_backup_codes WHERE did = ? AND used = 0');
1018 const row = stmt.get(did) as { count: number };
1019 return row.count;
1020 }
1021
1022 // ===== User Email Methods =====
1023
1024 saveUserEmail(email: Omit<UserEmail, 'id' | 'created_at' | 'verified_at'>): void {
1025 const stmt = this.db.prepare(`
1026 INSERT INTO user_emails (did, email, verified, is_primary)
1027 VALUES (?, ?, ?, ?)
1028 ON CONFLICT(did, email) DO UPDATE SET is_primary = excluded.is_primary
1029 `);
1030 stmt.run(email.did, email.email, email.verified ? 1 : 0, email.is_primary ? 1 : 0);
1031 }
1032
1033 getUserEmails(did: string): UserEmail[] {
1034 const stmt = this.db.prepare('SELECT * FROM user_emails WHERE did = ? ORDER BY is_primary DESC, created_at ASC');
1035 const rows = stmt.all(did) as Array<{
1036 id: number;
1037 did: string;
1038 email: string;
1039 verified: number;
1040 verified_at: string | null;
1041 is_primary: number;
1042 created_at: string;
1043 }>;
1044 return rows.map(row => ({
1045 id: row.id,
1046 did: row.did,
1047 email: row.email,
1048 verified: Boolean(row.verified),
1049 verified_at: row.verified_at ? new Date(row.verified_at) : null,
1050 is_primary: Boolean(row.is_primary),
1051 created_at: new Date(row.created_at),
1052 }));
1053 }
1054
1055 getUserByEmail(email: string): { did: string; handle?: string } | null {
1056 const stmt = this.db.prepare(`
1057 SELECT ue.did, um.handle FROM user_emails ue
1058 LEFT JOIN user_mappings um ON ue.did = um.did
1059 WHERE ue.email = ? AND ue.verified = 1
1060 LIMIT 1
1061 `);
1062 const row = stmt.get(email) as { did: string; handle?: string } | undefined;
1063 return row || null;
1064 }
1065
1066 verifyUserEmail(did: string, email: string): void {
1067 const stmt = this.db.prepare("UPDATE user_emails SET verified = 1, verified_at = datetime('now') WHERE did = ? AND email = ?");
1068 stmt.run(did, email);
1069 }
1070
1071 deleteUserEmail(did: string, email: string): void {
1072 const stmt = this.db.prepare('DELETE FROM user_emails WHERE did = ? AND email = ?');
1073 stmt.run(did, email);
1074 }
1075
1076 setPrimaryEmail(did: string, email: string): void {
1077 // Unset all as non-primary first
1078 this.db.prepare('UPDATE user_emails SET is_primary = 0 WHERE did = ?').run(did);
1079 // Set the specified email as primary
1080 this.db.prepare('UPDATE user_emails SET is_primary = 1 WHERE did = ? AND email = ?').run(did, email);
1081 }
1082
1083 // ===== Email Verification Code Methods =====
1084
1085 saveEmailVerificationCode(code: Omit<EmailVerificationCode, 'id' | 'created_at'>): void {
1086 const stmt = this.db.prepare(`
1087 INSERT INTO email_verification_codes (email, code_hash, purpose, expires_at)
1088 VALUES (?, ?, ?, ?)
1089 `);
1090 stmt.run(code.email, code.code_hash, code.purpose, code.expires_at.toISOString());
1091 }
1092
1093 getValidEmailVerificationCode(email: string, codeHash: string, purpose: string): EmailVerificationCode | null {
1094 const stmt = this.db.prepare(`
1095 SELECT * FROM email_verification_codes
1096 WHERE email = ? AND code_hash = ? AND purpose = ? AND used = 0 AND expires_at > datetime('now')
1097 `);
1098 const row = stmt.get(email, codeHash, purpose) as {
1099 id: number;
1100 email: string;
1101 code_hash: string;
1102 purpose: 'verify' | 'recovery';
1103 expires_at: string;
1104 used: number;
1105 created_at: string;
1106 } | undefined;
1107 if (!row) return null;
1108 return {
1109 id: row.id,
1110 email: row.email,
1111 code_hash: row.code_hash,
1112 purpose: row.purpose,
1113 expires_at: new Date(row.expires_at),
1114 used: Boolean(row.used),
1115 created_at: new Date(row.created_at),
1116 };
1117 }
1118
1119 markEmailVerificationCodeUsed(id: number): void {
1120 const stmt = this.db.prepare('UPDATE email_verification_codes SET used = 1 WHERE id = ?');
1121 stmt.run(id);
1122 }
1123
1124 cleanupExpiredEmailVerificationCodes(): number {
1125 const stmt = this.db.prepare("DELETE FROM email_verification_codes WHERE expires_at < datetime('now')");
1126 const result = stmt.run();
1127 return result.changes;
1128 }
1129
1130 // ===== OIDC Client Methods =====
1131
1132 getOIDCClient(clientId: string): OIDCClientConfig | null {
1133 const stmt = this.db.prepare('SELECT * FROM apps WHERE id = ?');
1134 const row = stmt.get(clientId) as {
1135 id: string;
1136 name: string;
1137 client_type: 'legacy' | 'oidc';
1138 hmac_secret?: string;
1139 client_secret?: string;
1140 redirect_uris: string;
1141 grant_types: string;
1142 allowed_scopes: string;
1143 token_ttl_seconds: number;
1144 id_token_ttl_seconds: number;
1145 access_token_ttl_seconds: number;
1146 refresh_token_ttl_seconds: number;
1147 require_pkce: number;
1148 token_endpoint_auth_method: string;
1149 created_at: string;
1150 } | undefined;
1151 if (!row) return null;
1152 return {
1153 id: row.id,
1154 name: row.name,
1155 client_type: row.client_type,
1156 hmac_secret: row.hmac_secret,
1157 client_secret: row.client_secret,
1158 redirect_uris: JSON.parse(row.redirect_uris || '[]'),
1159 grant_types: JSON.parse(row.grant_types || '["authorization_code"]'),
1160 allowed_scopes: JSON.parse(row.allowed_scopes || '["openid"]'),
1161 token_ttl_seconds: row.token_ttl_seconds,
1162 id_token_ttl_seconds: row.id_token_ttl_seconds,
1163 access_token_ttl_seconds: row.access_token_ttl_seconds,
1164 refresh_token_ttl_seconds: row.refresh_token_ttl_seconds,
1165 require_pkce: Boolean(row.require_pkce),
1166 token_endpoint_auth_method: row.token_endpoint_auth_method as 'client_secret_basic' | 'client_secret_post' | 'none',
1167 created_at: new Date(row.created_at),
1168 };
1169 }
1170
1171 upsertOIDCClient(client: Omit<OIDCClientConfig, 'created_at'>): void {
1172 const stmt = this.db.prepare(`
1173 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)
1174 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1175 ON CONFLICT(id) DO UPDATE SET
1176 name = excluded.name,
1177 client_type = excluded.client_type,
1178 hmac_secret = COALESCE(excluded.hmac_secret, hmac_secret),
1179 client_secret = COALESCE(excluded.client_secret, client_secret),
1180 redirect_uris = excluded.redirect_uris,
1181 grant_types = excluded.grant_types,
1182 allowed_scopes = excluded.allowed_scopes,
1183 token_ttl_seconds = excluded.token_ttl_seconds,
1184 id_token_ttl_seconds = excluded.id_token_ttl_seconds,
1185 access_token_ttl_seconds = excluded.access_token_ttl_seconds,
1186 refresh_token_ttl_seconds = excluded.refresh_token_ttl_seconds,
1187 require_pkce = excluded.require_pkce,
1188 token_endpoint_auth_method = excluded.token_endpoint_auth_method,
1189 callback_url = excluded.callback_url
1190 `);
1191 stmt.run(
1192 client.id,
1193 client.name,
1194 client.client_type,
1195 client.hmac_secret,
1196 client.client_secret,
1197 JSON.stringify(client.redirect_uris),
1198 JSON.stringify(client.grant_types),
1199 JSON.stringify(client.allowed_scopes),
1200 client.token_ttl_seconds,
1201 client.id_token_ttl_seconds,
1202 client.access_token_ttl_seconds,
1203 client.refresh_token_ttl_seconds,
1204 client.require_pkce ? 1 : 0,
1205 client.token_endpoint_auth_method,
1206 client.redirect_uris[0] || null
1207 );
1208 }
1209
1210 getAllOIDCClients(): OIDCClientConfig[] {
1211 const stmt = this.db.prepare("SELECT * FROM apps WHERE client_type = 'oidc' ORDER BY created_at DESC");
1212 const rows = stmt.all() as Array<{
1213 id: string;
1214 name: string;
1215 client_type: 'legacy' | 'oidc';
1216 hmac_secret?: string;
1217 client_secret?: string;
1218 redirect_uris: string;
1219 grant_types: string;
1220 allowed_scopes: string;
1221 token_ttl_seconds: number;
1222 id_token_ttl_seconds: number;
1223 access_token_ttl_seconds: number;
1224 refresh_token_ttl_seconds: number;
1225 require_pkce: number;
1226 token_endpoint_auth_method: string;
1227 created_at: string;
1228 }>;
1229 return rows.map(row => ({
1230 id: row.id,
1231 name: row.name,
1232 client_type: row.client_type,
1233 hmac_secret: row.hmac_secret,
1234 client_secret: row.client_secret,
1235 redirect_uris: JSON.parse(row.redirect_uris || '[]'),
1236 grant_types: JSON.parse(row.grant_types || '["authorization_code"]'),
1237 allowed_scopes: JSON.parse(row.allowed_scopes || '["openid"]'),
1238 token_ttl_seconds: row.token_ttl_seconds,
1239 id_token_ttl_seconds: row.id_token_ttl_seconds,
1240 access_token_ttl_seconds: row.access_token_ttl_seconds,
1241 refresh_token_ttl_seconds: row.refresh_token_ttl_seconds,
1242 require_pkce: Boolean(row.require_pkce),
1243 token_endpoint_auth_method: row.token_endpoint_auth_method as 'client_secret_basic' | 'client_secret_post' | 'none',
1244 created_at: new Date(row.created_at),
1245 }));
1246 }
1247
1248 updateOIDCClient(clientId: string, updates: {
1249 client_type?: 'oidc';
1250 client_secret?: string;
1251 redirect_uris?: string[];
1252 grant_types?: string[];
1253 allowed_scopes?: string[];
1254 require_pkce?: boolean;
1255 token_endpoint_auth_method?: string;
1256 id_token_ttl_seconds?: number;
1257 access_token_ttl_seconds?: number;
1258 refresh_token_ttl_seconds?: number;
1259 }): void {
1260 const sets: string[] = [];
1261 const values: unknown[] = [];
1262
1263 if (updates.client_type !== undefined) {
1264 sets.push('client_type = ?');
1265 values.push(updates.client_type);
1266 }
1267 if (updates.client_secret !== undefined) {
1268 sets.push('client_secret = ?');
1269 values.push(updates.client_secret);
1270 }
1271 if (updates.redirect_uris !== undefined) {
1272 sets.push('redirect_uris = ?');
1273 values.push(JSON.stringify(updates.redirect_uris));
1274 }
1275 if (updates.grant_types !== undefined) {
1276 sets.push('grant_types = ?');
1277 values.push(JSON.stringify(updates.grant_types));
1278 }
1279 if (updates.allowed_scopes !== undefined) {
1280 sets.push('allowed_scopes = ?');
1281 values.push(JSON.stringify(updates.allowed_scopes));
1282 }
1283 if (updates.require_pkce !== undefined) {
1284 sets.push('require_pkce = ?');
1285 values.push(updates.require_pkce ? 1 : 0);
1286 }
1287 if (updates.token_endpoint_auth_method !== undefined) {
1288 sets.push('token_endpoint_auth_method = ?');
1289 values.push(updates.token_endpoint_auth_method);
1290 }
1291 if (updates.id_token_ttl_seconds !== undefined) {
1292 sets.push('id_token_ttl_seconds = ?');
1293 values.push(updates.id_token_ttl_seconds);
1294 }
1295 if (updates.access_token_ttl_seconds !== undefined) {
1296 sets.push('access_token_ttl_seconds = ?');
1297 values.push(updates.access_token_ttl_seconds);
1298 sets.push('token_ttl_seconds = ?');
1299 values.push(updates.access_token_ttl_seconds);
1300 }
1301 if (updates.refresh_token_ttl_seconds !== undefined) {
1302 sets.push('refresh_token_ttl_seconds = ?');
1303 values.push(updates.refresh_token_ttl_seconds);
1304 }
1305
1306 if (sets.length > 0) {
1307 values.push(clientId);
1308 const stmt = this.db.prepare(`UPDATE apps SET ${sets.join(', ')} WHERE id = ?`);
1309 stmt.run(...values);
1310 }
1311 }
1312
1313 updateOIDCClientSecret(clientId: string, secretHash: string): void {
1314 const stmt = this.db.prepare('UPDATE apps SET client_secret = ? WHERE id = ?');
1315 stmt.run(secretHash, clientId);
1316 }
1317
1318 // ===== Session Admin Methods =====
1319
1320 getAllActiveSessions(appId?: string, did?: string, limit = 100): ActiveSession[] {
1321 let sql = "SELECT * FROM sessions WHERE expires_at > datetime('now')";
1322 const params: unknown[] = [];
1323
1324 if (appId) {
1325 sql += ' AND app_id = ?';
1326 params.push(appId);
1327 }
1328 if (did) {
1329 sql += ' AND did = ?';
1330 params.push(did);
1331 }
1332
1333 sql += ' ORDER BY created_at DESC LIMIT ?';
1334 params.push(limit);
1335
1336 const stmt = this.db.prepare(sql);
1337 const rows = stmt.all(...params) as Array<{
1338 id: string;
1339 did: string;
1340 handle: string;
1341 user_id: number | null;
1342 app_id: string;
1343 refresh_token?: string;
1344 created_at: string;
1345 expires_at: string;
1346 connection_state: SessionConnectionState;
1347 last_activity: string;
1348 client_info?: string;
1349 }>;
1350
1351 return rows.map((row) => ({
1352 ...row,
1353 created_at: new Date(row.created_at),
1354 expires_at: new Date(row.expires_at),
1355 last_activity: new Date(row.last_activity),
1356 connection_state: row.connection_state || 'pending',
1357 }));
1358 }
1359
1360 revokeAllSessionsForUser(did: string, appId?: string): number {
1361 let sql = 'DELETE FROM sessions WHERE did = ?';
1362 const params: unknown[] = [did];
1363
1364 if (appId) {
1365 sql += ' AND app_id = ?';
1366 params.push(appId);
1367 }
1368
1369 const stmt = this.db.prepare(sql);
1370 const result = stmt.run(...params);
1371 return result.changes;
1372 }
1373
1374 // ===== Forward-Auth Proxy Session Methods =====
1375
1376 createProxySession(session: ProxySession): void {
1377 const stmt = this.db.prepare(`
1378 INSERT INTO proxy_sessions (id, did, handle, created_at, expires_at, last_activity, user_agent, ip_address)
1379 VALUES (?, ?, ?, ?, ?, ?, ?, ?)
1380 `);
1381 stmt.run(
1382 session.id, session.did, session.handle,
1383 session.created_at, session.expires_at, session.last_activity,
1384 session.user_agent, session.ip_address,
1385 );
1386 }
1387
1388 getProxySession(id: string): ProxySession | null {
1389 const stmt = this.db.prepare('SELECT * FROM proxy_sessions WHERE id = ?');
1390 const row = stmt.get(id) as ProxySession | undefined;
1391 return row || null;
1392 }
1393
1394 updateProxySessionActivity(id: string): void {
1395 const now = Math.floor(Date.now() / 1000);
1396 const stmt = this.db.prepare('UPDATE proxy_sessions SET last_activity = ? WHERE id = ?');
1397 stmt.run(now, id);
1398 }
1399
1400 deleteProxySession(id: string): void {
1401 const stmt = this.db.prepare('DELETE FROM proxy_sessions WHERE id = ?');
1402 stmt.run(id);
1403 }
1404
1405 deleteProxySessionsForUser(did: string): number {
1406 const stmt = this.db.prepare('DELETE FROM proxy_sessions WHERE did = ?');
1407 const result = stmt.run(did);
1408 return result.changes;
1409 }
1410
1411 cleanupExpiredProxySessions(): number {
1412 const now = Math.floor(Date.now() / 1000);
1413 const stmt = this.db.prepare('DELETE FROM proxy_sessions WHERE expires_at < ?');
1414 const result = stmt.run(now);
1415 return result.changes;
1416 }
1417
1418 getAllProxySessions(did?: string, limit = 100): ProxySession[] {
1419 let sql = 'SELECT * FROM proxy_sessions WHERE expires_at > ?';
1420 const now = Math.floor(Date.now() / 1000);
1421 const params: unknown[] = [now];
1422
1423 if (did) {
1424 sql += ' AND did = ?';
1425 params.push(did);
1426 }
1427
1428 sql += ' ORDER BY last_activity DESC LIMIT ?';
1429 params.push(limit);
1430
1431 const stmt = this.db.prepare(sql);
1432 return stmt.all(...params) as ProxySession[];
1433 }
1434
1435 // ===== Forward-Auth Allowed Origins Methods =====
1436
1437 addProxyAllowedOrigin(origin: string, name: string): ProxyAllowedOrigin {
1438 const stmt = this.db.prepare(`
1439 INSERT INTO proxy_allowed_origins (origin, name) VALUES (?, ?)
1440 `);
1441 const result = stmt.run(origin, name);
1442 return {
1443 id: result.lastInsertRowid as number,
1444 origin,
1445 name,
1446 created_at: Math.floor(Date.now() / 1000),
1447 };
1448 }
1449
1450 removeProxyAllowedOrigin(id: number): void {
1451 this.db.prepare('DELETE FROM proxy_access_rules WHERE origin_id = ?').run(id);
1452 this.db.prepare('DELETE FROM proxy_allowed_origins WHERE id = ?').run(id);
1453 }
1454
1455 listProxyAllowedOrigins(): ProxyAllowedOrigin[] {
1456 const stmt = this.db.prepare('SELECT * FROM proxy_allowed_origins ORDER BY name ASC');
1457 return stmt.all() as ProxyAllowedOrigin[];
1458 }
1459
1460 isProxyOriginAllowed(origin: string): boolean {
1461 const stmt = this.db.prepare('SELECT 1 FROM proxy_allowed_origins WHERE origin = ? LIMIT 1');
1462 return !!stmt.get(origin);
1463 }
1464
1465 // ===== Forward-Auth Auth Request Methods =====
1466
1467 saveProxyAuthRequest(request: ProxyAuthRequest): void {
1468 const stmt = this.db.prepare(`
1469 INSERT INTO proxy_auth_requests (id, redirect_uri, created_at, expires_at) VALUES (?, ?, ?, ?)
1470 `);
1471 stmt.run(request.id, request.redirect_uri, request.created_at, request.expires_at);
1472 }
1473
1474 getProxyAuthRequest(id: string): ProxyAuthRequest | null {
1475 const stmt = this.db.prepare('SELECT * FROM proxy_auth_requests WHERE id = ?');
1476 const row = stmt.get(id) as ProxyAuthRequest | undefined;
1477 return row || null;
1478 }
1479
1480 deleteProxyAuthRequest(id: string): void {
1481 const stmt = this.db.prepare('DELETE FROM proxy_auth_requests WHERE id = ?');
1482 stmt.run(id);
1483 }
1484
1485 cleanupExpiredProxyAuthRequests(): number {
1486 const now = Math.floor(Date.now() / 1000);
1487 const stmt = this.db.prepare('DELETE FROM proxy_auth_requests WHERE expires_at < ?');
1488 const result = stmt.run(now);
1489 return result.changes;
1490 }
1491
1492 // ===== Forward-Auth Access Rules Methods =====
1493
1494 createProxyAccessRule(rule: Omit<ProxyAccessRule, 'id' | 'created_at'>): ProxyAccessRule {
1495 const stmt = this.db.prepare(`
1496 INSERT INTO proxy_access_rules (origin_id, rule_type, subject_type, subject_value, description)
1497 VALUES (?, ?, ?, ?, ?)
1498 `);
1499 const result = stmt.run(
1500 rule.origin_id, rule.rule_type, rule.subject_type,
1501 rule.subject_value, rule.description,
1502 );
1503 return {
1504 id: result.lastInsertRowid as number,
1505 origin_id: rule.origin_id,
1506 rule_type: rule.rule_type,
1507 subject_type: rule.subject_type,
1508 subject_value: rule.subject_value,
1509 description: rule.description,
1510 created_at: Math.floor(Date.now() / 1000),
1511 };
1512 }
1513
1514 deleteProxyAccessRule(id: number): void {
1515 this.db.prepare('DELETE FROM proxy_access_rules WHERE id = ?').run(id);
1516 }
1517
1518 listProxyAccessRules(originId?: number): ProxyAccessRule[] {
1519 if (originId !== undefined) {
1520 const stmt = this.db.prepare(
1521 'SELECT * FROM proxy_access_rules WHERE origin_id = ? OR origin_id IS NULL ORDER BY rule_type ASC, created_at ASC',
1522 );
1523 return stmt.all(originId) as ProxyAccessRule[];
1524 }
1525 const stmt = this.db.prepare(
1526 'SELECT * FROM proxy_access_rules ORDER BY origin_id ASC, rule_type ASC, created_at ASC',
1527 );
1528 return stmt.all() as ProxyAccessRule[];
1529 }
1530
1531 getProxyAccessRulesForCheck(originId: number): {
1532 denyRules: ProxyAccessRule[];
1533 originAllowRules: ProxyAccessRule[];
1534 globalAllowRules: ProxyAccessRule[];
1535 } {
1536 const stmt = this.db.prepare(
1537 'SELECT * FROM proxy_access_rules WHERE origin_id = ? OR origin_id IS NULL',
1538 );
1539 const rules = stmt.all(originId) as ProxyAccessRule[];
1540
1541 const denyRules: ProxyAccessRule[] = [];
1542 const originAllowRules: ProxyAccessRule[] = [];
1543 const globalAllowRules: ProxyAccessRule[] = [];
1544
1545 for (const rule of rules) {
1546 if (rule.rule_type === 'deny') {
1547 denyRules.push(rule);
1548 } else if (rule.origin_id !== null) {
1549 originAllowRules.push(rule);
1550 } else {
1551 globalAllowRules.push(rule);
1552 }
1553 }
1554
1555 return { denyRules, originAllowRules, globalAllowRules };
1556 }
1557
1558 getOriginIdByOrigin(origin: string): number | null {
1559 const stmt = this.db.prepare('SELECT id FROM proxy_allowed_origins WHERE origin = ? LIMIT 1');
1560 const row = stmt.get(origin) as { id: number } | undefined;
1561 return row?.id ?? null;
1562 }
1563}