# opensearch-ai/opensearch-sql architecture

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

- Interactive diagram: https://gitdiagram.com/opensearch-ai/opensearch-sql
- Repository: https://github.com/opensearch-ai/opensearch-sql

## Overview

##### Main Components
- Data and Few-shot Processing Module:
- Components in the Bird directory (bird_dev.json, fewshot/questions.json) are used to provide few-shot examples.
- Under src/database_process, scripts like data_preprocess.py, generate_question.py, make_emb.py, and prepare_train_queries.py handle data preparation, embed generation, and few-shot example creation.
- LLM Integration Module:
- The src/llm subdirectory contains key files such as model.py, all_prompt.py, db_conclusion.py, and prompts.py. These implement functions to build query prompts, interface with LLMs, and process model responses.
- Pipeline Management Module:
- Located in src/pipeline, there are dedicated scripts for different stages: align_correct.py, candidate_generate.py, extract_col_value.py, generate_db_schema.py, pipeline_manager.py, etc.
- This module orchestrates processing steps including extraction, candidate selection, alignment, and evaluation.
- Runner/Execution Module:
- The src/runner folder comprises multiple scripts (execution.py, database_manager.py, run_manager.py, logger.py, etc.) that implement the execution layer – validating, correcting, retrieving columns, managing statistics, and handling database interactions.
- Supporting Infrastructure and Run Scripts:
- The run folder, containing run_main.sh and run_preprocess.sh, provides the shell scripts required to activate the processing pipeline.
- The requirements.txt file and configuration files (like bird_dev.json and few-shot JSONs) indicate external dependencies and configuration details.

##### Relationships and Data Flow
- User or automated data input first goes through preprocessing in the database_process module to generate few-shot examples, prepare queries, and create embeddings for schema linkage.
- LLM Processing:
- Processed data moves to the LLM module where prompt formatting occurs. Here, the input (including optional chain-of-thought information) is formatted and sent to the chosen large language model (e.g., GPT, DeepSeek, Gemini).
- Pipeline Orchestration:
- The LLM outputs then flow into the pipeline module, where the response is further refined (extraction and alignment) to handle candidate SQL generation, vote selection strategies, and correction of any hallucinated information.
- Execution Phase:
- The final validated and processed SQL is handed off to the runner module where tasks like column retrieval, execution management, and logging take place. The database_manager also coordinates any direct database interactions.
- External Configuration and Few-shot Integration:
- The Bird directory and relevant configuration files act as input sources that might be used at both preprocessing and runtime (few-shot example generation).

##### Architectural Patterns and Principles
- Modular Design: Separation into distinct modules (preprocessing, LLM interaction, pipeline & execution) emphasizes a clean separation of concerns.
- Pipeline Flow: A step-by-step processing pipeline model that begins with data preparation and ends with SQL execution. This is especially clear in the pipeline_manager and workflow_builder that control how different stages are sequenced.
- Dynamic Few-shot and Alignment Strategies: The design integrates dynamic few-shot learning (leveraging examples from Bird) and alignment (in the pipeline module) to fine-tune output through chain-of-thought augmentation.
- Logging and Monitoring: The runner module has dedicated logging and statistics management to monitor system performance.

## Diagram (Mermaid)

