AI Functions

AI functions call an external model provider from SQL. They cover three kinds of work:

• Text with a chat model: ai_generate answers a prompt, ai_classify picks a label, ai_classify_labels picks every label that applies, ai_extract pulls out a value or a JSON object, ai_filter decides whether a condition holds, ai_score rates how well a text meets criteria, ai_rerank rates how relevant a document is to a search query, ai_translate translates and ai_redact masks personal information.
• Aggregates with a chat model: ai_agg answers an instruction over all the values in a group, and ai_summarize_agg summarizes them.
• Embeddings: ai_embed turns text into a vector for vector search, and ai_similarity compares two texts by the cosine similarity of their embeddings.
• Typed decisions: ai_system_one asks a Jev decision model a yes/no, multiple-choice or rating question and returns calibrated probabilities instead of prose.

Attention

Every AI function sends the text to the configured provider over the network. That costs money on hosted providers, the text leaves the server, and the text can steer the model's answer (prompt injection). ai_redact depends entirely on the model and is not a sufficient anonymization mechanism on its own.

Providers

The functions read the provider's endpoint and API key from a secret. There are two secret types:

• TYPE openai is used by every ai_* function. It names the OpenAI wire protocol, not a vendor, so it reaches OpenAI itself, any hosted provider with an OpenAI-compatible endpoint, such as OpenRouter or Google Gemini (see endpoint URLs), and locally hosted models served by Ollama, vLLM, LM Studio, LiteLLM or llama.cpp.
• TYPE typesafe is used by ai_system_one. It reaches the hosted TypeSafe Jev API, or a self-hosted Kev server, which serves the same API from open models.

For OpenAI itself, an api_key is all you need:

sql

CREATE SECRET openai (TYPE openai, api_key '⟨sk-...⟩', model 'gpt-4o-mini');

The runnable examples on this page use a local Ollama server with the all-minilm embedding model and the qwen2.5:0.5b chat model, and a local Kev server:

SQL

CREATE SECRET local_ai (    TYPE openai,    base_url 'http://localhost:11434',    embeddings_path '/v1/embeddings');

SQL

CREATE SECRET local_chat (    TYPE openai,    base_url 'http://localhost:11434',    model 'qwen2.5:0.5b');

SQL

CREATE SECRET local_system_one (    TYPE typesafe,    base_url 'http://localhost:8009');

openai parameter	Description
api_key	API key for the provider. Required by OpenAI; optional for open local endpoints.
base_url	Base URL of an OpenAI-compatible server. Omit for OpenAI itself.
model	Default chat model for the text functions. A model argument overrides it.
chat_path	Path of the chat completions endpoint, if it differs from /v1/chat/completions.
embeddings_path	Path of the embeddings endpoint, if it differs from /v1/embeddings.

typesafe parameter	Description
api_key	TypeSafe API key. Omit for a Kev server started without KEV_API_KEY.
base_url	Base URL of the API. Defaults to https://api.typesafe.ai; point it at a Kev server to run locally.
model	Model name. Defaults to jev-latest, which Kev also answers to.
path	Path of the System One endpoint, if it differs from /v1/systemone, for example behind a gateway.

A base_url that starts with http:// sends the text and the API key unencrypted. The functions therefore refuse a secret whose base_url uses http:// for a host other than localhost, 127.0.0.0/8 or ::1. To use such an endpoint, for example a model server on your private network, run SET sdb_ai_allow_insecure_endpoint = true.

Endpoint URLs

A function sends its request to base_url followed by the endpoint path:

• chat_path, or /v1/chat/completions, for the chat functions;
• embeddings_path, or /v1/embeddings, for ai_embed and ai_similarity;
• path, or /v1/systemone, for ai_system_one.

If base_url already ends with the last segment of that path, it is used as is. So you can also give a full endpoint URL such as https://openrouter.ai/api/v1/chat/completions, but that secret then serves only that one endpoint.

Providers usually publish a base URL that already ends in a version, the form OpenAI SDKs expect. If that version is /v1, drop it from base_url: the default paths add it back. OpenRouter publishes https://openrouter.ai/api/v1, so its secret uses https://openrouter.ai/api. OpenRouter model IDs start with the upstream provider. A model argument picks a different model for one call:

sql

