konnosDashboard
schema.ts300 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 )`,
24 `CREATE INDEX IF NOT EXISTS be_users_tenant_email_idx ON be_users (tenant_id, email)`,
25 `CREATE INDEX IF NOT EXISTS be_users_tenant_phone_idx ON be_users (tenant_id, phone)`,
26 `CREATE TABLE IF NOT EXISTS be_password_hashes (
27 user_id TEXT PRIMARY KEY,
28 password_hash TEXT NOT NULL,
29 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
30 )`,
31 `CREATE TABLE IF NOT EXISTS be_sessions (
32 session_handle TEXT PRIMARY KEY,
33 user_id TEXT NOT NULL,
34 tenant_id TEXT NOT NULL DEFAULT 'public',
35 refresh_token_hash TEXT NOT NULL,
36 access_payload_json TEXT NOT NULL DEFAULT '{}',
37 expires_at TIMESTAMPTZ NOT NULL,
38 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
39 )`,
40 `CREATE INDEX IF NOT EXISTS be_sessions_user_idx ON be_sessions (user_id)`,
41 `CREATE TABLE IF NOT EXISTS be_passwordless_codes (
42 pre_auth_session_id TEXT PRIMARY KEY,
43 tenant_id TEXT NOT NULL DEFAULT 'public',
44 email TEXT,
45 phone TEXT,
46 code_hash TEXT NOT NULL,
47 device_id TEXT NOT NULL,
48 expires_at TIMESTAMPTZ NOT NULL,
49 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
50 )`,
51 `CREATE TABLE IF NOT EXISTS be_third_party_links (
52 id TEXT PRIMARY KEY,
53 user_id TEXT NOT NULL,
54 tenant_id TEXT NOT NULL DEFAULT 'public',
55 third_party_id TEXT NOT NULL,
56 third_party_user_id TEXT NOT NULL,
57 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
58 )`,
59 `CREATE UNIQUE INDEX IF NOT EXISTS be_tp_unique
60 ON be_third_party_links (tenant_id, third_party_id, third_party_user_id)`,
61 // MFA TOTP devices
62 `CREATE TABLE IF NOT EXISTS be_totp_devices (
63 id TEXT PRIMARY KEY,
64 user_id TEXT NOT NULL,
65 tenant_id TEXT NOT NULL DEFAULT 'public',
66 device_name TEXT NOT NULL,
67 secret_base32 TEXT NOT NULL,
68 verified BOOLEAN NOT NULL DEFAULT FALSE,
69 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
70 )`,
71 `CREATE INDEX IF NOT EXISTS be_totp_user_idx ON be_totp_devices (user_id)`,
72 // Passkeys / WebAuthn
73 `CREATE TABLE IF NOT EXISTS be_webauthn_challenges (
74 challenge_id TEXT PRIMARY KEY,
75 tenant_id TEXT NOT NULL DEFAULT 'public',
76 user_id TEXT,
77 challenge TEXT NOT NULL,
78 type TEXT NOT NULL,
79 expires_at TIMESTAMPTZ NOT NULL,
80 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
81 )`,
82 `CREATE TABLE IF NOT EXISTS be_webauthn_credentials (
83 id TEXT PRIMARY KEY,
84 user_id TEXT NOT NULL,
85 tenant_id TEXT NOT NULL DEFAULT 'public',
86 credential_id TEXT NOT NULL,
87 public_key TEXT NOT NULL,
88 counter BIGINT NOT NULL DEFAULT 0,
89 device_type TEXT,
90 backed_up BOOLEAN NOT NULL DEFAULT FALSE,
91 transports TEXT,
92 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
93 )`,
94 `CREATE UNIQUE INDEX IF NOT EXISTS be_webauthn_cred_unique
95 ON be_webauthn_credentials (tenant_id, credential_id)`,
96 `CREATE INDEX IF NOT EXISTS be_webauthn_user_idx ON be_webauthn_credentials (user_id)`,
97 // Roles
98 `CREATE TABLE IF NOT EXISTS be_roles (
99 tenant_id TEXT NOT NULL,
100 role_name TEXT NOT NULL,
101 permissions_json TEXT NOT NULL DEFAULT '[]',
102 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
103 PRIMARY KEY (tenant_id, role_name)
104 )`,
105 `CREATE TABLE IF NOT EXISTS be_user_roles (
106 tenant_id TEXT NOT NULL,
107 user_id TEXT NOT NULL,
108 role_name TEXT NOT NULL,
109 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
110 PRIMARY KEY (tenant_id, user_id, role_name)
111 )`,
112 // Abuse counters (fallback when Redis unavailable)
113 `CREATE TABLE IF NOT EXISTS be_rate_limits (
114 bucket_key TEXT PRIMARY KEY,
115 hit_count INT NOT NULL DEFAULT 0,
116 window_start TIMESTAMPTZ NOT NULL DEFAULT NOW()
117 )`,
118 // Enterprise SSO (SAML + OIDC) — briven-engine native
119 `CREATE TABLE IF NOT EXISTS be_sso_connections (
120 id TEXT PRIMARY KEY,
121 project_id TEXT NOT NULL,
122 tenant_id TEXT NOT NULL,
123 name TEXT NOT NULL,
124 provider_type TEXT NOT NULL,
125 domains_json TEXT NOT NULL DEFAULT '[]',
126 config_json TEXT NOT NULL DEFAULT '{}',
127 jit_enabled BOOLEAN NOT NULL DEFAULT TRUE,
128 deactivated_at TIMESTAMPTZ,
129 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
130 )`,
131 `CREATE INDEX IF NOT EXISTS be_sso_conn_project_idx ON be_sso_connections (project_id)`,
132 `CREATE TABLE IF NOT EXISTS be_sso_states (
133 state_id TEXT PRIMARY KEY,
134 connection_id TEXT NOT NULL,
135 project_id TEXT NOT NULL,
136 provider_type TEXT NOT NULL,
137 code_verifier TEXT,
138 redirect_uri TEXT,
139 expires_at TIMESTAMPTZ NOT NULL,
140 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
141 )`,
142 // Security audit trail (SuperTokens-class event log; no raw IPs)
143 `CREATE TABLE IF NOT EXISTS be_audit_events (
144 id TEXT PRIMARY KEY,
145 tenant_id TEXT NOT NULL DEFAULT 'public',
146 project_id TEXT,
147 user_id TEXT,
148 action TEXT NOT NULL,
149 ip_hash_hint TEXT,
150 user_agent TEXT,
151 metadata_json TEXT NOT NULL DEFAULT '{}',
152 occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
153 )`,
154 `CREATE INDEX IF NOT EXISTS be_audit_tenant_time_idx
155 ON be_audit_events (tenant_id, occurred_at DESC)`,
156 `CREATE INDEX IF NOT EXISTS be_audit_project_time_idx
157 ON be_audit_events (project_id, occurred_at DESC)`,
158 `CREATE INDEX IF NOT EXISTS be_audit_action_time_idx
159 ON be_audit_events (action, occurred_at DESC)`,
160 // M2M OAuth2 client credentials (machine clients → short-lived tokens)
161 `CREATE TABLE IF NOT EXISTS be_m2m_clients (
162 id TEXT PRIMARY KEY,
163 client_id TEXT NOT NULL UNIQUE,
164 project_id TEXT NOT NULL,
165 tenant_id TEXT NOT NULL DEFAULT 'public',
166 name TEXT NOT NULL,
167 secret_hash TEXT NOT NULL,
168 secret_suffix TEXT NOT NULL,
169 role TEXT NOT NULL DEFAULT 'developer',
170 revoked_at TIMESTAMPTZ,
171 last_used_at TIMESTAMPTZ,
172 created_by TEXT,
173 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
174 )`,
175 `CREATE INDEX IF NOT EXISTS be_m2m_project_idx
176 ON be_m2m_clients (project_id)`,
177 // ─── OIDC / OAuth2 IdP (Briven as SuperTokens-class provider) ───
178 `CREATE TABLE IF NOT EXISTS be_oidc_signing_keys (
179 kid TEXT PRIMARY KEY,
180 private_pem TEXT NOT NULL,
181 public_jwk_json TEXT NOT NULL,
182 active BOOLEAN NOT NULL DEFAULT TRUE,
183 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
184 )`,
185 `CREATE TABLE IF NOT EXISTS be_oidc_clients (
186 id TEXT PRIMARY KEY,
187 client_id TEXT NOT NULL UNIQUE,
188 project_id TEXT NOT NULL,
189 tenant_id TEXT NOT NULL DEFAULT 'public',
190 name TEXT NOT NULL,
191 logo_url TEXT,
192 client_secret_hash TEXT,
193 client_secret_suffix TEXT,
194 is_public BOOLEAN NOT NULL DEFAULT FALSE,
195 redirect_uris_json TEXT NOT NULL DEFAULT '[]',
196 post_logout_uris_json TEXT NOT NULL DEFAULT '[]',
197 grant_types_json TEXT NOT NULL DEFAULT '["authorization_code","refresh_token"]',
198 scopes_json TEXT NOT NULL DEFAULT '["openid","profile","email","offline_access"]',
199 token_endpoint_auth_method TEXT NOT NULL DEFAULT 'client_secret_post',
200 created_by TEXT,
201 revoked_at TIMESTAMPTZ,
202 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
203 )`,
204 `CREATE INDEX IF NOT EXISTS be_oidc_clients_project_idx
205 ON be_oidc_clients (project_id)`,
206 `CREATE TABLE IF NOT EXISTS be_oidc_auth_requests (
207 id TEXT PRIMARY KEY,
208 client_id TEXT NOT NULL,
209 project_id TEXT NOT NULL,
210 redirect_uri TEXT NOT NULL,
211 scope TEXT NOT NULL,
212 state TEXT,
213 nonce TEXT,
214 code_challenge TEXT,
215 code_challenge_method TEXT,
216 response_type TEXT NOT NULL DEFAULT 'code',
217 user_id TEXT,
218 consented_at TIMESTAMPTZ,
219 expires_at TIMESTAMPTZ NOT NULL,
220 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
221 )`,
222 `CREATE INDEX IF NOT EXISTS be_oidc_auth_req_exp_idx
223 ON be_oidc_auth_requests (expires_at)`,
224 `CREATE TABLE IF NOT EXISTS be_oidc_auth_codes (
225 code_hash TEXT PRIMARY KEY,
226 client_id TEXT NOT NULL,
227 project_id TEXT NOT NULL,
228 user_id TEXT NOT NULL,
229 redirect_uri TEXT NOT NULL,
230 scope TEXT NOT NULL,
231 nonce TEXT,
232 code_challenge TEXT,
233 code_challenge_method TEXT,
234 expires_at TIMESTAMPTZ NOT NULL,
235 used_at TIMESTAMPTZ,
236 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
237 )`,
238 `CREATE TABLE IF NOT EXISTS be_oidc_refresh_tokens (
239 token_hash TEXT PRIMARY KEY,
240 client_id TEXT NOT NULL,
241 project_id TEXT NOT NULL,
242 user_id TEXT NOT NULL,
243 scope TEXT NOT NULL,
244 expires_at TIMESTAMPTZ NOT NULL,
245 revoked_at TIMESTAMPTZ,
246 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
247 )`,
248 `CREATE INDEX IF NOT EXISTS be_oidc_refresh_client_idx
249 ON be_oidc_refresh_tokens (client_id)`,
250 `CREATE TABLE IF NOT EXISTS be_oidc_consents (
251 user_id TEXT NOT NULL,
252 client_id TEXT NOT NULL,
253 scope TEXT NOT NULL,
254 granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
255 PRIMARY KEY (user_id, client_id)
256 )`,
257];
258
259export async function bootstrapBrivenEngineSchema(): Promise<void> {
260 const pool = getEnginePool();
261 for (const sql of STATEMENTS) {
262 try {
263 await pool.query(sql);
264 } catch (err) {
265 // Doltgres may reject IF NOT EXISTS on some objects — retry without, or log.
266 const message = err instanceof Error ? err.message : String(err);
267 if (/already exists/i.test(message)) continue;
268 log.warn('briven_engine_schema_stmt', { message, sql: sql.slice(0, 80) });
269 throw err;
270 }
271 }
272 log.info('briven_engine_schema_ready', {
273 engine: 'briven-engine',
274 storage: 'doltgres',
275 tables: [
276 'be_tenants',
277 'be_users',
278 'be_password_hashes',
279 'be_sessions',
280 'be_passwordless_codes',
281 'be_third_party_links',
282 'be_totp_devices',
283 'be_webauthn_challenges',
284 'be_webauthn_credentials',
285 'be_roles',
286 'be_user_roles',
287 'be_rate_limits',
288 'be_sso_connections',
289 'be_sso_states',
290 'be_audit_events',
291 'be_m2m_clients',
292 'be_oidc_signing_keys',
293 'be_oidc_clients',
294 'be_oidc_auth_requests',
295 'be_oidc_auth_codes',
296 'be_oidc_refresh_tokens',
297 'be_oidc_consents',
298 ],
299 });
300}