SQL function reference

This page documents the public SQL surface registered by the ai extension. The layout follows the DuckDB function reference style: a compact overview table first, then each function with description, example, and result shape.

Table functions must be called from FROM or with CALL, for example:

SELECT *
FROM ai_usage();

Inputs and types

• Text inputs accept any DuckDB type. Numbers, dates, UUIDs, BLOBs, lists, structs and whole rows are sent as their text form, so ai_complete(t) FROM tickets t sends the row as {'id': 1, 'body': ...}. ai_provider_call is the exception: its body must be JSON text.
• Positional model and provider arguments can be columns or macro parameters, so each row can use a different model: ai_complete(prompt, model_column).
• Named options use name := value and follow every positional argument. An unknown or misspelled option name is an error, and an argument passed by position is always positional, even when its column is called model or temperature.
• Named options must be constant, but they can come from getvariable(...), macro parameters and prepared-statement parameters (model := $1). A NULL option, for example an unset variable, leaves the session or provider default in place.
• JSON-valued options (response_schema, request_options, metadata, label_descriptions, examples) accept JSON text or a STRUCT, MAP or list, which is converted to JSON: ai_extract_record(body, {type: 'object', properties: {urgency: {type: 'integer'}}}).
• include_tables and exclude_tables accept a list, a single table name or a comma-separated string.

Function overview

Function	Type	Description
ai_complete(prompt[, model[, provider]])	Scalar	Calls a completion model and returns text.
ai_decide(state, questions[, ...])	Scalar	Answers typed questions with a decision model (Jev, Clef, Perplexity, Ollama, any /v1/systemone endpoint) and returns a typed STRUCT.
ai_jev(text, questions[, ...])	Scalar	ai_decide with the provider fixed to TypeSafe Jev.
ai_provider_call(request_json, provider := ...)	Scalar	Sends a native JSON body and returns full JSON or buffered SSE events.
ai_try_complete(prompt[, model[, provider]])	Scalar	Calls a completion model and returns STRUCT(response, error) for row-level failure capture.
ai_complete_json(prompt[, model[, provider]])	Scalar	Calls a completion model and validates the response as a JSON object or array.
ai_complete_record(prompt, response_schema[, model[, provider]])	Table	Calls a completion model and projects a JSON object into typed columns from a JSON Schema.
ai_extract_record(text, response_schema[, model[, provider]])	Scalar	Extracts one typed STRUCT per row from text using a JSON Schema.
ai_completion_request_json(prompt[, model[, provider]])	Scalar	Returns the completion request JSON without making a network call.
ai_embed(text[, model[, provider]])	Scalar	Calls an embedding model and returns DOUBLE[].
ai_embedding_request_json(text[, model[, provider]])	Scalar	Returns the embedding request JSON without making a network call.
ai_similarity(left_text, right_text[, model[, provider]])	Scalar	Embeds two strings and returns cosine similarity.
ai_rerank(query, candidate[, model[, provider]])	Scalar	Uses a completion model to score candidate relevance from 0 to 1.
ai_score(input, criteria[, model[, provider]])	Scalar	Scores how well input satisfies criteria from 0 to 1.
ai_summarize(text[, model[, provider]])	Scalar	Summarizes text with a completion model.
ai_sentiment(text[, model[, provider]])	Scalar	Classifies text as positive, neutral, or negative.
ai_fix_grammar(text[, model[, provider]])	Scalar	Rewrites text with corrected grammar, spelling, and punctuation.
ai_redact(text[, model[, provider]])	Scalar	Masks direct personal data, credentials, secrets, and payment identifiers.
ai_translate(text, target_language[, model[, provider]])	Scalar	Translates text to the target language.
ai_classify(text, labels[, model[, provider]])	Scalar	Chooses one label from a comma-separated VARCHAR or VARCHAR[] label list.
ai_classify_labels(text, labels[, model[, provider]])	Scalar	Chooses zero or more labels from a comma-separated VARCHAR or VARCHAR[] label list.
ai_classify_result(text, labels[, ...])	Scalar	Returns multi-label classification as STRUCT(value, error, metadata).
ai_classify_optimized(text, artifact[, ...])	Scalar	Uses a local centroid classifier and falls back to an LLM below the configured confidence margin.
ai_extract(text, instruction[, model[, provider]])	Scalar	Extracts requested information from text.
ai_filter(text, predicate[, model[, provider]])	Scalar	Evaluates a natural-language predicate and returns BOOLEAN.
ai_agg(text, instruction[, model[, provider]])	Aggregate	Runs one completion over grouped text values and an instruction.
ai_summarize_agg(text[, model[, provider]])	Aggregate	Summarizes grouped text values.
ai_build_classifier(text, labels[, ...])	Aggregate	Builds an experimental persisted classifier artifact for cost-optimized single-label classification.
ai_generate_chunks(text[, ...])	Table	Splits text into deterministic Unicode-aware fixed or recursive chunks.
ai_prep_search(text[, ...])	Table	Produces retrieval and context-enriched embedding chunks for RAG.
ai_parse_document(content, mime_type, parser_profile[, ...])	Table	Calls a normalized remote document parser without bundling PDF/OCR dependencies.
ai_sql(question[, schema_context[, model[, provider]]])	Scalar	Generates one read-only DuckDB SELECT statement.
ai_query_data(question[, schema_context[, model[, provider]]])	Table	Generates one read-only SELECT at bind time and executes it as a subquery.
ai_schema_prompt([include_tables])	Table	Returns deterministic local catalog context for prompting SQL models.
ai_explain_sql(sql[, ...])	Table	Explains one read-only DuckDB SELECT statement.
ai_fix_sql(sql[, ...])	Table	Rewrites a broken query as one corrected read-only DuckDB SELECT, or rewrites one line with mode := 'line'.
ai_is_read_only_sql(sql[, check_binding])	Scalar	Returns whether SQL is one parser-valid read-only SELECT; optionally also checks it binds against the catalog.
ai_validate_read_only_sql(sql[, check_binding])	Scalar	Returns normalized SQL or raises if it is not one read-only SELECT; optionally also checks it binds against the catalog.
ai_count_tokens(text[, model[, provider]])	Scalar	Returns a local approximate token count.
ai_recommended_batch_size(input_tokens_per_row, max_output_tokens_per_row, token_limit_per_minute[, request_limit_per_minute[, safety_factor]])	Scalar	Returns a conservative row batch size for rate-limited AI jobs.
ai_provider_base_url(provider)	Scalar	Returns the default base URL for a supported provider.
ai_provider_protocol(provider)	Scalar	Returns the internal provider protocol.
ai_usage()	Table	Returns recent per-database AI usage events.
ai_usage_summary()	Table	Returns query-level calls, batches, retries, cache hits, cost, and dropped-event counters.
ai_usage_totals()	Table	Returns lifetime provider counters independent of event-buffer eviction.
ai_query_cache_stats()	Table	Returns generated SQL cache size, bounds, hits, misses, and evictions.
ai_clear_usage()	Table	Clears the per-database usage event buffer and lifetime counters.
ai_clear_cache()	Table	Clears per-database in-memory response and generated-SQL caches.
ai_secrets()	Table	Lists configured duckdb_ai secrets with credentials redacted.
ai_models()	Table	Lists safe external-model metadata and validation status.
ai_provision_endpoint(profile[, ...])	Table	Dry-runs or explicitly submits guarded asynchronous endpoint provisioning.
ai_endpoint_status(operation_id)	Table	Returns the current normalized endpoint operation status.
ai_deprovision_endpoint(profile)	Table	Explicitly submits asynchronous endpoint deprovisioning.
ai_model_prices()	Table	Returns the built-in provider/model pricing catalog.

Native provider JSON

ai_provider_call(request_json, provider := ..., secret := ..., api := 'chat', base_url := ...) returns a VARCHAR containing the full provider JSON response. It uses existing secret/environment resolution, retries, timeouts, response-size limits, host allowlists and opt-in caching. It never executes tools.

The body must be a JSON object with a non-empty model. Put generation options, tools, conversation history and reasoning state in that body. Do not also pass SQL model, temperature, token-limit, system or response-format options. Duplicate top-level fields and credential fields are rejected. Credentials belong in DuckDB secrets or environment variables.

