# parth-yangandul-df/sql-chat-app architecture

> Architecture diagram of the GitHub repository parth-yangandul-df/sql-chat-app, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-04-07). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/parth-yangandul-df/sql-chat-app
- Repository: https://github.com/parth-yangandul-df/sql-chat-app

## Overview

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: [`backend/app/api/v1/router.py`](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/api/v1/router.py) (API v1), [`backend/app/db/session.py`](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/db/session.py) (App Database), [`docker-compose.yml`](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/docker-compose.yml) (Compose Stack), [`backend/app/services/query_service.py`](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/services/query_service.py) (Query Service)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_clients["Clients"]
  node_frontend_app["Frontend<br/>React admin UI"]
  node_chatbot_app["Chatbot FE<br/>React widget UI"]
  node_angular_test_app["Angular Test<br/>Angular host"]
end

subgraph group_backend["Backend"]
  node_api_router["API v1<br/>FastAPI router<br/>[router.py]"]
  node_auth_endpoints["Auth & Health<br/>API endpoints<br/>[auth.py]"]
  node_catalog_endpoints["Catalog APIs<br/>API endpoints<br/>[connections.py]"]
  node_query_endpoints["Query APIs<br/>API endpoints<br/>[query.py]"]
  node_backend_service["Backend Core<br/>FastAPI app<br/>[main.py]"]
  node_query_service["Query Service<br/>workflow service<br/>[query_service.py]"]
  node_schema_service["Schema Service<br/>metadata service<br/>[schema_service.py]"]
  node_connector_layer["Connectors<br/>plugin adapters<br/>[base_connector.py]"]
  node_semantic_layer["Semantic Layer<br/>context pipeline<br/>[context_builder.py]"]
  node_llm_orchestration["LLM Orchestration<br/>provider-agnostic LLM stack<br/>[graph.py]"]
end

subgraph group_data["Data & Ops"]
  node_app_database[("App Database<br/>PostgreSQL + pgvector<br/>[session.py]")]
  node_target_databases[("Target DBs<br/>external data sources")]
  node_migrations["Migrations<br/>Alembic"]
  node_seeding["Seed Scripts<br/>bootstrap data"]
  node_compose_stack["Compose Stack<br/>docker-compose<br/>[docker-compose.yml]"]
end

node_frontend_app -->|"REST calls"| node_api_router
node_chatbot_app -->|"REST calls"| node_api_router
node_angular_test_app -.->|"embed host"| node_chatbot_app
node_api_router -->|"routes"| node_auth_endpoints
node_api_router -->|"routes"| node_catalog_endpoints
node_api_router -->|"routes"| node_query_endpoints
node_catalog_endpoints -->|"schema ops"| node_schema_service
node_query_endpoints -->|"orchestrates"| node_query_service
node_query_service -->|"builds context"| node_semantic_layer
node_query_service -->|"uses agents"| node_llm_orchestration
node_schema_service -->|"introspects"| node_connector_layer
node_connector_layer -->|"queries"| node_target_databases
node_connector_layer -->|"stores metadata"| node_app_database
node_semantic_layer -->|"reads context"| node_app_database
node_llm_orchestration -->|"writes history"| node_app_database
node_backend_service -->|"hosts"| node_api_router
node_migrations -->|"evolves"| node_app_database
node_seeding -->|"bootstraps"| node_app_database
node_compose_stack -->|"starts"| node_frontend_app
node_compose_stack -->|"starts"| node_backend_service
node_compose_stack -->|"starts"| node_app_database

