Skip to content

Mirabelle AI Assistant — Internals

This page documents the internal architecture, modules, and configuration of Mirabelle, the conversational APS analyst built into ForecastAI. It is intended for developers extending or debugging the assistant — not for end users.

1. Architecture Overview

The pipeline transforms a natural-language question into a business answer with an optional chart, keeping the user's session context alive across turns.

flowchart LR
    Q[User question] --> API["/api/mirabelle/chat"]
    API --> R[Retriever<br/>LanceDB + keywords]
    R --> SG[SQL Generator<br/>Qwen3 via Ollama]
    SG --> SV[SQL Validator<br/>read-only guard]
    SV --> EX[ClickHouse Executor]
    EX --> EX2{Error?}
    EX2 -- Yes --> SG
    EX2 -- No --> EP[Explainer +<br/>Chart Advisor]
    EP --> M[Memory<br/>append turn]
    M --> RES[JSON Response]

    style API fill:#2563eb,color:#fff
    style R fill:#7c3aed,color:#fff
    style SG fill:#059669,color:#fff
    style SV fill:#dc2626,color:#fff
    style EX fill:#d97706,color:#fff
    style EP fill:#0891b2,color:#fff
    style M fill:#6d28d9,color:#fff

Key design decisions:

  • The LLM never receives the full schema — only what the retriever surfaces.
  • SQL generation is strictly JSON-structured; free-form text is never parsed.
  • A self-correction loop retries on validation or execution errors.
  • All inference runs locally via Ollama — no cloud LLM is contacted.

2. Module Reference

All Mirabelle code lives under files/mirabelle/. Each module is a single file with a clear responsibility boundary.

Module File Responsibility
service service.py End-to-end chat() entrypoint, retry loop, response shaping, lazy index bootstrap
retriever retriever.py Hybrid search: keyword extraction + LanceDB ANN + ontology boost
sql_generator sql_generator.py Qwen3 prompt assembly, Ollama /api/chat call, JSON extraction
sql_validator sql_validator.py SELECT/WITH only guard; blocks DDL/DML; sqlparse pre-check
executor executor.py Run SQL on ClickHouse, timeout guard, row cap, type serialisation
explainer explainer.py LLM-based result explanation; deterministic heuristic fallback
chart_advisor chart_advisor.py Pick chart type + axes from rows + LLM hint
memory memory.py In-process per-session deque (maxlen=6), 60-min TTL
indexer indexer.py Build/refresh LanceDB from JSON metadata + DB sample values
embeddings embeddings.py sentence-transformers/all-MiniLM-L6-v2 wrapper (384-dim); hash-based fallback
config config.py Env-var defaults (Ollama URL, model, paths, limits, feature flags)

3. Retrieval Layer Detail

3.1 LanceDB Tables

The retriever queries five on-disk LanceDB tables created by the indexer:

Table Source Content
schema_tables schema_metadata.json Table name, description, business purpose, tags, key columns
schema_columns schema_metadata.json Column name, type, description per (table, column)
business_glossary glossary.json APS terms, definitions, synonyms, related tables/columns
historical_sql sql_examples.json Few-shot question → SQL pairs
sample_values Live PG DB Actual dimension values (item names, site names, countries, pipelines)

3.2 Hybrid Retrieval Steps