api accepts chat, messages, responses, embeddings, rerank and fim. For non-chat APIs, set base_url (or secret BASE_URL) to the complete endpoint; no path is appended. This supports regional and workspace-specific endpoints. TypeSafe Jev uses its native /systemone endpoint automatically with provider := 'typesafe' (alias jev), without an api override. Its body contains model, state, and questions, and its response preserves answers and usage. See the Jev cookbook for several decisions in one call.

The service determines which models support each API. The extension does not convert protocols or supply capabilities a provider does not offer.

SELECT ai_provider_call(
  '{"model":"hy4-preview","input":"Explain vector search.","max_output_tokens":512}',
  provider := 'hunyuan', api := 'responses',
  base_url := 'https://tokenhub-intl.tencentcloudmaas.com/v1/responses'
);

Result shape: the complete non-streaming JSON object, including tool calls, reasoning, usage and finish/status fields. Refused, truncated and tool-only responses remain inspectable; callers must check those fields. HTTP errors and top-level error objects raise errors. fail_on_error := false returns NULL.

Requests with "stream":true buffer SSE and return {"events":[...provider event objects...]} after completion. SQL rows are not delivered incrementally. Missing terminators, invalid event JSON and error events fail. Token usage for streaming remains in the event objects and may be absent from summary usage counters. Native embedding/rerank results retain provider indices; native calls do not automatically batch or reorder them. Use ai_embed for the existing automatic embedding batching behavior.

For tool round trips, supply definitions and message history, extract returned tool calls, perform approved work in the caller, and send tool results with the original call IDs and reasoning/signature fields. Raw JSON is preserved, including large integer values. No tool is executed by this extension.

Plain completion functions additionally accept request_options := '{...}' for provider-native fields such as thinking, enable_thinking, reasoning_effort, preserve_thinking and top_p. Overrides of extension-owned model/input, stream, temperature, token-limit, response-format and cache fields are rejected.

SELECT ai_complete('Explain the proof.', provider := 'deepseek',
  request_options := '{"thinking":{"type":"enabled"},"reasoning_effort":"high"}');

Use ai_provider_call for tools and reasoning-only responses. ai_complete continues to return final text. See native APIs for reasoning providers.

Completion functions

ai_decide(state, questions[, ...])

Description: Answers named, typed questions about each row with a decision model and returns a typed STRUCT. Decision models return probabilities over answers you define instead of generating text, so the result needs no parsing. One call can ask several questions, which replaces separate ai_classify, ai_filter and ai_score calls on the same text. The same SQL works with every supported provider:

Provider	Models	Endpoint	Credentials
typesafe (alias jev)	jev-latest (default), jev-1.13.0	/v1/systemone	TYPESAFE_API_KEY
cloudflare	clef (default), clef-flash	Workers AI /ai/run/@cf/cloudflare/<model>	CLOUDFLARE_API_KEY and CLOUDFLARE_ACCOUNT_ID
perplexity	pplx-decider-v1.1-27b (default), pplx-decider-v1-27b	/v1/decisions	PERPLEXITY_API_KEY
ollama	nimble (default), tev1, tev1:0.8b	local /v1/systemone (Ollama 0.35 or later)	none
systemone	any; model is required	<base_url>/v1/systemone	optional SYSTEMONE_API_KEY

OpenAI's Decisions API is not supported until OpenAI publishes its request schema.

Example:

SELECT ticket_id, d.team, d.team_confidence, d.severity, d.urgent
FROM (
    SELECT ticket_id,
           ai_decide(body, {
               team: MAP {'billing': 'Payments and refunds', 'technical': 'Errors and outages'},
               severity: ['Routine', 'Degraded', 'Blocked'],
               urgent: MAP {'true': 'Needs action now', 'false': 'Can wait'},
               refund: {instructions: 'Is the customer asking for money back?',
                        criteria: MAP {'true': 'Asks for a refund', 'false': 'Does not'}}
           }, provider := 'cloudflare', model := 'clef-flash') AS d
    FROM support_tickets
);

Result shape:

STRUCT(team VARCHAR, team_confidence DOUBLE, severity DOUBLE, severity_confidence DOUBLE,
       urgent DOUBLE, refund DOUBLE)

Each field of questions is one question, and the field name becomes the result field name. Its value selects the question type:

Value	Question	Result fields
MAP {'label': 'description', ...}	Choice among 1 to 255 labels	<field> VARCHAR, the chosen label, and <field>_confidence DOUBLE
['lowest level', ..., 'highest level']	Score over 2 to 10 ordered levels	<field> DOUBLE, the level index from 0 to levels minus 1, and <field>_confidence DOUBLE
MAP {'true': 'yes criterion', 'false': 'no criterion'}	Yes/no question (TypeSafe calls it a Noul)	<field> DOUBLE, the probability of yes
{instructions: '...', criteria: <one of the above>}	Any of the above with its own instruction	as above

Rules:

• Questions must be constant, so the result type is known when the query is planned. Map keys, descriptions and list entries must be non-null strings.
• Only a map with exactly the lowercase keys true and false is a yes/no question. Other maps are choices.
• The descriptions define the task. Field names are SQL names and are not sent as instructions; use {instructions, criteria} to add one.
• Field names, including the generated _confidence names, must be unique ignoring ASCII case.
• Confidence is NULL when the provider omits it. NULL input returns a NULL struct without a request.
• Providers enforce their own limits. For example, Ollama accepts at most 26 options or levels and 64 questions per request.

Model and endpoint: the provider comes from provider := ..., a secret/profile, or duckdb_ai_provider. The session settings duckdb_ai_model and duckdb_ai_base_url name chat models and endpoints, so ai_decide ignores them, and it also ignores the MODEL of a chat provider's secret (for example an Ollama secret that names qwen3.8:27b). The model comes from, in order:

1. model := ... or a profile.
2. <PROVIDER>_DECISION_MODEL, for example OLLAMA_DECISION_MODEL, CLOUDFLARE_DECISION_MODEL, PERPLEXITY_DECISION_MODEL or TYPESAFE_DECISION_MODEL.
3. For TypeSafe, TYPESAFE_MODEL or the MODEL of a TypeSafe secret. For systemone, SYSTEMONE_MODEL or the MODEL of a systemone secret.
4. The provider default in the table above.

Batching: TypeSafe accepts several rows per request, so ai_decide with typesafe batches up to batch_size rows (default and maximum 32). Other providers receive one request per non-null row, with the row text as state, and run concurrently up to max_concurrent_requests. max_request_bytes (default 48000) caps every request body and splits batches without truncating data. A row too large for the cap fails, or becomes NULL with on_error := 'null'. The byte cap does not guarantee a fit within model token limits.

Errors: invalid choices, scores, probabilities and missing answers fail the affected request. With on_error := 'null', every row in that request is NULL. capture is not supported. Retries repeat the whole request.

Option	Default	Behavior
provider, profile, secret	Session provider	Select the decision provider and credentials.
model	See above	Pin a model version to keep results stable.
base_url	Provider default	Endpoint override; required for systemone.
batch_size	32	Rows per TypeSafe request, from 1 to 32. No effect on other providers.
max_request_bytes	48000	Request body cap, from 1024 to 1000000.
on_error	fail	fail stops the query, null returns NULL for affected rows.

The transport options timeout_seconds, retry_count, retry_backoff_ms, max_concurrent_requests, min_request_interval_ms, token_limit_per_minute, allowed_hosts and cache work as in Named options. Generation options such as temperature are rejected. ai_usage() records one ai_decide event per request, so a TypeSafe batch of 32 rows is one event.

Save results with CREATE TABLE ... AS SELECT ... before reading several fields or exporting them. See typed decisions for a table-to-Parquet workflow and route rows by decision confidence for thresholds and fallbacks. Use ai_provider_call for the raw probability distribution and resolved model metadata.

ai_jev(text, questions[, ...])

Description: ai_jev(text, questions, ...) is ai_decide(text, questions, provider := 'typesafe', ...). It does not accept the {instructions, criteria} form or the provider, profile and secret_name options. Existing queries keep working unchanged, and ai_usage() records its events as ai_jev.

Example:

SELECT ai_jev('Production imports are blocked.', {
    team: MAP {'technical': 'Bugs and outages', 'other': 'Other issues'},
    severity: ['Routine', 'Degraded', 'Blocked'],
    urgent: MAP {'true': 'Needs immediate action', 'false': 'Can wait'}
}, model := 'jev-1.13.0', batch_size := 32) AS decision;