CREATE SECRET openrouter (
    TYPE openai,
    base_url 'https://openrouter.ai/api',
    api_key '⟨sk-or-...⟩',
    model 'openai/gpt-4o-mini'
);

SELECT ai_classify(
    'The package arrived broken.', ['complaint', 'praise', 'question'],
    secret_name := 'openrouter'
) AS kind;

SELECT ai_generate(
    'Explain columnar storage in one sentence.',
    model := 'anthropic/claude-haiku-4.5',
    secret_name := 'openrouter'
) AS answer;

SELECT array_length(
    ai_embed('vector search', 'openai/text-embedding-3-small', 'openrouter'), 1
) AS dims;

Otherwise, keep the published base URL and give the paths without the version. Google Gemini publishes https://generativelanguage.googleapis.com/v1beta/openai:

sql

CREATE SECRET gemini (
    TYPE openai,
    base_url 'https://generativelanguage.googleapis.com/v1beta/openai',
    chat_path '/chat/completions',
    embeddings_path '/embeddings',
    api_key '⟨...⟩',
    model 'gemini-2.5-flash'
);

Choosing the secret

Each call names its secret with secret_name. When it doesn't, the function uses the secret named by a setting, and fails if that setting is empty:

Setting	Used by
sdb_ai_text_default_secret	ai_generate, ai_classify, ai_classify_labels, ai_extract, ai_filter, ai_score, ai_rerank, ai_translate, ai_redact, ai_agg, ai_summarize_agg
sdb_ai_embedding_default_secret	ai_embed, ai_similarity
sdb_ai_system_one_default_secret	ai_system_one

SQL

SET sdb_ai_text_default_secret = 'local_chat';
SELECT ai_translate('Good morning', 'German') AS german;

Common parameters

The text functions and aggregates take the per-row text and their own arguments, then these optional named parameters:

Parameter	Description
model	Chat model to use. Defaults to the secret's model.
secret_name	Name of the openai secret. Defaults to sdb_ai_text_default_secret.
temperature	Sampling temperature, a finite number of at least 0. The default depends on the function: 0.7 for ai_generate, 0.3 for ai_translate and 0 for the others.
max_tokens	Maximum number of tokens in the model's reply. Default 1024.

Every argument except the per-row text must be a constant. A NULL text returns NULL without a request. Except for ai_generate, rows with the same text share one request when they fall into the same batch (see Performance).

Each AI call looks up its secret and reads the settings when its query starts to run, so a prepared statement picks up a replaced secret and the current settings on every EXECUTE.

A reply counts as a failed row when the model stopped at max_tokens, when the provider withheld or filtered it, or when the model asked to call a tool instead of answering. See Errors, retries and quotas for how failed rows are handled.

Text as data

Each function sends its instruction as the system message and the row text as a separate user message. The instruction tells the model to treat the text as data and never follow instructions that appear in it. ai_classify, ai_classify_labels, ai_extract, ai_filter, ai_score and ai_rerank also request a strict JSON schema, so a provider that supports structured outputs can only reply with an allowed label, a boolean, a score or the requested keys. The replies are checked as well: an unknown label makes ai_classify return NULL and fails the row in ai_classify_labels, and a score outside [0, 1] fails the row.

These measures make instructions hidden in the data less effective, but they can't rule them out.

ai_generate

ai_generate(prompt [, system_prompt]) sends prompt to the chat model and returns its reply as VARCHAR:

SQL

SELECT ai_generate('What is 2 + 2? Reply with just the number.',                   secret_name := 'local_chat') AS answer;

 answer
--------
      4

system_prompt replaces the default instruction, "You are a helpful assistant. Provide a clear and concise response.":

SQL

SELECT ai_generate('Explain what a B-tree is.',                   system_prompt := 'You are a database expert. Answer in one sentence.',                   secret_name := 'local_chat') AS answer;

ai_classify

ai_classify(text, categories) returns the one label from categories that best fits text. The labels must be unique, ignoring case, and non-empty. A reply that matches no label, even case-insensitively, returns NULL:

SQL

SELECT id, ai_classify(body, ['positive', 'negative', 'neutral'],                       secret_name := 'local_chat') AS sentimentFROM reviewsORDER BY id;

 id | sentiment
----+-----------
  1 | positive
  2 | negative
  3 | NULL
  4 | positive

