# rharish1/schemadrill architecture

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

- Interactive diagram: https://gitdiagram.com/rharish1/schemadrill
- Repository: https://github.com/rharish1/schemadrill

## Overview

SchemaDrill accepts a natural-language database question, retrieves relevant table DDL, generates SQL, applies safety and database validation, and returns bounded query results. Separately, schema ingestion embeds table definitions for retrieval, while evaluation runners compare generated queries against gold SQL using execution, result-set, and AST metrics. The main runtime and ingestion/evaluation entry points are supported by sampled source; some evaluation capabilities and optional integrations are documented but not sampled.

Main entry points: [`app/pipeline.py`](https://github.com/rharish1/schemadrill/blob/main/app/pipeline.py) (Query pipeline), [`eval/evaluator.py`](https://github.com/rharish1/schemadrill/blob/main/eval/evaluator.py) (SQL pair evaluator), [`app/retrieval.py`](https://github.com/rharish1/schemadrill/blob/main/app/retrieval.py) (Schema retrieval), [`eval/run_benchmark.py`](https://github.com/rharish1/schemadrill/blob/main/eval/run_benchmark.py) (Prompt benchmark runner)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_request["Query workflow"]
  node_api["FastAPI query API<br/>[main.py]"]
  node_pipeline["Query pipeline<br/>[pipeline.py]"]
  node_prompting["Prompt and feedback<br/>[prompting.py]"]
  node_generator["Gemini client<br/>[llm_client.py]"]
end

subgraph group_schema["Schema retrieval"]
  node_retrieval["Schema retrieval<br/>[retrieval.py]"]
  node_embedding["Text embeddings<br/>[embedding.py]"]
  node_ingestion["Schema ingestion<br/>[schema_ingest.py]"]
end

subgraph group_safety["SQL safety and execution"]
  node_guard["SQL guard<br/>[sql_guard.py]"]
  node_executor["SQL validation and execution<br/>[executor.py]"]
end

subgraph group_evaluation["Evaluation"]
  node_benchmark["Prompt benchmark runner<br/>[run_benchmark.py]"]
  node_dataset_runner["Dataset evaluation runner<br/>[runner.py]"]
  node_evaluator["SQL pair evaluator<br/>[evaluator.py]"]
  node_judge["SQL AST judge<br/>[sql_judge.py]"]
  node_comparator["Result-set comparator<br/>[result_compare.py]"]
end

node_client(("Client"))
node_gemini["Gemini API"]
node_postgres[("PostgreSQL and pgvector")]
node_questions["Questions and gold SQL"]

node_client -->|"submits question"| node_api
node_api -->|"runs query"| node_pipeline
node_pipeline -->|"retrieves schema"| node_retrieval
node_retrieval -->|"embeds question"| node_embedding
node_retrieval -->|"searches vectors"| node_postgres
node_retrieval -.->|"reads FK neighbors and DDL"| node_postgres
node_pipeline -->|"builds retry messages"| node_prompting
node_pipeline -->|"requests SQL"| node_generator
node_generator -->|"generates SQL"| node_gemini
node_pipeline -->|"checks SQL"| node_guard
node_pipeline -->|"explains and executes"| node_executor
node_executor -->|"runs bounded queries"| node_postgres
node_pipeline -->|"returns query result"| node_api
node_ingestion -->|"reads table catalog; writes DDL vectors"| node_postgres
node_ingestion -->|"embeds table DDL"| node_embedding
node_questions -->|"supplies benchmark cases"| node_benchmark
node_benchmark -->|"runs each question"| node_pipeline
node_benchmark -->|"evaluates gold and generated SQL"| node_evaluator
node_dataset_runner -->|"delegates dataset run"| node_benchmark
node_dataset_runner -->|"runs gold-SQL smoke checks"| node_evaluator
node_evaluator -->|"executes SQL pairs"| node_postgres
node_evaluator -->|"compares SQL ASTs"| node_judge
node_evaluator -->|"compares result sets"| node_comparator

click node_api "https://github.com/rharish1/schemadrill/blob/main/app/main.py"
click node_pipeline "https://github.com/rharish1/schemadrill/blob/main/app/pipeline.py"
click node_prompting "https://github.com/rharish1/schemadrill/blob/main/app/prompting.py"
click node_generator "https://github.com/rharish1/schemadrill/blob/main/app/llm_client.py"
click node_retrieval "https://github.com/rharish1/schemadrill/blob/main/app/retrieval.py"
click node_embedding "https://github.com/rharish1/schemadrill/blob/main/app/embedding.py"
click node_ingestion "https://github.com/rharish1/schemadrill/blob/main/ingest/schema_ingest.py"
click node_guard "https://github.com/rharish1/schemadrill/blob/main/app/sql_guard.py"
click node_executor "https://github.com/rharish1/schemadrill/blob/main/app/executor.py"
click node_benchmark "https://github.com/rharish1/schemadrill/blob/main/eval/run_benchmark.py"
click node_dataset_runner "https://github.com/rharish1/schemadrill/blob/main/evaluation/runner.py"
click node_evaluator "https://github.com/rharish1/schemadrill/blob/main/eval/evaluator.py"
click node_judge "https://github.com/rharish1/schemadrill/blob/main/app/sql_judge.py"
click node_comparator "https://github.com/rharish1/schemadrill/blob/main/eval/result_compare.py"

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_api,node_pipeline,node_prompting,node_generator,node_client toneBlue
class node_retrieval,node_embedding,node_ingestion,node_postgres toneAmber
class node_guard,node_executor,node_gemini toneMint
class node_benchmark,node_dataset_runner,node_evaluator,node_judge,node_comparator toneRose
class node_questions toneIndigo
```

## Components

### Query workflow

- [FastAPI query API](https://github.com/rharish1/schemadrill/blob/main/app/main.py): `app/main.py`
- [Query pipeline](https://github.com/rharish1/schemadrill/blob/main/app/pipeline.py): `app/pipeline.py`
- [Prompt and feedback](https://github.com/rharish1/schemadrill/blob/main/app/prompting.py): `app/prompting.py`
- [Gemini client](https://github.com/rharish1/schemadrill/blob/main/app/llm_client.py): `app/llm_client.py`

### Schema retrieval

- [Schema retrieval](https://github.com/rharish1/schemadrill/blob/main/app/retrieval.py): `app/retrieval.py`
- [Text embeddings](https://github.com/rharish1/schemadrill/blob/main/app/embedding.py): `app/embedding.py`
- [Schema ingestion](https://github.com/rharish1/schemadrill/blob/main/ingest/schema_ingest.py): `ingest/schema_ingest.py`

### SQL safety and execution

- [SQL guard](https://github.com/rharish1/schemadrill/blob/main/app/sql_guard.py): `app/sql_guard.py`
- [SQL validation and execution](https://github.com/rharish1/schemadrill/blob/main/app/executor.py): `app/executor.py`

### Evaluation

- [Prompt benchmark runner](https://github.com/rharish1/schemadrill/blob/main/eval/run_benchmark.py): `eval/run_benchmark.py`
- [Dataset evaluation runner](https://github.com/rharish1/schemadrill/blob/main/evaluation/runner.py): `evaluation/runner.py`
- [SQL pair evaluator](https://github.com/rharish1/schemadrill/blob/main/eval/evaluator.py): `eval/evaluator.py`
- [SQL AST judge](https://github.com/rharish1/schemadrill/blob/main/app/sql_judge.py): `app/sql_judge.py`
- [Result-set comparator](https://github.com/rharish1/schemadrill/blob/main/eval/result_compare.py): `eval/result_compare.py`

### Other components

- **Client**
- **Gemini API**
- **PostgreSQL and pgvector**
- **Questions and gold SQL**

## Connections

- Client → FastAPI query API: submits question
- FastAPI query API → Query pipeline: runs query
- Query pipeline → Schema retrieval: retrieves schema
- Schema retrieval → Text embeddings: embeds question
- Schema retrieval → PostgreSQL and pgvector: searches vectors
- Schema retrieval → PostgreSQL and pgvector: reads FK neighbors and DDL
- Query pipeline → Prompt and feedback: builds retry messages
- Query pipeline → Gemini client: requests SQL
- Gemini client → Gemini API: generates SQL
- Query pipeline → SQL guard: checks SQL
- Query pipeline → SQL validation and execution: explains and executes
- SQL validation and execution → PostgreSQL and pgvector: runs bounded queries
- Query pipeline → FastAPI query API: returns query result
- Schema ingestion → PostgreSQL and pgvector: reads table catalog; writes DDL vectors
- Schema ingestion → Text embeddings: embeds table DDL
- Questions and gold SQL → Prompt benchmark runner: supplies benchmark cases
- Prompt benchmark runner → Query pipeline: runs each question
- Prompt benchmark runner → SQL pair evaluator: evaluates gold and generated SQL
- Dataset evaluation runner → Prompt benchmark runner: delegates dataset run
- Dataset evaluation runner → SQL pair evaluator: runs gold-SQL smoke checks
- SQL pair evaluator → PostgreSQL and pgvector: executes SQL pairs
- SQL pair evaluator → SQL AST judge: compares SQL ASTs
- SQL pair evaluator → Result-set comparator: compares result sets