Sherlock — AI Spending Detective

Point it at a company's AI usage logs and it shows where the money goes and how much is being wasted, with a specific fix and a dollar saving for each finding.

Everything runs in the browser. No backend, no database, no API keys.

npm install
npm run dev      # http://localhost:5173

Other scripts: npm test, npm run build, npm run preview, and:

npm run verify   # re-derive every dollar from the export and check it against ccusage

verify deliberately re-implements the pricing arithmetic instead of importing the app's — sharing the implementation would make both sides agree by construction and test nothing. It prints every flagged call with the evidence that flagged it, because savings have no ground truth and a claim you cannot trace to a specific call is not a finding.

---

What it reports

Load the baked-in sample month (6,082 calls across six features) and it finds:

Spending now	$213.66
Wasted	$106.88
Savable	50.0%

Five findings, largest first: a support chatbot re-answering three questions 2,597 times ($63.11), contract analysis pasting whole documents into the prompt ($22.50), a doc summarizer on a frontier model ($11.60), a contract retry loop firing the same call every six seconds ($9.60), and the chatbot's three first-seen FAQ calls ($0.07). Three features — email drafting, code review, internal search — are clean, and the report says so.

---

The costing

A call gets a price one of three ways, tried in order (src/lib/pricing.ts):

1. The row carries cost_usd. The exporter already did the maths against its own contracted rates, so we use it verbatim. This is the tier that works for any provider, forever.
2. The model matches the catalog. Names are normalised first, so anthropic.claude-opus-5, claude-opus-5, and claude-opus-4-5-20251101 all resolve — provider prefixes and pinned date suffixes are stripped.
3. Nothing matches. The call is reported as unpriced, not dropped and not guessed at. It stays in the row count, is excluded from every dollar figure, and the report names the model.

Rates are US dollars per million tokens. Only the Anthropic entries are verified against published pricing; the four gpt-5.5-pro / claude-sonnet / gpt-5-mini / phi-4-local names exist to drive the sample month and are invented. Add your own to CATALOG, or export cost_usd.

cost = input/1e6·in + output/1e6·out
     + (cacheWrite − cacheWrite1h)/1e6·(in × 1.25)   ← 5-minute TTL
     + cacheWrite1h/1e6·(in × 2.00)                  ← 1-hour TTL
     + cacheRead/1e6·(in × 0.1)

Four token types, not two. Cache reads are the reason. On real agent traffic they routinely run thousands of times the raw input — a month of Claude Code logs measured here showed 1,228M cache-read tokens against 0.2M input. Pricing only input + output understates that bill by orders of magnitude, and makes the Trim rule fire almost never because input_tokens is tiny on every row.

A cache write is priced by how long it holds, and the two rates are far apart: 1.25× input for five minutes, 2× for an hour. Charging every write the cheap rate understated this project's own bill by $27 of $392 — 7% of spend, and three and a half times everything the app reported as recoverable put together. cache_write_1h_tokens is the 1-hour share of cache_write_tokens; supply it and the total reconciles with ccusage to the cent. Omit it and every write prices at 1.25×, the spend is reported as a floor, and the skipped-checks panel says so — the share ran 0% on Haiku, 57% on Opus 5 and 83% on Opus 4.8 in one file, so it is not a thing that can be assumed either way.

The waste rules

Every call is classified once, by the first rule that matches. Because a call yields at most one saving, and each saving is capped by that call's own cost, total savings can never exceed total spend — the analyzer asserts this on every run, and a property test fuzzes 20 random datasets to confirm it.

1. Storm — this (feature, prompt) pair ran again within 60 seconds of the previous identical call. Nobody asks the same question twice inside a minute, so this is a retry loop or an unbounded agent, not demand. Saving is the call's full cost.
2. Repeat — the same pair appeared earlier, but spread out in time: genuine recurring demand that a cache would have served. Saving is the call's full cost.
3. Downgrade — routine work (faq, classify, route, summarize, extract, translate) on a model costing at least 2× the cheapest model in your own data. Saving is the current cost minus the same tokens re-priced on that model. The target is data-derived on purpose: recommending a migration to a model the company doesn't run isn't advice, and a hardcoded target named a model that appears in nobody's real export. A single-model dataset produces no downgrade findings, and the report says so rather than inventing an alternative.
4. Trim — input over 4,000 tokens. Assume half is padding; saving is input_tokens * 0.5 / 1e6 * inputPrice.
5. Otherwise the call is money well spent and contributes nothing to the savings figure.

Order matters. A repeated, over-modelled, bloated call lands in one bucket only — claiming the full cost once is both the largest honest saving and the only way the arithmetic stays sound.

Storm and Repeat recover identical money and differ only in the fix, which is exactly why they are separate. A cache is the wrong answer to a retry loop: it hides the loop instead of stopping it, and the loop keeps burning everything downstream of the cached call. Telling them apart needs called_at, and without it every duplicate falls back to Repeat — the safe reading, since a cache is the right advice whenever the cause is genuinely ambiguous.

---

Bring your own data

Use sample data reloads the baked-in month. Upload CSV re-runs the analysis on your own export; CSV template downloads a correctly shaped file to start from.

Three columns are required, in any order — without a model and token counts there is no price, and without a price there is nothing to audit:

model,input_tokens,output_tokens
gpt-5.5-pro,900,180

Common spellings are recognised, so most exports drop in unedited: prompt_tokens, completion_tokens, model_name, timestamp, and similar.

