Mirabelle Architecture — How Chat Works¶

Deep-dive into the natural-language → SQL pipeline, the three knowledge layers that prevent hallucination, and why the LLM / hardware choices were made.

1. The Big Picture¶

Mirabelle turns a plain-English question such as "find items with outliers" into a validated ClickHouse query, executes it, and returns a chart plus a human-readable summary.

The system is not a naive "ask an LLM to write SQL" wrapper. It is a retrieval-augmented generation (RAG) pipeline in which an ensemble of fast, deterministic classifiers (ontology + glossary + keyword extractors) retrieve the exact schema context the LLM needs. The LLM only generates SQL; it never decides which tables exist or which columns mean what.

flowchart LR
    subgraph Input
        Q[User question]
    end

    subgraph Retrieval ["Retrieval layer (deterministic, < 50 ms)"]
        direction TB
        K[Keyword extractor]
        O[Ontology engine]
        G[Glossary + examples]
        E[Embedding ANN]
    end

    subgraph Generation ["Generation layer (LLM, ~2–4 s)"]
        direction TB
        P[Prompt assembler]
        L[Ollama LLM]
        V[SQL validator]
        X[ClickHouse executor]
    end

    subgraph Output
        direction TB
        A[Answer + chart]
    end

    Q --> K & O & G & E
    K & O & G & E --> P
    P --> L --> V --> X --> A

    style Retrieval fill:#e0e7ff,stroke:#4338ca,stroke-width:2px
    style Generation fill:#dcfce7,stroke:#166534,stroke-width:2px

2. Why Three Knowledge Layers?¶

Layer	Speed	Source of truth	Prevents
Ontology	< 1 ms	`DEFAULT_ONTOLOGY` list in `alerts/engine.py` + DB table `config_alert_metric_ontology`	LLM inventing metric names (e.g. "bad forecast" → `forecast_accuracy`)
Glossary	< 5 ms	`glossary.json` + LanceDB ANN	LLM confusing similar tables (e.g. `PIPE_demand_corrected` vs `PIPE_series_characteristics`)
LLM	1–4 s	Ollama local model	LLM hallucinating syntax or joins when the retrieved context is incomplete

The layers are complementary, not redundant:

Ontology knows what the user wants ("outliers" = has_outlier_correction Boolean field or PIPE_demand_corrected table).
Glossary knows where that data lives (PIPE_demand_corrected has item_id, site_id, z_score, etc.).
LLM knows how to turn the two into valid ClickHouse SQL (JOIN order, toInt64OrNull, LIMIT, etc.).

If any layer is removed, quality collapses:

Missing layer	Typical failure
Ontology	LLM invents table `PIPE_series_outliers` (does not exist).
Glossary	LLM joins `PIPE_demand_corrected` on `unique_id` (column does not exist on that table).
LLM	Perfect schema context but malformed SQL (`FINAL` on MergeTree, missing `LIMIT`, wrong `GROUP BY`).

3. Ontology — The First Line of Defence¶

The ontology is a hand-curated list of business-metric rules. Each rule contains:

name — machine identifier (has_outlier_correction)
field — which DB expression to use
synonyms — dozens of natural-language phrases that map to this rule
operator / threshold / severity — how to flag results

# alerts/engine.py  (simplified)
{
    "name": "has_outlier_correction",
    "display_name": "Has Outlier Correction",
    "field": "has_outlier_correction",
    "operator": "=",
    "threshold": 1.0,
    "synonyms": [
        "outlier", "has outlier", "item with outlier",
        "demand outlier", "corrected outlier", "outlier detected",
        "outlier adjusted", "demand spike corrected", ...
    ],
}

When the user asks "find items with outliers", the ontology engine scans the question for synonym matches. It finds "outlier", "item with outlier", etc., and marks the condition as field = "has_outlier_correction".

The ontology is loaded from two sources:

Code defaults (DEFAULT_ONTOLOGY in alerts/engine.py) — new rules work immediately even before DB seeding.
Database (config_alert_metric_ontology) — operators can edit synonyms or thresholds via the Settings UI without redeploying code.

