Server-backed workspace snapshots in ClickHouse

[BorisTyshkevich]

Summary

Add optional server-backed workspace snapshot storage using a configured ClickHouse table.

The existing local workspace repository remains the live editing and autosave store. This feature adds two explicit Workbench File-menu operations:

Save workspace to server…
Load workspace from server…

Save appends an immutable version row. Load searches the server catalog, lets the user select one of the ten latest versions, validates the stored portable bundle, and transactionally replaces the current local workspace contents.

There is no app-specific backend. The browser reads and writes the configured table directly through the current authenticated ClickHouse connection.

Goals

• Store shareable, versioned workspace snapshots in ClickHouse.
• Keep every historic version as an immutable row.
• Use one row per version of one Dashboard-bearing workspace.
• Search latest Dashboard heads by user-visible name/title.
• Display the latest ten versions with timestamp, saver, counts, note, and a compact version tag.
• Reuse the canonical portable-bundle codec and transactional workspace import/replace pipeline.
• Make the feature deployment-configurable per ClickHouse server.

Non-goals

• Replacing the local IndexedDB workspace repository.
• Automatic background synchronization or autosave to ClickHouse.
• Collaborative editing, merge/conflict resolution, CAS, or CRDT behavior.
• Updating or deleting server versions.
• Automatically creating, altering, or migrating the configured ClickHouse table.
• A separate central storage server distinct from the current ClickHouse connection.
• Application-managed ACLs. Visibility is controlled by ClickHouse grants and row policies.
• Server storage for a workspace with no Dashboard.

Stored payload

Store canonical PortableBundleV1 JSON, not the internal StoredWorkspaceV1 representation.

Each saved payload must contain:

• exactly one Dashboard document;
• the complete current workspace saved-query catalog, including queries not referenced by the Dashboard;
• portable bundle metadata using the current workspace name;
• canonical portable-bundle encoding and validation.

The Dashboard's stable dashboard.id is the server history identity. The workspace name and Dashboard title are searchable/display metadata only and may change between versions without changing identity.

Save is unavailable when the current workspace has no Dashboard.

ClickHouse table

The configured table is append-only and must preserve every historic row. Use a plain MergeTree, not ReplacingMergeTree.

Reference schema:

CREATE TABLE asb.workspace_versions
(
    dashboard_id String,

    version UInt64 DEFAULT generateSnowflakeID(),

    saved_at DateTime64(3, 'UTC')
        ALIAS snowflakeIDToDateTime64(version),

    name String,
    dashboard_title String,

    saved_by String DEFAULT currentUser(),

    app_version LowCardinality(String),
    bundle_version UInt16,

    query_count UInt32,
    tile_count UInt32,
    filter_count UInt32,

    note String DEFAULT '',

    payload String CODEC(ZSTD(3)),

    payload_hash UInt64
        MATERIALIZED cityHash64(payload)
)
ENGINE = MergeTree
ORDER BY (dashboard_id, version);

The exact database and table are deployment-configured. The application must not assume asb.workspace_versions.

Required columns

The application must require compatible columns for:

dashboard_id String
version UInt64
name String
dashboard_title String
saved_by String
app_version String-compatible
bundle_version UInt16-compatible
query_count UInt32-compatible
tile_count UInt32-compatible
filter_count UInt32-compatible
note String
payload String

version must be generated by the table default. saved_by must be generated from currentUser() by the table default. The application does not send trusted values for either field.

saved_at and payload_hash may be aliases/materialized columns as shown, but the required listing queries must be supported.

ClickHouse requirement

Server workspace storage requires ClickHouse support for:

generateSnowflakeID()
snowflakeIDToDateTime64(...)

Document the minimum supported ClickHouse version for these functions.

Version identity

Use the full UInt64 Snowflake ID as the immutable version identifier and chronological ordering key.

Do not use UUIDs or a max(version) + 1 allocation query.

