Import Dashboards from a read-only ClickHouse catalogue

[BorisTyshkevich]

Goal

Add a read-only Dashboard catalogue backed by a ClickHouse relation on the current server.

When the current authenticated user can read a compatible catalogue, the existing catalogue/example Dashboard import experience must list Dashboards from that ClickHouse server. Selecting one imports a normal local copy through the existing additive Dashboard import pipeline.

This is not remote workspace storage. IndexedDB remains authoritative for all local workspaces and editing.

Product boundary

V1 provides only:

• catalogue capability detection for the current ClickHouse connection;
• listing the latest published version of available Dashboards;
• fetching one selected portable Dashboard bundle;
• validating it as untrusted input;
• importing it additively into the active local workspace;
• falling back to the shipped built-in examples when no readable compatible catalogue is available;
• an operator-side Bash loader for populating the catalogue from existing portable Dashboard JSON files.

There is no ongoing relationship between the imported local copy and the server row:

• later server changes do not update the imported Dashboard;
• local edits are not written back;
• importing the same catalogue entry again creates another local Dashboard copy;
• server Dashboard/query ids never become local workspace identity;
• no background synchronization or remote editing is introduced.

Existing UI and import path

Generalize the existing Import example Dashboard… catalogue flow from #506 into one command:

Import Dashboard from catalogue…

Keep ordinary Import Dashboard… as the local JSON file picker.

When the server catalogue is available, the catalogue dialog lists server entries. Otherwise it lists the existing built-in examples compiled into the application. The dialog must identify its active source, for example:

Dashboard catalogue — Production

or:

Built-in examples

Do not add another File menu implementation or a Dashboard-specific menu. Extend the shared File-menu command model from #452.

After selection, both server and built-in entries must converge on the existing pipeline:

1. obtain portable-bundle JSON text;
2. decode through decodePortableBundleJson;
3. require exactly one selected Dashboard;
4. calculate its query dependency closure;
5. run existing query-conflict resolution;
6. call the existing additive planImportDashboard path;
7. remint local Dashboard/query identities as the import planner already requires;
8. commit atomically through app.mutateWorkspace;
9. open the imported Dashboard in Edit mode.

No second importer is allowed.

Configuration

Extend config.json with an optional Dashboard-catalogue capability:

{
  "dashboard_catalog": {
    "table": "asb.dashboards"
  }
}

A saved host may override it:

{
  "hosts": [
    {
      "label": "Production",
      "url": "https://clickhouse.example.com",
      "auth": "oauth",
      "dashboard_catalog": {
        "table": "analytics.shared_dashboards"
      }
    }
  ]
}

Resolution order for the current connection:

1. host-specific dashboard_catalog.table;
2. top-level dashboard_catalog.table;
3. default asb.dashboards.

Allow an explicit deployment opt-out so a missing default table is never probed:

{
  "dashboard_catalog": false
}

The configured value must parse as exactly one qualified ClickHouse identifier, database.table. Quote database and table identifiers independently. Never accept a free-form SQL fragment.

Manual or otherwise unlisted connections may use the top-level/default relation for the current server; they must never inherit a host-specific relation belonging to another server.

Catalogue relation contract

The configured object may be a table or view, provided it exposes compatible columns.

Reference schema:

CREATE TABLE asb.dashboards
(
    dashboard_id String,

    version UInt64 DEFAULT generateSnowflakeID(),
    saved_at DateTime64(3, 'UTC')
        ALIAS snowflakeIDToDateTime64(version),

    title String,
    description String DEFAULT '',
    saved_by String DEFAULT currentUser(),

    bundle_version UInt16,
    query_count UInt32,
    tile_count UInt32,

    payload String CODEC(ZSTD(3))
)
ENGINE = MergeTree
ORDER BY (dashboard_id, version);

Required compatible columns:

dashboard_id String
version UInt64-compatible
title String
description String
saved_at DateTime/DateTime64-compatible
saved_by String
bundle_version UInt16-compatible
query_count UInt32-compatible
tile_count UInt32-compatible
payload String

The browser feature is read-only. It requires SELECT only and must not execute DDL, INSERT, UPDATE, DELETE, or schema migration.

The operator-side loader described below is the only v1 component that writes catalogue rows. It runs outside the browser with separately supplied ClickHouse credentials and requires INSERT on the target relation.

The table is version-capable from the start, but V1 lists only the latest version of each dashboard_id and does not expose version history.

Stored payload

