konnosDashboard
assistant.ts237 lines · main
1import { brivenError, ValidationError } from '@briven/shared';
2
3import { ollamaChat } from './ollama.js';
4import {
5 createTable,
6 getFullSchema,
7 insertRow,
8 STUDIO_COLUMN_TYPES,
9 type StudioColumnSpec,
10 type StudioColumnType,
11} from './studio.js';
12
13/**
14 * Briven's in-product database assistant.
15 *
16 * Flow is deliberately two-step so a non-technical user is never surprised:
17 * 1. planDatabase() — ask Ollama for a JSON "build plan" (tables, columns,
18 * sample rows). NOTHING is written. The dashboard shows it for review.
19 * 2. applyPlan() — once the user clicks "build it", we run the plan
20 * through the SAME validated studio operations a human uses
21 * (createTable / insertRow). The model never touches the database;
22 * it only proposes, we execute through tested code.
23 *
24 * We always add the `id` (uuid pk) and `created_at` columns ourselves, so
25 * every table is valid no matter what the model returns.
26 */
27
28export interface PlannedColumn {
29 readonly name: string;
30 readonly type: StudioColumnType;
31 readonly notNull: boolean;
32}
33
34export interface PlannedTable {
35 readonly name: string;
36 readonly description: string;
37 readonly columns: readonly PlannedColumn[];
38 readonly sampleRows: ReadonlyArray<Record<string, unknown>>;
39}
40
41export interface AssistantPlan {
42 readonly summary: string;
43 readonly tables: readonly PlannedTable[];
44}
45
46export interface ApplyResult {
47 readonly summary: string;
48 readonly created: ReadonlyArray<{ table: string; columns: number; rows: number }>;
49 readonly skipped: readonly string[];
50}
51
52const NAME_RE = /^[A-Za-z_][A-Za-z0-9_]{0,62}$/;
53const MAX_TABLES = 8;
54const MAX_COLS = 30;
55const MAX_SAMPLE_ROWS = 10;
56
57/** Columns Briven adds itself — never accepted from the model. */
58const RESERVED = new Set(['id', 'created_at', 'createdat']);
59
60const SYSTEM_PROMPT = [
61 "You are Briven's database design assistant for NON-TECHNICAL people.",
62 'Turn the user\'s plain-English description into a simple, sensible database.',
63 '',
64 'Reply with STRICT JSON only — no prose, no markdown — in exactly this shape:',
65 '{',
66 ' "summary": "one friendly sentence describing what you designed",',
67 ' "tables": [',
68 ' {',
69 ' "name": "snake_case_table_name",',
70 ' "description": "short plain-English purpose",',
71 ' "columns": [',
72 ' { "name": "snake_case_column", "type": "text", "notNull": true }',
73 ' ],',
74 ' "sampleRows": [ { "snake_case_column": "an example value" } ]',
75 ' }',
76 ' ]',
77 '}',
78 '',
79 `Allowed column "type" values ONLY: ${STUDIO_COLUMN_TYPES.join(', ')}.`,
80 'Rules:',
81 '- Use snake_case for every table and column name (letters, numbers, underscore).',
82 '- Do NOT add an "id" or "created_at" column — Briven adds those automatically.',
83 '- Keep each table to 3-6 meaningful columns. At most 6 tables.',
84 '- Give every table 2-3 realistic sample rows (omit id/created_at).',
85 '- For links between tables, add a column like "customer_id" of type "uuid".',
86 '- Prefer "text" for names/notes, "integer" for counts, "numeric" for money,',
87 ' "boolean" for yes/no, "timestamptz" for dates/times.',
88].join('\n');
89
90function asType(v: unknown): StudioColumnType | null {
91 return typeof v === 'string' && (STUDIO_COLUMN_TYPES as readonly string[]).includes(v)
92 ? (v as StudioColumnType)
93 : null;
94}
95
96/**
97 * Validate + normalise an untrusted plan object (from the model OR echoed
98 * back by the client). Drops anything malformed rather than trusting it.
99 * Throws a friendly ValidationError if nothing usable survives.
100 */
101export function normalizePlan(raw: unknown): AssistantPlan {
102 const obj = (raw ?? {}) as { summary?: unknown; tables?: unknown };
103 const summary = typeof obj.summary === 'string' ? obj.summary.slice(0, 400) : '';
104 const rawTables = Array.isArray(obj.tables) ? obj.tables : [];
105 const tables: PlannedTable[] = [];
106
107 for (const t of rawTables.slice(0, MAX_TABLES)) {
108 if (!t || typeof t !== 'object') continue;
109 const tt = t as {
110 name?: unknown;
111 description?: unknown;
112 columns?: unknown;
113 sampleRows?: unknown;
114 };
115 const name = typeof tt.name === 'string' ? tt.name.trim().toLowerCase() : '';
116 if (!NAME_RE.test(name) || name.startsWith('_briven_')) continue;
117
118 const seen = new Set<string>();
119 const columns: PlannedColumn[] = [];
120 for (const c of Array.isArray(tt.columns) ? tt.columns.slice(0, MAX_COLS) : []) {
121 if (!c || typeof c !== 'object') continue;
122 const cc = c as { name?: unknown; type?: unknown; notNull?: unknown };
123 const cname = typeof cc.name === 'string' ? cc.name.trim().toLowerCase() : '';
124 const ctype = asType(cc.type);
125 if (!NAME_RE.test(cname) || !ctype) continue;
126 if (RESERVED.has(cname) || seen.has(cname)) continue;
127 seen.add(cname);
128 columns.push({ name: cname, type: ctype, notNull: cc.notNull === true });
129 }
130 if (columns.length === 0) continue;
131
132 const colNames = new Set(columns.map((c) => c.name));
133 const sampleRows: Record<string, unknown>[] = [];
134 for (const r of Array.isArray(tt.sampleRows) ? tt.sampleRows.slice(0, MAX_SAMPLE_ROWS) : []) {
135 if (!r || typeof r !== 'object') continue;
136 const row: Record<string, unknown> = {};
137 for (const [k, v] of Object.entries(r as Record<string, unknown>)) {
138 const key = k.trim().toLowerCase();
139 if (colNames.has(key) && v !== undefined) row[key] = v;
140 }
141 if (Object.keys(row).length > 0) sampleRows.push(row);
142 }
143
144 tables.push({
145 name,
146 description: typeof tt.description === 'string' ? tt.description.slice(0, 200) : '',
147 columns,
148 sampleRows,
149 });
150 }
151
152 if (tables.length === 0) {
153 throw new ValidationError('the assistant could not turn that into a database — try rephrasing', {});
154 }
155 return { summary, tables };
156}
157
158/**
159 * Ask the brain for a build plan. Read-only — writes nothing. The current
160 * schema is passed as context so the model extends rather than collides.
161 */
162export async function planDatabase(projectId: string, prompt: string): Promise<AssistantPlan> {
163 if (typeof prompt !== 'string' || prompt.trim() === '') {
164 throw new ValidationError('describe what you want to track', {});
165 }
166 const schema = await getFullSchema(projectId);
167 const existing = schema.tables.map((t) => t.name);
168 const context =
169 existing.length > 0
170 ? `The project already has these tables (do not recreate them): ${existing.join(', ')}.`
171 : 'The project is empty — this is the first design.';
172
173 const content = await ollamaChat(
174 [
175 { role: 'system', content: SYSTEM_PROMPT },
176 { role: 'user', content: `${context}\n\nWhat the user wants:\n${prompt.slice(0, 2000)}` },
177 ],
178 { json: true, temperature: 0.2 },
179 );
180
181 let parsed: unknown;
182 try {
183 parsed = JSON.parse(content);
184 } catch {
185 throw new brivenError('assistant_bad_json', 'the assistant gave an answer we could not read — try again', {
186 status: 502,
187 });
188 }
189 return normalizePlan(parsed);
190}
191
192/**
193 * Execute an (already reviewed) plan through the validated studio ops.
194 * Each table gets an auto `id` (uuid pk) + `created_at`. Tables that already
195 * exist are skipped, not overwritten. Sample-row failures are tolerated so
196 * one bad value can't abort the whole build.
197 */
198export async function applyPlan(projectId: string, rawPlan: unknown): Promise<ApplyResult> {
199 const plan = normalizePlan(rawPlan);
200 const schema = await getFullSchema(projectId);
201 const existing = new Set(schema.tables.map((t) => t.name));
202
203 const created: Array<{ table: string; columns: number; rows: number }> = [];
204 const skipped: string[] = [];
205
206 for (const table of plan.tables) {
207 if (existing.has(table.name)) {
208 skipped.push(table.name);
209 continue;
210 }
211 const columns: StudioColumnSpec[] = [
212 { name: 'id', type: 'uuid', primaryKey: true, defaultExpr: 'gen_random_uuid()' },
213 ...table.columns.map((c) => ({ name: c.name, type: c.type, notNull: c.notNull })),
214 { name: 'created_at', type: 'timestamptz', notNull: true, defaultExpr: 'now()' },
215 ];
216 await createTable({ projectId, tableName: table.name, columns });
217 existing.add(table.name);
218
219 let rows = 0;
220 for (const sample of table.sampleRows) {
221 const values: Record<string, unknown> = {};
222 for (const col of table.columns) {
223 if (sample[col.name] !== undefined) values[col.name] = sample[col.name];
224 }
225 if (Object.keys(values).length === 0) continue;
226 try {
227 await insertRow({ projectId, tableName: table.name, values });
228 rows += 1;
229 } catch {
230 // tolerate a single bad sample row; keep building
231 }
232 }
233 created.push({ table: table.name, columns: columns.length, rows });
234 }
235
236 return { summary: plan.summary, created, skipped };
237}