Natural Language to SQL Conversion Pipeline

flowchart TD
    Question["Input: User question
(normalized)"]:::io

    subgraph Strategies["Conversion Strategies (run in parallel)"]
        direction LR
        subgraph Template["Template-Based Path - 90% of queries"]
            direction TB
            T1["Pattern matching"]:::template
            T2["Parameter substitution
Confidence: HIGH"]:::template
            T1 --> T2
        end
        subgraph LLM["LLM-Based Path - novel queries"]
            direction TB
            L1["Schema-aware prompt"]:::llm
            L2["LLM SQL generation
Confidence: MEDIUM"]:::llm
            L1 --> L2
        end
        subgraph Semantic["Semantic Parse - research"]
            direction TB
            P1["Logical form"]:::semantic
            P2["SQL from logical form
Confidence: MEDIUM"]:::semantic
            P1 --> P2
        end
    end

    Select["Select highest-confidence SQL candidate"]:::validate
    Schema["Schema check"]:::validate
    Pattern["Pattern check"]:::validate
    Sanitize["Parameter sanitize"]:::validate
    Safety["Safety + Execution
5s timeout, 1K row limit"]:::safety
    Output["Output: format, cache, log results"]:::io
    Escalate["Fallback: human escalation /
safe error message"]:::fallback

    Question --> Template
    Question --> LLM
    Question --> Semantic
    Template --> Select
    LLM --> Select
    Semantic --> Select
    Select --> Schema --> Pattern --> Sanitize --> Safety
    Safety --> Output
    Sanitize -.->|validation fails| Escalate
    Safety -.->|execution error| Escalate

    classDef io fill:#90a4ae,stroke:#37474f,stroke-width:2px,color:#fff,font-size:14px
    classDef template fill:#66bb6a,stroke:#1b5e20,stroke-width:2px,color:#fff,font-size:13px
    classDef llm fill:#ffd54f,stroke:#f57f17,stroke-width:2px,color:#333,font-size:13px
    classDef semantic fill:#ff8a65,stroke:#bf360c,stroke-width:2px,color:#fff,font-size:13px
    classDef validate fill:#42a5f5,stroke:#0d47a1,stroke-width:2px,color:#fff,font-size:14px
    classDef safety fill:#ef5350,stroke:#b71c1c,stroke-width:2px,color:#fff,font-size:14px
    classDef fallback fill:#bdbdbd,stroke:#616161,stroke-width:2px,color:#212121,font-size:13px

    style Template fill:#e8f5e9,stroke:#66bb6a
    style LLM fill:#fff8e1,stroke:#ffca28
    style Semantic fill:#fbe9e7,stroke:#ff8a65
    style Strategies fill:#f1f8ff,stroke:#90caf9

    linkStyle default stroke:#777,stroke-width:2px,font-size:13px