QueryPilot 🚀

QueryPilot is an intelligent, schema-aware RAG (Retrieval-Augmented Generation) system designed to convert natural language into precise SQL queries. Unlike generic text-to-SQL tools, QueryPilot extracts your database schema, enriches it with AI-generated synonyms, and uses semantic search to ensure queries are accurate and contextually relevant.

🌟 Key Features

• Schema Extraction: Automatically extracts tables, columns, and data types from your database.
• AI-Powered Synonyms: Generates semantic synonyms for tables and columns using OpenAI (for now) (e.g., "purchases" maps to order_items, "clients" maps to users), enabling natural language queries.
• Schema-Aware RAG: Uses ChromaDB to index schema metadata. Retrieves only relevant tables and columns for the LLM context, reducing hallucinations.
• Validation & Auto-Correction: Generated SQL is validated against the actual schema. If an invalid table or column is used, the system automatically feeds the error back to the LLM for correction.
• Django-Based: Built on a robust, production-ready web framework.

🛠️ Prerequisites

• Python 3.10+
• PostgreSQL (for the application database)
• OpenAI API Key (for embeddings and generation)

📥 Installation

1. Clone the repository:

   git clone https://github.com/yourusername/QueryPilot.git
   cd QueryPilot

2. Create and activate a virtual environment:

   python3 -m venv venv
   source venv/bin/activate  # On Windows: venv\Scripts\activate

3. Install dependencies:

   pip install -r requirements.txt

⚙️ Configuration

Create a .env file in the root directory and configure the following variables:

# Django Settings
DEBUG=True
DJANGO_SECRET_KEY=your-secret-key-here
allowed_hosts=*

# Application Database (PostgreSQL)
SQL_DATABASE=your_db_name
SQL_USER=your_db_user
SQL_PASSWORD=your_db_password
SQL_HOST=localhost
SQL_PORT=5432

🚀 Running the Project

1. Apply Migrations: Initialize the application database.

   python manage.py migrate

2. Start the Development Server:

   python manage.py runserver

3. Access the Application: Open your browser and navigate to http://localhost:8000/app/connect-ui/ (or the relevant start URL).

📖 Usage Guide

1. Connect Database: Enter the connection details for the target database you want to query (supports MySQL/PostgreSQL).
2. Schema Extraction: The system will fetch the schema and generate synonyms in the background.
3. Querying: Use the chat interface to ask questions like:
   • "Show me the top 5 customers by total spend."
   • "How many orders were placed last week?"
4. Results: The system generates the SQL, executes it (if configured), and displays the results.

📂 Project Structure

• apps/query_app/: Core logic (Views, Services).
  • services/schema_extractor.py: Extracts schema & generates synonyms.
  • services/embeddings.py: Handles ChromaDB indexing & vector search.
  • services/rag_engine.py: RAG logic, prompting, and validation.
• chroma_db/: Local vector store (auto-generated).
• core/: Project settings and configuration.
• templates/: HTML templates for the UI.

🤝 Contributing

Contributions are welcome! Please fork the repository and submit a pull request for any enhancements or bug fixes.