Result shape:

STRUCT(team VARCHAR, team_confidence DOUBLE,
       severity DOUBLE, severity_confidence DOUBLE, urgent DOUBLE)

ai_complete(prompt[, model[, provider]])

Description: Calls a configured completion provider and returns the completion text as VARCHAR.

Example:

SELECT ai_complete(
    'Write one sentence about DuckDB.',
    provider := 'openai',
    model := 'gpt-5.6-luna'
);

Result: VARCHAR

ai_try_complete(prompt[, model[, provider]])

Description: Calls a configured completion provider and returns a STRUCT(response VARCHAR, error VARCHAR). Successful rows have error = NULL; failed rows have response = NULL and the provider or validation error text. Use this for batch enrichment when one bad row should be written to a rejected rows table instead of failing the whole query.

Example:

WITH attempts AS (
    SELECT id, ai_try_complete(prompt, provider := 'openai') AS result
    FROM prompts
)
SELECT id, result.response
FROM attempts
WHERE result.error IS NULL;

Result: STRUCT(response VARCHAR, error VARCHAR)

ai_complete_json(prompt[, model[, provider]])

Description: Calls a completion provider, asks for JSON-only output, and validates that the returned text is a top-level JSON object or array. If response_schema or json_schema is supplied, the output is also validated against the supported JSON Schema subset. Local JSON Pointer references such as #/$defs/Address enforce the referenced constraints, together with sibling constraints. Encountered unresolved or external references fail validation. Non-progressing reference cycles and more than 128 active references also fail; nullable unions keep accepting JSON null. References do not fetch remote schemas.

Example:

SELECT ai_complete_json(
    'Extract a compact company profile for DuckDB.',
    provider := 'openai',
    response_schema := '{"type":"object","properties":{"name":{"type":"string"}},"required":["name"]}'
);

Result: VARCHAR containing valid JSON

ai_complete_record(prompt, response_schema[, model[, provider]])

Description: Table function that calls a completion provider, validates the JSON response against response_schema, and projects top-level schema properties as typed DuckDB columns.

Schema integer fields project as BIGINT without rounding through DOUBLE. Values outside the signed 64-bit range fail projection. With fail_on_error := false, a failed table result contains null columns. Reference paths that include array indices retain their existing projection types, including the VARCHAR fallback when no explicit type is supplied. Local validation still enforces the referenced constraints.

Example:

SELECT *
FROM ai_complete_record(
    'Extract a compact company profile for DuckDB.',
    '{"type":"object","properties":{"name":{"type":"string"},"score":{"type":"number"}},"required":["name"]}',
    provider := 'openai'
);

Result: one row with columns derived from response_schema

ai_extract_record(text, response_schema[, model[, provider]])

Description: Scalar function that calls a completion provider for each input row and returns a typed STRUCT projected from the top-level object properties in response_schema. The schema must be constant so DuckDB can bind the return type. Fields follow the order of the schema properties. Local $ref/$defs are followed and an anyOf/oneOf with one non-null branch maps like that branch, so pydantic-generated schemas work. A property with several types becomes VARCHAR. JSON inside a code fence or after a sentence is accepted. References with array indices retain their existing projection types while their constraints are validated.

Integer fields preserve exact BIGINT values. Schema or projection failures follow on_error/fail_on_error; with fail_on_error := false, the entire record is NULL.

Example:

SELECT
    ticket_id,
    extracted.product_area,
    extracted.urgency_score
FROM (
    SELECT
        ticket_id,
        ai_extract_record(
            subject || ': ' || body,
            '{
              "type": "object",
              "properties": {
                "product_area": {"type": "string"},
                "urgency_score": {"type": "integer"}
              },
              "required": ["product_area", "urgency_score"]
            }'
        ) AS extracted
    FROM support_tickets
);

Result: one STRUCT value whose fields are derived from response_schema

ai_completion_request_json(prompt[, model[, provider]])

Description: Builds the provider completion request body and returns it without making a network call. Use this for deterministic tests and provider debugging.

Example:

SELECT ai_completion_request_json(
    'Summarize this text.',
    provider := 'openai',
    model := 'gpt-5.6-luna',
    temperature := 0.2
);

Result: VARCHAR containing provider request JSON

Embedding functions

ai_embed(text[, model[, provider]])

Description: Calls an embedding provider and returns a DuckDB list of doubles.

Example:

SELECT ai_embed(
    'DuckDB is an analytical database.',
    provider := 'openai',
    model := 'text-embedding-3-small'
);

Result: DOUBLE[]

list_cosine_similarity works on the result directly. DuckDB's array_* distance functions and the VSS extension need a fixed-size array, so cast first, for example ai_embed(body)::FLOAT[1536] for a 1536-dimension model.

ai_embedding_request_json(text[, model[, provider]])

Description: Builds the provider embedding request body and returns it without making a network call.

Example:

SELECT ai_embedding_request_json(
    'DuckDB vector smoke',
    provider := 'openai',
    model := 'text-embedding-3-small'
);

Result: VARCHAR containing provider request JSON

ai_similarity(left_text, right_text[, model[, provider]])

Description: Embeds both input strings with the same provider/model and returns cosine similarity in [-1, 1]. Norms are scaled to support finite coordinates with very large or small magnitudes. Zero-norm vectors are rejected; fail_on_error := false returns NULL for those failures.

Example:

SELECT ai_similarity(
    'DuckDB analytics',
    'analytical SQL database',
    provider := 'openai',
    model := 'text-embedding-3-small'
);

Result: DOUBLE

ai_rerank(query, candidate[, model[, provider]])

Description: Uses a completion model to score how relevant candidate is for query. The reply must contain one decimal score from 0 to 1. A Score: label, markdown, a trailing explanation or a {"score": ...} object are accepted; other replies raise an error or follow on_error. Use it when you want LLM-based reranking over a short candidate set; use ai_similarity for embedding-based semantic comparison at larger scale.

Example:

SELECT *
FROM documents
ORDER BY ai_rerank('analytics database', title || chr(10) || body) DESC
LIMIT 10;

Result: DOUBLE

ai_score(input, criteria[, model[, provider]])

Description: Uses a completion model with a strict JSON Schema containing one numeric score to evaluate how well input satisfies criteria. Values outside the inclusive [0, 1] range and malformed responses are rejected.

Example:

SELECT ai_score(
    ticket_body,
    'contains a reproducible production-impacting database issue'
) AS escalation_score
FROM support_tickets;

Result: DOUBLE

Task wrappers

ai_summarize(text[, model[, provider]])

Description: Summarizes text and returns only the summary.

Example:

SELECT ai_summarize('DuckDB is an analytical database built for fast local queries.');

Result: VARCHAR

ai_sentiment(text[, model[, provider]])

Description: Convenience wrapper for ai_classify(text, 'positive, neutral, negative'). It always returns positive, neutral or negative; any other reply raises an error or follows on_error.

Example:

SELECT ai_sentiment('The import finished quickly and the query is fast.');

Result: VARCHAR

ai_fix_grammar(text[, model[, provider]])

Description: Fixes grammar, spelling, and punctuation while preserving meaning.

Example:

SELECT ai_fix_grammar('duckdb are fast');

Result: VARCHAR

ai_redact(text[, model[, provider]])

Description: Masks direct personal data, credentials, secrets, and payment identifiers. With provider openai_privacy_filter, sends the raw input text to a local or cloud-hosted OpenAI Privacy Filter service instead of wrapping it in a chat prompt.

Example:

SELECT ai_redact('email alice@example.com');

SELECT ai_redact(
    'email alice@example.com token fake-token',
    provider := 'openai_privacy_filter',
    base_url := 'http://localhost:8080'
);

Result: VARCHAR

ai_translate(text, target_language[, model[, provider]])

Description: Translates text to target_language while preserving meaning and formatting.

Example:

SELECT ai_translate('hello', 'Dutch');

Result: VARCHAR

ai_classify(text, labels[, model[, provider]])