categories can also be a STRUCT(label VARCHAR, description VARCHAR)[] that tells the model what each label means. A description may be NULL:

SQL

SELECT id, ai_classify(body, [{label: 'praise', description: 'the customer is satisfied'},                              {label: 'complaint', description: 'the customer reports a problem'}],                       secret_name := 'local_chat') AS kindFROM reviewsORDER BY id;

 id | kind
----+-----------
  1 | praise
  2 | complaint
  3 | NULL
  4 | praise

ai_classify_labels

ai_classify_labels(text, categories) returns every label from categories that applies to text as a VARCHAR[], or an empty array when none applies. categories takes the same two forms as in ai_classify. A reply that contains a label outside the list, or the same label twice, fails the row:

SQL

SELECT id, ai_classify_labels(body, ['price', 'quality', 'durability'],                              secret_name := 'local_chat') AS topicsFROM reviewsORDER BY id;

 id | topics
----+-----------------
  1 | {price,quality}
  2 | {quality}
  3 | NULL
  4 | {quality}

ai_extract

ai_extract(text, instruction_or_schema) has two modes. With a free-form instruction it returns the extracted value as VARCHAR, or NULL when the text doesn't contain it:

SQL

SELECT ai_extract('The package arrived late and was damaged.', 'the main complaint',                  secret_name := 'local_chat') AS complaint;

 complaint
-----------
 late

When the second argument is a JSON object, each key names a field and each value describes it. The function then returns JSON with exactly those keys, and null for any value the text doesn't contain:

SQL

SELECT ai_extract('John Smith, 42, lives in Berlin.',                  '{"name": "full name", "age": "age in years", "city": "city of residence"}',                  secret_name := 'local_chat') AS person;

 person
--------------------------------------------------
 {"name":"John Smith","age":"42","city":"Berlin"}

ai_filter

ai_filter(text, condition) returns true when the model decides that condition holds for text, and false otherwise, so it fits in a WHERE clause:

SQL

SELECT id, bodyFROM reviewsWHERE ai_filter(body, 'the customer is unhappy', secret_name := 'local_chat');

 id | body
----+---------------------------------
  2 | Broke after two days, terrible.

ai_score

ai_score(text, criteria) rates how well text satisfies criteria and returns a DOUBLE between 0, not at all, and 1, fully. A reply outside that range fails the row:

SQL

SELECT id, ai_score(body, 'the review is positive about the product',                    secret_name := 'local_chat') AS scoreFROM reviewsORDER BY id;

 id | score
----+-------
  1 |     1
  2 |     0
  3 |  NULL
  4 |     1

ai_rerank

ai_rerank(query, document) rates how relevant document is to the search query and returns a DOUBLE between 0, not relevant, and 1, answers the query exactly. query must be a constant. Use it to reorder the top candidates of a cheaper search, such as vector search:

SQL

SELECT id, bodyFROM reviewsWHERE body IS NOT NULLORDER BY ai_rerank('product stopped working', body, secret_name := 'local_chat') DESCLIMIT 2;

 ranked
--------
      3

ai_translate

ai_translate(text, target_language [, instructions]) translates text into target_language, given as a language name or a BCP-47 code. instructions adds guidance such as "Use the polite form":

SQL

SELECT ai_translate('Hello, world!', 'French', secret_name := 'local_chat') AS french;

 french
------------------
 Bonjour, monde !

ai_redact

ai_redact(text, categories [, replacement]) rewrites text with every occurrence of the listed kinds of personal information replaced by replacement (default [REDACTED]). An empty categories array stands for person names, email addresses, phone numbers, postal addresses, credit card numbers and IP addresses.

SQL

SELECT ai_redact('Purchase was done by customer John Doe with email test@test.org',                 ['email', 'name'], secret_name := 'local_chat') AS redacted;

ai_agg and ai_summarize_agg

ai_agg(text, instruction) is an aggregate function. It answers instruction over the non-NULL values of text in each group and returns the answer as VARCHAR. ai_summarize_agg(text) returns a summary of the values instead. Both return NULL for a group without values. An ORDER BY inside the call sets the order in which the values are sent. FILTER and DISTINCT work as for any other aggregate:

SQL

SELECT ai_agg(body, 'What do customers like most? Answer in one sentence.',              secret_name := 'local_chat') AS answerFROM reviews;

 answer
