AI · NL→SQL

The DLM — primary path

The primary engine is the DLM (Data Language Model): a per-dataset compiled context artifact in the API (services/dlm.py). It routes the question to a dataset, resolves the natural-language terms to columns and values, and — for the common shapes — returns a precomputed answer with no database scan at all. Only novel slices touch the warehouse.

Question  →  POST /api/v1/dlm/ask
   route → which dataset · resolve terms → columns/values (value index)
        │
        ├─ precomputed shape?  →  ⚡ From context · no DB scan   (in-memory dict hit)
        │      totals · by-dimension · single-dimension filter
        │
        └─ novel slice/combo   →  Live query · Xs   (assemble ONE warehouse query → cache)

Every answer is badged honestly — ⚡ From context · no DB scan or Live query · Xs — with the real timing. Because the kaveonmeta context plane is physically separate from the kaveon warehouse, context answers never wait behind a big scan. See Architecture for the plane split.

The fallback — template parser

For shapes the DLM does not yet build (mainly time-series trends), the home page falls back to a template-based keyword parser that runs entirely in the browser (utils/nlToSql.ts). The rest of this page documents that fallback engine.

Fallback:  User question
      │
      ▼
Dataset auto-detection      → score every loaded schema, pick the best
      │
      ▼
nlToSql(query, schema)      → 7 ordered patterns, fuzzy column/metric resolution
      │
      ▼
POST /api/v1/sql/execute    → kaveon-api runs the SQL against the source
      │
      ▼
InlineChart renders ECharts → chart type chosen from the result shape

Dataset auto-detection

On load, the home page fetches schemas for every dataset you can access. When you submit a question, each schema is scored against the text and the highest score wins (ties go to the first). The chosen dataset is shown in the UI so you can override it.

score += 0.3   per dataset-name word that appears in the query
score += 0.2   per column name that appears
score += 0.2   per metric name that appears
score += confidence   from the NL→SQL parser for this schema

Pattern matching

The parser tries seven patterns in this priority order and returns on the first match.

1 · Aggregate only

Triggers on total, sum, count, average, avg, mean, min, max with no grouping words.

"total revenue"  ·  "average order amount"  ·  "count of customers"
→  SELECT SUM(revenue) FROM orders LIMIT 1        (chart: kpi)

2 · Top N

Triggers on top <number> <group> by <metric>.

"top 10 countries by total deaths"
→  SELECT country, SUM(deaths) FROM ... GROUP BY country
   ORDER BY SUM(deaths) DESC LIMIT 10               (chart: bar)

3 · Trend over time

Triggers on over time, trend, by month/year/week/day, monthly, yearly…

It finds the first date-typed column in the schema; the metric comes from the remaining tokens, or falls back to the first defined metric.

"show revenue over time"  ·  "trend of new cases monthly"
→  SELECT date_col, SUM(metric) FROM ... GROUP BY date_col
   ORDER BY date_col LIMIT 1000                     (chart: line)

4 · Compare X vs Y

Triggers on compare … vs / versus / and / against …

Extracts the two literal values, finds a string column to filter on and a metric. With a date column it renders a multi-series line; otherwise a grouped bar.

5 · Distribution / breakdown

Triggers on distribution, breakdown, spread.

"distribution of order status"  ·  "breakdown of regions"
→  SELECT col, COUNT(*) AS count FROM ... GROUP BY col
   ORDER BY count DESC LIMIT 1000

Chart type: always bar. (pickChartType supports a “pie under 8 distinct values” rule, but the distribution pattern doesn’t pass a group count, so that branch is currently inactive.)

6 · Grouped by dimension

Triggers on by, per, for each, grouped by, group by.

"revenue by region"  ·  "orders per category"
→  SELECT region, SUM(revenue) FROM ... GROUP BY region
   ORDER BY SUM(revenue) DESC LIMIT 1000

Chart type: line if the group column is a date, otherwise bar.

7 · Fallback word scan

When no pattern matches, the engine scans every word against columns and metrics and builds the best it can:

• group column + metric → grouped query (confidence 0.5)
• metric only → single-value KPI
• group column only → COUNT by that column, chart type table
• nothing matched → returns null; the assistant asks you to rephrase

Fuzzy column matching

Every query token is resolved to a real schema column by findColumn(token, columns, typeFilter?), which walks a cascade — exact match, then case-insensitive, then singular/plural and underscore/space variants, then substring — optionally constrained to a type (e.g. only date columns for trend queries). The same idea resolves metrics, so “sales” can map to a total_sales metric.

Automatic chart selection

The chart type is inferred from the query shape and result, not chosen by hand: kpi for single values, line for time series, bar for grouped comparisons and distributions, andtable as a safe fallback. InlineChart renders it with ECharts directly in the conversation.