How soukicz/sql-ai-optimizer works
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