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

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

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. 1. Ensure python3.11 is installed and available in PATH.

If missing, follow references/install_python311.md.

1. 2. Create virtual environment:

CODEBLOCK2

1. 3. Activate and verify:

CODEBLOCK3

Expected version: Python 3.11.x.

Use --with-dev when dev dependencies are needed:

CODEBLOCK4

Run Commands

After activating .venv, run pipeline and service generation:

CODEBLOCK5

Run each step separately when debugging:

CODEBLOCK6

Start generated services:

CODEBLOCK7

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.
• INLINECODE22: 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.
• INLINECODE33: Final dashboard definition for rendering, including page-level global_filters.
• INLINECODE35: Static UI scaffold (index.html, app.js, style.css).
• INLINECODE39: FastAPI backend service using generated artifacts.
• INLINECODE40: Frontend service consuming backend API.
• INLINECODE41 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: INLINECODE47
• Chart mapping rules: INLINECODE48
• BI layout and widget sizing: INLINECODE49
• Python 3.11 installation and venv setup: INLINECODE50

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.