Semantic knowledge bases reference v7

Reference for semantic knowledge base (semantic KB) functions. For guide-style documentation, see Semantic knowledge bases.

Creating and managing a semantic KB

For concepts (creating a KB, resolving to a single KB, and auto-processing) see Getting started.

aidb.create_semantic_kb

Creates a semantic KB over one or more schemas, embeds the schema metadata with the given model, and crawls the schemas immediately.

Parameters

Returns

The KB name (TEXT).

Example

SELECT aidb.create_semantic_kb(
    name    => 'analytics_kb',
    model   => 'my_embedding_model',
    schemas => ARRAY['sales'],
    auto_processing => 'Live'
);

aidb.list_semantic_kbs

Lists every semantic KB with its model, schemas, and processing mode.

Example

SELECT * FROM aidb.list_semantic_kbs();

aidb.refresh_semantic_kb

Re-crawls a KB's schemas from scratch to pick up structural changes.

Parameters

Example

SELECT aidb.refresh_semantic_kb('analytics_kb');

aidb.update_semantic_kb_auto_processing

Changes how a KB reacts to schema changes after creation.

Parameters

Example

SELECT aidb.update_semantic_kb_auto_processing('analytics_kb', 'Live');

aidb.semantic_kb_stats

Returns entity counts and pending-change status for a KB.

Parameters

Returns

Example

SELECT total, tables, columns, pending, relationships, drifted, queries
FROM aidb.semantic_kb_stats('bank_kb');

aidb.delete_semantic_kb

Deletes a KB and its indexed metadata.

Parameters

Example

SELECT aidb.delete_semantic_kb('analytics_kb');

Auto-processing modes

Searching a semantic KB

For concepts (searching everything at once, tuning results, finding the joins around a search) see Searching a semantic KB.

aidb.semantic_kb_search

The composite entry point: runs the per-source ranked searches (schema metadata, relationships, curated semantic aliases, and query history) and fuses them into one ranked list with Reciprocal Rank Fusion (RRF).

Parameters

Returns

Example

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
);

aidb.get_metadata

The broadest schema search: returns columns, tables, and views together with full metadata.

Parameters

Returns

schema_name, relation_name, column_name, entity_type, definition, comment, definition_vector, similarity.

Example

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
);

aidb.get_entity_definitions

Matches tables and views only.

Parameters

Returns

schema_name, relation_name, entity_type, definition, comment, similarity.

Example

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']
);

aidb.get_column_definitions

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

Parameters

Returns

A single definition column.

Example

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

aidb.search_by_comment

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

Parameters

Returns

schema_name, relation_name, column_name, entity_type, definition, comment, similarity.

Example

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

aidb.semantic_kb_subgraph

Returns a question's neighborhood: seeds from the search hits and adds the relationships around them, so you get the joins connected to a question, not just the entities. Only traverses relationships that are approved and routable — see The routability rule.

Parameters

Returns

Example

SELECT hop, from_object, to_object, via_object, kind, confidence, seeded_by
FROM aidb.semantic_kb_subgraph(
    kb_name    => 'analytics_kb',
    query_text => 'money moved out of an account',
    top_k      => 3,
    max_hops   => 1
);

Managing relationships

For concepts (kind, source, status, confidence, the routability rule) see Relationships.

aidb.list_relationships

Lists recorded relationships, optionally filtered.

Parameters

Returns

Example

SELECT left_object, left_columns, right_object, right_columns, kind, cardinality, is_nullable
FROM aidb.list_relationships('bank_kb', relation => 'bank.customers')
ORDER BY left_object, left_columns;

aidb.find_join_path

Finds a route between two tables, ranked shortest first, then by confidence.

Parameters

Returns

Example

SELECT path_rank, route, kind, confidence
FROM aidb.find_join_path('bank_kb', 'bank.transactions', 'bank.customers');

aidb.suggest_joins

Lists every relationship one hop from a given set of tables, in either direction.

Parameters

Returns

Example

SELECT from_object, to_object, kind, confidence
FROM aidb.suggest_joins('bank_kb', ARRAY['bank.accounts'])
ORDER BY from_object, to_object;

aidb.add_relationship

Records a relationship by hand. SQL-only — not registered as a native agent tool. See Adding a relationship by hand.

Parameters

Returns

Example

SELECT relationship_id, left_object, left_columns, source, status, confidence, action
FROM aidb.add_relationship(
    kb_name       => 'bank_kb',
    left_object   => 'bank.payments',
    right_object  => 'bank.loan_instalments',
    kind          => 'key_join',
    predicate     => 'settles',
    left_columns  => ARRAY['loan_id', 'instalment_no'],
    right_columns => ARRAY['loan_id', 'instalment_no'],
    cardinality   => 'many-to-one',
    is_nullable   => false,
    description   => 'A payment settles one instalment of a loan.');

aidb.add_relationship_as_agent

Records a relationship the same way as aidb.add_relationship(), but always sets source = 'agent'. This is the function backing the add_relationship native agent tool — despite the shared tool name, the tool calls add_relationship_as_agent(), not add_relationship(). See the naming note in Adding a relationship by hand.

