◬  V A N T A G E

Ask in English. See the SQL. Trust the number.

An AI-native product-analytics service — events in over HTTP, funnels and retention out as
real SQL you can read — exposed as an MCP server so Claude can query it as a tool.

_{A personal project by Abheet, who owned A/B experimentation and product analytics at his last job and has been burned by every gotcha in here. Independent of any other project.}

▶  Live demo → vantage-abheet.fly.dev

_{Try it: open the app and click the example question — "Of the people who signed up in August, how many created a project within a week, and how many of those invited someone?" — then read the answer top to bottom: spec → SQL → number.}

_{The security money-shot: the model fills a typed spec (violet), the compiler turns it into a parameterised SELECT ($1…$7, no interpolation), and it runs as role vantage_reader · READ ONLY · timeout 5 s. A real capture of the deployed app — reproduce it with node tools/capture-hero.mjs.}

Note

Where this project is. Design and low-level design are approved and the build is feature-complete (slices S1–S8; see Gates). Event ingestion and identity, the funnel/retention/trend/paths/count query engine, the structural LLM→SQL boundary, the MCP server, the full nine-plus-one-screen web UI, real CI, and a performance bench are all built and tested. Everything below the install line runs today. No number in this README is hand-written; the only badge that asserts anything is the CI badge.

---

Contents

• The problem
• The one hard idea
• Ask → spec → SQL → number
• Three things that make it engineering
• The MCP surface
• The surface
• Architecture
• Tech stack
• Install
• Where this project is
• What it does not do yet
• Design documents

---

The problem

Product analytics tools answer questions with numbers you cannot check. Two dashboards disagree by 4 % and nobody can say why: one deduplicated retries and the other did not; one bucketed days in UTC and the other in the product's timezone; one counted a user's first signup and the other any. Now add a language model that writes the query for you, and the number is not only unverifiable but produced by something that can be talked into anything.

The one hard idea

The model never writes SQL. It fills in a small, typed query specification — funnel, retention, trend, paths, count — and Vantage's own compiler turns that spec into a parameterised SELECT, which runs as a database role that can only read, inside a read-only transaction, with a five-second statement timeout. There is no field in the grammar that can hold SQL, no code path from the model to the write pool, and no privilege in the database to abuse. Ask it to drop the events table and it cannot even say that; if it somehow did, Postgres would refuse.

question ─▶ model ─▶ text ─▶ [parse as QuerySpec or REFUSE] ─▶ [compile → parameterised SELECT] ─▶ [vantage_reader · READ ONLY · 5 s] ─▶ number + the SQL

_{Four independent layers; remove any one and the others still hold. The full argument is in DESIGN.md (a self-contained extract of 01-DESIGN.md §4) — the most interview-valuable document in this repo.}

⇄ Ask → spec → SQL → number

%%{init: {'theme':'base','themeVariables':{'primaryColor':'#1c1a2e','primaryTextColor':'#ece9f5','primaryBorderColor':'#e0a128','lineColor':'#a99bd1','secondaryColor':'#241f3a','tertiaryColor':'#2a2540','fontFamily':'Inter, system-ui, sans-serif'}}}%%
sequenceDiagram
  autonumber
  participant U as You
  participant A as AskModule
  participant M as Model (any)
  participant G as Grammar (Zod)
  participant C as Compiler (pure)
  participant DB as Postgres · vantage_reader
  participant L as Audit log
  U->>A: "Of August signups, how many created a project within a week?"
  A->>M: grammar + event catalog (fenced as data) + question
  M-->>A: {"kind":"funnel","steps":[…],"window":{"value":7,"unit":"days"}}
  A->>G: parse + validate
  alt not a valid spec
    G-->>A: refused (raw output kept)
    A->>L: decision = refused
    A-->>U: ⊘ Refused · raw model text shown
  else valid
    G-->>C: FunnelSpec
    C-->>DB: WITH e AS (…) SELECT … — params only, no interpolation
    DB-->>A: rows · or 57014 timeout · or 42501 refused
    A->>L: question · raw · spec · SQL · status · elapsed
    A-->>U: funnel bars + the exact SQL + ● Complete · 0.41 s · data until 09:12
  end

Loading

