konnosDashboard
data-plane.ts472 lines · main
1import pg from 'pg';
2
3import { env } from '../env.js';
4import { log } from '../lib/logger.js';
5
6/**
7 * Lazily-opened admin connection for the shared data-plane cluster
8 * (`BRIVEN_DATA_PLANE_URL`). Used for DoltGres database provisioning /
9 * teardown and readiness pings.
10 *
11 * ADR-0004 — Briven is Doltgres end-to-end. Both control and data plane use
12 * the `pg` driver (node-postgres). postgres.js desyncs against Doltgres
13 * wire protocol (`unhandled message`).
14 */
15let _client: pg.Pool | null = null;
16
17function client(): pg.Pool {
18 if (!env.BRIVEN_DATA_PLANE_URL) {
19 throw new Error('BRIVEN_DATA_PLANE_URL is not configured');
20 }
21 if (!_client) {
22 _client = new pg.Pool({
23 connectionString: env.BRIVEN_DATA_PLANE_URL,
24 max: 20,
25 idleTimeoutMillis: 30000,
26 connectionTimeoutMillis: 5000,
27 });
28 log.info('data_plane_connected', { max: 20 });
29 }
30 return _client;
31}
32
33/**
34 * Map a `projectId` to the DoltGres DATABASE name we provision for it.
35 *
36 * We are converging the platform onto database-per-project (each project
37 * gets its own DoltGres database so it has an independent commit history /
38 * branch namespace, and realtime change-detection is scoped per database).
39 *
40 * Mirror of `apps/runtime/src/db.ts:dbNameFor` and the realtime copies —
41 * all sides derive the database name deterministically from the project id
42 * so the name never has to be shipped in a request payload.
43 */
44export function dbNameFor(projectId: string): string {
45 const safe = projectId.replace(/[^a-zA-Z0-9]/g, '_').toLowerCase();
46 // Postgres identifiers max 63 bytes; our prefix + ULID is well under that.
47 return `proj_${safe}`;
48}
49
50/**
51 * Postgres role that owns day-to-day CRUD inside a project's own database.
52 * Granted at provision time; password is rotated on every `briven db shell`
53 * request. Derived from `dbNameFor` so the role tracks the project database.
54 */
55export function roleNameFor(projectId: string): string {
56 return `${dbNameFor(projectId)}_owner`;
57}
58
59/**
60 * Provision a dedicated DoltGres DATABASE for a new project and create the
61 * platform bookkeeping tables inside it. Idempotent — safe to call on retry.
62 *
63 * This is the database-per-project replacement for `provisionProjectSchema`.
64 * Each project needs an independent DoltGres commit history / branch
65 * namespace, which is per-DATABASE, not per-schema — hence a real database
66 * rather than a Postgres schema.
67 *
68 * Notes:
69 * - `CREATE DATABASE` cannot run inside a transaction, so we issue it as a
70 * bare statement (never via `sql.begin`).
71 * - DoltGres may not support `IF NOT EXISTS` on `CREATE DATABASE`, so we
72 * guard idempotency by checking `pg_database` first (mirrors how the role
73 * DO-block guards against `pg_roles`).
74 * - The `_briven_` bookkeeping tables now live unqualified inside the
75 * project's own database (no schema prefix), reusing the same DDL the
76 * schema path used.
77 */
78export async function provisionProjectDatabase(projectId: string): Promise<string> {
79 const url = env.BRIVEN_DATA_PLANE_URL;
80 if (!url) {
81 throw new Error('BRIVEN_DATA_PLANE_URL is not configured');
82 }
83 const dbName = dbNameFor(projectId);
84 const admin = client();
85
86 // Guard: CREATE DATABASE is not reliably idempotent on DoltGres, and it
87 // cannot run inside a transaction — so check pg_database, then issue a
88 // bare CREATE only if absent.
89 const existing = await admin.query('SELECT 1 FROM pg_database WHERE datname = $1', [dbName]);
90 if (existing.rows.length === 0) {
91 await admin.query(`CREATE DATABASE "${dbName}"`);
92 }
93
94 // Second pool bound to the freshly-created database. Small pool — this is
95 // only used for one-shot provisioning DDL, then ended. Parse the base URL
96 // once and override the database field (DoltGres can't switch DB mid-conn).
97 const base = new URL(url);
98 const projPool = new pg.Pool({
99 host: base.hostname,
100 port: Number(base.port || 5432),
101 user: decodeURIComponent(base.username),
102 password: decodeURIComponent(base.password),
103 database: dbName,
104 max: 2,
105 idleTimeoutMillis: 30000,
106 connectionTimeoutMillis: 5000,
107 });
108 try {
109 await projPool.query(`
110 CREATE TABLE IF NOT EXISTS "_briven_migrations" (
111 id text PRIMARY KEY,
112 deployment_id text,
113 applied_at timestamptz NOT NULL DEFAULT now(),
114 summary jsonb
115 )
116 `);
117 await projPool.query(`
118 CREATE TABLE IF NOT EXISTS "_briven_meta" (
119 key text PRIMARY KEY,
120 value jsonb NOT NULL
121 )
122 `);
123 } finally {
124 await projPool.end();
125 }
126
127 log.info('project_database_provisioned', { projectId, dbName });
128 return dbName;
129}
130
131/**
132 * Drop a project's DoltGres database (used for create-time rollback and,
133 * later, soft-delete GC). `DROP DATABASE` also cannot run inside a
134 * transaction; issued as a bare statement.
135 */
136export async function dropProjectDatabase(projectId: string): Promise<void> {
137 const dbName = dbNameFor(projectId);
138 const admin = client();
139 await admin.query(`DROP DATABASE IF EXISTS "${dbName}"`);
140 log.warn('project_database_dropped', { projectId, dbName });
141}
142
143/**
144 * Create the project's scoped login role and grant CRUD inside the project's
145 * OWN DoltGres database (database-per-project). Called at project creation and
146 * lazily at shell-token issue time for projects that pre-date this feature.
147 * Uses the `pg` driver (postgres.js desyncs against DoltGres — see ADR 0001).
148 */
149export async function provisionProjectRole(projectId: string): Promise<void> {
150 const url = env.BRIVEN_DATA_PLANE_URL;
151 if (!url) {
152 throw new Error('BRIVEN_DATA_PLANE_URL is not configured');
153 }
154 const dbName = dbNameFor(projectId);
155 const role = roleNameFor(projectId);
156 const admin = client();
157
158 // CREATE ROLE is cluster-global, so it MUST run on the admin connection
159 // (default database), not inside the project database. DoltGres's role system
160 // is incomplete: no plpgsql `DO $$...$$` guard, and `pg_roles` visibility is
161 // unreliable across pooled connections — so neither IF NOT EXISTS nor a
162 // check-then-create is dependable. Instead just attempt CREATE ROLE and treat
163 // "already exists" as success (idempotent). Role name is generated by us.
164 try {
165 await admin.query(`CREATE ROLE "${role}" WITH LOGIN NOINHERIT PASSWORD NULL`);
166 } catch (err) {
167 const msg = err instanceof Error ? err.message : String(err);
168 if (!/already exists/i.test(msg)) throw err;
169 }
170
171 // The GRANTs apply to objects that live INSIDE the project's own database,
172 // so they must run on a connection bound to that database and target the
173 // `public` schema there (NOT a `proj_<id>` schema). Short-lived pool, same
174 // pattern as provisionProjectDatabase's second client — parse the base URL
175 // and override `database` (DoltGres can't switch DB mid-connection).
176 const base = new URL(url);
177 const projPool = new pg.Pool({
178 host: base.hostname,
179 port: Number(base.port || 5432),
180 user: decodeURIComponent(base.username),
181 password: decodeURIComponent(base.password),
182 database: dbName,
183 max: 2,
184 idleTimeoutMillis: 30000,
185 connectionTimeoutMillis: 5000,
186 });
187 try {
188 await projPool.query(`GRANT USAGE ON SCHEMA public TO "${role}"`);
189 await projPool.query(`GRANT ALL ON ALL TABLES IN SCHEMA public TO "${role}"`);
190 await projPool.query(`GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO "${role}"`);
191 // NOTE: `ALTER DEFAULT PRIVILEGES` is NOT supported on DoltGres ("not yet
192 // supported"), so we can't auto-grant on FUTURE tables. Instead this whole
193 // function re-runs on every shell-token issue (rotateProjectRolePassword →
194 // provisionProjectRole), so the GRANT ALL ON ALL TABLES above re-covers any
195 // tables created since the last issue. Acceptable for short-lived tokens.
196 // Platform tables: readable by platform, never writable by the user. They
197 // live unqualified in the project database's public schema.
198 await projPool.query(`REVOKE ALL ON TABLE "_briven_migrations" FROM "${role}"`);
199 await projPool.query(`REVOKE ALL ON TABLE "_briven_meta" FROM "${role}"`);
200 } finally {
201 await projPool.end();
202 }
203}
204
205/**
206 * Rotate the project role's password to a fresh random value and return the
207 * plaintext + expiry. The caller constructs a DSN from these and never writes
208 * them to logs. Uses the `pg` admin connection (ALTER ROLE is cluster-global).
209 */
210export async function rotateProjectRolePassword(
211 projectId: string,
212 ttlSeconds: number,
213): Promise<{ role: string; password: string; expiresAt: Date }> {
214 await provisionProjectRole(projectId);
215 const role = roleNameFor(projectId);
216 const password = randomPassword(32);
217 // `expiresAt` is app-side bookkeeping returned to the caller only — see the
218 // DoltGres note below; it is NOT a DB-enforced TTL.
219 const expiresAt = new Date(Date.now() + ttlSeconds * 1000);
220 const admin = client();
221 // why: pg cannot parameterize an ALTER ROLE password, so we inline the
222 // generated password. randomPassword(32) is hex ([0-9a-f] only), so it can't
223 // break out of the SQL string literal; the role name is generated by us too.
224 //
225 // DoltGres limitation: `ALTER ROLE ... PASSWORD '...' VALID UNTIL '<ts>'`
226 // FAILS on DoltGres ("could not parse until"), so we drop the VALID UNTIL
227 // clause — SQL-side password expiry is NOT enforced. Security instead relies
228 // on rotate-on-issue: every shell-token issue replaces the password, which
229 // invalidates any previously-handed-out DSN.
230 await admin.query(`ALTER ROLE "${role}" WITH PASSWORD '${password}'`);
231 return { role, password, expiresAt };
232}
233
234function randomPassword(bytes: number): string {
235 // Hex-encoded random bytes — safe in every DSN, never contains chars
236 // needing URL-encoding.
237 const buf = new Uint8Array(bytes);
238 crypto.getRandomValues(buf);
239 return Array.from(buf, (b) => b.toString(16).padStart(2, '0')).join('');
240}
241
242/**
243 * The minimal transaction adapter handed to `fn` inside
244 * `runInProjectDatabase`. Mirrors the runtime's `ProjectTx`
245 * (`apps/runtime/src/db.ts`): a single `unsafe(text, params?)` that resolves
246 * to the result rows directly — the same shape postgres.js's `tx.unsafe`
247 * returned, so callers that used `.unsafe(...)` migrate without rewrites.
248 */
249export interface ProjectTx {
250 // eslint-disable-next-line @typescript-eslint/no-explicit-any
251 unsafe(text: string, params?: readonly unknown[]): Promise<any[]>;
252}
253
254/**
255 * Per-project `pg.Pool` cache, each bound to that project's own DoltGres
256 * DATABASE (`proj_<id>`). Built from the parsed `BRIVEN_DATA_PLANE_URL` with
257 * the `database` field overridden (DoltGres can't switch DB mid-connection),
258 * exactly like `provisionProjectDatabase`'s second client.
259 */
260const _projPools = new Map<string, pg.Pool>();
261
262function poolFor(projectId: string): pg.Pool {
263 const url = env.BRIVEN_DATA_PLANE_URL;
264 if (!url) {
265 throw new Error('BRIVEN_DATA_PLANE_URL is not configured');
266 }
267 const dbName = dbNameFor(projectId);
268 let pool = _projPools.get(dbName);
269 if (!pool) {
270 const base = new URL(url);
271 pool = new pg.Pool({
272 host: base.hostname,
273 port: Number(base.port || 5432),
274 user: decodeURIComponent(base.username),
275 password: decodeURIComponent(base.password),
276 database: dbName,
277 max: 10,
278 idleTimeoutMillis: 30000,
279 connectionTimeoutMillis: 5000,
280 });
281 _projPools.set(dbName, pool);
282 log.info('project_db_pool_opened', { projectId, dbName });
283 }
284 return pool;
285}
286
287/**
288 * Run an arbitrary SQL string inside the project's OWN DoltGres database.
289 *
290 * This is the database-per-project replacement for the legacy
291 * `runInProjectSchema` (schema-per-tenant on a shared database). No
292 * `search_path` is set — the connection is already bound to the project's
293 * dedicated database, so unqualified identifiers resolve to the project's
294 * tables directly. Uses the `pg` driver (postgres.js desyncs against
295 * DoltGres — see ADR 0001).
296 *
297 * Wraps `fn` in `BEGIN`/`COMMIT` (ROLLBACK on throw, release in finally).
298 */
299export async function runInProjectDatabase<T>(
300 projectId: string,
301 fn: (tx: ProjectTx) => Promise<T>,
302): Promise<T> {
303 const pool = poolFor(projectId);
304 const conn = await pool.connect();
305 const tx: ProjectTx = {
306 unsafe: (text, params) =>
307 conn.query(text, params ? [...params] : undefined).then((r) => r.rows),
308 };
309 try {
310 await conn.query('BEGIN');
311 const result = await fn(tx);
312 await conn.query('COMMIT');
313 return result;
314 } catch (err) {
315 try {
316 await conn.query('ROLLBACK');
317 } catch {
318 // A failed rollback must not mask the original error.
319 }
320 throw err;
321 } finally {
322 conn.release();
323 }
324}
325
326/**
327 * Evict ONE project's cached pool — the per-project "database restart".
328 * Closes every pooled connection (in-flight queries finish; idle ones drop)
329 * and removes the cache entry so the very next query opens a FRESH pool with
330 * a fresh auth handshake. This clears the stuck-connection / stale-auth class
331 * of incidents without touching any data. Returns false when no pool was
332 * cached (nothing to restart — also fine).
333 */
334export async function evictProjectPool(projectId: string): Promise<boolean> {
335 const dbName = dbNameFor(projectId);
336 const pool = _projPools.get(dbName);
337 if (!pool) return false;
338 _projPools.delete(dbName);
339 try {
340 await pool.end();
341 } catch (err) {
342 // The pool is already out of the cache — a noisy close must not fail the
343 // restart; the old pool is unreachable either way.
344 log.warn('project_db_pool_close_failed', {
345 projectId,
346 message: err instanceof Error ? err.message : String(err),
347 });
348 }
349 log.info('project_db_pool_evicted', { projectId, dbName });
350 return true;
351}
352
353export interface ProjectDbHealth {
354 reachable: boolean;
355 latencyMs: number | null;
356 tableCount: number | null;
357 /** Dolt HEAD commit hash — proves the versioning engine answers too. */
358 headCommit: string | null;
359 error: string | null;
360}
361
362/**
363 * Cheap per-project database health probe: fresh query through the normal
364 * pool path — counts user tables and reads the Dolt HEAD hash. Fail-soft:
365 * never throws, reports the failure in `error` instead.
366 */
367export async function checkProjectDbHealth(projectId: string): Promise<ProjectDbHealth> {
368 const started = Date.now();
369 try {
370 const rows = await runInProjectDatabase(projectId, (tx) =>
371 tx.unsafe(
372 `SELECT
373 (SELECT count(*)::int
374 FROM information_schema.tables
375 WHERE table_schema = 'public'
376 AND table_type = 'BASE TABLE'
377 AND left(table_name, 8) <> '_briven_') AS table_count,
378 DOLT_HASHOF('HEAD') AS head`,
379 ),
380 );
381 const row = (rows as Array<{ table_count: number; head: unknown }>)[0];
382 // DOLT_HASHOF comes back brace-wrapped on some DoltGres builds — strip.
383 const head = row ? String(row.head).replace(/[{}]/g, '') : null;
384 return {
385 reachable: true,
386 latencyMs: Date.now() - started,
387 tableCount: row ? Number(row.table_count) : null,
388 headCommit: head,
389 error: null,
390 };
391 } catch (err) {
392 return {
393 reachable: false,
394 latencyMs: Date.now() - started,
395 tableCount: null,
396 headCommit: null,
397 error: err instanceof Error ? err.message : String(err),
398 };
399 }
400}
401
402/**
403 * Close every cached per-project database pool. Called on shutdown alongside
404 * `closeDataPlane`.
405 */
406export async function closeProjectDbPools(): Promise<void> {
407 const pools = Array.from(_projPools.values());
408 _projPools.clear();
409 await Promise.all(pools.map((p) => p.end()));
410}
411
412export async function pingDataPlane(): Promise<boolean> {
413 if (!env.BRIVEN_DATA_PLANE_URL) return false;
414 try {
415 await client().query('SELECT 1');
416 return true;
417 } catch (err) {
418 log.warn('data_plane_ping_failed', {
419 message: err instanceof Error ? err.message : String(err),
420 });
421 return false;
422 }
423}
424
425/**
426 * DEEP vault liveness — a REAL-login probe, not a warm-pool `SELECT 1`.
427 *
428 * Why this exists: `pingDataPlane` reuses the long-lived admin pool, whose
429 * connection authenticated ONCE at pool-creation. If the vault's auth state
430 * later breaks (stale-password / auth.db corruption — the exact 2026-07-02
431 * outage), that warm connection keeps answering `SELECT 1` while database
432 * creation is dead. This probe opens a FRESH connection every call (so the
433 * auth handshake is re-tested now) and runs the SAME `pg_database` catalog
434 * read that gates every `CREATE DATABASE` in provisionProjectDatabase — so a
435 * green result genuinely means "provisioning could run", not just "a socket
436 * is open". DoltGres-proven query (see provisionProjectDatabase:91).
437 *
438 * Timeouts are client-side (node-postgres), so they don't depend on DoltGres
439 * honouring a server-side statement_timeout. Never throws — collapses any
440 * failure to false. The 3-strike seatbelt that decides /ready lives in
441 * platform-health.ts so a single blip can never pull the live site.
442 */
443export async function deepPingDataPlane(): Promise<boolean> {
444 if (!env.BRIVEN_DATA_PLANE_URL) return false;
445 const probe = new pg.Client({
446 connectionString: env.BRIVEN_DATA_PLANE_URL,
447 connectionTimeoutMillis: 3000,
448 query_timeout: 3000,
449 });
450 try {
451 await probe.connect();
452 await probe.query('SELECT 1 FROM pg_database LIMIT 1');
453 return true;
454 } catch (err) {
455 log.warn('data_plane_deep_ping_failed', {
456 message: err instanceof Error ? err.message : String(err),
457 });
458 return false;
459 } finally {
460 // Close the throwaway connection; ignore teardown errors so a failed
461 // end() can't mask the probe verdict.
462 await probe.end().catch(() => {});
463 }
464}
465
466export async function closeDataPlane(): Promise<void> {
467 await closeProjectDbPools();
468 if (_client) {
469 await _client.end();
470 _client = null;
471 }
472}