akshay2642005/etl_enrichment_pipline

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.