konnosDashboard
studio.ts1526 lines · main
1import { brivenError, ValidationError } from '@briven/shared';
2
3import { runInProjectDatabase } from '../db/data-plane.js';
4
5/**
6 * Studio read-mode services. Phase 2 first slice — table listing only.
7 *
8 * Future slices:
9 * - rows-per-table (paginated)
10 * - column metadata (types, nullability, defaults) for a tables-detail view
11 * - inline edit (write mode) — gated on RLS-style guard rails the runtime
12 * enforces; never exposed without an admin-tier session
13 */
14
15export interface TableSummary {
16 /** Table name (stripped of schema qualifier — `notes`, not `proj_xxx.notes`). */
17 readonly name: string;
18 /** Approximate row count via `pg_class.reltuples` — fast, slightly stale. */
19 readonly approxRowCount: number;
20 /** Total relation size on disk (bytes), including indexes + toast. */
21 readonly bytes: number;
22}
23
24/**
25 * Lists tables in a project's data-plane schema. Skips the platform-owned
26 * `_briven_*` tables — those are bookkeeping the customer never edits.
27 *
28 * Approx row count comes from pg_class.reltuples which is updated by
29 * autovacuum; fast (single index lookup) but can lag a recently-bulk-
30 * inserted table by ~30s. Acceptable for a dashboard summary; if a
31 * future view needs exact counts it can issue `count(*)` per table.
32 */
33export async function listProjectTables(projectId: string): Promise<TableSummary[]> {
34 return runInProjectDatabase(projectId, async (tx) => {
35 const rows = (await tx.unsafe(
36 `
37 SELECT
38 c.relname AS table_name,
39 c.reltuples::bigint AS reltuples,
40 pg_total_relation_size(c.oid)::bigint AS bytes
41 FROM pg_class c
42 JOIN pg_namespace n ON n.oid = c.relnamespace
43 WHERE n.nspname = 'public'
44 AND c.relkind = 'r'
45 -- DoltGres lacks the LIKE ... ESCAPE function (errno 1105
46 -- 'not_like_escape'), so the _briven_ housekeeping filter uses a
47 -- plain prefix compare instead. left()/<> are both supported.
48 AND left(c.relname, 8) <> '_briven_'
49 ORDER BY c.relname
50 `,
51 )) as Array<{ table_name: string; reltuples: string | number; bytes: string | number }>;
52 return rows.map((row) => ({
53 name: row.table_name,
54 approxRowCount: Number(row.reltuples) || 0,
55 bytes: Number(row.bytes) || 0,
56 }));
57 });
58}
59
60export interface ColumnInfo {
61 readonly name: string;
62 readonly dataType: string;
63 readonly nullable: boolean;
64 readonly defaultExpr: string | null;
65 readonly ordinalPosition: number;
66 /** True if this column participates in the table's primary key. */
67 readonly isPrimaryKey: boolean;
68 /** If this column is a foreign key, the target table.column. Null otherwise. */
69 readonly references: { table: string; column: string } | null;
70}
71
72export interface TableRows {
73 readonly columns: readonly ColumnInfo[];
74 readonly rows: ReadonlyArray<Record<string, unknown>>;
75 readonly limit: number;
76 readonly offset: number;
77 readonly hasMore: boolean;
78}
79
80const TABLE_NAME_RE = /^[A-Za-z_][A-Za-z0-9_]{0,62}$/;
81
82/**
83 * Validate that a table name is a real table in the project's schema.
84 * The regex blocks SQL-injection via the table identifier; the existence
85 * check is the actual defence against cross-schema reads — only tables
86 * present in the project's `pg_namespace` will ever be queried.
87 */
88async function assertTableExists(projectId: string, tableName: string): Promise<void> {
89 if (!TABLE_NAME_RE.test(tableName)) {
90 throw new ValidationError('invalid table name', { tableName });
91 }
92 if (tableName.startsWith('_briven_')) {
93 throw new ValidationError('platform-owned tables are not queryable via studio', {
94 tableName,
95 });
96 }
97 const row = await runInProjectDatabase(projectId, async (tx) => {
98 const [r] = (await tx.unsafe(
99 `
100 SELECT EXISTS(
101 SELECT 1 FROM pg_class c
102 JOIN pg_namespace n ON n.oid = c.relnamespace
103 WHERE n.nspname = 'public'
104 AND c.relname = $1
105 AND c.relkind = 'r'
106 ) AS exists
107 `,
108 [tableName],
109 )) as Array<{ exists: boolean }>;
110 return r;
111 });
112 if (!row?.exists) {
113 throw new brivenError('not_found', `table not found: ${tableName}`, { status: 404 });
114 }
115}
116
117export async function getTableColumns(
118 projectId: string,
119 tableName: string,
120): Promise<readonly ColumnInfo[]> {
121 await assertTableExists(projectId, tableName);
122 // Single query: information_schema.columns LEFT JOINed against the
123 // table's PK column set, plus a LEFT JOIN against information_schema's
124 // FK metadata so each column row can carry its (table.column) reference
125 // if there is one. PK columns come from information_schema.statistics
126 // (index_name = 'PRIMARY'), NOT pg_index — DoltGres rejects the pg_index
127 // join ("operator does not exist: smallint = int2vector"); same
128 // workaround as listIndexes below. Verified live against DoltGres.
129 const rows = (await runInProjectDatabase(projectId, async (tx) =>
130 tx.unsafe(
131 `
132 WITH pk_cols AS (
133 SELECT column_name
134 FROM information_schema.statistics
135 WHERE table_schema = 'public'
136 AND table_name = $1
137 AND index_name = 'PRIMARY'
138 ),
139 fk_cols AS (
140 SELECT
141 kcu.column_name,
142 ccu.table_name AS fk_table,
143 ccu.column_name AS fk_column
144 FROM information_schema.table_constraints tc
145 JOIN information_schema.key_column_usage kcu
146 ON kcu.constraint_name = tc.constraint_name
147 AND kcu.table_schema = tc.table_schema
148 JOIN information_schema.constraint_column_usage ccu
149 ON ccu.constraint_name = tc.constraint_name
150 AND ccu.table_schema = tc.table_schema
151 WHERE tc.constraint_type = 'FOREIGN KEY'
152 AND tc.table_schema = 'public'
153 AND tc.table_name = $1
154 )
155 SELECT
156 c.column_name,
157 c.data_type,
158 c.is_nullable,
159 c.column_default,
160 c.ordinal_position,
161 (pk.column_name IS NOT NULL) AS is_primary_key,
162 fk.fk_table,
163 fk.fk_column
164 FROM information_schema.columns c
165 LEFT JOIN pk_cols pk ON pk.column_name = c.column_name
166 LEFT JOIN fk_cols fk ON fk.column_name = c.column_name
167 WHERE c.table_schema = 'public' AND c.table_name = $1
168 ORDER BY c.ordinal_position
169 `,
170 [tableName],
171 ),
172 )) as Array<{
173 column_name: string;
174 data_type: string;
175 is_nullable: string;
176 column_default: string | null;
177 ordinal_position: number;
178 is_primary_key: boolean;
179 fk_table: string | null;
180 fk_column: string | null;
181 }>;
182 return rows.map((row) => ({
183 name: row.column_name,
184 dataType: row.data_type,
185 nullable: row.is_nullable === 'YES',
186 defaultExpr: row.column_default,
187 ordinalPosition: row.ordinal_position,
188 isPrimaryKey: Boolean(row.is_primary_key),
189 references:
190 row.fk_table && row.fk_column ? { table: row.fk_table, column: row.fk_column } : null,
191 }));
192}
193
194const MAX_LIMIT = 200;
195const DEFAULT_LIMIT = 50;
196
197/** Supported filter operators. Allow-list — anything else returns 400. */
198export const FILTER_OPS = ['eq', 'contains', 'gt', 'lt', 'gte', 'lte'] as const;
199export type FilterOp = (typeof FILTER_OPS)[number];
200
201/**
202 * Pure WHERE-clause builder. Extracted from `getTableRows` so the SQL
203 * shape, identifier validation, operator allow-list, and value-as-param
204 * guarantees are unit-testable without a live data plane.
205 *
206 * Returns `{ clauses, params }` — caller joins with ` AND ` and prefixes
207 * `WHERE `. Throws `ValidationError` on bad column name, unknown column,
208 * or unknown operator. Values are NEVER inlined — every value is pushed
209 * into `params` and referenced as `$N`, so user-supplied strings (e.g.
210 * `'; DROP TABLE …`) cannot escape parameterisation.
211 */
212export function buildFilterClauses(
213 filters: ReadonlyArray<{
214 column: string;
215 op: FilterOp;
216 value: string | number | boolean | null;
217 }>,
218 colNames: ReadonlySet<string>,
219 tableName: string,
220): { clauses: string[]; params: unknown[] } {
221 const clauses: string[] = [];
222 const params: unknown[] = [];
223 for (const { column: col, op, value } of filters) {
224 if (!COLUMN_NAME_RE.test(col)) {
225 throw new ValidationError('invalid filter column', { column: col });
226 }
227 if (!colNames.has(col)) {
228 throw new ValidationError('filter column not found on table', {
229 table: tableName,
230 column: col,
231 });
232 }
233 if (!(FILTER_OPS as readonly string[]).includes(op)) {
234 throw new ValidationError('invalid filter operator', { op });
235 }
236 params.push(value);
237 const placeholder = `$${params.length}`;
238 switch (op) {
239 case 'eq':
240 clauses.push(`"${col}" = ${placeholder}`);
241 break;
242 case 'contains':
243 // DoltGres has no ILIKE, so `lower(col) LIKE lower(value)` gives the
244 // same case-insensitive substring match. The placeholder is wrapped
245 // server-side so callers can't smuggle pattern characters by writing
246 // `%foo%` themselves — `'%' || lower($N) || '%'` parameterises the
247 // literal value with `%` glued in SQL, not in the input.
248 clauses.push(`lower("${col}"::text) LIKE '%' || lower(${placeholder}) || '%'`);
249 break;
250 case 'gt':
251 clauses.push(`"${col}" > ${placeholder}`);
252 break;
253 case 'lt':
254 clauses.push(`"${col}" < ${placeholder}`);
255 break;
256 case 'gte':
257 clauses.push(`"${col}" >= ${placeholder}`);
258 break;
259 case 'lte':
260 clauses.push(`"${col}" <= ${placeholder}`);
261 break;
262 }
263 }
264 return { clauses, params };
265}
266
267export interface RowsOpts {
268 readonly limit?: number;
269 readonly offset?: number;
270 readonly orderBy?: { column: string; direction: 'asc' | 'desc' } | null;
271 /** Filter clauses with operators. Order is preserved in WHERE generation. */
272 readonly filters?: ReadonlyArray<{
273 column: string;
274 op: FilterOp;
275 value: string | number | boolean | null;
276 }>;
277}
278
279/**
280 * Paginated table contents. Identifier interpolation is gated by
281 * `assertTableExists` — we never accept a raw table name without
282 * confirming it's in the project's schema. Limit/offset/orderBy/filter
283 * column names are validated against the actual columns set; values
284 * pass through `sql.unsafe`'s parameterisation, never string concat.
285 *
286 * Pulls one extra row to detect `hasMore` without a separate `count(*)`
287 * — counting a 10M-row table would dominate the request.
288 */
289export async function getTableRows(
290 projectId: string,
291 tableName: string,
292 opts: RowsOpts = {},
293): Promise<TableRows> {
294 await assertTableExists(projectId, tableName);
295 const limit = clamp(opts.limit ?? DEFAULT_LIMIT, 1, MAX_LIMIT);
296 const offset = Math.max(0, opts.offset ?? 0);
297 const columns = await getTableColumns(projectId, tableName);
298 const colNames = new Set(columns.map((c) => c.name));
299
300 const { clauses: filterClauses, params } = buildFilterClauses(
301 opts.filters ?? [],
302 colNames,
303 tableName,
304 );
305 const where = filterClauses.length > 0 ? `WHERE ${filterClauses.join(' AND ')}` : '';
306
307 // Build ORDER BY — validated identifier, fixed-set direction.
308 let orderBy = '';
309 if (opts.orderBy) {
310 if (!COLUMN_NAME_RE.test(opts.orderBy.column)) {
311 throw new ValidationError('invalid order-by column', { column: opts.orderBy.column });
312 }
313 if (!colNames.has(opts.orderBy.column)) {
314 throw new ValidationError('order-by column not found on table', {
315 table: tableName,
316 column: opts.orderBy.column,
317 });
318 }
319 const dir = opts.orderBy.direction === 'desc' ? 'DESC' : 'ASC';
320 orderBy = `ORDER BY "${opts.orderBy.column}" ${dir}`;
321 }
322
323 // limit + offset are appended as the last two parameters.
324 params.push(limit + 1);
325 params.push(offset);
326 const limitOffset = `LIMIT $${params.length - 1} OFFSET $${params.length}`;
327
328 const rows = (await runInProjectDatabase(projectId, async (tx) =>
329 tx.unsafe(
330 `SELECT * FROM "${tableName}" ${where} ${orderBy} ${limitOffset}`.replace(/\s+/g, ' ').trim(),
331 params as never[],
332 ),
333 )) as Array<Record<string, unknown>>;
334 const hasMore = rows.length > limit;
335 const trimmed = hasMore ? rows.slice(0, limit) : rows;
336 return { columns, rows: trimmed, limit, offset, hasMore };
337}
338
339function clamp(n: number, lo: number, hi: number): number {
340 if (!Number.isFinite(n)) return lo;
341 if (n < lo) return lo;
342 if (n > hi) return hi;
343 return Math.floor(n);
344}
345
346const COLUMN_NAME_RE = /^[A-Za-z_][A-Za-z0-9_]{0,62}$/;
347
348/**
349 * One {column, value} pair for matching a row by primary key. For
350 * single-PK tables this is a length-1 array; for composite-keyed tables
351 * (M2M, time-series with natural keys) it's length-N. The service
352 * enforces that the caller-provided column set EXACTLY matches the
353 * table's PK column set — a partial PK would silently match many rows.
354 */
355export interface PrimaryKeyValue {
356 readonly column: string;
357 readonly value: string | number;
358}
359
360export interface UpdateCellInput {
361 readonly projectId: string;
362 readonly tableName: string;
363 readonly primaryKey: ReadonlyArray<PrimaryKeyValue>;
364 readonly column: string;
365 /** Already-typed JS value. The data plane casts via parameter binding. */
366 readonly value: unknown;
367}
368
369export interface UpdateCellResult {
370 readonly affected: number;
371}
372
373function assertPrimaryKeyMatches(
374 cols: ReadonlyArray<{ name: string; isPrimaryKey: boolean }>,
375 pk: ReadonlyArray<PrimaryKeyValue>,
376): void {
377 if (pk.length === 0) {
378 throw new ValidationError('primary key array must not be empty', {});
379 }
380 for (const pair of pk) {
381 if (!COLUMN_NAME_RE.test(pair.column)) {
382 throw new ValidationError('invalid primary-key column', { column: pair.column });
383 }
384 }
385 const callerSet = new Set(pk.map((p) => p.column));
386 if (callerSet.size !== pk.length) {
387 throw new ValidationError('duplicate primary-key column in request', {});
388 }
389 const tablePkSet = new Set(cols.filter((c) => c.isPrimaryKey).map((c) => c.name));
390 if (tablePkSet.size === 0) {
391 throw new ValidationError('table has no primary key — inline edits disabled', {});
392 }
393 if (tablePkSet.size !== callerSet.size) {
394 throw new ValidationError(
395 `primary-key column count mismatch — table has ${tablePkSet.size}, request has ${callerSet.size}`,
396 {},
397 );
398 }
399 for (const name of callerSet) {
400 if (!tablePkSet.has(name)) {
401 throw new ValidationError("primary-key column doesn't match the table's pk", {
402 column: name,
403 });
404 }
405 }
406}
407
408/**
409 * Update a single cell on a single row. Admin-tier guarded at the route
410 * layer; this service additionally:
411 *
412 * - validates the table + column identifiers against existence in the
413 * project's schema (no SQL injection through identifiers)
414 * - refuses to touch platform-owned `_briven_*` tables
415 * - parameterises the row-key + the new value so the only thing
416 * interpolated is the verified table/column name
417 * - requires the primary-key array to match the table's pk EXACTLY —
418 * a partial PK on a composite-keyed table would silently match many
419 * rows and clobber every one
420 */
421export async function updateCell(input: UpdateCellInput): Promise<UpdateCellResult> {
422 // Single-column update is the one-value special case of a multi-column
423 // update — delegate so both paths share one validated, snap-back-safe
424 // SQL builder (see updateRow).
425 return updateRow({
426 projectId: input.projectId,
427 tableName: input.tableName,
428 primaryKey: input.primaryKey,
429 values: { [input.column]: input.value },
430 });
431}
432
433export interface UpdateRowInput {
434 readonly projectId: string;
435 readonly tableName: string;
436 readonly primaryKey: ReadonlyArray<PrimaryKeyValue>;
437 /**
438 * Column → new value. Every key is validated against the real schema and
439 * COLUMN_NAME_RE; every value is parameter-bound. Must contain ≥1 column.
440 */
441 readonly values: Record<string, unknown>;
442}
443
444/**
445 * Update one OR many columns on a single row, matched by primary key.
446 * Backs both the single-column studio cell edit (`updateCell` delegates
447 * here) and the multi-column dashboard save. Like `updateCell` it:
448 *
449 * - validates the table + every column identifier against existence in the
450 * project's schema (no SQL injection through identifiers)
451 * - refuses to touch platform-owned `_briven_*` tables
452 * - requires the primary-key array to match the table's pk EXACTLY —
453 * a partial PK on a composite-keyed table would silently match many rows
454 * - parameterises every SET value + every row-key value; the only things
455 * interpolated are verified table/column names
456 * - SETs all columns in ONE `UPDATE ... SET c1=$1, c2=$2, ... WHERE ...`
457 * statement inside the same `runInProjectDatabase` +
458 * `SET dolt_transaction_commit = 1` pattern as the rest of studio
459 *
460 * Snap-back fix: recovers the affected-row count via `RETURNING <pkColumn>`
461 * (a guaranteed-real column) rather than `RETURNING 1`. DoltGres accepts
462 * `RETURNING <column>` (proven by the dolt-compat alarm) but a bare literal
463 * `RETURNING 1` on UPDATE threw — which reverted single-cell edits in the
464 * dashboard ("snap-back"). The pg ProjectTx adapter returns only the rows
465 * array (no `.count`), so a real RETURNING column is how we count.
466 */
467export async function updateRow(input: UpdateRowInput): Promise<UpdateCellResult> {
468 await assertTableExists(input.projectId, input.tableName);
469 const setKeys = Object.keys(input.values);
470 if (setKeys.length === 0) {
471 throw new ValidationError('update requires at least one column', {});
472 }
473 for (const k of setKeys) {
474 if (!COLUMN_NAME_RE.test(k)) {
475 throw new ValidationError('invalid column name', { column: k });
476 }
477 }
478 const cols = await getTableColumns(input.projectId, input.tableName);
479 const colNames = new Set(cols.map((c) => c.name));
480 for (const k of setKeys) {
481 if (!colNames.has(k)) {
482 throw new ValidationError('column not found on table', {
483 table: input.tableName,
484 column: k,
485 });
486 }
487 }
488 assertPrimaryKeyMatches(cols, input.primaryKey);
489
490 // Param layout: $1..$M are the SET values (in setKeys order), then
491 // $M+1..$M+N are the WHERE (primary-key) values. Every identifier stays
492 // inside double-quotes; every value is parameter-bound.
493 const params: unknown[] = [];
494 const setSql = setKeys
495 .map((k) => {
496 params.push(input.values[k]);
497 return `"${k}" = $${params.length}`;
498 })
499 .join(', ');
500 const whereSql = input.primaryKey
501 .map((p) => {
502 params.push(p.value);
503 return `"${p.column}" = $${params.length}`;
504 })
505 .join(' AND ');
506 // `RETURNING <pkColumn>` (a real column) recovers the affected-row count —
507 // see the snap-back note above; a bare `RETURNING 1` reverted edits.
508 const pkReturn = `"${input.primaryKey[0]!.column}"`;
509 const rows = await runInProjectDatabase(input.projectId, async (tx) => {
510 await tx.unsafe('SET dolt_transaction_commit = 1');
511 return tx.unsafe(
512 `UPDATE "${input.tableName}" SET ${setSql} WHERE ${whereSql} RETURNING ${pkReturn}`,
513 params as unknown as never[],
514 );
515 });
516 return { affected: rows.length };
517}
518
519export interface InsertRowInput {
520 readonly projectId: string;
521 readonly tableName: string;
522 /** Column → value. Every key is validated against the actual schema. */
523 readonly values: Record<string, unknown>;
524}
525
526export interface InsertRowResult {
527 readonly inserted: Record<string, unknown> | null;
528}
529
530/**
531 * Insert one row, returning the row as the database stored it (with
532 * defaults filled in). Validates that every key in `values` is a real
533 * column on the table. Builds a parameterised INSERT — the only things
534 * interpolated are the verified schema/table/column identifiers.
535 */
536export async function insertRow(input: InsertRowInput): Promise<InsertRowResult> {
537 await assertTableExists(input.projectId, input.tableName);
538 const cols = await getTableColumns(input.projectId, input.tableName);
539 const colNames = new Set(cols.map((c) => c.name));
540 const keys = Object.keys(input.values);
541 for (const k of keys) {
542 if (!COLUMN_NAME_RE.test(k)) {
543 throw new ValidationError('invalid column name', { column: k });
544 }
545 if (!colNames.has(k)) {
546 throw new ValidationError('column not found on table', {
547 table: input.tableName,
548 column: k,
549 });
550 }
551 }
552 if (keys.length === 0) {
553 throw new ValidationError('insert requires at least one column', {});
554 }
555
556 const placeholders = keys.map((_, i) => `$${i + 1}`).join(', ');
557 const cols_sql = keys.map((k) => `"${k}"`).join(', ');
558 const params = keys.map((k) => input.values[k] as never);
559 const rows = (await runInProjectDatabase(input.projectId, async (tx) => {
560 await tx.unsafe('SET dolt_transaction_commit = 1');
561 return tx.unsafe(
562 `INSERT INTO "${input.tableName}" (${cols_sql}) VALUES (${placeholders}) RETURNING *`,
563 params,
564 );
565 })) as Array<Record<string, unknown>>;
566 return { inserted: rows[0] ?? null };
567}
568
569export interface InsertRowsInput {
570 readonly projectId: string;
571 readonly tableName: string;
572 /**
573 * The rows to insert. Every row must share the SAME set of columns (a
574 * uniform column set is required to build one multi-row INSERT), and
575 * every key is validated against the actual schema.
576 */
577 readonly rows: ReadonlyArray<Record<string, unknown>>;
578}
579
580export interface InsertRowsResult {
581 readonly inserted: number;
582 readonly rows: Array<Record<string, unknown>>;
583}
584
585/** Cap on rows per bulk insert — keeps one statement's parameter count sane. */
586const MAX_BULK_INSERT_ROWS = 500;
587
588/**
589 * Insert MANY rows in a single parameterised multi-row INSERT, returning the
590 * rows as the database stored them (with defaults filled in). This exists so a
591 * client can migrate a whole table in ONE request instead of one-request-per-row
592 * (which trips the per-minute mutate rate limit).
593 *
594 * Modelled on `insertRow`:
595 * - validates that every key across the rows is a real column on the table
596 * - requires all rows to share the SAME column set (uniform, so a single
597 * `VALUES (...),(...)` statement can be built)
598 * - builds a parameterised INSERT — the only things interpolated are the
599 * verified table/column identifiers; every value is parameter-bound in
600 * row-major order
601 * - runs inside one `runInProjectDatabase` transaction with the same
602 * `SET dolt_transaction_commit = 1` first statement as insertRow
603 */
604export async function insertRows(input: InsertRowsInput): Promise<InsertRowsResult> {
605 if (input.rows.length === 0) {
606 throw new ValidationError('bulk insert requires at least one row', {});
607 }
608 if (input.rows.length > MAX_BULK_INSERT_ROWS) {
609 throw new ValidationError(
610 `bulk insert too large — max ${MAX_BULK_INSERT_ROWS} rows per request; split into batches of ${MAX_BULK_INSERT_ROWS}`,
611 { max: MAX_BULK_INSERT_ROWS, received: input.rows.length },
612 );
613 }
614
615 await assertTableExists(input.projectId, input.tableName);
616 const cols = await getTableColumns(input.projectId, input.tableName);
617 const colNames = new Set(cols.map((c) => c.name));
618
619 // The first row defines the column set; every other row must match it
620 // exactly so one multi-row INSERT covers them all.
621 const keys = Object.keys(input.rows[0]!);
622 if (keys.length === 0) {
623 throw new ValidationError('bulk insert requires at least one column', {});
624 }
625 for (const k of keys) {
626 if (!COLUMN_NAME_RE.test(k)) {
627 throw new ValidationError('invalid column name', { column: k });
628 }
629 if (!colNames.has(k)) {
630 throw new ValidationError('column not found on table', {
631 table: input.tableName,
632 column: k,
633 });
634 }
635 }
636
637 const keySet = new Set(keys);
638 for (let i = 0; i < input.rows.length; i++) {
639 const rowKeys = Object.keys(input.rows[i]!);
640 if (
641 rowKeys.length !== keys.length ||
642 !rowKeys.every((k) => keySet.has(k))
643 ) {
644 throw new ValidationError(
645 `row ${i} has a different column set — every row must share the same columns for a bulk insert`,
646 { rowIndex: i, expected: keys, received: rowKeys },
647 );
648 }
649 }
650
651 const cols_sql = keys.map((k) => `"${k}"`).join(', ');
652 // Values are laid out row-major: row 0 gets $1..$K, row 1 gets $K+1..$2K, etc.
653 const valuesSql = input.rows
654 .map((_, r) => `(${keys.map((_, k) => `$${r * keys.length + k + 1}`).join(', ')})`)
655 .join(', ');
656 const params: never[] = [];
657 for (const row of input.rows) {
658 for (const k of keys) {
659 params.push(row[k] as never);
660 }
661 }
662
663 const rows = (await runInProjectDatabase(input.projectId, async (tx) => {
664 await tx.unsafe('SET dolt_transaction_commit = 1');
665 return tx.unsafe(
666 `INSERT INTO "${input.tableName}" (${cols_sql}) VALUES ${valuesSql} RETURNING *`,
667 params,
668 );
669 })) as Array<Record<string, unknown>>;
670 return { inserted: input.rows.length, rows };
671}
672
673export interface DeleteRowInput {
674 readonly projectId: string;
675 readonly tableName: string;
676 readonly primaryKey: ReadonlyArray<PrimaryKeyValue>;
677}
678
679export interface DeleteRowResult {
680 readonly affected: number;
681}
682
683export interface QueryResult {
684 readonly columns: ReadonlyArray<{ name: string; dataType: string }>;
685 readonly rows: ReadonlyArray<Record<string, unknown>>;
686 readonly rowCount: number;
687 readonly command: string;
688 readonly elapsedMs: number;
689}
690
691/**
692 * Run an arbitrary SQL statement against the project's OWN DoltGres database.
693 * Isolation now comes from the database boundary itself — the connection is
694 * bound to `proj_<id>`, so the user can only ever touch their own tables (the
695 * legacy per-project SET LOCAL ROLE scoping is obsolete in the
696 * database-per-project model).
697 *
698 * - bound to the project's own database (public schema) by the connection
699 * itself — no search_path pinning needed (DoltGres has no SET LOCAL)
700 * - transactional via `runInProjectDatabase`: rolls back if the statement
701 * errors, and `dolt_transaction_commit` makes the COMMIT a real Dolt commit
702 * for any writes
703 * - audit-logged by the route handler (sql text + elapsed)
704 *
705 * NB: trusting the user with their own data is fine; this lets them DROP
706 * their own tables if they want to.
707 */
708export async function executeQuery(projectId: string, sqlText: string): Promise<QueryResult> {
709 if (typeof sqlText !== 'string' || sqlText.trim() === '') {
710 throw new ValidationError('sql is required', {});
711 }
712 if (sqlText.length > 16 * 1024) {
713 throw new ValidationError('sql payload too large', { max: 16 * 1024 });
714 }
715 const t0 = Date.now();
716
717 // `runInProjectDatabase` opens one BEGIN/COMMIT transaction on a connection
718 // bound to the project's own database. `dolt_transaction_commit = 1` is the
719 // first statement so any write the user runs becomes a real Dolt commit
720 // (mirrors apps/runtime/src/db.ts:withProjectTx).
721 const out = (await runInProjectDatabase(projectId, async (tx) => {
722 await tx.unsafe('SET dolt_transaction_commit = 1');
723 // No SET LOCAL: DoltGres doesn't support it, and the connection is already
724 // bound to the project's own database (public schema), so search_path
725 // pinning is unnecessary. statement_timeout isn't enforceable here.
726 const result = (await tx.unsafe(sqlText)) as Array<Record<string, unknown>>;
727 const rows = Array.isArray(result) ? [...result] : [];
728 const first = rows[0];
729 return {
730 rows,
731 // The pg ProjectTx adapter returns only the rows array — there is no
732 // `.count`/`.command`/`.columns` like postgres.js exposed. Derive what
733 // we can: row count and column names from the first row's keys.
734 count: rows.length,
735 command: 'UNKNOWN',
736 columns: first ? Object.keys(first).map((name) => ({ name })) : [],
737 };
738 })) as {
739 rows: Array<Record<string, unknown>>;
740 count: number;
741 command: string;
742 columns: Array<{ name: string; type?: number }>;
743 };
744
745 const elapsedMs = Date.now() - t0;
746
747 // We don't have a cheap type-name lookup here. Surface the column name and
748 // leave dataType as 'unknown' so the UI can still render a table. Future
749 // slice: join against pg_type.
750 const columns = out.columns.map((c) => ({ name: c.name, dataType: 'unknown' }));
751
752 return {
753 columns,
754 rows: out.rows,
755 rowCount: out.count,
756 command: out.command,
757 elapsedMs,
758 };
759}
760
761/**
762 * Map a postgres `data_type` (information_schema flavour) onto the briven
763 * schema-DSL helper name. Best-effort — anything outside the known list
764 * is emitted as a TODO comment so the user can fix it by hand.
765 */
766function pgTypeToDslHelper(pgType: string): { helper: string; comment?: string } {
767 switch (pgType) {
768 case 'text':
769 case 'character varying':
770 case 'varchar':
771 return { helper: 'text()' };
772 case 'integer':
773 case 'int4':
774 return { helper: 'integer()' };
775 case 'bigint':
776 case 'int8':
777 return { helper: 'bigint()' };
778 case 'boolean':
779 return { helper: 'boolean()' };
780 case 'timestamp with time zone':
781 case 'timestamp without time zone':
782 case 'timestamp':
783 case 'timestamptz':
784 return { helper: 'timestamp()' };
785 case 'jsonb':
786 return { helper: 'jsonb()' };
787 case 'uuid':
788 return { helper: 'uuid()' };
789 case 'numeric':
790 case 'decimal':
791 return { helper: 'numeric()' };
792 default:
793 return { helper: 'text()', comment: `TODO: original type ${pgType} — adjust DSL helper` };
794 }
795}
796
797/**
798 * Generate an equivalent `briven/schema.ts` for the entire project. Used by
799 * the dashboard's "copy as schema.ts" affordance so a database the user
800 * built by clicking around in studio can be committed to git and managed
801 * via the CLI from then on.
802 */
803export async function exportSchemaAsDsl(projectId: string): Promise<string> {
804 const tables = await listProjectTables(projectId);
805 if (tables.length === 0) {
806 return [
807 "import { schema, table } from '@briven/cli/schema';",
808 '',
809 '// no tables yet — create one from the dashboard or define it here.',
810 'export default schema({});',
811 '',
812 ].join('\n');
813 }
814 const tableNames = tables.map((t) => t.name);
815 const perTable = await Promise.all(
816 tableNames.map(async (t) => ({ table: t, columns: await getTableColumns(projectId, t) })),
817 );
818
819 // Collect every helper actually used so the import line stays minimal.
820 const helpersUsed = new Set<string>();
821 helpersUsed.add('schema');
822 helpersUsed.add('table');
823
824 const lines: string[] = [];
825 for (const { table: tName, columns } of perTable) {
826 lines.push(` ${tName}: table({`);
827 for (const col of columns) {
828 const { helper, comment } = pgTypeToDslHelper(col.dataType);
829 const helperName = helper.replace(/\(\)$/, '');
830 helpersUsed.add(helperName);
831 const modifiers: string[] = [];
832 if (col.isPrimaryKey) modifiers.push('.primaryKey()');
833 if (!col.nullable && !col.isPrimaryKey) modifiers.push('.notNull()');
834 if (col.defaultExpr) {
835 // Single-quote the default for the DSL — it accepts a raw expression.
836 modifiers.push(`.default(${JSON.stringify(col.defaultExpr)})`);
837 }
838 if (col.references) {
839 modifiers.push(`.references('${col.references.table}', '${col.references.column}')`);
840 }
841 const commentSuffix = comment ? ` // ${comment}` : '';
842 lines.push(` ${col.name}: ${helper}${modifiers.join('')},${commentSuffix}`);
843 }
844 lines.push(' }),');
845 }
846
847 const helpersList = Array.from(helpersUsed).sort();
848 return [
849 `import { ${helpersList.join(', ')} } from '@briven/cli/schema';`,
850 '',
851 '// generated from the live database by briven studio.',
852 "// commit this file and run `briven deploy` to keep the schema in git.",
853 'export default schema({',
854 ...lines,
855 '});',
856 '',
857 ].join('\n');
858}
859
860/**
861 * Studio-supported column type vocabulary. Keep this list short — exotic
862 * types (arrays, enums, ranges) still work via the CLI schema.ts path. The
863 * names map 1:1 onto the actual postgres type a CREATE/ALTER statement uses.
864 */
865export const STUDIO_COLUMN_TYPES = [
866 'text',
867 'integer',
868 'bigint',
869 'boolean',
870 'timestamptz',
871 'jsonb',
872 'uuid',
873 'numeric',
874] as const;
875export type StudioColumnType = (typeof STUDIO_COLUMN_TYPES)[number];
876
877export interface StudioColumnReference {
878 readonly table: string;
879 readonly column: string;
880 /** `cascade` | `restrict` | `setNull` | `noAction` — defaults to `noAction`. */
881 readonly onDelete?: 'cascade' | 'restrict' | 'setNull' | 'noAction';
882}
883
884export interface StudioColumnSpec {
885 readonly name: string;
886 readonly type: StudioColumnType;
887 readonly notNull?: boolean;
888 readonly primaryKey?: boolean;
889 /** Raw SQL default expression — e.g. `'now()'`, `'gen_random_uuid()'`, `'0'`. */
890 readonly defaultExpr?: string | null;
891 /** Optional foreign-key target. Same schema only. */
892 readonly references?: StudioColumnReference | null;
893}
894
895export interface CreateTableInput {
896 readonly projectId: string;
897 readonly tableName: string;
898 readonly columns: readonly StudioColumnSpec[];
899}
900
901const DEFAULT_EXPR_RE = /^[A-Za-z0-9_().,:\s'"\-+/*]{1,200}$/;
902
903function assertColumnSpec(spec: StudioColumnSpec): void {
904 if (!COLUMN_NAME_RE.test(spec.name)) {
905 throw new ValidationError('invalid column name', { column: spec.name });
906 }
907 if (!STUDIO_COLUMN_TYPES.includes(spec.type)) {
908 throw new ValidationError('unsupported column type', {
909 type: spec.type,
910 supported: STUDIO_COLUMN_TYPES.join(','),
911 });
912 }
913 if (spec.defaultExpr != null && !DEFAULT_EXPR_RE.test(spec.defaultExpr)) {
914 throw new ValidationError('default expression contains disallowed characters', {
915 column: spec.name,
916 });
917 }
918 if (spec.references) {
919 if (!TABLE_NAME_RE.test(spec.references.table)) {
920 throw new ValidationError('invalid referenced table name', {
921 table: spec.references.table,
922 });
923 }
924 if (!COLUMN_NAME_RE.test(spec.references.column)) {
925 throw new ValidationError('invalid referenced column name', {
926 column: spec.references.column,
927 });
928 }
929 }
930}
931
932const ON_DELETE_SQL: Record<NonNullable<StudioColumnReference['onDelete']>, string> = {
933 cascade: 'CASCADE',
934 restrict: 'RESTRICT',
935 setNull: 'SET NULL',
936 noAction: 'NO ACTION',
937};
938
939function columnDdl(
940 spec: StudioColumnSpec,
941 opts: { suppressInlinePk?: boolean } = {},
942): string {
943 const parts = [`"${spec.name}" ${spec.type}`];
944 // For composite PKs, the inline PRIMARY KEY is suppressed — a single
945 // table-level PRIMARY KEY (col, col) constraint is emitted by the
946 // caller instead. NOT NULL is implicit on PK columns in Postgres so
947 // the existing "skip explicit NOT NULL when PK" behaviour stays right
948 // whether the key is inline or composite.
949 if (spec.primaryKey && !opts.suppressInlinePk) parts.push('PRIMARY KEY');
950 if (spec.notNull && !spec.primaryKey) parts.push('NOT NULL');
951 if (spec.defaultExpr != null && spec.defaultExpr !== '') {
952 parts.push(`DEFAULT ${spec.defaultExpr}`);
953 }
954 if (spec.references) {
955 // FK target lives in the same project database — unqualified identifier
956 // resolves to `public`, so no schema prefix is needed.
957 const onDelete = ON_DELETE_SQL[spec.references.onDelete ?? 'noAction'];
958 parts.push(
959 `REFERENCES "${spec.references.table}" ("${spec.references.column}") ON DELETE ${onDelete}`,
960 );
961 }
962 return parts.join(' ');
963}
964
965async function assertFkTarget(
966 projectId: string,
967 ref: StudioColumnReference,
968 selfTable: string,
969): Promise<void> {
970 // Self-reference is allowed (e.g. parent_id on comments) — skip the
971 // existence probe so brand-new tables can FK to themselves.
972 if (ref.table === selfTable) return;
973 const row = await runInProjectDatabase(projectId, async (tx) => {
974 const [r] = (await tx.unsafe(
975 `
976 SELECT EXISTS(
977 SELECT 1 FROM information_schema.columns
978 WHERE table_schema = 'public'
979 AND table_name = $1
980 AND column_name = $2
981 ) AS exists
982 `,
983 [ref.table, ref.column],
984 )) as Array<{ exists: boolean }>;
985 return r;
986 });
987 if (!row?.exists) {
988 throw new ValidationError('referenced table.column does not exist', {
989 table: ref.table,
990 column: ref.column,
991 });
992 }
993}
994
995/**
996 * Create a new table in the project's schema. Refuses platform-owned
997 * `_briven_*` names, enforces the studio type whitelist on every column,
998 * and demands at least one primary-key column so the row-edit path keeps
999 * working downstream.
1000 */
1001export async function createTable(input: CreateTableInput): Promise<{ name: string }> {
1002 if (!TABLE_NAME_RE.test(input.tableName)) {
1003 throw new ValidationError('invalid table name', { tableName: input.tableName });
1004 }
1005 if (input.tableName.startsWith('_briven_')) {
1006 throw new ValidationError('platform-owned table prefix is reserved', {
1007 tableName: input.tableName,
1008 });
1009 }
1010 if (input.columns.length === 0) {
1011 throw new ValidationError('a table needs at least one column', {});
1012 }
1013 const seen = new Set<string>();
1014 let pkCount = 0;
1015 for (const col of input.columns) {
1016 assertColumnSpec(col);
1017 if (seen.has(col.name)) {
1018 throw new ValidationError('duplicate column name', { column: col.name });
1019 }
1020 seen.add(col.name);
1021 if (col.primaryKey) pkCount++;
1022 }
1023 if (pkCount === 0) {
1024 throw new ValidationError('one column must be marked primaryKey', {});
1025 }
1026
1027 for (const col of input.columns) {
1028 if (col.references) {
1029 await assertFkTarget(input.projectId, col.references, input.tableName);
1030 }
1031 }
1032
1033 // Composite PK → table-level constraint; single PK stays inline so the
1034 // SQL output is unchanged for every non-M2M shape.
1035 const isComposite = pkCount > 1;
1036 const colsSql = input.columns
1037 .map((c) => columnDdl(c, { suppressInlinePk: isComposite }))
1038 .join(', ');
1039 const pkClause = isComposite
1040 ? `, PRIMARY KEY (${input.columns
1041 .filter((c) => c.primaryKey)
1042 .map((c) => `"${c.name}"`)
1043 .join(', ')})`
1044 : '';
1045 await runInProjectDatabase(input.projectId, async (tx) => {
1046 await tx.unsafe('SET dolt_transaction_commit = 1');
1047 await tx.unsafe(`CREATE TABLE "${input.tableName}" (${colsSql}${pkClause})`);
1048 });
1049 return { name: input.tableName };
1050}
1051
1052/**
1053 * Drop a table (cascade off — refuses if FKs point at it, so callers get
1054 * a clear error rather than nuking dependent data silently).
1055 */
1056export async function dropTable(projectId: string, tableName: string): Promise<void> {
1057 await assertTableExists(projectId, tableName);
1058 await runInProjectDatabase(projectId, async (tx) => {
1059 await tx.unsafe('SET dolt_transaction_commit = 1');
1060 await tx.unsafe(`DROP TABLE "${tableName}"`);
1061 });
1062}
1063
1064/**
1065 * TRUNCATE a table — wipes every row but keeps the schema. DoltGres does not
1066 * support `RESTART IDENTITY` (no serial sequences) nor `CASCADE`, so neither
1067 * is emitted; cascade=true is rejected with a clear error rather than sent as
1068 * SQL DoltGres can't run.
1069 */
1070export async function truncateTable(
1071 projectId: string,
1072 tableName: string,
1073 cascade = false,
1074): Promise<void> {
1075 await assertTableExists(projectId, tableName);
1076 if (cascade) {
1077 throw new ValidationError('TRUNCATE CASCADE is not supported on DoltGres', {});
1078 }
1079 await runInProjectDatabase(projectId, async (tx) => {
1080 await tx.unsafe('SET dolt_transaction_commit = 1');
1081 await tx.unsafe(`TRUNCATE TABLE "${tableName}"`);
1082 });
1083}
1084
1085/**
1086 * Add a column to an existing table. Same type whitelist + default-expr
1087 * regex as createTable.
1088 */
1089export async function addColumn(input: {
1090 projectId: string;
1091 tableName: string;
1092 column: StudioColumnSpec;
1093}): Promise<void> {
1094 await assertTableExists(input.projectId, input.tableName);
1095 assertColumnSpec(input.column);
1096 if (input.column.primaryKey) {
1097 throw new ValidationError('cannot add a primary-key column to an existing table via studio', {});
1098 }
1099 const cols = await getTableColumns(input.projectId, input.tableName);
1100 if (cols.find((c) => c.name === input.column.name)) {
1101 throw new ValidationError('column already exists', { column: input.column.name });
1102 }
1103 if (input.column.references) {
1104 await assertFkTarget(input.projectId, input.column.references, input.tableName);
1105 }
1106 await runInProjectDatabase(input.projectId, async (tx) => {
1107 await tx.unsafe('SET dolt_transaction_commit = 1');
1108 await tx.unsafe(
1109 `ALTER TABLE "${input.tableName}" ADD COLUMN ${columnDdl(input.column)}`,
1110 );
1111 });
1112}
1113
1114export interface RelationshipEdge {
1115 /** Table holding the FK column. */
1116 readonly fromTable: string;
1117 readonly fromColumn: string;
1118 /** Table the FK points at. */
1119 readonly toTable: string;
1120 readonly toColumn: string;
1121}
1122
1123/**
1124 * Every foreign-key edge in the project's schema. Drives the studio
1125 * overview's "relationships" panel so users can see the shape of their
1126 * data model at a glance.
1127 */
1128export interface FullSchemaTable {
1129 readonly name: string;
1130 readonly columns: readonly ColumnInfo[];
1131}
1132
1133export interface FullSchema {
1134 readonly tables: readonly FullSchemaTable[];
1135 readonly relationships: readonly RelationshipEdge[];
1136}
1137
1138/**
1139 * One-shot read of every table + its columns + every FK edge. Drives the
1140 * "schema overview" page so users see the entire data model in one place
1141 * without paginating through each table individually.
1142 *
1143 * Single information_schema sweep — no per-table round-trip.
1144 */
1145export async function getFullSchema(projectId: string): Promise<FullSchema> {
1146 const tables = await listProjectTables(projectId);
1147 const perTable = await Promise.all(
1148 tables.map(async (t) => ({
1149 name: t.name,
1150 columns: await getTableColumns(projectId, t.name),
1151 })),
1152 );
1153 const relationships = await listRelationships(projectId);
1154 return { tables: perTable, relationships };
1155}
1156
1157export async function listRelationships(projectId: string): Promise<readonly RelationshipEdge[]> {
1158 const rows = (await runInProjectDatabase(projectId, async (tx) =>
1159 tx.unsafe(`
1160 SELECT
1161 tc.table_name AS from_table,
1162 kcu.column_name AS from_column,
1163 ccu.table_name AS to_table,
1164 ccu.column_name AS to_column
1165 FROM information_schema.table_constraints tc
1166 JOIN information_schema.key_column_usage kcu
1167 ON kcu.constraint_name = tc.constraint_name
1168 AND kcu.table_schema = tc.table_schema
1169 JOIN information_schema.constraint_column_usage ccu
1170 ON ccu.constraint_name = tc.constraint_name
1171 AND ccu.table_schema = tc.table_schema
1172 WHERE tc.constraint_type = 'FOREIGN KEY'
1173 AND tc.table_schema = 'public'
1174 ORDER BY tc.table_name, kcu.column_name
1175 `),
1176 )) as Array<{
1177 from_table: string;
1178 from_column: string;
1179 to_table: string;
1180 to_column: string;
1181 }>;
1182 return rows.map((r) => ({
1183 fromTable: r.from_table,
1184 fromColumn: r.from_column,
1185 toTable: r.to_table,
1186 toColumn: r.to_column,
1187 }));
1188}
1189
1190export interface IndexSummary {
1191 /** Index name as it lives in pg_class. */
1192 readonly name: string;
1193 /** Ordered list of column names participating in the index. */
1194 readonly columns: readonly string[];
1195 /** True if the index was created with UNIQUE. */
1196 readonly unique: boolean;
1197 /** True if this is the table's primary-key index — never droppable from studio. */
1198 readonly isPrimary: boolean;
1199}
1200
1201const INDEX_NAME_RE = /^[A-Za-z_][A-Za-z0-9_]{0,62}$/;
1202
1203/**
1204 * Every index on a table — primary, unique, and plain. Used by the schema
1205 * panel so the user can see what's already indexed before adding more.
1206 */
1207export async function listIndexes(
1208 projectId: string,
1209 tableName: string,
1210): Promise<readonly IndexSummary[]> {
1211 await assertTableExists(projectId, tableName);
1212 // DoltGres rejects the pg_index/array_position introspection join
1213 // ("operator does not exist: smallint = int2vector"). information_schema.
1214 // statistics IS supported (MySQL-heritage view) and gives one row per
1215 // (index, column) with order + uniqueness. The primary key surfaces as the
1216 // synthetic name 'PRIMARY'. NB: DoltGres returns these column keys in
1217 // UPPERCASE, so we read each row case-insensitively.
1218 const rawRows = (await runInProjectDatabase(projectId, async (tx) =>
1219 tx.unsafe(
1220 `
1221 SELECT index_name, column_name, seq_in_index, non_unique
1222 FROM information_schema.statistics
1223 WHERE table_schema = 'public'
1224 AND table_name = $1
1225 ORDER BY index_name, seq_in_index
1226 `,
1227 [tableName],
1228 ),
1229 )) as Array<Record<string, unknown>>;
1230
1231 // Normalise UPPER/lower key casing into a predictable shape.
1232 const pick = (row: Record<string, unknown>, key: string): unknown =>
1233 row[key] ?? row[key.toUpperCase()] ?? row[key.toLowerCase()];
1234
1235 // Group the per-column rows into one entry per index, preserving column order.
1236 const byIndex = new Map<string, { columns: string[]; unique: boolean; isPrimary: boolean }>();
1237 for (const row of rawRows) {
1238 const indexName = String(pick(row, 'index_name'));
1239 const columnName = String(pick(row, 'column_name'));
1240 const nonUnique = Number(pick(row, 'non_unique')) === 1;
1241 let entry = byIndex.get(indexName);
1242 if (!entry) {
1243 entry = { columns: [], unique: !nonUnique, isPrimary: indexName === 'PRIMARY' };
1244 byIndex.set(indexName, entry);
1245 }
1246 entry.columns.push(columnName);
1247 }
1248
1249 const summaries: IndexSummary[] = Array.from(byIndex.entries()).map(([name, e]) => ({
1250 name,
1251 columns: e.columns,
1252 unique: e.unique,
1253 isPrimary: e.isPrimary,
1254 }));
1255 // Primary key first, then the rest by name — matches the previous ordering.
1256 return summaries.sort((a, b) =>
1257 a.isPrimary === b.isPrimary ? a.name.localeCompare(b.name) : a.isPrimary ? -1 : 1,
1258 );
1259}
1260
1261export interface CreateIndexInput {
1262 readonly projectId: string;
1263 readonly tableName: string;
1264 readonly columns: readonly string[];
1265 readonly unique?: boolean;
1266 /** Optional explicit index name. Defaults to `<table>_<cols>_idx`. */
1267 readonly name?: string | null;
1268}
1269
1270/**
1271 * Create an index on one or more columns. Refuses platform-owned tables
1272 * (via assertTableExists) and validates every column identifier against
1273 * the actual column set.
1274 */
1275export async function createIndex(input: CreateIndexInput): Promise<{ name: string }> {
1276 await assertTableExists(input.projectId, input.tableName);
1277 if (input.columns.length === 0) {
1278 throw new ValidationError('an index needs at least one column', {});
1279 }
1280 const cols = await getTableColumns(input.projectId, input.tableName);
1281 const colNames = new Set(cols.map((c) => c.name));
1282 for (const col of input.columns) {
1283 if (!COLUMN_NAME_RE.test(col)) {
1284 throw new ValidationError('invalid column name', { column: col });
1285 }
1286 if (!colNames.has(col)) {
1287 throw new ValidationError('column not found on table', {
1288 table: input.tableName,
1289 column: col,
1290 });
1291 }
1292 }
1293
1294 const indexName =
1295 input.name ??
1296 `${input.tableName}_${input.columns.join('_')}_idx`.replace(/[^A-Za-z0-9_]/g, '_').slice(0, 63);
1297 if (!INDEX_NAME_RE.test(indexName)) {
1298 throw new ValidationError('invalid index name', { name: indexName });
1299 }
1300
1301 const uniqueClause = input.unique ? 'UNIQUE' : '';
1302 const colsList = input.columns.map((c) => `"${c}"`).join(', ');
1303 await runInProjectDatabase(input.projectId, async (tx) => {
1304 await tx.unsafe('SET dolt_transaction_commit = 1');
1305 await tx.unsafe(
1306 `CREATE ${uniqueClause} INDEX "${indexName}" ON "${input.tableName}" (${colsList})`,
1307 );
1308 });
1309 return { name: indexName };
1310}
1311
1312/**
1313 * Drop an index by name. Refuses the primary-key index (which would
1314 * invalidate row-level operations) — that has to go via dropping the
1315 * column or the table.
1316 */
1317export async function dropIndex(
1318 projectId: string,
1319 tableName: string,
1320 indexName: string,
1321): Promise<void> {
1322 await assertTableExists(projectId, tableName);
1323 if (!INDEX_NAME_RE.test(indexName)) {
1324 throw new ValidationError('invalid index name', { name: indexName });
1325 }
1326 const all = await listIndexes(projectId, tableName);
1327 const target = all.find((i) => i.name === indexName);
1328 if (!target) {
1329 throw new ValidationError('index not found on table', { table: tableName, index: indexName });
1330 }
1331 if (target.isPrimary) {
1332 throw new ValidationError('cannot drop the primary-key index', { index: indexName });
1333 }
1334 await runInProjectDatabase(projectId, async (tx) => {
1335 await tx.unsafe('SET dolt_transaction_commit = 1');
1336 await tx.unsafe(`DROP INDEX "${indexName}"`);
1337 });
1338}
1339
1340/**
1341 * Rename a table. Refuses platform-owned `_briven_*` tables (source name)
1342 * and refuses to rename into the platform-owned prefix (target name).
1343 */
1344export async function renameTable(args: {
1345 projectId: string;
1346 oldName: string;
1347 newName: string;
1348}): Promise<void> {
1349 await assertTableExists(args.projectId, args.oldName);
1350 if (!TABLE_NAME_RE.test(args.newName)) {
1351 throw new ValidationError('invalid new table name', { newName: args.newName });
1352 }
1353 if (args.newName.startsWith('_briven_')) {
1354 throw new ValidationError('platform-owned table prefix is reserved', {
1355 newName: args.newName,
1356 });
1357 }
1358 if (args.oldName === args.newName) return;
1359 await runInProjectDatabase(args.projectId, async (tx) => {
1360 await tx.unsafe('SET dolt_transaction_commit = 1');
1361 await tx.unsafe(`ALTER TABLE "${args.oldName}" RENAME TO "${args.newName}"`);
1362 });
1363}
1364
1365/**
1366 * Rename a column on a table. PK / FK / index references survive
1367 * automatically — Postgres rewrites them by oid.
1368 */
1369export async function renameColumn(args: {
1370 projectId: string;
1371 tableName: string;
1372 oldName: string;
1373 newName: string;
1374}): Promise<void> {
1375 await assertTableExists(args.projectId, args.tableName);
1376 if (!COLUMN_NAME_RE.test(args.oldName)) {
1377 throw new ValidationError('invalid old column name', { oldName: args.oldName });
1378 }
1379 if (!COLUMN_NAME_RE.test(args.newName)) {
1380 throw new ValidationError('invalid new column name', { newName: args.newName });
1381 }
1382 if (args.oldName === args.newName) return;
1383 const cols = await getTableColumns(args.projectId, args.tableName);
1384 if (!cols.find((c) => c.name === args.oldName)) {
1385 throw new ValidationError('column not found on table', {
1386 table: args.tableName,
1387 column: args.oldName,
1388 });
1389 }
1390 if (cols.find((c) => c.name === args.newName)) {
1391 throw new ValidationError('a column with the new name already exists', {
1392 newName: args.newName,
1393 });
1394 }
1395 await runInProjectDatabase(args.projectId, async (tx) => {
1396 await tx.unsafe('SET dolt_transaction_commit = 1');
1397 await tx.unsafe(
1398 `ALTER TABLE "${args.tableName}" RENAME COLUMN "${args.oldName}" TO "${args.newName}"`,
1399 );
1400 });
1401}
1402
1403export interface AlterColumnInput {
1404 readonly projectId: string;
1405 readonly tableName: string;
1406 readonly column: string;
1407 /** Set to true to make NOT NULL; false to drop NOT NULL. */
1408 readonly notNull?: boolean;
1409 /**
1410 * Default expression. Pass `null` to drop the default. Pass a string to
1411 * set it. Omit to leave alone.
1412 */
1413 readonly defaultExpr?: string | null;
1414}
1415
1416/**
1417 * Change a column's nullability + default. Refuses to alter a PK column's
1418 * nullability (PKs are implicitly NOT NULL) and validates the default
1419 * expression against the same regex as create-column.
1420 */
1421export async function alterColumn(input: AlterColumnInput): Promise<void> {
1422 await assertTableExists(input.projectId, input.tableName);
1423 if (!COLUMN_NAME_RE.test(input.column)) {
1424 throw new ValidationError('invalid column name', { column: input.column });
1425 }
1426 const cols = await getTableColumns(input.projectId, input.tableName);
1427 const target = cols.find((c) => c.name === input.column);
1428 if (!target) {
1429 throw new ValidationError('column not found on table', {
1430 table: input.tableName,
1431 column: input.column,
1432 });
1433 }
1434 if (target.isPrimaryKey && typeof input.notNull === 'boolean') {
1435 throw new ValidationError('cannot change nullability of a primary-key column', {
1436 column: input.column,
1437 });
1438 }
1439 if (
1440 input.defaultExpr !== undefined
1441 && input.defaultExpr !== null
1442 && !DEFAULT_EXPR_RE.test(input.defaultExpr)
1443 ) {
1444 throw new ValidationError('default expression contains disallowed characters', {
1445 column: input.column,
1446 });
1447 }
1448
1449 await runInProjectDatabase(input.projectId, async (tx) => {
1450 await tx.unsafe('SET dolt_transaction_commit = 1');
1451 if (typeof input.notNull === 'boolean' && input.notNull !== !target.nullable) {
1452 const clause = input.notNull ? 'SET NOT NULL' : 'DROP NOT NULL';
1453 await tx.unsafe(
1454 `ALTER TABLE "${input.tableName}" ALTER COLUMN "${input.column}" ${clause}`,
1455 );
1456 }
1457 if (input.defaultExpr === null && target.defaultExpr !== null) {
1458 await tx.unsafe(
1459 `ALTER TABLE "${input.tableName}" ALTER COLUMN "${input.column}" DROP DEFAULT`,
1460 );
1461 } else if (typeof input.defaultExpr === 'string' && input.defaultExpr !== '') {
1462 await tx.unsafe(
1463 `ALTER TABLE "${input.tableName}" ALTER COLUMN "${input.column}" SET DEFAULT ${input.defaultExpr}`,
1464 );
1465 }
1466 });
1467}
1468
1469/**
1470 * Drop a column. Refuses to drop a PK column — that would invalidate every
1471 * row-level operation in studio and is almost always a mistake.
1472 */
1473export async function dropColumn(input: {
1474 projectId: string;
1475 tableName: string;
1476 column: string;
1477}): Promise<void> {
1478 await assertTableExists(input.projectId, input.tableName);
1479 if (!COLUMN_NAME_RE.test(input.column)) {
1480 throw new ValidationError('invalid column name', { column: input.column });
1481 }
1482 const cols = await getTableColumns(input.projectId, input.tableName);
1483 const target = cols.find((c) => c.name === input.column);
1484 if (!target) {
1485 throw new ValidationError('column not found on table', {
1486 table: input.tableName,
1487 column: input.column,
1488 });
1489 }
1490 if (target.isPrimaryKey) {
1491 throw new ValidationError('cannot drop a primary-key column', { column: input.column });
1492 }
1493 await runInProjectDatabase(input.projectId, async (tx) => {
1494 await tx.unsafe('SET dolt_transaction_commit = 1');
1495 await tx.unsafe(`ALTER TABLE "${input.tableName}" DROP COLUMN "${input.column}"`);
1496 });
1497}
1498
1499/**
1500 * Delete one row by primary key. Validates the table + pk-column
1501 * identifiers against the project's schema; parameterises the row-key.
1502 * Refuses to touch platform-owned `_briven_*` tables (caught by
1503 * `assertTableExists`).
1504 */
1505export async function deleteRow(input: DeleteRowInput): Promise<DeleteRowResult> {
1506 await assertTableExists(input.projectId, input.tableName);
1507 const cols = await getTableColumns(input.projectId, input.tableName);
1508 assertPrimaryKeyMatches(cols, input.primaryKey);
1509
1510 const whereSql = input.primaryKey
1511 .map((p, i) => `"${p.column}" = $${i + 1}`)
1512 .join(' AND ');
1513 const params: ReadonlyArray<never> = input.primaryKey.map((p) => p.value as never);
1514 // The pg ProjectTx adapter returns the rows array (not a result object with
1515 // `.count`), so `RETURNING *` lets us recover the affected-row count. NOT a
1516 // bare `RETURNING 1` — DoltGres rejects an integer literal in RETURNING and
1517 // throws (same snap-back bug that broke single-cell UPDATEs; see updateRow).
1518 const rows = await runInProjectDatabase(input.projectId, async (tx) => {
1519 await tx.unsafe('SET dolt_transaction_commit = 1');
1520 return tx.unsafe(
1521 `DELETE FROM "${input.tableName}" WHERE ${whereSql} RETURNING *`,
1522 params as unknown as never[],
1523 );
1524 });
1525 return { affected: rows.length };
1526}