Description: Classifies text into exactly one label from labels. labels can be a list or fixed-size array (['billing', 'support'], also per row from a column), a comma-separated or JSON-array string, or a constant MAP {'label': 'description', ...} whose descriptions are added to the prompt. Use a list when labels may contain commas. Use constant label_descriptions :=, instructions :=, and examples := to add label semantics, global rules, and few-shot examples without changing the return type. Labels must be non-empty, non-NULL and unique ignoring case; an invalid label set fails (or returns NULL with on_error := 'null') before any request is sent. The returned value is validated against the configured set and canonicalized to its original spelling. Replies that wrap the label in markdown or quotes, put it after a Label: prefix, or follow it with punctuation or an explanation still match. A reply that names no label raises an error or follows on_error.

With provider := 'typesafe' (alias jev), this sends a native Jev Choice question rather than generating a label as text. Jev accepts at most 255 options. To answer several questions about the same text in one call and get a confidence per label, use ai_decide with a decision-model provider. Use ai_provider_call for the raw probability distribution.

Example:

SELECT ai_classify('invoice overdue', 'billing, support');

SELECT ai_classify('invoice overdue', ['billing, overdue', 'support']);

SELECT ai_classify(
    'invoice overdue',
    ['billing', 'support'],
    label_descriptions := '{"billing":"payments, invoices, or charges","support":"product help"}',
    instructions := 'Prefer billing when an invoice is explicitly mentioned.',
    examples := '[{"input":"card charged twice","label":"billing"}]'
);

Result: VARCHAR

ai_classify_labels(text, labels[, model[, provider]])

Description: Classifies text into zero or more labels from labels. labels can be a comma-separated VARCHAR or a VARCHAR[]; use VARCHAR[] when labels may contain commas. The model is asked for a JSON array of labels from the configured set. Unquoted arrays ([billing, other]), a single bare label and a bullet list are also accepted, and repeated labels are returned once. A reply with a label outside the set raises an error or follows on_error.

Example:

SELECT ai_classify_labels(
    'invoice overdue and app is slow',
    ['billing, overdue', 'performance', 'support']
);

Result: VARCHAR[]

ai_classify_result(text, labels[, model[, provider]])

Description: Multi-label classification with pipeline-safe diagnostics. It always captures provider and parsing failures instead of failing the full vector, preserves metadata for failed rows, and returns metadata describing the selected provider and model when resolution succeeds.

Example:

WITH classified AS (
    SELECT id, ai_classify_result(body, ['billing', 'performance', 'support']) AS result
    FROM support_tickets
)
SELECT id, result.value, result.metadata
FROM classified
WHERE result.error IS NULL;

Result: STRUCT(value VARCHAR[], error VARCHAR, metadata VARCHAR)

ai_classify_optimized(text, artifact[, ...])

Description: Experimental single-label classification. The function embeds text, compares it with the centroids in an ai_build_classifier() artifact, and returns locally when the best/second-best cosine margin meets the artifact threshold. It calls the fallback completion profile only when the artifact is unusable, embedding fails, or the margin is too small. Embeddings are packed and deduplicated by model options across each DuckDB vector chunk.

The support_model profile and classifier_artifacts table come from the ai_build_classifier example; profiles are created with CREATE EXTERNAL MODEL.

Example:

SELECT
    ticket_id,
    result.value,
    result.used_fallback,
    result.metadata
FROM (
    SELECT
        ticket_id,
        ai_classify_optimized(body, artifact, fallback_profile := 'support_model') AS result
    FROM support_tickets
    CROSS JOIN classifier_artifacts
);

Result: STRUCT(value VARCHAR, used_fallback BOOLEAN, confidence DOUBLE, error VARCHAR, metadata VARCHAR)

ai_extract(text, instruction[, model[, provider]])

Description: Extracts requested information from text. When the instruction asks for structured data, the function prompts for concise JSON.

Example:

SELECT ai_extract('name: DuckDB', 'name');

Result: VARCHAR

ai_filter(text, predicate[, model[, provider]])

Description: Evaluates whether text matches a natural-language predicate. The reply must start with true, false, yes or no; punctuation, markdown, quotes and a following reason are accepted. With provider := 'typesafe' (alias jev), this sends a native yes/no question and returns true for a probability of at least 0.5. Invalid or out-of-range probabilities raise an error. To keep the probability and choose your own threshold, use ai_decide with a MAP {'true': ..., 'false': ...} question.

Example:

SELECT ai_filter('invoice overdue', 'is about billing');

Result: BOOLEAN

Aggregate functions

ai_agg(text, instruction[, model[, provider]])

Description: Collects all grouped input text. When the input exceeds max_context_chars, the default overflow_policy := 'hierarchical' recursively maps chunks to compact evidence and reduces those partials until a final prompt fits. No input is silently discarded. Use overflow_policy := 'error' when the query must fail instead of issuing a request tree.

Example:

SELECT ai_agg(message, 'List the top three recurring themes')
FROM customer_feedback;

Result: VARCHAR

ai_summarize_agg(text[, model[, provider]])

Description: Convenience wrapper around ai_agg with the built-in summarization instruction. It uses the same hierarchical overflow behavior and returns one summary per group.

Example:

SELECT customer_id, ai_summarize_agg(note ORDER BY created_at)
FROM support_notes
GROUP BY customer_id;

Result: VARCHAR

ai_build_classifier(text, labels[, ...])

Description: Experimental relation-level MINIMIZE_COST workflow. The aggregate deterministically samples up to sample_size distinct texts across the whole relation, labels them with the configured task model, embeds the successful samples in packed requests, builds one centroid per label, and validates on a deterministic held-out subset. The returned versioned artifact includes quality, fallback margin, embedding profile, labels, and centroids. Version 1 supports single-label classification only. Labels must be nonblank and unique ignoring case and surrounding whitespace. Quoting preserves commas, quotes, backslashes, and control characters through training and fallback. Centroids use an incremental mean to avoid overflowing sums of finite embeddings.

Sampling selects the lowest stable text-hash priorities and merges across worker partitions without depending on row order. Identical texts appear only once, so exact duplicates cannot leak between training and holdout data. Artifact fields sampling and validation_strategy describe this policy. Near-duplicate records still require dataset-level grouping during independent evaluation.

The holdout split runs within each observed label and preserves at least one training example for that label. Artifacts with no held-out validation examples are marked unusable even when quality_threshold := 0. Reported accuracy measures agreement with the labeling model on this sample; validate the artifact against separately labeled, representative data before production use.

The support_model and support_embeddings profiles in this example are created with CREATE EXTERNAL MODEL.

Example:

CREATE TABLE classifier_artifacts AS
SELECT ai_build_classifier(
    body,
    ['billing', 'performance', 'support'],
    optimization := 'minimize_cost',
    sample_size := 256,
    quality_threshold := 0.85,
    confidence_margin := 0.08,
    embedding_profile := 'support_embeddings',
    profile := 'support_model'
) AS artifact
FROM support_tickets;

Result: VARCHAR containing a versioned JSON classifier artifact, or NULL when fail_on_error := false and training cannot produce a usable artifact.

RAG and document functions

ai_generate_chunks(input[, ...])

Description: Local deterministic chunking. fixed splits at exact Unicode code point counts. recursive prefers paragraph, sentence, line, then word boundaries. overlap_percent is limited to 0 through 50; offsets are zero-based Unicode code points and end_offset is exclusive. IDs are stable for the same source text, source ID, strategy, size, overlap, and chunk index. Empty and NULL input return zero rows.

Example:

SELECT *
FROM ai_generate_chunks(
    '# Introduction' || chr(10) || chr(10) ||
        'DuckDB is an in-process analytical database.',
    source_id := 'document-42',
    chunk_size := 1000,
    overlap_percent := 10,
    strategy := 'recursive'
);

To chunk every row of a table, call it with a lateral join. Options may then also come from columns, for example a per-document source_id:

SELECT d.id, c.chunk_index, c.chunk
FROM documents AS d,
     ai_generate_chunks(d.body, source_id := 'doc-' || d.id, chunk_size := 1000) AS c;

Result columns: source_id, chunk_id, chunk_index, start_offset, end_offset, chunk_length, estimated_tokens, chunk.

ai_prep_search(input[, ...])

Description: Builds retrieval-ready rows from text or Markdown. It keeps the original chunk in chunk_to_retrieve, adds title and the active Markdown heading to chunk_to_embed, preserves page numbers separated by form-feed page breaks, and carries JSON metadata. Line-aware recursive boundaries keep Markdown table rows intact when a row fits the configured chunk size.