payload must be canonical portable-bundle JSON compatible with the application codec and contain:

• exactly one Dashboard document;
• exactly the saved queries required by that Dashboard's tile dependency closure;
• each query's SQL and complete supported Spec;
• Dashboard layout, tile order, presentation configuration, variable option SQL, and other portable Dashboard state.

It must not contain:

• unrelated Library queries;
• other Dashboards;
• workspace id, key, or local repository metadata;
• credentials, tokens, host configuration, or ClickHouse session ids;
• query results or caches;
• runtime Dashboard variable values;
• transient tabs, drafts, or editor state.

The application must still validate the payload independently; metadata columns are display/search hints and are never trusted as proof that the payload is valid.

Capability detection

Do not infer availability solely from system.tables, because visibility there may differ from actual SELECT privileges.

On first catalogue use for one authenticated connection session:

1. resolve and strictly quote the configured/default relation;
2. validate compatible columns with DESCRIBE TABLE or equivalent metadata query;
3. perform a bounded read such as the catalogue query with LIMIT 0 or LIMIT 1;
4. cache successful conformance for that authenticated connection session;
5. invalidate the cache when the ClickHouse origin, authenticated identity, or connection session changes.

A missing table, missing privilege, incompatible schema, or unsupported type means the server catalogue is unavailable. It must not block application login or ordinary SQL use.

When unavailable, open the same dialog using built-in examples. If loading the server catalogue fails after the dialog is opened, show a clear diagnostic and allow retry or switching to built-in examples without mutating the workspace.

Listing Dashboards

List metadata only. Do not fetch every payload to paint the catalogue.

Use one tuple-valued latest-row aggregate so displayed fields cannot come from different versions. Equivalent query:

SELECT
    dashboard_id,
    latest.1 AS title,
    latest.2 AS description,
    toString(latest.3) AS version,
    latest.4 AS saved_at,
    latest.5 AS saved_by,
    latest.6 AS bundle_version,
    latest.7 AS query_count,
    latest.8 AS tile_count
FROM
(
    SELECT
        dashboard_id,
        argMax(
            tuple(
                title,
                description,
                version,
                saved_at,
                saved_by,
                bundle_version,
                query_count,
                tile_count
            ),
            version
        ) AS latest
    FROM `database`.`table`
    GROUP BY dashboard_id
)
ORDER BY latest.4 DESC, title ASC, dashboard_id ASC
LIMIT 100

An equivalent implementation may add explicit submitted search over title/description, but V1 does not require pagination, categories, tags, screenshots, or remote previews.

Duplicate titles are valid. Each row must expose enough secondary identity to distinguish them, such as saver/time and a shortened dashboard_id fragment.

Treat UInt64 versions as decimal strings or BigInt; never round-trip them through JavaScript number.

Fetching one Dashboard

Pin both identities when the user selects a row:

SELECT payload
FROM `database`.`table`
WHERE dashboard_id = {dashboard_id:String}
  AND version = {version:UInt64}
LIMIT 1

Fetching the exact selected version prevents a newly published version from changing what the user imports after making a selection.

If the row disappears, several rows match unexpectedly, or the payload cannot be fetched, report the error and make no local change.

Listing and payload requests must use the ordinary authenticated ClickHouse request path, support cancellation when the dialog closes, and suppress stale responses from an older search/open attempt.

Validation and import safety

Treat catalogue payloads as untrusted input.

Before any workspace mutation:

• enforce existing JSON byte/depth/resource limits;
• decode through the current portable-bundle migration and validation pipeline;
• require exactly one Dashboard;
• require a complete valid query dependency closure;
• reject unsupported bundle, Dashboard, or query Spec versions;
• reject malformed, oversized, truncated, or semantically invalid payloads.

Do not partially import resources. All conflict decisions and the final additive import must be revalidated against the latest committed local workspace before the atomic commit.

A failed catalogue read or import must leave the current local workspace byte-for-byte unchanged.

Security and permissions

The catalogue contains readable SQL and Dashboard configuration. Visibility is controlled entirely by ClickHouse SELECT grants and row policies on the configured relation.

V1 adds no application ACL model, ownership enforcement, or tenant filtering beyond what the server query returns to the current authenticated user.

Do not expose payload text in diagnostics. Never execute imported panel SQL during listing or import. Queries run only after the normal authenticated Dashboard execution flow and ordinary safety gates apply.

Catalogue population Bash script

Add a checked-in operator tool, for example:

scripts/load-dashboard-catalogue.sh

