Skip to content

Your favorite shift management application!

12.6 KB
Raw Blame
export interface DistributionEntry {
  value: string;
  instances: number;
  share: number;
  /** Set only on the size-guard remainder entry; see MAX_DISTRIBUTION_ENTRIES. */
  remainder?: true;
}

export interface VersionEntry extends DistributionEntry {
  devInstances: number;
}

export interface HistoryPoint {
  day: string;
  instances: number;
}

export interface PublicAggregate {
  computedAt: string;
  instances: { total: number; activeLast7Days: number; newLast30Days: number };
  history: HistoryPoint[];
  versions: VersionEntry[];
  environment: Record<string, DistributionEntry[]>;
  configuration: Record<string, DistributionEntry[]>;
  sizes: Record<string, DistributionEntry[]>;
  features: Record<string, DistributionEntry[]>;
  customFieldTypes: DistributionEntry[];
  health: { avgUptimeHours: number; instancesWithSyncFailures: number };
}

const ACTIVE_WINDOW_DAYS = 30;
const HISTORY_DAYS = 90;
// Payload string fields are unbounded and the hub is unauthenticated.
const MAX_VALUE_LENGTH = 64;
// SIZE GUARD, NOT A PRIVACY RULE: a single ping can carry arbitrarily many
// distinct values (features.customFields.types is an unbounded string array), and
// every distinct value becomes a row in /data.json and a bar on the page. The long
// tail past this many entries is summed into one remainder entry so both stay
// bounded. Nothing is hidden for being rare — small counts are published as-is.
const MAX_DISTRIBUTION_ENTRIES = 25;

const remainderLabel = (values: number): string => `other (${values} more values)`;

const iso = (now: Date, daysAgo: number): string =>
  new Date(now.getTime() - daysAgo * 86_400_000).toISOString();

const truncate = (value: string): string =>
  value.length > MAX_VALUE_LENGTH ? value.slice(0, MAX_VALUE_LENGTH) : value;

interface RawGroup {
  key: string;
  value: string | number | null;
  instances: number;
}

// The GROUP BY queries have no ORDER BY, so entries tied on count could otherwise
// straddle the cap differently between runs — by name one hour, in the remainder
// the next. The value breaks the tie deterministically.
const byCountThenValue = (
  a: { value: string; instances: number },
  b: { value: string; instances: number },
): number => b.instances - a.instances || (a.value < b.value ? -1 : a.value > b.value ? 1 : 0);

const share = (instances: number, base: number): number =>
  base > 0 ? Math.round((instances / base) * 10_000) / 10_000 : 0;

/** Sorts by size, caps the long tail (see MAX_DISTRIBUTION_ENTRIES), adds shares. */
function collapse(rows: RawGroup[], base: number): DistributionEntry[] {
  const sorted = rows
    .map((row) => ({ value: truncate(String(row.value)), instances: row.instances }))
    .sort(byCountThenValue);

  const kept = sorted.slice(0, MAX_DISTRIBUTION_ENTRIES);
  const tail = sorted.slice(MAX_DISTRIBUTION_ENTRIES);

  const entries: DistributionEntry[] = kept.map((row) => ({
    value: row.value,
    instances: row.instances,
    share: share(row.instances, base),
  }));

  if (tail.length > 0) {
    const instances = tail.reduce((sum, row) => sum + row.instances, 0);
    entries.push({
      value: remainderLabel(tail.length),
      instances,
      share: share(instances, base),
      remainder: true,
    });
  }
  return entries;
}

/** Same tail cap as collapse(), plus a dev-instance count carried per entry. */
function buildVersions(
  rows: Array<{ value: string; instances: number; dev_instances: number }>,
  base: number,
): VersionEntry[] {
  const sorted = rows
    .map((row) => ({
      value: truncate(row.value),
      instances: row.instances,
      devInstances: Number(row.dev_instances ?? 0),
    }))
    .sort(byCountThenValue);

  const kept = sorted.slice(0, MAX_DISTRIBUTION_ENTRIES);
  const tail = sorted.slice(MAX_DISTRIBUTION_ENTRIES);

  const entries: VersionEntry[] = kept.map((row) => ({
    value: row.value,
    instances: row.instances,
    share: share(row.instances, base),
    devInstances: row.devInstances,
  }));

  if (tail.length > 0) {
    const instances = tail.reduce((sum, row) => sum + row.instances, 0);
    entries.push({
      value: remainderLabel(tail.length),
      instances,
      share: share(instances, base),
      devInstances: tail.reduce((sum, row) => sum + row.devInstances, 0),
      remainder: true,
    });
  }
  return entries;
}