click node_frontend_app "https://github.com/parth-yangandul-df/sql-chat-app/tree/main/frontend"
click node_chatbot_app "https://github.com/parth-yangandul-df/sql-chat-app/tree/main/chatbot-frontend"
click node_angular_test_app "https://github.com/parth-yangandul-df/sql-chat-app/tree/main/angular-test"
click node_api_router "https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/api/v1/router.py"
click node_auth_endpoints "https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/api/v1/endpoints/auth.py"
click node_catalog_endpoints "https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/api/v1/endpoints/connections.py"
click node_query_endpoints "https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/api/v1/endpoints/query.py"
click node_backend_service "https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/main.py"
click node_query_service "https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/services/query_service.py"
click node_schema_service "https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/services/schema_service.py"
click node_connector_layer "https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/connectors/base_connector.py"
click node_semantic_layer "https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/semantic/context_builder.py"
click node_llm_orchestration "https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/llm/graph/graph.py"
click node_app_database "https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/db/session.py"
click node_migrations "https://github.com/parth-yangandul-df/sql-chat-app/tree/main/backend/alembic"
click node_seeding "https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/scripts/seed_ifrs9_metadata.py"
click node_compose_stack "https://github.com/parth-yangandul-df/sql-chat-app/blob/main/docker-compose.yml"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_frontend_app,node_chatbot_app,node_angular_test_app toneBlue
class node_api_router,node_auth_endpoints,node_catalog_endpoints,node_query_endpoints,node_backend_service,node_query_service,node_schema_service,node_connector_layer,node_semantic_layer,node_llm_orchestration toneAmber
class node_app_database,node_target_databases,node_migrations,node_seeding,node_compose_stack toneMint
```

## Components

### Clients

User-facing React/Angular surfaces.

- [Frontend](https://github.com/parth-yangandul-df/sql-chat-app/tree/main/frontend): React admin UI · Main management console for connections, semantic metadata, query execution, and history. · `frontend`
- [Chatbot FE](https://github.com/parth-yangandul-df/sql-chat-app/tree/main/chatbot-frontend): React widget UI · Embedded and standalone chat surface with widget build artifact and chat-focused flows. · `chatbot-frontend`
- [Angular Test](https://github.com/parth-yangandul-df/sql-chat-app/tree/main/angular-test): Angular host · Integration host for embedding and redirect-based widget scenarios. · `angular-test`

### Backend

FastAPI service and core Python subsystems.

- [API v1](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/api/v1/router.py): FastAPI router · Versioned REST boundary exposing resource-oriented endpoints for clients. · `backend/app/api/v1/router.py`
- [Auth & Health](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/api/v1/endpoints/auth.py): API endpoints · Login, RBAC, and health endpoints for access control and service checks. · `backend/app/api/v1/endpoints/auth.py`
- [Catalog APIs](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/api/v1/endpoints/connections.py): API endpoints · Connections, schemas, glossary, metrics, dictionary, knowledge, and sample query endpoints. · `backend/app/api/v1/endpoints/connections.py`
- [Query APIs](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/api/v1/endpoints/query.py): API endpoints · Query execution, query history, and session endpoints for the conversational workflow. · `backend/app/api/v1/endpoints/query.py`
- [Backend Core](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/main.py): FastAPI app · Application entrypoint and service composition for the backend runtime. · `backend/app/main.py`
- [Query Service](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/services/query_service.py): workflow service · Orchestrates semantic context building, SQL generation, validation, execution, interpretation, and persistence. · `backend/app/services/query_service.py`
- [Schema Service](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/services/schema_service.py): metadata service · Introspects target databases and stores normalized schema cache in the app database. · `backend/app/services/schema_service.py`
- [Connectors](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/connectors/base_connector.py): plugin adapters · Base connector plus PostgreSQL, SQL Server, BigQuery, and Databricks implementations. · `backend/app/connectors/base_connector.py`
- [Semantic Layer](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/semantic/context_builder.py): context pipeline · Combines glossary, metrics, dictionary, knowledge, sample queries, and schema into LLM-ready context. · `backend/app/semantic/context_builder.py`
- [LLM Orchestration](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/llm/graph/graph.py): provider-agnostic LLM stack · 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](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/app/db/session.py): PostgreSQL + pgvector · Persistent store for application metadata, vectors, RBAC data, schema cache, and query history. · `backend/app/db/session.py`
- **Target DBs**: external data sources · User-connected databases queried through read-only connectors; separate from the app database.
- [Migrations](https://github.com/parth-yangandul-df/sql-chat-app/tree/main/backend/alembic): Alembic · Schema evolution for the app database and feature additions such as RBAC and chat sessions. · `backend/alembic`
- [Seed Scripts](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/backend/scripts/seed_ifrs9_metadata.py): bootstrap data · Initial population for sample metadata and IFRS 9 sample database setup. · `backend/scripts/seed_ifrs9_metadata.py`
- [Compose Stack](https://github.com/parth-yangandul-df/sql-chat-app/blob/main/docker-compose.yml): docker-compose · Brings up backend, frontend, app database, sample target database, and optional Ollama. · `docker-compose.yml`

## Connections

- 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.
