# ohadlivay/db-whisperer architecture

> Architecture diagram of the GitHub repository ohadlivay/db-whisperer, 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/ohadlivay/db-whisperer
- Repository: https://github.com/ohadlivay/db-whisperer

## Overview

1. **What this repo is**
   - DB Whisperer is a **Python + Streamlit** application for interactive database exploration.
   - Its core idea is an LLM-assisted workflow: the user asks questions in the UI, and the app routes them through query-generation, ambiguity-resolution, and data-loading services.

2. **Top-level entry point**
   - `app.py` is the deployable entry point used by Streamlit.
   - The main runtime boundary is: **browser UI → Streamlit app → package code under `src/db_whisperer`**.

3. **Presentation layer**
   - `src/db_whisperer/gui/app.py` contains the Streamlit UI logic.
   - `style.css` and `changelog.json` are UI assets, not core business logic.
   - This layer is responsible for session interaction, API key collection, and displaying results/errors.

4. **Application orchestration layer**
   - `src/db_whisperer/application/service.py` is the coordination layer.
   - It likely acts as the controller that decides which subsystem to invoke based on user intent: query a dataset, resolve ambiguity, or perform ETL-related preparation.
   - This is the main internal boundary between the UI and the domain-specific services.

5. **Querying subsystem**
   - `src/db_whisperer/querier/` is the path for “ask a question, get SQL/data back.”
   - It includes:
     - `prompt_builder.py` for constructing model prompts,
     - `openrouter_client.py` for LLM calls,
     - `sql_validator.py` for checking generated SQL,
     - `service.py` for orchestration.
   - Architecturally, this is the path that turns natural language into validated database queries.

6. **Ambiguity-resolution subsystem**
   - `src/db_whisperer/ambiguity/` handles cases where the user’s request is unclear or underspecified.
   - It has its own `prompt_builder.py`, `openrouter_client.py`, and `service.py`, which suggests a separate LLM interaction flow focused on clarification rather than query generation.
   - This is a distinct guardrail layer before the app commits to an SQL answer.

7. **ETL / dataset preparation subsystem**
   - `src/db_whisperer/etler/service.py` handles data loading or transformation concerns.
   - The repo ships with multiple datasets in `data/`, including:
     - relational CSV tables,
     - single CSV files,
     - a MIMIC-III clinical database demo.
   - This indicates the app supports prepared datasets and likely uses an ETL step to make them queryable.

8. **External service boundary**
   - The only clear external runtime dependency in the architecture is **OpenRouter**.
   - Both the ambiguity and querying flows have dedicated OpenRouter clients, so LLM calls are isolated behind subsystem-specific adapters.
   - The README says the API key is sent for the current session and not persisted, which matters for the trust boundary in the UI/session layer.

9. **Shared contracts and cross-cutting concerns**
   - `src/db_whisperer/contracts.py` likely defines shared data structures/interfaces between services.
   - `src/db_whisperer/prompt_logging.py` is a cross-cutting utility for recording prompts or model interactions, useful for traceability/debugging.
   - These pieces sit below the orchestration layer and above the UI/transport layer.

10. **Benchmarking and evaluation**
   - `benchmark/run.py` and `benchmark/cases.json` form a separate evaluation path from the interactive app.
   - This suggests the repo can run repeatable test cases against the same core logic, independent of the Streamlit UI.
   - For an architecture diagram, this is a sibling consumer of the service layer, not part of the main user flow.

11. **Testing structure**
   - `tests/` mirrors the package structure: `ambiguity`, `application`, `etler`, `gui`, and `querier`.
   - There are focused unit tests for prompt builders, OpenRouter clients, services, and UI behavior, plus at least one relational integration test.
   - That mirrors the repo’s modular architecture and confirms the subsystem boundaries.

12. **How to draw the main flow**
   - The primary diagram should show:
     - **User/browser**
     - **Streamlit GUI**
     - **Application service**
     - branching into **Querier**, **Ambiguity**, and **ETL**
     - with **OpenRouter** as the external LLM service
     - and **local dataset files** as the data source backing the querying/ETL path.
   - The benchmark runner should be shown separately as an offline evaluation consumer of the same core services.

Main entry points: [`src/db_whisperer/application/service.py`](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/application/service.py) (Application service), [`src/db_whisperer/querier/service.py`](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/querier/service.py) (Querier service), [`src/db_whisperer/ambiguity/service.py`](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/ambiguity/service.py) (Ambiguity service), [`src/db_whisperer/etler/service.py`](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/etler/service.py) (ETL service)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_g1["Interface"]
  node_n1(("Browser<br/>user/client"))
  node_n2["Streamlit entry<br/>app entrypoint<br/>[app.py]"]
  node_n3["GUI app<br/>presentation layer<br/>[app.py]"]
