◬  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 problem

• The one hard idea

• Demo

• Quick start

• System design

  • Ask → spec → SQL → number

  • Three things that make it engineering

  • Architecture

• The MCP surface

• The glass surface

• Tech stack

• 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.

Related MCP server: smolanalytics

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

▶ Demo

The reel at the top of this page is a real recording of the deployed app — nothing mocked, staged, or re-timed. It runs the showcase flow end to end:

1. Ask — the glass Ask screen with its example questions; the flagship one is clicked and POST /v1/ask fires.

2. Spec — the model's answer arrives as a typed QuerySpec (violet): kind: "funnel", a date range, ordered steps, a 7-day conversion window.

3. SQL · what actually ran — the compiler's parameterised SELECT with its role vantage_reader · READ ONLY · timeout 5 s badge and the $1…$9 bound parameters.

4. The number — the funnel bars the question produced: signup 13 → create_project 6 → invite_teammate 3 in a 7-day window, each bar carrying its share of the previous step and of the first.

5. A glance at the rest — the Funnel builder and the Retention cohort heatmap, run live, so the SQL panel that powers Ask is visibly the same engine everywhere.

# regenerate both from the LIVE deployment (Playwright drives it, ffmpeg encodes 60fps + gif)
node tools/capture-reel60.mjs
# or point it at a local web build:
$env:VANTAGE_URL = 'http://127.0.0.1:4200'; node tools/capture-reel60.mjs

ffmpeg is auto-detected (PATH → the winget install → $FFMPEG). The 60 fps is done with ffmpeg -r 60 and the minterpolate filter; the GIF is palette-quantised for size. See docs/DEMO.md for the 90-second live-narration script (ask → refuse a hostile question → psql denial → the MCP path).

⬇ Quick start

git clone https://github.com/abheet19/Vantage.git; cd 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

# run the web UI (the glass front end) against that API:
npm run web:dev                                         # http://127.0.0.1:4200

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

⌂ System design

The whole product is one seam held open on purpose: natural language on one side, a database on the other, and a typed grammar wedged between them so nothing the model says can ever become SQL. The three pieces below — the flow, the correctness gotchas it defends against, and the module/role topology — are the interview-relevant engineering.

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

Three things that make it engineering

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 · glass UI<br/>Ask · Funnel · Retention · Paths · Trend · Events · History · Settings]:::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

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.

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.

A 1,046-submission fixture across the named adversarial 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.

⌬ 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 glass surface

The shipped UI is 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. The redesign adds a Ctrl K command palette ("Jump to…"), a collapsible glass rail, and Bricolage/Manrope type. Eight rail screens — Ask, Funnel, Retention, Paths, Trend, Events, History, and a Settings shell that folds Projects & ingest, MCP, and Health behind a sub-nav (each still its own deep-linkable route).

▶ Open the interactive prototype — every screen and every flow, clickable

⚙ Tech stack

◬ Where this project is

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

The deliberate limits are listed in 01-DESIGN.md §7. The public deployment can require shared read/admin bearer tokens, but it does not implement per-user authentication, authorization, or operator isolation: 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