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