index.ts · Source

index.tspackages/database-engine/src/rollups/index.tsraw ← rollups5,347 B← Back to the app
1/**
2 * @factory/database-engine/rollups — the pure aggregate engine (TASK-0083 R1).
3 *
4 * `aggregateRollup` runs over the LINKED records' values for the target
5 * property (already filtered by the rollup's `filter` tree via the filter
6 * engine — the filter filters the *related* records, e.g. "status = Done"
7 * before counting). The recorded empty-set rules:
8 *   - COUNT over zero matching records = 0 (a count is defined on ∅);
9 *   - SUM/AVERAGE over zero matching NUMERIC values = null (no values);
10 *   - MIN/MAX over zero values = null; AVERAGE of zero numerics = null
11 *     (never NaN);
12 *   - PERCENT = matching / all-linked (0–1); no filter → 1.0 (degenerate,
13 *     the UI warns);
14 *   - UNIQUE = deduped value array; `display: 'count'` → the length.
15 * Result type: `inferredType(aggregate, targetType)` — SUM/AVG/COUNT/PERCENT
16 * → number; MIN/MAX → number|date; UNIQUE → array (count mode → number).
17 */
18 
19import type { PropertyValue, PropertyInfo } from "../values/types.js";
20 
21export type RollupAggregate = "COUNT" | "SUM" | "AVERAGE" | "MIN" | "MAX" | "PERCENT" | "UNIQUE";
22 
23export const ROLLUP_AGGREGATES: readonly RollupAggregate[] = [
24  "COUNT",
25  "SUM",
26  "AVERAGE",
27  "MIN",
28  "MAX",
29  "PERCENT",
30  "UNIQUE",
31];
32 
33export interface RollupConfig {
34  aggregate: RollupAggregate;
35  targetPropertyType: string;
36  display?: "list" | "count"; // UNIQUE only
37}
38 
39export type RollupValue = number | string | string[] | null;
40 
41/** Config-time guard: which target property types each aggregate allows. */
42export function aggregateAllowed(aggregate: RollupAggregate, targetType: string): boolean {
43  switch (aggregate) {
44    case "SUM":
45    case "AVERAGE":
46      return targetType === "number";
47    case "MIN":
48    case "MAX":
49      return targetType === "number" || targetType === "date";
50    case "COUNT":
51    case "PERCENT":
52    case "UNIQUE":
53      return true;
54  }
55}
56 
57export function inferredType(
58  aggregate: RollupAggregate,
59  targetType: string,
60): "number" | "date" | "array" {
61  if (aggregate === "MIN" || aggregate === "MAX") return targetType === "date" ? "date" : "number";
62  if (aggregate === "UNIQUE") return "array";
63  return "number";
64}
65 
66function isNumeric(v: PropertyValue): v is number {
67  return typeof v === "number" && Number.isFinite(v);
68}
69 
70function dateKey(v: PropertyValue): number | null {
71  if (typeof v === "string" && /^\d{4}-\d{2}-\d{2}/.test(v)) {
72    const d = new Date(v.length === 10 ? v + "T00:00:00Z" : v).getTime();
73    return Number.isNaN(d) ? null : d;
74  }
75  return null;
76}
77 
78/**
79 * Aggregate `values` (one entry per matching linked record — null when the
80 * record has no value for the target property) with the rollup config.
81 * `allLinkedCount` = total linked records (pre-filter) — needed for PERCENT.
82 */
83export function aggregateRollup(
84  values: readonly (PropertyValue | undefined)[],
85  config: RollupConfig,
86  allLinkedCount?: number,
87): RollupValue {
88  const present = values.filter((v): v is PropertyValue => v !== undefined && v !== null);
89  switch (config.aggregate) {
90    case "COUNT":
91      // count of matching records (rows), not non-null values
92      return values.length;
93    case "SUM": {
94      const nums = present.filter(isNumeric);
95      if (nums.length === 0) return null; // SUM of zero numerics = null (recorded)
96      return nums.reduce((a, b) => a + b, 0);
97    }
98    case "AVERAGE": {
99      const nums = present.filter(isNumeric);
100      if (nums.length === 0) return null; // AVERAGE of zero numerics = null (not NaN)
101      return nums.reduce((a, b) => a + b, 0) / nums.length;
102    }
103    case "MIN":
104    case "MAX": {
105      if (config.targetPropertyType === "date") {
106        const dated = present.map((v) => dateKey(v)).filter((k): k is number => k !== null);
107        if (dated.length === 0) return null;
108        const best = config.aggregate === "MIN" ? Math.min(...dated) : Math.max(...dated);
109        return new Date(best).toISOString().slice(0, 10);
110      }
111      const nums = present.filter(isNumeric);
112      if (nums.length === 0) return null;
113      return config.aggregate === "MIN" ? Math.min(...nums) : Math.max(...nums);
114    }
115    case "PERCENT": {
116      const total = allLinkedCount ?? values.length;
117      if (total === 0) return 0;
118      return values.length / total;
119    }
120    case "UNIQUE": {
121      const seen = new Set<PropertyValue>();
122      for (const v of present) seen.add(v);
123      const list = [...seen].map((v) =>
124        typeof v === "number" ? String(v) : typeof v === "string" ? v : JSON.stringify(v),
125      );
126      if (config.display === "count") return list.length;
127      return list;
128    }
129  }
130}
131 
132/**
133 * Collect the linked target records' values for the target property.
134 * `links` = this record's target record ids; `targetValues` = targetRecordId →
135 * value map (the API batch-fetches both in one IN query — TASK-0083 R1).
136 * When `filter` matches are precomputed by the caller (via applyFilter over
137 * the target db's rows), pass `matchIds` to restrict.
138 */
139export function collectLinkedValues(
140  links: readonly string[],
141  targetValues: ReadonlyMap<string, PropertyValue | undefined>,
142  matchIds?: ReadonlySet<string>,
143): (PropertyValue | undefined)[] {
144  return links
145    .filter((id) => matchIds === undefined || matchIds.has(id))
146    .map((id) => targetValues.get(id));
147}
148 
149export type { PropertyInfo };
150