enrich := false is fully local and deterministic. enrich := true makes one model call for concise document-level context and captures a row-level error when fail_on_error := false.

Like ai_generate_chunks, it accepts a column through a lateral join: FROM documents AS d, ai_prep_search(d.body, title := d.title). With enrich := true, each document costs one completion request.

Example:

SELECT *
FROM ai_prep_search(
    '# Billing' || chr(10) || chr(10) ||
        'The invoice was charged twice.',
    source_id := 'ticket-42',
    title := 'Duplicate charge',
    metadata := json_object('uri', '/tickets/42'),
    chunk_size := 1000,
    overlap_percent := 10,
    enrich := false
);

Result columns: source_id, chunk_id, chunk_index, chunk_to_retrieve, chunk_to_embed, heading, page, start_offset, end_offset, metadata, error.

ai_parse_document(content, mime_type, parser_profile[, ...])

Description: Sends binary content to the optional companion control plane's normalized document parser. The core extension performs base64 transport and schema normalization only; it does not bundle PDF, Office, OCR, or vendor SDK dependencies. For local extraction, use DuckDB's community pdf extension and pass its Markdown or text output to ai_prep_search().

Example:

SELECT *
FROM ai_parse_document(
    read_blob('contract.pdf').content,
    'application/pdf',
    'document-ai',
    pages := '1-10',
    fail_on_error := false
);

Result columns: document_id, page, element_index, element_type, text, markdown, bbox, confidence, metadata, error.

SQL assistant functions

ai_sql(question[, schema_context[, model[, provider]]])

Description: Calls a completion model to generate one DuckDB SELECT statement, strips common markdown code fences, and rejects output that is not one read-only SELECT. When schema_context is omitted, the function builds local catalog context from ai_schema_prompt(). With fix_attempts := N (0 to 5, default 0), the function verifies the generated SQL binds against the current catalog and, when it does not, feeds the bind error back to the model for up to N correction rounds before failing.

Example:

SELECT ai_sql('count orders by status');

Example with self-correction:

SELECT ai_sql('count orders by status', fix_attempts := 2);

Result: VARCHAR containing a read-only DuckDB SELECT

ai_query_data(question[, schema_context[, model[, provider]]])

Description: Table function that generates one read-only DuckDB SELECT at bind time and executes it as a subquery. Successful generated SQL is cached in the current DuckDB database instance for repeated binds with the same question, schema context, resolved provider credentials, model profile, and output-affecting options. The cache retains at most 1,024 entries and 64 MiB of key/SQL text, evicting least recently used entries; oversized entries bypass caching. Use on_error := 'null' to return an empty error-shaped relation instead of failing the bind; the error is recorded in ai_usage().

With fix_attempts := N (0 to 5, default 0), the function verifies the generated SQL binds against the current catalog before returning it. When binding fails (a hallucinated column, a wrong function name, a typo), the DuckDB bind error is fed back to the model for up to N correction rounds. Each correction is a regular model call and appears in ai_usage() alongside an ai_query_data_fix_attempt event. Cached SQL is re-verified on cache hits when fix_attempts > 0, so entries that stopped binding after a schema change are repaired instead of failing.

Example:

SELECT *
FROM ai_query_data('count orders by status', include_tables := ['main.orders']);

-- Table function arguments cannot be subqueries, so store a custom context in a variable first
SET VARIABLE orders_ctx = (SELECT summary FROM ai_schema_prompt(include_tables := ['main.orders']));
SELECT * FROM ai_query_data('count orders by status', schema_context := getvariable('orders_ctx'));

Example with self-correction (useful in views, scheduled queries, and other non-interactive contexts):

SELECT *
FROM ai_query_data(
    'revenue by pickup borough',
    include_tables := ['trips', 'zones'],
    fix_attempts := 2
);

Result: the result columns of the generated query

ai_schema_prompt([include_tables])

Description: Table function that returns one summary row with deterministic DuckDB catalog context. include_tables and exclude_tables accept table, schema.table, or catalog.schema.table names. sample_rows includes bounded sample rows for local DuckDB tables during execution.

Example:

SELECT summary
FROM ai_schema_prompt(
    include_tables := ['main.orders'],
    exclude_tables := ['main.internal_audit'],
    sample_rows := 3
);

Result: one summary VARCHAR column

ai_explain_sql(sql[, ...])

Description: Table function that explains one read-only DuckDB SELECT statement using optional schema context.

Example:

SELECT explanation
FROM ai_explain_sql(
    'SELECT status, count(*) FROM orders GROUP BY status',
    include_tables := ['main.orders']
);

Result: one explanation VARCHAR column

ai_fix_sql(sql[, ...])

Description: Table function that asks the model to rewrite a broken query as one corrected read-only DuckDB SELECT statement. With mode := 'line', it rewrites only the line identified by an error message and returns the detected line number plus replacement line. An error that points past the last line of the query fails before any request is sent.

In query mode, pass error := to include the failure message in the correction prompt. The error text is usually what makes the fix reliable. Passing error without an explicit mode selects line mode for backward compatibility, so combine it with mode := 'query' for full-query rewrites. Query mode also accepts fix_attempts := N (0 to 5, default 0) to verify the corrected SQL binds against the current catalog and re-correct with the bind error for up to N rounds.

Example:

SELECT sql
FROM ai_fix_sql('SEELECT status, count(*) FRUM orders GROUP BY status');

Example with the error message and bind verification:

SELECT sql
FROM ai_fix_sql(
    'SELECT statuss, count(*) FROM orders GROUP BY statuss',
    mode := 'query',
    error := 'Binder Error: Referenced column "statuss" not found',
    fix_attempts := 1
);

Result: one sql VARCHAR column

Example:

SELECT line_number, replacement_line
FROM ai_fix_sql(
    'SEELECT status FROM orders',
    mode := 'line',
    error := 'Parser Error: syntax error at or near "SEELECT" LINE 1'
);

Line-mode result: line_number BIGINT, replacement_line VARCHAR

SQL validation functions

ai_is_read_only_sql(sql[, check_binding])

Description: Returns whether sql parses as exactly one read-only DuckDB SELECT statement. With check_binding set to true, the statement must also bind against the current catalog: hallucinated tables, columns, or functions return false even when the SQL parses. The bind check runs in the current session, so temporary tables are visible, and nothing is executed.

Example:

SELECT ai_is_read_only_sql('SELECT 42');
SELECT ai_is_read_only_sql('SELECT missing_column FROM my_table', true);

Result: BOOLEAN

ai_validate_read_only_sql(sql[, check_binding])

Description: Returns the input SQL when it is one read-only DuckDB SELECT; otherwise raises an error. With check_binding set to true, the statement must also bind against the current catalog.

Example:

SELECT ai_validate_read_only_sql('SELECT count(*) FROM my_table');
SELECT ai_validate_read_only_sql('SELECT count(*) FROM my_table', true);

Result: VARCHAR

ai_count_tokens(text[, model[, provider]])

Description: Returns a local approximate token count. This function does not call a provider.

Example:

SELECT ai_count_tokens('DuckDB local analytics');

Result: BIGINT

ai_recommended_batch_size(input_tokens_per_row, max_output_tokens_per_row, token_limit_per_minute[, request_limit_per_minute[, safety_factor]])

Description: Returns a conservative number of rows to process per batch or minute for a rate-limited AI job. input_tokens_per_row can come from avg(ai_count_tokens(prompt)), max_output_tokens_per_row should match the planned max_tokens, and safety_factor defaults to 0.8. Results outside the BIGINT range raise an error.

Example:

SELECT ai_recommended_batch_size(100, 200, 200000, 500);

Result: BIGINT

Provider metadata functions

ai_provider_base_url(provider)

Description: Returns the default base URL for a supported provider after alias normalization.

Example:

SELECT ai_provider_base_url('ollama');

Result: VARCHAR

ai_provider_protocol(provider)

Description: Returns the internal request protocol used for provider request and response shaping.

Example:

SELECT ai_provider_protocol('anthropic');

Result: VARCHAR

Usage and catalog table functions

ai_usage()

Description: Returns recent completion, embedding, and local usage events for the current DuckDB database instance. The buffer keeps the latest 1,024 events.

Example:

SELECT *
FROM ai_usage()
ORDER BY event_id DESC;

