database.ts · Source

database.tspackages/ai/src/schemas/database.tsraw ← schemas15,103 B← Back to the app
1/**
2 * TASK-0124 — Database AI: shared, deterministic schemas + helpers.
3 *
4 * Single source of truth for the three database-generation tool calls
5 * (`createDatabase`, `insertRecords`, `generateFormula`) — the SAME zod
6 * shapes the LLM is told to emit AND the server validates against BEFORE any
7 * write (spec §Security: "validated by zod before use"). Importable by the
8 * API service, the MockProvider (deterministic CI mode), and the unit tests.
9 *
10 * Zero runtime deps beyond `zod` (the package's one allowed dep). No app
11 * imports here — this is domain-schema code, not a NestJS module.
12 */
13import { z } from "zod";
14 
15/**
16 * The 24 `PropertyType` values — a MIRROR of the Prisma `enum PropertyType`
17 * (`prisma/schema.prisma`) and `apps/api/src/constants.ts` `PROPERTY_TYPES`.
18 *
19 * NOTE (recorded, spec-vs-reality): the TASK-0124 spec says "the 22
20 * `PropertyType` values" and names an `extraction` type. The actual enum has
21 * **24** values and has no `EXTRACTION` member. We mirror the real enum (the
22 * architecture is the source of truth — AGENTS.md §29) and express the spec's
23 * generated-property allowlist as "all real types minus the ones that cannot
24 * be created standalone" (see {@link NON_GENERATABLE_PROPERTY_TYPES}).
25 */
26export const PROPERTY_TYPES = [
27  "TITLE",
28  "TEXT",
29  "RICH_TEXT",
30  "NUMBER",
31  "SELECT",
32  "MULTI_SELECT",
33  "STATUS",
34  "DATE",
35  "DATE_RANGE",
36  "PERSON",
37  "EMAIL",
38  "PHONE",
39  "URL",
40  "CHECKBOX",
41  "FILES",
42  "CREATED_TIME",
43  "CREATED_BY",
44  "LAST_EDITED_TIME",
45  "LAST_EDITED_BY",
46  "RELATION",
47  "ROLLUP",
48  "FORMULA",
49  "BUTTON",
50  "UNIQUE_ID",
51] as const;
52export type PropertyType = (typeof PROPERTY_TYPES)[number];
53 
54/** True when `v` is one of the 24 enum values. */
55export function isPropertyType(v: unknown): v is PropertyType {
56  return typeof v === "string" && (PROPERTY_TYPES as readonly string[]).includes(v);
57}
58 
59/**
60 * The generated-property allowlist: the types the model may CREATE standalone
61 * in a generated database. Every other type is REJECTED (with a reason) before
62 * any write. The complement of {@link GENERATABLE_PROPERTY_TYPES} is
63 * {@link NON_GENERATABLE_PROPERTY_TYPES} — the spec's "relation/rollup/
64 * button/formula/extraction not allowed in generated properties" plus `TITLE`
65 * (reserved: a database seeds exactly one title property, and creating a
66 * second throws `title_exists`), which the spec omits but the schema forbids.
67 *
68 * `EXTRACTION` is intentionally absent: it is not a `PropertyType` value.
69 */
70export const GENERATABLE_PROPERTY_TYPES: readonly PropertyType[] = [
71  "TEXT",
72  "RICH_TEXT",
73  "NUMBER",
74  "SELECT",
75  "MULTI_SELECT",
76  "STATUS",
77  "DATE",
78  "DATE_RANGE",
79  "PERSON",
80  "EMAIL",
81  "PHONE",
82  "URL",
83  "CHECKBOX",
84  "FILES",
85  "CREATED_TIME",
86  "CREATED_BY",
87  "LAST_EDITED_TIME",
88  "LAST_EDITED_BY",
89  "UNIQUE_ID",
90];
91 
92/** The reject table — every non-generatable type with its reason. */
93export const NON_GENERATABLE_PROPERTY_TYPES: Record<string, string> = {
94  TITLE: "reserved: a database seeds exactly one title property (use the `title` field)",
95  RELATION: "requires a target database (create a relation separately)",
96  ROLLUP: "requires a relation (create a relation separately)",
97  FORMULA: "generated by the dedicated formula tool, not database generation",
98  BUTTON: "requires view-action config",
99};
100 
101export function isGeneratablePropertyType(v: PropertyType): boolean {
102  return (GENERATABLE_PROPERTY_TYPES as readonly string[]).includes(v);
103}
104 
105/**
106 * LLM-vocabulary → PropertyType normalization. The model is instructed to emit
107 * one of the enum values but may also emit common synonyms ("multiSelect",
108 * "date range", "checkbox", "boolean", …). Case/punctuation-insensitive.
109 * Returns `null` for anything not mappable (the property is then rejected).
110 */
111const TYPE_ALIASES: Record<string, PropertyType> = {
112  // exact enum values (lowercased)
113  title: "TITLE",
114  text: "TEXT",
115  rich_text: "RICH_TEXT",
116  richtext: "RICH_TEXT",
117  "rich text": "RICH_TEXT",
118  number: "NUMBER",
119  numeric: "NUMBER",
120  integer: "NUMBER",
121  float: "NUMBER",
122  select: "SELECT",
123  "single select": "SELECT",
124  "single-select": "SELECT",
125  dropdown: "SELECT",
126  multi_select: "MULTI_SELECT",
127  multiselect: "MULTI_SELECT",
128  "multi select": "MULTI_SELECT",
129  "multi-select": "MULTI_SELECT",
130  tags: "MULTI_SELECT",
131  status: "STATUS",
132  date: "DATE",
133  date_range: "DATE_RANGE",
134  daterange: "DATE_RANGE",
135  "date range": "DATE_RANGE",
136  person: "PERSON",
137  user: "PERSON",
138  assignee: "PERSON",
139  email: "EMAIL",
140  phone: "PHONE",
141  url: "URL",
142  link: "URL",
143  web_link: "URL",
144  "web link": "URL",
145  checkbox: "CHECKBOX",
146  check: "CHECKBOX",
147  boolean: "CHECKBOX",
148  bool: "CHECKBOX",
149  yes_no: "CHECKBOX",
150  files: "FILES",
151  file: "FILES",
152  attachment: "FILES",
153  attachments: "FILES",
154  created_time: "CREATED_TIME",
155  "created time": "CREATED_TIME",
156  created: "CREATED_TIME",
157  created_by: "CREATED_BY",
158  "created by": "CREATED_BY",
159  last_edited_time: "LAST_EDITED_TIME",
160  "last edited time": "LAST_EDITED_TIME",
161  last_edited_by: "LAST_EDITED_BY",
162  "last edited by": "LAST_EDITED_BY",
163  relation: "RELATION",
164  "many to many": "RELATION",
165  rollup: "ROLLUP",
166  formula: "FORMULA",
167  computed: "FORMULA",
168  button: "BUTTON",
169  action: "BUTTON",
170  unique_id: "UNIQUE_ID",
171  "unique id": "UNIQUE_ID",
172  id: "UNIQUE_ID",
173};
174 
175/** Normalize a raw LLM `type` string to a PropertyType, or `null`. */
176export function normalizePropertyType(raw: unknown): PropertyType | null {
177  if (typeof raw !== "string") return null;
178  const key = raw.trim().toLowerCase().replace(/\s+/g, " ").replace(/[\s-]+/g, "_").replace(/_/g, "_");
179  // Try the exact (lowercased) key, then the spaced form, then the enum name.
180  const candidates = [key, key.replace(/_/g, " "), raw.trim().toLowerCase()];
181  for (const c of candidates) {
182    const hit = TYPE_ALIASES[c];
183    if (hit) return hit;
184  }
185  if (isPropertyType(raw)) return raw;
186  return null;
187}
188 
189// ---------------------------------------------------------------------------
190// Tool-call schemas (the LLM emits these; the server validates them pre-write)
191// ---------------------------------------------------------------------------
192 
193/** One generated property. `options` is required for select/multiSelect/status. */
194export const GeneratedPropertySchema = z.object({
195  name: z.string().min(1).max(100),
196  /** The model's raw type token — normalized via {@link normalizePropertyType}. */
197  type: z.string().min(1),
198  /** For select / multi_select / status: the option names (+ optional color). */
199  options: z
200    .array(
201      z.object({
202        name: z.string().min(1).max(50),
203        color: z.string().max(20).optional(),
204      }),
205    )
206    .optional(),
207  required: z.boolean().optional(),
208});
209export type GeneratedProperty = z.infer<typeof GeneratedPropertySchema>;
210 
211/** One sample record (≤10). `properties` is keyed by property NAME (the
212 *  properties are created in the same call, so no ids exist yet). */
213export const GeneratedSampleRecordSchema = z.object({
214  properties: z.record(z.string(), z.unknown()),
215});
216export type GeneratedSampleRecord = z.infer<typeof GeneratedSampleRecordSchema>;
217 
218/**
219 * The `createDatabase` tool call (generate). Validated before ANY write.
220 * Limits: ≤30 properties, ≤10 sample records (spec).
221 */
222export const CreateDatabaseSchema = z.object({
223  title: z.string().min(1).max(200),
224  description: z.string().max(500).optional(),
225  properties: z.array(GeneratedPropertySchema).min(1).max(30),
226  sampleRecords: z.array(GeneratedSampleRecordSchema).max(10).optional(),
227});
228export type CreateDatabaseArgs = z.infer<typeof CreateDatabaseSchema>;
229 
230/** One extracted record. `properties` is keyed by property ID (the database +
231 *  its properties already exist). ≤200 records per call (spec). */
232export const InsertedRecordSchema = z.object({
233  properties: z.record(z.string(), z.unknown()),
234});
235export type InsertedRecord = z.infer<typeof InsertedRecordSchema>;
236 
237/** The `insertRecords` tool call (extract). Validated before ANY write. */
238export const InsertRecordsSchema = z.object({
239  records: z.array(InsertedRecordSchema).min(1).max(200),
240  /** Column names the model could not map to a property (informational). */
241  ignoredColumns: z.array(z.string()).optional(),
242});
243export type InsertRecordsArgs = z.infer<typeof InsertRecordsSchema>;
244 
245/** The `generateFormula` tool call. Validated by the formula engine BEFORE
246 *  saving (spec: never store an invalid expression). */
247export const GenerateFormulaSchema = z.object({
248  expression: z.string().min(1).max(2000),
249  explanation: z.string().max(500).optional(),
250});
251export type GenerateFormulaArgs = z.infer<typeof GenerateFormulaSchema>;
252 
253/**
254 * RE-EXPORT — `optionIdForName` (a deterministic, stable option `id` from an
255 * option NAME: select/status/multi-select options carry a string `id` and the
256 * engine's `values` registry resolves a stored `optionId` against
257 * `config.options[].id`; the AI path generates these ids so it can map the
258 * model's option NAMES → the stored option IDs without a round-trip) and
259 * `normalizeSelectOptions` MOVED to `@factory/shared` (`dto/database.dto.ts`)
260 * so the web read boundary (which does not depend on `@factory/ai`) shares
261 * the EXACT same generator the server uses. Import from `@factory/shared`
262 * for new code; this alias keeps the existing `@factory/ai` import sites
263 * (the API, this package's tests) working unchanged.
264 */
265export { optionIdForName, normalizeSelectOptions } from "@factory/shared";
266 
267/** The property-type the engine's `propertyTypes` map wants (lowercase). */
268export function lowerPropertyType(t: string): string {
269  return t.toLowerCase();
270}
271 
272// ---------------------------------------------------------------------------
273// Limits (single source for the prompts + the service gates)
274// ---------------------------------------------------------------------------
275export const DB_GEN_MAX_PROPERTIES = 30;
276export const DB_GEN_MAX_SAMPLE_RECORDS = 10;
277export const DB_EXTRACT_MAX_RECORDS = 200;
278export const DB_EXTRACT_MAX_TEXT_CHARS = 50_000;
279export const DB_FORMULA_MAX_SAMPLE_ROWS = 5;
280 
281// ---------------------------------------------------------------------------
282// Markdown table parser (deterministic — the spec's extract path + unit tests)
283// ---------------------------------------------------------------------------
284 
285/**
286 * Parse a markdown table into `{ columns, rows }`.
287 *
288 * Handles: leading/trailing pipes, escaped pipes (`\|`), a missing header row
289 * (a table whose first line is a data row — columns are then `column_1..n`),
290 * and the `---` separator row (skipped). A non-table (no `|`, or a single
291 * row with no separator) returns `{ isTable: false, columns: [], rows: [] }`.
292 *
293 * A line is a table row iff it contains at least one unescaped `|`. The FIRST
294 * such line is the header; a following `---…` line (only `-`, `:`, spaces) is
295 * the separator and is skipped; the rest are data rows.
296 */
297export interface ParsedMarkdownTable {
298  isTable: boolean;
299  columns: string[];
300  /** Each row is `column → cell` (header-aligned; missing cells → ""). */
301  rows: Array<Record<string, string>>;
302}
303 
304function splitCells(line: string): string[] {
305  // Strip a leading/trailing pipe, then split on unescaped pipes.
306  let s = line.trim();
307  if (s.startsWith("|")) s = s.slice(1);
308  if (s.endsWith("|")) s = s.slice(0, -1);
309  // Un-escape `\|` to a sentinel, split, then restore.
310  const SENTINEL = "\u0000PIPE\u0000";
311  s = s.replace(/\\\|/g, SENTINEL);
312  const cells = s.split("|").map((c) => c.replace(new RegExp(SENTINEL, "g"), "|").trim());
313  return cells;
314}
315 
316/** A separator cell: only `-`, `:` (and alignment colons), e.g. `---`, `:---:`,
317 *  `-`. Empty cells are allowed (trailing alignment). */
318function isSeparatorCell(c: string): boolean {
319  const t = c.trim();
320  return t === "" || /^:?-+:?$/.test(t);
321}
322 
323/** A markdown separator row: every cell is a dash/colon alignment marker. */
324function isSeparatorRow(line: string): boolean {
325  const s = line.trim();
326  if (s === "") return false;
327  const cells = splitCells(s);
328  if (cells.length === 0) return false;
329  const nonEmpty = cells.filter((c) => c.trim() !== "");
330  if (nonEmpty.length === 0) return false; // all-empty row is not a separator
331  return cells.every(isSeparatorCell);
332}
333 
334export function parseMarkdownTable(text: string): ParsedMarkdownTable {
335  const lines = text.split(/\r?\n/).filter((l) => l.trim() !== "");
336  // Table rows contain an unescaped pipe (or the un-escaped sentinel, which
337  // cannot appear in raw text). Prose lines are ignored.
338  const rowLines = lines.filter((l) => l.includes("|"));
339  const hasSeparator = rowLines.some((l) => isSeparatorRow(l));
340  // A table needs a header + at least one data row, OR a header + separator.
341  // (A lone header line, or prose, is not a table.)
342  if (rowLines.length < 2) return { isTable: false, columns: [], rows: [] };
343  const isTable = hasSeparator || rowLines.length >= 2;
344  if (!isTable) return { isTable: false, columns: [], rows: [] };
345 
346  const header: string[] = [];
347  const dataLines: string[] = [];
348  for (const line of rowLines) {
349    if (isSeparatorRow(line)) continue;
350    if (header.length === 0 && dataLines.length === 0) {
351      // First non-separator row = header.
352      header.push(...splitCells(line));
353    } else {
354      dataLines.push(line);
355    }
356  }
357  if (header.length === 0) return { isTable: false, columns: [], rows: [] };
358 
359  // Header cells that are empty (a "missing header" column) get a synthetic
360  // `column_i` name so the mapping step can still address them (they simply
361  // never match a property → reported as `ignored`).
362  const columns = header.map((h, i) => (h === "" ? `column_${i + 1}` : h));
363 
364  const rows: Array<Record<string, string>> = dataLines.map((line) => {
365    const cells = splitCells(line);
366    const row: Record<string, string> = {};
367    columns.forEach((col, i) => {
368      row[col] = cells[i] ?? "";
369    });
370    return row;
371  });
372 
373  return { isTable: true, columns, rows };
374}
375 
376/**
377 * Map markdown COLUMNS to existing properties BY NAME (case-insensitive,
378 * whitespace-normalized). Returns the matched `{column, propertyId}` pairs and
379 * the `ignored` columns (no property with that name). Deterministic — the
380 * server uses it for `ignoredColumns` in the response, the mock uses it to
381 * build its `insertRecords` records.
382 */
383export function mapColumnsToProperties(
384  columns: string[],
385  properties: Array<{ id: string; name: string }>,
386): { mapped: Array<{ column: string; propertyId: string }>; ignored: string[] } {
387  const byName = new Map<string, string>();
388  for (const p of properties) byName.set(normalizeName(p.name), p.id);
389  const mapped: Array<{ column: string; propertyId: string }> = [];
390  const ignored: string[] = [];
391  for (const col of columns) {
392    const id = byName.get(normalizeName(col));
393    if (id) mapped.push({ column: col, propertyId: id });
394    else ignored.push(col);
395  }
396  return { mapped, ignored };
397}
398 
399function normalizeName(s: string): string {
400  return s.trim().toLowerCase().replace(/\s+/g, " ");
401}
402