Searching a semantic knowledge base v7

Once a semantic KB has crawled your schemas, you search it with a natural-language query. The KB embeds your query with the same model it used to index the schema, then returns the tables, views, and columns whose metadata is closest to the query. Every search function takes the KB name as kb_name, which you can omit when only one KB exists.

aidb.semantic_kb_search()

semantic_kb_search() is the composite entry point: it runs the per-source ranked searches (schema metadata and curated semantic aliases) and fuses them into one ranked list with Reciprocal Rank Fusion (RRF). Use it when you want a single ranked answer to "what in my schema relates to this?"

SELECT source_type, entity_type, schema_name, relation_name, column_name, rank
FROM aidb.semantic_kb_search(
    query_text => 'which customers spent the most',
    kb_name    => 'analytics_kb',
    top_k      => 5
);

It returns one row per match:

Because semantic_kb_search() returns both schema entities and matching aliases in one ranked list, it's the natural first call in a text-to-SQL workflow.

Schema-metadata functions

For narrower needs, four functions search only schema metadata. They share the same optional kb_name and a similarity score (cosine similarity, higher is closer), with min_similarity as an optional floor and top_k / offset for paging.

aidb.get_metadata()

This is the broadest schema search, returning columns, tables, and views together with full metadata.

SELECT schema_name, relation_name, column_name, entity_type, similarity
FROM aidb.get_metadata(
    kb_name    => 'analytics_kb',
    query_text => 'customer email address',
    top_k      => 10
);

Parameters: kb_name, query_text, min_similarity, top_k (default 10), offset (default 0). Returns schema_name, relation_name, column_name, entity_type, definition, comment, definition_vector, and similarity.

aidb.get_entity_definitions()

Matches tables and views only.

SELECT schema_name, relation_name, entity_type, similarity
FROM aidb.get_entity_definitions(
    kb_name      => 'analytics_kb',
    query_text   => 'orders and line items',
    entity_types => ARRAY['Table', 'View']
);

Parameters: kb_name, query_text, min_similarity, entity_types (default ARRAY['Table','View']), top_k, offset. Returns schema_name, relation_name, entity_type, definition, comment, similarity.

aidb.get_column_definitions()

Returns just the matching column and table definitions, the leanest result, useful as compact grounding for SQL generation.

SELECT definition
FROM aidb.get_column_definitions(
    kb_name    => 'analytics_kb',
    query_text => 'order total amount'
);

Parameters: kb_name, query_text, min_similarity, top_k, offset. Returns a single definition column.

aidb.search_by_comment()

Matches only the natural-language COMMENT ON text attached to schemas, tables, and columns.

SELECT relation_name, column_name, comment, similarity
FROM aidb.search_by_comment(
    kb_name    => 'analytics_kb',
    query_text => 'order status values'
);

Parameters: kb_name, query_text, min_similarity, top_k, offset. Returns schema_name, relation_name, column_name, entity_type, definition, comment, similarity.

This is the most direct way to leverage documentation you've already written into your schema. For example, a column commented "order lifecycle state: pending, confirmed, shipped, delivered, cancelled" surfaces for a query like "order status values" even if the column itself is named st. Because comment search depends on well-documented objects, keeping COMMENT ON statements current is the highest-leverage way to improve a semantic KB's results.

Tuning results

• min_similarity trades recall for precision. Lower it (for example 0.3) when a query returns nothing; raise it when results are too broad.
• top_k / offset page through results.
• entity_types narrows to the kinds you care about, such as ARRAY['Column'], when you're hunting for a specific field.

Next steps

• Save frequently asked questions as reusable, parameterized queries with semantic aliases.
• Let an agent drive these searches end to end in the text-to-SQL workflow.