# ramachandramurthy/test-dynamic-sql architecture

> Architecture diagram of the GitHub repository ramachandramurthy/test-dynamic-sql, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-03-08). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/ramachandramurthy/test-dynamic-sql
- Repository: https://github.com/ramachandramurthy/test-dynamic-sql

## Overview

##### What this project is (type + purpose)
This is a **small full-stack “chat-to-data” application**:

- **Frontend**: a browser UI (Vanilla JS/HTML/CSS) that presents a chat interface + schema explorer + raw data viewer.
- **Backend**: a **FastAPI** app that orchestrates user requests, calls an LLM, and returns results to the UI.
- **AI integration**: **Google Gemini** is used in two modes:
1) **Direct text-to-SQL** generation (via `backend/llm_service.py`)
2) **Tool-calling via MCP** (Model Context Protocol) through a dedicated **FastMCP server** (`backend/mcp_server.py`)
- **Database**: **SQLite** (`backend/chat_data.db`), auto-initialized/seeded (“self-healing”) by `backend/database.py`.

Architecturally, this is a **two-backend-process** local system (FastAPI API server + MCP server), with an external dependency on the Gemini API, and a local SQLite DB.

##### Top-level
- `frontend/` → a static web client (served by FastAPI according to README)
- `backend/` → Python services
- `start_server.ps1` / `stop_server.ps1` → confirms **two servers** started separately:
- MCP Server: `127.0.0.1:8001`
- Main API Server: `127.0.0.1:8000`

##### Backend modules (map them to “components”)
- `backend/main.py`
**FastAPI app** + routing + orchestration layer (the “system brain”)
- `backend/mcp_server.py`
**FastMCP server** exposing “tools” (schema viewing, raw access, query execution) to the LLM
- `backend/database.py`
SQLite connection, migrations/initialization, seeding; likely used by both servers
- `backend/llm_service.py`
Direct LLM calls for text-to-SQL; likely contains prompt logic + Gemini client setup

##### Container / Deployment view (what runs where, on what ports)
This should show processes and network boundaries clearly:

**Entities**
- User Browser (Frontend)
- FastAPI Server (Backend API) — port 8000
- MCP Server (FastMCP tools) — port 8001
- SQLite DB file (`backend/chat_data.db`) — local file/storage
- External: Google Gemini API

**Key interactions**
- Browser → FastAPI (HTTP)
- chat messages
- schema explorer requests
- raw data viewer requests
- FastAPI → Gemini API (HTTPS)
- for direct SQL generation and/or tool-calling planning
- FastAPI → MCP Server (HTTP on localhost)
- to let the LLM invoke tools (or to proxy tool execution depending on implementation)
- MCP Server → SQLite (local file/db access)
- schema inspection
- table reads
- query execution
- (Possibly) FastAPI → SQLite directly
- if `database.py` is used by `main.py` for some endpoints or initialization logic

##### Component view (inside the backend code)
This view should map to the actual modules:

**FastAPI (`backend/main.py`)**
- API routes / controllers
- Orchestrator:
- chooses “direct SQL generation” vs “MCP tool-calling”
- manages chat context
- formats responses for UI

**LLM Service (`backend/llm_service.py`)**
- Gemini client wrapper
- prompt / system instruction construction
- returns SQL (and maybe an explanation)

**MCP Server (`backend/mcp_server.py`)**
- Tool definitions (e.g., `get_schema`, `list_tables`, `read_table`, `run_query`)
- Tool execution layer (calls into `database.py`)

**Database module (`backend/database.py`)**
- open connection / execute statements
- initialization + seeding on startup (“self-healing”)

**Frontend (`frontend/app.js`)**
- chat UI state + message rendering
- calls backend endpoints
- renders tabular results / schema views

##### MCP server executes DB tool via `database.py` against SQLite
6. Tool result returns to FastAPI and is included in Gemini’s final response (or returned directly, depending on orchestration)

##### Architectural patterns to explicitly label
Labels because they explain “why” the system is shaped this way:

- **BFF pattern (Backend-for-Frontend)**: FastAPI serves and tailors responses specifically for the browser UI.
- **Orchestration layer**: `main.py` coordinates LLM + tools + DB.
- **Tool gateway / tool server**: MCP server is a separate tool execution plane.
- **Local persistence**: SQLite file-based DB (important boundary: “in-process file access” vs “network DB”).

##### Level 0 (Context): User ↔ System ↔ Gemini ↔ SQLite
Keep it very simple.
##### Level 1 (Containers): Browser, FastAPI:8000, MCP:8001, SQLite file, Gemini API
This is the most important for this repo.
3. **Level 2 (Components):** break FastAPI into main.py + llm_service + database module; MCP into mcp_server + database module; frontend into app.js + static assets.

This repo is small enough that Level 2 can still be readable and is worth doing.

