# aviroop07/scribbledb architecture

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

- Interactive diagram: https://gitdiagram.com/aviroop07/scribbledb
- Repository: https://github.com/aviroop07/scribbledb

## Overview

#### Purpose and Entry Points
NL2DATA converts natural-language database requirements into ER designs, normalized relational schemas, SQLite-valid DDL, query requirements, constraints, and synthetic-data generation plans. Programmatic workflow entry points are `NL2DATA/orchestration/graphs/master.py` and `NL2DATA/orchestration/state.py`; an example caller exists at `NL2DATA/orchestration/example_usage.py`. The deployable application starts in `backend/main.py`; the browser client starts in `front end/src/main.tsx`.

#### Client/API and Job Boundary
The React/TypeScript client submits descriptions, renders checkpoint editors and schema diagrams, and receives live progress through REST plus WebSocket connections. Its application state and transport boundaries are `front end/src/stores/useAppStore.ts`, `front end/src/services/apiService.ts`, and `front end/src/hooks/useWebSocket.ts`. FastAPI routes separate processing, schema/checkpoint persistence, and suggestions in `backend/api/routes/processing.py`, `backend/api/routes/checkpoints.py`, and `backend/api/routes/schema.py`. Long-running work is represented as jobs and broadcast through `backend/utils/job_manager.py` and `backend/utils/websocket_manager.py`.

#### Pipeline Orchestration and State
LangGraph composes a master workflow from per-phase graphs; phase transitions, conditional paths, loops, and shared graph behavior are centered in `NL2DATA/orchestration/graphs/master.py`, `NL2DATA/orchestration/graphs/common.py`, and `NL2DATA/orchestration/graphs/phase1.py`. A cumulative `IRGenerationState` carries the NL input and all derived artifacts across phases in `NL2DATA/orchestration/state.py`. Phase gates validate state at boundaries, while the step registry provides execution metadata and messaging: `NL2DATA/orchestration/phase_gates/gates.py`, `NL2DATA/orchestration/phase_gates/validators.py`, and `NL2DATA/orchestration/step_registry/registry.py`.

#### Discovery and Schema Construction
Phases 1–2 transform prose into a domain, entities, relations, cardinalities, attributes, keys, and early constraints. Representative extraction/consolidation stages are `NL2DATA/phases/phase1/step_1_4_key_entity_extraction.py`, `NL2DATA/phases/phase1/step_1_7_entity_consolidation.py`, and `NL2DATA/phases/phase2/step_2_2_intrinsic_attributes.py`. Phases 3–4 compile those findings first into ER design and then into the canonical relational schema at `NL2DATA/phases/phase3/step_3_1_er_design_compilation.py` and `NL2DATA/phases/phase4/step_4_1_relational_schema_compilation.py`. IR models define this internal boundary in `NL2DATA/ir/models/dataset_ir.py` and `NL2DATA/ir/models/er_relational.py`.

#### Type, DDL, and Query Stages
Phase 5 creates attribute dependency/type decisions, including deterministic foreign-key typing: `NL2DATA/phases/phase5/step_5_1_attribute_dependency_graph.py` and `NL2DATA/phases/phase5/step_5_3_deterministic_fk_data_types.py`. Phase 6 deterministically compiles, validates, and creates the schema in SQLite through `NL2DATA/phases/phase6/step_6_1_ddl_compilation.py`, `NL2DATA/phases/phase6/step_6_2_ddl_validation.py`, and `NL2DATA/phases/phase6/step_6_3_schema_creation.py`. Phase 7 derives information needs and read-only SQL validation in `NL2DATA/phases/phase7/step_7_1_information_need_identification.py` and `NL2DATA/phases/phase7/step_7_2_sql_generation_and_validation.py`.

