A Medical Agent That Routes Between SQLite Databases and Web Search

Plenty of AI chatbots are one-trick ponies. Ask a question and they either invent an answer from memory or decline because they can't reach your data. Real systems have to be smarter and pick the right source of truth for each question. This project tackles exactly that for medical questions: a multi-tool agent that reads a question and decides by itself whether the answer sits in a structured database or on the open web. It routes between three SQLite databases and a live web search tool, and it ran into two real problems after it was supposedly finished.

The goal

The brief for this course assignment asked for an agent that could do three things:

1. Answer statistical questions from three real medical datasets, covering heart disease, cancer and diabetes, each kept in its own SQLite database.
2. Answer general medical knowledge questions about definitions, symptoms, causes and treatments through live web search.
3. Route every incoming question to the right tool automatically, with no hint from the user about which one to use.

One brain, four tools, and the judgment to match each job to a tool.

The architecture

The mental model is a dispatcher:

┌────────────────────────┐
                        │      Main AI Agent      │
                        │   (LangChain create_agent) │
                        └────────────┬─────────────┘
                                     │ routes based on question type
        ┌────────────────┬──────────┼──────────┬────────────────┐
        ▼                ▼          ▼          ▼                ▼
HeartDiseaseDBTool  CancerDBTool  DiabetesDBTool        MedicalWebSearchTool
        │                │          │                            │
        ▼                ▼          ▼                            ▼
heart_disease.db     cancer.db  diabetes.db              Tavily Search API
(SQLite)             (SQLite)   (SQLite)                 (general knowledge)

The main agent never touches data itself. Its job is to read the question, choose a specialist and pass the work along. A question about the average cholesterol level in the heart disease dataset belongs to HeartDiseaseDBTool, while a question about what causes type 2 diabetes goes to MedicalWebSearchTool. If a question mixes both, the agent calls both tools and merges their outputs into one coherent reply.

The datasets

Three real, public Kaggle medical datasets were converted into SQLite databases with properly typed columns. The heart disease data lives in heart_disease.db with 303 rows covering age, sex, cholesterol, blood pressure and the target. The cancer cell measurements go into cancer.db with 101 rows covering clump thickness, cell size, bare nuclei and mitosis. The Pima Indians diabetes data goes into diabetes.db with 768 rows covering glucose, BMI, pregnancies and outcome.

Turning CSV into SQLite sounds trivial, but doing it properly means meaningful table names and column types (INTEGER, REAL, TEXT) inferred correctly and not everything dumped in as text. The conversion logic starts with a mapping from pandas dtypes to SQLite types:

DTYPE_MAP = {"int64": "INTEGER", "float64": "REAL", "object": "TEXT", "bool": "INTEGER"}

A helper then builds the column definitions of a CREATE TABLE statement from a DataFrame's dtypes:

def infer_sqlite_schema(df: pd.DataFrame) -> str:
    """I build a CREATE TABLE column definition string from a DataFrame's dtypes."""
    columns = []
    for col in df.columns:
        sqlite_type = DTYPE_MAP.get(str(df[col].dtype), "TEXT")
        safe_col = col.replace(" ", "_").replace("-", "_")
        columns.append(f'"{safe_col}" {sqlite_type}')
    return ", ".join(columns)

Once the databases exist, the schema can be trusted to reflect the real data, which matters a great deal when an LLM is about to write SQL against it.

Building the database tools

All three database tools follow the same three steps, so a single shared factory function replaces three copies of the logic:

1. Take the user's natural-language question.
2. Have the LLM turn that question into a SELECT-only SQL statement, using the table's schema as context.
3. Execute the query, and then prompt the LLM a second time to rewrite the raw rows as a plain-English reply.

The generation prompt casts the model as a SQLite expert and demands a syntactically correct, read-only SELECT:

