How parth-yangandul-df/sql-chat-app works
1. Overall shape
- This repo is a full-stack natural-language-to-SQL system with one main backend and multiple client surfaces.
- The main product UI is the React/TypeScript frontend for managing connections and the semantic layer.
- There is also a separate React chatbot-frontend for an embeddable widget / standalone chat experience, plus an angular-test app used as an integration/test host.
2. Runtime boundary
- The backend is a FastAPI service written in Python.
- The frontend(s) talk to it over a versioned REST API under /api/v1.
- Docker Compose ties together the app stack with the app database, a sample target database, the backend, the frontend, and optionally Ollama.
3. Persistence layer
- The backend uses a dedicated PostgreSQL app database with pgvector.
- This database stores the app’s own metadata: connections, cached schema, glossary terms, metrics, dictionary entries, knowledge documents/chunks, sample queries, query history, and RBAC-related user data.
- The actual databases being queried by users are separate target systems; they are not the same as the app DB.
4. Connector subsystem
- Database access is abstracted behind a plugin-style connector layer.
- BaseConnector is implemented for PostgreSQL, SQL Server, BigQuery, and Databricks.
- This layer handles connection testing, schema introspection, sample value retrieval, and query execution, while enforcing read-only behavior and query limits.
- Connection strings are stored encrypted at rest, and the API never returns them directly.
5. Schema discovery and caching
- A schema service introspects target databases and writes normalized schema metadata into the app DB.
- The cached schema includes tables, columns, relationships, and related lookup data used later by the semantic layer and query validation.
- This cache is the bridge between external databases and the LLM context pipeline.
6. Semantic layer
- The semantic layer is the repo’s core differentiator.
- It combines business glossary terms, metric definitions, dictionary mappings, knowledge documents, sample queries, and cached schema into a single context package for the LLM.
- Context selection is hybrid: vector similarity, keyword matching, and foreign-key graph expansion are combined and scored before prompt assembly.
7. Query pipeline
- A natural-language question flows through query_service into a multi-step pipeline:
1. build semantic context,
2. route to an appropriate model,
3. generate SQL,
4. validate SQL,
5. execute against the target DB,
6. interpret results in natural language,
7. persist the full execution record.
- Query history captures the question, SQL, result metadata, timing, token usage, retries, and favorite state.
8. LLM orchestration
- LLM access is provider-agnostic through a provider registry.
- Supported providers are Anthropic, OpenAI, and Ollama, including embeddings where applicable.
- A router estimates query complexity and chooses the model tier.
- Specialized agents split responsibilities into SQL composition, SQL validation, error correction, and result interpretation.
9. API surface
- The backend exposes bounded resource-oriented endpoints for connections, schemas, glossary, metrics, dictionary, knowledge, sample queries, query execution, and query history.
- This separation mirrors the internal subsystems: connection management, semantic metadata management, execution, and observability/history.
- There is also a health endpoint and auth-related endpoints for login/RBAC flows.
10. Frontend management UI
- The main React frontend is a management console for the semantic layer and query workflow.
- It has dedicated pages for connections, glossary, metrics, dictionary, knowledge, history, login, and query execution.
- API calls are organized into thin client modules, with hooks for connection state, embedding status, and pagination, and a protected-route boundary for authenticated areas.
11. Chat/widget frontend
- chatbot-frontend is a separate client optimized for embedded chat and standalone chatbot flows.
- It includes widget components such as the chat panel, recent questions, and results modal, plus a standalone chat page.
- A widget build artifact is produced separately, indicating this surface can be embedded into another host application.
12. Operational and lifecycle concerns
- Schema evolution is managed with Alembic migrations.
- There are seed scripts for the IFRS 9 sample database and sample metadata, which auto-populate the app on first startup.
- Tests cover the semantic layer, graph pipeline, intent classification, domain agents, and connector-related behavior, reflecting that the backend logic is the primary system boundary.
Main entry points: router.py API v1 · session.py App Database · docker-compose.yml Compose Stack · query_service.py Query Service
Components 18
Clients
User-facing React/Angular surfaces.
- Frontend · Main management console for connections, semantic metadata, query execution, and history.
frontend - Chatbot FE · Embedded and standalone chat surface with widget build artifact and chat-focused flows.
chatbot-frontend - Angular Test · Integration host for embedding and redirect-based widget scenarios.
angular-test
Backend
FastAPI service and core Python subsystems.
- API v1 · Versioned REST boundary exposing resource-oriented endpoints for clients.
backend/app/api/v1/router.py - Auth & Health · Login, RBAC, and health endpoints for access control and service checks.
backend/app/api/v1/endpoints/auth.py - Catalog APIs · Connections, schemas, glossary, metrics, dictionary, knowledge, and sample query endpoints.
backend/app/api/v1/endpoints/connections.py - Query APIs · Query execution, query history, and session endpoints for the conversational workflow.
backend/app/api/v1/endpoints/query.py - Backend Core · Application entrypoint and service composition for the backend runtime.
backend/app/main.py - Query Service · Orchestrates semantic context building, SQL generation, validation, execution, interpretation, and persistence.
backend/app/services/query_service.py - Schema Service · Introspects target databases and stores normalized schema cache in the app database.
backend/app/services/schema_service.py - Connectors · Base connector plus PostgreSQL, SQL Server, BigQuery, and Databricks implementations.
backend/app/connectors/base_connector.py - Semantic Layer · Combines glossary, metrics, dictionary, knowledge, sample queries, and schema into LLM-ready context.
backend/app/semantic/context_builder.py - LLM Orchestration · Provider registry, router, agents, prompts, and graph-based intent pipeline.
backend/app/llm/graph/graph.py
Data & Ops
Persistence, migrations, seeding, and deployment glue.
- App Database · Persistent store for application metadata, vectors, RBAC data, schema cache, and query history.
backend/app/db/session.py - Target DBs · User-connected databases queried through read-only connectors; separate from the app database.
- Migrations · Schema evolution for the app database and feature additions such as RBAC and chat sessions.
backend/alembic - Seed Scripts · Initial population for sample metadata and IFRS 9 sample database setup.
backend/scripts/seed_ifrs9_metadata.py - Compose Stack · Brings up backend, frontend, app database, sample target database, and optional Ollama.
docker-compose.yml
Connections 21
- Frontend → API v1: REST calls · Management UI uses the versioned API for catalog and query workflows.
- Chatbot FE → API v1: REST calls · Widget and standalone chat flows call the same backend API.
- Angular Test → Chatbot FE: embed host · Integration host exercises widget embedding and redirect flows.
- API v1 → Auth & Health: routes · API boundary exposes auth and health endpoints.
- API v1 → Catalog APIs: routes · API boundary exposes metadata management endpoints.
- API v1 → Query APIs: routes · API boundary exposes execution and history endpoints.
- Catalog APIs → Schema Service: schema ops · Catalog endpoints trigger discovery and schema cache refreshes.
- Query APIs → Query Service: orchestrates · Query endpoints hand off natural-language requests to the query pipeline.
- Query Service → Semantic Layer: builds context · Pipeline assembles glossary, schema, and knowledge into LLM context.
- Query Service → LLM Orchestration: uses agents · Pipeline routes through model selection, SQL generation, validation, correction, and interpretation.
- Schema Service → Connectors: introspects · Schema discovery uses connector plugins against target systems.
- Connectors → Target DBs: queries · Connectors test, inspect, and execute against external databases.
- Connectors → App Database: stores metadata · Connection state and cached schema are persisted in the app DB.
- Semantic Layer → App Database: reads context · Semantic assembly reads glossary, metrics, dictionary, knowledge, samples, and schema records.
- LLM Orchestration → App Database: writes history · Query runs persist execution records, token usage, retries, and favorites.
- Backend Core → API v1: hosts · Application entrypoint wires the API router into the backend runtime.
- Migrations → App Database: evolves · Alembic migrations manage the metadata schema over time.
- Seed Scripts → App Database: bootstraps · Seed scripts populate first-run metadata and sample content.
- Compose Stack → Frontend: starts · Compose brings up the main frontend service.
- Compose Stack → Backend Core: starts · Compose brings up the FastAPI backend service.
- Compose Stack → App Database: starts · Compose provisions the application PostgreSQL database.