🛫 DataPilot AI

DataPilot AI is a production-quality, AI-powered database assistant that allows users to upload CSV files, automatically converts them into an isolated SQLite database, and enables natural language data analysis using an Ollama LLM Agent (qwen3:8b) communicating strictly through Model Context Protocol (MCP) tools.

---

🏛️ Architecture & Clean Isolation

 +-----------------------------------------------------------------------+
 |                            Streamlit UI                               |
 |     - CSV Upload & Table Data Preview                                 |
 |     - Natural Language Query Interface                                |
 |     - Render SQL Queries, Results & Dynamic Plotly Charts             |
 +-----------------------------------+-----------------------------------+
                                     |
                                     v
 +-----------------------------------+-----------------------------------+
 |                             AI Agent                                  |
 |     - Ollama (`qwen3:8b`) Agent Loop                                  |
 |     - Translates user questions into MCP tool calls                   |
 |     - STRICTLY NO direct database access                              |
 +-----------------------------------+-----------------------------------+
                                     |
                                     | (MCP JSON-RPC Protocol)
                                     v
 +-----------------------------------+-----------------------------------+
 |                            MCP Server                                 |
 |     - Built with FastMCP / MCP Python SDK                             |
 |     - Exposes isolated tools:                                         |
 |         * list_tables()                                               |
 |         * describe_table(table_name)                                  |
 |         * run_sql(query)                                              |
 +-----------------------------------+-----------------------------------+
                                     |
                                     v
 +-----------------------------------+-----------------------------------+
 |                      Database & Storage Layer                         |
 |     - SQLite Database (`database/datapilot.db`)                       |
 |     - SQLAlchemy ORM & Engine abstraction                             |
 |     - Ingestion Layer (`database/csv_loader.py`)                      |
 +-----------------------------------------------------------------------+

Key Architectural Principles

• Strict MCP Tool Isolation: The AI Model never opens SQLite files or executes SQL directly. It interacts with data solely through registered MCP server tools.
• Security Guard: run_sql blocks write operations (DROP, DELETE, INSERT, UPDATE, ALTER).
• Type Hints & Clean Code: Type annotations (typing), modern Python practices (pathlib), modular functions under 30 lines, proper logging, and exception handling.

---

🛠️ Tech Stack

• Frontend: Streamlit
• Backend: Python 3.10+
• Database: SQLite, SQLAlchemy
• AI / LLM: Ollama (qwen3:8b)
• Protocol: Official MCP Python SDK / FastMCP
• Data Visualization: Plotly
• Data Processing: Pandas
• Configuration: python-dotenv, Pydantic

---

📂 Project Structure

DataPilot-AI/
├── app/
│   ├── __init__.py
│   └── main.py              # Streamlit Web UI application
├── database/
│   ├── __init__.py
│   ├── database.py          # SQLAlchemy database engine management
│   └── csv_loader.py        # CSV parsing & SQL table ingestion
├── agent/
│   ├── __init__.py
│   └── agent.py             # Ollama AI agent & MCP tool dispatcher
├── mcp_server/
│   ├── __init__.py
│   ├── server.py            # FastMCP server transport
│   └── tools.py             # MCP database tool implementations
├── charts/
│   ├── __init__.py
│   └── chart_generator.py   # Automated Plotly chart generator
├── uploads/                 # Storage for raw CSV uploads
├── database/                # SQLite database directory (`datapilot.db`)
├── scratch/                 # Verification test scripts
├── .env.example             # Environment variables template
├── requirements.txt         # Pinned project dependencies
└── README.md                # Project documentation

---

⚙️ Quickstart Guide

1. Clone & Setup Virtual Environment

git clone https://github.com/taneeshk12/hcai_project.git DataPilotAI
cd DataPilotAI

# Create virtual environment
python3 -m venv .venv
source .venv/bin/activate

# Install dependencies
pip install --upgrade pip
pip install -r requirements.txt

2. Configure Environment Variables

Copy .env.example to .env:

cp .env.example .env

3. Setup & Start Ollama

Make sure Ollama is installed and running locally with the target model:

# Start Ollama server
ollama serve

# Pull target model in a separate terminal
ollama pull qwen3:8b

4. Run the Application

Launch the Streamlit interface:

streamlit run app/main.py

Open http://localhost:8501 in your browser.

---

🔌 MCP Tools Specification

The MCP server exposes three database tools:

Tool Name	Parameters	Description
list_tables()	None	Returns a JSON list of all active tables in the SQLite database.
describe_table(table_name)	table_name: str	Returns schema, column types, total row count, and 3 sample records.
run_sql(query)	query: str	Executes a read-only SQL query and returns matching records.

---

💡 Usage Example

1. Upload CSV: Drag and drop amazon.csv (or any CSV dataset) into the uploader.
2. Automatic SQL Ingestion: DataPilot AI sanitizes the filename into a table name (e.g. amazon) and creates a SQLite table with full row insertion.
3. Ask Natural Language Questions:
   • "How many rows are in the database?"
   • "Show all columns for table amazon"
   • "What are the top 5 highest rated items?"
   • "Show average price by category"
4. Inspect Output:
   • Generated SQL Query displayed first in a code block.
   • AI Answer in clean natural language text.
   • MCP Execution Trace showing tool calls.
   • Automated Plotly Chart generated automatically for numerical data.

---

📜 License

MIT License. Created for AI Product Engineering portfolio.