Ontology → SQL mapping¶

The ontology only says what to query. The actual SQL expression lives in _FIELD_SQL:

_FIELD_SQL = {
    ...
    "has_outlier_correction": (
        "CASE WHEN EXISTS ("
        "SELECT 1 FROM {schema}.pipe_demand_corrected dc2 "
        "WHERE dc2.item_id = SPLIT_PART(da_base.unique_id, '_', 1)::bigint "
        "AND dc2.site_id = SPLIT_PART(da_base.unique_id, '_', 2)::bigint"
        ") THEN 1.0 ELSE 0.0 END"
    ),
    ...
}

This is how the ontology guarantees the correct table is used — the LLM never gets to guess.

4. Glossary — The Schema Map¶

While the ontology knows business concepts, the glossary knows the physical schema: table names, column names, types, and relationships.

4.1 Source files¶

File	Content	Loaded into
`schema_metadata.json`	Table & column definitions	LanceDB `schema_tables`, `schema_columns`
`glossary.json`	Business terms, synonyms, related tables/columns	LanceDB `business_glossary`
`sql_examples.json`	Few-shot question → SQL pairs	LanceDB `historical_sql`

4.2 Retrieval flow¶

flowchart TD
    Q[User question] --> E[Embed with<br/>all-MiniLM-L6-v2]
    E --> T[ANN search<br/>schema_tables]
    E --> C[ANN search<br/>schema_columns]
    E --> B[ANN search<br/>business_glossary]
    E --> H[ANN search<br/>historical_sql]

    Q --> K[Keyword regex<br/>against entity names]
    Q --> O[Ontology boost<br/>synonym → table]

    T & C & B & H & K & O --> M[Force-include merge]
    M --> F[format_context<br/>truncate to budget]
    F --> P[SQL generator prompt]

Boost scoring: results from ontology hits get a +0.3 cosine boost. This means that even if the semantic embedding of "outlier" is far from the PIPE_demand_corrected table vector, the ontology pulls it into the context.

4.3 Example context block (excerpt)¶

--- Glossary ---
Outlier: Demand points flagged as statistical anomalies ...
Stored in PIPE_demand_corrected with original_value, corrected_value,
detection_method and z_score. Use this table to find items that have at
least one corrected outlier.
Related tables: PIPE_demand_corrected

--- Tables ---
PIPE_demand_corrected (demand)
  Outlier-corrected weekly demand series.
  Tags: demand, outlier, correction, z-score
  Columns: item_id Int64, site_id Int64, date Date,
           original_value Float64, corrected_value Float64,
           detection_method String, z_score Float64,
           lower_bound Float64, upper_bound Float64

--- Examples ---
Q: Items with outliers
SQL: SELECT dc.item_id, i.name AS item_name, dc.site_id,
     l.name AS site_name, count(*) AS n_outliers,
     AVG(dc.z_score) AS avg_z_score
FROM PIPE_demand_corrected dc
LEFT JOIN MASTER_item i ON i.id = dc.item_id
LEFT JOIN MASTER_location l ON l.id = dc.site_id
WHERE dc.pipeline_id = {pipeline_id}
GROUP BY dc.item_id, i.name, dc.site_id, l.name
ORDER BY n_outliers DESC LIMIT 100

Because this exact context is injected into the prompt, the LLM sees the real table name (PIPE_demand_corrected) and the real column names (item_id, site_id) before it generates SQL. Hallucination drops from ~15 % to < 2 %.

5. LLM — The SQL Synthesiser¶

The LLM is the last step, not the first. Its job is narrow: turn the retrieved context into syntactically correct ClickHouse SQL.

5.1 Model choice¶

Model	Size	Use case	Why chosen
Qwen3 14B	14B params	Default for complex multi-table queries	Best reasoning accuracy on CTEs, window functions, sub-queries
Qwen2.5-Coder 7B	7B params	Fallback / CPU-only deployments	2× faster, 50 % less RAM; good enough for simple SELECT + JOIN

