cluster.ts

/// <reference types="node" />
/**
 * cluster.ts — pure, side-effect-free helpers for the monthly gap analysis.
 *
 * These functions are deterministic and dependency-free so they can be unit
 * tested without the network, an LLM, or any secrets. The entry point
 * (`monthly-gap-analysis.ts`) wires them to the live analytics API, the LLM
 * summarization pass, Notion, and Slack.
 *
 * The two responsibilities here are:
 *   1. Synthetic-query filtering — strip out the rows that the gap-analysis
 *      pipeline itself generates against the live MCP. Counting those would
 *      re-introduce the self-inflation that polluted the first manual run.
 *   2. Deterministic clustering — group near-identical queries by a normalized
 *      key so the single downstream LLM pass receives compact, de-duplicated
 *      buckets instead of thousands of raw rows.
 */

// ── Types (mirrors of the analytics API JSON shapes) ─────────────────────────
// Re-declared locally rather than imported from ../../src so this script (and
// its tests) stay decoupled from the server's runtime dependency graph
// (pg, express, …). The API contract is the source of truth; see
// src/db/analytics.ts for the canonical definitions.

export interface TopQuery {
  query_text: string;
  tool_name: string;
  count: number;
  avg_result_count: number | null;
  avg_top_score: number | null;
}

export interface EmptyQuery {
  query_text: string;
  tool_name: string;
  source_name: string | null;
  count: number;
  last_seen: string;
}

/** A row shape common to both top and empty queries for clustering purposes. */
export interface QueryRow {
  query_text: string;
  tool_name: string;
  count: number;
}

export interface QueryCluster {
  /** Normalized key the cluster was grouped on. */
  key: string;
  /** Representative (most frequent) raw query text in the cluster. */
  representative: string;
  /** Total occurrences across every member query. */
  totalCount: number;
  /** Distinct raw query texts that mapped to this cluster, count-desc. */
  members: Array<{ query_text: string; count: number }>;
  /** Distinct tool names observed in the cluster. */
  tools: string[];
}

// ── Synthetic-query filter ───────────────────────────────────────────────────

/**
 * Patterns that identify queries generated by the gap-analysis pipeline (or
 * other internal automation) rather than real users.
 *
 * The first manual gap-analysis run reproduced its own probe queries against
 * the live MCP, which were then logged to analytics and counted as "real"
 * demand on the next pass — a self-inflation loop. The two known synthetic
 * shapes are:
 *
 *   - `"<something> integration guide setup"` — the per-integration probe
 *     phrasing the diagnosis fleet uses (matches the SQL `LIKE
 *     '% integration guide setup'` the methodology blueprint calls out).
 *   - any query containing the literal token `_parity` (e.g. `_parity`,
 *     `_parity_check`, `langgraph_parity`) — the parity-suite marker.
 *
 * Matching is case-insensitive and trims surrounding whitespace. Kept as data
 * (not inlined regexes) so the test suite can assert the exact set and new
 * synthetic shapes can be added in one place.
 */
export const SYNTHETIC_SUFFIX = "integration guide setup";
export const SYNTHETIC_PARITY_TOKEN = "_parity";

/**
 * Returns true when `queryText` looks like an internally generated probe and
 * should be excluded from gap analysis.
 */
export function isSyntheticQuery(queryText: string): boolean {
  if (typeof queryText !== "string") return false;
  const normalized = queryText.trim().toLowerCase();
  if (normalized.length === 0) return false;

  // Suffix match: "<x> integration guide setup". endsWith (not includes) so a
  // legitimate user query that merely mentions "integration guide" elsewhere
  // isn't swept up — only the trailing probe phrasing is synthetic.
  if (normalized.endsWith(SYNTHETIC_SUFFIX)) return true;

  // Literal `_parity*` token anywhere in the text.
  if (normalized.includes(SYNTHETIC_PARITY_TOKEN)) return true;

  return false;
}

/**
 * Drop synthetic rows from a list of query rows. Generic over any row carrying
 * a `query_text` so it works for both top-queries and empty-queries payloads.
 */
export function filterSynthetic<T extends { query_text: string }>(
  rows: readonly T[],
): T[] {
  return rows.filter((r) => !isSyntheticQuery(r.query_text));
}

