# jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator architecture

> Architecture diagram of the GitHub repository jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-07-24). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator
- Repository: https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator

## Overview

#### Purpose and entry points
SQLSense is a MySQL natural-language analytics system: it builds a semantic knowledge base (KB), deterministically plans safe read-only SQL, validates and executes it, then returns tabular/chartable results. Runtime SQL generation does not use an LLM.
- CLI: `main.py`
- FastAPI BFF/API: `api_gateway/app.py`
- React/Vite UI: `forentendNew/src/start.ts`, `forentendNew/src/router.tsx`

#### Application orchestration and session boundary
`AppService` is the application-level coordinator used by CLI and gateway-facing flows. The gateway exposes one action-oriented REST surface, maintains cookie-scoped session state, and delegates database, KB, schema, query, and history actions rather than allowing the frontend to access MySQL directly.
- `core/app_service.py`
- `api_gateway/app.py`
- `forentendNew/src/api/client.ts`

#### Database connection and KB build pipeline
On connection or rebuild, the KB pipeline reflects the live MySQL schema, profiles data, derives semantic/schema facts and relationships, produces a business glossary, and persists KB/vector artifacts. This is the primary boundary between live database metadata/data sampling and reusable local semantic evidence.
- `kb_pipeline/connection.py`
- `kb_pipeline/database_service.py`
- `kb_pipeline/knowledge_base_builder.py`

#### Semantic enrichment, graph, and retrieval
Rule-based semantic mapping is the baseline. Optional AI enrichment occurs only while building the KB through a provider chain (local Ollama or cloud-capable provider abstraction), with fallback behavior. Relationship graphs provide join-path evidence; Chroma-backed vector indexing/retrieval supplements schema facts and glossary matches for planning context.
- `kb_pipeline/ai_semantic_enricher.py`
- `kb_pipeline/relationship_graph.py`
- `kb_pipeline/vector/chroma_store.py`

#### Deterministic question-planning pipeline
A question is normalized, optionally rewritten using persisted conversation context, classified into an intent/query shape, and enriched with retrieved KB evidence. The planner produces a structured plan or fails closed when metrics, dimensions, filters, joins, grain, or confidence are insufficient. Join resolution includes BFS path selection; planner caches preserve resolved path/grain/retrieval work.
- `query_pipeline/query_pipeline.py`
- `query_pipeline/planner/orchestrator.py`
- `query_pipeline/conversation/conversation_memory.py`

#### SQL safety, execution, and results
The SQL pipeline converts approved plans into deterministic SQL, validates SELECT-only structure and forbidden operations, and revalidates before execution against MySQL. Results become execution artifacts for display, history, conversation turns, and optional chart generation. This is the hard safety boundary: planners recommend, but only validated SQL reaches the executor.
- `sql_pipeline/question_service.py`
- `sql_pipeline/deterministic_sql_generator.py`
- `sql_pipeline/sql_validator.py`

#### Presentation, observability, and external runtime dependencies
The TypeScript frontend provides connection gating, an ask workspace, SQL/results/chart views, and app routes. Python runtime depends on MySQL via SQLAlchemy-oriented database services, optional Ollama during KB enrichment, and Chroma for vector retrieval. Cross-cutting observability provides structured context, redaction, logging, metrics, and tracing; credentials remain environment/local-session concerns.
- `forentendNew/src/features/ask/AskWorkspace.tsx`
- `forentendNew/src/features/shell/ConnectionGuard.tsx`
- `infrastructure/observability/redaction.py`

Main entry points: [`core/app_service.py`](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/core/app_service.py) (App Service), [`kb_pipeline/knowledge_base_builder.py`](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/kb_pipeline/knowledge_base_builder.py) (KB Builder), [`query_pipeline/planner/orchestrator.py`](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/query_pipeline/planner/orchestrator.py) (Plan Orchestrator), [`query_pipeline/query_pipeline.py`](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/query_pipeline/query_pipeline.py) (Question Pipeline)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_ui["Frontend"]
  node_connection_guard["Connection Guard<br/>React gate"]
  node_ask_workspace["Ask Workspace<br/>React workspace<br/>[AskWorkspace.tsx]"]
  node_frontend_client["API Client<br/>TypeScript client<br/>[client.ts]"]
end

subgraph group_api["Application API"]
  node_gateway["FastAPI Gateway<br/>BFF / REST API<br/>[app.py]"]
  node_app_service["App Service<br/>application coordinator<br/>[app_service.py]"]
  node_cli["CLI<br/>entry point<br/>[main.py]"]
end