end

subgraph group_g2["Core"]
  node_n4["Application service<br/>orchestrator<br/>[service.py]"]
  node_n5["Contracts<br/>shared schemas<br/>[contracts.py]"]
  node_n6["Prompt logging<br/>cross-cutting utility<br/>[prompt_logging.py]"]
  node_n7["Querier service<br/>query orchestrator<br/>[service.py]"]
  node_n8["Prompt builder<br/>prompt construction<br/>[prompt_builder.py]"]
  node_n9["SQL validator<br/>query guardrail<br/>[sql_validator.py]"]
  node_n10["Ambiguity service<br/>clarification flow<br/>[service.py]"]
  node_n11["Ambiguity prompts<br/>prompt construction<br/>[prompt_builder.py]"]
  node_n12["ETL service<br/>data prep<br/>[service.py]"]
end

subgraph group_g3["Evaluation"]
  node_n13["Benchmark runner<br/>offline evaluation<br/>[run.py]"]
  node_n14["Benchmark cases<br/>case set<br/>[cases.json]"]
end

subgraph group_g4["Data"]
  node_n16[("Relational data<br/>dataset bundle")]
  node_n17[("Single CSVs<br/>dataset bundle")]
  node_n18[("MIMIC demo<br/>clinical database demo")]
end

subgraph group_g5["External"]
  node_n15(("OpenRouter<br/>llm service"))
end

node_n1 -->|"opens"| node_n2
node_n2 -->|"renders"| node_n3
node_n3 -->|"submits intent"| node_n4
node_n4 -->|"query path"| node_n7
node_n4 -->|"clarify path"| node_n10
node_n4 -->|"prepare data"| node_n12
node_n7 -->|"builds prompt"| node_n8
node_n7 -->|"validates SQL"| node_n9
node_n7 -->|"calls model"| node_n15
node_n10 -->|"builds prompt"| node_n11
node_n10 -->|"calls model"| node_n15
node_n12 -->|"loads"| node_n16
node_n12 -->|"loads"| node_n17
node_n12 -->|"loads"| node_n18
node_n3 -.->|"logs prompts"| node_n6
node_n7 -->|"shares contracts"| node_n5
node_n10 -->|"shares contracts"| node_n5
node_n4 -->|"coordinates via"| node_n5
node_n13 -->|"consumes"| node_n14
node_n13 -->|"exercises"| node_n4

