An enterprise-grade, highly optimized NLP-to-SQL engine specifically designed for B2B Accounts Payable automation. This system features a declarative Semantic Layer, an intelligent heuristic visualization engine, exact-match routing query caching, and rigid natural language constraints to prevent data hallucination.
Instead of allowing the LLM to guess table definitions or field roles, we provide a strictly decoupled YAML semantic layer mapping out dependencies.
- Impact: Eliminates massive performance overheads by bypassing "chain-of-thought schema discovery" queries. Business concepts like "revenue" and "outstanding pipeline" are explicitly formulated in SQL math inside the YAML, meaning the LLM NEVER hallucinates formulaic metrics.
Parsing DDL metadata querying sqlite_master and scanning disk YAML is expensive. If 50 users query simultaneously, disk bounds would choke the server.
- Impact: Using
@lru_cache(maxsize=1)on thegenerate_schema_context()function ensures the massive composite metadata string is processed and stowed in memory exactly once during application startup. Subsequent LLM invocations retrieve context in O(1) time complexity.
For every query processed, the backend registers the lowercase string into a QUERY_CACHE dictionary mapped to the verified LLM-generated SQL string.
- Impact: Decreases token consumption by ~40% in enterprise environments where users ask highly repetitive questions (e.g., "Show me overdue bills"). The system entirely bypasses the LLM network call and pushes the known-safe SQL directly to SQLite. This drastically drops API costs and response latency from ~2.5 seconds to <20ms.
The greatest vulnerability in naive Text-to-SQL is the LLM attempting to fulfill impossible objective requests (e.g., "What is our payroll expense?" when no HR data exists, or "Who is the best vendor?").
- Impact: The System Prompt possesses explicit, negative constraints disabling Markdown SQL triple-backtick responses if assumptions are needed. If subjective metrics or unknown domains are requested, the LLM outputs a hard-coded strict string pattern enforcing a graceful text-only explanation of the limit, guaranteeing no imaginary database fields are queried.
Instead of forcing conversation history on users—which can cause the LLM to hallucinate ambiguous constraints on unrelated standard metrics—the UI decouple interactions into two modes:
- Dashboard Mode: Single-turn, pure analytical execution without historical payload baggage. Ideal for immediate answers.
- Chat Mode (Conversational History): A dedicated messaging interface where the React state array preserves the conversation session and passes past LLM responses as contextual history into the FastAPI endpoints, empowering the LLM to comprehend follow-ups like "Now break that down by division".
Instead of requiring an LLM to generate UI components, we utilize deterministic Python algorithms (suggest_visualization(columns, data)) inside the API endpoint.
- Impact: By iterating the types (e.g.,
strvsint/float), the backend decides the optimal visual shape (Bar chart for textual vs numeric, or Line chart for datetime vs numeric). The React frontend implicitly captures this tag and injects the output array straight into the lightweightRechartsmodule, ensuring UI visualizations are perfectly robust and do not crash due to LLM markup hallucinations.
Ensure cashflo_sample.db exists. If not, generate via:
sqlite3 cashflo_sample.db < cashflo_sample_schema_and_data.sqlcd backend
python -m venv .venv
.\.venv\Scripts\activate
pip install -r requirements.txt
uvicorn api:app --reload --port 8000cd frontend
npm install
npm run devOnce active, navigate to http://localhost:5173.
Try an ambiguous prompt: "Who is the best vendor?" (Witness the graceful text fallback without SQL hallucinations).
Try an analytical prompt: "Compare this quarter's invoice volume with last quarter" (Witness the Recharts bar diagram generation and multi-CTE window function output).
Google DeepMind Agentic Coding Model:
- Scaffolding: Directly built the full
semantic_layer.yaml, FastAPI backend, and Vite frontend. - Iteration: Iterated 5 times heavily strictly tuning the LLM
system_promptto stop SQLite Date integer-math errors. - Refinement: Created extensive
test_pipeline.pyevaluation scripts to spam the query engine with subjective ambiguity ensuring the negative-prompt strict formatting holds up in production. - Frontend-UI/UX Creation: Took help of AI to quickly create a functional UI for the application and then imporvised on it to extend the demo.
- Tradeoff (SQLite Type Affinity vs Postgres): SQLite lacks native
DECIMALandDATEtypes. We had to enforce rigorousstrftimestring conversions. In a real environment, switching to PostgreSQL makes relative date arithmetic (NOW() - INTERVAL '1 month') flawlessly simple without prompt engineering overhead. - Limitation (Naive Vector Search Missing): We load the entire schema into the prompt. At 12 tables, it's ~4000 context tokens. If this scaled to 800 tables, the token window would shatter. We'd need to cut corners via FAISS or Pinecone to inject only top-K semantically relevant DDL chunks.
- Limitation (Role-Based Access Control): Currently, the LLM constructs queries over the entire database. At scale, an aggressive AST parser middleware is needed before
cursor.execute(sql)to guarantee the LLM isn't querying cross-tenant customer IDs or bypassing Row-Level Security parameters.
To avoid manual transcription of large enterprise databases into the Semantic Layer, use the provided script:
python backend/auto_discover.pyThis script queries the active SQLite schema (PRAGMA table_list) and generates a boilerplate draft_semantic_layer.yaml instantly.
Backend (Terminal 1)
cd backend
.\.venv\Scripts\activate
uvicorn api:app --reload --port 8000Frontend (Terminal 2)
cd frontend
npm run dev