How do I build a chatbot that can use both search and SQL results?

A good pattern is to make the chatbot an orchestrator: it decides when to use search (unstructured info) and when to use SQL (structured data), then merges the results into one answer.

1) Split the problem by data type

• Search: for docs, FAQs, policies, support articles, web pages
• SQL: for metrics, inventory, orders, customers, transactions, internal tables

Usually:

• Search answers “what does the policy say?”
• SQL answers “how many orders last week?”

2) Build 3 layers

A. Data access tools

Create two tools/functions:

• search(query) -> passages
• run_sql(query) -> rows

Optionally add:

• get_schema() for SQL context
• search_filters() for narrowing search results

B. A router / planner

The model decides:

• Use search only
• Use SQL only
• Use both
• Ask a clarifying question

Common approach:

1. Classify the user query
2. Generate one or more tool calls
3. Combine outputs into a final response

C. Response composer

Take the retrieved snippets/rows and have the model produce:

• a concise answer
• citations / references if available
• caveats if data conflicts or is incomplete

---

3) Recommended architecture

A simple flow:

1. User asks a question
2. Router model decides:
   • Search?
   • SQL?
   • Both?
3. Tool execution
4. Answer synthesis
5. Return final response

Example:

“What was our revenue last month, and what does the refund policy say about chargebacks?”

• SQL: revenue last month
• Search: refund policy / chargeback policy
• Combine both in one answer

---

4) How to decide when to use each tool

Use SQL when the question includes:

• counts, sums, averages, trends
• “latest”, “last month”, “top 10”
• exact entity lookup from a database

Use search when the question includes:

• definitions, policies, procedures
• textual explanations
• references to docs or manuals

Use both when the question mixes:

• a metric + policy
• a customer/order record + help article
• data analysis + explanation

---

5) Practical implementation pattern

Tool calling

If your LLM supports function calling, define tools like:

• search_documents(query, top_k)
• execute_sql(sql)

Then prompt the model:

• “Use SQL for structured data questions.”
• “Use search for document-based questions.”
• “If both are needed, call both tools and combine results.”

Pseudocode

def answer প্রশ্ন(user հարց):
    intent = router(user প্রশ্ন)

    results = {}
    if intent.needs_sql:
        results["sql"] = execute_sql(intent.sql_query)

    if intent.needs_search:
        results["search"] = search_documents(intent.search_query)

    final = llm_summarize(user প্রশ্ন, results)
    return final

---

6) Important design tips

A. Keep SQL safe

• Use read-only DB credentials
• Limit allowed tables
• Validate SQL before execution
• Prefer generated SQL with guardrails
• Add row limits

B. Keep search grounded

• Return source snippets, not just rankings
• Include doc titles, URLs, timestamps
• Use chunking + embeddings if doing semantic search

C. Make synthesis robust

Tell the model:

• Don’t invent missing facts
• If results disagree, say so
• Cite where each part came from
• Separate “from SQL” vs “from docs”

D. Handle ambiguity

If the user says:

• “sales last month” but there are multiple time zones or business units
• “refund policy” but multiple policies exist

Then ask a clarifying question before querying.

---

7) Better version: multi-step reasoning

For harder questions:

1. Plan
2. Query search and SQL
3. Cross-check results
4. Generate answer

Example:

• Search finds policy says refunds allowed within 30 days
• SQL shows 12% of last month’s orders were refunded
• Final answer: “Refunds were 12% last month. Per policy, refunds are allowed within 30 days...”

---

8) Suggested tech stack

You can build this with:

• LLM: GPT-style model with tool calling
• Search: Elasticsearch, OpenSearch, pgvector, Pinecone, Weaviate
• SQL: Postgres, MySQL, Snowflake, BigQuery
• Orchestration: LangChain, LlamaIndex, Semantic Kernel, or custom code
• API server: FastAPI / Node.js / Flask

---

9) Minimal architecture that works well

If you want the simplest reliable version:

• One router prompt
• Two tools: search and SQL
• One final summarizer prompt
• Strict SQL safety checks
• Citations for search results

That’s enough for many production chatbots.

---

10) Example user flow

User: “How many enterprise customers churned last quarter, and what does our onboarding guide say about retention best practices?”

System:

• SQL query: churned enterprise customers last quarter
• Search query: onboarding guide retention best practices

Response:

• “23 enterprise customers churned last quarter.”
• “Our onboarding guide recommends weekly check-ins, time-to-value milestones, and early usage alerts.”
• “Based on both, the likely retention focus should be…”

---

If you want, I can also give you:

1. a reference architecture diagram,
2. a LangChain/LlamaIndex implementation, or
3. a Python example with tool calling for search + SQL.