The browser must treat UInt64 identifiers as decimal strings or BigInt; it must never round-trip them through JavaScript number.

User-facing version tag

Display the low 16 bits of the Snowflake ID as four uppercase hexadecimal digits:

7F2A
00C4
B913

The tag is a visual shorthand only. It may repeat and must never be used alone for selection or loading. All operations use the complete UInt64 value.

The server query may provide the tag directly:

right(hex(version), 4) AS version_tag

Configuration

Extend config.json with an optional server-storage capability:

{
  "workspace_storage": {
    "table": "asb.workspace_versions"
  }
}

The presence of a valid workspace_storage object enables the feature. A separate enabled flag is unnecessary.

The table value must be parsed strictly as a qualified ClickHouse identifier, for example database.table. Quote the database and table identifiers independently. Never treat the configured value as a free-form SQL fragment.

For configured alternate hosts, allow a host-specific capability:

{
  "hosts": [
    {
      "label": "Production",
      "url": "https://clickhouse.example.com",
      "auth": "oauth",
      "workspace_storage": {
        "table": "asb.workspace_versions"
      }
    }
  ]
}

Rules:

• top-level storage applies to the serving/current-origin connection;
• a configured host may provide its own storage table;
• manual or unlisted connections have no server-storage capability;
• storage always uses the current connected ClickHouse server and credentials;
• when no storage is configured, the two server operations are absent from the File menu.

Table conformance check

Before the first Save or Load operation for a connection, validate the configured table with a lightweight metadata query such as DESCRIBE TABLE.

Cache a successful check for the connection session. A failed check must show one clear diagnostic describing the missing/incompatible column or missing table privilege.

Do not run DDL and do not attempt automatic schema migration.

File menu

When server storage is configured and valid, add:

Save workspace to server…
Load workspace from server…

Use explicit destination wording so these operations are not confused with local persistence or JSON file import/export.

Suggested grouping:

New workspace…

Server
  Save workspace to server…
  Load workspace from server…

File
  Open workspace…
  Export workspace…
  Import queries…

The exact grouping may follow the current menu visual conventions, but keyboard order must match visual order.

This is a Workbench operation in the MVP. Do not expose it on detached/read-only Dashboard routes.

Save workspace flow

Opening Save shows a small dialog:

Save workspace to server

Name: [current workspace name]
Note: [optional]

Cancel                         Save

The name is editable metadata and defaults to the current workspace name. The note is optional.

Save sequence:

1. Disable duplicate submission while a request is in progress.
2. Flush all queued local workspace writes.
3. Load the latest committed local workspace.
4. Require a non-null Dashboard.
5. Build a canonical PortableBundleV1 containing exactly one Dashboard and the complete workspace query catalog.
6. Validate and encode the bundle before sending anything.
7. Collect metadata: Dashboard ID/title, name, application version, bundle version, and query/tile/filter counts.
8. POST one INSERT ... FORMAT JSONEachRow request to the configured table.
9. Let ClickHouse generate version, saved_at, saved_by, and payload_hash.
10. Report success only after ClickHouse confirms the INSERT.

Reference shape:

INSERT INTO `database`.`table`
(
    dashboard_id,
    name,
    dashboard_title,
    app_version,
    bundle_version,
    query_count,
    tile_count,
    filter_count,
    note,
    payload
)
FORMAT JSONEachRow
{"dashboard_id":"...","name":"...","payload":"..."}

Use the ordinary authenticated ClickHouse POST path with the SQL and JSONEachRow payload in the request body. Multi-megabyte workspace payloads are acceptable; do not introduce a separate upload protocol or URL-parameter transport.

Every explicit successful Save creates a new version, even when the payload matches the previous version. payload_hash is diagnostic metadata, not an MVP deduplication gate.

Save failure behavior

