database.dto.ts

database.dto.tspackages/shared/src/dto/database.dto.tsraw ← dto15,459 B← Back to the app
1/**
2 * @factory/shared — database DTOs (PRODUCT-SPEC §9; prisma `Database*`).
3 *
4 * NOTE: the spec header says "22 property types" but both the spec list and
5 * the prisma `PropertyType` enum define 24 (Title … Unique ID). We implement
6 * the full enum; see the recorded conflict in the package README.
7 */
8 
9import { z } from "zod";
10 
11import { LIMITS } from "../constants.js";
12 
13// --- property types ------------------------------------------------------------
14 
15/** All property types (lowercase snake_case; prisma `PropertyType`). */
16export const propertyTypeSchema = z.enum([
17  "title",
18  "text",
19  "rich_text",
20  "number",
21  "select",
22  "multi_select",
23  "status",
24  "date",
25  "date_range",
26  "person",
27  "email",
28  "phone",
29  "url",
30  "checkbox",
31  "files",
32  "created_time",
33  "created_by",
34  "last_edited_time",
35  "last_edited_by",
36  "relation",
37  "rollup",
38  "formula",
39  "button",
40  "unique_id",
41]);
42export type PropertyType = z.infer<typeof propertyTypeSchema>;
43 
44export const selectOptionSchema = z
45  .object({
46    id: z.string().min(1),
47    name: z.string().min(1).max(100),
48    color: z
49      .string()
50      .regex(/^(default|gray|brown|orange|yellow|green|blue|purple|pink|red)(_bg)?$/),
51    position: z.string().min(1).optional(),
52  })
53  .strict();
54export type SelectOption = z.infer<typeof selectOptionSchema>;
55 
56const optionsConfig = z.object({
57  options: z.array(selectOptionSchema).max(100),
58});
59 
60/**
61 * The option-id wire contract for select-family properties (SELECT /
62 * MULTI_SELECT / STATUS — docs/api/databases.md): options are
63 * `{ id, name, color }` and stored values reference the option via
64 * `{ optionId: <option.id> }`.
65 *
66 * `optionIdForName` is the ONE deterministic id generator (id-less options
67 * get it on write — `normalizeSelectOptions` — and on every read boundary,
68 * server AND client), so a stored NAME and its canonical id always resolve
69 * to the same option no matter which side saw the raw config first.
70 *
71 * Moved from `@factory/ai` (TASK-0124) into this package so the WEB read
72 * boundary can share the exact same generator (the web does not depend on
73 * `@factory/ai`). `@factory/ai` re-exports both names unchanged — existing
74 * import sites keep working.
75 */
76 
77/**
78 * Deterministic, slug-safe option id derived from an option NAME
79 * (e.g. "In Progress" → `opt-in-progress`). NFKD-strips diacritics and
80 * non-alphanumerics; falls back to a length-derived id for names with no
81 * surviving slug (e.g. all-whitespace). Pure (unit-tested in
82 * `database.dto.test.ts`).
83 */
84export function optionIdForName(name: string): string {
85  const slug = name
86    .trim()
87    .toLowerCase()
88    .normalize("NFKD")
89    .replace(/[\u0300-\u036f]/g, "")
90    .replace(/[^a-z0-9]+/g, "-")
91    .replace(/^-+|-+$/g, "");
92  return slug ? `opt-${slug}` : `opt-${name.length}`;
93}
94 
95/**
96 * Backfill a DETERMINISTIC id on any select/status/multi-select option that
97 * lacks one (the AI generation path historically emitted `config.options` as
98 * bare `{ name, color }`, which broke every id-based reader: empty chips,
99 * broken preselect, `optionId: undefined` commits).
100 *
101 * `existingOptions` (the option list already persisted for the property)
102 * reuses an option's id by NAME when an incoming patch re-lists that option
103 * without its id — INCLUDING a custom (non-deterministic) id — so existing
104 * `{ optionId }` values stay resolvable; a genuinely new option gets the
105 * deterministic `optionIdForName(name)` id.
106 *
107 * Idempotent: an already-conformant config is returned BY REFERENCE
108 * unchanged (callers may rely on referential stability). Pure (unit-tested).
109 */
110export function normalizeSelectOptions(config: unknown, existingOptions?: unknown): unknown {
111  const cfg = (config ?? {}) as Record<string, unknown>;
112  const options = cfg.options;
113  if (!Array.isArray(options)) return config;
114  const byName = new Map<string, string>();
115  if (Array.isArray(existingOptions)) {
116    for (const o of existingOptions) {
117      const e = o as { id?: unknown; name?: unknown } | null;
118      if (e && typeof e.name === "string" && typeof e.id === "string" && e.id) {
119        byName.set(e.name, e.id);
120      }
121    }
122  }
123  let changed = false;
124  const next = options.map((o) => {
125    const opt = o as { id?: string; name?: string };
126    if (opt && typeof opt.name === "string" && (!opt.id || typeof opt.id !== "string" || opt.id.length === 0)) {
127      const id = byName.get(opt.name) ?? optionIdForName(opt.name);
128      changed = true;
129      return { ...opt, id };
130    }
131    return o;
132  });
133  return changed ? { ...cfg, options: next } : config;
134}
135 
136const numberFormatSchema = z
137  .string()
138  .regex(/^(number|integer|decimal|percent|dollar|euro|pound|yen|cny|inr)$/);
139 
140/**
141 * Per-type property `config` (stored in `database_properties.config`).
142 * Types without settings use an empty object.
143 */
144export const propertyConfigSchema = z.discriminatedUnion("type", [
145  z.object({ type: z.literal("title"), config: z.object({}).strict() }),
146  z.object({ type: z.literal("text"), config: z.object({}).strict() }),
147  z.object({ type: z.literal("rich_text"), config: z.object({}).strict() }),
148  z.object({
149    type: z.literal("number"),
150    config: z
151      .object({
152        format: numberFormatSchema.default("number"),
153        decimals: z.number().int().min(0).max(8).optional(),
154        prefix: z.string().max(16).optional(),
155        suffix: z.string().max(16).optional(),
156      })
157      .strict(),
158  }),
159  z.object({ type: z.literal("select"), config: optionsConfig }),
160  z.object({ type: z.literal("multi_select"), config: optionsConfig }),
161  z.object({ type: z.literal("status"), config: optionsConfig }),
162  z.object({
163    type: z.literal("date"),
164    config: z
165      .object({
166        format: z
167          .string()
168          .regex(/^(yyyy-mm-dd|yyyy\/mm\/dd|mm\/dd\/yyyy|dd\/mm\/yyyy|dd-mm-yyyy)$/),
169        timeEnabled: z.boolean().default(false),
170      })
171      .strict(),
172  }),
173  z.object({
174    type: z.literal("date_range"),
175    config: z
176      .object({
177        format: z
178          .string()
179          .regex(/^(yyyy-mm-dd|yyyy\/mm\/dd|mm\/dd\/yyyy|dd\/mm\/yyyy|dd-mm-yyyy)$/),
180      })
181      .strict(),
182  }),
183  z.object({
184    type: z.literal("person"),
185    config: z
186      .object({
187        allowMultiple: z.boolean().default(true),
188      })
189      .strict(),
190  }),
191  z.object({ type: z.literal("email"), config: z.object({}).strict() }),
192  z.object({ type: z.literal("phone"), config: z.object({}).strict() }),
193  z.object({ type: z.literal("url"), config: z.object({}).strict() }),
194  z.object({ type: z.literal("checkbox"), config: z.object({}).strict() }),
195  z.object({
196    type: z.literal("files"),
197    config: z
198      .object({
199        allowMultiple: z.boolean().default(true),
200      })
201      .strict(),
202  }),
203  z.object({ type: z.literal("created_time"), config: z.object({}).strict() }),
204  z.object({ type: z.literal("created_by"), config: z.object({}).strict() }),
205  z.object({ type: z.literal("last_edited_time"), config: z.object({}).strict() }),
206  z.object({ type: z.literal("last_edited_by"), config: z.object({}).strict() }),
207  z.object({
208    type: z.literal("relation"),
209    config: z
210      .object({
211        relationId: z.string().min(1),
212      })
213      .strict(),
214  }),
215  z.object({
216    type: z.literal("rollup"),
217    config: z
218      .object({
219        /** Relation property used to reach the linked records. */
220        relationPropertyId: z.string().min(1),
221        /** Property on the linked records to aggregate. */
222        targetPropertyId: z.string().min(1),
223        aggregate: z.enum(["count", "sum", "average", "min", "max", "percent", "unique"]),
224      })
225      .strict(),
226  }),
227  z.object({
228    type: z.literal("formula"),
229    config: z
230      .object({
231        /** Safe expression source (evaluated by the AST engine, never JS). */
232        expression: z.string().min(1).max(2000),
233      })
234      .strict(),
235  }),
236  z.object({
237    type: z.literal("button"),
238    config: z
239      .object({
240        label: z.string().min(1).max(64).optional(),
241        actions: z
242          .array(
243            z
244              .object({
245                type: z.enum([
246                  "set_property",
247                  "create_page",
248                  "update_page",
249                  "archive_page",
250                  "duplicate_page",
251                  "send_webhook",
252                  "trigger_automation",
253                ]),
254                config: z.record(z.unknown()),
255              })
256              .strict(),
257          )
258          .min(1)
259          .max(10),
260      })
261      .strict(),
262  }),
263  z.object({
264    type: z.literal("unique_id"),
265    config: z
266      .object({
267        format: z.enum(["number", "uuid"]).default("number"),
268        prefix: z.string().max(16).optional(),
269      })
270      .strict(),
271  }),
272]);
273export type PropertyConfig = z.infer<typeof propertyConfigSchema>;
274 
275/** A database property definition (schema column). */
276export const databasePropertySchema = z
277  .object({
278    id: z.string().min(1).optional(),
279    name: z.string().min(1).max(100),
280    type: propertyTypeSchema,
281    /** Positional order of the column (fractional). */
282    position: z.string().min(1).optional(),
283    isTitle: z.boolean().default(false),
284    required: z.boolean().default(false),
285    config: z.record(z.unknown()).default({}),
286    default: z.unknown().optional(),
287  })
288  .strict()
289  .superRefine((val, ctx) => {
290    const result = propertyConfigSchema.safeParse({ type: val.type, config: val.config });
291    if (!result.success) {
292      for (const issue of result.error.issues) {
293        ctx.addIssue({
294          code: z.ZodIssueCode.custom,
295          message: `invalid config for property type "${val.type}": ${issue.message}`,
296          path: ["config", ...issue.path],
297        });
298      }
299    }
300  });
301export type DatabaseProperty = z.infer<typeof databasePropertySchema>;
302 
303// --- databases -----------------------------------------------------------------
304 
305export const createDatabaseSchema = z
306  .object({
307    title: z.string().min(1).max(200),
308    icon: z.string().max(32).optional().nullable(),
309    description: z.string().max(500).optional().nullable(),
310    /** Initial properties; one must be (or become) the title. */
311    properties: z.array(databasePropertySchema).max(100).optional(),
312  })
313  .strict();
314export type CreateDatabase = z.infer<typeof createDatabaseSchema>;
315 
316export const createPropertySchema = databasePropertySchema;
317export type CreateProperty = z.infer<typeof createPropertySchema>;
318 
319// --- views ---------------------------------------------------------------------
320 
321/** All 9 view types (prisma `ViewType`). */
322export const viewTypeSchema = z.enum([
323  "table",
324  "list",
325  "board",
326  "gallery",
327  "calendar",
328  "timeline",
329  "chart",
330  "dashboard",
331  "form",
332]);
333export type ViewType = z.infer<typeof viewTypeSchema>;
334 
335// --- filters / sorts / groups ---------------------------------------------------
336 
337// Leaf operators the TASK-0067 filter engine implements (the per-type matrix in
338// docs/tasks/phase-05-databases.md #TASK-0067). The shared union MUST cover the
339// full set the engine evaluates — a narrower enum both (a) breaks the engine's
340// own `op === "<leaf>"` type-narrowing (TS2367 "no overlap") and (b) makes the
341// API's `filterNodeSchema` reject valid filter trees (e.g. a checkbox
342// `isChecked` or a file `has_files`) that the engine fully supports.
343// (T-187: was 11 ops — the engine grew to the full matrix without the enum
344// being extended, so 10 spec'd operators were silently invalid at the boundary.)
345export const filterOperatorSchema = z.enum([
346  "=",
347  "!=",
348  "contains",
349  "doesNotContain",
350  "starts_with",
351  "ends_with",
352  ">",
353  ">=",
354  "<",
355  "<=",
356  "is_empty",
357  "is_not_empty",
358  "isBetween",
359  "isBefore",
360  "isAfter",
361  "isOnOrAfter",
362  "isOnOrBefore",
363  "isChecked",
364  "isUnchecked",
365  "has_files",
366  "has_no_files",
367]);
368export type FilterOperator = z.infer<typeof filterOperatorSchema>;
369 
370/**
371 * A recursive boolean filter tree (PRODUCT-SPEC §9.3):
372 *
373 * ```
374 * (Status = In Progress OR Status = Review) AND Owner = Current User
375 * ```
376 *
377 * Groups: `{op: "and"|"or"|"not", conditions: FilterNode[]}`.
378 * Leaf:   `{propertyId, operator, value?}` — `value` is omitted for
379 * `is_empty`/`is_not_empty`.
380 */
381export type FilterNode =
382  | { op: "and" | "or" | "not"; conditions: FilterNode[] }
383  | { propertyId: string; operator: FilterOperator; value?: unknown };
384 
385export const filterNodeSchema: z.ZodType<FilterNode> = z.lazy(() =>
386  z.union([
387    z.object({
388      op: z.enum(["and", "or", "not"]),
389      conditions: z.array(filterNodeSchema).min(1).max(100),
390    }),
391    z
392      .object({
393        propertyId: z.string().min(1),
394        operator: filterOperatorSchema,
395        value: z.unknown().optional(),
396      })
397      .superRefine((val, ctx) => {
398        const isEmptyOp = val.operator === "is_empty" || val.operator === "is_not_empty";
399        if (!isEmptyOp && val.value === undefined) {
400          ctx.addIssue({
401            code: z.ZodIssueCode.custom,
402            message: `operator "${val.operator}" requires a value`,
403          });
404        }
405      }),
406  ]),
407);
408 
409/** A single sort spec. */
410export const sortSpecSchema = z
411  .object({
412    propertyId: z.string().min(1),
413    direction: z.enum(["asc", "desc"]).default("asc"),
414  })
415  .strict();
416export type SortSpec = z.infer<typeof sortSpecSchema>;
417 
418/** A single group spec (board/calendar/timeline group-by). */
419export const groupSpecSchema = z
420  .object({
421    propertyId: z.string().min(1),
422    direction: z.enum(["asc", "desc"]).default("asc"),
423  })
424  .strict();
425export type GroupSpec = z.infer<typeof groupSpecSchema>;
426 
427// --- records / queries -----------------------------------------------------------
428 
429export const createRecordSchema = z
430  .object({
431    /** Property id or name → value (typed per property; validated in the
432     *  database service where property configs are known). */
433    properties: z.record(z.string(), z.unknown()),
434  })
435  .strict();
436export type CreateRecord = z.infer<typeof createRecordSchema>;
437 
438export const updateRecordSchema = z
439  .object({
440    properties: z.record(z.string(), z.unknown()),
441  })
442  .strict();
443export type UpdateRecord = z.infer<typeof updateRecordSchema>;
444 
445/**
446 * A paged, filtered database query (`POST /databases/:id/query`). The engine
447 * (packages/database-engine) evaluates filter/sort/group; see the query
448 * strategy documented there.
449 */
450export const recordQuerySchema = z
451  .object({
452    viewId: z.string().min(1).optional(),
453    filter: filterNodeSchema.optional(),
454    sort: z.array(sortSpecSchema).max(20).optional(),
455    group: z.array(groupSpecSchema).max(5).optional(),
456    /** Opaque cursor (record position or id) for the next page. */
457    cursor: z.string().min(1).optional(),
458    pageSize: z.number().int().min(1).max(LIMITS.MAX_RECORDS_PER_PAGE).default(50),
459  })
460  .strict();
461export type RecordQuery = z.infer<typeof recordQuerySchema>;
462 
463// --- views (continued) -------------------------------------------------------------
464 
465export const createViewSchema = z
466  .object({
467    name: z.string().min(1).max(100),
468    type: viewTypeSchema,
469    filter: filterNodeSchema.optional(),
470    sort: z.array(sortSpecSchema).max(20).optional(),
471    group: z.array(groupSpecSchema).max(5).optional(),
472    /** View config: visiblePropertyIds, columnWidths, cardLayout, color, density… */
473    config: z.record(z.unknown()).default({}),
474  })
475  .strict();
476export type CreateView = z.infer<typeof createViewSchema>;
477