# crushbank-inc/text2sql-app architecture

> Architecture diagram of the GitHub repository crushbank-inc/text2sql-app, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-03-06). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/crushbank-inc/text2sql-app
- Repository: https://github.com/crushbank-inc/text2sql-app

## Overview

##### Identify the Project Type and Purpose

**Project Type:**
The Watsonx Text to SQL Application is a **full-stack application** that provides APIs for converting natural language text into SQL queries using watsonx.ai. It involves backend services, API endpoints, deployment configurations, and integration with external services like watsonx.ai and databases.

**Purpose:**
The application enables users to input natural language queries, which are then classified and converted into executable SQL statements. These SQL queries are run against connected databases, facilitating effortless data retrieval and interaction.

##### Analyze the File Structure

**Top-Level Directories and Files:**
- `.gitignore`, `.DS_Store`, `README.md`: Standard repository files.
- `application/`: Contains the core application code, including `app.py`, Docker configurations, custom types, environment variables, and dependencies.
- `assets/`: Presumably holds static assets, though specifics are not detailed.
- `code-engine-setup/`, `openshift-setup/`, `watsonx-assistant-setup/`: Deployment and integration setups for different platforms.
- `prompts/`: Houses various prompt templates for different functionalities.
- `dynamic_templates.md`: Documentation on handling dynamic prompt templates.
- `watsonx-data/`: Contains data ingestion scripts and SQL statements for setting up database tables.

**Architectural Patterns and Choices:**
- **Microservices Architecture:** Deployment configurations for both OpenShift and IBM Code Engine suggest a containerized, scalable microservices approach.
- **MVC Pattern:** The presence of `app.py` and separation of concerns (e.g., `customTypes`) indicate a Model-View-Controller-like structure, focusing on handling API requests and responses.
- **Configuration Management:** Use of environment variables and Docker for containerization demonstrates best practices in configuration and deployment management.

**Configuration and Deployment Files:**
- `Dockerfile`: Defines the Docker image for the application.
- `requirements.txt`: Lists Python dependencies.
- `openshift-setup/`: YAML files for Kubernetes deployments.
- `code-engine-setup/`: Configuration for IBM Code Engine deployments.

##### Examine the README for Additional Insights

**Architecture and Dependencies:**
- Utilizes **watsonx.ai** for natural language processing and SQL generation.
- Integrates with **Presto Engine** for database interactions.
- Deployment on **OpenShift** and **IBM Code Engine** indicates the use of Kubernetes and cloud-native deployment strategies.
- API documentation via **Swagger** suggests a RESTful API design.

**System Components Mentioned:**
- **API Layer:** Python-based backend serving API endpoints.
- **Prompt Templates:** Managed within watsonx.ai, essential for generating SQL queries.
- **Database Integration:** Interaction with Presto for executing SQL queries.
- **Deployment Pipelines:** Configured for Kubernetes-based platforms.

**a. Identify the Main Components:**
- **Frontend (Optional):** While not explicitly mentioned, if there is a user interface, it should be included.
- **Backend Service:**
- **API Server (`app.py`):** Handles incoming API requests.
- **Classification Module (`promptClassify`):** Determines if the natural language input is a valid SQL query.
- **SQL Generation Module (`promptSQL`):** Converts text to SQL using watsonx.ai.
- **Database:**
- **Presto Engine:** Executes the generated SQL queries.
- **External Services:**
- **watsonx.ai:** Provides AI capabilities for NLP and SQL generation.
- **Deployment Infrastructure:**
- **OpenShift / IBM Code Engine:** Manages deployment, scaling, and orchestration.
- **Configuration Management:**
- **Environment Variables and Secrets:** Managed via `.env` files and OpenShift secrets.

**b. Determine Relationships and Interactions:**
- **User Interaction:** Users send natural language queries to the **API Server**.
- **API Server** interacts with the **Classification Module** to validate queries.
- Validated queries are passed to the **SQL Generation Module**, which leverages **watsonx.ai**.
- Generated SQL is executed on the **Presto Engine** database.
- Responses are sent back to the user via the **API Server**.
- Deployment components manage the lifecycle and scaling of these services.

**c. Highlight Architectural Patterns or Design Principles:**
- **Separation of Concerns:** Distinct modules for classification, SQL generation, and API handling.
- **Scalability:** Containerization allows horizontal scaling via OpenShift or Code Engine.
- **Security:** Use of API keys and environment variables to manage sensitive information.
- **Extensibility:** Modular prompt templates enable easy updates and additions to functionality.

**d. Include Relevant Technologies, Frameworks, or Libraries:**
- **Backend:** Python 3.12.8, Flask or a similar framework (inferred from `app.py`).
- **Containerization:** Docker.
- **Deployment:** OpenShift, IBM Code Engine.
- **API Documentation:** Swagger.
- **Database Interaction:** Presto Engine.
- **AI Services:** watsonx.ai.

##### Elements

- **Clear Labels for Each Component:** Ensure every part of the system (e.g., API Server, watsonx.ai, Presto Engine) is distinctly labeled.

##### Emphasize Detailed and Essential Architectural Elements

