The Gap Between Questions and Answers#

Every organization with a SQL database has unasked questions. Not because the data is missing, but because the path from question to answer requires someone who knows both the schema and the query language. A sales manager wants to know which customers are most profitable. Today, that means filing a ticket, waiting for an analyst, and hoping the query is correct on the first try.

We eliminated that layer. You type a question. The system finds the relevant tables, generates six SQL queries, validates each one against the live database, and renders a coordinated dashboard. No SQL knowledge required. No report builder opened.

How the Pipeline Works#

Figure 2 - End-to-End Pipeline: A natural language question flows through five stages. Schema matching identifies relevant views from 561 columns using semantic search. Claude Sonnet 4.6 plans six cards, each with a different visualization type. SQL generation produces six distinct queries. Every query is validated and tested against the live database before rendering. A three-attempt retry loop remains as a safety net for edge cases. The frontend renders each card natively with ECharts, AG Grid, or Leaflet.

The pipeline starts with schema retrieval: a five-stage process that maps business terms to database views. When a user asks about “revenue,” the system knows to query _v_sales_summary. When they ask about “customers,” it pulls from _v_customers. Static keyword matching handles the common cases. Semantic search with precomputed embeddings (all-mpnet-base-v2, cosine similarity threshold of 0.65) catches the rest.

With the schema context in hand, Claude Sonnet 4.6 plans the dashboard. It selects six visualization types from a palette of twelve: table, bar, row (horizontal bar), line, area, pie, scalar, combo (dual-axis), scatter, funnel, waterfall, and geographic map. Card one is always a data table with the direct answer. The remaining five are all different types, chosen based on the question’s analytical shape.

Then, for each card, the system generates a SQL query, validates it, and executes it. The query succeeds on the first attempt. A three-attempt retry loop is wired in for edge cases, but in current testing it no longer fires: prompts and post-processing fixes have closed the gaps that used to trigger it. The user never sees a stack trace.

Getting to 100% SQL Accuracy#

Figure 3 - Three Layers of SQL Reliability: Prompt rules catch the obvious patterns before generation. Post-processing fixes catch the model’s repeat mistakes before execution. A three-attempt retry loop remains as a safety net for anything the first two layers miss. In current testing, the retry layer no longer fires. The user never sees a broken card.

Here is the part most demos skip: what happens when AI-generated SQL is wrong.

Our first prototype generated SQL that looked syntactically correct but threw runtime errors. A column name spelled slightly wrong. A JOIN that produced duplicate rows. A date function that works in PostgreSQL but not in T-SQL. The SQL was valid enough to pass a linter but broken enough to crash against the actual database.

The fix was a three-layer architecture. Never trust generated SQL. Test every query against the live database before rendering. When edge cases slipped past the first layers, we captured them and taught the prompt and the post-processor to prevent them next time.

Layer 1: 14 T-SQL prompt rules. Claude receives explicit rules in its system prompt for the specifics that trip up most language models: no ORDER BY in subqueries without TOP, never mix TOP with OFFSET/FETCH, window functions must have ORDER BY in OVER(), always add WITH (NOLOCK) to every FROM and JOIN, and ten more.

Layer 2: 7 automatic post-processing fixes. The pipeline applies deterministic string transformations to handle patterns the model gets wrong repeatedly: stripping markdown fences, fixing TOP/OFFSET conflicts, injecting missing OVER() clauses, removing illegal ORDER BY from subqueries. Cheap, fast, and invisible.

Layer 3: three-attempt retry safety net. If a query still fails against the live database, the error message feeds back into the next generation attempt. In current testing, this layer no longer fires. We keep it wired in anyway. A safety net you never need is cheaper than one you wish you had.

Result: 100% success across 10+ query categories, with retry no longer observed. A seven-point security validation layer runs before every execution regardless: queries must start with SELECT or WITH, forbidden keywords (INSERT, UPDATE, DELETE, DROP) are rejected, system table access is blocked, UNION injection is caught, statement chaining is prevented. All of this runs in pure Python with zero external calls.