subgraph group_kb["Knowledge Base"]
  node_database_service["Database Service<br/>schema and profiling"]
  node_connection["Connection Manager<br/>database connection<br/>[connection.py]"]
  node_kb_builder["KB Builder<br/>KB pipeline"]
  node_semantic_enricher["Semantic Enricher<br/>optional AI enrichment"]
  node_relationship_graph["Relationship Graph<br/>join-path evidence"]
  node_chroma_store[("Chroma Store<br/>vector index<br/>[chroma_store.py]")]
end

subgraph group_query["Question Planning"]
  node_query_pipeline["Question Pipeline<br/>question processing<br/>[query_pipeline.py]"]
  node_conversation_memory[("Conversation Memory<br/>persisted context")]
  node_planner["Plan Orchestrator<br/>deterministic planner<br/>[orchestrator.py]"]
end

subgraph group_sql["Safe SQL Execution"]
  node_question_service["Question Service<br/>SQL workflow"]
  node_sql_generator["Deterministic SQL Generator<br/>SQL compiler"]
  node_sql_validator{{"SQL Validator<br/>safety boundary<br/>[sql_validator.py]"}}
end

node_mysql[("MySQL<br/>external database")]
node_ollama{{"AI Providers<br/>optional external runtime"}}
node_observability["Observability<br/>cross-cutting telemetry<br/>[redaction.py]"]

node_connection_guard -->|"permits connected session"| node_frontend_client
node_ask_workspace -->|"actions and results"| node_frontend_client
node_frontend_client -->|"cookie-scoped REST"| node_gateway
node_gateway -->|"delegates actions"| node_app_service
node_cli -->|"invokes"| node_app_service
node_app_service -->|"connects"| node_connection
node_connection -->|"SQLAlchemy access"| node_mysql
node_app_service -->|"builds or rebuilds KB"| node_kb_builder
node_kb_builder -->|"reflects and profiles"| node_database_service
node_database_service -->|"schema and samples"| node_mysql
node_kb_builder -.->|"optional enrichment"| node_semantic_enricher
node_semantic_enricher -.->|"provider chain"| node_ollama
node_kb_builder -->|"derives relationships"| node_relationship_graph
node_kb_builder -->|"persists semantic evidence"| node_chroma_store
node_app_service -->|"asks question"| node_query_pipeline
node_query_pipeline -.->|"rewrite context"| node_conversation_memory
node_query_pipeline -->|"normalized question"| node_planner
node_planner -->|"BFS join paths"| node_relationship_graph
node_planner -->|"retrieves KB evidence"| node_chroma_store
node_planner -->|"approved structured plan"| node_question_service
node_question_service -->|"compiles plan"| node_sql_generator
node_sql_generator -->|"generated SQL"| node_sql_validator
node_sql_validator -->|"validated SELECT execution"| node_mysql
node_question_service -->|"stores execution turn"| node_conversation_memory
node_app_service -.->|"telemetry"| node_observability

