DATABASE knowledge

<dependency>
    <groupId>dev.tnsai</groupId>
    <artifactId>tnsai-rag-jdbc</artifactId>
</dependency>

The version comes from tnsai-bom; see Installation.

Declare the source

@RoleSpec(knowledge = @Knowledge(
    name = "crm-accounts",
    type = Knowledge.Type.DATABASE,
    connection = "crm",
    query = "SELECT account_id AS id, notes AS content, account_name AS title FROM accounts"))
public final class AccountsRole extends Role {
    @Override
    public RoleIdentity getIdentity() {
        return new RoleIdentity("accounts", "Answer from CRM account notes", "CRM");
    }
}

An agent declares one the same way in @AgentSpec(knowledge = ...), and the agent's chat turns search it like any other SEARCH source.

Connect

The loader finds the connection in this order.

1. A bound DataSource. The application binds its pool under the source's connection name with JdbcConnections.bind. The binding lasts until it is closed:

try (JdbcConnections.Binding crm = JdbcConnections.bind("crm", dataSource)) {
    // sources whose connection is "crm" read dataSource
}

• The registry is process-wide. It is not tied to a @Config generation or to a run.
• The name is looked up on every load, so a source declared before its DataSource is bound reads it once it is.
• A name is bound once at a time. Binding it again while it is bound throws IllegalStateException; close the first binding first. A blank name, or one with surrounding whitespace, throws IllegalArgumentException.
• Closing a binding twice does nothing, and closing a stale binding never unbinds a later one under the same name.
• Each load borrows one connection from the DataSource and closes it.

2. A JDBC URL from a configuration file. When nothing is bound under the name, the loader connects with the jdbcUrl option. @Knowledge has no member for options: a class configuration file or a SCOP run file sets them, see Configuration files and the run file, and a source built in code takes them through KnowledgeSourceConfig.Builder.options(...):

{
  "knowledge": {
    "crm-accounts": {
      "options": {
        "jdbcUrl": "jdbc:postgresql://db.internal:5432/crm",
        "user": "reader",
        "passwordEnv": "CRM_DB_PASSWORD"
      }
    }
  }
}

The password comes only from the variable passwordEnv names, read as EnvLoader reads one: a system property of that name first, which a .env file can set, then the environment. A variable that is not set, or is empty, fails the source with CONFIGURATION.

A password option is refused, as is any key the loader does not accept. A URL that carries a password or token is refused too. The loader recognizes:

