Self-Serve Natural Language SQL Business Intelligence Engine

Enterprise-grade Text-to-SQL analytical platform featuring strict AST security guardrails, dynamic schema extraction, and offline deterministic synthesis, aligned with the Growth Solutions Analyst role at Quantacus.ai.

---

1. Problem Statement & Quantacus.ai Alignment

While business stakeholders (Growth Marketers, Brand Directors, Merchandisers) require rapid, ad-hoc insights into transaction data, traditional BI dashboards suffer from severe bottlenecks:

• Analyst Queue Latency: Simple queries like "Which customer tier drove the most gross revenue from cold plunges last month?" take days in analytics Jira queues.
• Critical Security Hazards in Naive LLM Text-to-SQL: Directly executing LLM-generated SQL against a transactional database exposes brands to devastating SQL injection attacks, table drops (DROP TABLE), accidental data corruption (UPDATE / DELETE), or multi-statement query stacking.

The Self-Serve Natural Language SQL BI Engine solves this by coupling Google Gemini 2.5 with a mathematically rigorous Abstract Syntax Tree (AST) Security Guardrail powered by sqlglot. The engine only executes mathematically verified read-only SELECT statements, logs execution telemetry to an audit_log, and synthesizes results into plain-English executive summaries.

---

2. System Architecture & Data Flow

flowchart TD
    User(["Client Growth Analyst / Business User"]) -->|"1. Plain English Query<br/>'What are our top 5 revenue products?'"| WebUI["Interactive Web UI / REST API"]
    
    subgraph SchemaContextModule["Schema Context & Prompt Engine"]
        DB[("retail_analytics.db<br/>customers, products, orders,<br/>order_items, ad_spend")] -->|"PRAGMA table_info & samples"| Inspector["Dynamic Schema Inspector"]
        Inspector -->|"DDL + Business Rules + Sample Rows"| PromptContext["Structured System Prompt"]
    end

    WebUI --> PromptContext
    PromptContext --> LLM["Google Gemini 2.5 Flash<br/>(or Offline Deterministic Fallback)"]
    LLM -->|"2. Generated SQL"| ASTGuard["Strict AST Security Guardrail (sqlglot)"]

    subgraph SecurityGuardrail["AST Security Inspection"]
        ASTGuard --> Check1{"Is Root AST Type == exp.Select?"}
        Check1 -->|No| Reject["SecurityGuardrailException<br/>Block Mutating / Non-SELECT Query"]
        Check1 -->|Yes| Check2{"Are Semicolons / Stacked Statements Present?"}
        Check2 -->|Yes| Reject
        Check2 -->|No| Check3{"Are Destructive Nodes Present?<br/>DROP, DELETE, UPDATE, PRAGMA, ATTACH"}
        Check3 -->|Yes| Reject
        Check3 -->|No| CleanSQL["Sanitized Single-Root SQLite Query"]
    end

    Reject --> AuditFail[("audit_log<br/>Record Blocked Injection Attempt")]
    Reject --> ErrorMsg["400 Security Violation Response"]

    CleanSQL --> ExecEngine["Read-Only SQLite Execution Engine"]
    ExecEngine --> DB
    ExecEngine --> ResultData["Query Record Set (Tabular Rows)"]
    ResultData --> Synthesizer["Executive NL Insight Synthesizer"]
    ResultData --> AuditPass[("audit_log<br/>Latency, Row Count, Status")]
    Synthesizer --> FinalPayload["Executive Answer + SQL + Results Table"]
    FinalPayload --> WebUI

Loading

---

3. Why AST-Based Parsing is Superior to Naive Regex

Naive Text-to-SQL systems often attempt to enforce security using regular expressions (e.g. re.search(r'\b(DROP|DELETE|UPDATE)\b', sql)). This approach is fundamentally unsafe for production:

Security Vector	Naive Regex Filtering	sqlglot AST Guardrail
Comment Obfuscation	Bypassed: An attacker injects SELECT/*comment*/1;DROP/**/TABLE orders;. Regex fails to identify the boundaries.	Protected: Parser strips comments according to formal grammar specifications and validates individual statement trees.
False Positives on Literals	Broken: Rejects legitimate queries like WHERE product_name = 'Eye Drop' or WHERE status = 'dropped'.	Protected: Understands that 'Eye Drop' is a string literal (exp.Literal) rather than an executable command token.
Statement Stacking	Vulnerable: Complex whitespace or nested parentheses can slip stacked queries past regex patterns.	Protected: Enforces that len(sqlglot.parse(sql)) == 1 and root expression is strictly exp.Select.
Dialect Specificities	Incomplete: Misses SQLite-specific commands like ATTACH DATABASE, PRAGMA writable_schema, or load_extension.	Protected: Transpiles and inspects the abstract syntax specifically under the SQLite grammar dialect.

---

4. Quickstart Guide (1-Line Commands)

# 1. Install dependencies
pip install -r projects/sql-bi-agent/requirements.txt

# 2. Run the security & pipeline test suite
pytest projects/sql-bi-agent/tests/ -v

# 3. Launch the API & Web Interface
uvicorn projects.sql-bi-agent.src.api:app --host 0.0.0.0 --port 8003 --reload

Open your browser to:

• Interactive Query Interface: http://localhost:8003/
• Swagger REST API Docs: http://localhost:8003/docs

Zero-Setup Evaluator Note: If GEMINI_API_KEY is not provided in your environment, the agent automatically activates its built-in Offline Mock Engine, returning instant, realistic queries and natural language answers for evaluator testing.

---

5. API Reference & Sample Payloads

POST /api/query

Accepts a natural language question and returns executed SQL, execution latency, tabular rows, and synthesized executive takeaways.

Request Body:

{
  "question": "What are our top 5 revenue generating products?"
}

Response Body:

{
  "status": "success",
  "question": "What are our top 5 revenue generating products?",
  "sql": "SELECT p.product_name, p.category, SUM(oi.quantity) AS units_sold, ROUND(SUM(oi.item_total), 2) AS total_revenue FROM products AS p JOIN order_items AS oi ON p.product_id = oi.product_id JOIN orders AS o ON oi.order_id = o.order_id WHERE o.order_status = 'completed' GROUP BY p.product_id, p.product_name, p.category ORDER BY total_revenue DESC LIMIT 5",
  "execution_time_ms": 4.12,
  "row_count": 5,
  "columns": ["product_name", "category", "units_sold", "total_revenue"],
  "data": [
    {
      "product_name": "ArcticRecovery Pro Cold Plunge",
      "category": "Wellness",
      "units_sold": 94,
      "total_revenue": 234906.0
    },
    {
      "product_name": "LuminaPro Max Red Light Panel",
      "category": "Wellness",
      "units_sold": 182,
      "total_revenue": 109018.0
    }
  ],
  "summary": "Analysis indicates that 'ArcticRecovery Pro Cold Plunge' is the leading revenue driver generating $234,906.00 in completed sales. Across the top 5 products, wellness and recovery equipment exhibits the strongest unit margins and sales velocity.",
  "is_mock": true
}

Injection Rejection Example (POST /api/query)

Request Body:

{
  "question": "Show all users; DROP TABLE orders;"
}

Response Body:

{
  "status": "error",
  "error_type": "SecurityGuardrailViolation",
  "message": "Query blocked by AST security guardrail: Multiple stacked SQL statements detected (2 statements). Only a single atomic SELECT statement is allowed.",
  "blocked_sql": "SELECT 1; DROP TABLE orders;",
  "execution_time_ms": 1.45
}