Build capability-aware documentation search service

[BorisTyshkevich]

Parent design: #420

Purpose

Implement the server/application layer for documentation search without adding the Reference drawer UI. The result must be a fully tested SchemaCatalogService.docSearch() API that searches documentation exposed by the active ClickHouse server.

The service must execute one dynamically generated, capability-aware UNION ALL query, normalize heterogeneous rows, retain physical source provenance, merge duplicate logical entities, rank candidates deterministically in JavaScript, and remain safe across reconnects and stale capability state.

Scope

Query normalization

• Trim leading and trailing whitespace.
• Collapse repeated internal whitespace.
• Minimum normalized query length: 2 visible characters.
• Maximum normalized query length: 200 characters.
• Tokenize multi-word input deterministically.
• Every token must match at least one searchable field of a candidate row.
• Do not support regular expressions.
• Do not search examples in this phase.

Initial physical sources

Support branches for:

• system.documentation
• system.functions
• system.formats
• system.table_engines
• system.database_engines
• system.data_type_families
• system.table_functions

The design must allow later source adapters without changing the public search contract.

Capability probing

Reuse the existing documentation capability model backed by system.columns.

For every source, determine independently:

• whether the table is available to the current connection;
• whether its required columns exist;
• which optional searchable/projected columns exist.

A query branch is emitted only when its minimum shape is confirmed. Never mention an unavailable table or unconfirmed optional column in generated SQL.

Examples:

• system.documentation requires name, type, and description; source is optional.
• system.functions may be usable with name and is_aggregate; alias_to, description, syntax, categories, and introduced_in are optional.
• structured engine/type/format branches search only confirmed rich fields and project neutral values for missing optional fields.

Capabilities are scoped to the active connection generation and reset by catalog invalidation/reconnect/sign-out.

One dynamic UNION ALL query

A valid submitted search executes exactly one generated SQL statement.

Every branch must project the same ordered, type-compatible columns and report its exact physical source as a literal:

'system.functions' AS source_table

Required normalized columns:

source_table   String
server_type    String
name           String
alias_to       String
description    String
syntax         String
categories     String
introduced_in  String

The exact optional payload may be extended, but source_table, server_type, and name are mandatory and physical provenance must never be inferred later from logical kind.

Conceptual shape:

WITH {query:String} AS q
SELECT
    source_table,
    server_type,
    name,
    alias_to,
    description,
    syntax,
    categories,
    introduced_in
FROM
(
    /* capability-generated branches */
)
LIMIT 300
FORMAT JSON

Use bound parameters where supported by the metadata transport, otherwise the existing SQL string-escaping seam. Raw user input must never be interpolated.

Candidate filtering

Search available fields in this order of importance:

• canonical name;
• alias;
• logical/server type;
• category;
• syntax;
• description.

For multiple tokens, each token must occur in at least one available searchable field. Tokens may match different fields.

Candidate bounds

• Maximum 75 rows per source branch.
• Maximum 300 rows in the outer query.
• Maximum 500 UTF-8 characters projected for candidate description text.
• Maximum 50 results after normalization, merge, and JavaScript ranking.

Each branch must use a preliminary name-heavy order before its LIMIT so exact and prefix candidates are less likely to be discarded before final ranking.

This ordering is candidate preservation only; final relevance is JavaScript-owned.

Contracts

Suggested source type:

type DocSearchSourceTable =
  | 'system.documentation'
  | 'system.functions'
  | 'system.formats'
  | 'system.table_engines'
  | 'system.database_engines'
  | 'system.data_type_families'
  | 'system.table_functions';

Suggested raw row:

interface RawDocSearchRow {
  sourceTable: DocSearchSourceTable;
  serverType: string;
  name: string;
  aliasTo?: string;
  description?: string;
  syntax?: string;
  categories?: string;
  introducedIn?: string;
}

Suggested result:

interface DocSearchResult {
  target: DocTarget;
  title: string;
  summary: string;
  score: number;
  matches: Array<'name' | 'alias' | 'syntax' | 'type' | 'category' | 'description'>;
  sources: DocSearchSourceTable[];
}

interface DocSearchOptions {
  limit?: number;
  kinds?: readonly DocKind[];
}

interface DocSearchResponse {
  results: DocSearchResult[];
  queriedSources: DocSearchSourceTable[];
}

Public service API:

docSearch(
  query: string,
  options?: DocSearchOptions,
): Promise<DocLookup<DocSearchResponse>>;

Expected statuses:

• found: one or more ranked results;
• missing: query succeeded with zero results;
• unavailable: no usable union could be executed or the bounded recovery attempt failed.

kinds must be supported by the service contract even though the first UI searches all kinds.

Kind normalization

Normalize source-specific representations into the existing stable DocKind vocabulary.

Examples:

• system.functions.is_aggregate = 0 → function
• system.functions.is_aggregate = 1 → aggregate-function
• system.documentation.type = 'Table Engine' → table-engine
• system.formats → format
• system.data_type_families → data-type

Unknown future system.documentation.type values map to unknown while preserving the original server label.

Keep physical source and logical kind separate:

sourceTable = system.functions
target.kind = aggregate-function

Baseline JavaScript ranking

Use deterministic field-weighted ranking:

