Query history v7

A semantic knowledge base (KB) describes a database's tables and columns, and the relationships its foreign keys declare between them. Most real databases also join tables without a connecting foreign key. The SQL running against the database records those joins on every execution.

Query history reads the recorded SQL from pg_stat_statements and keeps the statements joining tables in the KB's schemas. Use the following functions for each task:

Run aidb.import_query_history() yourself or from a scheduled job, because nothing is learned automatically. You decide which proposed joins to accept. Query history never changes how data is stored or queried.

Work through the following sections in order for a typical workflow:

1. Setting up query history: Enable pg_stat_statements, prepare your data, and create a model and a KB.
2. Importing history: Read the recorded SQL into the history store, and see Checking why statements are skipped if any statements are missing.
3. Excluding an application's SQL: Keep the SQL of text-to-SQL applications out of the store.
4. Browsing and searching history: List what was learned, and search past queries in English.
5. Working with join candidates: Review the proposed joins, and accept the ones you trust.

Create the shop schema and roles in Preparing your data to follow the examples. For every function's parameters and return columns, see the reference.

Concepts

Learn the following terms before you set up query history:

History store: One table per KB, aidb_internal.query_history_<kb_name>, with one row per distinct statement. Each row holds the normalized text, the tables read, the join conditions, the run count, the first and last time seen, the role that ran it, and an embedding for search.

Import: One run of aidb.import_query_history(). It reads pg_stat_statements for the current database, keeps qualifying statements, embeds the new ones, and proposes join candidates. You can repeat an import at any time, and each run refreshes what's already stored.

Qualifying statement: A single SELECT (a WITH query included) sent by a client, run at least min_calls times (default 5), and reading at least two tables, all in the KB's schemas.

Join predicate: A column equality between two joined tables, such as o.customer_id = c.id.

Join candidate: A join proposed by history as a relationship, with source = 'history' and status = 'proposed'. It records how many stored statements use the join and how often they ran. The join-route functions ignore a candidate until you accept it.

Excluded role: A role listed on the KB so that its statements, and its members' statements, aren't learned from. Use it for text-to-SQL applications. See Excluding an application's SQL.

Setting up query history

Query history requires pg_stat_statements. It's the only source of query history, and no other function fills the store. Set it up in three stages: enable pg_stat_statements, prepare your data, and create a model and a KB.

Enabling pg_stat_statements

Enable pg_stat_statements in three steps:

1. Load the library on the server. Add pg_stat_statements to the shared_preload_libraries parameter in postgresql.conf. See Configuring shared_preload_libraries.

   → WrapCopy

   shared_preload_libraries = 'aidb, pg_stat_statements'

   Note

   If shared_preload_libraries already lists other extensions, append pg_stat_statements using a comma separator. The order doesn't matter.

2. Restart Postgres. shared_preload_libraries is a cluster-wide setting, and a change takes effect only after a restart. A restart interrupts all connections to the server, so schedule it accordingly.

   After the restart, check that the library is loaded:

   → WrapCopy

   SELECT current_setting('shared_preload_libraries') AS preloaded;

   Output

               preloaded
   ----------------------------------
    aidb, pgvector, pg_stat_statements
   (1 row)

   The output lists the libraries the server loads, and pg_stat_statements must be among them.

3. Create the extension in the database:

   → WrapCopy

   CREATE EXTENSION pg_stat_statements;

   Then check that the extension is installed:

   → WrapCopy

   SELECT extname, extversion FROM pg_extension WHERE extname IN ('aidb', 'pg_stat_statements') ORDER BY 1;

   Output

         extname       | extversion
   --------------------+------------
    aidb               | 7.9.0
    pg_stat_statements | 1.12
   (2 rows)

   The output shows each installed extension with its version, and both aidb and pg_stat_statements must appear.

   In a database without the extension, the import stops with an error instead of returning nothing:

   → WrapCopy

   SELECT * FROM aidb.import_query_history('shop_kb');

   Output

   ERROR:  pg_stat_statements is not installed in this database. Query history reads from it and has no other source. Install it with CREATE EXTENSION pg_stat_statements; note that it also requires shared_preload_libraries, which needs a restart.

Preparing your data

Choose the schemas the KB will learn from. The data should already exist in your database, because query history reads statements over tables in those schemas. To follow the examples, create the following schema and roles. The shop tables have no foreign keys, and crm is a second schema used to show what the import keeps out:

CREATE SCHEMA shop;
CREATE TABLE shop.customers(id int PRIMARY KEY, region text);
CREATE TABLE shop.orders(id int PRIMARY KEY, customer_id int NOT NULL, placed_on date);
CREATE TABLE shop.returns(id int PRIMARY KEY, order_id int);
CREATE TABLE shop.instalments(order_id int, no int, due date, PRIMARY KEY (order_id, no));
CREATE TABLE shop.payments(id int PRIMARY KEY, order_id int NOT NULL, no int NOT NULL);
CREATE TABLE shop."Vouchers"(id int PRIMARY KEY, order_id int NOT NULL);
CREATE SCHEMA crm;
CREATE TABLE crm.notes(id int PRIMARY KEY, customer_id int);

