BigQuery Query Console Redesign

Status: Draft Date: 2026-05-03 Scope: Admin portal — /admin/analytics/bigquery/console

Goal

Replace the current Query Console layout with a tabbed-rail / work-surface structure that makes the editor and results the primary work area, unifies reference content (schema, saved queries, history) into a single collapsible rail, and treats result tables as a first-class viewing experience instead of an afterthought.

Why

The current console has four issues that compound:

1. Layout fights itself. Schema browser and editor share a row, but results render under the editor in the same right-hand column — so result tables get a narrow strip while the schema browser holds full vertical space.
2. Wasted vertical space. Schema, editor, saved queries, and built-in reports all stack as full-width sections; running a saved query and seeing results requires significant scrolling.
3. Flat hierarchy. Limit pills, section headers, cards, and results all carry equal visual weight.
4. Results presentation is weak. Columns aren't sized to content, long UUIDs wrap into multiple lines of mush, no way to expand a row, no copy-CSV, no fullscreen.

Built-in reports also live on this page today but are moving to their own page (separate work).

Out of Scope

• Built-in reports section — being removed from the console; lives on its own page (separate effort).
• Reference Docs · Export Status · Query Console · Scheduled Reports · Data Retention sub-nav (BigQueryTabs) — unchanged.
• Backend / API changes for query execution. Existing runBqQueryRaw, estimateBqQueryRaw, saveQuery, deleteSavedQuery, schema-fetch endpoints stay as-is.
• Authentication / authorization. No changes to admin role gating.

Layout

A three-zone shell:

┌─────────────────────────────────────────────────────────────────────┐
│ Toolbar:  ▶ Run   Estimate   Save   Format   Clear      [limits] ⛶ │
├──────────────┬──────────────────────────────────────────────────────┤
│ Tabbed rail  │  Editor                                              │
│              │                                                      │
│ Schema       │  ── drag handle ── (resizes editor / results split)  │
│ Saved (n)    │                                                      │
│ History      │  Cost & job meta strip                               │
│              │  ┌────────────────────────────────────────────────┐  │
│ [search…]    │  │ Results toolbar (filter · CSV · download · ⛶)  │  │
│              │  ├────────────────────────────────────────────────┤  │
│ list…        │  │ Results table (resizable cols, click to expand)│  │
│              │  └────────────────────────────────────────────────┘  │
└──────────────┴──────────────────────────────────────────────────────┘

Toolbar

Single horizontal bar at the top of the work surface.

