sql-server-query-ai/lib/jev.ts

import { TypeSafeClient, score, noul, choice } from "@typesafe-ai/sdk";
import { digestForModel, type Digest } from "./digest.ts";
import type { Candidate } from "./analyst.ts";

export type Dim = "bottleneck_fit" | "semantic_safety" | "ease" | "root_cause";
export const WEIGHTS: Record<Dim, number> = { bottleneck_fit: 0.4, semantic_safety: 0.3, ease: 0.15, root_cause: 0.15 };
const SAFETY_FLOOR = 0.6;
const SAFETY_HARD_FLOOR = 0.45;
const CONFIDENCE_FLOOR = 0.35;

const BOTTLENECK = [
  "Unrelated to the costly operator or the worst estimate error in the plan.",
  "Touches a minor operator; the dominant cost is untouched.",
  "Partly addresses the dominant cost; a scan or spill probably remains.",
  "Directly removes the dominant cost for this query shape.",
  "Removes the dominant cost and the pattern behind it, so the shape itself changes.",
] as const;
const SAFETY = [
  "Changes results: a predicate, join, grouping, or NULL handling is altered.",
  "Likely changes results in edge cases: duplicates, ordering, tie-breaking.",
  "Equivalent on most data but leans on unstated assumptions.",
  "Clearly equivalent, with a stated reason.",
  "Equivalent and checkable: ships a comparison or rollback plan.",
] as const;
const EASE = [
  "Needs a schema plus application redesign and a release.",
  "Needs an application deploy with change control on the app tier.",
  "DBA work with approval, e.g. an index create during a window.",
  "One rewritten query or session setting, reviewable in minutes.",
  "Copy-paste change with no server-side change at all.",
] as const;

export type Ranked = { key: string; dims: Record<Dim, { value: number; confidence: number | null }>;
  composite: number; flags: string[] };
export type Verdict = { order: Ranked[]; headline: string; source: "jev" | "composite"; jev_pick: string | null;
  jev_confidence: number; agrees: boolean; anything_worth_running: number; weights: Record<Dim, number>; flags: string[] };

export const makeJevClient = (apiKey: string, model = "jev-latest") =>
  new TypeSafeClient({ apiKey, defaultModel: model, timeout: 20_000 });

const norm = (s: number, levels: number) => Math.max(0, Math.min(1, s / (levels - 1)));

function rank(c: Candidate, a: any): Ranked {
  const dims = {
    bottleneck_fit: { value: norm(a.bottleneck_fit.score, BOTTLENECK.length), confidence: a.bottleneck_fit.confidence },
    semantic_safety: { value: norm(a.semantic_safety.score, SAFETY.length), confidence: a.semantic_safety.confidence },
    ease: { value: norm(a.ease.score, EASE.length), confidence: a.ease.confidence },
    root_cause: { value: a.root_cause.noul, confidence: null },  // noul reports no confidence
  } as Record<Dim, { value: number; confidence: number | null }>;
  const composite = (Object.keys(WEIGHTS) as Dim[]).reduce((s, k) => s + WEIGHTS[k] * dims[k].value, 0);
  const confs = Object.values(dims).map((d) => d.confidence).filter((x): x is number => x !== null);
  const flags: string[] = [];
  // Live Jev scores an index create around 0.57 ("probably equivalent, no proof stated"), so a blanket floor
  // flags every index option and the warning becomes noise. Semantics only move when the query text changes:
  // flag a rewrite below the soft floor, or anything at all below the hard floor.
  const safety = dims.semantic_safety.value;
  if (safety < SAFETY_HARD_FLOOR || (c.option_type === "rewrite" && safety < SAFETY_FLOOR)) flags.push("verify_semantics");
  if (confs.length && Math.min(...confs) < CONFIDENCE_FLOOR) flags.push("low_confidence");
  if (c.actions.some((x) => x.requires_change_control)) flags.push("change_control");
  return { key: c.key, dims, composite: Number(composite.toFixed(4)), flags };
}

export async function judgeCandidates(digest: Digest, candidates: Candidate[], client: TypeSafeClient): Promise<Verdict> {
  const plan = digestForModel(digest);
  const per = await Promise.all(candidates.map((c) => client.systemOne({
    state: { plan, candidate: c },
    questions: {
      bottleneck_fit: score("How directly do the actions in `candidate` attack the dominant bottleneck in `plan` — the most expensive operator and the worst estimate error?", BOTTLENECK),
      semantic_safety: score("If `candidate` is applied, how certain is it that the query still returns exactly the same rows as the original?", SAFETY),
      ease: score("How easy is `candidate` to apply in a real shop, counting people, approvals, and downtime?", EASE),
      root_cause: noul("The diagnosis in `candidate` names the actual cause of the cost in `plan`, not a symptom of it."),
    },
  })));

  const cross = await client.systemOne({
    state: { plan, options: candidates.map((c) => ({ key: c.key, title: c.title, diagnosis: c.diagnosis, expected: c.expected })) },
    questions: {
      first_to_run: choice("Which single entry in `options` should the DBA run first for the biggest drop in the cost shown in `plan`?",
        Object.fromEntries(candidates.map((c) => [c.key, `${c.title} — ${c.expected}`]))),
      anything_worth_running: noul("At least one entry in `options` would meaningfully reduce the cost shown in `plan`."),
    },
  });

  const order = candidates.map((c, i) => rank(c, (per[i] as any).answers)).sort((a, b) => b.composite - a.composite);
  const pick = (cross as any).answers.first_to_run;
  const worth = (cross as any).answers.anything_worth_running.noul;
  const valid = candidates.some((c) => c.key === pick.choice);
  const useJev = valid && pick.confidence >= 0.5;   // confidence-gated routing: act only when Jev is sure enough
  const flags: string[] = [];
  if (!useJev) flags.push(pick.confidence < CONFIDENCE_FLOOR ? "low_confidence" : "composite_order");
  if (worth < 0.5) flags.push("nothing_clearly_worthwhile");

  return { order, source: useJev ? "jev" : "composite", headline: useJev ? pick.choice : order[0].key,
    jev_pick: valid ? pick.choice : null, jev_confidence: pick.confidence,
    agrees: valid && pick.choice === order[0].key, anything_worth_running: worth, weights: WEIGHTS, flags };
}

// Neutral answers: Jev never spoke, so nothing is claimed about safety or fit. The UI must say so.
export function jevFallback(candidates: Candidate[], why: string): Verdict {
  // Scores are 0 because Jev never answered, not because the options are bad: drop every flag a score produced.
  const order = candidates.map((c) => rank(c, { bottleneck_fit: { score: 0, confidence: null },
    semantic_safety: { score: 0, confidence: null }, ease: { score: 0, confidence: null },
    root_cause: { noul: 0 } }))
    .map((r) => ({ ...r, flags: r.flags.filter((f) => f === "change_control") }))
    .sort((a, b) => b.composite - a.composite);
  return { order, source: "composite", headline: order[0]?.key ?? "", jev_pick: null, jev_confidence: 0,
    agrees: false, anything_worth_running: 0, weights: WEIGHTS, flags: ["jev_unavailable", why.slice(0, 200)] };
}