The script populates an already provisioned ClickHouse catalogue from portable Dashboard JSON files such as those under examples/. It is deployment/CI tooling, not a browser command and not a user-facing publishing workflow.

Inputs and modes

Support:

• one explicit JSON file;
• several explicit JSON files;
• a directory of JSON files;
• examples/dashboard-manifest.json, preserving manifest order and human-readable names where applicable;
• --table database.table, defaulting to asb.dashboards;
• ordinary clickhouse-client connection options or an existing ClickHouse client configuration;
• --dry-run, which validates and prints a metadata summary without inserting rows.

Example invocations:

scripts/load-dashboard-catalogue.sh \
  --table asb.dashboards \
  examples/clickhouse-operations.json

scripts/load-dashboard-catalogue.sh \
  --table analytics.shared_dashboards \
  --manifest examples/dashboard-manifest.json

scripts/load-dashboard-catalogue.sh \
  --dry-run \
  --directory examples

Do not embed credentials in the script. Prefer standard clickhouse-client configuration/environment mechanisms; command-line connection flags may be forwarded when explicitly supplied. Do not print passwords, tokens, or full payload contents.

Validation before INSERT

Bash should orchestrate the operation, but it must reuse the repository's canonical codec/validation tooling rather than attempting to validate portable bundles with ad-hoc jq checks alone.

For every input file:

1. read the complete JSON bytes without shell interpolation;
2. decode and validate through the same current portable-bundle validation code used by the application/build tooling;
3. require exactly one Dashboard;
4. require exactly its complete query dependency closure;
5. reject unrelated queries, multiple Dashboards, unsupported versions, malformed JSON, and resource-limit violations;
6. derive trusted insert metadata from the validated payload:
   • dashboard_id;
   • Dashboard title and description;
   • bundle version;
   • query count;
   • tile count;
7. preserve the canonical validated JSON text as payload.

A failed file must produce a non-zero exit status and no INSERT for that file. The default batch behaviour should fail fast before inserting anything when any selected input is invalid. An explicitly named future --continue-on-error mode may relax that, but is not required for v1.

Table and privilege checks

Before inserting:

• parse --table as exactly database.table and quote both identifiers independently;
• run DESCRIBE TABLE or an equivalent conformance check;
• require the columns and compatible types defined by this issue;
• verify the insert path without creating or altering schema;
• fail clearly when the table is missing, incompatible, or the operator lacks INSERT.

The script must not silently create, alter, truncate, update, or delete the catalogue table. Provide the reference CREATE TABLE statement as documentation or a separate checked-in SQL file for administrators.

INSERT behaviour

Insert one immutable row per validated Dashboard using FORMAT JSONEachRow or another body-safe ClickHouse input format. Do not build SQL by interpolating JSON strings into a quoted SQL literal.

The insert should provide only:

dashboard_id
title
description
bundle_version
query_count
tile_count
payload

Let the table defaults generate version and saved_by, and derive saved_at from the generated version as defined by the reference schema.

Each successful invocation for the same dashboard_id appends a new version. The script must not overwrite or deduplicate an existing version in v1. Re-running the examples therefore publishes a new latest version of each Dashboard.

Use one insert request per Dashboard so errors identify the affected file precisely. Report a concise success line containing file name, Dashboard title/id, and the server-generated version when it can be queried unambiguously; otherwise report that a new version was appended without guessing its ID.

Script safety

• Use strict Bash mode (set -euo pipefail).
• Use temporary files safely and remove them with a trap.
• Preserve Unicode, newlines, quotes, and backslashes in SQL and Spec content.
• Never pass the payload through shell eval or interpolate it into command text.
• Never expose payload contents in normal logs.
• Do not automatically retry an INSERT after an ambiguous network failure, because the row may already have committed.
• Return a non-zero status on validation, conformance, authentication, privilege, transport, or insert failure.

Architecture

Add a dedicated read-only Dashboard-catalogue service behind injected seams. Keep responsibilities separated:

• strict config parsing and per-connection capability resolution;
• qualified identifier parsing/quoting;
• relation conformance checks;
• catalogue-list SQL construction;
• exact-version payload fetch;
• cancellable ClickHouse transport;
• catalogue dialog presentation;
• portable-bundle decode/validation;
• delegation to the existing additive Dashboard import command.

Keep the catalogue loader separate from browser modules. Its reusable validation/metadata extraction helper may live in repository tooling and be called by the Bash wrapper, but browser code must not acquire INSERT capability.