• a parameter or property whose key ends in password (with or without trailing digits), passwd, pwd, token, secret, apikey, api_key, secretaccesskey, accountkey or sharedaccesssignature, in any case, after ?, &, ;, :, ( or ,. So sslpassword=, accessToken= and MySQL's (host=db,password=…) are refused;
• user info such as //user:secret@host;
• Oracle's thin:user/secret@host, quoted or not.

Percent escapes are decoded before the check, so pass%77ord= is refused too. The check is a best effort, so keep secrets out of URLs.

A bound DataSource wins. When one is bound under the name, the jdbcUrl, user and passwordEnv options are ignored and the passwordEnv variable is not read. If any of the three is set, each load logs a warning that names the ignored keys, never their values.

With neither, the source fails with CONFIGURATION. When connection is set, the message names it; when it is empty, the message says to set it or the jdbcUrl option.

When a connection cannot be opened, through a bound DataSource or the URL, the failure keeps only the driver's exception class, SQLState and vendor code. The driver's message, which may name the host, port, database or user, is dropped, and the driver's exception is not kept as a cause. The java.sql kind of the failure, such as SQLTimeoutException, is kept. A knowledge entry's toString names its option keys, never their values.

Rows to documents

• Column names match ignoring case. Metadata keys are the labels the driver reports, so some drivers, H2 for example, give them in upper case. Alias a column to set its key.
• The query may not return two columns with the same label, case ignored.
• A column that would overwrite metadata TnsAI sets is refused: title when it is not the title column, and tnsai.source.name, hierarchySource, temporalSource, hierarchyParentId and temporalTimestamp. This check compares labels exactly, case included.
• One column cannot be both the id and the content, or the title and either of them.
• SELECT * turns every other column into metadata, personal data included, and metadata travels with the retrieved text. Name the columns the documents need.

The loader does not split rows: each row is one document, so a source's document count equals the number of rows its query returned.

A bad row fails the source; none is skipped. A NULL or blank id, a NULL content, or an id an earlier row already had fails the source with CONFIGURATION. The message names the row by its number, never by its values, for example row 2 repeats the id of row 1. The number counts rows in the order the query returns them, so add ORDER BY to the query to locate one. An empty or blank content is accepted.

Safety

A database user that can only read is the real control. The loader's checks catch mistakes, not misuse. A SELECT can still call a function that writes, and read-only is a hint some drivers ignore.

• The query must start with SELECT or WITH, in any case. One trailing ; is dropped; any other ; is refused. The query takes no placeholders, so one containing ${ is refused. With updatedAtColumn set, the query runs as a prepared statement, so a ? in it is a parameter marker. A query that uses ? as an operator, such as PostgreSQL's jsonb ?, then fails at the first load. Write the driver's escape (?? on PostgreSQL) or a function such as jsonb_exists instead.
• The connection is set read-only for the read. A driver that refuses setReadOnly(true) fails the source with SOURCE_LOADING. After the read, the transaction is rolled back and auto-commit and read-only are set back as they were. Each of these steps runs even when another fails.
• Each statement gets the timeout option, 30 seconds by default. A statement that runs past it fails with SOURCE_LOADING, and the message gives the timeout. The timeout covers running the statement only. Connecting and fetching rows rely on the driver's own settings, such as its login and socket timeouts.
• A statement that returns more than maxRows rows, 10000 by default, fails with CONFIGURATION. The corpus is never truncated: narrow the query or raise maxRows.
• timeout and maxRows apply to each statement. A read with updatedAtColumn runs two statements; see Incremental reads.

Refresh and sync

A DATABASE source is read again in the same ways as a FILE source; see Refresh a corpus.

• refresh. Set a refresh interval to have the query run again once that interval has passed. The rows are compared with the last load by id and content, metadata included, in whatever order they come. A new or changed row is embedded again, a row the query no longer returns leaves the index, and an unchanged row is kept as it is. A row whose only change is in a metadata column counts as changed.
• RoleRagBinding.sync. It reads every DATABASE source of the Role again in full, whether or not the source sets refresh, and lists it in changed(), unchanged() or failed().

A source that cannot be read keeps its previous content and is listed as failed, as a FILE source is.

{
  "knowledge": {
    "crm-accounts": {
      "refresh": "10m",
      "options": {
        "updatedAtColumn": "updated_at"
      }
    }
  }
}

Incremental reads

Without updatedAtColumn, every refresh runs the whole query. With it, a refresh due by its interval reads only the rows whose updatedAtColumn value is at or after (>=) the latest value the last read saw. It also reads the id of every row, so a deleted row still leaves the index. Each load and each sync reads every row.

Both extra reads wrap the query as a derived table:

SELECT q.<idColumn>, q.<updatedAtColumn> FROM (<query>) q
SELECT * FROM (<query>) q WHERE q.<updatedAtColumn> >= ?

• A refresh runs the id read first, then the changed rows. A load or a sync runs the query, then the id read. So a query that is not valid as a derived table fails at the first load, not at the first refresh.
• The id column and updatedAtColumn must each be a plain name: a letter or _, then letters, digits and _. The derived-table reads write them unquoted, and the database folds an unquoted name to its own case. A column whose name is quoted in another case, such as "updated_at" on H2, is then not found. In the query, alias each of these columns to a plain name, unquoted.
• updatedAtColumn may not name the id, content or title column.
• A row whose updatedAtColumn is NULL or is not a timestamp fails the source with CONFIGURATION.
• A row changed with a timestamp older than the latest one the last read saw is missed until the next sync.
• A bad row found by one of the derived-table reads is named by its number among that read's rows. The message says so, because the id read need not keep the query's order and a refresh counts only the changed rows.

SEQUENTIAL sources

A DATABASE source whose use is SEQUENTIAL reads its rows once. sync does not read it and does not list it. Such a source that sets refresh fails with CONFIGURATION, and the message names the layer that set it. It fails at startup when the annotation, a configuration file, the run file or an agent's entry sets refresh. A builder source that sets it fails when the binding is built or first searched.

Which sources are SEQUENTIAL is read from the view the binding was bound with:

• A name the Role itself declares SEQUENTIAL stays SEQUENTIAL, whatever replaces it.
• Any other name has the use of whatever replaced it last. An agent's entry gives its own use. A builder source carries no use, so a builder source that replaces a declared one is SEARCH.
• An action's knowledge, the Role's own or the agent's action knowledge that replaces it, can make a source SEQUENTIAL. Such a source that sets refresh fails at startup the same way.

At agent startup, the agent's own knowledge is checked before its roles'. When more than one source is at fault, the agent's fault is the one reported.

Options

An unknown option, a malformed value or a value out of range fails the source with CONFIGURATION. For an unknown key, the message names the key and the options the loader accepts.

Failures

A read interrupted by its caller throws a cancellation instead, and the thread's interrupt flag stays set.

A DATABASE source that fails is recorded like any other source that fails to load, and the Role's other sources still build. Asking for the failed source, through an action that names it or documentCount(name), throws the recorded failure. Its message is the loader's message, such as the row number, the option or the timeout, followed by the source and the Role it belongs to.

Your own loader

The bundled loader is a fallback. A SourceLoader your application registers for DATABASE replaces it, whatever the ServiceLoader order, and no warning is logged. A refresh and a sync read the source through your loader in full. A loader that implements RefreshableSourceLoader can instead return only what changed since a cursor it chose, with the ids of every current document. It still reads in full at each load and each sync. See Source loaders.

Related

• RAG: the @Knowledge members and how configuration files layer.
• Install retrieval providers: the SourceLoader SPI.
• RAG diagnostics: what a Role resolved.