_{The number the question above resolves to — signup 13 → create_project 6 → invite_teammate 3 in a 7-day window — every bar carrying its share of the previous step and of the first. These are the hand-checked fixture's numbers (august.expected.md: "7 days: 13 → 6 → 3"); the 14-day window is 13 → 8 → 4.}

◈ Three things that make it engineering

	The gotcha	What Vantage does
Idempotent ingest	A mobile client retries; the same event arrives three times	UNIQUE (project_id, insert_id) + ON CONFLICT DO NOTHING; the response says duplicates: 2; a derived key when the client sends none — and the design names the failure mode that leaves (§2.4)
Funnels that stay correct and fast	Self-joins explode on power users; a UTC date_trunc puts a 22:00 Mumbai signup in yesterday's cohort; a 14-day window counted from the second signup	Window-function CTEs over per-person streams, AT TIME ZONE before bucketing, first-occurrence semantics, two covering indexes tied to specific queries, and a hand-computed fixture whose boundary rows fail by name (§3)
Honest results	A timeout returns a 200 with half the rows; last week's cohort looks "complete"	Every result carries status ∈ {complete, empty, timed_out, truncated, refused}, a data_until watermark, and in_progress computed in the SQL; a timeout returns nothing, never a partial

⌬ The MCP surface

Eight read-only tools — list_projects, list_events, describe_event, run_funnel, run_retention, run_trend, run_paths, explain_query — over stdio, every one annotated readOnlyHint: true, destructiveHint: false. There is no run_sql and no ask tool: the MCP client's own model does the English-to-spec step, so Vantage needs no LLM key of its own to be fully usable from Claude Desktop or Claude Code. Every tool result carries the SQL it ran.

# registers Vantage in Claude Desktop on Windows using an absolute node path (the bare-npx pitfall is avoided)
npm run mcp:install                  # writes claude_desktop_config.json (merges around other servers, backs up first)
npm run mcp:install -- --dry-run     # or preview it, and the `claude mcp add` one-liner, without writing

✦ The surface

An "observatory": opaque data surfaces, glass only on the navigation layer, and a query card that reads question → spec → SQL → number before the eye reaches a chart. Every number wears its status in the same typographic voice.

▶ Open the interactive prototype — every screen (Ask, Funnel, Retention, Paths, Events, History, Projects, MCP, Health) and every flow, clickable

Ask	Refused	Retention	History
spec (violet) → SQL (amber) → bars → ● Complete · 0.41 s	⊘ Refused with the raw model output; nothing ran	global-scale heatmap, hatched in progress cells	every ask: raw output · spec · SQL · decision · elapsed

⌂ Architecture

%%{init: {'theme':'base','themeVariables':{'primaryColor':'#1c1a2e','primaryTextColor':'#ece9f5','primaryBorderColor':'#e0a128','lineColor':'#a99bd1','fontFamily':'Inter, system-ui, sans-serif'}}}%%
flowchart LR
  SDK[Your app / SDK<br/>POST /v1/events]:::io
  WEB[Web SPA<br/>Ask · Funnel · Retention · Paths · History]:::io
  MCP[Claude Desktop / Code<br/>MCP client]:::io
  subgraph api[NestJS · 127.0.0.1]
    direction TB
    ING[IngestModule<br/>identity · dedupe]:::mod
    ASK[AskModule<br/>L0 prompt · L1 parse]:::mod
    INS[InsightsModule<br/>validate spec · run]:::mod
    MCPM[McpModule<br/>8 read-only tools]:::mod
    DOM[["domain (pure)<br/>QuerySpec grammar · compilers<br/>timestamp rule · dedupe key · tz buckets"]]:::pure
    LLM{{LlmPort<br/>anthropic · ollama · none}}:::io
  end
  subgraph pg[PostgreSQL 17]
    direction TB
    RW[(vantage_app<br/>INSERT only)]:::store
    RO[(vantage_reader<br/>SELECT only · READ ONLY · 5 s)]:::store
    AUD[(asks · audit log)]:::store
  end
  SDK --> ING --> RW
  WEB --> ASK --> LLM
  ASK --> DOM
  ASK --> INS
  WEB --> INS
  MCP --> MCPM --> INS
  INS --> DOM --> RO
  ASK --> AUD
  classDef pure fill:#3a2f5c,stroke:#e0a128,color:#ece9f5,stroke-width:2px
  classDef mod fill:#241f3a,stroke:#a99bd1,color:#ece9f5
  classDef io fill:#1c1a2e,stroke:#6d6489,color:#ece9f5
  classDef store fill:#2a2418,stroke:#d8be7e,color:#ece9f5

Loading

_{■ pure domain (unit-tested with no database)  ·  ■ NestJS modules  ·  ■ Postgres roles. InsightsModule cannot reach the model; AskModule cannot reach the write pool — both enforced by a lint rule that fails the build.}

Why Postgres and not DuckDB or SQLite?

The research pass recommended DuckDB for its analytics SQL, and it is genuinely nicer to write. But the learning goals here are index design and query plans and a read-only role, and only Postgres gives real roles, a database-enforced statement_timeout, and EXPLAIN (ANALYZE, BUFFERS). DuckDB's Node client cannot interrupt a query; SQLite has no roles and weak date maths. One free installer is a fair price. 01-DESIGN.md §0 A1.

What can a hostile model actually cause?

It can misread your question and produce a valid but wrong query — which is why the SQL is always shown. It can cost up to five seconds of one read-only connection. It cannot write, alter, or create anything; cannot read a table outside the four it is granted; cannot exceed the row cap; cannot run two statements; and cannot be manipulated by an event name into anything the grammar cannot say. 01-DESIGN.md §4.4.

How do you know the funnel number is right?

A ~120-event fixture across 14 people with the arithmetic written out by hand in august.expected.md, loaded through the real ingest endpoint, with deliberately adversarial rows: a conversion at exactly the window boundary and one second past it, an intervening event that breaks strict order, a second signup that must not restart the clock, a signup at 18:45 UTC that is tomorrow in Kolkata, and one identity merge. A plausible wrong number fails a test that names the person and the reason. 02-LLD.md §7.2.

⚙ Tech stack

Layer	Choice	Why
API	NestJS 12 (or 11 + nestjs-zod — decided at slice 1 by what is stable)	module boundaries make the model/database seam visible; DI tokens make the two pools distinct
Contracts	Zod	one schema is the HTTP DTO, the MCP inputSchema, and the TypeScript type
Database	PostgreSQL 17, pg	roles, statement_timeout, covering indexes, BRIN, real plans
MCP	@modelcontextprotocol/sdk, stdio	the spec's own recommendation for local servers
LLM	pluggable port: Anthropic API (paid, optional) · Ollama (free, local) · none (canned demo specs)	$0 to run; the MCP path needs no model at all
Web	React 19, Vite; purpose-built SVG/HTML for funnel bars, retention grid, transitions table	the SQL panel is the product, not chart variety
Tests	Vitest, fast-check, supertest, a Postgres service container in CI	property tests for the compiler; a hand-computed fixture for correctness

⬇ Install

cd D:\code\Vantage
npm install --legacy-peer-deps
$env:PGPASSWORD = '<your postgres superuser password>'
.\tools\db-setup.ps1      # creates the database and the three roles (owner / app / reader) idempotently
npm run check             # typecheck → lint → unit → integration (embedded Postgres 17) → coverage gates

# run the API against the hand-checked fixture, no LLM key needed:
npm run build; npm run migrate; npm run fixture:load   # prints the demo project id
$env:VANTAGE_LLM = 'none'; npm run start:api            # http://127.0.0.1:4100

# query it as an MCP server from Claude Desktop / Code:
npm run mcp:install -- --dry-run                        # prints the config + `claude mcp add` line

Note

npm run seed loads the demo's deterministic ~200k-event synthetic product (signup → create_project → invite_teammate, realistic drop-off, decaying retention, a couple of anonymous→identified stitches, one device ~3 h out of clock, a few late arrivals) into a Demo project through the real ingest path. It needs the API/DB up (npm run build; npm run migrate first), prints the project id and a ready-to-paste funnel query, and is idempotent: every event carries a stable insert_id, so a second run reuses the same Demo project and dedupes to zero new rows. Cap the volume with npm run seed -- --events 5000. npm run fixture:load (above) loads the smaller hand-checked fixture instead.

◬ Where this project is

Gate	Document	Status
1 · Design	docs/01-DESIGN.md · docs/03-UI.md · prototype	approved 2026-09-05
2 · LLD	docs/02-LLD.md	approved 2026-09-05
3 · Build	eight slices; the boundary and funnel correctness proven first (LLD §8)	feature-complete — S1–S8 built and tested (S1–S4 also hardened after hostile review)

The full approval and per-slice record — what each slice built, what every hostile review found, and how each fix was tested — is in docs/00-GATES.md. CI runs the six-gate suite (npm run check) against a real PostgreSQL 17 on Ubuntu plus the unit half on Windows, and a separate bench job guards funnel/retention/paths performance; the badge at the top reflects those runs.

∅ What it does not do yet

Everything. And, by design, ever (01-DESIGN.md §7): authentication or per-user access · multi-tenancy between operators · a chart library or saved dashboards · real-time streaming · alerting, anomaly detection, A/B analysis · free-text SQL from the model or from MCP clients · session replay or autocapture · partitioning and pre-aggregation (the named path once EXPLAIN says so) · anything that belongs to another project.

▤ Design documents

Doc	What it holds
DESIGN.md	the LLM-to-SQL boundary as a security problem — the four layers, the "drop the events table" trace, what a hostile model can and cannot cause (the interview centrepiece)
docs/DEMO.md	the 90-second demo script with the exact commands: ask → see the SQL → hostile question refused → psql denial → the MCP path
00-GATES.md	the gate process, approval record, and the verbatim build prompt for Gate 3
01-DESIGN.md	event model, idempotent ingestion argued, the real funnel and retention SQL, the LLM boundary as a security problem, MCP surface, architecture, scope, demo, risks
02-LLD.md	NestJS module map, full schema with the reason for every index, Zod contracts, 14 invariants, the query grammar as a type, MCP tool contract, the hand-computed fixture strategy, slices, adversarial plan
03-UI.md	the "Observatory glass" design language, status vocabulary with exact copy, every screen and flow
prototype/vantage.html	the clickable high-fidelity prototype the build must port
research/	dated research brief (analytics UIs, local databases, MCP 2026-07-28, NestJS 12, correctness gotchas, LLM-to-SQL prior art)

---

_{MIT · Sources in 01-DESIGN.md §10: OWASP LLM Top 10, MCP specification 2026-07-28, Amplitude / Mixpanel / Segment dedupe docs, PostHog funnel and retention semantics, PostgreSQL roles and covering indexes.}