Parameters

Takes only kb_name, left_object, right_object, kind, predicate, left_columns, right_columns, cardinality, is_nullable, and description — no junction fields, join_expr, inverse_predicate, curated_label, level, or source, so an agent can record only a key_join this way. The relationship is always stored with source = 'agent' and status = 'proposed', and isn't routed until a person approves it by re-asserting it with aidb.add_relationship().

aidb.delete_relationship

Deletes a relationship. Overloaded — takes either a relationship_id or the full identifying shape (endpoints, columns, kind) — so name your arguments rather than passing a single positional one. A crawled relationship can't be deleted while its backing constraint still stands, since the next crawl would restore it. Drop the constraint first.

Parameters

By ID

By shape

Returns

Both overloads return the same shape.

Example

SELECT * FROM aidb.delete_relationship(kb_name => 'bank_kb', relationship_id => 10);

aidb.relationship_drift

Detects and repairs drift on hand-added relationships when a named column is renamed or dropped. This is a write, not a read — it re-runs detection and repair on every call, so it can't run inside a read-only transaction. The native agent tool for this function is flagged read-only in the tool catalog. That flag is inaccurate.

Parameters

Returns

Example

SELECT detected_at, relationship_id, left_object, right_object, source, event, detail
FROM aidb.relationship_drift('bank_kb');

aidb.import_query_history

Reads pg_stat_statements to find joins the application runs but the schema never declared. Requires pg_stat_statements to be loaded (shared_preload_libraries) and installed (CREATE EXTENSION pg_stat_statements;). Errors if the extension isn't installed, rather than returning zero rows. See Learning from the query workload.

Parameters

Returns

Example

SELECT import_id, statements_read, statements_ingested, skipped, candidates_derived, outcome
FROM aidb.import_query_history('bank_kb');

aidb.relationship_candidates

Lists relationship candidates derived from query history that aren't yet known relationships.

Parameters

Returns

Example

SELECT relationship_id, left_object, left_columns, right_object, right_columns,
       confidence, statements, executions, definition
FROM aidb.relationship_candidates('bank_kb');

aidb.accept_relationship_candidates

Accepts one or more relationship candidates, making them routable. source becomes manual. Provenance keeps the evidence.

Parameters

Returns

Example

SELECT * FROM aidb.accept_relationship_candidates(
  ARRAY[(SELECT relationship_id FROM aidb.relationship_candidates('bank_kb') LIMIT 1)]::bigint[],
  'bank_kb');

aidb.classify_query_history_statement

Explains why a single statement would or wouldn't be used by import_query_history() — the quickest way to understand an import that ingested less than expected.

Parameters

Returns

A JSONB object:

Example

SELECT jsonb_pretty(aidb.classify_query_history_statement(
  'SELECT 1 FROM bank.accounts a JOIN bank.customers c ON a.cust_id = c.id'));

{
    "qualifies": true,
    "relations": [
        "bank.accounts",
        "bank.customers"
    ],
    "predicates": [
        {
            "left_column": "cust_id",
            "right_column": "id",
            "left_relation": "bank.accounts",
            "right_relation": "bank.customers"
        }
    ],
    "skip_reason": null,
    "had_unresolved": false
}

A statement that doesn't qualify returns just enough to say why:

SELECT aidb.classify_query_history_statement('UPDATE bank.accounts SET opened_on = now()');

{"qualifies": false, "skip_reason": "unparseable"}

aidb.list_query_history

Lists the normalized statements, relations touched, and join predicates recorded by import_query_history().

Parameters

Returns

Example

SELECT normalized_sql, referenced_objects, join_predicates, exec_count, executed_by
FROM aidb.list_query_history('bank_kb') ORDER BY normalized_sql;

Managing descriptions and curation

For concepts (modes, protection, concurrency, review) see Descriptions and curation.

aidb.add_comment_to_object

Writes a COMMENT ON for a table, view, column, or constraint, and reconciles every KB that indexes the object so the new text is searchable immediately.

Parameters

Returns

Example

SELECT jsonb_pretty(aidb.add_comment_to_object(
  'bank', 'transactions', 'A movement of money against an account.', kb_name => 'bank_kb'));

aidb.remove_comment

Clears a description. Takes the same expected_comment and force protections as add_comment_to_object().

Parameters

Example

SELECT jsonb_pretty(aidb.remove_comment('bank', 'transactions', kb_name => 'bank_kb'));

aidb.list_proposed_comments

Lists comment writes queued for review under curation.

Parameters

Returns

object, object_type, proposed_comment, proposed_by.

Example

SELECT object, object_type, proposed_comment, proposed_by FROM aidb.list_proposed_comments('bank_kb');

aidb.resolve_object_comment

Approves or rejects a proposed comment. Deliberately SQL-only — not a native agent tool, so approving a proposal stays with a person.

Parameters

Example