Result columns: event_id, created_at, event, function_name, query_id, operation_id, parent_operation_id, provider, protocol, model, character counts, token counts, cached-token counts, elapsed time, retry count, HTTP status, cache hit flag, status, error, and estimated cost. Counts the provider did not report, and http_status for events without an HTTP call, are NULL. Character counts are Unicode characters. A reply that fails validation still records its token counts, with status = 'error' and the reason in error.

ai_usage_summary()

Description: Groups the retained usage buffer by query_id. calls counts per-input events (one event per request for ai_decide and ai_jev), while batch_count counts distinct provider request operation IDs, so packed embedding execution is visible without losing row-level token and error details. The result also includes retries, cache hits, total tokens, elapsed time, estimated cost, retained events, and counters for usage or log events dropped from bounded buffers. total_tokens and estimated_cost_usd are NULL when no call in the group reported tokens or had a price.

Example:

SELECT query_id, provider, calls, batch_count, retries, cache_hits,
       estimated_cost_usd, dropped_events
FROM ai_usage_summary()
ORDER BY estimated_cost_usd DESC;

Result columns: query_id, provider, model, calls, batch_count, retries, failures, cache_hits, total_tokens, elapsed_ms, estimated_cost_usd, retained_events, dropped_events, queued_log_events, dropped_log_events.

ai_usage_totals()

Description: One-row snapshot of per-database provider counters since database startup or ai_clear_usage(), independent of the bounded event buffer. Local helper events are excluded. request_attempts includes retries and attempted transports, including failures; cache hits and requests canceled while waiting for pacing do not increment it.

SELECT * FROM ai_usage_totals();

Result: provider_events, request_attempts, failures, cache_hits, known_total_tokens, and unknown_token_events, all UBIGINT. Provider events count rows, so a packed request can produce multiple events. Known tokens are the sum of event-reported totals, including the existing cached-event accounting; they are partial when unknown_token_events > 0, and are not a billing total. These counters survive event eviction but not database shutdown. Persist snapshots for jobs that need totals after reopening, as in the resumable enrichment example.

ai_query_cache_stats()

Description: One-row snapshot of generated SQL cache retention and lookup counters for the current database. It makes no provider request.

SELECT * FROM ai_query_cache_stats();

Result: entries, bytes, max_entries, max_bytes, hits, misses, evictions, and oversized_skips, all UBIGINT. Bytes count retained SQL and both owned key copies; container overhead is additional. ai_clear_cache() clears entries and resets these counters. Replacing an entry is not an eviction.

ai_clear_usage()

Description: Clears the current DuckDB database instance's usage event buffer and returns one confirmation row.

Example:

SELECT *
FROM ai_clear_usage();

Result: one cleared BOOLEAN column

ai_clear_cache()

Description: Clears the current DuckDB database instance's opt-in in-memory response cache, ai_query_data() generated-SQL cache, and bounded query-local ai_similarity() embedding cache, then returns one confirmation row.

Example:

SELECT *
FROM ai_clear_cache();

Result: one cleared BOOLEAN column

ai_secrets()

Description: Lists configured duckdb_ai secrets with credential values redacted.

Example:

SELECT name, provider, model, base_url, has_api_key
FROM ai_secrets();

Result columns: name, provider, model, base_url, scope, storage, persistent, has_api_key

CREATE EXTERNAL MODEL and ai_models()

Description: CREATE EXTERNAL MODEL registers a non-secret model profile in DuckDB's local secret catalog. The object stores provider routing, a reference to a TYPE duckdb_ai credential secret, capabilities, and safe JSON options; it never stores API keys. Existing profile := resolution checks external models first and then falls back to a credential-secret profile with that name.

Example:

CREATE OR REPLACE SECRET azure_ai (
    TYPE duckdb_ai,
    AI_PROVIDER 'azure',
    API_KEY '...'
);

CREATE OR REPLACE EXTERNAL MODEL support_model
WITH (
    provider = 'azure',
    model = 'gpt-4o',
    location = 'https://my-resource.openai.azure.com/openai/v1',
    credential = 'azure_ai',
    model_type = 'completion',
    capabilities = 'completion,json_schema',
    options = '{"max_input_tokens":128000,"max_batch_inputs":512,"input_token_price_per_million":2.5,"output_token_price_per_million":10}'
);

SELECT ai_complete('Summarize this ticket.', profile := 'support_model');
SELECT * FROM ai_models();

Quote option values. An unquoted value is read up to the next , or ) with its whitespace removed, so model = gpt-4o-mini works but a value containing spaces must be quoted.

Embedding profiles can set max_batch_inputs, max_inputs, max_batch_tokens, max_request_bytes, context_size, max_input_tokens, token_estimate_multiplier, embedding_dimensions, and native_batch_support in options. Packing uses these limits before sending requests and recursively splits provider HTTP 413 responses. Declared embedding dimensions are validated against provider output. Completion profiles enforce declared input-token and request-byte limits before issuing an HTTP request. model_type must be completion or embedding. Profiles can also set non-negative input_token_price_per_million and output_token_price_per_million; an explicit per-call or session price takes precedence.

ai_models() result columns: name, provider, model, location, credential, model_type, capabilities, options, max_batch_inputs, max_batch_tokens, max_request_bytes, context_tokens, embedding_dimensions, native_batch_jobs, input/output token prices, storage, persistent, and validation_status.

Endpoint control-plane functions

Description: Optional table functions for an external, asynchronous endpoint service. Registration and inference never create billable cloud resources. ai_provision_endpoint() defaults to a local dry run. Submission requires both dry_run := false and a positive max_hourly_cost_usd; the service is expected to make operations idempotent and keep cloud credentials server-side.

SELECT * FROM ai_provision_endpoint('support_model');

SELECT *
FROM ai_provision_endpoint(
    'support_model',
    dry_run := false,
    max_hourly_cost_usd := 5.00
);

SELECT * FROM ai_endpoint_status('operation-id');
SELECT * FROM ai_deprovision_endpoint('support_model');

Set DUCKDB_AI_CONTROL_PLANE_URL for applied operations and optionally DUCKDB_AI_CONTROL_PLANE_TOKEN for bearer authentication. The normalized result columns are operation_id, status, endpoint_url, estimated_hourly_cost_usd, action_required, and response.

ai_model_prices()

Description: Returns the small built-in provider/model pricing catalog used by opt-in cost estimation.

Example:

SELECT provider, model, operation, input_token_price_per_million
FROM ai_model_prices();

Result columns: provider, model, operation, input_token_price_per_million, output_token_price_per_million, source_url, source_note, last_reviewed

Named options

Completion functions accept constant named options unless noted otherwise. Provider credentials are resolved from environment variables or DuckDB secrets, not from direct API key arguments.