#### Constraints and Data-Generation Planning
Phase 8 adds functional dependencies, categorical values, enforcement decisions, and conflict-checked constraint specifications; its defining stages are `NL2DATA/phases/phase8/step_8_1_functional_dependency_analysis.py`, `NL2DATA/phases/phase8/step_8_4_constraint_detection.py`, and `NL2DATA/phases/phase8/step_8_8_constraint_compilation.py`. Phase 9 produces attribute distributions, volumes, partitioning, and strategy selections via `NL2DATA/phases/phase9/step_9_6_distribution_compilation.py`, `NL2DATA/phases/phase9/tools/strategy_selection.py`, and `NL2DATA/phases/phase9/strategies/base.py`.

#### External Services and Cross-Cutting Runtime
Python uses LangChain/LangGraph for asynchronous LLM workflow execution, OpenAI models via routing/configuration, SQLite for DDL validation/schema creation, and optional LangSmith tracing. LLM call, retry, tool, and structured-output boundaries reside in `NL2DATA/utils/llm/base_router.py`, `NL2DATA/utils/llm/agent_executor.py`, and `NL2DATA/utils/llm/structured_output.py`; runtime policy is configured in `NL2DATA/config/config.yaml`. The custom derived-attribute DSL is parsed and validated through `NL2DATA/utils/dsl/parser.py` and `NL2DATA/utils/dsl/validator.py`.