KEY INSIGHT: Reliable AI-generated SQL is not one clever technique. It is three layers stacked so the expensive layer (the retry loop) rarely has to run. When prompts and post-processing get good enough, the retry layer becomes the safety net you hope never fires. Currently, it doesn’t.

Twelve Chart Types, One Question#

Figure 4 - Different Question, Different Dashboard: “Top 25 customers by revenue this quarter” produced an entirely different chart mix: a multicolored horizontal bar chart ranking all 25 customers, a scatter plot correlating revenue against profit, a donut chart by customer category, a monthly revenue trend line, and a geographic map. Compare this to Figure 1. Same system, different question, different visualizations.

Every dashboard tells a complete analytical story. The AI does not just pick chart types at random. It analyzes the question’s intent and data shape.

Figure 5 - No Map, No Problem: “Top 5 salespersons by revenue in 2024” triggered a different selection again: vertical bars for revenue comparison, a stacked combo chart showing revenue versus profit, a monthly trend line, a donut for share of total revenue, and a scatter plot for revenue versus unique customers. Notice: no map card. The system recognized this query is about salespersons, not geographic customer locations, and chose a more relevant sixth visualization instead.

The system enforces that all six types are different. If the AI planner returns two bar charts, the validator automatically replaces the duplicate with an unused type. Map cards only appear when the query targets a filtered subset of customers (Top N). No more useless all-customer maps on salesperson queries.

Why We Removed Metabase#

Figure 6 - V1 vs V2 Architecture: V1 required four services including a 493 MB Metabase Java server. V2 removed Metabase entirely, executing SQL directly via pyodbc and rendering charts natively in React. The result: one fewer service, no Java dependency, no API keys for the visualization layer, and full control over chart rendering.

The first version of this system used Metabase, a Java-based BI platform, to render dashboards. The AI pipeline generated SQL, created Metabase cards via its API, assembled them into a dashboard, and embedded the result in our React frontend through the Metabase SDK.

It worked. But the deployment story was painful.

Metabase required Java 21. The JAR file was 493 MB. It shipped with 17 database driver plugins, most unused. The SDK pinned us to Vite 5 and React 18 with no upgrade path. The embedded dashboard rendered in an iframe-like container that managed its own height, leaving a blank gap below the cards we could not fix without a ResizeObserver hack. API key management added another configuration step.

We replaced all of it with three npm packages: echarts (6.0.0), ag-grid-community (35.2.1), and leaflet (1.9.4). Ten chart mapper files (one per visualization type) translate the backend’s JSON response into ECharts options. A ChartCard component with a React error boundary routes each card to the correct renderer. A DashboardGrid component arranges them in a responsive 3-column CSS grid.

The frontend went from embedding a third-party BI platform to rendering standard React components. Every chart is a first-class citizen in the component tree. We control the styling, the tooltips, the axis labels, the color palette, and the responsive behavior. No more fighting an SDK for pixel control.

KEY INSIGHT: Removing Metabase was not about the technology being bad. Metabase is a capable BI platform. It was about the deployment complexity being disproportionate to the value it added over native rendering. Three npm packages replaced a 493 MB Java server.

Bolt-On, Not Rip-and-Replace#

Figure 7 - Bolt-On Architecture: The system connects to any SQL database with a read-only connection. It discovers the schema through database views, requires no modifications to existing tables, and runs as an independent service. Swap the connection string and the system adapts.

The system connects to your existing database with a read-only connection. It does not create tables, run migrations, or modify your schema. Your database does not know it exists.

All queries run through database views prefixed with _v_ (e.g., _v_customers, _v_sales_summary). This abstraction layer hides base table complexity and makes the system portable. Point it at a different database, create views that expose the data you want queryable, and the semantic search pipeline handles the rest.

The frontend runs standalone or drops into any existing React application. No vendor lock-in on the visualization layer. No external server to maintain beyond the Python backend.

Performance and Caching#