Option	Type	Applies to	Description
model	VARCHAR	Completion, embedding, SQL assistant, aggregate	Provider model name.
provider	VARCHAR	Completion, embedding, SQL assistant, aggregate	Provider name or alias.
profile, secret, secret_name	VARCHAR	Completion, embedding, SQL assistant, aggregate	DuckDB secret name or profile.
temperature	DOUBLE	Completion, SQL assistant, aggregate	Optional sampling temperature between 0 and 2. When omitted, the provider or model default is used.
system_prompt	VARCHAR	Completion, SQL assistant, aggregate	Optional system message for providers that support chat-style payloads.
max_tokens	BIGINT	Completion, SQL assistant, aggregate	Maximum provider output tokens. Must be greater than 0. OpenAI, Cloudflare, MiniMax, Moonshot/Kimi, Xiaomi MiMo, and Snowflake requests emit the current max_completion_tokens API field; other compatible providers retain max_tokens.
base_url	VARCHAR	Completion, embedding, SQL assistant, aggregate	Provider or gateway base URL override.
timeout_seconds	BIGINT	Completion, embedding, SQL assistant, aggregate	HTTP timeout. Must be greater than 0.
connect_timeout_seconds	BIGINT	Completion, embedding, SQL assistant, aggregate	HTTP connection timeout from 1 to 31536000 seconds. It must be less than or equal to the total timeout when a provider call runs.
retry_count	BIGINT	Completion, embedding, SQL assistant, aggregate	Retry count from 0 to 10 for curl failures and retryable HTTP status codes. Retries honor Retry-After on 429/5xx responses when present.
retry_backoff_ms	BIGINT	Completion, embedding, SQL assistant, aggregate	Base retry backoff from 0 to 60000 milliseconds; exponential jitter is added per retry.
max_concurrent_requests	BIGINT	Completion, embedding, SQL assistant, aggregate	Per-database request concurrency cap from 0 to 64.
min_request_interval_ms	BIGINT	Completion, embedding, SQL assistant, aggregate	Per-database minimum interval between provider request starts.
token_limit_per_minute	BIGINT	Completion, embedding, SQL assistant, aggregate	Per-database estimated token cap per rolling minute. 0 disables the token cap.
cache	BOOLEAN	Completion, embedding, SQL assistant, aggregate	Enables the current database instance's in-memory response cache for successful provider responses.
cache_ttl_seconds	BIGINT	Completion, embedding, SQL assistant, aggregate	Optional response-cache expiration from 0 to 31536000 seconds. 0 means entries do not expire by age. The DUCKDB_AI_CACHE_TTL_SECONDS environment variable sets the default.
cache_max_entries	BIGINT	Completion, embedding, SQL assistant, aggregate	Maximum in-memory response-cache entries from 0 to 1000000. 0 disables response-cache storage. The DUCKDB_AI_CACHE_MAX_ENTRIES environment variable sets the default.
prompt_cache	BOOLEAN	Completion, task wrappers, and SQL assistant	Emits provider-side prompt-cache controls where supported. OpenAI requests include a stable prompt_cache_key; GPT-5.6 requests also mark the stable system-message prefix with an explicit cache breakpoint. Anthropic requests attach ephemeral cache control to the system prompt; xAI requests send x-grok-conv-id. The DUCKDB_AI_PROMPT_CACHE environment variable enables this by default.
allowed_hosts	VARCHAR	Completion, embedding, SQL assistant, aggregate	Comma-separated provider/logging host allowlist. Entries may be hostnames, host:port, full URLs, *.example.com, or *.
on_error	VARCHAR	Completion, embedding, SQL assistant, aggregate	Error handling mode: fail, null, or capture. capture is used by ai_try_complete; scalar/table functions that cannot return an error field use NULL behavior.
fail_on_error	BOOLEAN	Completion, embedding, SQL assistant, aggregate	Compatibility alias: true maps to on_error := 'fail', false maps to on_error := 'null'.
response_format	VARCHAR	Completion and SQL assistant	text, json_object, or json_schema. Poe's Chat Completions API ignores this field, so non-text formats are rejected for that provider.
response_schema, json_schema	VARCHAR	Completion and SQL assistant	JSON Schema object for provider-enforced structured output where supported, including OpenAI-compatible APIs, Anthropic, Cohere, llama.cpp, and Ollama. ai_complete_json, ai_complete_record and ai_extract_record also validate the response locally; ai_complete and ai_try_complete return the text as received. Poe is excluded because its Chat Completions API ignores response_format.
input_token_price_per_million	DOUBLE	Completion, embedding, SQL assistant, aggregate	Manual input-token price for cost estimation.
output_token_price_per_million	DOUBLE	Completion, SQL assistant, aggregate	Manual output-token price for cost estimation.
use_builtin_model_prices	BOOLEAN	Completion, embedding, SQL assistant, aggregate	Enables lookup from ai_model_prices() when manual prices are not supplied.
log_format	VARCHAR	Completion, embedding, SQL assistant, aggregate	generic, json, generic_json, otlp, or otlp_json.
log_tags	VARCHAR	Completion, embedding, SQL assistant, aggregate	Comma-separated tags copied into usage log payloads.
log_sample_rate	DOUBLE	Completion, embedding, SQL assistant, aggregate	Stable sampling rate from 0 to 1.
log_include_text	BOOLEAN	ai_complete_record; all families through duckdb_ai_log_include_text or DUCKDB_AI_LOG_INCLUDE_TEXT	Include prompt/output text in outbound usage logs. Defaults to false.
log_strict	BOOLEAN	ai_complete_record; all families through duckdb_ai_log_strict or DUCKDB_AI_LOG_STRICT	Post outbound usage logs synchronously and fail the SQL query when logging fails. Without strict logging, outbound logs are queued asynchronously on a best-effort basis.

See Runtime behavior for the execution semantics behind volatility, per-database state, concurrency, cancellation, retries, response caching, and egress allowlisting.

SQL assistant table functions also accept:

Option	Type	Description
schema_context, schema	VARCHAR	Prompt context to use instead of generated local catalog context.
include_tables	VARCHAR[]	Limit generated local catalog context to matching tables.
exclude_tables	VARCHAR[]	Remove matching tables from generated local catalog context.
sample_rows	BIGINT	Include up to 100 sample rows per local table.
mode	VARCHAR	ai_fix_sql only. query or full rewrites the full query; line rewrites one error line.
error	VARCHAR	ai_fix_sql only. Error text included in the correction prompt; in line mode it also identifies the target line. Without an explicit mode, passing error selects line mode.
fix_attempts	BIGINT	ai_sql, ai_query_data, and ai_fix_sql query mode. Number of bind-verified self-correction rounds from 0 to 5. Default 0 keeps single-shot behavior.

Aggregate functions also accept:

Option	Type	Description
instruction, task	VARCHAR	Constant instruction for ai_agg.
separator	VARCHAR	Separator inserted between grouped input values.
max_context_chars	BIGINT	Maximum grouped text characters sent to the model.
overflow_policy	VARCHAR	hierarchical (default) recursively reduces every input, while error rejects groups that exceed max_context_chars.

Classification functions also accept:

Option	Type	Description
label_descriptions	VARCHAR	Constant label semantics, commonly encoded as JSON.
instructions	VARCHAR	Constant global classification rules.
examples	VARCHAR	Constant few-shot examples, commonly encoded as JSON.

ai_build_classifier() additionally accepts optimization := 'minimize_cost', sample_size, quality_threshold, confidence_margin, embedding_model, embedding_provider, and embedding_profile. ai_classify_optimized() accepts fallback_profile plus completion options for the uncertain-row fallback.

Provider settings and secrets

Supported provider names and aliases are:

Provider	Aliases	Completion support	Embedding support	Default completion model
ollama	none	Yes	Yes	llama3.2
openai	none	Yes	Yes	gpt-5.6-luna
azure	azure_openai, azure-openai	Yes	Yes	gpt-5.6-luna
anthropic	claude	Yes	No	claude-haiku-5-5
bedrock	aws, aws_bedrock, amazon_bedrock, bedrock_mantle	Yes	No	openai.gpt-oss-120b
cerebras	cerebras_cloud	Yes	No	gpt-oss-120b
cloudflare	workers_ai, cloudflare_workers_ai, cloudflare_ai	Yes	Yes	@cf/zai-org/glm-4.7-flash
cohere	cohere_ai	Yes	Yes	command-a-plus-05-2026
dashscope	qwen, alibaba, alibaba_cloud, alibaba_model_studio, model_studio	Yes	Yes	qwen-plus
databricks	mosaic, mosaic_ai, databricks_ai	Yes	No	databricks-gpt-oss-120b
deepinfra	deepinfra_ai	Yes	Yes	meta-llama/Meta-Llama-3.1-8B-Instruct-Turbo
deepseek	none	Yes	No	deepseek-flash
fireworks	fireworks_ai	Yes	Yes	accounts/fireworks/models/gpt-oss-120b
gemini	gcp, google, google_gemini	Yes	Yes	gemini-3.8-flash
groq	groqcloud, groq_cloud	Yes	No	openai/gpt-oss-20b
huggingface	hf, hugging_face, huggingface_hub, hf_inference	Yes	No	openai/gpt-oss-120b
hunyuan	tencent, tencent_hunyuan	Yes	No	hy3
minimax	mini_max	Yes	No	MiniMax-M2.7
mimo	xiaomi, xiaomi_mimo	Yes	No	mimo-v2.6-flash
mistral	none	Yes	Yes	mistral-small-latest
moonshot	kimi, moonshot_ai, kimi_api	Yes	No	kimi-k3
nebius	nebius_ai, nebius_token_factory, token_factory	Yes	No	Qwen/Qwen3-30B-A3B-Instruct-2507
nvidia	nvidia_nim, nim	Yes	No	nvidia/nemotron-3-super-120b-a12b
openrouter	none	Yes	Yes	openai/gpt-4o-mini
perplexity	pplx	Yes	No	sonar
poe	poe_api	Yes	No	GPT-5.4
qianfan	baidu, baidu_qianfan, ernie, wenxin	Yes	No	ernie-5.1
sambanova	sambanova_ai, samba_nova, sambacloud, samba_cloud	Yes	No	Meta-Llama-3.3-70B-Instruct
siliconflow	silicon_flow	Yes	No	Qwen/Qwen2.5-72B-Instruct
snowflake	none	Yes	No	claude-sonnet-4-5
stepfun	step, step_fun, stepfun_ai	Yes	No	step-3.5-flash
together	together_ai	Yes	Yes	meta-llama/Llama-3.3-70B-Instruct-Turbo
vercel	vercel_ai_gateway, vercel_gateway, ai_gateway	Yes	Yes	openai/gpt-4o-mini
vertex	google_vertex, vertex_ai, gcp_vertex	Yes	No	google/gemini-3.8-flash
volcengine	volcano, volcano_engine, volcengine_ark, doubao, ark	Yes	No	doubao-seed-2-1-pro-260628
xai	x.ai, x-ai, grok	Yes	No	grok-4.6
zai	zhipu	Yes	Yes	glm-4.7-flash
openai_privacy_filter	privacy_filter, pii_filter, opf	Redaction only	No	openai/privacy-filter
openai_compatible	local, openai-compatible, local_openai, local-models, local_models	Yes	Yes	gpt-4o-mini
llamacpp	llama.cpp, llama-cpp, llama_cpp, llama-server, llama_server	Yes	Yes	default (llama-server answers with its loaded model)
typesafe	jev	Decisions only (ai_decide, ai_jev, ai_classify, ai_filter)	No	jev-latest
systemone	system_one, decision	Decisions only (ai_decide)	No	none; pass model := or set SYSTEMONE_MODEL

