Autonomous Data Analyst (ADA)

> Multi-Agent Text-to-SQL system that tames messy, heavily-relational databases through autonomous schema simplification and RAG-powered query execution.

---

The Problem

Real-world databases are often too large and chaotic for traditional Text-to-SQL pipelines. A single table can be joined to ten others scattered across five schemas, and passing the raw DDL of even a handful of such tables into an LLM immediately exhausts the context window — leading to hallucinations, missed columns, and broken SQL. ADA solves this by separating the work into two distinct phases: an Offline Profiling Pipeline that runs autonomously once (or periodically) to compress the database into a clean semantic meta-schema, and a Runtime Agentic Workflow that uses that meta-schema at query time to build a minimal, noise-free context before generating and executing SQL.

---

Phase 1 — Offline Profiling Pipeline

The goal of this phase is to convert a raw, messy database into a compact, LLM-friendly representation that lives in the same database (using the agents schema). It runs independently of any user query.

Step 1 — Schema & Data Extraction

• The database is reflected through SQLAlchemy, with table DDL, foreign keys, and a small sample of rows captured per table.
• Each run compares live DDL hashes with stored metadata so unchanged tables can be skipped.

Step 2 — Table Summarization

• Tables are summarized one at a time with an LLM to keep context bounded.
• Each summary is stored with its table metadata and an embedding string for later retrieval.

Step 3 — Relationship Mapping

• Exact foreign-key edges are written first, then batched LLM passes infer additional soft relations.
• Batched relation inference is constrained to schema/FK-connected groups and avoids conflicts with explicit keys.
• The relationship graph is persisted as incremental database artifacts and CSV outputs.
• The LLM returns a simplified JSON relationship graph:

json { "relationships": [ { "from": "orders", "to": "customers", "via": "customer_id", "type": "many-to-one" } ] }

• This graph is the key artifact that allows the runtime agent to discover JOIN paths without ever looking at raw DDL again.

Step 4 — Vector Embedding & Persistence

• Table and schema summaries are embedded and written into agents.agent_tables and agents.agent_schemas.
• Vectors are stored using the pgvector extension for efficient similarity search.

---

Phase 2 — Runtime Agentic Workflow

This phase runs every time a user submits a natural-language question.

Step 5 — Query Intent Embedding & Retrieval

• User queries are normalized with schema summary context before embedding.
• The normalized query is vectorized and matched against stored table embeddings using cosine similarity in the database.

Step 6 — Schema Pruning / State Construction

• Retrieved tables are expanded through the relationship graph with BFS-style JOIN traversal.
• The runtime state keeps only the relevant tables, relationships, and query context needed downstream.
• Deterministic schema budget guards evaluate table/column/token complexity and can prune or block overly broad contexts before SQL generation.

Step 7 — State Injection

• The pruned schema, relationship graph, query text, and downstream fields flow through shared runtime state.

Step 8 — SQL Generation

• The SQL agent builds a single SELECT statement from the query, pruned schema, and relationship graph.
• SQL generation token limits are now budget-driven (tiny/standard/deep) rather than fixed per request.

Step 9 — Validation & Execution

• The validation agent executes SQL against the database and retries with LLM-assisted repairs on failure.

Step 10 — Visualization

• The visualization agent inspects the result shape and produces a chart config or table view automatically.

---

Project Structure

