filter-builder.ts

filter-builder.tsapps/api/src/lib/filter-builder.tsraw ← lib11,574 B← Back to the app
1/**
2 * Record filter builder — maps property filters to a Prisma where-fragment
3 * for `DatabaseRecord.properties` (relation `RecordProperty`, whose value
4 * lives in the single JSONB column `value`, typed per property.type).
5 *
6 * Pure function: returns a plain object, unit-testable without a database.
7 * The fragment is `AND`-combined — one `{ properties: { some: [...] } }`
8 * clause per SQL-expressible filter (the standard Prisma pattern for
9 * multiple `some`s). Filters SQL cannot express (envelope values, ranges,
10 * contains) are validated here but left to the service's exact JS pass.
11 *
12 * Value shapes passed as filter values (per property type — the docs §5.3
13 * envelopes are unwrapped by the service before this is called):
14 *   text-ish (TITLE TEXT RICH_TEXT EMAIL PHONE URL UNIQUE_ID) → string
15 *   NUMBER → number          SELECT STATUS → string (option name)
16 *   MULTI_SELECT PERSON FILES RELATION → string[]
17 *   DATE → ISO string        DATE_RANGE → {start, end}
18 *   CHECKBOX → boolean
19 */
20 
21import { ApiError } from "../common/errors.js";
22import { FILTER_OPS } from "../constants.js";
23 
24export interface PropertyFilter {
25  propertyId: string;
26  op: string;
27  value?: unknown;
28}
29 
30export type PropertyType = string;
31 
32const DATE_TYPES = new Set(["DATE", "DATE_RANGE"]);
33const NUMBER_TYPES = new Set(["NUMBER"]);
34const TEXT_TYPES = new Set(["TITLE", "TEXT", "RICH_TEXT", "EMAIL", "PHONE", "URL", "UNIQUE_ID", "SELECT", "STATUS"]);
35 
36/**
37 * Ops evaluated in JS only (query.service.ts `leafMatches`): they need row
38 * context (option resolution, the current user) or are array-membership
39 * tests Prisma cannot express on the JSONB column. buildRecordFilter
40 * validates them but emits no SQL clause for them.
41 */
42export const JS_ONLY_OPS = new Set(["contains_any", "contains_all", "isCurrentUser"]);
43 
44interface ValueCondition {
45  [key: string]: unknown;
46}
47 
48/**
49 * Build the value-condition for one (op, value, type) triple.
50 * Exported for unit tests.
51 */
52export function buildValueCondition(op: string, value: unknown, type: PropertyType): ValueCondition {
53  switch (op) {
54    case "eq":
55      if (typeof value !== "string" && typeof value !== "number" && typeof value !== "boolean" && !Array.isArray(value)) {
56        throw new ApiError("VALIDATION_ERROR", 400, `operator ${op} requires a scalar value`, [{ field: "filter.value", issue: "scalar required" }]);
57      }
58      return { equals: value };
59    case "neq":
60      if (typeof value !== "string" && typeof value !== "number" && typeof value !== "boolean" && !Array.isArray(value)) {
61        throw new ApiError("VALIDATION_ERROR", 400, `operator ${op} requires a scalar value`, [{ field: "filter.value", issue: "scalar required" }]);
62      }
63      // exclude rows where value equals the given one OR is null
64      return { AND: [{ not: { equals: value } }, { not: { equals: null } }] };
65    case "contains":
66      assertText(type, op);
67      return { string_contains: String(value) };
68    case "ncontains":
69      assertText(type, op);
70      return { AND: [{ not: { string_contains: String(value) } }, { not: { equals: null } }] };
71    case "startsWith":
72      assertText(type, op);
73      return { string_starts_with: String(value) };
74    case "endsWith":
75      assertText(type, op);
76      return { string_ends_with: String(value) };
77    case "isEmpty":
78      return { equals: null };
79    case "isNotEmpty":
80      return { not: { equals: null } };
81    case "contains_any":
82    case "contains_all": {
83      // Validated only (JS pass evaluates membership). The web sends an
84      // {optionIds:[…]} envelope (simplifyValue does not unwrap it), so
85      // unwrap id envelopes before the array check. Empty arrays are
86      // rejected as a no-op filter.
87      const ids = unwrapIdArray(value);
88      if (ids.length === 0) {
89        throw new ApiError("VALIDATION_ERROR", 400, `operator ${op} requires a non-empty array value`, [
90          { field: "filter.value", issue: "array required" }
91        ]);
92      }
93      return { equals: ids };
94    }
95    case "isCurrentUser":
96      // Evaluated against the requesting user in the JS pass.
97      return { equals: true };
98    case "isThisWeek":
99    case "isThisMonth":
100    case "isPast":
101    case "isFuture":
102      // Date-window ops evaluated in JS (needs "now"); validated via the
103      // date types only.
104      if (!DATE_TYPES.has(type)) {
105        throw new ApiError("VALIDATION_ERROR", 400, `operator ${op} requires a date property`, [
106          { field: "filter.property", issue: `property type ${type} is not date` }
107        ]);
108      }
109      return { equals: true };
110    case "withinNextDays": {
111      // The web sends a {days: N} envelope (simplifyValue does not unwrap
112      // `days`); accept a bare number/string for the docs' shape too.
113      const nRaw = value !== null && typeof value === "object" && !Array.isArray(value)
114        ? (value as { days?: unknown }).days
115        : value;
116      const n = typeof nRaw === "string" ? Number(nRaw) : nRaw;
117      if (typeof n !== "number" || Number.isNaN(n) || n <= 0) {
118        throw new ApiError("VALIDATION_ERROR", 400, "operator withinNextDays requires a positive number of days", [
119          { field: "filter.value", issue: "positive number required" }
120        ]);
121      }
122      if (!DATE_TYPES.has(type)) {
123        throw new ApiError("VALIDATION_ERROR", 400, `operator ${op} requires a date property`, [
124          { field: "filter.property", issue: `property type ${type} is not date` }
125        ]);
126      }
127      return { equals: n };
128    }
129    case "between": {
130      if (!DATE_TYPES.has(type)) {
131        throw new ApiError("VALIDATION_ERROR", 400, `operator ${op} requires a date property`, [
132          { field: "filter.property", issue: `property type ${type} is not date` }
133        ]);
134      }
135      // The web's default between is {start:"",end:""} — treat empty bounds
136      // as unbounded (rather than 400) so the default filter stays valid.
137      const range = value as { start?: unknown; end?: unknown };
138      if (range?.start != null && range?.start !== "") toIso(String(range.start));
139      if (range?.end != null && range?.end !== "") toIso(String(range.end));
140      return { equals: value };
141    }
142    case "gte":
143    case "lte":
144    case "gt":
145    case "lt":
146    case "after":
147    case "before": {
148      const opMap: Record<string, string> = {
149        gte: "gte", lte: "lte", gt: "gt", lt: "lt", after: "gte", before: "lte"
150      };
151      const key = opMap[op]!;
152      if (DATE_TYPES.has(type)) {
153        if (type === "DATE_RANGE") {
154          // range matches when it overlaps: start <= value(after) and end >= value(before)
155          if (op === "after") return { gte: toIso(value) };
156          if (op === "before") return { lte: toIso(value) };
157          return { [key]: toIso(value) };
158        }
159        return { [key]: toIso(value) };
160      }
161      const n = typeof value === "string" ? Number(value) : value;
162      if (typeof n !== "number" || Number.isNaN(n)) {
163        throw new ApiError("VALIDATION_ERROR", 400, `operator ${op} requires a number or ISO date`, [{ field: "filter.value", issue: "number/date required" }]);
164      }
165      return { [key]: n };
166    }
167    default:
168      throw new ApiError("VALIDATION_ERROR", 400, `unknown filter operator: ${op}`, [
169        { field: "filter.op", issue: "unknown op", received: op }
170      ]);
171  }
172}
173 
174/**
175 * Unwrap a filter value into a string[] of ids. Accepts a bare array, an
176 * id-envelope object ({optionIds}/{userIds}/{recordIds}/{fileIds}/{ids}),
177 * or a single id string. Returns [] when nothing usable is present.
178 */
179function unwrapIdArray(value: unknown): string[] {
180  if (Array.isArray(value)) return value.map((v) => String(v));
181  if (value !== null && typeof value === "object") {
182    for (const k of ["optionIds", "userIds", "recordIds", "fileIds", "ids"]) {
183      const v = (value as Record<string, unknown>)[k];
184      if (Array.isArray(v)) return v.map((x) => String(x));
185    }
186  }
187  if (typeof value === "string" && value !== "") return [value];
188  return [];
189}
190 
191function assertText(type: string, op: string): void {
192  if (!TEXT_TYPES.has(type)) {
193    throw new ApiError("VALIDATION_ERROR", 400, `operator ${op} requires a text property`, [
194      { field: "filter.property", issue: `property type ${type} is not text` }
195    ]);
196  }
197}
198 
199function toIso(value: unknown): string {
200  if (value instanceof Date) return value.toISOString();
201  if (typeof value !== "string") {
202    throw new ApiError("VALIDATION_ERROR", 400, "date filter requires an ISO date string", [{ field: "filter.value", issue: "iso date required" }]);
203  }
204  const d = new Date(value);
205  if (Number.isNaN(d.getTime())) {
206    throw new ApiError("VALIDATION_ERROR", 400, "invalid date value", [{ field: "filter.value", issue: "invalid date", received: value }]);
207  }
208  return d.toISOString();
209}
210 
211/**
212 * Types whose `RecordProperty.value` is stored as a **raw scalar** (not a
213 * JSON envelope like `{ text }` / `{ value }` / `{ optionId }` — see
214 * `coerceValue`). Only these can be pre-filtered in SQL: Prisma's JSONB
215 * filter without a `path` only supports whole-value `equals`/`not`, and a
216 * whole-envelope `equals` would never match the raw filter value.
217 *
218 * Every other (op, type) combination is still VALIDATED here (unknown ops,
219 * `contains` on a non-text type, bad dates → 400 with a message), but it
220 * produces no SQL clause: the query service re-checks the whole filter tree
221 * in JS over the bounded row set (`applyDisjunction`), which is the exact
222 * evaluator — the SQL fragment is only a pre-filter, so skipping it changes
223 * no results, it only fetches a bit more for the JS pass (≤ 5000 rows, the
224 * documented bound).
225 *
226 * Historically every op was pushed into a top-level JSONB condition
227 * (`string_contains`, `gte`, … directly on the `value` column), which is
228 * not a valid `RecordPropertyWhereInput` and made Prisma throw
229 * `PrismaClientValidationError` → 400 "Invalid database query." for any
230 * filtered list (search-in-view, quick filters, saved view filters).
231 */
232const RAW_SCALAR_TYPES = new Set(["CHECKBOX"]);
233 
234/**
235 * Build the DatabaseRecord where-fragment for a list of property filters.
236 *
237 * `propertyTypes` maps propertyId → PropertyType (used to pick the value
238 * comparison semantics). Returns `{ AND: [...] }`; an empty input (or one
239 * with nothing SQL-expressible) returns an empty fragment `{}`.
240 */
241export function buildRecordFilter(
242  propertyFilters: PropertyFilter[],
243  propertyTypes: Record<string, PropertyType>,
244  _prisma?: unknown
245): Record<string, unknown> {
246  if (propertyFilters.length === 0) return {};
247  const clauses: Record<string, unknown>[] = [];
248  for (const f of propertyFilters) {
249    if (!FILTER_OPS.includes(f.op as (typeof FILTER_OPS)[number])) {
250      throw new ApiError("VALIDATION_ERROR", 400, `unknown filter operator: ${f.op}`, [
251        { field: "filter.op", issue: "unknown op", received: f.op }
252      ]);
253    }
254    const type = propertyTypes[f.propertyId] ?? "TEXT";
255    // Validate the (op, value, type) triple first so malformed filters still
256    // 400 with a message — then only emit a clause when SQL can express it.
257    const cond = buildValueCondition(f.op, f.value, type);
258    if (!RAW_SCALAR_TYPES.has(type) || (f.op !== "eq" && f.op !== "neq")) continue;
259    // The JSONB column is compared whole — nest the condition under `value`.
260    clauses.push({ properties: { some: [{ propertyId: f.propertyId, value: cond }] } });
261  }
262  return clauses.length === 1 ? clauses[0]! : clauses.length > 1 ? { AND: clauses } : {};
263}
264