----------------------------------------------------------
 Customers like to have a great value and work perfectly.

SQL

SELECT ai_summarize_agg(body, secret_name := 'local_chat' ORDER BY id) AS summaryFROM reviews;

Besides the common parameters, both take max_context_chars (default 100000), the maximum size in bytes of the UTF-8 values sent in one request. A group with more text is split into parts, and each part is condensed into notes that keep what the instruction needs. The notes are then combined in further requests until they fit into one request. A value longer than the limit is split across parts, at character boundaries. If condensing stops making the notes shorter, the group fails with an error that suggests raising max_context_chars or max_tokens.

ai_embed(text, model, secret_name)

ai_embed(text, model [, secret_name] [, dimensions]) sends text to the embedding model and returns the embedding as a FLOAT[]. The vector's length is the model's embedding dimension, 384 for all-minilm:

SQL

SELECT array_length(ai_embed('space exploration', 'all-minilm', 'local_ai'), 1) AS dims;

 dims
------
  384

 unit_length
-------------
 t

A NULL or empty text returns NULL without a request, so rows without text are simply skipped:

SQL

SELECT ai_embed(NULL, 'all-minilm', 'local_ai') IS NULL AS is_null;

 is_null
---------
 t

Argument	Description
text	The text to embed. NULL or an empty string yields NULL.
model	The provider's embedding model name, for example 'all-minilm' or 'text-embedding-3-small'.
secret_name	Name of the openai secret. Defaults to sdb_ai_embedding_default_secret.
dimensions	Requested vector size, for models that can shorten their embeddings (such as OpenAI's text-embedding-3-*). 0 or omitted keeps the native size. A reply with an embedding of any other size fails every row of that request.

Returns a variable-length FLOAT[]. An empty embedding in a reply fails every row of that request. To store embeddings in an IVF vector column, which requires a fixed size, cast to FLOAT[N] with the model's dimension, for example ai_embed(...)::FLOAT[384]. Every stored row and the query vector must use the same model and dimension, or the index and the distance comparisons will not line up.

Choosing a model

The embedding dimension N is fixed by the model. A few common ones:

Model	Provider	Dimension N
text-embedding-3-small	OpenAI	1536
text-embedding-3-large	OpenAI	3072
all-minilm (all-MiniLM-L6-v2)	Ollama / local	384

Check your provider's documentation for the exact dimension and use it as the N in the stored FLOAT[N] column. Match the index's distance metric to how the model's vectors are meant to be compared. Most text-embedding models are tuned for cosine similarity.

Performance

Each ai_embed call is a network request to the provider, so embed documents once at write time and store the vectors; only the query text is embedded at search time. Rows are sent in batches of up to sdb_ai_embedding_max_batch_size texts per request. If the provider rejects a batch of several texts with HTTP 400, 413 or 422, for example because one text is longer than the model accepts, the batch is split in half and sent again, down to single texts, so only the rejected texts fail. A single text rejected with HTTP 422 fails the query, like any other 422. On the first HTTP 400 or 422, one extra request with the single text x checks whether the provider rejects every request, for example because of the dimensions value; if it does, the rejected batches fail without further splitting. Embedding a column is just a SELECT, and NULLs pass through and are easy to count or filter:

SQL

SELECT    count(*) FILTER (WHERE e IS NOT NULL) AS embedded,    count(*) FILTER (WHERE e IS NULL)     AS skippedFROM (    SELECT ai_embed(name, 'all-minilm', 'local_ai') AS e    FROM fruits) t;

 embedded | skipped
----------+---------
        4 |       1

ai_similarity

ai_similarity(text1, text2, model [, secret_name] [, dimensions]) embeds both texts with model and returns their cosine similarity as a DOUBLE in [-1, 1]. It returns NULL when either text is NULL or empty. The arguments after the texts work as in ai_embed:

SQL

SELECT ai_similarity('cat', 'kitten', 'all-minilm', 'local_ai')     > ai_similarity('cat', 'spreadsheet', 'all-minilm', 'local_ai') AS closer;

 closer
--------
 t

For ranking many rows against one query, store the embeddings and use vector search instead; ai_similarity embeds both texts on every call.

ai_system_one

ai_system_one asks a Jev decision model a closed question about a text and returns a typed answer: a probability, a choice from a fixed list, or a position on an ordered scale. It uses the TypeSafe System One API, which a local Kev server also serves.

sql

ai_system_one(input, instructions [, noul | choice | score] [, batch_size] [, model] [, secret_name])
ai_system_one(input, questions := ... [, model] [, secret_name])

input is the per-row text; every other argument must be a constant. The question type is set by which of noul, choice and score you pass. They can't be combined, and passing none asks a noul question.

Question	Criteria	Returns
noul (yes/no)	optional, exactly the labels true and false	DOUBLE, the probability that the answer is yes
choice	2 to 255 labels	STRUCT(choice VARCHAR, probabilities STRUCT(value VARCHAR, probability DOUBLE)[], confidence DOUBLE)
score	2 to 10 levels, lowest first	STRUCT(score DOUBLE, probabilities STRUCT(index INTEGER, value VARCHAR, probability DOUBLE)[], confidence DOUBLE)

probabilities lists every label in the order given. For score, score is the expected level index, between 0 and the number of levels minus one, so 1.4 sits between the second and third level. confidence is between 0 and 1 and shows how concentrated the probabilities are.

A noul question returns the probability of yes:

SQL

SELECT ai_system_one(    'Please refund my order, it never arrived.',    'Does this request a refund?',    secret_name := 'local_system_one') AS refund_probability;

 refund_probability
--------------------
             0.9143

A choice question picks one label:

SQL

SELECT (routing).choice, (routing).confidence, (routing).probabilitiesFROM (    SELECT ai_system_one(        'I was charged twice for last month and the invoice does not match.',        'Which team should handle this?',        choice := ['billing', 'technical', 'sales'],        secret_name := 'local_system_one'    ) AS routing) t;

 choice  | confidence | probabilities
---------+------------+------------------------------------------------------------
 billing |     0.7331 | {"(billing,0.8221)","(technical,0.0797)","(sales,0.0983)"}

Criteria are either a VARCHAR[] of labels, or a STRUCT(label VARCHAR, description VARCHAR)[] that explains what each label means. Labels must be unique and non-empty; a description may be NULL:

SQL

SELECT (ai_system_one(    'My invoice shows the wrong VAT number.',    'Which team should handle this?',    choice := [        {label: 'billing', description: 'Payments, invoices, and refunds'},        {label: 'technical', description: 'Errors, outages, and integrations'},        {label: 'sales', description: 'Pricing, trials, and upgrades'}    ],    secret_name := 'local_system_one')).choice AS team;

 team
---------
 billing

A score question rates the text on an ordered scale:

SQL

SELECT (severity).score, (severity).confidence, (severity).probabilitiesFROM (    SELECT ai_system_one(        'The product is unusable and we are considering cancelling.',        'Rate the severity of this message.',        score := ['low', 'medium', 'high'],        secret_name := 'local_system_one'    ) AS severity) t;

 score  | confidence | probabilities
--------+------------+---------------------------------------------------------
 1.2858 |     0.6429 | {"(0,low,0.2022)","(1,medium,0.3098)","(2,high,0.488)"}

Several questions at once

questions asks several questions about the same text in one request and returns a STRUCT with one field per question, each in its type's shape above. Each question is a STRUCT with type, instructions and, for choice and score, criteria. questions can't be combined with instructions, noul, choice, score or batch_size.

SQL

SELECT    (answer).refund,    (answer).category.choice AS category,    (answer).urgency.score AS urgencyFROM (    SELECT ai_system_one(        'Please refund my order, it never arrived. The app keeps crashing too.',        questions := {            refund: {type: 'noul', instructions: 'Does this request a refund?'},            category: {type: 'choice', instructions: 'Classify the primary subject.',                       criteria: ['billing', 'technical', 'other']},            urgency: {type: 'score', instructions: 'How urgent is this?',                      criteria: ['low', 'medium', 'high']}        },        secret_name := 'local_system_one'    ) AS answer) t;

 refund | category | urgency
--------+----------+---------
 0.9129 | other    |  1.3825

questions can also be a JSON object in the API's own format, which allows structured instructions and criteria descriptions with fields such as examples:

SQL

SELECT (ai_system_one(    'My invoice shows a duplicate charge.',    questions := '{      "category": {        "type": "choice",        "instructions": {"question": "Classify the primary subject", "focus": ["primary intent"]},        "criteria": {          "billing": {"description": "Charges and invoices", "examples": ["duplicate charge"]},          "technical": "Bugs and integrations"        }      }    }'::JSON,    secret_name := 'local_system_one')).category.choice AS category;

 category
----------
 billing

Batching

A single-question call packs up to batch_size rows (1 to 64, default 32) into one request. Packing sends fewer requests, but the model sees several rows at once, so answers can drift compared with asking about each row alone. Set batch_size := 1 for strict per-row isolation. If the provider rejects a packed request (HTTP 400, 413 or 422, for example because the batch is too long), the batch is split in half and retried, down to single rows. On the first HTTP 400 or 422, one extra request with the single state x checks whether the provider rejects the question itself; if it does, the rejected batches fail without further splitting. questions calls always send one request per row.

SQL

SELECT id, (ai_system_one(body, 'Which team should handle this?',                       choice := ['billing', 'technical', 'sales'],                       secret_name := 'local_system_one')).choice AS teamFROM support_messagesORDER BY id;

 id | team
----+-----------
  1 | billing
  2 | technical
  3 | NULL
  4 | sales

SQL

SELECT id, ai_system_one(body, 'Does this message report a problem?',                      batch_size := 1, secret_name := 'local_system_one') AS problemFROM support_messagesORDER BY id;

Because each call is a request, materialize results you reuse, for example in a table or a MATERIALIZED CTE, instead of calling ai_system_one again.

Classify rows as they are written

A stored generated column calls ai_system_one when a row is inserted, and again when an UPDATE changes a column that its expression reads. The answer is stored with the row, so queries read it without sending requests, and an UPDATE of any other column sends none. The expression is bound when the table is created and by every statement that computes it, so pass secret_name in the expression instead of relying on sdb_ai_system_one_default_secret:

SQL

CREATE TABLE tickets (    id INTEGER PRIMARY KEY,    body VARCHAR,    team GENERATED ALWAYS AS ((ai_system_one(body, 'Which team should handle this?',                                          choice := ['billing', 'technical', 'sales'],                                          secret_name := 'local_system_one')).choice) STORED);
INSERT INTO tickets (id, body) VALUES    (1, 'I was double charged on my card.'),    (2, 'The API returns 500 errors since this morning.'),    (3, NULL);
UPDATE tickets SET body = 'Can I get a discount for 50 seats?' WHERE id = 2;
SELECT id, team FROM tickets ORDER BY id;

 id | team
----+---------
  1 | billing
  2 | sales
  3 | NULL

ALTER TABLE ... ADD COLUMN can't add a generated column yet. To add an answer to a table that already has rows, add a plain column and fill it in place with UPDATE. The conditions in WHERE are checked first, so only the matching rows are sent:

SQL

ALTER TABLE support_messages ADD COLUMN problem DOUBLE;
UPDATE support_messagesSET problem = ai_system_one(body, 'Does this message report a problem?', secret_name := 'local_system_one')WHERE id <= 2;
SELECT id, round(problem, 2) AS problem FROM support_messages ORDER BY id;

 id | problem
----+---------
  1 |    0.72
  2 |    0.74
  3 |    NULL
  4 |    NULL

Errors, retries and quotas

These settings apply to every AI function:

Setting	Default	Description
sdb_ai_throw_on_error	true	When false, a row whose request fails returns NULL instead of failing the query. HTTP 401, 403, 404 and 422 always fail the query, because they mean a wrong key, endpoint, model or question. So does HTTP 429 with the error type or code insufficient_quota, which means the account's quota is used up.
sdb_ai_max_retries	3	Retries after a connection error or HTTP 408, 429, 5xx or 529. A 429 with insufficient_quota isn't retried. Retries count toward sdb_ai_max_api_calls_per_query.
sdb_ai_retry_initial_delay_ms	500	Delay before the first retry; each further retry doubles it, up to 60 seconds. A Retry-After response header takes precedence, also up to 60 seconds.
sdb_ai_request_timeout	120	Timeout of a single request, in seconds.
sdb_ai_max_concurrent_requests	16	Maximum requests a query has in flight, across all of its AI calls and threads. Lower it for a local server that can't keep up. See Performance.
sdb_ai_max_api_calls_per_query	0	Maximum requests a query may send. 0 = unlimited.
sdb_ai_max_output_tokens_per_query	0	Maximum output tokens a query may consume, as reported by the provider. The check happens before each request, so requests already in flight can go over it. 0 = unlimited.
sdb_ai_throw_on_quota_exceeded	true	When false, rows after a quota is exhausted return NULL instead of failing the query.
sdb_ai_embedding_max_batch_size	100	Maximum texts per embeddings request.
sdb_ai_allow_insecure_endpoint	false	Allow http:// endpoints on hosts other than localhost. See Providers.

There is no input-token quota: the number of input tokens is only known to the provider.

SQL

SET sdb_ai_max_api_calls_per_query = 2;SET sdb_ai_throw_on_quota_exceeded = false;
SELECT count(*) FILTER (WHERE s IS NULL) AS skippedFROM (SELECT ai_classify(body, ['positive', 'negative'], secret_name := 'local_chat') AS s      FROM reviews WHERE body IS NOT NULL) t;

 skipped
---------
       1

Performance

An AI function spends almost all of its time waiting for the provider, so each call sends the requests for a chunk of rows (up to 2048) at the same time and waits until they are answered.

• The thread that evaluates the call sends requests itself, and tasks on DuckDB's async I/O threads send the rest. The thread waits without using the CPU, but it stays busy until the chunk's last answer arrives.
• A query has at most sdb_ai_max_concurrent_requests requests in flight, across all of its AI calls and threads. Besides the threads that evaluate the calls, it sends them from at most async_threads tasks, by default twice the number of CPU cores. With async_threads = 0, only the evaluating threads send requests.
• Rows of a chunk that have the same text share one request. ai_embed and ai_similarity send up to sdb_ai_embedding_max_batch_size texts per request, and ai_system_one up to batch_size rows.
• In a WHERE clause, conditions joined by AND are checked one after the other, so rows that one condition rejects are never sent to the next. Comparisons that are pushed into a table scan, such as a column compared with a constant, are checked before the AI calls; other conditions can be checked after them.
• In a query without ORDER BY, a constant LIMIT below 8192 is applied before the AI calls in the SELECT list, so SELECT ai_generate(...) FROM t LIMIT 10 sends 10 requests. With ORDER BY ... LIMIT, the AI calls in the SELECT list run for every row before the sort; to call them only for the rows that remain, sort and limit in a subquery: SELECT ai_generate(body) FROM (SELECT body FROM t ORDER BY id LIMIT 10) s.
• Calls inside CASE, COALESCE, AND, OR and TRY run only for the rows that reach them.
• ai_agg and ai_summarize_agg gather each group's values first, then send the requests of all groups in a chunk together.
• Cancelling a query or disconnecting stops new requests at once. Requests already in flight finish, or give up after sdb_ai_request_timeout.
• A request that fails the query, such as a wrong API key, or any failed request while sdb_ai_throw_on_error is true, stops the requests of its chunk that were not sent yet.
• A client that fetches rows in pages, for example JDBC with setFetchSize, runs AI calls only for the chunks it has fetched, so closing its cursor early sends no more requests.

End-to-end: semantic search

Embed each row once, store the vector in a fixed-size FLOAT[N] column and build an IVF index over it:

SQL

CREATE TABLE catalog (id INTEGER PRIMARY KEY, name VARCHAR, embedding FLOAT[384]);
INSERT INTO catalogSELECT id, name, ai_embed(name, 'all-minilm', 'local_ai')::FLOAT[384]FROM fruitsWHERE name IS NOT NULL;
CREATE INDEX catalog_idx ON catalog    USING inverted (id, name, embedding ivf (metric = 'cosine'));

Then embed the query text at search time and rank by vector distance — the embedding model maps semantically related words close together. The index uses the cosine metric, so rank with its operator <=>; another operator would compute distances without the index:

sql

SELECT id, name
FROM catalog_idx
ORDER BY embedding <=> ai_embed('tropical fruit', 'all-minilm', 'local_ai')::FLOAT[384]
LIMIT 3;

Because name is also full-text indexed in the same index, you can pair a lexical filter with semantic ranking; see Hybrid Search.

See also

• Vector Search: IVF indexing and the <-> operator
• Hybrid Search: combine full-text filters with vector ranking
• CREATE SECRET: configure the provider
• Secrets Manager: secret types and scopes