Feature: open favorited Library queries as an interactive Dashboard

[BorisTyshkevich]

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 — a chart in its saved chart config, 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).

Crucially, no new dashboard schema is introduced: KPI framing, chart shape, and filter
option-lists are all expressed in SQL and existing saved-query metadata. A dashboard is simply
"the favorited subset of the Library, rendered together."

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.

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 — what becomes a tile

• Only favorite: true queries are included (header shows an "N favorites" chip). The flag
  and per-query chart/view config already exist (src/state.js, src/core/saved-io.js).
• A favorite renders as:
  • KPI tile — result is a single row (see §4).
  • Chart tile — has a saved chart config, or a chartable multi-row result (see §5).
• Pinned: favorites that are neither single-row nor chartable are skipped in v1, with a
  small "N not shown" note in the header. Table tiles are a future phase, not v1.
• 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 config { 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: scan all tile SQL with the existing detectParams; every param used by ≥1 tile
  becomes a candidate filter. No manual registration.
• Option lists — text by default, SQL-sourced when curated (we deliberately do not
  introspect the schema, because panels may use arbitrary joins and there's no reliable single
  table to DISTINCT):
  1. Text input (default): no list supplied → free-text box; typed value injected; empty =
     predicate omitted.
  2. filter: <param> Library query: a specially-named saved query supplies the option list,
     upgrading the control to dropdown. Author writes the exact query (any joins, dimension
     tables, DISTINCT, ORDER BY) — cost under their control. Result columns by convention:
     1 col → values; 2 cols → value + label (store code, show name).

     -- saved query named "filter: origin"
     SELECT Code AS value, Name AS label
     FROM airline.airports ORDER BY Name

  3. filter[]: <param>: multiselect; binds col IN ({params:Array(T)}).
  4. filters: bundle — deferred; per-param queries are the default (graceful degradation,
     independent caching, parallelism, cascading). Revisit only if a real dashboard accumulates
     many cheap enums.
• Binding: inject the value only when set; empty/"All" omits it (statement must be written so
  the predicate degrades, or the tile is simply re-run with the param unset per the existing
  unfilled-params gate — resolve in Phase 3).
• Cascading: a filter: query may reference other params (… WHERE Year = {year:UInt16});
  changing the parent re-runs and narrows the dependent list.
• Re-run: changing any filter re-runs affected tiles with a loading shimmer and updates the
  "Updated …" timestamp; option-list queries re-run on Refresh.

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 — non-single-row, non-chartable favorites are skipped with an
   "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)

• Phase D1 — foundation + read-only dashboard (PR feat: open favorited Library queries as a read-only Dashboard (#149 D1) #150): route (http_handlers.xml regex +
  bootstrap pathname branch), auth handoff + login fallback, "Open as dashboard" menu item,
  page shell, favorites → static chart tiles (reusing renderChart/autoChart), header
  (back link, favorites chip, Refresh-all).
• Phase D2 — layout: Report mode and Arrange/Report switcher. (PR feat: dashboard Arrange/Report layout switcher (#149 D2) #151)
• Phase D3 — global filters (text) (**Phase D3 — global filters (text)**: filter bar over detected {name:Type} params, re-run affected tiles #152, PR feat: dashboard global filter bar (#149 D3) #156): filter bar over params detected across tile
  SQL (detectParams/readStatementParams), text inputs bound to the shared varValues +
  paramArgs wiring, re-run affected tiles (per-tile gating + generation guard), empty-value
  semantics. Uses the same {name:Type} mechanism as the SQL Browser workbench (Support variables in SELECT queries #134). No filter
  bar at all when no favorite has a param.
• Phase D4 — layout (**Phase D4 — layout**: drag-reorder, 1/2-col span, open panel in window. #153): drag-reorder, 1/2-col span, open panel in window.
• Phase D5 — KPI tiles (Phase D5 — KPI tiles: readKpi + formatKpiValue in src/core/ (100%), single-row classification, KPI row, bare-scalar fallback, skipped-tiles note. #154): readKpi + formatKpiValue in src/core/ (100%),
  single-row classification, KPI row, bare-scalar fallback, skipped-tiles note.
• Phase D6 — curated filters (**Phase D6 — curated filters**: filter:/filter[]: naming convention → dropdown/multiselect, option-query execution (parallel, cached, re-run on Refresh), cascading #160): filter: / filter[]: naming convention →
  dropdown / multiselect, option-query execution (parallel, cached, re-run on Refresh), cascading
  (evaluation order + dependent-selection invalidation).
• Phase D7 — tile interactivity: expand modal, per-tile Type/X/Y overrides (persisted to
  dashboard state, not the source query), drill-down highlight + chip.
• Phase D8 — export: Markdown (extend buildMarkdownDoc), print CSS for PDF, per-chart
  PNG.

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.
• Charted favorites render in their saved chart shape; no-config favorites use autoChart;
  non-renderable 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 dropdown/multiselect when a filter: / filter[]: query exists.
• Changing a filter re-runs affected tiles (loading state + timestamp update); cascading
  option lists narrow correctly and invalid dependent selections are cleared.
• 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 — only new conventions: filter: / filter[]: naming
  and KPI column names (both live in SQL).

Open questions (remaining)

• Empty-filter semantics (Phase D3) — resolved in **Phase D3 — global filters (text)**: filter bar over detected {name:Type} params, re-run affected tiles #152: an unset/empty param blocks
  that tile's execution via the existing unfilledParams gate, applied per-tile (no SQL-side
  sentinel convention needed).
• Cascading evaluation order (Phase D6) — spec written in **Phase D6 — curated filters**: filter:/filter[]: naming convention → dropdown/multiselect, option-query execution (parallel, cached, re-run on Refresh), cascading #160: parent-first
  topological evaluation over a dependency DAG detected via readStatementParams, cycles
  degrade to a plain text input, dependent selections no longer in a refreshed option list are
  cleared and the invalidation cascades to further dependents.
• Refresh-token rotation across two tabs (Phase D1): BroadcastChannel sync vs documented
  limitation.
• Table tiles / true predicate drill-down / whole-dashboard PNG / auto-refresh / filters:
  bundle — all deferred post-v1, each needing its own small design.