# sxjalxo/dbbuddy architecture

> Architecture diagram of the GitHub repository sxjalxo/dbbuddy, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-06-22). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/sxjalxo/dbbuddy
- Repository: https://github.com/sxjalxo/dbbuddy

## Overview

**1) Overall shape**
DB Buddy is a three-tier system centered on a Python core engine. The core package `dbbuddy_core` contains the NL→SQL pipeline, safety checks, explainability, learning, and database access. Around it sit two delivery surfaces: a Python CLI (`dbbuddy/`) and a FastAPI backend (`backend/`) used by the React frontend (`frontend/`).

**2) Core architectural boundary**
`dbbuddy_core` is the main subsystem boundary. It is where query understanding, semantic mapping, planning, SQL generation, validation, execution, and post-query learning all happen. The rest of the repository mostly wraps or presents this core rather than duplicating logic.

**3) End-to-end query flow**
The documented flow is: schema retrieval → semantic layer construction → query enhancement with memory → intent building → deterministic planning → SQL generation → validation → safe execution → explanation → learning. This is the backbone of the architecture and the clearest diagram axis for the repo.

**4) Semantic understanding layer**
Modules such as `schema.py`, `relationship_graph.py`, `semantic_matcher.py`, `semantic_query_engine.py`, `semantic_enhancer.py`, `embedding_engine.py`, `embedding_index.py`, `vector_store.py`, and `semantic_layer.json` form the schema-aware meaning layer. This layer maps user language to database concepts like entities, monetary fields, temporal fields, and identifiers, and can enrich queries using learned or embedded associations.

**5) Deterministic planning and SQL generation**
`intent_builder.py`, `query_planner.py`, `planner_utils.py`, `pipeline.py`, `mapping.py`, `query.py`, `contracts.py`, and `models.py` are responsible for turning intent into an executable plan and then into SQL. The repository explicitly avoids “blind AI SQL generation”; AI is used for semantic labeling, while SQL generation and execution planning are deterministic.

**6) Validation, safety, and execution controls**
`validation.py`-style responsibilities are spread across `system_validation.py`, `safety.py`, `execution.py`, `db.py`, `rate_limiter.py`, `query_logger.py`, `query_history.py`, and `cache.py`. These modules enforce correctness, parameterization, approval behavior, dry-run support, FK-aware delete handling, and query logging. This is the main trust boundary between generated intent and the live database.

**7) Explainability and observability**
`explainability.py`, `plan_explanation.py`, `plan_visualization.py`, `observability.py`, and `logger.py` provide the traceability layer. They turn internal planner decisions into human-readable reasons such as join inference, aggregation choice, grouping rules, and ranking logic. This is a distinct subsystem from execution; it is meant to explain what the engine already decided, not influence the decision itself.

**8) Self-learning and adaptive improvement**
Learning-related components include `learning_engine.py`, `learning_module.py`, `adaptive_improvement.py`, `feedback.py`, `relevance.py`, `clarification.py`, `ambiguity_predictor.py`, `failure_classifier.py`, and `system_intelligence.py`. These modules build a guarded memory of semantic mappings and query outcomes, with guardrails like thresholds, decay, and caps. The architecture treats learning as assistive, not authoritative.

**9) AI integration**
`ai.py` and `config.py` support optional AI providers for semantic labeling only, with local, remote, and hybrid modes mentioned in the README. The important architectural point is that AI is not part of SQL execution authority; it augments schema understanding and labeling, and can fall back to deterministic rules if unavailable.

**10) Plugin and extension model**
`dbbuddy_core/plugins/` and `dbbuddy/plugins/` indicate an extension boundary for custom mappings or behaviors. The default plugin loader and base classes suggest the system can be adapted to different schemas or domain conventions without changing the core planner.

**11) CLI and API surfaces**
`dbbuddy/main.py` and `dbbuddy/__main__.py` provide the command-line entrypoint, while `backend/main.py` exposes the core through an HTTP API. These are thin orchestration layers that call into `dbbuddy_core`; they should be shown as presentation/integration layers rather than business-logic owners.

**12) Frontend**
`frontend/` is a separate React application built with Vite and a modern component stack. It provides the user-facing query experience, settings, and likely API management for AI keys. Architecturally, it depends on the backend and should be drawn as a client consuming the API, not as part of the execution engine.

**13) Testing and validation**
`tests/` is substantial and includes benchmark, behavioral, integration, security, SQL validation, and end-to-end execution coverage. This signals that the architecture relies heavily on regression tests to preserve deterministic behavior across schemas, not just on unit tests.