Most multi-word aliases also accept a hyphen in place of the underscore, for example aws-bedrock or together-ai. Names are case-insensitive.

Session defaults can be configured with DuckDB settings:

SET duckdb_ai_provider = 'openai';
SET duckdb_ai_model = 'gpt-5.6-luna';
SET duckdb_ai_embedding_model = 'text-embedding-3-small';
SET duckdb_ai_base_url = 'https://api.openai.com/v1';
SET duckdb_ai_timeout_seconds = 120;
SET duckdb_ai_allowed_hosts = 'api.openai.com,collector.example';
SET duckdb_ai_cache = true;

Every setting below is a session default. The matching named option on a call overrides it. A numeric value of -1, or an empty or NULL value, means the extension default applies. To list the settings in your installed version, run SELECT name, input_type, description FROM duckdb_settings() WHERE starts_with(name, 'duckdb_ai');.

Setting	Type	Description
duckdb_ai_provider	VARCHAR	Default provider.
duckdb_ai_model	VARCHAR	Global default model.
duckdb_ai_completion_model	VARCHAR	Default model for completion functions.
duckdb_ai_task_model	VARCHAR	Default model for text task functions.
duckdb_ai_aggregate_model	VARCHAR	Default model for aggregate functions.
duckdb_ai_sql_assistant_model	VARCHAR	Default model for SQL assistant functions.
duckdb_ai_embedding_model	VARCHAR	Default model for embedding functions.
duckdb_ai_base_url	VARCHAR	Provider base URL override.
duckdb_ai_allowed_hosts	VARCHAR	Comma-separated provider and logging host allowlist. Empty allows all hosts that the DUCKDB_AI_ALLOWED_HOSTS environment variable allows; it can narrow that variable but not widen it.
duckdb_ai_timeout_seconds	BIGINT	HTTP timeout in seconds. 0 uses the extension default.
duckdb_ai_connect_timeout_seconds	BIGINT	Connection timeout from 1 to 31536000 seconds.
duckdb_ai_retry_count	BIGINT	Retries from 0 to 10 for transport failures and retryable HTTP statuses.
duckdb_ai_retry_backoff_ms	BIGINT	Base retry backoff from 0 to 60000 milliseconds.
duckdb_ai_max_concurrent_requests	BIGINT	Concurrent provider requests from 0 to 64. 0 removes the explicit cap; each chunk then uses up to min(CPU threads, 8) workers.
duckdb_ai_min_request_interval_ms	BIGINT	Minimum interval between request starts, from 0 to 60000 milliseconds.
duckdb_ai_token_limit_per_minute	BIGINT	Estimated token cap per rolling minute. 0 disables the limit.
duckdb_ai_on_error	VARCHAR	Error handling: fail, null, or capture.
duckdb_ai_response_format	VARCHAR	Default response format: text, json_object, or json_schema.
duckdb_ai_response_schema	VARCHAR	Default JSON Schema object for structured responses.
duckdb_ai_cache	BOOLEAN	Cache successful provider responses in the current DuckDB instance.
duckdb_ai_cache_max_entries	BIGINT	Response-cache entries from 0 to 1000000. 0 disables cache storage.
duckdb_ai_cache_ttl_seconds	BIGINT	Response-cache entry age from 0 to 31536000 seconds. 0 disables age expiry.
duckdb_ai_prompt_cache	BOOLEAN	Send provider-side prompt-cache hints where supported.
duckdb_ai_input_token_price_per_million	DOUBLE	Input token price for estimated usage cost. -1 disables manual pricing.
duckdb_ai_output_token_price_per_million	DOUBLE	Output token price for estimated usage cost. -1 disables manual pricing.
duckdb_ai_use_builtin_model_prices	BOOLEAN	Estimate cost from the built-in ai_model_prices() catalog.
duckdb_ai_log_endpoint	VARCHAR	HTTP endpoint for privacy-minimized usage logs.
duckdb_ai_log_format	VARCHAR	Usage log payload format: generic_json (aliases generic, json) or otlp_json (alias otlp).
duckdb_ai_log_tags	VARCHAR	Tag string included in usage logs.
duckdb_ai_log_sample_rate	DOUBLE	Usage log sampling rate from 0 to 1.
duckdb_ai_log_include_text	BOOLEAN	Include prompt and response text in usage logs.
duckdb_ai_log_strict	BOOLEAN	Fail the SQL query when usage log delivery fails.

duckdb_ai_model is the global model fallback. Family-specific model settings override it for their function groups, while per-call model := ... still takes highest precedence:

Setting	Applies to
duckdb_ai_completion_model	ai_complete, ai_complete_json, ai_complete_record, ai_completion_request_json, ai_rerank, ai_score
duckdb_ai_task_model	ai_summarize, ai_sentiment, ai_fix_grammar, ai_redact, ai_translate, ai_classify, ai_classify_labels, ai_classify_result, ai_extract, ai_filter, classifier labeling and fallback
duckdb_ai_aggregate_model	ai_agg, ai_summarize_agg
duckdb_ai_sql_assistant_model	ai_sql, ai_query_data, ai_explain_sql, ai_fix_sql
duckdb_ai_embedding_model	ai_embed, ai_embedding_request_json, ai_similarity, ai_build_classifier, ai_classify_optimized

Provider and model resolution order is:

1. Per-call provider := ... and model := ...
2. External model selected through profile := ...
3. Session settings: the matching function-family model setting, then duckdb_ai_provider and duckdb_ai_model
4. AI_PROVIDER and MODEL stored in a TYPE duckdb_ai secret (named with secret := ..., or found by provider scope). A secret only fills what the steps above left unset, but it always supplies the API key.
5. Provider environment variables, such as OPENAI_MODEL, then DUCKDB_AI_MODEL
6. Provider default model

A secret for a different provider than the resolved one is skipped, or raises an error when it was named with secret :=. If session settings select Ollama and you call a hosted model through a secret, pass provider := ... and model := ... on the call or RESET duckdb_ai_provider; RESET duckdb_ai_model; first. ai_decide and ai_jev resolve models differently; see ai_decide.

Runtime defaults when nothing is set: a 120-second total timeout per request (DUCKDB_AI_TIMEOUT_SECONDS overrides it), no retries, and up to min(CPU threads, 8) concurrent requests per execution chunk.

Credentials should use environment variables or DuckDB secrets:

CREATE OR REPLACE SECRET openai_ai (
    TYPE duckdb_ai,
    AI_PROVIDER 'openai',
    API_KEY '...',
    MODEL 'gpt-5.6-luna'
);

SELECT ai_complete('hello', secret := 'openai_ai');