ADA_Sysltd/
│
├── src/
│   ├── __init__.py
│   │
│   ├── profiling/                    # Offline pipeline
│   │   ├── __init__.py
│   │   ├── db_connector.py           # SQLAlchemy DB connection & reflection
│   │   ├── detailed_schema_builder.py # Pruned schema payload construction
│   │   ├── pipeline.py               # Incremental profiling orchestration
│   │   ├── pipeline_artifacts.py     # Database schema/tables setup and persistence
│   │   ├── pipeline_config.py        # Profiling config and env flags
│   │   ├── pipeline_relations.py     # Relationship batching and filtering
│   │   ├── pipeline_schema.py        # Table and schema profiling helpers
│   │   ├── schema_extractor.py       # DDL extraction + 1-2 row samples
│   │   ├── summarization_agent.py    # Per-table LLM summarization
│   │   └── relational_mapping_agent.py  # Relationship graph builder
│   │
│   ├── runtime/                      # Online / query-time pipeline
│   │   ├── __init__.py
│   │   ├── schema_builder.py         # Schema context construction and pruning
│   │   └── retrieval/                # Query normalization and top-k retrieval
│   │       ├── __init__.py
│   │       ├── contract.py           # TypedDict definitions for state
│   │       ├── normalizer.py         # LLM query normalization
│   │       ├── retriever.py          # Direct pgvector similarity search
│   │       ├── service.py            # Retrieval entry points
│   │       ├── state.py              # RetrievalState construction and orchestration
│   │       └── table_search.py       # Helper for table candidate search
│   │
│   └── agents/                       # Execution agents
│       ├── __init__.py
│       ├── sql_agent.py              # Natural language → SQL (generate_sql)
│       ├── validation_agent.py       # SQL execution (validate_and_execute)
│       ├── visualization_agent.py    # Data → chart config (generate_chart_config)
│       └── workflow.py               # LangGraph state machine definition
│
├── requirements.txt
├── .gitignore
└── README.md

---

Tech Stack

| Layer | Technology | |---|---| | Agent orchestration | LangChain + LangGraph | | LLM provider | OpenAI GPT-4o (or any LangChain-compatible LLM) | | Vector store | PostgreSQL + pgvector | | Database connectivity | SQLAlchemy | | Data manipulation | Pandas | | Visualization | Plotly, Matplotlib | | Environment management | python-dotenv |

---

Getting Started

1. Clone & install dependencies

git clone https://github.com/Zeeshan506/ADA_Sysltd.git
cd ADA_Sysltd
uv venv --python 3.11 .venv
source .venv/bin/activate   # Windows: .venv\Scripts\activate
uv sync --python 3.11

2. Configure environment variables

cp .env.example .env
# Edit .env and set your OPENAI_API_KEY (or equivalent LLM credentials)
# and your DATABASE_URL (e.g. postgresql://user:pass@host/dbname)
# Optional: set RETRIEVAL_MAX_TOKENS=16000 (or lower) to cap normalizer output tokens.
# Optional: keep RETRIEVAL_MAX_TOKENS as a fallback only; runtime now uses per-query budget caps
#           (normalizer/sql caps come from the deterministic guard pipeline).
# Optional provider switch:
#   USE_OLLAMA=true    -> use local Ollama chat models
#   OLLAMA_MODEL=qwen3 -> override default Ollama model name
#   USE_AZURE=true     -> use Azure OpenAI chat deployments
#   AZURE_APIKEY=...   -> Azure OpenAI API key (AZURE_API_KEY also works)
#   TARGET_URL=...     -> Azure deployment chat completions URL
# If neither USE_OLLAMA nor USE_AZURE is set, OpenRouter/OpenAI-compatible env vars are used as normal.
# Ensure the database has the pgvector extension installed.

3. Run the Offline Profiling Pipeline

# This pipeline handles DDL extraction, summarization, relationship mapping, and embedding persistence.
uv run python -m src.profiling.pipeline  # Runs all stages

4. Run a query (example)

from src.profiling.db_connector import get_engine
from src.runtime.retrieval.state import build_retrieval_state

engine = get_engine()
# build_retrieval_state runs normalization, retrieval, schema pruning, and the agent workflow (SQL, validation, visualization).
state = build_retrieval_state(engine, "Show me total revenue by region for last quarter")

print(f"Generated SQL: {state['generated_sql']}")
print(f"Validation Score: {state['validation_score']}")
if state["query_result"]:
    print(f"Result (first row): {state['query_result'][0]}")

---

Roadmap

• [x] Phase 1: Offline Profiling Pipeline implementation
• [x] Phase 2: Runtime RAG + LangGraph state management
• [x] Phase 3: Normalizer
• [x] Phase 4: Schema Builder
• [x] Phase 5: SQL Generator
• [ ] Phase 6: Validation and Visualization agents
• [x] Phase 7: CLI / API interface
• [x] Phase 8: Follow Up Query (Context Conversion Across Quries)
• [x] Phase 9: Web UI baseline (ui/ Next.js + Python bridge)

