# DuckDB AI extension > duckdb-ai is a DuckDB extension that calls LLMs from SQL: classify, summarize and extract typed data, generate embeddings for semantic search, and turn questions into read-only SQL, with Ollama, OpenAI, Claude, Gemini and 30+ providers. # DuckDB AI: LLMs and embeddings in SQL duckdb-ai is a DuckDB extension that calls large language models from SQL. Use it to summarize, classify and filter rows, extract typed fields with a JSON Schema, generate embeddings for semantic search and RAG, and turn natural-language questions into read-only SQL. It works with local models (Ollama, llama.cpp, any OpenAI-compatible server) and hosted providers such as OpenAI, Anthropic Claude, Google Gemini, Azure OpenAI, Amazon Bedrock, Databricks and Snowflake Cortex. | What | Name | | --- | --- | | Extension (`INSTALL` / `LOAD`) | `ai` | | SQL functions | `ai_*` | | Settings | `duckdb_ai_*` | | Secret type | `TYPE duckdb_ai` | ## Install The extension is published as a [DuckDB community extension](https://duckdb.org/community_extensions/extensions/ai): ```sql INSTALL ai FROM community; LOAD ai; ``` Releases from 0.9.0 target DuckDB 2.0. The 0.8.x releases are the DuckDB 1.5.6 line. These docs describe the source on `main`. The community package can lag behind it, so check what your installed version provides: ```sql SELECT function_name FROM duckdb_functions() WHERE starts_with(function_name, 'ai_') ORDER BY function_name; ``` ## First query With [Ollama](https://ollama.com/download) running locally and a model pulled (`ollama pull qwen3.8:27b`), no API key is needed: ```sql SET duckdb_ai_provider = 'ollama'; SET duckdb_ai_model = 'qwen3.8:27b'; SELECT ai_complete('Describe DuckDB in one sentence.'); SELECT ticket, ai_classify(ticket, ['billing', 'performance', 'other']) AS category FROM (VALUES ('I was charged twice.'), ('My query got slow.')) AS t(ticket); ``` Without a model setting, `provider := 'ollama'` uses `llama3.2`, so pull that model or set one as shown. For `ai_decide`, also run `ollama pull nimble` (Ollama 0.35 or later). To use a hosted provider, put its API key in an environment variable and follow the [provider guides](provider-guides.md). ## Find your way around | If you want to | Read | | --- | --- | | Look up a function, option or setting | [SQL function reference](functions.md) | | Configure a local or hosted model | [Provider guides](provider-guides.md) | | Copy a working workflow | [Cookbooks](cookbooks/index.md) | | Get typed answers with confidence from a decision model | [`ai_decide`](functions.md#ai_decidestate-questions-) and [typed decisions](cookbooks/jev-decisions.md) | | Write SQL for this extension from an agent | [Agent guide](agent-guide.md) | | Run AI enrichment in production | [Best practices](best-practices.md) | | Understand retries, caching and concurrency | [Runtime behavior](runtime-behavior.md) | | Know what data leaves your machine | [Security and data flow](security-data-flow.md) | | Serve AI functions to remote DuckDB clients | [Run on a Quack server](quack-server.md) | Agents and LLM tools can read the whole documentation set as plain text from [`llms.txt`](https://leonardovida.github.io/duckdb-ai/llms.txt) and [`llms-full.txt`](https://leonardovida.github.io/duckdb-ai/llms-full.txt). # Use DuckDB AI from coding agents Use this guide when an agent writes SQL against the `ai` extension or changes its source. Start with the installed API: source documentation may describe features that have not reached the DuckDB community package yet. ## Identify the package and installed API | Name | Meaning | | --- | --- | | `duckdb-ai` | Repository and project name | | `ai` | Extension name in `INSTALL` and `LOAD` | | `ai_*` | SQL function prefix | | `duckdb_ai_*` | DuckDB setting prefix | | `TYPE duckdb_ai` | DuckDB secret type | After installation, run these checks in the connection that will run AI SQL: ```sql LOAD ai; SELECT extension_name, extension_version, loaded FROM duckdb_extensions() WHERE extension_name = 'ai'; SELECT function_name, function_type, parameters, parameter_types, return_type FROM duckdb_functions() WHERE starts_with(function_name, 'ai_') ORDER BY function_name; ``` Use [the function reference](functions.md) for named options and full signatures. The runtime function catalog may display generic parameter names and separate overloads. Table functions such as `ai_usage()` and `ai_complete_record()` belong in `FROM`. `ai_extract_record()` and `ai_decide()` are scalars that return one `STRUCT` per input row. ## Configure the intended provider Read [the provider matrix](provider-guides.md#provider-matrix), then choose the provider, an available model, and any required base URL. Use an embedding model for `ai_embed` and `ai_similarity`; `ai_rerank` uses a completion model. Keep keys in a process environment or a secret manager. If a named DuckDB secret is used, pass `secret := 'name'` explicitly to make the intended configuration clear. Do not put credentials into prompts, `request_options`, source files, or test output. The hosted example in the [repository README](https://github.com/leonardovida/duckdb-ai#connect-a-hosted-provider) uses `OPENAI_API_KEY` from the process environment. Use explicit per-call `provider` and `model` options for isolated examples. For repeated queries, configure session defaults or a named secret. Session settings outrank the `MODEL` stored in a secret, so a leftover `SET duckdb_ai_model` sends that model to whatever provider the secret names. Check [the resolution order](functions.md#provider-settings-and-secrets) before combining them, and `RESET` settings you no longer need. For `ai_decide`, the session settings `duckdb_ai_model` and `duckdb_ai_base_url` are ignored, and so is the `MODEL` of a chat provider's secret. Pass `model := ...` or set `_DECISION_MODEL` (for example `OLLAMA_DECISION_MODEL`). Only `typesafe`, `cloudflare`, `perplexity`, `ollama` (0.35 or later) and `systemone` support it. ## Preview before making a model call This query constructs JSON locally and needs no API key: ```sql SELECT ai_completion_request_json( 'Reply OK', provider := 'openai', model := 'gpt-5.6-luna', max_tokens := 64 ); ``` Check the model, messages, and token-limit field. `ai_embedding_request_json` provides the equivalent preview for embeddings. A correct preview proves request construction, not authentication, model availability, or successful inference. For an authorized live check, use one short prompt and a bounded `max_tokens`. Reasoning models may spend the budget before producing visible text; use their documented reasoning controls rather than repeatedly increasing the cap. Inspect `ai_usage()` for status and token counts. A missing-key or insufficient- credit response does not verify a successful completion. ## Handle results and data access deliberately - Use `ai_try_complete` for `STRUCT(response, error)` results when individual failures should not abort a batch. Materialize results before separating successful and failed rows to avoid repeating model calls. - Use `ai_complete_json` with `response_schema` for validated JSON, or the record functions for typed output. `ai_extract` alone does not establish a JSON Schema contract. - Limit SQL assistant context with `include_tables` and review any sampled data before sending it to a hosted provider. `ai_sql` returns SQL text; `ai_query_data` runs generated SQL as a subquery. Read-only validation does not replace DuckDB permissions or external-access restrictions. - Loading the extension does not start inference. Model calls and configured logging endpoints can send data outside the process. A locally hosted model is private only within the deployment and logging configuration you chose. See [security and data flow](security-data-flow.md) for the data sent by each function family, and [runtime behavior](runtime-behavior.md) for retries, rate limits, caching, and cancellation. ## Avoid repeated or unbounded model calls - Model functions run once per input row. Bound the input in a subquery, for example `FROM (SELECT * FROM t ORDER BY id LIMIT 100)`. An outer `OFFSET` can still evaluate model calls for the rows it skips, so paginate inside the input subquery. - Model functions are `VOLATILE`: re-running a query, or referencing the same call in several places, calls the provider again. Save results with `CREATE TABLE ... AS SELECT ...` and read fields from that table. - `cache := true` (or `SET duckdb_ai_cache = true`) adds an opt-in in-memory response cache for the current DuckDB instance. It does not persist across processes. - Use `ai_count_tokens` and `ai_recommended_batch_size` to size batches before calling a rate-limited provider. ## Chain several AI steps When a task needs more than one model call per row: - First try to merge steps: `ai_decide` answers several typed questions in one request, and `ai_extract_record` returns several fields in one call. Keep `ai_redact` as its own step. - Call each model function once per row in a subquery, then read its fields outside: `SELECT r.response, r.error FROM (SELECT ai_try_complete(x) AS r ...)`. Writing `(ai_try_complete(x)).response, (ai_try_complete(x)).error` calls the model twice. - Do not reuse a model call's alias in the same `SELECT` (`SELECT ai_redact(x) AS clean, ai_summarize(clean)` is a binder error). Use a subquery or a step table. - Pass NULL to skip a row: NULL inputs make no call. `coalesce(x, '')` turns a skip into a paid request for an empty string (or an error for `ai_complete`). - Feed the next step only successful rows (`WHERE error IS NULL`), and put conditional steps behind `CASE` or `WHERE`; only matching rows are sent. - Save every step to a table keyed by row, input hash and a version string, and insert only missing rows, so reruns do not pay again. The [chain AI steps cookbook](cookbooks/chain-ai-steps.md) has a complete, tested pipeline. ## Machine-readable documentation The documentation site publishes two plain-text files for LLMs and agents, regenerated on every docs build: - [`llms.txt`](https://leonardovida.github.io/duckdb-ai/llms.txt): an index of every page with a one-line description. - [`llms-full.txt`](https://leonardovida.github.io/duckdb-ai/llms-full.txt): the full Markdown text of every page in one file. Fetch `llms-full.txt` when you need the complete function reference in context. It documents the source on `main`, so confirm the installed version as shown above before relying on a recently added function. ## Verify source changes Read the repository's [AGENTS.md](https://github.com/leonardovida/duckdb-ai/blob/main/AGENTS.md) and [CONTRIBUTING.md](https://github.com/leonardovida/duckdb-ai/blob/main/CONTRIBUTING.md) before editing. Keep tests deterministic: ```sh GEN=ninja make release GEN=ninja make test GEN=ninja make smoke ``` `make smoke` checks request and response shapes against local HTTP mocks. It does not prove that a live provider accepts the request or returns every possible response. For docs changes, run `npm run typecheck` and `npm run build` from `website/`. Report the version or commit tested and distinguish local mock checks, live checks, hosted CI, and published artifacts. Do not describe an unmerged change as available in community installs. # Cookbooks These cookbooks show practical duckdb-ai workflows. Most of them use the same `support_tickets` sample table, so you can move from one example to the next without changing context. A few create their own small tables, so that their tests have fixed inputs. The production workflows show how to combine duckdb-ai with DuckDB storage, file, and database extensions. Each page lists its provider, model and credential prerequisites at the top. ## Start with sample data - [Create the sample support tickets table](support-ticket-data.md): pasteable setup data with text, metadata, numeric, and timestamp columns. ## Production workflows - [Chain several AI steps in SQL](chain-ai-steps.md): translate, triage and summarize in separate saved steps, skip failed rows downstream, and rerun without paying twice. - [Evaluate a prompt or model before a batch run](evaluate-before-batch.md): compare candidates on a labeled sample for accuracy, per-label errors, confidence thresholds and cost per correct answer. - [Run production batch enrichment from S3 or Parquet](production-batch-enrichment.md): read bounded object-storage inputs, capture row-level failures, and persist outputs. - [Resume a local enrichment job](resumable-enrichment.md): checkpoint bounded batches in DuckDB and retry failed rows without repeating saved successes. - [Enrich rows from Postgres or MySQL safely](source-database-enrichment.md): attach source databases read-only, materialize local batches, and write only to reviewed staging targets. - [Write audited AI outputs to lakehouse tables](audited-lakehouse-output.md): keep run metadata, successful rows, rejected rows, and usage events together. - [Monitor AI usage, failures, and cost](usage-cost-observability.md): snapshot `ai_usage()` for latency, retry, failure, token, and cost reporting. - [Normalize messy documents into structured records](messy-document-intake.md): read JSON, Avro, and Excel inputs before extracting typed records. ## Try common workflows - [Enrich support tickets with AI text functions](support-ticket-enrichment.md): summarize, classify, filter, extract, redact, and translate table columns. - [Compare support tickets with embeddings](support-ticket-similarity.md): rank tickets by semantic similarity, and store embeddings once for repeated searches. - [Store embeddings in Lance for semantic search](lance-semantic-search.md): persist, index, search, and rerank reusable embeddings. - [Extract typed records from model output](structured-triage-records.md): get one typed `STRUCT` per row with `ai_extract_record`. - [Turn rows into typed decisions (ai_decide)](jev-decisions.md): save choices, scores and probabilities as SQL fields, batch rows with TypeSafe, then filter or export them. - [Route rows by decision confidence](decision-routing.md): accept confident `ai_decide` answers and send the rest to a chat-model fallback or a review table. - [Generate read-only SQL over local tables](sql-assistant.md): use local schema context with `ai_schema_prompt`, `ai_sql`, and `ai_query_data`. Provider setup is covered separately in the [provider guides](../provider-guides.md). # Create the sample support tickets table Use this small table to try the cookbook examples. It includes customer metadata, timestamps, free-text ticket fields, numeric account context, and an internal note column with realistic content for redaction tests. ```sql LOAD ai; CREATE OR REPLACE TABLE support_tickets AS SELECT * FROM ( VALUES ( 1001, 'acme_corp', 'enterprise', TIMESTAMP '2026-06-28 09:15:00', 'email', 'high', 120000, 2, 'en', 'Slack export delayed', 'The Slack export has been stuck for two hours and blocks the finance close.', 'Escalated by CSM. Mentions workspace ws_prod_eu and user pat@acme.test.' ), ( 1002, 'acme_corp', 'enterprise', TIMESTAMP '2026-06-28 10:40:00', 'chat', 'medium', 120000, 3, 'en', 'Invoice owner changed', 'Please update the billing owner before the next renewal invoice is issued.', 'Billing contact moved teams; keep renewal risk on the account watchlist.' ), ( 1003, 'northwind', 'startup', TIMESTAMP '2026-06-29 13:05:00', 'web', 'low', 18000, 5, 'nl', 'Vraag om documentatie', 'Kunt u een voorbeeld geven dat orderregels koppelt aan de verzendstatus?', 'Good candidate for a docs snippet; no production impact.' ), ( 1004, 'globex', 'business', TIMESTAMP '2026-06-30 08:20:00', 'email', 'high', 64000, 1, 'en', 'Query regression after upgrade', 'Dashboard query that used to finish in 8 seconds now times out after the upgrade.', 'Likely performance regression. Ask engineering to inspect query_id q_8842.' ) ) AS t( ticket_id, customer_id, customer_tier, created_at, channel, priority, arr_usd, satisfaction_score, language, subject, body, internal_note ); ``` Check that the table is ready: ```sql SELECT count(*) AS ticket_count, min(created_at) AS first_ticket, max(created_at) AS last_ticket FROM support_tickets; ``` Expected shape: | ticket_count | first_ticket | last_ticket | | --- | --- | --- | | `4` | `2026-06-28 09:15:00` | `2026-06-30 08:20:00` | Ticket 1003 is written in Dutch, so the translation examples have something to translate. A few cookbooks, such as [chaining AI steps](chain-ai-steps.md) and [typed decisions](jev-decisions.md), create their own small tables because their tests need fixed inputs. Next: [enrich support tickets with AI text functions](support-ticket-enrichment.md). # Chain several AI steps in SQL Use this cookbook when one model call is not enough: for example, translate a ticket, then triage it, then write a note for the urgent ones. Every step is plain SQL, and every step is saved, so a rerun only calls the model for rows that are new, changed, or failed last time. Prerequisites: - Steps 1 and 3 use the completion provider and model from your session settings (see the [quickstart](../index.md#first-query)). For a hosted provider, set its key in the environment, for example `OPENAI_API_KEY`. - Step 2 uses a different model: the local Ollama decision model `nimble` through `ai_decide`. It needs Ollama 0.35 or later and `ollama pull nimble`. `ai_decide` ignores the session model settings, so the chat model from steps 1 and 3 is never sent to it. ## How chained calls behave These behaviors are covered by the extension's tests, so you can rely on them: | Pattern | Model calls | | --- | --- | | A NULL input to any model function | None. The result is NULL. | | A step reads only rows where the previous step succeeded | Only those rows. Failed rows are never sent again downstream. | | `CASE WHEN THEN ai_...(x) END` | Only rows where the condition is true. | | `WHERE ` before a model function in `SELECT` | Only rows that pass the filter. | | Reading several fields of a call from a subquery: `SELECT r.a, r.b FROM (SELECT ai_...(x) AS r ...)` | One per row. | Avoid these patterns: | Pattern | Problem | Write instead | | --- | --- | --- | | `(ai_try_complete(x)).response, (ai_try_complete(x)).error` | Two calls per row. | Call once in a subquery and read both fields outside it. | | `SELECT ai_redact(x) AS clean, ai_summarize(clean)` | Binder error: an alias with side effects cannot be reused in the same `SELECT`. | Compute `clean` in a subquery or an earlier step table. | | `ai_summarize(coalesce(x, ''))` | An empty string still sends a paid request (only `ai_complete` raises an error for it). | Pass NULL through. NULL inputs make no call. | | `... LIMIT 100 OFFSET 1000` on the outer query | `OFFSET` can still call the model for the skipped rows. | Page inside the input subquery. | | Rerunning a `CREATE OR REPLACE TABLE ... AS SELECT ai_...` | Every row is sent again. | Insert only rows missing from the step table, as below. | ## Combine steps before chaining them Every step costs one request per row, so first check whether one call can do the work of several: - [`ai_decide`](../functions.md#ai_decidestate-questions-) answers several typed questions (team, urgency, sentiment) in one request instead of separate `ai_classify`, `ai_filter` and `ai_score` calls. See [typed decisions](jev-decisions.md) and [routing by confidence](decision-routing.md). - `ai_extract_record` with a multi-field JSON Schema returns a category, a score and a short summary in one call. Keep a step separate when its input must be different: for example, run `ai_redact` on its own so that raw text never reaches a general model. ## Create sample data This page uses its own small `tickets` table with a non-English row and a NULL body, so you can see both cases being handled. ```sql CREATE OR REPLACE TABLE tickets AS SELECT * FROM (VALUES (1, 'Le paiement a été débité deux fois.'), (2, 'The dashboard is down for every user since 9am.'), (3, 'Can you update the billing email on our account?'), (4, NULL) ) AS t(ticket_id, body); SET VARIABLE pipeline_version = 'triage-v1'; ``` `pipeline_version` names the prompts and models in use. Change it when you change a prompt or a model, and the next run recomputes every row. ## Step 1: normalize the text, keeping errors Each step table stores its output and the error, keyed by row, input hash and pipeline version, so a changed ticket or a new version is recomputed in every step. The input subquery selects only rows without a successful result, so a rerun retries failures and skips finished rows. `LIMIT` stays inside the input subquery to bound each run. ```sql CREATE TABLE IF NOT EXISTS step_normalize ( ticket_id BIGINT, input_hash VARCHAR, version VARCHAR, english VARCHAR, error VARCHAR, PRIMARY KEY (ticket_id, input_hash, version) ); INSERT OR REPLACE INTO step_normalize SELECT ticket_id, input_hash, getvariable('pipeline_version'), result.response, result.error FROM ( SELECT ticket_id, md5(body) AS input_hash, ai_try_complete('Translate this support ticket to plain English. Return only the text: ' || body, max_tokens := 200) AS result FROM ( SELECT t.* FROM tickets t ANTI JOIN (SELECT * FROM step_normalize WHERE error IS NULL) done ON done.ticket_id = t.ticket_id AND done.input_hash = md5(t.body) AND done.version = getvariable('pipeline_version') WHERE t.body IS NOT NULL ORDER BY t.ticket_id LIMIT 500 ) ); ``` ## Step 2: answer several questions in one call `ai_decide` reads only rows that step 1 translated successfully, and asks two questions in a single request per row. `team` comes back as a label with `team_confidence`, and `urgent` as the probability of yes. With `on_error := 'null'`, a failed request leaves `team` NULL, so the next run retries that row. ```sql CREATE TABLE IF NOT EXISTS step_triage ( ticket_id BIGINT, input_hash VARCHAR, version VARCHAR, team VARCHAR, team_confidence DOUBLE, urgent DOUBLE, PRIMARY KEY (ticket_id, input_hash, version) ); INSERT OR REPLACE INTO step_triage SELECT ticket_id, input_hash, version, d.team, d.team_confidence, d.urgent FROM ( SELECT n.ticket_id, n.input_hash, n.version, ai_decide(n.english, { team: MAP {'billing': 'Payments, invoices and refunds', 'technical': 'Errors, outages and slow queries', 'other': 'Anything else'}, urgent: MAP {'true': 'Needs action today', 'false': 'Can wait'} }, provider := 'ollama', on_error := 'null') AS d FROM step_normalize n ANTI JOIN (SELECT * FROM step_triage WHERE team IS NOT NULL) done USING (ticket_id, input_hash, version) WHERE n.error IS NULL AND n.version = getvariable('pipeline_version') ); ``` ## Step 3: call a model only where it is needed The filter runs before the model call, so only urgent technical tickets are summarized. ```sql CREATE TABLE IF NOT EXISTS step_escalation ( ticket_id BIGINT, input_hash VARCHAR, version VARCHAR, note VARCHAR, PRIMARY KEY (ticket_id, input_hash, version) ); INSERT OR REPLACE INTO step_escalation SELECT ticket_id, input_hash, version, ai_summarize(english, on_error := 'null') FROM ( SELECT n.ticket_id, n.input_hash, n.version, n.english FROM step_triage tr JOIN step_normalize n USING (ticket_id, input_hash, version) ANTI JOIN (SELECT * FROM step_escalation WHERE note IS NOT NULL) done USING (ticket_id, input_hash, version) WHERE n.error IS NULL AND tr.version = getvariable('pipeline_version') AND tr.team = 'technical' AND tr.urgent >= 0.5 ); ``` ## Read the results and check progress The first query joins the step tables back to the current ticket text, so a ticket whose body changed shows only its latest result. The second counts the model calls of this run by function. ```sql SELECT n.ticket_id, n.english, tr.team, tr.urgent, e.note, n.error FROM step_normalize n LEFT JOIN step_triage tr USING (ticket_id, input_hash, version) LEFT JOIN step_escalation e USING (ticket_id, input_hash, version) SEMI JOIN tickets t ON t.ticket_id = n.ticket_id AND md5(t.body) = n.input_hash WHERE n.version = getvariable('pipeline_version') ORDER BY n.ticket_id; SELECT function_name, count(*) AS calls, count(*) FILTER (status <> 'ok') AS failures, sum(total_tokens) AS tokens FROM ai_usage() GROUP BY function_name ORDER BY function_name; ``` Run the same statements again to continue. Rows that already succeeded are not sent again, so a second run only retries failed rows and picks up new tickets. `ai_usage()` lives in memory for the current DuckDB instance and keeps only the latest 1,024 events. Copy it into a table after each run if you need cost history, as in [usage and cost monitoring](usage-cost-observability.md). ## Options per step - Shared options, such as the provider, timeouts and retries, belong in session settings (`SET duckdb_ai_retry_count = 2`). - Options that differ by step go on the call. To change them without editing the query, read them from a variable: run `SET VARIABLE summary_tokens = 120;` first, then pass `max_tokens := getvariable('summary_tokens')`. An unset variable is NULL, and `max_tokens` rejects NULL. - `CREATE EXTERNAL MODEL` profiles bundle a provider, model and credential under one name, so each step can use `profile := 'small_model'` or `profile := 'large_model'`. For a Python driver that loops over batches and stops on a budget, see [resumable enrichment](resumable-enrichment.md). Before a large run, check the prompts and models on a labeled sample as shown in [evaluate before a batch run](evaluate-before-batch.md). ## Clean up The step tables are meant to persist between runs. To start over, or to remove the example, run `DROP TABLE IF EXISTS step_escalation, step_triage, step_normalize, tickets;` in the same database. # Evaluate a prompt or model on a labeled sample before a batch run Use this cookbook before you run a prompt over a whole table. You label a small sample by hand, run each candidate prompt or model on it, and compare accuracy, mistakes and cost in SQL. Each run is saved in a table, so you pay for every candidate once and can rerun the analysis for free. ## Prerequisites - A completion provider. The examples use OpenAI `gpt-5.6-luna`, so set `OPENAI_API_KEY` in the environment before starting DuckDB. See the [provider guides](../provider-guides.md). - Optional, for the decision-model candidate: Ollama 0.35 or later and `ollama pull nimble`. - The [sample `support_tickets` table](support-ticket-data.md). - A persistent database file, such as `duckdb eval.duckdb`, if you want to keep the results across sessions. Turn on the built-in price list, so `ai_usage()` fills `estimated_cost_usd`. Leave the response cache (`duckdb_ai_cache`) off, its default, so every candidate pays for its own requests and the cost comparison is fair: ```sql SET duckdb_ai_use_builtin_model_prices = true; ``` ## Create the labeled sample Write down the answer you expect for each row. The four sample tickets keep this page short. For a real decision, label 50 to 200 rows that cover every label and the hard cases you know about. Keep each candidate under 1,024 rows, because `ai_usage()` keeps only the latest 1,024 events. ```sql CREATE OR REPLACE TABLE eval_gold AS SELECT * FROM ( VALUES (1001, 'integration'), (1002, 'billing'), (1003, 'documentation'), (1004, 'performance') ) AS t(ticket_id, expected_label); CREATE OR REPLACE TABLE eval_sample AS SELECT t.ticket_id, t.subject || chr(10) || t.body AS ticket_text, g.expected_label FROM support_tickets AS t JOIN eval_gold AS g USING (ticket_id); ``` ## Run each candidate once and save it Change one thing per candidate, such as the prompt or the model, so you know what caused a difference. For each candidate: 1. Clear the usage buffer. 2. Save the predictions with `CREATE TABLE IF NOT EXISTS ... AS`. If the table already exists, DuckDB skips the statement and makes no calls, so rerunning the block never pays twice. 3. Save that candidate's usage events. Candidate A sends the labels only: ```sql SELECT * FROM ai_clear_usage(); CREATE TABLE IF NOT EXISTS eval_run_a AS SELECT 'luna-labels-only' AS candidate, ticket_id, ai_classify( ticket_text, ['billing', 'performance', 'integration', 'documentation', 'other'], provider := 'openai', model := 'gpt-5.6-luna', on_error := 'null' ) AS predicted_label FROM eval_sample; CREATE TABLE IF NOT EXISTS eval_usage_a AS SELECT 'luna-labels-only' AS candidate, * FROM ai_usage(); ``` Candidate B keeps the model and changes the prompt. It adds a description per label and one instruction: ```sql SELECT * FROM ai_clear_usage(); CREATE TABLE IF NOT EXISTS eval_run_b AS SELECT 'luna-described' AS candidate, ticket_id, ai_classify( ticket_text, ['billing', 'performance', 'integration', 'documentation', 'other'], label_descriptions := '{ "billing": "invoices, payments, refunds, billing contacts", "performance": "slow queries, timeouts, regressions", "integration": "exports, imports, connections to other tools", "documentation": "requests for examples or docs", "other": "anything else" }', instructions := 'The ticket may be in any language.', provider := 'openai', model := 'gpt-5.6-luna', on_error := 'null' ) AS predicted_label FROM eval_sample; CREATE TABLE IF NOT EXISTS eval_usage_b AS SELECT 'luna-described' AS candidate, * FROM ai_usage(); ``` To compare models instead, copy candidate A, give it a new candidate name and table names, and change only `model :=`, for example to `gpt-5.4-mini`. Candidate C is optional. It uses a decision model, which also returns a confidence for each label, so you can test thresholds later: ```sql SELECT * FROM ai_clear_usage(); CREATE TABLE IF NOT EXISTS eval_run_c AS SELECT 'nimble-decision' AS candidate, ticket_id, d.label AS predicted_label, d.label_confidence AS confidence FROM ( SELECT ticket_id, ai_decide( ticket_text, {label: MAP { 'billing': 'Invoices, payments, refunds, billing contacts', 'performance': 'Slow queries, timeouts, regressions', 'integration': 'Exports, imports, connections to other tools', 'documentation': 'Requests for examples or docs', 'other': 'Anything else' }}, provider := 'ollama', model := 'nimble', on_error := 'null' ) AS d FROM eval_sample ); CREATE TABLE IF NOT EXISTS eval_usage_c AS SELECT 'nimble-decision' AS candidate, * FROM ai_usage(); ``` Combine the saved runs. If you skipped candidate C, remove its two lines: ```sql CREATE OR REPLACE VIEW eval_predictions AS SELECT * FROM eval_run_a UNION ALL BY NAME SELECT * FROM eval_run_b UNION ALL BY NAME SELECT * FROM eval_run_c; CREATE OR REPLACE VIEW eval_usage AS SELECT * FROM eval_usage_a UNION ALL BY NAME SELECT * FROM eval_usage_b UNION ALL BY NAME SELECT * FROM eval_usage_c; ``` Every query below reads these saved tables and makes no provider calls. ## Compare accuracy A failed call leaves `predicted_label` NULL and counts as wrong: ```sql SELECT p.candidate, count(*) AS sample_rows, count(*) FILTER (WHERE p.predicted_label = s.expected_label) AS correct, count(*) FILTER (WHERE p.predicted_label IS NULL) AS failed, round(count(*) FILTER (WHERE p.predicted_label = s.expected_label) / count(*), 3) AS accuracy FROM eval_predictions AS p JOIN eval_sample AS s USING (ticket_id) GROUP BY p.candidate ORDER BY accuracy DESC; ``` Result: one row per candidate with `accuracy DOUBLE` from 0 to 1. ## Find the errors per label Overall accuracy can hide a label that always fails. Count the correct answers per expected label: ```sql SELECT p.candidate, s.expected_label, count(*) AS sample_rows, count(*) FILTER (WHERE p.predicted_label = s.expected_label) AS correct FROM eval_predictions AS p JOIN eval_sample AS s USING (ticket_id) GROUP BY ALL ORDER BY s.expected_label, p.candidate; ``` Then read the mistakes themselves: ```sql SELECT p.candidate, s.ticket_id, s.expected_label, p.predicted_label, s.ticket_text FROM eval_predictions AS p JOIN eval_sample AS s USING (ticket_id) WHERE p.predicted_label IS DISTINCT FROM s.expected_label ORDER BY s.ticket_id, p.candidate; ``` ## Estimate the cost per correct answer `estimated_cost_usd` comes from `ai_model_prices()`. Local models and models without a price row get NULL, so the query also counts unpriced requests. Events with unknown token counts report a NULL `total_tokens`, which `sum()` skips. ```sql WITH quality AS ( SELECT p.candidate, count(*) AS sample_rows, count(*) FILTER (WHERE p.predicted_label = s.expected_label) AS correct FROM eval_predictions AS p JOIN eval_sample AS s USING (ticket_id) GROUP BY p.candidate ), cost AS ( SELECT candidate, count(*) AS requests, sum(total_tokens) AS known_tokens, sum(estimated_cost_usd) AS cost_usd, count(*) FILTER (WHERE estimated_cost_usd IS NULL) AS unpriced_requests, avg(elapsed_ms) AS avg_elapsed_ms FROM eval_usage GROUP BY candidate ) SELECT q.candidate, round(q.correct / q.sample_rows, 3) AS accuracy, c.requests, c.known_tokens, c.cost_usd, c.cost_usd / nullif(q.correct, 0) AS cost_per_correct_usd, c.cost_usd / q.sample_rows * 100000 AS est_cost_per_100k_rows_usd, c.unpriced_requests, c.avg_elapsed_ms FROM quality AS q JOIN cost AS c USING (candidate) ORDER BY cost_per_correct_usd NULLS LAST; ``` `est_cost_per_100k_rows_usd` scales the sample cost linearly. It is a rough guide: longer rows in the full table cost more. Check the full table's size with `ai_count_tokens` as in [production batch enrichment](production-batch-enrichment.md#estimate-a-starting-batch-size). ## Pick a confidence threshold Candidate C stored a confidence per row. For each threshold, see how many rows you would accept automatically and how accurate those rows are: ```sql SELECT t.threshold, count(*) FILTER (WHERE p.confidence >= t.threshold) AS accepted_rows, count(*) FILTER (WHERE p.confidence >= t.threshold) / count(*) AS accepted_share, count(*) FILTER (WHERE p.confidence >= t.threshold AND p.predicted_label = s.expected_label) / nullif(count(*) FILTER (WHERE p.confidence >= t.threshold), 0) AS accepted_accuracy FROM eval_run_c AS p JOIN eval_sample AS s USING (ticket_id) CROSS JOIN (VALUES (0.5), (0.7), (0.8), (0.9)) AS t(threshold) GROUP BY t.threshold ORDER BY t.threshold; ``` A higher threshold usually raises `accepted_accuracy` and lowers `accepted_share`. The rows below the threshold go to a fallback or to review, as in [route rows by decision confidence](decision-routing.md). ## Decide 1. Drop every candidate whose accuracy, or accuracy for an important label, is below what the job needs. 2. From the rest, pick the lowest `cost_per_correct_usd`. 3. Run the batch with exactly that prompt and model, and record the candidate name with the output, for example as the `pipeline_version` in [chain several AI steps](chain-ai-steps.md). To test a new idea later, add a candidate D with its own tables and add it to the two views. Candidates A to C are not called again. ## Clean up ```sql DROP VIEW IF EXISTS eval_predictions; DROP VIEW IF EXISTS eval_usage; DROP TABLE IF EXISTS eval_run_a; DROP TABLE IF EXISTS eval_run_b; DROP TABLE IF EXISTS eval_run_c; DROP TABLE IF EXISTS eval_usage_a; DROP TABLE IF EXISTS eval_usage_b; DROP TABLE IF EXISTS eval_usage_c; DROP TABLE IF EXISTS eval_sample; DROP TABLE IF EXISTS eval_gold; ``` ## Related - [Typed decisions](jev-decisions.md) includes a TypeSafe evaluator script that also reports precision, recall and F1 per label. - [Monitor AI usage, failures, and cost](usage-cost-observability.md) - [`ai_model_prices()` reference](../functions.md#ai_model_prices) # Run production batch enrichment from S3 or Parquet Use this cookbook when source rows already live in object storage and the output should be written back as durable files. The pattern is: 1. Read a bounded Parquet slice. 2. Estimate the batch size. 3. Call the provider with row-level failure capture. 4. Split successful rows from rejected rows. 5. Persist outputs and usage data. ## Prerequisites - Set `OPENAI_API_KEY` in the environment before starting DuckDB. The `openai_ai` secret below stores the provider and model, not the key. See the [provider guides](../provider-guides.md). - Configure object-storage credentials with DuckDB secrets. - Use a stable `run_id` for every production job attempt. - Check the prompt and model on a labeled sample first, as in [evaluate before a batch run](evaluate-before-batch.md). ```sql INSTALL ai FROM community; LOAD ai; INSTALL json; LOAD json; INSTALL httpfs; LOAD httpfs; INSTALL aws; LOAD aws; CREATE OR REPLACE SECRET s3_prod ( TYPE s3, PROVIDER credential_chain, REGION 'us-east-1' ); CREATE OR REPLACE SECRET openai_ai ( TYPE duckdb_ai, AI_PROVIDER 'openai', MODEL 'gpt-5.6-luna' ); SET VARIABLE run_id = 'support-triage-2026-07-07T100000Z'; -- Start the job with an empty usage buffer, so the usage snapshot covers only this run. SELECT * FROM ai_clear_usage(); ``` For S3-compatible storage such as R2, GCS interoperability, MinIO, or lakeFS, use the matching DuckDB secret type or endpoint options for your environment. ## Read a bounded input slice Read Parquet files by glob, keep the input filename for lineage, and materialize the job input locally before calling the provider. This prevents repeated scans of the remote object store if you rerun downstream steps. The `LIMIT` caps how many rows, and so how many provider calls, one run can make. ```sql CREATE OR REPLACE TEMP TABLE job_input AS SELECT ticket_id, customer_id, priority, status, subject, body, filename AS source_file, getvariable('run_id') AS run_id FROM read_parquet( 's3://support-prod/tickets/dt=2026-07-07/*.parquet', union_by_name = true, filename = true, hive_partitioning = true ) WHERE status = 'open' AND priority IN ('high', 'urgent') ORDER BY ticket_id LIMIT 5000; ``` Check the row count before provider calls: ```sql SELECT count(*) AS input_rows FROM job_input; ``` ## Estimate a starting batch size Use the local token estimator before a large run. Replace the limit values with the provider limits you operate under. ```sql WITH prompt_stats AS ( SELECT avg(ai_count_tokens(subject || chr(10) || body)) AS input_tokens_per_row FROM job_input ) SELECT ai_recommended_batch_size( input_tokens_per_row, 250, 200000, 500 ) AS recommended_rows_per_batch FROM prompt_stats; ``` Use the result to adjust the `LIMIT` for a first production run, or to split the input into multiple scheduled chunks. ## Enrich rows with failure capture Use `ai_try_complete` so one bad row does not fail the whole query. Ask for JSON so the successful rows can be parsed and validated downstream. `result` is a `STRUCT` with `response` and `error`. ```sql CREATE OR REPLACE TEMP TABLE ai_attempts AS SELECT run_id, ticket_id, customer_id, priority, source_file, ai_try_complete( 'Return compact JSON with keys summary, product_area, urgency_score. ' || 'Use urgency_score from 1 to 5.' || chr(10) || 'Subject: ' || subject || chr(10) || 'Body: ' || body, secret := 'openai_ai', response_format := 'json_object', max_tokens := 250, retry_count := 2, retry_backoff_ms := 1000, max_concurrent_requests := 4, min_request_interval_ms := 100, token_limit_per_minute := 200000 ) AS result FROM job_input; ``` `urgency_score` is a fixed scale. With a decision provider, you can skip the JSON parsing for that field: `ai_decide(body, {urgency_score: ['Routine', 'Minor', 'Degraded', 'Severe', 'Production down']}, provider := 'ollama', on_error := 'null')` returns `urgency_score DOUBLE` (a level from 0 to 4, so add 1 for the 1 to 5 scale) and `urgency_score_confidence`. `ai_decide` has no capture mode, so treat a NULL result as rejected. See [route rows by decision confidence](decision-routing.md). Keep `ai_try_complete` for free-text fields such as `summary`. ## Split successes and rejected rows Keep successful rows and rejected rows as separate datasets. Rejected rows should retain the provider error and enough lineage to replay only those rows. `response_format := 'json_object'` asks for JSON but does not check the reply. A reply that is not valid JSON has `error = NULL`, and casting it to `JSON` fails the whole statement. Filter with `json_valid` so such rows land in the rejected table instead. ```sql CREATE OR REPLACE TEMP TABLE ai_successes AS SELECT run_id, ticket_id, customer_id, priority, source_file, result.response::JSON AS response_json, now() AS loaded_at FROM ai_attempts WHERE result.error IS NULL AND json_valid(result.response); CREATE OR REPLACE TEMP TABLE ai_rejected_rows AS SELECT run_id, ticket_id, customer_id, priority, source_file, coalesce(result.error, 'missing or invalid JSON response') AS error, now() AS loaded_at FROM ai_attempts WHERE result.error IS NOT NULL OR NOT coalesce(json_valid(result.response), false); ``` ## Persist outputs Write successful rows, rejected rows, and usage events to separate locations. `PARTITION_BY (run_id)` writes each run to its own `run_id=/` folder, so a retry of one run does not touch the files of other runs. Read the folders back with `read_parquet('.../run_id=*/*.parquet', hive_partitioning = true)`. ```sql COPY ai_successes TO 's3://support-prod/ai/ticket_triage' ( FORMAT parquet, COMPRESSION zstd, ROW_GROUP_SIZE 100000, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true ); COPY ai_rejected_rows TO 's3://support-prod/ai/ticket_triage_rejected' ( FORMAT parquet, COMPRESSION zstd, ROW_GROUP_SIZE 100000, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true ); ``` `ai_usage()` keeps only the latest 1,024 events. Check that none were dropped before you save them. If `dropped_events` is above 0, the usage file is incomplete: run smaller batches and snapshot after each one. `ai_usage_totals()` keeps its counters even when events are dropped, so save it with the run: ```sql SELECT max(dropped_events) AS dropped_events FROM ai_usage_summary(); COPY ( SELECT getvariable('run_id') AS run_id, now() AS captured_at, * FROM ai_usage() ) TO 's3://support-prod/ai/usage' ( FORMAT parquet, COMPRESSION zstd, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true ); COPY ( SELECT getvariable('run_id') AS run_id, now() AS captured_at, * FROM ai_usage_totals() ) TO 's3://support-prod/ai/usage_totals' ( FORMAT parquet, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true ); ``` After the run is durably captured, clear the in-process usage buffer if the same DuckDB process will run another job: ```sql SELECT * FROM ai_clear_usage(); ``` ## Resume after a process restart The temporary tables above live only for the current connection. For a local, single-writer job that skips previously successful rows after reopening DuckDB, use the [resumable enrichment example](resumable-enrichment.md). ## Related cookbooks - [Enrich rows from Postgres or MySQL](source-database-enrichment.md) - [Write audited AI outputs to lakehouse tables](audited-lakehouse-output.md) - [Monitor AI usage, failures, and cost](usage-cost-observability.md) - [Normalize messy documents](messy-document-intake.md) ## Learn more - [DuckDB S3 API support](https://duckdb.org/docs/current/core_extensions/httpfs/s3api) - [DuckDB AWS extension](https://duckdb.org/docs/current/core_extensions/aws) - [DuckDB Parquet tips](https://duckdb.org/docs/current/data/parquet/tips) - [Reading multiple files](https://duckdb.org/docs/current/data/multiple_files/overview) # Resume a local enrichment job Use the source-checkout example `examples/resumable_enrichment.py` when a local batch job must resume after closing DuckDB without calling the provider again for saved successful rows. This is a single-writer example using the existing `ai_try_complete` API and a persistent DuckDB database. ## Prepare the job Use stable source identifiers and non-null prompts. Keep the checkpoint database on durable local storage. Configure credentials through the provider's environment variables before starting the process, following the [provider guide](../provider-guides.md). Never put credentials in prompts or configuration versions. The checkpoint identity includes the source identifier, prompt hash and explicit configuration version. Change the configuration version when changing anything that affects results, including provider, model, model revision, endpoint, prompt policy or generation settings. A moving model alias cannot automatically invalidate stored results. Changing the prompt itself creates a new identity. From the repository root, create `input.csv`: ```csv source_id,prompt 1,Summarize in one sentence: I was charged twice. 2,Summarize in one sentence: My query became slow. ``` Use Python 3 and a DuckDB shell with `ai` installed, or the source build at `build/release/duckdb`. The command below uses the local Ollama model `qwen3.8:27b`. Pull it first, which is a large download, or pass a model you already have with `--model`: ```sh ollama pull qwen3.8:27b ``` Then run one batch: ```sh python3 examples/resumable_enrichment.py \ --duckdb ./build/release/duckdb \ --database ./enrichment.duckdb \ --input ./input.csv \ --provider ollama \ --model qwen3.8:27b \ --config-version triage-v1 \ --batch-size 100 ``` This command calls your configured model. The provider must be running or accessible. Hosted providers may charge for these calls. The optional `--base-url` overrides the endpoint, and credentials remain in environment variables. Provider, model and explicit endpoint are included automatically in the configuration fingerprint. Keep `--config-version` for changes the example cannot detect, such as a model revision behind an alias. ## Resume and inspect results Each invocation selects a bounded batch of pending work before evaluating the model function. Results are materialized once, then saved. Run the same command again to process remaining rows and retry failures. Reopening the same checkpoint database retains successful rows and avoids repeating their calls. A provider error remains eligible for a later attempt. A successful provider response means the API returned text, not that the content is correct. Add the validation required by your application before using model output for decisions. Older prompt/configuration versions remain in the checkpoint database for inspection. Untouched rows are selected before failed rows, followed by the least-attempted failures, so permanent failures do not block new work. Each invocation prints `attempted`, `succeeded` and `failed` counts. Zero attempted rows means every current input identity already has a saved success. Inspect the checkpoint with the same DuckDB shell: ```sh ./build/release/duckdb enrichment.duckdb -c \ 'SELECT source_id, config_version, response, error, attempts FROM enrichment_results ORDER BY source_id;' ``` This table includes historical versions. Filter by `config_hash` and `prompt_hash` when adapting the example into a downstream join. The example replaces a failed identity with its latest attempt, rather than keeping an attempt-by-attempt audit log. Duplicate identifiers or missing prompts fail before provider calls. Run the local HTTP mock experiment without credentials or live model calls: ```sh python3 test/smoke/resumable_enrichment_smoke.py --duckdb ./build/release/duckdb ``` On POSIX systems, it checks bounded batches, failure retries, persistence across new processes, prompt/configuration invalidation, input rejection and rollback after killing a process midway through a batch. The crash check also confirms that the first provider call is repeated on restart when its result was not committed. ## Persist batch usage The example also commits `enrichment_batch_usage` with each batch's results. It records the configuration hash, completion time, provider row events, transport attempts (including retries), failures, cache hits, known reported tokens, and unknown-token event counts from `ai_usage_totals()`. These snapshots survive reopening and roll back with an uncommitted batch. Treat known tokens as partial when unknown events are present; database rollback cannot undo provider charges. ```sql SELECT config_hash, sum(request_attempts) AS attempts, sum(known_total_tokens) AS known_tokens, sum(unknown_token_events) AS unknown_events FROM enrichment_batch_usage GROUP BY config_hash; ``` ## Understand the limits - Run one writer at a time. This example does not coordinate workers or schedule jobs. - A crash after the provider responds but before the batch commits can repeat requests on restart. Database transactions cannot roll back an external model call or its cost. Smaller batches reduce the amount of uncommitted work. - The database stores prompts, responses and error text. Apply the same access and retention controls as the source data. A prompt hash is an identity aid, not a privacy boundary. - Failed rows can be retried indefinitely across invocations. Inspect persistent errors before repeatedly running the job, and use a separate remediation queue for permanent failures when adapting this example. For a SQL-only version of the same idea, where each step table skips rows that already succeeded, see [chain several AI steps](chain-ai-steps.md). For object-storage exports and usage capture, see [production batch enrichment](production-batch-enrichment.md), [Postgres and MySQL enrichment](source-database-enrichment.md) and [usage monitoring](usage-cost-observability.md). # Enrich rows from Postgres or MySQL safely Use this cookbook when production rows live in an OLTP database. The safe pattern is to attach the source read-only, materialize a bounded local working set, run AI enrichment locally, and write results to an explicit staging target. ## Prerequisites - Use read-only database credentials for the source attachment. - Set `OPENAI_API_KEY` in the environment before starting DuckDB. The `openai_ai` secret below stores the provider and model, not the key. See the [provider guides](../provider-guides.md). - Configure object-storage credentials if the enriched output will be exported to S3-compatible storage. - Decide whether enriched rows should be exported to files or inserted into a separate staging table in the source database. ```sql INSTALL ai FROM community; LOAD ai; INSTALL json; LOAD json; INSTALL postgres; LOAD postgres; INSTALL mysql; LOAD mysql; INSTALL httpfs; LOAD httpfs; INSTALL aws; LOAD aws; CREATE OR REPLACE SECRET openai_ai ( TYPE duckdb_ai, AI_PROVIDER 'openai', MODEL 'gpt-5.6-luna' ); CREATE OR REPLACE SECRET s3_prod ( TYPE s3, PROVIDER credential_chain, REGION 'us-east-1' ); SET VARIABLE run_id = 'source-enrichment-2026-07-07T100000Z'; -- Start the job with an empty usage buffer, so the usage snapshot covers only this run. SELECT * FROM ai_clear_usage(); ``` ## Attach source databases read-only Store source credentials in DuckDB secrets. Avoid putting passwords in connection strings, because failed connection errors can print the full string. ```sql CREATE OR REPLACE SECRET app_postgres ( TYPE postgres, HOST 'postgres.internal.example', PORT 5432, DATABASE 'app', USER 'readonly_ai_worker', PASSWORD '' ); ATTACH '' AS app_pg ( TYPE postgres, SECRET app_postgres, READ_ONLY, SCHEMA 'public' ); ``` For MySQL, use the same pattern: ```sql CREATE OR REPLACE SECRET app_mysql ( TYPE mysql, HOST 'mysql.internal.example', PORT 3306, DATABASE 'app', USER 'readonly_ai_worker', PASSWORD '' ); ATTACH '' AS app_mysql ( TYPE mysql, SECRET app_mysql, READ_ONLY ); ``` ## Materialize a local working set Filter the source table before any provider calls. This keeps scans predictable and avoids re-reading the OLTP database during retry or export steps. ```sql CREATE OR REPLACE TEMP TABLE source_ticket_batch AS SELECT ticket_id, customer_id, priority, status, subject, body, updated_at, getvariable('run_id') AS run_id FROM app_pg.public.support_tickets WHERE status = 'open' AND priority IN ('high', 'urgent') ORDER BY updated_at LIMIT 1000; ``` If the source schema changed while the same DuckDB connection is running, clear the extension schema cache before re-querying: ```sql SELECT pg_clear_cache(); ``` Use `mysql_clear_cache()` for attached MySQL sources. ## Enrich the local batch Run provider calls only after the batch is local. Use `ai_try_complete` so bad rows become rejected rows instead of aborting the whole job. `result` is a `STRUCT` with `response` and `error`. ```sql CREATE OR REPLACE TEMP TABLE source_ticket_attempts AS SELECT run_id, ticket_id, customer_id, priority, ai_try_complete( 'Return JSON with keys summary, next_action, risk_level. ' || 'risk_level must be low, medium, or high.' || chr(10) || 'Subject: ' || subject || chr(10) || 'Body: ' || body, secret := 'openai_ai', response_format := 'json_object', retry_count := 2, retry_backoff_ms := 1000, max_concurrent_requests := 4, token_limit_per_minute := 200000 ) AS result FROM source_ticket_batch; ``` `risk_level` is a fixed scale. With a decision provider, you can skip the JSON parsing for that field: `ai_decide(body, {risk_level: ['low', 'medium', 'high']}, provider := 'ollama', on_error := 'null')` returns `risk_level DOUBLE` (0 to 2) and `risk_level_confidence`. `ai_decide` has no capture mode, so treat a NULL result as rejected. See [route rows by decision confidence](decision-routing.md). Keep `ai_try_complete` for free-text fields such as `summary` and `next_action`. ## Export or stage the output For the lowest-risk production path, export success and rejection files and let a separate application-owned process load them into the source system. `response_format := 'json_object'` asks for JSON but does not check the reply. A reply that is not valid JSON has `error = NULL`, and casting it to `JSON` fails the whole statement. The `json_valid` filters below route such rows to the rejected file. `PARTITION_BY (run_id)` writes each run to its own `run_id=/` folder. ```sql COPY ( SELECT run_id, ticket_id, customer_id, priority, result.response::JSON AS response_json, now() AS loaded_at FROM source_ticket_attempts WHERE result.error IS NULL AND json_valid(result.response) ) TO 's3://support-prod/ai/source_ticket_enriched' (FORMAT parquet, COMPRESSION zstd, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true); COPY ( SELECT run_id, ticket_id, customer_id, priority, coalesce(result.error, 'missing or invalid JSON response') AS error, now() AS loaded_at FROM source_ticket_attempts WHERE result.error IS NOT NULL OR NOT coalesce(json_valid(result.response), false) ) TO 's3://support-prod/ai/source_ticket_rejected' (FORMAT parquet, COMPRESSION zstd, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true); ``` If you intentionally write back to Postgres or MySQL, use a separate writable attachment and write only to a staging table. Review that staging table before merging into production tables. ```sql CREATE OR REPLACE SECRET app_postgres_writer ( TYPE postgres, HOST 'postgres.internal.example', PORT 5432, DATABASE 'app', USER 'ai_staging_writer', PASSWORD '' ); ATTACH '' AS app_pg_write ( TYPE postgres, SECRET app_postgres_writer, SCHEMA 'public' ); CREATE TABLE IF NOT EXISTS app_pg_write.public.ai_ticket_triage_staging ( run_id VARCHAR, ticket_id BIGINT, customer_id VARCHAR, response_json JSON, loaded_at TIMESTAMP ); INSERT INTO app_pg_write.public.ai_ticket_triage_staging SELECT run_id, ticket_id, customer_id, result.response::JSON AS response_json, now() AS loaded_at FROM source_ticket_attempts WHERE result.error IS NULL AND json_valid(result.response); ``` Do not write provider output directly into customer-facing source columns until you have a review, backfill, and rollback plan. ## Capture usage Persist usage events with the same run id. `ai_usage()` keeps only the latest 1,024 events, so check `dropped_events` first. If it is above 0, the snapshot is incomplete: use smaller batches and snapshot after each one. `ai_usage_totals()` keeps its counters even when events are dropped. ```sql SELECT max(dropped_events) AS dropped_events FROM ai_usage_summary(); COPY ( SELECT getvariable('run_id') AS run_id, now() AS captured_at, * FROM ai_usage() ) TO 's3://support-prod/ai/source_ticket_usage' (FORMAT parquet, COMPRESSION zstd, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true); SELECT getvariable('run_id') AS run_id, * FROM ai_usage_totals(); ``` ## Related cookbooks - [Run production batch enrichment from S3 or Parquet](production-batch-enrichment.md) - [Write audited AI outputs to lakehouse tables](audited-lakehouse-output.md) - [Monitor AI usage, failures, and cost](usage-cost-observability.md) - [Resume a local enrichment job](resumable-enrichment.md) ## Learn more - [DuckDB PostgreSQL extension](https://duckdb.org/docs/current/core_extensions/postgres/overview) - [PostgreSQL secrets](https://duckdb.org/docs/current/core_extensions/postgres/secrets) - [DuckDB MySQL extension](https://duckdb.org/docs/current/core_extensions/mysql) # Write audited AI outputs to lakehouse tables Use this cookbook when AI output must be queryable later with enough context to answer: which input row, which model, which run, which prompt contract, which error, and which usage event produced this result? The examples use Parquet as the portable baseline and show where to swap in Delta, Iceberg, or DuckLake tables if your platform already uses them. ## Prerequisites - Set `OPENAI_API_KEY` in the environment before starting DuckDB. The `openai_ai` secret below stores the provider and model, not the key. See the [provider guides](../provider-guides.md). - Create the [sample `support_tickets` table](support-ticket-data.md). The examples read its `ticket_id`, `customer_id`, `subject` and `body` columns. - Use a stable run id from the scheduler. - Decide the output schema before the run starts. ```sql INSTALL ai FROM community; LOAD ai; INSTALL json; LOAD json; CREATE OR REPLACE SECRET openai_ai ( TYPE duckdb_ai, AI_PROVIDER 'openai', MODEL 'gpt-5.6-luna' ); SET VARIABLE run_id = 'ticket-audit-2026-07-07T100000Z'; SET VARIABLE prompt_contract_version = 'ticket_triage_v1'; -- Fill ai_usage().estimated_cost_usd from the built-in price list. SET duckdb_ai_use_builtin_model_prices = true; ``` ## Create a run record Store the run metadata as data, not only as scheduler logs. Then clear the usage buffer, so the usage events you capture later belong to this run only. ```sql CREATE OR REPLACE TEMP TABLE ai_run AS SELECT getvariable('run_id') AS run_id, getvariable('prompt_contract_version') AS prompt_contract_version, 'openai' AS provider, 'gpt-5.6-luna' AS model, now() AS started_at, 'support_ticket_triage' AS job_name; SELECT * FROM ai_clear_usage(); ``` ## Run enrichment with a stable contract Keep the prompt contract and row output shape stable across reruns. Use `ai_try_complete` to preserve row-level failures. `result` is a `STRUCT` with `response` and `error`. ```sql CREATE OR REPLACE TEMP TABLE ai_attempts AS SELECT getvariable('run_id') AS run_id, getvariable('prompt_contract_version') AS prompt_contract_version, ticket_id, customer_id, subject, body, md5(subject || chr(10) || body) AS input_hash, ai_try_complete( 'Return JSON with keys summary, product_area, next_action, risk_level. ' || 'risk_level must be low, medium, or high.' || chr(10) || 'Subject: ' || subject || chr(10) || 'Body: ' || body, secret := 'openai_ai', response_format := 'json_object', retry_count := 2, retry_backoff_ms := 1000 ) AS result FROM support_tickets; ``` `risk_level` is a fixed scale. With a decision provider, you can skip the JSON parsing for that field: `ai_decide(body, {risk_level: ['low', 'medium', 'high']}, provider := 'ollama', on_error := 'null')` returns `risk_level DOUBLE` (0 to 2) and `risk_level_confidence`. `ai_decide` has no capture mode, so treat a NULL result as rejected. See [route rows by decision confidence](decision-routing.md). Keep `ai_try_complete` for free-text fields. ## Build audited row tables Successful rows and rejected rows should have the same identifiers and run columns so they can be reconciled later. `response_format := 'json_object'` asks for JSON but does not check the reply. A reply that is not valid JSON has `error = NULL`, and casting it to `JSON` fails the whole statement. The `json_valid` filters below route such rows to the rejected table, so every input row lands in exactly one of the two tables. ```sql CREATE OR REPLACE TEMP TABLE ai_ticket_enriched AS SELECT run_id, prompt_contract_version, ticket_id, customer_id, input_hash, result.response::JSON AS response_json, result.response::JSON->>'summary' AS summary, result.response::JSON->>'product_area' AS product_area, result.response::JSON->>'next_action' AS next_action, result.response::JSON->>'risk_level' AS risk_level, now() AS loaded_at FROM ai_attempts WHERE result.error IS NULL AND json_valid(result.response); CREATE OR REPLACE TEMP TABLE ai_ticket_rejected AS SELECT run_id, prompt_contract_version, ticket_id, customer_id, input_hash, coalesce(result.error, 'missing or invalid JSON response') AS error, now() AS loaded_at FROM ai_attempts WHERE result.error IS NOT NULL OR NOT coalesce(json_valid(result.response), false); ``` Capture usage events before calling `ai_clear_usage()` again. `ai_usage()` keeps only the latest 1,024 events, so check `dropped_events` first. If it is above 0, the snapshot is incomplete: run smaller batches and snapshot after each one. `ai_usage_totals()` keeps its counters even when events are dropped, so store it with the run. ```sql CREATE OR REPLACE TEMP TABLE ai_run_usage AS SELECT getvariable('run_id') AS run_id, now() AS captured_at, * FROM ai_usage(); SELECT max(dropped_events) AS dropped_events FROM ai_usage_summary(); CREATE OR REPLACE TEMP TABLE ai_run_usage_totals AS SELECT getvariable('run_id') AS run_id, now() AS captured_at, * FROM ai_usage_totals(); ``` ## Write Parquet audit datasets Use separate destinations for run metadata, successful rows, rejected rows, and usage events. This keeps each table easy to query and replay. `PARTITION_BY (run_id)` writes each run to its own `run_id=/` folder. ```sql COPY ai_run TO 's3://support-prod/ai/audit/runs' (FORMAT parquet, COMPRESSION zstd, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true); COPY ai_ticket_enriched TO 's3://support-prod/ai/audit/ticket_enriched' (FORMAT parquet, COMPRESSION zstd, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true); COPY ai_ticket_rejected TO 's3://support-prod/ai/audit/ticket_rejected' (FORMAT parquet, COMPRESSION zstd, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true); COPY ai_run_usage TO 's3://support-prod/ai/audit/usage' (FORMAT parquet, COMPRESSION zstd, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true); COPY ai_run_usage_totals TO 's3://support-prod/ai/audit/usage_totals' (FORMAT parquet, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true); ``` ## Load into managed lakehouse tables If your organization uses Delta, Iceberg, or DuckLake, keep the same audited row shape and load into managed tables after the temp tables are created. For Delta tables, attach the table and append reviewed rows. The Delta table must already exist with the same columns as `ai_ticket_enriched`. This example appends to it and does not create it. ```sql INSTALL delta; LOAD delta; ATTACH 's3://support-prod/lake/ticket_enriched_delta' AS ticket_enriched_delta (TYPE delta); INSERT INTO ticket_enriched_delta SELECT * FROM ai_ticket_enriched; ``` For an Iceberg REST catalog, attach the catalog first, then use normal SQL. The `ATTACH` line is a placeholder: replace it with your catalog's `ATTACH` statement and remove the comment marker, or the statements after it fail because `iceberg_prod` does not exist. ```sql INSTALL iceberg; LOAD iceberg; -- ATTACH '' AS iceberg_prod (TYPE iceberg, ...); CREATE SCHEMA IF NOT EXISTS iceberg_prod.ai; CREATE TABLE IF NOT EXISTS iceberg_prod.ai.ticket_enriched AS SELECT * FROM ai_ticket_enriched LIMIT 0; INSERT INTO iceberg_prod.ai.ticket_enriched SELECT * FROM ai_ticket_enriched; ``` For DuckLake, attach the catalog and data path used by your deployment: ```sql INSTALL ducklake; LOAD ducklake; ATTACH 'ducklake:metadata.ducklake' AS ai_lake (DATA_PATH 's3://support-prod/ducklake-data'); CREATE SCHEMA IF NOT EXISTS ai_lake.ai; CREATE TABLE IF NOT EXISTS ai_lake.ai.ticket_enriched AS SELECT * FROM ai_ticket_enriched LIMIT 0; INSERT INTO ai_lake.ai.ticket_enriched SELECT * FROM ai_ticket_enriched; ``` ## Validate the run Use simple reconciliation checks before publishing downstream tables: ```sql SELECT 'input' AS table_name, count(*) AS rows FROM ai_attempts UNION ALL SELECT 'success', count(*) FROM ai_ticket_enriched UNION ALL SELECT 'rejected', count(*) FROM ai_ticket_rejected; SELECT provider, model, status, count(*) AS calls, sum(total_tokens) AS total_tokens, sum(estimated_cost_usd) AS estimated_cost_usd FROM ai_run_usage GROUP BY ALL ORDER BY estimated_cost_usd DESC NULLS LAST; ``` ## Related cookbooks - [Run production batch enrichment from S3 or Parquet](production-batch-enrichment.md) - [Enrich rows from Postgres or MySQL](source-database-enrichment.md) - [Monitor AI usage, failures, and cost](usage-cost-observability.md), which reconciles these tables against usage events - [Normalize messy documents](messy-document-intake.md) ## Learn more - [DuckDB Delta extension](https://duckdb.org/docs/current/core_extensions/delta) - [DuckDB Iceberg extension](https://duckdb.org/docs/current/core_extensions/iceberg/overview) - [Writing Iceberg tables](https://duckdb.org/docs/current/core_extensions/iceberg/writing) - [DuckLake extension](https://duckdb.org/docs/current/core_extensions/ducklake) # Monitor AI usage, failures, and cost Use this cookbook when a production job needs basic operational visibility: calls, latency, failures, retries, cache hits, token counts, and estimated cost. `ai_usage()` is in-process state for the current DuckDB database instance, so capture it before the process exits or before you call `ai_clear_usage()`. It keeps only the latest 1,024 events. For larger jobs, snapshot it after each batch, check `dropped_events` in `ai_usage_summary()`, and record `ai_usage_totals()`, whose counters survive dropped events. ## Prerequisites - Configure provider credentials in the environment, for example `OPENAI_API_KEY`. See the [provider guides](../provider-guides.md). - Set a run id before provider calls. - Decide where usage snapshots should be persisted. ```sql INSTALL ai FROM community; LOAD ai; SET VARIABLE run_id = 'usage-monitoring-2026-07-07T100000Z'; SET duckdb_ai_use_builtin_model_prices = true; ``` `SELECT * FROM ai_model_prices()` lists the built-in prices. A model with no row there gets `estimated_cost_usd = NULL`. If the built-in prices do not match your provider contract, pass explicit prices on the production calls instead. Replace the example numbers with your own: ```sql SELECT ai_complete( 'Summarize one production incident.', provider := 'openai', model := 'gpt-5.6-luna', input_token_price_per_million := 0.20, output_token_price_per_million := 1.20 ); ``` ## Add job tags to outbound logs Outbound logs are optional. They omit prompt, input, and response text by default. Replace the endpoint below with your own collector: once it is set, every later call in the session tries to send a log event there. ```sql SET duckdb_ai_log_endpoint = 'https://collector.example/ai-usage'; SET duckdb_ai_log_format = 'otlp_json'; SET duckdb_ai_log_tags = 'service=support-pipeline,env=prod'; SET duckdb_ai_log_strict = false; ``` Use strict logging only when the SQL query should fail if the collector is unavailable. To stop sending logs, run `RESET duckdb_ai_log_endpoint;`. ## Snapshot local usage events Capture events after each production run, or after each batch for jobs with more than 1,024 calls: ```sql CREATE TABLE IF NOT EXISTS ai_usage_events AS SELECT getvariable('run_id') AS run_id, now() AS captured_at, * FROM ai_usage() LIMIT 0; INSERT INTO ai_usage_events SELECT getvariable('run_id') AS run_id, now() AS captured_at, * FROM ai_usage(); SELECT max(dropped_events) AS dropped_events FROM ai_usage_summary(); ``` If `dropped_events` is above 0, some events were evicted before the snapshot, so the totals below are low. Snapshot more often. Persist this run's snapshot before clearing the usage buffer. `PARTITION_BY (run_id)` writes it to its own `run_id=/` folder: ```sql COPY ( SELECT * FROM ai_usage_events WHERE run_id = getvariable('run_id') ) TO 's3://support-prod/ai/observability/usage' (FORMAT parquet, COMPRESSION zstd, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true); SELECT * FROM ai_clear_usage(); ``` ## Summarize cost and latency Aggregate by provider, model, function, and status: ```sql SELECT provider, model, function_name, status, count(*) AS calls, sum(prompt_tokens) AS prompt_tokens, sum(completion_tokens) AS completion_tokens, sum(total_tokens) AS total_tokens, sum(estimated_cost_usd) AS estimated_cost_usd, avg(elapsed_ms) AS avg_elapsed_ms, quantile_cont(elapsed_ms, 0.95) AS p95_elapsed_ms FROM ai_usage_events GROUP BY ALL ORDER BY estimated_cost_usd DESC NULLS LAST; ``` Find calls that failed or retried: ```sql SELECT event_id, function_name, provider, model, http_status, retries, error FROM ai_usage_events WHERE status <> 'ok' OR retries > 0 ORDER BY event_id DESC; ``` ## Build alertable checks These queries are useful as scheduler assertions after a run. ```sql WITH totals AS ( SELECT count(*) AS calls, count(*) FILTER (WHERE status <> 'ok') AS failed_calls, sum(estimated_cost_usd) AS estimated_cost_usd FROM ai_usage_events ) SELECT calls, failed_calls, failed_calls::DOUBLE / nullif(calls, 0) AS failure_rate, estimated_cost_usd FROM totals; ``` Alert when `failure_rate` or `estimated_cost_usd` crosses your job budget. ## Reconcile usage to output rows Usage events are call-level, while output rows are job-level. Store both with the same run id so they can be reconciled later. This query assumes you ran the [audited lakehouse output](audited-lakehouse-output.md) cookbook earlier in the same DuckDB session and kept its `run_id` (skip this page's `SET VARIABLE run_id`), so that its `ai_ticket_enriched` table and this page's `ai_usage_events` table share run ids. On its own, it fails because `ai_ticket_enriched` does not exist. With different run ids, it returns no rows. ```sql SELECT output.run_id, count(*) AS output_rows, usage.calls, usage.failed_calls, usage.estimated_cost_usd FROM ai_ticket_enriched AS output JOIN ( SELECT run_id, count(*) AS calls, count(*) FILTER (WHERE status <> 'ok') AS failed_calls, sum(estimated_cost_usd) AS estimated_cost_usd FROM ai_usage_events GROUP BY run_id ) AS usage ON output.run_id = usage.run_id GROUP BY output.run_id, usage.calls, usage.failed_calls, usage.estimated_cost_usd; ``` ## Clean up Stop sending outbound logs when you are done: ```sql RESET duckdb_ai_log_endpoint; RESET duckdb_ai_log_format; RESET duckdb_ai_log_tags; RESET duckdb_ai_log_strict; ``` ## Related cookbooks - [Run production batch enrichment from S3 or Parquet](production-batch-enrichment.md) - [Enrich rows from Postgres or MySQL](source-database-enrichment.md) - [Write audited AI outputs to lakehouse tables](audited-lakehouse-output.md) - [Evaluate before a batch run](evaluate-before-batch.md), which uses `ai_usage()` to compare the cost of candidate prompts and models ## Learn more - [SQL function reference: usage functions](../functions.md#usage-and-catalog-table-functions) - [Best practices: logging and cost](../best-practices.md#log-usage-without-leaking-text) - [Runtime behavior: usage and cache state](../runtime-behavior.md#runtime-state-scope) # Normalize messy documents into structured records Use this cookbook when operational inputs arrive as mixed JSON, Avro, Excel, or CSV/Parquet files and need to become one typed enrichment table. The production pattern is: 1. Read each source with explicit lineage. 2. Normalize all inputs to `document_id`, `source_uri`, `title`, and `body`. 3. Call the model with row-level failure capture. 4. Parse successful JSON into typed columns. 5. Export successes and rejected rows separately. ## Prerequisites - Set `OPENAI_API_KEY` in the environment before starting DuckDB. The `openai_ai` secret below stores the provider and model, not the key. See the [provider guides](../provider-guides.md). - Install file-format extensions used by your sources. - Use object-storage secrets when reading files from S3-compatible storage. ```sql INSTALL ai FROM community; LOAD ai; INSTALL json; LOAD json; INSTALL httpfs; LOAD httpfs; INSTALL avro; LOAD avro; INSTALL excel; LOAD excel; CREATE OR REPLACE SECRET openai_ai ( TYPE duckdb_ai, AI_PROVIDER 'openai', MODEL 'gpt-5.6-luna' ); SET VARIABLE run_id = 'document-intake-2026-07-07T100000Z'; ``` ## Read JSON documents For newline-delimited JSON, keep the source filename for lineage. Documents without an `id` get one from a hash of their file name and content, so the same document gets the same id on every rerun. A row number would change when files are read in a different order. ```sql CREATE OR REPLACE TEMP TABLE raw_json_documents AS SELECT coalesce( id::VARCHAR, md5(filename || chr(10) || coalesce(title::VARCHAR, '') || chr(10) || coalesce(body::VARCHAR, '')) ) AS document_id, filename AS source_uri, 'json' AS source_type, title::VARCHAR AS title, body::VARCHAR AS body, created_at::TIMESTAMP AS created_at FROM read_json( 's3://support-prod/raw/documents/*.jsonl', format = 'newline_delimited', filename = true, union_by_name = true ); ``` ## Read Avro documents Avro schemas are converted to DuckDB columns. Keep filenames when source paths carry partition or producer information. ```sql CREATE OR REPLACE TEMP TABLE raw_avro_documents AS SELECT event_id::VARCHAR AS document_id, filename AS source_uri, 'avro' AS source_type, subject::VARCHAR AS title, message::VARCHAR AS body, event_time::TIMESTAMP AS created_at FROM read_avro( 's3://support-prod/raw/events/*.avro', filename = true ); ``` ## Read Excel handoffs For inconsistent spreadsheets, start with all text and cast only after inspection. ```sql CREATE OR REPLACE TEMP TABLE raw_excel_documents AS SELECT ticket_id::VARCHAR AS document_id, 's3://support-prod/raw/handoffs/customer_escalations.xlsx' AS source_uri, 'xlsx' AS source_type, subject::VARCHAR AS title, notes::VARCHAR AS body, try_cast(created_at AS TIMESTAMP) AS created_at FROM read_xlsx( 's3://support-prod/raw/handoffs/customer_escalations.xlsx', sheet = 'Escalations', all_varchar = true, ignore_errors = true ); ``` ## Normalize the document stream Keep only rows with enough text for extraction. Store the original source and type so rejected rows can be traced back. A source that does not exist makes the whole `UNION ALL` fail, so keep only the `SELECT` lines for the sources you actually created above. ```sql CREATE OR REPLACE TEMP TABLE normalized_documents AS SELECT * FROM raw_json_documents UNION ALL SELECT * FROM raw_avro_documents UNION ALL SELECT * FROM raw_excel_documents; CREATE OR REPLACE TEMP TABLE document_batch AS SELECT getvariable('run_id') AS run_id, document_id, source_uri, source_type, coalesce(title, '') AS title, coalesce(body, '') AS body, created_at FROM normalized_documents WHERE length(coalesce(title, '') || coalesce(body, '')) >= 20; ``` ## Extract structured records Ask for JSON and preserve row-level errors. Keep the response schema beside the prompt contract in source control. `result` is a `STRUCT` with `response` and `error`. ```sql CREATE OR REPLACE TEMP TABLE document_extract_attempts AS SELECT run_id, document_id, source_uri, source_type, ai_try_complete( 'Extract a support document record as JSON with keys ' || 'category, customer_request, urgency_score, contains_pii. ' || 'urgency_score must be an integer from 1 to 5.' || chr(10) || 'Title: ' || title || chr(10) || 'Body: ' || body, secret := 'openai_ai', response_format := 'json_object', retry_count := 2, retry_backoff_ms := 1000 ) AS result FROM document_batch; ``` Project successful JSON into typed columns. `response_format := 'json_object'` asks for JSON but does not check the reply. A reply that is not valid JSON has `error = NULL`, and casting it to `JSON` fails the whole statement, so filter with `json_valid` and send those rows to the rejected table: ```sql CREATE OR REPLACE TEMP TABLE document_records AS SELECT run_id, document_id, source_uri, source_type, result.response::JSON->>'category' AS category, result.response::JSON->>'customer_request' AS customer_request, try_cast(result.response::JSON->>'urgency_score' AS INTEGER) AS urgency_score, try_cast(result.response::JSON->>'contains_pii' AS BOOLEAN) AS contains_pii, result.response::JSON AS response_json, now() AS loaded_at FROM document_extract_attempts WHERE result.error IS NULL AND json_valid(result.response); ``` Capture rejected rows: ```sql CREATE OR REPLACE TEMP TABLE document_rejected_rows AS SELECT run_id, document_id, source_uri, source_type, coalesce(result.error, 'missing or invalid JSON response') AS error, now() AS loaded_at FROM document_extract_attempts WHERE result.error IS NOT NULL OR NOT coalesce(json_valid(result.response), false); ``` ## Export structured records Persist records and rejected rows separately. `PARTITION_BY (run_id)` writes each run to its own `run_id=/` folder: ```sql COPY document_records TO 's3://support-prod/ai/document_records' (FORMAT parquet, COMPRESSION zstd, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true); COPY document_rejected_rows TO 's3://support-prod/ai/document_rejected' (FORMAT parquet, COMPRESSION zstd, PARTITION_BY (run_id), OVERWRITE_OR_IGNORE true); ``` To get typed fields without parsing JSON yourself, use [`ai_extract_record`](structured-triage-records.md) with a JSON Schema. It returns one `STRUCT` per row. ## Related cookbooks - [Run production batch enrichment from S3 or Parquet](production-batch-enrichment.md) - [Write audited AI outputs to lakehouse tables](audited-lakehouse-output.md) - [Monitor AI usage, failures, and cost](usage-cost-observability.md) ## Learn more - [DuckDB JSON loading](https://duckdb.org/docs/current/data/json/loading_json) - [DuckDB Avro extension](https://duckdb.org/docs/current/core_extensions/avro) - [DuckDB Excel extension](https://duckdb.org/docs/current/core_extensions/excel) - [Reading multiple files](https://duckdb.org/docs/current/data/multiple_files/overview) # Enrich support tickets with AI text functions Use this cookbook when you have text columns in a local table and want to add AI summaries, labels, extraction, redaction, or translations in SQL. ## Prerequisites - Build and load the extension. - Configure a completion provider with session settings or a `TYPE duckdb_ai` secret. Hosted providers read their key from the environment, for example `OPENAI_API_KEY`. See the [provider guides](../provider-guides.md). - Create the [sample `support_tickets` table](support-ticket-data.md). Every function on this page sends one request per non-NULL row. ## Summarize each ticket Combine the columns that give the model enough context: ```sql SELECT ticket_id, ai_summarize(subject || chr(10) || body) AS ticket_summary FROM support_tickets; ``` Result: one `ticket_summary VARCHAR` per ticket. ## Summarize by customer Use the aggregate form when several rows belong to the same account: ```sql SELECT customer_id, ai_summarize_agg(subject || ': ' || body ORDER BY created_at) AS summary FROM support_tickets GROUP BY customer_id; ``` Result: one `summary VARCHAR` per customer. ## Classify tickets Keep labels short and mutually exclusive: ```sql SELECT ticket_id, ai_classify( subject || chr(10) || body, 'billing, performance, integration, documentation, other' ) AS category FROM support_tickets; ``` Result: `category VARCHAR`, always one of the listed labels. ## Filter rows with a natural-language predicate Use `ai_filter` when keyword logic would be too brittle. Send only the columns the model needs: `internal_note` contains an email address, so it stays out of the prompt until it has been [redacted](#redact-internal-notes). ```sql SELECT * FROM support_tickets WHERE ai_filter( subject || chr(10) || body, 'mentions urgent production impact or needs engineering follow-up' ); ``` ## Answer several questions in one call The classify and filter steps above each send one request per row. With a decision-model provider (`typesafe`, `cloudflare`, `perplexity`, `ollama` 0.35 or later, or `systemone`), one `ai_decide` call answers both questions and returns a confidence for the label and a probability for the yes/no question. You then choose the thresholds in SQL. See [route rows by decision confidence](decision-routing.md). If you only have a chat provider, keep `ai_classify` and `ai_filter`. ## Extract compact JSON Use `ai_extract` for lightweight structured values that can stay in a JSON text column: ```sql SELECT ticket_id, ai_extract( body, 'Return compact JSON with product_area, customer_request, and urgency' ) AS extracted_json FROM support_tickets; ``` Result: `extracted_json VARCHAR`. The function does not check the JSON against a schema. Use [`ai_extract_record`](../functions.md#ai_extract_recordtext-response_schema-model-provider) to get one typed `STRUCT` per row instead. See [typed records from model output](structured-triage-records.md). ## Redact internal notes Redact notes before sharing operational context outside the team: ```sql SELECT ticket_id, ai_redact(internal_note) AS redacted_internal_note FROM support_tickets; ``` ## Translate customer text Translate only the rows that need it: ```sql SELECT ticket_id, language, ai_translate(subject || ': ' || body, 'English') AS english_text FROM support_tickets WHERE language <> 'en'; ``` ## Inspect usage After running provider calls, inspect recent usage events: ```sql SELECT function_name, provider, model, prompt_tokens, completion_tokens, total_tokens, elapsed_ms FROM ai_usage() ORDER BY event_id DESC LIMIT 10; ``` `ai_usage()` keeps the latest 1,024 events for the current DuckDB instance. See [usage and cost monitoring](usage-cost-observability.md) to keep them longer. # Compare support tickets with embeddings Use this cookbook to find semantically similar support tickets from text columns. `ai_similarity` embeds both inputs with the same model and returns cosine similarity as a `DOUBLE` from -1 to 1. ## Prerequisites - Configure an embedding-capable provider. The examples use OpenAI `text-embedding-3-small`, so set `OPENAI_API_KEY` in the environment before starting DuckDB. See the [provider guides](../provider-guides.md). - Create the [sample `support_tickets` table](support-ticket-data.md). ## Rank ticket pairs Compare every ticket pair and rank the closest matches: ```sql SELECT left_ticket.ticket_id AS left_ticket_id, right_ticket.ticket_id AS right_ticket_id, ai_similarity( left_ticket.subject || chr(10) || left_ticket.body, right_ticket.subject || chr(10) || right_ticket.body, provider := 'openai', model := 'text-embedding-3-small' ) AS similarity FROM support_tickets AS left_ticket JOIN support_tickets AS right_ticket ON left_ticket.ticket_id < right_ticket.ticket_id ORDER BY similarity DESC; ``` ## Compare against a target description Use a fixed description when you want to find rows that match a theme: ```sql SELECT ticket_id, subject, ai_similarity( subject || chr(10) || body, 'production incident blocking an important business workflow', provider := 'openai', model := 'text-embedding-3-small' ) AS similarity FROM support_tickets ORDER BY similarity DESC; ``` ## Keep batches bounded Pairwise comparison grows quickly. For larger tables, filter first: ```sql SELECT left_ticket.ticket_id AS left_ticket_id, right_ticket.ticket_id AS right_ticket_id, ai_similarity( left_ticket.subject || chr(10) || left_ticket.body, right_ticket.subject || chr(10) || right_ticket.body, provider := 'openai', model := 'text-embedding-3-small' ) AS similarity FROM support_tickets AS left_ticket JOIN support_tickets AS right_ticket ON left_ticket.ticket_id < right_ticket.ticket_id WHERE left_ticket.priority = 'high' OR right_ticket.priority = 'high' ORDER BY similarity DESC; ``` ## Store embeddings once `ai_similarity` embeds both of its inputs on every call, so each run of the queries above pays for the embeddings again. When you search the same rows more than once, embed each row once, save the vectors in a table, and compare them with DuckDB's `list_cosine_similarity`. That comparison runs locally and costs nothing. Embed every ticket once. `ai_embed` returns a `DOUBLE[]`. With `on_error := 'null'`, a failed row gets a NULL embedding instead of failing the statement: ```sql CREATE OR REPLACE TABLE ticket_embeddings AS SELECT ticket_id, subject, body, ai_embed( subject || chr(10) || body, provider := 'openai', model := 'text-embedding-3-small', on_error := 'null' ) AS embedding FROM support_tickets; ``` Embed the search text once and keep it in a variable: ```sql SET VARIABLE ticket_query = 'production incident blocking a business workflow'; SET VARIABLE ticket_query_embedding = ( SELECT ai_embed( getvariable('ticket_query'), provider := 'openai', model := 'text-embedding-3-small' ) ); ``` Rank the stored vectors. This query makes no provider calls, so you can rerun it as often as you like: ```sql SELECT ticket_id, subject, list_cosine_similarity(embedding, getvariable('ticket_query_embedding')) AS similarity FROM ticket_embeddings WHERE embedding IS NOT NULL ORDER BY similarity DESC LIMIT 3; ``` Result: the top 3 tickets with `similarity DOUBLE`. Both vectors must come from the same embedding model, because vectors of different lengths cannot be compared. The same table also replaces the pairwise query, with no extra calls: ```sql SELECT a.ticket_id AS left_ticket_id, b.ticket_id AS right_ticket_id, list_cosine_similarity(a.embedding, b.embedding) AS similarity FROM ticket_embeddings AS a JOIN ticket_embeddings AS b ON a.ticket_id < b.ticket_id ORDER BY similarity DESC; ``` ### Rerank the short list Embedding similarity is cheap but coarse. To reorder the best few matches, call `ai_rerank` on the short list only. The `LIMIT` sits inside the subquery, so the completion model is called for 3 rows, not for the whole table: ```sql SELECT ticket_id, subject, similarity, ai_rerank( getvariable('ticket_query'), subject || chr(10) || body, provider := 'openai', model := 'gpt-5.6-luna' ) AS rerank_score FROM ( SELECT ticket_id, subject, body, list_cosine_similarity(embedding, getvariable('ticket_query_embedding')) AS similarity FROM ticket_embeddings WHERE embedding IS NOT NULL ORDER BY similarity DESC LIMIT 3 ) ORDER BY rerank_score DESC; ``` Result: `rerank_score DOUBLE` from 0 to 1, one completion call per short-list row. `list_cosine_similarity` scans every stored vector, which is fine for small and medium tables. For a vector index, full-text search, or larger tables, write the same embeddings to Lance as shown in [semantic search with Lance](lance-semantic-search.md). Remove the example table when you are done: ```sql DROP TABLE IF EXISTS ticket_embeddings; ``` # Store embeddings in Lance for semantic search Use this cookbook when you want to reuse embeddings across searches, build a vector index, or combine semantic search with full-text search. Keep `ai_similarity` for small ad hoc comparisons. For a plain DuckDB table of embeddings without an index, see [store embeddings once](support-ticket-similarity.md#store-embeddings-once). Use the Lance extension when the same embeddings should be stored, indexed, and queried repeatedly. ## Prerequisites - Install and load both extensions. - Configure an embedding-capable provider. The examples use OpenAI, so set `OPENAI_API_KEY` in the environment before starting DuckDB. See the [provider guides](../provider-guides.md). - Create the [sample `support_tickets` table](support-ticket-data.md). ```sql INSTALL ai FROM community; LOAD ai; INSTALL lance; LOAD lance; ``` The examples below use OpenAI `text-embedding-3-small` and cast embeddings to `FLOAT[1536]`. Replace `1536` with the width returned by your embedding model. Lance can search `FLOAT[]` and `DOUBLE[]` query vectors, but vector indexes need a fixed-size vector column such as `FLOAT[1536]` or `DOUBLE[1536]`. ## Build an embedding staging table Create embeddings once and keep the source text beside the vector. Use `on_error := 'null'` when you want failed provider calls to be filtered out instead of failing the whole batch. ```sql CREATE OR REPLACE TABLE support_ticket_embedding_stage AS SELECT ticket_id, customer_id, priority, subject, body, subject || chr(10) || body AS search_text, ai_embed( subject || chr(10) || body, provider := 'openai', model := 'text-embedding-3-small', on_error := 'null' ) AS embedding FROM support_tickets; ``` Check for rejected rows before writing the Lance dataset: ```sql SELECT count(*) AS rejected_rows FROM support_ticket_embedding_stage WHERE embedding IS NULL; ``` ## Write a Lance dataset Cast the embedding to a fixed-size vector before writing the dataset. This keeps the Lance table ready for vector index creation. ```sql COPY ( SELECT ticket_id, customer_id, priority, subject, body, search_text, embedding::FLOAT[1536] AS embedding FROM support_ticket_embedding_stage WHERE embedding IS NOT NULL ) TO 'support_ticket_embeddings.lance' ( FORMAT lance, mode 'overwrite' ); ``` You can also attach a Lance namespace when you want stable table names: ```sql ATTACH './lance_data' AS lance_ns (TYPE LANCE); CREATE OR REPLACE TABLE lance_ns.main.support_ticket_embeddings AS SELECT ticket_id, customer_id, priority, subject, body, search_text, embedding::FLOAT[1536] AS embedding FROM support_ticket_embedding_stage WHERE embedding IS NOT NULL; ``` ## Create search indexes Create a vector index for semantic search. Start with a small `num_partitions` value for small datasets, then tune it for larger tables. ```sql CREATE INDEX support_ticket_embedding_idx ON 'support_ticket_embeddings.lance' (embedding) USING IVF_FLAT WITH (num_partitions = 1, metric_type = 'l2'); ``` Add a full-text index when keyword matching should also influence retrieval: ```sql CREATE INDEX support_ticket_text_idx ON 'support_ticket_embeddings.lance' (search_text) USING INVERTED; ``` Inspect the indexes: ```sql SHOW INDEXES ON 'support_ticket_embeddings.lance'; ``` ## Search with a query embedding Embed the search request into a DuckDB variable, then pass that vector to `lance_vector_search`. Lance search parameters must be literals, prepared parameters, or variable lookups rather than lateral columns from a CTE. Lance returns `_distance`; smaller values are closer matches. ```sql SET VARIABLE semantic_query = 'production incident blocking a business workflow'; SET VARIABLE semantic_query_embedding = ( SELECT ai_embed( getvariable('semantic_query'), provider := 'openai', model := 'text-embedding-3-small' )::FLOAT[1536] AS embedding ); SELECT result.ticket_id, result.subject, result.priority, result._distance FROM lance_vector_search( 'support_ticket_embeddings.lance', 'embedding', getvariable('semantic_query_embedding'), k = 10, use_index = true, refine_factor = 2 ) AS result ORDER BY result._distance ASC; ``` ## Rerank the shortlist Use `ai_rerank` only on the small candidate set returned by Lance. This keeps the expensive model-based scoring step bounded. ```sql SET VARIABLE semantic_query = 'production incident blocking a business workflow'; SET VARIABLE semantic_query_embedding = ( SELECT ai_embed( getvariable('semantic_query'), provider := 'openai', model := 'text-embedding-3-small' )::FLOAT[1536] AS embedding ); WITH candidates AS ( SELECT result.ticket_id, result.subject, result.body, result._distance FROM lance_vector_search( 'support_ticket_embeddings.lance', 'embedding', getvariable('semantic_query_embedding'), k = 20, use_index = true ) AS result ) SELECT candidates.ticket_id, candidates.subject, candidates._distance, ai_rerank( getvariable('semantic_query'), candidates.subject || chr(10) || candidates.body, provider := 'openai', model := 'gpt-5.6-luna' ) AS rerank_score FROM candidates ORDER BY rerank_score DESC, candidates._distance ASC LIMIT 10; ``` ## Combine vector and full-text search Use Lance hybrid search when you want semantic similarity and keyword relevance in the same retrieval step. ```sql SET VARIABLE hybrid_query = 'billing outage after plan upgrade'; SET VARIABLE hybrid_query_embedding = ( SELECT ai_embed( getvariable('hybrid_query'), provider := 'openai', model := 'text-embedding-3-small' )::FLOAT[1536] AS embedding ); SELECT result.ticket_id, result.subject, result._hybrid_score, result._distance, result._score FROM lance_hybrid_search( 'support_ticket_embeddings.lance', 'embedding', getvariable('hybrid_query_embedding'), 'search_text', getvariable('hybrid_query'), k = 10, alpha = 0.7, oversample_factor = 4 ) AS result ORDER BY result._hybrid_score DESC; ``` Use a lower `alpha` when keyword matches should matter more, and a higher `alpha` when vector similarity should dominate. ## Clean up The examples write files to the current directory. To remove them, detach the namespace and drop the staging table: ```sql DETACH lance_ns; DROP TABLE IF EXISTS support_ticket_embedding_stage; ``` Then delete the files from a shell: ```sh rm -rf support_ticket_embeddings.lance ./lance_data ``` ## Learn more - [DuckDB Lance extension](https://duckdb.org/docs/current/core_extensions/lance) - [Lance DuckDB SQL reference](https://github.com/lance-format/lance-duckdb/blob/main/docs/sql.md) - [LanceDB DuckDB integration](https://docs.lancedb.com/integrations/data/duckdb) # Extract typed records from model output Use this cookbook when you want a model to return structured JSON and use the result as typed DuckDB values, without parsing JSON text yourself. There are two functions: | Function | Input | Result | Use it for | | --- | --- | --- | --- | | `ai_extract_record(text, schema)` | A column, one call per row | One `STRUCT` per row | Table rows | | `ai_complete_record(prompt, schema)` | A constant prompt | A one-row table | One-off, self-contained prompts | Both validate the response against the JSON Schema before returning it. ## Prerequisites - Configure a completion provider. The examples use OpenAI `gpt-5.6-luna`, so set `OPENAI_API_KEY` in the environment before starting DuckDB. See the [provider guides](../provider-guides.md). - Create the [sample `support_tickets` table](support-ticket-data.md). ## Extract one record per row `ai_extract_record` takes the row text and a constant JSON Schema. Top-level schema properties become fields of the returned `STRUCT`. Call it once in a subquery and read the fields outside it, so each row costs one request: ```sql SELECT ticket_id, triage.product_area, triage.needs_engineering, triage.urgency_score, triage.recommended_owner FROM ( SELECT ticket_id, ai_extract_record( subject || chr(10) || body, '{ "type": "object", "properties": { "product_area": {"type": "string"}, "needs_engineering": {"type": "boolean"}, "urgency_score": {"type": "integer", "minimum": 0, "maximum": 10}, "recommended_owner": {"type": "string"} }, "required": ["product_area", "needs_engineering", "urgency_score"] }', provider := 'openai', model := 'gpt-5.6-luna' ) AS triage FROM support_tickets ); ``` Result shape: | Column | Type | | --- | --- | | `ticket_id` | `INTEGER` | | `product_area` | `VARCHAR` | | `needs_engineering` | `BOOLEAN` | | `urgency_score` | `BIGINT` | | `recommended_owner` | `VARCHAR` | The `STRUCT` itself orders its fields alphabetically, so `triage.*` returns `needs_engineering, product_area, recommended_owner, urgency_score`. Name the fields, as above, when column order matters. Schema `integer` fields become `BIGINT`. A property that is not `required` can be NULL. ## Save the records, then filter on typed fields Save the records in a table first. Later queries then read saved values instead of calling the model again: ```sql CREATE OR REPLACE TABLE ticket_triage AS SELECT ticket_id, triage FROM ( SELECT ticket_id, ai_extract_record( subject || chr(10) || body, '{ "type": "object", "properties": { "product_area": {"type": "string"}, "needs_engineering": {"type": "boolean"}, "urgency_score": {"type": "integer", "minimum": 0, "maximum": 10} }, "required": ["product_area", "needs_engineering", "urgency_score"] }', provider := 'openai', model := 'gpt-5.6-luna', on_error := 'null' ) AS triage FROM support_tickets ); SELECT ticket_id, triage.product_area, triage.urgency_score FROM ticket_triage WHERE triage.needs_engineering AND triage.urgency_score >= 7; ``` With `on_error := 'null'`, a failed or invalid response leaves `triage` NULL for that row instead of failing the statement. Find those rows with `WHERE triage IS NULL`. ## Create one record from a self-contained prompt `ai_complete_record` is a table function. Its prompt must be a constant: a column or a subquery is rejected. Use it when the prompt already contains everything the model needs: ```sql SELECT * FROM ai_complete_record( 'Extract a support triage profile from this ticket. ' || 'Subject: Query regression after upgrade. ' || 'Body: Dashboard query that used to finish in 8 seconds now times out after the upgrade.', '{ "type": "object", "properties": { "product_area": {"type": "string"}, "needs_engineering": {"type": "boolean"}, "urgency_score": {"type": "integer", "minimum": 0, "maximum": 10}, "recommended_owner": {"type": "string"} }, "required": ["product_area", "needs_engineering", "urgency_score"] }', provider := 'openai', model := 'gpt-5.6-luna' ); ``` Result: one row with columns `needs_engineering BOOLEAN`, `product_area VARCHAR`, `recommended_owner VARCHAR` and `urgency_score BIGINT`. `SELECT *` returns them in alphabetical order, not in schema order. ## Related - [Chain several AI steps](chain-ai-steps.md) feeds typed records into later steps and reruns without paying twice. - [Typed decisions](jev-decisions.md) return labels, scores and probabilities from a decision model instead of generated JSON. - [`ai_extract_record` reference](../functions.md#ai_extract_recordtext-response_schema-model-provider) Remove the example table when you are done: ```sql DROP TABLE IF EXISTS ticket_triage; ``` # Turn rows into typed decisions Use `ai_decide` to classify, rate and flag table rows in one call. You write the questions as SQL values and get a `STRUCT` back. Its fields work directly in `SELECT`, `WHERE`, aggregations and Parquet exports, without parsing JSON. This page uses TypeSafe Jev (`provider := 'typesafe'`), the only decision provider that batches rows: up to 32 non-null rows per request by default. `ai_jev(x, q)` is shorthand for `ai_decide(x, q, provider := 'typesafe')`. It does not accept the `provider` or `profile` options or the `{instructions, criteria}` question form. :::tip Other decision models The first three sections (configure, define, downstream SQL) also work with another decision provider. Change `provider := 'typesafe'` to `'ollama'` (Ollama 0.35 or later, after `ollama pull nimble`) or `'cloudflare'` (Clef), and change or drop `model`. The batching section and the evaluator script apply only to TypeSafe: other providers send one request per row, and `batch_size` has no effect on them. See [decision models](../provider-guides.md#decision-models). ::: ## Configure Jev Set `TYPESAFE_API_KEY` in the environment before starting DuckDB. Keep credentials out of SQL literals and query history. The provider also supports [`TYPE duckdb_ai` secrets](../provider-guides.md#typesafe-jev). ```sh export TYPESAFE_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE TABLE decision_tickets AS SELECT * FROM (VALUES (1, 'I was charged twice. Please refund the duplicate.'), (2, 'Production imports are blocked with no workaround.'), (3, NULL) ) AS t(id, body); ``` This page uses its own `decision_tickets` table, with a NULL row, instead of the shared sample data. ## Define the decisions and save the results Give each decision a field name and criteria. Descriptions define the decision, so make them specific. A field name such as `urgent` is a SQL output name, not an instruction to the model. ```sql CREATE OR REPLACE TABLE ticket_decisions AS SELECT id, ai_decide(body, { department: MAP { 'billing': 'Payments, duplicate charges, invoices, refunds', 'technical': 'Bugs, outages, data imports, integrations', 'other': 'None of the above' }, urgency: [ 'Routine question with no blocked work', 'Degraded work with a workaround', 'Production work is blocked with no workaround' ], refund_requested: MAP { 'true': 'Explicitly asks for money to be returned', 'false': 'Does not explicitly ask for money to be returned' } }, provider := 'typesafe', model := 'jev-1.13.0') AS decision FROM decision_tickets; ``` This statement calls Jev and can incur charges. Saving the result in a table means the queries below read saved values instead of calling the provider again. Use a persistent DuckDB database file to keep that table across sessions. The result has this shape: | Field | SQL type | Meaning | | --- | --- | --- | | `decision.department` | `VARCHAR` | One of the three declared labels | | `decision.department_confidence` | `DOUBLE` | Confidence in the label, when the provider returns it | | `decision.urgency` | `DOUBLE` | Weighted rubric position, here from 0 to 2 | | `decision.urgency_confidence` | `DOUBLE` | Confidence in the score, when the provider returns it | | `decision.refund_requested` | `DOUBLE` | Probability of yes, from 0 to 1 | A NULL input produces a NULL `decision` without a provider call. For example, ticket 3 remains in the saved table with a NULL decision. Model answers for the other tickets depend on the model, so the field types and bounds are guaranteed, not specific predictions. ## Use ordinary SQL downstream Select individual fields and apply your own routing thresholds: ```sql SELECT id, decision.department AS department, decision.urgency AS urgency, decision.refund_requested AS refund_probability FROM ticket_decisions WHERE decision.department_confidence >= 0.8; ``` Keep uncertain or missing results available for review: ```sql SELECT id, decision FROM ticket_decisions WHERE decision IS NULL OR decision.department_confidence IS NULL OR decision.department_confidence < 0.8; ``` Count decisions or export a flat, typed dataset: ```sql SELECT decision.department AS department, count(*) AS tickets FROM ticket_decisions GROUP BY ALL; COPY ( SELECT id, decision.* FROM ticket_decisions ) TO 'ticket_decisions.parquet' (FORMAT PARQUET); ``` The threshold `0.8` is illustrative. Tune it on labeled data, as in [evaluate before a batch run](evaluate-before-batch.md), and see [route rows by decision confidence](decision-routing.md) for a fallback when confidence is low. A score is a rubric position, not a probability. A yes/no answer (TypeSafe calls this question type a Noul) is the probability of yes, which is also different from a choice confidence. ## Control batching and failures This section applies to `provider := 'typesafe'` and `ai_jev` only. `batch_size := 32` is the default row cap. Use `batch_size := 1` for a one-row comparison. Batching happens within DuckDB execution chunks, so a query may send several underfilled requests. NULL rows consume no request slots. Each row becomes one question per declared decision. With the three decisions above, 32 rows means 96 questions in one request. Every question carries its own record in structured instructions. Shared state is empty, so unrelated rows are not put into the context shared by every question. `max_request_bytes` limits the serialized request body. The function splits a batch before exceeding it, and rejects a row that cannot fit by itself. It never truncates text. The byte limit is not a tokenizer or a guarantee that Jev's context window is satisfied. Smaller batches and shorter relevant inputs are the first remedies for a provider context-limit error. By default a failed request or invalid answer fails the SQL query. Use `on_error := 'null'` if you prefer a NULL result for affected rows. All rows in a failed batch are affected. NULL alone does not distinguish a provider failure from a NULL input, so keep the source text or check usage errors when that matters. Retries can repeat the entire request. A later failed batch does not undo earlier external calls or charges, even if SQL rolls back the table write. Use the existing `ai_usage()` and `ai_usage_summary()` tables to inspect requests, retries and token usage. Batching reduces initial HTTP requests, but does not guarantee proportional cost or latency improvements. Data and criteria are repeated per question, particularly when asking several decisions per row. ## Evaluate batch size on labeled data Before choosing a batch size for a job, compare predictions on a representative sample with human-reviewed labels. Use the source-checkout example `examples/jev_batch_evaluation.py` for a single choice question. It calls `ai_jev`, so it only measures TypeSafe. It compares batch sizes 1, 8, 16 and 32 using the same rows, criteria and explicit model. It does not evaluate score or yes/no questions. In particular, a criteria map containing exactly `true` and `false` is rejected because it selects yes/no mode. To measure accuracy and cost with any provider in plain SQL, see [evaluate before a batch run](evaluate-before-batch.md). Create `tickets.csv` with unique, non-empty identifiers, text and expected labels: ```csv id,text,label 1,I was charged twice. Please refund the duplicate.,billing 2,Production imports are blocked with no workaround.,technical ``` Create `criteria.json` with the allowed labels and their descriptions: ```json { "billing": "Payments, duplicate charges, invoices, refunds", "technical": "Bugs, outages, data imports, integrations" } ``` Check the sample locally without credentials, a DuckDB build or provider calls: ```sh python3 examples/jev_batch_evaluation.py \ --input tickets.csv --criteria criteria.json --preflight ``` The JSON summary reports row count, counts for every allowed label (including unused labels), and minimum, median and maximum UTF-8 text bytes. Inspect label balance and long rows before the live comparison. The byte counts exclude criteria and request framing, so they do not predict Jev token use or whether a row fits `max_request_bytes`. The input and criteria hashes can be compared with the later live report to confirm the same sample and criteria were used. Quality-gate options (`--min-*` and `--required-label`) require `--allow-live`: preflight makes no predictions and cannot measure their quality. For the live comparison, use a build containing `ai_jev` and configure `TYPESAFE_API_KEY` as above. The example requires explicit live-call opt-in because it sends the sample four times and may incur charges: ```sh python3 examples/jev_batch_evaluation.py \ --duckdb ./build/release/duckdb \ --input tickets.csv \ --criteria criteria.json \ --model jev-1.13.0 \ --timeout-seconds 3600 \ --allow-live > evaluation.json ``` The timeout applies to each batch-size run and defaults to one hour. Increase it for slow endpoints or larger samples. If a later run fails or times out, the command exits nonzero and writes the completed comparisons with `complete: false` and an error. The interrupted run is not saved, and it may already have incurred charges. Successful reports have `complete: true`. The report retains predictions by identifier for inspection. Accuracy counts missing predictions as incorrect. It also reports answer coverage, per-class support, precision, recall, F1, and a confusion matrix with a separate failed count for each actual class. Failures reduce recall and F1. Precision is `null` when a class has no predictions; recall is `null` when it has no labeled examples. Macro F1 averages the classes present in the labeled data, listed explicitly in `macro_f1_labels`; unobserved classes remain visible in the per-class table. These metrics compare predictions with the supplied ground-truth labels. Agreement compares each run with batch size 1 among rows where both runs returned a prediction. Coverage counts show how many rows each run answered. A missing prediction never counts as agreement. Inspect coverage before interpreting agreement, since it excludes missing pairs. Two rows are enough to check the workflow, but use a representative sample larger than 32 rows to compare all four batch sizes. The example runs each batch size in a fresh DuckDB process with one thread and one provider request at a time, response caching disabled and no retries. It uses `on_error := 'null'` so a failed request remains visible as missing predictions. Input is limited to 1,000 labeled rows to keep request operations within the usage buffer. Request bodies can still split at the byte limit, so configured batch size is an upper bound on rows per request. A row that cannot fit by itself is missing without a provider request. If no requests are recorded at all, the command fails with guidance to check credentials and request size. Token usage is provider-reported. Totals remain unknown when an operation fails or omits usage, with event counts indicating coverage. Missing usage is not evidence of zero cost. Dropped usage events also make totals incomplete. Wall time includes local process and SQL overhead, so it is not isolated API latency. A single run does not establish a speedup or prediction stability. Repeat comparisons and inspect disagreements before adopting a batch size. Provider-side caching, service load and model changes can affect results. The local mock test establishes the evaluator's request accounting and metrics, not Jev's live quality or costs. ## Gate changes against labeled data Supply acceptance thresholds to make the evaluator fail when any tested batch size misses them: ```sh python3 examples/jev_batch_evaluation.py --duckdb build/release/duckdb \ --input labeled-tickets.csv --criteria criteria.json --model jev-1.13.0 \ --allow-live --min-accuracy 0.95 --min-macro-f1 0.9 \ --min-coverage 0.99 --min-class-recall 0.8 ``` The JSON report contains `quality_gate`, including failing metrics and labels; a failed gate exits with status 2. When gating is enabled, every criteria label must have ground-truth examples by default. Repeated `--required-label` options may explicitly select required classes. Use labels independent of the model's training/teacher outputs, keep minority and out-of-domain cases, and group related records when splitting datasets. Thresholds must reflect the application's error costs. CI includes a hand-authored synthetic multilingual fixture that verifies gate arithmetic and failure handling; it does not certify production model quality. ## Choose the appropriate interface - Use `ai_decide` for typed choices, scores and yes/no probabilities across rows, with any decision provider. Add a per-question instruction with the `{instructions: '...', criteria: ...}` form. `ai_jev` is the TypeSafe shorthand. - Keep `ai_classify` or `ai_filter` when you only have a chat provider, or for existing single-decision queries. - Use `ai_provider_call` when you need full probability distributions, the resolved model version or shared state. It returns raw JSON and does not automatically combine separate SQL rows. - Use a generative provider for summaries, free-text extraction and SQL generation. Keep arithmetic, date comparisons and counting in DuckDB. See the [function reference](../functions.md#ai_decidestate-questions-) for question types, options and validation rules. For multi-step pipelines that save each step, see [chain several AI steps](chain-ai-steps.md). The local mock suite checks request counts, row mapping, types and errors. It does not establish live prediction quality, latency or token savings. The compact typed-question interface was inspired by [`colliber/duckdb-jev`](https://github.com/colliber/duckdb-jev). This extension uses its own existing provider runtime and adds row batching. TypeSafe documents [structured instructions](https://docs.typesafe.ai/primitives/advanced), [model limits](https://docs.typesafe.ai/models) and [known model limitations](https://docs.typesafe.ai/model-jaggedness/jev-1.13). ## Clean up To remove the example, run `DROP TABLE IF EXISTS ticket_decisions, decision_tickets;` and delete `ticket_decisions.parquet` from the directory where DuckDB was started. # Route rows by decision confidence Use this cookbook when you label many rows and want to act automatically only on the answers the model is sure about. A decision model answers several fixed-choice questions per row in one call and returns a confidence or a probability with each answer. You accept the confident rows, send the uncertain ones to a chat model that also explains its answer, and put the rest in a review table. ## Prerequisites - A decision model. The examples use the local Ollama model `nimble`, which needs Ollama 0.35 or later and `ollama pull nimble`. To use another decision provider (`typesafe`, `cloudflare`, `perplexity` or `systemone`), change `provider` and `model`. See [decision models](../provider-guides.md#decision-models). - A chat model for the fallback step. The examples use OpenAI `gpt-5.6-luna`, so set `OPENAI_API_KEY` in the environment before starting DuckDB. - The [sample `support_tickets` table](support-ticket-data.md). ## Decision model or chat model? | | Decision model: `ai_decide` | Chat model: `ai_classify`, `ai_extract_record` | | --- | --- | --- | | What you get back | A label from your list with a confidence, a level index, or a probability of yes | Text or JSON, checked against your labels or JSON Schema | | Several questions about one row | One request answers all of them | One request per function call, or one JSON Schema with several fields | | Confidence for a threshold | Yes | No | | Free text, such as a reason or a summary | No | Yes | | Providers | `typesafe`, `cloudflare`, `perplexity`, `ollama` 0.35 or later, `systemone` | Any completion provider | Use a decision model for fixed-choice questions at volume, when you want to set your own threshold. Use a chat model when you need an explanation or other free text, or when you only have a chat provider. This page uses both: the decision model for every row, and the chat model only for the rows it is unsure about. ## Ask several questions in one call Each field of the second argument is one question. A `MAP` of labels is a choice, a list is an ordered score, and a `MAP` with the keys `true` and `false` is a yes/no question. The `{instructions, criteria}` form adds an instruction to one question. Save the result in a table, so the queries after this one read saved values instead of calling the model again: ```sql CREATE OR REPLACE TABLE route_decisions AS SELECT ticket_id, ai_decide( subject || chr(10) || body, { team: MAP { 'billing': 'Invoices, payments, refunds and billing contacts', 'performance': 'Slow or failing queries, timeouts and regressions', 'integration': 'Exports, imports and connections to other tools', 'other': 'Anything else, including documentation requests' }, severity: [ 'Routine question', 'Degraded work with a workaround', 'Work is blocked' ], urgent: { instructions: 'Judge urgency from the business impact the customer describes.', criteria: MAP { 'true': 'Needs action today', 'false': 'Can wait for the normal queue' } } }, provider := 'ollama', model := 'nimble', on_error := 'null' ) AS decision FROM support_tickets; ``` Result: `decision` is a `STRUCT(team VARCHAR, team_confidence DOUBLE, severity DOUBLE, severity_confidence DOUBLE, urgent DOUBLE)`: | Field | Meaning | | --- | --- | | `team` | One of the four labels | | `team_confidence` | Confidence in that label, from 0 to 1. NULL if the provider does not return one. | | `severity` | Level index from 0 (`Routine question`) to 2 (`Work is blocked`) | | `severity_confidence` | Confidence in that level | | `urgent` | Probability that the answer is yes, from 0 to 1 | With Ollama, this sends one request per ticket: 4 requests for the sample table. The model is named explicitly because `ai_decide` ignores the session setting `duckdb_ai_model`, which names a chat model. ## Choose a threshold and route each row Pick a confidence threshold and give every row a route: - `auto`: the decision model is confident, so use its answer. - `fallback`: the decision model is unsure, so ask a chat model. - `review`: the decision failed, so a person looks at it. ```sql SET VARIABLE min_team_confidence = 0.8; CREATE OR REPLACE TABLE route_assignments AS SELECT ticket_id, decision.team AS team, decision.team_confidence AS team_confidence, decision.severity AS severity, decision.urgent AS urgent_probability, CASE WHEN decision IS NULL THEN 'review' WHEN decision.team_confidence >= getvariable('min_team_confidence') THEN 'auto' ELSE 'fallback' END AS route FROM route_decisions; SELECT route, count(*) AS tickets FROM route_assignments GROUP BY route ORDER BY route; ``` A NULL `team_confidence` fails the comparison, so that row goes to `fallback`. The threshold `0.8` is only a starting point. Confidence is the model's own score, not a measured accuracy. Pick the threshold from a labeled sample, as shown in [evaluate before a batch run](evaluate-before-batch.md). ## Send uncertain rows to a chat model The `WHERE` clause runs before the model call, so only `fallback` rows reach the chat model. `ai_extract_record` returns a typed `STRUCT`, and the JSON Schema `enum` limits `team` to the same four labels. A reply outside the list fails validation and, with `on_error := 'null'`, becomes NULL: ```sql CREATE OR REPLACE TABLE route_fallback AS SELECT ticket_id, answer.team AS team, answer.reason AS reason FROM ( SELECT r.ticket_id, ai_extract_record( 'Choose the support team for this ticket and give a one-sentence reason.' || chr(10) || t.subject || chr(10) || t.body, '{ "type": "object", "properties": { "team": {"type": "string", "enum": ["billing", "performance", "integration", "other"]}, "reason": {"type": "string"} }, "required": ["team", "reason"] }', provider := 'openai', model := 'gpt-5.6-luna', on_error := 'null' ) AS answer FROM route_assignments AS r JOIN support_tickets AS t USING (ticket_id) WHERE r.route = 'fallback' ); ``` Result: one row per fallback ticket with `team VARCHAR` and `reason VARCHAR`. If you do not need the reason, `ai_classify(text, ['billing', 'performance', 'integration', 'other'])` returns the label alone. ## Combine the answers and fill the review table Accept the confident decisions and the successful fallback answers: ```sql CREATE OR REPLACE TABLE route_final AS SELECT r.ticket_id, CASE WHEN r.route = 'auto' THEN r.team ELSE f.team END AS team, CASE WHEN r.route = 'auto' THEN 'decision model' ELSE 'chat fallback' END AS answered_by, r.severity, r.urgent_probability >= 0.5 AS urgent FROM route_assignments AS r LEFT JOIN route_fallback AS f USING (ticket_id) WHERE r.route = 'auto' OR f.team IS NOT NULL; ``` Send everything else to people: failed decisions, failed fallbacks, and rows whose urgency probability is close to 0.5. A row can be in both tables when its team is settled but its urgency is not. ```sql CREATE OR REPLACE TABLE route_review AS SELECT r.ticket_id, r.route, r.team AS decision_team, r.team_confidence, r.urgent_probability, f.team AS fallback_team, f.reason AS fallback_reason FROM route_assignments AS r LEFT JOIN route_fallback AS f USING (ticket_id) WHERE r.route = 'review' OR (r.route = 'fallback' AND f.team IS NULL) OR r.urgent_probability BETWEEN 0.4 AND 0.6; ``` To be stricter, also review fallback rows where `f.team` differs from the decision model's low-confidence `r.team`. ## Check the calls ```sql SELECT function_name, model, count(*) AS requests, count(*) FILTER (WHERE status <> 'ok') AS failures FROM ai_usage() WHERE function_name IN ('ai_decide', 'ai_extract_record') GROUP BY ALL ORDER BY function_name; ``` Result: one `ai_decide` request per non-NULL ticket, plus one `ai_extract_record` request per fallback row. A failed request also shows its message in the `error` column of `ai_usage()`. ## Limits - **No capture mode.** `on_error` accepts only `'fail'` (the default) and `'null'`. A NULL `decision` does not say why: a NULL input also returns NULL. Read the reason from `ai_usage()` rows with `function_name = 'ai_decide'` and `status = 'error'`. - **One request per row, except TypeSafe.** `ollama`, `cloudflare`, `perplexity` and `systemone` send one request per non-NULL row, run concurrently up to `max_concurrent_requests`. Only `typesafe` batches rows, up to 32 per request (`batch_size`). See [typed decisions](jev-decisions.md). - **Question limits.** A choice takes 1 to 255 labels, and a score takes 2 to 10 levels. Providers enforce their own caps on top of that: for example, Ollama accepts at most 26 options or levels and 64 questions per request. Split a large question set across two calls. - **Constant questions.** Labels, levels and instructions must be constants, so they cannot change per row. - **Model settings.** `ai_decide` ignores `duckdb_ai_model` and `duckdb_ai_base_url`. Set the model with `model :=` or an environment variable such as `OLLAMA_DECISION_MODEL`. - **No generated text.** Generation options such as `temperature` are rejected, and the model cannot explain its answer. Use the chat fallback when you need a reason. ## Clean up ```sql DROP TABLE IF EXISTS route_review; DROP TABLE IF EXISTS route_final; DROP TABLE IF EXISTS route_fallback; DROP TABLE IF EXISTS route_assignments; DROP TABLE IF EXISTS route_decisions; ``` ## Related - [Typed decisions](jev-decisions.md): save, filter and export decision fields, and batch rows with TypeSafe. - [Evaluate before a batch run](evaluate-before-batch.md): measure accuracy and cost on a labeled sample before choosing a model or a threshold. - [Chain several AI steps](chain-ai-steps.md): save each step and rerun only what is missing. - [`ai_decide` reference](../functions.md#ai_decidestate-questions-) # Generate read-only SQL over local tables Use this cookbook when you want a model to write DuckDB `SELECT` statements from local table context. The SQL assistant functions check that the generated SQL is one parser-valid, read-only `SELECT` before returning or running it. | Function | Result | | --- | --- | | `ai_schema_prompt(...)` | One row with `summary VARCHAR`: the table context sent to the model. No provider call. | | `ai_sql(question, ...)` | `VARCHAR` with the generated `SELECT`. It does not run the query. | | `ai_query_data(question, ...)` | The columns of the generated query, after running it. | ## Prerequisites - Configure a completion provider. Hosted providers read their key from the environment, for example `OPENAI_API_KEY`. See the [provider guides](../provider-guides.md). - Create the [sample `support_tickets` table](support-ticket-data.md). ## Decide what the model may see The model receives table names, column names and types. With `sample_rows := N`, it also receives up to N real rows from each table. In the sample data, `internal_note` contains an email address, so leave that column out before you send sample rows. `ai_schema_prompt` reads tables, not views. Copy the columns you want to share into a table: ```sql CREATE OR REPLACE TABLE tickets_for_sql AS SELECT * EXCLUDE (internal_note) FROM support_tickets; ``` For sensitive tables, omit `sample_rows`. The model then sees only the schema. ## Inspect the schema prompt Check what table context the model will see. This makes no provider call: ```sql SELECT summary FROM ai_schema_prompt( include_tables := ['main.tickets_for_sql'], sample_rows := 3 ); ``` The context includes table names, column names, types, and up to three sample rows. ## Generate SQL without running it Use `ai_sql` when you want to inspect or store the generated query: ```sql SELECT ai_sql( 'Which high-priority customers have the lowest satisfaction scores?', include_tables := ['main.tickets_for_sql'], sample_rows := 3 ) AS generated_sql; ``` Result: `generated_sql VARCHAR`, one read-only `SELECT`. ## Generate and run SQL Use `ai_query_data` when you want the generated query to run immediately: ```sql SELECT * FROM ai_query_data( 'Count tickets by priority and customer tier', include_tables := ['main.tickets_for_sql'], sample_rows := 3 ); ``` Result: whatever columns the generated query returns, so the shape can change when the question changes. `ai_query_data` calls the model while DuckDB binds the query, before any rows are read. It caches the generated SQL in the current DuckDB instance, keyed by the question, the schema context, the model and the options. Running the same statement again reuses the cached SQL without another provider call. Check the cache with `SELECT * FROM ai_query_cache_stats();`. ## Retry when generated SQL does not bind A model can invent a column or a function name. With `fix_attempts := N` (0 to 5, default 0), the function binds the generated SQL against your catalog and, when binding fails, sends the error back to the model for up to N more calls: ```sql SELECT * FROM ai_query_data( 'Average satisfaction score by channel for high priority tickets', include_tables := ['main.tickets_for_sql'], fix_attempts := 2 ); ``` Each correction is a normal model call and appears in `ai_usage()`. `ai_sql` accepts the same option. ## Keep context scoped Limit `include_tables` to the tables relevant to the question. For a larger database, this keeps the prompt smaller and reduces the chance that the model uses the wrong table. ```sql SELECT ai_sql( 'Show the average satisfaction score by channel for high priority tickets', include_tables := ['main.tickets_for_sql'], sample_rows := 2 ); ``` ## Validate generated SQL yourself If you store generated SQL before running it, validate it again at the boundary. With `true` as the second argument, the SQL must also bind against the current catalog, so a missing table or column raises an error: ```sql WITH generated AS ( SELECT ai_sql( 'Count tickets by customer tier', include_tables := ['main.tickets_for_sql'], sample_rows := 3 ) AS sql_text ) SELECT ai_validate_read_only_sql(sql_text, true) AS validated_sql FROM generated; ``` Result: `validated_sql VARCHAR`, the input SQL unchanged. Use `ai_is_read_only_sql(sql_text, true)` instead to get a `BOOLEAN` without raising an error. Remove the example table when you are done: ```sql DROP TABLE IF EXISTS tickets_for_sql; ``` See the [SQL assistant reference](../functions.md#sql-assistant-functions) for every option. # 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: ```sql 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](cookbooks/jev-decisions.md) 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. ```sql 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. ```sql 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](provider-guides.md#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/` | `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 | `/v1/systemone` | optional `SYSTEMONE_API_KEY` | OpenAI's Decisions API is not supported until OpenAI publishes its request schema. Example: ```sql 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: ```text 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 | ` VARCHAR`, the chosen label, and `_confidence DOUBLE` | | `['lowest level', ..., 'highest level']` | Score over 2 to 10 ordered levels | ` DOUBLE`, the level index from 0 to levels minus 1, and `_confidence DOUBLE` | | `MAP {'true': 'yes criterion', 'false': 'no criterion'}` | Yes/no question (TypeSafe calls it a Noul) | ` DOUBLE`, the probability of yes | | `{instructions: '...', criteria: }` | 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. `_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](#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](cookbooks/jev-decisions.md) for a table-to-Parquet workflow and [route rows by decision confidence](cookbooks/decision-routing.md) 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: ```sql 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: ```text 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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`](#ai_decidestate-questions-) with a decision-model provider. Use `ai_provider_call` for the raw probability distribution. Example: ```sql 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: ```sql 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: ```sql 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`](#ai_build_classifiertext-labels-) example; profiles are created with [`CREATE EXTERNAL MODEL`](#create-external-model-and-ai_models). Example: ```sql 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: ```sql 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`](#ai_decidestate-questions-) with a `MAP {'true': ..., 'false': ...}` question. Example: ```sql 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: ```sql 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: ```sql 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`](#create-external-model-and-ai_models). Example: ```sql 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: ```sql 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`: ```sql 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: ```sql 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: ```sql 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: ```sql SELECT ai_sql('count orders by status'); ``` Example with self-correction: ```sql 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: ```sql 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): ```sql 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: ```sql 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: ```sql 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: ```sql SELECT sql FROM ai_fix_sql('SEELECT status, count(*) FRUM orders GROUP BY status'); ``` Example with the error message and bind verification: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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. ```sql 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. ```sql 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: ```sql 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: ```sql SELECT * FROM ai_clear_cache(); ``` Result: one `cleared BOOLEAN` column #### `ai_secrets()` Description: Lists configured `duckdb_ai` secrets with credential values redacted. Example: ```sql 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: ```sql 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. ```sql 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: ```sql 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](runtime-behavior.md) 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: ```sql 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`](#ai_decidestate-questions-). 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: ```sql 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'); ``` # Provider guides This page shows how to configure each supported provider, with one simple end-to-end example each. Examples assume the extension is installed and loaded: ```sql INSTALL ai FROM community; LOAD ai; ``` (Or build from source with `GEN=ninja make release` and run `./build/release/duckdb`.) Each guide uses a DuckDB secret for provider, model, and base URL settings. The API key is read from the matching environment variable when the provider call runs. If you prefer a fully DuckDB-managed secret, add `API_KEY '...'` to the `CREATE SECRET` statement. After any provider call, inspect the local usage buffer: ```sql SELECT function_name, provider, protocol, model, http_status, elapsed_ms FROM ai_usage() ORDER BY event_id DESC LIMIT 1; ``` ## Provider matrix | Provider | Protocol | Default model | Credentials | Base URL behavior | | --- | --- | --- | --- | --- | | `ollama` | Ollama chat and embeddings | `llama3.2`; embeddings use `nomic-embed-text` | Optional `OLLAMA_API_KEY` | Defaults to `http://localhost:11434`; `OLLAMA_HOST` overrides it. | | `openai` | OpenAI-compatible chat and embeddings | `gpt-5.6-luna`; embeddings use `text-embedding-3-small` | `OPENAI_API_KEY` | Defaults to `https://api.openai.com/v1`. | | `azure` | OpenAI-compatible chat and embeddings | `gpt-5.6-luna`; embeddings use `text-embedding-3-small` | `AZURE_OPENAI_API_KEY` | Appends `/openai/v1` to `AZURE_OPENAI_BASE_URL`, `AZURE_OPENAI_ENDPOINT`, or secret `BASE_URL` when needed. | | `anthropic` / `claude` | Anthropic Messages | `claude-haiku-5-5` | `ANTHROPIC_API_KEY` or `CLAUDE_API_KEY` | Defaults to `https://api.anthropic.com/v1`. | | `bedrock` | OpenAI-compatible chat | `openai.gpt-oss-120b` | `AWS_BEDROCK_API_KEY`, `AWS_BEARER_TOKEN_BEDROCK`, or `BEDROCK_API_KEY` | Set `AWS_REGION`, `AWS_BEDROCK_REGION`, `AWS_BEDROCK_BASE_URL`, or secret `BASE_URL`. | | `cerebras` | OpenAI-compatible chat | `gpt-oss-120b` | `CEREBRAS_API_KEY` | Defaults to `https://api.cerebras.ai/v1`. | | `cloudflare` / `workers_ai` | OpenAI-compatible chat and embeddings | `@cf/zai-org/glm-4.7-flash`; embeddings use `@cf/baai/bge-base-en-v1.5` | `CLOUDFLARE_API_KEY`, `CLOUDFLARE_API_TOKEN`, or `CLOUDFLARE_AUTH_TOKEN` | Derives the endpoint from `CLOUDFLARE_ACCOUNT_ID`, or accepts `CLOUDFLARE_WORKERS_AI_BASE_URL`, `CLOUDFLARE_AI_BASE_URL`, or secret `BASE_URL`. | | `cohere` | OpenAI-compatible chat and embeddings | `command-a-plus-05-2026`; embeddings use `embed-v4.0` | `COHERE_API_KEY` | Defaults to `https://api.cohere.ai/compatibility/v1`. | | `dashscope` / `qwen` | OpenAI-compatible chat and embeddings | `qwen-plus`; embeddings use `text-embedding-v4` | `DASHSCOPE_API_KEY`, `ALIBABA_API_KEY`, or `QWEN_API_KEY` | Defaults to `https://dashscope-intl.aliyuncs.com/compatible-mode/v1`; override with a workspace-specific base URL when needed. | | `deepinfra` | OpenAI-compatible chat and embeddings | `meta-llama/Meta-Llama-3.1-8B-Instruct-Turbo`; embeddings use `BAAI/bge-large-en-v1.5` | `DEEPINFRA_API_KEY` | Defaults to `https://api.deepinfra.com/v1/openai`. | | `fireworks` | OpenAI-compatible chat and embeddings | `accounts/fireworks/models/gpt-oss-120b`; embeddings use `nomic-ai/nomic-embed-text-v1.5` | `FIREWORKS_API_KEY` | Defaults to `https://api.fireworks.ai/inference/v1`. | | `gemini` / `gcp` / `google` | OpenAI-compatible chat and embeddings | `gemini-3.8-flash`; embeddings use `gemini-embedding-2` | `GEMINI_API_KEY` | Defaults to Google's OpenAI-compatible endpoint. | | `groq` | OpenAI-compatible chat | `openai/gpt-oss-20b` | `GROQ_API_KEY` | Defaults to `https://api.groq.com/openai/v1`. | | `huggingface` / `hf` | OpenAI-compatible chat | `openai/gpt-oss-120b` | `HF_TOKEN`, `HUGGINGFACE_API_KEY`, or `HUGGING_FACE_HUB_TOKEN` | Defaults to `https://router.huggingface.co/v1`. | | `hunyuan` / `tencent_hunyuan` | OpenAI-compatible chat through Tencent TokenHub | `hy3` | `HUNYUAN_API_KEY`, `TOKENHUB_API_KEY`, or `TENCENT_TOKENHUB_API_KEY` | Defaults to `https://tokenhub.tencentmaas.com/v1`; `TOKENHUB_BASE_URL` selects another TokenHub region. | | `minimax` | OpenAI-compatible chat | `MiniMax-M2.7` | `MINIMAX_API_KEY` or `MINI_MAX_API_KEY` | Defaults to `https://api.minimax.io/v1`. | | `mimo` / `xiaomi` | OpenAI-compatible chat | `mimo-v2.6-flash` | `MIMO_API_KEY` (sent in the `api-key` header) | Defaults to `https://api.xiaomimimo.com/v1`. | | `mistral` | OpenAI-compatible chat and embeddings | `mistral-small-latest`; embeddings use `mistral-embed` | `MISTRAL_API_KEY` | Defaults to `https://api.mistral.ai/v1`. | | `moonshot` / `kimi` | OpenAI-compatible chat | `kimi-k3` | `MOONSHOT_API_KEY` or `KIMI_API_KEY` | Defaults to `https://api.moonshot.ai/v1`. | | `nebius` / `nebius_token_factory` | OpenAI-compatible chat | `Qwen/Qwen3-30B-A3B-Instruct-2507` | `NEBIUS_API_KEY` or `TOKEN_FACTORY_API_KEY` | Defaults to `https://api.tokenfactory.nebius.com/v1`. | | `nvidia` / `nvidia_nim` | OpenAI-compatible chat | `nvidia/nemotron-3-super-120b-a12b` | `NVIDIA_API_KEY` | Defaults to `https://integrate.api.nvidia.com/v1`. | | `zai` / `zhipu` | OpenAI-compatible chat and embeddings | `glm-4.7-flash`; embeddings use `embedding-3` | `ZAI_API_KEY` | Defaults to `https://api.z.ai/api/paas/v4`. | | `deepseek` | OpenAI-compatible chat | `deepseek-flash` | `DEEPSEEK_API_KEY` | Defaults to `https://api.deepseek.com`. | | `openrouter` | OpenAI-compatible chat and embeddings | `openai/gpt-4o-mini`; embeddings use `openai/text-embedding-3-small` | `OPENROUTER_API_KEY` | Defaults to `https://openrouter.ai/api/v1`. | | `databricks` | OpenAI-compatible chat | `databricks-gpt-oss-120b` | `DATABRICKS_TOKEN` | Derives `/serving-endpoints` from `DATABRICKS_HOST`, or accepts full Model Serving, AI Gateway, or chat-completions URLs. | | `snowflake` | OpenAI-compatible chat | `claude-sonnet-4-5` | `SNOWFLAKE_PAT` or `SNOWFLAKE_TOKEN` | Derives `/api/v2/cortex/v1` from Snowflake account URL, host, or account id. | | `perplexity` / `pplx` | OpenAI-compatible chat; Decisions API for `ai_decide` | `sonar`; decisions use `pplx-decider-v1.1-27b` | `PERPLEXITY_API_KEY` | Defaults to `https://api.perplexity.ai`. | | `poe` | OpenAI-compatible chat | `GPT-5.4` | `POE_API_KEY` | Defaults to `https://api.poe.com/v1`. | | `qianfan` / `ernie` | OpenAI-compatible chat | `ernie-5.1` | `QIANFAN_API_KEY`, `BAIDU_QIANFAN_API_KEY`, or `BAIDU_API_KEY` | Defaults to `https://qianfan.baidubce.com/v2`. | | `sambanova` | OpenAI-compatible chat | `Meta-Llama-3.3-70B-Instruct` | `SAMBANOVA_API_KEY` | Defaults to `https://api.sambanova.ai/v1`. | | `siliconflow` | OpenAI-compatible chat | `Qwen/Qwen2.5-72B-Instruct` | `SILICONFLOW_API_KEY` | Defaults to `https://api.siliconflow.com/v1`. | | `together` | OpenAI-compatible chat and embeddings | `meta-llama/Llama-3.3-70B-Instruct-Turbo`; embeddings use `intfloat/multilingual-e5-large-instruct` | `TOGETHER_API_KEY` | Defaults to `https://api.together.xyz/v1`. | | `stepfun` / `step` | OpenAI-compatible chat | `step-3.5-flash` | `STEPFUN_API_KEY` or `STEP_API_KEY` | Defaults to `https://api.stepfun.com/v1`. | | `vercel` / `vercel_ai_gateway` | OpenAI-compatible chat and embeddings | `openai/gpt-4o-mini`; embeddings use `openai/text-embedding-3-small` | `AI_GATEWAY_API_KEY`, `VERCEL_AI_GATEWAY_API_KEY`, or `VERCEL_OIDC_TOKEN` | Defaults to `https://ai-gateway.vercel.sh/v1`. | | `vertex` / `google_vertex` | OpenAI-compatible chat | `google/gemini-3.8-flash` | `VERTEX_AI_ACCESS_TOKEN`, `GOOGLE_CLOUD_ACCESS_TOKEN`, or `VERTEX_API_KEY` | Derives the endpoint from `GOOGLE_CLOUD_PROJECT`, or accepts `VERTEX_AI_BASE_URL`, `GOOGLE_VERTEX_BASE_URL`, or secret `BASE_URL`. | | `volcengine` / `doubao` | OpenAI-compatible chat | `doubao-seed-2-1-pro-260628` | `VOLCENGINE_API_KEY`, `ARK_API_KEY`, or `DOUBAO_API_KEY` | Defaults to `https://ark.cn-beijing.volces.com/api/v3`. | | `xai` / `grok` | OpenAI-compatible chat | `grok-4.6` | `XAI_API_KEY` | Defaults to `https://api.x.ai/v1`. | | `typesafe` / `jev` | TypeSafe System One decisions | `jev-latest` | `TYPESAFE_API_KEY` | Defaults to `https://api.typesafe.ai/v1` and calls `/systemone`. | | `systemone` / `decision` | Any System One decision endpoint | none; pass `model :=` or set `SYSTEMONE_MODEL` | Optional `SYSTEMONE_API_KEY` | Requires `base_url`, `SYSTEMONE_BASE_URL` or secret `BASE_URL`; calls `/v1/systemone`. | | `openai_privacy_filter` | Dedicated redaction endpoint | `openai/privacy-filter` | Optional `OPENAI_PRIVACY_FILTER_API_KEY` | Defaults to `http://localhost:8080` and calls `POST /redact`. | | `openai_compatible` / `local` | OpenAI-compatible chat and embeddings | `gpt-4o-mini`; embeddings use `text-embedding-3-small` | Optional `OPENAI_COMPATIBLE_API_KEY` | Requires `BASE_URL` or `OPENAI_COMPATIBLE_BASE_URL`. | | `llamacpp` / `llama.cpp` | OpenAI-compatible chat and embeddings | `default` (llama-server answers with its loaded model) | Optional `LLAMACPP_API_KEY` (`llama-server --api-key`) | Defaults to `http://localhost:8080/v1`. Embeddings need `llama-server --embeddings`. | For guidance on choosing providers, credentials, logging, cost, throughput, and PII workflows, see [Best practices](best-practices.md). ## Native APIs for reasoning providers This section covers the native APIs of DeepSeek, Qwen, GLM/Z.ai, Kimi, MiniMax, Tencent HY3/HY4 and Xiaomi MiMo: reasoning, tool calls and Anthropic-style Messages endpoints. The native JSON interface described in the [function reference](functions.md#native-provider-json) preserves reasoning and tools across protocol round trips. All model IDs are passed through, including account-specific deployments and dated versions. Models do not need a built-in pricing/catalog entry to be called. The table is a documentation snapshot reviewed on **2026-09-14**, not a live account availability check. Native transport is tested with mock HTTP for all seven providers. Real inference, model limits, regional availability and pricing are **not live-verified**. No new hard-coded prices or limits are inferred from model names. Check each linked catalog for current lifecycle and account terms. | Provider | Chat selection | Other documented text APIs and endpoints | Credentials | | --- | --- | --- | --- | | DeepSeek | `deepseek`; explicit V4 Flash/Pro model IDs | [Thinking/tools](https://api-docs.deepseek.com/guides/thinking_mode), JSON output; FIM uses a separately selected beta endpoint/model | `DEEPSEEK_API_KEY` | | Qwen | `qwen` / `dashscope`; explicit Qwen model ID | [Chat/Responses/embeddings](https://docs.qwencloud.com/api-reference/toolkitframework/openai-compatible/overview); Messages at `https://dashscope-intl.aliyuncs.com/apps/anthropic/v1/messages`; Responses at `https://dashscope-intl.aliyuncs.com/api/v2/apps/protocols/compatible-mode/v1/responses` | `DASHSCOPE_API_KEY` / `QWEN_API_KEY` | | GLM | `zai`; explicit GLM model ID | [Chat](https://docs.z.ai/api-reference/llm/chat-completion), embeddings; [Messages](https://docs.z.ai/devpack/tool/claude) at `https://api.z.ai/api/anthropic/v1/messages` | `ZAI_API_KEY` | | Kimi | `kimi` / `moonshot`; explicit Kimi ID | [Chat/Responses/Messages](https://platform.moonshot.ai/docs/introduction); Messages at `https://api.moonshot.ai/anthropic/v1/messages` | `MOONSHOT_API_KEY` / `KIMI_API_KEY` | | MiniMax | `minimax`; explicit MiniMax ID | [Messages](https://platform.minimax.io/docs/api-reference/text-chat-anthropic) at `https://api.minimax.io/anthropic/v1/messages` | `MINIMAX_API_KEY` | | Tencent | `hunyuan` / `tencent`; `hy3`, `hy4-preview` | [Chat, Responses, Messages](https://www.tencentcloud.com/document/product/1300/80632); append the respective path to the selected regional host | `HUNYUAN_API_KEY` / `TOKENHUB_API_KEY` | | MiMo | `mimo`, `xiaomi`, `xiaomi_mimo`; `mimo-v2.6-flash` default, explicit IDs supported | [Chat](https://mimo.mi.com/docs/en-US/api/chat/openai-api) at `https://api.xiaomimimo.com/v1/chat/completions`; [Messages](https://mimo.mi.com/docs/en-US/api/chat/anthropic-api) at `https://api.xiaomimimo.com/anthropic/v1/messages` | `MIMO_API_KEY` via `api-key` header | Tencent's documented regional bases are Singapore `https://tokenhub-intl.tencentcloudmaas.com/v1`, Guangzhou `https://tokenhub.tencentcloudmaas.com/v1`, and US `https://tokenhub-us.tencentcloudmaas.tech/v1`. Select the region matching your key explicitly. The extension's default, `https://tokenhub.tencentmaas.com/v1`, is kept for existing setups. HY4 is a preview; activation and model availability are controlled by Tencent. Qwen native reranking has model-dependent paths and payloads. Use [`qwen3-rerank`](https://www.alibabacloud.com/help/en/model-studio/text-rerank-api) with the full workspace `/compatible-mode/v1/reranks` endpoint, `api := 'rerank'`, and a body containing `model`, `query`, `documents`, and optional `top_n`. GTE reranking uses nested `input`/`parameters` and a different endpoint; send its documented body unchanged. The existing `ai_rerank` is completion-based scoring, not a claim that every provider has a native reranking model. No native embeddings or reranking are claimed for providers without a verified public reference. Reasoning options vary: DeepSeek, GLM and MiMo use `thinking`; Qwen uses `enable_thinking` and supports `preserve_thinking` on selected models. Preserve returned reasoning state when replying to tool calls. Some Qwen models require streaming; use native JSON with `stream:true` and read the buffered event array. Use the provider's JSON-object response format when JSON Schema is unsupported, then validate the result locally. Coding/subscription keys may require different endpoints from general API keys. No automatic account, region or protocol fallback is performed. Image, audio, video generation and asynchronous media jobs are out of scope for this extension. ```sql SELECT ai_complete('Explain columnar storage.', provider := 'mimo', request_options := '{"thinking":{"type":"disabled"}}', max_tokens := 256); ``` ## Decision models Decision models answer typed questions about an input with probabilities instead of generating text. Use them for classification, routing, moderation and triage at high volume. `ai_decide` sends the same SQL to every supported provider; see the [function reference](functions.md#ai_decidestate-questions-) for question types and result fields. | Provider | Default model | Setup | | --- | --- | --- | | [TypeSafe Jev](#typesafe-jev) | `jev-latest` | `TYPESAFE_API_KEY` | | Cloudflare Clef | `clef` (also `clef-flash`) | `CLOUDFLARE_API_KEY` and `CLOUDFLARE_ACCOUNT_ID`, as for [Workers AI](#cloudflare-workers-ai) | | Perplexity | `pplx-decider-v1.1-27b` (also `pplx-decider-v1-27b`) | `PERPLEXITY_API_KEY` | | Ollama | `nimble` (also `tev1`, `tev1:0.8b`) | Ollama 0.35 or later, no key | | Any `/v1/systemone` endpoint (`systemone`) | none, `model` is required | `base_url := ...` or `SYSTEMONE_BASE_URL`; `model := ...` or `SYSTEMONE_MODEL`; optional `SYSTEMONE_API_KEY` | ```sql -- Local, no API key: ollama pull nimble SELECT ai_decide('Checkout fails for every customer', { team: MAP {'billing': 'Payments and refunds', 'technical': 'Errors and outages'}, urgent: MAP {'true': 'Needs action now', 'false': 'Can wait'} }, provider := 'ollama') AS decision; -- Cloudflare Workers AI SELECT ai_decide(body, {team: MAP {'billing': 'Payments', 'technical': 'Errors'}}, provider := 'cloudflare', model := 'clef-flash') AS decision FROM support_tickets; -- A self-hosted Clef or another System One compatible service CREATE OR REPLACE SECRET decisions ( TYPE duckdb_ai, AI_PROVIDER 'systemone', BASE_URL 'https://decisions.internal.example', MODEL 'clef' ); SELECT ai_decide(body, {spam: MAP {'true': 'Spam', 'false': 'Legitimate'}}, secret := 'decisions') FROM messages; ``` Each question field becomes a result field. A choice returns `` `VARCHAR` plus `_confidence`, a yes/no question returns `` as the probability of yes, and a score returns the level index as a `DOUBLE`, so a query can filter on `decision.urgent > 0.5`. Choosing a decision model for a chat provider does not change that provider's chat or embedding behavior: `ai_complete(..., provider := 'cloudflare')` still uses Workers AI chat models. `ai_decide` ignores the session settings `duckdb_ai_model` and `duckdb_ai_base_url`, and the `MODEL` of a chat provider's secret, because they name chat models. Set the model with `model := ...` or `_DECISION_MODEL`, for example `CLOUDFLARE_DECISION_MODEL=clef-flash` or `OLLAMA_DECISION_MODEL=tev1`. Per-question `instructions` (the `{instructions, criteria}` form) work with every provider. Only TypeSafe batches up to 32 rows into one request. Other providers send one request per row, concurrently up to `max_concurrent_requests`. OpenAI announced a Decisions API, but it has no public request schema yet, so `ai_decide` does not support it. Sources: [Cloudflare Clef](https://developers.cloudflare.com/workers-ai/models/clef/), [Perplexity Decisions API](https://docs.perplexity.ai/docs/decisions/quickstart), [Ollama decision models](https://ollama.com/blog/ollama-now-supports-jev-style-decision-models), [TypeSafe API reference](https://docs.typesafe.ai/api). ## TypeSafe Jev Jev evaluates state against typed questions and returns probabilities, choices, and rubric scores. It does not generate text or embeddings. Use `typesafe` (or its alias `jev`) for the direct TypeSafe API. The supported entry points are `ai_decide`, `ai_jev`, `ai_provider_call`, `ai_classify`, and `ai_filter`. Other AI task functions, including `ai_score`, require a completion provider. Generation options such as `temperature`, `max_tokens`, and `system_prompt` are rejected. ```sh export TYPESAFE_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET typesafe_ai ( TYPE duckdb_ai, AI_PROVIDER 'typesafe', MODEL 'jev-latest' ); SELECT ai_decide('I was charged twice. Please refund me.', { department: MAP {'billing': 'Payments and refunds', 'other': 'Other requests'}, refund_requested: {instructions: 'Does the text explicitly request a refund?', criteria: MAP {'true': 'Asks for money back', 'false': 'Does not ask for money back'}} }, provider := 'typesafe', secret := 'typesafe_ai') AS decision; ``` Use `decision.department`, `decision.department_confidence` and `decision.refund_requested` downstream. With TypeSafe, `ai_decide` batches up to 32 rows per request. `ai_jev(text, questions)` is the same call with the provider fixed to TypeSafe. Follow the [typed decisions cookbook](cookbooks/jev-decisions.md) to save results before filtering or exporting them. `ai_classify` and `ai_filter` also accept `provider := 'typesafe'`: classification sends a native choice question, and filtering sends a yes/no question and returns `true` when its probability is at least `0.5`. Both send one request per row. ```sql SELECT ai_classify( 'I was charged twice.', ['billing', 'technical', 'other'], secret := 'typesafe_ai' ) AS department; ``` For full probability distributions, the resolved model version or shared state, use `ai_provider_call`: ```sql SELECT ai_provider_call( '{ "model": "jev-1.13.0", "state": "I was charged twice. Please refund the duplicate.", "questions": { "refund_requested": { "type": "noul", "instructions": "Does the text explicitly request a refund?" }, "department": { "type": "choice", "instructions": "Which team should handle this?", "criteria": { "billing": "Payments and refunds", "other": "Other requests" } } } }', secret := 'typesafe_ai' ); ``` Native requests specify `model` in the body, even when a secret contains a model. The result preserves the complete response, including `answers`, per-option probabilities, confidence, the resolved model version, and usage. The extension appends `/systemone` to the base URL automatically. As checked on September 18, 2026, TypeSafe lists `jev-1.13.0` with aliases `jev-latest` and `jev-preview`, at $0.042 per million input tokens and free output tokens. Pin the version when tuning decision thresholds. Choice supports up to 255 options, and Score accepts 2 to 10 ordered levels. A Score uses the level indices, so a three-level rubric produces values from 0 to 2. The extension's mock tests check HTTP contracts, not Jev's live latency or prediction quality. Follow the [typed decisions cookbook](cookbooks/jev-decisions.md) to reduce repeated state and calls. See TypeSafe's [API reference](https://docs.typesafe.ai/api), [models and limits](https://docs.typesafe.ai/models), and [known limitations](https://docs.typesafe.ai/model-jaggedness/jev-1.13). ## Ollama Use Ollama for local models without a hosted API key. ```sh ollama serve ollama pull qwen3.8:27b ollama pull nomic-embed-text ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET ollama_ai ( TYPE duckdb_ai, AI_PROVIDER 'ollama', MODEL 'qwen3.8:27b' ); SELECT ai_complete( 'Write one sentence about why DuckDB is useful for analytics.', secret := 'ollama_ai', timeout_seconds := 120 ) AS answer; SELECT ai_embed( 'DuckDB local analytics', secret := 'ollama_ai', model := 'nomic-embed-text' )[1] AS first_embedding_value; ``` If Ollama runs on a non-default host, set `OLLAMA_HOST` before starting DuckDB or add `BASE_URL 'http://host:11434'` to the secret. Without a model setting, `provider := 'ollama'` uses `llama3.2`, so pull that model or set one as shown. For `ai_decide`, also run `ollama pull nimble` (Ollama 0.35 or later). `ai_decide` ignores the secret's chat model and uses `nimble` unless you pass `model := ...` or set `OLLAMA_DECISION_MODEL`. ## OpenAI ```sh export OPENAI_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET openai_ai ( TYPE duckdb_ai, AI_PROVIDER 'openai', MODEL 'gpt-5.6-luna' ); SELECT ai_complete( 'Summarize DuckDB in one short sentence.', secret := 'openai_ai' ) AS answer; SELECT ai_complete_json( 'Return a JSON object with keys "name" and "kind" for DuckDB.', secret := 'openai_ai', response_schema := '{ "type": "object", "properties": { "name": {"type": "string"}, "kind": {"type": "string"} }, "required": ["name", "kind"] }' ) AS profile_json; SELECT ai_embed( 'DuckDB vector search', secret := 'openai_ai', model := 'text-embedding-3-small' )[1] AS first_embedding_value; ``` For text completions, set `model := 'gpt-6-astra'`, `gpt-6.1-sol`, `gpt-6-sol`, or `gpt-6-luna` to use the GPT-6 family. The existing default remains `gpt-5.6-luna`. These models support this extension's Chat Completions requests without tools. GPT-6 reasoning requests omit the `temperature` option. GPT-6 Sol and Luna preserve it when `request_options := '{"reasoning_effort":"none"}'` disables reasoning. Use `request_options` for provider-native controls such as `reasoning_effort`. With `prompt_cache := true` and a stable system prompt, the extension uses an explicit cache breakpoint for GPT-6. Built-in cost estimates use standard text token rates for requests up to 272K input tokens; long-context, regional, Batch/Flex/Fast, and hosted-tool charges require separate accounting. See [`ai_model_prices()`](functions.md#ai_model_prices) for the catalog rates. ## Azure OpenAI Use your Azure OpenAI resource URL and deployment name. The extension appends `/openai/v1` to the base URL when needed. ```sh export AZURE_OPENAI_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET azure_ai ( TYPE duckdb_ai, AI_PROVIDER 'azure', BASE_URL 'https://my-resource.openai.azure.com', MODEL 'gpt-5.6-luna' ); SELECT ai_complete( 'Explain DuckDB to a data analyst in one sentence.', secret := 'azure_ai' ) AS answer; SELECT ai_embed( 'DuckDB vector search', secret := 'azure_ai', model := 'text-embedding-3-small' )[1] AS first_embedding_value; ``` If your Azure deployment names differ from the model names above, use the deployment name in `MODEL` or `model := ...`. The default deployment name is `gpt-5.6-luna`, because Azure no longer offers `gpt-4o` to new deployments. ## Claude / Anthropic ```sh export ANTHROPIC_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET claude_ai ( TYPE duckdb_ai, AI_PROVIDER 'anthropic', MODEL 'claude-haiku-5-5' ); SELECT ai_complete( 'Explain DuckDB in one concise paragraph.', secret := 'claude_ai', max_tokens := 160 ) AS answer; SELECT ai_summarize( 'DuckDB is an in-process analytical database built for fast local queries.', secret := 'claude_ai', max_tokens := 80 ) AS summary; SELECT ai_complete_json( 'Extract DuckDB as a database profile.', secret := 'claude_ai', response_schema := '{ "type": "object", "properties": {"name": {"type": "string"}}, "required": ["name"], "additionalProperties": false }' ) AS profile; ``` For `response_schema := ...`, the extension sends Anthropic's `output_config.format` JSON Schema request shape. Claude is configured for completion calls; embeddings are not configured for this provider. The default model is `claude-haiku-5-5`. Set `model := 'claude-fable-5-1'`, `claude-opus-5-5`, or `claude-sonnet-5-5` for larger models, or `claude-haiku-4-5` to keep the previous default. The 5.x models reject non-default sampling parameters, so the extension omits `temperature` for them. Haiku 5.5, Fable 5.1 and Opus 5.5 use adaptive thinking by default, and Haiku 5.5's tokenizer counts about 30% more tokens than Haiku 4.5 for the same text. Increase `max_tokens` when the requested answer needs room for both thinking and visible text. Built-in estimates use standard text rates and model-specific cache-read prices; long-context and other service charges require separate accounting. ## Gemini Gemini uses Google's OpenAI-compatible endpoint. ```sh export GEMINI_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET gemini_ai ( TYPE duckdb_ai, AI_PROVIDER 'gemini', MODEL 'gemini-3.8-flash' ); SELECT ai_complete( 'Write one sentence about DuckDB extensions.', secret := 'gemini_ai' ) AS answer; SELECT ai_embed( 'DuckDB vector search', secret := 'gemini_ai', model := 'gemini-embedding-2' )[1] AS first_embedding_value; ``` Provider aliases `gcp`, `google`, and `google_gemini` also resolve to Gemini. The default model is `gemini-3.8-flash`. Gemini 3.8, 3.7 and 3.6 Flash and 3.5 Flash-Lite deprecate sampling parameters, so `duckdb_ai` omits `temperature` for these models even when the generic SQL option is provided. Built-in cost estimates use Google's introductory 3.8/3.7/3.6 Flash pricing through December 31, 2026 and roll over to the published standard rate on January 1, 2027. ## Mistral ```sh export MISTRAL_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET mistral_ai ( TYPE duckdb_ai, AI_PROVIDER 'mistral', MODEL 'mistral-small-latest' ); SELECT ai_complete( 'Give one practical use case for DuckDB and AI functions.', secret := 'mistral_ai' ) AS answer; SELECT ai_embed( 'DuckDB vector search', secret := 'mistral_ai', model := 'mistral-embed' )[1] AS first_embedding_value; ``` ## Z.ai ```sh export ZAI_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET zai_ai ( TYPE duckdb_ai, AI_PROVIDER 'zai', MODEL 'glm-4.7-flash' ); SELECT ai_complete( 'Summarize DuckDB in one sentence.', secret := 'zai_ai' ) AS answer; SELECT ai_embed( 'DuckDB vector search', secret := 'zai_ai', model := 'embedding-3' )[1] AS first_embedding_value; ``` Provider aliases `zai` and `zhipu` resolve to the same provider. ## DeepSeek ```sh export DEEPSEEK_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET deepseek_ai ( TYPE duckdb_ai, AI_PROVIDER 'deepseek', MODEL 'deepseek-flash' ); SELECT ai_complete( 'Write one sentence about using SQL with language models.', secret := 'deepseek_ai' ) AS answer; SELECT ai_classify( 'Customer says the invoice is overdue.', 'billing, support, sales, other', secret := 'deepseek_ai' ) AS category; ``` DeepSeek is configured for completion calls. Embeddings are not configured for this provider. The default model is `deepseek-flash` (DeepSeek-V4.1-Flash). DeepSeek retired `deepseek-v4-flash` on 2026-09-10 and currently routes it to `deepseek-flash`. Thinking is on by default; for short row-by-row answers, pass `request_options := '{"thinking":{"type":"disabled"}}'`. Built-in cost estimates use the published peak-hour cache-miss rates; DeepSeek charges 50% less during its documented off-peak windows. ## OpenRouter ```sh export OPENROUTER_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET openrouter_ai ( TYPE duckdb_ai, AI_PROVIDER 'openrouter', MODEL 'openai/gpt-4o-mini' ); SELECT ai_complete( 'Explain DuckDB extensions in one sentence.', secret := 'openrouter_ai' ) AS answer; SELECT ai_embed( 'DuckDB vector search', secret := 'openrouter_ai', model := 'openai/text-embedding-3-small' )[1] AS first_embedding_value; ``` OpenRouter model names include the upstream provider prefix, for example `openai/gpt-4o-mini`. Set `OPENROUTER_HTTP_REFERER` and `OPENROUTER_X_TITLE` to send OpenRouter's optional attribution headers. ## Groq ```sh export GROQ_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET groq_ai ( TYPE duckdb_ai, AI_PROVIDER 'groq', MODEL 'openai/gpt-oss-20b' ); SELECT ai_complete( 'Explain vectorized SQL execution in one sentence.', secret := 'groq_ai' ) AS answer; ``` Groq is configured for completion calls. Embeddings are not configured for this provider. ## Together AI ```sh export TOGETHER_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET together_ai ( TYPE duckdb_ai, AI_PROVIDER 'together', MODEL 'meta-llama/Llama-3.3-70B-Instruct-Turbo' ); SELECT ai_complete( 'Write one sentence about local-first analytics.', secret := 'together_ai' ) AS answer; SELECT ai_embed( 'DuckDB vector search', secret := 'together_ai', model := 'intfloat/multilingual-e5-large-instruct' )[1] AS first_embedding_value; ``` Together removed its serverless embedding models in September 2026, so the default embedding model `intfloat/multilingual-e5-large-instruct` only works with a dedicated endpoint that serves it. Pass `model := ...` with the model your dedicated endpoint runs, or use another embedding provider. ## Fireworks AI ```sh export FIREWORKS_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET fireworks_ai ( TYPE duckdb_ai, AI_PROVIDER 'fireworks', MODEL 'accounts/fireworks/models/gpt-oss-120b' ); SELECT ai_complete( 'Give one reason to enrich data inside SQL.', secret := 'fireworks_ai' ) AS answer; SELECT ai_embed( 'DuckDB vector search', secret := 'fireworks_ai', model := 'nomic-ai/nomic-embed-text-v1.5' )[1] AS first_embedding_value; ``` Fireworks model IDs are passed through unchanged. This supports serverless base models such as `accounts/fireworks/models/gpt-oss-120b`, fast routers such as `accounts/fireworks/routers/kimi-k2p6-turbo`, account deployments, and embedding models such as `fireworks/qwen3-embedding-8b`. Use a model ID available to the configured Fireworks account. ## DeepInfra ```sh export DEEPINFRA_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET deepinfra_ai ( TYPE duckdb_ai, AI_PROVIDER 'deepinfra', MODEL 'meta-llama/Meta-Llama-3.1-8B-Instruct-Turbo' ); SELECT ai_complete( 'Summarize why open-weight hosted inference is useful.', secret := 'deepinfra_ai' ) AS answer; SELECT ai_embed( 'DuckDB vector search', secret := 'deepinfra_ai', model := 'BAAI/bge-large-en-v1.5' )[1] AS first_embedding_value; ``` ## Cerebras ```sh export CEREBRAS_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET cerebras_ai ( TYPE duckdb_ai, AI_PROVIDER 'cerebras', MODEL 'gpt-oss-120b' ); SELECT ai_complete( 'Explain fast inference for analytical workflows in one sentence.', secret := 'cerebras_ai' ) AS answer; ``` Cerebras is configured for completion calls. Embeddings are not configured for this provider. ## Cohere ```sh export COHERE_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET cohere_ai ( TYPE duckdb_ai, AI_PROVIDER 'cohere', MODEL 'command-a-plus-05-2026' ); SELECT ai_complete( 'Classify this support note as billing, support, sales, or other.', secret := 'cohere_ai' ) AS answer; SELECT ai_embed( 'DuckDB vector search', secret := 'cohere_ai', model := 'embed-v4.0' )[1] AS first_embedding_value; ``` ## Cloudflare Workers AI Cloudflare Workers AI's OpenAI-compatible endpoint is account scoped. Set `CLOUDFLARE_ACCOUNT_ID`, or provide a full `BASE_URL`. ```sh export CLOUDFLARE_API_KEY='...' export CLOUDFLARE_ACCOUNT_ID='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET cloudflare_ai ( TYPE duckdb_ai, AI_PROVIDER 'workers_ai', MODEL '@cf/zai-org/glm-4.7-flash' ); SELECT ai_complete( 'Explain edge-hosted inference in one sentence.', secret := 'cloudflare_ai' ) AS answer; SELECT ai_embed( 'DuckDB vector search', secret := 'cloudflare_ai', model := '@cf/baai/bge-base-en-v1.5' )[1] AS first_embedding_value; ``` Aliases `cloudflare`, `workers_ai`, `cloudflare_workers_ai`, and `cloudflare_ai` resolve to the same provider. ## Alibaba Cloud Model Studio / DashScope DashScope exposes Qwen models through an OpenAI-compatible endpoint. The default base URL uses the existing international compatible-mode endpoint; use a secret `BASE_URL`, `DASHSCOPE_BASE_URL`, or `ALIBABA_MODEL_STUDIO_BASE_URL` for a workspace-specific endpoint. ```sh export DASHSCOPE_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET dashscope_ai ( TYPE duckdb_ai, AI_PROVIDER 'qwen', MODEL 'qwen-plus' ); SELECT ai_complete( 'Write one sentence about analytics agents.', secret := 'dashscope_ai' ) AS answer; SELECT ai_embed( 'DuckDB vector search', secret := 'dashscope_ai', model := 'text-embedding-v4' )[1] AS first_embedding_value; ``` Aliases `dashscope`, `qwen`, `alibaba`, `alibaba_model_studio`, and `model_studio` resolve to the same provider. ## Nebius Token Factory ```sh export NEBIUS_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET nebius_ai ( TYPE duckdb_ai, AI_PROVIDER 'nebius_token_factory', MODEL 'Qwen/Qwen3-30B-A3B-Instruct-2507' ); SELECT ai_complete( 'Summarize why managed open-weight inference matters.', secret := 'nebius_ai' ) AS answer; ``` Nebius is configured for completion calls. Embeddings are not configured for this provider. ## SambaNova Cloud ```sh export SAMBANOVA_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET sambanova_ai ( TYPE duckdb_ai, AI_PROVIDER 'sambanova', MODEL 'Meta-Llama-3.3-70B-Instruct' ); SELECT ai_complete( 'Explain fast open-weight inference in one sentence.', secret := 'sambanova_ai' ) AS answer; ``` Aliases `sambanova`, `sambanova_ai`, `samba_nova`, and `sambacloud` resolve to the same provider. Embeddings are not configured for this provider. ## SiliconFlow ```sh export SILICONFLOW_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET siliconflow_ai ( TYPE duckdb_ai, AI_PROVIDER 'siliconflow', MODEL 'Qwen/Qwen2.5-72B-Instruct' ); SELECT ai_complete( 'Write one sentence about open model hosting.', secret := 'siliconflow_ai' ) AS answer; ``` Aliases `siliconflow` and `silicon_flow` resolve to the same provider. Embeddings are not configured for this provider. ## Vercel AI Gateway ```sh export AI_GATEWAY_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET vercel_ai ( TYPE duckdb_ai, AI_PROVIDER 'vercel_ai_gateway', MODEL 'openai/gpt-4o-mini' ); SELECT ai_complete( 'Explain model gateway routing in one sentence.', secret := 'vercel_ai' ) AS answer; SELECT ai_embed( 'DuckDB vector search', secret := 'vercel_ai', model := 'openai/text-embedding-3-small' )[1] AS first_embedding_value; ``` Aliases `vercel`, `vercel_ai_gateway`, `vercel_gateway`, and `ai_gateway` resolve to the same provider. ## Moonshot AI / Kimi ```sh export MOONSHOT_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET kimi_ai ( TYPE duckdb_ai, AI_PROVIDER 'kimi', MODEL 'kimi-k3' ); SELECT ai_complete( 'Explain code-oriented model inference in one sentence.', secret := 'kimi_ai' ) AS answer; ``` Aliases `moonshot`, `kimi`, `moonshot_ai`, and `kimi_api` resolve to the same provider. The current Kimi chat model IDs are passed through unchanged, including `kimi-k3`, `kimi-k2.7-code`, `kimi-k2.7-code-highspeed`, `kimi-k2.6`, and `kimi-k2.5`. Moonshot V1 model IDs are also accepted while they remain available to the configured account. Embeddings are not configured for this provider. ## Baidu Qianfan / ERNIE ```sh export QIANFAN_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET qianfan_ai ( TYPE duckdb_ai, AI_PROVIDER 'ernie', MODEL 'ernie-5.1' ); SELECT ai_complete( 'Write one sentence about enterprise AI platforms.', secret := 'qianfan_ai' ) AS answer; ``` Aliases `qianfan`, `baidu`, `baidu_qianfan`, `ernie`, and `wenxin` resolve to the same provider. Embeddings are not configured for this provider. ## Tencent TokenHub / Hunyuan ```sh export TOKENHUB_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET hunyuan_ai ( TYPE duckdb_ai, AI_PROVIDER 'tencent_hunyuan', MODEL 'hy3' ); SELECT ai_complete( 'Summarize governed model APIs in one sentence.', secret := 'hunyuan_ai' ) AS answer; ``` Aliases `hunyuan`, `tencent`, and `tencent_hunyuan` resolve to the same provider. `HUNYUAN_API_KEY` and `TENCENT_HUNYUAN_API_KEY` remain accepted for existing configurations. Set `TOKENHUB_BASE_URL` for the international or a custom regional TokenHub endpoint. Embeddings are not configured for this provider. ## StepFun ```sh export STEPFUN_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET stepfun_ai ( TYPE duckdb_ai, AI_PROVIDER 'step', MODEL 'step-3.5-flash' ); SELECT ai_complete( 'Write one sentence about fast chat models.', secret := 'stepfun_ai' ) AS answer; ``` Aliases `stepfun`, `step`, and `step_fun` resolve to the same provider. Embeddings are not configured for this provider. ## MiniMax ```sh export MINIMAX_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET minimax_ai ( TYPE duckdb_ai, AI_PROVIDER 'minimax', MODEL 'MiniMax-M2.7' ); SELECT ai_complete( 'Explain model choice for SQL enrichment in one sentence.', secret := 'minimax_ai' ) AS answer; ``` Aliases `minimax` and `mini_max` resolve to the same provider. Embeddings are not configured for this provider. ## Poe ```sh export POE_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET poe_ai ( TYPE duckdb_ai, AI_PROVIDER 'poe', MODEL 'GPT-5.4' ); SELECT ai_complete( 'Summarize model routing in one sentence.', secret := 'poe_ai' ) AS answer; ``` Aliases `poe` and `poe_api` resolve to the same provider. Embeddings are not configured for this provider. Poe's Chat Completions compatibility layer ignores `response_format`, so `duckdb_ai` rejects non-text `response_format` values and `response_schema` for this provider instead of promising unenforced structured output. ## Volcengine Ark / Doubao ```sh export VOLCENGINE_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET doubao_ai ( TYPE duckdb_ai, AI_PROVIDER 'doubao', MODEL 'doubao-seed-2-1-pro-260628' ); SELECT ai_complete( 'Explain hosted model APIs in one sentence.', secret := 'doubao_ai' ) AS answer; ``` Aliases `volcengine`, `volcano_engine`, `volcengine_ark`, `doubao`, and `ark` resolve to the same provider. Embeddings are not configured for this provider. ## Hugging Face ```sh export HF_TOKEN='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET huggingface_ai ( TYPE duckdb_ai, AI_PROVIDER 'huggingface', MODEL 'openai/gpt-oss-120b' ); SELECT ai_complete( 'Write one sentence about open-source model routing.', secret := 'huggingface_ai' ) AS answer; ``` Aliases `hf`, `hugging_face`, `huggingface_hub`, and `hf_inference` resolve to `huggingface`. Embeddings are not configured by default; use `openai_compatible` with an embedding-capable router endpoint if needed. ## xAI / SpaceXAI ```sh export XAI_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET xai_ai ( TYPE duckdb_ai, AI_PROVIDER 'xai', MODEL 'grok-4.6' ); SELECT ai_complete( 'Summarize this SQL migration risk in one sentence.', secret := 'xai_ai' ) AS answer; ``` Aliases `xai`, `x.ai`, `x-ai`, and `grok` resolve to the same provider. Embeddings are not configured for this provider. ## Perplexity ```sh export PERPLEXITY_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET perplexity_ai ( TYPE duckdb_ai, AI_PROVIDER 'perplexity', MODEL 'sonar' ); SELECT ai_complete( 'Give one current consideration for managed model APIs.', secret := 'perplexity_ai' ) AS answer; ``` Perplexity is configured for completion calls. Embeddings are not configured for this provider. ## NVIDIA NIM ```sh export NVIDIA_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET nvidia_ai ( TYPE duckdb_ai, AI_PROVIDER 'nvidia_nim', MODEL 'nvidia/nemotron-3-super-120b-a12b' ); SELECT ai_complete( 'Explain GPU-hosted inference in one sentence.', secret := 'nvidia_ai' ) AS answer; ``` Aliases `nvidia`, `nvidia_nim`, and `nim` resolve to the same provider. Embeddings are not configured for this provider. ## Amazon Bedrock Bedrock's OpenAI-compatible endpoint is regional. Set either a full base URL or a region; the extension derives `https://bedrock-mantle..api.aws/v1` from the region. ```sh export AWS_BEARER_TOKEN_BEDROCK='...' export AWS_REGION='us-east-1' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET bedrock_ai ( TYPE duckdb_ai, AI_PROVIDER 'bedrock', MODEL 'openai.gpt-oss-120b' ); SELECT ai_complete( 'Summarize why governed enterprise inference matters.', secret := 'bedrock_ai' ) AS answer; ``` Aliases `bedrock`, `aws_bedrock`, `amazon_bedrock`, and `bedrock_mantle` resolve to the same provider. Embeddings are not configured by default. ## Google Vertex AI Vertex AI's OpenAI-compatible endpoint is project and location scoped. Set a full base URL, or set `GOOGLE_CLOUD_PROJECT` and optionally `GOOGLE_CLOUD_LOCATION`. ```sh export GOOGLE_CLOUD_PROJECT='my-project' export GOOGLE_CLOUD_LOCATION='global' export VERTEX_AI_ACCESS_TOKEN="$(gcloud auth print-access-token)" ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET vertex_ai ( TYPE duckdb_ai, AI_PROVIDER 'vertex', MODEL 'google/gemini-3.8-flash' ); SELECT ai_complete( 'Explain BigQuery and DuckDB together in one sentence.', secret := 'vertex_ai' ) AS answer; ``` Aliases `vertex`, `google_vertex`, `vertex_ai`, and `gcp_vertex` resolve to the same provider. Embeddings are not configured for this provider. The default model is `google/gemini-3.8-flash`, because Vertex retires Gemini 2.5 Flash in October 2026. As on the Gemini API, the extension omits `temperature` for Gemini 3.8, 3.7 and 3.6 Flash and 3.5 Flash-Lite. ## Databricks Databricks Model Serving exposes chat endpoints through an OpenAI-compatible API. Use a Databricks personal access token or service-principal token, and set the model to the serving endpoint name. ```sh export DATABRICKS_TOKEN='...' export DATABRICKS_HOST='https://.cloud.databricks.com' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET databricks_ai ( TYPE duckdb_ai, AI_PROVIDER 'databricks', MODEL 'databricks-gpt-oss-120b' ); SELECT ai_complete( 'Explain Delta Lake in one sentence.', secret := 'databricks_ai' ) AS answer; ``` If `BASE_URL` is omitted, set `DATABRICKS_HOST`; the extension derives `https:///serving-endpoints`. You can also set a secret `BASE_URL`, `DATABRICKS_BASE_URL`, or per-call `base_url := ...` to use a full `/serving-endpoints`, `/ai-gateway/mlflow/v1`, or `/chat/completions` endpoint. Aliases `mosaic`, `mosaic_ai`, and `databricks_ai` resolve to `databricks`. Databricks reasoning models can return `message.content` as typed reasoning and text blocks. `duckdb_ai` returns the text blocks and ignores reasoning summaries. Databricks Claude Sonnet 5 and Claude Opus 5 model services reject sampling temperature, so the extension omits `temperature` for those model IDs even when the generic SQL option is set. Older Claude and other Databricks models retain explicit temperature support. ## Snowflake Cortex REST Snowflake Cortex REST exposes a Chat Completions API compatible with the OpenAI request shape. Use a Snowflake Programmatic Access Token, OAuth token, or JWT with a role that can call Cortex REST. ```sh export SNOWFLAKE_PAT='...' export SNOWFLAKE_ACCOUNT_URL='https://.snowflakecomputing.com' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET snowflake_ai ( TYPE duckdb_ai, AI_PROVIDER 'snowflake', MODEL 'claude-sonnet-4-5' ); SELECT ai_complete( 'Summarize why governed model inference matters.', secret := 'snowflake_ai' ) AS answer; ``` If `BASE_URL` is omitted, set `SNOWFLAKE_ACCOUNT_URL`, `SNOWFLAKE_HOST`, or `SNOWFLAKE_ACCOUNT`; the extension derives `https://.snowflakecomputing.com/api/v2/cortex/v1`. You can also set a secret `BASE_URL`, `SNOWFLAKE_BASE_URL`, or per-call `base_url := ...` to use a full `/api/v2/cortex/v1` or `/chat/completions` endpoint. Snowflake model IDs include values such as `claude-sonnet-4-5`, `llama4-maverick`, and `llama3.3-70b`, depending on region and model access. ## OpenAI Privacy Filter OpenAI Privacy Filter is an open-weight PII detection and masking model. The extension calls it through a small HTTP service so the model can run either on the same machine as DuckDB or in your own cloud deployment. Use local hosting when unredacted PII should not leave the machine running DuckDB: ```sh # Host a wrapper around the openai/privacy-filter Python package. # The wrapper should expose POST /redact. export OPF_CHECKPOINT="$HOME/.opf/privacy_filter" ./serve-privacy-filter --host 127.0.0.1 --port 8080 ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET privacy_filter_local ( TYPE duckdb_ai, AI_PROVIDER 'openai_privacy_filter', BASE_URL 'http://localhost:8080' ); SELECT ai_redact( 'email alice@example.com token fake-token', secret := 'privacy_filter_local' ) AS redacted_text; ``` Use cloud hosting when multiple DuckDB clients should share one managed Privacy Filter deployment: ```sql CREATE OR REPLACE SECRET privacy_filter_cloud ( TYPE duckdb_ai, AI_PROVIDER 'openai_privacy_filter', BASE_URL 'https://privacy-filter.example.com', API_KEY '...' ); SELECT ai_redact( internal_note, secret := 'privacy_filter_cloud' ) AS redacted_note FROM support_tickets; ``` The service contract is intentionally small: ```http POST /redact Content-Type: application/json Authorization: Bearer ... # optional {"text":"email alice@example.com","model":"openai/privacy-filter"} ``` The response should include one of `redacted_text`, `masked_text`, `text`, or `output`: ```json {"redacted_text":"email [PRIVATE_EMAIL]"} ``` Aliases `privacy_filter`, `pii_filter`, and `opf` resolve to `openai_privacy_filter`. The default base URL is `http://localhost:8080`; set `OPENAI_PRIVACY_FILTER_BASE_URL`, `DUCKDB_AI_BASE_URL`, a secret `BASE_URL`, or a per-call `base_url := ...` to point at a different local or cloud service. For cloud auth, use a secret `API_KEY`, `OPENAI_PRIVACY_FILTER_API_KEY`, or `DUCKDB_AI_API_KEY`. ## OpenAI-compatible / Local gateway Use this path for vLLM, LM Studio, LiteLLM, Ollama's `/v1` endpoint, or any gateway that exposes an OpenAI-compatible chat API. For local Ollama's OpenAI-compatible endpoint: ```sh ollama serve ollama pull qwen3.8:27b ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET local_openai_ai ( TYPE duckdb_ai, AI_PROVIDER 'local', BASE_URL 'http://localhost:11434/v1', MODEL 'qwen3.8:27b' ); SELECT ai_complete( 'Write one sentence about local AI models in DuckDB.', secret := 'local_openai_ai' ) AS answer; ``` For a hosted OpenAI-compatible gateway, add an API key: ```sh export OPENAI_COMPATIBLE_API_KEY='...' ./build/release/duckdb ``` ```sql LOAD ai; CREATE OR REPLACE SECRET gateway_ai ( TYPE duckdb_ai, AI_PROVIDER 'openai_compatible', BASE_URL 'https://gateway.example/v1', MODEL 'provider/model-name' ); SELECT ai_complete( 'Write one sentence about DuckDB.', secret := 'gateway_ai' ) AS answer; ``` Aliases `local`, `openai_compatible`, `openai-compatible`, `local_openai`, `local-models`, and `local_models` all use the OpenAI-compatible protocol. ## llama.cpp Use the `llamacpp` provider (aliases `llama.cpp`, `llama-cpp`, `llama_cpp`, `llama-server`, `llama_server`) for a local [llama.cpp](https://github.com/ggml-org/llama.cpp) `llama-server`. It uses the OpenAI-compatible protocol and defaults to `http://localhost:8080/v1`, so no base URL setup is needed for a default server: ```sh llama-server -m model.gguf --embeddings ./build/release/duckdb ``` ```sql LOAD ai; SET duckdb_ai_provider = 'llama.cpp'; SELECT ai_complete('Write one sentence about DuckDB.') AS answer; SELECT ai_embed('DuckDB'); ``` The request `model` defaults to `default`; `llama-server` ignores it and answers with its loaded model, so no model configuration is needed either. Set `LLAMACPP_BASE_URL` for a non-default host or port, and `LLAMACPP_API_KEY` when the server runs with `--api-key`. Embedding calls require starting `llama-server` with `--embeddings`. `response_schema := ...` uses llama.cpp's direct `response_format.schema` request shape so `llama-server` can enforce the supplied JSON Schema. For throughput, start `llama-server` with parallel slots (for example `--parallel 4`) and match `max_concurrent_requests` so DuckDB keeps every slot busy; llama.cpp reuses cached prompt prefixes automatically, so shared system prompts stay cheap across rows. # Best practices Use this page as the operating guide for running duckdb-ai in notebooks, batch jobs, local workflows, and managed environments. ## Choose the right provider path Use the most constrained provider that fits the job: | Workload | Recommended path | Why | | --- | --- | --- | | Local development without hosted credentials | `ollama` | Keeps prompts local and avoids provider costs. | | Production chat, extraction, classification, and SQL assistant calls | A managed LLM provider such as `openai`, `azure`, `databricks`, `snowflake`, `anthropic`, `gemini`, `mistral`, `deepseek`, `zai`, or `openrouter` | Hosted models with provider-side quotas and access controls. | | OpenAI-compatible gateways, vLLM, LM Studio, LiteLLM, or local Ollama `/v1` | `openai_compatible` or `local` | Uses the common chat-completions request shape. | | Local llama.cpp `llama-server` | `llamacpp` or `llama.cpp` | Talks to `http://localhost:8080/v1` out of the box; the server answers with its loaded model. | | PII redaction where raw text should stay local | `openai_privacy_filter` with local hosting | Sends raw text to a dedicated redaction service instead of a general chat model. | | Fixed-choice questions at high volume (routing, triage, moderation) | A decision model through `ai_decide`: `typesafe`, `cloudflare`, `perplexity`, `ollama` 0.35 or later, or `systemone` | Returns labels with confidences and probabilities instead of text, and answers several questions in one request. | | PII redaction shared across teams | `openai_privacy_filter` with a private cloud service | Centralizes deployment, auth, scaling, and audit controls while preserving the dedicated redaction contract. | `ai_redact` behaves differently for `openai_privacy_filter`: it sends the raw input text to `POST /redact`. With other completion providers, it uses the general task prompt for masking. ## Configure credentials safely Prefer environment variables or DuckDB secrets. Avoid embedding API keys in SQL files, notebooks, CI logs, or shell history. ```sql CREATE OR REPLACE SECRET openai_ai ( TYPE duckdb_ai, AI_PROVIDER 'openai', MODEL 'gpt-5.6-luna' ); SELECT ai_complete('hello', secret := 'openai_ai'); ``` When a secret includes `API_KEY`, `ai_secrets()` reports only whether a key is present. It does not print the key value. Use provider-specific environment variables for local development: | Provider | Credential env var | Base URL env var | | --- | --- | --- | | `openai` | `OPENAI_API_KEY` | `OPENAI_BASE_URL` | | `azure` | `AZURE_OPENAI_API_KEY` | `AZURE_OPENAI_BASE_URL` or `AZURE_OPENAI_ENDPOINT` | | `anthropic` / `claude` | `ANTHROPIC_API_KEY` or `CLAUDE_API_KEY` | `ANTHROPIC_BASE_URL` or `CLAUDE_BASE_URL` | | `bedrock` | `AWS_BEDROCK_API_KEY`, `AWS_BEARER_TOKEN_BEDROCK`, or `BEDROCK_API_KEY` | `AWS_BEDROCK_BASE_URL`, `BEDROCK_BASE_URL`, `AWS_BEDROCK_REGION`, `AWS_REGION`, or `AWS_DEFAULT_REGION` | | `cerebras` | `CEREBRAS_API_KEY` | `CEREBRAS_BASE_URL` | | `cloudflare` / `workers_ai` | `CLOUDFLARE_API_KEY`, `CLOUDFLARE_API_TOKEN`, or `CLOUDFLARE_AUTH_TOKEN` | `CLOUDFLARE_ACCOUNT_ID`, `CLOUDFLARE_WORKERS_AI_BASE_URL`, `CLOUDFLARE_AI_BASE_URL`, or `CLOUDFLARE_BASE_URL` | | `cohere` | `COHERE_API_KEY` | `COHERE_BASE_URL` | | `dashscope` / `qwen` | `DASHSCOPE_API_KEY`, `ALIBABA_API_KEY`, or `QWEN_API_KEY` | `DASHSCOPE_BASE_URL`, `ALIBABA_MODEL_STUDIO_BASE_URL`, or `QWEN_BASE_URL` | | `databricks` | `DATABRICKS_TOKEN` | `DATABRICKS_HOST` or `DATABRICKS_BASE_URL` | | `deepinfra` | `DEEPINFRA_API_KEY` | `DEEPINFRA_BASE_URL` | | `deepseek` | `DEEPSEEK_API_KEY` | `DEEPSEEK_BASE_URL` | | `fireworks` | `FIREWORKS_API_KEY` | `FIREWORKS_BASE_URL` | | `gemini` | `GEMINI_API_KEY` | `GEMINI_BASE_URL` | | `groq` | `GROQ_API_KEY` | `GROQ_BASE_URL` | | `huggingface` / `hf` | `HF_TOKEN`, `HUGGINGFACE_API_KEY`, or `HUGGING_FACE_HUB_TOKEN` | `HUGGINGFACE_BASE_URL` | | `hunyuan` / `tencent_hunyuan` | `HUNYUAN_API_KEY` or `TENCENT_HUNYUAN_API_KEY` | `HUNYUAN_BASE_URL` or `TENCENT_HUNYUAN_BASE_URL` | | `minimax` | `MINIMAX_API_KEY` or `MINI_MAX_API_KEY` | `MINIMAX_BASE_URL` | | `mistral` | `MISTRAL_API_KEY` | `MISTRAL_BASE_URL` | | `moonshot` / `kimi` | `MOONSHOT_API_KEY` or `KIMI_API_KEY` | `MOONSHOT_BASE_URL` or `KIMI_BASE_URL` | | `nebius` / `nebius_token_factory` | `NEBIUS_API_KEY` or `TOKEN_FACTORY_API_KEY` | `NEBIUS_BASE_URL` or `TOKEN_FACTORY_BASE_URL` | | `nvidia` / `nvidia_nim` | `NVIDIA_API_KEY` | `NVIDIA_BASE_URL` | | `openrouter` | `OPENROUTER_API_KEY` | `OPENROUTER_BASE_URL` | | `perplexity` | `PERPLEXITY_API_KEY` | `PERPLEXITY_BASE_URL` | | `poe` | `POE_API_KEY` | `POE_BASE_URL` | | `qianfan` / `ernie` | `QIANFAN_API_KEY`, `BAIDU_QIANFAN_API_KEY`, or `BAIDU_API_KEY` | `QIANFAN_BASE_URL` or `BAIDU_QIANFAN_BASE_URL` | | `sambanova` | `SAMBANOVA_API_KEY` | `SAMBANOVA_BASE_URL` or `SAMBANOVA_URL` | | `siliconflow` | `SILICONFLOW_API_KEY` | `SILICONFLOW_BASE_URL` | | `snowflake` | `SNOWFLAKE_PAT` or `SNOWFLAKE_TOKEN` | `SNOWFLAKE_ACCOUNT_URL`, `SNOWFLAKE_HOST`, `SNOWFLAKE_ACCOUNT`, or `SNOWFLAKE_BASE_URL` | | `stepfun` / `step` | `STEPFUN_API_KEY` or `STEP_API_KEY` | `STEPFUN_BASE_URL` or `STEP_BASE_URL` | | `together` | `TOGETHER_API_KEY` | `TOGETHER_BASE_URL` | | `vercel` / `vercel_ai_gateway` | `AI_GATEWAY_API_KEY`, `VERCEL_AI_GATEWAY_API_KEY`, or `VERCEL_OIDC_TOKEN` | `AI_GATEWAY_BASE_URL`, `VERCEL_AI_GATEWAY_BASE_URL`, or `VERCEL_BASE_URL` | | `vertex` / `google_vertex` | `VERTEX_AI_ACCESS_TOKEN`, `GOOGLE_CLOUD_ACCESS_TOKEN`, `GOOGLE_OAUTH_ACCESS_TOKEN`, or `VERTEX_API_KEY` | `VERTEX_AI_BASE_URL`, `GOOGLE_VERTEX_BASE_URL`, `GOOGLE_CLOUD_PROJECT`, or `VERTEX_BASE_URL` | | `volcengine` / `doubao` | `VOLCENGINE_API_KEY`, `ARK_API_KEY`, or `DOUBAO_API_KEY` | `VOLCENGINE_BASE_URL`, `ARK_BASE_URL`, or `DOUBAO_BASE_URL` | | `xai` / `grok` | `XAI_API_KEY` | `XAI_BASE_URL` | | `zai` | `ZAI_API_KEY` | `ZAI_BASE_URL` | | `openai_privacy_filter` | `OPENAI_PRIVACY_FILTER_API_KEY` | `OPENAI_PRIVACY_FILTER_BASE_URL` | | `openai_compatible` / `local` | `OPENAI_COMPATIBLE_API_KEY` | `OPENAI_COMPATIBLE_BASE_URL` | | `llamacpp` / `llama.cpp` | `LLAMACPP_API_KEY` | `LLAMACPP_BASE_URL` | `DUCKDB_AI_API_KEY`, `DUCKDB_AI_BASE_URL`, and `DUCKDB_AI_MODEL` are generic fallbacks. Use them only when one process talks to one provider; provider- specific variables are clearer in mixed-provider environments. ## Set defaults deliberately Use global defaults for short interactive sessions: ```sql SET duckdb_ai_provider = 'openai'; SET duckdb_ai_model = 'gpt-5.6-luna'; SET duckdb_ai_timeout_seconds = 120; ``` Use family-specific model settings when different function groups need different models: ```sql SET duckdb_ai_completion_model = 'gpt-5.6-luna'; SET duckdb_ai_task_model = 'gpt-5.6-luna'; SET duckdb_ai_sql_assistant_model = 'gpt-5.6'; SET duckdb_ai_aggregate_model = 'gpt-5.6-luna'; SET duckdb_ai_embedding_model = 'text-embedding-3-small'; ``` A per-call `model := ...` or `profile := ...` overrides these settings, and the settings override the `MODEL` stored in a secret. The full order is in [provider settings and secrets](functions.md#provider-settings-and-secrets). For repeatable jobs, prefer secrets and explicit per-job settings over relying on ambient environment variables. ## Restrict provider egress Use an allowlist when the extension should only call approved providers or an internal AI gateway: ```sql SET duckdb_ai_allowed_hosts = 'ai-gateway.internal,api.openai.com'; SELECT ai_complete( 'Summarize this.', provider := 'openai', allowed_hosts := 'api.openai.com' ); ``` Allowlist entries match the URL host. Entries may be hostnames, `host:port`, full URLs, `*.example.com`, or `*`. The same control applies to outbound usage log endpoints. ## Preview requests before calling providers Use request-preview functions in tests, reviews, and provider debugging: ```sql SELECT ai_completion_request_json( 'Summarize this.', provider := 'snowflake', model := 'claude-sonnet-4-5' ); SELECT ai_embedding_request_json( 'DuckDB vector search', provider := 'openai', model := 'text-embedding-3-small' ); ``` These functions do not make network calls. They are the safest way to confirm model selection, message structure, response-format hints, and provider aliases. ## Prefer structured output for data pipelines For pipelines, avoid parsing prose. For one value per row, use `ai_extract_record`, which returns a typed `STRUCT` from a JSON Schema: ```sql SELECT ticket_id, r.urgency, r.product_area FROM ( SELECT ticket_id, ai_extract_record(subject || chr(10) || body, '{ "type": "object", "properties": { "urgency": {"type": "string", "enum": ["low", "medium", "high"]}, "product_area": {"type": "string"} }, "required": ["urgency", "product_area"], "additionalProperties": false }', provider := 'openai') AS r FROM support_tickets ); ``` `ai_complete_record` is a table function for one self-contained prompt: its arguments must be constants, so it cannot read a column. ```sql SELECT * FROM ai_complete_record( 'Extract name and urgency from: customer says checkout is down', '{ "type": "object", "properties": { "name": {"type": "string"}, "urgency": {"type": "string"} }, "required": ["urgency"], "additionalProperties": false }', provider := 'openai' ); ``` Keep schemas narrow. Add `required`, `additionalProperties: false`, bounded strings, enum-like labels, and typed arrays when you need stable downstream columns. ## Keep generated SQL bounded Use `ai_schema_prompt` to inspect exactly what table context the SQL assistant will see: ```sql SELECT summary FROM ai_schema_prompt( include_tables := ['main.support_tickets'], sample_rows := 3 ); ``` Always pass `include_tables` or `exclude_tables`. Avoid sending an entire database catalog when the question only needs one schema or table. `sample_rows := N` sends up to N real rows per table to the provider, so omit it for sensitive tables. Generated SQL is validated as one read-only DuckDB `SELECT`, but you should still inspect generated queries before using them in important workflows: ```sql WITH generated AS ( SELECT ai_sql( 'Count open tickets by priority', include_tables := ['main.support_tickets'] ) AS sql ) SELECT ai_validate_read_only_sql(sql, true) -- true also checks that it binds FROM generated; ``` ## Treat redaction as a privacy control, not a proof Use `openai_privacy_filter` for dedicated PII masking when raw text should not be sent to a general chat model. Host it locally so raw text never leaves the machine: ```sql CREATE OR REPLACE SECRET privacy_filter_local ( TYPE duckdb_ai, AI_PROVIDER 'openai_privacy_filter', BASE_URL 'http://localhost:8080' ); ``` For high-sensitivity domains, evaluate redaction output on representative data, keep human review paths, and avoid treating model redaction as a compliance certificate. ## Control cost and throughput Provider calls happen per row unless you aggregate or pre-filter first. Reduce cost and latency by: - Filtering rows before AI calls. - Deduplicating repeated input text. - Asking several fixed-choice questions about the same text in one `ai_decide` call instead of separate `ai_classify`, `ai_filter` and `ai_score` calls. - Using `ai_agg` or `ai_summarize_agg` when one grouped call is enough. - Persisting embeddings instead of recomputing pairwise similarity. - Setting lower `max_tokens` values for classification and extraction tasks. For batch workloads, set per-database concurrency and pacing: ```sql SET duckdb_ai_max_concurrent_requests = 4; SET duckdb_ai_min_request_interval_ms = 100; SET duckdb_ai_token_limit_per_minute = 200000; ``` Row-wise provider scalar functions can run provider work concurrently within a DuckDB vector chunk while still respecting the per-database request and token pacing controls. See [Runtime behavior](runtime-behavior.md) for the exact function coverage and worker limits. Response caching is opt-in and in-memory for the current DuckDB database instance. Enable it when repeated prompts or embeddings should not repay the same provider call: ```sql SET duckdb_ai_cache = true; SELECT ai_complete('Summarize this repeated prompt.'); SELECT * FROM ai_clear_cache(); ``` Use `ai_count_tokens` and `ai_recommended_batch_size` before large jobs. Keep provider limits in a small table so the numbers are easy to update when your account tier changes: ```sql CREATE OR REPLACE TABLE ai_provider_limits AS SELECT 'openai' AS provider, 'gpt-5.6-luna' AS model, 200000::BIGINT AS token_limit_per_minute, 500::BIGINT AS request_limit_per_minute; WITH prompt_stats AS ( SELECT avg(ai_count_tokens(subject || ': ' || body)) AS input_tokens_per_row FROM support_tickets ) SELECT ai_recommended_batch_size( input_tokens_per_row, 200, token_limit_per_minute, request_limit_per_minute ) AS recommended_rows_per_minute FROM prompt_stats, ai_provider_limits WHERE provider = 'openai' AND model = 'gpt-5.6-luna'; ``` Retries are disabled by default. Enable small retry counts only for transient provider failures: ```sql SELECT ai_complete( 'Summarize this.', retry_count := 2, retry_backoff_ms := 1000 ); ``` Retry sleeps are interruptible and provider `Retry-After` headers take precedence over the configured backoff, up to 60 seconds per wait. Provider redirects are not followed. Use `on_error := 'null'` for exploratory enrichment where a missing output is acceptable: ```sql SELECT ai_classify( subject, 'billing, support, sales, other', on_error := 'null' ) FROM support_tickets; ``` For production enrichment, prefer `ai_try_complete` so failed rows keep their error reason: ```sql CREATE TEMP TABLE ticket_ai_attempts AS SELECT ticket_id, ai_try_complete( subject || ': ' || body, provider := 'openai', model := 'gpt-5.6-luna', max_tokens := 200, token_limit_per_minute := 200000 ) AS result FROM support_tickets; CREATE OR REPLACE TABLE ticket_summaries AS SELECT ticket_id, result.response AS summary FROM ticket_ai_attempts WHERE result.error IS NULL; CREATE OR REPLACE TABLE ticket_ai_failed_rows AS SELECT ticket_id, result.error AS error_reason, current_timestamp AS failed_at FROM ticket_ai_attempts WHERE result.error IS NOT NULL; ``` Use the same rejected-row `SELECT` inside `COPY (...) TO 'failed_rows.parquet'` or an `s3://...` target when failures should live outside the DuckDB database. ## Chain steps without paying twice Multi-step pipelines multiply calls per row. Merge steps where one call can answer several questions (`ai_decide`, multi-field `ai_extract_record`), call each model function once per row in a subquery, send only successful rows downstream, and save every step to a table so reruns retry only missing rows. See [chain AI steps](cookbooks/chain-ai-steps.md). ## Export provider-native batch requests for offline jobs The `ai` extension runs provider calls synchronously inside the DuckDB query. For overnight jobs that should use provider-native Batch APIs, materialize request bodies with the request-preview functions, then submit and poll them with your job runner or provider SDK: ```sql COPY ( SELECT 'ticket-' || ticket_id AS custom_id, 'POST' AS method, '/v1/chat/completions' AS url, ai_completion_request_json( subject || chr(10) || body, provider := 'openai', model := 'gpt-5.6-luna', system_prompt := 'Summarize this support ticket in one sentence.' )::JSON AS body FROM support_tickets ) TO 'openai-batch-requests.jsonl' (FORMAT json); ``` Each line has the `custom_id`, `method`, `url` and `body` object that the OpenAI Batch API expects. Store `custom_id` with the provider response and join it back to the source table after the batch finishes. Keep `response_schema` or downstream DuckDB validation in the exported request path so the offline job has the same shape checks as interactive SQL enrichment. ## Use provider-side prompt caching for stable prefixes Keep static instructions, schemas, and catalog context byte-stable across rows. `ai_complete_json` and `ai_complete_record` put their JSON instructions and schema in the system message so the row-specific text stays last. Task wrappers put their static task instructions in the system message, and SQL assistant functions put their static rules and schema context there for the same reason. For providers that expose cache controls, opt in with `prompt_cache := true` or `DUCKDB_AI_PROMPT_CACHE=1`: ```sql SELECT ai_complete_json( body, provider := 'openai', prompt_cache := true, response_schema := '{"type":"object","properties":{"summary":{"type":"string"}}}' ) FROM documents; ``` OpenAI receives a stable `prompt_cache_key`. GPT-5.6 requests also mark the end of the system message as an explicit cache breakpoint and disable the implicit breakpoint on the changing user message. Anthropic receives ephemeral cache control on the system prompt, and xAI receives the matching `x-grok-conv-id` header for chat-completion calls. OpenAI-compatible prompt caching generally needs a stable prefix of at least 1,024 tokens before cached-token discounts appear. Anthropic cache reads and writes are exposed in `ai_usage()` as `cached_prompt_tokens` and `cache_creation_prompt_tokens`. This is separate from the extension's exact-response cache. Use `cache := true` when identical prompts may repeat inside or across chunks in the same database process. Concurrent identical misses are coalesced into one provider request, and `cache_ttl_seconds := ...` or `DUCKDB_AI_CACHE_TTL_SECONDS` can expire entries by age. ## Log usage without leaking text `ai_usage()` keeps recent events in process: ```sql SELECT function_name, query_id, provider, model, elapsed_ms, http_status, estimated_cost_usd FROM ai_usage() ORDER BY event_id DESC; ``` Outbound usage logs omit prompt, input, and response text by default: ```sql SET duckdb_ai_log_endpoint = 'https://collector.example/ai-usage'; SET duckdb_ai_log_format = 'generic_json'; SET duckdb_ai_log_tags = 'app=support-triage,env=prod'; SET duckdb_ai_log_sample_rate = 0.25; ``` Only enable text logging when you have a documented retention and access-control reason: ```sql SET duckdb_ai_log_include_text = true; ``` By default, outbound usage logs are queued asynchronously and sent on a best-effort basis. Use `duckdb_ai_log_strict = true` only when losing a usage log must fail the SQL query; strict logs are posted synchronously. ## Corporate proxy and TLS Provider HTTP calls use libcurl. Standard proxy and CA environment variables such as `HTTPS_PROXY`, `HTTP_PROXY`, `NO_PROXY`, `SSL_CERT_FILE`, and `CURL_CA_BUNDLE` are honored by libcurl in typical deployments. Prefer an internal gateway plus `duckdb_ai_allowed_hosts` when company policy requires all model traffic to pass through managed egress. ## Estimate cost carefully Cost estimates are optional. Use explicit prices for a job, or opt into the built-in catalog when it covers your model: ```sql SET duckdb_ai_use_builtin_model_prices = true; SELECT * FROM ai_model_prices(); ``` Treat `ai_model_prices()` as a convenience catalog, not a billing source of truth. Provider pricing and regional availability change; verify prices with the provider before using estimates for chargeback or budgeting. # Runtime behavior This page documents how the `ai` extension executes provider calls at runtime. It is the operational reference for the retry, caching, concurrency and egress controls used by the SQL functions. ## Function stability Model-backed scalar and aggregate functions are registered as `VOLATILE` in DuckDB. The planner must not constant-fold or reuse their results as if provider calls were deterministic. Local helper functions such as `ai_count_tokens()` and `ai_is_read_only_sql()` remain deterministic and do not call providers. ## Prompt shape Completion functions accept one user prompt plus an optional system prompt. Task wrappers and SQL assistant functions build provider messages from their SQL arguments: static instructions and schema context are placed in the system message when the selected provider protocol supports it, while row-specific text stays in the user message. Multi-turn chat transcripts are deliberately outside the scalar function surface. Store or serialize the conversation in your own table and pass the conversation summary or transcript as the prompt when you need row-wise SQL enrichment. ## Runtime state scope Usage events, response-cache entries, and provider pacing state are scoped to the current DuckDB database instance. Separate embedded DuckDB databases in the same process do not share these buffers. The affected state includes: - `ai_usage()` and `ai_clear_usage()`, - opt-in response cache entries used by `cache := true` or `duckdb_ai_cache`, - bounded query-local embeddings used to deduplicate `ai_similarity()` inputs, - generated SQL cache entries for successful `ai_query_data()` binds, - `duckdb_ai_max_concurrent_requests`, - `duckdb_ai_min_request_interval_ms`, and - `duckdb_ai_token_limit_per_minute`. ## Intra-chunk provider concurrency Row-wise provider scalar functions prepare row inputs, options, and secrets on the DuckDB execution thread, then submit provider work to a bounded executor owned by the current DuckDB database instance. Worker threads persist across vector chunks instead of being created once per vector. The DuckDB result vectors are filled after worker completion on the execution thread. This applies to: - `ai_complete()`, `ai_try_complete()`, `ai_complete_json()`, and `ai_completion_request_json()`, - task wrappers such as `ai_summarize()`, `ai_classify()`, `ai_redact()`, `ai_translate()`, and `ai_filter()`, - `ai_embed()`, `ai_embedding_request_json()`, and `ai_similarity()`, and - `ai_sql()`. The worker count is bounded by `duckdb_ai_max_concurrent_requests` or `max_concurrent_requests := ...` when configured. Without an explicit cap, the extension uses a conservative local worker count for the chunk while the provider rate limiter still controls outbound request pacing. Configured worker caps must be between 0 and 64; larger values are rejected instead of being silently clamped. SQL-assistant table functions perform one provider call per invocation, plus up to `fix_attempts` correction calls when the generated SQL does not bind. Aggregate functions use one request for groups that fit `max_context_chars` and a bounded parallel map/reduce request tree for larger groups. ## Embedding request packing `ai_embed()`, `ai_similarity()`, classifier training, and optimized classification pack embedding inputs by provider/model option group. A request is closed when the configured input count, estimated token count, or request byte limit would be exceeded. External model `options` can override these limits; conservative built-in defaults apply otherwise. `ai_similarity()` deduplicates both sides of every row, keeps a bounded query-local embedding cache across DuckDB vector chunks, embeds each distinct value once, and computes cosine similarity locally. Its request count therefore scales with packed distinct values rather than two HTTP requests per row. The query cache is capped at 8 MiB and the database keeps at most eight recent query caches; `ai_clear_cache()` clears it explicitly. Query-cache hits appear in `ai_usage_summary()` without increasing `batch_count`. When a provider rejects a multi-input embedding request with HTTP 413 or a recognized payload/context-size error, the extension recursively bisects that request. Recoverable splits do not count as terminal failures in usage summaries. Input packing measures the encoded JSON payload, so quotes, control characters, and other escaped content are included in the byte limit. A single input that exceeds an external model's declared context or byte limit fails explicitly. ## Context-safe aggregate reduction `ai_agg()` and `ai_summarize_agg()` never truncate grouped input. Groups within `max_context_chars` use one final request. Larger groups are split on UTF-8-safe boundaries, mapped concurrently into compact evidence, packed, and recursively reduced until one final request fits. Every child uses the parent query's operation tree for usage attribution. Set `overflow_policy := 'error'` to reject an oversized group before any provider call. Hierarchical reduction also stops immediately with an explicit error when a provider's intermediate responses do not reduce either the chunk count or total byte size. ## Estimated token pacing Completion reservations include the user prompt, system prompt, response schema, and requested output limit (512 tokens when no output limit is supplied). Schema text can appear in both the system instructions and the provider's structured output field; both transmitted copies count. External model input limits use the same input estimate. `max_input_tokens` limits only input; `context_size` limits estimated input plus the requested output maximum (512 estimated tokens when unspecified). Both limits apply when a profile supplies both. Native output limits are read from the request JSON body. For embedding models, both context limits constrain input. Profiles may set `token_estimate_multiplier` to a finite value from 1 through 16 to scale input estimates conservatively for that provider/model; completion pacing, context checks, and embedding batching share the multiplier. Choose it from measured usage on representative prompts. It does not provide exact provider tokenization, and `ai_count_tokens()` retains its unscaled local estimate. Estimates still use roughly one token per four UTF-8 bytes, so they do not guarantee exact enforcement of a provider's token quota. Generated SQL caching for `ai_query_data()` also uses the resolved provider, credential fingerprint, and profile options. Rotating credentials or replacing a profile therefore creates a new cache key. Recency updates take constant time; retained key and SQL text is limited to 64 MiB and 1,024 entries per database. Container overhead is additional. Oversized entries are not retained. ## Cancellation and retries Provider HTTP calls use libcurl with interrupt-aware progress callbacks. When a DuckDB query is interrupted, in-flight provider calls abort and retry sleeps are also interruptible. Retries are disabled by default. When enabled with `retry_count` or `duckdb_ai_retry_count`, retryable HTTP failures use exponential backoff with jitter. HTTP `Retry-After` headers on provider responses take precedence over the configured backoff. Every retry wait, including one requested by `Retry-After`, is capped at 60 seconds. Usage events keep one `operation_id` for all input events produced by a packed request. Hierarchical aggregate children additionally carry a `parent_operation_id`; retries retain the same operation ID and increment the event's retry count. `ai_usage_summary()` exposes both row-event calls and distinct request batches, plus bounded-buffer drop counters. ## HTTP connection behavior The extension initializes libcurl once per process, reuses a thread-local easy handle for provider requests, and attaches those handles to a shared libcurl connection cache. This allows libcurl to reuse connections across provider worker threads where the provider and libcurl build support it. Provider calls use the configured `timeout_seconds` for the total request (default 120 seconds, or `DUCKDB_AI_TIMEOUT_SECONDS`). The connect timeout defaults to the smaller of 10 seconds and the total timeout. Set `connect_timeout_seconds := ...`, `duckdb_ai_connect_timeout_seconds`, or `DUCKDB_AI_CONNECT_TIMEOUT_SECONDS` to override it for provider requests. Provider redirects are not followed. This avoids forwarding authorization or API-key headers to an unexpected redirect target. Provider and usage-log destinations must use `http` or `https`. URLs containing embedded credentials or raw control characters are rejected before libcurl is called. The same control-character check applies to generated HTTP headers. Provider and usage-log response bodies are limited to 64 MiB by default to prevent an untrusted endpoint from growing an unbounded in-memory buffer. Set `DUCKDB_AI_MAX_RESPONSE_BYTES` to a positive byte count, up to 1 GiB, when a large batched embedding response requires a different bound. `CURLOPT_NOSIGNAL` is enabled so DuckDB host processes are not exposed to libcurl signal behavior during DNS or timeout handling. ## Response caching Response caching is opt-in: ```sql SET duckdb_ai_cache = true; SELECT ai_complete('Summarize this repeated prompt.'); SELECT ai_complete('Summarize this repeated prompt.'); SELECT * FROM ai_clear_cache(); ``` The response cache is in-memory and scoped to the current DuckDB database instance. It is keyed by provider, model, endpoint, request payload, and response-relevant options. API keys are not stored directly in cache keys. Cached responses still record usage events, but their elapsed time is reported as `0` because no provider HTTP request was made. The maximum number of cached entries defaults to `1024`. Set `cache_max_entries := ...`, `duckdb_ai_cache_max_entries`, or `DUCKDB_AI_CACHE_MAX_ENTRIES` to change the bound. Use `0` to disable response-cache storage. Cached response bodies and both retained key copies also have a hard 64 MiB per-database bound; entries exceeding that bound are not cached. Container overhead is additional. `ai_query_data()` also keeps a small in-memory generated-SQL cache for successful binds. `ai_clear_cache()` clears the response, generated-SQL, and similarity query caches. Typed completion functions validate their output before recording a successful usage event. Invalid JSON, schema violations, unknown classification labels, invalid booleans or scores, and record projection failures produce error events and evict the rejected response from the cache. A later call can request a fresh response. Validation failures do not automatically retry the model call. ## Deterministic performance benchmark The local benchmark uses a threaded mock embedding endpoint and reports request count, wall time, peak DuckDB thread count, peak resident memory, and dropped usage events for 1,000 and 10,000 rows. It also derives a provider cost estimate from mock token usage at a fixed price: ```sh python3 test/benchmarks/ai_runtime_benchmark.py ``` It covers unique `ai_embed()` inputs and repeated `ai_similarity()` inputs. The latter uses 20 distinct values at both row counts, making a regression from query-level distinct-value scaling directly visible in `http_requests`. ## Egress allowlisting Use `duckdb_ai_allowed_hosts` or per-call `allowed_hosts := ...` to restrict provider and usage-log destinations: ```sql SET duckdb_ai_allowed_hosts = 'ai-gateway.internal,api.openai.com'; SELECT ai_complete( 'Summarize this.', allowed_hosts := 'api.openai.com' ); ``` Entries can be hostnames, `host:port`, full URLs, wildcard subdomains such as `*.example.com`, or `*`. The allowlist check runs before the HTTP request, so disallowed hosts fail without sending a provider request. When the `DUCKDB_AI_ALLOWED_HOSTS` environment variable is set, a request must match it as well, so session and per-call allowlists can only narrow it. Stored API keys are only sent to the origin they are configured for; see [security and data flow](security-data-flow.md). ## JSON parsing Provider responses and JSON Schema inputs are parsed with DuckDB's vendored `yyjson` parser. This covers completion text extraction, token usage fields, embedding arrays, `ai_complete_json()` validation, and `ai_complete_record()` projection. `ai_complete_json()` and `ai_complete_record()` intentionally implement a documented JSON Schema subset rather than full JSON Schema draft parity. Schema `pattern` checks use RE2-compatible regular expressions so validation has bounded-time matching behavior; constructs unsupported by RE2 are rejected. Type unions accept only their listed types. Nested boolean schemas are supported: `true` allows any value and `false` rejects every value. String `minLength` and `maxLength` count Unicode code points, so an emoji counts as one and a combining mark counts separately from its base character. Numeric equality and bounds compare the original decimal values, including equivalent exponent spellings. Integer `multipleOf` checks use decimal arithmetic without narrowing to 64 bits. Numbers whose double conversion is non-finite are rejected. Typed integer projection preserves exact values within the signed `BIGINT` range; projected `number` fields use DuckDB `DOUBLE` precision. Non-integer `multipleOf` checks still use floating-point tolerance. ## Lifetime and persisted diagnostics `ai_query_cache_stats()` exposes generated SQL cache retention, bounds and lookup/eviction counters. `ai_usage_totals()` keeps provider row events, transport attempts, failures, cache hits and partial known-token totals independently of usage-event eviction. Counters are per database, protected by the existing state mutexes, and reset by their corresponding clear function. They are in memory; [`examples/resumable_enrichment.py`](https://github.com/leonardovida/duckdb-ai/blob/main/examples/resumable_enrichment.py) (see the [resumable enrichment cookbook](cookbooks/resumable-enrichment.md)) persists each batch's usage snapshot in the same transaction as its results, so totals survive process reopening and uncommitted snapshots roll back with a crashed batch. # Security and data flow The `ai` extension is a network-calling DuckDB extension. Treat every provider call as an explicit data egress path from DuckDB to the configured provider or gateway. ## Defaults - No telemetry is sent by default. - Usage events stay in the current DuckDB database instance unless `duckdb_ai_log_endpoint` or `DUCKDB_AI_LOG_ENDPOINT` is configured. - Usage logs omit prompt, input, and response text by default. - API keys are read from environment variables or `TYPE duckdb_ai` secrets. - An API key is only sent to the origin (scheme, host and port) it is configured for: the `BASE_URL` of the secret that holds it, otherwise the provider's built-in endpoint or base URL environment variable, plus any host listed in `DUCKDB_AI_ALLOWED_HOSTS`. A query-level `base_url`, `duckdb_ai_base_url` or external model `location` cannot redirect it. - External model objects contain only safe routing metadata and the name of a `TYPE duckdb_ai` credential secret; they never contain credential material. - `TYPE duckdb_ai` secrets redact `API_KEY` values when listed. - Provider error messages redact the active API key before surfacing errors. - Provider redirects are not followed for API calls. - Provider and log URLs are restricted to HTTP(S), and URLs with embedded credentials or raw control characters are rejected. - HTTP response bodies are bounded to 64 MiB by default; use `DUCKDB_AI_MAX_RESPONSE_BYTES` to configure a different bound up to 1 GiB. - Caller-controlled HTTP header values reject CR, LF, and NUL characters. - Provider response parsing uses DuckDB's vendored `yyjson` parser. ## Egress controls Use `duckdb_ai_allowed_hosts` or per-call `allowed_hosts := ...` to restrict provider and usage-log destinations: ```sql SET duckdb_ai_allowed_hosts = 'ai-gateway.internal,api.openai.com'; ``` Allowlist entries match URL hosts. Entries may be hostnames, `host:port`, full URLs, `*.example.com`, or `*`. The allowlist check runs before the HTTP request, including for usage-log endpoints. The `DUCKDB_AI_ALLOWED_HOSTS` environment variable is a ceiling set by whoever starts the process. Every request must match it as well as any session allowlist, so `duckdb_ai_allowed_hosts` and `allowed_hosts := ...` can narrow it but not widen it. The URL validation still runs when no host allowlist is configured. An allowlist controls which HTTP(S) hosts are reachable; it does not enable other libcurl protocols. `SET enable_external_access = false` blocks every provider and usage-log request, including requests to a local model server, with a `Permission Error`. Endpoints listed in DuckDB's `allowed_directories` or `allowed_paths` stay reachable, for example after `SET allowed_directories = ['https://api.openai.com/']` before external access is disabled. Functions that only build or inspect requests, such as `ai_completion_request_json`, keep working. Under `on_error := 'null'` a blocked row returns `NULL` and `ai_usage()` records the reason. DuckDB also stops reading the persistent secret directory once external access is off, so set `allow_persistent_secrets = false` first or use environment variables and temporary secrets. For a shared server, such as one serving remote clients over Quack, see [Run on a Quack server](quack-server.md). ## Data sent by function family | Function family | Data sent | | --- | --- | | `ai_complete`, `ai_try_complete`, `ai_complete_json`, `ai_rerank`, task wrappers, aggregates, and SQL assistant functions | Prompt text, configured system prompt, model name, response-format hints, and provider options needed by the selected provider API. | | `ai_complete_record`, `ai_extract_record`, `ai_complete_json` | Prompt or row text plus the supplied JSON Schema. | | `ai_decide`, `ai_jev`, and `ai_classify`/`ai_filter` with `typesafe` | Row text as `state`, plus question labels, descriptions and instructions. TypeSafe batches up to 32 rows per request; other decision providers receive one row per request. | | `ai_provider_call` | The full request body you supply. | | `ai_embed`, `ai_similarity`, and optimized classification | Input text to embed and model name. Inputs with the same options are packed by count, estimated tokens, and bytes. `ai_similarity` uses a bounded query-local cache to send each distinct value once across vector chunks. | | `ai_redact` with `openai_privacy_filter` | Raw input text and model name to `POST /redact`. | | `ai_generate_chunks`, `ai_prep_search(..., enrich := false)`, `ai_completion_request_json`, `ai_embedding_request_json`, `ai_schema_prompt`, `ai_is_read_only_sql`, `ai_validate_read_only_sql`, `ai_count_tokens`, `ai_recommended_batch_size`, metadata functions, and catalog table functions | No provider network call. | | `ai_sql`, `ai_query_data`, `ai_explain_sql`, `ai_fix_sql` with `sample_rows := N` | Up to N real rows per included table are added to the schema context. Omit `sample_rows` for sensitive tables, or build a table without the sensitive columns, for example `CREATE TABLE tickets_for_sql AS SELECT * EXCLUDE (internal_note) FROM support_tickets;`. Views are not described by `ai_schema_prompt`. | | `ai_query_data` | The natural-language question and generated schema context are sent during bind; generated SQL is validated as one read-only DuckDB `SELECT` before execution. SQL assistant schema context is sent in the provider system message when the provider protocol supports it. Successful generated SQL is cached in memory for repeated binds with the same question, schema context, and output-affecting options. | | `ai_parse_document` | Binary document content encoded as base64, MIME type, parser profile, and optional page selection are sent to `DUCKDB_AI_CONTROL_PLANE_URL`. | | Endpoint provisioning functions | Safe external-model metadata, operation IDs, and explicit spend ceilings are sent to `DUCKDB_AI_CONTROL_PLANE_URL`. Cloud credentials are not read or sent by the extension. | ## Optional control plane Endpoint provisioning and remote document parsing are disabled until `DUCKDB_AI_CONTROL_PLANE_URL` is configured. The optional `DUCKDB_AI_CONTROL_PLANE_TOKEN` is sent as a bearer token and should be scoped to the companion service, not to a cloud provider. Applied provisioning requires an explicit `dry_run := false` and positive hourly spend ceiling. Registration, query binding, and first inference never provision resources. ## Logging Outbound usage logs are disabled unless a log endpoint is configured. Default payloads contain function name, query id, provider, protocol, model, character counts, token counts, elapsed time, HTTP status, optional tags, and optional estimated cost. Text fields are included only when `duckdb_ai_log_include_text` or `DUCKDB_AI_LOG_INCLUDE_TEXT=1` is enabled. When strict logging is disabled, outbound usage logs are queued in memory and sent asynchronously on a best-effort basis. Enabling `duckdb_ai_log_strict` or `DUCKDB_AI_LOG_STRICT=1` posts logs synchronously and fails the SQL query if the collector request fails. ## Proxy and TLS Provider HTTP calls use libcurl. Standard environment variables such as `HTTPS_PROXY`, `HTTP_PROXY`, `NO_PROXY`, `SSL_CERT_FILE`, and `CURL_CA_BUNDLE` can be used where supported by the libcurl build. ## Vulnerabilities Report suspected vulnerabilities using the process in the repository `SECURITY.md`. Do not include API keys, provider credentials, private data, or full model prompts in public issues. # Run on a Quack server DuckDB 2.0 ships [Quack](https://duckdb.org/docs/current/quack/overview), a client-server protocol that lets one DuckDB instance serve others over HTTP. When a client routes its session to the server with `CONNECT`, every query runs on the server. The `ai` extension, its provider credentials and its outbound requests then all live on the server, and clients need neither the extension nor any API key. ## Start the server Community extensions are not autoloaded, so the server has to load `ai` itself. Put the setup in an init file: ```sql -- ai-server.sql INSTALL ai FROM community; LOAD ai; SET GLOBAL duckdb_ai_provider = 'openai'; ``` Then start the server with the provider key in its environment: ```sh OPENAI_API_KEY=... duckdb analytics.duckdb -init ai-server.sql -serve ``` `-serve` starts Quack on `localhost:9494` and stores its token in a persistent `quack` secret, so `duckdb -connect` on the same machine works without further setup. To serve other machines, call `quack_serve` with `allow_other_hostname` and put a TLS-terminating reverse proxy in front, as described in the [Quack security guide](https://duckdb.org/docs/current/quack/security). Instead of an environment variable, the key can live in a persistent secret on the server, which survives restarts: ```sql CREATE PERSISTENT SECRET openai_server ( TYPE duckdb_ai, AI_PROVIDER 'openai', API_KEY '...' ); ``` ## Call AI functions from a client Attach the server and route the session to it: ```sql CREATE SECRET (TYPE quack, TOKEN '⟨server token⟩', SCOPE 'quack:ai-server.example:443'); ATTACH 'quack:ai-server.example:443' AS srv; CONNECT srv; SELECT ticket_id, ai_classify(body, ['billing', 'outage', 'other']) AS category FROM support_tickets; DISCONNECT; ``` While connected, every statement runs on the server, including `SET duckdb_ai_*` and `CREATE SECRET`. A client can therefore pick models or bring its own `TYPE duckdb_ai` secret for its session. Where the call runs depends on how the client reaches the server: | Client SQL | Where `ai_*` runs | Who needs the extension and a key | | --- | --- | --- | | `CONNECT srv; SELECT ai_classify(body, ...) FROM t` | Server | Server | | `FROM srv.query('SELECT ai_classify(body, ...) FROM t')` | Server | Server | | `SELECT ai_classify(body, ...) FROM srv.t` without `CONNECT` | Client | Client | AI calls stop when the server interrupts the query, for example when the server's `max_execution_time` passes or a client cancels the query with `quack_cancel`. ## What clients share Each `CONNECT`ed client gets its own server session, but the extension's state is per database instance: - `ai_usage()` lists the usage events of every client, and `ai_clear_usage()` or `ai_clear_cache()` from one client clears them for all. - The response cache, rate limits and token budgets are shared. All clients spend from the same provider account when they use the server's key. Use Quack's authentication and authorization hooks to decide who may connect and which statements they may run. ## Keep credentials and egress on the server Remote clients can run any SQL the server allows, so the extension does not let a session decide where the server's keys or requests go: - **Keys stay with their origin.** An API key from a `TYPE duckdb_ai` secret is only sent to the scheme, host and port of the `BASE_URL` stored in that secret. Without one, the key, like a key from an environment variable, is only sent to the provider's endpoint: its built-in URL or the base URL environment variable (for example `OPENAI_BASE_URL`). A client that passes `base_url := ...`, sets `duckdb_ai_base_url`, or creates an external model whose `location` points elsewhere gets an error, and no request is sent. - **The environment allowlist is a ceiling.** When the server process has `DUCKDB_AI_ALLOWED_HOSTS` set, every provider and usage-log request must match it. A session can narrow it with `duckdb_ai_allowed_hosts` but not widen it. Hosts in this variable may also receive the server's keys. - **`enable_external_access = false` applies.** With external access disabled, the extension only sends requests to endpoints listed in `allowed_directories` or `allowed_paths`. A locked-down server starts Quack in its init file before it disables external access, because Quack and persistent secrets are read from the home directory: ```sql -- ai-server-locked.sql INSTALL ai FROM community; LOAD ai; SET GLOBAL duckdb_ai_provider = 'openai'; CALL quack_serve(); SET allowed_directories = ['https://api.openai.com/']; SET enable_external_access = false; ``` ```sh DUCKDB_AI_ALLOWED_HOSTS=api.openai.com OPENAI_API_KEY=... duckdb analytics.duckdb -init ai-server-locked.sql ``` `enable_external_access` cannot be turned back on for the running instance. To send a key through a gateway, store the gateway URL in the secret, set the provider's base URL environment variable, or list the gateway host in `DUCKDB_AI_ALLOWED_HOSTS`: ```sql CREATE PERSISTENT SECRET gateway ( TYPE duckdb_ai, AI_PROVIDER 'openai', API_KEY '...', BASE_URL 'https://ai-gateway.internal/v1' ); ``` See [security and data flow](security-data-flow.md) for the full list of egress controls.