Multi-workspace persistence: keyed repository, stable workspace keys, list/create/delete

[BorisTyshkevich]

⚠️ Reconciled 2026-07-23 during implementation. The accepted contract is StoredWorkspaceV2; the primary store is the new asb-workspaces-v2 collection, so existing development IndexedDB data is intentionally not migrated. lastOpenedAt is store-owned metadata (not portable workspace content), written with the injected wall clock whenever a workspace opens successfully. If the last-used preference is absent or invalid, implicit startup selects the valid workspace with the newest timestamp, then breaks ties deterministically by key; explicit ?ws= never falls back. Workspace import is additive and remints local identity. Cross-tab create races fail atomically with a duplicate-key diagnostic; broader stale-write protection remains #409. Tracked in roadmap #68 Phase 9.

Goal

Replace the current single current workspace record with a real local multi-workspace repository. Each workspace must be independently addressable, listable, creatable, mutable, exportable, and deletable.

This is the persistence foundation for bookmarkable workspace URLs such as:

/sql?ws=clickhouse_operations

Product decisions

• A browser profile may contain multiple local workspaces.
• Each workspace owns an ordered saved-query collection and zero or one dashboard.
• Workspace identity has three distinct fields:
  • id: opaque immutable application identity.
  • key: immutable, unique, human-readable URL key such as clickhouse_operations.
  • name: mutable display name such as ClickHouse Operations.
• The ws URL parameter resolves key, not the mutable display name and not the opaque ID.
• Workspace keys are unique case-insensitively.
• Editing a workspace name does not break bookmarks.
• Editing the workspace key is not exposed in the initial UI.
• There is no migration requirement for existing development data. The schema/store may be reset during development.

Current limitation

The current IndexedDB adapter stores one aggregate under a fixed key (current) and WorkspaceRepository exposes only current-workspace operations. Multi-workspace routing and management cannot be implemented cleanly on top of that model.

Relevant current modules include:

• src/workspace/indexeddb-workspace-store.ts
• src/workspace/workspace-store.types.ts
• src/workspace/workspace-repository.ts
• src/workspace/stored-workspace.ts
• generated JSON-schema types and source schemas

Required data model

Evolve the stored-workspace contract to include a stable URL key:

interface StoredWorkspaceV2 {
  storageVersion: 2;
  id: string;
  key: string;
  name: string;
  queries: SavedQueryV2[];
  dashboard: DashboardDocumentV1 | null;
}

Exact versioning/naming may differ, but the three identity concepts must remain separate.

Key constraints:

• non-empty;
• lowercase canonical form;
• ASCII letters, digits, _, and - only;
• begins with a letter or digit;
• unique case-insensitively;
• stable after creation.

Suggested derivation:

ClickHouse Operations -> clickhouse_operations
Production EU         -> production_eu

When the generated key collides, derive a deterministic available key such as clickhouse_operations_2, while allowing the create dialog to validate a user-entered key before commit.

Repository contract

Replace current-record semantics with collection semantics. The exact interface may vary, but it must support equivalent operations:

interface WorkspaceSummary {
  id: string;
  key: string;
  name: string;
  queryCount: number;
  hasDashboard: boolean;
}

interface WorkspaceRepository {
  list(): Promise<WorkspaceSummary[]>;
  loadByKey(key: string): Promise<StoredWorkspaceV2 | null>;
  loadById(id: string): Promise<StoredWorkspaceV2 | null>;
  create(workspace: StoredWorkspaceV2): Promise<WorkspaceCommitResult>;
  commit(workspace: StoredWorkspaceV2): Promise<WorkspaceCommitResult>;
  delete(id: string): Promise<void>;
}

Requirements:

• IndexedDB stores one record per workspace.
• Prefer immutable id as the primary object-store key.
• Add a unique key index for workspace.key, or maintain an equivalent validated lookup structure.
• create rejects duplicate IDs and duplicate keys.
• commit validates the complete workspace before one atomic record write.
• delete removes exactly one workspace.
• list returns summaries without requiring callers to understand encoded storage internals.
• Sort order is a UI concern; repository results may be deterministic by key or ID.

Last-used workspace preference

Persist the last successfully opened workspace key separately from workspace records.

Expected behavior for callers:

• /sql may resolve the last-used key.
• Explicit /sql?ws=... must never silently fall back to the last-used workspace.
• Deleting the last-used workspace clears or updates the preference atomically enough that startup cannot repeatedly resolve a deleted key.

This may use a small preferences store or a dedicated record outside the workspace object store.

Workspace operations

Provide application-level operations for:

• create workspace;
• rename display name;
• delete workspace;
• list summaries;
• resolve by URL key.

Rename changes only name. It must not rewrite key or id.

Creating a workspace initializes:

• fresh opaque ID;
• validated unique key;
• chosen display name;
• empty query collection;
• dashboard: null.

Import/export semantics

This issue provides repository primitives needed by later UI work.

Settled behavior:

• Import workspace creates a new workspace; it does not replace the active workspace.
• Imported workspace IDs are reminted locally.
• Imported workspace keys are validated and made unique through an explicit conflict resolution step in the UI/application layer.
• Import queries remains an operation against the active workspace.
• Export continues to serialize one workspace as a portable bundle; URL key metadata may be retained as workspace metadata, but portable import must still mint local identity.

Deletion semantics

Repository deletion is permanent in the initial version; there is no trash.

The UI confirmation and post-delete navigation are covered by the workspace-management issue, but persistence must guarantee:

• only the selected workspace record is removed;
• unrelated workspaces remain byte-for-byte unaffected;
• loading the deleted ID/key returns null;
• deleting an unknown ID is either idempotent or returns a documented not-found result.

Validation and failure behavior

• Whole-workspace structural and semantic validation remains fail-closed.
• Duplicate workspace keys fail before persistence.
• Failed writes do not publish candidate state.
• Failed deletes do not make the application believe deletion succeeded.
• Diagnostics should distinguish invalid workspace content, duplicate key, persistence failure, and not found where relevant.

Tests

Add unit/integration coverage for at least:

1. Creating two workspaces and loading each independently.
2. Listing summaries with correct query count and dashboard presence.
3. Case-insensitive duplicate-key rejection.
4. Renaming name without changing key or id.
5. Committing one workspace without changing another.
6. Deleting one workspace without changing another.
7. Last-used preference read/write and deleted-key handling.
8. Persistence failures leave prior records intact.
9. Whole-workspace validation still runs on every create/commit.
10. Key canonicalization and collision behavior.

Acceptance criteria

• At least two workspaces can coexist in IndexedDB and survive reload.
• Each can be loaded by its stable human-readable key.
• Workspace display-name changes do not change its URL key.
• The repository can list, create, commit, and delete individual workspaces.
• Last-used workspace is persisted separately.
• No production code depends on a single fixed current workspace record.

Non-goals

• Cross-device or multi-user synchronization.
• Backend storage or workspace ACLs.
• Trash/recovery.
• Editable workspace keys.
• Cross-tab conflict prevention; tracked separately.
• Migration of existing development IndexedDB data.