// ── Deterministic clustering ─────────────────────────────────────────────────

// English stop words removed from the normalized clustering key. Small, fixed
// list — enough to collapse phrasings like "how to set up auth" vs
// "setting up the auth" without a stemmer/NLP dependency.
const STOP_WORDS = new Set([
  "a",
  "an",
  "the",
  "to",
  "of",
  "for",
  "in",
  "on",
  "with",
  "and",
  "or",
  "how",
  "do",
  "does",
  "is",
  "are",
  "can",
  "i",
  "my",
  "me",
  "what",
  "when",
  "where",
  "why",
  "use",
  "using",
  "get",
  "getting",
  "set",
  "setup",
  "up",
]);

/**
 * Normalize a query into a clustering key: lowercase, strip punctuation,
 * remove stop words, singularize trailing-`s` tokens crudely, then sort the
 * remaining tokens so word order doesn't fragment a cluster.
 *
 * This is intentionally simple and deterministic — the goal is to collapse
 * obvious restatements of the same need, not to do real semantic clustering
 * (that is what the single downstream LLM pass is for). A query that reduces
 * to no significant tokens falls back to its trimmed, lowercased form so it
 * still clusters with identical restatements.
 */
export function normalizeQueryKey(queryText: string): string {
  const cleaned = queryText
    .toLowerCase()
    .replace(/[^a-z0-9\s]/g, " ")
    .replace(/\s+/g, " ")
    .trim();

  const tokens = cleaned
    .split(" ")
    .filter((t) => t.length > 0 && !STOP_WORDS.has(t))
    .map((t) => (t.length > 3 && t.endsWith("s") ? t.slice(0, -1) : t));

  if (tokens.length === 0) {
    // Nothing significant left — fall back to the cleaned form so identical
    // low-signal queries still group together instead of each becoming its
    // own singleton cluster keyed on "".
    return cleaned;
  }

  return Array.from(new Set(tokens)).sort().join(" ");
}

/**
 * Cluster query rows by their normalized key. Returns clusters sorted by total
 * occurrence count (desc). Each cluster's `representative` is the highest-count
 * raw query text, and `members` lists the distinct raw texts (count-desc).
 *
 * Counts are accumulated DURING clustering: each incoming row adds its `count`
 * to both the cluster total and the per-`query_text` tally in the members map.
 * So the same raw text arriving on multiple rows (e.g. under different tools or
 * sources) is summed into a single member entry, and every member of a cluster
 * contributes its full weight to the cluster total.
 */
export function clusterQueries(rows: readonly QueryRow[]): QueryCluster[] {
  const clusters = new Map<
    string,
    {
      key: string;
      totalCount: number;
      members: Map<string, number>;
      tools: Set<string>;
    }
  >();

  for (const row of rows) {
    const text = row.query_text;
    const count = Number.isFinite(row.count) ? row.count : 0;
    const key = normalizeQueryKey(text);

    let cluster = clusters.get(key);
    if (!cluster) {
      cluster = {
        key,
        totalCount: 0,
        members: new Map<string, number>(),
        tools: new Set<string>(),
      };
      clusters.set(key, cluster);
    }
    cluster.totalCount += count;
    cluster.members.set(text, (cluster.members.get(text) ?? 0) + count);
    if (row.tool_name) cluster.tools.add(row.tool_name);
  }

  const result: QueryCluster[] = [];
  for (const c of clusters.values()) {
    const members = Array.from(c.members.entries())
      .map(([query_text, count]) => ({ query_text, count }))
      .sort(
        (a, b) => b.count - a.count || a.query_text.localeCompare(b.query_text),
      );
    result.push({
      key: c.key,
      representative: members[0]?.query_text ?? c.key,
      totalCount: c.totalCount,
      members,
      tools: Array.from(c.tools).sort(),
    });
  }

  // Sort by total count desc, then by representative for deterministic ties so
  // snapshot-style assertions and the LLM prompt ordering are stable run-to-run.
  result.sort(
    (a, b) =>
      b.totalCount - a.totalCount ||
      a.representative.localeCompare(b.representative),
  );
  return result;
}