CREATE ROLE text2sql_app LOGIN;                          -- a text-to-SQL application
CREATE ROLE text2sql_worker LOGIN IN ROLE text2sql_app;  -- a member of it
CREATE ROLE analyst LOGIN;                               -- a person
CREATE ROLE dba_admin SUPERUSER;                         -- an unlisted superuser
GRANT USAGE ON SCHEMA shop, crm TO text2sql_app, analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA shop, crm TO text2sql_app, analyst;

Creating a model and a KB

Create an embedding model and a KB over those schemas. Give the KB a vector index so the history store gets one too (see Keeping the store clean), and use auto_processing => 'Live' so the store stays clean as tables change. The example creates the KB over shop only:

SELECT aidb.create_model('bge_small', 'llamacpp_embeddings',
  '{"local_path": "/path/to/bge-small-en-v1.5-f16.gguf", "n_ctx": 512}'::jsonb);

SELECT aidb.create_semantic_kb('shop_kb', 'bge_small', ARRAY['shop'],
  auto_processing => 'Live', vector_index => '{"type": "hnsw"}');

 create_semantic_kb
--------------------
 shop_kb
(1 row)

Importing history

Import your database's query history by running aidb.import_query_history(). The function reads pg_stat_statements for the current database, stores the statements that qualify, and proposes join candidates. These tasks don't run automatically, so run the function after the workload changes, or from a scheduled job.

Run the import as a role that can read every role's statements: a superuser or a member of pg_read_all_stats. Otherwise, pg_stat_statements hides other roles' statements and the import skips most of the workload as redacted. See Controlling access and auditing.

All arguments are optional:

Re-importing is cheap. A statement that's already stored isn't embedded again. Its run count and last-seen time are refreshed. If the KB's embedding model changes, statements stored under the old model are embedded again on the next import.

To import history in your own environment:

1. Let the workload run. The import reads the statistics pg_stat_statements has collected, and ignores statements that ran fewer than min_calls times.

2. Run the import:

   → WrapCopy

   SELECT * FROM aidb.import_query_history('<kb_name>');

Example

To reproduce the example on a test database, import its workload in three steps:

1. Reset pg_stat_statements so the counts match the example. Run this step only on the example database, because a reset discards all the statistics collected so far.

   → WrapCopy

   SELECT pg_stat_statements_reset() IS NOT NULL AS reset;

   Output

    reset
   -------
    t

2. Run the workload. The import ignores statements that ran fewer than min_calls times (default 5), so run the following script five times. For example, save it as workload.sql and run for i in 1 2 3 4 5; do psql -d <database> -f workload.sql; done. To run it once, pass min_calls => 1 to the import in the next step, which changes the counts in the outputs that follow. The workload has no foreign keys, so history is the only source of its joins.

   → WrapCopy

   -- workload.sql: run as the KB's owner, or as a superuser
   SELECT c.region FROM shop.customers c JOIN shop.orders o ON o.customer_id = c.id;
   SELECT count(*) FROM shop.customers c JOIN shop.orders o ON o.customer_id = c.id;
   SELECT p.id FROM shop.payments p JOIN shop.instalments i ON i.order_id = p.order_id AND i.no = p.no;
   SELECT r.id FROM shop.returns r JOIN shop.orders o ON o.id = r.order_id;
   SELECT v.id FROM shop."Vouchers" v JOIN shop.orders o ON o.id = v.order_id;
   SELECT n.id FROM shop.customers c JOIN crm.notes n ON n.customer_id = c.id;
   SELECT 1 FROM shop.orders WHERE id = 1;
   \d shop.orders
   
   SET ROLE text2sql_app;
   SELECT o.placed_on FROM shop.orders o JOIN shop.customers c ON c.id = o.customer_id;
   RESET ROLE;
   
   SET ROLE text2sql_worker;
   SELECT o.id FROM shop.orders o JOIN shop.customers c ON c.id = o.customer_id;
   RESET ROLE;
   
   SET ROLE analyst;
   SELECT c.id FROM shop.orders o JOIN shop.customers c ON c.id = o.customer_id;
   RESET ROLE;
   
   SET ROLE dba_admin;
   SELECT o.customer_id FROM shop.orders o JOIN shop.customers c ON c.id = o.customer_id;
   RESET ROLE;

3. Run the import. The workload includes the application role text2sql_app and its member text2sql_worker, before either is excluded. The first import keeps everything that qualifies:

   → WrapCopy

   SELECT statements_read, statements_ingested, statements_embedded, rows_pruned,
          candidates_derived, skipped, outcome
   FROM aidb.import_query_history('shop_kb');

   Output

    statements_read | statements_ingested | statements_embedded | rows_pruned | candidates_derived |                 skipped                  | outcome
   -----------------+---------------------+---------------------+-------------+--------------------+------------------------------------------+---------
                 22 |                   9 |                   9 |           0 |                  4 | {"out_of_scope": 9, "not_qualifying": 4} | ok
   (1 row)

