Design NL-to-SQL performance optimization assistant

## Scenario You are building an internal tool for engineers/analysts to ask **natural-language questions about database performance** (e.g., “Why is the `orders` dashboard slow today?” or “Which queries are causing the most CPU?”). The system must: 1. Interpret the natural-language question. 2. Determine which diagnostic data to query (e.g., slow query logs, query stats, table/index metadata). 3. Generate and run the relevant SQL (and/or EXPLAIN plans) safely. 4. Analyze the results to identify root causes. 5. Produce actionable optimization recommendations (e.g., index suggestions, query rewrites, schema changes, config tuning), with evidence. Assume the database is a relational OLTP system (e.g., PostgreSQL/MySQL) with access to: - Query statistics (e.g., `pg_stat_statements`-like), slow logs - System metrics (CPU/IO), table/index metadata - Ability to run read-only SQL and optionally `EXPLAIN (ANALYZE)` in controlled environments ## Requirements ### Functional - NL question → structured intent (what to investigate, which time window, which service/table/query) - Generate safe SQL diagnostics queries - Execute queries with guardrails (timeouts, read-only, row limits) - Analyze results and produce ranked hypotheses - Output recommendations with confidence and supporting data - Ask clarifying questions when the NL query is ambiguous ### Non-functional - Security: least privilege, tenant isolation, PII protection - Reliability: handle partial data sources; graceful degradation - Latency: interactive (e.g., p95 < 5–10s for common questions) - Auditability: log prompts, SQL, results summaries, and decisions - Cost controls if using LLMs (caching, batching, smaller models) ## Deliverables Describe the end-to-end system design: architecture, main components, data flow, safety mechanisms, and how you would evaluate quality.