click node_n2 "https://github.com/ohadlivay/db-whisperer/blob/main/app.py"
click node_n3 "https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/gui/app.py"
click node_n4 "https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/application/service.py"
click node_n5 "https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/contracts.py"
click node_n6 "https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/prompt_logging.py"
click node_n7 "https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/querier/service.py"
click node_n8 "https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/querier/prompt_builder.py"
click node_n9 "https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/querier/sql_validator.py"
click node_n10 "https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/ambiguity/service.py"
click node_n11 "https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/ambiguity/prompt_builder.py"
click node_n12 "https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/etler/service.py"
click node_n13 "https://github.com/ohadlivay/db-whisperer/blob/main/benchmark/run.py"
click node_n14 "https://github.com/ohadlivay/db-whisperer/blob/main/benchmark/cases.json"
click node_n16 "https://github.com/ohadlivay/db-whisperer/tree/main/data/relational csv"
click node_n17 "https://github.com/ohadlivay/db-whisperer/tree/main/data/single csv"
click node_n18 "https://github.com/ohadlivay/db-whisperer/blob/main/data/mimic-iii-clinical-database-demo-1.4-20260615T211207Z-3-001/mimic-iii-clinical-database-demo-1.4"

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_n1,node_n2,node_n3 toneBlue
class node_n4,node_n5,node_n6,node_n7,node_n8,node_n9,node_n10,node_n11,node_n12 toneAmber
class node_n13,node_n14 toneMint
class node_n16,node_n17,node_n18 toneRose
class node_n15 toneIndigo
```

## Components

### Interface

User-facing Streamlit entry and UI assets

- **Browser**: user/client · Human interacts with the app through a web browser
- [Streamlit entry](https://github.com/ohadlivay/db-whisperer/blob/main/app.py): app entrypoint · Deployable Streamlit entry module · `app.py`
- [GUI app](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/gui/app.py): presentation layer · Streamlit UI logic, session handling, and result rendering · `src/db_whisperer/gui/app.py`

### Core

Application orchestration and domain services

- [Application service](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/application/service.py): orchestrator · Coordinates intent routing across querying, ambiguity resolution, and ETL · `src/db_whisperer/application/service.py`
- [Contracts](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/contracts.py): shared schemas · Shared data structures and interfaces between subsystems · `src/db_whisperer/contracts.py`
- [Prompt logging](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/prompt_logging.py): cross-cutting utility · Records prompts or model interactions for traceability · `src/db_whisperer/prompt_logging.py`
- [Querier service](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/querier/service.py): query orchestrator · Turns natural language requests into validated database queries · `src/db_whisperer/querier/service.py`
- [Prompt builder](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/querier/prompt_builder.py): prompt construction · Builds the query-generation prompts · `src/db_whisperer/querier/prompt_builder.py`
- [SQL validator](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/querier/sql_validator.py): query guardrail · Checks generated SQL before execution or return · `src/db_whisperer/querier/sql_validator.py`
- [Ambiguity service](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/ambiguity/service.py): clarification flow · Handles underspecified requests and drives clarification · `src/db_whisperer/ambiguity/service.py`
- [Ambiguity prompts](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/ambiguity/prompt_builder.py): prompt construction · Builds clarification prompts for ambiguous user requests · `src/db_whisperer/ambiguity/prompt_builder.py`
- [ETL service](https://github.com/ohadlivay/db-whisperer/blob/main/src/db_whisperer/etler/service.py): data prep · Prepares or loads datasets for querying · `src/db_whisperer/etler/service.py`

### Evaluation

Offline benchmark runner using the core services

- [Benchmark runner](https://github.com/ohadlivay/db-whisperer/blob/main/benchmark/run.py): offline evaluation · Runs repeatable benchmark cases against core logic · `benchmark/run.py`
- [Benchmark cases](https://github.com/ohadlivay/db-whisperer/blob/main/benchmark/cases.json): case set · Serialized benchmark scenarios and expected interactions · `benchmark/cases.json`

### Data

Prepared datasets and database assets used by ETL and querying

- [Relational data](https://github.com/ohadlivay/db-whisperer/tree/HEAD/data/relational%20csv): dataset bundle · Prepared CSV tables for relational querying and ETL · `data/relational csv`
- [Single CSVs](https://github.com/ohadlivay/db-whisperer/tree/HEAD/data/single%20csv): dataset bundle · Standalone CSV inputs used as source data · `data/single csv`
- [MIMIC demo](https://github.com/ohadlivay/db-whisperer/blob/main/data/mimic-iii-clinical-database-demo-1.4-20260615T211207Z-3-001/mimic-iii-clinical-database-demo-1.4): clinical database demo · Demo clinical database dump and schema files · `data/mimic-iii-clinical-database-demo-1.4-20260615T211207Z-3-001/mimic-iii-clinical-database-demo-1.4`

### External

Third-party runtime boundary

- **OpenRouter**: llm service · External LLM API used by querying and ambiguity flows

## Connections

- Browser → Streamlit entry: opens · Browser loads the Streamlit entrypoint
- Streamlit entry → GUI app: renders · Entry module hands off to the GUI layer
- GUI app → Application service: submits intent · UI forwards user actions and session context
- Application service → Querier service: query path · Routes question-answering requests to the querier
- Application service → Ambiguity service: clarify path · Routes underspecified requests to ambiguity handling
- Application service → ETL service: prepare data · Invokes ETL/data-loading preparation when needed
- Querier service → Prompt builder: builds prompt · Creates model prompt for SQL generation
- Querier service → SQL validator: validates SQL · Checks generated SQL before use
- Querier service → OpenRouter: calls model · Uses OpenRouter for query generation
- Ambiguity service → Ambiguity prompts: builds prompt · Creates clarification prompt for ambiguous requests
- Ambiguity service → OpenRouter: calls model · Uses OpenRouter for clarification turns
- ETL service → Relational data: loads · Uses prepared relational tables as queryable data
- ETL service → Single CSVs: loads · Uses standalone CSV sources for preparation
- ETL service → MIMIC demo: loads · Can prepare the clinical demo database assets
- GUI app → Prompt logging: logs prompts · UI-adjacent tracing and debugging support
- Querier service → Contracts: shares contracts · Uses common request/response structures
- Ambiguity service → Contracts: shares contracts · Uses common request/response structures
- Application service → Contracts: coordinates via · Orchestration layer relies on shared contracts
- Benchmark runner → Benchmark cases: consumes · Benchmark cases drive repeatable runs
- Benchmark runner → Application service: exercises · Offline runner uses the same core orchestration