# akshay2642005/etl_enrichment_pipline architecture

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

- Interactive diagram: https://gitdiagram.com/akshay2642005/etl_enrichment_pipline
- Repository: https://github.com/akshay2642005/etl_enrichment_pipline

## Overview

1. **Overall shape**
   - This repo is a two-part system: a **Python backend** that extracts and enriches database schema metadata, and a **React/TypeScript frontend** that visualizes the resulting schema intelligence.
   - The backend can run as either a **CLI pipeline** or a **FastAPI service**; the frontend is a separate Vite app that consumes backend APIs.

2. **Primary backend entrypoints**
   - `main.py` is the top-level launcher for both modes: `api` and `pipeline`.
   - The API lives under `src/etl_enrichment_pipeline/api/main.py` and exposes health/enrichment endpoints.
   - The CLI path accepts a raw metadata JSON file, a SQL DDL file, or a named database connection from config, then writes enriched output to `output/enriched_metadata.json`.

3. **Pipeline core**
   - `src/etl_enrichment_pipeline/core/pipeline.py` is the orchestration layer for the enrichment workflow.
   - The pipeline is stateful: it passes a shared pipeline state object through a sequence of agent nodes, rather than performing one monolithic transform.
   - Supporting core modules handle LLM access, embeddings, vector storage, graph storage, SQL validation, context assembly, and persistence/loading of intermediate artifacts.

4. **Agent-based enrichment stages**
   - The `agents/` package contains the domain-specific nodes that do the actual work: extraction, DDL parsing, consolidated enrichment, pattern detection, quality checks, validation, insights generation, rule application, and NL2SQL generation.
   - The architecture separates **structural understanding** of the database from **semantic enrichment** and **output validation**, which makes the pipeline easier to extend or swap step-by-step.

5. **Data model boundary**
   - `models/` defines the canonical shapes used across the system: connection schema, canonical metadata, final output, and pipeline state.
   - This is the main contract boundary between extraction, enrichment, API responses, and frontend consumption.
   - The repository appears to normalize multiple input sources into a shared enriched schema model before presenting or persisting results.

6. **Rules and domain knowledge**
   - `src/etl_enrichment_pipeline/rules/` contains YAML rule packs for semantic types, PII, and pattern detection.
   - These rules act as deterministic overlays on top of LLM-driven enrichment, so the pipeline combines **symbolic rules** with **model-generated context**.
   - This is a significant architectural boundary: rule files encode stable business logic, while agents and LLMs fill in inferred metadata.

7. **Connection and source ingestion**
   - The backend supports multiple source types through configuration in `config/*.yaml`: SQLite, Postgres, MySQL, MariaDB, Oracle, and SQL Server.
   - The ingestion path can start from:
     - a live database connection,
     - a SQL DDL file,
     - or a pre-parsed/raw metadata JSON file.
   - That means the system has a source-adapter boundary before enrichment begins.

8. **API layer responsibilities**
   - The FastAPI layer is thin and delegates to services in `api/`.
   - Service modules are split by concern: connection handling, extraction, insights, NL2SQL, and quality.
   - `shared_state.py` suggests the API coordinates a shared in-memory or session-scoped workflow state across requests or multi-step interactions.

9. **Storage and artifacts**
   - The project persists intermediate and final artifacts in `output/` and has sample inputs in `sql_json/`.
   - Core persistence helpers include vector and graph stores, which indicate the pipeline is not purely stateless; it maintains embeddings/relationships for retrieval and context building.
   - This suggests a hybrid architecture: structured pipeline state plus auxiliary semantic stores.

10. **Frontend architecture**
   - The frontend is a standalone Vite React app in `frontend/` with pages for connection setup, dashboard, schema exploration, insights, and an SQL agent view.
   - UI components include database selection, table/view detail modals, ER diagrams, interactive graphs, and table explorers, so the frontend is primarily a **schema intelligence visualization layer** rather than a general-purpose app.
   - `src/services/api.ts` is the boundary to the backend, while `store/useAppStore.ts` holds UI/application state.

11. **Frontend-to-backend data flow**
   - The UI likely starts with database selection or uploaded schema data, calls backend endpoints to enrich or fetch metadata, then renders the resulting schema graph, tables, and insights.
   - The ER/graph components indicate the backend output is consumed as a relationship-rich schema graph, not just flat JSON.

12. **LLM and retrieval integration**
   - `core/llm.py`, `embedding_service.py`, `vector_store.py`, and `context_builder.py` show the system is LLM-centric but retrieval-aware.
   - The pipeline likely builds context from schema metadata, vector similarity, and graph relationships before sending prompts to the configured model provider.
   - README defaults to an OpenAI-compatible Ollama setup, but the `.env.template` implies provider abstraction across OpenAI, Groq, OpenRouter, and Azure.