1. exact canonical name, case-sensitive;
2. exact canonical name, case-insensitive;
3. exact alias;
4. canonical-name prefix;
5. alias prefix;
6. canonical-name substring;
7. alias substring;
8. syntax match;
9. type/category match;
10. description match.

Suggested weights:

1000 exact canonical name
 900 exact canonical name ignoring case
 850 exact alias
 800 canonical-name prefix
 750 alias prefix
 700 canonical-name substring
 650 alias substring
 400 syntax
 300 type/category
 100 description

Cap repeated description contribution so long prose cannot outrank a strong name match.

Deterministic tie breakers:

1. score descending;
2. shorter canonical name;
3. logical kind label;
4. case-insensitive name;
5. case-sensitive name.

Position/proximity-based ranking and match-centered snippets are intentionally deferred to a separate child issue.

Deduplication and merge

The union may return the same logical target from broad and structured tables.

Group by:

`${target.kind}:${canonicalName.toLocaleLowerCase()}`

Rows with the same name but different logical kinds remain distinct.

Merge policy:

• canonical name: exact structured row, then broad row;
• alias: structured source;
• syntax: structured source;
• categories: structured source;
• introduced version: structured source;
• summary: structured description when populated, otherwise broad documentation text;
• sources: union of all contributing physical tables.

Search must not duplicate full entry normalization. Selecting a result later will use existing docEntry(target) behavior.

Connection and request lifecycle

• Identical concurrent searches with the same normalized query, kinds, limit, and connection generation share one promise.
• Completed responses may use a per-connection LRU cache capped at 20 searches.
• Cache normalized/ranked summaries, not full documentation entries.
• Clear cache and search capabilities on catalog.invalidate() and connection/reference reload.
• Old-generation responses must not populate current caches.
• Physical query cancellation is not required; logical generation safety is required.

Capability recovery

The single union is atomic. Implement one bounded stale-capability recovery attempt:

1. Build and execute using current capabilities.
2. On a table/column/capability-shaped failure, invalidate documentation-search capability state.
3. Reprobe relevant sources.
4. Rebuild and retry once.
5. Return unavailable if the retry fails.

Do not repeatedly remove sources based on parsed errors. Do not toast; the caller renders an unavailable state.

Suggested boundaries

src/core/doc-search.ts

Pure logic for:

• normalization/tokenization;
• source capability-to-branch SQL;
• normalized projection definitions;
• raw row normalization;
• kind mapping;
• deduplication and merge;
• baseline scoring and sorting;
• bounds.

src/application/schema-catalog-service.ts

Own:

• capability state and probes;
• generated union execution;
• bounded reprobe/retry;
• connection generation;
• in-flight deduplication;
• LRU cache;
• docSearch() API.

src/net/ch-client.ts

Add a narrow loader for the generated statement and bounded JSON row decoding. It must remain independent of UI and ranking.

Tests

Normalization

• trims and collapses whitespace;
• rejects fewer than two characters;
• enforces maximum length;
• deterministic tokens;
• never emits unescaped raw input.

SQL generation

• emits one union containing every usable branch;
• omits unavailable tables;
• omits unconfirmed optional columns;
• projects identical ordered columns and compatible neutral values;
• includes exact physical source_table in every branch;
• applies per-branch and outer limits;
• bounds description projection;
• applies preliminary exact/prefix/name ordering;
• supports one usable source;
• produces no malformed SQL when no source is usable.

Normalization and kind mapping

• scalar/aggregate functions;
• all initial structured sources;
• known broad type labels;
• unknown future labels;
• null and blank optional fields;
• physical provenance retained.

Ranking and merge

• exact > prefix > substring;
• canonical name > alias > syntax/category > description;
• prose contribution capped;
• multi-token coverage required;
• deterministic ties;
• duplicate broad/structured targets merge;
• source provenance unions;
• structured fields take precedence;
• same name under different kinds remains separate;
• case-only duplicates collapse.

Service lifecycle

• one request per search;
• identical concurrent searches dedupe;
• cache key includes query/kinds/limit/generation;
• LRU is bounded;
• invalidate/reconnect clears state;
• stale generation cannot populate cache;
• first capability failure performs exactly one reprobe/retry;
• successful retry returns results;
• second failure returns unavailable;
• zero rows returns missing;
• final result count is bounded.

Acceptance criteria

• SchemaCatalogService.docSearch() is available without any drawer dependency.
• Every valid search executes one dynamically generated capability-safe UNION ALL statement.
• Every branch reports its actual physical source table.
• No branch references a missing table or unconfirmed optional column.
• Results normalize into the existing DocKind/DocTarget domain.
• Duplicate entities merge while preserving all source provenance.
• Baseline relevance ranking is deterministic and JavaScript-owned.
• Server and final payload limits are enforced.
• Search is connection-generation-safe, cached with a bounded LRU, and invalidated on reconnect/sign-out.
• Stale capability recovery is attempted at most once.
• Unit tests cover SQL, normalization, ranking, merge, caching, and lifecycle.

Non-goals

• Reference drawer UI.
• Search Back-stack behavior.
• Position/proximity-aware ranking.
• Match-centered snippets.
• Search-as-you-type.
• Fuzzy, semantic, vector, or regex search.
• Remote documentation sources.