SQL To BI Builder

Overview

Use this skill to transform

sql.md

query collections into a service-based BI prototype. This skill must generate both backend and frontend services from SQL-derived artifacts.

Workflow

1. Parse markdown SQL blocks into a normalized query catalog.
2. Infer query semantics (metrics, dimensions, time columns, grain hints).
3. Extract P0 filter candidates from SQL DSL (

   WHERE

   predicates) into structured filter metadata (

   dsl_ast

   first, regex fallback).
4. Recommend chart types from inferred semantics.
5. Build a dashboard specification with layout coordinates.
6. Generate a UI scaffold that renders the dashboard structure.
7. Generate service bundle (

   services/backend

   +

   services/frontend

   ) that depends on generated SQL artifacts.

Input Contract

Expect one markdown file with one or more SQL fenced blocks. Use this pattern for best results:

# Sales Dashboard

## card: Daily GMV
- id: daily_gmv
- datasource: mysql_prod
- refresh: 5m
- chart: auto
- filters: date, region

```sql
SELECT DATE(pay_time) AS dt, SUM(amount) AS gmv
FROM orders
WHERE pay_status = 'paid'
GROUP BY 1
ORDER BY 1;

Rules:
- Keep one logical query per SQL fenced block.
- Provide stable `id` metadata when possible.
- Keep aliases explicit (`AS alias`) to improve semantic inference.

## Python Environment Setup (Required)
Run from the skill folder.

1. Ensure `python3.11` is installed and available in `PATH`.
   If missing, follow `references/install_python311.md`.
2. Create virtual environment:

```bash
bash scripts/setup_venv.sh

3. Activate and verify:

source .venv/bin/activate
python --version

Expected version:

Python 3.11.x

.

Use

--with-dev

when dev dependencies are needed:

bash scripts/setup_venv.sh --with-dev

Run Commands

After activating

.venv

, run pipeline and service generation:

python scripts/run_pipeline.py \
  --input /abs/path/sql.md \
  --out /abs/path/out \
  --with-services

Run each step separately when debugging:

python scripts/parse_sql_md.py --input /abs/path/sql.md --output /abs/path/out/query_catalog.json
python scripts/infer_semantics.py --input /abs/path/out/query_catalog.json --output /abs/path/out/semantic_catalog.json
python scripts/recommend_chart.py --input /abs/path/out/semantic_catalog.json --output /abs/path/out/chart_plan.json
python scripts/build_dashboard_spec.py --queries /abs/path/out/query_catalog.json --semantics /abs/path/out/semantic_catalog.json --charts /abs/path/out/chart_plan.json --output /abs/path/out/dashboard.json
python scripts/generate_ui_scaffold.py --dashboard /abs/path/out/dashboard.json --out /abs/path/out/ui
python scripts/generate_service_bundle.py --artifacts /abs/path/out --output /abs/path/out/services

Start generated services:

bash /abs/path/out/services/start_backend.sh
bash /abs/path/out/services/start_frontend.sh

Runtime And Version Control

• Use Python

  3.11.x

  only.
• Keep

  .python-version

  at

  3.11

  .
• Keep

  pyproject.toml

  requires-python = ">=3.11,<3.12"

  .
• Install dev dependency before running upstream validator:

  pip install -r requirements-dev.txt

  .
• Commit changes by scope: parser, semantics, chart rules, layout rules, scaffold.
• Tag stable milestones using semantic version tags such as

  v0.1.0

  ,

  v0.2.0

  .

Outputs

• query_catalog.json

  : Parsed query units and metadata.

• semantic_catalog.json

  : Field roles, grain hints, and

  dsl_filters

  extracted from SQL conditions.

  dsl_filters

  includes

  value_type

  and

  value_format

  , with date support for:

  yyyy-mm-dd

  ,

  yyyy/mm/dd

  ,

  yyyymmdd

  ,

  yyyy-mm-dd hh:mm:ss

  , ISO-8601,

  yyyymmdd_int

  , unix second/ms integers.

• chart_plan.json

  : Recommended chart type per query.

• dashboard.json

  : Final dashboard definition for rendering, including page-level

  global_filters

  .

• ui/

  : Static UI scaffold (

  index.html

  ,

  app.js

  ,

  style.css

  ).

• services/backend

  : FastAPI backend service using generated artifacts.

• services/frontend

  : Frontend service consuming backend API.

• services/start_backend.sh

  and

  services/start_frontend.sh

  : service start scripts.

UI Upgrade Notes (2026-03)

When using repo-level service UI (

services/frontend

), the upgraded experience includes:

• KPI summary strip (click-to-focus widgets)
• Layout switch (

  Classic

  /

  Focus

  )
• New

  Midnight Ops

  theme preset
• stronger visual hierarchy for demos

Heuristic References

Load only the file needed for the current issue:

• SQL parsing and naming constraints:

  references/sql_style.md

• Chart mapping rules:

  references/chart_rules.md

• BI layout and widget sizing:

  references/layout_rules.md

• Python 3.11 installation and venv setup:

  references/install_python311.md

Limits And Escalation

Treat current scripts as heuristic MVP. Escalate for manual review when SQL includes nested CTE chains, window-heavy ranking logic, or unions with incompatible column semantics. Fallback to

table

visualization when chart confidence is low.