13. **Testing and maturity signals**
   - There is unit coverage for context building, embeddings, graph storage, NL2SQL generation, SQL validation, and vector storage, plus integration tests for the pipeline and API.
   - That test split mirrors the architecture: core algorithmic services are unit-tested, while the end-to-end pipeline is integration-tested.
   - The README note that the repo is archival/retired is operationally relevant but does not change the runtime architecture.

14. **What to draw in an architecture diagram**
   - Show three main zones: **inputs/source systems**, **Python enrichment backend**, and **React visualization frontend**.
   - Inside the backend, show a flow from source ingestion/config → extraction/DDL parsing → canonical model → enrichment agents/rules/LLM → validation/quality → final enriched metadata + semantic stores.
   - Connect the frontend only to the API layer, and treat the vector/graph stores and output artifacts as backend-side supporting persistence, not as primary user-facing components.

Main entry points: [`src/etl_enrichment_pipeline/api/main.py`](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/api/main.py) (API app), [`src/etl_enrichment_pipeline/core/pipeline.py`](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/core/pipeline.py) (Pipeline), [`src/etl_enrichment_pipeline/agents/extraction_agent.py`](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/agents/extraction_agent.py) (Extract), [`src/etl_enrichment_pipeline/models/canonical.py`](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/models/canonical.py) (Canonical model)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_inputs["Inputs & sources"]
  node_source_config["Source config<br/>connection adapters<br/>[config_global.py]"]
  node_raw_inputs["Raw inputs<br/>schema artifacts<br/>[sample_schema.sql]"]
  node_pattern_rules["Rules<br/>yaml rule packs"]
  node_final_output["Enriched output<br/>artifact"]
end

subgraph group_backend["Python backend"]
  node_main_py(("Launcher<br/>entrypoint<br/>[main.py]"))
  node_api_main(("API app<br/>FastAPI service<br/>[main.py]"))
  node_pipeline_core["Pipeline<br/>orchestrator<br/>[pipeline.py]"]
  node_orchestrator_core["Orchestrator<br/>control layer<br/>[orchestrator.py]"]
  node_connection_service["Connections<br/>API service"]
  node_extraction_service["Extraction API<br/>API service"]
  node_shared_state["Shared state<br/>session state<br/>[shared_state.py]"]
  node_extraction_agent["Extract<br/>agent"]
  node_ddl_parser["DDL parser<br/>agent<br/>[ddl_parser.py]"]
  node_canonical_model["Canonical model<br/>data model<br/>[canonical.py]"]
  node_enrichment_agent["Enrichment<br/>agent"]
  node_quality_validation["Validate<br/>quality checks<br/>[quality_agent.py]"]
  node_insights_nl2sql["Insights & SQL<br/>generation agents<br/>[insights_agent.py]"]
  node_llm_context["LLM context<br/>core services<br/>[llm.py]"]
  node_stores[("Semantic stores<br/>vector/graph storage<br/>[vector_store.py]")]
end

subgraph group_frontend["React frontend"]
  node_frontend_app(("React app<br/>Vite frontend<br/>[main.tsx]"))
  node_api_client["API client<br/>service boundary<br/>[api.ts]"]
  node_schema_views["Schema views<br/>UI screens<br/>[DashboardView.tsx]"]
  node_graph_ui["Graph UI<br/>visualization components"]
end

node_main_py -->|"starts API"| node_api_main
node_main_py -->|"starts pipeline"| node_pipeline_core
node_source_config -->|"selects source"| node_connection_service
node_raw_inputs -->|"feeds input"| node_extraction_agent
node_connection_service -->|"stores state"| node_shared_state
node_extraction_service -->|"invokes"| node_extraction_agent
node_extraction_agent -->|"parses ddl"| node_ddl_parser
node_ddl_parser -->|"normalizes"| node_canonical_model
node_extraction_agent -->|"emits"| node_canonical_model
node_canonical_model -->|"enters pipeline"| node_pipeline_core
node_pipeline_core -->|"coordinates"| node_orchestrator_core
node_pipeline_core -->|"runs"| node_enrichment_agent
node_pattern_rules -->|"applies rules"| node_enrichment_agent
node_llm_context -->|"supplies context"| node_enrichment_agent
node_stores -->|"retrieves context"| node_llm_context
node_enrichment_agent -->|"checks"| node_quality_validation
node_quality_validation -->|"enables"| node_insights_nl2sql
node_quality_validation -->|"writes"| node_final_output
node_pipeline_core -->|"persists"| node_final_output
node_api_main -->|"exposes"| node_insights_nl2sql
node_api_main -->|"exposes"| node_extraction_service
node_api_main -->|"exposes"| node_connection_service
node_frontend_app -->|"uses"| node_api_client
node_api_client -->|"calls"| node_api_main
node_api_client -->|"drives"| node_schema_views
node_schema_views -->|"renders"| node_graph_ui
node_graph_ui -->|"consumes"| node_canonical_model