flowchart TD
    Q[User question] --> KW[1. Keyword extraction<br/>regex match against<br/>entity names from PG]
    Q --> ONT[2. Ontology boost<br/>synonym → metric → table]
    Q --> EMB[3. Embed question<br/>all-MiniLM-L6-v2]
    EMB --> ANN[4. ANN search<br/>5 LanceDB tables]

    KW --> MERGE[5. Force-include tables<br/>for keyword + ontology hits]
    ONT --> MERGE
    ANN --> MERGE

    MERGE --> CTX[6. format_context<br/>concatenate sections<br/>truncate to 2 000 tokens]
    CTX --> PROMPT[SQL generator prompt]
  1. Keyword extraction — regex word-boundary match of the question against entity names loaded from PostgreSQL (items, locations, customers, countries, pipelines). Results are cached for 10 minutes.
  2. Ontology boost — the question is scanned for alert-ontology synonyms; matched metrics are mapped to implied tables (e.g. stock_days → PIPE_supply_inventory_projection).
  3. Embedding — the question is encoded to a 384-dim vector via all-MiniLM-L6-v2 (or the hash fallback when sentence-transformers is absent).
  4. ANN search — top-k nearest-neighbour queries on each LanceDB table (TOP_K_TABLES=5, TOP_K_COLUMNS=10, TOP_K_GLOSSARY=3, TOP_K_EXAMPLES=3, sample values top-5).
  5. Force-include — tables implied by keyword or ontology hits are force-injected if not already surfaced by semantic search. Missing tables are fetched from LanceDB by name, or a minimal stub is injected.
  6. Context assembly — format_context() concatenates glossary → tables → columns → examples → entities → ontology hits → sample values, truncating to the CONTEXT_TOKEN_BUDGET (2 000 tokens, approximated as 4 chars ≈ 1 token).

Keyword SQL fallback

When LanceDB is unavailable or the historical_sql table is empty, a word-overlap scorer ranks entries from sql_examples.json so the model still receives few-shot examples. This prevents table-name hallucination on fresh installs.

4. SQL Generation Prompt

4.1 System Prompt

The system prompt enforces strict JSON output and ClickHouse conventions. It includes built-in name column lists, critical column rules, and worked SQL examples to prevent common hallucination patterns.

Key rules enforced:

  • BUILT-IN NAME COLUMNS: Most PIPE_* tables have item_name, site_name built-in — no JOIN needed. Only tables without built-in names need JOINs on item_id/site_id directly (never splitByString('_', unique_id) — unique_id was dropped in the v6 migration).
  • NO forecast_accuracy — use smape or mape on PIPE_series_backtest_metrics.
  • NO country/region — filter by site_name LIKE '%Spain%'.
  • NO unique_id on any table — every series is keyed by item_id + site_id; PIPE_demand_corrected exposes item_id, site_id, item_name, site_name directly.
  • NO scenario_id on PIPE_forecast_netting — filter only by pipeline_id.
  • NO consumed column — use SUM(stat_consumed_qty + maint_consumed_qty + causal_consumed_qty).
  • Date arithmetic: always INTERVAL N UNIT syntax, never toIntervalWeek().
  • INTERACT_* tables have no unique_id or name columns — JOIN directly on item_id/site_id.

The prompt also includes 7 worked SQL examples for the most common query patterns (bad forecast, low fill rate, forecast consumption, outliers, seasonality, return forecast, service level by geography).

4.2 User Prompt

The user prompt is assembled from:

  1. Pipeline/scenario IDs — injected as pipeline_id = N; scenario_id = N.
  2. Retrieval context — the output of format_context().
  3. Conversation history — last 6 turns (role, content, assistant SQL).
  4. User question — the raw question text.
  5. Previous error — on retry, the last validation/execution error with a "Please correct the SQL." directive.

4.3 Ollama Call

payload = {
    "model":  "qwen2.5-coder:7b-instruct-q3_K_M",
    "messages": [
        {"role": "system", "content": SYSTEM_PROMPT},
        {"role": "user",   "content": user_prompt},
    ],
    "stream": False,
    "format": "json",    # request JSON-mode if model supports it
    "think":  False,     # disable qwen3 chain-of-thought
    "options": {
        "temperature": 0.0,
        "num_predict": 800,
        "num_ctx":    3072,
    },
}
# POST to http://localhost:31434/api/chat

4.4 JSON Extraction

The response content passes through a tolerant extractor (_extract_json):

  1. Strip markdown code fences.
  2. Try json.loads() on the full text.
  3. Regex-greedy match the first {…} block and parse that.

5. Self-Correction Loop

When the generated SQL fails validation or execution, the loop retries with the error message injected into the next prompt so the LLM can self-correct.

