🌊 text-to-sql-chat

AI-powered database chat using Llama 3.1, RAG, and local LLMs. Ask questions in plain English, get SQL queries and results instantly.

Ask questions about your database in plain English. No SQL knowledge required.

A self-hosted AI-powered chat application that translates natural language into SQL queries using local LLMs. Built with privacy-first architecture—all processing happens on your machine, no API keys needed.

---

🎯 What It Does

Turn this:

"Which researcher has led the most patrols this year?"

Into this:

SELECT r.name, COUNT(e.id) as patrol_count 
FROM researchers r 
JOIN event_reports e ON r.id = e.lead_researcher_id 
WHERE EXTRACT(YEAR FROM e.start_time) = 2025
GROUP BY r.name 
ORDER BY patrol_count DESC 
LIMIT 1

And get instant results—no SQL knowledge required.

---

✨ Key Features

🤖 AI-Powered SQL Generation

• Uses Llama 3.1 (8B) for accurate text-to-SQL conversion
• Retrieval Augmented Generation (RAG) for context-aware queries
• Learns from your database schema and example queries

🔒 Privacy & Security First

• 100% local processing - no data sent to external APIs
• Read-only database access - prevents accidental data modifications
• SQL injection protection - blocks dangerous operations (DELETE, DROP, UPDATE)
• No API costs - runs entirely on your infrastructure

💻 Dual Interface

• Streamlit Web App - Clean, user-friendly interface for end users
• Jupyter Notebooks - Interactive development environment for data exploration

🎨 Smart Features

• Automatic data visualizations for numeric results
• Multiple output formats (table, JSON, markdown)
• Context-aware follow-up questions
• Query explanation before execution

---

🏗️ Architecture

┌─────────────────────────────────────────────────────────────┐
│                    User Interface Layer                     │
│           Streamlit Web App  |  Jupyter Notebooks           │
└───────────────────────┬─────────────────────────────────────┘
                        │
┌───────────────────────▼─────────────────────────────────────┐
│                   Vanna AI Framework                        │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐       │
│  │   Query      │  │     RAG      │  │   Safety     │       │
│  │  Generator   │  │   Engine     │  │   Guard      │       │
│  └──────────────┘  └──────────────┘  └──────────────┘       │
└───────┬────────────────────┬────────────────────┬───────────┘
        │                    │                    │
        ▼                    ▼                    ▼
┌──────────────┐  ┌──────────────────┐  ┌────────────────┐
│   Ollama     │  │    Weaviate      │  │  PostgreSQL    │
│ (Llama 3.1)  │  │ (Vector Store)   │  │   Database     │
│              │  │                  │  │                │
│ • LLM        │  │ • DDL Storage    │  │ • Marine Data  │
│ • Embeddings │  │ • Documentation  │  │ • Read-only    │
└──────────────┘  └──────────────────┘  └────────────────┘

---

🚀 Quick Start

Prerequisites

Before you begin, ensure you have:

• Docker Desktop (or Docker Engine + Docker Compose)
• 16GB RAM minimum (32GB recommended)
• 20GB free disk space
• (Optional) NVIDIA GPU with CUDA for faster inference

Installation

1. Clone the repository

git clone https://github.com/yourusername/marine-research-chat.git
cd marine-research-chat

2. Start all services

docker-compose up -d

This will start:

• PostgreSQL database (with sample marine research data)
• Ollama LLM server
• Weaviate vector database
• Jupyter Lab
• Streamlit web app

3. Wait for services to initialize (~3-5 minutes)

# Watch the logs
docker-compose logs -f

# Check service health
docker-compose ps

4. Verify everything is running

# All services should show "healthy"
docker-compose ps

# Quick health check
curl http://localhost:8501        # Streamlit should respond
curl http://localhost:8888        # Jupyter should respond
curl http://localhost:11434/api/tags  # Ollama should respond

5. Train the AI on your database (one-time setup)

Open Jupyter Lab: http://localhost:8888

Run the training notebook: notebooks/01_setup_and_training.ipynb

This will:

• Connect to your database
• Extract the schema
• Add business context documentation
• Train the AI with example queries

