How dataease/sqlbot works
1. What this repository is
- SQLBot is a full-stack “chat with data” system: users ask questions in natural language, the backend turns them into SQL and analysis steps with LLM/RAG support, and the frontend renders answers as charts, tables, or narrative output.
- The codebase is organized as a Python backend, a Vue 3 frontend, and a small Node-based chart SSR service.
2. Top-level runtime layout
- The main application runs as a backend API service plus a single-page frontend.
- Docker is the primary packaging/deployment path, with separate Dockerfiles and a compose-based installer flow.
- Persistent state is split across a relational database, file volumes for uploads/logs/images, and external model/data integrations.
3. Backend architecture
- backend/main.py and backend/apps/api.py form the entry points into a modular FastAPI-style backend.
- Feature code is organized by domain under backend/apps/*, while shared infrastructure lives in backend/common/*.
- Database schema is managed with Alembic migrations in backend/alembic/versions, indicating a strongly versioned relational model.
4. Core infrastructure boundaries
- backend/apps/db centralizes database and engine abstractions, including a separate es_engine.py, which suggests the backend can route some retrieval/search workloads through a search engine layer.
- backend/common/core contains cross-cutting concerns: config, dependency wiring, pagination, file helpers, security, caching, and response middleware.
- This separation keeps business modules thin and lets the app share auth, locale, and response behavior consistently.
5. Identity, tenant, and platform management
- The system module owns users, login, workspaces, assistants, API keys, authentication providers, platform parameters, and system variables.
- Workspace is a key boundary in the architecture: it is used to isolate resources and permissions rather than treating the app as a single global tenant space.
- Authentication also supports multiple enterprise login modes, reflected by the xpack-specific frontend login views and the backend auth middleware.
6. Datasource and governance domain
- backend/apps/datasource is one of the main domains: it manages data sources, tables, fields, permissions, row permissions, table relations, and “recommended problem” prompts.
- This module also includes embedding-related code for datasource/table embeddings, which is part of the RAG layer used to improve text-to-SQL matching.
- Excel utilities and datasource-specific helpers show that structured file imports are part of the data onboarding path.
7. AI model abstraction
- backend/apps/ai_model abstracts LLM and embedding providers behind a factory layer.
- The tree shows a native OpenAI implementation plus OpenAI-compatible provider support, matching the README’s list of supported model vendors.
- This provider abstraction is the main boundary between SQLBot’s prompt/agent logic and the external model APIs.
8. Chat and question-answer execution flow
- backend/apps/chat handles the end-user question lifecycle: chat APIs, CRUD/state management, chat models, and task execution.
- backend/apps/chat/task/llm.py is where the backend likely orchestrates the model call, SQL generation, and answer assembly.
- The template subsystem under backend/apps/template is central to this flow: it contains generators for SQL, charts, analysis, predictions, dynamic prompts, guess-question flows, datasource selection, and filtering.
9. Knowledge refinement and prompt assets
- backend/apps/terminology stores domain terminology used to improve query interpretation and SQL generation.
- backend/apps/data_training appears to manage training/feedback data used to improve future answers.
- The backend also stores reusable SQL examples and template YAML assets under backend/templates, which are likely used to calibrate prompt behavior across different databases.
10. Dashboard and visualization subsystem
- backend/apps/dashboard manages dashboard metadata and CRUD logic, while the frontend contains a substantial canvas/editor/preview stack for composing dashboard layouts.
- The frontend chart components and the g2-ssr service indicate chart rendering is split between client-side editing and server-side chart generation/rendering support.
- This makes dashboards a separate product surface from chat: one is conversational analytics, the other is persisted visual composition.
11. Audit, logs, and operational traceability
- backend/common/audit provides request-context and logging decorators, and the migrations include system logs and chat logs tables.
- This suggests the system records not only login/admin activity but also the execution trace of chat interactions and generated outputs.
- Those logs are important for debugging, traceability, and improving generated answers over time.
12. Frontend structure
- The frontend is a Vue 3 + Vite SPA with domain-based structure: api/, stores/, router/, components/, and views/.
- Major view groups mirror backend domains: chat, dashboard, datasource management, system administration, embedded mode, login, and workspaces.
- Internationalization is built in with multiple locale files, and there are dedicated embedded/UI helper assets for assistant and swagger-like documentation experiences.
13. Integration and embedding
- SQLBot is designed to be embedded into other products, reflected by frontend/src/views/embedded, frontend/public/assistant.js, and the README’s emphasis on web embedding, popup embedding, and MCP usage.
- The backend/apps/mcp module is the backend integration boundary for external agent/tool calling.
- In architecture terms, SQLBot is not just a standalone app; it is also an embeddable AI data-answering service.
14. Repository-level delivery and operations
- CI/CD workflows build and push container images, run checks, and sync to alternate registries.
- Installer scripts and compose files support packaged deployment in constrained environments, including offline-style installation flows.
- Overall, the repo is structured for enterprise self-hosting: versioned schema, containerized runtime, workspace isolation, and configurable external LLM backends.
Main entry points: api.py API router · src SPA shell · template Templates · installer Deploy
Components 24
Backend
Python API service and domain modules
- App entry · Bootstraps the backend service and mounts API modules
backend/main.py - API router · Wires domain routers and middleware into the application
backend/apps/api.py - Core · Cross-cutting config, deps, security, pagination, files, caching, and responses
backend/common/core - Audit · Request context and log decoration for operational traceability
backend/common/audit - DB layer · Database engine, SQL helpers, constants, and search engine integration
backend/apps/db - System · Users, login, workspaces, assistants, API keys, parameters, and platform variables
backend/apps/system - Auth middleware · Authentication and enterprise login enforcement
backend/apps/system/middleware/auth.py - Datasource · Data sources, tables, fields, permissions, row rules, relations, and recommended problems
backend/apps/datasource - Embeddings · Datasource and table embeddings used for retrieval and text-to-SQL matching
backend/apps/datasource/embedding - AI models · Provider abstraction for LLM and embedding backends
backend/apps/ai_model - Chat · Chat state, CRUD, task execution, and answer assembly
backend/apps/chat - Templates · Generators for SQL, charts, analysis, predictions, dynamic prompts, datasource selection, and filters
backend/apps/template - Dashboard · Persisted dashboard metadata and CRUD services
backend/apps/dashboard - Terminology · Domain vocabulary used to improve interpretation and SQL generation
backend/apps/terminology - Training · Stores data used to refine future answers
backend/apps/data_training - MCP · Integration boundary for external agent/tool calling
backend/apps/mcp
Frontend
Vue SPA and UI feature surfaces
- SPA shell · Vite/Vue application shell, router, stores, and shared UI runtime
frontend/src - Chat UI · Conversation views, answers, execution logs, and chart rendering components
frontend/src/views/chat - Dashboard UI · Dashboard editor, preview, canvas layout, and component library
frontend/src/views/dashboard - Datasource UI · Datasource, tables, permissions, relations, and upload flows
frontend/src/views/ds - System UI · User, model, auth, workspace, prompt, training, variables, and platform administration
frontend/src/views/system - Embedded UI · Embeddable assistant and page views for integration into other products
frontend/src/views/embedded
Connections 28
- App entry → API router: starts · Application entry composes the API service
- API router → Core: uses · Shared runtime concerns are applied across routes
- API router → Audit: logs · Request and action tracing flows through audit helpers
- API router → DB layer: persists · Business modules rely on database and search abstractions
- API router → System: mounts · Identity and platform endpoints are part of the service
- API router → Datasource: mounts · Datasource management endpoints are part of the service
- API router → Chat: mounts · Chat lifecycle endpoints are part of the service
- API router → Dashboard: mounts · Dashboard endpoints are part of the service
- API router → Terminology: mounts · Terminology endpoints support query refinement
- API router → Training: mounts · Training-feedback endpoints support improvement loops
- API router → MCP: exposes · Tool-calling integration is exposed through the backend
- System → Auth middleware: enforces · Authentication middleware protects system-wide access
- Datasource → Embeddings: indexes · Datasource content is embedded for retrieval support
- Chat → AI models: calls · Chat orchestration delegates model calls to provider abstractions
- Chat → Templates: prompts · Chat execution depends on prompt and generation templates
- Templates → Datasource: selects · Templates include datasource selection and filtering logic
- Templates → Terminology: uses · Query interpretation can incorporate terminology assets
- Templates → AI models: formats · Prompt generation targets provider-specific model capabilities
- SPA shell → Chat UI: renders · Primary SPA surface includes conversational analytics
- SPA shell → Dashboard UI: renders · Primary SPA surface includes dashboard editing and preview
- SPA shell → Datasource UI: renders · Primary SPA surface includes datasource administration
- SPA shell → System UI: renders · Primary SPA surface includes system administration
- SPA shell → Embedded UI: renders · Primary SPA surface includes embeddable views
- Chat UI → Chart SSR: uses · Chart views can rely on server-side rendering support
- Dashboard UI → Chart SSR: uses · Dashboard composition and preview benefit from SSR charting
- Deploy → App entry: packages · Container and installer flows deliver the backend runtime
- Deploy → SPA shell: packages · Deployment bundles the SPA frontend assets
- Deploy → Chart SSR: packages · Deployment includes the chart rendering service