```mermaid
flowchart TD

    %% Data Preprocessing Module
    subgraph "Data Preprocessing Module"
        subgraph "Bird Config & Few-Shot"
            BIRD1["\"bird_dev.json\""]:::externalBlue
            BIRD2["\"questions.json\""]:::externalBlue
        end
        subgraph "database_process"
            DP1["\"data_preprocess.py\""]:::moduleBlue
            DP2["\"generate_question.py\""]:::moduleBlue
            DP3["\"make_emb.py\""]:::moduleBlue
            DP4["\"prepare_train_queries.py\""]:::moduleBlue
        end
    end

    %% LLM Module
    subgraph "LLM Module"
        LLM1["\"model.py\""]:::moduleGreen
        LLM2["\"prompts.py\""]:::moduleGreen
        LLM3["\"all_prompt.py\""]:::moduleGreen
        LLM4["\"db_conclusion.py\""]:::moduleGreen
    end

    %% Pipeline Module
    subgraph "Pipeline Module"
        PIPE1["\"align_correct.py\""]:::moduleYellow
        PIPE2["\"candidate_generate.py\""]:::moduleYellow
        PIPE3["\"extract_col_value.py\""]:::moduleYellow
        PIPE4["\"generate_db_schema.py\""]:::moduleYellow
        PIPE5["\"pipeline_manager.py\""]:::moduleYellow
        PIPE6["\"workflow_builder.py\""]:::moduleYellow
        PIPE7["\"evaluation.py\""]:::moduleYellow
    end

    %% Runner/Execution Module
    subgraph "Runner/Execution Module"
        RUN1["\"execution.py\""]:::moduleRed
        RUN2["\"database_manager.py\""]:::moduleRed
        RUN3["\"run_manager.py\""]:::moduleRed
        RUN4["\"logger.py\""]:::moduleRed
        RUN5["\"check_and_correct.py\""]:::moduleRed
        RUN6["\"column_retrieve.py\""]:::moduleRed
        RUN7["\"column_update.py\""]:::moduleRed
        RUN8["\"extract.py\""]:::moduleRed
        RUN9["\"statistics_manager.py\""]:::moduleRed
        RUN10["\"task.py\""]:::moduleRed
    end

    %% Infrastructure Module
    subgraph "Infrastructure / Run Scripts"
        INF1["\"run_main.sh\""]:::modulePurple
        INF2["\"run_preprocess.sh\""]:::modulePurple
        INF3["\"requirements.txt\""]:::modulePurple
    end

    %% Connections within Data Preprocessing Module
    BIRD1 --> DP1
    BIRD2 --> DP1
    DP1 --> DP2
    DP2 --> DP3
    DP3 --> DP4

    %% Flow between main modules
    DP4 -->|"PreprocessedData"| LLM1
    LLM4 -->|"LLMPrompt"| PIPE1
    PIPE7 -->|"CandidateSQL"| RUN1
    RUN10 -->|"ValidatedExecution"| INF1

    %% Feedback loop from Runner for error correction
    RUN5 -->|"ErrorCorrection"| PIPE5

    %% Class Definitions for Colors
    classDef externalBlue fill:#aqua,stroke:#000,stroke-width:2px;
    classDef moduleBlue fill:#b3cde0,stroke:#000,stroke-width:2px;
    classDef moduleGreen fill:#ccebc5,stroke:#000,stroke-width:2px;
    classDef moduleYellow fill:#fddaec,stroke:#000,stroke-width:2px;
    classDef moduleRed fill:#fbb4ae,stroke:#000,stroke-width:2px;
    classDef modulePurple fill:#decbe4,stroke:#000,stroke-width:2px;

    %% Click Events for Data and Few-shot Processing Module
    click BIRD1 "https://github.com/opensearch-ai/opensearch-sql/blob/main/Bird/bird_dev.json"
    click BIRD2 "https://github.com/opensearch-ai/opensearch-sql/blob/main/Bird/fewshot/questions.json"
    click DP1 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/database_process/data_preprocess.py"
    click DP2 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/database_process/generate_question.py"
    click DP3 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/database_process/make_emb.py"
    click DP4 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/database_process/prepare_train_queries.py"

    %% Click Events for LLM Integration Module
    click LLM1 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/llm/model.py"
    click LLM2 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/llm/prompts.py"
    click LLM3 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/llm/all_prompt.py"
    click LLM4 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/llm/db_conclusion.py"

    %% Click Events for Pipeline Management Module
    click PIPE1 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/pipeline/align_correct.py"
    click PIPE2 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/pipeline/candidate_generate.py"
    click PIPE3 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/pipeline/extract_col_value.py"
    click PIPE4 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/pipeline/generate_db_schema.py"
    click PIPE5 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/pipeline/pipeline_manager.py"
    click PIPE6 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/pipeline/workflow_builder.py"
    click PIPE7 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/pipeline/evaluation.py"

    %% Click Events for Runner/Execution Module
    click RUN1 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/runner/execution.py"
    click RUN2 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/runner/database_manager.py"
    click RUN3 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/runner/run_manager.py"
    click RUN4 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/runner/logger.py"
    click RUN5 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/runner/check_and_correct.py"
    click RUN6 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/runner/column_retrieve.py"
    click RUN7 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/runner/column_update.py"
    click RUN8 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/runner/extract.py"
    click RUN9 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/runner/statistics_manager.py"
    click RUN10 "https://github.com/opensearch-ai/opensearch-sql/blob/main/src/runner/task.py"

    %% Click Events for Infrastructure and Run Scripts
    click INF1 "https://github.com/opensearch-ai/opensearch-sql/blob/main/run/run_main.sh"
    click INF2 "https://github.com/opensearch-ai/opensearch-sql/blob/main/run/run_preprocess.sh"
    click INF3 "https://github.com/opensearch-ai/opensearch-sql/blob/main/requirements.txt"
```
