Function breakdown

telemetry-dashboard/src/api.ts:488–583

* Sums one dimension over the range. * * On the `machines` metric: `daily_dim_counts.machines` is per day, so summing * it over a range gives **machine-days**, not distinct machines — a machine * seen on ten days counts ten times. A range-wide distinct count per dimension * value is not recover

(env: Env, url: URL, range: Range)

 * `max` is over a single row and the question does not arise.
 */
async function breakdown(env: Env, url: URL, range: Range): Promise<ApiResult> {
  const dim = url.searchParams.get('dim') ?? '';
  const spec = DIMS[dim];
  if (!spec) return fail(`unknown dim — one of: ${Object.keys(DIMS).join(', ')}`);

  const requestedMetric = url.searchParams.get('metric');
  if (requestedMetric !== null && requestedMetric !== 'count' && requestedMetric !== 'machines') {
    return fail('metric must be count or machines');
  }
  const metric = requestedMetric ?? spec.metric;

  const rawLimit = url.searchParams.get('limit');
  const limit = rawLimit === null ? DEFAULT_BREAKDOWN_LIMIT : Number(rawLimit);
  if (!Number.isInteger(limit) || limit < 1 || limit > MAX_BREAKDOWN_LIMIT) {
    return fail(`limit must be an integer between 1 and ${MAX_BREAKDOWN_LIMIT}`);
  }

  const event = url.searchParams.get('event') ?? spec.event ?? null;
  if (event !== null && !/^[a-z_]{1,32}$/.test(event)) return fail('event must be a bare event name');

  const binds: (string | number)[] = [dim, range.from, range.to];
  if (event !== null) binds.push(event);

  const { results } = await env.DB.prepare(
    `SELECT value, sum(day_count) AS count, sum(day_machines) AS machines
       FROM (SELECT day, value, sum(count) AS day_count, max(machines) AS day_machines
               FROM daily_dim_counts
              WHERE dim = ? AND day BETWEEN ? AND ?${event !== null ? ' AND event = ?' : ''}
              GROUP BY day, value)
      GROUP BY value`,
  )
    .bind(...binds)
    .all<BreakdownRow>();

  const rows = results.map((r) => ({
    value: r.value,
    count: r.count ?? 0,
    machines: r.machines ?? 0,
  }));

  const pick = (r: BreakdownRow): number => (metric === 'machines' ? r.machines : r.count);

  let ordered: BreakdownRow[];
  let truncated = false;
  if (spec.order === 'bucket' && spec.buckets) {
    // An ordered scale: the buckets keep their own order and all of them show,
    // including empty ones, so the ordinal colour ramp always means the same thing.
    const found = new Map(rows.map((r) => [r.value, r]));
    const extra = rows.filter((r) => !spec.buckets?.includes(r.value)).sort((x, y) => pick(y) - pick(x));
    ordered = [
      ...spec.buckets.map((b) => found.get(b) ?? { value: b, count: 0, machines: 0 }),
      ...extra,
    ];
  } else {
    const sorted = [...rows].sort(
      spec.order === 'version_desc'
        ? (x, y) => compareVersionsDesc(x.value, y.value)
        : (x, y) => pick(y) - pick(x) || x.value.localeCompare(y.value),