##### Checklist to ensure you didn’t miss essential components
- [ ] Two separate backend servers are shown (ports 8000 and 8001).
- [ ] Gemini is shown as an external dependency (not “inside” the backend).
- [ ] SQLite is shown as a local persistent store and “self-healing” init is called out.
- [ ] MCP is represented as a tool server with a clear responsibility boundary (schema/raw/query tools).
- [ ] Main flows (direct SQL + MCP tool-calling) are both represented.
- [ ] Frontend is shown as static assets + JS client calling API endpoints.

## Diagram (Mermaid)

```mermaid
flowchart TD
  %% =========================
  %% Level 0: Context
  %% =========================
  subgraph "Level 0 — Context"
    U["User (Browser)"]:::external
    SYS["Chat-to-Data System (Local)"]:::system
    GEM["Google Gemini API (External)"]:::cloud
    DB0[("SQLite DB (Local file)")]:::db
    U -->|"Uses"| SYS
    SYS -->|"HTTPS"| GEM
    SYS -->|"File/SQLite_driver"| DB0
  end

  %% =========================
  %% Level 1: Containers / Deployment
  %% =========================
  subgraph "Level 1 — Containers / Deployment (Two-process backend)"
    direction TB

    subgraph "User Device"
      direction TB
      B["Browser"]:::external
      subgraph "Static Frontend (served by FastAPI)"
        direction TB
        FE["Frontend Static Assets"]:::frontend
        FE_HTML["index.html"]:::frontend
        FE_JS["app.js"]:::frontend
        FE_CSS["style.css"]:::frontend
        FE --> FE_HTML
        FE --> FE_JS
        FE --> FE_CSS
      end
      B -->|"Loads_static_assets"| FE
    end

    subgraph "Local Host"
      direction TB

      API["FastAPI API Server :8000 (BFF + Orchestrator)"]:::api
      MCP["MCP Tool Server :8001 (FastMCP)"]:::mcp
      DB[("SQLite DB file (chat_data.db)")]:::db

      API -->|"HTTP_localhost:8001 (tool_invocations)"| MCP
      MCP -->|"File/SQLite_driver (schema/read/query)"| DB
      API -.->|"File/SQLite_driver (optional_direct_access + init)"| DB
    end

    GEM1["Google Gemini API (External)"]:::cloud
    B -->|"HTTP :8000 (chat/schema/raw)"| API
    API <-->|"HTTPS (prompt + completion/tool_plan)"| GEM1
  end

  %% =========================
  %% Level 2: Components (repo modules)
  %% =========================
  subgraph "Level 2 — Components (Code-level)"
    direction TB

    subgraph "Frontend Components"
      direction TB
      F_INDEX["Frontend Entry (index.html)"]:::frontend
      F_APP["Chat UI + State + API Calls (app.js)"]:::frontend
      F_STYLE["Styling (style.css)"]:::frontend
      F_INDEX -->|"Bootstraps_UI"| F_APP
      F_STYLE -->|"Applies_styles"| F_INDEX
      F_STYLE -->|"Applies_styles"| F_APP
    end

    subgraph "Backend API (FastAPI) Components"
      direction TB
      BE_PKG["Backend Package Root (__init__.py)"]:::pkg
      BE_MAIN["FastAPI App + Routes + Orchestrator (main.py)"]:::api
      BE_LLM["LLM Service (Gemini direct text-to-SQL) (llm_service.py)"]:::llm
      BE_DB["Database Module (SQLite init/seed + execute) (database.py)"]:::dbmod

      BE_PKG --> BE_MAIN
      BE_MAIN -->|"Uses"| BE_LLM
      BE_MAIN -->|"Uses"| BE_DB
    end

    subgraph "MCP Tool Server Components"
      direction TB
      MCP_SRV["FastMCP Server + Tools (mcp_server.py)"]:::mcp
      MCP_TOOLS["Tools: get_schema/list_tables/read_table/run_query"]:::mcp
      MCP_DB["Database Module (database.py)"]:::dbmod

      MCP_SRV -->|"Exposes"| MCP_TOOLS
      MCP_TOOLS -->|"Executes_via"| MCP_DB
    end

    GEM2["Google Gemini API"]:::cloud
    SQLITE2[("SQLite DB file (chat_data.db)")]:::db

    %% Component connections
    F_APP -->|"HTTP :8000 (chat/schema/raw)"| BE_MAIN
    BE_LLM <-->|"HTTPS (Gemini SDK/API)"| GEM2
    BE_MAIN -->|"HTTP_localhost:8001 (tool_calls)"| MCP_SRV
    MCP_DB -->|"File/SQLite_driver"| SQLITE2
    BE_DB -.->|"File/SQLite_driver (optional)"| SQLITE2
  end

  %% =========================
  %% Key Flows (explicit)
  %% =========================
  subgraph "Key Flows (Control vs Data)"
    direction TB

    FLOW1["Flow 1: Direct NL → SQL → Results"]:::note
    FLOW2["Flow 2: Tool-calling via MCP (LLM uses tools)"]:::note
    FLOW3["Flow 3: Schema Explorer / Raw Data Viewer"]:::note
    FLOW4["Flow 4: Startup self-healing DB init/seed"]:::note

    %% Flow 1
    F_APP -->|"1_chat_message"| BE_MAIN
    BE_MAIN -->|"2_generate_SQL"| BE_LLM
    BE_MAIN -->|"3_execute_SQL (direct_or_via_tools)"| BE_DB
    BE_DB -->|"4_rows+metadata"| BE_MAIN
    BE_MAIN -->|"5_response"| F_APP

    %% Flow 2 (control plane emphasized)
    BE_MAIN -->|"1_prompt_with_tools"| GEM2
    GEM2 -->|"2_tool_request(s)"| BE_MAIN
    BE_MAIN -->|"3_tool_invocation"| MCP_SRV
    MCP_SRV -->|"4_tool_exec"| MCP_DB
    MCP_DB -->|"5_data_result"| MCP_SRV
    MCP_SRV -->|"6_tool_result"| BE_MAIN
    BE_MAIN -->|"7_final_answer(+data)"| F_APP

    %% Flow 3
    F_APP -->|"schema/raw_requests"| BE_MAIN
    BE_MAIN -->|"schema/raw_via_db_or_mcp"| MCP_SRV
    BE_MAIN -->|"schema/raw_optional_direct"| BE_DB
    BE_MAIN -->|"renderable_JSON"| F_APP

    %% Flow 4
    BE_MAIN -->|"startup_init_seed"| BE_DB
    MCP_SRV -->|"startup_init_seed"| MCP_DB
    BE_DB -->|"create/seed_if_missing"| SQLITE2
    MCP_DB -->|"create/seed_if_missing"| SQLITE2
  end

  %% =========================
  %% Ops / Docs / Scripts (repo signals)
  %% =========================
  subgraph "Ops & Documentation (Repo Anchors)"
    direction TB
    DOC_ARCH["Architecture Spec (ARCHITECTURE.md)"]:::doc
    DOC_README["Run Instructions (README.md)"]:::doc
    OPS_START["Start (two-process: API:8000 + MCP:8001) (start_server.ps1)"]:::ops
    OPS_STOP["Stop (stop_server.ps1)"]:::ops
    OPS_REQ["Dependencies (requirements.txt)"]:::ops

    DOC_README --> OPS_START
    OPS_START -->|"Starts"| API
    OPS_START -->|"Starts"| MCP
    OPS_STOP -->|"Stops"| API
    OPS_STOP -->|"Stops"| MCP
    DOC_ARCH -->|"Validates_boundaries/flows"| BE_MAIN
  end

  %% =========================
  %% Click Events (component mapping)
  %% =========================
  click FE "https://github.com/ramachandramurthy/test-dynamic-sql/tree/main/frontend/"
  click FE_JS "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/frontend/app.js"
  click F_APP "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/frontend/app.js"
  click FE_HTML "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/frontend/index.html"
  click F_INDEX "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/frontend/index.html"
  click FE_CSS "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/frontend/style.css"
  click F_STYLE "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/frontend/style.css"

  click BE_MAIN "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/backend/main.py"
  click BE_PKG "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/backend/__init__.py"
  click BE_LLM "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/backend/llm_service.py"
  click MCP_SRV "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/backend/mcp_server.py"
  click BE_DB "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/backend/database.py"
  click MCP_DB "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/backend/database.py"

  click DOC_ARCH "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/ARCHITECTURE.md"
  click OPS_START "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/start_server.ps1"
  click OPS_STOP "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/stop_server.ps1"
  click OPS_REQ "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/requirements.txt"
  click DOC_README "https://github.com/ramachandramurthy/test-dynamic-sql/blob/main/README.md"

  %% =========================
  %% Styles
  %% =========================
  classDef external fill:#F1F5F9,stroke:#0F172A,stroke-width:1px,color:#0F172A;
  classDef system fill:#E2E8F0,stroke:#334155,stroke-width:1px,color:#0F172A;
  classDef frontend fill:#DBEAFE,stroke:#1D4ED8,stroke-width:1px,color:#0B1220;
  classDef api fill:#DCFCE7,stroke:#16A34A,stroke-width:1px,color:#052e16;
  classDef mcp fill:#FFE4E6,stroke:#E11D48,stroke-width:1px,color:#3f0a0a;
  classDef llm fill:#FEF9C3,stroke:#CA8A04,stroke-width:1px,color:#1f1300;
  classDef db fill:#EDE9FE,stroke:#6D28D9,stroke-width:1px,color:#1f1147;
  classDef dbmod fill:#F5F3FF,stroke:#7C3AED,stroke-width:1px,color:#1f1147;
  classDef cloud fill:#FFEDD5,stroke:#C2410C,stroke-width:1px,color:#1f0c00;
  classDef note fill:#FFFFFF,stroke:#64748B,stroke-width:1px,color:#0F172A,stroke-dasharray: 4 3;
  classDef doc fill:#ECFCCB,stroke:#65A30D,stroke-width:1px,color:#1a2e05;
  classDef ops fill:#E0F2FE,stroke:#0284C7,stroke-width:1px,color:#0B1220;
  classDef pkg fill:#FAFAFA,stroke:#94A3B8,stroke-width:1px,color:#0F172A;
```