Before validation, four deterministic SQL fixers run to correct patterns the LLM repeatedly gets wrong:

  1. _fix_sql_placeholders() — replaces {pipeline_id} / {scenario_id} placeholders with concrete values.
  2. _fix_sql_interval_funcs() — rewrites toIntervalWeek(N) and subtract() to INTERVAL N UNIT syntax.
  3. _fix_sql_builtin_names() — when SQL references any table with splitByString('_', alias.unique_id), rewrites to alias.item_id / alias.site_id (unique_id was dropped from every table in the v6 migration) and removes unnecessary MASTER_item/MASTER_location JOINs on tables with built-in names.
  4. _fix_sql_scenario_ids() — resolves per-table scenario IDs from config_scenario_pipeline for the active pipeline_id; injects WHERE scenario_id = <id> for single-table queries or returns a retry hint for mixed-type joins that the LLM aliases on the next attempt.
for attempt in range(1, MAX_SQL_RETRIES + 1):
    payload = generate(question, context, history, pipeline_id, scenario_id, last_error)

    sql = payload["sql"]
    sql = _fix_sql_placeholders(sql, pipeline_id, scenario_id)
    sql = _fix_sql_interval_funcs(sql)
    sql = _fix_sql_builtin_names(sql)
    sql = _fix_sql_scenario_ids(sql, pipeline_id)

    ok, vmsg = validate(sql)
    if not ok:
        last_error = f"validation: {vmsg}"
        continue

    rows, cols, exec_err, ms = execute(sql, ch_client=ch_client)
    if exec_err:
        last_error = f"clickhouse: {exec_err}"
        continue

    return success(rows, payload)

return failure(last_error)

Retry budget

Default MAX_SQL_RETRIES = 2 (configurable via MIRABELLE_MAX_SQL_RETRIES). The attempts field in the response tells the front-end how many tries were needed.

6. Read-Only Enforcement

The SQL validator (sql_validator.py) applies multiple layers of defence:

6.1 Blocked Keywords

_BLOCKED = {
    "INSERT", "UPDATE", "DELETE", "DROP", "ALTER", "TRUNCATE",
    "CREATE", "GRANT", "REVOKE", "ATTACH", "DETACH", "RENAME",
    "OPTIMIZE", "EXCHANGE", "REPLACE", "MOVE", "MUTATE",
}

Each keyword is matched as a word-boundary token (\b…\b, case-insensitive).

6.2 Validation Pipeline

Step Check Rejection reason
1 Comment stripping --[^\n]* and /*…*/ removed before analysis
2 Leading keyword SQL must start with SELECT or WITH
3 Multi-statement Semicolons followed by more text are rejected
4 Blocked token Any blocked keyword as an identifier-bounded token
5 sqlparse parse Optional syntax pre-check when sqlparse is installed 
def validate(sql: str) -> Tuple[bool, str]:
    cleaned = strip_comments(sql).strip().rstrip(";")
    if not _LEADING_RE.match(cleaned):      # must start with SELECT/WITH
        return False, "SQL must start with SELECT or WITH"
    if len(inner_stmts) > 1:                # no multi-statement
        return False, "multiple statements not allowed"
    if blocked_match := _BLOCKED_TOKEN_RE.search(cleaned):
        return False, f"blocked SQL keyword: {blocked_match.group(1)}"
    return True, ""

7. Chart Auto-Selection

The chart advisor (chart_advisor.py) first honours the LLM's chart_type hint if it references columns that exist in the result. When the hint is absent or invalid, heuristic rules decide:

Condition Chart type
0 rows none
1 row kpi (cards showing numeric values)
Date column + numeric column(s) line
Category column + 1 numeric, n ≤ 30 bar
Category column + 2+ numerics, n ≤ 30 bar_stacked
Everything else table

Column classification:

  • date — column name matches (date|week|month|year|forecast_origin|…)$, or string values match YYYY-MM-DD.
  • numeric — first non-null value is int or float.
  • category — everything else.

The front-end (MirabellePanel.jsx) renders each type via Plotly (line, bar, bar_stacked), KPI cards, or an HTML table.

8. Session Memory

8.1 Storage

In-process Python dict keyed by session ID. Each session holds a collections.deque(maxlen=6) of turns. There is no persistence to disk — memory is lost on server restart, by design.

8.2 TTL