• Run (primary, accent-colored). Keyboard: Cmd/Ctrl+Enter.
• Estimate — runs dry-run against current SQL.
• Save — opens the existing save-query inline form.
• Format (ghost) — formats the SQL in the editor via the sql-formatter package (BigQuery dialect).
• Clear (ghost) — empties the editor (with undo via the editor's own history).
• Limits pill — collapses the three pills (SELECT only, 1 GB, 5k rows) into one compact pill (SELECT only · 1 GB · 5k rows); full text on hover via title attribute.
• Fullscreen toggle (right side) — CSS-only: hides the admin left sidebar and BigQueryTabs page tabs so the console fills the browser viewport. Browser chrome / URL bar stay. Esc exits. State is local to the component — does not persist across navigation.

Tabbed Rail (left)

Single rail with three tabs. Width is resizable via a drag handle on the right edge; width persists per admin in localStorage.

Schema tab — current SchemaBrowser content. One filter input that searches across datasets / tables / columns. Click a column or table to insert at cursor in the editor.

Saved tab — current SavedQueriesPanel content, with the count badge in the tab label. Each entry shows name, SQL preview, author, timestamp, and Load/Delete actions. "Load into editor" replaces editor contents.

History tab — new. Last 25 successful runs by the current admin, stored in localStorage (key: bq-console:history:<adminId>). Each entry: SQL snippet, timestamp, scan size, runtime. Click to load into editor. Cleared via a "Clear history" link in the tab header.

The rail is collapsible to a thin icon strip via a chevron in the rail header; collapsed state persists.

Work Surface

Editor. Existing RawSqlEditor component, retained as-is for SQL input. Drag handle along its bottom edge resizes the editor / results split; ratio persists.

Cost & job meta strip. Persistent strip between editor and results showing the most recent estimate (✓ Last estimate: 711 KB · $0.000003) and the most recent job result (Job: 1.2s · 10 rows · cache miss). Errors render here inline (red text + icon) instead of in a separate ErrorCard below the results.

Results.

• Results toolbar: row-filter input (client-side substring filter across all columns), Copy CSV, Download CSV, Fullscreen (toggles same fullscreen as the toolbar button).
• Results table: existing QueryResultTable extended with column resize (drag column-edge; widths persist per query name when applicable), and row-click to expand. The expand panel renders the row as a key/value list with full untruncated values (handles long UUIDs cleanly without wide columns).
• Empty state: "Run a query to see results" with a hint about Cmd/Ctrl+Enter.
• Loading state: skeleton rows + a subtle pulsing accent on the Run button.

Component Breakdown

The current BigQueryWorkspace.jsx is ~1530 lines and combines export status, retention, scheduled reports, query console, schema browser, saved queries, raw editor, and result rendering. Touching it for this redesign without splitting will compound the problem.

Refactor as part of this work — only the Console-related pieces:

apps/admin/src/admin/analytics/
├── BigQueryWorkspace.jsx          # router shell, kept (other panels stay)
├── console/
│   ├── ConsolePanel.jsx           # orchestrator; replaces in-file ConsolePanel
│   ├── ConsoleToolbar.jsx         # Run / Estimate / Save / Format / Clear / limits / fullscreen
│   ├── ConsoleRail.jsx            # tabbed rail (Schema · Saved · History)
│   ├── ConsoleEditor.jsx          # wraps RawSqlEditor + the resize handle
│   ├── ConsoleMetaStrip.jsx       # estimate / job meta / inline errors
│   ├── ConsoleResults.jsx         # results toolbar + table + row-expand
│   ├── HistoryPanel.jsx           # rail's history tab
│   ├── useConsoleState.js         # editor SQL, last estimate, last job, error
│   ├── useConsoleHistory.js       # localStorage history hook
│   └── useConsolePersistence.js   # rail width/collapsed, editor split ratio
└── (existing files for ExportStatus / Schedule / Retention untouched)

SchemaBrowser, SavedQueriesPanel, RawSqlEditor, QueryResultTable, ErrorCard, ResultMetaFooter, SectionHeader, formatBytes, and structuredError are all only used by the Console flow today (verified: ExportStatusPanel, ScheduledReportsPanel, and DataRetentionPanel don't reference them). They move into apps/admin/src/admin/analytics/console/ and are deleted from BigQueryWorkspace.jsx. The orchestrator (ConsolePanel) is the only new top-level component the router renders for the console route.

useConsoleState is the single source of truth the toolbar, editor, meta strip, and results all read/write through. No prop-drilling chains.

Persistence

Per-admin localStorage keys (namespaced by admin id):

• bq-console:rail:width — pixel width of the rail
• bq-console:rail:collapsed — boolean
• bq-console:rail:active-tab — 'schema' | 'saved' | 'history'
• bq-console:editor:height — pixel height of the editor pane
• bq-console:history — array of { sql, ts, scanBytes, durationMs }, capped at 25
• bq-console:column-widths:<queryFingerprint> — optional, per-query column widths

A query "fingerprint" is a hash of the SQL text after format-normalization — column widths only persist for queries that re-run with stable shape.

Keyboard

• Cmd/Ctrl+Enter — Run
• Cmd/Ctrl+S — Save (opens existing save form)
• Cmd/Ctrl+E — Estimate
• Cmd/Ctrl+K — focus rail search input
• Esc — collapse rail (when not in an input)
• Esc (in fullscreen) — exit fullscreen

Error Handling

• Query errors (BigQuery rejection, timeout, scan-cap exceeded): render in the cost/job meta strip as inline error text with the existing structuredError shape.
• Schema fetch failure: existing behavior preserved — error card inside the Schema tab.
• Saved-query fetch failure: error card inside the Saved tab; rest of the console remains functional.
• History corruption (bad JSON in localStorage): silently reset and continue.
• Network offline: Run/Estimate buttons disable; toolbar shows a small "offline" pill. Rail tabs (Schema, Saved, History) remain functional for any data already cached, but show a stale-data hint where applicable.

Testing

Existing tests in apps/admin/src/admin/analytics/__tests__/ for the BigQuery flow continue to pass. New tests:

• ConsolePanel.test.jsx — rail tab switching; toolbar wiring; meta strip updates after estimate/run; row expand toggles.
• useConsoleHistory.test.js — capacity cap; corruption recovery; ordering.
• useConsolePersistence.test.js — width/height persistence; reset to defaults when keys missing.
• Storybook story for ConsolePanel covering: empty / loading / results / error / fullscreen states.

Visual regression isn't currently set up for this area; not adding it as part of this work.

Migration / Rollout

Single PR. No feature flag — admin-only surface, low blast radius, instant rollback by revert. Manual smoke check:

1. Schema browser inserts at cursor as before.
2. Saved queries load into editor as before.
3. Estimate and Run produce the same response shapes.
4. Errors render readably.
5. History entries appear after a run and survive a page reload.
6. Resize handles persist across reloads.

Decisions Made

These started as open questions and are now resolved:

• History storage — localStorage per admin. Saved queries already use Firestore (adminSavedQueries), but history is per-device ephemera (recent runs by the current admin) and doesn't justify Firestore writes on every successful run. Revisit if admins request cross-device history.
• SQL formatting — Ships with sql-formatter (BigQuery dialect). Adds the Format button as a real feature in v1, not a follow-up. Bundle cost (~25 KB gz) is acceptable for an admin-only surface.
• Fullscreen — CSS-only toggle. Hides the admin left sidebar and BigQueryTabs page tabs; console fills the browser viewport. Matches the BigQuery web UI pattern. Esc exits.

New Dependency

• sql-formatter (npm) — added to apps/admin for the Format button. BigQuery dialect.