Example#

This example walks through a complete semantic knowledge base workflow over a small sales schema: documenting the schema, indexing it, searching it by meaning, and capturing a recurring question as a reusable alias.

Step 1: Register an embedding model#

A semantic KB embeds schema metadata with a text embedding model. Register one first:

SELECT aidb.create_model(sales_embeddings, bert_local);
__OUTPUT__
   create_model
- -------------------
 sales_embeddings
(1 row)

Step 2: Create and document the schema#

Comments are embedded alongside the structural metadata, so documenting the schema directly improves search quality.

CREATE SCHEMA sales;

CREATE TABLE sales.customers (
    id     INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email  TEXT NOT NULL,
    region TEXT
);

CREATE TABLE sales.orders (
    id          INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES sales.customers(id),
    amount      NUMERIC(12,2) NOT NULL,
    status      TEXT NOT NULL,
    order_date  DATE NOT NULL
);

COMMENT ON TABLE  sales.orders          IS One row per customer order, with its monetary amount and lifecycle status.;
COMMENT ON COLUMN sales.orders.amount   IS Order total in USD.;
COMMENT ON COLUMN sales.orders.status   IS Order lifecycle state: pending, confirmed, shipped, delivered, cancelled.;
COMMENT ON COLUMN sales.customers.email IS Customer contact email address.;

Step 3: Create the semantic KB#

Create the KB over the sales schema. model is required; Live auto-processing keeps the index current as the schema changes.

SELECT aidb.create_semantic_kb(
    name    => analytics_kb,
    model   => sales_embeddings,
    schemas => ARRAY[sales],
    auto_processing => Live
);
__OUTPUT__
 create_semantic_kb
- -------------------
 analytics_kb
(1 row)

Step 4: Check what was indexed#

aidb.semantic_kb_stats() reports entity counts and any pending schema changes:

SELECT * FROM aidb.semantic_kb_stats(kb_name => analytics_kb);
__OUTPUT__
 total | tables | views | columns | pending
- ------+--------+-------+---------+---------
    10 |      2 |     0 |       8 |       0
(1 row)

Step 5: Search by meaning#

Find the schema entities that map to a concept, even when names don’t match the words used. semantic_kb_search() returns schema entities and any matching aliases in one ranked list:

SELECT source_type, entity_type, schema_name, relation_name, column_name, rank
FROM aidb.semantic_kb_search(
    query_text => customer email address,
    kb_name    => analytics_kb,
    top_k      => 3
);
__OUTPUT__
 source_type | entity_type | schema_name | relation_name | column_name | rank
- ------------+-------------+-------------+---------------+-------------+------
 schema      | Column      | sales       | customers     | email       |    1
 schema      | Table       | sales       | customers     |             |    2
 schema      | Column      | sales       | orders        | customer_id |    3
(3 rows)

Comment search finds entities through the intent you documented, not just their names:

SELECT relation_name, column_name, comment
FROM aidb.search_by_comment(
    query_text => order status values,
    kb_name    => analytics_kb,
    top_k      => 1
);
__OUTPUT__
 relation_name | column_name |                             comment
- --------------+-------------+------------------------------------------------------------------
 orders        | status      | Order lifecycle state: pending, confirmed, shipped, delivered...
(1 row)

Step 6: Save a recurring question as an alias#

The question “how much revenue did we make in a given month?” recurs, so capture it as a Semantic aliases rather than regenerating SQL each time. AIDB infers that the alias is a member of analytics_kb , the KB that owns the sales schema the query reads:

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"}
    ]
);
__OUTPUT__
 create_semantic_alias
- ----------------------
 monthly_revenue
(1 row)

Step 7: Find and run the alias#

Search resolves a natural-language question to the alias:

SELECT name, similarity
FROM aidb.search_semantic_aliases(
    query_text => how much money did we make last month,
    kb_name    => analytics_kb,
    top_k      => 1
);
__OUTPUT__
      name       | similarity
- ----------------+------------
 monthly_revenue |      0.83
(1 row)

Run it with parameters:

SELECT result
FROM aidb.execute_semantic_alias(
    alias_name => monthly_revenue,
    args       => {"month": 3, "year": 2025}
);
__OUTPUT__
        result
- ----------------------
 {"total": 48250.00}
(1 row)

Using it from an agent#

Everything above is also available to an Agents reference . Give an agent the semantic KB search tools and run_sql_query , and it can answer open-ended questions itself: it calls semantic_kb_search to discover the relevant tables and columns, generates SQL grounded in those definitions, and runs it — the Example: a text-to-SQL agent workflow.

What you built#

A semantic KB (analytics_kb ) indexing a documented sales schema, kept current in Live mode.
Natural-language schema search that finds relations and columns by meaning.
A reusable, parameterized alias for a recurring question, findable by meaning and runnable with parameters.

Example

Contents