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

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.

1. Create virtual environment:

bash scripts/setup_venv.sh

1. 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.