**14) Operational and configuration boundaries**
Key operational concerns are `requirements.txt`/`pyproject.toml` for Python packaging, the frontend `package.json`/Bun/Vite toolchain, and environment-driven config in the core. `secrets_store.py` shows a dedicated boundary for credential storage, especially for external AI provider keys, which are kept out of the core query path.

**15) What to emphasize in a diagram**
The most important diagram flows are: user input enters CLI/API/UI → core pipeline resolves schema semantics → planner produces SQL → validator/safety gate checks it → execution talks to the database → explanation and logs are emitted → feedback updates the learning layer. The database is the only hard external runtime dependency; AI services are optional and isolated to semantic labeling.

Main entry points: [`dbbuddy_core/semantic_matcher.py`](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/semantic_matcher.py) (Semantic matcher), [`dbbuddy_core/execution.py`](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/execution.py) (Execution), [`dbbuddy_core/orchestrator.py`](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/orchestrator.py) (Orchestrator), [`dbbuddy_core/pipeline.py`](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/pipeline.py) (Pipeline)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_surfaces["Delivery surfaces"]
  node_frontend["Frontend<br/>React UI"]
  node_backend["Backend API<br/>FastAPI service<br/>[main.py]"]
  node_cli["CLI<br/>Python entrypoint<br/>[main.py]"]
end

subgraph group_core["Core engine"]
  node_orchestrator["Orchestrator<br/>pipeline coordinator<br/>[orchestrator.py]"]
  node_pipeline["Pipeline<br/>query flow<br/>[pipeline.py]"]
  node_schema[("Schema layer<br/>schema intelligence<br/>[schema.py]")]
  node_semantic["Semantic matcher<br/>meaning layer"]
  node_planner["Planner<br/>deterministic planning<br/>[query_planner.py]"]
  node_intent["Intent builder<br/>intent model<br/>[intent_builder.py]"]
  node_sql["SQL generator<br/>query compiler<br/>[query.py]"]
  node_validation{{"Safety gate<br/>validation"}}
  node_execution["Execution<br/>db runtime<br/>[execution.py]"]
  node_explain["Explainability<br/>trace layer<br/>[explainability.py]"]
  node_learning(("Learning<br/>adaptive memory<br/>[learning_engine.py]"))
  node_ai(("AI labeling<br/>optional provider<br/>[ai.py]"))
end

subgraph group_support["Support systems"]
  node_db[("Database<br/>external DB<br/>[db.py]")]
  node_plugins["Plugins<br/>extension boundary"]
  node_tests["Tests<br/>validation suite"]
end

node_frontend -->|"API calls"| node_backend
node_backend -->|"requests"| node_orchestrator
node_cli -->|"commands"| node_orchestrator
node_orchestrator -->|"drives"| node_pipeline
node_pipeline -->|"load schema"| node_schema
node_schema -->|"semantic context"| node_semantic
node_semantic -.->|"labeling help"| node_ai
node_semantic -->|"build intent"| node_intent
node_intent -->|"plan query"| node_planner
node_planner -->|"compile SQL"| node_sql
node_sql -->|"validate"| node_validation
node_validation -->|"approve"| node_execution
node_execution -->|"run query"| node_db
node_execution -->|"explain"| node_explain
node_execution -->|"feedback"| node_learning
node_learning -.->|"refine mappings"| node_semantic
node_plugins -.->|"custom rules"| node_semantic
node_tests -.->|"exercise"| node_pipeline
node_tests -.->|"verify safety"| node_validation

