​

Benchmarking API

The benchmarking API provides cross-site KPI comparison for organizations with multiple sites (level-1 workspaces). It enables executive-level performance analysis through site-level KPI averaging, SQCDP scorecard matrices, time-series trend overlays, and ranked performance tables. All procedures require organization-level authentication (orgProcedure).

​

Data Model

Benchmarking operates on the following hierarchy:

Organization
  +-- Workspaces (level=1 = "Sites")
        +-- Projects
              +-- Boards
                    +-- KPI Definitions
                          +-- KPI Values

KPI definitions are mapped back to their ancestor site (level-1 workspace) through the project-board chain. This mapping enables per-site aggregation and cross-site comparison for any KPI name.

​

Traffic-Light Status Evaluation

All benchmark values are evaluated against KPI thresholds to produce a traffic-light status:

​

benchmarking.getSiteBenchmark

Compare a specific KPI across all sites in the organization. For a given KPI name (and optional date range and category), aggregates values per site and returns the average value for each site plus the organization-wide average.

Type: Query

Input Schema:

{
  kpiName: string;           // Required: KPI name to compare (e.g., "OEE")
  category?: "S" | "Q" | "C" | "D" | "P" | "custom";
  dateFrom?: string;         // ISO date for analysis start
  dateTo?: string;           // ISO date for analysis end
}

Output:

{
  kpiName: string;
  unit: string;              // e.g., "%", "ppm", "hours"
  direction: string;         // "higher_is_better" | "lower_is_better"
  orgAverage: number;        // Organization-wide average (rounded to 2 decimals)
  sites: Array<{
    siteId: string;
    siteName: string;
    average: number;         // Site average (rounded to 2 decimals)
    count: number;           // Number of data points
    status: "green" | "amber" | "red" | null;
  }>;
}

Sites are sorted by average value (best first, based on the KPI’s direction). Non-numeric KPI values are filtered out before aggregation to prevent NaN pollution.

​

benchmarking.getCategoryBenchmark

Generate the SQCDP scorecard matrix with sites as rows and categories as columns. For each site-category cell, aggregates all KPIs in that category and returns an overall traffic-light status.

Type: Query

Input Schema:

{
  category?: "S" | "Q" | "C" | "D" | "P" | "custom";  // Optional: filter to one category
  dateFrom?: string;         // ISO date for analysis start
  dateTo?: string;           // ISO date for analysis end
}

Output:

{
  sites: Array<{
    id: string;
    name: string;
    slug: string;
    color: string;
  }>;
  categories: string[];       // e.g., ["S", "Q", "C", "D", "P"]
  scorecard: Array<{
    siteId: string;
    siteName: string;
    siteColor: string;
    cells: Array<{
      category: string;
      average: number | null;
      status: "green" | "amber" | "red" | null;
      count: number;          // Number of KPIs in this cell
    }>;
  }>;
}

This view powers the executive dashboard where site managers and portfolio directors quickly identify which sites are performing well or poorly across each SQCDP dimension.

​

benchmarking.getTrendComparison

Get time-series data for a KPI across all sites, suitable for overlaid line charts. Returns data points grouped by site, allowing visual comparison of how a KPI trends over time at different locations.

Type: Query

Input Schema:

{
  kpiName: string;           // Required: KPI name to compare
  category?: "S" | "Q" | "C" | "D" | "P" | "custom";
  dateFrom?: string;         // ISO date for analysis start
  dateTo?: string;           // ISO date for analysis end
}

Output:

{
  kpiName: string;
  unit: string;
  direction: string;
  sites: Array<{
    siteId: string;
    siteName: string;
    dataPoints: Array<{
      date: string;
      value: number;
    }>;
  }>;
}

Data points are sorted chronologically within each site. The UI renders these as overlaid line charts where each site is a different-colored series.

​

benchmarking.getRanking

Rank sites by a specific KPI value or by overall performance across all KPIs. Returns an ordered list from best to worst based on the KPI’s direction.

Type: Query

Input Schema:

{
  kpiName?: string;          // Specific KPI to rank by (omit for overall composite)
  category?: "S" | "Q" | "C" | "D" | "P" | "custom";
  dateFrom?: string;
  dateTo?: string;
}

Output:

{
  rankings: Array<{
    rank: number;
    siteId: string;
    siteName: string;
    average: number;
    count: number;
    status: "green" | "amber" | "red" | null;
  }>;
  kpiName: string | null;
  unit: string;
  direction: string;
}

Rankings are computed by sorting the per-site averages. For higher_is_better KPIs, the highest average is ranked first. For lower_is_better KPIs, the lowest average is ranked first.

​

Permissions

​

Multi-Tenancy

All benchmarking queries are scoped to the caller’s organizationId via orgProcedure. Cross-site comparisons only happen within the same organization. The KPI-to-site mapping join includes organizationId filters on both workspaces and kpiDefinitions tables for defense-in-depth tenant isolation.

​

Performance Considerations

• The KPI-to-site mapping uses inner joins through the full hierarchy (workspace -> project -> board -> kpiDefinition), ensuring only KPIs with a complete ancestry chain are included.
• Date range filtering is applied at the kpiValues query level to limit the dataset before aggregation.
• Non-numeric values are filtered out during aggregation to prevent NaN from corrupting averages.
• Sites with no matching KPI data for the requested parameters are excluded from results rather than returned with zero values.