SELECT jsonb_pretty(aidb.resolve_object_comment('bank_kb', 'bank', 'accounts', 'approve'));

aidb.update_semantic_kb_curation

Turns the comment-review queue on or off for a KB. Deliberately SQL-only — not a native agent tool, so toggling review stays with a person.

Parameters

Returns

current, previous (both booleans, as JSONB).

Example

SELECT aidb.update_semantic_kb_curation('bank_kb', true);

aidb.semantic_kb_audit

Lists recorded semantic KB operations. Only a superuser or the extension owner can call it.

Parameters

Returns

TABLE(ts TIMESTAMPTZ, kb_name TEXT, op TEXT, func TEXT, caller_kind TEXT, actor_name TEXT, login_name TEXT, object_ref TEXT, detail JSONB)

caller_kind is agent_hub, mcp, or direct. actor_name is the role the operation ran as; login_name is the session's login role. object_ref names the affected object. detail holds operation-specific data, for example mode, forced, and discarded for a description write.

Example

SELECT ts, op, func, actor_name, object_ref, detail
FROM aidb.semantic_kb_audit(kb_name => 'bank_kb', op => 'write')
ORDER BY ts DESC LIMIT 3;

aidb.semkb_audit_prune

Deletes the audit records of one knowledge base that are older than a given age. Nothing deletes records automatically; run this function as an operator or from a scheduled job such as pg_cron. The caller must be a member of the knowledge base's owner role.

Parameters

Returns

BIGINT: the number of records deleted.

Example

SELECT aidb.semkb_audit_prune('bank_kb', '90 days');

Creating and managing aliases

For concepts (multi-KB membership, finding, running) see Semantic aliases.

aidb.create_semantic_alias

Creates a named, parameterized SQL query paired with a natural-language description, and embeds the description in every semantic KB that owns the query's schema(s). Placeholders in the query use ${name} syntax, and the query must be a single read-only SELECT.

Parameters

Each entry in the params JSONB array:

If the query's schema maps to more than one KB, the alias is embedded once for each of them — creation never fails on ambiguity. Pass kb_name only to narrow it to a single KB. A schema not yet owned by any KB leaves the alias unembedded, until a KB covering it is created or adopts it later.

Returns

The alias name (TEXT).

Example

SELECT aidb.create_semantic_alias(
    name        => 'monthly_revenue',
    description => 'Total revenue for a given month and year',
    query_text  => $$
        SELECT SUM(amount) AS total
        FROM sales.orders
        WHERE EXTRACT(MONTH FROM order_date) = ${month}
          AND EXTRACT(YEAR  FROM order_date) = ${year}
    $$,
    params => '[
        {"name": "month", "param_type": "integer", "description": "Month number (1-12)"},
        {"name": "year",  "param_type": "integer", "description": "Four-digit year"}
    ]'
);

aidb.search_semantic_aliases

Searches aliases by natural-language query, embedding the query once per distinct model among the searched KBs so results stay dimension-safe and de-duplicated to the best match per alias. Without kb_name, it searches default_semkb if that KB exists, or every KB with stored alias embeddings otherwise. Aliases also appear in aidb.semantic_kb_search() results with source_type = 'alias'.

Parameters

Returns

name, query_text, similarity.

Example

SELECT name, query_text, similarity
FROM aidb.search_semantic_aliases(
    query_text => 'how much money did we make last month',
    kb_name    => 'analytics_kb',
    top_k      => 5
);

aidb.execute_semantic_alias

Runs an alias by name, substituting its parameters. Use execute_role to run it under a least-privilege reporting role rather than the connecting user. Because every alias is validated as a single read-only SELECT at creation and re-validated at execution, an alias can't be used to run writes.

Parameters

Returns

A set of result rows, each a JSONB object.

Example

SELECT result
FROM aidb.execute_semantic_alias(
    alias_name => 'monthly_revenue',
    args       => '{"month": 3, "year": 2025}'
);

aidb.get_semantic_aliases

Lists every alias, with its name, description, query text, and parameter count.

Returns

name, description, query_text, param_count.

Example

SELECT * FROM aidb.get_semantic_aliases();

aidb.get_semantic_alias

Gets one alias by name, with its full definition: description, query text, and parameter list.

Parameters

Returns

name, description, query_text, params.

Example

SELECT * FROM aidb.get_semantic_alias('monthly_revenue');

aidb.update_semantic_alias

Updates an alias. Changing query_text or kb_name re-infers its owning KB(s) and reconciles embeddings, adding KBs it newly belongs to and dropping ones it no longer does. Changing description re-embeds it in every KB it's already in.

Parameters

Same fields as aidb.create_semantic_alias() (name identifies the alias to update). Which fields are required versus optional on update isn't stated in the guide — confirm before publishing.

Example

SELECT aidb.update_semantic_alias(
    name        => 'monthly_revenue',
    description => 'Total revenue for a given month and year, in USD'
);

aidb.delete_semantic_alias

Deletes an alias by name.

Parameters

Example

SELECT aidb.delete_semantic_alias('monthly_revenue');