index.ts · Source

index.tspackages/database-engine/src/filter/index.tsraw ← filter11,516 B← Back to the app
1/**
2 * @factory/database-engine/filter — the pure filter engine (TASK-0067).
3 *
4 * Evaluates the shared `FilterNode` tree (and/or/not + conditions) over
5 * `RecordRow`s for optimistic client-side UI and the server's residual path.
6 * No Prisma imports: the push-down to `WHERE` lives in apps/api. Empty
7 * semantics per type (missing EAV row = empty for text/select/number; checkbox
8 * missing = false; multi_select missing = []). Special value tokens
9 * ("me"/"today"/"now") resolve against the EvalContext.
10 */
11 
12import type { FilterNode, FilterOperator } from "@factory/shared/dto";
13 
14import type {
15  RecordRow,
16  PropertyInfo,
17  EvalContext,
18  PropertyValue,
19  DateRangeValue,
20} from "../values/types.js";
21import { dateToMs, dateBucket, DAY_MS } from "../values/dates.js";
22 
23// --- token resolution ----------------------------------------------------------------
24 
25/** Resolve "me"/"today"/"now" tokens in a condition value against ctx. */
26function resolveToken(v: unknown, ctx: EvalContext): unknown {
27  if (v === "me") return ctx.actorUserId ?? null;
28  if (v === "now") return ctx.now;
29  if (v === "today") return new Date(ctx.now).toISOString().slice(0, 10);
30  return v;
31}
32 
33/** Relative date windows for `isRelative`-style comparisons ("this week", …). */
34function relativeWindow(token: string, ctx: EvalContext): { start: number; end: number } | null {
35  const now = new Date(ctx.now);
36  const startOfDay = (d: Date) =>
37    new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth(), d.getUTCDate())).getTime();
38  const dow = (now.getUTCDay() + 6) % 7; // Monday = 0
39  if (token === "today") {
40    const s = startOfDay(now);
41    return { start: s, end: s + DAY_MS - 1 };
42  }
43  if (token === "yesterday") {
44    const s = startOfDay(now) - DAY_MS;
45    return { start: s, end: s + DAY_MS - 1 };
46  }
47  if (token === "tomorrow") {
48    const s = startOfDay(now) + DAY_MS;
49    return { start: s, end: s + DAY_MS - 1 };
50  }
51  if (token === "this week") {
52    const s = startOfDay(now) - dow * DAY_MS;
53    return { start: s, end: s + 7 * DAY_MS - 1 };
54  }
55  if (token === "last week") {
56    const s = startOfDay(now) - dow * DAY_MS - 7 * DAY_MS;
57    return { start: s, end: s + 7 * DAY_MS - 1 };
58  }
59  if (token === "next week") {
60    const s = startOfDay(now) - dow * DAY_MS + 7 * DAY_MS;
61    return { start: s, end: s + 7 * DAY_MS - 1 };
62  }
63  if (token === "this month") {
64    const s = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), 1)).getTime();
65    const e = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth() + 1, 1)).getTime() - 1;
66    return { start: s, end: e };
67  }
68  if (token === "last month") {
69    const s = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth() - 1, 1)).getTime();
70    const e = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), 1)).getTime() - 1;
71    return { start: s, end: e };
72  }
73  if (token === "next month") {
74    const s = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth() + 1, 1)).getTime();
75    const e = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth() + 2, 1)).getTime() - 1;
76    return { start: s, end: e };
77  }
78  if (token === "this year") {
79    const s = Date.UTC(now.getUTCFullYear(), 0, 1);
80    const e = Date.UTC(now.getUTCFullYear() + 1, 0, 1) - 1;
81    return { start: s, end: e };
82  }
83  return null;
84}
85 
86// --- value normalization for comparison ---------------------------------------------
87 
88function rawValue(row: RecordRow, propertyId: string): PropertyValue | undefined {
89  return row.values[propertyId];
90}
91 
92function isMissing(v: PropertyValue | undefined, type: string): boolean {
93  if (v === undefined || v === null) return true;
94  if (type === "checkbox") return v === false ? false : false; // checkbox missing=false is handled by caller
95  if (Array.isArray(v)) return v.length === 0;
96  if (typeof v === "string") return v.length === 0;
97  return false;
98}
99 
100function toComparable(
101  type: string,
102  v: PropertyValue | undefined,
103): number | string | boolean | null | unknown[] | DateRangeValue {
104  if (v === undefined || v === null) return null;
105  switch (type) {
106    case "number":
107    case "created_time":
108    case "last_edited_time":
109    case "unique_id":
110      return typeof v === "number" ? v : null;
111    case "checkbox":
112      return v === true;
113    case "select":
114    case "status":
115    case "date":
116    case "text":
117    case "rich_text":
118    case "title":
119    case "email":
120    case "phone":
121    case "url":
122    case "created_by":
123    case "last_edited_by":
124      return typeof v === "string" ? v : null;
125    case "multi_select":
126    case "person":
127    case "relation":
128    case "files":
129      return Array.isArray(v) ? v : null;
130    case "date_range":
131      return v; // {start,end}
132    default:
133      return v;
134  }
135}
136 
137function dateMs(v: PropertyValue | undefined): number | null {
138  if (typeof v === "string" && /^\d{4}-\d{2}-\d{2}/.test(v)) {
139    try {
140      return dateToMs(v);
141    } catch {
142      return null;
143    }
144  }
145  return null;
146}
147 
148function rangeStartMs(v: PropertyValue | undefined): number | null {
149  if (v && typeof v === "object" && !Array.isArray(v)) {
150    const o = v as DateRangeValue;
151    if (typeof o.start === "string") {
152      try {
153        return dateToMs(o.start);
154      } catch {
155        return null;
156      }
157    }
158  }
159  return null;
160}
161 
162// --- condition evaluation --------------------------------------------------------------
163 
164export function evaluateCondition(
165  row: RecordRow,
166  property: PropertyInfo,
167  condition: Extract<FilterNode, { propertyId: string }>,
168  ctx: EvalContext,
169): boolean {
170  const op = condition.operator as FilterOperator;
171  const token = resolveToken(condition.value, ctx);
172  const value = toComparable(property.type, rawValue(row, property.id));
173 
174  // empty operators are type-agnostic
175  if (op === "is_empty") return isMissing(rawValue(row, property.id), property.type);
176  if (op === "is_not_empty") return !isMissing(rawValue(row, property.id), property.type);
177  if (op === "has_files") return Array.isArray(value) && value.length > 0;
178  if (op === "has_no_files") return !(Array.isArray(value) && value.length > 0);
179  if (op === "isChecked") return value === true;
180  if (op === "isUnchecked") return value !== true;
181 
182  // relative date windows (value is a window token, e.g. "this week")
183  if (
184    property.type === "date" ||
185    property.type === "date_range" ||
186    property.type === "created_time"
187  ) {
188    const win = typeof token === "string" ? relativeWindow(token, ctx) : null;
189    if (win) {
190      const startMs =
191        property.type === "date_range"
192          ? rangeStartMs(rawValue(row, property.id))
193          : dateMs(rawValue(row, property.id));
194      if (startMs === null) return false;
195      return startMs >= win.start && startMs <= win.end;
196    }
197  }
198 
199  // missing value + comparison op → false (except is_not_empty handled above)
200  if (value === null) return false;
201 
202  switch (property.type) {
203    case "number":
204    case "created_time":
205    case "last_edited_time":
206    case "unique_id": {
207      const a = value as number;
208      if (op === "=") return a === (token as number);
209      if (op === "!=") return a !== (token as number);
210      if (op === ">") return a > (token as number);
211      if (op === ">=") return a >= (token as number);
212      if (op === "<") return a < (token as number);
213      if (op === "<=") return a <= (token as number);
214      if (op === "isBetween") {
215        if (Array.isArray(token) && token.length === 2)
216          return a >= (token[0] as number) && a <= (token[1] as number);
217        return false;
218      }
219      return false;
220    }
221    case "date":
222    case "date_range": {
223      const aMs =
224        property.type === "date_range"
225          ? rangeStartMs(rawValue(row, property.id))!
226          : dateMs(rawValue(row, property.id))!;
227      let bMs: number | null = null;
228      if (typeof token === "string" && /^\d{4}-\d{2}-\d{2}/.test(token)) {
229        try {
230          bMs = dateToMs(token);
231        } catch {
232          bMs = null;
233        }
234      } else if (typeof token === "number") bMs = token;
235      if (bMs === null) return false;
236      if (op === "=") return aMs === bMs;
237      if (op === "!=") return aMs !== bMs;
238      if (op === ">") return aMs > bMs;
239      if (op === ">=" || op === "isOnOrAfter") return aMs >= bMs;
240      if (op === "<") return aMs < bMs;
241      if (op === "<=" || op === "isOnOrBefore") return aMs <= bMs;
242      if (op === "isBefore") return aMs < bMs;
243      if (op === "isAfter") return aMs > bMs;
244      return false;
245    }
246    case "checkbox": {
247      if (op === "=")
248        return (value as boolean) === (token === true || token === "true" || token === 1);
249      if (op === "!=")
250        return (value as boolean) !== (token === true || token === "true" || token === 1);
251      return false;
252    }
253    case "multi_select":
254    case "person":
255    case "relation":
256    case "files": {
257      const arr = value as unknown[];
258      const t = token;
259      if (op === "=") return Array.isArray(t) ? t.every((x) => arr.includes(x)) : arr.includes(t);
260      if (op === "!=")
261        return Array.isArray(t) ? !t.every((x) => arr.includes(x)) : !arr.includes(t);
262      if (op === "contains")
263        return Array.isArray(t) ? t.some((x) => arr.includes(x)) : arr.includes(t);
264      if (op === "doesNotContain")
265        return Array.isArray(t) ? !t.some((x) => arr.includes(x)) : !arr.includes(t);
266      return false;
267    }
268    default: {
269      // text-family string comparison
270      const a = String(value ?? "");
271      const b = typeof token === "string" ? token : String(token ?? "");
272      if (op === "=") return a === b;
273      if (op === "!=") return a !== b;
274      if (op === "contains") return a.toLowerCase().includes(b.toLowerCase());
275      if (op === "doesNotContain") return !a.toLowerCase().includes(b.toLowerCase());
276      if (op === "starts_with") return a.toLowerCase().startsWith(b.toLowerCase());
277      if (op === "ends_with") return a.toLowerCase().endsWith(b.toLowerCase());
278      if (op === ">") return a > b;
279      if (op === ">=") return a >= b;
280      if (op === "<") return a < b;
281      if (op === "<=") return a <= b;
282      return false;
283    }
284  }
285}
286 
287export interface GroupNode {
288  node: FilterNode;
289  property: PropertyInfo;
290  condition: Extract<FilterNode, { propertyId: string }>;
291}
292 
293export function evaluateTree(
294  row: RecordRow,
295  tree: FilterNode,
296  properties: Map<string, PropertyInfo>,
297  ctx: EvalContext,
298): boolean {
299  if ("op" in tree) {
300    const op = tree.op;
301    const children = tree.conditions;
302    if (children.length === 0) return op !== "not";
303    if (op === "and") return children.every((c) => evaluateTree(row, c, properties, ctx));
304    if (op === "or") return children.some((c) => evaluateTree(row, c, properties, ctx));
305    if (op === "not") return !evaluateTree(row, children[0]!, properties, ctx);
306    return false;
307  }
308  const property = properties.get(tree.propertyId);
309  if (!property) return false;
310  return evaluateCondition(row, property, tree, ctx);
311}
312 
313/** Apply a filter tree to a set of records (the optimistic-UI + residual path). */
314export function applyFilter(
315  rows: RecordRow[],
316  tree: FilterNode | undefined | null,
317  properties: Map<string, PropertyInfo>,
318  ctx: EvalContext,
319): RecordRow[] {
320  if (!tree) return rows;
321  return rows.filter((r) => evaluateTree(r, tree, properties, ctx));
322}
323 
324export function propertiesMap(list: PropertyInfo[]): Map<string, PropertyInfo> {
325  const m = new Map<string, PropertyInfo>();
326  for (const p of list) m.set(p.id, p);
327  return m;
328}
329 
330export { dateBucket, DAY_MS };
331