Web UI (Cloud Shell Friendly)

Use the new ui/ app when Streamlit is not available on your environment.

cd ui
npm install
npm run dev -- --hostname 0.0.0.0 --port 8080

Then open Cloud Shell Web Preview on port 8080.

FastAPI backend (Phase 1 migration path)

Run backend API with SSE query streaming:

uv run uvicorn backend.main:app --host 0.0.0.0 --port 8000

Enable the frontend stream path in ui/.env.local:

NEXT_PUBLIC_UI_USE_FASTAPI_STREAM=true
NEXT_PUBLIC_BACKEND_URL=http://127.0.0.1:8000
NEXT_PUBLIC_UI_ENABLE_INCREMENTAL_ROWS=true
NEXT_PUBLIC_UI_ENABLE_NEW_CHARTS=true
NEXT_PUBLIC_UI_STREAM_RETRY_COUNT=2

Optional backend row-event cap:

QUERY_STREAM_ROW_LIMIT=100

Runtime Guard/Budget State Fields

Both backend (backend/query_runner.py) and UI bridge (tools/ui_query_bridge.py) now return guard/budget diagnostics in state:

• normalizer_mode
• normalizer_budget
• retrieval_budget
• schema_budget
• complexity_score
• complexity_reasons
• guard_action (proceed / prune_and_proceed / block)
• schema_guard_action
• schema_guard_reasons
• schema_guard_metrics

These fields are intended for observability, tuning, and explaining blocked/pruned query behavior.

Profiling admin (Phase 2)

Open http://localhost:8080/profiling (or your Cloud Shell preview URL + /profiling) to:

1. browse schemas and profiled tables from agents.agent_tables
2. edit table summary fields (purpose, key_columns, notes) with embedding refresh
3. trigger the profiling pipeline and track run status

---

License

MIT

Sample Quries:

1. List employees along with their current department and shift assignment. Show only active employees and their latest department records.

1. Find products that have had the highest number of inventory movements and include their category and subcategory details.

1. Analyze customers who have placed multiple orders and identify which regions they belong to. Compare their total order value across different regions.

1. Determine which vendors supply products that are most frequently included in completed purchase orders, and evaluate whether those products have stable pricing over time compared to similar products in the same category.

MCP Setup

• Install gitnexus and run gitnexus analyze . in the repo
• run gitnexus serve to run the mcp server locally
• Add this to ~/.copilot/mcp-config.json

{
  "mcpServers": {
    "gitnexus": {
      "url": "http://localhost:4747/api/mcp"
    }
  }
}

Courses To Cover first Fifteen days deeplearning.ai

Week 1: Advanced Agent Architecture & Memory

1. Functions, Tools and Agents with LangChain
2. Multi AI Agent Systems with crewAI
3. AI Agentic Design Patterns with AutoGen
4. LLMs as Operating Systems: Agent Memory
5. Building Agentic RAG with LlamaIndex

Week 2: Evaluation, Testing & Reliability

1. Evaluating AI Agents
2. Building and Evaluating Advanced RAG Applications
3. Automated Testing for LLMOps
4. Quality and Safety for LLM Applications
5. Red Teaming LLM Applications

Week 3: Production, Optimization & Cloud Alignment

1. Quantization Fundamentals with Hugging Face (DeepLearning.AI) - Teaches you how to shrink massive models to run locally or cheaply.
2. Getting Started with Mistral (DeepLearning.AI) - Mistral is the biggest open-source rival to OpenAI. Learning their API is highly valued.
3. Build Apps with Windsurf's AI Coding Agents (DeepLearning.AI) - Good for understanding graph-aware coding agents.
4. (Alternative) Hugging Face NLP Course - Fine-Tuning (Free) - Skip the AWS Generative AI course and take the free Hugging Face course chapter on Fine-Tuning. It will teach you the math and code behind PEFT and LoRA using free Colab compute.
5. (Alternative): Hugging Face RLHF Tutorial (Free) - Search for Hugging Face’s free blog/tutorial on "Illustrating Reinforcement Learning from Human Feedback." This covers the exact alignment techniques you would have learned in the AWS specialization, but using open-source tools.