database.service.ts

database.service.tsapps/api/src/modules/databases/database.service.tsraw ← databases41,188 B← Back to the app
1import { Inject, Injectable } from "@nestjs/common";
2import { Prisma } from "@prisma/client";
3import { z } from "zod";
4import { normalizeSelectOptions } from "@factory/shared";
5export { normalizeSelectOptions };
6import { PrismaService } from "../../common/prisma.service.js";
7import { AuditService } from "../../common/audit.service.js";
8import { EventBus } from "../../common/event-bus.js";
9import { CacheService } from "../../common/cache.js";
10import { ApiError } from "../../common/errors.js";
11import { DatabaseQueryService } from "./query.service.js";
12import { actorMembership, assertCan } from "../../lib/authorize.js";
13import { PROPERTY_CACHE } from "./property-cache-schema.js";
14export { PROPERTY_CACHE };
15import { between } from "../../lib/positions.js";
16import { EVENTS } from "../../constants.js";
17import type {
18  CreateFormulaInput,
19  CreatePropertyInput,
20  CreateRelationInput,
21  CreateViewInput,
22  UpdateDatabaseInput,
23  UpdatePropertyInput,
24  UpdateViewInput,
25  ValidateFormulaInput
26} from "./dto.js";
27 
28const DB_SELECT = {
29  id: true, workspaceId: true, pageId: true, title: true, icon: true,
30  description: true, schemaVersion: true, isArchived: true, createdBy: true,
31  createdAt: true, updatedAt: true
32} as const;
33type DbRow = Prisma.DatabaseGetPayload<{ select: typeof DB_SELECT }>;
34 
35/**
36 * TASK-0135: the database column metadata (property list + view settings) is
37 * cached for 60 s and busted on `database.updated` (a property/view change).
38 * The schemas below match the JSON shape of the LIVE `{ data: [...] }`
39 * responses so a corrupt entry is a cache MISS, never a 500. `findMany` is
40 * called with NO `include`/`select`, so Prisma returns every SCALAR field and
41 * omits the relations — the schemas below mirror exactly those scalars
42 * (Prisma JSON columns are opaque objects in JSON form; dates → ISO strings).
43 */
44const VIEW_CACHE = z.object({
45  id: z.string(),
46  databaseId: z.string(),
47  name: z.string(),
48  type: z.string(),
49  position: z.string(),
50  config: z.unknown(),
51  filter: z.unknown().nullable(),
52  sort: z.unknown().nullable(),
53  group: z.unknown().nullable(),
54  pageSize: z.number().nullable(),
55  createdAt: z.string(),
56  updatedAt: z.string()
57});
58 
59/**
60 * `normalizeSelectOptions` — the option-id backfill (deterministic ids for
61 * id-less select/status/multi-select options) — moved to `@factory/shared`
62 * (`dto/database.dto.ts`) as the single source shared by the SERVER read
63 * boundaries, the AI generation path, AND the web read boundary (the web
64 * cannot depend on `@factory/ai`). Re-exported here so the existing import
65 * sites (the API unit tests) keep working unchanged.
66 */
67 
68/** Ban obviously dangerous constructs in formula source before storage. */
69export function assertFormulaSafe(expression: string): void {
70  if (/eval\s*\(|Function\s*\(|new\s+Function|import\s*\(|require\s*\(|child_process|process\.env/i.test(expression)) {
71    throw ApiError.validation("Formula contains a forbidden construct.", [
72      { field: "expression", issue: "forbidden_construct" }
73    ]);
74  }
75}
76 
77/** Very loose dependency scan: property references inside prop("...") calls. */
78export function scanFormulaDependencies(expression: string): string[] {
79  const refs = new Set<string>();
80  const re = /prop\s*\(\s*["']([^"']+)["']\s*\)/g;
81  let m: RegExpExecArray | null;
82  while ((m = re.exec(expression)) !== null) refs.add(m[1]!);
83  return [...refs];
84}
85 
86/**
87 * Database schema service: properties (22 types), views (9 types),
88 * relations, formulas. Record query/CRUD lives in query.service.ts.
89 */
90@Injectable()
91export class DatabaseService {
92  constructor(
93    @Inject(PrismaService) private readonly prisma: PrismaService,
94    @Inject(AuditService) private readonly audit: AuditService,
95    @Inject(EventBus) private readonly events: EventBus,
96    @Inject(DatabaseQueryService) private readonly query: DatabaseQueryService,
97    @Inject(CacheService) private readonly cache: CacheService
98  ) {}
99 
100  private async getDb(databaseId: string): Promise<DbRow> {
101    const db = await this.prisma.database.findUnique({ where: { id: databaseId }, select: DB_SELECT });
102    if (!db || db.isArchived) throw ApiError.notFound("Database not found.");
103    return db;
104  }
105 
106  /**
107   * List the workspace's (non-archived) databases — the mention popup's
108   * database group (TASK-0094 R3). Membership-gated (any role): the popup
109   * surfaces exactly what the sidebar already shows, so no extra scoping
110   * beyond membership. Bounded: `limit` (default 50, capped 100) by
111   * recency.
112   */
113  async listByWorkspace(workspaceId: string, userId: string, limit = 50) {
114    // TASK-0127: actor-aware membership (an API key of the workspace is a
115    // member-equivalent; the creator's membership is never probed).
116    const membership = await actorMembership(this.prisma, userId, workspaceId);
117    if (!membership) throw ApiError.notFound("Workspace not found.");
118    const capped = Math.max(1, Math.min(limit, 100));
119    const rows = await this.prisma.database.findMany({
120      where: { workspaceId, isArchived: false },
121      select: { id: true, title: true, icon: true },
122      orderBy: { updatedAt: "desc" },
123      take: capped
124    });
125    return rows;
126  }
127 
128  private async getDbChecked(databaseId: string, userId: string, need: "VIEW" | "EDIT" | "FULL_ACCESS") {
129    const db = await this.getDb(databaseId);
130    await assertCan(this.prisma, { workspaceId: db.workspaceId, userId, resource: { databaseId: db.id } }, need);
131    return db;
132  }
133 
134  /** db + properties + views + records count. */
135  async get(databaseId: string, userId: string) {
136    const db = await this.getDbChecked(databaseId, userId, "VIEW");
137    const [properties, views, recordsCount, relations, formulas, rollups] = await Promise.all([
138      this.prisma.databaseProperty.findMany({ where: { databaseId, deletedAt: null }, orderBy: { position: "asc" } }),
139      this.prisma.databaseView.findMany({ where: { databaseId }, orderBy: { position: "asc" } }),
140      this.prisma.databaseRecord.count({ where: { databaseId, archivedAt: null } }),
141      this.prisma.relation.findMany({ where: { databaseId } }),
142      this.prisma.formula.findMany({ where: { databaseId } }),
143      this.prisma.rollup.findMany({ where: { databaseId } })
144    ]);
145    // LEGACY rows may still carry id-less select options (AI-generated before
146    // the write-boundary normalization). Normalize on read so the contract
147    // holds without a data migration; values that stored the option NAME
148    // resolve because `optionIdForName(name)` is deterministic.
149    const normalized = properties.map((p) =>
150      p.type === "SELECT" || p.type === "MULTI_SELECT" || p.type === "STATUS"
151        ? { ...p, config: normalizeSelectOptions(p.config) as object }
152        : p
153    );
154    return { database: db, properties: normalized, views, recordsCount, relations, formulas, rollups };
155  }
156 
157  async update(databaseId: string, userId: string, input: UpdateDatabaseInput, req: any) {
158    const db = await this.getDbChecked(databaseId, userId, "EDIT");
159    const updated = await this.prisma.database.update({
160      where: { id: databaseId },
161      data: {
162        ...(input.title !== undefined ? { title: input.title } : {}),
163        ...(input.icon !== undefined ? { icon: input.icon } : {}),
164        ...(input.description !== undefined ? { description: input.description } : {}),
165        schemaVersion: { increment: 1 }
166      },
167      select: DB_SELECT
168    });
169    // keep the page title in sync when the database has a page
170    if (input.title !== undefined && db.pageId) {
171      await this.prisma.page.updateMany({ where: { id: db.pageId }, data: { title: input.title } });
172    }
173    await this.audit.audit(req, req?.auth, {
174      action: "database.updated",
175      resourceType: "database",
176      resourceId: databaseId,
177      workspaceId: db.workspaceId
178    });
179    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, fields: Object.keys(input) });
180    return updated;
181  }
182 
183  /** tx: views/props/records/relations/rollups + the database page. */
184  async remove(databaseId: string, userId: string, req: any) {
185    const db = await this.getDbChecked(databaseId, userId, "FULL_ACCESS");
186    await this.prisma.$transaction(async (tx) => {
187      const records = await tx.databaseRecord.findMany({ where: { databaseId }, select: { id: true, pageId: true } });
188      const pageIds = records.map((r) => r.pageId).filter((p): p is string => Boolean(p));
189      if (pageIds.length > 0) {
190        await tx.databaseRecord.deleteMany({ where: { databaseId } });
191        await tx.block.deleteMany({ where: { pageId: { in: pageIds } } });
192        await tx.page.deleteMany({ where: { id: { in: pageIds } } });
193      }
194      await tx.database.delete({ where: { id: databaseId } });
195      // the database block on the host page (if any)
196      if (db.pageId) {
197        await tx.block.deleteMany({
198          where: { pageId: db.pageId, type: "DATABASE", content: { path: ["databaseId"], equals: databaseId } }
199        });
200      }
201    });
202    await this.audit.audit(req, req?.auth, {
203      action: "database.deleted",
204      resourceType: "database",
205      resourceId: databaseId,
206      workspaceId: db.workspaceId
207    });
208    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, deleted: true });
209  }
210 
211  // ---------- properties ----------
212 
213  async listProperties(databaseId: string, userId: string) {
214    // TASK-0135: the column metadata is cached for 60 s + busted on
215    // `database.updated`. The VIEW access check runs live (cheap, correct);
216    // only the findMany is cached.
217    await this.getDbChecked(databaseId, userId, "VIEW");
218    const { data } = await this.cache.getMetadata(
219      databaseId,
220      "properties",
221      async () => {
222        const properties = await this.prisma.databaseProperty.findMany({
223          where: { databaseId, deletedAt: null },
224          orderBy: { position: "asc" }
225        });
226        // Same legacy-id normalization as `get()` (id-less select options):
227        // the cached entry stores the normalized shape (the cache is busted
228        // on `database.updated`, and writes now persist normalized ids).
229        const normalized = properties.map((p) =>
230          p.type === "SELECT" || p.type === "MULTI_SELECT" || p.type === "STATUS"
231            ? { ...p, config: normalizeSelectOptions(p.config) as object }
232            : p
233        );
234        return { data: normalized };
235      },
236      z.object({ data: z.array(PROPERTY_CACHE) })
237    );
238    return data as { data: Prisma.DatabasePropertyGetPayload<{}>[] };
239  }
240 
241  async createProperty(databaseId: string, userId: string, input: CreatePropertyInput, req: any) {
242    const db = await this.getDbChecked(databaseId, userId, "EDIT");
243    this.validatePropertyForType(input.type, input.config, input.name);
244    // Assign deterministic option ids (the docs contract) so stored
245    // `optionId` values always resolve — the AI path emits bare
246    // `{ name, color }` options, which previously broke every id-based
247    // reader (web chips/preselect, board grouping, filters).
248    const config = normalizeSelectOptions(input.config) as Record<string, unknown>;
249    if (input.type === "TITLE") {
250      const existing = await this.prisma.databaseProperty.findFirst({
251        where: { databaseId, isTitle: true, deletedAt: null }
252      });
253      if (existing) throw ApiError.validation("Database already has a title property.", [{ field: "type", issue: "title_exists" }]);
254    }
255    const last = await this.prisma.databaseProperty.findFirst({
256      where: { databaseId, deletedAt: null },
257      orderBy: { position: "desc" },
258      select: { position: true }
259    });
260    let prop;
261    try {
262      prop = await this.prisma.databaseProperty.create({
263        data: {
264          databaseId,
265          name: input.name,
266          type: input.type,
267          config: config as object,
268          required: input.required ?? false,
269          isVisible: input.isVisible ?? true,
270          default: (input.default === undefined ? undefined : input.default as object) as object | undefined,
271          position: between(last?.position)
272        }
273      });
274    } catch {
275      throw new ApiError("CONFLICT", 409, "Property name already exists in this database.", [
276        { field: "name", issue: "already_exists" }
277      ]);
278    }
279    await this.audit.audit(req, req?.auth, {
280      action: "database.property_created",
281      resourceType: "database_property",
282      resourceId: prop.id,
283      workspaceId: db.workspaceId,
284      metadata: { databaseId, name: input.name, type: input.type }
285    });
286    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, propertyId: prop.id });
287    return prop;
288  }
289 
290  private validatePropertyForType(type: string, config: Record<string, unknown>, name: string): void {
291    if (type === "SELECT" || type === "MULTI_SELECT" || type === "STATUS") {
292      const options = config.options;
293      if (!Array.isArray(options) || options.length === 0) {
294        throw ApiError.validation("select/status property requires config.options.", [
295          { field: "config.options", issue: "required" }
296        ]);
297      }
298    }
299    if (type === "RELATION" && typeof config.relationId !== "string") {
300      throw ApiError.validation("relation property requires config.relationId (create a relation first).", [
301        { field: "config.relationId", issue: "required" }
302      ]);
303    }
304    if (type === "ROLLUP") {
305      if (typeof config.relationId !== "string" || typeof config.aggregate !== "string") {
306        throw ApiError.validation("rollup property requires config.relationId and config.aggregate.", [
307          { field: "config", issue: "relationId+aggregate required" }
308        ]);
309      }
310    }
311    if (type === "FORMULA" && typeof config.relationId === "string") {
312      // formula references are stored on the Formula row; fine.
313    }
314    void name;
315  }
316 
317  async updateProperty(databaseId: string, propertyId: string, userId: string, input: UpdatePropertyInput, req: any) {
318    const db = await this.getDbChecked(databaseId, userId, "EDIT");
319    const prop = await this.prisma.databaseProperty.findFirst({ where: { id: propertyId, databaseId, deletedAt: null } });
320    if (!prop) throw ApiError.notFound("Property not found.");
321    if (input.config) this.validatePropertyForType(prop.type, input.config, input.name ?? prop.name);
322    // A partial options patch keeps the ids of options already present (so
323    // existing record values stay resolvable) and assigns ids to any new
324    // option that arrives without one.
325    const existingOptions = ((prop.config as Record<string, unknown> | null)?.options) as unknown[] | undefined;
326    const config =
327      input.config !== undefined ? normalizeSelectOptions(input.config, existingOptions) : (prop.config as object);
328    const updated = await this.prisma.databaseProperty.update({
329      where: { id: propertyId },
330      data: {
331        ...(input.name !== undefined ? { name: input.name } : {}),
332        ...(input.config !== undefined ? { config: config as object } : {}),
333        ...(input.required !== undefined ? { required: input.required } : {}),
334        ...(input.isVisible !== undefined ? { isVisible: input.isVisible } : {}),
335        ...(input.default !== undefined ? { default: input.default as object } : {})
336      }
337    });
338    await this.audit.audit(req, req?.auth, {
339      action: "database.property_updated",
340      resourceType: "database_property",
341      resourceId: propertyId,
342      workspaceId: db.workspaceId,
343      metadata: { databaseId }
344    });
345    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, propertyId });
346    return updated;
347  }
348 
349  /** Soft-delete a property, guarded against in-use references. */
350  async deleteProperty(databaseId: string, propertyId: string, userId: string, req: any) {
351    const db = await this.getDbChecked(databaseId, userId, "EDIT");
352    const prop = await this.prisma.databaseProperty.findFirst({ where: { id: propertyId, databaseId, deletedAt: null } });
353    if (!prop) throw ApiError.notFound("Property not found.");
354    if (prop.isTitle) {
355      throw ApiError.validation("The title property cannot be deleted.", [{ field: "property", issue: "title_protected" }]);
356    }
357    // guard: referenced by formulas/rollups/relations?
358    const [formulaCount, rollupCount, relationCount] = await Promise.all([
359      this.prisma.formula.count({ where: { databaseId, dependencies: { has: propertyId } } }),
360      this.prisma.rollup.count({ where: { databaseId, targetPropertyId: propertyId } }),
361      this.prisma.relation.count({ where: { OR: [{ propertyId }, { reversePropertyId: propertyId }] } })
362    ]);
363    if (formulaCount + rollupCount + relationCount > 0) {
364      throw new ApiError("UNPROCESSABLE_ENTITY", 422, "Property is used by formulas, rollups or relations — remove those first.", [
365        { issue: "in_use", formulas: formulaCount, rollups: rollupCount, relations: relationCount }
366      ]);
367    }
368    await this.prisma.$transaction([
369      this.prisma.databaseProperty.update({ where: { id: propertyId }, data: { deletedAt: new Date() } }),
370      this.prisma.recordProperty.deleteMany({ where: { propertyId } })
371    ]);
372    await this.audit.audit(req, req?.auth, {
373      action: "database.property_deleted",
374      resourceType: "database_property",
375      resourceId: propertyId,
376      workspaceId: db.workspaceId,
377      metadata: { databaseId, name: prop.name }
378    });
379    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, propertyId, deleted: true });
380  }
381 
382  async reorderProperty(databaseId: string, propertyId: string, userId: string, position: { after?: string; before?: string; index?: number }, req: any) {
383    await this.getDbChecked(databaseId, userId, "EDIT");
384    const prop = await this.prisma.databaseProperty.findFirst({ where: { id: propertyId, databaseId, deletedAt: null } });
385    if (!prop) throw ApiError.notFound("Property not found.");
386    let newPos: string | undefined;
387    if (position.after || position.before) {
388      let before: string | undefined;
389      let after: string | undefined;
390      if (position.before) {
391        const b = await this.prisma.databaseProperty.findUnique({ where: { id: position.before }, select: { position: true } });
392        if (!b) throw ApiError.notFound("before property not found.");
393        before = b.position;
394      }
395      if (position.after) {
396        const a = await this.prisma.databaseProperty.findUnique({ where: { id: position.after }, select: { position: true } });
397        if (!a) throw ApiError.notFound("after property not found.");
398        after = a.position;
399      }
400      newPos = between(after, before);
401    } else if (position.index !== undefined) {
402      const props = await this.prisma.databaseProperty.findMany({
403        where: { databaseId, deletedAt: null },
404        orderBy: { position: "asc" },
405        select: { id: true, position: true }
406      });
407      const at = Math.max(0, Math.min(position.index, props.length - 1));
408      const sorted = [...props].sort((a, b) => a.position.localeCompare(b.position));
409      const prev = at > 0 ? sorted[at - 1]?.position : undefined;
410      const next = sorted[at + 1]?.position;
411      newPos = between(prev, next);
412    }
413    if (!newPos) throw ApiError.validation("Provide after/before property ids or an index.", [{ field: "position", issue: "required" }]);
414    const updated = await this.prisma.databaseProperty.update({ where: { id: propertyId }, data: { position: newPos } });
415    const db2 = await this.getDb(databaseId);
416    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db2.workspaceId, databaseId, propertyId, reordered: true });
417    return updated;
418  }
419 
420  // ---------- views ----------
421 
422  async listViews(databaseId: string, userId: string) {
423    // TASK-0135: view settings cached for 60 s + busted on `database.updated`
424    // (a view change carries `viewId`). Access check stays live.
425    await this.getDbChecked(databaseId, userId, "VIEW");
426    const { data } = await this.cache.getMetadata(
427      databaseId,
428      "views",
429      async () => {
430        const views = await this.prisma.databaseView.findMany({ where: { databaseId }, orderBy: { position: "asc" } });
431        return { data: views };
432      },
433      z.object({ data: z.array(VIEW_CACHE) })
434    );
435    return data as { data: Prisma.DatabaseViewGetPayload<{}>[] };
436  }
437 
438  async createView(databaseId: string, userId: string, input: CreateViewInput, req: any) {
439    const db = await this.getDbChecked(databaseId, userId, "EDIT");
440    const last = await this.prisma.databaseView.findFirst({
441      where: { databaseId },
442      orderBy: { position: "desc" },
443      select: { position: true }
444    });
445    const view = await this.prisma.databaseView.create({
446      data: {
447        databaseId,
448        name: input.name,
449        type: input.type,
450        config: input.config as object,
451        position: between(last?.position)
452      }
453    });
454    await this.audit.audit(req, req?.auth, {
455      action: "database.view_created",
456      resourceType: "database_view",
457      resourceId: view.id,
458      workspaceId: db.workspaceId,
459      metadata: { databaseId, name: input.name, type: input.type }
460    });
461    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, viewId: view.id });
462    return view;
463  }
464 
465  async updateView(databaseId: string, viewId: string, userId: string, input: UpdateViewInput, req: any) {
466    const db = await this.getDbChecked(databaseId, userId, "EDIT");
467    const view = await this.prisma.databaseView.findFirst({ where: { id: viewId, databaseId } });
468    if (!view) throw ApiError.notFound("View not found.");
469    const merged = { ...(view.config as object), ...(input.config ?? {}) } as object;
470    const updated = await this.prisma.databaseView.update({
471      where: { id: viewId },
472      data: {
473        ...(input.name !== undefined ? { name: input.name } : {}),
474        config: merged,
475        ...(input.filter !== undefined ? { filter: input.filter as object } : {}),
476        ...(input.sort !== undefined ? { sort: input.sort as object } : {}),
477        ...(input.group !== undefined ? { group: input.group as object } : {}),
478        // T-166: rows-per-page is a top-level column (the §6 query reads it as
479        // the listing default) — written only when explicitly provided.
480        ...(input.pageSize !== undefined ? { pageSize: input.pageSize } : {})
481      }
482    });
483    await this.audit.audit(req, req?.auth, {
484      action: "database.view_updated",
485      resourceType: "database_view",
486      resourceId: viewId,
487      workspaceId: db.workspaceId,
488      metadata: { databaseId }
489    });
490    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, viewId });
491    return updated;
492  }
493 
494  async deleteView(databaseId: string, viewId: string, userId: string, req: any) {
495    const db = await this.getDbChecked(databaseId, userId, "EDIT");
496    const view = await this.prisma.databaseView.findFirst({ where: { id: viewId, databaseId } });
497    if (!view) throw ApiError.notFound("View not found.");
498    await this.prisma.databaseView.delete({ where: { id: viewId } });
499    await this.audit.audit(req, req?.auth, {
500      action: "database.view_deleted",
501      resourceType: "database_view",
502      resourceId: viewId,
503      workspaceId: db.workspaceId,
504      metadata: { databaseId, name: view.name }
505    });
506    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, viewId, deleted: true });
507  }
508 
509  async reorderView(databaseId: string, viewId: string, userId: string, position: { after?: string; before?: string }, req: any) {
510    const db = await this.getDbChecked(databaseId, userId, "EDIT");
511    const view = await this.prisma.databaseView.findFirst({ where: { id: viewId, databaseId } });
512    if (!view) throw ApiError.notFound("View not found.");
513    let before: string | undefined;
514    let after: string | undefined;
515    if (position.before) {
516      const b = await this.prisma.databaseView.findUnique({ where: { id: position.before }, select: { position: true } });
517      if (!b) throw ApiError.notFound("before view not found.");
518      before = b.position;
519    }
520    if (position.after) {
521      const a = await this.prisma.databaseView.findUnique({ where: { id: position.after }, select: { position: true } });
522      if (!a) throw ApiError.notFound("after view not found.");
523      after = a.position;
524    }
525    const updated = await this.prisma.databaseView.update({
526      where: { id: viewId },
527      data: { position: between(after, before) }
528    });
529    await this.audit.audit(req, req?.auth, {
530      action: "database.view_reordered",
531      resourceType: "database_view",
532      resourceId: viewId,
533      workspaceId: db.workspaceId,
534      metadata: { databaseId, position: updated.position }
535    });
536    // T-166: reorder must bust the 60 s `listViews` metadata cache (and drive
537    // the client's invalidation) — the same `DATABASE_UPDATED` the sibling
538    // view ops publish. Without it the web "set as default" reorder was a
539    // silent no-op (stale position served for up to a minute).
540    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, viewId });
541    return updated;
542  }
543 
544  // ---------- relations ----------
545 
546  async listRelations(databaseId: string, userId: string) {
547    await this.getDbChecked(databaseId, userId, "VIEW");
548    const relations = await this.prisma.relation.findMany({ where: { databaseId } });
549    return { data: relations };
550  }
551 
552  async createRelation(databaseId: string, userId: string, input: CreateRelationInput, req: any) {
553    const db = await this.getDbChecked(databaseId, userId, "EDIT");
554    const target = await this.getDb(input.targetDatabaseId);
555    if (target.workspaceId !== db.workspaceId) {
556      throw ApiError.validation("Both databases must be in the same workspace.", [{ field: "targetDatabaseId", issue: "workspace_mismatch" }]);
557    }
558 
559    const created = await this.prisma.$transaction(async (tx) => {
560      const lastSrc = await tx.databaseProperty.findFirst({ where: { databaseId, deletedAt: null }, orderBy: { position: "desc" }, select: { position: true } });
561      const prop = await tx.databaseProperty.create({
562        data: {
563          databaseId,
564          name: input.name,
565          type: "RELATION",
566          config: { targetDatabaseId: input.targetDatabaseId } as object,
567          position: between(lastSrc?.position)
568        }
569      });
570      let reverseProp;
571      let reverseName: string | undefined;
572      if (input.twoWay) {
573        reverseName = input.reverseName ?? input.name;
574        const lastTgt = await tx.databaseProperty.findFirst({ where: { databaseId: input.targetDatabaseId, deletedAt: null }, orderBy: { position: "desc" }, select: { position: true } });
575        reverseProp = await tx.databaseProperty.create({
576          data: {
577            databaseId: input.targetDatabaseId,
578            name: reverseName,
579            type: "RELATION",
580            config: { targetDatabaseId: databaseId, reverse: true } as object,
581            position: between(lastTgt?.position)
582          }
583        });
584      }
585      const relation = await tx.relation.create({
586        data: {
587          databaseId,
588          targetDatabaseId: input.targetDatabaseId,
589          name: input.name,
590          targetName: input.twoWay ? reverseName : undefined,
591          propertyId: prop.id,
592          reversePropertyId: reverseProp?.id,
593          cardinality: input.cardinality,
594          bidirectional: input.twoWay
595        }
596      });
597      return { relation, prop, reverseProp };
598    });
599 
600    await this.audit.audit(req, req?.auth, {
601      action: "database.relation_created",
602      resourceType: "relation",
603      resourceId: created.relation.id,
604      workspaceId: db.workspaceId,
605      metadata: { databaseId, targetDatabaseId: input.targetDatabaseId, name: input.name, twoWay: input.twoWay }
606    });
607    await this.events.publish(EVENTS.DATABASE_UPDATED, {
608      workspaceId: db.workspaceId,
609      databaseId,
610      relationId: created.relation.id
611    });
612    return created.relation;
613  }
614 
615  async updateRelation(databaseId: string, relationId: string, userId: string, patch: { name?: string; targetName?: string }, req: any) {
616    await this.getDbChecked(databaseId, userId, "EDIT");
617    const rel = await this.prisma.relation.findFirst({ where: { id: relationId, databaseId } });
618    if (!rel) throw ApiError.notFound("Relation not found.");
619    const updated = await this.prisma.relation.update({
620      where: { id: relationId },
621      data: {
622        ...(patch.name !== undefined ? { name: patch.name } : {}),
623        ...(patch.targetName !== undefined ? { targetName: patch.targetName } : {})
624      }
625    });
626    // rename the properties too
627    if (patch.name !== undefined && rel.propertyId) {
628      await this.prisma.databaseProperty.updateMany({ where: { id: rel.propertyId }, data: { name: patch.name } });
629    }
630    if (patch.targetName !== undefined && rel.reversePropertyId) {
631      await this.prisma.databaseProperty.updateMany({ where: { id: rel.reversePropertyId }, data: { name: patch.targetName } });
632    }
633    await this.audit.audit(req, req?.auth, {
634      action: "database.relation_updated",
635      resourceType: "relation",
636      resourceId: relationId,
637      workspaceId: (await this.getDb(databaseId)).workspaceId
638    });
639    return updated;
640  }
641 
642  /** Drop both relation properties + links + the relation row. */
643  async deleteRelation(databaseId: string, relationId: string, userId: string, req: any) {
644    const db = await this.getDbChecked(databaseId, userId, "EDIT");
645    const rel = await this.prisma.relation.findFirst({ where: { id: relationId, databaseId } });
646    if (!rel) throw ApiError.notFound("Relation not found.");
647    await this.prisma.$transaction(async (tx) => {
648      await tx.relationLink.deleteMany({ where: { databaseId, record: { properties: { some: { propertyId: rel.propertyId ?? undefined } } } } });
649      await tx.relationLink.deleteMany({ where: { targetDatabaseId: rel.targetDatabaseId, record: { databaseId: rel.targetDatabaseId } } });
650      if (rel.propertyId) await tx.databaseProperty.update({ where: { id: rel.propertyId }, data: { deletedAt: new Date() } });
651      if (rel.reversePropertyId) await tx.databaseProperty.update({ where: { id: rel.reversePropertyId }, data: { deletedAt: new Date() } });
652      await tx.recordProperty.deleteMany({ where: { propertyId: { in: [rel.propertyId, rel.reversePropertyId].filter(Boolean) as string[] } } });
653      await tx.relation.delete({ where: { id: relationId } });
654    });
655    await this.audit.audit(req, req?.auth, {
656      action: "database.relation_deleted",
657      resourceType: "relation",
658      resourceId: relationId,
659      workspaceId: db.workspaceId,
660      metadata: { name: rel.name, targetDatabaseId: rel.targetDatabaseId }
661    });
662    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, relationId, deleted: true });
663  }
664 
665  // ---------- formulas ----------
666 
667  async listFormulas(databaseId: string, userId: string) {
668    await this.getDbChecked(databaseId, userId, "VIEW");
669    const formulas = await this.prisma.formula.findMany({ where: { databaseId } });
670    return { data: formulas };
671  }
672 
673  async createFormula(databaseId: string, userId: string, input: CreateFormulaInput, req: any) {
674    const db = await this.getDbChecked(databaseId, userId, "EDIT");
675    assertFormulaSafe(input.expression);
676    const deps = await this.propertyNamesToIds(databaseId, scanFormulaDependencies(input.expression));
677    const prop = await this.prisma.databaseProperty.create({
678      data: {
679        databaseId,
680        name: input.name,
681        type: "FORMULA",
682        config: {} as object,
683        position: "Z"
684      }
685    });
686    const formula = await this.prisma.formula.create({
687      data: {
688        databaseId,
689        propertyId: prop.id,
690        expression: input.expression,
691        // TASK-0124: an engine-validated AST (the AI formula generator) is
692        // stored verbatim; otherwise the historical placeholder.
693        ast: (input.ast ?? { raw: input.expression, safe: true }) as object,
694        returnType: input.returnType ?? "UNKNOWN",
695        dependencies: deps
696      }
697    });
698    await this.audit.audit(req, req?.auth, {
699      action: "database.formula_created",
700      resourceType: "formula",
701      resourceId: formula.id,
702      workspaceId: db.workspaceId,
703      metadata: { databaseId, name: input.name, dependencies: deps }
704    });
705    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, formulaId: formula.id });
706    return formula;
707  }
708 
709  private async propertyNamesToIds(databaseId: string, names: string[]): Promise<string[]> {
710    if (names.length === 0) return [];
711    const props = await this.prisma.databaseProperty.findMany({
712      where: { databaseId, deletedAt: null },
713      select: { id: true, name: true }
714    });
715    const byName = new Map(props.map((p) => [p.name.toLowerCase(), p.id]));
716    return names.map((n) => byName.get(n.toLowerCase())).filter((x): x is string => Boolean(x));
717  }
718 
719  /**
720   * Validate with packages/database-engine when installed; otherwise a
721   * lightweight syntax check (balanced parens + safe construct scan) and a
722   * clear "engine not installed" note.
723   */
724  async validateFormula(input: ValidateFormulaInput): Promise<{ valid: boolean; error?: string; engine: string }> {
725    assertFormulaSafe(input.expression);
726    const fallback = "built-in (engine not installed — run pnpm install)";
727    const mod = await loadFormulaEngine();
728    if (mod) {
729      try {
730        // Save-time validation = parse + closed function table (the
731        // syntax-level guarantees). Name resolution (unknown property →
732        // TYPE) is checked at EVALUATION time against a real record's
733        // values, not here — a formula may reference a property that only
734        // exists once the caller's sample record is known.
735        mod.parseFormula(input.expression);
736        return { valid: true, engine: "packages/database-engine" };
737      } catch (err) {
738        return { valid: false, error: (err as Error).message, engine: "packages/database-engine" };
739      }
740    }
741    if (!balanced(input.expression)) {
742      return { valid: false, error: "unbalanced parentheses", engine: fallback };
743    }
744    return { valid: true, engine: fallback };
745  }
746 
747  /**
748   * TASK-0134 — evaluate a stored formula against a record with the safe
749   * engine (100k-op + 50 ms wall-clock guards). VIEW-gated (reuses
750   * `query.getRecord`). On a budget breach the engine throws
751   * `FormulaError("FORMULA_TIMEOUT")` → canonical 422 `FORMULA_TIMEOUT`
752   * (docs/api/authentication.md §9.1); other engine errors (PARSE/TYPE/
753   * RUNTIME/UNKNOWN_FUNCTION) → 400. The engine is loaded lazily; if the
754   * package is absent the endpoint answers `evaluated: null` + a note
755   * (backward compatible with the "engine not installed" fallback).
756   */
757  async evaluateRecordFormula(
758    recordId: string,
759    formulaId: string,
760    actor: string
761  ): Promise<Record<string, unknown>> {
762    // VIEW-gated record fetch (also yields databaseId + the id-keyed values).
763    const record = await this.query.getRecord(recordId, actor);
764    const { databaseId, properties: idProps } = record as {
765      databaseId: string;
766      properties: Record<string, unknown>;
767    };
768    const formula = (await this.listFormulas(databaseId, actor)) as unknown as { data: { id: string; expression: string }[] };
769    const f = formula.data.find((x) => x.id === formulaId);
770    if (!f) throw ApiError.notFound("Formula not found.");
771 
772    const mod = await loadFormulaEngine();
773    if (!mod) {
774      return { formulaId, expression: f.expression, evaluated: null, value: null, engine: "not installed (run pnpm install)" };
775    }
776 
777    // Build the name-keyed evaluation context: the record's values are
778    // id-keyed in the DTO; the engine resolves `prop("Name")` by name. The
779    // title property is stored as a `{text, marks}` object — unwrap it to
780    // its text so `prop("Title")` yields a string, not an object.
781    const props = await this.prisma.databaseProperty.findMany({
782      where: { databaseId, deletedAt: null },
783      select: { id: true, name: true, type: true }
784    });
785    const ctxProps: Record<string, unknown> = {};
786    const ctxTypes: Record<string, string> = {};
787    for (const p of props) {
788      const raw = idProps[p.id];
789      ctxProps[p.name] =
790        raw && typeof raw === "object" && "text" in raw ? (raw as { text: string }).text : raw == null ? null : raw;
791      ctxTypes[p.name] = p.type;
792    }
793 
794    let ast: unknown;
795    try {
796      ast = mod.parseFormula(f.expression);
797    } catch (err) {
798      // A stored formula that fails to parse (corrupt / hand-written) →
799      // 400, never a 500.
800      throw new ApiError("VALIDATION_ERROR", 400, `Formula does not parse: ${(err as Error).message}`, [
801        { issue: "formula_parse", resource: "formula", received: formulaId }
802      ]);
803    }
804 
805    try {
806      const { value, steps } = mod.evaluateFormula(ast, {
807        now: Date.now(),
808        properties: ctxProps,
809        propertyTypes: ctxTypes
810      });
811      return { formulaId, expression: f.expression, evaluated: true, value: value.v, steps, engine: "packages/database-engine" };
812    } catch (err) {
813      if (err instanceof mod.FormulaError) {
814        if (err.code === "FORMULA_TIMEOUT") {
815          // The canonical 422 — a syntactically valid formula that exceeds
816          // the 100k-op / 50 ms budget. Distinct from the save-time codes.
817          throw new ApiError("FORMULA_TIMEOUT", 422, "Formula exceeded the evaluation budget.", [
818            { reason: "formula_timeout", limit: "100k ops / 50 ms", resource: "formula", received: formulaId }
819          ]);
820        }
821        throw new ApiError("VALIDATION_ERROR", 400, `Formula evaluation failed: ${err.message}`, [
822          { issue: err.code, resource: "formula", received: formulaId }
823        ]);
824      }
825      throw err;
826    }
827  }
828}
829 
830/**
831 * The formula-engine surface the API actually uses (TASK-0134 widened this —
832 * it was a `validateFormula(expr): {valid, error?}` shape that matched a
833 * non-existent `engine.ts`, so the loader silently fell back to the built-in
834 * check and the endpoint NEVER evaluated. It now points at the real package
835 * entry and exposes the AST evaluate/parse + FormulaError the record-eval
836 * path needs to surface `FORMULA_TIMEOUT`).
837 */
838interface FormulaErrorLike {
839  readonly code: string;
840  message: string;
841}
842interface FormulaEngineModule {
843  parseFormula(src: string): unknown;
844  evaluateFormula(
845    ast: unknown,
846    ctx: { now: number; properties: Record<string, unknown>; propertyTypes?: Record<string, string> }
847  ): { value: { v: unknown; t: string }; steps: number };
848  FormulaError: new (code: string, message?: string) => FormulaErrorLike;
849}
850 
851/**
852 * Try to load the safe formula engine from packages/database-engine (dev
853 * source or built dist). Returns undefined when the package is absent —
854 * the endpoint then answers with the built-in check + an install note.
855 * Robust under vitest / any subdirectory cwd (walks up to the repo root),
856 * mirroring the AI service's loader.
857 */
858async function loadFormulaEngine(): Promise<FormulaEngineModule | undefined> {
859  const { existsSync } = await import("node:fs");
860  const { join, resolve } = await import("node:path");
861  const { pathToFileURL } = await import("node:url");
862  const rel = "packages/database-engine/src/formula/index.ts";
863  const distRel = "packages/database-engine/dist/formula/index.js";
864  const candidates: string[] = [join(process.cwd(), rel), join(process.cwd(), distRel)];
865  // src/modules/databases and dist/modules/databases are both 5 levels under
866  // the repo root. `__dirname` is CJS-only (undefined in pure ESM) — guarded.
867  if (typeof __dirname !== "undefined") {
868    candidates.push(
869      resolve(__dirname, "..", "..", "..", "..", "..", rel),
870      resolve(__dirname, "..", "..", "..", "..", "..", distRel)
871    );
872  }
873  // Walk up from cwd — robust under vitest (cwd = a tests/ subdir) or a tool
874  // launched from any subdirectory.
875  let dir = process.cwd();
876  for (let i = 0; i < 8; i++) {
877    candidates.push(join(dir, rel), join(dir, distRel));
878    const parent = resolve(dir, "..");
879    if (parent === dir) break;
880    dir = parent;
881  }
882  for (const candidate of new Set(candidates)) {
883    if (existsSync(candidate)) {
884      try {
885        const mod = (await import(/* webpackIgnore: true */ pathToFileURL(candidate).href)) as FormulaEngineModule;
886        if (typeof mod.parseFormula === "function" && typeof mod.evaluateFormula === "function") return mod;
887      } catch {
888        // broken source — keep trying
889      }
890    }
891  }
892  return undefined;
893}
894 
895function balanced(s: string): boolean {
896  let depth = 0;
897  for (const ch of s) {
898    if (ch === "(") depth++;
899    else if (ch === ")") depth--;
900    if (depth < 0) return false;
901  }
902  return depth === 0;
903}
904