Figure 8 - Timing Breakdown: First-run queries complete in 12 to 25 seconds. Schema retrieval takes 100 to 500 milliseconds. Card planning (one Claude API call) takes 2 to 5 seconds. SQL generation across six cards takes 12 to 18 seconds. SQL execution takes 100 to 500 milliseconds per card. Cached repeat queries skip Claude entirely and complete in under 5 seconds.

A three-tier Redis cache prevents redundant work. The first tier caches complete dashboards (24-hour TTL): same question, same cards, skip Claude entirely. The second tier caches individual SQL templates (24-hour TTL): if a card’s data query was answered before, reuse the SQL. The third tier caches raw query results (1-hour TTL).

A dashboard that takes 25 seconds to generate the first time completes in under 5 seconds the second time. The cache key is a SHA-256 hash of the normalized query text.

Redis is optional. The system works without it. Queries take longer, but nothing breaks. Graceful degradation, not hard failure.

Dashboard Management#

Figure 9 - Dashboard Management: The save/load drawer shows 25 saved dashboards, here filtered to the “Customers” category (3 matches). Each entry displays the dashboard name, original query, creation date, and category badge. Action buttons provide one-click regenerate (re-run through latest AI pipeline), edit (rename, recategorize), and delete. The Load button restores the full six-card layout with fresh data from the current database state.

Saved dashboards store card specifications (SQL, display type, viz settings) without row data. Loading a saved dashboard re-executes all six SQL queries, returning fresh data from the current database state. The dashboard you saved last week shows this week’s numbers.

A regenerate button clears the cache and re-runs the entire AI pipeline for a saved dashboard. If the prompts or SQL generation logic improved since the dashboard was first created, regeneration picks up those improvements automatically.

Figure 10 - SQL Transparency: The expandable “Generated SQL (6 queries)” panel reveals every query the AI wrote. Here, the first card’s SQL is a Common Table Expression against _v_sales_summary with proper WITH (NOLOCK) hints, filtering by invoice year and quarter months. Users can verify the AI’s work, debug unexpected results, and learn T-SQL patterns from the output.

KEY INSIGHT: The ROI of natural language dashboards is not measured in analyst hours saved. It is measured in questions that were never asked because the friction was too high. Every organization has hundreds of unasked questions sitting in the gap between curiosity and capability.

Key Achievements#

The Full Stack#

Figure 11 - Technology Stack: The frontend combines React 18, TypeScript, Tailwind CSS v4, Apache ECharts 6, AG Grid Community 35, and Leaflet 1.9. The backend runs FastAPI with Python 3.11, connecting to Claude Sonnet 4.6 for AI planning and SQL generation, Qdrant for semantic schema retrieval, Redis for 3-tier caching, and MS SQL Server via pyodbc for direct query execution.

What This Changes#

Figure 12 - Time to Insight: Traditional workflow: request filed (Day 1), analyst assigned (Day 2-3), SQL written and reviewed (Day 3-5), dashboard built (Day 5-7), revisions (Day 7-10). Natural language dashboard: question typed (second 0), dashboard rendered (second 30). Follow-up question at second 60.

The traditional path from question to dashboard takes days. File a ticket. Wait for an analyst. Review the SQL. Request changes. Wait again. The natural language path takes 30 seconds. Rephrase and iterate in minutes.

The system is fully functional and tested against a production-scale database. It demonstrates that natural language BI dashboards are practical, reliable, and deployable as a bolt-on capability for any SQL-backed application, without requiring a Java server, a BI platform, or a team of report builders.

KEY INSIGHT: The fastest path to AI-powered analytics is not replacing your BI stack. It is adding a natural language layer on top of the database you already have. The database, the data model, and the existing reports all stay exactly where they are.

The Series#

This is Part 2 of a 2-part series on building AI-powered dashboards:

1. Ask Your Database Anything: The Metabase Version: The original V1 with Metabase-embedded dashboards, for clients who want a full BI platform with natural language on top.
2. Ask Your Database Anything: Native React Dashboards (this article): The V2 native chart rendering approach, for clients who want to embed AI dashboards in an existing product with no BI platform underneath.