function groupByKey(rows: RawGroup[], base: number): Record<string, DistributionEntry[]> {
  const buckets = new Map<string, RawGroup[]>();
  for (const row of rows) {
    const list = buckets.get(row.key) ?? [];
    list.push(row);
    buckets.set(row.key, list);
  }
  const out: Record<string, DistributionEntry[]> = {};
  for (const [key, list] of buckets) out[key] = collapse(list, base);
  return out;
}

// Unpivots columns into (key, value) pairs, reading the already-filtered `active`
// CTE (see buildAggregate) instead of the view directly. D1 caps a compound SELECT
// at five terms, so UNION ALL is not an option for the columns themselves.
const unpivot = (pairs: string) => `
  SELECT t.key AS key, t.value AS value, COUNT(*) AS instances
  FROM active AS p, json_each(json_object(${pairs})) AS t
  GROUP BY t.key, t.value`;

// Wraps a (key?, value, instances[, extra]) query as a JSON array so it can sit
// in its own scalar-subquery column of the single mega-query below. That keeps
// every one of these reports reading the same MATERIALIZED `active` CTE instead
// of each re-deriving "the latest ping per instance" from the full ping history.
const asJsonArray = (columns: string, innerSql: string) =>
  `(SELECT COALESCE(json_group_array(json_object(${columns})), '[]') FROM (${innerSql}))`;

const VERSIONS_SQL = `
  SELECT app_version AS value, COUNT(*) AS instances,
         SUM(CASE WHEN app_is_dev = 1 THEN 1 ELSE 0 END) AS dev_instances
  FROM active GROUP BY app_version`;

const FIELD_TYPES_SQL = `
  SELECT t.value AS value, COUNT(DISTINCT p.id) AS instances
  FROM active AS p, json_each(p.features_custom_field_types) AS t
  GROUP BY t.value`;

/**
 * Every report below used to read the `latest_pings` view independently (nine
 * references in total), and the view itself has no time bound: each reference
 * re-scanned the entire ping history to find each instance's newest row before
 * filtering it down to the active window. At the README's own worked example
 * (~36,000 rows/year) that is roughly 9x the necessary reads per cron run.
 *
 * Instead, `active` is materialized exactly once (one scan of `instance_pings`,
 * enforced with the MATERIALIZED hint so SQLite doesn't just inline the CTE per
 * reference) and every report reads that bounded, already-filtered result as a
 * scalar subquery of one row -- one statement, one full scan, regardless of how
 * many breakdowns are computed from it.
 */
const MEGA_SQL = `
  WITH active AS MATERIALIZED (SELECT * FROM latest_pings WHERE received_at >= ?1)
  SELECT
    (SELECT COUNT(*) FROM active) AS total,
    (SELECT COUNT(*) FROM active WHERE received_at >= ?2) AS active_7d,
    (SELECT COUNT(*) FROM (
       SELECT instance_id FROM instance_pings
       WHERE instance_id IS NOT NULL
       GROUP BY instance_id HAVING MIN(received_at) >= ?1
    )) AS new_30d,
    ${asJsonArray("'value', value, 'instances', instances, 'dev_instances', dev_instances", VERSIONS_SQL)} AS versions_json,
    ${asJsonArray(
      "'key', key, 'value', value, 'instances', instances",
      unpivot(
        `'node', p.runtime_node, 'platform', p.runtime_platform, 'arch', p.runtime_arch,
         'sqlite', p.runtime_sqlite, 'timezone', p.runtime_timezone`,
      ),
    )} AS environment_json,
    ${asJsonArray(
      "'key', key, 'value', value, 'instances', instances",
      unpivot(
        `'auth_enabled', p.config_auth_enabled, 'guest_access', p.config_guest_access,
         'registration_open', p.config_registration_open,
         'update_check_enabled', p.config_update_check_enabled,
         'default_locale', p.config_default_locale,
         'rate_limit_overrides', p.config_rate_limit_overrides`,
      ),
    )} AS configuration_json,
    ${asJsonArray(
      "'key', key, 'value', value, 'instances', instances",
      unpivot(
        `'users', p.scale_users, 'calendars', p.scale_calendars, 'shifts', p.scale_shifts,
         'presets', p.scale_presets, 'notes', p.scale_notes, 'bundles', p.scale_bundles,
         'shares', p.scale_shares, 'access_tokens', p.scale_access_tokens,
         'signups', p.scale_signups`,
      ),
    )} AS sizes_json,
    ${asJsonArray(
      "'key', key, 'value', value, 'instances', instances",
      unpivot(
        `'external_syncs', p.features_external_syncs,
         'calendar_view_overrides', p.features_calendar_view_overrides,
         'custom_fields_count', p.features_custom_fields_count,
         'archived_presets', p.features_archived_presets,
         'split_shifts', p.features_split_shifts`,
      ),
    )} AS features_json,
    ${asJsonArray("'value', value, 'instances', instances", FIELD_TYPES_SQL)} AS field_types_json,
    (SELECT ROUND(AVG(health_uptime_hours), 1) FROM active) AS avg_uptime,
    (SELECT SUM(CASE WHEN health_sync_failures_24h > 0 THEN 1 ELSE 0 END) FROM active) AS with_failures`;

