# Local SQL Agent — Technical Documentation > **Project Name:** Local SQL Agent (Auction NLQ Engine) > > **Purpose:** A fully local, on-premise Natural Language Query (NLQ) engine that converts Persian or English questions into Microsoft SQL Server queries, executes them, and returns results — without sending any data to external services. > > **Target Users:** Data analysts at the Iran Mercantile Exchange (IME) who need to query the auction database using plain Persian or English language instead of writing SQL manually. --- ## Table of Contents 1. [Project Overview](#1-project-overview) 2. [System Architecture](#2-system-architecture) 3. [End-to-End Workflow — Step by Step](#3-end-to-end-workflow--step-by-step) 4. [Module Breakdown](#4-module-breakdown) 5. [API Endpoints](#5-api-endpoints) 6. [Security Model](#6-security-model) 7. [Configuration](#7-configuration) 8. [How to Run](#8-how-to-run) 9. [Testing](#9-testing) --- ## 1. Project Overview ### 1.1 What It Does — One-Line Summary ``` Persian/English Question ──► SQL Query ──► Result Table (NLQ) (T-SQL) (DataFrame) ``` ### 1.2 The Problem We Solve ``` ┌─────────────────────────────────────────────────────────────────────┐ │ │ │ BEFORE (Manual Process): │ │ │ │ Analyst thinks question ──► Analyst writes SQL ──► Runs query │ │ in Persian (complex, error-prone) manually │ │ │ │ Problems: │ │ ✗ Analysts don't know SQL │ │ ✗ SQL requires English syntax │ │ ✗ Complex JOINs across 5+ tables │ │ ✗ Time-consuming, error-prone │ │ │ ├─────────────────────────────────────────────────────────────────────┤ │ │ │ AFTER (With Local SQL Agent): │ │ │ │ Analyst thinks question ──► System generates SQL ──► Returns data │ │ in Persian automatically + Excel │ │ │ │ Benefits: │ │ ✓ Zero SQL knowledge required │ │ ✓ Persian language natively supported │ │ ✓ Correct JOINs generated automatically │ │ ✓ Results in seconds, exported to Excel │ │ │ └─────────────────────────────────────────────────────────────────────┘ ``` ### 1.3 Why It Must Be Local ``` ┌──────────────────────────────────────────────────────────────────┐ │ SECURITY CONSTRAINTS │ ├──────────────────────────────────────────────────────────────────┤ │ │ │ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │ │ │ SENSITIVE │ │ NO CLOUD │ │ NO EXTERNAL│ │ │ │ FINANCIAL │ │ PERMISSIONS│ │ API BUDGET │ │ │ │ DATA │ │ │ │ │ │ │ └──────┬───────┘ └──────┬───────┘ └──────┬───────┘ │ │ │ │ │ │ │ └────────────────────┼────────────────────┘ │ │ │ │ │ ▼ │ │ ┌──────────────────┐ │ │ │ MUST RUN ON- │ │ │ │ PREMISE ONLY │ │ │ │ │ │ │ │ OpenAI-compat. │ │ │ │ LLM + SQL │ │ │ │ Server on │ │ │ │ company hardware│ │ │ └──────────────────┘ │ │ │ └──────────────────────────────────────────────────────────────────┘ ``` ### 1.4 Key Numbers ``` ┌─────────────────────────────────────────────────────────────┐ │ PROJECT AT A GLANCE │ ├──────────────────┬──────────────────┬───────────────────────┤ │ LANGUAGES │ MODELS │ RETRIEVAL │ │ ───────── │ ────── │ ───────── │ │ Persian + │ Any Model on │ 6 Independent │ │ English │ the OpenAI- │ Modules │ │ │ compatible │ │ │ │ endpoint │ │ │ │ (gpt-oss, …) │ │ ├──────────────────┼──────────────────┼───────────────────────┤ │ TESTS │ DEPLOYMENT │ CLOUD │ │ ────── │ ────────── │ ───── │ │ 5,000+ │ On-Premise │ OpenAI-compatible │ │ Unit + │ Only │ LLM Endpoint │ │ Integration │ │ │ └──────────────────┴──────────────────┴───────────────────────┘ ``` ### 1.5 Database Schema Overview The system operates on a **Star Schema** data warehouse (`sales`/`ref` in the shipped example config): ``` ┌──────────────┐ │ sales │ │ Date │ └──────┬───────┘ │ ┌──────────────┐ ┌───────────────┼───────────────┐ ┌──────────────┐ │ sales │ │ │ │ │ ref │ │ Customer ├───┤ │ ├───┤ Ring │ └──────────────┘ │ │ │ └──────────────┘ │ │ │ ┌──────────────┐ │ │ │ ┌──────────────┐ │ ref │ │ │ │ │ ref │ │ Broker ├───┤ │ ├───┤ Symbol │ └──────────────┘ │ │ │ └──────────────┘ │ ┌──────────┴──────────┐ │ │ │ sales │ │ │ │ ┌─────────────┐ │ │ └────┴───┤ Order ├───┴────┘ │ └─────────────┘ │ └─────────────────────┘ sales = Fact + core dimension tables (Order, Customer, Date, OrderStatus) ref = Shared reference/dimension tables (Broker, Currency, Location, Ring, Symbol, Supplier) ``` --- ## 2. System Architecture The system is built as a **modular pipeline**. Each step is a separate module that can be tested, replaced, or extended independently. ### 2.1 High-Level Architecture — Layered View ``` ╔═══════════════════════════════════════════════════════════════════════╗ ║ ║ ║ LAYER 1: USER INTERFACE ║ ║ ┌─────────────────────────────┐ ┌──────────────────────────────┐ ║ ║ │ │ │ │ ║ ║ │ CLI (Terminal REPL) │ │ FastAPI HTTP API │ ║ ║ │ │ │ │ ║ ║ │ app.py │ │ api/server.py │ ║ ║ │ │ │ │ ║ ║ │ Interactive terminal │ │ REST endpoints: │ ║ ║ │ for single-user use │ │ POST /query │ ║ ║ │ │ │ GET /health │ ║ ║ └──────────────┬──────────────┘ │ GET /cache/stats │ ║ ║ │ └──────────────┬───────────────┘ ║ ║ │ │ ║ ╠═════════════════╪═════════════════════════════════╪═══════════════════╣ ║ ▼ ▼ ║ ║ LAYER 2: ORCHESTRATION ║ ║ ┌────────────────────────────────────────────────────────────────┐ ║ ║ │ │ ║ ║ │ ┌──────────────────┐ ┌──────────────────────────┐ │ ║ ║ │ │ api/runner.py │ │ llm/sql_agent.py │ │ ║ ║ │ │ │ │ │ │ ║ ║ │ │ HTTP query │ │ Core pipeline: │ │ ║ ║ │ │ orchestrator │ │ retrieve → prompt → │ │ ║ ║ │ │ + cache mgmt │ │ generate → validate → │ │ ║ ║ │ │ │ │ execute → correct │ │ ║ ║ │ └────────┬─────────┘ └────────────┬─────────────┘ │ ║ ║ │ │ │ │ ║ ║ └───────────┼─────────────────────────────────┼──────────────────┘ ║ ║ │ │ ║ ╠══════════════╪═════════════════════════════════╪══════════════════════╣ ║ ▼ ▼ ║ ║ LAYER 3: RETRIEVAL PIPELINE ║ ║ ┌────────────────────────────────────────────────────────────────┐ ║ ║ │ │ ║ ║ │ retrieval/context_retriever.py (Orchestrator) │ ║ ║ │ │ ║ ║ │ ┌──────────────┐ ┌──────────────┐ ┌──────────────────┐ │ ║ ║ │ │ Entity │ │ Fact │ │ Relationship │ │ ║ ║ │ │ Retriever │ │ Retriever │ │ Retriever │ │ ║ ║ │ │ │ │ │ │ │ │ ║ ║ │ │ Detects │ │ Detects │ │ Generates │ │ ║ ║ │ │ dimension │ │ fact tables │ │ JOIN clauses │ │ ║ ║ │ │ tables │ │ │ │ │ │ ║ ║ │ └──────────────┘ └──────────────┘ └──────────────────┘ │ ║ ║ │ ┌──────────────┐ ┌──────────────┐ ┌──────────────────┐ │ ║ ║ │ │ Rule │ │ Example │ │ Value │ │ ║ ║ │ │ Retriever │ │ Retriever │ │ Retriever │ │ ║ ║ │ │ │ │ │ │ │ │ ║ ║ │ │ Injects │ │ Selects │ │ Extracts │ │ ║ ║ │ │ business │ │ few-shot │ │ filter values │ │ ║ ║ │ │ rules │ │ examples │ │ from question │ │ ║ ║ │ └──────────────┘ └──────────────┘ └──────────────────┘ │ ║ ║ │ │ ║ ║ └──────────────────────────────┬─────────────────────────────────┘ ║ ║ │ ║ ╠═════════════════════════════════╪═════════════════════════════════════╣ ║ ▼ ║ ║ LAYER 4: PROMPT ASSEMBLY ║ ║ ┌────────────────────────────────────────────────────────────────┐ ║ ║ │ │ ║ ║ │ prompt_engine/builder.py │ ║ ║ │ │ ║ ║ │ Static prefix + variable suffix (Step 5): │ ║ ║ │ │ ║ ║ │ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ ║ ║ │ │ System │ │ Business │ │ Schema │ │ Relations│ │ ║ ║ │ │ Prompt │ │ Rules │ │ │ │ hips │ │ ║ ║ │ └────┬─────┘ └────┬─────┘ └────┬─────┘ └────┬─────┘ │ ║ ║ │ │ │ │ │ │ ║ ║ │ └────────┬───┴────────┬───┴────────┬───┘ │ ║ ║ │ │ │ │ │ ║ ║ │ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ ║ ║ │ │ Filters │ │ Examples │ │ Question │ ──► Single Prompt │ ║ ║ │ └────┬─────┘ └────┬─────┘ └────┬─────┘ │ ║ ║ │ └────────┬───┴────────┬───┘ │ ║ ║ │ └────────┬───┘ │ ║ ║ └──────────────────────────┼─────────────────────────────────────┘ ║ ║ │ ║ ╠═════════════════════════════╪═════════════════════════════════════════╣ ║ ▼ ║ ║ LAYER 5: LLM GENERATION ║ ║ ┌────────────────────────────────────────────────────────────────┐ ║ ║ │ │ ║ ║ │ llm/router.py ──► OpenAI-compat. API │ ║ ║ │ │ ║ ║ │ ┌──────────────────────────────────────────────────────┐ │ ║ ║ │ │ │ │ ║ ║ │ │ Prompt ──► POST /chat/completions │ │ ║ ║ │ │ │ │ │ ║ ║ │ │ ▼ │ │ ║ ║ │ │ ┌──────────────┐ │ │ ║ ║ │ │ │ OpenAI-compat│ │ │ ║ ║ │ │ │ Model │ gpt-oss / vLLM / │ │ ║ ║ │ │ │ (Endpoint) │ LM Studio / ... │ │ ║ ║ │ │ └──────┬───────┘ │ │ ║ ║ │ │ │ │ │ ║ ║ │ │ ▼ │ │ ║ ║ │ │ Raw SQL Text ◄─── (3 retries + backoff) │ │ ║ ║ │ │ │ │ ║ ║ │ └──────────────────────────────────────────────────────┘ │ ║ ║ │ │ ║ ║ └──────────────────────────────┬─────────────────────────────────┘ ║ ║ │ ║ ╠═════════════════════════════════╪═════════════════════════════════════╣ ║ ▼ ║ ║ LAYER 6: SECURITY VALIDATION ║ ║ ┌────────────────────────────────────────────────────────────────┐ ║ ║ │ │ ║ ║ │ security/sql_guard.py │ ║ ║ │ │ ║ ║ │ Raw SQL ──► clean_sql ──► validate_sql ──► ensure_top │ ║ ║ │ │ │ ║ ║ │ ├── One SELECT only? ──► ERR │ ║ ║ │ ├── Tables/columns ok? ──► ERR │ ║ ║ │ └── ACL ok? ──► PASS │ ║ ║ │ │ ║ ║ └──────────────────────────────┬─────────────────────────────────┘ ║ ║ │ ║ ╠═════════════════════════════════╪═════════════════════════════════════╣ ║ ▼ ║ ║ LAYER 7: DATABASE EXECUTION ║ ║ ┌────────────────────────────────────────────────────────────────┐ ║ ║ │ │ ║ ║ │ database/connection.py ──► database/executor.py │ ║ ║ │ │ ║ ║ │ ┌──────────────────────────────────────────────────────┐ │ ║ ║ │ │ SQLAlchemy Engine (one per data source) │ │ ║ ║ │ │ pool_size=10 | max_overflow=20 | idle ping │ │ ║ ║ │ └──────────────────────┬───────────────────────────────┘ │ ║ ║ │ │ │ ║ ║ │ ▼ │ ║ ║ │ ┌──────────────────────────────────────────────────────┐ │ ║ ║ │ │ SQL Server (ODBC Driver 17 or 18) │ │ ║ ║ │ │ the warehouse database │ │ ║ ║ │ └──────────────────────┬───────────────────────────────┘ │ ║ ║ │ │ │ ║ ║ │ ▼ │ ║ ║ │ Result Set │ ║ ║ │ (pandas DataFrame) │ ║ ║ └──────────────────────────────┬─────────────────────────────────┘ ║ ║ │ ║ ╠═════════════════════════════════╪═════════════════════════════════════╣ ║ ▼ ║ ║ LAYER 8: OUTPUT ║ ║ ┌────────────────────────────────────────────────────────────────┐ ║ ║ │ │ ║ ║ │ ┌──────────────┐ ┌──────────────┐ ┌──────────────────┐ │ ║ ║ │ │ Excel Export │ │ JSON Response │ │ Structured Log │ │ ║ ║ │ │ │ │ │ │ │ │ ║ ║ │ │ .xlsx file │ │ REST API │ │ JSONL rotating │ │ ║ ║ │ │ auto-fitted │ │ response │ │ files │ │ ║ ║ │ │ columns │ │ │ │ │ │ ║ ║ │ └──────────────┘ └──────────────┘ └──────────────────┘ │ ║ ║ │ │ ║ ║ └────────────────────────────────────────────────────────────────┘ ║ ║ ║ ╚═══════════════════════════════════════════════════════════════════════╝ ``` ### 2.2 Data Flow Diagram This diagram shows how data flows through the system from input to output: ``` ┌───────────┐ │ User's │ │ Question │ └─────┬─────┘ │ ▼ ┌─────────────────────────────────────────────────────────────────────┐ │ DATA FLOW PIPELINE │ │ │ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ │ │ │ │ │ │ │ │ │ │ │ │ Question│───►│Retriever│───►│ Prompt │───►│ LLM │ │ │ │ (NLQ) │ │Pipeline │ │ Builder │ │Backend │ │ │ │ │ │ │ │ │ │ │ │ │ └─────────┘ └────┬────┘ └─────────┘ └────┬────┘ │ │ │ │ │ │ ▼ ▼ │ │ ┌──────────────┐ ┌──────────────┐ │ │ │ Retrieval │ │ Raw SQL │ │ │ │ Context │ │ Text │ │ │ │ │ │ │ │ │ │ • entities │ │ May include: │ │ │ │ • facts │ │ • Markdown │ │ │ │ • joins │ │ • Prose │ │ │ │ • rules │ │ • LIMIT │ │ │ │ • examples │ └──────┬───────┘ │ │ │ • filters │ │ │ │ └──────────────┘ ▼ │ │ ┌──────────────┐ │ │ │ SQL Guard │ │ │ │ │ │ │ │ clean ──► │ │ │ │ validate ──► │ │ │ │ ensure_top │ │ │ └──────┬───────┘ │ │ │ │ │ ▼ │ │ ┌──────────────┐ │ │ │ Valid T-SQL │ │ │ │ Query │ │ │ └──────┬───────┘ │ │ │ │ │ ▼ │ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ │ │ │ │ │ │ │ │ │ │ │ │ Excel │◄───│ JSON │◄───│ DataFrame│◄───│ SQL │ │ │ │ Export │ │ Response│ │ Results │ │ Execute │ │ │ │ │ │ │ │ │ │ │ │ │ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │ │ │ │ │ ▼ │ │ ┌─────────┐ │ │ │ User │ │ │ │ Gets │ │ │ │ Results │ │ │ └─────────┘ │ └─────────────────────────────────────────────────────────────────────┘ ``` ### 2.3 Component Interaction Map This diagram shows which modules communicate with each other: ``` ┌──────────────┐ │ app.py │ │ (CLI) │ └──────┬───────┘ │ ▼ ┌──────────────┐ ┌──────────│ sql_agent │──────────┐ │ │ .py │ │ │ └──────┬───────┘ │ │ │ │ ▼ ▼ ▼ ┌───────────────┐ ┌──────────────┐ ┌───────────────────┐ │ context │ │ wizard_llm │ │ sql_guard.py │ │ _retriever.py│ │ .py │ │ │ └───────┬───────┘ └──────────────┘ └───────────────────┘ │ │ calls 6 sub-retrievers: │ ├──► entity_retriever.py ──► schema_data/retriever.py (TF-IDF) ├──► fact_retriever.py ──► schema_data/retriever.py (TF-IDF) ├──► relationship_retriever.py ──► schema_data/relationships.py ├──► rule_retriever.py ──► knowledge/business_rules.py ├──► example_retriever.py──► knowledge/examples.py └──► value_retriever.py ──► knowledge/aliases.py ┌──────────────┐ ┌──────────│ server.py │──────────┐ │ │ (FastAPI) │ │ │ └──────┬───────┘ │ │ │ │ ▼ ▼ ▼ ┌───────────────┐ ┌──────────────┐ ┌───────────────────┐ │ runner.py │ │ middleware │ │ query_cache.py │ │ (orchestr.) │ │ .py │ │ (LRU + TTL) │ └───────────────┘ └──────────────┘ └───────────────────┘ ``` --- ## 3. End-to-End Workflow — Step by Step This section walks through exactly what happens from the moment a user asks a question to the moment they receive results. ### Master Flow — All 12 Steps ``` ┌─────────────────────────────────────────────────────────────────┐ │ │ │ STEP 1 STEP 2 STEP 3 STEP 4 STEP 5 STEP 6 │ │ ┌───┐ ┌───┐ ┌───┐ ┌───┐ ┌───┐ ┌───┐ │ │ │ Q │────►│MW │────►│ C │────►│ R │────►│ P │────►│ L │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └───┘ └───┘ └───┘ └───┘ └───┘ └───┘ │ │ │ │ STEP 7 STEP 8 STEP 9 STEP 10 STEP 11 STEP 12 │ │ ┌───┐ ┌───┐ ┌───┐ ┌───┐ ┌───┐ ┌───┐ │ │ │ C │────►│ V │────►│ A │────►│ D │────►│ O │────►│ L │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └───┘ └───┘ └───┘ └───┘ └───┘ └───┘ │ │ │ │ Q = Question Input MW = Middleware │ │ C = Cache Lookup R = Retrieval Pipeline │ │ P = Prompt Assembly L = LLM Generation │ │ C = SQL Cleaning V = SQL Validation │ │ A = Auto-Correction D = Database Execution │ │ O = Output/Export L = Logging │ │ │ └─────────────────────────────────────────────────────────────────┘ ``` --- ### Step 1: User Submits a Question **Entry Point:** `app.py` (CLI) or `api/server.py` (HTTP API) ``` ┌─────────────────────────────────────────────────────────┐ │ │ │ CLI MODE: │ │ │ │ ❓ Question: برترین مشتریان از نظر ارزش خرید در 1402 │ │ │ │ (User types Persian or English question at terminal) │ │ │ ├─────────────────────────────────────────────────────────┤ │ │ │ HTTP MODE: │ │ │ │ POST http://localhost:8000/query │ │ { │ │ "question": "فروش ماهانه تالار پتروشیمی در 1402", │ │ "mode": "full" │ │ } │ │ │ │ (Client sends JSON request via REST API) │ │ │ └─────────────────────────────────────────────────────────┘ ``` Every HTTP route except `GET /health` requires `Authorization: Bearer `. --- ### Step 2: Middleware Processing (HTTP Mode Only) **Modules:** `api/middleware.py`, `api/auth.py`, `api/concurrency.py` Every request passes through a stack of middleware before a route runs. From the outermost layer in (`api/server.py` documents the order and why): | Layer | What it does | |---|---| | `SecurityHeadersMiddleware` | Outermost, so an error response carries the same security headers as a 200 | | `CORSMiddleware` | Answers a browser's preflight; the allowed origins are `CORS_ALLOWED_ORIGINS` (default `http://localhost:8080` and `http://127.0.0.1:8080`) | | `RequestIDMiddleware` | Assigns `X-Request-ID` (the same id the audit record carries) and adds `X-Response-Time` (seconds, e.g. `0.412s`) | | `AuthMiddleware` | Resolves the API key (`Authorization: Bearer `) to a principal, 401 otherwise; `GET /health` is the one open route | | `RateLimitMiddleware` | Token bucket per (principal, IP): `RATE_LIMIT_REQUESTS` (600) per `RATE_LIMIT_WINDOW_SEC` (60) with `RATE_LIMIT_BURST` (40) extra; 429 on excess. Failed authentications have their own small bucket | | `ConcurrencyMiddleware` | Innermost: at most `MAX_CONCURRENT_REQUESTS` (10) pipeline requests in flight (`/query`, `/query/stream`, v2 turns, the assumptions `PATCH`); 503 `SERVER_OVERLOAD` beyond that. The slot is held until the response body is fully sent | --- ### Step 3: Cache Lookup (HTTP Mode Only) **Module:** `api/query_cache.py` ``` Cache key = (normalised question, mode, prompt-prefix version, scope key) scope key = a hash of the caller's denied_columns (and of any pinned memory that changed the answer): two principals who may see the same data share entries, two who may not never do. mode "sql" and requests with interpret=true are not cached. Max size: CACHE_MAX_SIZE (256) entries TTL: CACHE_TTL_SECONDS (300; 0 = off) Eviction: LRU Thread safety: a lock around every operation ``` A hit returns the stored result immediately: no model call, no database statement, and no source routing, because a hit never routes anything (`docs/design/DATASOURCES.md`, "Result cache"). A miss continues to Step 4. --- ### Step 4: Retrieval Pipeline — Building Context **Module:** `retrieval/context_retriever.py` + 6 sub-retrievers This is the **most critical step**. The retrievers pick the **relevant subset** of the knowledge base for each question. Whether the prompt then shows that subset or, while the whole knowledge base fits `PROMPT_RETRIEVAL_TOKEN_BUDGET`, everything (Step 5), is decided when the prompt is built; the retrieval output also feeds the filters, the value resolution and, with several data sources, the choice of source. ``` ┌─────────────────────────────────────────────────────────────────┐ │ │ │ RETRIEVAL PIPELINE — 6 PARALLEL RETRIEVERS │ │ │ │ Question: "فروش ماهانه تالار پتروشیمی در 1402" │ │ (monthly sales of petrochemical hall in 1402) │ │ │ │ ┌──────────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ ┌─────────────────────┐ │ │ │ │ │ 4.1 ENTITY RETRIEVER│ │ │ │ │ │ ─────────────────── │ │ │ │ │ │ │ │ │ │ │ │ Input: question │ │ │ │ │ │ Output: dim tables │──► Ring, Customer │ │ │ │ │ │ │ │ │ │ │ Method: │ │ │ │ │ │ 1. Alias match │ │ │ │ │ │ 2. TF-IDF fallback │ │ │ │ │ └─────────────────────┘ │ │ │ │ │ │ │ │ ┌─────────────────────┐ │ │ │ │ │ 4.2 FACT RETRIEVER │ │ │ │ │ │ ────────────────── │ │ │ │ │ │ │ │ │ │ │ │ Input: question │ │ │ │ │ │ Output: fact tables │──► Order │ │ │ │ │ │ │ │ │ │ │ Method: │ │ │ │ │ │ 1. Alias match │ │ │ │ │ │ 2. TF-IDF fallback │ │ │ │ │ └─────────────────────┘ │ │ │ │ │ │ │ │ ┌─────────────────────┐ │ │ │ │ │ 4.3 RELATIONSHIP │ │ │ │ │ │ RETRIEVER │ │ │ │ │ │ ────────────────── │ │ │ │ │ │ │ │ │ │ │ │ Input: [Ring, │ │ │ │ │ │ Customer, Order] │ │ │ │ │ │ │ │ │ │ │ │ Output: JOIN clauses│ │ │ │ │ │ │ │ │ │ │ │ From: relationships.py │ │ │ │ └─────────────────────┘ │ │ │ │ │ │ │ │ ┌─────────────────────┐ │ │ │ │ │ 4.4 RULE RETRIEVER │ │ │ │ │ │ ────────────────── │ │ │ │ │ │ │ │ │ │ │ │ Input: question │ │ │ │ │ │ Output: biz rules │──► "Ring names use full form" │ │ │ │ │ │ "Persian year = Farvardin" │ │ │ │ │ From: business_rules.py │ │ │ │ └─────────────────────┘ │ │ │ │ │ │ │ │ ┌─────────────────────┐ │ │ │ │ │ 4.5 EXAMPLE │ │ │ │ │ │ RETRIEVER │ │ │ │ │ │ ────────────────── │ │ │ │ │ │ │ │ │ │ │ │ Input: question │ │ │ │ │ │ Output: 2-3 SQL │──► Similar NLQ→SQL pairs │ │ │ │ │ examples │ ranked by tag overlap │ │ │ │ │ │ │ │ │ │ │ From: examples.py │ │ │ │ │ │ (22+ annotated │ │ │ │ │ │ NLQ→SQL pairs) │ │ │ │ │ └─────────────────────┘ │ │ │ │ │ │ │ │ ┌─────────────────────┐ │ │ │ │ │ 4.6 VALUE RETRIEVER │ │ │ │ │ │ ────────────────── │ │ │ │ │ │ │ │ │ │ │ │ Input: question │ │ │ │ │ │ Output: filters │──► Ring: تالار پتروشیمی │ │ │ │ │ │ PersianYear: 1402 │ │ │ │ │ │ │ │ │ │ │ From: aliases.py │ │ │ │ │ └─────────────────────┘ │ │ │ │ │ │ │ └──────────────────────────────────────────────────────────┘ │ │ │ │ ALL 6 RESULTS COMBINED INTO: │ │ │ │ ┌──────────────────────────────────────────────────────────┐ │ │ │ RetrievalContext (frozen dataclass) │ │ │ │ │ │ │ │ .entities = ["Ring", "Customer"] │ │ │ │ .facts = ["Order"] │ │ │ │ .relationships = ["JOIN ... ON ...", "JOIN ... ON ..."] │ │ │ │ .business_rules= ["Ring names...", "Persian year..."] │ │ │ │ .examples = [{question, sql, tags}, ...] │ │ │ │ .filters = {Ring: "تالار پتروشیمی", Year: 1402} │ │ │ └──────────────────────────────────────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────┘ ``` #### Two-Tier Retrieval Strategy Each sub-retriever uses a **two-tier matching strategy**: ``` Question Text │ ▼ ┌──────────────────────────────────────────────────────────┐ │ │ │ TIER 1: Fast Alias/Pattern Match │ │ ───────────────────────────────── │ │ │ │ ┌────────────────────────────────────────────────┐ │ │ │ Check if question contains known aliases: │ │ │ │ │ │ │ │ "پتروشیمی" ──► Ring │ │ │ │ "خرید" ──► Order │ │ │ │ "سال 1402" ──► Date (PersianYear=1402) │ │ │ └────────────────────────────────────────────────┘ │ │ │ │ Speed: ~1ms | Accuracy: High for known terms │ │ │ │ ┌─────────┐ │ │ │ Match? │ │ │ └────┬────┘ │ │ │ │ │ YES │ NO │ │ ────┤ ──── │ │ │ │ │ │ │ ▼ │ ▼ │ │ Done │ ┌─────────────────────────────────────────┐ │ │ │ │ │ │ │ │ │ TIER 2: TF-IDF Bigram Scoring │ │ │ │ │ ─────────────────────────────── │ │ │ │ │ │ │ │ │ │ Score each table by: │ │ │ │ │ • Term frequency in description │ │ │ │ │ • IDF weighting (rare terms = higher) │ │ │ │ │ • Bigram matching (1.5x multiplier) │ │ │ │ │ • Synonym expansion │ │ │ │ │ │ │ │ │ │ Speed: ~10ms | Accuracy: Good │ │ │ │ │ │ │ │ │ │ Return top 6 ranked tables │ │ │ │ │ │ │ │ │ └─────────────────────────────────────────┘ │ │ │ └──────────────────────────────────────────────────────────┘ ``` --- ### Step 5: Prompt Assembly **Modules:** `prompt_engine/builder.py`, `prompt_engine/static_prefix.py`, `prompt_engine/templates.py` `PromptBuilder.build()` assembles one prompt string from a **static prefix** and a **variable suffix**. One gate decides how the prefix is made, `should_use_static_prefix`, which compares the prefix's token estimate (`len(text) // 4`) with `PROMPT_RETRIEVAL_TOKEN_BUDGET` (default 6000): ``` STATIC PATH (the prefix fits the budget — the default) ┌─────────────────────────────────────────────────────────────┐ │ system prompt /system_prompt.md │ │ BUSINESS RULES every rule (business_rules) │ │ METRICS every metric (metrics) │ │ DATABASE SCHEMA every table (schema.yaml) │ byte-identical for │ RELATIONSHIPS every relationship (schema.yaml) │ every request, built │ EXAMPLES every example (examples) │ once and cached, so └─────────────────────────────────────────────────────────────┘ the model server can ┌─────────────────────────────────────────────────────────────┐ reuse its KV cache │ DETECTED FILTERS ValueRetriever (Step 4.6) │ │ RESOLVED WAREHOUSE VALUES matched against the warehouse, │ the only part that │ fenced as data, not as orders │ changes per request │ SESSION CONTEXT the last turns of a session │ │ USER QUESTION the original input │ └─────────────────────────────────────────────────────────────┘ RETRIEVAL PATH (the prefix is over the budget) The same sections, built per question from only the tables, relationships, rules and examples the six retrievers selected (Step 4). ``` **With several data sources** (`project_config/datasources.yaml`) one source is chosen for the question first, with no model call (`retrieval/source_selector.py`: `keywords:`, then the conversation, then retrieval evidence, then the default), and the prompt describes that source only: its tables (those shared with other sources included), the relationships between them, and the examples whose SQL reads only those tables, under one `Data source: — ` line. The gate runs per source, so each source has its own cached prefix and its own budget decision; a source over the budget uses the retrieval path restricted to its tables. If the model answers `OUT_OF_SCOPE`, the request is retried once with the next candidate source (`llm/source_routing.py`). With one source none of this runs and the prompt is exactly what it was. See `docs/design/DATASOURCES.md`. **Why this matters:** an identical prefix lets a local model server skip prefill for everything but the short suffix, which is the latency win. Retrieval remains the escape hatch that keeps a schema too large for the context window usable. --- ### Step 6: LLM Generation **Modules:** `llm/router.py` → `llm/providers.py` (the CLI's one-shot path uses `llm/wizard_llm.py`) → OpenAI-compatible API ``` ┌─────────────────────────────────────────────────────────────────┐ │ │ │ LLM GENERATION PROCESS │ │ │ │ Assembled Prompt │ │ │ │ │ ▼ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ POST /chat/completions │ │ │ │ │ │ │ │ { │ │ │ │ "model": "gpt-oss-20:F16", │ │ │ │ "messages": [{"role": "user", │ │ │ │ "content": ""}], │ │ │ │ "stream": false │ │ │ │ } │ │ │ │ │ │ │ └────────────────────────┬───────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ RETRY LOGIC (3 attempts, exponential backoff): │ │ │ │ │ │ │ │ Attempt 1 ──► Wait 1s ──► Attempt 2 ──► Wait 2s │ │ │ │ │ │ │ │ │ │ │ │ Fail │ Fail │ │ │ │ │ ▼ ▼ ▼ │ │ │ │ ┌────────┐ ┌────────┐ ┌────────┐ │ │ │ │ │ Retry │ │ Retry │ │ FAIL │ │ │ │ │ └────────┘ └────────┘ └────────┘ │ │ │ │ │ │ │ └────────────────────────┬───────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ RESPONSE HANDLING: │ │ │ │ │ │ │ │ ┌─────────────────────┐ ┌──────────────────────┐ │ │ │ │ │ Response: │ │ Response: │ │ │ │ │ │ "OUT_OF_SCOPE" │ │ Raw SQL text │ │ │ │ │ │ │ │ │ │ │ │ │ │ → Reject question │ │ → Proceed to Step 7 │ │ │ │ │ │ (not auction- │ │ │ │ │ │ │ │ related) │ │ May include: │ │ │ │ │ └─────────────────────┘ │ • Markdown fences │ │ │ │ │ │ • Prose preamble │ │ │ │ │ ┌─────────────────────┐ │ • LIMIT clause │ │ │ │ │ │ Response: EMPTY │ └──────────────────────┘ │ │ │ │ │ │ │ │ │ │ │ → Raise error │ │ │ │ │ └─────────────────────┘ │ │ │ │ │ │ │ └────────────────────────────────────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────┘ ``` --- ### Step 7: SQL Cleaning **Module:** `security/sql_guard.py` → `clean_sql()` function ``` Raw LLM Output │ ▼ ┌─────────────────────────────────────────────────────────────────┐ │ │ │ SQL CLEANING PIPELINE │ │ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ INPUT: │ │ │ │ ```sql │ │ │ │ SELECT TOP 100 c.Name, SUM(cc.TotalPrice) AS Value │ │ │ │ FROM [sales].[Order] c │ │ │ │ JOIN [sales].[Customer] cc ON c.CustID = cc.ID │ │ │ │ LIMIT 100 │ │ │ │ ``` │ │ │ └─────────────────────┬───────────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ STEP 7.1: Extract from markdown fences │ │ │ │ │ │ │ │ ```sql ... ``` ──► SELECT TOP 100 c.Name ... │ │ │ │ │ │ │ └─────────────────────┬───────────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ STEP 7.2: Remove prose preamble │ │ │ │ │ │ │ │ "Here is the query: SELECT ..." ──► SELECT ... │ │ │ │ │ │ │ └─────────────────────┬───────────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ STEP 7.3: Convert LIMIT to TOP │ │ │ │ │ │ │ │ SELECT * FROM T LIMIT 5 │ │ │ │ │ │ │ │ │ ▼ │ │ │ │ SELECT TOP 5 * FROM T │ │ │ │ │ │ │ │ (If TOP already exists, just strip LIMIT) │ │ │ │ │ │ │ └─────────────────────┬───────────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ STEP 7.4: Fix TOP DISTINCT order │ │ │ │ │ │ │ │ SELECT TOP 10 DISTINCT ... │ │ │ │ │ │ │ │ │ ▼ │ │ │ │ SELECT DISTINCT TOP 10 ... │ │ │ │ │ │ │ └─────────────────────┬───────────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ OUTPUT: │ │ │ │ SELECT TOP 100 c.Name, SUM(cc.TotalPrice) AS Value │ │ │ │ FROM [sales].[Order] c │ │ │ │ JOIN [sales].[Customer] cc ON c.CustID = cc.ID │ │ │ │ │ │ │ └─────────────────────────────────────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────┘ ``` --- ### Step 8: SQL Validation (Security Check) **Module:** `security/sql_guard.py` → `validate_sql()` function `validate_sql` is **parser-based**: it parses the statement with sqlglot and decides from the syntax tree, not from keywords in the text (`tests/test_sql_guard_bypass.py` holds the bypasses and false positives this replaced). A statement is accepted only if all of these hold; each refusal carries a `reason` that clients and the audit trail use (`docs/api-contract-v2.md` §4): ``` Cleaned SQL │ ▼ 1. exactly one statement (stacked statements refused as a class) 2. a SELECT/WITH root, or a top-level UNION / INTERSECT / EXCEPT DDL, DML, EXEC, SELECT ... INTO, xp_* / sp_* / OPENROWSET ... refused wherever they appear in the tree 3. no comments (refused because present) 4. no system catalogue (INFORMATION_SCHEMA, sys.*, ...) 5. every table is on the allowlist derived from schema.yaml (a table with a `columns` map), or a CTE of this query; a schema written in front of a name must match the table's known schema → unknown_table, a bare name that two schemas share is refused → ambiguous_table 6. every qualified column exists on its table; denied_columns refused (also through * ) → denied_column scoped entries (schema.Table.Col, Source:...): the column may only be a `a.col = b.col` key in a JOIN ON → join_only_column 7. function calls: only allowlisted data functions; anything that reads server or session state is refused 8. at least one non-CTE table → no_table_reference 9. one data source can run it: some configured source has EVERY table the statement reads (database.routing.choose_datasource, the same function the executor uses) → cross_datasource │ ▼ accepted → ensure_top() → execute ``` `LIMIT` is not on the refusal list: `clean_sql` (Step 7) has already rewritten it to `TOP n`. --- ### Step 9: Self-Correction Loop (if needed) **Module:** `llm/sql_agent.py` ``` ┌─────────────────────────────────────────────────────────────────┐ │ │ │ SELF-CORRECTION LOOP │ │ │ │ ┌───────────────────────────────────────────────────────┐ │ │ │ ATTEMPT 1 (Initial Generation) │ │ │ │ │ │ │ │ question ──► retrieval ──► prompt ──► LLM │ │ │ │ │ │ │ │ │ ▼ │ │ │ │ clean ──► validate│ │ │ │ │ │ │ │ │ ▼ │ │ │ │ execute │ │ │ │ │ │ │ │ │ ┌───────┴───────┐ │ │ │ │ │ │ │ │ │ │ SUCCESS ERROR │ │ │ │ │ │ │ │ │ │ ▼ │ │ │ │ │ Return Result │ │ │ │ │ │ │ │ │ └───────────────────────────────────────────────────┼──┘ │ │ │ │ │ ▼ │ │ ┌───────────────────────────────────────────────────────┐ │ │ │ ATTEMPT 2 (Correction Round 1) │ │ │ │ │ │ │ │ initial_prompt + error_message │ │ │ │ │ │ │ │ │ ▼ │ │ │ │ LLM ──► clean ──► validate ──► execute │ │ │ │ │ │ │ │ │ ┌───────┴───────┐ │ │ │ │ │ │ │ │ │ │ SUCCESS ERROR │ │ │ │ │ │ │ │ │ │ ▼ │ │ │ │ │ Return Result │ │ │ │ │ │ │ │ │ └────────────────────────────────────────────┼────────┘ │ │ │ │ │ ▼ │ │ ┌───────────────────────────────────────────────────────┐ │ │ │ ATTEMPT 3 (Correction Round 2 — Final) │ │ │ │ │ │ │ │ initial_prompt + error_message │ │ │ │ │ │ │ │ │ ▼ │ │ │ │ LLM ──► clean ──► validate ──► execute │ │ │ │ │ │ │ │ │ ┌───────┴───────┐ │ │ │ │ │ │ │ │ │ │ SUCCESS ERROR │ │ │ │ │ │ │ │ │ │ ▼ ▼ │ │ │ │ Return Result RAISE FINAL │ │ │ │ ERROR TO │ │ │ │ CALLER │ │ │ └───────────────────────────────────────────────────────┘ │ │ │ │ MAX_CORRECTION_ATTEMPTS = 2 (configurable) │ │ │ └─────────────────────────────────────────────────────────────────┘ ``` A rejection that no rewrite could satisfy (a policy refusal such as a denied column, or `cross_datasource`) is not re-prompted, and a response cut short by `LLM_NUM_PREDICT` before any SQL is not retried either. With several data sources, the one retry on the next source after an `OUT_OF_SCOPE` answer is outside this correction budget. --- ### Step 10: Database Execution **Module:** `database/connection.py` + `database/executor.py` ``` Validated SQL Query │ ▼ ┌─────────────────────────────────────────────────────────────────┐ │ │ │ DATABASE EXECUTION │ │ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ SQLALCHEMY ENGINE │ │ │ │ │ │ │ │ ┌─────────────────────────────────────────────────┐ │ │ │ │ │ Configuration: │ │ │ │ │ │ │ │ │ │ │ │ pool_size = 10 (persistent connections) │ │ │ │ │ │ max_overflow = 20 (burst connections) │ │ │ │ │ │ pool_recycle = 3600s (recycle every hour) │ │ │ │ │ │ idle-aware ping (DB_POOL_PING_IDLE_SECONDS) │ │ │ │ │ │ (SELECT-only: no fast_executemany) │ │ │ │ │ │ │ │ │ │ │ └─────────────────────────────────────────────────┘ │ │ │ │ │ │ │ └─────────────────────────┬───────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ EXECUTION STEPS: │ │ │ │ │ │ │ │ 1. SET LOCK_TIMEOUT {timeout_ms} │ │ │ │ └─ Prevents indefinite blocking │ │ │ │ │ │ │ │ 2. Execute SQL query │ │ │ │ └─ Against SQL Server via ODBC 17 or 18 │ │ │ │ │ │ │ │ 3. fetchmany(MAX_ROWS_RETURNED) │ │ │ │ └─ Hard row cap: 1000 rows max │ │ │ │ └─ Enforced even if TOP clause is present │ │ │ │ │ │ │ │ 4. Return as pandas DataFrame │ │ │ │ └─ Column names from cursor description │ │ │ │ │ │ │ └─────────────────────────┬───────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ Result: pandas.DataFrame │ │ │ │ │ │ │ │ ┌─────────────────────────────────────────────────┐ │ │ │ │ │ Name │ Month │ TradeValue │ │ │ │ │ │──────────────│─────────│────────────────────────│ │ │ │ │ │ PetroCo │ فروردین │ 48,320,000,000 │ │ │ │ │ │ PetroCo │ اردیبهشت│ 39,210,000,000 │ │ │ │ │ │ ... │ ... │ ... │ │ │ │ │ └─────────────────────────────────────────────────┘ │ │ │ │ │ │ │ └─────────────────────────────────────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────┘ ``` One SQLAlchemy engine per configured data source is cached for the life of the process (`database.connection.get_engine(datasource=...)`); a deployment with no `datasources.yaml` has one, exactly as pictured. Which engine a statement uses is derived from its tables, never chosen by the model: the source that has every table it reads (the default source if it qualifies). With `nolock: true` on that source, the executor inserts ` WITH (NOLOCK)` after each table reference just before sending the text. Routing, the shared-table rule and `NOLOCK` are in `docs/design/DATASOURCES.md`; the operator's procedure is `docs/deployment-runbook.md` §16. --- ### Step 11: Result Processing and Output ``` pandas DataFrame │ ├──────────────────────────────────────────────────────────┐ │ │ ▼ ▼ ┌─────────────────────┐ ┌─────────────────────┐ │ CLI MODE │ │ HTTP MODE │ │ (app.py) │ │ (api/runner.py) │ ├─────────────────────┤ ├─────────────────────┤ │ │ │ │ │ 1. Display in │ │ 1. Serialize to │ │ terminal │ │ JSON response │ │ (up to 20 rows) │ │ │ │ │ │ 2. Include: │ │ 2. Export to │ │ • question │ │ Excel file │ │ • sql (if full) │ │ auto-fitted │ │ • result rows │ │ columns │ │ • row_count │ │ │ │ • status │ │ 3. Log structured │ │ │ │ JSON entry │ │ 3. If interpret: │ │ │ │ LLM generates │ │ │ │ plain summary │ │ │ │ │ │ │ │ 4. Cache result │ │ │ │ for future use │ │ │ │ │ └─────────────────────┘ └─────────────────────┘ ``` --- ### Step 12: Logging **Modules:** `logs/logger.py` + `logs/query_log.py` (the CLI), `observability/audit.py` (the HTTP API) ``` CLI — one line per question in logs/query_log.jsonl: { "timestamp": "2026-06-27T14:22:57", "question": "top 5 customers by purchase value in 2024", "generated_sql": "SELECT TOP 5 c.Name ...", "model_name": "openai:gpt-oss-20:F16", "status": "SUCCESS", "row_count": 5, "execution_time_seconds": 3.456, "error_message": null, "excel_file": "exports/result_20260627_142257.xlsx" } HTTP API — one record per request in logs/audit_log.jsonl: request and session ids, the principal, the question's tier, the guard verdict and tables touched, the LLM status block (tokens, prefix-cache hit, finish reason), per-stage timings, the configuration version, `datasource` (where the SQL ran) and, with several data sources, `datasource_selection` (which source the model was shown and why). Never result rows. ``` Both files rotate by size (`LOG_MAX_BYTES`, `LOG_BACKUP_COUNT`). `scripts/analyze_audit_log.py` aggregates the audit log without question text or SQL (`docs/deployment-runbook.md` §8). --- ## 4. Module Breakdown ### 4.1 Directory Structure The README's "Project structure" section is the maintained file-by-file tree. By layer: | Directory | Role | |---|---| | `app.py` | CLI entry point (interactive REPL) | | `config.py` | Typed `Settings` singleton, read from the environment | | `api/` | FastAPI service: `server.py` (app and routes), `runner.py` (cache-aware orchestrator), `v2_routes.py` (conversations), `admin_*.py` (admin panel API), middleware, auth, health, errors | | `session/` | Conversational engine: `TurnEngine`, refinement, CTE composition, declared assumptions, persistence | | `retrieval/` | The six retrievers, `context_retriever.py` (orchestrator), value resolution, the dimension vocabulary, `source_selector.py` (which data source) | | `prompt_engine/` | `builder.py`, `static_prefix.py` (one cacheable prefix per data source), `source_scope.py`, `templates.py` | | `llm/` | `router.py` (task routing and fallback), `providers.py` (OpenAI-compatible client with retries), `sql_agent.py` (generate, clean, validate, correct), `source_routing.py` (per-question source and the single `OUT_OF_SCOPE` retry), `wizard_llm.py` (the CLI's one-shot path) | | `security/` | `sql_guard.py` (clean, validate, cap, transpile, `pretty_sql`), `sql_format.py` (house layout of displayed SQL), `dialects.py`, `auth.py` | | `database/` | `connection.py` (one engine per data source), `datasources.py` (`datasources.yaml`), `routing.py`, `executor.py`, `table_hints.py` (`WITH (NOLOCK)`), `catalogue.py` | | `schema_data/`, `knowledge/` | Loaders and validation for `project_config/*.yaml` (`schema_data/registry.py` is the schema allowlist; `drift.py` compares it with the live catalogues) | | `appdb/` | Application database: API keys, role grants, config versions, feedback, access requests | | `observability/` | Audit records, the LLM status block, stage timings | | `exporters/`, `logs/` | Excel / CSV / JSON exports; rotating JSONL logger | | `core/` | Shared models, the Persian normaliser, strict YAML loading, the start-up notice | | `scripts/` | Operator tools: `verify_deployment.py`, `issue_api_key.py`, `sync_schema.py`, `draft_descriptions.py`, `draft_policies.py`, `assign_datasources.py`, `prompt_budget.py`, `analyze_audit_log.py`, `analyze_misses.py`, `migrate_app_db.py` | | `web/` | Static Persian/RTL client and the admin panel (no build step) | | `project_config/` | Deployment-specific domain data, git-ignored (template: `project_config.example/`) | ### 4.2 Module Dependency Map ``` app.py ──► llm/wizard_llm.py ──► retrieval ──► prompt_engine ──► llm/providers.py ──► model endpoint │ │ │ └────────► security/sql_guard.py └──► database/executor.py ──► database/routing.py ──► database/connection.py ──► SQL Server (source per statement) (engine per data source) api/server.py ──► api/runner.py ──► llm/sql_agent.py ──► llm/router.py ──► llm/providers.py │ │ │ │ │ ├──► retrieval (incl. source_selector) ──► prompt_engine │ │ ├──► security/sql_guard.py │ │ └──► database/executor.py ──► ... (as above) │ └──► api/query_cache.py ├──► api/v2_routes.py ──► session/engine.py ──► (the same stages) └──► api/middleware.py, api/auth.py ``` --- ## 5. API Endpoints ### 5.1 Endpoint Overview ``` ┌─────────────────────────────────────────────────────────────────┐ │ │ │ API ENDPOINT MAP │ │ │ │ ┌──────────────┐ ┌──────────────────────────────────┐ │ │ │ POST │ │ /query │ │ │ │ /query │───►│ Translate question to SQL │ │ │ │ │ │ and/or execute it │ │ │ └──────────────┘ └──────────────────────────────────┘ │ │ │ │ ┌──────────────┐ ┌──────────────────────────────────┐ │ │ │ GET │ │ /health │ │ │ │ /health │───►│ Check DB + LLM endpoint │ │ │ │ │ │ reachability │ │ │ └──────────────┘ └──────────────────────────────────┘ │ │ │ │ ┌──────────────┐ ┌──────────────────────────────────┐ │ │ │ GET │ │ /cache/stats │ │ │ │ /cache/stats│───►│ Return cache metrics │ │ │ └──────────────┘ └──────────────────────────────────┘ │ │ │ │ ┌──────────────┐ ┌──────────────────────────────────┐ │ │ │ POST │ │ /cache/invalidate │ │ │ │ /cache/ │───►│ Evict specific cache entry │ │ │ │ invalidate │ └──────────────────────────────────┘ │ │ └──────────────┘ │ │ │ │ ┌──────────────┐ ┌──────────────────────────────────┐ │ │ │ POST │ │ /cache/clear │ │ │ │ /cache/clear│───►│ Flush entire cache │ │ │ └──────────────┘ └──────────────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────┘ ``` The map shows the original routes. Since then the service gained `POST /query/stream` (the same request as Server-Sent Events), the conversational routes under `/v2/` (sessions, turns, memory, feedback, access requests; `docs/api-contract-v2.md`) and the admin panel's `/admin/` routes (`docs/admin-panel-architecture.md`); the README's "API endpoints" table lists them. Every route except `GET /health` requires `Authorization: Bearer `. ### 5.2 POST /query — Request Modes ``` ┌─────────────────────────────────────────────────────────────────┐ │ │ │ QUERY MODES │ │ │ │ ┌───────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ mode = "sql" │ │ │ │ │ │ │ │ ┌─────────┐ ┌─────────┐ │ │ │ │ │ Question│───►│ LLM │───► SQL only │ │ │ │ │ │ │ │ (no execution) │ │ │ │ └─────────┘ └─────────┘ │ │ │ │ │ │ │ │ Use case: Preview the generated SQL │ │ │ │ Cache: SKIPPED (freshness matters) │ │ │ │ │ │ │ └───────────────────────────────────────────────────────┘ │ │ │ │ ┌───────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ mode = "result" │ │ │ │ │ │ │ │ ┌─────────┐ ┌─────────┐ ┌──────────┐ │ │ │ │ │ Question│───►│ LLM │───►│ Execute │──► Data │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─────────┘ └─────────┘ └──────────┘ │ │ │ │ │ │ │ │ Use case: Get data without seeing SQL │ │ │ │ Cache: USED │ │ │ │ │ │ │ └───────────────────────────────────────────────────────┘ │ │ │ │ ┌───────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ mode = "full" │ │ │ │ │ │ │ │ ┌─────────┐ ┌─────────┐ ┌──────────┐ │ │ │ │ │ Question│───►│ LLM │───►│ Execute │──► Data │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─────────┘ └─────────┘ └──────────┘ │ │ │ │ │ │ │ │ Use case: Full transparency (SQL + results) │ │ │ │ Cache: USED │ │ │ │ │ │ │ └───────────────────────────────────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────┘ ``` --- ## 6. Security Model Every generated SQL query passes through a **multi-layer security pipeline** before execution: ``` ┌─────────────────────────────────────────────────────────────────┐ │ │ │ SECURITY PIPELINE │ │ │ │ Raw LLM Output │ │ │ │ │ ▼ │ │ LAYER 1: clean_sql() │ │ • Extract from markdown fences, drop prose preamble │ │ • Convert LIMIT → TOP, fix TOP DISTINCT order │ │ │ │ │ ▼ │ │ LAYER 2: validate_sql() (parser-based, Step 8) │ │ • exactly one SELECT/WITH statement │ │ • table and column allowlist from schema.yaml, schema │ │ qualifier checked, column ACL (denied_columns) │ │ • no comments, no system catalogues, allowlisted functions │ │ • one data source can run it (cross_datasource otherwise) │ │ │ │ │ ▼ │ │ LAYER 3: ensure_top() │ │ • inject TOP DEFAULT_TOP_N when there is no row limit │ │ │ │ │ ▼ │ │ LAYER 4: execute_sql() │ │ • runs on the source that has every table │ │ • SET LOCK_TIMEOUT, driver timeout, always rolled back │ │ • fetchmany(MAX_ROWS_RETURNED): the hard row cap │ │ │ │ │ ▼ │ │ Result set │ │ │ └─────────────────────────────────────────────────────────────────┘ ``` ### Security Guarantees Summary - Only read-only `SELECT` statements over allowlisted tables and columns are executed; DDL, DML and procedure calls are refused by syntax-tree node type. - A principal's `denied_columns` are enforced in the guard, not only partitioned in the cache. - Row limits are enforced at SQL level (`TOP`) and again in the application (`fetchmany`). - Every route except `GET /health` requires an API key; keys are stored only as SHA-256 digests. - No credentials in code or in versioned config: secrets come from the environment (`datasources.yaml` names only the variable that holds a password). - Defence in depth belongs on the server as well: `docs/db-hardening.md` specifies the read-only login, `DENY` grants and Resource Governor group for each warehouse server. - With a local model endpoint no question, schema or row leaves the network; a remote endpoint is refused unless `LLM_ALLOW_REMOTE` is true. The README's "Security model" and "Authentication" sections are the full list. --- ## 7. Configuration All configuration is read from **environment variables** (or a `.env` file); `.env.example` documents every one, and `config.py` carries the reasoning behind each default. The ones most deployments touch: | Variable | Default | Description | |---|---|---| | `OPENAI_BASE_URL` | `https://api.openai.com/v1` | OpenAI-compatible endpoint (set it to your own for on-premise use) | | `OPENAI_MODEL` | `gpt-4o-mini` | Model name served by the endpoint | | `OPENAI_API_KEY` | *(empty)* | Endpoint credential; a local server usually needs none | | `DB_CONNECTION_URL` | *(required without `datasources.yaml`)* | SQLAlchemy connection string, without the password | | `DB_PASSWORD` | *(empty)* | The raw password for `DB_CONNECTION_URL` | | `QUERY_TIMEOUT_SECONDS` | `60` | Max query time (seconds) | | `MAX_ROWS_RETURNED` | `1000` | Hard row cap | | `PROMPT_RETRIEVAL_TOKEN_BUDGET` | `6000` | Static prefix up to this estimate, retrieval path above it; per data source | | `LLM_PREFIX_WARMUP_ON_STARTUP` / `LLM_PREFIX_WARMUP_TIMEOUT_SECONDS` | `true` / `180` | Background start-up request per data source that primes the model server's prefix cache (`llm/warmup.py`); `POST /admin/llm/warmup` on demand | | `LLM_PREFIX_KEEPWARM_SECONDS` | `0` | Seconds between keep-warm passes that repeat the warm-up (`llm/warmup.py`, `KeepWarm`); a source a real request used within the interval is skipped (`llm/prefix_activity.py`); `0` = off, else at least `30` | | `LLM_CACHE_SUSPECT_MS` | `50` | Reply time under which byte-identical SQL for a different question marks the request `suspected_cached_response` in the audit `llm` block and logs one warning per streak (`observability/response_cache_watch.py`, called from `api/runner.py`); `0` = off | | `LLM_STREAM_TIMINGS` | `false` | Stream the call to record `ttft_ms` / `generation_ms` in the audit `llm` block (`llm/providers.py`) | | `CACHE_TTL_SECONDS` / `CACHE_MAX_SIZE` | `300` / `256` | Query cache (`0` TTL = disabled) | | `API_KEYS_JSON` / `API_KEYS_FILE` | *(empty)* | The API key array, inline or in a file; set one | | `AUTH_REQUIRED` | `true` | Fail-closed authentication | | `LOG_DIR` / `EXPORT_DIR` | `logs` / `exports` | Log and export directories | `DB_CONNECTION_URL` is what a deployment with exactly **one** warehouse connection sets, and the password goes in `DB_PASSWORD`, raw, so nothing is percent-encoded by hand (`database.datasources.apply_db_password`). A deployment that queries more than one database instead adds `project_config/datasources.yaml`, whose sources describe the connection (host, port, database, driver, login, options, or `trusted_connection: true`) while `.env` holds only the raw password, one variable per source, named by `password_env`; `DB_CONNECTION_URL` and `DB_PASSWORD` are then unused. The design is `docs/design/DATASOURCES.md` and the ordered procedure (assigning each table's `datasource:`, keywords, the token budget, `nolock`) is `docs/deployment-runbook.md` §16. --- ## 8. How to Run ### 8.1 Prerequisites ``` ┌─────────────────────────────────────────────────────────────────┐ │ │ │ PREREQUISITES │ │ │ │ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐ │ │ │ │ │ │ │ │ │ │ │ Python │ │ OpenAI- │ │ SQL Server │ │ │ │ 3.11+ │ │ compatible │ │ + ODBC │ │ │ │ │ │ LLM endpoint │ │ Driver 17 │ │ │ │ │ │ (vLLM/LM │ │ │ │ │ │ │ │ Studio/…) │ │ │ │ │ └───────────────┘ └───────────────┘ └───────────────┘ │ │ │ │ │ │ │ └────────────────────┼────────────────────┘ │ │ │ │ │ ▼ │ │ ┌─────────────────────┐ │ │ │ Set in .env: │ │ │ │ OPENAI_BASE_URL, │ │ │ │ OPENAI_MODEL, │ │ │ │ OPENAI_API_KEY │ │ │ └─────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────┘ ``` ### 8.2 CLI Mode — Quick Start ```bash # Step 1: Install dependencies pip install -r requirements.lock # Step 2: Configure environment cp .env.example .env # Edit .env with your database URL, OPENAI_BASE_URL / OPENAI_MODEL / OPENAI_API_KEY # Step 3: Run the interactive CLI python app.py ``` ### 8.3 HTTP API Mode — Quick Start ```bash # Start the FastAPI server uvicorn api.server:app --host 0.0.0.0 --port 8000 --no-server-header # Send a query via HTTP curl -X POST http://localhost:8000/query \ -H 'Authorization: Bearer ' \ -H 'Content-Type: application/json' \ -d '{"question": "فروش ماهانه تالار پتروشیمی در 1402", "mode": "full"}' # the key comes from: python -m scripts.issue_api_key --id analyst-1 --name "Jane Analyst" # (docs/deployment-runbook.md §1-§2); a real deployment also runs # python -m scripts.verify_deployment first (§3) ``` --- ## 9. Testing ```bash # Run all tests pytest tests/ -v # Run a specific test module pytest tests/test_sql_guard.py -v # Run with coverage report pytest --cov=. --cov-report=html ``` **Test Categories:** ``` ┌─────────────────────────────────────────────────────────────────┐ │ │ │ TEST SUITE (5,000+ tests) │ │ │ │ ┌───────────────────┐ ┌───────────────────┐ │ │ │ Unit Tests │ │ Integration │ │ │ │ │ │ Tests │ │ │ │ • retriever │ │ │ │ │ │ • sql_guard │ │ • Full pipeline │ │ │ │ • executor │ │ • API endpoints │ │ │ │ • cache │ │ • DB connection │ │ │ │ • middleware │ │ │ │ │ └───────────────────┘ └───────────────────┘ │ │ │ │ ┌───────────────────┐ ┌───────────────────┐ │ │ │ Stress Tests │ │ Cache Tests │ │ │ │ │ │ │ │ │ │ • Concurrent │ │ • TTL expiry │ │ │ │ requests │ │ • LRU eviction │ │ │ │ • Rate limiting │ │ • Thread safety │ │ │ │ • Error recovery │ │ • Invalidation │ │ │ └───────────────────┘ └───────────────────┘ │ │ │ │ CI: GitHub Actions (3 OSes, Python 3.11-3.13) │ │ │ └─────────────────────────────────────────────────────────────────┘ ``` --- *This document describes the Local SQL Agent system as of release 6.6.1 (October 2026). Where it and the code disagree, the code wins; `README.md`, `docs/deployment-runbook.md` and `docs/design/DATASOURCES.md` are the maintained references.*