use-database.ts

use-database.tsapps/web/src/hooks/use-database.tsraw ← hooks43,653 B← Back to the app
1"use client";
2 
3/**
4 * Data hooks for databases, properties, views and records.
5 *
6 * Wire shapes follow docs/api/databases.md (the canonical contract):
7 *  - GET    /databases/:id                        → database + properties + views
8 *  - POST   /databases/:id/query                  → filter/sort/group/paginate (server-side engine)
9 *  - GET    /databases/:id/records?viewId=...     → same, view-rendered (GET variant)
10 *  - POST   /databases/:id/records                → create record
11 *  - PATCH  /records/:recordId                    → update (If-Match optimistic concurrency)
12 *  - POST   /records/:recordId/archive|unarchive  → soft archive
13 *  - DELETE /records/:recordId                    → hard delete (confirm = recordId)
14 *  - POST/PATCH/DELETE /databases/:id/properties[/:propertyId]
15 *  - POST/PATCH/DELETE /databases/:id/views[/:viewId]
16 *  - POST   /databases/:id/formulas/validate      → { expression, sampleRecordIds }
17 *
18 * Envelope note: the API returns `{ data: … }`. `unwrap()` below accepts both
19 * the envelope and an already-unwrapped body so this file is independent of
20 * the exact apiFetch convention the core layer settles on.
21 */
22import { useCallback, useMemo } from "react";
23import {
24  useInfiniteQuery,
25  useMutation,
26  useQuery,
27  useQueryClient
28} from "@tanstack/react-query";
29import { apiFetch } from "@/lib/api";
30// The SAME deterministic option-id generator + config normalizer the server
31// uses (single source of truth). The web cannot depend on `@factory/ai`, so
32// this lives in `@factory/shared`.
33//
34// IMPORTANT: import the browser-safe `@factory/shared/dto` SUBPATH, not the
35// package ROOT (`@factory/shared`). The root barrel re-exports `crypto.ts`,
36// `events.ts`, `webhook-delivery.ts`, `env.ts`, … which import `node:crypto`
37// / `node:fs` — a root import here would pull those into the browser bundle
38// and fail the Next.js build with `UnhandledSchemeError: node:crypto`.
39import { normalizeSelectOptions } from "@factory/shared/dto";
40 
41// ---------------------------------------------------------------------------
42// Wire types (mirror of docs/api/databases.md + prisma schema enums)
43// ---------------------------------------------------------------------------
44 
45export type PropertyType =
46  | "title" | "text" | "rich_text" | "number" | "select" | "multi_select"
47  | "status" | "date" | "date_range" | "person" | "email" | "phone" | "url"
48  | "checkbox" | "files" | "created_time" | "created_by" | "last_edited_time"
49  | "last_edited_by" | "relation" | "rollup" | "formula" | "button" | "unique_id";
50 
51export type ViewType =
52  | "table" | "list" | "board" | "gallery" | "calendar" | "timeline"
53  | "chart" | "dashboard" | "form";
54 
55export interface SelectOption {
56  id: string;
57  name: string;
58  /** gray | brown | orange | yellow | green | blue | purple | pink | red | default */
59  color: string;
60}
61 
62/** Free-form per-type config (docs/api/databases.md §3.4). */
63export interface PropertyConfig extends Record<string, unknown> {
64  options?: SelectOption[];
65  format?: "number" | "decimal" | "currency" | "percent" | "scientific";
66  currency?: string;
67  precision?: number;
68  includeTime?: boolean;
69  timeZone?: string | null;
70  targetDatabaseId?: string;
71  cardinality?: "one_to_one" | "one_to_many" | "many_to_many";
72  bidirectional?: boolean;
73  reverseName?: string;
74  relationPropertyId?: string;
75  targetPropertyId?: string;
76  aggregate?: "COUNT" | "SUM" | "AVERAGE" | "MIN" | "MAX" | "PERCENT" | "UNIQUE";
77  expression?: string;
78  prefix?: string;
79  actions?: unknown[];
80  maxFiles?: number;
81  acceptedMimeTypes?: string[];
82  defaultCountry?: string;
83  maxLines?: number | null;
84  groups?: { name: string; optionIds: string[]; color: string }[];
85}
86 
87export interface DatabaseProperty {
88  id: string;
89  databaseId: string;
90  name: string;
91  type: PropertyType;
92  position: string;
93  isTitle: boolean;
94  isVisible: boolean;
95  required: boolean;
96  config: PropertyConfig;
97  default: unknown | null;
98  createdAt?: string;
99  updatedAt?: string;
100}
101 
102/** Typed per-type value (docs/api/databases.md §3.4 "Stored value shape"). */
103export interface RecordValue {
104  text?: string;
105  marks?: unknown[];
106  value?: number | string | boolean;
107  optionId?: string;
108  optionIds?: string[];
109  userIds?: string[];
110  userId?: string;
111  start?: string | null;
112  end?: string | null;
113  dateTimeTimezone?: string | null;
114  fileIds?: string[];
115  recordIds?: string[];
116  title?: string;
117  type?: string;
118  [key: string]: unknown;
119}
120 
121export interface RecordDto {
122  id: string;
123  workspaceId: string;
124  databaseId: string;
125  pageId: string | null;
126  icon: string | null;
127  coverUrl: string | null;
128  archivedAt: string | null;
129  isSubItem: boolean;
130  position: string;
131  version: number;
132  createdBy: string | null;
133  createdTime: string;
134  lastEditedBy: string | null;
135  lastEditedTime: string;
136  /** Record title (the title property's text) — the API returns it top-level
137   *  (`toDto`: `title: page?.title`). Used by relation chips + record links. */
138  title?: string;
139  /** propertyId → typed value */
140  properties: Record<string, RecordValue>;
141  parentRecordId?: string | null;
142  subItemCount?: number;
143}
144 
145export interface FilterCondition {
146  propertyId: string;
147  op: string;
148  value?: RecordValue | Record<string, unknown>;
149}
150export interface FilterGroup {
151  op: "and" | "or" | "not";
152  conditions: (FilterGroup | FilterCondition)[];
153}
154export type FilterTree = FilterGroup | FilterCondition;
155 
156export interface SortSpec {
157  propertyId: string;
158  direction: "asc" | "desc";
159}
160export interface GroupSpec {
161  propertyId: string;
162  direction: "asc" | "desc";
163}
164 
165export interface ChartConfig {
166  type: "bar" | "doughnut" | "line";
167  /** numeric property to aggregate (number/rollup/formula) */
168  property: string;
169  /** property to bucket by (select/multi_select/status/person/date); omit = single bucket */
170  groupBy?: string;
171  aggregation?: "COUNT" | "SUM" | "AVERAGE" | "MIN" | "MAX";
172}
173 
174export interface DatabaseButtonDef {
175  id?: string;
176  name: string;
177  icon?: string;
178  color?: string;
179  /** optional prompt shown before execution */
180  prompt?: string;
181  action:
182    | { type: "update"; propertyId: string; value: RecordValue }
183    | { type: "create"; template?: Record<string, RecordValue> }
184    | { type: "navigate"; url?: string; pageId?: string }
185    | { type: "formula" };
186}
187 
188export interface ViewConfig extends Record<string, unknown> {
189  visibleProperties?: string[];
190  cardProperties?: string[];
191  columnWidths?: Record<string, number>;
192  headerRow?: boolean;
193  zebra?: boolean;
194  showIcons?: boolean;
195  subtitlePropertyId?: string;
196  cardLayout?: "cover" | "title" | "compact";
197  columnWidthPx?: number;
198  coverPropertyId?: string;
199  cardsPerRow?: number;
200  cardRatio?: string;
201  startDatePropertyId?: string;
202  endDatePropertyId?: string;
203  weekStartsOn?: number;
204  monthDensity?: string;
205  scale?: "day" | "week" | "month" | "quarter";
206  lanePropertyId?: string;
207  chartConfig?: ChartConfig;
208  chartId?: string;
209  formId?: string;
210  buttons?: DatabaseButtonDef[];
211  color?: string | null;
212  density?: "compact" | "comfortable" | "spacious";
213  intro?: string;
214  successMessage?: string;
215  branding?: Record<string, unknown>;
216}
217 
218export interface DatabaseView {
219  id: string;
220  databaseId: string;
221  name: string;
222  type: ViewType;
223  position: string;
224  config: ViewConfig;
225  filter: FilterTree | null;
226  sort: SortSpec[] | null;
227  group: GroupSpec[] | null;
228  pageSize: number | null;
229  recordCount?: number;
230  createdAt?: string;
231  updatedAt?: string;
232}
233 
234export interface DatabaseDto {
235  id: string;
236  workspaceId: string;
237  pageId: string | null;
238  title: string;
239  icon: string | null;
240  description: string | null;
241  schemaVersion: number;
242  isArchived: boolean;
243  recordCount?: number;
244  createdBy?: string | null;
245  createdAt: string;
246  updatedAt?: string;
247  properties: DatabaseProperty[];
248  views: DatabaseView[];
249  buttons?: DatabaseButtonDef[];
250}
251 
252export interface RecordGroup {
253  key: { propertyId: string; value: RecordValue; label: string };
254  count: number;
255  records: RecordDto[];
256}
257 
258export interface QueryResult {
259  records: RecordDto[];
260  groups: RecordGroup[] | null;
261  aggregates: Record<string, unknown> | null;
262  totalCount: number | null;
263  nextCursor: string | null;
264}
265 
266// ---------------------------------------------------------------------------
267// Small value helpers (shared by cells, CSV export, chart, search-in-view)
268// ---------------------------------------------------------------------------
269 
270function unwrap<T>(body: unknown): T {
271  const b = body as { data?: T } | null;
272  if (b && typeof b === "object" && "data" in (b as Record<string, unknown>)) {
273    return (b as { data: T }).data;
274  }
275  return body as T;
276}
277 
278export function titlePropertyOf(properties: DatabaseProperty[]): DatabaseProperty | undefined {
279  return properties.find((p) => p.isTitle) ?? properties.find((p) => p.type === "title");
280}
281 
282export function recordTitle(record: RecordDto, properties: DatabaseProperty[]): string {
283  const t = titlePropertyOf(properties);
284  if (!t) return "Untitled";
285  const v = record.properties[t.id];
286  if (!v) return "Untitled";
287  if (typeof v.text === "string") return v.text || "Untitled";
288  if (typeof v.value === "string") return v.value || "Untitled";
289  return "Untitled";
290}
291 
292/**
293 * Tolerant option lookup — id first, then NAME.
294 *
295 * The API contract is `{ optionId: <option.id> }` and the server now
296 * guarantees option ids on read (select/status/multi-select config + stored
297 * values are canonicalized). A stored option NAME can still reach the client
298 * through optimistic merges and realtime payloads, so every consumer resolves
299 * a ref against BOTH the id and the name — legacy chips and preselects render
300 * instead of blanking.
301 */
302export function findOption(options: SelectOption[] | undefined | null, ref: unknown): SelectOption | undefined {
303  if (typeof ref !== "string" || ref === "") return undefined;
304  if (!options) return undefined;
305  return options.find((o) => o.id === ref) ?? options.find((o) => o.name === ref);
306}
307 
308/**
309 * Build the URL of a record/database page from the CURRENT pathname.
310 *
311 * The only page route is `/w/[workspace]/page/[id]` (app/(app)/w/[workspace]/
312 * page/[id]/page.tsx) — there is NO `/p/[id]` alias. The workspace slug is the
313 * SECOND path segment (the first is the literal `w`); the old code took the
314 * FIRST segment and produced `/w/p/{id}` → 404 "This page does not exist".
315 * Pure (unit-tested) and shared by the embedded database header ("Open" icon)
316 * and the record panel (ExternalLink icon).
317 */
318export function pageUrlFromPath(pathname: string | null | undefined, pageId: string): string {
319  const m = pathname?.match(/^\/w\/([^/]+)/);
320  return m?.[1] ? `/w/${m[1]}/page/${pageId}` : `/page/${pageId}`;
321}
322 
323/** Plain-text rendering of any typed value — used by CSV export, chart, search. */
324export function valueToText(value: RecordValue | undefined | null, property?: DatabaseProperty): string {
325  if (value == null) return "";
326  if (typeof value === "string") return value;
327  if (value.text != null) return String(value.text);
328  if (value.value != null) {
329    if (property?.config?.format === "currency" && typeof value.value === "number") {
330      return `${property.config.currency ?? "USD"} ${value.value}`;
331    }
332    return String(value.value);
333  }
334  if (value.optionId != null && property) {
335    return findOption(property.config.options, value.optionId)?.name ?? "";
336  }
337  if (value.optionIds?.length && property) {
338    return value.optionIds
339      .map((id) => findOption(property.config.options, id)?.name)
340      .filter(Boolean)
341      .join(", ");
342  }
343  if (value.start) return value.start.slice(0, 10);
344  if (value.recordIds) return `${value.recordIds.length} linked`;
345  if (value.fileIds) return `${value.fileIds.length} file(s)`;
346  if (value.userIds) return `${value.userIds.length} person(s)`;
347  return JSON.stringify(value);
348}
349 
350/**
351 * Build a "contains text" filter over the title + all text-ish properties.
352 * Used by SearchInView — composes documented filter ops only (the query
353 * engine itself stays server-side / in @factory/database-engine).
354 */
355export function searchFilter(properties: DatabaseProperty[], text: string): FilterTree | null {
356  const t = text.trim();
357  if (!t) return null;
358  const conditions: FilterCondition[] = [];
359  for (const p of properties) {
360    if (p.type === "title" || p.type === "text" || p.type === "rich_text") {
361      conditions.push({ propertyId: p.id, op: "contains", value: { text: t } });
362    }
363  }
364  if (conditions.length === 0) return null;
365  return conditions.length === 1 ? conditions[0] : { op: "or", conditions };
366}
367 
368/** Merge a view's saved filter with ad-hoc conditions (search, quick filters). */
369export function andFilters(...trees: (FilterTree | null | undefined)[]): FilterTree | null {
370  const present = trees.filter((t): t is FilterTree => t != null);
371  if (present.length === 0) return null;
372  if (present.length === 1) return present[0];
373  return { op: "and", conditions: present };
374}
375 
376export function isCondition(node: FilterGroup | FilterCondition): node is FilterCondition {
377  return (node as FilterCondition).propertyId != null;
378}
379 
380// ---------------------------------------------------------------------------
381// useDatabase
382// ---------------------------------------------------------------------------
383 
384const dbKey = (id: string) => ["database", id] as const;
385 
386/**
387 * Normalize a database view's `type` to the web's internal LOWERCASE contract.
388 *
389 * The API stores/serializes `ViewType` as the Prisma UPPERCASE enum (TABLE,
390 * BOARD, …, FORM — the web's ViewRouter / view tabs / ViewDialog are all
391 * lowercase). Without this translation every view falls through the
392 * ViewRouter's `default` branch (→ TableView) and UI view creation is rejected
393 * (the API's `z.enum(VIEW_TYPES)` wants uppercase). This is the single read
394 * point for views (useDatabase) + the create/update hooks' optimistic updates
395 * — the wire casing (uppercase) is re-applied on write below.
396 */
397function normalizeViewType(view: DatabaseView): DatabaseView {
398  if (!view || typeof view.type !== "string") return view;
399  const t = view.type.toLowerCase();
400  // Cast is safe: we are translating the wire casing (UPPERCASE Prisma enum)
401  // into the web's lowercase union; an unrecognized value falls through the
402  // ViewRouter's `default` branch either way.
403  return t === view.type ? view : ({ ...view, type: t } as DatabaseView);
404}
405 
406/**
407 * Lowercase a property's wire type (UPPERCASE Prisma enum) into the web's
408 * lowercase `PropertyType` union, mirroring {@link normalizeViewType}.
409 *
410 * The API persists/returns property `type` UPPERCASE (`TITLE`, `SELECT`,
411 * `DATE` …) but every web consumer compares lowercase
412 * (`p.type === "date"`, `TYPE_META[p.type]`, the cell renderer's switch, the
413 * property editor, `titlePropertySafe`). Without this the seeded Title/Status
414 * survive only via `isTitle`/position fallbacks and every type-driven code
415 * path (cell rendering, the `Add a title…` placeholder, filter/sort icons,
416 * property editing) silently no-ops. The write path is unaffected: it sends
417 * the union's own (lowercase) casing and the API's `propertyTypeSchema`
418 * uppercases it. Applied once, at the read boundary (flattenDatabase).
419 */
420export function normalizePropertyType(property: DatabaseProperty): DatabaseProperty {
421  if (!property || typeof property.type !== "string") return property;
422  const t = property.type.toLowerCase();
423  // Backfill deterministic ids on any id-less select/status/multi-select
424  // options (the shared `normalizeSelectOptions` only acts when
425  // `config.options` is an array, so non-select configs pass through). This
426  // makes the cached option set id-conformant on the CLIENT side too, so
427  // `findOption`'s id match succeeds even if a stale/legacy wire payload
428  // still carries bare `{ name, color }` options — the root cause of the
429  // "select works but the cell renders blank" symptom.
430  const config = normalizeSelectOptions(property.config) as PropertyConfig;
431  if (t === property.type && config === property.config) return property;
432  return { ...property, type: t, config } as DatabaseProperty;
433}
434 
435/**
436 * Flatten the wire shape of `GET /databases/:id` into the web's flat
437 * `DatabaseDto`.
438 *
439 * The API (database.service.get) returns the detail NESTED:
440 *   `{ database: {id, workspaceId, title, icon, …}, properties, views,
441 *      recordsCount, relations, formulas, rollups }`
442 * but the web's `DatabaseDto` (and every consumer — the page header reads
443 * `database.title`/`icon`, the view router reads `database.id`/`workspaceId`)
444 * is FLAT: `id`, `workspaceId`, `title`, …, `properties`, `views`, `recordCount`.
445 *
446 * Without this translation `database.id`/`workspaceId`/`title` are all
447 * `undefined`, so the entire database home — table view included, not just the
448 * FORM view — degrades. This is the single read point, so the flatten lives
449 * here (next to `normalizeViewType`); the wire stays nested. Tolerant of an
450 * already-flat body (no `database` key) so the call stays idempotent.
451 */
452function flattenDatabase(body: unknown): DatabaseDto {
453  const b = body as Record<string, unknown> | null | undefined;
454  if (!b || typeof b !== "object") throw new Error("useDatabase: empty database detail");
455  const nested = b.database as Record<string, unknown> | undefined;
456  if (!nested) {
457    // Already flat — normalize views + properties and return as-is.
458    const flat = b as unknown as DatabaseDto;
459    return {
460      ...flat,
461      properties: (flat.properties ?? []).map(normalizePropertyType),
462      views: (flat.views ?? []).map(normalizeViewType)
463    };
464  }
465  return {
466    ...nested,
467    properties: ((b.properties as DatabaseProperty[]) ?? []).map(normalizePropertyType),
468    views: ((b.views as DatabaseView[]) ?? []).map(normalizeViewType),
469    recordCount: (b.recordsCount as number | undefined) ?? undefined,
470    createdBy: (nested.createdBy as string | undefined) ?? null
471  } as DatabaseDto;
472}
473 
474export function useDatabase(id: string | undefined) {
475  return useQuery({
476    queryKey: dbKey(id ?? "none"),
477    enabled: !!id,
478    queryFn: async (): Promise<DatabaseDto> => {
479      const body: unknown = await apiFetch(`/databases/${id}`);
480      const db: DatabaseDto = flattenDatabase(unwrap(body));
481      return { ...db, views: (db.views ?? []).map(normalizeViewType) };
482    }
483  });
484}
485 
486// ---------------------------------------------------------------------------
487// useDatabaseRecords — POST /databases/:id/query (server-side engine)
488//
489// The server evaluates filter → sort → group → paginate (the 100k-record
490// scale design in docs/api/databases.md), so filter/sort/group are always
491// sent to the server — no local reimplementation of query logic. Cursor
492// pagination is handled by useInfiniteQuery.
493// ---------------------------------------------------------------------------
494 
495export interface UseDatabaseRecordsOptions {
496  viewId?: string | null;
497  filter?: FilterTree | null;
498  sort?: SortSpec[] | null;
499  group?: GroupSpec[] | null;
500  pageSize?: number;
501  includeArchived?: boolean;
502  select?: string[];
503  enabled?: boolean;
504}
505 
506export interface UseDatabaseRecordsResult {
507  /** All loaded pages concatenated (use `groups` when `group` is set). */
508  data: QueryResult;
509  isLoading: boolean;
510  isFetching: boolean;
511  error: Error | null;
512  /** Fetch the next page (appends). */
513  loadMore: () => void;
514  /** Discard the last loaded page (step "back" a page). */
515  goPrev: () => void;
516  canPrev: boolean;
517  hasMore: boolean;
518  /** Reset to only the first page (after filter/view changes). */
519  reset: () => void;
520}
521 
522const EMPTY_RESULT: QueryResult = {
523  records: [],
524  groups: null,
525  aggregates: null,
526  totalCount: null,
527  nextCursor: null
528};
529 
530interface FlatPage {
531  records: RecordDto[];
532  groups: RecordGroup[] | null;
533  totalCount: number | null;
534  nextCursor: string | null;
535}
536 
537export function useDatabaseRecords(
538  databaseId: string | undefined,
539  options: UseDatabaseRecordsOptions
540): UseDatabaseRecordsResult {
541  const { viewId, filter, sort, group, pageSize = 50, includeArchived = false, select, enabled = true } = options;
542 
543  const filterJson = filter ? JSON.stringify(filter) : null;
544  const sortJson = sort && sort.length ? JSON.stringify(sort) : null;
545  const groupJson = group && group.length ? JSON.stringify(group) : null;
546  const selectKey = select?.join(",") ?? null;
547 
548  const infinite = useInfiniteQuery({
549    queryKey: [
550      "database",
551      databaseId,
552      "query",
553      { viewId, filter: filterJson, sort: sortJson, group: groupJson, pageSize, includeArchived, select: selectKey }
554    ],
555    enabled: enabled && !!databaseId,
556    initialPageParam: null as string | null,
557    // TASK-0124 (repair): @tanstack/react-query v5 requires `getNextPageParam`
558    // (the v4 name was `getPageParam`). It was dormant while the records fetch
559    // 404'd (no successful page → never invoked); now that the §6 query
560    // endpoint exists, a successful page without it threw
561    // `options.getNextPageParam is not a function` and crashed the whole
562    // database page. Mirrors FileBrowser.tsx (the v5 pattern).
563    getNextPageParam: (last) => (last as FlatPage).nextCursor ?? undefined,
564    queryFn: async ({ pageParam }): Promise<FlatPage> => {
565      const body: Record<string, unknown> = { includeArchived, pageSize };
566      if (viewId) body.viewId = viewId;
567      if (filterJson) body.filter = JSON.parse(filterJson);
568      if (sortJson) body.sort = JSON.parse(sortJson);
569      if (groupJson) body.group = JSON.parse(groupJson);
570      if (pageParam) body.cursor = pageParam;
571      if (selectKey) body.select = selectKey.split(",");
572      const data = unwrap<Record<string, unknown> | null>(
573        await apiFetch(`/databases/${databaseId}/query`, {
574          method: "POST",
575          body: JSON.stringify(body)
576        })
577      );
578      if (!data) return { records: [], groups: null, totalCount: null, nextCursor: null };
579      // Server returns the grouped shape when a group is sent, flat otherwise.
580      const hasGroups = Array.isArray((data as { groups?: unknown }).groups);
581      const groups: RecordGroup[] | null = hasGroups
582        ? ((data as { groups: RecordGroup[] }).groups ?? [])
583        : null;
584      const records: RecordDto[] = hasGroups
585        ? (groups ?? []).flatMap((g) => g.records ?? [])
586        : (((data as { records?: RecordDto[] }).records ?? []) as RecordDto[]);
587      return {
588        records,
589        groups,
590        totalCount: (data as { totalCount?: number }).totalCount ?? null,
591        nextCursor: (data as { nextCursor?: string | null }).nextCursor ?? null
592      };
593    }
594  });
595 
596  const reset = useCallback(() => {
597    void infinite.setPageCount(1);
598  }, [infinite.setPageCount]);
599 
600  const goPrev = useCallback(() => {
601    if (infinite.data && infinite.data.pages.length > 1) {
602      void infinite.setPageCount(infinite.data.pages.length - 1);
603    }
604  }, [infinite.data?.pages.length, infinite.setPageCount]);
605 
606  const data = useMemo<QueryResult>(() => {
607    if (!infinite.data) return EMPTY_RESULT;
608    const records = infinite.data.pages.flatMap((p) => p.records);
609    const groups = infinite.data.pages.flatMap((p) => p.groups ?? []);
610    const last = infinite.data.pages[infinite.data.pages.length - 1];
611    return {
612      records,
613      groups: groups.length ? groups : null,
614      aggregates: null,
615      totalCount: last.totalCount,
616      nextCursor: last.nextCursor
617    };
618  }, [infinite.data]);
619 
620  return {
621    data,
622    isLoading: infinite.isPending,
623    isFetching: infinite.isFetching,
624    error: (infinite.error as Error | null) ?? null,
625    loadMore: () => {
626      if (infinite.hasNextPage && !infinite.isFetchingNextPage) void infinite.fetchNextPage();
627    },
628    goPrev,
629    canPrev: (infinite.data?.pages.length ?? 0) > 1,
630    hasMore: infinite.hasNextPage,
631    reset
632  };
633}
634 
635// ---------------------------------------------------------------------------
636// useDatabaseRecord — GET /records/:recordId
637// ---------------------------------------------------------------------------
638 
639export function useDatabaseRecord(_databaseId: string | undefined, recordId: string | undefined) {
640  return useQuery({
641    queryKey: ["record", recordId ?? "none"],
642    enabled: !!recordId,
643    queryFn: async (): Promise<RecordDto> => unwrap(await apiFetch(`/records/${recordId}`))
644  });
645}
646 
647// ---------------------------------------------------------------------------
648// Cache helpers — record pages live under many query keys; patch by scanning.
649// ---------------------------------------------------------------------------
650 
651/**
652 * Optimistically apply a per-record transform to every cached page of this
653 * database's record queries (table/board/gallery/list views all share the
654 * `["database", id, "query", …]` key family).
655 *
656 * MUST go through `qc.setQueryData` with an IMMUTABLE new structure — never
657 * mutate `query.state.data` in place. In-place mutation bypasses RQ's
658 * reference-based change detection: the `useInfiniteQuery` data object keeps
659 * the same identity, so consumers that memoize on `[infinite.data]` never
660 * recompute and the grid renders a stale snapshot even though the cache
661 * holds the right value. RQ v5 structural sharing makes this worse — when a
662 * follow-up refetch returns the same value the optimistic patch already
663 * wrote, it reuses the cached reference entirely. A fresh object graph from
664 * `setQueryData` changes the reference, so the memo recomputes and the grid
665 * reflects the edit immediately.
666 */
667function patchRecordsInCache(
668  qc: ReturnType<typeof useQueryClient>,
669  databaseId: string,
670  transform: (record: RecordDto) => RecordDto
671) {
672  for (const query of qc.getQueryCache().getAll()) {
673    const key = query.queryKey as readonly unknown[];
674    if (key.length >= 3 && key[0] === "database" && key[1] === databaseId && key[2] === "query") {
675      qc.setQueryData(key, (old: { pages: FlatPage[] } | undefined) => {
676        if (!old?.pages) return old;
677        const pages = old.pages.map((page) => ({
678          ...page,
679          records: page.records.map(transform),
680          groups: page.groups?.map((g) => ({ ...g, records: g.records.map(transform) })) ?? null
681        }));
682        return { ...old, pages };
683      });
684    }
685  }
686}
687 
688// ---------------------------------------------------------------------------
689// Mutations
690// ---------------------------------------------------------------------------
691 
692export function useCreateDatabase() {
693  const qc = useQueryClient();
694  return useMutation({
695    mutationFn: async (payload: {
696      title: string;
697      icon?: string;
698      description?: string;
699      pageId?: string | null;
700      parentId?: string | null;
701      properties?: { name: string; type: string; config?: PropertyConfig }[];
702      views?: { name: string; type: string; group?: GroupSpec[] }[];
703    }): Promise<DatabaseDto> =>
704      unwrap(await apiFetch("/databases", { method: "POST", body: JSON.stringify(payload) })),
705    onSuccess: (_db, variables) => {
706      if (variables.pageId) qc.invalidateQueries({ queryKey: ["page", variables.pageId] });
707    }
708  });
709}
710 
711export function useUpdateDatabase(databaseId: string) {
712  const qc = useQueryClient();
713  return useMutation({
714    mutationFn: async (payload: {
715      title?: string;
716      icon?: string;
717      description?: string;
718      archived?: boolean;
719    }): Promise<DatabaseDto> =>
720      unwrap(await apiFetch(`/databases/${databaseId}`, {
721        method: "PATCH",
722        body: JSON.stringify(payload)
723      })),
724    onSuccess: (updated) => {
725      qc.setQueryData(dbKey(databaseId), (old: DatabaseDto | undefined) =>
726        old ? { ...old, ...updated } : old
727      );
728    }
729  });
730}
731 
732export function useCreateRecord(databaseId: string) {
733  const qc = useQueryClient();
734  return useMutation({
735    mutationFn: async (payload: {
736      /** Top-level record title (the title property's value). The API
737       *  requires the title HERE and rejects it inside `properties`
738       *  (`title_via_title_field` — query.service.ts), so callers must route
739       *  the title property's value into this field instead of `properties`. */
740      title?: string;
741      properties: Record<string, RecordValue>;
742      icon?: string;
743      coverUrl?: string;
744      position?: string;
745      parentRecordId?: string | null;
746    }): Promise<RecordDto> =>
747      unwrap(await apiFetch(`/databases/${databaseId}/records`, {
748        method: "POST",
749        body: JSON.stringify(payload)
750      })),
751    onSuccess: () => {
752      qc.invalidateQueries({ queryKey: dbKey(databaseId) });
753    }
754  });
755}
756 
757export function useUpdateRecord(databaseId: string) {
758  const qc = useQueryClient();
759  return useMutation({
760    mutationFn: async (payload: {
761      recordId: string;
762      /** Top-level record title (the title property's value). The API
763       *  requires the title HERE and rejects it inside `properties`
764       *  (`title_via_title_field` — query.service.ts). Pass
765       *  `titlePropertyId` alongside so the optimistic merge below can
766       *  update the title property's cached value too. */
767      title?: string;
768      titlePropertyId?: string;
769      properties?: Record<string, RecordValue>;
770      position?: string;
771      icon?: string;
772      version?: number;
773      parentRecordId?: string | null;
774    }): Promise<RecordDto> =>
775      unwrap(
776        await apiFetch(`/records/${payload.recordId}`, {
777          method: "PATCH",
778          headers: payload.version != null ? { "If-Match": String(payload.version) } : undefined,
779          body: JSON.stringify({
780            title: payload.title,
781            properties: payload.properties,
782            position: payload.position,
783            icon: payload.icon,
784            parentRecordId: payload.parentRecordId,
785            version: payload.version
786          })
787        })
788      ),
789    onMutate: async (payload) => {
790      await qc.cancelQueries({ queryKey: ["database", databaseId] });
791      // Optimistic: merge the property patch (+ title) into every cached
792      // page of this db (immutable — see patchRecordsInCache).
793      patchRecordsInCache(qc, databaseId, (r) => {
794        if (r.id !== payload.recordId) return r;
795        const properties = { ...r.properties, ...(payload.properties ?? {}) };
796        if (payload.titlePropertyId != null && payload.title != null) {
797          properties[payload.titlePropertyId] = { text: payload.title, marks: [] };
798        }
799        return {
800          ...r,
801          version: (r.version ?? 0) + 1,
802          ...(payload.title != null ? { title: payload.title } : {}),
803          properties
804        };
805      });
806      const prevRecord = qc.getQueryData<RecordDto>(["record", payload.recordId]);
807      qc.setQueryData(["record", payload.recordId], (old: RecordDto | undefined) => {
808        if (!old) return old;
809        const properties = { ...old.properties, ...(payload.properties ?? {}) };
810        if (payload.titlePropertyId != null && payload.title != null) {
811          properties[payload.titlePropertyId] = { text: payload.title, marks: [] };
812        }
813        return {
814          ...old,
815          version: (old.version ?? 0) + 1,
816          ...(payload.title != null ? { title: payload.title } : {}),
817          properties
818        };
819      });
820      return { prevRecord };
821    },
822    onError: (_err, _payload, ctx) => {
823      // Rollback the optimistic write; the next refetch restores the truth.
824      if (ctx?.prevRecord) qc.setQueryData(["record", _payload.recordId], ctx.prevRecord);
825      qc.invalidateQueries({ queryKey: ["database", databaseId] });
826    },
827    onSuccess: (updated) => {
828      qc.setQueryData(["record", updated.id], updated);
829      // Reconcile the table/board/gallery record queries with the server
830      // truth. The optimistic merge above covers the in-place flash, but the
831      // infinite-query pages are a separate cache that the merge does not
832      // reach for every consumer shape (e.g. a record created moments ago
833      // whose version the grid hasn't seen, or a write routed through the
834      // title field). Invalidating here makes every view refetch, so edits
835      // never "disappear" and the cache can't drift from the DB.
836      qc.invalidateQueries({ queryKey: ["database", databaseId] });
837    }
838  });
839}
840 
841/**
842 * Bulk update across many records. The documented surface is per-record
843 * PATCH (docs/api/databases.md §7.3); the architecture mentions a single
844 * `/records/batch` — when the API lands, swap the loop for one POST.
845 */
846export function useBulkUpdateRecords(databaseId: string) {
847  const qc = useQueryClient();
848  return useMutation({
849    mutationFn: async (
850      items: { recordId: string; properties: Record<string, RecordValue>; version?: number }[]
851    ): Promise<{ succeeded: number; failed: { recordId: string; error: string }[] }> => {
852      const results = await Promise.allSettled(
853        items.map((it) =>
854          apiFetch(`/records/${it.recordId}`, {
855            method: "PATCH",
856            headers: it.version != null ? { "If-Match": String(it.version) } : undefined,
857            body: JSON.stringify({ properties: it.properties, version: it.version })
858          })
859        )
860      );
861      const failed: { recordId: string; error: string }[] = [];
862      results.forEach((r, i) => {
863        if (r.status === "rejected") {
864          failed.push({ recordId: items[i].recordId, error: (r.reason as Error)?.message ?? "failed" });
865        }
866      });
867      return { succeeded: results.length - failed.length, failed };
868    },
869    onSuccess: (_result, items) => {
870      patchRecordsInCache(qc, databaseId, (r) => {
871        const props = new Map(items.map((i) => [i.recordId, i.properties])).get(r.id);
872        if (!props) return r;
873        return {
874          ...r,
875          version: (r.version ?? 0) + 1,
876          properties: { ...r.properties, ...props }
877        };
878      });
879      qc.invalidateQueries({ queryKey: ["database", databaseId] });
880    }
881  });
882}
883 
884export function useDeleteRecord(databaseId: string) {
885  const qc = useQueryClient();
886  return useMutation({
887    mutationFn: async (recordId: string): Promise<RecordDto | null> => {
888      // Hard delete requires `confirm` to equal the record id (typo protection).
889      try {
890        return unwrap(
891          await apiFetch(`/records/${recordId}`, {
892            method: "DELETE",
893            body: JSON.stringify({ confirm: recordId })
894          })
895        );
896      } catch (e) {
897        // If hard delete is unavailable (404/405), fall back to archiving so
898        // the UI always offers a recoverable path.
899        const err = e as { code?: string; status?: number };
900        if (err?.code === "NOT_FOUND" || err?.status === 404 || err?.status === 405) {
901          return unwrap(await apiFetch(`/records/${recordId}/archive`, { method: "POST" }));
902        }
903        throw e;
904      }
905    },
906    onSuccess: (_data, recordId) => {
907      qc.removeQueries({ queryKey: ["record", recordId] });
908      qc.invalidateQueries({ queryKey: ["database", databaseId] });
909    }
910  });
911}
912 
913// --- Properties -------------------------------------------------------------
914 
915export function useCreateProperty(databaseId: string) {
916  const qc = useQueryClient();
917  return useMutation({
918    mutationFn: async (payload: {
919      name: string;
920      type: string;
921      position?: string;
922      isVisible?: boolean;
923      required?: boolean;
924      config?: PropertyConfig;
925      default?: unknown | null;
926    }): Promise<DatabaseProperty> =>
927      unwrap(await apiFetch(`/databases/${databaseId}/properties`, {
928        method: "POST",
929        body: JSON.stringify(payload)
930      })),
931    onSuccess: () => qc.invalidateQueries({ queryKey: dbKey(databaseId) })
932  });
933}
934 
935export function useUpdateProperty(databaseId: string) {
936  const qc = useQueryClient();
937  return useMutation({
938    mutationFn: async (payload: {
939      propertyId: string;
940      name?: string;
941      position?: string;
942      isVisible?: boolean;
943      required?: boolean;
944      config?: PropertyConfig;
945    }): Promise<DatabaseProperty> =>
946      unwrap(
947        await apiFetch(`/databases/${databaseId}/properties/${payload.propertyId}`, {
948          method: "PATCH",
949          body: JSON.stringify({
950            name: payload.name,
951            position: payload.position,
952            isVisible: payload.isVisible,
953            required: payload.required,
954            config: payload.config
955          })
956        })
957      ),
958    onSuccess: () => qc.invalidateQueries({ queryKey: dbKey(databaseId) })
959  });
960}
961 
962export function useDeleteProperty(databaseId: string) {
963  const qc = useQueryClient();
964  return useMutation({
965    mutationFn: async (propertyId: string): Promise<{ affectedRecords: number } | null> =>
966      unwrap(await apiFetch(`/databases/${databaseId}/properties/${propertyId}`, { method: "DELETE" })),
967    onSuccess: () => qc.invalidateQueries({ queryKey: dbKey(databaseId) })
968  });
969}
970 
971// --- Views ------------------------------------------------------------------
972 
973export function useCreateView(databaseId: string) {
974  const qc = useQueryClient();
975  return useMutation({
976    mutationFn: async (payload: {
977      name: string;
978      type: string;
979      config?: ViewConfig;
980      filter?: FilterTree | null;
981      sort?: SortSpec[] | null;
982      group?: GroupSpec[] | null;
983      pageSize?: number | null;
984      position?: string;
985    }): Promise<DatabaseView> =>
986      unwrap(
987        await apiFetch(`/databases/${databaseId}/views`, {
988          method: "POST",
989          // The wire contract is the UPPERCASE Prisma ViewType (the UI uses
990          // lowercase); re-apply the casing on write (see normalizeViewType).
991          body: JSON.stringify({ ...payload, type: payload.type.toUpperCase() })
992        })
993      ),
994    onSuccess: () => qc.invalidateQueries({ queryKey: dbKey(databaseId) })
995  });
996}
997 
998export function useUpdateView(databaseId: string) {
999  const qc = useQueryClient();
1000  return useMutation({
1001    mutationFn: async (payload: {
1002      viewId: string;
1003      name?: string;
1004      config?: ViewConfig;
1005      filter?: FilterTree | null;
1006      sort?: SortSpec[] | null;
1007      group?: GroupSpec[] | null;
1008      pageSize?: number | null;
1009    }): Promise<DatabaseView> =>
1010      unwrap(
1011        await apiFetch(`/databases/${databaseId}/views/${payload.viewId}`, {
1012          method: "PATCH",
1013          body: JSON.stringify({
1014            name: payload.name,
1015            config: payload.config,
1016            filter: payload.filter,
1017            sort: payload.sort,
1018            group: payload.group,
1019            pageSize: payload.pageSize
1020          })
1021        })
1022      ),
1023    onSuccess: (updated) => {
1024      const norm = normalizeViewType(updated);
1025      qc.setQueryData(dbKey(databaseId), (old: DatabaseDto | undefined) =>
1026        old
1027          ? { ...old, views: old.views.map((v) => (v.id === updated.id ? { ...v, ...norm } : v)) }
1028          : old
1029      );
1030    }
1031  });
1032}
1033 
1034export function useDeleteView(databaseId: string) {
1035  const qc = useQueryClient();
1036  return useMutation({
1037    mutationFn: async (viewId: string): Promise<void> =>
1038      unwrap(await apiFetch(`/databases/${databaseId}/views/${viewId}`, { method: "DELETE" })),
1039    onSuccess: () => qc.invalidateQueries({ queryKey: dbKey(databaseId) })
1040  });
1041}
1042 
1043/**
1044 * Reorder a view within the database (fractional `position`).
1045 * T-166: the real endpoint is `POST /databases/:id/views/:viewId/reorder`
1046 * with `{ after, before }` (view ids). The old body PATCHed `position`, which
1047 * the server's UpdateViewDto strips — so the call was a silent no-op and
1048 * "set as default" could never actually move the view.
1049 */
1050export function useReorderView(databaseId: string) {
1051  const qc = useQueryClient();
1052  return useMutation({
1053    mutationFn: async (payload: { viewId: string; after?: string; before?: string }): Promise<DatabaseView> =>
1054      unwrap(
1055        await apiFetch(`/databases/${databaseId}/views/${payload.viewId}/reorder`, {
1056          method: "POST",
1057          body: JSON.stringify({ after: payload.after, before: payload.before })
1058        })
1059      ),
1060    onSuccess: () => qc.invalidateQueries({ queryKey: dbKey(databaseId) })
1061  });
1062}
1063 
1064// --- Formulas ---------------------------------------------------------------
1065 
1066export interface FormulaValidationResult {
1067  ok: boolean;
1068  returnType?: string;
1069  samples?: { recordId: string; value: unknown; error?: string }[];
1070  error?: string;
1071  position?: number;
1072}
1073 
1074/**
1075 * Validate a formula expression server-side (safe AST parser — never eval,
1076 * PRODUCT-SPEC §9.6). POST /databases/:id/formulas/validate
1077 * { expression, sampleRecordIds }
1078 */
1079export function useValidateFormula(databaseId: string) {
1080  return useMutation({
1081    mutationFn: async (payload: { expression: string; sampleRecordIds?: string[] }): Promise<FormulaValidationResult> => {
1082      const data = unwrap<FormulaValidationResult | null>(
1083        await apiFetch(`/databases/${databaseId}/formulas/validate`, {
1084          method: "POST",
1085          body: JSON.stringify(payload)
1086        })
1087      );
1088      return (data ?? { ok: false }) as FormulaValidationResult;
1089    }
1090  });
1091}
1092 
1093// --- Relations (for relation cells) ------------------------------------------
1094 
1095/**
1096 * Resolve linked record titles for relation cells. The relation value carries
1097 * `recordIds`; targets are fetched lazily (first 3 shown, "+N" for the rest)
1098 * so table rows stay cheap on large databases.
1099 */
1100export function useRelations(
1101  _databaseId: string | undefined,
1102  targetDatabaseId: string | undefined,
1103  recordIds: string[]
1104) {
1105  const limited = (recordIds ?? []).slice(0, 3);
1106  return useQuery({
1107    queryKey: ["relations", targetDatabaseId, limited.join(",")],
1108    enabled: !!targetDatabaseId && limited.length > 0,
1109    queryFn: async () => {
1110      const results = await Promise.allSettled(
1111        limited.map(async (id) => {
1112          const record = unwrap<RecordDto>(await apiFetch(`/records/${id}`));
1113          return { recordId: id, record };
1114        })
1115      );
1116      // Unwrap the `PromiseFulfilledResult` → the inner `{ recordId, record }`.
1117      // Consumers read `x.recordId` / `x.record` directly; returning the raw
1118      // `{ status, value }` wrapper (the old behavior) made every lookup
1119      // `undefined` → relation chips always rendered the "Record" fallback.
1120      return results
1121        .filter(
1122          (r): r is PromiseFulfilledResult<{ recordId: string; record: RecordDto }> => r.status === "fulfilled"
1123        )
1124        .map((r) => r.value);
1125    }
1126  });
1127}
1128 
1129// --- CSV export (client-side, from the currently loaded records) -------------
1130 
1131function csvEscape(value: string): string {
1132  if (/[",\n]/.test(value)) return `"${value.replace(/"/g, '""')}"`;
1133  return value;
1134}
1135 
1136/**
1137 * Export the *currently loaded* records (client-side, no server round-trip)
1138 * as a CSV download. Columns = visible properties in order.
1139 */
1140export function useExportCsv(databaseId: string, properties: DatabaseProperty[]) {
1141  return useCallback(
1142    (records: RecordDto[], filename?: string) => {
1143      if (!records.length) return;
1144      const header = properties.map((p) => csvEscape(p.name)).join(",");
1145      const rows = records.map((r) =>
1146        properties.map((p) => csvEscape(valueToText(r.properties[p.id], p))).join(",")
1147      );
1148      const csv = [header, ...rows].join("\n");
1149      const blob = new Blob([csv], { type: "text/csv;charset=utf-8" });
1150      const url = URL.createObjectURL(blob);
1151      const a = document.createElement("a");
1152      a.href = url;
1153      a.download = filename ?? `database-${(databaseId ?? "export").slice(-8)}.csv`;
1154      a.click();
1155      URL.revokeObjectURL(url);
1156    },
1157    [databaseId, properties]
1158  );
1159}
1160