interface VersionJsonRow {
  value: string;
  instances: number;
  dev_instances: number;
}

// Falling back to [] would otherwise publish an all-zeros aggregate with nothing
// in the logs, so every unexpected shape is reported before it is swallowed.
function parseJsonArray<T>(value: unknown): T[] {
  if (typeof value !== "string") {
    console.error(`Aggregate column was ${typeof value}, expected a JSON string`);
    return [];
  }
  try {
    const parsed: unknown = JSON.parse(value);
    if (Array.isArray(parsed)) return parsed as T[];
    console.error("Aggregate column parsed to a non-array");
    return [];
  } catch {
    console.error("Aggregate column is not valid JSON");
    return [];
  }
}

export async function buildAggregate(db: D1Database, now: Date = new Date()): Promise<PublicAggregate> {
  const activeCutoff = iso(now, ACTIVE_WINDOW_DAYS);
  const weekCutoff = iso(now, 7);
  const historyCutoff = iso(now, HISTORY_DAYS).slice(0, 10);

  const [mega, history] = await db.batch([
    db.prepare(MEGA_SQL).bind(activeCutoff, weekCutoff),
    db
      .prepare(
        `SELECT ping_day AS day, COUNT(DISTINCT COALESCE(instance_id, 'anon:' || id)) AS instances
         FROM instance_pings WHERE ping_day >= ?1 GROUP BY ping_day ORDER BY day`,
      )
      .bind(historyCutoff),
  ]);

  const megaRow = (mega.results[0] ?? {}) as Record<string, unknown>;
  const base = Number(megaRow.total ?? 0);

  const instances = {
    total: base,
    activeLast7Days: Number(megaRow.active_7d ?? 0),
    newLast30Days: Number(megaRow.new_30d ?? 0),
  };

  const versionRows = parseJsonArray<VersionJsonRow>(megaRow.versions_json);

  return {
    computedAt: now.toISOString(),
    instances,
    history: history.results as unknown as HistoryPoint[],
    versions: buildVersions(
      versionRows.map((r) => ({
        value: r.value,
        instances: Number(r.instances),
        dev_instances: Number(r.dev_instances),
      })),
      base,
    ),
    environment: groupByKey(parseJsonArray<RawGroup>(megaRow.environment_json), base),
    configuration: groupByKey(parseJsonArray<RawGroup>(megaRow.configuration_json), base),
    sizes: groupByKey(parseJsonArray<RawGroup>(megaRow.sizes_json), base),
    features: groupByKey(parseJsonArray<RawGroup>(megaRow.features_json), base),
    customFieldTypes: collapse(
      parseJsonArray<RawGroup>(megaRow.field_types_json).map((r) => ({ ...r, key: "types" })),
      base,
    ),
    health: {
      avgUptimeHours: Number(megaRow.avg_uptime ?? 0),
      instancesWithSyncFailures: Number(megaRow.with_failures ?? 0),
    },
  };
}

export async function storeAggregate(db: D1Database, aggregate: PublicAggregate): Promise<void> {
  const result = await db
    .prepare("INSERT OR REPLACE INTO aggregates (key, json, computed_at) VALUES ('public', ?1, ?2)")
    .bind(JSON.stringify(aggregate), aggregate.computedAt)
    .run();
  if (!result.success) throw new Error("Aggregate write did not report success");
}

export async function readAggregate(db: D1Database): Promise<PublicAggregate | null> {
  let row: { json: string } | null;
  try {
    row = await db.prepare("SELECT json FROM aggregates WHERE key = 'public'").first<{ json: string }>();
  } catch (error) {
    // A D1 read error (e.g. the table is missing on an un-migrated database)
    // degrades to the empty state instead of a 500; see src/page.ts / src/index.ts.
    console.error(error instanceof Error ? error.message : "Aggregate read failed");
    return null;
  }
  if (!row) return null;
  try {
    return JSON.parse(row.json) as PublicAggregate;
  } catch {
    // A corrupt row is treated exactly like a missing one.
    return null;
  }
}