Query Sense — AI Business-Intelligence Bot

The problem

LLM-powered analytics is magical but slow and expensive: every question round-trips to a model, generates SQL, executes, and renders. For an interactive BI tool where users ask many similar questions, paying full LLM latency and cost on every call is a non-starter.

The goal: keep the natural-language flexibility, but make repeat and near-repeat questions feel instant.

Architecture

A three-layer search engine that escalates only as far as it needs to — most questions never reach the LLM.

flowchart LR
  A[NL Question] --> B{Semantic Cache}
  B -->|hit| Z[Answer]
  B -->|miss| C{RAG Retrieval}
  C -->|pgvector match| D[Refined SQL]
  C -->|no match| E[LLM: Gemini]
  E --> D
  D --> F[Execute + Visualise]
  F --> Z

Layer 1 — Semantic cache: embed the question and check for a semantically-equivalent past query. A hit returns in under 20ms, skipping the model entirely.

Layer 2 — RAG: on a miss, retrieve domain-specific query patterns via pgvector + local HuggingFace embeddings to refine the SQL — teaching the system business logic without fine-tuning.

Layer 3 — LLM: only genuinely novel questions hit Google Gemini for text-to-SQL generation, then the result feeds back into the cache.

Key decisions & trade-offs

Semantic cache before RAG before LLM. Ordering the layers by cost/latency means the expensive path runs rarely. The trade-off is embedding every incoming question — cheap compared to an LLM call, and it powers both the cache and retrieval.

pgvector instead of a dedicated vector DB. Keeping embeddings in PostgreSQL alongside the operational data avoided a second datastore to operate and sync, at the cost of some raw ANN performance — a worthwhile trade for a single-service deployment.

Shape-aware visualisation. Rather than asking the LLM to also choose a chart type, a deterministic engine inspects the result shape (cardinality, types) and renders a KPI card or chart — faster and more predictable.

Results

• Recurring text-to-SQL query latency: 1.5s → under 20ms for cache hits.
• A three-tier engine where the majority of queries never invoke the LLM, cutting cost and latency.
• Domain-specific accuracy via RAG with zero fine-tuning.