click node_connection_guard "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/forentendNew/src/features/shell/ConnectionGuard.tsx"
click node_ask_workspace "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/forentendNew/src/features/ask/AskWorkspace.tsx"
click node_frontend_client "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/forentendNew/src/api/client.ts"
click node_gateway "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/api_gateway/app.py"
click node_app_service "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/core/app_service.py"
click node_cli "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/main.py"
click node_database_service "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/kb_pipeline/database_service.py"
click node_connection "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/kb_pipeline/connection.py"
click node_kb_builder "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/kb_pipeline/knowledge_base_builder.py"
click node_semantic_enricher "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/kb_pipeline/ai_semantic_enricher.py"
click node_relationship_graph "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/kb_pipeline/relationship_graph.py"
click node_chroma_store "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/kb_pipeline/vector/chroma_store.py"
click node_query_pipeline "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/query_pipeline/query_pipeline.py"
click node_conversation_memory "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/query_pipeline/conversation/conversation_memory.py"
click node_planner "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/query_pipeline/planner/orchestrator.py"
click node_question_service "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/sql_pipeline/question_service.py"
click node_sql_generator "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/sql_pipeline/deterministic_sql_generator.py"
click node_sql_validator "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/sql_pipeline/sql_validator.py"
click node_observability "https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/infrastructure/observability/redaction.py"

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_connection_guard,node_ask_workspace,node_frontend_client toneBlue
class node_gateway,node_app_service,node_cli toneAmber
class node_database_service,node_connection,node_kb_builder,node_semantic_enricher,node_relationship_graph,node_chroma_store toneMint
class node_query_pipeline,node_conversation_memory,node_planner toneRose
class node_question_service,node_sql_generator,node_sql_validator toneIndigo
class node_mysql,node_ollama,node_observability toneNeutral
```

## Components

### Frontend

- [Connection Guard](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/forentendNew/src/features/shell/ConnectionGuard.tsx): React gate · Blocks workspace access until a database session is connected. · `forentendNew/src/features/shell/ConnectionGuard.tsx`
- [Ask Workspace](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/forentendNew/src/features/ask/AskWorkspace.tsx): React workspace · Presents questions, SQL, results, and charts. · `forentendNew/src/features/ask/AskWorkspace.tsx`
- [API Client](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/forentendNew/src/api/client.ts): TypeScript client · `forentendNew/src/api/client.ts`

### Application API

- [FastAPI Gateway](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/api_gateway/app.py): BFF / REST API · Owns cookie-scoped sessions and action-oriented API access. · `api_gateway/app.py`
- [App Service](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/core/app_service.py): application coordinator · Shared coordinator for CLI and gateway-facing flows. · `core/app_service.py`
- [CLI](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/main.py): entry point · `main.py`

### Knowledge Base

- [Database Service](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/kb_pipeline/database_service.py): schema and profiling · Reflects live schema and profiles sampled MySQL data. · `kb_pipeline/database_service.py`
- [Connection Manager](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/kb_pipeline/connection.py): database connection · `kb_pipeline/connection.py`
- [KB Builder](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/kb_pipeline/knowledge_base_builder.py): KB pipeline · Builds persistent semantic and schema evidence from the live database. · `kb_pipeline/knowledge_base_builder.py`
- [Semantic Enricher](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/kb_pipeline/ai_semantic_enricher.py): optional AI enrichment · Uses provider fallback only during KB construction. · `kb_pipeline/ai_semantic_enricher.py`
- [Relationship Graph](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/kb_pipeline/relationship_graph.py): join-path evidence · Stores table relationships used for join resolution. · `kb_pipeline/relationship_graph.py`
- [Chroma Store](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/kb_pipeline/vector/chroma_store.py): vector index · Persists and retrieves semantic KB evidence. · `kb_pipeline/vector/chroma_store.py`

### Question Planning

- [Question Pipeline](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/query_pipeline/query_pipeline.py): question processing · Normalizes questions and assembles planning context. · `query_pipeline/query_pipeline.py`
- [Conversation Memory](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/query_pipeline/conversation/conversation_memory.py): persisted context · Supplies optional question rewrites and records turns. · `query_pipeline/conversation/conversation_memory.py`
- [Plan Orchestrator](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/query_pipeline/planner/orchestrator.py): deterministic planner · Classifies intent, retrieves evidence, resolves joins, and fails closed on insufficient confidence. · `query_pipeline/planner/orchestrator.py`

### Safe SQL Execution

- [Question Service](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/sql_pipeline/question_service.py): SQL workflow · Coordinates approved-plan SQL generation, validation, execution, and artifacts. · `sql_pipeline/question_service.py`
- [Deterministic SQL Generator](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/sql_pipeline/deterministic_sql_generator.py): SQL compiler · `sql_pipeline/deterministic_sql_generator.py`
- [SQL Validator](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/sql_pipeline/sql_validator.py): safety boundary · Enforces SELECT-only SQL and forbidden-operation checks before execution. · `sql_pipeline/sql_validator.py`

### Other components

- **MySQL**: external database · Live source database and query execution target.
- **AI Providers**: optional external runtime · Optional Ollama or cloud-capable enrichment provider.
- [Observability](https://github.com/jaydeeppatil0825/sqlsense---ai-powered-cli-sql-query-generator/blob/codex/review-project-overview/infrastructure/observability/redaction.py): cross-cutting telemetry · Provides contextual logging, redaction, metrics, and tracing. · `infrastructure/observability/redaction.py`

## Connections

- Connection Guard → API Client: permits connected session
- Ask Workspace → API Client: actions and results
- API Client → FastAPI Gateway: cookie-scoped REST
- FastAPI Gateway → App Service: delegates actions
- CLI → App Service: invokes
- App Service → Connection Manager: connects
- Connection Manager → MySQL: SQLAlchemy access
- App Service → KB Builder: builds or rebuilds KB
- KB Builder → Database Service: reflects and profiles
- Database Service → MySQL: schema and samples
- KB Builder → Semantic Enricher: optional enrichment
- Semantic Enricher → AI Providers: provider chain
- KB Builder → Relationship Graph: derives relationships
- KB Builder → Chroma Store: persists semantic evidence
- App Service → Question Pipeline: asks question
- Question Pipeline → Conversation Memory: rewrite context
- Question Pipeline → Plan Orchestrator: normalized question
- Plan Orchestrator → Relationship Graph: BFS join paths
- Plan Orchestrator → Chroma Store: retrieves KB evidence
- Plan Orchestrator → Question Service: approved structured plan
- Question Service → Deterministic SQL Generator: compiles plan
- Deterministic SQL Generator → SQL Validator: generated SQL
- SQL Validator → MySQL: validated SELECT execution
- Question Service → Conversation Memory: stores execution turn
- App Service → Observability: telemetry