Sessions idle for more than 60 minutes (MEMORY_TTL_SECONDS = 3600) are evicted lazily on the next read or write.

8.3 Turn Structure

# User turn
{"role": "user", "content": "What's the forecast error for SKU X?", "ts": 1717521000.0}

# Assistant turn
{
    "role": "assistant",
    "content": "The average SMAPE for SKU X is 23.5%…",
    "sql": "SELECT … FROM PIPE_series_backtest_metrics …",
    "result_summary": "3 rows; columns: item_id, site_id, avg_smape, item_name",
    "ts": 1717521005.3,
}

The result_summary is truncated to 200 characters to prevent prompt bloat on follow-up turns.

8.4 Session ID Resolution

The API resolves a stable session ID with this priority order:

Priority Source Format
1 Explicit session_id in request body as-is
2 JWT jti claim u:<jti>
3 JWT user_id or email claim u:<user_id>
4 Client IP ip:<host>
5 Fallback anonymous 
def _mirabelle_session_id(request, body_session_id):
    if body_session_id:
        return body_session_id
    user = getattr(request.state, "user", None)
    if user:
        sid = user.get("jti") or user.get("user_id") or user.get("email")
        if sid:
            return f"u:{sid}"
    if request and request.client:
        return f"ip:{request.client.host}"
    return "anonymous"

Memory is cleared via POST /api/mirabelle/clear and on logout.

9. Configuration

All configuration is centralised in config.py and overridable via environment variables.

9.1 Ollama / LLM

Env var Default Description
MIRABELLE_OLLAMA_URL http://localhost:31434 Ollama API base URL
MIRABELLE_LLM_MODEL qwen2.5-coder:7b-instruct-q3_K_M Model name for SQL generation and explanation
MIRABELLE_LLM_TIMEOUT 180 Request timeout in seconds

9.2 Embeddings

Env var Default Description
MIRABELLE_EMBED_MODEL sentence-transformers/all-MiniLM-L6-v2 Embedding model ID
MIRABELLE_EMBED_DIM 384 Embedding dimensionality

9.3 Retrieval

Env var Default Description
MIRABELLE_TOP_K_TABLES 5 ANN top-k for schema tables
MIRABELLE_TOP_K_COLUMNS 10 ANN top-k for schema columns
MIRABELLE_TOP_K_GLOSSARY 3 ANN top-k for business glossary
MIRABELLE_TOP_K_EXAMPLES 3 ANN top-k for historical SQL
MIRABELLE_CONTEXT_TOKENS 2000 Max approximate tokens in the context block

9.4 SQL Generation & Execution

Env var Default Description
MIRABELLE_MAX_SQL_RETRIES 2 Self-correction loop iterations
MIRABELLE_SQL_ROW_LIMIT 500 Default LIMIT injected in the prompt
MIRABELLE_EXEC_TIMEOUT_S 8 ClickHouse query timeout in seconds

9.5 Memory

Env var Default Description
MIRABELLE_MEMORY_TURNS 6 deque maxlen per session
MIRABELLE_MEMORY_TTL_S 3600 Session TTL in seconds (60 min)

9.6 Cache & Paths

Env var Default Description
MIRABELLE_LANCEDB_PATH files/mirabelle/lancedb On-disk LanceDB directory
MIRABELLE_CACHE_SIZE 64 Response cache max entries
MIRABELLE_CACHE_TTL_S 300 Response cache TTL in seconds

9.7 Feature Flags

Env var Default Description
MIRABELLE_ENABLE_EXPLAINER 1 Enable LLM-based result explanation (set 0 to use heuristic only)
MIRABELLE_ENABLE_LANCEDB 1 Enable LanceDB retrieval (set 0 for keyword-only mode)

10. API Surface

All endpoints are prefixed with /api/mirabelle.

10.1 POST /api/mirabelle/chat

Main entrypoint: NL question → retrieval → SQL → execute → explanation + chart.

Request:

{
  "question": "Show top 20 items with the largest forecast error",
  "pipeline_id": 1,
  "scenario_id": 2,
  "session_id": ""
}

Response (200):

