Skip to content

Interactive Dashboards #149

Open
1 of 2 issues completed
Open
Interactive Dashboards#149
1 of 2 issues completed
Labels
enhancementNew feature or request

Description

@BorisTyshkevich
BorisTyshkevich
opened on Jul 4, 2026

Summary

Add a Dashboard view that opens all favorited (starred) Library queries in a new
browser tab as a single, full-scale interactive dashboard. Each saved query renders in its
saved shape — per its saved panel config (#166): a chart, a table, a logs view, a
single-row result as a KPI tile — laid out
in a grid the user can rearrange. The dashboard layers on global filters (shared
{name:Type} parameter bindings that re-run every tile), per-tile controls, drill-down,
expand-to-fullscreen, refresh, and export (Markdown / PDF / per-chart PNG).

No separate dashboard document is introduced. The dashboard remains the favorited subset of the
Library. Each saved query may carry two independent pieces of metadata:

panel      // how the query result is visualized
dashboard  // what job the saved query performs in the dashboard

The dashboard role is optional and defaults to Panel, so existing saved queries retain their
behavior.

2026-07-04 revision — spec reconciled against the codebase: parameter syntax standardized
on the already-shipped ClickHouse-native {name:Type} mechanism (#134), four open questions
pinned (see Decisions pinned), architecture (routing / auth / no-framework) decided, and the
work split into shippable phases. Original design-generated spec preserved otherwise.

2026-07-12 clarification — visualization vs dashboard role.
Panels remain visual result renderers: chart, table, logs, text, and KPI. Query-backed filter
choices and setup scripts are not Panel types. A saved query has a separate optional dashboard
participation field:

dashboard.role = 'panel' | 'filter' | 'setup'

Missing metadata defaults to panel. A favorited Panel becomes a grid tile; a favorited Filter
source docks into the filter bar; a favorited Setup source runs before filters and Panels and
remains hidden. Existing panel configuration is preserved while another role is active.

Motivation

The Library already treats saved queries as a portable, document-style collection. Users curate
favorites as "the things worth watching." Rendering that starred subset together — KPIs on top,
charts below, one shared filter bar — turns a query collection into a monitoring surface without
a separate dashboard-authoring tool or schema. Everything the dashboard needs is already stored
per query, or expressible in SQL.

Reference implementation

An interactive HTML/React mockup demonstrating the tile/layout/drill/expand behaviour (against
sample airline.ontime data) lives in the Claude Design project ("sql browser"):

  • Dashboard.html — shell + theme tokens (matches the app's dark/light system)
  • dashboard-data.jsx — sample favorites, saved chart configs, KPI queries
  • dashboard-charts.jsx — chart canvas (bar/hbar/line/area/pie) + hover tooltip + drill callback
  • dashboard-app.jsx — tiles, KPI reader (readKpi/formatKpiValue), per-tile controls, expand modal
  • dashboard-root.jsx — header, filter bar, layout switcher, export, tweaks

Treat the mockup as the visual + interaction source of truth; this issue is the buildable
spec. Two mockup caveats:

  • The mockup's charts are hand-rolled SVG (the design tool can't bundle Chart.js). The real
    build reuses the app's chart stack — pure src/core/chart-data.js + the injected app.Chart
    seam + renderChart — not a port of the SVG code.
  • The mockup's filter bar is hardcoded (Year/Range selects over sample data). The real
    filter engine (§6) has no mockup yet — supplemental mockups requested from Claude Design
    (tasks/dashboard-filter-mockups.md in the design project).

Requirements

1. Entry point

  • Library ▾ menu → "Open as dashboard", enabled when ≥1 favorite exists
    (src/ui/file-menu.js).
  • Opens the standalone route /sql/dashboard in a new tab.
  • The dashboard is a snapshot of favorites at open time. Later Library edits do not
    live-mutate an open tab; Refresh re-reads.

2. Scope — dashboard participation

Only favorite:true saved queries participate.

Before execution, favorites are partitioned by dashboard.role:

  • Panel (default) — renders as a dashboard grid panel using its saved/derived panel
    configuration;
  • Filter source — supplies options for one existing dashboard parameter and docks into the
    filter bar; never a grid panel;
  • Setup — runs before Filter sources and Panels in session mode; never rendered.

Role and favorite state are independent. Unfavoriting removes the saved query from the dashboard
but preserves its configured role.

Unknown roles are preserved on import and excluded with a visible diagnostic.

Among displayed Panels: a favorite renders as a KPI tile when its result is a single row
(§4), or a Chart tile using its saved chart-family panel config or a chartable multi-row
result (§5). Non-chartable/non-single-row favorites skip in v1 with a small "N not shown" note
in the header (superseded for table-shaped results by #164, Phase D9). KPI tiles float to a top
row; chart tiles fill the grid beneath in Library order, then user-reorderable.

3. Layout — two views + a switcher

  • Arrange (default) — uniform grid, 2 or 3 columns. Tiles are drag-reorderable (grip
    handle in each tile header) and each tile can snap between 1- and 2-column width.
  • Report — single scrolling column, full-width tiles, taller charts; still drag-reorderable
    (width toggle N/A in one column).
  • A segmented switcher (Arrange | Report) lives in the filter bar — the primary, visible
    control. Column count (2/3), KPI-row visibility, and appearance live in secondary settings.
  • Persist per dashboard: layout, gridCols, KPI-row visibility, tile order, per-tile span.

4. KPI tiles — SQL-driven, one-row / multi-column

A KPI is a favorited query returning one row. There is no stored display config; the tile
reads the returned row by column-name convention:

Column Role Notes
value the big number Formatting inferred from column type: integer/large → 6.71M / 402.0K; float → 13.2.
unit suffix e.g. '%', ' min'; % also switches value to fixed-decimal.
delta change vs comparison period Signed → ▲/▼. Computed in the query (e.g. vs prior year).
higher_is_better 0/1 Colours the delta green/red. Delays/cancellations set 0.

Example (real prior-year delta in the same query):

SELECT
  sumIf(Cancelled, Year = {year:UInt16})                                 AS value,
  ''                                                                     AS unit,
  round((sumIf(Cancelled, Year = {year:UInt16})
        / sumIf(Cancelled, Year = {year:UInt16} - 1) - 1) * 100, 1)      AS delta,
  0                                                                      AS higher_is_better
FROM airline.ontime
WHERE Year IN ({year:UInt16}, {year:UInt16} - 1)

Graceful fallback: a bare SELECT count() … (one column, no aliases) still renders — the
single numeric column is value, no unit/delta. Tile footer shows 1 row × N cols + query time.

5. Chart tiles

  • Render from the saved chart-family panel cfg (Panels: visualization registry + Panel drawer tab + Library panel field #166) { type, x, y[], series }, type ∈ bar | hbar | line | area | pie (same config the Results → Chart view produces).
  • No chart → apply the existing autoChart heuristic (src/core/chart-data.js) for a default.
  • Per-tile controls (settings popover): change Type / X / Y inline. Changes persist with the
    dashboard as overrides, not back to the source query.
  • Hover tooltip (x-label + every series value). Footer meta: rows · ms · bytes scanned.

6. Global filters

Filters are named parameters in ClickHouse-native syntax — {name:Type}, e.g.
{year:UInt16} — injected into every tile query referencing them. This mechanism already
exists (#134): detection in src/core/query-params.js, values in the shared persisted
state.varValues map, transport as injection-safe param_<name> HTTP args. The type is
declared in the SQL itself, which resolves typing/quoting by construction.

  • Discovery: analyze SQL from displayed Panel sources and Setup sources. Every parameter
    referenced by at least one executable dashboard source becomes a candidate filter.
    Filter-source option SQL is parameter-free in Dashboard: multi-filter option bundles, shared preview, and role-aware result selector #160 v1.
  • Option lists — text by default, query-backed when configured (we deliberately do not
    introspect the schema, because panels may use arbitrary joins and there's no reliable single
    table to DISTINCT): no list supplied → free-text box; typed value injected; empty = predicate
    omitted.

Query-backed filter options

A saved query configured as:

dashboard: {
  role: 'filter',
  param: 'origin'
}

may supply the choices for an existing dashboard parameter.

The single-select MVP is tracked in #160:

  • one row-returning statement;
  • no parameters in the option query in v1;
  • one result column = value/label;
  • two result columns = value + label;
  • strict searchable selection;
  • server-side option cap and lossless string transport;
  • persisted-selection reconciliation before Panels run;
  • fallback to the normal field with a diagnostic on configuration/query failure.

Multiselect is tracked separately in #189.

Dependent/cascading option queries and caches are deferred until a concrete dashboard requires
them.

7. Drill-down

  • Clicking a chart element (bar / pie slice / hbar row) sets a global drill filter
    { column, value }, shown as a dismissible chip; non-matching data dims across charts.
  • Re-clicking the element or the chip ✕ clears it.
  • Pinned: v1 is client-side highlight only (what the mockup implements). True
    predicate-push drill-down (apply as an added predicate and re-run tiles) needs a
    column→parameter mapping that is ill-defined across arbitrary queries — deferred to a future
    phase with its own design.

8. Expand / fullscreen

  • Each chart tile → Expand modal: large chart + the same per-tile controls + a read-only
    SQL peek. Esc / backdrop closes.

9. Header

  • ← SQL Browser back link.
  • Editable title (defaults to Library name; inline rename, Enter/Esc; drives export filename;
    persisted).
  • "N favorites" chip — the sole scope marker (+ "N not shown" note when tiles are skipped,
    §2). (Per-tile star badges intentionally omitted: in an all-favorites view a badge true for
    100% of tiles carries no information.)
  • Description subtitle, Source chip (host + live-connection dot), Updated .
  • Refresh (re-runs all tiles + option queries; spinner; updates timestamp).
  • Export ▾ — Markdown / PDF / per-chart PNG.

10. Export

  • Markdown — heading + KPI bullet list (with delta) + one ## <name> section per chart with a
    fenced ```sql block and a small results-table preview. Extend the Library's Markdown serializer
    (buildMarkdownDoc, `src/core/saved-io.js`).
  • PDF — browser print (print CSS hides interactive chrome).
  • PNG — per-chart PNG via the Chart.js canvas (toDataURL), free of new dependencies.
    Whole-dashboard PNG is out of v1: DOM rasterization means an html2canvas-class runtime
    dependency — an explicit hard-rule-4 decision to make separately if wanted.

Architecture (decided)

  • Stack: no React / no framework — same architecture as the app (hyperscript render
    functions + @preact/signals-core), per CLAUDE.md rule 5 and the ADR-0001 Preact-spike
    addendum. The mockup's React is its prototyping medium, not a directive: the valuable mockup
    logic (readKpi, formatKpiValue, conventions) is framework-free and ports to src/core/
    directly; charts reuse the existing Chart.js seam. The dashboard's dataflow (filters → re-run
    affected tiles → re-render) is exactly what signals model; drag-reorder stays an imperative
    pointer surface like the app's other ones. If dashboard state coordination proves genuinely
    painful, that is new evidence for an ADR-0001 addendum — not a pre-emptive fork.
  • Serving/routing: one artifact, client-side route. Widen the ^/sql/?$ rule in
    deploy/http_handlers.xml to also serve /sql/dashboard from the same sql.html, and branch
    on location.pathname in bootstrap (src/main.js). Zero build changes; auth/config wiring
    shared.
  • Auth: one-time handoff + full login fallback. Tokens live in per-tab sessionStorage, so
    a new tab is signed out by default. "Open as dashboard" opens the tab via window.open and
    passes the current credentials (OAuth tokens or basic-auth) once via postMessage; the
    dashboard tab stores them in its own sessionStorage, which survives reloads of that tab.
    A cold/bookmarked visit to /sql/dashboard runs the same login/OAuth flow as the main page
    (same artifact, same bootstrap). No migration to localStorage. Known residual: two tabs
    independently refreshing a rotating refresh token can race — resolve in Phase 1
    (BroadcastChannel token sync, or document as unsupported IdP config).

Decisions pinned (2026-07-04)

  1. Parameter syntax = {name:Type} (ClickHouse-native, already shipped in Support variables in SELECT queries #134). The
    original :param wording was mockup shorthand. Typing/quoting is resolved by the SQL itself.
  2. Filter values live in the existing state.varValues — already global across queries and
    persisted (asb:varValues), so dashboard filters and workbench variable values stay
    consistent by design. A dashboard-scoped namespace can be added later if needed.
  3. Table tiles are out of v1 — superseded by **Phase D9 — table tiles + logs view**: saved view:'table' + non-chartable fallback render as table/logs tiles, server row cap for all tiles #164 (Phase D9): non-chartable
    multi-row favorites (and any favorite explicitly saved with view:'table') render as a table
    or logs tile instead of being skipped; only empty/single-row (KPI, D5) results still count
    toward the "N not shown" header note.
  4. Drill-down v1 = client-side highlight; predicate-push deferred.
  5. Manual Refresh only in v1 — auto-refresh/polling is post-v1.
  6. Export = Markdown + PDF(print) + per-chart PNG; whole-dashboard PNG deferred
    (new-dependency decision).

Build phases (each a shippable PR passing the coverage gate)

Acceptance criteria

  • "Open as dashboard" appears in the Library menu and opens /sql/dashboard in a new tab
    showing only favorites; the new tab is authenticated via handoff; a cold visit falls
    back to the normal login flow.
  • Single-row favorites render as KPI tiles reading value/unit/delta/higher_is_better
    by convention, with type-driven formatting and a graceful bare-scalar fallback.
  • delta is computed in-query (prior-period) and coloured by higher_is_better.
  • Favorites render per their saved panel cfg (Panels: visualization registry + Panel drawer tab + Library panel field #166); no-config favorites use autoPanel;
    non-chartable multi-row favorites and view:'table' favorites render as table/logs tiles
    (**Phase D9 — table tiles + logs view**: saved view:'table' + non-chartable fallback render as table/logs tiles, server row cap for all tiles #164); only empty/single-row (KPI) favorites are skipped, with an "N not shown" note.
  • Per-tile Type/X/Y controls work and persist as overrides (not to the source query).
  • Params ({name:Type}) are auto-discovered from tile SQL; each renders as text by default,
    or a strict single-select when a favorited Filter source (Dashboard: multi-filter option bundles, shared preview, and role-aware result selector #160) targets it.
  • Changing a filter re-runs affected tiles (loading state + timestamp update).
  • Drill-down chip appears on chart-element click, dims non-matching data across charts, and
    is dismissible (client-side highlight).
  • Expand modal shows enlarged chart, controls, and SQL; closes on Esc/backdrop.
  • Arrange (drag-reorder + 1/2-col width) and Report views work, toggled via the filter-bar
    switcher; layout/order/spans/title persist per dashboard.
  • Export produces valid Markdown, PDF (print), and per-chart PNG.
  • Header shows editable title, favorites count, source chip, last-refreshed; Refresh works.
  • No saved-query schema change — superseded by the Panels delivery (Panels: visualization registry + Panel drawer tab + Library panel field #166) and the
    2026-07-12 dashboard-role clarification: the saved query gains the panel field (with
    transparent import upgrade from chart) and an independent optional dashboard.role. No
    separate dashboard schema is introduced — that pin stands.

Open questions (remaining)

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Type

    No type

    Fields

    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions