Pagila Database AI Assistant

This project is a Streamlit-based chatbot that allows users to query a PostgreSQL database (Pagila schema) using natural language. It leverages Google's Gemini API for SQL generation and the Model Context Protocol (MCP) for secure database execution.

Features

• Natural Language to SQL: Converts English questions into valid SQL queries.

• Agentic Workflow: The AI autonomously explores the database schema (listing tables, checking columns) before writing queries.

• Vector Caching (RAG): Uses ChromaDB to cache successful SQL queries locally. If a similar question is asked again, the cached SQL is executed immediately, saving API costs and time.

• Model Context Protocol (MCP): Uses a dedicated local server (mcp_pagila_server.py) to handle database operations, separating the UI from the backend logic.

• Cost Tracking: Monitors token usage and estimates costs for both the current session and global history.

• Schema Visualization: Displays the database schema diagram and metadata within the UI.

Architecture

1. Frontend: Streamlit (app.py) handles user input, chat history, and visualization.

2. AI Brain: Google Gemini (via google-generativeai) acts as the reasoning engine.

3. Backend: An MCP Server (mcp_pagila_server.py) runs as a subprocess, exposing tools like list_tables, get_table_schema, and execute_sql.

4. Database: PostgreSQL hosting the Pagila sample database.

5. Cache: ChromaDB stores embeddings of questions and their corresponding SQL.

Prerequisites

• Python 3.10+

• PostgreSQL database with the Pagila schema installed.

• Google Gemini API Key.

Installation

1. Clone the repository:

   git clone <repository-url>
   cd mcp-pagila-server

2. Install dependencies:

   pip install -r requirements.txt

3. Configure Environment: Create a config.env file in the root directory:

   GEMINI_API_KEY=your_google_api_key_here
   PGHOST=localhost
   PGUSER=your_postgres_user
   PGPASSWORD=your_postgres_password
   PGDATABASE=pagila
   LOG_DIR=logs

   Note: It is recommended to use a read-only database user for security.

Usage

Run the Streamlit application:

streamlit run app.py

Testing & Inspection + +streamlit_app.py is provided as a lightweight, chat-like interface to test the MCP server directly without the full Gemini Agent loop. It is useful for debugging the MCP server connection and running raw SQL (using a run: prefix) or heuristic-based queries

Project Structure

• app.py: Main Streamlit application.

• mcp_pagila_server.py: MCP server implementation.

• pagila-metadata.txt: Text-based schema summary for the AI.

• requirements.txt: Python dependencies.

• vector_store/: Directory where ChromaDB persists data (ignored in git).

• usage_stats.json: Local file tracking usage costs (ignored in git).