Skip to content

sql-result-visualization.md

Latest commit

 

History

History
167 lines (140 loc) · 7.87 KB

sql-result-visualization.md

File metadata and controls

167 lines (140 loc) · 7.87 KB
Raw

SQL Result Visualization Architecture

This document is normative for AI-assisted SQL result visualization in Studio (view=sql).

The implementation MUST reuse the existing SQL-view AI transport, render inside the shared scrollable result grid, and generate a small Studio-owned Bklit chart config instead of accepting arbitrary chart-library options.

Scope

This architecture governs:

  • the optional AI visualization row inside SQL result rendering
  • prompt construction from the executed query plus full result rows
  • response validation for AI-generated Bklit chart configs
  • Bklit chart composition and lifecycle management
  • visualization reset behavior across query executions

Canonical Components

Result-Area Contract

  • Visualization MUST remain optional. If llm is not provided, SQL result rendering MUST NOT show an empty visualization affordance.
  • The idle visualization affordance MUST render on the SQL result summary row, right-aligned on the same line as the "row(s) returned in Xms" text.
  • The mounted chart surface MUST render above the SQL result column header row but inside the shared scrollable grid container.
  • The implementation MUST use DataGrid.getBeforeHeaderRows(...) for the mounted chart rather than a bespoke parallel scroll region.
  • The mounted chart surface MUST render inside a full-width background band that stays pinned to the visible scroll-container width rather than stretching with the total table width.
  • The actual chart body inside that band MUST be centered and width-clamped:
    • minimum 300px
    • grow with available visible width
    • maximum 1200px
    • clip horizontally when the visible space is narrower than 300px
  • The chart band MUST leave a small bottom margin before the grid and visually define the grid top edge with a border.
  • The idle visualization affordance MUST stay visually minimal: icon plus plain text, no bordered button chrome, and no surrounding explanatory copy.
  • The default idle state label MUST read Visualize data with AI.
  • If the user manually runs SQL that was generated by AI and marked as graph-worthy, the idle affordance MUST be skipped and chart generation MUST begin automatically for that result.
  • After graph generation succeeds or fails, the idle action MUST NOT reappear until the next query execution resets the visualization state.

Prompt and Validation Contract

  • The visualization prompt MUST reuse the same embedder-provided llm({ task, prompt }) hook already used for SQL generation.
  • The prompt MUST include:
    • the concrete database engine name
    • the executed SQL text under a stable SQL: line
    • the original AI SQL request when the result came from Generate SQL with AI
    • the full result row set
  • The prompt MUST explicitly request a Studio Bklit chart config and forbid external libraries.
  • AI responses MUST be strict JSON and MUST NOT include markdown fences or commentary.
  • JSON-response validation and correction retries SHOULD flow through the shared SQL-view AI JSON utility rather than a visualization-specific retry loop.
  • Provider-level output-limit failures MUST surface as explicit retry issues instead of collapsing into a generic malformed-JSON error.
  • The validated response shape MUST contain a config object with this minimal schema:
    • type: one of bar, horizontal-bar, line, pie, or doughnut
    • title: optional short display title
    • data: plain JSON objects with primitive field values only
    • xKey plus series[] for bar, horizontal-bar, and line charts
    • optional stacked: true for bar and horizontal-bar charts only
    • labelKey plus valueKey for pie and doughnut charts
  • line charts MUST use date-like xKey values: ISO dates, ISO datetimes, or epoch milliseconds. Generic categorical data should use bar.
  • horizontal-bar charts SHOULD be used for ranked categorical results, top-N lists, and long category labels that would collide on a vertical x-axis.
  • Stacked bar and horizontal-bar charts MUST use one data row per category and separate numeric series fields for each stack segment. Long/tidy SQL result rows MAY be pivoted by the AI visualization response into this Studio-owned chart config.
  • The supported chart types MUST be constrained to the known allowlist above.
  • The implementation MUST retry up to two times when the model returns malformed JSON or an invalid chart config, and each correction prompt MUST include the latest validation error.

Chart Config Examples

Vertical or horizontal bar charts use one category key and one or more numeric series keys:

{
  "config": {
    "type": "bar",
    "title": "Incidents by severity",
    "xKey": "severity",
    "series": [{ "key": "count", "label": "Incidents" }],
    "data": [
      { "severity": "Low", "count": 12 },
      { "severity": "High", "count": 3 }
    ]
  }
}

Stacked bar charts use separate numeric fields for each stack segment, not one long/tidy row per segment:

{
  "config": {
    "type": "horizontal-bar",
    "title": "Team skills by organization",
    "xKey": "organization",
    "stacked": true,
    "series": [
      { "key": "typescript", "label": "TypeScript" },
      { "key": "postgres", "label": "Postgres" }
    ],
    "data": [
      { "organization": "Acme", "typescript": 4, "postgres": 2 },
      { "organization": "Globex", "typescript": 1, "postgres": 5 }
    ]
  }
}

Line charts require date-like x-values:

{
  "config": {
    "type": "line",
    "title": "Rows created over time",
    "xKey": "day",
    "series": [{ "key": "created", "label": "Created rows" }],
    "data": [
      { "day": "2026-06-01", "created": 18 },
      { "day": "2026-06-02", "created": 27 }
    ]
  }
}

Pie-like charts use one label key and one numeric value key:

{
  "config": {
    "type": "doughnut",
    "title": "Feature flag states",
    "labelKey": "state",
    "valueKey": "count",
    "data": [
      { "state": "Enabled", "count": 9 },
      { "state": "Disabled", "count": 4 }
    ]
  }
}

Renderer Contract

  • bar and horizontal-bar render through BarChart, Bar, BarXAxis, and BarYAxis.
  • line renders through LineChart, Line, and XAxis.
  • pie and doughnut render through PieChart and PieSlice.
  • All chart types use the shared Bklit Grid and ChartTooltip primitives where applicable.
  • Series colors default to Studio chart CSS variables (--chart-1 through --chart-5) unless a validated series color is provided.
  • The renderer MUST ignore arbitrary chart-library options. All display behavior is derived from the validated Studio config and local component composition.

Chart Lifecycle Contract

  • Chart rendering MUST use the Bklit ShadCN chart primitives checked into ui/components/charts.
  • The mounted chart MUST remain a React composition, not a canvas lifecycle object.
  • Visualization reset MUST happen when a new SQL execution starts, even if the previous result grid is still visible while the next query is in flight.

Testing Requirements

Changes to SQL result visualization MUST include tests covering:

  • prompt construction with full result rows
  • validation of supported and unsupported Bklit chart configs
  • SQL-view integration showing the summary-row Visualize data with AI affordance
  • automatic chart generation for AI-generated SQL results that request visualization
  • replacement of the action with a mounted chart
  • reset behavior when another query execution starts
  • validation and rendering support for stacked bar and horizontal-bar chart configs