The import read 22 statements, kept 9, and skipped the other 13. The result columns, in the order of the output:

The function also returns import_id, an ID for the run. The audit log records it. See Controlling access and auditing.

The outcome column, last in the output, is one of the following:

Checking why statements are skipped

Examine the skipped column returned by aidb.import_query_history() to find out why the import ignored a statement. A skipped statement is one the import read from pg_stat_statements but didn't store, and each one is counted under a single skip reason. The import stores only SQL sent directly by a user or client, and only when it joins tables in the KB's schemas. In the example output, 9 statements were out_of_scope and 4 were not_qualifying:

The following sections explain the rules behind the skip reasons above: which SQL the import never reads, what makes a statement out_of_scope, and which column equalities count as joins. For excluded_role, see Excluding an application's SQL.

Leaving out SQL that AIDB runs itself

Don't expect AIDB's own SQL in the store. Statements run inside a function are never imported, including everything AIDB runs on a user's behalf: semantic aliases, Tools Hub tools, Agent Hub steps, and the Model Context Protocol (MCP) server. pg_stat_statements marks each statement as sent by a client or run inside a function, and the import reads only client-sent statements. No configuration is needed.

Keeping statements inside the KB's schemas

Keep the statements you want learned inside the KB's schemas. The import keeps a statement only if every table it reads is in one of them, and skips everything else as out_of_scope: psql's \d queries over pg_catalog, other KBs' traffic, and joins reaching another schema.

aidb.classify_query_history_statement(statement, kb_name) applies the same rule as the import, so you can test any statement before importing:

SELECT aidb.classify_query_history_statement(
  'SELECT n.id FROM shop.customers c JOIN crm.notes n ON n.customer_id = c.id', 'shop_kb')->>'skip_reason' AS other_schema;

 other_schema
--------------
 out_of_scope
(1 row)

SELECT aidb.classify_query_history_statement(
  'SELECT c.relname FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace', 'shop_kb')->>'skip_reason' AS catalog;

   catalog
--------------
 out_of_scope
(1 row)

The rule covers three further cases:

• Unqualified names. pg_stat_statements doesn't record the search_path a statement ran under. A name with no schema, such as FROM customers, counts only if exactly one schema in the database has a table of that name, and that schema is covered.
• Temporary tables. A session finds its temporary tables first, so a temporary table with the same name makes an unqualified name ambiguous, and the statement is skipped.
• Every table must be read. A FROM clause the import can't fully read is skipped as unparseable, instead of being kept with a table missing, because the missing table could be one outside the KB.

SELECT aidb.classify_query_history_statement(
  'SELECT 1 FROM customers c JOIN orders o ON o.customer_id = c.id', 'shop_kb')->'relations' AS unqualified;

            unqualified
-----------------------------------
 ["shop.customers", "shop.orders"]
(1 row)

CREATE TEMP TABLE customers(id int);
SELECT aidb.classify_query_history_statement(
  'SELECT 1 FROM customers c JOIN shop.orders o ON o.customer_id = c.id', 'shop_kb')->>'skip_reason' AS with_temp_table;

 with_temp_table
-----------------
 out_of_scope
(1 row)

Writing joins as column equalities

Write joins as column equalities among the ANDed conditions of an ON or WHERE clause. The import records a column equality as a join only in that form. The following aren't joins:

• a.x = b.y OR a.z = b.w
• NOT a.x = b.y
• An equality inside CASE or a function call
• An equality in the select list or HAVING
• a.x = b.y + 1

Two parts of one statement that reuse the same aliases, such as the branches of a UNION, are kept apart.

SELECT aidb.classify_query_history_statement(
  'SELECT 1 FROM shop.orders o JOIN shop.customers c ON c.id = o.customer_id OR c.region = $1')->'predicates' AS disjunction;

 disjunction
-------------
 []
(1 row)

A join over several columns is read as one join:

SELECT jsonb_pretty(aidb.classify_query_history_statement(
  'SELECT 1 FROM shop.payments p JOIN shop.instalments i ON i.order_id = p.order_id AND i.no = p.no', 'shop_kb'));

                   jsonb_pretty
--------------------------------------------------
 {                                               +
     "qualifies": true,                          +
     "relations": [                              +
         "shop.instalments",                     +
         "shop.payments"                         +
     ],                                          +
     "predicates": [                             +
         {                                       +
             "left_column": "no",                +
             "right_column": "no",               +
             "left_relation": "shop.instalments",+
             "right_relation": "shop.payments"   +
         },                                      +
         {                                       +
             "left_column": "order_id",          +
             "right_column": "order_id",         +
             "left_relation": "shop.instalments",+
             "right_relation": "shop.payments"   +
         }                                       +
     ],                                          +
     "skip_reason": null,                        +
     "had_unresolved": false                     +
 }