• A failed INSERT leaves the local workspace unchanged.
• Validation failure prevents the request.
• A ClickHouse error shows a useful table/grant/schema diagnostic.
• Do not automatically retry an INSERT after an ambiguous network failure: the server may have committed even if the response was lost.
• For an ambiguous completion, report that the save result is unknown and direct the user to reopen server history.
• Authentication refresh before the first send may follow the existing client behavior, but no generic post-send retry may create another version automatically.

Search and Load flow

Open a dialog with a name/title search field:

Load workspace from server

Name or Dashboard title: [________________] [Search]

Search returns up to ten latest Dashboard heads. Name is not an identity and duplicate names are allowed.

Each result shows enough metadata to distinguish duplicates:

Query Log Explorer
Latest: 22 Jul 2026 16:42 · alice · 18 queries · version 7F2A
Dashboard ID: qle-dashboard

The Dashboard ID may be visually muted or shown only when needed, but selection is always by stable dashboard_id.

Latest-head query

Use one tuple-valued argMax so all displayed metadata comes from the same latest row:

SELECT *
FROM
(
    SELECT
        dashboard_id,
        argMax(
            tuple(
                name,
                dashboard_title,
                version,
                saved_by,
                query_count,
                tile_count,
                filter_count,
                payload_hash
            ),
            version
        ) AS latest,
        argMin(saved_by, version) AS created_by,
        min(version) AS first_version
    FROM `database`.`table`
    GROUP BY dashboard_id
)
WHERE
    positionCaseInsensitive(latest.1, {search:String}) > 0
    OR positionCaseInsensitive(latest.2, {search:String}) > 0
ORDER BY latest.3 DESC
LIMIT 10

The implementation may use an equivalent query, but it must not combine metadata from different versions.

Version list

After selecting a Dashboard, show its ten latest versions:

SELECT
    toString(version) AS version,
    right(hex(version), 4) AS version_tag,
    snowflakeIDToDateTime64(version) AS saved_at,
    saved_by,
    name,
    note,
    query_count,
    tile_count,
    filter_count,
    length(payload) AS payload_bytes
FROM `database`.`table`
WHERE dashboard_id = {dashboard_id:String}
ORDER BY version DESC
LIMIT 10

Example UI:

22 Jul 16:42 · alice · 7F2A
Add customer filter
18 queries · 9 tiles

22 Jul 11:03 · bob · 00C4
Before layout changes
17 queries · 8 tiles

Ten versions is the MVP limit. Older versions remain in the table but do not need a Load-more UI in this issue.

Loading one version

Load by exact full version ID:

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

Before changing local state:

1. fetch the selected payload;
2. decode it as PortableBundleV1;
3. require exactly one Dashboard;
4. run complete structural and cross-resource semantic validation;
5. show the existing destructive workspace-replacement confirmation;
6. transactionally replace the current workspace contents through the existing import planner/repository path;
7. adopt the selected row's name as the local workspace name;
8. retain the loaded Dashboard ID so a later server Save appends to the same Dashboard history.

No local workspace mutation occurs until the payload has completely decoded and validated.

Loading an older version does not update or delete server rows. A later Save appends a new version with a higher Snowflake ID under the same Dashboard ID.

Timestamp presentation

Snowflake time is authoritative. Derive it with snowflakeIDToDateTime64(version).

Store and query timestamps in UTC. Display them using the application's normal localized date/time formatting, while preserving the full instant in an accessible title or detail surface.

Security and privileges

The application relies on ClickHouse authorization.

Minimum required privileges on the configured table:

SELECT
INSERT

No UPDATE, DELETE, ALTER, or CREATE privilege is required.

Workspace bundles contain SQL and may contain sensitive literals. Deployments must choose the desired visibility model through ClickHouse grants and row policies, for example:

• a shared catalog visible to all permitted users;
• private rows restricted by saved_by = currentUser();
• a deployment-specific team/tenant policy.

The application must not implement a second ACL system inside the payload.

Architecture

Introduce a dedicated server-workspace storage service behind an injected application/network seam.

Responsibilities should remain separated:

