konnosDashboard
schema.ts395 lines · main
1/**
2 * Doltgres schema for briven-engine (all Auth product state).
3 * Applied at boot via ensure + bootstrap — no stock Postgres.
4 */
5
6import { getEnginePool } from './db.js';
7import { log } from '../../lib/logger.js';
8
9const STATEMENTS = [
10 `CREATE TABLE IF NOT EXISTS be_tenants (
11 tenant_id TEXT PRIMARY KEY,
12 project_id TEXT NOT NULL,
13 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
14 )`,
15 `CREATE TABLE IF NOT EXISTS be_users (
16 id TEXT PRIMARY KEY,
17 tenant_id TEXT NOT NULL DEFAULT 'public',
18 email TEXT,
19 phone TEXT,
20 email_verified BOOLEAN NOT NULL DEFAULT FALSE,
21 time_joined TIMESTAMPTZ NOT NULL DEFAULT NOW(),
22 metadata_json TEXT NOT NULL DEFAULT '{}',
23 held_at TIMESTAMPTZ,
24 held_reason TEXT,
25 archived_at TIMESTAMPTZ,
26 archived_reason TEXT
27 )`,
28 `CREATE INDEX IF NOT EXISTS be_users_tenant_email_idx ON be_users (tenant_id, email)`,
29 `CREATE INDEX IF NOT EXISTS be_users_tenant_phone_idx ON be_users (tenant_id, phone)`,
30 `CREATE TABLE IF NOT EXISTS be_password_hashes (
31 user_id TEXT PRIMARY KEY,
32 password_hash TEXT NOT NULL,
33 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
34 )`,
35 `CREATE TABLE IF NOT EXISTS be_sessions (
36 session_handle TEXT PRIMARY KEY,
37 user_id TEXT NOT NULL,
38 tenant_id TEXT NOT NULL DEFAULT 'public',
39 refresh_token_hash TEXT NOT NULL,
40 access_payload_json TEXT NOT NULL DEFAULT '{}',
41 expires_at TIMESTAMPTZ NOT NULL,
42 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
43 )`,
44 `CREATE INDEX IF NOT EXISTS be_sessions_user_idx ON be_sessions (user_id)`,
45 `CREATE TABLE IF NOT EXISTS be_passwordless_codes (
46 pre_auth_session_id TEXT PRIMARY KEY,
47 tenant_id TEXT NOT NULL DEFAULT 'public',
48 email TEXT,
49 phone TEXT,
50 code_hash TEXT NOT NULL,
51 device_id TEXT NOT NULL,
52 expires_at TIMESTAMPTZ NOT NULL,
53 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
54 )`,
55 `CREATE TABLE IF NOT EXISTS be_third_party_links (
56 id TEXT PRIMARY KEY,
57 user_id TEXT NOT NULL,
58 tenant_id TEXT NOT NULL DEFAULT 'public',
59 third_party_id TEXT NOT NULL,
60 third_party_user_id TEXT NOT NULL,
61 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
62 )`,
63 `CREATE UNIQUE INDEX IF NOT EXISTS be_tp_unique
64 ON be_third_party_links (tenant_id, third_party_id, third_party_user_id)`,
65 // MFA TOTP devices
66 `CREATE TABLE IF NOT EXISTS be_totp_devices (
67 id TEXT PRIMARY KEY,
68 user_id TEXT NOT NULL,
69 tenant_id TEXT NOT NULL DEFAULT 'public',
70 device_name TEXT NOT NULL,
71 secret_base32 TEXT NOT NULL,
72 verified BOOLEAN NOT NULL DEFAULT FALSE,
73 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
74 )`,
75 `CREATE INDEX IF NOT EXISTS be_totp_user_idx ON be_totp_devices (user_id)`,
76 // Passkeys / WebAuthn
77 `CREATE TABLE IF NOT EXISTS be_webauthn_challenges (
78 challenge_id TEXT PRIMARY KEY,
79 tenant_id TEXT NOT NULL DEFAULT 'public',
80 user_id TEXT,
81 challenge TEXT NOT NULL,
82 type TEXT NOT NULL,
83 expires_at TIMESTAMPTZ NOT NULL,
84 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
85 )`,
86 `CREATE TABLE IF NOT EXISTS be_webauthn_credentials (
87 id TEXT PRIMARY KEY,
88 user_id TEXT NOT NULL,
89 tenant_id TEXT NOT NULL DEFAULT 'public',
90 credential_id TEXT NOT NULL,
91 public_key TEXT NOT NULL,
92 counter BIGINT NOT NULL DEFAULT 0,
93 device_type TEXT,
94 backed_up BOOLEAN NOT NULL DEFAULT FALSE,
95 transports TEXT,
96 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
97 )`,
98 `CREATE UNIQUE INDEX IF NOT EXISTS be_webauthn_cred_unique
99 ON be_webauthn_credentials (tenant_id, credential_id)`,
100 `CREATE INDEX IF NOT EXISTS be_webauthn_user_idx ON be_webauthn_credentials (user_id)`,
101 // Roles
102 `CREATE TABLE IF NOT EXISTS be_roles (
103 tenant_id TEXT NOT NULL,
104 role_name TEXT NOT NULL,
105 permissions_json TEXT NOT NULL DEFAULT '[]',
106 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
107 PRIMARY KEY (tenant_id, role_name)
108 )`,
109 `CREATE TABLE IF NOT EXISTS be_user_roles (
110 tenant_id TEXT NOT NULL,
111 user_id TEXT NOT NULL,
112 role_name TEXT NOT NULL,
113 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
114 PRIMARY KEY (tenant_id, user_id, role_name)
115 )`,
116 // Abuse counters (fallback when Redis unavailable)
117 `CREATE TABLE IF NOT EXISTS be_rate_limits (
118 bucket_key TEXT PRIMARY KEY,
119 hit_count INT NOT NULL DEFAULT 0,
120 window_start TIMESTAMPTZ NOT NULL DEFAULT NOW()
121 )`,
122 // Enterprise SSO (SAML + OIDC) — briven-engine native
123 `CREATE TABLE IF NOT EXISTS be_sso_connections (
124 id TEXT PRIMARY KEY,
125 project_id TEXT NOT NULL,
126 tenant_id TEXT NOT NULL,
127 name TEXT NOT NULL,
128 provider_type TEXT NOT NULL,
129 domains_json TEXT NOT NULL DEFAULT '[]',
130 config_json TEXT NOT NULL DEFAULT '{}',
131 jit_enabled BOOLEAN NOT NULL DEFAULT TRUE,
132 deactivated_at TIMESTAMPTZ,
133 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
134 )`,
135 `CREATE INDEX IF NOT EXISTS be_sso_conn_project_idx ON be_sso_connections (project_id)`,
136 `CREATE TABLE IF NOT EXISTS be_sso_states (
137 state_id TEXT PRIMARY KEY,
138 connection_id TEXT NOT NULL,
139 project_id TEXT NOT NULL,
140 provider_type TEXT NOT NULL,
141 code_verifier TEXT,
142 redirect_uri TEXT,
143 return_to TEXT,
144 expires_at TIMESTAMPTZ NOT NULL,
145 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
146 )`,
147 // Security audit trail (SuperTokens-class event log; no raw IPs)
148 `CREATE TABLE IF NOT EXISTS be_audit_events (
149 id TEXT PRIMARY KEY,
150 tenant_id TEXT NOT NULL DEFAULT 'public',
151 project_id TEXT,
152 user_id TEXT,
153 action TEXT NOT NULL,
154 ip_hash_hint TEXT,
155 user_agent TEXT,
156 metadata_json TEXT NOT NULL DEFAULT '{}',
157 occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
158 )`,
159 `CREATE INDEX IF NOT EXISTS be_audit_tenant_time_idx
160 ON be_audit_events (tenant_id, occurred_at DESC)`,
161 `CREATE INDEX IF NOT EXISTS be_audit_project_time_idx
162 ON be_audit_events (project_id, occurred_at DESC)`,
163 `CREATE INDEX IF NOT EXISTS be_audit_action_time_idx
164 ON be_audit_events (action, occurred_at DESC)`,
165 // M2M OAuth2 client credentials (machine clients → short-lived tokens)
166 `CREATE TABLE IF NOT EXISTS be_m2m_clients (
167 id TEXT PRIMARY KEY,
168 client_id TEXT NOT NULL UNIQUE,
169 project_id TEXT NOT NULL,
170 tenant_id TEXT NOT NULL DEFAULT 'public',
171 name TEXT NOT NULL,
172 secret_hash TEXT NOT NULL,
173 secret_suffix TEXT NOT NULL,
174 role TEXT NOT NULL DEFAULT 'developer',
175 revoked_at TIMESTAMPTZ,
176 last_used_at TIMESTAMPTZ,
177 created_by TEXT,
178 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
179 )`,
180 `CREATE INDEX IF NOT EXISTS be_m2m_project_idx
181 ON be_m2m_clients (project_id)`,
182 // ─── OIDC / OAuth2 IdP (Briven as SuperTokens-class provider) ───
183 `CREATE TABLE IF NOT EXISTS be_oidc_signing_keys (
184 kid TEXT PRIMARY KEY,
185 private_pem TEXT NOT NULL,
186 public_jwk_json TEXT NOT NULL,
187 active BOOLEAN NOT NULL DEFAULT TRUE,
188 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
189 )`,
190 `CREATE TABLE IF NOT EXISTS be_oidc_clients (
191 id TEXT PRIMARY KEY,
192 client_id TEXT NOT NULL UNIQUE,
193 project_id TEXT NOT NULL,
194 tenant_id TEXT NOT NULL DEFAULT 'public',
195 name TEXT NOT NULL,
196 logo_url TEXT,
197 client_secret_hash TEXT,
198 client_secret_suffix TEXT,
199 is_public BOOLEAN NOT NULL DEFAULT FALSE,
200 redirect_uris_json TEXT NOT NULL DEFAULT '[]',
201 post_logout_uris_json TEXT NOT NULL DEFAULT '[]',
202 grant_types_json TEXT NOT NULL DEFAULT '["authorization_code","refresh_token"]',
203 scopes_json TEXT NOT NULL DEFAULT '["openid","profile","email","offline_access"]',
204 token_endpoint_auth_method TEXT NOT NULL DEFAULT 'client_secret_post',
205 created_by TEXT,
206 revoked_at TIMESTAMPTZ,
207 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
208 )`,
209 `CREATE INDEX IF NOT EXISTS be_oidc_clients_project_idx
210 ON be_oidc_clients (project_id)`,
211 `CREATE TABLE IF NOT EXISTS be_oidc_auth_requests (
212 id TEXT PRIMARY KEY,
213 client_id TEXT NOT NULL,
214 project_id TEXT NOT NULL,
215 redirect_uri TEXT NOT NULL,
216 scope TEXT NOT NULL,
217 state TEXT,
218 nonce TEXT,
219 code_challenge TEXT,
220 code_challenge_method TEXT,
221 response_type TEXT NOT NULL DEFAULT 'code',
222 user_id TEXT,
223 consented_at TIMESTAMPTZ,
224 expires_at TIMESTAMPTZ NOT NULL,
225 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
226 )`,
227 `CREATE INDEX IF NOT EXISTS be_oidc_auth_req_exp_idx
228 ON be_oidc_auth_requests (expires_at)`,
229 `CREATE TABLE IF NOT EXISTS be_oidc_auth_codes (
230 code_hash TEXT PRIMARY KEY,
231 client_id TEXT NOT NULL,
232 project_id TEXT NOT NULL,
233 user_id TEXT NOT NULL,
234 redirect_uri TEXT NOT NULL,
235 scope TEXT NOT NULL,
236 nonce TEXT,
237 code_challenge TEXT,
238 code_challenge_method TEXT,
239 expires_at TIMESTAMPTZ NOT NULL,
240 used_at TIMESTAMPTZ,
241 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
242 )`,
243 `CREATE TABLE IF NOT EXISTS be_oidc_refresh_tokens (
244 token_hash TEXT PRIMARY KEY,
245 client_id TEXT NOT NULL,
246 project_id TEXT NOT NULL,
247 user_id TEXT NOT NULL,
248 scope TEXT NOT NULL,
249 expires_at TIMESTAMPTZ NOT NULL,
250 revoked_at TIMESTAMPTZ,
251 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
252 )`,
253 `CREATE INDEX IF NOT EXISTS be_oidc_refresh_client_idx
254 ON be_oidc_refresh_tokens (client_id)`,
255 `CREATE TABLE IF NOT EXISTS be_oidc_consents (
256 user_id TEXT NOT NULL,
257 client_id TEXT NOT NULL,
258 scope TEXT NOT NULL,
259 granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
260 PRIMARY KEY (user_id, client_id)
261 )`,
262 // AI agent tokens (SuperTokens-class AI auth first cut)
263 `CREATE TABLE IF NOT EXISTS be_ai_agent_tokens (
264 id TEXT PRIMARY KEY,
265 project_id TEXT NOT NULL,
266 tenant_id TEXT NOT NULL DEFAULT 'public',
267 agent_name TEXT NOT NULL,
268 scopes_json TEXT NOT NULL DEFAULT '["ai.invoke"]',
269 token_hash TEXT NOT NULL UNIQUE,
270 token_suffix TEXT NOT NULL,
271 expires_at TIMESTAMPTZ,
272 revoked_at TIMESTAMPTZ,
273 last_used_at TIMESTAMPTZ,
274 created_by TEXT,
275 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
276 )`,
277 `CREATE INDEX IF NOT EXISTS be_ai_agent_project_idx
278 ON be_ai_agent_tokens (project_id)`,
279];
280
281/** Soft-disable Auth per project without deleting end-user data. */
282async function ensureBeTenantsDisabledColumn(): Promise<void> {
283 const pool = getEnginePool();
284 try {
285 const probe = await pool.query(
286 `SELECT 1 AS ok FROM information_schema.columns
287 WHERE table_name = 'be_tenants' AND column_name = 'disabled_at' LIMIT 1`,
288 );
289 if ((probe.rowCount ?? 0) > 0 || (probe.rows?.length ?? 0) > 0) return;
290 await pool.query(`ALTER TABLE be_tenants ADD COLUMN disabled_at TIMESTAMPTZ`);
291 } catch (err) {
292 const message = err instanceof Error ? err.message : String(err);
293 if (/already exists|duplicate/i.test(message)) return;
294 log.warn('briven_engine_tenant_disabled_col', { message });
295 }
296}
297
298/** AUTH-HARDEN-90: OIDC app return URL — Doltgres has no ADD COLUMN IF NOT EXISTS. */
299async function ensureBeSsoStatesReturnToColumn(): Promise<void> {
300 const pool = getEnginePool();
301 try {
302 const probe = await pool.query(
303 `SELECT 1 AS ok FROM information_schema.columns
304 WHERE table_name = 'be_sso_states' AND column_name = 'return_to' LIMIT 1`,
305 );
306 if ((probe.rowCount ?? 0) > 0 || (probe.rows?.length ?? 0) > 0) return;
307 await pool.query(`ALTER TABLE be_sso_states ADD COLUMN return_to TEXT`);
308 } catch (err) {
309 const message = err instanceof Error ? err.message : String(err);
310 if (/already exists|duplicate/i.test(message)) return;
311 log.warn('briven_engine_sso_return_to_col', { message });
312 }
313}
314
315/** Doltgres often lacks ADD COLUMN IF NOT EXISTS — probe then add. */
316async function ensureBeUsersModerationColumns(): Promise<void> {
317 const pool = getEnginePool();
318 const cols: Array<{ name: string; ddl: string }> = [
319 { name: 'held_at', ddl: 'ALTER TABLE be_users ADD COLUMN held_at TIMESTAMPTZ' },
320 { name: 'held_reason', ddl: 'ALTER TABLE be_users ADD COLUMN held_reason TEXT' },
321 {
322 name: 'archived_at',
323 ddl: 'ALTER TABLE be_users ADD COLUMN archived_at TIMESTAMPTZ',
324 },
325 {
326 name: 'archived_reason',
327 ddl: 'ALTER TABLE be_users ADD COLUMN archived_reason TEXT',
328 },
329 ];
330 for (const col of cols) {
331 try {
332 const probe = await pool.query(
333 `SELECT 1 AS ok FROM information_schema.columns
334 WHERE table_name = 'be_users' AND column_name = $1 LIMIT 1`,
335 [col.name],
336 );
337 if ((probe.rowCount ?? 0) > 0 || (probe.rows?.length ?? 0) > 0) continue;
338 await pool.query(col.ddl);
339 } catch (err) {
340 const message = err instanceof Error ? err.message : String(err);
341 if (/already exists|duplicate/i.test(message)) continue;
342 log.warn('briven_engine_user_moderation_col', {
343 column: col.name,
344 message,
345 });
346 }
347 }
348}
349
350export async function bootstrapBrivenEngineSchema(): Promise<void> {
351 const pool = getEnginePool();
352 for (const sql of STATEMENTS) {
353 try {
354 await pool.query(sql);
355 } catch (err) {
356 // Doltgres may reject IF NOT EXISTS on some objects — retry without, or log.
357 const message = err instanceof Error ? err.message : String(err);
358 if (/already exists/i.test(message)) continue;
359 log.warn('briven_engine_schema_stmt', { message, sql: sql.slice(0, 80) });
360 throw err;
361 }
362 }
363 await ensureBeUsersModerationColumns();
364 await ensureBeTenantsDisabledColumn();
365 await ensureBeSsoStatesReturnToColumn();
366 log.info('briven_engine_schema_ready', {
367 engine: 'briven-engine',
368 storage: 'doltgres',
369 tables: [
370 'be_tenants',
371 'be_users',
372 'be_password_hashes',
373 'be_sessions',
374 'be_passwordless_codes',
375 'be_third_party_links',
376 'be_totp_devices',
377 'be_webauthn_challenges',
378 'be_webauthn_credentials',
379 'be_roles',
380 'be_user_roles',
381 'be_rate_limits',
382 'be_sso_connections',
383 'be_sso_states',
384 'be_audit_events',
385 'be_m2m_clients',
386 'be_oidc_signing_keys',
387 'be_oidc_clients',
388 'be_oidc_auth_requests',
389 'be_oidc_auth_codes',
390 'be_oidc_refresh_tokens',
391 'be_oidc_consents',
392 'be_ai_agent_tokens',
393 ],
394 });
395}