click node_main_py "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/main.py"
click node_api_main "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/api/main.py"
click node_pipeline_core "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/core/pipeline.py"
click node_orchestrator_core "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/core/orchestrator.py"
click node_source_config "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/config/config_global.py"
click node_raw_inputs "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/sql_json/sample_schema.sql"
click node_connection_service "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/api/connection_service.py"
click node_extraction_service "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/api/extraction_service.py"
click node_shared_state "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/api/shared_state.py"
click node_extraction_agent "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/agents/extraction_agent.py"
click node_ddl_parser "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/agents/ddl_parser.py"
click node_canonical_model "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/models/canonical.py"
click node_enrichment_agent "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/agents/consolidated_enrichment_agent.py"
click node_pattern_rules "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/rules/semantic_type_rules.yaml"
click node_quality_validation "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/agents/quality_agent.py"
click node_insights_nl2sql "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/agents/insights_agent.py"
click node_llm_context "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/core/llm.py"
click node_stores "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/core/vector_store.py"
click node_final_output "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/output/enriched_metadata.json"
click node_frontend_app "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/frontend/src/main.tsx"
click node_api_client "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/frontend/src/services/api.ts"
click node_schema_views "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/frontend/src/pages/DashboardView.tsx"
click node_graph_ui "https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/frontend/src/components/diagram/InteractiveGraph.tsx"

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_source_config,node_raw_inputs,node_pattern_rules,node_final_output toneBlue
class node_main_py,node_api_main,node_pipeline_core,node_orchestrator_core,node_connection_service,node_extraction_service,node_shared_state,node_extraction_agent,node_ddl_parser,node_canonical_model,node_enrichment_agent,node_quality_validation,node_insights_nl2sql,node_llm_context,node_stores toneAmber
class node_frontend_app,node_api_client,node_schema_views,node_graph_ui toneMint
```

## Components

### Inputs & sources

Database connections, raw schema files, and persisted artifacts that feed the backend.

- [Source config](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/config/config_global.py): connection adapters · Database-specific connection presets and source selection configuration. · `config/config_global.py`
- [Raw inputs](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/sql_json/sample_schema.sql): schema artifacts · DDL, raw metadata JSON, and sample database inputs used to start enrichment. · `sql_json/sample_schema.sql`
- [Rules](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/rules/semantic_type_rules.yaml): yaml rule packs · Deterministic overlays for semantic types, PII, and pattern detection. · `src/etl_enrichment_pipeline/rules/semantic_type_rules.yaml`
- [Enriched output](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/output/enriched_metadata.json): artifact · Persisted enriched metadata written by the pipeline for downstream use. · `output/enriched_metadata.json`

### Python backend

CLI pipeline, API surface, orchestration, agents, models, and supporting services.

- [Launcher](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/main.py): entrypoint · Top-level mode switch for API and pipeline execution. · `main.py`
- [API app](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/api/main.py): FastAPI service · Thin HTTP service exposing health, extraction, insights, NL2SQL, and quality workflows. · `src/etl_enrichment_pipeline/api/main.py`
- [Pipeline](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/core/pipeline.py): orchestrator · Stateful enrichment workflow that sequences agent nodes over shared pipeline state. · `src/etl_enrichment_pipeline/core/pipeline.py`
- [Orchestrator](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/core/orchestrator.py): control layer · Coordinates execution and higher-level workflow control around the pipeline. · `src/etl_enrichment_pipeline/core/orchestrator.py`
- [Connections](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/api/connection_service.py): API service · API-side connection handling and source setup for workflows. · `src/etl_enrichment_pipeline/api/connection_service.py`
- [Extraction API](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/api/extraction_service.py): API service · API-side extraction entrypoint that bridges requests into backend processing. · `src/etl_enrichment_pipeline/api/extraction_service.py`
- [Shared state](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/api/shared_state.py): session state · Shared in-memory workflow state used by API requests and multi-step interactions. · `src/etl_enrichment_pipeline/api/shared_state.py`
- [Extract](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/agents/extraction_agent.py): agent · Reads source schemas and normalizes them into canonical metadata. · `src/etl_enrichment_pipeline/agents/extraction_agent.py`
- [DDL parser](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/agents/ddl_parser.py): agent · Parses SQL DDL into structured schema metadata. · `src/etl_enrichment_pipeline/agents/ddl_parser.py`
- [Canonical model](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/models/canonical.py): data model · Shared schema contract used across extraction, enrichment, API responses, and the frontend. · `src/etl_enrichment_pipeline/models/canonical.py`
- [Enrichment](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/agents/consolidated_enrichment_agent.py): agent · Combines LLM, rules, and schema context into enriched metadata. · `src/etl_enrichment_pipeline/agents/consolidated_enrichment_agent.py`
- [Validate](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/agents/quality_agent.py): quality checks · Quality, validation, and SQL safety checks on enriched output. · `src/etl_enrichment_pipeline/agents/quality_agent.py`
- [Insights & SQL](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/agents/insights_agent.py): generation agents · Produces schema insights and natural-language-to-SQL assistance. · `src/etl_enrichment_pipeline/agents/insights_agent.py`
- [LLM context](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/core/llm.py): core services · Model access, embeddings, retrieval context, and validation utilities. · `src/etl_enrichment_pipeline/core/llm.py`
- [Semantic stores](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/src/etl_enrichment_pipeline/core/vector_store.py): vector/graph storage · Vector and graph persistence used to retain relationships and retrieval context. · `src/etl_enrichment_pipeline/core/vector_store.py`

### React frontend

Standalone Vite app for schema exploration and insights visualization.

- [React app](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/frontend/src/main.tsx): Vite frontend · Standalone UI shell for schema intelligence visualization. · `frontend/src/main.tsx`
- [API client](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/frontend/src/services/api.ts): service boundary · Frontend boundary for backend communication. · `frontend/src/services/api.ts`
- [Schema views](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/frontend/src/pages/DashboardView.tsx): UI screens · Connection, dashboard, schema, insights, and SQL agent pages. · `frontend/src/pages/DashboardView.tsx`
- [Graph UI](https://github.com/akshay2642005/etl_enrichment_pipline/blob/main/frontend/src/components/diagram/InteractiveGraph.tsx): visualization components · ER diagram, interactive graph, and table/detail views for schema exploration. · `frontend/src/components/diagram/InteractiveGraph.tsx`

## Connections

- Launcher → API app: starts API · Launches the FastAPI service mode.
- Launcher → Pipeline: starts pipeline · Launches the CLI enrichment workflow.
- Source config → Connections: selects source · Database presets and connection details drive API-side setup.
- Raw inputs → Extract: feeds input · DDL and raw schema artifacts are ingested into extraction.
- Connections → Shared state: stores state · Connection setup contributes to shared workflow state.
- Extraction API → Extract: invokes · API extraction requests trigger backend extraction logic.
- Extract → DDL parser: parses ddl · DDL parser turns SQL definitions into structured schema data.
- DDL parser → Canonical model: normalizes · Parsed schema is shaped into the canonical contract.
- Extract → Canonical model: emits · Extraction produces canonical metadata for downstream steps.
- Canonical model → Pipeline: enters pipeline · Canonical metadata becomes the shared workflow input.
- Pipeline → Orchestrator: coordinates · Pipeline execution is managed by the orchestration layer.
- Pipeline → Enrichment: runs · Core workflow passes state into enrichment stages.
- Rules → Enrichment: applies rules · Rule packs overlay deterministic business logic on enrichment.
- LLM context → Enrichment: supplies context · LLM, embeddings, and retrieval context support enrichment.
- Semantic stores → LLM context: retrieves context · Vector and graph stores feed context assembly.
- Enrichment → Validate: checks · Enriched metadata is validated and quality-checked.
- Validate → Insights & SQL: enables · Validated schema intelligence supports insights and NL2SQL generation.
- Validate → Enriched output: writes · Validated output is persisted as enriched metadata.
- Pipeline → Enriched output: persists · Pipeline stores final artifacts under output/.
- API app → Insights & SQL: exposes · API routes delegate to insights and NL2SQL services.
- API app → Extraction API: exposes · API routes delegate extraction concerns to services.
- API app → Connections: exposes · API routes expose connection management.
- React app → API client: uses · Frontend UI communicates through a backend service client.
- API client → API app: calls · Frontend requests hit the FastAPI backend.
- API client → Schema views: drives · Fetched metadata populates schema and insights screens.
- Schema views → Graph UI: renders · Views display relationships through diagrams and table explorers.
- Graph UI → Canonical model: consumes · Visualization is based on the canonical enriched schema contract.