(1 row)

Statements that read one table, and statements other than SELECT, are never kept:

SELECT aidb.classify_query_history_statement('SELECT 1 FROM shop.orders WHERE id = $1')->>'skip_reason' AS one_table;

   one_table
----------------
 not_qualifying
(1 row)

SELECT aidb.classify_query_history_statement('UPDATE shop.orders SET placed_on = now()')->>'skip_reason' AS not_a_select;

 not_a_select
--------------
 unparseable
(1 row)

Excluding an application's SQL

Exclude the role of each text-to-SQL application, per KB. An application running its own generated SQL would otherwise teach the KB from its own output. A wrong join it produced once would be stored and returned in search results as a past query, and every repeat would strengthen it.

After the first import, the application's statements are in the store:

SELECT executed_by, count(*) AS statements
FROM aidb.list_query_history('shop_kb') GROUP BY 1 ORDER BY 1;

   executed_by   | statements
-----------------+------------
 analyst         |          1
 dba_admin       |          1
 kb_owner        |          5
 text2sql_app    |          1
 text2sql_worker |          1
(5 rows)

Exclude the application's role with aidb.update_semantic_kb_history_filter():

SELECT jsonb_pretty(aidb.update_semantic_kb_history_filter('shop_kb', ARRAY['text2sql_app']));

        jsonb_pretty
-----------------------------
 {                          +
     "purged": 2,           +
     "current": [           +
         "text2sql_app"     +
     ],                     +
     "previous": [          +
     ],                     +
     "candidates_retired": 0+
 }
(1 row)

Setting the list deletes the statements those roles already contributed, and reports how many under purged. Here that's one statement from text2sql_app, and one from text2sql_worker, which is a member of it:

SELECT executed_by, count(*) AS statements
FROM aidb.list_query_history('shop_kb') GROUP BY 1 ORDER BY 1;

  executed_by   | statements
----------------+------------
 analyst        |          1
 dba_admin      |          1
 kb_owner       |          5
(3 rows)

From then on, each import skips those roles' statements and counts them as excluded_role:

SELECT statements_read, statements_ingested, statements_embedded, rows_pruned,
       candidates_derived, outcome, jsonb_pretty(skipped) AS skipped
FROM aidb.import_query_history('shop_kb');

 statements_read | statements_ingested | statements_embedded | rows_pruned | candidates_derived | outcome |         skipped
-----------------+---------------------+---------------------+-------------+--------------------+---------+-------------------------
              22 |                   7 |                   0 |           0 |                  0 | ok      | {                      +
                 |                     |                     |             |                    |         |     "out_of_scope": 9, +
                 |                     |                     |             |                    |         |     "excluded_role": 2,+
                 |                     |                     |             |                    |         |     "not_qualifying": 4+
                 |                     |                     |             |                    |         | }
(1 row)

• excluded_role: 2 is the two application roles.
• out_of_scope: 9 is the join into crm, plus the eight catalog queries psql's \d sends. The number of \d queries depends on the psql version.
• not_qualifying: 4 is statements that read fewer than two tables, such as SELECT 1 FROM shop.orders WHERE id = 1.
• statements_embedded: 0 because all seven statements kept were already stored.

Checking which roles are excluded

Check which roles the exclusion covers:

• The listed roles, and every role that's a member of one through GRANT.
• On PostgreSQL 16 and later, a membership counts only if it lets the member act as the role (INHERIT or SET). A CREATEROLE user gets an ADMIN-only membership in every role it creates, and that membership doesn't exclude it.
• A superuser is excluded only if listed. In the example, dba_admin, an unlisted superuser, is still learned from.
• pg_stat_statements records the role a statement ran as. An application that runs SET ROLE must have the role it switches to listed.

The KB keeps each role by name together with its object identifier (OID). A renamed role stays on the list. After a dump and restore, which gives roles new OIDs, the name is what matches. aidb.refresh_semantic_kb() records each listed role's current name and OID.

Checking what happens to candidates

Expect candidates to follow their statements. A proposed candidate supported only by the purged statements is removed with them and counted under candidates_retired. It's deleted, or retired if someone wrote a description or label on it. An already accepted candidate stays accepted, and the statements and executions counts in its provenance follow the remaining statements, down to zero.

Removals and count reductions run only for a caller who can read every role's history: a superuser or a member of pg_read_all_stats. For anyone else, an import can raise counts but never lower them, because they see only part of the evidence.

Clearing the list

Pass an empty array to clear the list. NULL is refused, so you can't clear it by accident:

SELECT jsonb_pretty(aidb.update_semantic_kb_history_filter('shop_kb', ARRAY[]::text[]));

        jsonb_pretty
-----------------------------
 {                          +
     "purged": 0,           +
     "current": [           +
     ],                     +
     "previous": [          +
         "text2sql_app"     +
     ],                     +
     "candidates_retired": 0+
 }
(1 row)

The examples that follow assume the exclusion is still active. Set the list again before you continue:

