🪢 Open source agent evals & observability: Trace, evaluate, and improve LLM applications with one open platform.
TypeScript analyticsautogenevaluationlangchainlarge-language-modelsllama-indexllmllm-evaluationllm-observabilityllmopsmonitoringobservabilityopen-sourceopenaiplaygroundprompt-engineeringprompt-managementself-hostedycombinator
/**
* @fileoverview Utility functions and type definitions for Pivot Table widget functionality
*
* This module provides core utilities for transforming flat query results into nested
* pivot table structures with configurable dimensions, subtotals, and grand totals.
*
* Key Features:
* - Configurable maximum dimensions (currently limited to 2)
* - Support for 0-N dimensions with proper nesting and indentation
* - Automatic subtotal generation for first dimension level
* - Grand total calculation across all metrics
* - Row limiting to prevent performance issues
*
* Usage:
* - Used by PivotTable React component for data transformation
* - Integrated with QueryBuilder for SQL generation
* - Supports future expansion beyond current 2-dimension limit
*/
import { isNotNullOrUndefined } from "@/src/utils/types";
import { type OrderByState } from "@langfuse/shared";
/**
* Default dimension limit for pivot table data rows
* This prevents performance issues and maintains readability
*/
export const MAX_PIVOT_TABLE_DIMENSIONS = 2;
/**
* Default row limit for pivot table data rows (excluding total rows)
* This prevents performance issues with large datasets while maintaining
* useful data visibility.
*/
export const DEFAULT_ROW_LIMIT = 20;
/**
* Maximum number of metrics allowed in a pivot table
* This prevents performance issues and maintains readability
*/
export const MAX_PIVOT_TABLE_METRICS = 10;
/**
* Represents a single row in the processed pivot table structure
* Supports different row types for data, subtotals, and grand totals
* with appropriate styling and indentation levels.
*/
export interface PivotTableRow {
/** Unique identifier for this row */
id: string;
/** Type of row determines styling and behavior */
type: "data" | "subtotal" | "total";
/** Indentation level for nested dimensions (0-based) */
level: number;
/** Display label for the row (dimension value or "Total"/"Subtotal") */
label: string;
/** Metric values for this row, keyed by metric name */
values: Record<string, number | string>;
/** Whether this row represents a subtotal */
isSubtotal?: boolean;
/** Whether this row represents the grand total */
isTotal?: boolean;
/** Original dimension values for this row (for data rows only) */
dimensionValues?: Record<string, string>;
}
/**
* Configuration for pivot table data transformation
* Defines the structure and limits for the resulting table
*/
export interface PivotTableConfig {
/** Array of dimension field names (max length = MAX_PIVOT_TABLE_DIMENSIONS) */
dimensions: string[];
/** Array of metric field names to display as columns */
metrics: string[];
/** Per-metric units, ordered to match `metrics`. Effects label formatting */
units?: (string | undefined)[];
/** Maximum number of data rows to display (before totals) */
rowLimit?: number;
defaultSort?: OrderByState;
}
/**
* Raw database row structure from query results
* Contains dimension values and metric calculations
*/
export interface DatabaseRow {
/** Dimension field values */
[dimensionField: string]: string | number | null;
}
/**
* Validates that the provided configuration is valid for pivot table generation
*
* @param config - Pivot table configuration to validate
* @throws Error if configuration is invalid
*/
export function validatePivotTableConfig(config: PivotTableConfig): void {
if (config.dimensions.length > MAX_PIVOT_TABLE_DIMENSIONS) {
throw new Error(
`Cannot create pivot table with ${config.dimensions.length} dimensions. ` +
`Maximum supported dimensions: ${MAX_PIVOT_TABLE_DIMENSIONS}`,
);
}
if (config.metrics.length === 0) {
throw new Error("At least one metric is required for pivot table");
}
if (config.metrics.length > MAX_PIVOT_TABLE_METRICS) {
throw new Error(
`Cannot create pivot table with ${config.metrics.length} metrics. ` +
`Maximum supported metrics: ${MAX_PIVOT_TABLE_METRICS}`,
);
}
if (config.rowLimit !== undefined && config.rowLimit <= 0) {
throw new Error("Row limit must be a positive number");
}
}
/**
* Generates a unique row ID for pivot table rows
* Uses dimension values and row type to create stable identifiers
*
* @param dimensionValues - Values for each dimension field
* @param type - Type of row (data, subtotal, total)
* @param level - Indentation level for the row
* @returns Unique string identifier for the row
*/
export function generateRowId(
dimensionValues: Record<string, string>,
type: PivotTableRow["type"],
level: number,
): string {
const valueKey = Object.entries(dimensionValues)
.sort(([a], [b]) => a.localeCompare(b))
.map(([key, value]) => `${key}:${value}`)
.join("|");
return `${type}-${level}-${valueKey}`;
}
/**
* Creates an empty metric values object with all metrics set to 0
* Used as a fallback for rows with missing data
*
* @param metrics - Array of metric field names
* @returns Object with all metrics initialized to 0
*/
export function createEmptyMetricValues(
metrics: string[],
): Record<string, number> {
return metrics.reduce(
(acc, metric) => {
acc[metric] = 0;
return acc;
},
{} as Record<string, number>,
);
}
/**
* Recursively processes dimensions to create nested pivot table structure
* This function handles N dimensions dynamically instead of hardcoded cases
*
* @param data - Array of database rows to process at this level
* @param remainingDimensions - Array of dimension names still to be processed
* @param metrics - Array of metric field names
* @param currentLevel - Current indentation level (0-based)
* @param dimensionPath - Array of dimension values from parent levels for labeling
* @returns Array of pivot table rows for this level and all nested levels
*/
function processLevelRecursively(
data: DatabaseRow[],
remainingDimensions: string[],
metrics: string[],
totalDimensions: number,
dimensionPath: string[],
): PivotTableRow[] {
const rows: PivotTableRow[] = [];
const currentDimensionIndex = totalDimensions - remainingDimensions.length;
// Base case: no more dimensions to process, create data rows
if (remainingDimensions.length === 0) {
// Data rows should be at level (totalDimensions - 1) for proper indentation
const dataLevel = Math.max(0, totalDimensions - 1);
// Create data rows for the final level
const dataRows = data.map((row, index) => {
const dimensionValues = extractDimensionValues(row, []);
const metricValues = extractMetricValues(row, metrics);
// Create label from dimension path (all parent dimension values)
const label =
dimensionPath.length > 0 ? dimensionPath.join(" - ") : "Data";
return {
id: `data-${dataLevel}-${dimensionPath.join("-")}-${index}`,
type: "data" as const,
level: dataLevel,
label,
values: metricValues,
dimensionValues,
};
});
return dataRows;
}
// Recursive case: process current dimension and recurse on remaining dimensions
const [currentDimension, ...nextDimensions] = remainingDimensions;
const groups = groupDataByDimension(data, currentDimension!);
const sortedGroups = Object.entries(groups).sort(([a], [b]) =>
a.localeCompare(b),
);
for (const [dimensionValue, groupData] of sortedGroups) {
const newDimensionPath = [...dimensionPath, dimensionValue];
// Add subtotal row for this dimension group BEFORE processing child rows
// Only if there are more dimensions to process (not the deepest level)
if (nextDimensions.length > 0 && groupData.length > 0) {
const subtotalValues = calculateSubtotals(groupData, metrics);
const subtotalRow = createSubtotalRow(
dimensionValue,
subtotalValues,
currentDimensionIndex, // Subtotals at current dimension level
);
rows.push(subtotalRow);
}
// Recursively process remaining dimensions for this group
const childRows = processLevelRecursively(
groupData,
nextDimensions,
metrics,
totalDimensions,
newDimensionPath,
);
rows.push(...childRows);
}
return rows;
}
/**
* Transforms flat query results into nested pivot table structure with totals and subtotals
* Uses a recursive algorithm to handle N dimensions dynamically instead of hardcoded cases
*
* Features:
* - Supports any number of dimensions up to MAX_PIVOT_TABLE_DIMENSIONS
* - Creates subtotals at each dimension level except the deepest
* - Proper indentation and nesting for hierarchical data
* - Grand total calculation across all data
* - Row limiting applied before processing for performance
*
* Algorithm:
* 1. Recursively groups data by each dimension in order
* 2. At each level, processes remaining dimensions for nested structure
* 3. Adds subtotals for non-leaf dimension groups
* 4. Creates data rows at the deepest level with full dimension path
* 5. Appends grand total row at the end
*
* @param data - Array of raw database rows from query
* @param config - Configuration for pivot table generation
* @returns Array of processed rows ready for table rendering
*
* @example
* ```typescript
* const data = [
* { userId: "user1", country: "US", count: 10, avg_score: 85 },
* { userId: "user2", country: "US", count: 5, avg_score: 90 },
* { userId: "user3", country: "CA", count: 8, avg_score: 78 }
* ];
*
* const config = {
* dimensions: ["country", "userId"],
* metrics: ["count", "avg_score"],
* rowLimit: 20
* };
*
* const result = transformToPivotTable(data, config);
* // Returns:
* // 1. Data rows for each user grouped by country
* // 2. Subtotal rows for each country
* // 3. Grand total row for all data
* ```
*/
export function transformToPivotTable(
data: DatabaseRow[],
config: PivotTableConfig,
): PivotTableRow[] {
// Validate configuration
validatePivotTableConfig(config);
const { dimensions, metrics, rowLimit = DEFAULT_ROW_LIMIT } = config;
// Handle empty data
if (data.length === 0) {
return [createGrandTotalRow(metrics, createEmptyMetricValues(metrics))];
}
// Handle zero dimensions - just return grand total
if (dimensions.length === 0) {
const grandTotalValues = calculateGrandTotals(data, metrics);
return [createGrandTotalRow(metrics, grandTotalValues)];
}
// Apply row limit to data before processing
const limitedData = data.slice(0, rowLimit);
// Add grand total row at the beginning
const grandTotalValues = calculateGrandTotals(limitedData, metrics);
const grandTotalRow = createGrandTotalRow(metrics, grandTotalValues);
// Process dimensions recursively
const pivotRows = processLevelRecursively(
limitedData,
dimensions,
metrics,
dimensions.length, // total number of dimensions
[], // dimension path for labeling
);
return [grandTotalRow, ...pivotRows];
}
/**
* Utility function to get dimension values from a database row
* Extracts only the specified dimension fields from the row data
*
* @param row - Database row containing all field values
* @param dimensions - Array of dimension field names to extract
* @returns Object containing only dimension field values
*/
export function extractDimensionValues(
row: DatabaseRow,
dimensions: string[],
): Record<string, string> {
return dimensions.reduce(
(acc, dimension) => {
const value = row[dimension];
acc[dimension] = value?.toString() ?? "";
return acc;
},
{} as Record<string, string>,
);
}
/**
* Utility function to get metric values from a database row
* Extracts only the specified metric fields from the row data
*
* @param row - Database row containing all field values
* @param metrics - Array of metric field names to extract
* @returns Object containing only metric field values
*/
export function extractMetricValues(
row: DatabaseRow,
metrics: string[],
): Record<string, number> {
return metrics.reduce(
(acc, metric) => {
const value = row[metric];
if (typeof value === "number") {
acc[metric] = value;
} else if (typeof value === "string") {
const parsedValue = parseFloat(value);
acc[metric] = isNaN(parsedValue) ? 0 : parsedValue;
} else {
acc[metric] = 0;
}
return acc;
},
{} as Record<string, number>,
);
}
/**
* Type guard to check if a row is a data row (not subtotal or total)
*
* @param row - Pivot table row to check
* @returns True if the row contains actual data, false for summary rows
*/
export function isDataRow(row: PivotTableRow): boolean {
return row.type === "data";
}
/**
* Type guard to check if a row is a subtotal row
*
* @param row - Pivot table row to check
* @returns True if the row is a subtotal, false otherwise
*/
export function isSubtotalRow(row: PivotTableRow): boolean {
return row.type === "subtotal" || row.isSubtotal === true;
}
/**
* Type guard to check if a row is the grand total row
*
* @param row - Pivot table row to check
* @returns True if the row is the grand total, false otherwise
*/
export function isTotalRow(row: PivotTableRow): boolean {
return row.type === "total" || row.isTotal === true;
}
/**
* Groups data by a single dimension field
* Used for creating single-level groupings in pivot table
*
* @param data - Array of database rows to group
* @param dimensionField - Field name to group by
* @returns Object with dimension values as keys and arrays of rows as values
*/
export function groupDataByDimension(
data: DatabaseRow[],
dimensionField: string,
): Record<string, DatabaseRow[]> {
return data.reduce(
(acc, row) => {
const dimensionValue =
(row[dimensionField]?.toString() ?? "").trim() || "n/a";
if (!acc[dimensionValue]) {
acc[dimensionValue] = [];
}
acc[dimensionValue]!.push(row);
return acc;
},
{} as Record<string, DatabaseRow[]>,
);
}
/**
* Detects the aggregation type from a metric field name
* Parses field names like "count_count", "avg_latency", "sum_tokens", "p95_duration"
*
* @param metricName - The metric field name to analyze
* @returns The aggregation type (count, sum, avg, min, max, p95, etc.)
*/
function detectAggregationType(metricName: string): string {
// Extract the aggregation prefix from field names like "count_count", "avg_latency"
const parts = metricName.split("_");
if (parts.length >= 2) {
const prefix = parts[0]?.toLowerCase();
// Map common aggregation prefixes
switch (prefix) {
case "count":
return "count";
case "sum":
return "sum";
case "avg":
case "average":
return "avg";
case "min":
return "min";
case "max":
return "max";
case "p95":
case "p99":
case "p50":
return "percentile";
default:
// Default to sum for unknown aggregations
return "sum";
}
}
// Default to sum if we can't determine the type
return "sum";
}
/**
* Applies the correct aggregation function based on the aggregation type
*
* @param values - Array of numeric values to aggregate
* @param aggregationType - The type of aggregation to perform
* @returns The aggregated result
*/
function applyAggregation(values: number[], aggregationType: string): number {
if (values.length === 0) return 0;
switch (aggregationType) {
case "count":
case "sum":
return values.reduce((sum, val) => sum + val, 0);
case "avg":
return values.reduce((sum, val) => sum + val, 0) / values.length;
case "min":
return Math.min(...values);
case "max":
return Math.max(...values);
case "percentile":
// Percentiles cannot be recalculated exactly from per-group percentiles;
// this unweighted mean is only the fallback used when no count/weight
// metric is available (see calculateSubtotals for the weighted path).
return values.reduce((sum, val) => sum + val, 0) / values.length;
default:
// Default to sum
return values.reduce((sum, val) => sum + val, 0);
}
}
/**
* Picks the column to weight `metric` by when aggregating per-row averages:
* its own `count_<measure>`, else the row count (`count_count` or `count`),
* else the only count column. With several unrelated count columns there is no
* safe choice, so this returns null rather than letting metric order decide.
*/
function findWeightMetric(metric: string, metrics: string[]): string | null {
const counts = metrics.filter(
(m) =>
m !== metric &&
(detectAggregationType(m) === "count" ||
m.toLowerCase().startsWith("count")),
);
const measure = metric.slice(metric.indexOf("_") + 1);
return (
counts.find((m) => m === `count_${measure}`) ??
counts.find((m) => m === "count_count" || m === "count") ??
(counts.length === 1 ? counts[0] : null)
);
}
/**
* Weighted mean of (value, weight) pairs, or null when the total weight is 0
* (so callers can fall back to an unweighted aggregation).
*/
function weightedMean(
pairs: Array<{ value: number; weight: number }>,
): number | null {
let weightedSum = 0;
let totalWeight = 0;
for (const { value, weight } of pairs) {
if (weight > 0) {
weightedSum += value * weight;
totalWeight += weight;
}
}
return totalWeight > 0 ? weightedSum / totalWeight : null;
}
/**
* Calculates subtotals for a group of data rows
* Aggregates metric values across all rows in the group using the correct aggregation function
*
* @param data - Array of database rows to calculate subtotals for
* @param metrics - Array of metric field names to aggregate
* @returns Object with metric names as keys and calculated totals as values
*/
export function calculateSubtotals(
data: DatabaseRow[],
metrics: string[],
): Record<string, number> {
const subtotals: Record<string, number> = {};
for (const metric of metrics) {
const aggregationType = detectAggregationType(metric);
const weightMetric =
aggregationType === "avg" || aggregationType === "percentile"
? findWeightMetric(metric, metrics)
: null;
let result: number;
if (weightMetric) {
// Weight each row's value by its count so the group aggregate equals the
// true overall average (rather than an average of averages). Missing
// fields coerce to 0 via extractMetricValues, as elsewhere in this file;
// a row with no count carries weight 0 and drops out of the weighted mean.
const pairs = data.map((row) => {
const extracted = extractMetricValues(row, [metric, weightMetric]);
return { value: extracted[metric], weight: extracted[weightMetric] };
});
const weighted = weightedMean(pairs);
result =
weighted ??
// No usable weights (e.g. no count column populated) → best-effort
// unweighted aggregation, matching prior behavior.
applyAggregation(
pairs.map((p) => p.value),
aggregationType,
);
} else {
const values = data
.map((row) => extractMetricValues(row, [metric])[metric])
.filter(isNotNullOrUndefined);
result = applyAggregation(values, aggregationType);
}
// Round to 10 decimal places to avoid floating-point precision issues
subtotals[metric] = Math.round(result * 1e10) / 1e10;
}
return subtotals;
}
/**
* Calculates grand totals across all data rows
* Aggregates metric values across the entire dataset
*
* @param data - Array of database rows to calculate grand totals for
* @param metrics - Array of metric field names to aggregate
* @returns Object with metric names as keys and calculated grand totals as values
*/
export function calculateGrandTotals(
data: DatabaseRow[],
metrics: string[],
): Record<string, number> {
return calculateSubtotals(data, metrics);
}
/**
* Creates a subtotal row for a specific dimension group
* Generates subtotal row with appropriate styling and values
*
* @param dimensionValue - The dimension value this subtotal represents
* @param subtotalValues - Calculated subtotal values for metrics
* @param level - Indentation level for the row
* @returns Formatted pivot table subtotal row
*/
export function createSubtotalRow(
dimensionValue: string,
subtotalValues: Record<string, number>,
level: number,
): PivotTableRow {
const dimensionValues = { subtotal: dimensionValue };
return {
id: generateRowId(dimensionValues, "subtotal", level),
type: "subtotal",
level,
label: `${dimensionValue} (Subtotal)`,
values: subtotalValues,
isSubtotal: true,
dimensionValues,
};
}
/**
* Creates the grand total row for the pivot table
* Generates final total row with all metric grand totals
*
* @param metrics - Array of metric field names
* @param grandTotalValues - Calculated grand total values for metrics
* @returns Formatted pivot table grand total row
*/
export function createGrandTotalRow(
metrics: string[],
grandTotalValues: Record<string, number>,
): PivotTableRow {
const dimensionValues = { total: "grand" };
return {
id: generateRowId(dimensionValues, "total", 0),
type: "total",
level: 0,
label: "Total",
values: grandTotalValues,
isTotal: true,
dimensionValues,
};
}
/**
* Sorts pivot table rows hierarchically based on sort configuration
*
* Hierarchical sorting behavior:
* 1. Sort groups (subtotal rows) by their total values first
* 2. Sort individual items within each group
* 3. Maintain grand total at the top
* 4. Preserve group hierarchy and indentation
*
* @param rows - Array of pivot table rows to sort
* @param sortConfig - Sort configuration with column and order
* @returns Sorted array of pivot table rows
*/
export function sortPivotTableRows(
rows: PivotTableRow[],
sortConfig: { column: string; order: "ASC" | "DESC" },
): PivotTableRow[] {
if (!sortConfig || !sortConfig.column) {
return rows;
}
const { column, order } = sortConfig;
const isAscending = order === "ASC";
// Helper function to get sortable value from a row
const getSortValue = (row: PivotTableRow): number => {
const value = row.values[column];
if (typeof value === "number") {
return value;
}
if (typeof value === "string") {
// Try to parse as number, fallback to 0
const parsed = parseFloat(value);
return isNaN(parsed) ? 0 : parsed;
}
return 0;
};
// Helper function to compare two values
const compareValues = (a: number, b: number): number => {
if (isAscending) {
return a - b;
}
return b - a;
};
// Helper function to compare strings (for equal numeric values)
const compareLabels = (a: string, b: string): number => {
if (isAscending) {
return a.localeCompare(b);
}
return b.localeCompare(a);
};
// Separate different row types for processing
const grandTotalRows = rows.filter(isTotalRow);
const subtotalRows = rows.filter(isSubtotalRow);
const dataRows = rows.filter(isDataRow);
// Sort subtotal rows (groups) by their total values
const sortedSubtotalRows = subtotalRows.sort((a, b) => {
const aValue = getSortValue(a);
const bValue = getSortValue(b);
const valueComparison = compareValues(aValue, bValue);
if (valueComparison !== 0) {
return valueComparison;
}
// For equal values, sort by label
return compareLabels(a.label, b.label);
});
// Group data rows by their parent subtotal
const dataRowsByGroup: Record<string, PivotTableRow[]> = {};
for (const row of dataRows) {
// Find the parent subtotal for this data row
const parentGroup = findParentGroup(row, subtotalRows);
const groupKey = parentGroup?.label || "ungrouped";
if (!dataRowsByGroup[groupKey]) {
dataRowsByGroup[groupKey] = [];
}
dataRowsByGroup[groupKey].push(row);
}
// Sort data rows within each group
for (const groupKey in dataRowsByGroup) {
dataRowsByGroup[groupKey].sort((a, b) => {
const aValue = getSortValue(a);
const bValue = getSortValue(b);
const valueComparison = compareValues(aValue, bValue);
if (valueComparison !== 0) {
return valueComparison;
}
// For equal values, sort by label
return compareLabels(a.label, b.label);
});
}
// Reconstruct the sorted rows maintaining hierarchy
const sortedRows: PivotTableRow[] = [];
// Add grand total at the top
sortedRows.push(...grandTotalRows);
// Add sorted subtotal rows with their sorted data rows
for (const subtotalRow of sortedSubtotalRows) {
sortedRows.push(subtotalRow);
// Add sorted data rows for this group
const groupKey = subtotalRow.label;
const groupDataRows = dataRowsByGroup[groupKey] || [];
sortedRows.push(...groupDataRows);
}
// Add any ungrouped data rows at the end
const ungroupedRows = dataRowsByGroup["ungrouped"] || [];
sortedRows.push(...ungroupedRows);
return sortedRows;
}
/**
* Finds the parent subtotal group for a data row
*
* @param dataRow - The data row to find parent for
* @param subtotalRows - Array of all subtotal rows
* @returns The parent subtotal row or null if not found
*/
function findParentGroup(
dataRow: PivotTableRow,
subtotalRows: PivotTableRow[],
): PivotTableRow | null {
// For now, use a simple approach: find subtotal row with matching level
// This can be enhanced later for more complex grouping logic
const parentLevel = dataRow.level - 1;
return (
subtotalRows.find(
(subtotal) =>
subtotal.level === parentLevel &&
subtotal.label.includes(dataRow.label.split(" - ")[0]), // Simple matching
) || null
);
}
/**
* Gets the next sort state in the simple cycle: DESC → ASC → unsorted
*
* @param defaultSort - Default sort state (OrderByState or null for unsorted)
* @param currentSort - Current sort state (OrderByState or null for unsorted)
* @param column - Column to sort by
* @returns Next sort state in the cycle
*/
export function getNextSortState(
defaultSort: OrderByState,
currentSort: OrderByState,
column: string,
): OrderByState | null {
// Different column or no current sort → start with DESC
if (!currentSort || currentSort.column !== column) {
return { column, order: "DESC" };
}
// Same column: DESC → ASC → null (unsorted)
if (currentSort.order === "DESC") {
return { column, order: "ASC" };
}
// Column other than the default column, go back to default
if (
currentSort.order === "ASC" &&
currentSort.column !== defaultSort?.column
) {
return defaultSort || null;
}
// Default column, flip back to DESC
if (
currentSort.order === "ASC" &&
currentSort.column === defaultSort?.column
) {
return { column, order: "DESC" };
}
// Fallback (shouldn't happen)
return null;
}