sxjalxo/dbbuddy

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.