SQL_GENERATION_PROMPT = ChatPromptTemplate.from_template(
    """You are a SQLite expert. Given an input question and a table schema,
write a syntactically correct, READ-ONLY SQLite SELECT query that answers
the question.

The rest of the template supplies the schema and the question and insists on raw SQL only, with no explanation and no markdown. A cleanup function follows:

Table schema:
{table_info}Question: {question}Return ONLY the raw SQL query. No explanation, no markdown formatting.
SQL Query:"""
)def _clean_sql(raw: str) -> str:
    """Strips markdown fences (if any) and blocks write operations."""
    sql = raw.strip()
    if sql.startswith("```"):
        sql = sql.strip("`")
        if sql.lower().startswith("sql"):
            sql = sql[3:]
        sql = sql.strip()    forbidden = ("insert", "update", "delete", "drop", "alter", "create")
    if sql.lower().startswith(forbidden):
        raise ValueError("Write operations are not permitted on this read-only tool.")
    return sql

That _clean_sql guard does quiet but important work. Whatever the LLM generates, the agent can never modify the underlying data, so access stays read-only.

Each of the three tools is then a thin wrapper naming its database, its table and, critically, a clear description telling the routing agent when to use it:

heart_disease_tool = make_db_tool(
    db_path=HEART_DB_PATH,
    table_name="heart_disease_records",
    tool_name="HeartDiseaseDBTool",
    description=(
        "Use this tool to answer STATISTICAL or DATA questions about the "
        "Heart Disease dataset (columns: age, sex, cp, trestbps, chol, "
        "fbs, restecg, thalach, exang, oldpeak, slope, ca, thal, target). "
        "Do NOT use this tool for general medical definitions or symptoms."
    ),
)

The final sentence of each description, the explicit statement of what the tool shouldn't be used for, matters more than expected. LLM-based routing lives or dies on how precisely each tool's job is described.

The web search tool

For general medical knowledge, the Tavily Search API sits behind a tool whose description is just as deliberate. It limits the tool to general knowledge so it can't be confused with a statistics question:

medical_web_search_tool = StructuredTool.from_function(
    func=_run_web_search,
    name="MedicalWebSearchTool",
    description=(
        "Use this tool ONLY for GENERAL medical knowledge questions such "
        "as definitions, symptoms, causes, risk factors, treatments, or "
        "cures. Do NOT use this tool for statistics or numeric dataset "
        "questions."
    ),
    args_schema=WebSearchInput,
)

Assembling the agent

With the four tools built, the last piece is the router: a system prompt laying out the decision logic in plain terms, plus LangChain's agent constructor to tie everything together. The prompt opens by telling the model it's a professional medical data assistant with four tools:

SYSTEM_PROMPT = """You are a professional medical data assistant with access to four tools.

The routing rules follow, to be applied strictly. Questions about statistics, data, numbers, counts, averages or specific records from a dataset go to the matching database tool, and requests for a definition, symptoms, causes or treatments go to web search:

Routing rules (follow strictly):
1. If the question asks about STATISTICS, DATA, NUMBERS, COUNTS, AVERAGES,
   or specific RECORDS from a dataset -> use the matching DB tool.
2. If the question asks for a DEFINITION, SYMPTOM, CAUSE, RISK FACTOR, or
   CURE/TREATMENT -> use MedicalWebSearchTool.
3. If a question mixes both, call both tools and combine the answers clearly.
4. Never invent numbers. Always answer in clear, professional natural language.
"""TOOLS = [heart_disease_tool, cancer_tool, diabetes_tool, medical_web_search_tool]def build_agent_executor():
    llm = get_llm()
    return create_agent(model=llm, tools=TOOLS, system_prompt=SYSTEM_PROMPT)

Ask for the average cholesterol level in the heart disease dataset and the answer comes back as roughly 246.26. Ask what the typical symptoms of heart disease are and the agent changes course, searches the web and returns a structured list covering chest pain, shortness of breath, fatigue and more, with sources and without invention.

When the ground shifted

The most instructive part of the build wasn't planned. Two things broke after the agent was complete.

The first problem was that the free LLM backend vanished. The agent had been built on GitHub Models, a free OpenAI-compatible inference endpoint. Partway through testing, every call began failing with github_models_retirement_brownout, because GitHub had permanently retired the service. No debugging of the project's own code could repair that. The fix was moving the whole backend to Google Gemini, which meant redesigning config.py to support several providers (Gemini, Azure OpenAI and direct OpenAI) so that no single point of failure remains.