SELECT jsonb_pretty(aidb.update_semantic_kb_history_filter('shop_kb', ARRAY['text2sql_app']));

        jsonb_pretty
-----------------------------
 {                          +
     "purged": 0,           +
     "current": [           +
         "text2sql_app"     +
     ],                     +
     "previous": [          +
     ],                     +
     "candidates_retired": 0+
 }
(1 row)

Assigning a role to each application

Give each text-to-SQL application its own login role, not one shared with human users, and list the role on every KB it queries. Exclusion can't split a login used by both an application and human users.

Don't list aidb_users. Every AIDB user holds it, so listing it excludes everyone's statements. The function warns but doesn't refuse:

SELECT jsonb_pretty(aidb.update_semantic_kb_history_filter('shop_kb', ARRAY['aidb_users']));

WARNING:  aidb_users holds every aidb user's grants, so excluding it excludes every aidb user's statements from query history

update_semantic_kb_history_filter isn't available as a Tools Hub tool, so an agent can't change the list that governs what it learns from. Each change is written to the audit log.

Browsing and searching history

Browse the stored statements, or search them in English to find a past query that answers a question.

Browsing history

Run aidb.list_query_history() to list the statements the import stored for a KB: the normalized SQL, the tables each statement reads, how often it ran, and the role that ran it. Results come back most-run first. Pass kb_name when the database has more than one KB. The other parameters are optional and narrow the results:

Callers see only their own statements unless they hold pg_read_all_stats. See Controlling access and auditing.

normalized_sql is the text as pg_stat_statements normalized it: constants become $1, $2, and runs of whitespace become one space.

Example: list the statements that read shop.payments:

SELECT normalized_sql, referenced_objects, exec_count, executed_by
FROM aidb.list_query_history('shop_kb', relation => 'shop.payments');

                                           normalized_sql                                            |        referenced_objects        | exec_count | executed_by
-----------------------------------------------------------------------------------------------------+----------------------------------+------------+-------------
 SELECT p.id FROM shop.payments p JOIN shop.instalments i ON i.order_id = p.order_id AND i.no = p.no | {shop.instalments,shop.payments} |          5 | kb_owner
(1 row)

Example: show the join conditions a statement uses. Each row carries them in join_predicates:

SELECT normalized_sql, referenced_objects, join_predicates, exec_count, first_seen IS NOT NULL AS has_first_seen, executed_by
FROM aidb.list_query_history('shop_kb', relation => 'shop.returns');

-[ RECORD 1 ]------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
normalized_sql     | SELECT r.id FROM shop.returns r JOIN shop.orders o ON o.id = r.order_id
referenced_objects | {shop.orders,shop.returns}
join_predicates    | [{"l": {"name": "orders", "alias": "o", "column": "id", "schema": "shop", "relation": "shop.orders"}, "r": {"name": "returns", "alias": "r", "column": "order_id", "schema": "shop", "relation": "shop.returns"}, "block": 0}]
exec_count         | 5
has_first_seen     | t
executed_by        | kb_owner

Example: list the tables the stored statements read. The store names each table as SQL would, quoting where needed: shop.orders, or shop."Vouchers" for a mixed-case name:

SELECT DISTINCT o AS object FROM aidb.list_query_history('shop_kb'), unnest(referenced_objects) o ORDER BY 1;

      object
------------------
 shop."Vouchers"
 shop.customers
 shop.instalments
 shop.orders
 shop.payments
 shop.returns
(6 rows)

The relation parameter of list_query_history() accepts the stored form, the unquoted form, or a name as SQL reads it, where SHOP.ORDERS is shop.orders:

SELECT (SELECT count(*) FROM aidb.list_query_history('shop_kb', relation => 'shop."Vouchers"')) AS stored_form,
       (SELECT count(*) FROM aidb.list_query_history('shop_kb', relation => 'shop.Vouchers'))   AS unquoted_form,
       (SELECT count(*) FROM aidb.list_query_history('shop_kb', relation => 'SHOP.ORDERS'))     AS sql_form;

 stored_form | unquoted_form | sql_form
-------------+---------------+----------
           1 |             1 |        6
(1 row)

Searching history

Run aidb.semantic_kb_search() to find past queries that answer a question in English. The function searches the stored statements by meaning and returns each one as a result of type Query, so you or an agent can start from SQL that already ran. Each stored statement is its own result, even when several statements read the same tables. Pass the question and the KB name, and use these parameters to narrow the search:

Example: find past queries about the regions customers order from:

SELECT rank, entity_type, object_ref, left(definition, 72) AS statement
FROM aidb.semantic_kb_search('which regions do customers order from', 'shop_kb', sources => ARRAY['history'], top_k => 5);

 rank | entity_type |         object_ref          |                                statement
