# soukicz/sql-ai-optimizer architecture

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

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

## Overview

##### Project Type and Purpose
- SQL AI Optimizer is a PHP-based full-stack web application.
- It connects to a live MySQL/MariaDB Performance Schema, analyzes SQL query performance, invokes Anthropic’s LLM via API, and renders optimization suggestions in a browser UI.

##### High-Level Architectural Components
a. HTTP Client (Web Browser)
b. Web Server & PHP Runtime (built-in server, Apache+PHP-FPM, or Nginx+PHP-FPM)
c. Application Entry Point (index.php) & Configuration Loader (.env)
d. MVC Layers
- Controllers (`src/Controller/*`)
- Services & Core Logic (`src/Service/*`, `src/QueryAnalyzer.php`, `src/QuerySelector.php`)
- Tools/Data Access (`src/Tool/*`, `src/AnalyzedDatabase.php`, `src/StateDatabase.php`)
e. External AI Service (Anthropic API)
f. Databases
- Analyzed Database: MySQL/MariaDB Performance Schema
- State Database: Local (SQLite or similar) for history, schema defined in `src/schema/state_database.sql`
g. Presentation Layer (Twig templates in `templates/`)
h. Logging & Caching (`src/LLMFileLogger.php`, local cache directories)

##### Relationships & Data Flows
1. Browser → HTTP Request → Web Server
2. Web Server → index.php loads environment, boots framework/composer autoloader
3. Request routed to a specific Controller (IndexController, RunController, AnalysisController)
4. Controller invokes Services (e.g. QueryAnalyzer, QuerySelector)
5. Services call Tools/Data Access:
– PerformanceSchemaQueryTool queries the production DB via AnalyzedDatabase
– DatabaseQueryExecutor runs specific analysis queries
– StateDatabase reads/writes history tables
6. For AI logic, Service sends query metadata to Anthropic API and receives optimization suggestions
7. Controller passes results to Twig (`QueryResultFormatter` formats data)
8. Twig renders HTML, returned to Browser
9. LLMFileLogger persists a log of queries and LLM responses to disk

##### Architectural Patterns & Design Principles
- MVC separation: Controllers vs Services vs Data Access
- Single Responsibility: each class focuses on one concern
- Dependency Injection via constructor injection (managed by composer/autoloader)
- Externalization of config (.env)
- Use of a local state DB to avoid polluting production schema

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Presentation Layer
    subgraph "Presentation Layer"
        direction TB
        UI["Browser (Client)"]:::presentation
        Templates["Twig Templates"]:::presentation
    end

    %% Application Server Layer
    subgraph "Server & App Initialization"
        direction TB
        WebServer["Web Server + PHP Runtime"]:::service
        Index["index.php"]:::service
        Env[".env Loader"]:::config
    end

    %% Controller Layer
    subgraph "Controller Layer"
        direction TB
        BaseCtrl["BaseController"]:::presentation
        IndexCtrl["IndexController"]:::presentation
        RunCtrl["RunController"]:::presentation
        AnalysisCtrl["AnalysisController"]:::presentation
    end

    %% Service Layer
    subgraph "Service Layer"
        direction TB
        Analyzer["QueryAnalyzer"]:::service
        Selector["QuerySelector"]:::service
        Formatter["QueryResultFormatter"]:::service
        DBExec["DatabaseQueryExecutor"]:::service
    end

    %% Data Access Layer
    subgraph "Tool/Data Access Layer"
        direction TB
        PerfTool["PerformanceSchemaQueryTool"]:::data
        QueryTool["QueryTool"]:::data
        AnalyzedDB["AnalyzedDatabase"]:::database
        StateDB["StateDatabase"]:::database
        SchemaSQL["state_database.sql"]:::data
    end

    %% External & Logging
    subgraph "External & Logging"
        direction TB
        AIService["Anthropic API"]:::external
        Logger["LLMFileLogger"]:::data
    end

    %% Databases
    subgraph "Databases"
        direction TB
        ProdDB["Production DB\n(MySQL/MariaDB)"]:::database
        LocalState["State DB\n(SQLite)"]:::database
    end

    %% Flows
    UI -->|HTTP request| WebServer
    WebServer -->|boot framework| Index
    Index -->|load config| Env
    Index -->|route request| BaseCtrl
    BaseCtrl -->|delegates| IndexCtrl
    BaseCtrl -->|delegates| RunCtrl
    BaseCtrl -->|delegates| AnalysisCtrl

    IndexCtrl -->|invoke| Analyzer
    RunCtrl -->|invoke| Selector
    AnalysisCtrl -->|invoke| DBExec

    Analyzer -->|calls| PerfTool
    Analyzer -->|calls| QueryTool
    PerfTool -->|connects| AnalyzedDB
    PerfTool -->|executes SQL| ProdDB
    DBExec -->|executes SQL| ProdDB

    Selector -->|reads/writes| StateDB
    StateDB -->|persists| LocalState
    SchemaSQL -->|applies schema| LocalState

    Analyzer -->|LLM API call| AIService
    Analyzer -->|log responses| Logger

    Analyzer -->|formats results| Formatter
    Formatter -->|renders via| Templates
    Templates -->|HTML response| UI

    %% Click Events
    click Index "https://github.com/soukicz/sql-ai-optimizer/blob/main/index.php"
    click Env "https://github.com/soukicz/sql-ai-optimizer/blob/main/.env.sample"
    click BaseCtrl "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/Controller/BaseController.php"
    click IndexCtrl "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/Controller/IndexController.php"
    click RunCtrl "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/Controller/RunController.php"
    click AnalysisCtrl "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/Controller/AnalysisController.php"
    click Analyzer "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/QueryAnalyzer.php"
    click Selector "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/QuerySelector.php"
    click DBExec "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/Service/DatabaseQueryExecutor.php"
    click Formatter "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/Service/QueryResultFormatter.php"
    click PerfTool "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/Tool/PerformanceSchemaQueryTool.php"
    click QueryTool "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/Tool/QueryTool.php"
    click AnalyzedDB "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/AnalyzedDatabase.php"
    click StateDB "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/StateDatabase.php"
    click SchemaSQL "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/schema/state_database.sql"
    click Logger "https://github.com/soukicz/sql-ai-optimizer/blob/main/src/LLMFileLogger.php"
    click Templates "https://github.com/soukicz/sql-ai-optimizer/tree/main/templates/"

    %% Styles
    classDef presentation fill:#cce5ff,stroke:#004085,color:#004085
    classDef service fill:#d4edda,stroke:#155724,color:#155724
    classDef data fill:#fff3cd,stroke:#856404,color:#856404
    classDef database fill:#e2e3e5,stroke:#6c757d,color:#6c757d
    classDef external fill:#f8d7da,stroke:#721c24,color:#721c24
    classDef config fill:#f0f0f0,stroke:#666666,color:#333333
```