• config parsing and capability resolution;
• pure table-name parsing and SQL construction;
• portable-bundle snapshot building and validation;
• ClickHouse POST operations;
• Workbench File-menu dialogs;
• transactional projection through the existing workspace mutation/import path.

Do not make UI modules construct unescaped identifiers or raw payload SQL manually.

The local WorkspaceRepository contract remains unchanged and authoritative for live editing.

Cancellation and concurrency

• Search and version-list requests should be cancellable when the dialog closes or a newer search replaces them.
• Closing the dialog must not mutate workspace state.
• Save prevents ordinary double submission.
• A server Load enters the same local workspace write queue used by other destructive workspace operations.
• A local write that completes before Save snapshot construction must be included because Save flushes the queue first.
• A Load must re-confirm against the current workspace before replacement if another local mutation landed while the selection dialog was open.

Tests

Cover at least:

Configuration and schema

• top-level configured store;
• host-specific configured store;
• no configuration hides both actions;
• strict database.table identifier parsing and quoting;
• invalid/incompatible table conformance diagnostics;
• no DDL or migration attempts.

Save

• workspace without Dashboard cannot be saved;
• Save flushes queued writes and snapshots committed truth;
• portable bundle contains exactly one Dashboard and the complete query catalog;
• canonical encode/validation failure sends no request;
• INSERT excludes server-generated fields;
• payload with quotes, backslashes, Unicode, newlines, and several-megabyte SQL round-trips through JSONEachRow;
• successful Save appends a row and reports server metadata;
• identical explicit Saves create separate versions;
• duplicate click is suppressed while pending;
• ambiguous network failure is not automatically retried.

Search/history

• latest-head grouping is by Dashboard ID, not name;
• duplicate names remain distinct;
• tuple-valued latest metadata comes from one version;
• search matches name and Dashboard title;
• version list is ordered by full UInt64 descending and limited to ten;
• full IDs remain strings/BigInt without precision loss;
• low 16-bit tag renders as four uppercase hexadecimal digits;
• tag collisions do not affect selection.

Load

• selected full version fetches the exact payload;
• malformed, unsupported, multi-Dashboard, or semantically invalid bundles do not mutate local state;
• cancellation writes nothing;
• confirmed load replaces contents atomically through the existing planner;
• loaded row name becomes the local workspace name;
• loading an old version then saving appends to the same Dashboard history;
• failure leaves the previous committed local workspace unchanged.

Permissions and errors

• missing SELECT and INSERT privileges produce useful diagnostics;
• missing table and incompatible schema produce useful diagnostics;
• authentication loss follows the existing sign-in behavior;
• search/load errors remain non-destructive.

Acceptance criteria

• config.json can declare the exact ClickHouse table providing workspace storage for the current server/host.
• When configured, the Workbench File menu exposes explicit Save-to-server and Load-from-server operations.
• When not configured, existing local and JSON-file behavior is unchanged.
• Save is available only for a workspace containing one Dashboard.
• One Save POST inserts one immutable portable-bundle snapshot row containing one Dashboard and the complete query catalog.
• Server-generated UInt64 Snowflake IDs provide version identity and chronological ordering.
• The UI displays a four-hex-digit low-16-bit version tag but always selects/loads by the full ID.
• Server-generated time and currentUser() metadata are shown in search/history results.
• Latest Dashboard heads are selected with a consistent tuple-valued argMax by Dashboard ID.
• Load searches by name/title, shows ten latest versions, validates the selected bundle, confirms replacement, and commits atomically.
• Loading an older version and saving creates a new version without modifying history.
• The local IndexedDB workspace remains the live editing repository; this feature is explicit snapshot Save/Load only.
• Table access is governed exclusively by ClickHouse SELECT/INSERT grants and optional row policies.
• Large POST bodies, UInt64 precision, cancellation, concurrency, and failure paths have regression coverage.

[BorisTyshkevich]

https://chatgpt.com/c/6a5fa2f5-1120-83eb-9bdf-467388ad301b