# roy2392/txt2sql-gemini architecture

> Architecture diagram of the GitHub repository roy2392/txt2sql-gemini, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-01-07). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/roy2392/txt2sql-gemini
- Repository: https://github.com/roy2392/txt2sql-gemini

## Overview

##### Main Components

a) Client Layer:
- External clients making HTTP requests
- Supported endpoints (/health, /query)

b) Application Layer:
- FastAPI Service (src/api/)
- Route handlers (src/api/routes.py)
- Data models (src/api/models.py)

c) Processing Layer:
- SQL Generator Module (src/utils/sql_generator.py)
- Gemini LLM Client (src/llm/gemini_client.py)
- Database Manager (src/database/db_manager.py)

d) Data Layer:
- PostgreSQL Database
- Initialization scripts (docker/init.sql)

e) Infrastructure Layer:
- Docker containers
- AWS Infrastructure (managed via Terraform)

##### Component Relationships and Data Flow

**1. Client → FastAPI Service**
- HTTP POST /query with natural language question
- HTTP GET /health for monitoring

**2. FastAPI Service → SQL Generator**
- Passes natural language query
- Receives generated SQL query

**3. SQL Generator ↔ Gemini LLM**
- Sends prompt for SQL generation
- Receives SQL query and explanation

**4. Database Manager ↔ PostgreSQL**
- Executes SQL queries
- Retrieves query results

**5. FastAPI Service → Client**
- Returns JSON response with:
- * Generated SQL query
- * Query results
- * Natural language explanation

##### Infrastructure Details

- Docker container architecture
- API container
- PostgreSQL container
- Network connections
- AWS infrastructure components (from Terraform)
- IAM roles
- EC2 instances
- Network configuration

##### Additional Elements

Supplementary information:
- Environment variables section
- Health monitoring flow
- Error handling paths
- Testing coverage indicators

##### Recommendations

- Create multiple views if needed (runtime view, deployment view)
- Version information and date
- Document any assumptions or constraints

Remember to emphasize the unique aspects of this project: the natural language processing capabilities, the SQL generation workflow, and the integration with Google's Gemini LLM.

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Styles and classes
    classDef client fill:#FF9900,stroke:#333,stroke-width:2px
    classDef api fill:#2496ED,stroke:#333,stroke-width:2px
    classDef database fill:#4CAF50,stroke:#333,stroke-width:2px
    classDef llm fill:#9C27B0,stroke:#333,stroke-width:2px
    classDef infra fill:#78909C,stroke:#333,stroke-width:2px
    
    %% Client Layer
    subgraph ClientLayer["Client Layer"]
        Client["External Clients"]:::client
        Endpoints["/health, /query endpoints"]:::client
    end

    %% Application Layer
    subgraph AppLayer["Application Layer"]
        FastAPI["FastAPI Service"]:::api
        Routes["Route Handlers"]:::api
        Models["Data Models"]:::api
    end

    %% Processing Layer
    subgraph ProcessLayer["Processing Layer"]
        SQLGen["SQL Generator"]:::api
        GeminiLLM["Gemini LLM Client"]:::llm
        DBManager["Database Manager"]:::api
    end

    %% Data Layer
    subgraph DataLayer["Data Layer"]
        PostgreSQL[(PostgreSQL)]:::database
        DBInit["Init Scripts"]:::database
    end

    %% Infrastructure Layer
    subgraph InfraLayer["Infrastructure Layer"]
        Docker["Docker Containers"]:::infra
        AWS["AWS Infrastructure"]:::infra
        IAM["IAM Roles"]:::infra
        EC2["EC2 Instances"]:::infra
    end

    %% Relationships
    Client --> Endpoints
    Endpoints --> FastAPI
    FastAPI --> Routes
    Routes --> Models
    Routes --> SQLGen
    SQLGen <--> GeminiLLM
    Routes --> DBManager
    DBManager <--> PostgreSQL
    DBInit --> PostgreSQL
    
    %% Infrastructure Relationships
    Docker --> FastAPI
    Docker --> PostgreSQL
    AWS --> EC2
    AWS --> IAM

    %% Click Events
    click FastAPI "https://github.com/roy2392/txt2sql-gemini/blob/main/src/main.py"
    click Routes "https://github.com/roy2392/txt2sql-gemini/blob/main/src/api/routes.py"
    click Models "https://github.com/roy2392/txt2sql-gemini/blob/main/src/api/models.py"
    click SQLGen "https://github.com/roy2392/txt2sql-gemini/blob/main/src/utils/sql_generator.py"
    click GeminiLLM "https://github.com/roy2392/txt2sql-gemini/blob/main/src/llm/gemini_client.py"
    click DBManager "https://github.com/roy2392/txt2sql-gemini/blob/main/src/database/db_manager.py"
    click DBInit "https://github.com/roy2392/txt2sql-gemini/blob/main/docker/init.sql"
    click Docker "https://github.com/roy2392/txt2sql-gemini/tree/main/docker"
    click AWS "https://github.com/roy2392/txt2sql-gemini/tree/main/terraform"
    click IAM "https://github.com/roy2392/txt2sql-gemini/blob/main/terraform/iam.tf"

    %% Legend
    subgraph Legend
        Client_L["Client Components"]:::client
        API_L["API Components"]:::api
        DB_L["Database Components"]:::database
        LLM_L["LLM Services"]:::llm
        Infra_L["Infrastructure"]:::infra
    end
```