Main entry points: [`NL2DATA/orchestration/graphs/master.py`](https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/orchestration/graphs/master.py) (Master LangGraph workflow), [`backend/utils/job_manager.py`](https://github.com/aviroop07/scribbledb/blob/main/backend/utils/job_manager.py) (Jobs & WebSocket broadcast), [`front end/src/stores/useAppStore.ts`](https://github.com/aviroop07/scribbledb/blob/main/front%20end/src/stores/useAppStore.ts) (App store & transport), [`backend/api/routes/processing.py`](https://github.com/aviroop07/scribbledb/blob/main/backend/api/routes/processing.py) (Processing & persistence routes)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_client["Browser Client"]
  node_react_client["React client<br/>TypeScript UI<br/>[main.tsx]"]
  node_client_store["App store &amp; transport<br/>client state<br/>[useAppStore.ts]"]
end

subgraph group_backend["Backend Runtime"]
  node_fastapi["FastAPI application<br/>API server<br/>[main.py]"]
  node_api_routes["Processing &amp; persistence routes<br/>REST routes<br/>[processing.py]"]
  node_jobs["Jobs &amp; WebSocket broadcast<br/>job runtime<br/>[job_manager.py]"]
end

subgraph group_workflow["NL2DATA Workflow"]
  node_master_graph["Master LangGraph workflow<br/>orchestration graph<br/>[master.py]"]
  node_ir_state["IRGenerationState<br/>shared workflow state<br/>[state.py]"]
  node_gates_registry["Phase gates &amp; step registry<br/>workflow controls<br/>[gates.py]"]
  node_discovery["Discovery &amp; schema findings<br/>phases 1–2"]
  node_er_relational["ER and relational models<br/>IR models<br/>[er_relational.py]"]
  node_schema_compilation["ER &amp; relational compilation<br/>phases 3–4"]
  node_type_ddl["Type and DDL pipeline<br/>phases 5–6"]
  node_query_requirements["Query requirements<br/>phase 7"]
  node_constraints["Constraint compilation<br/>phase 8"]
  node_data_plan["Synthetic-data plan<br/>phase 9"]
  node_dsl["Derived-attribute DSL<br/>parser &amp; validator<br/>[parser.py]"]
end

subgraph group_services["External Runtime"]
  node_llm_runtime{{"LLM execution layer<br/>LangChain/OpenAI<br/>[base_router.py]"}}
  node_sqlite[("SQLite<br/>database engine")]
end

node_react_client -->|"uses"| node_client_store
node_client_store -->|"REST requests"| node_api_routes
node_client_store -->|"WebSocket progress"| node_jobs
node_fastapi -->|"serves"| node_api_routes
node_api_routes -->|"creates jobs"| node_jobs
node_jobs -->|"runs workflow"| node_master_graph
node_master_graph -->|"reads and updates"| node_ir_state
node_master_graph -->|"gates phases"| node_gates_registry
node_master_graph -->|"phases 1–2"| node_discovery
node_discovery -->|"schema findings"| node_er_relational
node_master_graph -->|"phases 3–4"| node_schema_compilation
node_schema_compilation -->|"compiles"| node_er_relational
node_master_graph -->|"phases 5–6"| node_type_ddl
node_er_relational -->|"canonical schema"| node_type_ddl
node_type_ddl -->|"validate and create"| node_sqlite
node_master_graph -->|"phase 7"| node_query_requirements
node_master_graph -->|"phase 8"| node_constraints
node_master_graph -->|"phase 9"| node_data_plan
node_master_graph -->|"LLM stages"| node_llm_runtime
node_constraints -->|"validates expressions"| node_dsl
node_jobs -->|"broadcasts progress"| node_react_client

click node_react_client "https://github.com/aviroop07/scribbledb/blob/main/front%20end/src/main.tsx"
click node_client_store "https://github.com/aviroop07/scribbledb/blob/main/front%20end/src/stores/useAppStore.ts"
click node_fastapi "https://github.com/aviroop07/scribbledb/blob/main/backend/main.py"
click node_api_routes "https://github.com/aviroop07/scribbledb/blob/main/backend/api/routes/processing.py"
click node_jobs "https://github.com/aviroop07/scribbledb/blob/main/backend/utils/job_manager.py"
click node_master_graph "https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/orchestration/graphs/master.py"
click node_ir_state "https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/orchestration/state.py"
click node_gates_registry "https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/orchestration/phase_gates/gates.py"
click node_discovery "https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/phases/phase1/step_1_4_key_entity_extraction.py"
click node_er_relational "https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/ir/models/er_relational.py"
click node_schema_compilation "https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/phases/phase4/step_4_1_relational_schema_compilation.py"
click node_type_ddl "https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/phases/phase6/step_6_1_ddl_compilation.py"
click node_query_requirements "https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/phases/phase7/step_7_2_sql_generation_and_validation.py"
click node_constraints "https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/phases/phase8/step_8_8_constraint_compilation.py"
click node_data_plan "https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/phases/phase9/step_9_6_distribution_compilation.py"
click node_llm_runtime "https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/utils/llm/base_router.py"
click node_dsl "https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/utils/dsl/parser.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_react_client,node_client_store toneBlue
class node_fastapi,node_api_routes,node_jobs toneAmber
class node_master_graph,node_ir_state,node_gates_registry,node_discovery,node_er_relational,node_schema_compilation,node_type_ddl,node_query_requirements,node_constraints,node_data_plan,node_dsl toneMint
class node_llm_runtime,node_sqlite toneRose
```

## Components

### Browser Client

- [React client](https://github.com/aviroop07/scribbledb/blob/main/front%20end/src/main.tsx): TypeScript UI · Submits requirements and renders editors, diagrams, and progress. · `front end/src/main.tsx`
- [App store & transport](https://github.com/aviroop07/scribbledb/blob/main/front%20end/src/stores/useAppStore.ts): client state · `front end/src/stores/useAppStore.ts`

### Backend Runtime

- [FastAPI application](https://github.com/aviroop07/scribbledb/blob/main/backend/main.py): API server · `backend/main.py`
- [Processing & persistence routes](https://github.com/aviroop07/scribbledb/blob/main/backend/api/routes/processing.py): REST routes · Processing, checkpoints, schemas, and suggestions endpoints. · `backend/api/routes/processing.py`
- [Jobs & WebSocket broadcast](https://github.com/aviroop07/scribbledb/blob/main/backend/utils/job_manager.py): job runtime · Represents long-running work and streams progress. · `backend/utils/job_manager.py`

### NL2DATA Workflow

- [Master LangGraph workflow](https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/orchestration/graphs/master.py): orchestration graph · Composes phases, transitions, conditional paths, and loops. · `NL2DATA/orchestration/graphs/master.py`
- [IRGenerationState](https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/orchestration/state.py): shared workflow state · Carries input and cumulative artifacts across all phases. · `NL2DATA/orchestration/state.py`
- [Phase gates & step registry](https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/orchestration/phase_gates/gates.py): workflow controls · Validates phase boundaries and supplies execution metadata. · `NL2DATA/orchestration/phase_gates/gates.py`
- [Discovery & schema findings](https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/phases/phase1/step_1_4_key_entity_extraction.py): phases 1–2 · Extracts and consolidates domain entities, relationships, attributes, and keys. · `NL2DATA/phases/phase1/step_1_4_key_entity_extraction.py`
- [ER and relational models](https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/ir/models/er_relational.py): IR models · Internal boundary for ER design and canonical relational schema. · `NL2DATA/ir/models/er_relational.py`
- [ER & relational compilation](https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/phases/phase4/step_4_1_relational_schema_compilation.py): phases 3–4 · Compiles findings into ER design then relational schema. · `NL2DATA/phases/phase4/step_4_1_relational_schema_compilation.py`
- [Type and DDL pipeline](https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/phases/phase6/step_6_1_ddl_compilation.py): phases 5–6 · Determines types, compiles and validates SQLite DDL, then creates schema. · `NL2DATA/phases/phase6/step_6_1_ddl_compilation.py`
- [Query requirements](https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/phases/phase7/step_7_2_sql_generation_and_validation.py): phase 7 · Derives information needs and validates read-only SQL. · `NL2DATA/phases/phase7/step_7_2_sql_generation_and_validation.py`
- [Constraint compilation](https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/phases/phase8/step_8_8_constraint_compilation.py): phase 8 · Builds conflict-checked constraint specifications. · `NL2DATA/phases/phase8/step_8_8_constraint_compilation.py`
- [Synthetic-data plan](https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/phases/phase9/step_9_6_distribution_compilation.py): phase 9 · Compiles distributions, volumes, partitions, and generation strategies. · `NL2DATA/phases/phase9/step_9_6_distribution_compilation.py`
- [Derived-attribute DSL](https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/utils/dsl/parser.py): parser & validator · `NL2DATA/utils/dsl/parser.py`

### External Runtime

- [LLM execution layer](https://github.com/aviroop07/scribbledb/blob/main/NL2DATA/utils/llm/base_router.py): LangChain/OpenAI · Routes calls with retries, tools, and structured outputs. · `NL2DATA/utils/llm/base_router.py`
- **SQLite**: database engine · Validates DDL and hosts created schemas.

## Connections

- React client → App store & transport: uses
- App store & transport → Processing & persistence routes: REST requests
- App store & transport → Jobs & WebSocket broadcast: WebSocket progress
- FastAPI application → Processing & persistence routes: serves
- Processing & persistence routes → Jobs & WebSocket broadcast: creates jobs
- Jobs & WebSocket broadcast → Master LangGraph workflow: runs workflow
- Master LangGraph workflow → IRGenerationState: reads and updates
- Master LangGraph workflow → Phase gates & step registry: gates phases
- Master LangGraph workflow → Discovery & schema findings: phases 1–2
- Discovery & schema findings → ER and relational models: schema findings
- Master LangGraph workflow → ER & relational compilation: phases 3–4
- ER & relational compilation → ER and relational models: compiles
- Master LangGraph workflow → Type and DDL pipeline: phases 5–6
- ER and relational models → Type and DDL pipeline: canonical schema
- Type and DDL pipeline → SQLite: validate and create
- Master LangGraph workflow → Query requirements: phase 7
- Master LangGraph workflow → Constraint compilation: phase 8
- Master LangGraph workflow → Synthetic-data plan: phase 9
- Master LangGraph workflow → LLM execution layer: LLM stages
- Constraint compilation → Derived-attribute DSL: validates expressions
- Jobs & WebSocket broadcast → React client: broadcasts progress