How ramachandramurthy/test-dynamic-sql works
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.