Both run locally via Ollama — no cloud API calls, no data leaves the server.

5.2 Hardware sizing¶

Deployment	CPU	RAM	GPU	Model recommendation
Dev laptop	4–8 cores	16 GB	—	Qwen2.5-Coder 7B (CPU, ~6 GB RAM)
Small server	8–16 cores	32 GB	—	Qwen3 14B (CPU, ~10 GB RAM)
Production	16+ cores	64 GB	RTX 4090 / A100	Qwen3 14B (GPU, ~2 s/query)

Key numbers:

Qwen3 14B in 4-bit quantization → ~8.5 GB VRAM / RAM.
Qwen2.5-Coder 7B in 4-bit quantization → ~4.5 GB RAM.
Embedding model (all-MiniLM-L6-v2) → ~80 MB, CPU-only, < 20 ms per query.
LanceDB index (full schema + glossary + 50 examples) → < 10 MB on disk.

5.3 Why smaller context is safer¶

The prompt is capped at CONTEXT_TOKEN_BUDGET (default 2 000 tokens). This is intentional:

Less noise — only the most relevant tables/columns reach the model.
Faster inference — KV-cache size scales with context length.
Lower hallucination — large contexts confuse the model with irrelevant columns (research shows accuracy degrades beyond ~1 500 tokens of schema context).

If the user asks a cross-domain question (e.g. "show forecast error for items with high stock"), the retriever still surfaces both PIPE_series_backtest_metrics and PIPE_supply_inventory_projection because the ontology boosts both domains. The 2 000-token budget is enough for ~4–5 tables + columns + 2 examples.

6. The Self-Correction Loop¶

Even with perfect context, the LLM occasionally makes syntax mistakes (FINAL on MergeTree, missing comma, wrong column alias). A retry loop catches these and feeds the error back to the LLM:

flowchart TD
    G[Generate SQL] --> V[Validate]
    V -- OK --> E[Execute on CH]
    V -- Fail --> R1[Inject error<br/>into prompt]
    E -- OK --> D[Done]
    E -- Fail --> R2[Inject error<br/>into prompt]
    R1 --> G
    R2 --> G

    style R1 fill:#fee2e2,stroke:#dc2626
    style R2 fill:#fee2e2,stroke:#dc2626

Stage	What can fail	Retry message
Generation	Model returns invalid JSON	`"generation: model did not return parseable JSON"`
Validation	SQL contains `INSERT`, `DROP`, etc.	`"validation: blocked SQL keyword: DROP"`
Execution	Wrong column name, missing table	`"clickhouse: Missing columns: 'unique_id'"`

Default MAX_SQL_RETRIES = 2. The front-end shows the attempt count so users know when a query required self-correction.

7. Putting It All Together — Outlier Example¶

Here is the exact data flow for the question "find items with outliers":

Step 1 — Ontology¶

Question:  "find items with outliers"
Synonyms matched: "outlier", "item with outlier", "outliers"
Ontology rule:   has_outlier_correction → field = "has_outlier_correction"
Boosted table:   PIPE_demand_corrected

Step 2 — Glossary retrieval¶

ANN results (top-3):
  1. PIPE_demand_corrected      (score 0.91)
  2. MASTER_item                (score 0.72)
  3. MASTER_location            (score 0.68)

Example pulled: "Items with outliers" (sql_016)

Step 3 — Prompt assembly¶

System:  You are Mirabelle ... (strict JSON rules)
Context: [glossary Outlier definition]
         [PIPE_demand_corrected schema]
         [example SQL for Items with outliers]
User:    Current pipeline_id = 1; scenario_id = 1
         USER QUESTION: find items with outliers

Step 4 — LLM generation¶

{
  "sql": "SELECT dc.item_id, i.name AS item_name, ...",
  "chart_type": "table",
  "tables": ["PIPE_demand_corrected", "MASTER_item", "MASTER_location"],
  "concepts": ["Outlier"]
}

