AI Analytics Chatbot Combining SQL Aggregations and Vector Retrieval

I built an AI analytics chatbot that answered questions by combining SQL aggregations from structured database tables with retrieved context from a vector store.

The system was not just Text2SQL, and it was not just RAG. It had to understand a user question, decide what data was needed, run database aggregations where exact values were required, retrieve supporting context from unstructured knowledge sources, combine both result types, validate them, and explain the answer back to the user.

The hard part was building the bridge between structured analytics and retrieval: SQL aggregations for exact database-backed facts, plus BM25/vector retrieval and reranking for supporting context from the vector store.

The goal was to build one AI interface that could answer questions across structured data and unstructured knowledge without forcing everything into one method.

Users needed to ask natural-language questions over mixed data.

Some answers lived in database tables. Some answers lived in unstructured text or knowledge records. Some questions required both: a numeric aggregation from SQL, while also needing retrieved context from the vector store to explain what that number meant or why it mattered.

The system had to understand whether a question needed SQL, retrieval, or both. Generated SQL had to match the database schema. SQL aggregation results had to be validated before being used in the final answer.

Unstructured retrieval had to support both exact terms and semantic meaning. The system needed reranking so the final context was not just top similar chunks, but the strongest context for the actual question.

The final response had to combine database-backed values with retrieved explanatory context without mixing unsupported claims into the answer.

The system also needed to support multilingual questions and track usage/analytics around conversations and retrieval behavior.

The challenge was to make database access, retrieval, validation, and explanation feel like one coherent AI system.

Natural language had to become valid database queries. The user was not writing SQL. They were asking questions in natural language, and the system had to map that intent to the right tables, columns, filters, aggregations, and result format.

This required more than asking a model to write a query. The system needed schema awareness and a flow for validating generated queries before trusting the answer.

SQL aggregations and vector-store retrieval had to be combined. Some questions needed exact values from structured tables: counts, totals, averages, grouped results, filters, and aggregations. Other questions needed retrieved context from unstructured knowledge sources. The harder questions needed both.

I designed the system so SQL aggregation results and vector-store retrieval results could be merged before answer generation. That meant the final answer could use exact database-backed numbers while also using retrieved context to explain the result.

For unstructured retrieval, the system used BM25 for exact terms, semantic vector search for meaning, and reranking to improve the final context selection.

Query generation needed validation. Generated SQL can fail in obvious ways, like invalid syntax, but it can also fail quietly with the wrong table, wrong column, wrong filter, or wrong aggregation.

Retrieved context needed ranking, not just similarity. BM25 and semantic search worked together so the system could retrieve both exact wording and related meaning, while reranking helped select the strongest final context before answer generation.

SQL results and retrieved context had to be merged safely. A SQL aggregation might return the exact number, but not the explanation. A retrieved chunk might explain the surrounding context, but not provide the exact value. The answer workflow had to keep those roles clear.

The system had to explain results, not just return data. Raw rows, values, or aggregations had to become a readable explanation tied to validated query results and reranked retrieved context.

Multilingual questions added complexity because the system had to map user intent correctly to schemas, retrieval logic, SQL aggregation, and answer generation across more than one language.

Supabase integration had to support the full flow, including data management, setup, database seeding, schema/data issues, and integration behavior around actual structured data.

Analytics tracking had to be part of the system so usage patterns, retrieval behavior, query failures, and answer workflow behavior could be inspected and improved.

The architecture followed a combined retrieval-and-aggregation flow.

A user asked a natural-language analytics question. The system interpreted the question and decided which evidence was needed: SQL aggregation results, retrieved vector-store context, or both.

For structured evidence, the system generated SQL against the database schema, ran validation, executed the query, and captured the aggregation or row-level result.

For unstructured evidence, the system used hybrid retrieval over the vector store: BM25 for exact matching, semantic search for meaning, and reranking to select the strongest final context.

When a question needed both, the system merged the SQL result and the retrieved context into one answer context before generation.

The answer-generation layer then produced a response grounded in both sources: database-backed values and retrieved explanatory context.

The system used LLMs for understanding user intent, generating queries, validating results, and explaining answers. Supabase handled the data layer. The retrieval workflow supported hybrid search over unstructured sources.

The important architectural decision was not to force every question through one method. SQL was used when the answer needed exact database-backed facts. BM25 was used when exact terms mattered. Semantic vector search was used when meaning mattered. Reranking was used to improve the final retrieved context.

I built the AI analytics chatbot workflow for structured and unstructured data.

The result was an AI system that could answer analytics-style questions from both database records and unstructured knowledge sources.

• Setting up the Text2SQL chatbot flow
• Connecting the system to database-backed structured data
• Working with Supabase integration
• Handling database seeding and data setup issues
• Building natural-language-to-SQL behavior
• Supporting query generation against database schema
• Supporting SQL aggregations for exact database-backed answers
• Adding result validation before answer generation
• Combining SQL aggregation results with BM25/vector-store retrieval and reranked context
• Using BM25 for exact-term retrieval
• Using semantic vector search for meaning-based retrieval
• Using reranking to improve final context selection
• Merging SQL results with retrieved context before answer generation
• Supporting unstructured knowledge retrieval
• Supporting multilingual user questions
• Building automated retrieval and explanation workflows
• Tracking analytics around usage and answers
• Testing query behavior and answer outputs
• Debugging database and chatbot setup issues
• Improving the workflow so structured and unstructured data could be used together

• Text2SQL
• SQL query generation
• SQL aggregations
• Database schema awareness
• Query validation
• Structured-data retrieval
• Vector-store retrieval
• BM25 retrieval
• Semantic vector search
• Reranking
• Hybrid retrieval
• Combined answer context
• SQL result and retrieved-context merging
• Unstructured knowledge retrieval
• Result explanation
• Multilingual input handling
• Supabase integration
• Database seeding
• Analytics tracking
• LLM-based intent handling
• OpenAI/Gemini model usage
• Automated retrieval workflow
• Answer validation
• Debugging and testing

This was technically hard because structured and unstructured data behave differently.

SQL needs precision. The system has to query the right table, column, filter, and aggregation.

Hybrid retrieval needs both precision and meaning. BM25 helps when exact terms matter. Semantic search helps when the wording is different. Reranking helps decide which retrieved context is actually strongest for the final answer.

The hard part was making both systems work together without confusing them.

If the model generated the wrong SQL, the answer could be confidently wrong. If the SQL aggregation was correct but the retrieved context was weak, the explanation could become misleading.

If BM25 matched the wrong exact term, the retrieved context could be noisy. If semantic retrieval returned weak context, the explanation could become vague. If reranking selected the wrong context, the final answer could look well-supported while relying on the wrong material.

If the system used SQL when it needed retrieval context, the answer would be incomplete. If the system used retrieval when it needed exact database values, the answer would be unreliable.

The system had to route questions, validate results, combine sources, rank context, and explain answers clearly.