That's it! 🎉

---

📖 Usage

Option 1: Streamlit Web App (Recommended for End Users)

Access: http://localhost:8501

Perfect for non-technical users who want to query the database conversationally.

Example Questions:

• "How many researchers do we have?"
• "Show me all patrols from the last month"
• "Which species have been observed most frequently?"
• "What's the average water temperature by location?"

Features:

• ✅ Clean, intuitive interface
• ✅ Automatic SQL generation and display
• ✅ Visual charts for numeric data
• ✅ One-click example questions

Option 2: Jupyter Notebooks (For Data Scientists)

Access: http://localhost:8888

Perfect for exploratory data analysis and advanced queries.

Available Notebooks:

1. 01_setup_and_training.ipynb - Initial setup and training
2. 02_interactive_chat.ipynb - Interactive query interface
3. 03_examples_and_demos.ipynb - Comprehensive examples

---

🗄️ Database Schema

The system includes a sample marine research database with:

Tables

• researchers - Marine biologists and scientists
• event_reports - Research patrols and expeditions
• species_observed - Marine species sightings
• environmental_conditions - Water quality measurements
• equipment_used - Scientific equipment deployments

Sample Queries You Can Ask

"How many patrols were conducted last month?"
"Show me all researchers specializing in coral ecology"
"What species were observed at Coral Garden Zone A?"
"Compare water temperature trends across locations"
"Which researcher has the highest biodiversity observations?"

---

🛠️ Configuration

Service Ports

Service	Port	Purpose
Streamlit	8501	Web UI
Jupyter	8888	Notebooks
PostgreSQL	5432	Database
Weaviate	8080	Vector store
Ollama	11434	LLM API

Environment Variables

Key settings in .env:

# Database
POSTGRES_DB=chatdb
POSTGRES_USER=chatuser
POSTGRES_PASSWORD=chatpass

# LLM Model
OLLAMA_MODEL=llama3.1:8b

# Safety Settings
MAX_QUERY_RESULTS=1000
QUERY_TIMEOUT=30

Using Your Own Database

1. Update database connection in config/vanna_config.yaml:

database:
  host: your-db-host
  port: 5432
  database: your-database
  user: your-user
  password: your-password

2. Re-run the training notebook to learn your schema
3. Add business context specific to your domain

---

🎓 How It Works

The RAG (Retrieval Augmented Generation) Pipeline

1. User asks a question in natural language
2. Question is embedded using Ollama's embedding model
3. Similar examples are retrieved from Weaviate vector store
4. Context is constructed with:
   • Database schema (DDL)
   • Business documentation
   • Similar question-SQL pairs
5. Llama 3.1 generates SQL using the context
6. Safety checks validate the query (no DELETE/DROP/UPDATE)
7. Query executes on PostgreSQL
8. Results are formatted and displayed with visualizations

Why This Approach Works

• RAG provides context - The LLM knows your specific schema and terminology
• Examples improve accuracy - Learning from past queries produces better SQL
• Local processing - No data leaves your infrastructure
• Safety first - Multiple validation layers prevent dangerous operations

---

📚 Tech Stack

Component	Technology	Purpose
LLM	Llama 3.1 (8B) via Ollama	SQL generation
Vector DB	Weaviate	Semantic search, RAG
Database	PostgreSQL 16	Data storage
Backend	Vanna AI	Text-to-SQL orchestration
Frontend	Streamlit	Web interface
Dev Environment	Jupyter Lab	Interactive notebooks
Orchestration	Docker Compose	Service management

---

🙏 Acknowledgments

• Vanna AI - Text-to-SQL framework
• Meta - Llama 3.1 model
• Ollama - Local LLM serving
• Weaviate - Vector database

---

🎯 What Makes This Project Special

This is not just another chatbot. It demonstrates:

✅ Modern AI Architecture - RAG, embeddings, vector search
✅ Production Patterns - Docker, health checks, safety guards
✅ Privacy-First Design - No external APIs, local processing
✅ Full-Stack Skills - Database, backend, frontend, ML
✅ Real-World Application - Solves actual data access problems

---