Business data often sits locked in analytics stores - readable only by people who write SQL. Product and ops teams wait on analysts for questions that should take seconds. From January to April 2025 I built CAP-AI, a conversational analytics platform: JARVIS, a LangGraph-powered Text-to-SQL backend over Apache Druid that auto-generates interactive ECharts, plus multi-tenant RAG on Qdrant and a Next.js 14 dashboard builder. Plain-English questions become live charts. This case study is how that system was designed.
Why can’t business users get answers from analytics warehouses?
The bottleneck is not storage - it is the skill and latency between a business question and a trustworthy chart. Warehouses and OLAP stores like Apache Druid are excellent at scanning large event data. They are poor at speaking product language. An analyst queue turns every “how did this publisher do last week?” into a ticket.
A chatbot that only pastes SQL back at the user fails the real job. So does a chatbot that returns raw rows with no visualisation. CAP-AI’s product promise was narrower and clearer: ask in plain English, get a live chart in the dashboard - with the SQL path audited in the backend. The same intent shows up in the homepage CAP-AI case study card.
How does Text-to-SQL become live charts, not just rows?
Chart generation is a first-class step in the graph, not a frontend afterthought. JARVIS classifies the question, retrieves schema context, generates parameterized SQL for Apache Druid, executes it, then emits chart metadata - chart type, series, axes and time buckets - that CAP-UI renders with ECharts.
| Step | What it does |
|---|---|
| Classify intent | Route the question to the right analytics shape before writing SQL |
| Retrieve schema and tenant context | Load only the tables, metrics and tenant rules needed for that intent |
| Generate parameterized SQL | Produce a query for Apache Druid with safe parameters, not free-form string concat |
| Execute and shape results | Run the query and attach chart metadata: series, axes, time buckets, chart type |
| Render in CAP-UI | Next.js dashboard shows interactive ECharts, threads and saved insights |
Parameterized SQL matters for safety. The model proposes structure and dimensions; the harness binds values and enforces tenant filters before execution. Auto-generated chart metadata matters for speed: the frontend does not guess whether a result should be a line, a map or a KPI card - the backend already chose based on intent.
Orchestration sits on LangGraph for the same reasons as other production agents I ship: explicit nodes, shared state, and room for interrupts. The broader pattern is in agentic AI systems architecture with LangGraph and agent harness engineering.
How do you design analytics intents?
10+ analytics intent types beat one catch-all Text-to-SQL prompt. CAP-AI routes questions into shapes such as time-series, KPIs, maps, tables and publisher overviews - more than 10 intent types in the shipped catalogue. Each intent owns its schema hints, SQL patterns and default chart metadata, including intelligent time bucket selection for series questions.
| Intent | Example question | Default visualisation |
|---|---|---|
| Time-series | How did traffic trend last 30 days? | Line or area with automatic time bucket selection |
| KPIs | What is today’s active publishers? | Single-value or KPI card set |
| Maps | Where are readers concentrated? | Geo or regional breakdown |
| Tables | Top articles by engagement | Sortable table, optional sparkline column |
| Publisher overviews | Summarise this publisher’s week | Composite view: KPI strip + series + table |
Intent design is product work. New question classes get a new intent when the SQL shape or chart type diverges - not when marketing wants a new buzzword. Composite intents (publisher overviews) assemble several chart blocks in one answer so the UI feels like a brief, not a single plot.
How do you keep tenants’ data separate in RAG?
Not every question is SQL. Multi-tenant RAG on Qdrant answers document-style and help questions without leaking one client’s context into another. CAP-AI used Qdrant for context-aware chatbot responses across different client organisations. Each tenant’s vectors and filters stay isolated; retrieval always carries the organisation boundary. Analytics queries against Druid get the same discipline: tenant predicates are harness-enforced, not left to the model’s goodwill.
Article search sat on MongoDB and Elasticsearch for content that needed classic search rather than pure vector recall. Choosing Qdrant for multi-tenancy and filtering matches the guidance in vector databases for semantic search: pick the store for the isolation and filter features you actually need.
What feedback loop improves query accuracy?
Launch accuracy is a baseline. A user feedback reinforcement loop is how the system keeps learning which SQL and chart choices were right. CAP-AI collected corrections and ratings on answers, then used that signal to improve later query behaviour. Pair that with tracing - every intent, retrieval, SQL string and chart decision - so a bad answer is a debuggable artefact, not a vague complaint.
CAP-UI (Next.js 14, TypeScript, ECharts) closed the product loop: drag-and-drop dashboard builder, thread management and saved insights so useful answers become reusable artefacts for the team, not one-off chats.
What does the dashboard need beyond a chat box?
CAP-UI is a Next.js 14 TypeScript app with ECharts, drag-and-drop dashboard building, thread management and saved insights - because a useful answer should become a reusable artefact. A bare chat window encourages one-off questions. Threads keep the analytical conversation coherent. Saved insights and a dashboard builder let a good answer survive the meeting where it was asked. That product surface is part of the case study: conversational analytics fails quietly if every chart dies when the tab closes.
The backend and frontend contract is the chart metadata plus the result payload. Once that contract is stable, designers can iterate on layout without reopening the Text-to-SQL graph. Conversely, new intent types can ship behind the same renderers when they emit known chart shapes.
What would you reuse from a Text-to-SQL analytics build?
Reuse intent routing, parameterized SQL, chart-metadata emission and tenant isolation on any conversational analytics build. Redesign the intent catalogue and schema pack for each domain - media analytics is not FinTech ledgers. Swap Druid for PostgreSQL or another store when the workload fits, but keep the graph stages. Do not ship Text-to-SQL without a visualisation step if your users are business operators rather than analysts.
I would push harder on golden eval sets before a wide rollout: paired questions with expected intent, expected table set and expected chart family. Feedback loops help after launch; evals stop you from shipping a regressing prompt. The same discipline applies to agent harness engineering generally.
Related systems on the projects section include the FinTech assistant and restaurant operations RAG dashboards. For retrieval isolation patterns, see vector databases for semantic search.
Frequently asked questions
How do I build a Text-to-SQL chatbot that returns charts?
Do not stop at SQL execution. After the query returns, generate chart metadata - type, axes, series, time buckets - and render with a chart library such as ECharts. Intent classification first makes the chart choice reliable.
Why LangGraph for conversational analytics?
LangGraph makes intent routing, schema retrieval, SQL generation and chart shaping explicit nodes with shared state. You can checkpoint long threads, add human review on risky queries, and trace each step - the same harness pattern as other production agents.
How do you keep tenants’ data separate in a multi-tenant RAG system?
Isolate each organisation in its own Qdrant namespace or equivalent tenant boundary, filter every retrieval by tenant id, and never share embeddings or documents across tenants. Apply the same tenant filter on analytics queries against Druid.
What is CAP-AI?
CAP-AI is a conversational analytics platform I built from January to April 2025: a LangGraph Text-to-SQL backend (JARVIS) over Apache Druid, multi-tenant RAG on Qdrant, and a Next.js 14 CAP-UI with drag-and-drop dashboards and saved insights.
When is Text-to-SQL the wrong product?
When users need free-form document answers rather than metrics, start with RAG over documents. When they need governed, repeatable KPIs only, a fixed dashboard may be safer. Conversational analytics shines when the question set is wide but still maps to a known schema.
References
Related: vector databases for semantic search, agentic AI architecture with LangGraph, agent harness engineering.