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

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