Architecture Overview

System Diagram — POC (Current)

The diagram below shows the current POC architecture backed by SQLite and local ChromaDB.

flowchart TD
    Client(["🌐 Client\n(curl / browser / app)"])

    subgraph API["⚡ FastAPI Application (main.py)"]
        Router_Chat["api/v1/chat.py\n(Chat Router)"]
        Router_Train["api/v1/training.py\n(Training Router)"]
        DI["core/dependencies.py\n(Dependency Injection)"]
        Service["services/vanna_service.py\n(VannaService — Singleton)"]
    end

    ChromaDB[("🟣 ChromaDB\nVector Store\nchroma.sqlite3")]
    OpenAI["☁️ OpenAI\ngpt-4o + ada-002"]
    SQLite[("📦 SQLite\ndbn-poc-database.db")]

    Client -->|"POST /chat/ask\nPOST /chat/generate-sql\nPOST /chat/run-sql"| Router_Chat
    Client -->|"GET/POST/DELETE /training/*"| Router_Train
    Router_Chat --> DI
    Router_Train --> DI
    DI -->|"singleton"| Service
    Service -->|"embed + similarity_search(question)"| ChromaDB
    ChromaDB -->|"top-10 DDL + SQL context"| Service
    Service -->|"generate_sql(prompt + context)"| OpenAI
    OpenAI -->|"SQL string"| Service
    Service -->|"run_sql(sql)"| SQLite
    SQLite -->|"pandas DataFrame"| Service
    Service -->|"AskResponse / TrainResponse"| Client

Production target: SQLite → Azure SQL Database (MS SQL Server), local ChromaDB → Azure-hosted ChromaDB or Azure AI Search. See DBN Production Setup.

---

Project Structure

dbn-analytics-poc/
│
├── api/
│   └── v1/
│       ├── chat.py         # POST /chat/ask · /generate-sql · /run-sql
│       └── training.py     # GET/POST/DELETE /training/*
│
├── core/
│   ├── config.py           # Pydantic Settings — env var management
│   └── dependencies.py     # FastAPI DI providers (get_vanna singleton)
│
├── docs/                   # MkDocs Material documentation site
│   ├── api/                # API reference pages
│   ├── architecture/       # Architecture & DBN production docs
│   ├── assets/             # CSS, logo, images
│   ├── deployment/         # Docker · Fly.io · Azure guides
│   ├── getting-started/    # Installation · Config · Quickstart
│   ├── training/           # RAG training lifecycle docs
│   └── index.md            # Home page
│
├── models/
│   └── schemas.py          # All Pydantic v2 request/response models
│
├── services/
│   └── vanna_service.py    # Vanna singleton: inference + training methods
│
├── scripts/                # Utility scripts (seed training, audit, retrain)
│
├── dbn-poc-database.db     # SQLite database (msmeloans table)
├── chroma.sqlite3          # ChromaDB persistent vector index
│
├── main.py                 # FastAPI app factory, CORS middleware, router mounts
├── Dockerfile              # Production container definition
├── fly.toml                # Fly.io deployment config
├── mkdocs.yml              # Documentation site config
├── requirements.txt        # Python package dependencies
└── README.md               # Repository entry-point

---

Design Principles

Principle	Implementation	Benefit
Separation of Concerns	Routes, services, schemas in distinct modules	Independently testable and replaceable
Singleton Services	VannaService initialised once via DI	Single DB + ChromaDB connection per process
Validation-First	Pydantic v2 on all inputs and outputs	Clear error messages, zero manual parsing
Fail-Safe Charting	Plotly generation errors caught silently	Data always returned even if chart fails
Pluggable Database	vn.run_sql is overridable	Easy swap from SQLite → MS SQL Server
Immutable Config	Pydantic BaseSettings loaded at startup	No runtime env mutations

---

API Surface Summary

Tag	Method	Path	Description
chat	POST	/api/v1/chat/ask	Full RAG: NL → SQL → Data → Plotly
chat	POST	/api/v1/chat/generate-sql	NL → SQL only (no execution)
chat	POST	/api/v1/chat/run-sql	Execute raw SQL → Data
training	GET	/api/v1/training/data	List all ChromaDB training records
training	POST	/api/v1/training/sql	Add question–SQL pair
training	POST	/api/v1/training/ddl	Add DDL statement
training	POST	/api/v1/training/documentation	Add business doc string
training	POST	/api/v1/training/from-database	Auto-ingest schema DDL
training	DELETE	/api/v1/training/{id}	Remove a training record
health	GET	/health	Liveness probe

---

Request Lifecycle — /chat/ask

sequenceDiagram
    autonumber
    participant C as Client
    participant F as FastAPI Router
    participant P as Pydantic (AskRequest)
    participant V as VannaService
    participant CH as ChromaDB
    participant OA as OpenAI GPT-4o
    participant DB as Database (SQLite / Azure SQL)

    C->>F: POST /api/v1/chat/ask {"question": "..."}
    F->>P: Validate request body
    P-->>F: AskRequest(question="...")
    F->>V: get_vanna() singleton
    V->>CH: similarity_search(question, n=10)
    CH-->>V: [DDL schema, SQL examples, docs]
    V->>OA: generate_sql(system_prompt + context + question)
    OA-->>V: "SELECT Sector, AVG(...) FROM msmeloans GROUP BY ..."
    V->>DB: run_sql(sql) → pd.read_sql_query(...)
    DB-->>V: pandas DataFrame (N rows × M cols)
    V->>OA: generate_plotly_code(question, sql, df)
    OA-->>V: "fig = px.bar(df, x='Sector', ...)"
    V-->>F: AskResponse(sql, data, plotly_code)
    F-->>C: 200 OK {"sql": "...", "data": [...], "plotly_code": "..."}