UI modules must not concatenate untrusted identifiers or payload values into SQL.

Tests

Cover at least:

Configuration and capability

• host-specific relation overrides top-level/default;
• top-level relation applies to the current server;
• absent configuration probes asb.dashboards;
• explicit dashboard_catalog: false disables probing and uses built-ins;
• invalid qualified identifiers fail closed;
• configured identifiers are quoted independently;
• missing table, missing SELECT, and incompatible columns fall back cleanly;
• capability cache is scoped to the authenticated connection session.

Catalogue listing

• latest rows are grouped by dashboard_id, not title;
• tuple-valued argMax keeps metadata from one version;
• duplicate titles remain distinguishable;
• ordering and 100-row bound are deterministic;
• UInt64 versions retain exact precision;
• payload is not selected by the list query;
• cancellation and stale-response suppression work.

Payload and import

• exact dashboard_id plus version fetches the selected payload;
• valid PortableBundleV2 with one Dashboard imports through the existing additive path;
• local Dashboard and query identities are reminted;
• query conflicts use existing decisions and revalidation;
• malformed, unsupported, multi-Dashboard, incomplete-closure, and oversized payloads do not mutate local state;
• row disappearance and permission loss are non-destructive;
• imported Dashboard opens in Edit mode;
• importing the same server entry twice creates two independent local copies.

Catalogue loader

• one explicit example JSON file validates and inserts one row;
• manifest mode loads entries in manifest order;
• directory and multiple-file modes are deterministic;
• --dry-run performs no INSERT;
• table override is parsed and quoted safely;
• invalid, unsupported, multi-Dashboard, unrelated-query, incomplete-closure, and oversized files fail before insertion;
• a batch containing one invalid file inserts no rows by default;
• Unicode, newlines, quotes, and backslashes round-trip exactly in payload;
• inserted metadata matches the validated payload rather than untrusted external flags;
• repeated loading appends a newer version instead of replacing a row;
• missing table, incompatible schema, missing INSERT, and ambiguous transport failure return non-zero without automatic retry;
• credentials and payload contents are absent from ordinary logs.

Fallback and UI

• readable compatible server catalogue replaces built-in entries in the catalogue dialog;
• unavailable catalogue shows built-in examples;
• source identity is visible in the dialog;
• Cancel, Escape, outside close, and request cancellation make no workspace changes;
• ordinary local-file Import Dashboard… remains unchanged;
• every application surface uses the same shared File-menu command.

Acceptance criteria

• The current ClickHouse server can expose a Dashboard catalogue through a configured relation or the default asb.dashboards name.
• A host-specific configured relation is used only for that host.
• dashboard_catalog: false disables the default probe.
• The browser requires only read access and performs no server writes or DDL.
• When a compatible catalogue is readable, the catalogue import dialog lists its latest Dashboard entries instead of built-in examples.
• When it is unavailable, built-in examples remain usable.
• Listing does not fetch payloads.
• Selection fetches the exact pinned Dashboard version.
• Payloads are fully validated as untrusted portable bundles before mutation.
• Import reuses the existing additive Dashboard import planner and atomic workspace mutation path.
• Imported resources are independent local copies with reminted identities.
• Server changes never synchronize into a previously imported Dashboard.
• Local changes are never written to the catalogue.
• A checked-in Bash loader can validate and upload one file, several files, a directory, or the examples manifest to a configured catalogue table.
• The loader reuses canonical portable-bundle validation and rejects invalid input before INSERT.
• The loader inserts body-safe JSON, preserves payload bytes, appends immutable versions, and performs no DDL/update/delete operations.
• Failures and cancellation leave the active workspace unchanged.
• Unit and E2E coverage exercise server catalogue, fallback, validation, additive import, and loader behaviour.
• config.json examples, loader usage, catalogue schema/provisioning, deployment documentation, security documentation, and CHANGELOG.md are updated.

Non-goals

• Remote or server-authoritative workspaces.
• Background synchronization or autosave.
• Browser-based publishing, updating, or deleting catalogue entries.
• Dashboard version-history UI.
• Stable server-backed Dashboard view links.
• Collaboration, locking, CAS updates, CRDTs, or merge handling.
• Application-managed ACLs, sharing invitations, expiry, or revocation.
• Server-side query execution or stored query results.
• Replacing ordinary local JSON Dashboard import/export.
• A general catalogue administration UI.

Supersedes the read-side product direction of #378 with a smaller Dashboard-only v1. Future browser publishing must be specified separately.