# itsashna/nlp-to-sql-queries architecture

> Architecture diagram of the GitHub repository itsashna/nlp-to-sql-queries, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-11-13). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/itsashna/nlp-to-sql-queries
- Repository: https://github.com/itsashna/nlp-to-sql-queries

## Overview

##### Project type and purpose
- This is a backend “NLP-to-SQL” microservice built with FastAPI, containerized with Docker, deployed on AWS EKS.
- It translates user‐supplied natural language into SQL (via OpenAI GPT), runs that SQL on Amazon Athena against data stored in S3, and returns results as JSON.

##### File structure
Top-level files and directories:
- .github/workflows/ecr-push.yml → CI/CD workflow that builds & pushes to ECR
- Dockerfile, .dockerignore → containerization
- deployment.yaml, service.yaml → Kubernetes manifests for EKS
- buildspec.yml → optional AWS CodeBuild spec
- nlp-agent/ & nlp_agent/ → Python application code (Athena client, NLP→SQL logic, FastAPI main)
- requirements.txt → Python dependencies
- dataset.py, upload_to_s3.py, *.csv → scripts/data to load Northwind into S3/Athena
Patterns & technologies:
- Microservice/API layer (FastAPI)
- Container registry (ECR) + CI/CD (GitHub Actions)
- Kubernetes orchestration (EKS)
- External services (OpenAI API, Athena, S3)

##### README for additional insights
- Sequence: User → FastAPI → OpenAI → Athena → S3 → FastAPI Response
- CI/CD: GitHub Actions triggers on push to main, builds Docker image, pushes to ECR
- Deployment: Kubernetes manifests create Deployment, Service, Load Balancer on EKS
- Security: API keys managed via Kubernetes secrets / config

##### Detail
- Don’t just show “Backend” – break it into “FastAPI application” + “NLP→SQL module” + “Athena client.”
- Separate CI/CD pipeline into “Build” vs. “Deploy” phases.
- Where Docker images live (ECR) and how they’re consumed by EKS.
- How data files (CSV) get into S3 (via upload_to_s3.py/dataset.py).

## Diagram (Mermaid)

```mermaid
flowchart TD
    direction TB

    subgraph "Client Layer"
        U["Client/User"]
    end

    subgraph "Network"
        LB["AWS Load Balancer\n(Service Type=LoadBalancer)"]:::services
    end

    subgraph "AWS EKS Cluster"
        direction TB
        subgraph "Pods"
            FA["FastAPI Pod\n(FastAPI Application)"]:::compute
            FA1["NLP→SQL Module"]:::compute
            FA2["Athena Client"]:::compute
            FA3["Config (Secrets/ConfigMap)"]:::compute
        end
        subgraph "Kubernetes Manifests"
            DEP["deployment.yaml"]:::services
            SER["service.yaml"]:::services
        end
    end

    subgraph "Containerization"
        DF["Dockerfile"]:::compute
        DI[".dockerignore"]:::compute
    end

    subgraph "CI/CD Pipeline"
        direction TB
        GH["GitHub Actions\n(ecr-push.yml)"]:::ci
        CB["AWS CodeBuild\n(buildspec.yml)"]:::ci
        GH -->|"build & test\npush to ECR"| ECR
        CB -->|"optional build"| ECR
    end

    ECR["Amazon ECR"]:::services

    U -->|HTTP Request| LB
    LB -->|routes to| FA
    FA -->|calls| FA1
    FA -->|"calls OpenAI GPT"| OP["OpenAI GPT API"]:::external
    FA -->|"submits SQL"| FA2
    FA2 -->|queries| ATH["Amazon Athena"]:::services
    ATH -->|reads data from| S3["Amazon S3"]:::services
    ATH -->|returns results| FA2
    FA2 -->|returns data| FA
    FA -->|responds JSON| U

    ECR -->|pulls image| FA

    DF -->|defines image| CB
    DI -->|excludes files| CB

    GH -->|triggers| DEP
    GH -->|triggers| SER

    subgraph "Data Ingestion"
        DS["dataset.py"]:::data
        UP["upload_to_s3.py"]:::data
        DS --> UP
        UP -->|uploads CSVs| S3
        UP -->|registers tables| ATH
    end

    subgraph "Northwind Dataset Files"
        CAT["Categories.csv"]:::data
        CUS["Customers.csv"]:::data
        ORD["Orders.csv"]:::data
        PRO["Products.csv"]:::data
    end

    UP --> CAT
    UP --> CUS
    UP --> ORD
    UP --> PRO

    REQ["requirements.txt"]:::data

    click FA "https://github.com/itsashna/nlp-to-sql-queries/blob/main/nlp-agent/main.py"
    click FA1 "https://github.com/itsashna/nlp-to-sql-queries/blob/main/nlp-agent/nlp_to_sql.py"
    click FA2 "https://github.com/itsashna/nlp-to-sql-queries/blob/main/nlp-agent/athena_query.py"
    click FA3 "https://github.com/itsashna/nlp-to-sql-queries/blob/main/nlp-agent/config.py"
    click DF "https://github.com/itsashna/nlp-to-sql-queries/tree/main/Dockerfile"
    click DI "https://github.com/itsashna/nlp-to-sql-queries/blob/main/.dockerignore"
    click GH "https://github.com/itsashna/nlp-to-sql-queries/blob/main/.github/workflows/ecr-push.yml"
    click CB "https://github.com/itsashna/nlp-to-sql-queries/blob/main/buildspec.yml"
    click DEP "https://github.com/itsashna/nlp-to-sql-queries/blob/main/deployment.yaml"
    click SER "https://github.com/itsashna/nlp-to-sql-queries/blob/main/service.yaml"
    click DS "https://github.com/itsashna/nlp-to-sql-queries/blob/main/dataset.py"
    click UP "https://github.com/itsashna/nlp-to-sql-queries/blob/main/upload_to_s3.py"
    click CAT "https://github.com/itsashna/nlp-to-sql-queries/blob/main/Categories.csv"
    click CUS "https://github.com/itsashna/nlp-to-sql-queries/blob/main/Customers.csv"
    click ORD "https://github.com/itsashna/nlp-to-sql-queries/blob/main/Orders.csv"
    click PRO "https://github.com/itsashna/nlp-to-sql-queries/blob/main/Products.csv"
    click REQ "https://github.com/itsashna/nlp-to-sql-queries/blob/main/requirements.txt"

    classDef compute fill:#D0E1F9,stroke:#333,stroke-width:1px;
    classDef services fill:#D5E8D4,stroke:#333,stroke-width:1px;
    classDef external fill:#FFE6CC,stroke:#333,stroke-width:1px;
    classDef ci fill:#FFF2CC,stroke:#333,stroke-width:1px;
    classDef data fill:#E1D5E7,stroke:#333,stroke-width:1px;
```