click node_frontend "https://github.com/sxjalxo/dbbuddy/tree/main/frontend"
click node_backend "https://github.com/sxjalxo/dbbuddy/blob/main/backend/main.py"
click node_cli "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy/main.py"
click node_orchestrator "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/orchestrator.py"
click node_pipeline "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/pipeline.py"
click node_schema "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/schema.py"
click node_semantic "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/semantic_matcher.py"
click node_planner "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/query_planner.py"
click node_intent "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/intent_builder.py"
click node_sql "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/query.py"
click node_validation "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/system_validation.py"
click node_execution "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/execution.py"
click node_explain "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/explainability.py"
click node_learning "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/learning_engine.py"
click node_ai "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/ai.py"
click node_db "https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/db.py"
click node_plugins "https://github.com/sxjalxo/dbbuddy/tree/main/dbbuddy_core/plugins"
click node_tests "https://github.com/sxjalxo/dbbuddy/tree/main/tests"

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,node_backend,node_cli toneBlue
class node_orchestrator,node_pipeline,node_schema,node_semantic,node_planner,node_intent,node_sql,node_validation,node_execution,node_explain,node_learning,node_ai toneAmber
class node_db,node_plugins,node_tests toneMint
```

## Components

### Delivery surfaces

CLI, API, and frontend entrypoints

- [Frontend](https://github.com/sxjalxo/dbbuddy/tree/main/frontend): React UI · User-facing query and settings application · `frontend`
- [Backend API](https://github.com/sxjalxo/dbbuddy/blob/main/backend/main.py): FastAPI service · HTTP surface that exposes core capabilities · `backend/main.py`
- [CLI](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy/main.py): Python entrypoint · Command-line interface for running the system · `dbbuddy/main.py`

### Core engine

NL to SQL pipeline, safety, execution, and learning

- [Orchestrator](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/orchestrator.py): pipeline coordinator · Top-level control flow for request handling · `dbbuddy_core/orchestrator.py`
- [Pipeline](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/pipeline.py): query flow · Coordinates the end-to-end query lifecycle · `dbbuddy_core/pipeline.py`
- [Schema layer](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/schema.py): schema intelligence · Loads database structure and semantic metadata · `dbbuddy_core/schema.py`
- [Semantic matcher](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/semantic_matcher.py): meaning layer · Maps user language to entities, fields, and relationships · `dbbuddy_core/semantic_matcher.py`
- [Planner](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/query_planner.py): deterministic planning · Turns intent into an executable plan · `dbbuddy_core/query_planner.py`
- [Intent builder](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/intent_builder.py): intent model · Builds normalized query intent from input · `dbbuddy_core/intent_builder.py`
- [SQL generator](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/query.py): query compiler · Compiles planned intent into SQL · `dbbuddy_core/query.py`
- [Safety gate](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/system_validation.py): validation · Checks correctness, parameterization, and approval rules · `dbbuddy_core/system_validation.py`
- [Execution](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/execution.py): db runtime · Runs approved queries against the database · `dbbuddy_core/execution.py`
- [Explainability](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/explainability.py): trace layer · Explains planner decisions and query behavior · `dbbuddy_core/explainability.py`
- [Learning](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/learning_engine.py): adaptive memory · Stores feedback and improves semantic guidance · `dbbuddy_core/learning_engine.py`
- [AI labeling](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/ai.py): optional provider · Optional AI assistance for semantic labeling only · `dbbuddy_core/ai.py`

### Support systems

Plugins, tests, and repository operations

- [Database](https://github.com/sxjalxo/dbbuddy/blob/main/dbbuddy_core/db.py): external DB · Live data source queried by the engine · `dbbuddy_core/db.py`
- [Plugins](https://github.com/sxjalxo/dbbuddy/tree/main/dbbuddy_core/plugins): extension boundary · Custom mappings and domain-specific behavior hooks · `dbbuddy_core/plugins`
- [Tests](https://github.com/sxjalxo/dbbuddy/tree/main/tests): validation suite · Regression coverage for behavior, security, and execution · `tests`

## Connections

- Frontend → Backend API: API calls · Frontend consumes backend endpoints
- Backend API → Orchestrator: requests · Backend delegates work to the core orchestrator
- CLI → Orchestrator: commands · CLI invokes the same core orchestration path
- Orchestrator → Pipeline: drives · Top-level coordination enters the query pipeline
- Pipeline → Schema layer: load schema · Retrieves database and semantic structure
- Schema layer → Semantic matcher: semantic context · Schema knowledge feeds language-to-structure matching
- Semantic matcher → AI labeling: labeling help · Optional AI improves semantic labeling
- Semantic matcher → Intent builder: build intent · Semantic matches are converted into normalized intent
- Intent builder → Planner: plan query · Intent is transformed into a deterministic execution plan
- Planner → SQL generator: compile SQL · Planner output is compiled into SQL
- SQL generator → Safety gate: validate · Generated SQL is checked before execution
- Safety gate → Execution: approve · Only safe, valid queries proceed to runtime execution
- Execution → Database: run query · Execution layer talks to the live database
- Execution → Explainability: explain · Execution and planning decisions are summarized for users
- Execution → Learning: feedback · Query outcomes update adaptive learning memory
- Learning → Semantic matcher: refine mappings · Learned associations improve future semantic matching
- Plugins → Semantic matcher: custom rules · Extension hooks can modify mappings and behaviors
- Tests → Pipeline: exercise · Test suites cover the core query lifecycle
- Tests → Safety gate: verify safety · Security and SQL validation are regression-tested