# weaidb/cracksql architecture

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

- Interactive diagram: https://gitdiagram.com/weaidb/cracksql
- Repository: https://github.com/weaidb/cracksql

## Overview

##### Project type and purpose
- CrackSQL is a full-stack, hybrid SQL-dialect translation service.
- Purpose: translate SQL queries between PostgreSQL, MySQL, and Oracle using a combination of rule-based parsing (ANTLR + SQLGlot) and LLM/embedding models.  It exposes:
- A Python API & CLI (translate.py, init_knowledge_base.py)
- A Flask REST backend (backend/app.py)
- A Vue3/Vite web interface (webui/)

##### File structure
- Top-level “backend” directory:
- api/ – Flask route definitions and routers
- services/ – business-logic implementations mirrored under api/services
- config/ – configuration files (YAML, logging, database, Gunicorn)
- preprocessor/ – ANTLR-based SQL parsers and tree mappers
- llm_model/ – LLM and embedding model management & service wrappers
- vector_store/ – Chroma-based vector storage for knowledge retrieval
- translator/ & rewrite.py – orchestrator module combining rule/LLM pipelines
- models.py, db_config.py – ORM models and database connectors
- “data” directory: ANTLR grammars (.g4), processed JSON specs, seed docs, pretrained embedding models
- “webui” directory: Vue.js single-page application with components, API clients, routing, state management

##### README for insights
- Describes three translation modes (rule‐only, LLM‐direct, rule+LLM)
- Lists supported entry points (Python, CLI, web)
- Mentions external dependencies: OpenAI/HuggingFace APIs, local embedding, relational database, vector store

a. Main components
- Client tier: Web UI (Vue SPA), CLI, Python API
- API tier: Flask app (Gunicorn/WSGI)
- Business logic: Translator orchestrator, Preprocessor (ANTLR), Rule engine (SQLGlot), LLM/Embedding services
- Data tier: Relational DB (SQLAlchemy), Vector store (Chroma), File system (grammar & spec JSON)
- External services: OpenAI/HuggingFace endpoints for LLM & embeddings
b. Relationships & interactions
1. User → Web UI or CLI
2. → HTTP/REST → Flask API endpoints (/translate, /initkb, /rewrite…)
3. Translator module invokes Preprocessor to parse SQL AST
4. Optionally query Vector Store for knowledge retrieval
5. Call local or cloud LLM/Embedding service
6. Apply rule engine and compose final SQL
7. Persist logs/metadata to Relational DB
8. Return translated SQL → UI/CLI
c. Architectural patterns
- Layered architecture (Presentation → API → Service → Data)
- Adapter pattern for plugging in different LLM/embedding backends
- Pipeline pattern in translator: parse → retrieve → model → post-process
d. Key technologies/frameworks
- Python: Flask, SQLAlchemy, ANTLR4, SQLGlot, Chroma
- LLMs: OpenAI API or HuggingFace models
- Frontend: Vue3, Vite, Element-Plus

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Client Tier
    subgraph "Client Tier"
        direction TB
        WebUI["Web UI (Vue3 SPA)"]:::client
        CLI["CLI (translate.py)"]:::client
        PyAPI["Python API (init_knowledge_base.py)"]:::client
    end

    %% Backend Tier
    subgraph "Backend Tier"
        direction TB
        FlaskAPI["Flask API"]:::backend
        Routers["API Routers"]:::backend
        Services["API Services"]:::backend
        Config["Configuration"]:::backend
        WSGI["Gunicorn WSGI"]:::backend

        Preprocessor["SQL Preprocessor"]:::backend
        RuleEngine["Rule Engine (SQLGlot)"]:::backend
        Translator["Translator Orchestrator"]:::backend
        LLMService["LLM & Embedding Services"]:::backend
        VectorStore["Chroma Vector Store"]:::backend
        RelationalDB["Relational Database"]:::data
        FileSystem["Grammar & Specs FS"]:::data
        ExternalLLM["External LLM APIs"]:::external
    end

    %% Data Tier
    subgraph "Data Tier"
        direction TB
        RelationalDB
        VectorStore
        FileSystem
    end

    %% External Services
    subgraph "External Services"
        direction TB
        OpenAI["OpenAI API"]:::external
        HuggingFace["HuggingFace API"]:::external
    end

    %% Interactions
    WebUI -->|HTTP/REST| FlaskAPI
    CLI -->|Invoke| FlaskAPI
    PyAPI -->|Invoke| Translator

    FlaskAPI -->|routes to| Routers
    Routers -->|calls| Services
    Services -->|uses| Translator

    Translator -->|parse SQL| Preprocessor
    Translator -->|rewrite SQL| RuleEngine
    Translator -->|retrieve context| VectorStore
    Translator -->|call LLM| LLMService
    LLMService -->|calls| OpenAI
    LLMService -->|calls| HuggingFace

    Translator -->|log metadata| RelationalDB
    FileSystem -->|provide grammars| Preprocessor

    %% Optional flows
    Translator -.->|rule-only mode| RuleEngine
    Translator -.->|skip LLM| LLMService

    %% Click Events
    click CLI "https://github.com/weaidb/cracksql/blob/pypi/0.0.0-alpha/backend/translate.py"
    click PyAPI "https://github.com/weaidb/cracksql/blob/pypi/0.0.0-alpha/backend/init_knowledge_base.py"
    click FlaskAPI "https://github.com/weaidb/cracksql/blob/pypi/0.0.0-alpha/backend/app.py"
    click Routers "https://github.com/weaidb/cracksql/blob/pypi/0.0.0-alpha/backend/api/router.py"
    click Services "https://github.com/weaidb/cracksql/tree/pypi/0.0.0-alpha/backend/api/services/"
    click Config "https://github.com/weaidb/cracksql/tree/pypi/0.0.0-alpha/backend/config/"
    click WSGI "https://github.com/weaidb/cracksql/blob/pypi/0.0.0-alpha/backend/wsgi_gunicorn.py"
    click Preprocessor "https://github.com/weaidb/cracksql/tree/pypi/0.0.0-alpha/backend/preprocessor/"
    click RuleEngine "https://github.com/weaidb/cracksql/blob/pypi/0.0.0-alpha/backend/rewrite.py"
    click Translator "https://github.com/weaidb/cracksql/tree/pypi/0.0.0-alpha/backend/translator/"
    click LLMService "https://github.com/weaidb/cracksql/tree/pypi/0.0.0-alpha/backend/llm_model/"
    click VectorStore "https://github.com/weaidb/cracksql/blob/pypi/0.0.0-alpha/backend/vector_store/chroma_store.py"
    click RelationalDB "https://github.com/weaidb/cracksql/blob/pypi/0.0.0-alpha/backend/models.py"
    click FileSystem "https://github.com/weaidb/cracksql/tree/pypi/0.0.0-alpha/data/"
    click WebUI "https://github.com/weaidb/cracksql/tree/pypi/0.0.0-alpha/webui/"

    %% Styles
    classDef client fill:#cce5ff,stroke:#004085,color:#004085;
    classDef backend fill:#d4edda,stroke:#155724,color:#155724;
    classDef data fill:#fff3cd,stroke:#856404,color:#856404;
    classDef external fill:#e2e3e5,stroke:#6c757d,color:#6c757d;
```