------+-------------+-----------------------------+--------------------------------------------------------------------------
    1 | Query       | shop.customers, shop.orders | SELECT c.region FROM shop.customers c JOIN shop.orders o ON o.customer_i
    2 | Query       | shop.customers, shop.orders | SELECT c.id FROM shop.orders o JOIN shop.customers c ON c.id = o.custome
    3 | Query       | shop.customers, shop.orders | SELECT o.customer_id FROM shop.orders o JOIN shop.customers c ON c.id =
    4 | Query       | shop.orders, shop.returns   | SELECT r.id FROM shop.returns r JOIN shop.orders o ON o.id = r.order_id
    5 | Query       | shop.customers, shop.orders | SELECT count(*) FROM shop.customers c JOIN shop.orders o ON o.customer_i
(5 rows)

For the other sources and how results are fused, see Searching a semantic KB.

Working with join candidates

Review the joins that history proposes, and accept the ones you trust.

Reviewing candidates

Run aidb.relationship_candidates() to review every join in the stored statements not already known to the KB. Each import proposes them as candidates. The example workload's four joins have no foreign keys, so all four are proposed:

SELECT left_object, left_columns, right_object, right_columns, confidence, statements, executions
FROM aidb.relationship_candidates('shop_kb') ORDER BY left_object, right_object;

  left_object  | left_columns  |   right_object   | right_columns | confidence | statements | executions
---------------+---------------+------------------+---------------+------------+------------+------------
 shop.Vouchers | {order_id}    | shop.orders      | {id}          |       0.75 |          1 |          5
 shop.orders   | {customer_id} | shop.customers   | {id}          |       0.75 |          4 |         20
 shop.payments | {no,order_id} | shop.instalments | {no,order_id} |       0.75 |          1 |          5
 shop.returns  | {order_id}    | shop.orders      | {id}          |       0.75 |          1 |          5
(4 rows)

• There's one candidate per join, with its evidence. orders → customers is backed by the four stored statements that use it. The excluded application roles' statements aren't counted.
• A join over several columns is one candidate. payments → instalments joins on both order_id and no.
• The same table joined twice under two aliases gives two candidates, one per join.

The candidate's shape comes from the database's own definitions:

SELECT left_object, right_object, cardinality, is_nullable, status, join_expr
FROM aidb.list_relationships('shop_kb', source => 'history') ORDER BY left_object, right_object;

  left_object  |   right_object   | cardinality | is_nullable |  status  |                                           join_expr
---------------+------------------+-------------+-------------+----------+-----------------------------------------------------------------------------------------------
 shop.Vouchers | shop.orders      | many-to-one | f           | proposed | shop."Vouchers".order_id = shop.orders.id
 shop.orders   | shop.customers   | many-to-one | f           | proposed | shop.orders.customer_id = shop.customers.id
 shop.payments | shop.instalments | many-to-one | f           | proposed | shop.payments.no = shop.instalments.no AND shop.payments.order_id = shop.instalments.order_id
 shop.returns  | shop.orders      | many-to-one | t           | proposed | shop.returns.order_id = shop.orders.id
(4 rows)

• Direction. The side whose columns a unique index covers is the "one" side, and it's placed on the right. If both sides are unique, the join is one-to-one. If neither is, it's many-to-many, and the order is alphabetical.
• Nullability. is_nullable is true when the "many" side's columns allow NULL. returns.order_id does.

The join-route functions don't use a candidate:

SELECT count(*) AS routes FROM aidb.find_join_path('shop_kb', 'shop.returns', 'shop.orders');

 routes
--------
      0
(1 row)

Accepting candidates

Accept candidates by passing their IDs to aidb.accept_relationship_candidates(), with kb_name second. Where you know better than the database's definitions, override the proposed shape with the cardinality and is_nullable arguments. Here shop.returns.order_id allows NULL, but every return has an order:

SELECT * FROM aidb.accept_relationship_candidates(
  ARRAY(SELECT relationship_id FROM aidb.relationship_candidates('shop_kb')
         WHERE left_object = 'shop.returns'), 'shop_kb',
  is_nullable => false);

 relationship_id | accepted |  reason
-----------------+----------+----------
               4 | t        | approved
(1 row)

Accepting makes the claim yours. source becomes manual, provenance keeps the evidence and records who approved it, and the join-route functions start using it:

SELECT left_object, right_object, source, status, cardinality, is_nullable, provenance
FROM aidb.list_relationships('shop_kb', relation => 'shop.returns');

-[ RECORD 1 ]+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
left_object  | shop.returns
right_object | shop.orders
source       | manual
status       | approved
cardinality  | many-to-one
is_nullable  | f
provenance   | {"model": "bge_small", "derived_at": "2026-10-15T11:08:55.749657+05:30", "executions": 5, "statements": 1, "approved_by": "kb_owner", "asserted_by": "manual:kb_owner", "history_refreshed_at": "2026-10-15T11:08:55.822742+05:30"}

SELECT path_rank, hops, route FROM aidb.find_join_path('shop_kb', 'shop.returns', 'shop.orders');

 path_rank | hops |            route
-----------+------+-----------------------------
         1 |    1 | shop.returns -> shop.orders
