konnosDashboard
auth-customer-schema.ts999 lines · main
1/**
2 * Drizzle models for briven auth's customer-schema tables.
3 *
4 * These tables live in each project's own DoltGres DATABASE (`proj_<id>`,
5 * database-per-project), in the `public` schema. The Better Auth Drizzle
6 * adapter consumes the user/session/account/verification models per-tenant —
7 * see `apps/api/src/services/auth-tenant-pool.ts`.
8 *
9 * IMPORTANT (sprint S2.1b): these four models are deliberately shaped to match
10 * **Better Auth's** core schema (field/property names + types it reads/writes),
11 * because the Drizzle adapter looks columns up by the model's property names.
12 * The earlier NextAuth-style shape (emailVerified as a timestamp, no
13 * account.password, verification.valueHash) did not match Better Auth, so
14 * sign-up/sign-in 500'd. Property names here therefore mirror Better Auth:
15 * user: id, name, email, emailVerified(boolean), image, createdAt, updatedAt
16 * session: id, userId, token, expiresAt, ipAddress, userAgent, createdAt, updatedAt
17 * account: id, userId, accountId, providerId, accessToken, refreshToken,
18 * idToken, accessTokenExpiresAt, refreshTokenExpiresAt, scope,
19 * password, createdAt, updatedAt
20 * verification: id, identifier, value, expiresAt, createdAt, updatedAt
21 *
22 * `_briven_auth_audit_log` is briven's own (Better Auth has no audit model);
23 * it is NOT passed to the adapter and keeps its original shape.
24 *
25 * The physical DDL is hand-emitted by `services/auth-provisioning.ts` (kept in
26 * sync with these models). DoltGres notes: no `citext` (email is text + a
27 * UNIQUE index on email); no `CREATE EXTENSION`.
28 */
29
30import { bigint, boolean, index, jsonb, pgTable, text, timestamp, uniqueIndex } from 'drizzle-orm/pg-core';
31
32// Shared helpers — mirror the conventions in apps/api/src/db/schema.ts.
33const id = () => text('id').primaryKey();
34const ts = (name: string) => timestamp(name, { withTimezone: true, mode: 'date' });
35
36/**
37 * Authenticated end-users of a customer's project. One row per unique email
38 * within the tenant. `email` is `text`; case-insensitive uniqueness is enforced
39 * by a UNIQUE index on `email` in the provisioning DDL (DoltGres has no
40 * citext). `emailVerified` is a BOOLEAN — what Better Auth expects.
41 */
42export const authUsers = pgTable(
43 '_briven_auth_users',
44 {
45 id: id(),
46 name: text('name'),
47 email: text('email').notNull(),
48 emailVerified: boolean('email_verified').notNull().default(false),
49 image: text('image'),
50 twoFactorEnabled: boolean('two_factor_enabled').notNull().default(false),
51 createdAt: ts('created_at').notNull().defaultNow(),
52 updatedAt: ts('updated_at').notNull().defaultNow(),
53 },
54 (t) => ({
55 // Cosmetic for query typing; the real UNIQUE(email) index lives in
56 // the provisioning DDL.
57 emailUniq: uniqueIndex('_briven_auth_users_email_uniq').on(t.email),
58 }),
59);
60
61/**
62 * Active sessions. `token` is opaque. Better Auth manages these rows.
63 * `ipAddress` exists for Better Auth compatibility but IP tracking is disabled
64 * (privacy — CLAUDE.md §5.1), so it stays null in practice.
65 */
66export const authSessions = pgTable(
67 '_briven_auth_sessions',
68 {
69 id: id(),
70 userId: text('user_id')
71 .notNull()
72 .references(() => authUsers.id, { onDelete: 'cascade' }),
73 token: text('token').notNull(),
74 expiresAt: ts('expires_at').notNull(),
75 ipAddress: text('ip_address'),
76 userAgent: text('user_agent'),
77 createdAt: ts('created_at').notNull().defaultNow(),
78 updatedAt: ts('updated_at').notNull().defaultNow(),
79 },
80 (t) => ({
81 tokenUniq: uniqueIndex('_briven_auth_sessions_token_uniq').on(t.token),
82 userIdx: index('_briven_auth_sessions_user_idx').on(t.userId),
83 expiresIdx: index('_briven_auth_sessions_expires_idx').on(t.expiresAt),
84 }),
85);
86
87/**
88 * Linked accounts — both the email/password "credential" account (password
89 * hash in `password`) and, later, OAuth accounts (tokens in access/refresh/
90 * idToken). Shaped to Better Auth's account model. OAuth is not wired in v1, so
91 * the token columns are unused-but-present. Uniqueness is per (providerId,
92 * accountId) — Better Auth's natural key.
93 */
94export const authAccounts = pgTable(
95 '_briven_auth_accounts',
96 {
97 id: id(),
98 userId: text('user_id')
99 .notNull()
100 .references(() => authUsers.id, { onDelete: 'cascade' }),
101 accountId: text('account_id').notNull(),
102 providerId: text('provider_id').notNull(),
103 accessToken: text('access_token'),
104 refreshToken: text('refresh_token'),
105 idToken: text('id_token'),
106 accessTokenExpiresAt: ts('access_token_expires_at'),
107 refreshTokenExpiresAt: ts('refresh_token_expires_at'),
108 scope: text('scope'),
109 password: text('password'),
110 createdAt: ts('created_at').notNull().defaultNow(),
111 updatedAt: ts('updated_at').notNull().defaultNow(),
112 },
113 (t) => ({
114 providerPairUniq: uniqueIndex('_briven_auth_accounts_provider_pair_uniq').on(
115 t.providerId,
116 t.accountId,
117 ),
118 userIdx: index('_briven_auth_accounts_user_idx').on(t.userId),
119 }),
120);
121
122/**
123 * Verification rows — magic links, email OTP, password resets, email-verify.
124 * Better Auth creates and consumes these directly, so the shape is Better
125 * Auth's: `{ identifier, value, expiresAt }`.
126 */
127export const authVerificationTokens = pgTable(
128 '_briven_auth_verification_tokens',
129 {
130 id: id(),
131 identifier: text('identifier').notNull(),
132 value: text('value').notNull(),
133 expiresAt: ts('expires_at').notNull(),
134 createdAt: ts('created_at').notNull().defaultNow(),
135 updatedAt: ts('updated_at').notNull().defaultNow(),
136 },
137 (t) => ({
138 identifierIdx: index('_briven_auth_verif_identifier_idx').on(t.identifier),
139 expiresIdx: index('_briven_auth_verif_expires_idx').on(t.expiresAt),
140 }),
141);
142
143/**
144 * JWT signing keys — Better Auth's jwt-plugin `jwks` model. One row per
145 * generated key pair; the plugin creates the first row lazily on the first
146 * `/token` or `/jwks` request. `privateKey` holds the private JWK encrypted
147 * by Better Auth with the instance secret (ciphertext JSON, never a raw
148 * key); `publicKey` is the public JWK served on `GET /v1/auth-tenant/jwks`.
149 * Field names mirror the plugin's schema exactly: publicKey, privateKey,
150 * createdAt (required), expiresAt (optional, key rotation).
151 */
152export const authJwks = pgTable('_briven_auth_jwks', {
153 id: id(),
154 publicKey: text('public_key').notNull(),
155 privateKey: text('private_key').notNull(),
156 createdAt: ts('created_at').notNull().defaultNow(),
157 expiresAt: ts('expires_at'),
158});
159
160/**
161 * Append-only audit log of authentication events within this tenant. Briven's
162 * own table (Better Auth has no audit model), unchanged by the S2.1b rebuild.
163 * `metadata` never includes raw IPs or full emails — CLAUDE.md §5.1.
164 */
165export const authAuditLog = pgTable(
166 '_briven_auth_audit_log',
167 {
168 id: id(),
169 userId: text('user_id').references(() => authUsers.id, { onDelete: 'set null' }),
170 action: text('action').notNull(),
171 ipAddressHash: text('ip_address_hash'),
172 userAgent: text('user_agent'),
173 metadata: jsonb('metadata').notNull().default({}),
174 occurredAt: ts('occurred_at').notNull().defaultNow(),
175 },
176 (t) => ({
177 userOccurredIdx: index('_briven_auth_audit_user_occurred_idx').on(t.userId, t.occurredAt),
178 actionOccurredIdx: index('_briven_auth_audit_action_occurred_idx').on(t.action, t.occurredAt),
179 }),
180);
181
182/**
183 * Better Auth model-name mapping. The engine ships with singular model names;
184 * we map them onto our prefixed tables when instantiating per-tenant.
185 */
186/**
187 * Organizations — customer-facing multi-tenant teams (Phase 2).
188 * These are NOT Better Auth models; they are briven-specific extensions
189 * that customer apps use for org/team management.
190 */
191export const authOrgs = pgTable(
192 '_briven_auth_orgs',
193 {
194 id: id(),
195 name: text('name').notNull(),
196 slug: text('slug').notNull(),
197 logo: text('logo'),
198 metadata: jsonb('metadata').notNull().default({}),
199 createdAt: ts('created_at').notNull().defaultNow(),
200 updatedAt: ts('updated_at').notNull().defaultNow(),
201 },
202 (t) => ({
203 slugUniq: uniqueIndex('_briven_auth_orgs_slug_uniq').on(t.slug),
204 }),
205);
206
207export const authOrgMembers = pgTable(
208 '_briven_auth_org_members',
209 {
210 id: id(),
211 orgId: text('org_id')
212 .notNull()
213 .references(() => authOrgs.id, { onDelete: 'cascade' }),
214 userId: text('user_id')
215 .notNull()
216 .references(() => authUsers.id, { onDelete: 'cascade' }),
217 role: text('role').notNull().default('member'),
218 createdAt: ts('created_at').notNull().defaultNow(),
219 updatedAt: ts('updated_at').notNull().defaultNow(),
220 },
221 (t) => ({
222 pairUniq: uniqueIndex('_briven_auth_org_members_pair_uniq').on(t.orgId, t.userId),
223 userIdx: index('_briven_auth_org_members_user_idx').on(t.userId),
224 }),
225);
226
227export const authOrgInvites = pgTable(
228 '_briven_auth_org_invites',
229 {
230 id: id(),
231 orgId: text('org_id')
232 .notNull()
233 .references(() => authOrgs.id, { onDelete: 'cascade' }),
234 email: text('email').notNull(),
235 role: text('role').notNull().default('member'),
236 token: text('token').notNull(),
237 expiresAt: ts('expires_at').notNull(),
238 invitedBy: text('invited_by').references(() => authUsers.id, { onDelete: 'set null' }),
239 acceptedAt: ts('accepted_at'),
240 createdAt: ts('created_at').notNull().defaultNow(),
241 updatedAt: ts('updated_at').notNull().defaultNow(),
242 },
243 (t) => ({
244 tokenUniq: uniqueIndex('_briven_auth_org_invites_token_uniq').on(t.token),
245 orgIdx: index('_briven_auth_org_invites_org_idx').on(t.orgId),
246 }),
247);
248
249/**
250 * Two-factor authentication — TOTP secrets + backup codes.
251 * Better Auth twoFactor plugin model (model name `twoFactor`).
252 */
253export const authTwoFactors = pgTable(
254 '_briven_auth_two_factors',
255 {
256 id: id(),
257 secret: text('secret').notNull(),
258 backupCodes: text('backup_codes').notNull(),
259 userId: text('user_id')
260 .notNull()
261 .references(() => authUsers.id, { onDelete: 'cascade' }),
262 verified: boolean('verified').notNull().default(true),
263 createdAt: ts('created_at').notNull().defaultNow(),
264 updatedAt: ts('updated_at').notNull().defaultNow(),
265 },
266 (t) => ({
267 userIdx: index('_briven_auth_two_factors_user_idx').on(t.userId),
268 }),
269);
270
271/**
272 * WebAuthn passkeys — FIDO2 credentials.
273 * Better Auth passkey plugin model (model name `passkey`).
274 */
275export const authPasskeys = pgTable(
276 '_briven_auth_passkeys',
277 {
278 id: id(),
279 name: text('name'),
280 publicKey: text('public_key').notNull(),
281 userId: text('user_id')
282 .notNull()
283 .references(() => authUsers.id, { onDelete: 'cascade' }),
284 credentialID: text('credential_id').notNull(),
285 counter: bigint('counter', { mode: 'number' }).notNull().default(0),
286 /** Better Auth passkey plugin — device class (platform / cross-platform). */
287 deviceType: text('device_type').notNull().default('unknown'),
288 /** Better Auth — whether the credential is backed up / multi-device. */
289 backedUp: boolean('backed_up').notNull().default(false),
290 transports: text('transports'),
291 aaguid: text('aaguid'),
292 createdAt: ts('created_at').notNull().defaultNow(),
293 updatedAt: ts('updated_at').notNull().defaultNow(),
294 },
295 (t) => ({
296 userIdx: index('_briven_auth_passkeys_user_idx').on(t.userId),
297 }),
298);
299
300/**
301 * User security state — suspensions, bans, and moderation flags.
302 * Auxiliary table (never ALTER the users table per DoltGres constraints).
303 * One row per user; created on first moderation action.
304 */
305export const authUserSecurity = pgTable(
306 '_briven_auth_user_security',
307 {
308 id: id(),
309 userId: text('user_id')
310 .notNull()
311 .references(() => authUsers.id, { onDelete: 'cascade' }),
312 /** When set, the user cannot sign in or use sessions. */
313 suspendedAt: ts('suspended_at'),
314 suspendedReason: text('suspended_reason'),
315 /** When set, the user is permanently banned. Takes precedence over suspension. */
316 bannedAt: ts('banned_at'),
317 bannedReason: text('banned_reason'),
318 /** Optional ban expiry; null = permanent. */
319 banExpiresAt: ts('ban_expires_at'),
320 createdAt: ts('created_at').notNull().defaultNow(),
321 updatedAt: ts('updated_at').notNull().defaultNow(),
322 },
323 (t) => ({
324 userIdx: index('_briven_auth_user_security_user_idx').on(t.userId),
325 }),
326);
327
328/**
329 * Waitlist — users who requested access when signUpMode = 'waitlist'.
330 * Admins approve/reject via dashboard; approved users receive an email
331 * and can then complete sign-up.
332 */
333export const authWaitlist = pgTable(
334 '_briven_auth_waitlist',
335 {
336 id: id(),
337 email: text('email').notNull(),
338 name: text('name'),
339 /** pending | approved | rejected */
340 status: text('status').notNull().default('pending'),
341 approvedAt: ts('approved_at'),
342 approvedBy: text('approved_by'),
343 rejectedAt: ts('rejected_at'),
344 rejectedReason: text('rejected_reason'),
345 createdAt: ts('created_at').notNull().defaultNow(),
346 updatedAt: ts('updated_at').notNull().defaultNow(),
347 },
348 (t) => ({
349 emailIdx: uniqueIndex('_briven_auth_waitlist_email_idx').on(t.email),
350 statusIdx: index('_briven_auth_waitlist_status_idx').on(t.status),
351 }),
352);
353
354/**
355 * Session activity tracking — for inactivity timeout (Phase 2).
356 * One row per active session. Updated on every authenticated request.
357 * When inactivity timeout is disabled, this table is not written to.
358 */
359export const authSessionActivity = pgTable(
360 '_briven_auth_session_activity',
361 {
362 id: id(),
363 sessionId: text('session_id')
364 .notNull()
365 .references(() => authSessions.id, { onDelete: 'cascade' }),
366 lastActiveAt: ts('last_active_at').notNull().defaultNow(),
367 createdAt: ts('created_at').notNull().defaultNow(),
368 updatedAt: ts('updated_at').notNull().defaultNow(),
369 },
370 (t) => ({
371 sessionIdx: uniqueIndex('_briven_auth_session_activity_session_idx').on(t.sessionId),
372 }),
373);
374
375
376
377/**
378 * Sign-in tokens — single-use JWTs for programmatic session creation.
379 * Admin/backend creates a token; the client exchanges it for a session.
380 * Once used, the token cannot be reused.
381 */
382export const authSigninTokens = pgTable(
383 '_briven_auth_signin_tokens',
384 {
385 id: id(),
386 userId: text('user_id')
387 .notNull()
388 .references(() => authUsers.id, { onDelete: 'cascade' }),
389 tokenHash: text('token_hash').notNull(),
390 expiresAt: ts('expires_at').notNull(),
391 usedAt: ts('used_at'),
392 createdAt: ts('created_at').notNull().defaultNow(),
393 updatedAt: ts('updated_at').notNull().defaultNow(),
394 },
395 (t) => ({
396 tokenHashUniq: uniqueIndex('_briven_auth_signin_tokens_hash_uniq').on(t.tokenHash),
397 userIdx: index('_briven_auth_signin_tokens_user_idx').on(t.userId),
398 expiresIdx: index('_briven_auth_signin_tokens_expires_idx').on(t.expiresAt),
399 }),
400);
401
402export type AuthSigninToken = typeof authSigninTokens.$inferSelect;
403
404export type AuthUser = typeof authUsers.$inferSelect;
405export type AuthSession = typeof authSessions.$inferSelect;
406export type AuthAccount = typeof authAccounts.$inferSelect;
407export type AuthVerificationToken = typeof authVerificationTokens.$inferSelect;
408export type AuthJwk = typeof authJwks.$inferSelect;
409export type AuthAuditLogEntry = typeof authAuditLog.$inferSelect;
410export type AuthOrg = typeof authOrgs.$inferSelect;
411export type AuthOrgMember = typeof authOrgMembers.$inferSelect;
412export type AuthOrgInvite = typeof authOrgInvites.$inferSelect;
413export type AuthTwoFactor = typeof authTwoFactors.$inferSelect;
414export type AuthPasskey = typeof authPasskeys.$inferSelect;
415export type AuthUserSecurity = typeof authUserSecurity.$inferSelect;
416export type AuthWaitlist = typeof authWaitlist.$inferSelect;
417export type AuthSessionActivity = typeof authSessionActivity.$inferSelect;
418
419/**
420 * User metadata — public and private JSON blobs (Phase 3).
421 * Public metadata is readable from frontend + backend.
422 * Private metadata is backend-only.
423 * One row per user; created lazily on first write.
424 */
425export const authUserMetadata = pgTable(
426 '_briven_auth_user_metadata',
427 {
428 id: id(),
429 userId: text('user_id')
430 .notNull()
431 .references(() => authUsers.id, { onDelete: 'cascade' }),
432 publicMetadata: jsonb('public_metadata').notNull().default({}),
433 privateMetadata: jsonb('private_metadata').notNull().default({}),
434 createdAt: ts('created_at').notNull().defaultNow(),
435 updatedAt: ts('updated_at').notNull().defaultNow(),
436 },
437 (t) => ({
438 userIdx: uniqueIndex('_briven_auth_user_metadata_user_idx').on(t.userId),
439 }),
440);
441
442/**
443 * Additional email addresses per user (Phase 3).
444 * Users can have multiple verified emails; one is primary.
445 * The `email` column on `_briven_auth_users` remains the primary
446 * sign-in identifier; this table stores extras.
447 */
448export const authUserEmails = pgTable(
449 '_briven_auth_user_emails',
450 {
451 id: id(),
452 userId: text('user_id')
453 .notNull()
454 .references(() => authUsers.id, { onDelete: 'cascade' }),
455 email: text('email').notNull(),
456 verified: boolean('verified').notNull().default(false),
457 primary: boolean('primary').notNull().default(false),
458 createdAt: ts('created_at').notNull().defaultNow(),
459 updatedAt: ts('updated_at').notNull().defaultNow(),
460 },
461 (t) => ({
462 userEmailUniq: uniqueIndex('_briven_auth_user_emails_user_email_uniq').on(t.userId, t.email),
463 }),
464);
465
466export type AuthUserMetadata = typeof authUserMetadata.$inferSelect;
467export type AuthUserEmail = typeof authUserEmails.$inferSelect;
468
469/**
470 * Phase 4 — Custom roles & permissions per organization.
471 * Each org can define roles with a JSONB array of permission strings.
472 * Default roles (owner, admin, member) are seeded on org creation.
473 */
474export const authOrgRoles = pgTable(
475 '_briven_auth_org_roles',
476 {
477 id: id(),
478 orgId: text('org_id')
479 .notNull()
480 .references(() => authOrgs.id, { onDelete: 'cascade' }),
481 name: text('name').notNull(),
482 permissions: jsonb('permissions').notNull().default([]),
483 /** System roles (owner/admin/member) cannot be deleted or renamed. */
484 isSystem: boolean('is_system').notNull().default(false),
485 createdAt: ts('created_at').notNull().defaultNow(),
486 updatedAt: ts('updated_at').notNull().defaultNow(),
487 },
488 (t) => ({
489 orgNameUniq: uniqueIndex('_briven_auth_org_roles_org_name_uniq').on(t.orgId, t.name),
490 }),
491);
492
493export type AuthOrgRole = typeof authOrgRoles.$inferSelect;
494
495/**
496 * Phase 4 — Verified domains for organizations.
497 * Admins add a domain, receive a DNS TXT challenge, and verify ownership.
498 * When `auto_join_enabled` is true, users whose email domain matches are
499 * automatically added as members on their first sign-in.
500 */
501export const authOrgDomains = pgTable(
502 '_briven_auth_org_domains',
503 {
504 id: id(),
505 orgId: text('org_id')
506 .notNull()
507 .references(() => authOrgs.id, { onDelete: 'cascade' }),
508 domain: text('domain').notNull(),
509 verificationToken: text('verification_token').notNull(),
510 verifiedAt: ts('verified_at'),
511 autoJoinEnabled: boolean('auto_join_enabled').notNull().default(false),
512 createdAt: ts('created_at').notNull().defaultNow(),
513 updatedAt: ts('updated_at').notNull().defaultNow(),
514 },
515 (t) => ({
516 orgDomainUniq: uniqueIndex('_briven_auth_org_domains_org_domain_uniq').on(t.orgId, t.domain),
517 }),
518);
519
520export type AuthOrgDomain = typeof authOrgDomains.$inferSelect;
521
522/**
523 * Phase 4 — Membership requests (request-to-join flow).
524 * Users request to join an org; admins approve or reject.
525 */
526export const authOrgMembershipRequests = pgTable(
527 '_briven_auth_org_membership_requests',
528 {
529 id: id(),
530 orgId: text('org_id')
531 .notNull()
532 .references(() => authOrgs.id, { onDelete: 'cascade' }),
533 userId: text('user_id')
534 .notNull()
535 .references(() => authUsers.id, { onDelete: 'cascade' }),
536 /** pending | approved | rejected */
537 status: text('status').notNull().default('pending'),
538 message: text('message'),
539 requestedAt: ts('requested_at').notNull().defaultNow(),
540 resolvedAt: ts('resolved_at'),
541 resolvedBy: text('resolved_by').references(() => authUsers.id, { onDelete: 'set null' }),
542 createdAt: ts('created_at').notNull().defaultNow(),
543 updatedAt: ts('updated_at').notNull().defaultNow(),
544 },
545 (t) => ({
546 orgUserUniq: uniqueIndex('_briven_auth_org_membership_requests_org_user_uniq').on(t.orgId, t.userId),
547 orgStatusIdx: index('_briven_auth_org_membership_requests_org_status_idx').on(t.orgId, t.status),
548 }),
549);
550
551export type AuthOrgMembershipRequest = typeof authOrgMembershipRequests.$inferSelect;
552
553/**
554 * Phase 4 — Active organization per session.
555 * Tracks which org the user has selected as "active" for each session.
556 * This enables org switching without re-authentication.
557 * Never ALTER the sessions table — auxiliary table per DoltGres rules.
558 */
559export const authSessionOrgs = pgTable(
560 '_briven_auth_session_orgs',
561 {
562 id: id(),
563 sessionId: text('session_id')
564 .notNull()
565 .references(() => authSessions.id, { onDelete: 'cascade' }),
566 orgId: text('org_id')
567 .notNull()
568 .references(() => authOrgs.id, { onDelete: 'cascade' }),
569 createdAt: ts('created_at').notNull().defaultNow(),
570 updatedAt: ts('updated_at').notNull().defaultNow(),
571 },
572 (t) => ({
573 sessionUniq: uniqueIndex('_briven_auth_session_orgs_session_uniq').on(t.sessionId),
574 }),
575);
576
577export type AuthSessionOrg = typeof authSessionOrgs.$inferSelect;
578
579/**
580 * Phase 5 — Enterprise SSO connections (SAML 2.0 + OIDC).
581 * Each connection represents a single enterprise IdP relationship.
582 * `config` is a JSONB blob that holds provider-specific settings:
583 * - SAML: idpMetadataXml, idpSsoUrl, idpCert, spEntityId, etc.
584 * - OIDC: issuer, clientId, authorizationUrl, tokenUrl, userinfoUrl, etc.
585 * `domains` restricts which email domains may use this connection.
586 * `jitEnabled` controls whether unknown users are auto-created on first SSO.
587 */
588export const authSsoConnections = pgTable(
589 '_briven_auth_sso_connections',
590 {
591 id: id(),
592 name: text('name').notNull(),
593 providerType: text('provider_type').notNull(), // 'saml' | 'oidc'
594 config: jsonb('config').notNull().default({}),
595 domains: jsonb('domains').notNull().default([]),
596 jitEnabled: boolean('jit_enabled').notNull().default(true),
597 deactivatedAt: ts('deactivated_at'),
598 createdAt: ts('created_at').notNull().defaultNow(),
599 updatedAt: ts('updated_at').notNull().defaultNow(),
600 },
601 (t) => ({
602 nameIdx: index('_briven_auth_sso_connections_name_idx').on(t.name),
603 }),
604);
605
606export type AuthSsoConnection = typeof authSsoConnections.$inferSelect;
607
608/**
609 * Phase 5 — SSO session tracking.
610 * Links a Better Auth session to the SSO connection that created it.
611 * Enables automatic deprovisioning (revoke all sessions for a connection).
612 * One row per session created via enterprise SSO.
613 */
614export const authSsoSessions = pgTable(
615 '_briven_auth_sso_sessions',
616 {
617 id: id(),
618 sessionId: text('session_id')
619 .notNull()
620 .references(() => authSessions.id, { onDelete: 'cascade' }),
621 connectionId: text('connection_id')
622 .notNull()
623 .references(() => authSsoConnections.id, { onDelete: 'cascade' }),
624 createdAt: ts('created_at').notNull().defaultNow(),
625 updatedAt: ts('updated_at').notNull().defaultNow(),
626 },
627 (t) => ({
628 sessionUniq: uniqueIndex('_briven_auth_sso_sessions_session_uniq').on(t.sessionId),
629 connIdx: index('_briven_auth_sso_sessions_conn_idx').on(t.connectionId),
630 }),
631);
632
633export type AuthSsoSession = typeof authSsoSessions.$inferSelect;
634
635/**
636 * Phase 6.2 — Impersonation session tracking.
637 * Links a session to the admin who created it for support purposes.
638 * Enables audit trails and "stop impersonating" flows.
639 */
640export const authImpersonationSessions = pgTable(
641 '_briven_auth_impersonation_sessions',
642 {
643 id: id(),
644 sessionId: text('session_id')
645 .notNull()
646 .references(() => authSessions.id, { onDelete: 'cascade' }),
647 impersonatedBy: text('impersonated_by').notNull(),
648 targetUserId: text('target_user_id').notNull(),
649 stoppedAt: ts('stopped_at'),
650 createdAt: ts('created_at').notNull().defaultNow(),
651 updatedAt: ts('updated_at').notNull().defaultNow(),
652 },
653 (t) => ({
654 sessionUniq: uniqueIndex('_briven_auth_impersonation_sessions_session_uniq').on(t.sessionId),
655 }),
656);
657
658export type AuthImpersonationSession = typeof authImpersonationSessions.$inferSelect;
659
660/**
661 * Phase 6.3 — Application logs.
662 * Structured operational logs for the auth tenant (errors, warnings, info).
663 * Retention is controlled by the tenant's retention config.
664 */
665export const authAppLogs = pgTable(
666 '_briven_auth_app_logs',
667 {
668 id: id(),
669 level: text('level').notNull(), // 'error' | 'warn' | 'info'
670 action: text('action').notNull(),
671 message: text('message').notNull(),
672 metadata: jsonb('metadata').notNull().default({}),
673 createdAt: ts('created_at').notNull().defaultNow(),
674 },
675 (t) => ({
676 levelCreatedIdx: index('_briven_auth_app_logs_level_created_idx').on(t.level, t.createdAt),
677 actionCreatedIdx: index('_briven_auth_app_logs_action_created_idx').on(t.action, t.createdAt),
678 }),
679);
680
681export type AuthAppLog = typeof authAppLogs.$inferSelect;
682
683/**
684 * Phase 6.6 — Compliance metadata.
685 * Tracks SOC 2, HIPAA BAA, and GDPR DPA status per tenant.
686 */
687export const authCompliance = pgTable(
688 '_briven_auth_compliance',
689 {
690 id: id(),
691 soc2ControlsUrl: text('soc2_controls_url'),
692 hipaaBaaSignedAt: ts('hipaa_baa_signed_at'),
693 hipaaBaaSignedBy: text('hipaa_baa_signed_by'),
694 gdprDpaSignedAt: ts('gdpr_dpa_signed_at'),
695 gdprDpaSignedBy: text('gdpr_dpa_signed_by'),
696 encryptionAtRestEnabled: boolean('encryption_at_rest_enabled').notNull().default(true),
697 createdAt: ts('created_at').notNull().defaultNow(),
698 updatedAt: ts('updated_at').notNull().defaultNow(),
699 },
700);
701
702export type AuthCompliance = typeof authCompliance.$inferSelect;
703
704/**
705 * Phase 7.1 — JWT Templates.
706 * Named claim sets that tenants can define and reference when requesting
707 * a signed JWT. Claims are merged with the default token payload.
708 */
709export const authJwtTemplates = pgTable(
710 '_briven_auth_jwt_templates',
711 {
712 id: id(),
713 name: text('name').notNull(),
714 claims: jsonb('claims').notNull().default({}),
715 createdAt: ts('created_at').notNull().defaultNow(),
716 updatedAt: ts('updated_at').notNull().defaultNow(),
717 },
718 (t) => ({
719 nameUniq: uniqueIndex('_briven_auth_jwt_templates_name_uniq').on(t.name),
720 }),
721);
722
723export type AuthJwtTemplate = typeof authJwtTemplates.$inferSelect;
724
725/**
726 * Phase 7.3 — User usernames.
727 * Auxiliary table for username-based authentication. Each user can have
728 * at most one username. Uniqueness is per tenant.
729 */
730export const authUserUsernames = pgTable(
731 '_briven_auth_user_usernames',
732 {
733 id: id(),
734 userId: text('user_id')
735 .notNull()
736 .references(() => authUsers.id, { onDelete: 'cascade' }),
737 username: text('username').notNull(),
738 createdAt: ts('created_at').notNull().defaultNow(),
739 updatedAt: ts('updated_at').notNull().defaultNow(),
740 },
741 (t) => ({
742 usernameUniq: uniqueIndex('_briven_auth_user_usernames_username_uniq').on(t.username),
743 userIdx: uniqueIndex('_briven_auth_user_usernames_user_idx').on(t.userId),
744 }),
745);
746
747export type AuthUserUsername = typeof authUserUsernames.$inferSelect;
748
749/**
750 * Phase 7.4 — Testing tokens.
751 * Special tokens for E2E test suites that bypass bot protection,
752 * rate limiting, and MFA requirements. Created via admin API;
753 * exchanged for a real session via customer API.
754 */
755export const authTestTokens = pgTable(
756 '_briven_auth_test_tokens',
757 {
758 id: id(),
759 userId: text('user_id')
760 .notNull()
761 .references(() => authUsers.id, { onDelete: 'cascade' }),
762 tokenHash: text('token_hash').notNull(),
763 name: text('name'),
764 expiresAt: ts('expires_at').notNull(),
765 createdAt: ts('created_at').notNull().defaultNow(),
766 updatedAt: ts('updated_at').notNull().defaultNow(),
767 },
768 (t) => ({
769 tokenHashUniq: uniqueIndex('_briven_auth_test_tokens_hash_uniq').on(t.tokenHash),
770 userIdx: index('_briven_auth_test_tokens_user_idx').on(t.userId),
771 }),
772);
773
774export type AuthTestToken = typeof authTestTokens.$inferSelect;
775
776/**
777 * Phase 7.5 — Email templates.
778 * Per-tenant overrides for transactional emails.
779 * When active, the custom template replaces the default briven template.
780 */
781export const authEmailTemplates = pgTable(
782 '_briven_auth_email_templates',
783 {
784 id: id(),
785 name: text('name').notNull(), // 'verification' | 'magic-link' | 'otp' | 'password-reset'
786 subject: text('subject').notNull(),
787 html: text('html').notNull(),
788 text: text('text'),
789 active: boolean('active').notNull().default(true),
790 createdAt: ts('created_at').notNull().defaultNow(),
791 updatedAt: ts('updated_at').notNull().defaultNow(),
792 },
793 (t) => ({
794 nameUniq: uniqueIndex('_briven_auth_email_templates_name_uniq').on(t.name),
795 }),
796);
797
798export type AuthEmailTemplate = typeof authEmailTemplates.$inferSelect;
799
800/**
801 * OIDC state store — short-lived nonce/state pairs for the OIDC enterprise
802 * authorization-code flow. States expire after 10 minutes and are deleted
803 * after a single use.
804 */
805export const authOidcStates = pgTable(
806 '_briven_auth_oidc_states',
807 {
808 id: id(),
809 state: text('state').notNull(),
810 nonce: text('nonce').notNull(),
811 connectionId: text('connection_id').notNull(),
812 expiresAt: ts('expires_at').notNull(),
813 createdAt: ts('created_at').notNull().defaultNow(),
814 },
815 (t) => ({
816 stateUniq: uniqueIndex('_briven_auth_oidc_states_state_uniq').on(t.state),
817 }),
818);
819
820export type AuthOidcState = typeof authOidcStates.$inferSelect;
821
822/**
823 * Device tracking — fingerprint-based new-device detection (Gap Fix #6).
824 * Stores a hashed fingerprint per user + device. No raw IPs (privacy).
825 */
826export const authDevices = pgTable(
827 '_briven_auth_devices',
828 {
829 id: id(),
830 userId: text('user_id')
831 .notNull()
832 .references(() => authUsers.id, { onDelete: 'cascade' }),
833 fingerprint: text('fingerprint').notNull(),
834 userAgent: text('user_agent'),
835 createdAt: ts('created_at').notNull().defaultNow(),
836 updatedAt: ts('updated_at').notNull().defaultNow(),
837 },
838 (t) => ({
839 userFingerprintUniq: uniqueIndex('_briven_auth_devices_user_fingerprint_uniq').on(t.userId, t.fingerprint),
840 userIdx: index('_briven_auth_devices_user_idx').on(t.userId),
841 }),
842);
843
844export type AuthDevice = typeof authDevices.$inferSelect;
845
846/**
847 * Phase 7.1 — Custom JWT signing keys.
848 * Separate from Better Auth's jwks table so we control the key lifecycle
849 * independently. Private key is a JWK string (JSON); encryption at rest
850 * is handled by DoltGres / the storage layer.
851 */
852export const authCustomJwks = pgTable('_briven_auth_custom_jwks', {
853 id: id(),
854 publicKey: text('public_key').notNull(),
855 privateKey: text('private_key').notNull(),
856 createdAt: ts('created_at').notNull().defaultNow(),
857 expiresAt: ts('expires_at'),
858});
859
860export type AuthCustomJwk = typeof authCustomJwks.$inferSelect;
861
862/**
863 * Phase 8.4 — Password policy per tenant.
864 * One row per project; enforced on sign-up and password change.
865 */
866export const authPasswordPolicy = pgTable('_briven_auth_password_policy', {
867 id: id(),
868 minLength: bigint('min_length', { mode: 'number' }).notNull().default(8),
869 requireUppercase: boolean('require_uppercase').notNull().default(false),
870 requireLowercase: boolean('require_lowercase').notNull().default(false),
871 requireNumber: boolean('require_number').notNull().default(false),
872 requireSpecial: boolean('require_special').notNull().default(false),
873 maxAgeDays: bigint('max_age_days', { mode: 'number' }),
874 preventReuse: bigint('prevent_reuse', { mode: 'number' }).notNull().default(0),
875 createdAt: ts('created_at').notNull().defaultNow(),
876 updatedAt: ts('updated_at').notNull().defaultNow(),
877});
878
879export type AuthPasswordPolicy = typeof authPasswordPolicy.$inferSelect;
880
881/**
882 * Phase 8.4 — Password history per user (for reuse prevention).
883 */
884export const authPasswordHistory = pgTable(
885 '_briven_auth_password_history',
886 {
887 id: id(),
888 userId: text('user_id')
889 .notNull()
890 .references(() => authUsers.id, { onDelete: 'cascade' }),
891 passwordHash: text('password_hash').notNull(),
892 createdAt: ts('created_at').notNull().defaultNow(),
893 },
894 (t) => ({
895 userIdx: index('_briven_auth_password_history_user_idx').on(t.userId),
896 }),
897);
898
899export type AuthPasswordHistory = typeof authPasswordHistory.$inferSelect;
900
901export const authSchema = {
902 user: authUsers,
903 session: authSessions,
904 account: authAccounts,
905 verification: authVerificationTokens,
906 // jwt-plugin key store (model name `jwks` is what the plugin looks up).
907 jwks: authJwks,
908 // briven-specific extra — Better Auth doesn't ship an audit-log model.
909 auditLog: authAuditLog,
910 // Organization tables (Phase 2 — not consumed by Better Auth directly).
911 org: authOrgs,
912 orgMember: authOrgMembers,
913 orgInvite: authOrgInvites,
914 // Phase 3 — MFA + Passkeys.
915 twoFactor: authTwoFactors,
916 passkey: authPasskeys,
917 // Phase 1 — Security Foundation.
918 userSecurity: authUserSecurity,
919 waitlist: authWaitlist,
920 // Phase 2 — Session activity tracking.
921 sessionActivity: authSessionActivity,
922 // Gap Fix #6 — Device tracking.
923 device: authDevices,
924 // Phase 3 — User metadata.
925 userMetadata: authUserMetadata,
926 // Phase 3 — Multiple emails per user.
927 userEmail: authUserEmails,
928 // Phase 3 — Sign-in tokens.
929 signinToken: authSigninTokens,
930 // Phase 4 — Organizations & B2B.
931 orgRole: authOrgRoles,
932 orgDomain: authOrgDomains,
933 orgMembershipRequest: authOrgMembershipRequests,
934 sessionOrg: authSessionOrgs,
935 // Phase 5 — Enterprise SSO.
936 ssoConnection: authSsoConnections,
937 ssoSession: authSsoSessions,
938 // Phase 6.2 — Impersonation tracking.
939 impersonationSession: authImpersonationSessions,
940 // Phase 6.3 — Application logs.
941 appLog: authAppLogs,
942 // Phase 6.6 — Compliance metadata.
943 compliance: authCompliance,
944 // Phase 7.1 — JWT Templates.
945 jwtTemplate: authJwtTemplates,
946 customJwks: authCustomJwks,
947 // Phase 7.3 — User usernames.
948 userUsername: authUserUsernames,
949 // Phase 7.4 — Testing tokens.
950 testToken: authTestTokens,
951 // Phase 7.5 — Email templates.
952 emailTemplate: authEmailTemplates,
953 // OIDC state store (Gap Fix #3).
954 oidcState: authOidcStates,
955 // Gap Fix #13 — Password policy.
956 passwordPolicy: authPasswordPolicy,
957 passwordHistory: authPasswordHistory,
958} as const;
959
960/** Audit-action vocabulary. Open union — services can emit any string, but the
961 * listed values are guaranteed to be handled by the admin dashboard's filter
962 * UI without extra wiring. */
963export const AUTH_AUDIT_ACTIONS = [
964 'signup',
965 'signin',
966 'signout',
967 'session.revoked',
968 'account.linked',
969 'account.unlinked',
970 'password.reset',
971 'email.verified',
972] as const;
973export type AuthAuditAction = (typeof AUTH_AUDIT_ACTIONS)[number] | (string & {});
974
975/** Verification-token type vocabulary (used by mailer/UI labels). */
976export const AUTH_VERIFICATION_TYPES = [
977 'magic_link',
978 'otp',
979 'password_reset',
980 'email_verify',
981] as const;
982export type AuthVerificationType = (typeof AUTH_VERIFICATION_TYPES)[number];
983
984/** OAuth provider ids known to briven auth. */
985export const AUTH_OAUTH_PROVIDERS = [
986 'google',
987 'github',
988 'discord',
989 'microsoft',
990 'apple',
991 'twitter',
992 'linkedin',
993 'gitlab',
994 'bitbucket',
995 'dropbox',
996 'facebook',
997 'spotify',
998] as const;
999export type AuthOAuthProvider = (typeof AUTH_OAUTH_PROVIDERS)[number];