Everything else — feature, task, prompt, called_at — is optional, because no provider emits them. feature and task are labels a company applies to its own traffic; a raw usage export has neither. Each missing column costs a specific check and the app names it rather than quietly reporting on the part it understood:

Missing	What stops working
prompt	Repeat and retry-loop detection entirely (a hash works — the check only needs equality)
task	The downgrade check's label test
feature	The cost-by-feature breakdown; everything groups as "Unlabelled"
called_at	Telling a retry loop from organic repetition; all duplicates report as cacheable
cache_read_tokens / cache_write_tokens	Cache spend is invisible — on agent traffic that's most of the bill
cost_usd	Only matters for models the catalog doesn't know; supply it and any provider works

called_at is optional and worth supplying — it is the only thing that separates a retry loop from organic repetition. ISO 8601 parses reliably. If the column is absent, the app says so and reports every duplicate as a caching opportunity; if a single row's date is unreadable that row keeps its place and only loses loop detection. Loop detection needs the column to be complete: on a partly dated export the app declines to order the calls at all rather than guess, so every duplicate falls back to Repeat.

Quoted fields, embedded commas and newlines, and CRLF endings are handled. Rows using a model that isn't in the price list are skipped with a warning rather than failing the upload — the app will not guess at a price it doesn't have.

---

Optional: rewrite the explanations with Claude

Everything above is offline. The Rewrite with Claude button in the "Wasted money we found" section is the one exception: paste an Anthropic API key and the findings — the numbers, not your prompts — go to the Claude API, which rewrites each explanation for a CFO or for the engineering team. The key lives in React state for the tab and is never written to localStorage, a cookie, or a server; the only request leaves for api.anthropic.com.

One request, one model call: claude-opus-5 at effort: "low", constrained to a JSON schema so the response maps back to findings by id, with fallbacks: "default" so a safety decline is re-run on the recommended model rather than returning nothing. See src/lib/aiExplanations.ts.

The request path is verified end-to-end (headers, body, CORS, and error handling for bad keys, rate limits, and refusals). The response-parsing path needs a real key to exercise — it is the one part of the app that has not been run against a live response.

---

Pointing this at real Snowflake Cortex usage

The analyzer only ever sees CallRow[], so switching to real data is a query and a mapping — nothing in analyze.ts changes.

Cortex usage lands in two places. SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY gives you tokens and credits per function per hour, which is enough for the spend charts but has no prompt text, so it cannot support the Repeat rule. To get per-call rows, log them yourself — wrap SNOWFLAKE.CORTEX.COMPLETE in a stored procedure that inserts one row per call:

CREATE TABLE ai_calls (
  called_at     TIMESTAMP_NTZ,
  feature       STRING,   -- which product surface made the call
  model         STRING,   -- must match a key in the price list
  task          STRING,   -- faq | classify | route | summarize | draft | ...
  input_tokens  NUMBER,
  output_tokens NUMBER,
  prompt        STRING    -- or a hash: the Repeat rule only needs equality
);

Then export the shape the app already reads:

SELECT feature, model, task, input_tokens, output_tokens, prompt,
       TO_VARCHAR(called_at, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS called_at
FROM ai_calls
WHERE called_at >= DATEADD(month, -1, CURRENT_TIMESTAMP());

Keep called_at in the export. It costs one column and it is the difference between telling a customer "add a cache" and telling them "your retry client has no attempt cap" — and on real agent traffic the second one is often the bigger number.

Three things to adjust:

• Prices. Replace PRICES in src/lib/pricing.ts with your Cortex model rates. Cortex bills in credits, so multiply by your contracted dollars-per-credit to keep every figure in dollars.
• Model names. They must match the price-list keys exactly, or the rows are skipped as unpriced. Alias them in the SQL (CASE WHEN model = 'mistral-large2' THEN ... END).
• Prompt privacy. The Repeat rule needs only equality, so SHA2(prompt) works and keeps prompt text out of the browser. You lose the readable prompt column in the raw-calls table.

For a live dashboard rather than a CSV round-trip, the same CallRow[] can come from a Snowflake SQL API call or a Streamlit-in-Snowflake wrapper — swap the parseCsv call in App.tsx for the fetch and the rest of the app is unchanged.

---

Layout

src/
  lib/
    types.ts            CallRow, AnalyzedCall, Finding, Analysis
    pricing.ts          price list + cost maths + rule thresholds
    analyze.ts          the classifier, aggregates, and findings
    analyze.test.ts     pricing, each rule, priority, the global invariant, the sample numbers
    csv.ts              RFC-4180 parser, column validation, template
    csv.test.ts         quoting, CRLF, reordered columns, bad rows
    sampleData.ts       the baked-in month
    aiExplanations.ts   optional Claude rewrite (the only network call)
    format.ts           money / count / percent / token formatting
    buckets.tsx         one colour + icon per waste bucket, fixed
    useTheme.ts         light / dark, remembered
  components/
    Hero.tsx            the verdict sentence
    LedgerBar.tsx       every dollar of the month, end to end
    KpiCards.tsx        spending now / wasted / could save
    CostCharts.tsx      cost by feature, cost by model
    Findings.tsx        one card per finding
    Method.tsx          the price list and the rules, shown
    CallsTable.tsx      the raw ledger, filterable and paginated
    ui/                 card, button, badge, table

Stack

Vite · React 19 · TypeScript · Tailwind v4 · Recharts · lucide-react · Vitest. Fonts (IBM Plex Sans and Mono) are bundled, so the app works with no network at all.