konnosDashboard
auth-provisioning.test.ts155 lines · main
1import { describe, expect, test } from 'bun:test';
2
3import { AUTH_TABLES, renderAuthProvisioningSql } from './auth-provisioning.js';
4
5// NOTE: this file previously asserted MySQL DDL (backtick quoting,
6// COLLATE utf8mb4_unicode_ci, JSON_OBJECT()) while the emitter produces
7// Postgres/DoltGres DDL — so it never matched reality. Rewritten to assert the
8// actual output, and to lock DoltGres-safe choices (no citext; no expression
9// indexes; reserved column names quoted).
10describe('auth-provisioning — DDL emitter (Postgres/DoltGres)', () => {
11 const stmts = renderAuthProvisioningSql();
12
13 test('S2.3: never emits CREATE EXTENSION / citext (unsupported on DoltGres)', () => {
14 for (const s of stmts) {
15 expect(s).not.toContain('CREATE EXTENSION');
16 expect(s).not.toContain('citext');
17 }
18 });
19
20 test('first statement creates the "_briven_auth_users" table', () => {
21 expect(stmts[0]).toContain('CREATE TABLE IF NOT EXISTS "_briven_auth_users"');
22 });
23
24 test('emits all six _briven_auth_* tables with IF NOT EXISTS', () => {
25 for (const table of AUTH_TABLES) {
26 const hasCreateTable = stmts.some((s) =>
27 s.startsWith(`CREATE TABLE IF NOT EXISTS "${table}"`),
28 );
29 expect(hasCreateTable).toBe(true);
30 }
31 });
32
33 test('every CREATE INDEX uses IF NOT EXISTS (idempotency)', () => {
34 const indexStmts = stmts.filter((s) => s.includes('CREATE') && s.includes('INDEX'));
35 expect(indexStmts.length).toBeGreaterThan(0);
36 for (const s of indexStmts) {
37 expect(s).toContain('IF NOT EXISTS');
38 }
39 });
40
41 test('S2.3: users.email is text with a UNIQUE index on (email) — no expression indexes', () => {
42 const usersTable = stmts.find((s) =>
43 s.startsWith('CREATE TABLE IF NOT EXISTS "_briven_auth_users"'),
44 );
45 expect(usersTable).toBeDefined();
46 expect(usersTable!).toContain('email text NOT NULL');
47 const uniq = stmts.find(
48 (s) =>
49 s.includes('_briven_auth_users_email_uniq') &&
50 s.includes('UNIQUE') &&
51 s.includes('(email)'),
52 );
53 expect(uniq).toBeDefined();
54 expect(uniq!).not.toContain('lower(email)');
55 });
56
57 test('user_emails."primary" is quoted (reserved keyword on DoltGres)', () => {
58 const emails = stmts.find((s) =>
59 s.startsWith('CREATE TABLE IF NOT EXISTS "_briven_auth_user_emails"'),
60 );
61 expect(emails).toBeDefined();
62 expect(emails!).toContain('"primary"');
63 // bare `primary boolean` is the bug that broke enable-auth (2026-07-20)
64 expect(emails!).not.toMatch(/(?<!")primary\s+boolean/u);
65 });
66
67 test('S2.1b: user.email_verified is BOOLEAN (Better-Auth shape, not timestamp)', () => {
68 const usersTable = stmts.find((s) =>
69 s.startsWith('CREATE TABLE IF NOT EXISTS "_briven_auth_users"'),
70 );
71 expect(usersTable!).toContain('email_verified boolean NOT NULL DEFAULT false');
72 });
73
74 test('S2.1b: accounts has a password column + account_id (Better-Auth credential)', () => {
75 const accounts = stmts.find((s) =>
76 s.startsWith('CREATE TABLE IF NOT EXISTS "_briven_auth_accounts"'),
77 );
78 expect(accounts!).toContain('account_id text NOT NULL');
79 expect(accounts!).toContain('password text');
80 });
81
82 test('sessions cascade-delete on user removal', () => {
83 const sessions = stmts.find((s) =>
84 s.startsWith('CREATE TABLE IF NOT EXISTS "_briven_auth_sessions"'),
85 );
86 expect(sessions).toBeDefined();
87 expect(sessions!).toContain('ON DELETE CASCADE');
88 });
89
90 test('accounts cascade-delete on user removal', () => {
91 const accounts = stmts.find((s) =>
92 s.startsWith('CREATE TABLE IF NOT EXISTS "_briven_auth_accounts"'),
93 );
94 expect(accounts!).toContain('ON DELETE CASCADE');
95 });
96
97 test('audit_log preserves rows when user is deleted (forensic value)', () => {
98 const audit = stmts.find((s) =>
99 s.startsWith('CREATE TABLE IF NOT EXISTS "_briven_auth_audit_log"'),
100 );
101 expect(audit!).toContain('ON DELETE SET NULL');
102 });
103
104 test('accounts has the (provider_id, provider_account_id) uniqueness constraint', () => {
105 const uniq = stmts.find(
106 (s) =>
107 s.includes('_briven_auth_accounts_provider_pair_uniq') &&
108 s.includes('UNIQUE') &&
109 s.includes('provider_id'),
110 );
111 expect(uniq).toBeDefined();
112 });
113
114 test('S2.1b: verification table uses Better-Auth shape (value, not value_hash)', () => {
115 const verif = stmts.find((s) =>
116 s.startsWith('CREATE TABLE IF NOT EXISTS "_briven_auth_verification_tokens"'),
117 );
118 expect(verif!).toContain('value text NOT NULL');
119 expect(verif!).not.toContain('value_hash');
120 });
121
122 test('audit_log metadata defaults to empty jsonb', () => {
123 const audit = stmts.find((s) =>
124 s.startsWith('CREATE TABLE IF NOT EXISTS "_briven_auth_audit_log"'),
125 );
126 expect(audit!).toContain(`jsonb NOT NULL DEFAULT '{}'::jsonb`);
127 });
128
129 test('jwks table matches the jwt plugin model (public/private key, created/expires)', () => {
130 const jwks = stmts.find((s) =>
131 s.startsWith('CREATE TABLE IF NOT EXISTS "_briven_auth_jwks"'),
132 );
133 expect(jwks).toBeDefined();
134 expect(jwks!).toContain('public_key text NOT NULL');
135 expect(jwks!).toContain('private_key text NOT NULL');
136 expect(jwks!).toContain('created_at timestamptz NOT NULL DEFAULT now()');
137 // expiresAt is optional in the plugin schema (only set with key rotation).
138 expect(jwks!).toContain('expires_at timestamptz');
139 expect(jwks!).not.toContain('expires_at timestamptz NOT NULL');
140 });
141
142 test('AUTH_TABLES is exhaustive and matches the emitted CREATE TABLEs', () => {
143 const createdTables = stmts
144 .filter((s) => s.startsWith('CREATE TABLE'))
145 .map((s) => s.match(/"(_briven_auth_[a-z_]+)"/)?.[1]);
146 expect(new Set(createdTables)).toEqual(new Set(AUTH_TABLES));
147 });
148
149 test('all statements are single-line (whitespace normalised)', () => {
150 for (const s of stmts) {
151 expect(s).not.toContain('\n');
152 expect(s.length).toBeGreaterThan(0);
153 }
154 });
155});