Retail Analytics Copilot

A local AI agent for answering retail analytics questions using RAG + SQL over Northwind data.

Graph Design

The LangGraph implementation has 7 nodes forming a hybrid RAG + SQL pipeline:

1. Router: Rule-based classifier that routes queries to rag/sql/hybrid paths
2. Retriever: TF-IDF over markdown docs in docs/, returns top-k chunks with chunk IDs
3. Planner: Extracts date ranges, KPI formulas, and entities from retrieved documents
4. SQL Generator: DSPy module that generates SQLite queries using schema introspection and constraints
5. Executor: Executes SQL queries against Northwind DB, captures results or errors
6. Repair Loop: On SQL error or empty results, retries with DSPy repair module (max 2 iterations)
7. Synthesizer: Formats final answer to match format_hint, adds citations to tables and doc chunks

The graph maintains a stateful trace log for debugging and checkpointing.

DSPy Optimization

Optimized Module: SQLGenerator (NL-to-SQL translation)

Optimizer: BootstrapFewShot with LabeledFewShot fallback, using 21 hand-crafted training examples.

Why BootstrapFewShot Instead of MIPROv2?

The task.md mentions MIPROv2 as a recommended optimizer, but it doesn't work with small local LLMs like Phi-3.5 (3.8B):

Requirement	MIPROv2 Needs	Phi-3.5 Provides
Instruction Generation	LLM must generate structured JSON proposals	❌ Fails - outputs malformed JSON
Model Size	Works best with GPT-4, Claude, or 7B+ models	❌ Too small (3.8B)
Context Length	Needs room for meta-prompts + examples	❌ Limited context window

BootstrapFewShot is the better choice because:

• ✅ Works with small LLMs - no meta-prompt generation needed
• ✅ Bootstraps examples from successful execution traces
• ✅ Minimal LLM overhead during optimization
• ✅ Produces measurable improvements on SQL generation

Training Data Challenges & Solutions

During development, we encountered several challenges with the DSPy optimization:

Challenge	Problem	Solution
Table Name Mismatch	LLM generates OrderDetails or Order Details without quotes, but SQLite requires "Order Details" (with double quotes and space)	Added robust regex-based post-processing to fix all variations
Context Overflow	With 3-5 few-shot demos + full schema, Phi-3.5 (3.8B) couldn't handle the context length	Reduced demos to 1-2, truncated schema to 1500 chars
Date Range Mismatch	Questions reference 1997 but database contains 2012-2023 data	Replace 1997→2022 in questions before sending to LLM
Alias Conflicts	Generated SQL used same alias for different tables (e.g., c for both Categories and Customers)	Improved DSPy signature with explicit JOIN patterns

Metrics

DSPy Optimization Results (BootstrapFewShot, Tesla T4 GPU):

Metric	Before (Baseline)	After (Optimized)	Delta
Average Score	0.86	1.0	+0.14
Success Rate (%)	80.0	100.0	+20.0
Test Queries Passed	4/5	5/5	+1

Evaluation Results (6 eval queries):

Metric	Result
Queries Passed	6/6 (100%)
SQL Execution Success	5/6 (1 RAG-only)
Repair Loop Triggered	0 times

The compiled model is saved to compiled_sql_generator.pkl and automatically loaded at runtime.

Evaluation Results

Query ID	Status	Answer
rag_policy_beverages_return_days	✅	14
hybrid_top_category_qty_summer_1997	✅	{Confections, 18977}
hybrid_aov_winter_1997	✅	28745.2
sql_top3_products_by_revenue_alltime	✅	[Côte de Blaye, Thüringer Rostbratwurst, Mishi Kobe Niku]
hybrid_revenue_beverages_summer_1997	✅	642512.75
hybrid_best_customer_margin_1997	✅	{customer: "IT", margin: 310111.78}

All 6 queries pass! (100% success rate)

Assumptions & Data Notes

Database Date Range

Important: The Northwind database from the task.md URL (jpwhite3/northwind-SQLite3) contains data from 2012-2023, not the classic 1996-1998 range. The marketing calendar references "1997" campaigns, but no 1997 data exists in this database version.

Solution: We replace "1997" with "2022" in questions before sending to the LLM:

• "Summer Beverages 1997" → queries June 2022 data
• "Winter Classics 1997" → queries December 2022 data
• "in 1997" → queries full year 2022 data

This ensures queries return valid results while maintaining the business logic from the docs.

KPI Calculations

• CostOfGoods: Approximated as 70% of UnitPrice (Northwind DB has no cost field)
• Revenue: SUM(UnitPrice * Quantity * (1 - Discount)) from Order Details
• AOV: Revenue / COUNT(DISTINCT OrderID)
• Gross Margin: SUM((UnitPrice - 0.7*UnitPrice) * Quantity * (1 - Discount))

Table Name Handling

The "Order Details" table requires special handling:

• SQLite requires double quotes for table names with spaces
• LLMs frequently generate incorrect variations
• Post-processing regex fixes applied to all generated SQL

Setup & Usage

1. Install Dependencies

pip install -r requirements.txt

2. Start Local LLM Server

# Using Ollama (recommended)
ollama pull phi3.5:3.8b-mini-instruct-q4_K_M
ollama serve

# Or using llama.cpp with the GGUF model in data/
# ./llama-server -m data/phi3.5-q4_K_M.gguf --port 8080

3. Optimize DSPy Modules (Optional but Recommended)

python optimize.py

This trains the SQL generator and saves compiled_sql_generator.pkl.

4. Run Agent on Evaluation Set

python run_agent_hybrid.py \
  --batch sample_questions_hybrid_eval.jsonl \
  --out outputs_hybrid.jsonl

The agent will automatically load the optimized model if available.

Project Structure

├── agent/
│   ├── graph_hybrid.py       # LangGraph implementation (7 nodes)
│   └── dspy_signatures.py    # DSPy modules (Router, SQLGenerator, etc.)
├── rag/
│   └── retrieval.py          # TF-IDF retriever over docs/
├── tools/
│   └── sqlite_tool.py        # SQLite interface + schema introspection
├── docs/                     # RAG corpus (marketing calendar, KPIs, policies)
├── data/
│   └── northwind.sqlite      # Northwind sample database
├── training_data.json        # 21 examples for DSPy optimization
├── optimize.py               # DSPy optimization script
├── run_agent_hybrid.py       # Main CLI entrypoint
└── sample_questions_hybrid_eval.jsonl  # 6 test questions

Lessons Learned

1. Small LLMs are context-sensitive: Phi-3.5 (3.8B) performs better with minimal few-shot examples rather than many demos that overflow context.

2. Post-processing is essential: LLMs make predictable mistakes (like wrong table names) that can be fixed with deterministic post-processing.

3. Optimization can regress: Always evaluate optimized models against baseline and only deploy if they improve.

4. Data alignment matters: Training data must match the actual database contents (dates, schema, values).

5. Hybrid approach works best: Combining DSPy generation with rule-based constraint extraction and post-processing produces more reliable results than pure LLM generation.