query.service.ts

query.service.tsapps/api/src/modules/databases/query.service.tsraw ← databases97,776 B← Back to the app
1import { Inject, Injectable } from "@nestjs/common";
2import type { Request } from "express";
3import { Prisma, PrismaClient } from "@prisma/client";
4import { z } from "zod";
5import { optionIdForName, normalizeSelectOptions } from "@factory/shared";
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 { assertCan } from "../../lib/authorize.js";
12import { buildRecordFilter, type PropertyFilter } from "../../lib/filter-builder.js";
13import { MAX_PRECISION, between, compare, rebalancePositions } from "../../lib/positions.js";
14import { parsePagination } from "../../lib/pagination.js";
15import { parseCsv } from "../../lib/csv-parser.js";
16import { EVENTS, MAX_CSV_IMPORT_ROWS, MAX_EXPORT_ROWS } from "../../constants.js";
17import { NotificationsService } from "../notifications/notifications.service.js";
18import type { BulkRecordsInput, CreateRecordInput, CsvImportInput, DatabaseQueryInput, ListRecordsInput, UpdateRecordInput } from "./dto.js";
19import { PROPERTY_CACHE } from "./property-cache-schema.js";
20 
21/** Read-only computed property types — writes are rejected with 422. */
22const READ_ONLY_TYPES = new Set([
23  "CREATED_TIME", "CREATED_BY", "LAST_EDITED_TIME", "LAST_EDITED_BY",
24  "ROLLUP", "FORMULA", "BUTTON", "UNIQUE_ID"
25]);
26 
27/**
28 * Docs §5.3 condition ops → FILTER_OPS (lib/filter-builder.ts) aliases.
29 * Conflict recorded per AGENTS.md rule 29: the docs use `is` / `equals` /
30 * `greater_than` / `does_not_contain` while the filter builder accepts
31 * `eq` / `gte` / `ncontains`. We normalize at the service boundary; the lib
32 * is left untouched. `contains_any` / `contains_all` have no SQL
33 * fragment equivalent and are evaluated in JS only.
34 */
35const OP_ALIASES: Record<string, string> = {
36  is: "eq",
37  is_not: "neq",
38  equals: "eq",
39  not_equals: "neq",
40  greater_than: "gt",
41  greater_or_equal: "gte",
42  less_than: "lt",
43  less_or_equal: "lte",
44  does_not_contain: "ncontains",
45  starts_with: "startsWith",
46  ends_with: "endsWith",
47  is_empty: "isEmpty",
48  is_not_empty: "isNotEmpty",
49  between: "between",
50  is_this_week: "isThisWeek",
51  is_this_month: "isThisMonth",
52  within_next_days: "withinNextDays",
53  is_past: "isPast",
54  is_future: "isFuture",
55  is_current_user: "isCurrentUser",
56  on_or_after: "gte",
57  on_or_before: "lte"
58};
59// Ops whose filter value is an envelope that `simplifyValue` would mangle
60// before the validator sees it. `between` sends {start, end} but
61// simplifyValue unwraps {start} → a bare start string, silently dropping
62// `end` (so a bad `end` would slip past validation). These keep the RAW
63// envelope so buildValueCondition can validate the full value. All ops are
64// still validated — none skip it — and the JS pass (applyDisjunction) is the
65// exact evaluator; SQL is only emitted for CHECKBOX eq/neq (RAW_SCALAR_TYPES).
66const RAW_ENVELOPE_OPS = new Set(["between"]);
67 
68interface PropertyMeta {
69  id: string;
70  name: string;
71  type: string;
72  isTitle: boolean;
73  /** Stored `config` (Prisma JsonValue) — select/status options carry
74   *  `{ id, name, color }`, used to canonicalize legacy name-based
75   *  `optionId` values at the read and write boundaries (no-op for ids
76   *  that already resolve). */
77  config?: unknown;
78}
79 
80/** One failed CSV import row (T-165). `row` is the 1-based CSV line
81 *  (header = line 1, so the first data row is line 2). */
82interface CsvImportRowError {
83  row: number;
84  column: string;
85  error: string;
86}
87 
88interface RowT {
89  id: string;
90  workspaceId: string;
91  databaseId: string;
92  position: string;
93  pageId: string | null;
94  icon: string | null;
95  coverUrl: string | null;
96  archivedAt: Date | null;
97  version: number;
98  createdTime: Date;
99  lastEditedTime: Date;
100  createdAt: Date;
101  lastEditedBy: string | null;
102  createdBy: string | null;
103  page: { title: string; icon: string | null } | null;
104  properties: { propertyId: string; value: unknown }[];
105}
106 
107interface SortClause {
108  propertyId: string;
109  direction: "asc" | "desc";
110}
111 
112interface GroupClause {
113  propertyId: string;
114}
115 
116interface FilterLeaf {
117  propertyId: string;
118  op: string;
119  value?: unknown;
120}
121 
122/**
123 * A normalized §5.3 filter node — either a leaf condition
124 * (`{ propertyId, op, value? }`) or a branch
125 * (`{ op: "and" | "or" | "not", conditions }`). Both producers
126 * (`parseFilterTree` for the query param, `asFilters` for stored view
127 * filters) return ONE root tree, so `filters` below is a single
128 * `FilterNode | null` (the `FilterNode[]` array annotation was a
129 * leftover of an earlier flat-list design).
130 */
131interface FilterNode {
132  propertyId?: string;
133  op?: string;
134  value?: unknown;
135  conditions?: FilterNode[];
136}
137 
138/**
139 * Record query engine: filter → sort → paginate over DatabaseRecord rows,
140 * joining RecordProperty values by propertyId, and expanding relations.
141 * Also record CRUD (record = page, 1:1) + bulk ops + CSV export.
142 *
143 * Sorting/grouping are applied in JS after a bounded fetch (take ≤ 5000):
144 * property values live in one JSONB column (RecordProperty.value), so SQL
145 * cannot order by them without a per-type case ladder.
146 *
147 * Filter trees (docs/api/databases.md §5.3) compile as follows:
148 *  - The whole tree is always evaluated in JS over the bounded row set
149 *    (≤ 5000 rows) — `nodeMatches`/`leafMatches` are the exact evaluator
150 *    (title comes from the page relation; envelope values are unwrapped).
151 *  - `and` leaves get an ADDITIONAL SQL pre-filter via buildRecordFilter,
152 *    but only when Prisma can express it (raw-scalar value + exact eq/neq —
153 *    i.e. CHECKBOX; envelope-typed values live under the JSONB `value`
154 *    column and only whole-value equals is valid there). Pushing any other
155 *    JSONB operator (string_contains, gte, …) at the top level made Prisma
156 *    throw PrismaClientValidationError → 400 "Invalid database query." for
157 *    every filtered list (search-in-view, quick filters, saved view filters).
158 *
159 * Events: record.created / record.updated carry
160 * `{ recordId, workspaceId, databaseId, fields }` — the worker re-reads
161 * the row from Prisma for search indexing (no denormalized payload).
162 * Record deletion is hard (schema cascades); there is no `record.deleted`
163 * event in the worker catalog, so deletions emit no event — search docs
164 * are dropped by the periodic reconcile (recorded per rule 29).
165 */
166@Injectable()
167export class DatabaseQueryService {
168  constructor(
169    @Inject(PrismaService) private readonly prisma: PrismaService,
170    @Inject(AuditService) private readonly audit: AuditService,
171    @Inject(EventBus) private readonly events: EventBus,
172    @Inject(CacheService) private readonly cache: CacheService,
173    @Inject(NotificationsService) private readonly notifications: NotificationsService
174  ) {}
175 
176  // ---------- listing ----------
177 
178  /**
179   * List records: view defaults → filter → bounded fetch → JS sort →
180   * cursor pagination (or grouped response when `group` is set).
181   */
182  async list(databaseId: string, userId: string, query: ListRecordsInput) {
183    await this.getDbChecked(databaseId, userId, "VIEW");
184 
185    // View defaults (explicit params override — docs §6.1).
186    let filters: FilterNode | null = null;
187    let sort: SortClause[] | null = null;
188    let group: GroupClause[] | null = null;
189    let pageSize = parsePagination({ cursor: query.cursor, limit: query.limit }).limit;
190    if (query.viewId) {
191      const view = await this.prisma.databaseView.findFirst({ where: { id: query.viewId, databaseId } });
192      if (!view) throw ApiError.notFound("View not found.");
193      filters = this.asFilters(view.filter);
194      sort = this.asSort(view.sort);
195      group = this.asGroup(view.group);
196      if (view.pageSize && view.pageSize > 0) pageSize = Math.min(view.pageSize, 200);
197    }
198    if (query.filter) {
199      const parsed = this.parseFilterTree(query.filter);
200      if (parsed) filters = parsed;
201    }
202    if (query.sort) sort = this.parseSort(query.sort);
203    if (query.group) {
204      const g = this.parseGroup(query.group);
205      group = g.length > 0 ? g : null;
206    }
207    if (group && group.length === 0) group = null;
208 
209    const properties = await this.prisma.databaseProperty.findMany({
210      where: { databaseId, deletedAt: null },
211      orderBy: { position: "asc" },
212      select: { id: true, name: true, type: true, isTitle: true, config: true }
213    });
214    const byId = new Map(properties.map((p) => [p.id, p]));
215 
216    const where = this.compileWhere(filters, byId);
217    // TASK-0135 (replica routing): the record LISTING is a spec-named replica
218    // read (`db.read()`). Listing staleness (a tiny replica-lag window) is the
219    // documented, spec-acceptable trade-off; writes + the create path stay on
220    // the primary (the k6 `database-load` create → read-your-own-create path is
221    // a separate request, not a same-request write-then-read).
222    const rows = await this.prisma.read().databaseRecord.findMany({
223      where: { ...where, databaseId, archivedAt: null },
224      include: { page: { select: { title: true, icon: true } }, properties: true },
225      orderBy: { position: "asc" },
226      take: 5000
227    }) as RowT[];
228 
229    let rowsF = this.applyDisjunction(rows, filters, byId, userId);
230    rowsF = this.applySort(rowsF, sort, byId);
231    // Relation values live in `RelationLink` (not `DatabaseRecordProperty`);
232    // batch-load once for the full filtered set so `toDto` synthesizes
233    // `properties[relPropId] = { recordIds }`.
234    const relLinkRecords = await this.buildRelationLinkRecords(rowsF, byId);
235 
236    // Grouped listing: groups in first-seen (post-sort) order, rows
237    // pre-sorted within groups; no cross-group cursor (page within group).
238    if (group) {
239      const groupProp = group[0]!;
240      const groups: { key: { propertyId: string; value: unknown; label: string }; count: number; records: Record<string, unknown>[] }[] = [];
241      const index = new Map<string, (typeof groups)[number]>();
242      const groupMeta = byId.get(groupProp.propertyId);
243      for (const row of rowsF) {
244        // Group by the CANONICAL value (legacy name refs → deterministic ids)
245        // so the group key + the web's column value both resolve by id.
246        const value = this.canonicalOptionValue(
247          this.propertyValue(row, byId, groupProp.propertyId),
248          groupMeta?.type ?? "TEXT",
249          groupMeta?.config
250        );
251        const gid = `${groupProp.propertyId}::${this.groupKey(value)}`;
252        let g = index.get(gid);
253        if (!g) {
254          g = { key: { propertyId: groupProp.propertyId, value, label: this.groupLabel(groupProp.propertyId, value, byId) }, count: 0, records: [] };
255          index.set(gid, g);
256          groups.push(g);
257        }
258        g.records.push(this.toDto(row, byId, undefined, relLinkRecords));
259        g.count += 1;
260      }
261      return { data: { groups }, nextCursor: null };
262    }
263 
264    const offset = query.cursor ? this.cursorOffset(rowsF, query.cursor) : 0;
265    const slice = rowsF.slice(offset, offset + pageSize);
266    const more = offset + pageSize < rowsF.length;
267    const last = slice.length > 0 ? slice[slice.length - 1] : null;
268    const nextCursor = more && last ? this.makeCursor(last.id) : null;
269    const relations = query.include === "relations" ? await this.buildRelations(slice) : undefined;
270 
271    return {
272      data: slice.map((r) => this.toDto(r, byId, relations, relLinkRecords)),
273      nextCursor
274    };
275  }
276 
277  /**
278   * TASK-0124 (repair): docs §6 query DSL — `POST /databases/:id/query`.
279   * Same engine as `list()` (filter → bounded fetch → JS sort → cursor
280   * pagination) but takes JSON bodies and returns the §6 envelope the web
281   * `useDatabaseRecords` expects: flat `{ data: { records, totalCount,
282   * nextCursor } }` or `{ data: { groups, totalCount, nextCursor } }` when
283   * grouped. `viewId` seeds filter/sort/group/pageSize from the stored view;
284   * explicit body fields override. `select` limits the returned
285   * `properties` map to the given property ids (plus the title property).
286   * `aggregate` is accepted for contract completeness but returns no
287   * `aggregates` object (no client sends it — recorded per rule 29).
288   */
289  async query(
290    databaseId: string,
291    userId: string,
292    body: DatabaseQueryInput
293  ): Promise<Record<string, unknown>> {
294    await this.getDbChecked(databaseId, userId, "VIEW");
295 
296    let filters: FilterNode | null = null;
297    let sort: SortClause[] | null = null;
298    let group: GroupClause[] | null = null;
299    let pageSize = body.pageSize;
300    if (body.viewId) {
301      const view = await this.prisma.databaseView.findFirst({ where: { id: body.viewId, databaseId } });
302      if (!view) throw ApiError.notFound("View not found.");
303      filters = this.asFilters(view.filter);
304      sort = this.asSort(view.sort);
305      group = this.asGroup(view.group);
306      if (view.pageSize && view.pageSize > 0) pageSize = Math.min(view.pageSize, 200);
307    }
308    if (body.filter !== undefined && body.filter !== null) {
309      filters = this.normalizeTree(body.filter);
310    }
311    if (body.sort && body.sort.length > 0) {
312      sort = body.sort.map((s) => ({ propertyId: s.propertyId, direction: s.direction }));
313    }
314    if (body.group && body.group.length > 0) {
315      group = body.group.map((g) => ({ propertyId: g.propertyId }));
316    }
317    if (group && group.length === 0) group = null;
318 
319    const properties = await this.prisma.databaseProperty.findMany({
320      where: { databaseId, deletedAt: null },
321      orderBy: { position: "asc" },
322      select: { id: true, name: true, type: true, isTitle: true, config: true }
323    });
324    const byId = new Map(properties.map((p) => [p.id, p]));
325 
326    // `select` (docs §6): restrict the DTO's properties map; the title
327    // property is always kept (row identity) even when not selected.
328    const selectSet = body.select && body.select.length > 0 ? new Set(body.select) : null;
329 
330    const where = this.compileWhere(filters, byId);
331    // TASK-0135 (replica routing): the table-view record QUERY (filter/sort/
332    // page over records — the "listing" read) is a spec-named replica read.
333    // Same staleness contract as `list()` above.
334    const rows = await this.prisma.read().databaseRecord.findMany({
335      where: {
336        ...where,
337        databaseId,
338        ...(body.includeArchived ? {} : { archivedAt: null })
339      },
340      include: { page: { select: { title: true, icon: true } }, properties: true },
341      orderBy: { position: "asc" },
342      take: 5000
343    }) as RowT[];
344 
345    let rowsF = this.applyDisjunction(rows, filters, byId, userId);
346    rowsF = this.applySort(rowsF, sort, byId);
347    const totalCount = rowsF.length;
348 
349    // Relation values live in `RelationLink`, not `DatabaseRecordProperty` —
350    // batch-load them once for the full filtered set so `toDto` can synthesize
351    // `properties[relPropId] = { recordIds }`. (Skipping this makes every
352    // relation cell read `null` and created links are invisible in the UI.)
353    const relLinkRecords = await this.buildRelationLinkRecords(rowsF, byId);
354 
355    const mapRow = (row: RowT) => {
356      const dto = this.toDto(row, byId, undefined, relLinkRecords) as Record<
357        string,
358        unknown
359      > & { properties: Record<string, unknown> };
360      if (!selectSet) return dto;
361      const kept: Record<string, unknown> = {};
362      for (const [pid, value] of Object.entries(dto.properties)) {
363        const prop = byId.get(pid);
364        if (selectSet.has(pid) || prop?.isTitle || prop?.type === "TITLE") kept[pid] = value;
365      }
366      return { ...dto, properties: kept };
367    };
368 
369    if (group) {
370      const groupProp = group[0]!;
371      const groupMeta = byId.get(groupProp.propertyId);
372      const groups: { key: { propertyId: string; value: unknown; label: string }; count: number; records: Record<string, unknown>[] }[] = [];
373      const index = new Map<string, (typeof groups)[number]>();
374      for (const row of rowsF) {
375        const value = this.canonicalOptionValue(
376          this.propertyValue(row, byId, groupProp.propertyId),
377          groupMeta?.type ?? "TEXT",
378          groupMeta?.config
379        );
380        const gid = `${groupProp.propertyId}::${this.groupKey(value)}`;
381        let g = index.get(gid);
382        if (!g) {
383          g = {
384            key: { propertyId: groupProp.propertyId, value, label: this.groupLabel(groupProp.propertyId, value, byId) },
385            count: 0,
386            records: []
387          };
388          index.set(gid, g);
389          groups.push(g);
390        }
391        g.records.push(mapRow(row));
392        g.count += 1;
393      }
394      return { data: { groups, totalCount, nextCursor: null } };
395    }
396 
397    const offset = body.cursor ? this.cursorOffset(rowsF, body.cursor) : 0;
398    const slice = rowsF.slice(offset, offset + pageSize);
399    const more = offset + pageSize < rowsF.length;
400    const last = slice.length > 0 ? slice[slice.length - 1] : null;
401    const nextCursor = more && last ? this.makeCursor(last.id) : null;
402 
403    return { data: { records: slice.map(mapRow), totalCount, nextCursor } };
404  }
405 
406  // ---------- CRUD ----------
407 
408  async createRecord(databaseId: string, userId: string, body: CreateRecordInput, req: Request) {
409    const db = await this.getDbChecked(databaseId, userId, "EDIT");
410    const properties = await this.prisma.databaseProperty.findMany({
411      where: { databaseId, deletedAt: null },
412      select: { id: true, name: true, type: true, isTitle: true, config: true }
413    });
414    const byId = new Map(properties.map((p) => [p.id, p]));
415    this.validateWritable(properties, body.properties, body.links);
416 
417    const last = await this.prisma.databaseRecord.findFirst({
418      where: { databaseId },
419      orderBy: { position: "desc" },
420      select: { position: true }
421    });
422    const position = between(last?.position, undefined);
423    const title = body.title?.trim() || "Untitled";
424 
425    const created = await this.prisma.$transaction(async (tx) => {
426      const page = await tx.page.create({
427        data: { workspaceId: db.workspaceId, title, position, createdBy: userId, updatedBy: userId }
428      });
429      const record = await tx.databaseRecord.create({
430        data: {
431          workspaceId: db.workspaceId,
432          databaseId,
433          pageId: page.id,
434          position,
435          createdBy: userId,
436          lastEditedBy: userId
437        }
438      });
439      await this.writeProperties(tx, record.id, byId, body.properties);
440      await this.writeLinks(tx, db.id, record.id, byId, body.links);
441      return { page, record };
442    });
443 
444    const full = (await this.prisma.databaseRecord.findUniqueOrThrow({
445      where: { id: created.record.id },
446      include: { page: { select: { title: true, icon: true } }, properties: true }
447    })) as RowT;
448 
449    await this.audit.audit(req, req?.auth, {
450      action: "record.created",
451      resourceType: "record",
452      resourceId: created.record.id,
453      workspaceId: db.workspaceId,
454      pageId: created.page.id,
455      metadata: { databaseId }
456    });
457    // TASK-0110: the Phase-5 record event carries `changedProperties`
458    // (the field the Phase-11 automation matcher + PROPERTY_CHANGED trigger
459    // read). Record creation changes every field, hence `["*"]`.
460    await this.events.publish(EVENTS.RECORD_CREATED, {
461      workspaceId: db.workspaceId,
462      databaseId,
463      recordId: created.record.id,
464      changedProperties: ["*"]
465    });
466    return this.toDto(full, byId);
467  }
468 
469  async getRecord(recordId: string, userId: string) {
470    // TASK-0135 query-shape fix (record-open, hot path #5): the row is read
471    // ONCE in a NARROW select (no unused relation include) — the two used
472    // relations (the page title + the property rows) come in the same
473    // round-trip, and the profiled double-fetch (a wide pre-permission
474    // `findUnique` + a post-permission `include`) is gone. The permission
475    // check still runs BEFORE anything is returned; the read is a plain
476    // fetch (no side effect) of data the response never exposes to a
477    // non-member (the 403 is thrown from `getDbChecked` before the
478    // assembly/return).
479    const row = (await this.prisma.databaseRecord.findUnique({
480      where: { id: recordId },
481      select: {
482        id: true,
483        workspaceId: true,
484        databaseId: true,
485        pageId: true,
486        icon: true,
487        coverUrl: true,
488        archivedAt: true,
489        version: true,
490        position: true,
491        createdTime: true,
492        lastEditedTime: true,
493        createdBy: true,
494        lastEditedBy: true,
495        createdAt: true,
496        page: { select: { title: true, icon: true } },
497        properties: { select: { propertyId: true, value: true } }
498      }
499    })) as RowT | null;
500    if (!row || row.archivedAt !== null) throw ApiError.notFound("Record not found.");
501 
502    const db = await this.getDbChecked(row.databaseId, userId, "VIEW", row.workspaceId);
503 
504    // The column metadata is the shared 60 s cache (busted on
505    // `database.updated`) — the SAME key + bust semantics + entry shape as
506    // `listProperties`, so the record-open and the table-view property list
507    // never disagree (a narrower entry here would corrupt the shared key —
508    // zod-on-read would treat it as a miss on every cross read).
509    const { data } = await this.cache.getMetadata(
510      db.id,
511      "properties",
512      async () => {
513        const properties = await this.prisma.databaseProperty.findMany({
514          where: { databaseId: db.id, deletedAt: null },
515          orderBy: { position: "asc" }
516        });
517        // The SAME legacy-id normalization as `listProperties` (and `get`):
518        // both loaders write to the ONE shared `…:properties` cache key, so
519        // they must store the SAME normalized shape — whichever fills first
520        // wins for the 60 s TTL, and a raw (id-less) entry filled by a
521        // record-open would make the table view's `findOption` miss every
522        // canonical `{ optionId }` value → blank select/status cells.
523        const normalized = properties.map((p) =>
524          p.type === "SELECT" || p.type === "MULTI_SELECT" || p.type === "STATUS"
525            ? { ...p, config: normalizeSelectOptions(p.config) as object }
526            : p
527        );
528        return { data: normalized };
529      },
530      z.object({ data: z.array(PROPERTY_CACHE) })
531    );
532    const byId = new Map(
533      (data as { data: Array<PropertyMeta & { databaseId: string }> }).data.map((p) => [p.id, p])
534    );
535 
536    const relations = await this.buildRelations([row]);
537    const relLinkRecords = await this.buildRelationLinkRecords([row], byId);
538    return this.toDto(row, byId, relations, relLinkRecords);
539  }
540 
541  async updateRecord(recordId: string, userId: string, body: UpdateRecordInput, req: Request) {
542    const record = await this.prisma.databaseRecord.findUnique({ where: { id: recordId } });
543    if (!record) throw ApiError.notFound("Record not found.");
544    const db = await this.getDbChecked(record.databaseId, userId, "EDIT", record.workspaceId);
545    const properties = await this.prisma.databaseProperty.findMany({
546      where: { databaseId: db.id, deletedAt: null },
547      select: { id: true, name: true, type: true, isTitle: true, config: true }
548    });
549    const byId = new Map(properties.map((p) => [p.id, p]));
550    this.validateWritable(properties, body.properties, body.links);
551 
552    // TASK-0094 R4 — the ASSIGNMENT hook: capture the PRE-UPDATE values of
553    // any PERSON properties being changed, so the new assignees (new minus
554    // old, minus the caller) get an ASSIGNMENT notification after the write.
555    const personPropertyIds =
556      body.properties != null
557        ? Object.entries(body.properties)
558            .filter(([pid]) => byId.get(pid)?.type === "PERSON")
559            .map(([pid]) => pid)
560        : [];
561    const oldPersonValues = new Map<string, string[]>();
562    if (personPropertyIds.length > 0) {
563      const oldRows = await this.prisma.recordProperty.findMany({
564        where: { recordId, propertyId: { in: personPropertyIds } },
565        select: { propertyId: true, value: true }
566      });
567      for (const row of oldRows) {
568        const v = row.value as { userIds?: unknown } | null;
569        oldPersonValues.set(
570          row.propertyId,
571          Array.isArray(v?.userIds) ? (v!.userIds as string[]) : []
572        );
573      }
574    }
575 
576    await this.prisma.$transaction(async (tx) => {
577      await tx.databaseRecord.update({
578        where: { id: recordId },
579        data: {
580          ...(body.coverUrl !== undefined ? { coverUrl: body.coverUrl } : {}),
581          ...(body.icon !== undefined ? { icon: body.icon } : {}),
582          ...(body.archived !== undefined ? { archivedAt: body.archived ? new Date() : null } : {}),
583          version: { increment: 1 },
584          lastEditedBy: userId
585        }
586      });
587      if (record.pageId && (body.title !== undefined || body.archived !== undefined)) {
588        await tx.page.update({
589          where: { id: record.pageId },
590          data: {
591            ...(body.title !== undefined ? { title: body.title } : {}),
592            ...(body.archived !== undefined ? { archivedAt: body.archived ? new Date() : null } : {}),
593            updatedBy: userId
594          }
595        });
596      }
597      if (body.properties) await this.writeProperties(tx, recordId, byId, body.properties);
598      if (body.links) await this.writeLinks(tx, db.id, recordId, byId, body.links);
599    });
600 
601    const full = (await this.prisma.databaseRecord.findUniqueOrThrow({
602      where: { id: recordId },
603      include: { page: { select: { title: true, icon: true } }, properties: true }
604    })) as RowT;
605 
606    await this.audit.audit(req, req?.auth, {
607      action: "record.updated",
608      resourceType: "record",
609      resourceId: recordId,
610      workspaceId: db.workspaceId,
611      pageId: record.pageId ?? undefined,
612      metadata: { databaseId: db.id, changedProperties: this.changedFields(body) }
613    });
614    // TASK-0110: `changedProperties` (not `fields`) — the field the Phase-11
615    // automation matcher + PROPERTY_CHANGED trigger read. An event lacking
616    // it never fires PROPERTY_CHANGED (no false positives).
617    await this.events.publish(EVENTS.RECORD_UPDATED, {
618      workspaceId: db.workspaceId,
619      databaseId: db.id,
620      recordId,
621      changedProperties: this.changedFields(body)
622    });
623    // The ASSIGNMENT notifications (the PERSON-property changes) — after
624    // the write + event, best-effort (a notification failure never fails
625    // the record update). `db` may be the assert-only stub (workspaceId
626    // override path), so fetch the title explicitly for the notification.
627    if (body.properties) {
628      const dbTitle =
629        (await this.prisma.database.findUnique({
630          where: { id: record.databaseId },
631          select: { title: true }
632        }))?.title ?? "a database";
633      await this.notifyAssignments(record, dbTitle, body.properties, byId, oldPersonValues, userId);
634    }
635    // Read-your-own-write: the mutation response must reflect the link that
636    // was JUST written, so read the junction from the primary (not the
637    // replica — a lagging replica could return a stale relation value).
638    const relLinkRecords = await this.buildRelationLinkRecords([full], byId, this.prisma);
639    return this.toDto(full, byId, undefined, relLinkRecords);
640  }
641 
642  /**
643   * TASK-0094 R4 — PERSON property gained users → an ASSIGNMENT
644   * notification for each NEW assignee (new − old, minus the caller — the
645   * self-notification suppression; a user assigning themselves isn't
646   * "assigned" in the product sense, recorded). The deep link opens the
647   * record's 1:1 page (records are pages — the `record` deep-link type).
648   */
649  private async notifyAssignments(
650    record: { id: string; workspaceId: string; pageId?: string | null },
651    databaseTitle: string,
652    properties: Record<string, unknown>,
653    byId: Map<string, PropertyMeta>,
654    oldValues: Map<string, string[]>,
655    actorId: string
656  ): Promise<void> {
657    for (const [propertyId, raw] of Object.entries(properties)) {
658      const prop = byId.get(propertyId);
659      if (!prop || prop.type !== "PERSON") continue;
660      const newIds = new Set(this.asStringArray(raw, ["userIds"], "userIds"));
661      const oldIds = new Set(oldValues.get(propertyId) ?? []);
662      const assigned = [...newIds].filter((id) => !oldIds.has(id) && id !== actorId);
663      if (assigned.length === 0) continue;
664      for (const assigneeId of assigned) {
665        await this.notifications
666          .notify({
667            workspaceId: record.workspaceId,
668            userId: assigneeId,
669            type: "ASSIGNMENT",
670            title: `You were assigned on “${databaseTitle}”`,
671            body: `${prop.name} → you`,
672            // `pageId` ride-along (TASK-0094 R5): records are 1:1 pages, so
673            // the web can open the record's page directly.
674            link: { type: "record", id: record.id, pageId: record.pageId ?? undefined },
675            actorId
676          })
677          .catch(() => undefined);
678      }
679    }
680  }
681 
682  async deleteRecord(recordId: string, userId: string, req: Request): Promise<{ deleted: true }> {
683    const record = await this.prisma.databaseRecord.findUnique({
684      where: { id: recordId },
685      select: { id: true, pageId: true, workspaceId: true, databaseId: true }
686    });
687    if (!record) throw ApiError.notFound("Record not found.");
688    const db = await this.getDbChecked(record.databaseId, userId, "EDIT", record.workspaceId);
689 
690    // Hard delete (properties/links cascade per schema); the 1:1 page and
691    // its blocks are removed explicitly — mirrors DatabaseService.remove.
692    await this.prisma.$transaction(async (tx) => {
693      if (record.pageId) {
694        await tx.block.deleteMany({ where: { pageId: record.pageId } });
695        await tx.page.delete({ where: { id: record.pageId } });
696      }
697      await tx.databaseRecord.delete({ where: { id: recordId } });
698    });
699 
700    await this.audit.audit(req, req?.auth, {
701      action: "record.deleted",
702      resourceType: "record",
703      resourceId: recordId,
704      workspaceId: db.workspaceId,
705      pageId: record.pageId ?? undefined,
706      metadata: { databaseId: db.id }
707    });
708    // No record.deleted event: the worker catalog only indexes
709    // record.created / record.updated and re-reads rows from Prisma, so a
710    // publish would be a no-op (schema constraint, recorded per rule 29).
711    // Search docs are dropped by the periodic reconcile.
712    return { deleted: true };
713  }
714 
715  async bulk(databaseId: string, userId: string, body: BulkRecordsInput, req: Request) {
716    const db = await this.getDbChecked(databaseId, userId, "EDIT");
717    const targets = await this.prisma.databaseRecord.findMany({
718      where: { id: { in: body.recordIds }, databaseId },
719      select: { id: true, pageId: true }
720    });
721    if (targets.length === 0) throw ApiError.notFound("No matching records found.");
722    const properties = await this.prisma.databaseProperty.findMany({
723      where: { databaseId, deletedAt: null },
724      select: { id: true, name: true, type: true, isTitle: true, config: true }
725    });
726    const byId = new Map(properties.map((p) => [p.id, p]));
727    if (body.op === "update") this.validateWritable(properties, body.properties, undefined);
728 
729    await this.prisma.$transaction(async (tx) => {
730      if (body.op === "update") {
731        await this.bulkUpdate(tx, targets, byId, body.properties ?? {}, userId);
732      } else {
733        const pageIds = targets.map((t) => t.pageId).filter((p): p is string => Boolean(p));
734        if (pageIds.length > 0) {
735          await tx.block.deleteMany({ where: { pageId: { in: pageIds } } });
736          await tx.page.deleteMany({ where: { id: { in: pageIds } } });
737        }
738        await tx.databaseRecord.deleteMany({ where: { id: { in: targets.map((t) => t.id) } } });
739      }
740    });
741 
742    const changedProperties = body.op === "update" ? this.changedFields(body) : ["*"];
743    await this.audit.audit(req, req?.auth, {
744      action: body.op === "update" ? "record.bulk_updated" : "record.bulk_deleted",
745      resourceType: "record",
746      resourceId: targets.map((t) => t.id).join(","),
747      workspaceId: db.workspaceId,
748      metadata: { databaseId, count: targets.length }
749    });
750    if (body.op === "update") {
751      // Cap stream fan-out: individual events for the first 50 records
752      // (the worker re-reads each by id); the audit row covers the tail.
753      const MAX_EVENTS = 50;
754      for (let i = 0; i < Math.min(targets.length, MAX_EVENTS); i += 1) {
755        const id = targets[i]?.id;
756        if (!id) continue;
757        await this.events.publish(EVENTS.RECORD_UPDATED, { workspaceId: db.workspaceId, databaseId, recordId: id, changedProperties });
758      }
759    }
760    return { affected: targets.length };
761  }
762 
763  // ---------- CSV export ----------
764 
765  async exportCsv(databaseId: string, userId: string, query: ListRecordsInput): Promise<{ csv: string; rows: number; truncated: boolean }> {
766    await this.getDbChecked(databaseId, userId, "VIEW");
767    const properties = await this.prisma.databaseProperty.findMany({
768      where: { databaseId, deletedAt: null },
769      orderBy: { position: "asc" },
770      select: { id: true, name: true, type: true, isTitle: true, config: true }
771    });
772    const byId = new Map(properties.map((p) => [p.id, p]));
773 
774    // Reuse the listing pipeline for filter/sort parity (group ignored —
775    // a CSV is flat).
776    let filters: FilterNode | null = null;
777    let sort: SortClause[] | null = null;
778    if (query.viewId) {
779      const view = await this.prisma.databaseView.findFirst({ where: { id: query.viewId, databaseId } });
780      if (!view) throw ApiError.notFound("View not found.");
781      filters = this.asFilters(view.filter);
782      sort = this.asSort(view.sort);
783    }
784    if (query.filter) {
785      const parsed = this.parseFilterTree(query.filter);
786      if (parsed) filters = parsed;
787    }
788    if (query.sort) sort = this.parseSort(query.sort);
789 
790    const where = this.compileWhere(filters, byId);
791    const rows = (await this.prisma.databaseRecord.findMany({
792      where: { ...where, databaseId, archivedAt: null },
793      include: { page: { select: { title: true, icon: true } }, properties: true },
794      orderBy: { position: "asc" },
795      take: MAX_EXPORT_ROWS + 1
796    })) as RowT[];
797    const rowsF = this.applySort(this.applyDisjunction(rows, filters, byId, userId), sort, byId);
798    const truncated = rowsF.length > MAX_EXPORT_ROWS;
799    const page = rowsF.slice(0, MAX_EXPORT_ROWS);
800 
801    const columns: { label: string; get: (r: RowT) => string }[] = [
802      { label: "Title", get: (r) => r.page?.title ?? "" },
803      ...properties.filter((p) => !p.isTitle).map((p) => ({ label: p.name, get: (r: RowT) => this.exportValue(r, byId, p.id) }))
804    ];
805    const lines = [
806      columns.map((c) => this.csvCell(c.label)).join(","),
807      ...page.map((r) => columns.map((c) => this.csvCell(c.get(r))).join(","))
808    ];
809    return { csv: lines.join("\n") + "\n", rows: page.length, truncated };
810  }
811 
812  // ---------- CSV import (T-165) ----------
813 
814  /**
815   * Import CSV rows as records. `dryRun` computes the header → property
816   * mapping and returns a sample without writing; the follow-up call with
817   * `dryRun: false` performs a single-transaction import (page + record +
818   * property rows per row, same shape as `createRecord`).
819   *
820   * Caps: the request body is limited by the route-scoped body parser
821   * (`MAX_CSV_IMPORT_BODY_BYTES`, see `common/csv-import-body.ts`) and the
822   * parsed table by `MAX_CSV_IMPORT_ROWS`. Read-only / relation / title
823   * properties are never importable — they are reported unmapped in the
824   * dry run, and a mapping that targets one is a 400.
825   */
826  async importCsv(databaseId: string, userId: string, input: CsvImportInput, req: Request) {
827    const db = await this.getDbChecked(databaseId, userId, "EDIT");
828    const properties = await this.prisma.databaseProperty.findMany({
829      where: { databaseId, deletedAt: null },
830      select: { id: true, name: true, type: true, isTitle: true, config: true }
831    });
832    const byId = new Map(properties.map((p) => [p.id, p]));
833    const titleProp = properties.find((p) => p.isTitle || p.type === "TITLE");
834    if (!titleProp) throw ApiError.validation("The database has no title property.", [{ issue: "no_title_property" }]);
835 
836    const rows = parseCsv(input.csv);
837    if (rows.length === 0) {
838      throw ApiError.validation("The CSV is empty (a header row is required).", [{ issue: "empty_csv" }]);
839    }
840    const [headerRow, ...dataRows] = rows;
841    const header: string[] = (headerRow ?? []).map((h) => h.trim());
842 
843    // Row cap (the body-size cap is enforced upstream by the body parser).
844    // A hard 400 — not a silent truncation — because an over-cap file would
845    // otherwise drop rows the client expects to be imported.
846    if (dataRows.length > MAX_CSV_IMPORT_ROWS) {
847      throw ApiError.validation(
848        `The CSV has ${dataRows.length} data rows; the limit is ${MAX_CSV_IMPORT_ROWS}.`,
849        [{ issue: "too_many_rows", received: dataRows.length, limit: MAX_CSV_IMPORT_ROWS }]
850      );
851    }
852    const limited = dataRows;
853 
854    // ---- column mapping (name-based, then explicit overrides) ----
855    // Importable = writable value properties. Read-only (FORMULA/ROLLUP/
856    // timestamps/…), RELATION (written via links, needs a target record id)
857    // and PERSON/FILES (values are user/file ids — no round-trip from a
858    // human-readable CSV) are reported unmapped with a reason.
859    const importable = properties.filter((p) => !p.isTitle && p.type !== "TITLE" && !READ_ONLY_TYPES.has(p.type) && p.type !== "RELATION" && p.type !== "PERSON" && p.type !== "FILES");
860    const notImportableReason = (p: PropertyMeta): string =>
861      p.isTitle || p.type === "TITLE" ? "title" : READ_ONLY_TYPES.has(p.type) ? "read_only" : p.type === "RELATION" ? "relation" : p.type === "PERSON" || p.type === "FILES" ? "record_ids_only" : "not_importable";
862    const headerIndex = new Map<string, number>();
863    header.forEach((h, i) => {
864      if (h !== "" && !headerIndex.has(h)) headerIndex.set(h, i);
865    });
866 
867    // The title column is written via the page title, not a property row.
868    const wantedTitle = (input.titleHeader ?? titleProp.name).trim();
869    const titleCol = headerIndex.get(wantedTitle);
870    if (input.titleHeader !== undefined && titleCol === undefined) {
871      throw ApiError.validation(`No column named "${wantedTitle}" for the title.`, [
872        { field: "titleHeader", issue: "unknown_header", received: wantedTitle }
873      ]);
874    }
875 
876    // First match wins: a property whose name appears in several headers is
877    // claimed by the FIRST one only (deterministic; later headers report
878    // `duplicate_header`).
879    const columns: { index: number; header: string; propertyId: string | null; reason: string }[] = [];
880    const seenProp = new Map<string, number>(); // propertyId → claiming header index
881    const claimedPropIds = new Set<string>();
882    header.forEach((h, i) => {
883      if (i === titleCol) {
884        columns.push({ index: i, header: h, propertyId: titleProp.id, reason: "title" });
885        seenProp.set(titleProp.id, i);
886        return;
887      }
888      const prop = importable.find((p) => p.name === h);
889      if (prop && !claimedPropIds.has(prop.id)) {
890        claimedPropIds.add(prop.id);
891        seenProp.set(prop.id, i);
892        columns.push({ index: i, header: h, propertyId: prop.id, reason: "" });
893      } else {
894        const reason = h === "" ? "no_header" : prop ? "duplicate_header" : "no_matching_property";
895        columns.push({ index: i, header: h, propertyId: null, reason });
896      }
897    });
898    // Explicit overrides: header → propertyId (must be a real, importable property).
899    for (const [h, pid] of Object.entries(input.mappings ?? {})) {
900      const idx = headerIndex.get(h);
901      if (idx === undefined) {
902        throw ApiError.validation(`mapping references unknown header "${h}".`, [
903          { field: "mappings", issue: "unknown_header", received: h }
904        ]);
905      }
906      const prop = byId.get(pid);
907      if (!prop) {
908        throw ApiError.validation(`mapping propertyId "${pid}" is not a property of this database.`, [
909          { field: "mappings", issue: "unknown_property", received: pid }
910        ]);
911      }
912      if (prop.isTitle || prop.type === "TITLE") {
913        throw ApiError.validation(`"${h}" maps to the title property; use titleHeader instead.`, [
914          { field: "mappings", issue: "title_via_title_header", received: h }
915        ]);
916      }
917      if (READ_ONLY_TYPES.has(prop.type)) {
918        throw ApiError.validation(`"${h}" cannot map to read-only property ${prop.name} (${prop.type}).`, [
919          { field: "mappings", issue: "read_only_property", received: h }
920        ]);
921      }
922      if (prop.type === "RELATION") {
923        throw ApiError.validation(`"${h}" cannot map to a relation property.`, [
924          { field: "mappings", issue: "relation_not_importable", received: h }
925        ]);
926      }
927      if (prop.type === "PERSON" || prop.type === "FILES") {
928        throw ApiError.validation(`"${h}" cannot map to ${prop.type} (stored as ids — not importable).`, [
929          { field: "mappings", issue: "record_ids_only", received: h }
930        ]);
931      }
932      // Re-point the column: drop the property's previous claim first.
933      const prevIdx = seenProp.get(pid);
934      if (prevIdx !== undefined && prevIdx !== idx) {
935        const prev = columns[prevIdx]!;
936        prev.propertyId = null;
937        prev.reason = "reassigned";
938      }
939      const col = columns[idx]!;
940      col.propertyId = pid;
941      col.reason = "";
942      seenProp.set(pid, idx);
943    }
944    // Drop duplicate claims (a property matched by two headers): keep the
945    // first. The title column keeps its claim (handled by `titleCol`).
946    for (const c of columns) {
947      if (!c.propertyId) continue;
948      if (c.index !== seenProp.get(c.propertyId)) {
949        c.propertyId = null;
950        c.reason = "duplicate_header";
951      }
952    }
953    // Final propertyId → header index (title included — written via page.title).
954    const columnOf = new Map<string, number>();
955    for (const c of columns) if (c.propertyId) columnOf.set(c.propertyId, c.index);
956 
957    if (!input.dryRun && columnOf.size === 0) {
958      throw ApiError.validation("No columns map to writable properties — nothing to import.", [
959        { issue: "no_importable_columns" }
960      ]);
961    }
962 
963    // ---- dry run: report the mapping + a sample, write nothing ----
964    if (input.dryRun) {
965      return {
966        dryRun: true,
967        databaseId,
968        rowCount: limited.length,
969        columns: columns.map((c) => ({ ...c })),
970        unmappedProperties: properties
971          .filter((p) => p !== titleProp && !columnOf.has(p.id))
972          .map((p) => ({ id: p.id, name: p.name, type: p.type, reason: notImportableReason(p) })),
973        sampleRows: limited.slice(0, 5).map((r) => {
974          const o: Record<string, string> = {};
975          columns.forEach((c) => {
976            o[c.header || `col_${c.index}`] = (r as string[])[c.index] ?? "";
977          });
978          return o;
979        })
980      };
981    }
982 
983    // ---- perform the import (single transaction) ----
984    const errors: CsvImportRowError[] = [];
985    const coerceCell = (prop: PropertyMeta, raw: string): unknown => {
986      const text = raw.trim();
987      if (text === "") return null;
988      switch (prop.type) {
989        case "TEXT":
990        case "RICH_TEXT":
991        case "TITLE":
992          return { text, marks: [] };
993        case "NUMBER": {
994          const n = Number(text);
995          if (Number.isNaN(n)) throw ApiError.validation(`not a number: "${text}"`, [{ issue: "bad_number" }]);
996          return { value: n };
997        }
998        case "CHECKBOX": {
999          const t = text.toLowerCase();
1000          if (["true", "1", "yes", "on"].includes(t)) return { value: true };
1001          if (["false", "0", "no", "off", ""].includes(t)) return { value: false };
1002          throw ApiError.validation(`not a boolean: "${text}"`, [{ issue: "bad_checkbox" }]);
1003        }
1004        case "SELECT":
1005        case "STATUS": {
1006          const id = this.resolveOptionRef(text, this.optionsOf(prop.config));
1007          if (!this.optionExists(id, this.optionsOf(prop.config))) {
1008            throw ApiError.validation(`unknown option: "${text}"`, [{ issue: "unknown_option", received: text }]);
1009          }
1010          return { optionId: id };
1011        }
1012        case "MULTI_SELECT": {
1013          const ids = text.split("|").map((s) => s.trim()).filter(Boolean);
1014          for (const s of ids) {
1015            const id = this.resolveOptionRef(s, this.optionsOf(prop.config));
1016            if (!this.optionExists(id, this.optionsOf(prop.config))) {
1017              throw ApiError.validation(`unknown option: "${s}"`, [{ issue: "unknown_option", received: s }]);
1018            }
1019          }
1020          return { optionIds: ids.map((s) => this.resolveOptionRef(s, this.optionsOf(prop.config))) };
1021        }
1022        case "DATE":
1023        case "DATE_RANGE": {
1024          const d = new Date(text);
1025          if (Number.isNaN(d.getTime())) throw ApiError.validation(`not a date: "${text}"`, [{ issue: "bad_date", received: text }]);
1026          return { start: d.toISOString(), end: null };
1027        }
1028        case "EMAIL":
1029        case "PHONE":
1030        case "URL":
1031          return this.coerceAndCanonical(text, prop);
1032        default:
1033          // FILES (and anything unmapped-by-accident): canonical passthrough.
1034          return this.coerceAndCanonical(text, prop);
1035      }
1036    };
1037 
1038    const recordIds: string[] = [];
1039    await this.prisma.$transaction(async (tx) => {
1040      const last = await tx.databaseRecord.findFirst({
1041        where: { databaseId },
1042        orderBy: { position: "desc" },
1043        select: { position: true }
1044      });
1045      const positions = this.appendPositions(last?.position, limited.length);
1046      if (positions.length === 0) return;
1047      if (!positions.every((p) => p.length <= MAX_PRECISION)) {
1048        // The tail is at (or past) MAX_PRECISION: re-space the whole
1049        // database to an even grid in-transaction (the house rebalance
1050        // pattern from block.service) so the new rows append after it.
1051        const all = await tx.databaseRecord.findMany({
1052          where: { databaseId },
1053          orderBy: { position: "asc" },
1054          select: { id: true, position: true }
1055        });
1056        const fresh = rebalancePositions(all.map((r) => r.position).concat(limited.map(() => "A")));
1057        for (let i = 0; i < all.length; i += 1) {
1058          const r = all[i]!;
1059          if (r.position !== fresh[i]) {
1060            await tx.databaseRecord.update({ where: { id: r.id }, data: { position: fresh[i] } });
1061          }
1062        }
1063        // The imported rows take the grid slots after the existing rows.
1064        positions.length = 0;
1065        positions.push(...fresh.slice(all.length));
1066      }
1067      for (let i = 0; i < limited.length; i += 1) {
1068        const r = limited[i] as string[];
1069        const line = i + 2; // header is line 1
1070        // Per-cell validation: a bad cell fails THAT row only.
1071        const propsPatch: Record<string, unknown> = {};
1072        let rowFailed = false;
1073        for (const [pid, hIdx] of columnOf) {
1074          if (pid === titleProp.id) continue; // title handled below
1075          const prop = byId.get(pid);
1076          if (!prop) continue;
1077          const raw = (r as string[])[hIdx] ?? "";
1078          try {
1079            propsPatch[pid] = coerceCell(prop, raw);
1080          } catch (e) {
1081            errors.push({ row: line, column: prop.name, error: e instanceof Error ? e.message : "invalid value" });
1082            rowFailed = true;
1083            break;
1084          }
1085        }
1086        if (rowFailed) continue;
1087 
1088        const titleRaw = titleCol !== undefined ? ((r as string[])[titleCol] ?? "").trim() : "";
1089        const title = titleRaw || "Untitled";
1090        const position = positions[i]!;
1091        const page = await tx.page.create({
1092          data: { workspaceId: db.workspaceId, title, position, createdBy: userId, updatedBy: userId }
1093        });
1094        const record = await tx.databaseRecord.create({
1095          data: {
1096            workspaceId: db.workspaceId,
1097            databaseId,
1098            pageId: page.id,
1099            position,
1100            createdBy: userId,
1101            lastEditedBy: userId
1102          }
1103        });
1104        await this.writeProperties(tx, record.id, byId, propsPatch);
1105        recordIds.push(record.id);
1106      }
1107    });
1108 
1109    await this.audit.audit(req, req?.auth, {
1110      action: "database.records_imported",
1111      resourceType: "database",
1112      resourceId: databaseId,
1113      workspaceId: db.workspaceId,
1114      metadata: { imported: recordIds.length, failed: errors.length, rows: limited.length, databaseId }
1115    });
1116    // Cap stream fan-out like `bulk`: individual events for the first 50
1117    // records (the worker re-reads each by id); the audit row covers the tail.
1118    const MAX_EVENTS = 50;
1119    for (let i = 0; i < Math.min(recordIds.length, MAX_EVENTS); i += 1) {
1120      await this.events.publish(EVENTS.RECORD_CREATED, {
1121        workspaceId: db.workspaceId,
1122        databaseId,
1123        recordId: recordIds[i]!,
1124        changedProperties: ["*"]
1125      });
1126    }
1127 
1128    return {
1129      dryRun: false,
1130      databaseId,
1131      imported: recordIds.length,
1132      failed: errors.length,
1133      rows: limited.length,
1134      errors
1135    };
1136  }
1137 
1138  /**
1139   * Generate N positions that sort AFTER `last` (all records are appended to
1140   * the end). `rebalancePositions` builds an even grid over `n` cells; we
1141   * prefix each with `last` so every new position is `> last` and the list
1142   * stays evenly spaced. (The `a + midpoint` sequential-append of
1143   * `between(last)` would grow one char per row and exhaust MAX_PRECISION
1144   * around row 128.)
1145   */
1146  private appendPositions(last: string | undefined, n: number): string[] {
1147    if (n === 0) return [];
1148    const grid = rebalancePositions(Array.from({ length: n }, () => "A"));
1149    const base = last ?? "";
1150    return grid.map((g) => base + g);
1151  }
1152 
1153  /** True when `ref` resolves to an existing option (id, legacy name, or
1154   *  deterministic id) — used to reject unknown SELECT/STATUS labels in the
1155   *  import (per-row error) instead of silently storing the raw label. */
1156  private optionExists(ref: string, options: unknown[]): boolean {
1157    if (ref === "") return false;
1158    for (const o of options) {
1159      const opt = o as { id?: unknown; name?: unknown } | null;
1160      const id = this.effectiveOptionId(opt);
1161      if (!id) continue;
1162      if (id === ref) return true;
1163      if (typeof opt?.name === "string" && opt.name === ref) return true;
1164    }
1165    return false;
1166  }
1167 
1168  // ---------- internals ----------
1169 
1170  private async getDbChecked(
1171    databaseId: string,
1172    userId: string,
1173    need: "VIEW" | "EDIT" | "FULL_ACCESS",
1174    workspaceIdOverride?: string
1175  ) {
1176    const db = workspaceIdOverride
1177      ? { id: databaseId, workspaceId: workspaceIdOverride, isArchived: false }
1178      : await this.prisma.database.findUnique({ where: { id: databaseId } });
1179    if (!db) throw ApiError.notFound("Database not found.");
1180    if (!workspaceIdOverride && db.isArchived) throw ApiError.notFound("Database not found.");
1181    await assertCan(this.prisma, { workspaceId: db.workspaceId, userId, resource: { databaseId: db.id } }, need);
1182    return db;
1183  }
1184 
1185  /** Validate that only writable properties/relations are in the payload. */
1186  private validateWritable(
1187    properties: PropertyMeta[],
1188    propertiesPatch?: Record<string, unknown>,
1189    links?: Record<string, string[]>
1190  ): void {
1191    for (const key of [...(propertiesPatch ? Object.keys(propertiesPatch) : []), ...(links ? Object.keys(links) : [])]) {
1192      const prop = properties.find((p) => p.id === key);
1193      if (!prop) {
1194        throw ApiError.validation(`Unknown property: ${key}.`, [
1195          { field: "properties", issue: "unknown_property", received: key }
1196        ]);
1197      }
1198      if (READ_ONLY_TYPES.has(prop.type)) {
1199        throw new ApiError("UNPROCESSABLE_ENTITY", 422, `Property ${prop.name} is read-only and cannot be written.`, [
1200          { issue: "read_only_property", property: prop.name }
1201        ]);
1202      }
1203      if (prop.isTitle || prop.type === "TITLE") {
1204        throw ApiError.validation("The title is set via the `title` field, not properties.", [
1205          { field: "properties", issue: "title_via_title_field", received: key }
1206        ]);
1207      }
1208    }
1209    if (links) {
1210      for (const [propId, targetIds] of Object.entries(links)) {
1211        const prop = properties.find((p) => p.id === propId);
1212        if (!prop || prop.type !== "RELATION") {
1213          throw ApiError.validation("links keys must be relation property ids.", [
1214            { field: "links", issue: "not_a_relation", received: propId }
1215          ]);
1216        }
1217        if (!Array.isArray(targetIds)) {
1218          throw ApiError.validation("links values must be arrays of record ids.", [
1219            { field: "links", issue: "array_required", received: propId }
1220          ]);
1221        }
1222      }
1223    }
1224  }
1225 
1226  /** Upsert RecordProperty rows for the given patch (title handled by caller). */
1227  private async writeProperties(
1228    tx: Prisma.TransactionClient,
1229    recordId: string,
1230    byId: Map<string, PropertyMeta>,
1231    patch: Record<string, unknown>
1232  ): Promise<void> {
1233    for (const [propertyId, raw] of Object.entries(patch)) {
1234      const prop = byId.get(propertyId);
1235      if (!prop) {
1236        throw ApiError.validation(`Unknown property: ${propertyId}.`, [
1237          { field: "properties", issue: "unknown_property", received: propertyId }
1238        ]);
1239      }
1240      // Canonicalize select-family refs (a legacy option NAME → the
1241      // deterministic id) so NEW rows store the id, not the name.
1242      const value = this.coerceAndCanonical(raw, prop);
1243      const json = value === null ? Prisma.JsonNull : (value as Prisma.InputJsonValue);
1244      await tx.recordProperty.upsert({
1245        where: { recordId_propertyId: { recordId, propertyId } },
1246        update: { value: json },
1247        create: { recordId, propertyId, value: json }
1248      });
1249    }
1250  }
1251 
1252  /**
1253   * Replace the record's relation links to each target database named by
1254   * the given relation properties (the target db is resolved via the
1255   * Relation row that owns the property).
1256   */
1257  private async writeLinks(
1258    tx: Prisma.TransactionClient,
1259    databaseId: string,
1260    recordId: string,
1261    byId: Map<string, PropertyMeta>,
1262    links: Record<string, string[]>
1263  ): Promise<void> {
1264    for (const [propId, targetIds] of Object.entries(links)) {
1265      const prop = byId.get(propId);
1266      if (!prop || prop.type !== "RELATION") continue;
1267      const rel = await tx.relation.findFirst({
1268        where: { databaseId, propertyId: propId },
1269        select: { targetDatabaseId: true }
1270      });
1271      if (!rel) continue; // relation row missing — nothing to link against
1272      const targetDatabaseId = rel.targetDatabaseId;
1273      await tx.relationLink.deleteMany({ where: { recordId, databaseId, targetDatabaseId } });
1274      const existing = await tx.databaseRecord.findMany({
1275        where: { id: { in: targetIds }, databaseId: targetDatabaseId },
1276        select: { id: true }
1277      });
1278      const found = new Set(existing.map((r) => r.id));
1279      for (const targetRecordId of targetIds) {
1280        if (!found.has(targetRecordId)) {
1281          throw ApiError.validation(`Link target record not found: ${targetRecordId}.`, [
1282            { field: "links", issue: "unknown_record", received: targetRecordId }
1283          ]);
1284        }
1285        await tx.relationLink.upsert({
1286          where: {
1287            databaseId_recordId_targetDatabaseId_targetRecordId: {
1288              databaseId,
1289              recordId,
1290              targetDatabaseId,
1291              targetRecordId
1292            }
1293          },
1294          update: {},
1295          create: { databaseId, recordId, targetDatabaseId, targetRecordId }
1296        });
1297      }
1298    }
1299  }
1300 
1301  /**
1302   * Resolve one stored option REF (an `optionId`) to the option's real id, or
1303   * `null` when the ref is not an option reference. LEGACY records created by
1304   * the pre-fix AI path stored the option NAME as the ref (`{ optionId:
1305   * "To Do" }` on options that carried no ids). The property options (after
1306   * the service-level `normalizeSelectOptions`) are keyed by
1307   * `optionIdForName(name)`, so a name ref resolves against BOTH the id and
1308   * the name — modern id refs resolve via the id, name refs via the name,
1309   * and everything else passes through untouched.
1310   */
1311  private optionsOf(config?: unknown): unknown[] {
1312    const cfg = (config ?? {}) as Record<string, unknown>;
1313    return Array.isArray(cfg.options) ? (cfg.options as unknown[]) : [];
1314  }
1315 
1316  /**
1317   * The option's effective id: its stored `id`, or — for a LEGACY AI option
1318   * that has none — the deterministic `optionIdForName(name)`. This is the
1319   * same id the service's `normalizeSelectOptions` assigns, so a legacy name
1320   * ref resolves to it even before the row's options are back-filled.
1321   */
1322  private effectiveOptionId(opt: { id?: unknown; name?: unknown } | null): string | null {
1323    if (!opt) return null;
1324    if (typeof opt.id === "string" && opt.id) return opt.id;
1325    if (typeof opt.name === "string" && opt.name) return optionIdForName(opt.name);
1326    return null;
1327  }
1328 
1329  private resolveOptionRef(ref: unknown, options: unknown[]): string {
1330    if (typeof ref !== "string" || ref.length === 0) return String(ref);
1331    for (const o of options) {
1332      const opt = o as { id?: unknown; name?: unknown } | null;
1333      const id = this.effectiveOptionId(opt);
1334      if (!id) continue;
1335      if (id === ref) return id;
1336      if (typeof opt?.name === "string" && opt.name === ref) return id;
1337    }
1338    return ref;
1339  }
1340 
1341  /**
1342   * The stored refs that a filter reference (an option id or, legacy, an
1343   * option NAME) should match: the canonical id PLUS the ref itself, so a
1344   * filter survives whichever side was written before the id fix (row stored
1345   * by name vs filter by id, or the inverse). Id refs collapse to the id.
1346   */
1347  private optionMatchSet(ref: string, config?: unknown): string[] {
1348    const resolved = this.resolveOptionRef(ref, this.optionsOf(config));
1349    return resolved === ref ? [ref] : [resolved, ref];
1350  }
1351 
1352  /** The display label (option NAME) of a stored select/status/multi-select value. */
1353  private selectLabelText(value: unknown, type: string, config?: unknown): string | null {
1354    if (type !== "SELECT" && type !== "STATUS" && type !== "MULTI_SELECT") return this.toText(value, type);
1355    const options = this.optionsOf(config);
1356    const nameOf = (ref: unknown): string => {
1357      if (typeof ref !== "string" || ref === "") return "";
1358      for (const o of options) {
1359        const opt = o as { id?: unknown; name?: unknown } | null;
1360        // Match the stored id, the EFFECTIVE id (a legacy option without an
1361        // id resolves to optionIdForName(name)), or the name itself.
1362        if (opt && typeof opt.name === "string" && (opt.id === ref || this.effectiveOptionId(opt) === ref || opt.name === ref)) {
1363          return opt.name;
1364        }
1365      }
1366      return ref;
1367    };
1368    if (type === "MULTI_SELECT") {
1369      const ids = this.toIdArray(value);
1370      const names = ids.map(nameOf).filter(Boolean);
1371      return names.length > 0 ? names.join(", ") : null;
1372    }
1373    const ref = (value as { optionId?: unknown } | null)?.optionId;
1374    if (typeof ref !== "string" || ref === "") return null;
1375    return nameOf(ref);
1376  }
1377 
1378  /** Canonicalize a stored value's option ref(s) for a select-family type. */
1379  private canonicalOptionValue(value: unknown, type: string, config?: unknown): unknown {
1380    if (value === null || value === undefined || typeof value !== "object") return value;
1381    const options = this.optionsOf(config);
1382    const v = value as Record<string, unknown>;
1383    if (type === "SELECT" || type === "STATUS") {
1384      if (typeof v.optionId === "string" && v.optionId !== "") {
1385        const id = this.resolveOptionRef(v.optionId, options);
1386        return id === v.optionId ? value : { ...v, optionId: id };
1387      }
1388      return value;
1389    }
1390    if (type === "MULTI_SELECT" && Array.isArray(v.optionIds)) {
1391      const ids = v.optionIds.map((x) => this.resolveOptionRef(x, options));
1392      if (ids.every((x, i) => x === (v.optionIds as unknown[])[i])) return value;
1393      return { ...v, optionIds: ids };
1394    }
1395    return value;
1396  }
1397 
1398  /**
1399   * Shape a raw API value to the stored per-type JSON value (or null), then
1400   * canonicalize select-family option refs (a legacy option NAME → the
1401   * deterministic id) so a write stores the id, not the name. Reads back
1402   * through `canonicalOptionValue` anyway, but writing the id keeps the DB
1403   * clean for the next reader (and makes `actualScalar`/sorts stable).
1404   */
1405  private coerceAndCanonical(raw: unknown, prop: PropertyMeta): unknown {
1406    const value = this.coerceValue(raw, prop.type);
1407    return this.canonicalOptionValue(value, prop.type, prop.config);
1408  }
1409 
1410  /** Shape a raw API value to the stored per-type JSON value (or null). */
1411  private coerceValue(raw: unknown, type: string): unknown {
1412    if (raw === undefined || raw === null) return null;
1413    switch (type) {
1414      case "TEXT":
1415      case "RICH_TEXT":
1416      case "TITLE": {
1417        if (typeof raw === "string") return { text: raw, marks: [] };
1418        if (typeof raw === "object" && raw !== null && "text" in raw) return raw;
1419        throw ApiError.validation("text value required.", [{ issue: "text_required", type }]);
1420      }
1421      case "NUMBER": {
1422        if (typeof raw === "number") return { value: raw };
1423        if (typeof raw === "string" && raw !== "" && !Number.isNaN(Number(raw))) return { value: Number(raw) };
1424        if (typeof raw === "object" && raw !== null && "value" in raw) return raw;
1425        throw ApiError.validation("number value required.", [{ issue: "number_required", type }]);
1426      }
1427      case "SELECT":
1428      case "STATUS": {
1429        if (typeof raw === "string") return { optionId: raw };
1430        if (typeof raw === "object" && raw !== null && "optionId" in raw) return raw;
1431        throw ApiError.validation("optionId value required.", [{ issue: "option_required", type }]);
1432      }
1433      case "MULTI_SELECT": {
1434        const ids = this.asStringArray(raw, ["optionIds"], "optionIds");
1435        return { optionIds: ids };
1436      }
1437      case "DATE":
1438      case "DATE_RANGE": {
1439        if (typeof raw === "string") return { start: raw, end: null };
1440        if (typeof raw === "object" && raw !== null && "start" in raw) return raw;
1441        throw ApiError.validation("date value required.", [{ issue: "date_required", type }]);
1442      }
1443      case "PERSON": {
1444        const ids = this.asStringArray(raw, ["userIds"], "userIds");
1445        return { userIds: ids };
1446      }
1447      case "EMAIL":
1448      case "PHONE": {
1449        if (typeof raw === "string") return { value: raw };
1450        if (typeof raw === "object" && raw !== null && "value" in raw) return raw;
1451        throw ApiError.validation(`${type} value required.`, [{ issue: "value_required", type }]);
1452      }
1453      case "URL": {
1454        if (typeof raw === "string") return { value: raw, title: null };
1455        if (typeof raw === "object" && raw !== null && "value" in raw) return raw;
1456        throw ApiError.validation("url value required.", [{ issue: "value_required", type }]);
1457      }
1458      case "CHECKBOX": {
1459        if (typeof raw === "boolean") return { value: raw };
1460        if (typeof raw === "object" && raw !== null && "value" in raw) return raw;
1461        throw ApiError.validation("boolean value required.", [{ issue: "boolean_required", type }]);
1462      }
1463      case "FILES": {
1464        const ids = this.asStringArray(raw, ["fileIds"], "fileIds");
1465        return { fileIds: ids };
1466      }
1467      case "RELATION":
1468        // relations are written via the `links` field, never properties.
1469        return null;
1470      default:
1471        throw new ApiError("UNPROCESSABLE_ENTITY", 422, `Property type ${type} does not accept direct writes.`, [
1472          { issue: "read_only_property", property: type }
1473        ]);
1474    }
1475  }
1476 
1477  private asStringArray(raw: unknown, objectKeys: string[], label: string): string[] {
1478    let ids: unknown;
1479    if (Array.isArray(raw)) ids = raw;
1480    else if (typeof raw === "string") ids = [raw];
1481    else if (typeof raw === "object" && raw !== null) {
1482      ids = null;
1483      for (const k of objectKeys) {
1484        const v = (raw as Record<string, unknown>)[k];
1485        if (Array.isArray(v)) {
1486          ids = v;
1487          break;
1488        }
1489      }
1490    }
1491    if (ids === null || ids === undefined) ids = [];
1492    if (!Array.isArray(ids) || !ids.every((i) => typeof i === "string")) {
1493      throw ApiError.validation(`${label} array required.`, [{ issue: `${label}_required`, received: raw }]);
1494    }
1495    return ids;
1496  }
1497 
1498  private changedFields(body: UpdateRecordInput | BulkRecordsInput): string[] {
1499    const fields: string[] = [];
1500    if ("title" in body && body.title !== undefined) fields.push("title");
1501    if ("icon" in body && body.icon !== undefined) fields.push("icon");
1502    if ("coverUrl" in body && body.coverUrl !== undefined) fields.push("coverUrl");
1503    if ("archived" in body && body.archived !== undefined) fields.push("archived");
1504    if (body.properties) fields.push(...Object.keys(body.properties));
1505    if ("links" in body && body.links) fields.push(...Object.keys(body.links));
1506    return fields.length > 0 ? fields : ["*"];
1507  }
1508 
1509  // ---------- filter parsing ----------
1510 
1511  /** Parse a URL-encoded filter tree (§5.3) into a normalized tree. */
1512  private parseFilterTree(json: string): FilterNode | null {
1513    let tree: unknown;
1514    try {
1515      tree = JSON.parse(json);
1516    } catch {
1517      throw ApiError.validation("filter must be valid JSON.", [{ field: "filter", issue: "invalid_json" }]);
1518    }
1519    const root = this.normalizeTree(tree);
1520    const leaves: FilterNode[] = [];
1521    this.collectLeaves(root, leaves);
1522    if (leaves.length === 0) return null;
1523    return root;
1524  }
1525 
1526  /** Accept a bare leaf, a leaf array, or a §5.3 and/or/not tree. */
1527  private normalizeTree(tree: unknown): FilterNode {
1528    if (Array.isArray(tree)) {
1529      if (tree.length === 0) throw ApiError.validation("filter must not be empty.", [{ field: "filter", issue: "empty" }]);
1530      return { op: "and", conditions: tree.map((t) => this.normalizeTree(t)) };
1531    }
1532    if (typeof tree !== "object" || tree === null) {
1533      throw ApiError.validation("filter must be an object or array.", [{ field: "filter", issue: "shape" }]);
1534    }
1535    const t = tree as Record<string, unknown>;
1536    if (t.op === "and" || t.op === "or") {
1537      if (!Array.isArray(t.conditions)) {
1538        throw ApiError.validation(`${t.op} node requires a conditions array.`, [
1539          { field: "filter", issue: "conditions_required", received: t.op }
1540        ]);
1541      }
1542      return { op: t.op, conditions: t.conditions.map((c) => this.normalizeTree(c)) };
1543    }
1544    if (t.op === "not") {
1545      if (!Array.isArray(t.conditions) || t.conditions.length !== 1) {
1546        throw ApiError.validation("not node requires exactly one condition.", [
1547          { field: "filter", issue: "not_requires_one" }
1548        ]);
1549      }
1550      return { op: "not", conditions: [this.normalizeTree(t.conditions[0])] };
1551    }
1552    if (typeof t.propertyId === "string" && typeof t.op === "string") {
1553      return {
1554        propertyId: t.propertyId,
1555        op: this.normalizeOp(t.op),
1556        ...(t.value !== undefined ? { value: t.value } : {})
1557      };
1558    }
1559    throw ApiError.validation("filter node must be a condition or and/or/not node.", [
1560      { field: "filter", issue: "unknown_node" }
1561    ]);
1562  }
1563 
1564  /** Map docs §5.3 op names onto the filter builder's FILTER_OPS. */
1565  private normalizeOp(op: string): string {
1566    return OP_ALIASES[op] ?? op;
1567  }
1568 
1569  /** Count leaf conditions (any branch) — used to reject empty trees. */
1570  private collectLeaves(node: FilterNode, out: FilterNode[]): void {
1571    if (node.propertyId !== undefined) {
1572      out.push(node);
1573      return;
1574    }
1575    for (const child of node.conditions ?? []) this.collectLeaves(child, out);
1576  }
1577 
1578  /**
1579   * Compile the SQL-pre-filterable subset: `and`-reachable leaves, with
1580   * values unwrapped to the lib's raw shape. `or`/`not` subtrees and
1581   * JS-only ops are skipped here (re-checked in JS over the row set).
1582   */
1583  private compileWhere(root: FilterNode | null, byId: Map<string, PropertyMeta>): Record<string, unknown> {
1584    if (!root) return {};
1585    const leaves: FilterLeaf[] = [];
1586    this.collectAndLeaves(root, leaves);
1587    if (leaves.length === 0) return {};
1588    const propertyTypes: Record<string, string> = {};
1589    for (const leaf of leaves) propertyTypes[leaf.propertyId] = byId.get(leaf.propertyId)?.type ?? "TEXT";
1590    return buildRecordFilter(leaves as PropertyFilter[], propertyTypes);
1591  }
1592 
1593  private collectAndLeaves(node: FilterNode, out: FilterLeaf[]): void {
1594    if (node.propertyId !== undefined) {
1595      const op = node.op ?? "eq"; // same default as evalLeaf
1596      // Every leaf is validated by buildValueCondition (unknown op / bad
1597      // type / bad value → 400 with a message). No op skips validation — a
1598      // filter that the SQL fragment can't express still has to be a well-
1599      // formed one. Envelopes that simplifyValue would mangle (between's
1600      // {start,end}) are passed through raw so the validator sees the full
1601      // value; everything else is unwrapped to the raw scalar.
1602      out.push({
1603        propertyId: node.propertyId,
1604        op,
1605        value: RAW_ENVELOPE_OPS.has(op) ? node.value : this.simplifyValue(node.value)
1606      });
1607      return;
1608    }
1609    if ((node.op ?? "and") !== "and") return; // or/not handled in JS
1610    for (const child of node.conditions ?? []) this.collectAndLeaves(child, out);
1611  }
1612 
1613  /**
1614   * Unwrap docs §5.3 value envelopes ({text}, {value}, {optionId},
1615   * {start}, …) to the raw shape buildValueCondition expects.
1616   */
1617  private simplifyValue(value: unknown): unknown {
1618    if (typeof value !== "object" || value === null || Array.isArray(value)) return value;
1619    const v = value as Record<string, unknown>;
1620    if ("text" in v) return String(v.text);
1621    if ("optionId" in v) return String(v.optionId);
1622    if ("start" in v) return v.start as unknown;
1623    if ("value" in v) return v.value;
1624    return value;
1625  }
1626 
1627  /**
1628   * JS evaluation of the parts SQL cannot express (or/not trees,
1629   * contains_any/all, and re-validation of the and-part so the bounded
1630   * row set is exact).
1631   */
1632  private applyDisjunction(rows: RowT[], root: FilterNode | null, byId: Map<string, PropertyMeta>, userId: string): RowT[] {
1633    // `root` is the single normalized §5.3 tree (or null) — NOT an array.
1634    if (!root) return rows;
1635    return rows.filter((row) => this.nodeMatches(row, root, byId, userId));
1636  }
1637 
1638  private nodeMatches(row: RowT, node: FilterNode, byId: Map<string, PropertyMeta>, userId: string): boolean {
1639    if (node.propertyId !== undefined) {
1640      const prop = byId.get(node.propertyId);
1641      // Legacy name-based option refs resolve to the deterministic ids (the
1642      // toDto side does the same) so a filter on an option NAME written before
1643      // the id fix still matches records stored with the id (and vice versa).
1644      const value = this.canonicalOptionValue(
1645        this.propertyValue(row, byId, node.propertyId),
1646        prop?.type ?? "TEXT",
1647        prop?.config
1648      );
1649      return this.leafMatches(node, value, prop?.type ?? "TEXT", userId, prop?.config);
1650    }
1651    const op = node.op ?? "and";
1652    const children = node.conditions ?? [];
1653    if (op === "and") return children.every((c) => this.nodeMatches(row, c, byId, userId));
1654    if (op === "or") return children.length > 0 && children.some((c) => this.nodeMatches(row, c, byId, userId));
1655    if (op === "not") return children.length > 0 && !this.nodeMatches(row, children[0]!, byId, userId);
1656    return true;
1657  }
1658 
1659  /** JS mirror of buildValueCondition semantics for the docs' op set. */
1660  private leafMatches(node: FilterNode, value: unknown, type: string, userId: string, config?: unknown): boolean {
1661    const op = node.op;
1662    const expected = this.simplifyValue(node.value);
1663    const options = this.optionsOf(config);
1664    // A filter written with an option NAME (legacy) must match records stored
1665    // with the id, and vice versa — canonicalize the expected scalar the same
1666    // way the row value was canonicalized (nodeMatches).
1667    const expectedRef =
1668      (type === "SELECT" || type === "STATUS" || type === "MULTI_SELECT") &&
1669      (typeof expected === "string" || Array.isArray(expected))
1670        ? type === "MULTI_SELECT"
1671          ? (expected as unknown[]).map((x) => this.resolveOptionRef(x, options))
1672          : this.resolveOptionRef(expected, options)
1673        : expected;
1674    switch (op) {
1675      case "eq": {
1676        if (this.isEmptyValue(value)) return false;
1677        return this.scalarEquals(this.actualScalar(value, type), expectedRef);
1678      }
1679      case "neq": {
1680        if (this.isEmptyValue(value)) return false; // lib semantics: neq excludes null
1681        return !this.scalarEquals(this.actualScalar(value, type), expectedRef);
1682      }
1683      case "isEmpty":
1684        return this.isEmptyValue(value);
1685      case "isNotEmpty":
1686        return !this.isEmptyValue(value);
1687      case "contains":
1688      case "ncontains": {
1689        // Select-family values: "contains" on the option NAME(s) (the UI
1690        // shows names); every other type compares the plain text rendering.
1691        let mine = this.toText(value, type) ?? "";
1692        if (type === "SELECT" || type === "STATUS" || type === "MULTI_SELECT") mine = this.selectLabelText(value, type, config) ?? "";
1693        const needle = typeof expectedRef === "string" ? expectedRef : String(expectedRef ?? "");
1694        const hit = mine.toLowerCase().includes(needle.toLowerCase());
1695        return op === "contains" ? hit : !hit;
1696      }
1697      case "contains_any":
1698      case "contains_all": {
1699        const mine = this.toIdArray(value);
1700        const raw = Array.isArray(expectedRef) ? (expectedRef as string[]) : typeof expectedRef === "string" ? [expectedRef] : [];
1701        const target = raw.flatMap((t) => this.optionMatchSet(t, config));
1702        if (target.length === 0) return op === "contains_any" ? false : true;
1703        return op === "contains_any"
1704          ? target.some((t) => mine.includes(t))
1705          : target.every((t) => mine.includes(t));
1706      }
1707      case "gte":
1708      case "gt":
1709      case "lte":
1710      case "lt":
1711      case "after":
1712      case "before": {
1713        if (type === "DATE" || type === "DATE_RANGE") {
1714          const a = this.toDateMs(value, type);
1715          const b = typeof expected === "string" || typeof expected === "number" ? new Date(expected).getTime() : NaN;
1716          if (a === null || Number.isNaN(b)) return false;
1717          return this.compareByOp(op, a, b);
1718        }
1719        const a = this.toNumber(value);
1720        const b = typeof expected === "number" ? expected : Number(expected);
1721        if (a === null || Number.isNaN(b)) return false;
1722        return this.compareByOp(op, a, b);
1723      }
1724      case "startsWith":
1725      case "endsWith": {
1726        const mine = this.toText(value, type) ?? "";
1727        const needle = typeof expected === "string" ? expected : String(expected ?? "");
1728        const hay = mine.toLowerCase();
1729        const n = needle.toLowerCase();
1730        if (n.length === 0) return true;
1731        return op === "startsWith" ? hay.startsWith(n) : hay.endsWith(n);
1732      }
1733      case "between": {
1734        if (type !== "DATE" && type !== "DATE_RANGE") return false;
1735        const a = this.toDateMs(value, type);
1736        // The web sends a raw {start, end} envelope; simplifyValue would drop
1737        // `end`, so read the raw node value for the range bounds.
1738        const range = (node.value ?? {}) as { start?: unknown; end?: unknown };
1739        const s = typeof range.start === "string" || typeof range.start === "number" ? new Date(String(range.start)).getTime() : NaN;
1740        const e = typeof range.end === "string" || typeof range.end === "number" ? new Date(String(range.end)).getTime() : NaN;
1741        if (a === null || Number.isNaN(s) || Number.isNaN(e)) return false;
1742        return a >= s && a <= e;
1743      }
1744      case "isThisWeek":
1745      case "isThisMonth":
1746      case "isPast":
1747      case "isFuture": {
1748        if (type !== "DATE" && type !== "DATE_RANGE") return false;
1749        const a = this.toDateMs(value, type);
1750        if (a === null) return false;
1751        const now = Date.now();
1752        if (op === "isPast") return a < now;
1753        if (op === "isFuture") return a > now;
1754        if (op === "isThisMonth") {
1755          const d = new Date(a);
1756          const n = new Date(now);
1757          return d.getFullYear() === n.getFullYear() && d.getMonth() === n.getMonth();
1758        }
1759        // isThisWeek — ISO week, Monday-start (matches the UI's mental model).
1760        const d = new Date(a);
1761        const day = (d.getDay() + 6) % 7; // Mon=0
1762        const weekStart = new Date(d);
1763        weekStart.setHours(0, 0, 0, 0);
1764        weekStart.setDate(d.getDate() - day);
1765        return a >= weekStart.getTime() && a < weekStart.getTime() + 7 * 86400000;
1766      }
1767      case "withinNextDays": {
1768        if (type !== "DATE" && type !== "DATE_RANGE") return false;
1769        const a = this.toDateMs(value, type);
1770        // The web sends {days: N}; simplifyValue leaves that envelope intact,
1771        // so unwrap it here (also accept a bare number for the docs' shape).
1772        const nRaw = (expected as { days?: unknown })?.days ?? expected;
1773        const n = typeof nRaw === "number" ? nRaw : Number(nRaw);
1774        if (a === null || Number.isNaN(n)) return false;
1775        const now = Date.now();
1776        return a >= now && a <= now + n * 86400000;
1777      }
1778      case "isCurrentUser": {
1779        if (type !== "PERSON") return false;
1780        const mine = this.toIdArray(value);
1781        return mine.includes(userId);
1782      }
1783      default:
1784        return true; // unknown ops are rejected at normalize/compile time
1785    }
1786  }
1787 
1788  private compareByOp(op: string, a: number, b: number): boolean {
1789    switch (op) {
1790      case "gte":
1791      case "after":
1792        return a >= b;
1793      case "gt":
1794        return a > b;
1795      case "lte":
1796      case "before":
1797        return a <= b;
1798      default:
1799        return a < b;
1800    }
1801  }
1802 
1803  private scalarEquals(a: unknown, b: unknown): boolean {
1804    if (a === b) return true;
1805    if (typeof a === "number" && typeof b === "string") return a === Number(b);
1806    if (typeof b === "number" && typeof a === "string") return Number(a) === b;
1807    if (Array.isArray(a) && Array.isArray(b)) {
1808      return JSON.stringify([...a].sort()) === JSON.stringify([...b].sort());
1809    }
1810    return JSON.stringify(a) === JSON.stringify(b);
1811  }
1812 
1813  /**
1814   * Reduce a stored JSONB value to the comparison-relevant scalar for its
1815   * property type (text → string, number → number, select → optionId, …).
1816   */
1817  private actualScalar(value: unknown, type: string): unknown {
1818    if (value === null || value === undefined) return null;
1819    switch (type) {
1820      case "TITLE":
1821      case "TEXT":
1822      case "RICH_TEXT":
1823      case "EMAIL":
1824      case "PHONE":
1825      case "URL":
1826        return this.toText(value, type);
1827      case "NUMBER":
1828        return this.toNumber(value);
1829      case "CHECKBOX":
1830        return typeof value === "boolean" ? value : (value as { value?: boolean })?.value ?? null;
1831      case "SELECT":
1832      case "STATUS":
1833        return (value as { optionId?: string })?.optionId ?? null;
1834      case "MULTI_SELECT":
1835      case "PERSON":
1836      case "FILES":
1837      case "RELATION":
1838        return this.toIdArray(value);
1839      case "DATE":
1840      case "DATE_RANGE": {
1841        const start = (value as { start?: string })?.start;
1842        return typeof start === "string" ? start : null;
1843      }
1844      default:
1845        return value;
1846    }
1847  }
1848 
1849  private isEmptyValue(value: unknown): boolean {
1850    if (value === null || value === undefined) return true;
1851    if (typeof value === "string") return value === "";
1852    if (Array.isArray(value)) return value.length === 0;
1853    if (typeof value === "object") {
1854      for (const v of Object.values(value as Record<string, unknown>)) {
1855        if (!this.isEmptyValue(v)) return false;
1856      }
1857      return true;
1858    }
1859    return false;
1860  }
1861 
1862  private toIdArray(value: unknown): string[] {
1863    if (Array.isArray(value)) return value as string[];
1864    if (typeof value === "object" && value !== null) {
1865      for (const k of ["optionIds", "userIds", "fileIds", "recordIds", "ids"]) {
1866        const v = (value as Record<string, unknown>)[k];
1867        if (Array.isArray(v)) return v as string[];
1868      }
1869    }
1870    return typeof value === "string" ? [value] : [];
1871  }
1872 
1873  private toNumber(value: unknown): number | null {
1874    if (typeof value === "number") return value;
1875    if (typeof value === "string" && value !== "" && !Number.isNaN(Number(value))) return Number(value);
1876    if (typeof value === "object" && value !== null) {
1877      const v = (value as Record<string, unknown>).value;
1878      if (typeof v === "number") return v;
1879      if (typeof v === "string" && v !== "" && !Number.isNaN(Number(v))) return Number(v);
1880    }
1881    return null;
1882  }
1883 
1884  private toDateMs(value: unknown, type: string): number | null {
1885    let raw: unknown;
1886    if (type === "DATE_RANGE" || (typeof value === "object" && value !== null)) {
1887      raw = (value as { start?: unknown })?.start;
1888    } else {
1889      raw = value;
1890    }
1891    if (typeof raw === "string" && !Number.isNaN(new Date(raw).getTime())) return new Date(raw).getTime();
1892    if (typeof raw === "number" && !Number.isNaN(new Date(raw).getTime())) return new Date(raw).getTime();
1893    return null;
1894  }
1895 
1896  private toText(value: unknown, type: string, config?: unknown): string | null {
1897    if (value === null || value === undefined) return null;
1898    // Select-family values store `{ optionId }` — the plain rendering is the
1899    // option NAME (without this, CSV export / sort fell through to
1900    // `String(value)` → "[object Object]").
1901    if (type === "SELECT" || type === "STATUS" || type === "MULTI_SELECT") {
1902      return this.selectLabelText(value, type, config);
1903    }
1904    if (typeof value === "string") return value;
1905    if (typeof value === "object") {
1906      const v = value as Record<string, unknown>;
1907      if (type === "TITLE" || type === "TEXT" || type === "RICH_TEXT") {
1908        if (typeof v.text === "string") return v.text;
1909      }
1910      if (typeof v.value === "string") return v.value;
1911    }
1912    return String(value);
1913  }
1914 
1915  // ---------- sorting / grouping / values ----------
1916 
1917  private parseSort(json: string): SortClause[] {
1918    const parsed = this.parseJsonArray(json, "sort");
1919    return parsed.map((s) => {
1920      if (typeof s !== "object" || s === null || typeof (s as { propertyId?: unknown }).propertyId !== "string") {
1921        throw ApiError.validation("sort entries need a propertyId.", [
1922          { field: "sort", issue: "propertyId_required", received: s }
1923        ]);
1924      }
1925      const dir = (s as { direction?: string; dir?: string }).direction ?? (s as { dir?: string }).dir ?? "asc";
1926      if (dir !== "asc" && dir !== "desc") {
1927        throw ApiError.validation("sort direction must be asc or desc.", [{ field: "sort", issue: "direction", received: dir }]);
1928      }
1929      return { propertyId: (s as { propertyId: string }).propertyId, direction: dir };
1930    });
1931  }
1932 
1933  private parseGroup(json: string): GroupClause[] {
1934    let parsed: unknown;
1935    try {
1936      parsed = JSON.parse(json);
1937    } catch {
1938      throw ApiError.validation("group must be valid JSON.", [{ field: "group", issue: "invalid_json" }]);
1939    }
1940    const arr = Array.isArray(parsed) ? parsed : [parsed];
1941    return arr
1942      .map((g) => {
1943        if (typeof g !== "object" || g === null || typeof (g as { propertyId?: unknown }).propertyId !== "string") {
1944          throw ApiError.validation("group entries need a propertyId.", [
1945            { field: "group", issue: "propertyId_required", received: g }
1946          ]);
1947        }
1948        return { propertyId: (g as { propertyId: string }).propertyId };
1949      })
1950      .filter((g) => Boolean(g.propertyId));
1951  }
1952 
1953  private parseJsonArray(json: string, field: string): unknown[] {
1954    let parsed: unknown;
1955    try {
1956      parsed = JSON.parse(json);
1957    } catch {
1958      throw ApiError.validation(`${field} must be valid JSON.`, [{ field, issue: "invalid_json" }]);
1959    }
1960    if (!Array.isArray(parsed) || parsed.length === 0) {
1961      throw ApiError.validation(`${field} must be a non-empty array.`, [{ field, issue: "array_required" }]);
1962    }
1963    return parsed;
1964  }
1965 
1966  private asFilters(json: unknown): FilterNode | null {
1967    if (!json) return null;
1968    try {
1969      return this.normalizeTree(typeof json === "string" ? JSON.parse(json) : json);
1970    } catch {
1971      return null;
1972    }
1973  }
1974 
1975  private asSort(json: unknown): SortClause[] | null {
1976    if (!json) return null;
1977    try {
1978      const parsed: unknown = typeof json === "string" ? JSON.parse(json) : json;
1979      if (!Array.isArray(parsed) || parsed.length === 0) return null;
1980      return (parsed as { propertyId: string; direction?: string }[]).map((s) => ({
1981        propertyId: s.propertyId,
1982        direction: s.direction === "desc" ? "desc" : "asc"
1983      }));
1984    } catch {
1985      return null;
1986    }
1987  }
1988 
1989  private asGroup(json: unknown): GroupClause[] | null {
1990    if (!json) return null;
1991    try {
1992      const parsed: unknown = typeof json === "string" ? JSON.parse(json) : json;
1993      const arr = Array.isArray(parsed) ? parsed : [parsed];
1994      return arr
1995        .map((g) => ({ propertyId: (g as { propertyId?: string }).propertyId ?? "" }))
1996        .filter((g) => Boolean(g.propertyId));
1997    } catch {
1998      return null;
1999    }
2000  }
2001 
2002  private propertyValue(row: RowT, byId: Map<string, PropertyMeta>, propertyId: string): unknown {
2003    const prop = byId.get(propertyId);
2004    if (prop?.isTitle || prop?.type === "TITLE") return row.page?.title ?? "";
2005    const rp = row.properties.find((p) => p.propertyId === propertyId);
2006    return rp ? rp.value : null;
2007  }
2008 
2009  /** Sort comparator: missing values always last, direction-aware. */
2010  private applySort(rows: RowT[], sort: SortClause[] | null, byId: Map<string, PropertyMeta>): RowT[] {
2011    if (!sort || sort.length === 0) return rows;
2012    return [...rows].sort((a, b) => {
2013      for (const clause of sort) {
2014        const va = this.propertyValue(a, byId, clause.propertyId);
2015        const vb = this.propertyValue(b, byId, clause.propertyId);
2016        const ma = this.isEmptyValue(va);
2017        const mb = this.isEmptyValue(vb);
2018        let cmp = 0;
2019        if (ma && mb) cmp = 0;
2020        else if (ma) cmp = 1; // missing last regardless of direction
2021        else if (mb) cmp = -1;
2022        else {
2023          const na = this.toNumber(va);
2024          const nb = this.toNumber(vb);
2025          if (na !== null && nb !== null) {
2026            cmp = na < nb ? -1 : na > nb ? 1 : 0;
2027          } else {
2028            const meta = byId.get(clause.propertyId);
2029            const type = meta?.type ?? "TEXT";
2030            const ta = this.toText(va, type, meta?.config) ?? "";
2031            const tb = this.toText(vb, type, meta?.config) ?? "";
2032            cmp = ta < tb ? -1 : ta > tb ? 1 : 0;
2033          }
2034        }
2035        if (cmp !== 0) return clause.direction === "desc" ? -cmp : cmp;
2036      }
2037      return compare(a.position, b.position);
2038    });
2039  }
2040 
2041  private groupKey(value: unknown): string {
2042    if (value === null || value === undefined) return "__empty__";
2043    return JSON.stringify(value);
2044  }
2045 
2046  private groupLabel(propertyId: string, value: unknown, byId: Map<string, PropertyMeta>): string {
2047    const type = byId.get(propertyId)?.type ?? "TEXT";
2048    if (this.isEmptyValue(value)) return "(empty)";
2049    // Select-family values: the option NAME (the id is internal); everything
2050    // else keeps the plain-text rendering.
2051    const text =
2052      type === "SELECT" || type === "STATUS" || type === "MULTI_SELECT"
2053        ? this.selectLabelText(value, type, byId.get(propertyId)?.config)
2054        : this.toText(value, type);
2055    if (text) return text.length > 60 ? text.slice(0, 60) + "…" : text;
2056    return this.groupKey(value);
2057  }
2058 
2059  private exportValue(row: RowT, byId: Map<string, PropertyMeta>, propertyId: string): string {
2060    const type = byId.get(propertyId)?.type ?? "TEXT";
2061    const value = this.propertyValue(row, byId, propertyId);
2062    if (this.isEmptyValue(value)) return "";
2063    if (type === "PERSON" || type === "FILES" || type === "RELATION" || type === "MULTI_SELECT") {
2064      const ids = this.toIdArray(value);
2065      if (ids.length > 0) return ids.join(" | ");
2066    }
2067    return this.toText(value, type) ?? this.groupKey(value);
2068  }
2069 
2070  private csvCell(value: string): string {
2071    if (/[",\n\r]/.test(value)) return `"${value.replace(/"/g, '""')}"`;
2072    return value;
2073  }
2074 
2075  // ---------- relations ----------
2076 
2077  /**
2078   * Expand each row's RelationLinks into `{ [targetDatabaseTitle]: targetRecordTitles[] }`,
2079   * keyed by the target database's title (RelationLink rows carry the target
2080   * database/record ids only). Rows are expected to come from one database.
2081   */
2082  private async buildRelations(rows: RowT[]): Promise<Map<string, Record<string, string[]>>> {
2083    const result = new Map<string, Record<string, string[]>>();
2084    for (const row of rows) result.set(row.id, {});
2085    const recordIds = rows.map((r) => r.id);
2086    if (recordIds.length === 0) return result;
2087 
2088    const links = await this.prisma.relationLink.findMany({
2089      where: { recordId: { in: recordIds } },
2090      select: { recordId: true, targetRecordId: true, targetDatabaseId: true }
2091    });
2092    if (links.length === 0) return result;
2093 
2094    const targetDbIds = [...new Set(links.map((l) => l.targetDatabaseId))];
2095    const targetDbs = await this.prisma.database.findMany({
2096      where: { id: { in: targetDbIds } },
2097      select: { id: true, title: true }
2098    });
2099    const dbTitle = new Map(targetDbs.map((d) => [d.id, d.title || "Linked"]));
2100 
2101    const targetRecordIds = [...new Set(links.map((l) => l.targetRecordId))];
2102    const targets = await this.prisma.databaseRecord.findMany({
2103      where: { id: { in: targetRecordIds } },
2104      include: { page: { select: { title: true } } }
2105    });
2106    const targetTitle = new Map(targets.map((t) => [t.id, t.page?.title || "(untitled)"]));
2107 
2108    for (const link of links) {
2109      const name = dbTitle.get(link.targetDatabaseId) ?? "Linked";
2110      const title = targetTitle.get(link.targetRecordId) ?? "(deleted)";
2111      const bucket = result.get(link.recordId);
2112      if (!bucket) continue;
2113      (bucket[name] ??= []).push(title);
2114    }
2115    return result;
2116  }
2117 
2118  // ---------- DTO ----------
2119 
2120  private toDto(
2121    row: RowT,
2122    byId: Map<string, PropertyMeta>,
2123    relations?: Map<string, Record<string, string[]>>,
2124    relLinkRecords?: Map<string, Record<string, string[]>>
2125  ): Record<string, unknown> {
2126    const properties: Record<string, unknown> = {};
2127    for (const p of byId.values()) {
2128      if (p.isTitle || p.type === "TITLE") {
2129        properties[p.id] = { text: row.page?.title ?? "", marks: [] };
2130      } else if (p.type === "RELATION") {
2131        // Relation values are NOT stored in `DatabaseRecordProperty` — they
2132        // live in the `RelationLink` junction table (written via the `links`
2133        // field by create/update). Without this synthesis every relation
2134        // property would read back `null`, so created links were invisible in
2135        // the table (and to `getRecord` / a post-link update). The link map
2136        // keys recordId → { propertyId → targetRecordId[] }.
2137        const links = relLinkRecords?.get(row.id);
2138        properties[p.id] =
2139          links && Array.isArray(links[p.id]) && links[p.id].length > 0
2140            ? { recordIds: links[p.id] }
2141            : null;
2142      } else {
2143        const rp = row.properties.find((x) => x.propertyId === p.id);
2144        const value = rp ? rp.value : null;
2145        // Legacy name-based option refs resolve to the deterministic ids the
2146        // service assigns on read (chips / preselect / board columns then
2147        // match by id). No-op for modern id refs.
2148        properties[p.id] = this.canonicalOptionValue(value, p.type, p.config);
2149      }
2150    }
2151    return {
2152      id: row.id,
2153      workspaceId: row.workspaceId,
2154      databaseId: row.databaseId,
2155      pageId: row.pageId,
2156      title: row.page?.title ?? "",
2157      icon: row.icon,
2158      coverUrl: row.coverUrl,
2159      position: row.position,
2160      version: row.version,
2161      archivedAt: row.archivedAt,
2162      createdTime: row.createdTime,
2163      lastEditedTime: row.lastEditedTime,
2164      createdBy: row.createdBy,
2165      lastEditedBy: row.lastEditedBy,
2166      properties,
2167      ...(relations?.has(row.id) ? { relations: relations.get(row.id) } : {})
2168    };
2169  }
2170 
2171  /**
2172   * Batch-load the relation links for a set of records and map them to their
2173   * property ids. Relation values live in the `RelationLink` junction table,
2174   * NOT in `DatabaseRecordProperty` (the `properties` include). Without this
2175   * synthesis every relation property reads back `null`, so a freshly created
2176   * relation + link was invisible in the table and in `getRecord`. The
2177   * `Relation` row is the authoritative propertyId ↔ targetDatabaseId link
2178   * (the `reversePropertyId` resolves the two-way counterpart, which shares
2179   * the same junction rows). One indexed query for the links + one for the
2180   * relations — no per-record N+1.
2181   */
2182  private async buildRelationLinkRecords(
2183    rows: RowT[],
2184    byId: Map<string, PropertyMeta>,
2185    client: PrismaClient = this.prisma.read()
2186  ): Promise<Map<string, Record<string, string[]>>> {
2187    const result = new Map<string, Record<string, string[]>>();
2188    const recordIds = rows.map((r) => r.id);
2189    if (recordIds.length === 0) return result;
2190    // Only databases that actually have a RELATION property need the query.
2191    const relationPropIds = [...byId.values()]
2192      .filter((p) => p.type === "RELATION")
2193      .map((p) => p.id);
2194    if (relationPropIds.length === 0) return result;
2195    const databaseId = rows[0]!.databaseId;
2196    const links = await client.relationLink.findMany({
2197      where: { databaseId, recordId: { in: recordIds } },
2198      select: { recordId: true, targetRecordId: true, targetDatabaseId: true }
2199    });
2200    if (links.length === 0) return result;
2201    const targetDbIds = [...new Set(links.map((l) => l.targetDatabaseId))];
2202    const relations = await client.relation.findMany({
2203      where: { databaseId, targetDatabaseId: { in: targetDbIds } },
2204      select: { propertyId: true, reversePropertyId: true, targetDatabaseId: true }
2205    });
2206    const propForTarget = new Map<string, string[]>();
2207    for (const rel of relations) {
2208      for (const pid of [rel.propertyId, rel.reversePropertyId]) {
2209        if (!pid) continue;
2210        const arr = propForTarget.get(rel.targetDatabaseId);
2211        if (arr) arr.push(pid);
2212        else propForTarget.set(rel.targetDatabaseId, [pid]);
2213      }
2214    }
2215    for (const link of links) {
2216      const propIds = propForTarget.get(link.targetDatabaseId) ?? [];
2217      if (!result.has(link.recordId)) result.set(link.recordId, {});
2218      const bucket = result.get(link.recordId)!;
2219      for (const pid of propIds) {
2220        const arr = bucket[pid];
2221        if (arr) arr.push(link.targetRecordId);
2222        else bucket[pid] = [link.targetRecordId];
2223      }
2224    }
2225    return result;
2226  }
2227  // ---------- cursor (row-id based; stable under any sort) ----------
2228 
2229  private makeCursor(recordId: string): string {
2230    return Buffer.from(`id:${recordId}`, "utf8").toString("base64url");
2231  }
2232 
2233  /** Index after the cursor row (0 when the row no longer exists). */
2234  private cursorOffset(rows: RowT[], cursor: string): number {
2235    let recordId: string | null = null;
2236    try {
2237      const decoded = Buffer.from(cursor, "base64url").toString("utf8");
2238      recordId = decoded.startsWith("id:") ? decoded.slice(3) : null;
2239    } catch {
2240      recordId = null;
2241    }
2242    if (recordId === null) return 0;
2243    const idx = rows.findIndex((r) => r.id === recordId);
2244    return idx === -1 ? 0 : idx + 1;
2245  }
2246 
2247  // ---------- bulk ----------
2248 
2249  private async bulkUpdate(
2250    tx: Prisma.TransactionClient,
2251    targets: { id: string; pageId: string | null }[],
2252    byId: Map<string, PropertyMeta>,
2253    properties: Record<string, unknown>,
2254    userId: string
2255  ): Promise<void> {
2256    const patchKeys = Object.keys(properties);
2257    if (patchKeys.length === 0) {
2258      await tx.databaseRecord.updateMany({
2259        where: { id: { in: targets.map((t) => t.id) } },
2260        data: { version: { increment: 1 }, lastEditedBy: userId }
2261      });
2262      return;
2263    }
2264    for (const target of targets) {
2265      for (const propertyId of patchKeys) {
2266        const prop = byId.get(propertyId);
2267        if (!prop) continue;
2268        const value = this.coerceAndCanonical(properties[propertyId], prop);
2269        const json = value === null ? Prisma.JsonNull : (value as Prisma.InputJsonValue);
2270        await tx.recordProperty.upsert({
2271          where: { recordId_propertyId: { recordId: target.id, propertyId } },
2272          update: { value: json },
2273          create: { recordId: target.id, propertyId, value: json }
2274        });
2275      }
2276      await tx.databaseRecord.update({
2277        where: { id: target.id },
2278        data: { version: { increment: 1 }, lastEditedBy: userId }
2279      });
2280      if (target.pageId) {
2281        await tx.page.update({ where: { id: target.pageId }, data: { updatedBy: userId } });
2282      }
2283    }
2284  }
2285}
2286