Example v7

Suggest edits

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 alias 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 AIDB agent. 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 text-to-SQL 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.
← Prev

Text-to-SQL

↑ Up

Semantic knowledge bases

Next →

SQL functions