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