konnosDashboard
auth-provisioning.ts829 lines · main
1/**
2 * SQL emitter for the briven auth customer-schema tables.
3 *
4 * Called once per project, the first time a customer clicks **Enable Auth**
5 * in the dashboard. Emits a single transactional batch of statements that:
6 * 1. ensures the `citext` extension exists in the project's schema
7 * 2. creates the six `_briven_auth_*` tables
8 * 3. creates the supporting indexes
9 *
10 * Shape matches BUILD_PLAN.md §3 exactly. Drizzle model definitions live
11 * at `apps/api/src/db/auth-customer-schema.ts` and stay the source of
12 * truth for application-layer reads; this emitter is the source of truth
13 * for the DDL that lands in the customer schema.
14 *
15 * Why not run drizzle-kit migrate? Customer schemas are dynamic — one
16 * per project — and drizzle-kit's snapshot/diff workflow is built for a
17 * fixed schema set. Hand-emitting the DDL keeps the provisioning step
18 * idempotent (IF NOT EXISTS everywhere) and free of the drizzle-kit TTY
19 * blocker tracked in road-to-ga.md §2.9.
20 *
21 * Idempotency: every statement is `IF NOT EXISTS`. Re-running on an
22 * already-provisioned schema is a no-op, which matters because the
23 * provisioning route is idempotent by design (BUILD_PLAN.md §4 admin
24 * endpoint `POST /v1/projects/:id/auth/enable`).
25 */
26
27/**
28 * DDL for `_briven_auth_jwks` — Better Auth's jwt-plugin key store (model
29 * `jwks`: id, publicKey, privateKey, createdAt, expiresAt). The private key
30 * is encrypted at rest by Better Auth with the instance secret before it is
31 * written, so the column holds ciphertext JSON, never a raw key.
32 *
33 * Exported separately (not just inside `renderAuthProvisioningSql`) because
34 * the jwks table postdates many live projects: `auth-tenant-pool.ts` re-runs
35 * this single idempotent statement on tenant-instance boot so existing
36 * projects self-heal without a re-provisioning step. New tables via
37 * `CREATE TABLE IF NOT EXISTS` are safe on DoltGres (ALTER ADD COLUMN
38 * IF NOT EXISTS is not — never retrofit columns onto existing tables).
39 */
40export const AUTH_JWKS_TABLE_SQL = `CREATE TABLE IF NOT EXISTS "_briven_auth_jwks" (
41 id text PRIMARY KEY,
42 public_key text NOT NULL,
43 private_key text NOT NULL,
44 created_at timestamptz NOT NULL DEFAULT now(),
45 expires_at timestamptz
46 )`
47 .replace(/\s+/g, ' ')
48 .trim();
49
50/**
51 * Tables that older "Enable Auth" projects may be missing after schema growth.
52 * CREATE TABLE IF NOT EXISTS only — safe on Doltgres (no ALTER IF NOT EXISTS).
53 * Called on every tenant auth instance boot so magic-link / OTP / passkey
54 * do not 500 on schema drift (Mavi Pay incident 2026-07-21:
55 * missing `_briven_auth_email_templates` + `two_factor_enabled` column).
56 */
57export const AUTH_SELF_HEAL_TABLE_SQL: readonly string[] = [
58 AUTH_JWKS_TABLE_SQL,
59 `CREATE TABLE IF NOT EXISTS "_briven_auth_email_templates" (
60 id text PRIMARY KEY,
61 name text NOT NULL,
62 subject text NOT NULL,
63 html text NOT NULL,
64 text text,
65 active boolean NOT NULL DEFAULT true,
66 created_at timestamptz NOT NULL DEFAULT now(),
67 updated_at timestamptz NOT NULL DEFAULT now()
68 )`,
69 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_email_templates_name_uniq"
70 ON "_briven_auth_email_templates" (name)`,
71 `CREATE TABLE IF NOT EXISTS "_briven_auth_two_factors" (
72 id text PRIMARY KEY,
73 secret text NOT NULL,
74 backup_codes text NOT NULL,
75 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
76 verified boolean NOT NULL DEFAULT true,
77 created_at timestamptz NOT NULL DEFAULT now(),
78 updated_at timestamptz NOT NULL DEFAULT now()
79 )`,
80 `CREATE INDEX IF NOT EXISTS "_briven_auth_two_factors_user_idx"
81 ON "_briven_auth_two_factors" (user_id)`,
82 `CREATE TABLE IF NOT EXISTS "_briven_auth_passkeys" (
83 id text PRIMARY KEY,
84 name text,
85 public_key text NOT NULL,
86 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
87 credential_id text NOT NULL,
88 counter bigint NOT NULL DEFAULT 0,
89 device_type text NOT NULL DEFAULT 'unknown',
90 backed_up boolean NOT NULL DEFAULT false,
91 transports text,
92 aaguid text,
93 created_at timestamptz NOT NULL DEFAULT now(),
94 updated_at timestamptz NOT NULL DEFAULT now()
95 )`,
96 `CREATE INDEX IF NOT EXISTS "_briven_auth_passkeys_user_idx"
97 ON "_briven_auth_passkeys" (user_id)`,
98].map((stmt) => stmt.replace(/\s+/g, ' ').trim());
99
100/** Columns Better Auth passkey plugin requires that older tables may lack. */
101const PASSKEY_COLUMN_HEALS: readonly { column: string; sql: string }[] = [
102 {
103 column: 'device_type',
104 sql: `ALTER TABLE "_briven_auth_passkeys" ADD COLUMN device_type text NOT NULL DEFAULT 'unknown'`,
105 },
106 {
107 column: 'backed_up',
108 sql: `ALTER TABLE "_briven_auth_passkeys" ADD COLUMN backed_up boolean NOT NULL DEFAULT false`,
109 },
110 {
111 column: 'transports',
112 sql: `ALTER TABLE "_briven_auth_passkeys" ADD COLUMN transports text`,
113 },
114 {
115 column: 'aaguid',
116 sql: `ALTER TABLE "_briven_auth_passkeys" ADD COLUMN aaguid text`,
117 },
118];
119
120/**
121 * Minimal pg-like client for self-heal (pool or client with `.query`).
122 */
123export interface AuthSchemaQueryClient {
124 query(sql: string, params?: unknown[]): Promise<unknown>;
125}
126
127/**
128 * Ensure critical auth tables + the `two_factor_enabled` user column exist.
129 * Doltgres does not support `ADD COLUMN IF NOT EXISTS`, so we probe
130 * information_schema then issue a bare ADD COLUMN when missing.
131 *
132 * Failures are logged by the caller — individual statements continue so one
133 * bad DDL does not block the rest.
134 */
135export async function ensureTenantAuthSchema(
136 client: AuthSchemaQueryClient,
137): Promise<{ tablesOk: number; columnAdded: boolean }> {
138 let tablesOk = 0;
139 for (const sql of AUTH_SELF_HEAL_TABLE_SQL) {
140 try {
141 await client.query(sql);
142 tablesOk += 1;
143 } catch {
144 // continue — table may already exist under a partial shape
145 }
146 }
147
148 let columnAdded = false;
149 try {
150 const probe = (await client.query(
151 `SELECT 1 AS ok FROM information_schema.columns
152 WHERE table_schema = 'public'
153 AND table_name = '_briven_auth_users'
154 AND column_name = 'two_factor_enabled'
155 LIMIT 1`,
156 )) as { rows?: Array<{ ok: number }> };
157 const hasCol = Array.isArray(probe?.rows) && probe.rows.length > 0;
158 if (!hasCol) {
159 await client.query(
160 `ALTER TABLE "_briven_auth_users"
161 ADD COLUMN two_factor_enabled boolean NOT NULL DEFAULT false`,
162 );
163 columnAdded = true;
164 }
165 } catch {
166 // users table missing entirely (auth not provisioned) — skip
167 }
168
169 // Passkey plugin fields (deviceType / backedUp / transports / aaguid)
170 for (const heal of PASSKEY_COLUMN_HEALS) {
171 try {
172 const probe = (await client.query(
173 `SELECT 1 AS ok FROM information_schema.columns
174 WHERE table_schema = 'public'
175 AND table_name = '_briven_auth_passkeys'
176 AND column_name = $1
177 LIMIT 1`,
178 [heal.column],
179 )) as { rows?: Array<{ ok: number }> };
180 const hasCol = Array.isArray(probe?.rows) && probe.rows.length > 0;
181 if (!hasCol) {
182 await client.query(heal.sql);
183 columnAdded = true;
184 }
185 } catch {
186 // passkeys table missing — CREATE TABLE above should have created it
187 }
188 }
189
190 return { tablesOk, columnAdded };
191}
192
193/**
194 * Emit the full DDL batch for a project's auth tables. Caller wraps it in
195 * `runInProjectDatabase(projectId, tx)` — the connection is bound to the
196 * project's own DoltGres database (`proj_<id>`, public schema), so no
197 * search_path is needed. Tables are shaped to Better Auth's schema (S2.1b).
198 */
199export function renderAuthProvisioningSql(): string[] {
200 return [
201 // _briven_auth_users — Better-Auth user shape (sprint S2.1b).
202 // DoltGres has no `citext`/`CREATE EXTENSION` and no expression indexes
203 // (`(lower(email))` fails with "two different columns with the same name").
204 // Email is stored `text` + UNIQUE(email); app layer normalizes to lowercase
205 // on write (see auth-security normalizeEmail / Better Auth paths).
206 // `email_verified` is BOOLEAN — what Better Auth reads/writes.
207 `CREATE TABLE IF NOT EXISTS "_briven_auth_users" (
208 id text PRIMARY KEY,
209 name text,
210 email text NOT NULL,
211 email_verified boolean NOT NULL DEFAULT false,
212 image text,
213 two_factor_enabled boolean NOT NULL DEFAULT false,
214 created_at timestamptz NOT NULL DEFAULT now(),
215 updated_at timestamptz NOT NULL DEFAULT now()
216 )`,
217 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_users_email_uniq"
218 ON "_briven_auth_users" (email)`,
219
220 // _briven_auth_sessions — Better-Auth session shape. `ip_address` exists for
221 // compatibility but IP tracking is disabled (privacy), so it stays null.
222 `CREATE TABLE IF NOT EXISTS "_briven_auth_sessions" (
223 id text PRIMARY KEY,
224 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
225 token text NOT NULL,
226 expires_at timestamptz NOT NULL,
227 ip_address text,
228 user_agent text,
229 created_at timestamptz NOT NULL DEFAULT now(),
230 updated_at timestamptz NOT NULL DEFAULT now()
231 )`,
232 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_sessions_token_uniq"
233 ON "_briven_auth_sessions" (token)`,
234 `CREATE INDEX IF NOT EXISTS "_briven_auth_sessions_user_idx"
235 ON "_briven_auth_sessions" (user_id)`,
236 `CREATE INDEX IF NOT EXISTS "_briven_auth_sessions_expires_idx"
237 ON "_briven_auth_sessions" (expires_at)`,
238
239 // _briven_auth_accounts — Better-Auth account shape. The email/password
240 // "credential" account stores the password hash in `password`. OAuth token
241 // columns are present but unused in v1 (no social providers wired).
242 `CREATE TABLE IF NOT EXISTS "_briven_auth_accounts" (
243 id text PRIMARY KEY,
244 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
245 account_id text NOT NULL,
246 provider_id text NOT NULL,
247 access_token text,
248 refresh_token text,
249 id_token text,
250 access_token_expires_at timestamptz,
251 refresh_token_expires_at timestamptz,
252 scope text,
253 password text,
254 created_at timestamptz NOT NULL DEFAULT now(),
255 updated_at timestamptz NOT NULL DEFAULT now()
256 )`,
257 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_accounts_provider_pair_uniq"
258 ON "_briven_auth_accounts" (provider_id, account_id)`,
259 `CREATE INDEX IF NOT EXISTS "_briven_auth_accounts_user_idx"
260 ON "_briven_auth_accounts" (user_id)`,
261
262 // _briven_auth_verification_tokens — Better-Auth verification shape.
263 // Better Auth creates + consumes these rows directly ({identifier,value}).
264 `CREATE TABLE IF NOT EXISTS "_briven_auth_verification_tokens" (
265 id text PRIMARY KEY,
266 identifier text NOT NULL,
267 value text NOT NULL,
268 expires_at timestamptz NOT NULL,
269 created_at timestamptz NOT NULL DEFAULT now(),
270 updated_at timestamptz NOT NULL DEFAULT now()
271 )`,
272 `CREATE INDEX IF NOT EXISTS "_briven_auth_verif_identifier_idx"
273 ON "_briven_auth_verification_tokens" (identifier)`,
274 `CREATE INDEX IF NOT EXISTS "_briven_auth_verif_expires_idx"
275 ON "_briven_auth_verification_tokens" (expires_at)`,
276
277 // _briven_auth_audit_log
278 `CREATE TABLE IF NOT EXISTS "_briven_auth_audit_log" (
279 id text PRIMARY KEY,
280 user_id text REFERENCES "_briven_auth_users"(id) ON DELETE SET NULL,
281 action text NOT NULL,
282 ip_address_hash text,
283 user_agent text,
284 metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
285 occurred_at timestamptz NOT NULL DEFAULT now()
286 )`,
287 `CREATE INDEX IF NOT EXISTS "_briven_auth_audit_user_occurred_idx"
288 ON "_briven_auth_audit_log" (user_id, occurred_at DESC)`,
289 `CREATE INDEX IF NOT EXISTS "_briven_auth_audit_action_occurred_idx"
290 ON "_briven_auth_audit_log" (action, occurred_at DESC)`,
291
292 // _briven_auth_jwks — jwt-plugin signing keys (shared constant above so
293 // the tenant-pool boot ensure and this batch never drift).
294 AUTH_JWKS_TABLE_SQL,
295
296 // ─── Organizations (Phase 2 — Clerk-competitor) ─────────────────────────
297 `CREATE TABLE IF NOT EXISTS "_briven_auth_orgs" (
298 id text PRIMARY KEY,
299 name text NOT NULL,
300 slug text NOT NULL,
301 logo text,
302 metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
303 created_at timestamptz NOT NULL DEFAULT now(),
304 updated_at timestamptz NOT NULL DEFAULT now()
305 )`,
306 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_orgs_slug_uniq"
307 ON "_briven_auth_orgs" (slug)`,
308
309 `CREATE TABLE IF NOT EXISTS "_briven_auth_org_members" (
310 id text PRIMARY KEY,
311 org_id text NOT NULL REFERENCES "_briven_auth_orgs"(id) ON DELETE CASCADE,
312 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
313 role text NOT NULL DEFAULT 'member',
314 created_at timestamptz NOT NULL DEFAULT now(),
315 updated_at timestamptz NOT NULL DEFAULT now()
316 )`,
317 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_org_members_pair_uniq"
318 ON "_briven_auth_org_members" (org_id, user_id)`,
319 `CREATE INDEX IF NOT EXISTS "_briven_auth_org_members_user_idx"
320 ON "_briven_auth_org_members" (user_id)`,
321
322 `CREATE TABLE IF NOT EXISTS "_briven_auth_org_invites" (
323 id text PRIMARY KEY,
324 org_id text NOT NULL REFERENCES "_briven_auth_orgs"(id) ON DELETE CASCADE,
325 email text NOT NULL,
326 role text NOT NULL DEFAULT 'member',
327 token text NOT NULL,
328 expires_at timestamptz NOT NULL,
329 invited_by text REFERENCES "_briven_auth_users"(id) ON DELETE SET NULL,
330 accepted_at timestamptz,
331 created_at timestamptz NOT NULL DEFAULT now(),
332 updated_at timestamptz NOT NULL DEFAULT now()
333 )`,
334 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_org_invites_token_uniq"
335 ON "_briven_auth_org_invites" (token)`,
336 `CREATE INDEX IF NOT EXISTS "_briven_auth_org_invites_org_idx"
337 ON "_briven_auth_org_invites" (org_id)`,
338
339 // ─── Two-Factor (Phase 3) ───────────────────────────────────────────────
340 `CREATE TABLE IF NOT EXISTS "_briven_auth_two_factors" (
341 id text PRIMARY KEY,
342 secret text NOT NULL,
343 backup_codes text NOT NULL,
344 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
345 verified boolean NOT NULL DEFAULT true,
346 created_at timestamptz NOT NULL DEFAULT now(),
347 updated_at timestamptz NOT NULL DEFAULT now()
348 )`,
349 `CREATE INDEX IF NOT EXISTS "_briven_auth_two_factors_user_idx"
350 ON "_briven_auth_two_factors" (user_id)`,
351
352 // ─── Passkeys (Phase 3) ─────────────────────────────────────────────────
353 `CREATE TABLE IF NOT EXISTS "_briven_auth_passkeys" (
354 id text PRIMARY KEY,
355 name text,
356 public_key text NOT NULL,
357 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
358 credential_id text NOT NULL,
359 counter bigint NOT NULL DEFAULT 0,
360 device_type text NOT NULL DEFAULT 'unknown',
361 backed_up boolean NOT NULL DEFAULT false,
362 transports text,
363 aaguid text,
364 created_at timestamptz NOT NULL DEFAULT now(),
365 updated_at timestamptz NOT NULL DEFAULT now()
366 )`,
367 `CREATE INDEX IF NOT EXISTS "_briven_auth_passkeys_user_idx"
368 ON "_briven_auth_passkeys" (user_id)`,
369
370 // ─── User Security (Phase 1 — suspensions, bans) ────────────────────────
371 `CREATE TABLE IF NOT EXISTS "_briven_auth_user_security" (
372 id text PRIMARY KEY,
373 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
374 suspended_at timestamptz,
375 suspended_reason text,
376 banned_at timestamptz,
377 banned_reason text,
378 ban_expires_at timestamptz,
379 created_at timestamptz NOT NULL DEFAULT now(),
380 updated_at timestamptz NOT NULL DEFAULT now()
381 )`,
382 `CREATE INDEX IF NOT EXISTS "_briven_auth_user_security_user_idx"
383 ON "_briven_auth_user_security" (user_id)`,
384
385 // ─── Waitlist (Phase 1 — waitlist mode) ─────────────────────────────────
386 `CREATE TABLE IF NOT EXISTS "_briven_auth_waitlist" (
387 id text PRIMARY KEY,
388 email text NOT NULL,
389 name text,
390 status text NOT NULL DEFAULT 'pending',
391 approved_at timestamptz,
392 approved_by text,
393 rejected_at timestamptz,
394 rejected_reason text,
395 created_at timestamptz NOT NULL DEFAULT now(),
396 updated_at timestamptz NOT NULL DEFAULT now()
397 )`,
398 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_waitlist_email_idx"
399 ON "_briven_auth_waitlist" (email)`,
400 `CREATE INDEX IF NOT EXISTS "_briven_auth_waitlist_status_idx"
401 ON "_briven_auth_waitlist" (status)`,
402
403 // ─── Session Activity (Phase 2 — inactivity timeout) ────────────────────
404 `CREATE TABLE IF NOT EXISTS "_briven_auth_session_activity" (
405 id text PRIMARY KEY,
406 session_id text NOT NULL REFERENCES "_briven_auth_sessions"(id) ON DELETE CASCADE,
407 last_active_at timestamptz NOT NULL DEFAULT now(),
408 created_at timestamptz NOT NULL DEFAULT now(),
409 updated_at timestamptz NOT NULL DEFAULT now()
410 )`,
411 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_session_activity_session_idx"
412 ON "_briven_auth_session_activity" (session_id)`,
413
414 // ─── Device Tracking (Gap Fix #6) ───────────────────────────────────────
415 `CREATE TABLE IF NOT EXISTS "_briven_auth_devices" (
416 id text PRIMARY KEY,
417 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
418 fingerprint text NOT NULL,
419 user_agent text,
420 created_at timestamptz NOT NULL DEFAULT now(),
421 updated_at timestamptz NOT NULL DEFAULT now()
422 )`,
423 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_devices_user_fingerprint_uniq"
424 ON "_briven_auth_devices" (user_id, fingerprint)`,
425 `CREATE INDEX IF NOT EXISTS "_briven_auth_devices_user_idx"
426 ON "_briven_auth_devices" (user_id)`,
427
428 // ─── User Metadata (Phase 3) ────────────────────────────────────────────
429 `CREATE TABLE IF NOT EXISTS "_briven_auth_user_metadata" (
430 id text PRIMARY KEY,
431 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
432 public_metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
433 private_metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
434 created_at timestamptz NOT NULL DEFAULT now(),
435 updated_at timestamptz NOT NULL DEFAULT now()
436 )`,
437 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_user_metadata_user_idx"
438 ON "_briven_auth_user_metadata" (user_id)`,
439
440 // ─── User Emails (Phase 3 — multiple emails per user) ───────────────────
441 // `"primary"` must be quoted — unquoted PRIMARY is reserved SQL, and
442 // DoltGres errors with: at or near "boolean": syntax error (2026-07-20).
443 `CREATE TABLE IF NOT EXISTS "_briven_auth_user_emails" (
444 id text PRIMARY KEY,
445 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
446 email text NOT NULL,
447 verified boolean NOT NULL DEFAULT false,
448 "primary" boolean NOT NULL DEFAULT false,
449 created_at timestamptz NOT NULL DEFAULT now(),
450 updated_at timestamptz NOT NULL DEFAULT now()
451 )`,
452 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_user_emails_user_email_uniq"
453 ON "_briven_auth_user_emails" (user_id, email)`,
454
455 // ─── Sign-in Tokens (Phase 3 — single-use programmatic session creation)
456 `CREATE TABLE IF NOT EXISTS "_briven_auth_signin_tokens" (
457 id text PRIMARY KEY,
458 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
459 token_hash text NOT NULL,
460 expires_at timestamptz NOT NULL,
461 used_at timestamptz,
462 created_at timestamptz NOT NULL DEFAULT now(),
463 updated_at timestamptz NOT NULL DEFAULT now()
464 )`,
465 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_signin_tokens_hash_uniq"
466 ON "_briven_auth_signin_tokens" (token_hash)`,
467 `CREATE INDEX IF NOT EXISTS "_briven_auth_signin_tokens_user_idx"
468 ON "_briven_auth_signin_tokens" (user_id)`,
469 `CREATE INDEX IF NOT EXISTS "_briven_auth_signin_tokens_expires_idx"
470 ON "_briven_auth_signin_tokens" (expires_at)`,
471
472 // ─── Organization Roles (Phase 4 — custom roles & permissions)
473 `CREATE TABLE IF NOT EXISTS "_briven_auth_org_roles" (
474 id text PRIMARY KEY,
475 org_id text NOT NULL REFERENCES "_briven_auth_orgs"(id) ON DELETE CASCADE,
476 name text NOT NULL,
477 permissions jsonb NOT NULL DEFAULT '[]'::jsonb,
478 is_system boolean NOT NULL DEFAULT false,
479 created_at timestamptz NOT NULL DEFAULT now(),
480 updated_at timestamptz NOT NULL DEFAULT now()
481 )`,
482 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_org_roles_org_name_uniq"
483 ON "_briven_auth_org_roles" (org_id, name)`,
484
485 // ─── Organization Domains (Phase 4 — domain verification + auto-join)
486 `CREATE TABLE IF NOT EXISTS "_briven_auth_org_domains" (
487 id text PRIMARY KEY,
488 org_id text NOT NULL REFERENCES "_briven_auth_orgs"(id) ON DELETE CASCADE,
489 domain text NOT NULL,
490 verification_token text NOT NULL,
491 verified_at timestamptz,
492 auto_join_enabled boolean NOT NULL DEFAULT false,
493 created_at timestamptz NOT NULL DEFAULT now(),
494 updated_at timestamptz NOT NULL DEFAULT now()
495 )`,
496 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_org_domains_org_domain_uniq"
497 ON "_briven_auth_org_domains" (org_id, domain)`,
498
499 // ─── Organization Membership Requests (Phase 4 — request to join)
500 `CREATE TABLE IF NOT EXISTS "_briven_auth_org_membership_requests" (
501 id text PRIMARY KEY,
502 org_id text NOT NULL REFERENCES "_briven_auth_orgs"(id) ON DELETE CASCADE,
503 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
504 status text NOT NULL DEFAULT 'pending',
505 message text,
506 requested_at timestamptz NOT NULL DEFAULT now(),
507 resolved_at timestamptz,
508 resolved_by text REFERENCES "_briven_auth_users"(id) ON DELETE SET NULL,
509 created_at timestamptz NOT NULL DEFAULT now(),
510 updated_at timestamptz NOT NULL DEFAULT now()
511 )`,
512 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_org_membership_requests_org_user_uniq"
513 ON "_briven_auth_org_membership_requests" (org_id, user_id)`,
514 `CREATE INDEX IF NOT EXISTS "_briven_auth_org_membership_requests_org_status_idx"
515 ON "_briven_auth_org_membership_requests" (org_id, status)`,
516
517 // ─── Session Active Organization (Phase 4 — org switch without re-auth)
518 `CREATE TABLE IF NOT EXISTS "_briven_auth_session_orgs" (
519 id text PRIMARY KEY,
520 session_id text NOT NULL REFERENCES "_briven_auth_sessions"(id) ON DELETE CASCADE,
521 org_id text NOT NULL REFERENCES "_briven_auth_orgs"(id) ON DELETE CASCADE,
522 created_at timestamptz NOT NULL DEFAULT now(),
523 updated_at timestamptz NOT NULL DEFAULT now()
524 )`,
525 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_session_orgs_session_uniq"
526 ON "_briven_auth_session_orgs" (session_id)`,
527
528 // ─── Enterprise SSO Connections (Phase 5 — SAML + OIDC)
529 `CREATE TABLE IF NOT EXISTS "_briven_auth_sso_connections" (
530 id text PRIMARY KEY,
531 name text NOT NULL,
532 provider_type text NOT NULL,
533 config jsonb NOT NULL DEFAULT '{}'::jsonb,
534 domains jsonb NOT NULL DEFAULT '[]'::jsonb,
535 jit_enabled boolean NOT NULL DEFAULT true,
536 deactivated_at timestamptz,
537 created_at timestamptz NOT NULL DEFAULT now(),
538 updated_at timestamptz NOT NULL DEFAULT now()
539 )`,
540 `CREATE INDEX IF NOT EXISTS "_briven_auth_sso_connections_name_idx"
541 ON "_briven_auth_sso_connections" (name)`,
542
543 // ─── SSO Session Tracking (Phase 5 — deprovisioning)
544 `CREATE TABLE IF NOT EXISTS "_briven_auth_sso_sessions" (
545 id text PRIMARY KEY,
546 session_id text NOT NULL REFERENCES "_briven_auth_sessions"(id) ON DELETE CASCADE,
547 connection_id text NOT NULL REFERENCES "_briven_auth_sso_connections"(id) ON DELETE CASCADE,
548 created_at timestamptz NOT NULL DEFAULT now(),
549 updated_at timestamptz NOT NULL DEFAULT now()
550 )`,
551 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_sso_sessions_session_uniq"
552 ON "_briven_auth_sso_sessions" (session_id)`,
553 `CREATE INDEX IF NOT EXISTS "_briven_auth_sso_sessions_conn_idx"
554 ON "_briven_auth_sso_sessions" (connection_id)`,
555
556 // ─── Impersonation Sessions (Phase 6.2 — support impersonation audit)
557 `CREATE TABLE IF NOT EXISTS "_briven_auth_impersonation_sessions" (
558 id text PRIMARY KEY,
559 session_id text NOT NULL REFERENCES "_briven_auth_sessions"(id) ON DELETE CASCADE,
560 impersonated_by text NOT NULL,
561 target_user_id text NOT NULL,
562 stopped_at timestamptz,
563 created_at timestamptz NOT NULL DEFAULT now(),
564 updated_at timestamptz NOT NULL DEFAULT now()
565 )`,
566 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_impersonation_sessions_session_uniq"
567 ON "_briven_auth_impersonation_sessions" (session_id)`,
568
569 // ─── Application Logs (Phase 6.3 — structured operational logs)
570 `CREATE TABLE IF NOT EXISTS "_briven_auth_app_logs" (
571 id text PRIMARY KEY,
572 level text NOT NULL,
573 action text NOT NULL,
574 message text NOT NULL,
575 metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
576 created_at timestamptz NOT NULL DEFAULT now()
577 )`,
578 `CREATE INDEX IF NOT EXISTS "_briven_auth_app_logs_level_created_idx"
579 ON "_briven_auth_app_logs" (level, created_at DESC)`,
580 `CREATE INDEX IF NOT EXISTS "_briven_auth_app_logs_action_created_idx"
581 ON "_briven_auth_app_logs" (action, created_at DESC)`,
582
583 // ─── Compliance Metadata (Phase 6.6 — SOC 2 / HIPAA / GDPR)
584 `CREATE TABLE IF NOT EXISTS "_briven_auth_compliance" (
585 id text PRIMARY KEY,
586 soc2_controls_url text,
587 hipaa_baa_signed_at timestamptz,
588 hipaa_baa_signed_by text,
589 gdpr_dpa_signed_at timestamptz,
590 gdpr_dpa_signed_by text,
591 encryption_at_rest_enabled boolean NOT NULL DEFAULT true,
592 created_at timestamptz NOT NULL DEFAULT now(),
593 updated_at timestamptz NOT NULL DEFAULT now()
594 )`,
595
596 // ─── JWT Templates (Phase 7.1 — named claim sets for custom tokens)
597 `CREATE TABLE IF NOT EXISTS "_briven_auth_jwt_templates" (
598 id text PRIMARY KEY,
599 name text NOT NULL,
600 claims jsonb NOT NULL DEFAULT '{}'::jsonb,
601 created_at timestamptz NOT NULL DEFAULT now(),
602 updated_at timestamptz NOT NULL DEFAULT now()
603 )`,
604 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_jwt_templates_name_uniq"
605 ON "_briven_auth_jwt_templates" (name)`,
606
607 // ─── Custom JWT Signing Keys (Phase 7.1 — independent from Better Auth jwks)
608 `CREATE TABLE IF NOT EXISTS "_briven_auth_custom_jwks" (
609 id text PRIMARY KEY,
610 public_key text NOT NULL,
611 private_key text NOT NULL,
612 created_at timestamptz NOT NULL DEFAULT now(),
613 expires_at timestamptz
614 )`,
615
616 // ─── User Usernames (Phase 7.3 — username-based authentication)
617 `CREATE TABLE IF NOT EXISTS "_briven_auth_user_usernames" (
618 id text PRIMARY KEY,
619 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
620 username text NOT NULL,
621 created_at timestamptz NOT NULL DEFAULT now(),
622 updated_at timestamptz NOT NULL DEFAULT now()
623 )`,
624 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_user_usernames_username_uniq"
625 ON "_briven_auth_user_usernames" (username)`,
626 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_user_usernames_user_idx"
627 ON "_briven_auth_user_usernames" (user_id)`,
628
629 // ─── Testing Tokens (Phase 7.4 — E2E test bypass tokens)
630 `CREATE TABLE IF NOT EXISTS "_briven_auth_test_tokens" (
631 id text PRIMARY KEY,
632 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
633 token_hash text NOT NULL,
634 name text,
635 expires_at timestamptz NOT NULL,
636 created_at timestamptz NOT NULL DEFAULT now(),
637 updated_at timestamptz NOT NULL DEFAULT now()
638 )`,
639 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_test_tokens_hash_uniq"
640 ON "_briven_auth_test_tokens" (token_hash)`,
641 `CREATE INDEX IF NOT EXISTS "_briven_auth_test_tokens_user_idx"
642 ON "_briven_auth_test_tokens" (user_id)`,
643
644 // ─── Email Templates (Phase 7.5 — per-tenant transactional email overrides)
645 `CREATE TABLE IF NOT EXISTS "_briven_auth_email_templates" (
646 id text PRIMARY KEY,
647 name text NOT NULL,
648 subject text NOT NULL,
649 html text NOT NULL,
650 text text,
651 active boolean NOT NULL DEFAULT true,
652 created_at timestamptz NOT NULL DEFAULT now(),
653 updated_at timestamptz NOT NULL DEFAULT now()
654 )`,
655 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_email_templates_name_uniq"
656 ON "_briven_auth_email_templates" (name)`,
657
658 // ─── OIDC State Store (Gap Fix #3 — OIDC enterprise authorization flow)
659 `CREATE TABLE IF NOT EXISTS "_briven_auth_oidc_states" (
660 id text PRIMARY KEY,
661 state text NOT NULL,
662 nonce text NOT NULL,
663 connection_id text NOT NULL,
664 expires_at timestamptz NOT NULL,
665 created_at timestamptz NOT NULL DEFAULT now()
666 )`,
667 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_oidc_states_state_uniq"
668 ON "_briven_auth_oidc_states" (state)`,
669
670 // ─── Password Policy (Gap Fix #13 — password complexity & expiration)
671 `CREATE TABLE IF NOT EXISTS "_briven_auth_password_policy" (
672 id text PRIMARY KEY,
673 min_length bigint NOT NULL DEFAULT 8,
674 require_uppercase boolean NOT NULL DEFAULT false,
675 require_lowercase boolean NOT NULL DEFAULT false,
676 require_number boolean NOT NULL DEFAULT false,
677 require_special boolean NOT NULL DEFAULT false,
678 max_age_days bigint,
679 prevent_reuse bigint NOT NULL DEFAULT 0,
680 created_at timestamptz NOT NULL DEFAULT now(),
681 updated_at timestamptz NOT NULL DEFAULT now()
682 )`,
683
684 // ─── Password History (Gap Fix #13 — prevent password reuse)
685 `CREATE TABLE IF NOT EXISTS "_briven_auth_password_history" (
686 id text PRIMARY KEY,
687 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
688 password_hash text NOT NULL,
689 created_at timestamptz NOT NULL DEFAULT now()
690 )`,
691 `CREATE INDEX IF NOT EXISTS "_briven_auth_password_history_user_idx"
692 ON "_briven_auth_password_history" (user_id)`,
693
694 // ─── Force password reset (Sprint S3 — admin-flagged next sign-in) ─────
695 `CREATE TABLE IF NOT EXISTS "_briven_auth_password_force_reset" (
696 user_id text PRIMARY KEY REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
697 reason text,
698 forced_at timestamptz NOT NULL DEFAULT now()
699 )`,
700
701 // ─── SCIM 2.0 (Phase 9 — enterprise directory provisioning) ────────────
702 ...AUTH_SCIM_DDL_SQL,
703
704 ].map((stmt) => stmt.replace(/\s+/g, ' ').trim());
705}
706
707/**
708 * SCIM tables — also exported so existing tenants can self-heal without a
709 * full re-Enable Auth (same pattern as AUTH_JWKS_TABLE_SQL).
710 */
711export const AUTH_SCIM_DDL_SQL: readonly string[] = [
712 `CREATE TABLE IF NOT EXISTS "_briven_auth_scim_tokens" (
713 id text PRIMARY KEY,
714 name text NOT NULL,
715 hash text NOT NULL,
716 prefix text NOT NULL,
717 suffix text NOT NULL,
718 last_used_at timestamptz,
719 created_at timestamptz NOT NULL DEFAULT now(),
720 revoked_at timestamptz
721 )`,
722 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_scim_tokens_hash_uniq"
723 ON "_briven_auth_scim_tokens" (hash)`,
724 `CREATE TABLE IF NOT EXISTS "_briven_auth_scim_users" (
725 id text PRIMARY KEY,
726 user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE,
727 external_id text,
728 user_name text NOT NULL,
729 active boolean NOT NULL DEFAULT true,
730 display_name text,
731 raw jsonb NOT NULL DEFAULT '{}'::jsonb,
732 created_at timestamptz NOT NULL DEFAULT now(),
733 updated_at timestamptz NOT NULL DEFAULT now()
734 )`,
735 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_scim_users_user_uniq"
736 ON "_briven_auth_scim_users" (user_id)`,
737 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_scim_users_username_uniq"
738 ON "_briven_auth_scim_users" (user_name)`,
739 `CREATE INDEX IF NOT EXISTS "_briven_auth_scim_users_external_idx"
740 ON "_briven_auth_scim_users" (external_id)`,
741 `CREATE TABLE IF NOT EXISTS "_briven_auth_scim_groups" (
742 id text PRIMARY KEY,
743 display_name text NOT NULL,
744 external_id text,
745 active boolean NOT NULL DEFAULT true,
746 raw jsonb NOT NULL DEFAULT '{}'::jsonb,
747 created_at timestamptz NOT NULL DEFAULT now(),
748 updated_at timestamptz NOT NULL DEFAULT now()
749 )`,
750 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_scim_groups_name_uniq"
751 ON "_briven_auth_scim_groups" (display_name)`,
752 `CREATE TABLE IF NOT EXISTS "_briven_auth_scim_group_members" (
753 group_id text NOT NULL REFERENCES "_briven_auth_scim_groups"(id) ON DELETE CASCADE,
754 member_id text NOT NULL,
755 created_at timestamptz NOT NULL DEFAULT now(),
756 PRIMARY KEY (group_id, member_id)
757 )`,
758 // SCIM group displayName → Briven org + role (Phase 9.2)
759 `CREATE TABLE IF NOT EXISTS "_briven_auth_scim_role_maps" (
760 id text PRIMARY KEY,
761 display_name text NOT NULL,
762 org_id text NOT NULL,
763 role text NOT NULL DEFAULT 'member',
764 created_at timestamptz NOT NULL DEFAULT now(),
765 updated_at timestamptz NOT NULL DEFAULT now()
766 )`,
767 `CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_scim_role_maps_name_uniq"
768 ON "_briven_auth_scim_role_maps" (display_name)`,
769 // OIDC PKCE verifier storage (never ALTER oidc_states — new table)
770 `CREATE TABLE IF NOT EXISTS "_briven_auth_oidc_pkce" (
771 state text PRIMARY KEY,
772 code_verifier text NOT NULL,
773 redirect_to text,
774 expires_at timestamptz NOT NULL,
775 created_at timestamptz NOT NULL DEFAULT now()
776 )`,
777].map((stmt) => stmt.replace(/\s+/g, ' ').trim());
778
779/**
780 * Names of every table this emitter creates. Useful for the "is auth
781 * provisioned?" probe + for the `disable` endpoint's drop-without-data
782 * path (which intentionally does NOT drop — disable just flips the
783 * meta flag).
784 */
785export const AUTH_TABLES = [
786 '_briven_auth_users',
787 '_briven_auth_sessions',
788 '_briven_auth_accounts',
789 '_briven_auth_verification_tokens',
790 '_briven_auth_audit_log',
791 '_briven_auth_jwks',
792 '_briven_auth_orgs',
793 '_briven_auth_org_members',
794 '_briven_auth_org_invites',
795 '_briven_auth_two_factors',
796 '_briven_auth_passkeys',
797 '_briven_auth_user_security',
798 '_briven_auth_waitlist',
799 '_briven_auth_session_activity',
800 '_briven_auth_devices',
801 '_briven_auth_user_metadata',
802 '_briven_auth_user_emails',
803 '_briven_auth_signin_tokens',
804 '_briven_auth_org_roles',
805 '_briven_auth_org_domains',
806 '_briven_auth_org_membership_requests',
807 '_briven_auth_session_orgs',
808 '_briven_auth_sso_connections',
809 '_briven_auth_sso_sessions',
810 '_briven_auth_impersonation_sessions',
811 '_briven_auth_app_logs',
812 '_briven_auth_compliance',
813 '_briven_auth_jwt_templates',
814 '_briven_auth_custom_jwks',
815 '_briven_auth_user_usernames',
816 '_briven_auth_test_tokens',
817 '_briven_auth_email_templates',
818 '_briven_auth_oidc_states',
819 '_briven_auth_password_policy',
820 '_briven_auth_password_history',
821 '_briven_auth_password_force_reset',
822 '_briven_auth_scim_tokens',
823 '_briven_auth_scim_users',
824 '_briven_auth_scim_groups',
825 '_briven_auth_scim_group_members',
826 '_briven_auth_scim_role_maps',
827 '_briven_auth_oidc_pkce',
828] as const;
829export type AuthTableName = (typeof AUTH_TABLES)[number];