{
  "answer": "The top items by forecast error are…",
  "sql": "SELECT bm.item_id, bm.site_id, i.name AS item_name … LIMIT 20",
  "tables_used": ["PIPE_series_backtest_metrics"],
  "concepts_used": ["Forecast Accuracy"],
  "chart": {
    "type": "bar",
    "x_col": "item_name",
    "y_cols": ["avg_smape"],
    "title": "Top 20 items by forecast error"
  },
  "data": [{"item_name": "SKU-A", "site_name": "DC-EU", "avg_smape": 0.42}],
  "columns": ["item_id", "site_id", "item_name", "site_name", "avg_smape"],
  "row_count": 20,
  "attempts": 1,
  "duration_ms": 4230.5,
  "session_id": "u:abc123"
}

Error response (on max retries exhausted):

{
  "answer": "Sorry — I couldn't generate a valid query after 2 attempts. Last error: …",
  "sql": "SELECT …",
  "tables_used": [],
  "concepts_used": [],
  "chart": {"type": "none", "x_col": null, "y_cols": [], "title": ""},
  "data": [],
  "columns": [],
  "row_count": 0,
  "attempts": 2,
  "duration_ms": 6100.0,
  "error": "clickhouse: Missing columns: …"
}

10.2 GET /api/mirabelle/status

Health probe — Ollama availability, model, LanceDB index stats, memory stats.

Response (200):

{
  "ollama_url": "http://localhost:31434",
  "model": "qwen3:14b",
  "ollama_available": true,
  "available_models": ["qwen3:14b", "llama3:8b"],
  "embedding_model": "sentence-transformers/all-MiniLM-L6-v2",
  "embedding_fallback": false,
  "lancedb_ready": true,
  "lancedb_path": "files/mirabelle/lancedb",
  "index_stats": {
    "schema_tables": 12,
    "schema_columns": 85,
    "business_glossary": 8,
    "historical_sql": 6,
    "sample_values": 340
  },
  "memory": {
    "sessions": 3,
    "total_turns": 14,
    "max_turns_per_session": 6,
    "ttl_seconds": 3600
  }
}

10.3 POST /api/mirabelle/index

Admin-only. Trigger a full LanceDB rebuild from JSON metadata + DB sample-value sweep.

Response (200):

{
  "status": "ok",
  "counts": {
    "schema_tables": 12,
    "schema_columns": 85,
    "business_glossary": 8,
    "historical_sql": 6,
    "sample_values": 340
  }
}

Error (403): {"detail": "admin only"}

10.4 GET /api/mirabelle/history

Return the current session's conversation history.

Query params: session_id (optional)

Response (200):

{
  "session_id": "u:abc123",
  "history": [
    {"role": "user", "content": "Show top forecast errors", "ts": 1717521000.0},
    {"role": "assistant", "content": "The top items…", "sql": "SELECT …", "result_summary": "20 rows", "ts": 1717521005.0}
  ]
}

10.5 POST /api/mirabelle/clear

Clear the current session's conversation history.

Response (200):

{
  "cleared": true,
  "session_id": "u:abc123"
}

11. Building the Index

CLI

# Full rebuild (schema + glossary + examples + sample values from DB)
python -m files.mirabelle.indexer build

# Incremental refresh (glossary + SQL examples only — no DB connection needed)
python -m files.mirabelle.indexer refresh

# Row counts per table
python -m files.mirabelle.indexer stats

# Wipe the LanceDB directory
python -m files.mirabelle.indexer clear

HTTP

POST /api/mirabelle/index    # admin only; full rebuild

Automatic bootstrap

On the first chat() call, if the LanceDB directory is empty, the service lazily runs indexer.build() behind a thread lock. This means the first question may be slow; subsequent calls are fast.

Sample values require a DB connection

The build CLI command and HTTP endpoint accept an optional PostgreSQL connection to populate sample_values. Without it, the table is created but left empty. The auto-bootstrap runs without a connection, so sample values are only available after an explicit POST /api/mirabelle/index.

12. Performance Targets

These are the latency budgets for each stage of the pipeline on a single concurrent request (CPU-only, no GPU):

