Distribution Analytics AI Agent

A sophisticated AI-powered application for querying and visualizing distribution analytics data from the Chinook music store database. This project leverages a graph-based agent architecture to process natural language queries, generate SQL, and create interactive data visualizations.

Features

• Natural Language to SQL: Converts complex user questions into specific SQLite queries.
• Intelligent Query Rewriting: Automatically refines vague queries (e.g., "show sales") into actionable requests.
• Agentic Workflow: Uses LangGraph to orchestrate a team of specialized agents (Router, Table Selector, SQL Generator, Validator, Visualization Planner).
• Interactive Visualizations: Dynamically generates Vega-Lite charts when data is best suited for visual representation.
• Scope Guardrails: A General Agent politely handles out-of-scope queries, guiding users back to the domain.
• Real-time Streaming: Server-Sent Events (SSE) provide a real-time view of the agent's thought process and execution steps.
• Modern UI: A polished, responsive React frontend built with Tailwind CSS.

Architecture

graph TD
    User(User Query) --> Router{Query Router}
    
    Router -- Irrelevant --> General[General Agent]
    Router -- Relevant --> Rewriter[Query Rewriter]
    
    General --> End
    
    Rewriter --> TableSelector[Table Selector]
    
    TableSelector --> SQLGen[SQL Generator]
    
    SQLGen --> Validator{SQL Validator}
    
    Validator -- Invalid (Retry ≤3) --> SQLGen
    Validator -- Invalid (Max Retries) --> End
    Validator -- Valid --> Executor[SQL Executor]
    
    Executor -- Error (Retry ≤3) --> SQLGen
    Executor -- Success --> Synthesizer[Response Synthesizer]
    Executor -- Error (Max Retries) --> Synthesizer
    
    Synthesizer --> VizPlanner{Visualization Planner}
    
    VizPlanner -- No Viz Needed --> End
    VizPlanner -- Needs Viz --> VizGen[Visualization Generator]
    
    VizGen --> End

Loading

Getting Started

Prerequisites

• Python: 3.10 or higher
• Node.js: 18 or higher
• Backend Manager: uv (recommended) or pip
• Frontend Manager: npm or bun
• OpenAI API Key: You need a valid API key with access to GPT-4o.

Installation

Clone the repository:

git clone https://github.com/Thebinary110/Agenctic_sql.git

1. Backend Setup

Navigate to the backend directory:

cd backend

Create a virtual environment and install dependencies.

Using uv (Fast & Recommended):

uv sync

Using pip:

python -m venv .venv
source .venv/bin/activate  # On Windows: .venv\Scripts\activate
pip install -r requirements.txt

Set up your environment variables: Create a .env file in the backend directory:

touch .env

Add your OpenAI API key to .env:

OPENAI_API_KEY=sk-your-openai-api-key-here

2. Frontend Setup

Open a new terminal and navigate to the frontend directory:

cd frontend

Install dependencies:

npm install
# OR
bun install

Running the Application

You will need to run both the backend and frontend servers simultaneously.

Terminal 1 (Backend):

cd backend
./run_api.sh

The backend API will start at http://localhost:8000.

Terminal 2 (Frontend):

cd frontend
npm run dev

The React application will start at http://localhost:5173.

Project Structure

sql-agent-app/
├── backend/                # FastAPI application
│   ├── app/
│   │   ├── agents/         # Logic for specific agents (Router, SQL, Viz, etc.)
│   │   ├── graph/          # LangGraph workflow definition
│   │   ├── api/            # REST endpoints and SSE streaming
│   │   ├── tools/          # Utilities for DB access and validation
│   │   └── state/          # Global state definition
│   ├── run_api.sh          # Helper script to launch the server
│   └── requirements.txt    # Python dependencies
│
└── frontend/               # React application
    ├── src/
    │   ├── components/     # UI components (ChatInterface, TraceMonitor)
    │   ├── hooks/          # Custom React hooks (useChat)
    │   └── types/          # TypeScript interfaces
    ├── tailwind.config.js  # Styling configuration
    └── package.json        # Frontend dependencies