(1 row)

Where neither side has a unique index, the proposed direction is only alphabetical. cardinality => 'one-to-many' states that the left side is the "one" side. The relationship is turned round (endpoints, columns, and join expression) and stored as many-to-one.

An ID that isn't a candidate is reported per row, not raised:

SELECT * FROM aidb.accept_relationship_candidates(ARRAY[9999]::bigint[], 'shop_kb');

 relationship_id | accepted |                   reason
-----------------+----------+---------------------------------------------
            9999 | f        | no such relationship in this knowledge base
(1 row)

Acceptance is by explicit ID only. The function deliberately takes no filter, to prevent approving in bulk what nobody reviewed in detail.

Refreshing the evidence for accepted joins

Re-run the import to refresh the evidence on an accepted join. A join that already exists isn't proposed again, whether you accepted it earlier or the foreign-key crawl found it. Each import refreshes the statements and executions counts on the relationship instead. After three more runs of the returns join:

SELECT statements_ingested, statements_embedded, rows_pruned, candidates_derived, outcome
FROM aidb.import_query_history('shop_kb');

 statements_ingested | statements_embedded | rows_pruned | candidates_derived | outcome
---------------------+---------------------+-------------+--------------------+---------
                   7 |                   0 |           0 |                  0 | ok
(1 row)

SELECT count(*) AS returns_candidates FROM aidb.relationship_candidates('shop_kb', relation => 'shop.returns');

 returns_candidates
--------------------
                  0
(1 row)

SELECT provenance->'executions' AS executions, provenance->'statements' AS statements,
       provenance ? 'history_refreshed_at' AS refreshed
FROM aidb.list_relationships('shop_kb', relation => 'shop.returns');

 executions | statements | refreshed
------------+------------+-----------
 8          | 1          | t
(1 row)

Nothing history proposes is used until a person accepts it by ID. For how accepted relationships behave once approved, see Relationships.

Keeping the store clean

Remove stale statements with two mechanisms: schema-change handling, and the pruning each import does. A stored statement over a table no longer in the database is an example of a now-failing query. Rows are removed when their tables go, not when they get old, so there's no time-based retention. See Limitations.

Removing statements when the schema changes

Expect a schema change to remove statements when the KB's auto_processing is Live or Background:

• DROP TABLE removes every stored statement that reads the table.
• Renaming a table or schema removes the statements that name the old one.
• Dropping or renaming a column removes the statements whose join uses it.

SELECT count(*) AS rows_before FROM aidb.list_query_history('shop_kb');

 rows_before
-------------
           7
(1 row)

DROP TABLE shop.payments;
SELECT count(*) AS rows_after_drop FROM aidb.list_query_history('shop_kb');

 rows_after_drop
-----------------
               6
(1 row)

ALTER TABLE shop.returns RENAME TO refunds;
SELECT count(*) AS rows_after_rename FROM aidb.list_query_history('shop_kb');

 rows_after_rename
-------------------
                 5
(1 row)

Pruning at every import

Run an import to prune stale statements. Each import removes stored statements whose tables no longer exist in the KB's schemas, or whose join columns were dropped or renamed, and reports the count as rows_pruned. This covers KBs without schema-change handling (Disabled or Manual), changes made while no handler ran, and changes no handler covers, such as ALTER TABLE ... SET SCHEMA.

Candidates follow their statements. A proposed candidate whose supporting statements are all removed is removed too, as with excluded roles.

Keeping history search fast

Create the KB with a vector index so history search stays fast as the store grows. The history store gets an index too:

SELECT indexrelid::regclass AS index, pg_get_indexdef(indexrelid) LIKE '%USING hnsw%' AS hnsw
FROM pg_index WHERE indrelid = 'aidb_internal.query_history_shop_kb'::regclass ORDER BY 1;

                  index                   | hnsw
------------------------------------------+------
 aidb_internal.query_history_shop_kb_pkey | f
 aidb_internal.qh_objects_shop_kb         | f
 aidb_internal.vdx_qh_shop_kb             | t
(3 rows)

Controlling access and auditing

Control who can run the query history functions and see the stored statements, and review what they did.

Checking function permissions

Check the grants on the query history functions. Every query history function is granted EXECUTE to aidb_users:

SELECT p.proname, array_to_string(p.proacl, ', ') AS grants
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'aidb' AND p.proname IN ('import_query_history', 'list_query_history', 'relationship_candidates',
  'accept_relationship_candidates', 'classify_query_history_statement', 'update_semantic_kb_history_filter')
ORDER BY 1;

              proname              |                          grants
-----------------------------------+----------------------------------------------------------
 accept_relationship_candidates    | aidb_owner=X/aidb_owner, aidb_users=X/aidb_owner
 classify_query_history_statement  | aidb_owner=X/aidb_owner, aidb_users=X/aidb_owner
 import_query_history              | aidb_owner=X/aidb_owner, aidb_users=X/aidb_owner
 list_query_history                | aidb_owner=X/aidb_owner, aidb_users=X/aidb_owner
 relationship_candidates           | aidb_owner=X/aidb_owner, aidb_users=X/aidb_owner
 update_semantic_kb_history_filter | aidb_owner=X/aidb_owner, aidb_users=X/aidb_owner