Stage Target Notes
Embed question (all-MiniLM-L6-v2) < 50 ms CPU inference on 1 sentence
LanceDB ANN search (5 tables) < 100 ms On-disk, local SSD
Full retrieval (keywords + ANN + ontology) < 200 ms Includes keyword regex + force-include
SQL generation (Ollama /api/chat) < 30 s qwen2.5-coder:7b-instruct-q3_K_M, num_predict=800, GPU-accelerated
ClickHouse execution < 2 s max_execution_time=8s, most queries < 500 ms
Explanation (Ollama /api/chat) < 15 s num_predict=256, temp=0.2
Total end-to-end < 60 s Single attempt; +30 s per retry

Warming up

First-call latency is dominated by model loading (~70 s for qwen2.5-coder:7b-instruct-q3_K_M on first Ollama call on CPU, ~20 s with GPU). Sending a GET /api/mirabelle/status on page load triggers the embedding model load and checks Ollama availability, effectively warming the cache.

13. Anti-Hallucination Measures

The LLM frequently generates the same incorrect SQL patterns. A multi-layer defence prevents hallucinated columns and tables from reaching ClickHouse:

13.1 Ground-Truth Schema

schema_metadata.json is the single source of truth for table and column definitions. It was rebuilt from DESCRIBE TABLE output against the live ClickHouse instance to match the actual schema exactly. Key facts that the LLM frequently gets wrong:

  • Most PIPE_* tables have built-in item_name, site_name — no JOIN needed.
  • There is NO country, region, or forecast_accuracy column in any table.
  • PIPE_demand_corrected has NO unique_id — it uses item_id/site_id directly.
  • PIPE_forecast_netting has NO scenario_id — filter only by pipeline_id.

13.2 System Prompt Rules

The system prompt in sql_generator.py includes explicit column rules and worked SQL examples for the most common query patterns:

  • "Bad forecast" → smape > 0.3 on PIPE_series_backtest_metrics
  • "Low fill rate" → fill_rate < 0.9 on PIPE_meio_results
  • "Forecast consumption" → SUM(stat_consumed_qty + maint_consumed_qty + causal_consumed_qty) on PIPE_forecast_netting
  • "Outliers" → GROUP BY item_id, item_name, site_id, site_name on PIPE_demand_corrected
  • "Seasonality" → has_seasonality = 1 on PIPE_series_characteristics
  • "Return forecast" → firm_return_qty on PIPE_forecast_netting

13.3 Deterministic SQL Fixers

Four regex fixers in service.py rewrite SQL before validation, handling patterns the LLM repeatedly generates even after correction:

Fixer What it fixes
_fix_sql_placeholders() {pipeline_id} → concrete value
_fix_sql_interval_funcs() toIntervalWeek(N) → INTERVAL N WEEK
_fix_sql_builtin_names() splitByString('_', alias.unique_id) → alias.item_id / alias.site_id on any table, removes redundant JOINs on tables with built-in names
_fix_sql_scenario_ids() resolves per-table scenario IDs from config_scenario_pipeline; injects WHERE scenario_id = <id> for single-table queries or returns a retry hint for mixed-type joins

13.4 Column Validation

sql_validator.py builds _TABLE_COLUMNS from schema_metadata.json and checks every alias.column reference against the known columns. Bare column names in aggregates (e.g. SUM(consumed)) pass validation but fail at ClickHouse — the deterministic fixers and prompt rules cover most of these.

13.5 Synonym → Table Mapping

config.py:SYNONYM_TABLE_MAP maps question keywords to the correct table, preventing the LLM from choosing the wrong table (e.g. "return" → PIPE_forecast_netting instead of INTERACT_sales_return, "seasonality" → PIPE_series_characteristics instead of PIPE_demand_corrected).

13.6 Row-Click Navigation

_navTarget() in MirabellePanel.jsx maps the query's tables_used to the correct detail page tab:

Table Navigate to
PIPE_forecast_netting /series/:unique_id?tab=netting
PIPE_meio_results /pipelines?unique_id=...
PIPE_demand_corrected /series/:unique_id?tab=outlier
PIPE_supply_* /supply?unique_id=...