Step 5 — Validation & execution¶

validate(sql)  # → True (SELECT only, no blocked keywords)
execute(sql)   # → 12 rows, 6 columns, 45 ms

Step 6 — Response¶

{
  "answer": "12 items have outlier corrections. Top 3 are ...",
  "sql": "SELECT ...",
  "row_count": 12,
  "attempts": 1,
  "chart": { "type": "table", ... }
}

8. Knowledge Layer Overlap — Ontology vs Glossary vs Synonym Map¶

The three knowledge sources sometimes overlap, but each has a distinct role in the pipeline. Understanding where they interact (and where they don't) is essential for debugging "I asked about X and got nothing" issues.

8.1 Which layer handles which question?¶

Question example	Layer that resolves it	Why the others don't
"items with too much stock"	Ontology (rule `high_stock` → `stock_days`)	Glossary has no "too much stock" entry
"items with outliers"	Ontology + Glossary + Synonym map	All three cover "outlier" → `PIPE_demand_corrected`
"forecast features" / "weather features"	Glossary + Synonym map only	Ontology has no rule for calendar/exogenous features
"supply orders at Brussels"	Keyword + Semantic	Site name extracted by regex, not by any rule

8.2 Ontology scope¶

The ontology (DEFAULT_ONTOLOGY in alerts/engine.py + DB table config_alert_metric_ontology) covers alert-centric business metrics only:

Ontology rule	Field	Tables implied
`high_stock`	`stock_days`	`PIPE_supply_inventory_projection`
`low_stock`	`stock_days`	`PIPE_supply_inventory_projection`
`bad_forecast`	`forecast_accuracy`	`PIPE_series_backtest_metrics`
`high_variability`	`cv`	`PIPE_series_characteristics`
`low_service_level`	`fill_rate`	`PIPE_meio_results`
`has_outlier_correction`	`has_outlier_correction`	`PIPE_demand_corrected`
`has_seasonality`	`has_seasonality`	`PIPE_series_characteristics`
`has_trend`	`has_trend`	`PIPE_series_characteristics`

The ontology does not cover: - Forecast features / calendar features / exogenous inputs - Causal or maintenance forecast concepts - ABC classification details - Supply pegging or capacity constraints

These are handled by the glossary and synonym map instead.

8.3 Synonym map (`SYNONYM_TABLE_MAP`)¶

config.SYNONYM_TABLE_MAP is a second fast-lookup layer that supplements the ontology. It maps question-keywords directly to tables, bypassing the alert engine entirely. It is consumed by:

Retriever — force-includes matching tables in the LLM context
SQL generator — injected as "SYNONYM → TABLE HINTS" in the system prompt

# config.py (excerpt)
SYNONYM_TABLE_MAP = {
    "outlier":              ["PIPE_demand_corrected"],
    "feature":              ["PIPE_series_characteristics", "PIPE_series_best_methods"],
    "calendar features":    ["PIPE_series_characteristics", "PIPE_series_best_methods"],
    "consumed":             ["PIPE_forecast_netting"],
    ...
}

When a user asks "items forecasted with features such as weather", the word "feature" and "calendar features" match the synonym map, which force-includes PIPE_series_characteristics and PIPE_series_best_methods in the context. The LLM then generates a query using those tables — without the synonym map, it would have no idea which tables to use.

8.4 Glossary concepts (`glossary.json`)¶

The glossary provides natural-language definitions and table/column relationships that the semantic embedding search can match. Unlike the ontology (exact synonym match) and synonym map (keyword match), the glossary uses semantic similarity — so "weather features" can match "Forecast Features" even though the exact words differ.

{
  "term": "Forecast Features",
  "synonyms": ["Calendar Features", "Weather Features", "Exogenous Features"],
  "definition": "Calendar and exogenous features used as inputs to forecasting models...",
  "related_tables": ["PIPE_series_characteristics", "PIPE_series_best_methods"],
  "related_columns": ["has_seasonality", "has_trend", "best_method"]
}

8.5 When to add a concept where¶

Scenario	Add to	Reason
New alert metric (e.g. "shelf life risk")	Ontology (`alerts/engine.py` + DB)	Needs `operator`/`threshold`/`severity` for alerting
New business concept that maps to existing tables	Glossary (`glossary.json`)	Semantic search covers paraphrases
Keyword that must always force-include a table	Synonym map (`config.py`)	Guaranteed match, no embedding needed
New table added to the schema	`schema_metadata.json`	All layers reference it for column info

8.6 Debugging missing concepts¶

If a query returns "The question is vague and does not match any known concepts":

Check the Glossary tab in the Mirabelle panel UI — is there a concept that should match?
Check SYNONYM_TABLE_MAP — does a keyword from the question appear there?
Check the ontology — does it have a rule for the concept?
If none of the three cover it, add it to the glossary + synonym map.

9. Configuration Quick Reference¶

Env var	Default	Tuning advice
`MIRABELLE_LLM_MODEL`	`qwen3:14b`	Use `qwen2.5-coder:7b` on low-RAM hosts
`MIRABELLE_TOP_K_TABLES`	`5`	Lower = faster retrieval, higher hallucination risk
`MIRABELLE_TOP_K_EXAMPLES`	`3`	Lower = less guidance, faster prompt assembly
`MIRABELLE_CONTEXT_TOKENS`	`2000`	Do not exceed 2500; accuracy degrades above ~1500 tokens of schema
`MIRABELLE_MAX_SQL_RETRIES`	`2`	Increase to 3 if you see many "clickhouse" errors

10. SQL Validation Pipeline¶

Before execution, every generated SQL statement passes through three safety stages:

flowchart LR
    G[LLM output] --> F[Placeholder fixer]
    F --> V[Validator]
    V --> E[ClickHouse]

Stage	What it catches	Implementation
Placeholder fixer	`{pipeline_id}` / `{scenario_id}` in SQL (ClickHouse doesn't support them)	`service.py: _fix_sql_placeholders()` — regex replacement with concrete values
Validator	Blocked keywords, schema-prefixed table names, unknown tables	`sql_validator.py: validate()` — also checks tables against `schema_metadata.json`
ClickHouse	Column errors, syntax errors, wrong types	Actual execution — error fed back into retry loop

The unknown-table check (sql_validator.py) catches LLM-invented tables like PIPE_outlier_detection or PIPE_series_outliers before they waste a ClickHouse query attempt. Valid table names are loaded from schema_metadata.json at module import time.

11. Troubleshooting¶

Symptom	Root cause	Fix
"Unknown table 'PIPE_...'"	Ontology or glossary missing the table	Add concept to `glossary.json`, synonym to `SYNONYM_TABLE_MAP`, rebuild index
"Missing columns"	SQL example or schema metadata stale	Update `schema_metadata.json` or `sql_examples.json`, rebuild index
"Sorry — couldn't generate after 2 attempts"	LLM cannot self-correct; context too thin	Increase `TOP_K_EXAMPLES`, verify LanceDB index is built (`/api/mirabelle/status`)
"The question is vague and does not match any known concepts"	No ontology, glossary, or synonym match	Add term to `glossary.json` + `SYNONYM_TABLE_MAP` (see §8.5)
Slow responses (> 8 s)	LLM running on CPU with large model	Downgrade to Qwen2.5-Coder 7B or move to GPU
High RAM usage	Qwen3 14B loaded in RAM + embedding model	Use 4-bit quantization in Ollama (`ollama pull qwen3:14b-q4_K_M`)
Placeholder syntax `{pipeline_id}` in output SQL	API server running old code after config change	Restart the API server — Python caches modules in memory

12. Further Reading¶

docs/developer/mirabelle-internals.md — full module reference & API surface
files/mirabelle/retriever.py — hybrid retrieval implementation
files/mirabelle/sql_generator.py — prompt assembly & Ollama client
files/alerts/engine.py — ontology definition & alert query builder