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
- Parse markdown SQL blocks into a normalized query catalog.
- Infer query semantics (metrics, dimensions, time columns, grain hints).
- Extract P0 filter candidates from SQL DSL (
WHERE predicates) into structured filter metadata (dsl_ast first, regex fallback).
- Recommend chart types from inferred semantics.
- Build a dashboard specification with layout coordinates.
- Generate a UI scaffold that renders the dashboard structure.
- 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
- 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.
