konnosDashboard
studio.ts1181 lines · main
1import { Hono } from 'hono';
2
3import { projectRateLimit } from '../middleware/rate-limit.js';
4import { requireProjectAuth, requireProjectRole } from '../middleware/project-auth.js';
5import { requireServiceProduct } from '../middleware/service-product.js';
6import { audit, hashIp } from '../services/audit.js';
7import { exportProjectSchema } from '../services/schema-export.js';
8import {
9 addColumn,
10 alterColumn,
11 createIndex,
12 createTable,
13 deleteRow,
14 dropColumn,
15 dropIndex,
16 dropTable,
17 executeQuery,
18 exportSchemaAsDsl,
19 getFullSchema,
20 getTableColumns,
21 getTableRows,
22 insertRow,
23 insertRows,
24 listIndexes,
25 listProjectTables,
26 listRelationships,
27 renameColumn,
28 renameTable,
29 truncateTable,
30 STUDIO_COLUMN_TYPES,
31 FILTER_OPS,
32 updateCell,
33 updateRow,
34 type FilterOp,
35 type PrimaryKeyValue,
36 type StudioColumnReference,
37 type StudioColumnSpec,
38 type StudioColumnType,
39} from '../services/studio.js';
40import { seedTemplate } from '../services/templates.js';
41import {
42 createSnapshot,
43 deleteSnapshot,
44 diffSnapshot,
45 listSnapshots,
46 restoreSnapshot,
47 SNAP_ID_RE,
48} from '../services/snapshots.js';
49import {
50 getAutoSnapshotSettings,
51 upsertAutoSnapshotSettings,
52} from '../services/auto-snapshots.js';
53import { autoSnapshotFrequency, type AutoSnapshotFrequency } from '../db/schema.js';
54import { applyPlan, planDatabase } from '../services/assistant.js';
55import { assistantConfigured } from '../services/ollama.js';
56import { assertWithinStorageLimit } from '../services/storage-admin.js';
57import { ValidationError } from '@briven/shared';
58
59/**
60 * Shape-validate the `primaryKey` array a client sent. Returns the typed
61 * array on success, null on any malformed input — the route then returns
62 * a 400 with a clear example. The service layer additionally checks that
63 * the column SET matches the table's actual PK; that's not done here so
64 * the route doesn't need a db roundtrip to reject obvious garbage.
65 */
66function parsePrimaryKey(raw: unknown): ReadonlyArray<PrimaryKeyValue> | null {
67 if (!Array.isArray(raw) || raw.length === 0) return null;
68 const out: PrimaryKeyValue[] = [];
69 for (const entry of raw) {
70 if (!entry || typeof entry !== 'object') return null;
71 const e = entry as { column?: unknown; value?: unknown };
72 if (typeof e.column !== 'string') return null;
73 if (typeof e.value !== 'string' && typeof e.value !== 'number') return null;
74 out.push({ column: e.column, value: e.value });
75 }
76 return out;
77}
78
79const FK_ON_DELETE = ['cascade', 'restrict', 'setNull', 'noAction'] as const;
80import type { ProjectAppEnv as AppEnv } from '../types/app-env.js';
81
82/**
83 * Studio routes — read-mode only. Admin-tier (developer is not enough):
84 * the data view surfaces full row contents which could include customer
85 * secrets or PII.
86 */
87export const studioRouter = new Hono<AppEnv>();
88
89// Studio is the Doltgres wall — service badges must be product=db.
90studioRouter.use(
91 '/v1/projects/:id/studio/*',
92 requireProjectAuth(),
93 requireServiceProduct('db'),
94);
95
96studioRouter.get(
97 '/v1/projects/:id/studio/tables',
98 projectRateLimit('read'),
99 requireProjectRole('admin'),
100 async (c) => {
101 const tables = await listProjectTables(c.req.param('id'));
102 return c.json({ tables });
103 },
104);
105
106/**
107 * AI assistant — "describe it and Briven builds it". Powered by flndrn's
108 * self-hosted Ollama. Two steps so the user is never surprised:
109 * POST .../assistant/plan → JSON build plan (writes nothing)
110 * POST .../assistant/apply → runs the reviewed plan through createTable/insertRow
111 * Admin-tier only (it creates tables + data).
112 */
113studioRouter.post(
114 '/v1/projects/:id/studio/assistant/plan',
115 projectRateLimit('mutate'),
116 requireProjectRole('admin'),
117 async (c) => {
118 if (!assistantConfigured()) {
119 return c.json({ code: 'assistant_unconfigured', message: 'the assistant is resting — try again soon' }, 503);
120 }
121 const body = (await c.req.json().catch(() => null)) as { prompt?: string } | null;
122 if (!body || typeof body.prompt !== 'string' || body.prompt.trim() === '') {
123 return c.json({ code: 'validation_failed', message: 'expected { prompt: string }' }, 400);
124 }
125 const plan = await planDatabase(c.req.param('id'), body.prompt.slice(0, 2000));
126 return c.json({ plan });
127 },
128);
129
130studioRouter.post(
131 '/v1/projects/:id/studio/assistant/apply',
132 projectRateLimit('mutate'),
133 requireProjectRole('admin'),
134 async (c) => {
135 const projectId = c.req.param('id');
136 const body = (await c.req.json().catch(() => null)) as { plan?: unknown } | null;
137 if (!body || !body.plan) {
138 return c.json({ code: 'validation_failed', message: 'expected { plan }' }, 400);
139 }
140 const result = await applyPlan(projectId, body.plan);
141 const user = c.get('user');
142 await audit({
143 actorId: user?.id ?? null,
144 projectId,
145 action: 'studio.assistant.apply',
146 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
147 userAgent: c.req.header('user-agent') ?? null,
148 metadata: { created: result.created.map((x) => x.table), skipped: result.skipped },
149 });
150 return c.json(result);
151 },
152);
153
154/**
155 * Run arbitrary SQL against the project's schema, scoped via SET LOCAL ROLE
156 * to the project-owner role so cross-schema access is impossible. Audit-
157 * logged so an admin can later replay what was run. Sql text is truncated
158 * to 1KB in the audit to keep the table size bounded.
159 */
160studioRouter.post(
161 '/v1/projects/:id/studio/query',
162 projectRateLimit('mutate'),
163 requireProjectRole('admin'),
164 async (c) => {
165 const projectId = c.req.param('id');
166 const body = (await c.req.json().catch(() => null)) as { sql?: string } | null;
167 if (!body || typeof body.sql !== 'string' || body.sql.trim() === '') {
168 return c.json({ code: 'validation_failed', message: 'expected { sql: string }' }, 400);
169 }
170 try {
171 const result = await executeQuery(projectId, body.sql);
172 const user = c.get('user');
173 await audit({
174 actorId: user?.id ?? null,
175 projectId,
176 action: 'studio.query.run',
177 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
178 userAgent: c.req.header('user-agent') ?? null,
179 metadata: {
180 sqlPreview: body.sql.slice(0, 1024),
181 command: result.command,
182 rowCount: result.rowCount,
183 elapsedMs: result.elapsedMs,
184 },
185 });
186 return c.json(result);
187 } catch (err) {
188 const message = err instanceof Error ? err.message : 'query failed';
189 return c.json({ code: 'query_failed', message }, 400);
190 }
191 },
192);
193
194/**
195 * Full one-shot schema: every table with its columns + every FK edge.
196 * Drives the studio schema overview page.
197 */
198studioRouter.get(
199 '/v1/projects/:id/studio/schema',
200 projectRateLimit('read'),
201 requireProjectRole('admin'),
202 async (c) => {
203 const projectId = c.req.param('id');
204 const schema = await getFullSchema(projectId);
205 return c.json(schema);
206 },
207);
208
209/**
210 * Every FK edge in the schema. Drives the relationships panel on the
211 * studio overview.
212 */
213studioRouter.get(
214 '/v1/projects/:id/studio/relationships',
215 projectRateLimit('read'),
216 requireProjectRole('admin'),
217 async (c) => {
218 const projectId = c.req.param('id');
219 const edges = await listRelationships(projectId);
220 return c.json({ edges });
221 },
222);
223
224/**
225 * JSON-wrapped schema export — returns `{ schemaTs }`. Used by the CLI
226 * (`briven pull`) to materialise a local `briven/schema.ts` from the
227 * server's current schema. Distinct from the text/plain `schema.ts`
228 * endpoint below, which the browser studio uses for direct download.
229 */
230studioRouter.get(
231 '/v1/projects/:id/studio/schema-export',
232 projectRateLimit('read'),
233 requireProjectRole('admin'),
234 async (c) => {
235 const projectId = c.req.param('id');
236 const schemaTs = await exportProjectSchema(projectId);
237 return c.json({ schemaTs });
238 },
239);
240
241/**
242 * Generate an equivalent `briven/schema.ts` for the project — for users
243 * who started in studio and want to graduate to git-tracked CLI deploys.
244 */
245studioRouter.get(
246 '/v1/projects/:id/studio/schema.ts',
247 projectRateLimit('read'),
248 requireProjectRole('admin'),
249 async (c) => {
250 const projectId = c.req.param('id');
251 const body = await exportSchemaAsDsl(projectId);
252 return new Response(body, {
253 status: 200,
254 headers: { 'content-type': 'text/plain; charset=utf-8' },
255 });
256 },
257);
258
259studioRouter.get(
260 '/v1/projects/:id/studio/tables/:table/columns',
261 projectRateLimit('read'),
262 requireProjectRole('admin'),
263 async (c) => {
264 const projectId = c.req.param('id');
265 const tableName = c.req.param('table');
266 if (!projectId || !tableName) {
267 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
268 }
269 const columns = await getTableColumns(projectId, tableName);
270 return c.json({ columns });
271 },
272);
273
274studioRouter.get(
275 '/v1/projects/:id/studio/tables/:table/rows',
276 projectRateLimit('read'),
277 requireProjectRole('admin'),
278 async (c) => {
279 const projectId = c.req.param('id');
280 const tableName = c.req.param('table');
281 if (!projectId || !tableName) {
282 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
283 }
284 const limit = Number(c.req.query('limit') ?? '50');
285 const offset = Number(c.req.query('offset') ?? '0');
286 // orderBy: `?orderBy=column&dir=asc|desc`. Both optional; dir defaults
287 // to asc. Validated against the actual column set inside the service.
288 const orderByCol = c.req.query('orderBy');
289 const orderByDir = c.req.query('dir') === 'desc' ? 'desc' : 'asc';
290 // Query params:
291 // ?limit / ?offset / ?cursor → pagination (reserved; never a filter)
292 // ?orderBy / ?dir → sort (reserved; never a filter)
293 // ?<col>__<op>=value → operator filter (op ∈ FILTER_OPS: eq,
294 // contains, gt, lt, gte, lte)
295 // ?<col>=value → shorthand equality filter (same as
296 // ?<col>__eq=value); any real column.
297 // Multiple filter params are AND-ed together. Every filter column is
298 // validated against the table's real columns inside the service (via
299 // getTableColumns + COLUMN_NAME_RE); unknown columns raise a 400. Values
300 // are parameter-bound in buildFilterClauses — never interpolated.
301 const RESERVED_PARAMS = new Set(['limit', 'offset', 'cursor', 'orderBy', 'dir']);
302 const filters: Array<{ column: string; op: FilterOp; value: string }> = [];
303 for (const [k, v] of Object.entries(c.req.queries())) {
304 if (RESERVED_PARAMS.has(k)) continue;
305 if (!Array.isArray(v) || v[0] === undefined) continue;
306 const sepAt = k.lastIndexOf('__');
307 if (sepAt > 0) {
308 // Operator form: `?col__op=value`. Unknown ops are dropped here; the
309 // service validates the column and raises 400 on unknown columns.
310 const col = k.slice(0, sepAt);
311 const op = k.slice(sepAt + 2);
312 if (!(FILTER_OPS as readonly string[]).includes(op)) continue;
313 filters.push({ column: col, op: op as FilterOp, value: v[0] });
314 } else {
315 // Shorthand equality form: `?col=value` → `col = value`.
316 filters.push({ column: k, op: 'eq', value: v[0] });
317 }
318 }
319 const result = await getTableRows(projectId, tableName, {
320 limit: Number.isFinite(limit) ? limit : undefined,
321 offset: Number.isFinite(offset) ? offset : undefined,
322 orderBy: orderByCol ? { column: orderByCol, direction: orderByDir } : null,
323 filters: filters.length > 0 ? filters : undefined,
324 });
325 return c.json(result);
326 },
327);
328
329/**
330 * Inline cell update — write mode for studio. Admin-tier; tier-aware
331 * mutate rate limit; every successful write lands an audit-log row
332 * recording (table, column, primary-key column, affected count) but
333 * never the value itself, per CLAUDE.md §5.1.
334 */
335studioRouter.patch(
336 '/v1/projects/:id/studio/tables/:table/rows',
337 projectRateLimit('mutate'),
338 requireProjectRole('admin'),
339 async (c) => {
340 const projectId = c.req.param('id');
341 const tableName = c.req.param('table');
342 if (!projectId || !tableName) {
343 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
344 }
345 // Two body shapes (branch on which is present):
346 // Single column (backward compatible):
347 // { primaryKey: [{column, value}, ...], column, value }
348 // Multi column (one UPDATE SETs all columns):
349 // { primaryKey: [{column, value}, ...], values: { col1: v1, col2: v2, ... } }
350 const body = (await c.req.json().catch(() => null)) as {
351 primaryKey?: Array<{ column?: unknown; value?: unknown }>;
352 column?: string;
353 value?: unknown;
354 values?: Record<string, unknown>;
355 } | null;
356 const primaryKey = parsePrimaryKey(body?.primaryKey);
357 if (!body || !primaryKey) {
358 return c.json(
359 {
360 code: 'validation_failed',
361 message:
362 'expected { primaryKey: [{column, value}, ...], column, value } (single column) or { primaryKey: [{column, value}, ...], values: { col: value, ... } } (multi column) — primaryKey must be a non-empty array of {column: string, value: string | number}',
363 },
364 400,
365 );
366 }
367
368 // Multi-column path: `values` is a plain column→value object → one UPDATE
369 // that SETs every column. (Arrays are rejected so `values` is a map, not a
370 // row list.)
371 if (body.values && typeof body.values === 'object' && !Array.isArray(body.values)) {
372 const columns = Object.keys(body.values);
373 if (columns.length === 0) {
374 return c.json(
375 { code: 'validation_failed', message: 'values must contain at least one column' },
376 400,
377 );
378 }
379 const result = await updateRow({
380 projectId,
381 tableName,
382 primaryKey,
383 values: body.values,
384 });
385 const user = c.get('user');
386 await audit({
387 actorId: user?.id ?? null,
388 projectId,
389 action: 'studio.cell.update',
390 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
391 userAgent: c.req.header('user-agent') ?? null,
392 metadata: {
393 table: tableName,
394 // Per CLAUDE.md §5.1 — record the column names touched, never values.
395 columns,
396 primaryKeyColumns: primaryKey.map((p) => p.column),
397 affected: result.affected,
398 },
399 });
400 return c.json(result);
401 }
402
403 // Single-column path (unchanged): `{ column, value }`.
404 if (typeof body.column !== 'string') {
405 return c.json(
406 {
407 code: 'validation_failed',
408 message:
409 'expected { primaryKey: [{column, value}, ...], column, value } (single column) or { primaryKey: [{column, value}, ...], values: { col: value, ... } } (multi column) — primaryKey must be a non-empty array of {column: string, value: string | number}',
410 },
411 400,
412 );
413 }
414 const result = await updateCell({
415 projectId,
416 tableName,
417 primaryKey,
418 column: body.column,
419 value: body.value,
420 });
421 const user = c.get('user');
422 await audit({
423 actorId: user?.id ?? null,
424 projectId,
425 action: 'studio.cell.update',
426 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
427 userAgent: c.req.header('user-agent') ?? null,
428 metadata: {
429 table: tableName,
430 column: body.column,
431 primaryKeyColumns: primaryKey.map((p) => p.column),
432 affected: result.affected,
433 },
434 });
435 return c.json(result);
436 },
437);
438
439/**
440 * Insert one OR many rows. Returns the inserted row(s) including any DB-side
441 * defaults (server-generated ulids, timestamps, etc.).
442 *
443 * - Single row (backward compatible): `{ values: { col: value, ... } }` →
444 * returns the one inserted row (201).
445 * - Bulk (one request, one rate-limit count): `{ values: [ {..}, {..}, ... ] }`
446 * → returns `{ inserted, rows }` (201). Sidesteps the per-minute mutate cap
447 * so a whole table can be migrated in a handful of requests.
448 */
449studioRouter.post(
450 '/v1/projects/:id/studio/tables/:table/rows',
451 projectRateLimit('mutate'),
452 requireProjectRole('admin'),
453 async (c) => {
454 const projectId = c.req.param('id');
455 const tableName = c.req.param('table');
456 if (!projectId || !tableName) {
457 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
458 }
459 const body = (await c.req.json().catch(() => null)) as {
460 values?: Record<string, unknown> | Array<Record<string, unknown>>;
461 } | null;
462 const user = c.get('user');
463
464 // Bulk path: `values` is an array of row objects → one multi-row INSERT.
465 if (body && Array.isArray(body.values)) {
466 // Storage enforcement: block-mode projects that are (or would go) over
467 // their row cap are refused; flag-mode is a no-op. Batch size is passed
468 // so the whole insert is weighed at once. Fails open on any lookup miss.
469 try {
470 await assertWithinStorageLimit(projectId, 'row', body.values.length);
471 } catch (err) {
472 if (err instanceof ValidationError) {
473 return c.json({ code: 'storage_limit_reached', message: err.message }, 413);
474 }
475 throw err;
476 }
477 const { inserted, rows } = await insertRows({
478 projectId,
479 tableName,
480 rows: body.values,
481 });
482 await audit({
483 actorId: user?.id ?? null,
484 projectId,
485 action: 'studio.row.insert',
486 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
487 userAgent: c.req.header('user-agent') ?? null,
488 metadata: {
489 table: tableName,
490 rowsInserted: inserted,
491 bulk: true,
492 },
493 });
494 return c.json({ inserted, rows }, 201);
495 }
496
497 // Single-row path (unchanged): `values` is a plain column→value object.
498 // (Arrays already took the bulk branch above; this guard also narrows the
499 // union so `body.values` is a plain object below.)
500 if (!body || !body.values || typeof body.values !== 'object' || Array.isArray(body.values)) {
501 return c.json(
502 {
503 code: 'validation_failed',
504 message:
505 'expected { values: { col: value, ... } } or { values: [ { col: value, ... }, ... ] }',
506 },
507 400,
508 );
509 }
510 // Storage enforcement (block-mode only; flag-mode no-op, fails open).
511 try {
512 await assertWithinStorageLimit(projectId, 'row');
513 } catch (err) {
514 if (err instanceof ValidationError) {
515 return c.json({ code: 'storage_limit_reached', message: err.message }, 413);
516 }
517 throw err;
518 }
519 const result = await insertRow({ projectId, tableName, values: body.values });
520 await audit({
521 actorId: user?.id ?? null,
522 projectId,
523 action: 'studio.row.insert',
524 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
525 userAgent: c.req.header('user-agent') ?? null,
526 metadata: {
527 table: tableName,
528 // Per CLAUDE.md §5.1 — record the column names that were
529 // populated, not the values themselves.
530 columns: Object.keys(body.values),
531 },
532 });
533 return c.json(result, 201);
534 },
535);
536
537/**
538 * Delete a row by primary key. Body: `{ primaryKeyColumn, primaryKeyValue }`.
539 */
540/**
541 * Create a new table. Body: `{ tableName, columns: [{ name, type, notNull?,
542 * primaryKey?, defaultExpr? }] }`. Service-side validates the type
543 * whitelist + identifier shape + at-least-one-pk rule.
544 */
545function parseColumnSpec(input: unknown): StudioColumnSpec | null {
546 if (!input || typeof input !== 'object') return null;
547 const c = input as Record<string, unknown>;
548 if (typeof c.name !== 'string') return null;
549 if (typeof c.type !== 'string') return null;
550 if (!(STUDIO_COLUMN_TYPES as readonly string[]).includes(c.type)) return null;
551
552 let references: StudioColumnReference | null | undefined;
553 if (c.references === null) {
554 references = null;
555 } else if (c.references && typeof c.references === 'object') {
556 const r = c.references as Record<string, unknown>;
557 if (typeof r.table !== 'string' || typeof r.column !== 'string') return null;
558 const onDelete =
559 typeof r.onDelete === 'string' && (FK_ON_DELETE as readonly string[]).includes(r.onDelete)
560 ? (r.onDelete as StudioColumnReference['onDelete'])
561 : 'noAction';
562 references = { table: r.table, column: r.column, onDelete };
563 }
564
565 return {
566 name: c.name,
567 type: c.type as StudioColumnType,
568 notNull: typeof c.notNull === 'boolean' ? c.notNull : undefined,
569 primaryKey: typeof c.primaryKey === 'boolean' ? c.primaryKey : undefined,
570 defaultExpr:
571 typeof c.defaultExpr === 'string' || c.defaultExpr === null
572 ? (c.defaultExpr as string | null)
573 : undefined,
574 references,
575 };
576}
577
578studioRouter.post(
579 '/v1/projects/:id/studio/tables',
580 projectRateLimit('mutate'),
581 requireProjectRole('admin'),
582 async (c) => {
583 const projectId = c.req.param('id');
584 const body = (await c.req.json().catch(() => null)) as {
585 tableName?: string;
586 columns?: unknown[];
587 } | null;
588 if (!body || typeof body.tableName !== 'string' || !Array.isArray(body.columns)) {
589 return c.json(
590 { code: 'validation_failed', message: 'expected { tableName, columns: [...] }' },
591 400,
592 );
593 }
594 const cols: StudioColumnSpec[] = [];
595 for (const raw of body.columns) {
596 const spec = parseColumnSpec(raw);
597 if (!spec) {
598 return c.json(
599 { code: 'validation_failed', message: 'each column needs { name, type } at minimum' },
600 400,
601 );
602 }
603 cols.push(spec);
604 }
605 // Storage enforcement (block-mode only; flag-mode no-op, fails open).
606 try {
607 await assertWithinStorageLimit(projectId, 'table');
608 } catch (err) {
609 if (err instanceof ValidationError) {
610 return c.json({ code: 'storage_limit_reached', message: err.message }, 413);
611 }
612 throw err;
613 }
614 const result = await createTable({ projectId, tableName: body.tableName, columns: cols });
615 const user = c.get('user');
616 await audit({
617 actorId: user?.id ?? null,
618 projectId,
619 action: 'studio.table.create',
620 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
621 userAgent: c.req.header('user-agent') ?? null,
622 metadata: { table: result.name, columnCount: cols.length },
623 });
624 return c.json(result, 201);
625 },
626);
627
628/**
629 * Apply a starter template to a (freshly created, empty) project: creates the
630 * template's tables in FK order and seeds sample rows, so a non-coder lands on
631 * a working database instead of a blank screen. Body: `{ templateId }`.
632 */
633studioRouter.post(
634 '/v1/projects/:id/studio/apply-template',
635 projectRateLimit('mutate'),
636 requireProjectRole('admin'),
637 async (c) => {
638 const projectId = c.req.param('id');
639 const body = (await c.req.json().catch(() => null)) as { templateId?: string } | null;
640 if (!body || typeof body.templateId !== 'string') {
641 return c.json({ code: 'validation_failed', message: 'expected { templateId }' }, 400);
642 }
643 const result = await seedTemplate(projectId, body.templateId);
644 const user = c.get('user');
645 await audit({
646 actorId: user?.id ?? null,
647 projectId,
648 action: 'studio.template.apply',
649 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
650 userAgent: c.req.header('user-agent') ?? null,
651 metadata: {
652 templateId: result.templateId,
653 tablesCreated: result.tablesCreated,
654 rowsInserted: result.rowsInserted,
655 },
656 });
657 return c.json(result, 201);
658 },
659);
660
661/**
662 * Snapshots — the non-coder "undo button" (lite git-for-data on Postgres).
663 * Save / list / restore / delete point-in-time copies of a project's data.
664 */
665studioRouter.get(
666 '/v1/projects/:id/studio/snapshots',
667 projectRateLimit('read'),
668 requireProjectRole('admin'),
669 async (c) => {
670 const snapshots = await listSnapshots(c.req.param('id'));
671 return c.json({ snapshots });
672 },
673);
674
675studioRouter.post(
676 '/v1/projects/:id/studio/snapshots',
677 projectRateLimit('mutate'),
678 requireProjectRole('admin'),
679 async (c) => {
680 const projectId = c.req.param('id');
681 const body = (await c.req.json().catch(() => null)) as { name?: string } | null;
682 const result = await createSnapshot(projectId, body?.name ?? 'snapshot');
683 const user = c.get('user');
684 await audit({
685 actorId: user?.id ?? null,
686 projectId,
687 action: 'studio.snapshot.create',
688 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
689 userAgent: c.req.header('user-agent') ?? null,
690 metadata: { snapshotId: result.id, tableCount: result.tableCount },
691 });
692 return c.json(result, 201);
693 },
694);
695
696/**
697 * Snapshot diff — "what changed since this save point". Read-only: compares
698 * the live schema against the snapshot's copy and reports tables/columns
699 * added or removed plus per-table row deltas (added/removed/changed, matched
700 * by primary key, capped per table). Drives the dashboard "compare" view.
701 */
702studioRouter.get(
703 '/v1/projects/:id/studio/snapshots/:snapId/diff',
704 projectRateLimit('read'),
705 requireProjectRole('admin'),
706 async (c) => {
707 const projectId = c.req.param('id');
708 const snapId = c.req.param('snapId');
709 if (!SNAP_ID_RE.test(snapId)) {
710 return c.json({ code: 'validation_failed', message: 'invalid snapshot id' }, 400);
711 }
712 const diff = await diffSnapshot(projectId, snapId);
713 const user = c.get('user');
714 await audit({
715 actorId: user?.id ?? null,
716 projectId,
717 action: 'studio.snapshot.diff',
718 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
719 userAgent: c.req.header('user-agent') ?? null,
720 metadata: {
721 snapshotId: snapId,
722 tablesAdded: diff.tablesAdded.length,
723 tablesRemoved: diff.tablesRemoved.length,
724 tablesCompared: diff.tables.length,
725 },
726 });
727 return c.json(diff);
728 },
729);
730
731studioRouter.post(
732 '/v1/projects/:id/studio/snapshots/:snapId/restore',
733 projectRateLimit('mutate'),
734 requireProjectRole('admin'),
735 async (c) => {
736 const projectId = c.req.param('id');
737 const snapId = c.req.param('snapId');
738 const result = await restoreSnapshot(projectId, snapId);
739 const user = c.get('user');
740 await audit({
741 actorId: user?.id ?? null,
742 projectId,
743 action: 'studio.snapshot.restore',
744 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
745 userAgent: c.req.header('user-agent') ?? null,
746 metadata: { snapshotId: snapId, restored: result.restored },
747 });
748 return c.json(result);
749 },
750);
751
752studioRouter.delete(
753 '/v1/projects/:id/studio/snapshots/:snapId',
754 projectRateLimit('mutate'),
755 requireProjectRole('admin'),
756 async (c) => {
757 const projectId = c.req.param('id');
758 const snapId = c.req.param('snapId');
759 await deleteSnapshot(projectId, snapId);
760 const user = c.get('user');
761 await audit({
762 actorId: user?.id ?? null,
763 projectId,
764 action: 'studio.snapshot.delete',
765 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
766 userAgent: c.req.header('user-agent') ?? null,
767 metadata: { snapshotId: snapId },
768 });
769 return c.json({ ok: true });
770 },
771);
772
773/**
774 * Automatic snapshots — read + update the per-project schedule that takes
775 * save-points for the customer on a cadence (daily / twice-daily) and keeps
776 * the last N. Admin-tier like the rest of studio. The actual runs happen in
777 * the auto-snapshot worker; these routes only configure it.
778 */
779studioRouter.get(
780 '/v1/projects/:id/studio/auto-snapshots',
781 projectRateLimit('read'),
782 requireProjectRole('admin'),
783 async (c) => {
784 const settings = await getAutoSnapshotSettings(c.req.param('id'));
785 return c.json(settings);
786 },
787);
788
789studioRouter.put(
790 '/v1/projects/:id/studio/auto-snapshots',
791 projectRateLimit('mutate'),
792 requireProjectRole('admin'),
793 async (c) => {
794 const projectId = c.req.param('id');
795 const body = (await c.req.json().catch(() => null)) as {
796 enabled?: unknown;
797 frequency?: unknown;
798 retentionCount?: unknown;
799 } | null;
800 if (!body) {
801 return c.json({ code: 'validation_failed', message: 'request body required' }, 400);
802 }
803 const frequency = body.frequency;
804 if (typeof frequency !== 'string' || !autoSnapshotFrequency.includes(frequency as AutoSnapshotFrequency)) {
805 return c.json(
806 { code: 'validation_failed', message: `frequency must be one of: ${autoSnapshotFrequency.join(', ')}` },
807 400,
808 );
809 }
810 const retentionCount = Number(body.retentionCount);
811 const user = c.get('user');
812 const settings = await upsertAutoSnapshotSettings(projectId, {
813 enabled: body.enabled === true,
814 frequency: frequency as AutoSnapshotFrequency,
815 retentionCount,
816 updatedBy: user?.id ?? null,
817 });
818 await audit({
819 actorId: user?.id ?? null,
820 projectId,
821 action: 'studio.snapshot.auto.configure',
822 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
823 userAgent: c.req.header('user-agent') ?? null,
824 metadata: {
825 enabled: settings.enabled,
826 frequency: settings.frequency,
827 retentionCount: settings.retentionCount,
828 },
829 });
830 return c.json(settings);
831 },
832);
833
834studioRouter.patch(
835 '/v1/projects/:id/studio/tables/:table',
836 projectRateLimit('mutate'),
837 requireProjectRole('admin'),
838 async (c) => {
839 const projectId = c.req.param('id');
840 const tableName = c.req.param('table');
841 if (!projectId || !tableName) {
842 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
843 }
844 const body = (await c.req.json().catch(() => null)) as { newName?: string } | null;
845 if (!body || typeof body.newName !== 'string') {
846 return c.json({ code: 'validation_failed', message: 'expected { newName: string }' }, 400);
847 }
848 await renameTable({ projectId, oldName: tableName, newName: body.newName });
849 const user = c.get('user');
850 await audit({
851 actorId: user?.id ?? null,
852 projectId,
853 action: 'studio.table.rename',
854 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
855 userAgent: c.req.header('user-agent') ?? null,
856 metadata: { oldName: tableName, newName: body.newName },
857 });
858 return c.json({ renamed: body.newName });
859 },
860);
861
862studioRouter.patch(
863 '/v1/projects/:id/studio/tables/:table/columns/:column',
864 projectRateLimit('mutate'),
865 requireProjectRole('admin'),
866 async (c) => {
867 const projectId = c.req.param('id');
868 const tableName = c.req.param('table');
869 const column = c.req.param('column');
870 if (!projectId || !tableName || !column) {
871 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
872 }
873 const body = (await c.req.json().catch(() => null)) as {
874 newName?: string;
875 notNull?: boolean;
876 defaultExpr?: string | null;
877 } | null;
878 if (!body) {
879 return c.json({ code: 'validation_failed', message: 'body required' }, 400);
880 }
881 // Two-mode patch: rename (newName) OR alter (notNull, defaultExpr).
882 // Mutually exclusive so audit metadata stays clean.
883 if (typeof body.newName === 'string') {
884 await renameColumn({
885 projectId,
886 tableName,
887 oldName: column,
888 newName: body.newName,
889 });
890 const user = c.get('user');
891 await audit({
892 actorId: user?.id ?? null,
893 projectId,
894 action: 'studio.column.rename',
895 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
896 userAgent: c.req.header('user-agent') ?? null,
897 metadata: { table: tableName, oldName: column, newName: body.newName },
898 });
899 return c.json({ renamed: body.newName });
900 }
901 if (typeof body.notNull === 'boolean' || body.defaultExpr !== undefined) {
902 await alterColumn({
903 projectId,
904 tableName,
905 column,
906 notNull: typeof body.notNull === 'boolean' ? body.notNull : undefined,
907 defaultExpr:
908 body.defaultExpr === null
909 ? null
910 : typeof body.defaultExpr === 'string'
911 ? body.defaultExpr
912 : undefined,
913 });
914 const user = c.get('user');
915 await audit({
916 actorId: user?.id ?? null,
917 projectId,
918 action: 'studio.column.alter',
919 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
920 userAgent: c.req.header('user-agent') ?? null,
921 metadata: {
922 table: tableName,
923 column,
924 notNull: typeof body.notNull === 'boolean' ? body.notNull : null,
925 defaultExpr:
926 body.defaultExpr === null
927 ? '(dropped)'
928 : typeof body.defaultExpr === 'string'
929 ? body.defaultExpr
930 : null,
931 },
932 });
933 return c.json({ altered: column });
934 }
935 return c.json(
936 {
937 code: 'validation_failed',
938 message: 'expected { newName } or { notNull?, defaultExpr? }',
939 },
940 400,
941 );
942 },
943);
944
945studioRouter.post(
946 '/v1/projects/:id/studio/tables/:table/truncate',
947 projectRateLimit('mutate'),
948 requireProjectRole('admin'),
949 async (c) => {
950 const projectId = c.req.param('id');
951 const tableName = c.req.param('table');
952 if (!projectId || !tableName) {
953 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
954 }
955 const body = (await c.req.json().catch(() => null)) as { cascade?: boolean } | null;
956 await truncateTable(projectId, tableName, Boolean(body?.cascade));
957 const user = c.get('user');
958 await audit({
959 actorId: user?.id ?? null,
960 projectId,
961 action: 'studio.table.truncate',
962 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
963 userAgent: c.req.header('user-agent') ?? null,
964 metadata: { table: tableName, cascade: Boolean(body?.cascade) },
965 });
966 return c.json({ truncated: tableName });
967 },
968);
969
970studioRouter.delete(
971 '/v1/projects/:id/studio/tables/:table',
972 projectRateLimit('mutate'),
973 requireProjectRole('admin'),
974 async (c) => {
975 const projectId = c.req.param('id');
976 const tableName = c.req.param('table');
977 if (!projectId || !tableName) {
978 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
979 }
980 await dropTable(projectId, tableName);
981 const user = c.get('user');
982 await audit({
983 actorId: user?.id ?? null,
984 projectId,
985 action: 'studio.table.drop',
986 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
987 userAgent: c.req.header('user-agent') ?? null,
988 metadata: { table: tableName },
989 });
990 return c.json({ dropped: tableName });
991 },
992);
993
994studioRouter.post(
995 '/v1/projects/:id/studio/tables/:table/columns',
996 projectRateLimit('mutate'),
997 requireProjectRole('admin'),
998 async (c) => {
999 const projectId = c.req.param('id');
1000 const tableName = c.req.param('table');
1001 if (!projectId || !tableName) {
1002 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
1003 }
1004 const body = (await c.req.json().catch(() => null)) as { column?: unknown } | null;
1005 const spec = body ? parseColumnSpec(body.column) : null;
1006 if (!spec) {
1007 return c.json(
1008 { code: 'validation_failed', message: 'expected { column: { name, type, ... } }' },
1009 400,
1010 );
1011 }
1012 await addColumn({ projectId, tableName, column: spec });
1013 const user = c.get('user');
1014 await audit({
1015 actorId: user?.id ?? null,
1016 projectId,
1017 action: 'studio.column.add',
1018 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
1019 userAgent: c.req.header('user-agent') ?? null,
1020 metadata: { table: tableName, column: spec.name, type: spec.type },
1021 });
1022 return c.json({ added: spec.name }, 201);
1023 },
1024);
1025
1026studioRouter.delete(
1027 '/v1/projects/:id/studio/tables/:table/columns/:column',
1028 projectRateLimit('mutate'),
1029 requireProjectRole('admin'),
1030 async (c) => {
1031 const projectId = c.req.param('id');
1032 const tableName = c.req.param('table');
1033 const column = c.req.param('column');
1034 if (!projectId || !tableName || !column) {
1035 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
1036 }
1037 await dropColumn({ projectId, tableName, column });
1038 const user = c.get('user');
1039 await audit({
1040 actorId: user?.id ?? null,
1041 projectId,
1042 action: 'studio.column.drop',
1043 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
1044 userAgent: c.req.header('user-agent') ?? null,
1045 metadata: { table: tableName, column },
1046 });
1047 return c.json({ dropped: column });
1048 },
1049);
1050
1051/**
1052 * List, create, and drop indexes on a table.
1053 */
1054studioRouter.get(
1055 '/v1/projects/:id/studio/tables/:table/indexes',
1056 projectRateLimit('read'),
1057 requireProjectRole('admin'),
1058 async (c) => {
1059 const projectId = c.req.param('id');
1060 const tableName = c.req.param('table');
1061 if (!projectId || !tableName) {
1062 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
1063 }
1064 const indexes = await listIndexes(projectId, tableName);
1065 return c.json({ indexes });
1066 },
1067);
1068
1069studioRouter.post(
1070 '/v1/projects/:id/studio/tables/:table/indexes',
1071 projectRateLimit('mutate'),
1072 requireProjectRole('admin'),
1073 async (c) => {
1074 const projectId = c.req.param('id');
1075 const tableName = c.req.param('table');
1076 if (!projectId || !tableName) {
1077 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
1078 }
1079 const body = (await c.req.json().catch(() => null)) as {
1080 columns?: string[];
1081 unique?: boolean;
1082 name?: string | null;
1083 } | null;
1084 if (!body || !Array.isArray(body.columns) || body.columns.length === 0) {
1085 return c.json(
1086 { code: 'validation_failed', message: 'expected { columns: [...], unique?, name? }' },
1087 400,
1088 );
1089 }
1090 const cols = body.columns.filter((c) => typeof c === 'string');
1091 const result = await createIndex({
1092 projectId,
1093 tableName,
1094 columns: cols,
1095 unique: Boolean(body.unique),
1096 name: typeof body.name === 'string' ? body.name : null,
1097 });
1098 const user = c.get('user');
1099 await audit({
1100 actorId: user?.id ?? null,
1101 projectId,
1102 action: 'studio.index.create',
1103 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
1104 userAgent: c.req.header('user-agent') ?? null,
1105 metadata: {
1106 table: tableName,
1107 index: result.name,
1108 columns: cols,
1109 unique: Boolean(body.unique),
1110 },
1111 });
1112 return c.json(result, 201);
1113 },
1114);
1115
1116studioRouter.delete(
1117 '/v1/projects/:id/studio/tables/:table/indexes/:name',
1118 projectRateLimit('mutate'),
1119 requireProjectRole('admin'),
1120 async (c) => {
1121 const projectId = c.req.param('id');
1122 const tableName = c.req.param('table');
1123 const indexName = c.req.param('name');
1124 if (!projectId || !tableName || !indexName) {
1125 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
1126 }
1127 await dropIndex(projectId, tableName, indexName);
1128 const user = c.get('user');
1129 await audit({
1130 actorId: user?.id ?? null,
1131 projectId,
1132 action: 'studio.index.drop',
1133 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
1134 userAgent: c.req.header('user-agent') ?? null,
1135 metadata: { table: tableName, index: indexName },
1136 });
1137 return c.json({ dropped: indexName });
1138 },
1139);
1140
1141studioRouter.delete(
1142 '/v1/projects/:id/studio/tables/:table/rows',
1143 projectRateLimit('mutate'),
1144 requireProjectRole('admin'),
1145 async (c) => {
1146 const projectId = c.req.param('id');
1147 const tableName = c.req.param('table');
1148 if (!projectId || !tableName) {
1149 return c.json({ code: 'validation_failed', message: 'missing path params' }, 400);
1150 }
1151 const body = (await c.req.json().catch(() => null)) as {
1152 primaryKey?: Array<{ column?: unknown; value?: unknown }>;
1153 } | null;
1154 const primaryKey = parsePrimaryKey(body?.primaryKey);
1155 if (!primaryKey) {
1156 return c.json(
1157 {
1158 code: 'validation_failed',
1159 message:
1160 'expected { primaryKey: [{column, value}, ...] } — primaryKey must be a non-empty array of {column: string, value: string | number}',
1161 },
1162 400,
1163 );
1164 }
1165 const result = await deleteRow({ projectId, tableName, primaryKey });
1166 const user = c.get('user');
1167 await audit({
1168 actorId: user?.id ?? null,
1169 projectId,
1170 action: 'studio.row.delete',
1171 ipHash: hashIp(c.req.raw.headers.get('cf-connecting-ip') ?? null),
1172 userAgent: c.req.header('user-agent') ?? null,
1173 metadata: {
1174 table: tableName,
1175 primaryKeyColumns: primaryKey.map((p) => p.column),
1176 affected: result.affected,
1177 },
1178 });
1179 return c.json(result);
1180 },
1181);