Next, Gemini's newest models broke the agent framework. Google's newer thinking models (the 3.x family) tag every tool call with an internal thought_signature, a token the client must send back on the following turn. The original agent combined LangChain's older create_tool_calling_agent and AgentExecutor, and that pairing reassembles its own message scratchpad between steps, discarding the signature without any warning. Every tool call then failed with the obscure error Function call is missing a thought_signature.

No patch fixed it. The solution was moving to LangChain's current create_agent, which sits on LangGraph and carries the original message objects through each turn rather than rebuilding them. The signature stays attached automatically, and that whole family of errors goes away.

The broader point is that agentic systems rest on infrastructure that changes underneath them. Providers deprecate endpoints, and new model generations introduce new protocol requirements. Diagnosing why a 400 error is happening, and not just what its message says, is the real skill on test here, more than any single line of code.

Watching the router decide

The satisfying moment came from watching the agent make the right call question after question without being told what to do. A sample session shows it.

Asked for the average single epithelial cell size in the cancer data, the agent routed to CancerDBTool, generated SELECT AVG(single_epithelial_cell_size) FROM cancer_records; and answered that the average is approximately 2.37.

Asked for the average age of patients diagnosed with diabetes, it routed to DiabetesDBTool and correctly filtered to diagnosed patients (Outcome = 1) before averaging, and not across the whole dataset. The answer was approximately 37 years.

The last question, about what diabetes is and what the average glucose level is across the dataset, was the real test. The agent saw a compound request, called MedicalWebSearchTool for the definition and DiabetesDBTool for the glucose figure, and joined both results in one coherent paragraph, though nobody told it to. That behavior traces to one line of the system prompt: if a question mixes both kinds, call both tools and combine the answers clearly. Routing isn't magic, only precise instructions plus a model that follows them well.

Prompt engineering for tool routing

The single most influential decision in the build wasn't a line of Python. It was how each tool's description field was written. The early descriptions were too vague, along the lines of answering questions about heart disease data, and the agent occasionally grabbed web search for statistical questions because nothing ruled that out.

The remedy was to make every description do two jobs: state clearly what the tool is for, and state explicitly what it isn't for.

"Use this tool to answer STATISTICAL or DATA questions about the Heart
Disease dataset ... Do NOT use this tool for general medical definitions
or symptoms — use MedicalWebSearchTool for those."

Writing that second sentence carries about as much routing weight as the system prompt does. Four tools with just one meant for general knowledge means any vagueness in the descriptions turns up at once as a misrouted question. Tight, mutually exclusive tool descriptions were what the assignment really tested, and the SQL and API plumbing came second.

Safety by design

A few principles held throughout:

• Read-only access, always. Statements containing INSERT, UPDATE, DELETE, DROP or ALTER are rejected by every SQL tool, so none can reach the database.
• No invented numbers. The system prompt forbids fabricating statistics, and if a tool returns nothing, the agent says so.
• Secrets stay local. API keys sit in a .env file excluded from version control by .gitignore, so nothing sensitive reaches the repository.

Tech stack

• LangChain (create_agent, built on LangGraph) as the routing brain
• Google Gemini (gemini-3.5-flash-lite) as the primary LLM
• SQLite for lightweight, file-based storage of all three datasets
• Tavily Search API for real-time general knowledge retrieval
• Python with SQLAlchemy and Pandas as the glue

Lessons from the build

Getting the happy path working took an afternoon. Hardening it was the slow part: the agent had to outlast the retirement of one provider's service and a breaking change from another, which called for patient reading of error output and a readiness to restructure the design instead of working around a basic incompatibility. For anyone building agentic AI, the main takeaway is to assume that the model provider, the API format and even the agent framework will shift mid-build. Projects that withstand that are the ones worth shipping.

Three smaller lessons follow from it:

• Abstract the LLM provider from day one. A get_llm() function that checks several possible credentials, instead of hardcoding one provider everywhere, turned the Gemini migration into a single-file change and not a full rewrite.
• Read the whole error payload and not only the headline text. Both github_models_retirement_brownout and Function call is missing a thought_signature look like plain 400 errors at first glance. Whether a problem took five minutes or half a day to solve came down to what the error body said, and the headline never told the story.
• Pin dependencies deliberately and document why. After settling on the LangChain 1.x plus create_agent stack, a comment in requirements.txt explains exactly why the older 0.3.x line no longer works, so nobody has to rediscover it the hard way.