(6 rows)

As with the rest of the semantic KB, the stores behind these functions carry no table-level grant, so in practice the KB's owner or a superuser is the caller. See Permissions.

Restricting what each caller sees

Check the row-level security on the history store, forced even for its owner. A caller reads, updates, and deletes only the statements that a role they're a member of ran, unless they hold pg_read_all_stats:

SELECT policyname, cmd, qual FROM pg_policies
WHERE schemaname = 'aidb_internal' AND tablename = 'query_history_shop_kb' ORDER BY policyname;

-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------
policyname | qh_deleter_shop_kb
cmd        | DELETE
qual       | (pg_has_role(CURRENT_USER, executed_by, 'MEMBER'::text) OR pg_has_role(CURRENT_USER, 'pg_read_all_stats'::name, 'MEMBER'::text))
-[ RECORD 2 ]--------------------------------------------------------------------------------------------------------------------------------
policyname | qh_reader_shop_kb
cmd        | SELECT
qual       | (pg_has_role(CURRENT_USER, executed_by, 'MEMBER'::text) OR pg_has_role(CURRENT_USER, 'pg_read_all_stats'::name, 'MEMBER'::text))
-[ RECORD 3 ]--------------------------------------------------------------------------------------------------------------------------------
policyname | qh_updater_shop_kb
cmd        | UPDATE
qual       | (pg_has_role(CURRENT_USER, executed_by, 'MEMBER'::text) OR pg_has_role(CURRENT_USER, 'pg_read_all_stats'::name, 'MEMBER'::text))
-[ RECORD 4 ]--------------------------------------------------------------------------------------------------------------------------------
policyname | qh_writer_shop_kb
cmd        | INSERT
qual       |

pg_stat_statements applies the same rule to its own text. Without pg_read_all_stats, other roles' statements come back hidden and are skipped as redacted. The role that runs scheduled imports should hold pg_read_all_stats, or be a superuser. Otherwise most of the workload is redacted.

Running query history from Tools Hub

Use the registered Tools Hub tools to let an agent run query history for the person it acts for: import_query_history, list_query_history, relationship_candidates, and accept_relationship_candidates. update_semantic_kb_history_filter and classify_query_history_statement aren't registered.

Reviewing the audit log

Query the audit log to review imports, acceptances, changes to the exclusion list, and updates to the evidence counts on existing relationships. Each is recorded in aidb_internal.semkb_audit_log:

SELECT fn, count(*) FROM aidb_internal.semkb_audit_log
WHERE kb_name = 'shop_kb' AND fn IN ('update_history_filter', 'import_query_history',
  'accept_relationship_candidate', 'refresh_relationship_evidence')
GROUP BY fn ORDER BY fn;

              fn               | count
-------------------------------+-------
 accept_relationship_candidate |     1
 import_query_history          |     3
 refresh_relationship_evidence |     4
 update_history_filter         |     2
(4 rows)

For reading the audit log with aidb.semantic_kb_audit(), see Auditing changes.

Common errors

Look up an error message to find its cause and fix.

There are no custom SQLSTATEs, so match errors by message text. Invalid parameters: ... marks an unusable value, and Missing value: ... marks an absent one.

Filters are matched, not validated. A misspelled relation returns zero rows, not an error.

Limitations

Plan around the following limitations:

• No time-based retention. Statements are removed when their tables or join columns go, or when their role is excluded, never for age.
• Self-joins aren't proposed. employees e JOIN employees m ON e.manager_id = m.id is read, but no candidate is proposed. The foreign-key crawl finds a self-referencing foreign key.
• Text normalization merges some statements. Whitespace runs are collapsed throughout the stored text, including inside -- comments and quoted names. A -- comment then appears to run to the end of the stored statement, and two quoted names that differ only in spacing are stored as one statement. The joins learned aren't affected.
• relationship_candidates(relation => ...) takes the unquoted form only, schema.table, unlike list_query_history(), which also takes the quoted and SQL forms.
• PostgreSQL 18 groups statements by name. From PostgreSQL 18, pg_stat_statements identifies a table by name rather than by OID. The same SQL over same-named tables in two schemas is one entry, recorded under the text of whichever schema ran it first. A KB over the other schema may therefore skip it as out_of_scope.
• Exclusion matches the role a statement ran as, not the session user. See Checking which roles are excluded.

Related documentation

See the following pages for related topics:

• Relationships, which covers the foreign-key crawl, join-path finding, and how accepted relationships are used.
• Searching a semantic KB, which covers semantic_kb_search() and how sources are fused.
• Semantic knowledge bases reference, the full API reference for every query history function.
• Native tools, which lists the Tools Hub tools for query history.