- **Modular Components:** Each functional part of the application should be represented as a separate component.
- **Interactions and Dependencies:** Clearly show how components interact and depend on each other.
- **Deployment Specifics:** Highlight differences or configurations specific to OpenShift and IBM Code Engine if applicable.
- **Security Considerations:** Indicate where authentication (e.g., API keys) is applied within the system.
- **Scalability and Redundancy:** If applicable, show how the system can scale or handle failures.

Avoid clutter by maintaining a balance between detail and readability. Prioritize representing the flow of data and critical interactions that define the application's functionality.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Global entities
    User("User"):::external

    %% Subgraphs and modules
    subgraph "Backend Service"
        APIServer["API Server"]:::service
        Classification["Classification Module"]:::service
        SQLGeneration["SQL Generation Module"]:::service
        Presto["Presto Engine"]:::database
    end

    subgraph "External Services"
        WatsonxAI["watsonx.ai"]:::externalService
    end

    subgraph "Deployment Infrastructure"
        OpenShift["OpenShift Setup"]:::deployment
        CodeEngine["IBM Code Engine Setup"]:::deployment
    end

    subgraph "Configuration Management"
        EnvConfig["Environment Configuration"]:::config
        Secrets["Secrets Management"]:::config
    end

    subgraph "Data Management"
        DataIngestion["Data Ingestion Scripts"]:::data
        DBSetup["Database Setup Scripts"]:::data
    end

    subgraph "Development Tools"
        Docker["Containerization Configuration"]:::dev
        Dependencies["Project Dependencies"]:::dev
    end

    subgraph "Integration Setup"
        WatsonxSetup["watsonx.ai Integration Setup"]:::externalService
    end

    subgraph "API Documentation"
        Swagger1["sequifi_openapi_v1.json"]:::doc
        Swagger2["texttosql_openapi_v1.json"]:::doc
    end

    %% Connections
    User -->|sends query| APIServer
    APIServer -->|validates query| Classification
    Classification -->|valid query| SQLGeneration
    SQLGeneration -->|generates SQL| WatsonxAI
    SQLGeneration -->|executes SQL| Presto
    Presto -->|returns data| APIServer
    APIServer -->|sends response| User
    APIServer --> EnvConfig
    APIServer --> Secrets
    APIServer --> Docker
    APIServer --> Dependencies
    Classification -->|uses| PromptTemplates["Prompt Templates"]:::data
    SQLGeneration -->|uses| PromptTemplates
    DataIngestion --> DBSetup
    OpenShift --> APIServer
    CodeEngine --> APIServer
    WatsonxSetup --> WatsonxAI
    Swagger1 --> APIServer
    Swagger2 --> APIServer

    %% Click Events
    click APIServer "https://github.com/crushbank-inc/text2sql-app/blob/main/application/app.py"
    click Classification "https://github.com/crushbank-inc/text2sql-app/tree/main/prompts/promptClassify"
    click SQLGeneration "https://github.com/crushbank-inc/text2sql-app/tree/main/prompts/promptSQL"
    click PromptTemplates "https://github.com/crushbank-inc/text2sql-app/tree/main/prompts/"
    click EnvConfig "https://github.com/crushbank-inc/text2sql-app/tree/main/application/env"
    click Secrets "https://github.com/crushbank-inc/text2sql-app/blob/main/openshift-setup/secrets.yaml"
    click OpenShift "https://github.com/crushbank-inc/text2sql-app/tree/main/openshift-setup/"
    click CodeEngine "https://github.com/crushbank-inc/text2sql-app/tree/main/code-engine-setup/"
    click DataIngestion "https://github.com/crushbank-inc/text2sql-app/blob/main/watsonx-data/Data_Ingestion.ipynb"
    click DBSetup "https://github.com/crushbank-inc/text2sql-app/blob/main/watsonx-data/create_table_statements.sql"
    click Docker "https://github.com/crushbank-inc/text2sql-app/tree/main/application/Dockerfile"
    click Dependencies "https://github.com/crushbank-inc/text2sql-app/blob/main/application/requirements.txt"
    click WatsonxSetup "https://github.com/crushbank-inc/text2sql-app/tree/main/watsonx-assistant-setup/"
    click Swagger1 "https://github.com/crushbank-inc/text2sql-app/blob/main/watsonx-assistant-setup/sequifi_openapi_v1.json"
    click Swagger2 "https://github.com/crushbank-inc/text2sql-app/blob/main/watsonx-assistant-setup/texttosql_openapi_v1.json"

    %% Styles
    classDef service fill:#1f78b4,stroke:#333,stroke-width:2px;
    classDef externalService fill:#33a02c,stroke:#333,stroke-width:2px;
    classDef database fill:#ff7f00,stroke:#333,stroke-width:2px,shape:cylinder;
    classDef deployment fill:#6a3d9a,stroke:#333,stroke-width:2px;
    classDef config fill:#b15928,stroke:#333,stroke-width:2px;
    classDef data fill:#e31a1c,stroke:#333,stroke-width:2px;
    classDef dev fill:#b2df8a,stroke:#333,stroke-width:2px;
    classDef external fill:#a6cee3,stroke:#333,stroke-width:2px;
    classDef doc fill:#fb9a99,stroke:#333,stroke-width:2px;
    class User external
```
