Integrations
DuckDB
Answer typed questions about every row of a DuckDB table in SQL with prompt_drex, the DuckDB extension for Drex.
drex is a DuckDB extension that calls Drex, Nace's decision models, from SQL. Its prompt_drex() function answers typed questions about each row and returns calibrated probabilities. See Questions for how to write them. There are three types:
choicepicks one label from a set of options.scorerates the input on ordered levels.noulgives the probability that a yes/no statement is true.
prompt_drex() takes the same arguments and returns the same types as MotherDuck's prompt_jev, plus a model argument. See Switching from prompt_jev.
Install
The drex extension isn't in DuckDB's community extensions yet, so the commands below work once it's published.
INSTALL drex FROM community;
LOAD drex;The extension requires DuckDB 2.0 and runs in any local DuckDB client except DuckDB-Wasm in the browser. Its source is nace-ai/drex-duckdb on GitHub, under the MIT license.
Set your API key
Store the key in a DuckDB secret:
CREATE SECRET (TYPE nace, API_KEY 'nace_sk_...');Outside a quick try, read the key from the environment instead, which keeps it out of your SQL and your shell history:
CREATE SECRET (TYPE nace, PROVIDER env);PROVIDER env reads NACE_API_KEY, and NACE_BASE_URL if it's set, when the secret is created. A query run without a secret fails. duckdb_secrets() shows the secret with its key redacted. Create a key as shown in Authentication.
BASE_URL (or NACE_BASE_URL) must use https://. http:// is accepted only for localhost, 127.0.0.1 and [::1], since the key would otherwise cross the network in the clear.
Usage
Ask one question per row
Each non-NULL row is one Drex request. A NULL input returns NULL and isn't sent or billed.
SELECT
ticket_id,
prompt_drex(
body,
'Which team should handle this support ticket?',
choice := [
{'label': 'billing', 'description': 'Payments, invoices, refunds and charges'},
{'label': 'technical', 'description': 'Bugs, crashes and errors in the product'},
{'label': 'sales', 'description': NULL}
]
) AS team
FROM tickets;┌───────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ ticket_id │ team │
│ int32 │ struct(choice varchar, probabilities struct("value" varchar, probability double)[], confidence double) │
├───────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 1 │ {'choice': billing, 'probabilities': [{'value': billing, 'probability': 0.9832}, {'value': sales, 'probability': 0.005}, {'value': technical, 'probability': 0.0118}], 'confidence': 0.9748} │
│ 2 │ {'choice': technical, 'probabilities': [{'value': billing, 'probability': 0.0106}, {'value': sales, 'probability': 0.0132}, {'value': technical, 'probability': 0.9762}], 'confidence': 0.9643} │
└───────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘The return type depends on the question:
| Argument | Returns |
|---|---|
noul := criteria (or none) | DOUBLE, the probability of yes |
choice := [...] | STRUCT(choice, probabilities STRUCT(value, probability)[], confidence) |
score := [...] | STRUCT(score, probabilities STRUCT(index, value, probability)[], confidence) |
questions := {...} | A STRUCT with one field per question |
probabilities always holds every option, sorted by label. Reading answers explains each field.
Ask several questions at once
questions := sends every question in one request per row, so the row is sent and billed once instead of once per question.
SELECT
ticket_id,
prompt_drex(
body,
questions := {
'urgency': {'type': 'score', 'instructions': 'How urgent is this ticket?', 'criteria': ['can wait', 'this week', 'today']},
'refund': {'type': 'noul', 'instructions': 'Is the customer asking for a refund?'}
}
) AS answers
FROM tickets;┌───────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ ticket_id │ answers │
│ int32 │ struct(urgency struct(score double, probabilities struct("index" uinteger, "value" varchar, probability double)[], confidence double), refund double) │
├───────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 1 │ {'urgency': {'score': 1.2151, 'probabilities': [{'index': 0, 'value': can wait, 'probability': 0.1565}, {'index': 1, 'value': this week, 'probability': 0.4719}, {'index': 2, 'value': today, 'probability': 0.3716}], 'confidence': 0.7359}, 'refund': 0.9075} │
│ 2 │ {'urgency': {'score': 1.4252, 'probabilities': [{'index': 0, 'value': can wait, 'probability': 0.1188}, {'index': 1, 'value': this week, 'probability': 0.3372}, {'index': 2, 'value': today, 'probability': 0.544}], 'confidence': 0.7126}, 'refund': 0.0651} │
└───────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘A JSON input is sent as structured state, so a whole row works too: prompt_drex(to_json(tickets), ...).
Use thresholds
Every answer carries its probabilities, so a query decides what is confident enough and leaves the rest for a person or another model:
WITH labelled AS (
SELECT ticket_id, prompt_drex(body, 'Which team should handle this support ticket?', choice := [...]) AS team
FROM tickets
)
SELECT ticket_id, team.choice
FROM labelled
WHERE team.confidence >= 0.8;Choose the threshold from a labelled sample; Reading answers covers what each probability means.
Read images
Drex Max reads images. Pass a BLOB, or a LIST of them, as the input; prompts refer to them as "Image 1", "Image 2" and so on. PNG, JPEG and WebP are accepted.
SELECT
filename,
prompt_drex(
content,
'What kind of document is Image 1?',
choice := [
{'label': 'invoice', 'description': 'A bill or invoice'},
{'label': 'receipt', 'description': 'A proof of payment'},
{'label': 'other', 'description': NULL}
],
model := 'drex-max'
) AS kind
FROM read_blob('scans/*.jpg');A row whose request would exceed the 4 MiB body limit returns NULL, so resize large images first. Drex Max's image limits list the rest.
Models
The default model is drex-v2.0, which reads text and JSON. Pass model := 'drex-max' for Drex Max, which also reads images. drex-v2.0 costs $0.06 and Drex Max $0.03 per million input tokens; see Pricing.
Pin model := in production if your queries rely on thresholds. A new model can shift probabilities.
The extension checks the query before it reads any row: the model must be one that GET /v1/models lists for your key, images need a model that reads them, and the questions must fit the model's limits. A query that fails a check returns an error and sends nothing.
Concurrency and rate limits
The extension sends up to 8 requests at once, shared by every query and thread in the DuckDB process. That fits the in-flight limit of a free account:
| Tier | RPM | In-flight |
|---|---|---|
| free | 120 | 8 |
| paid | 600 | 16 |
On a paid account, raise it for the session or for one call:
SET drex_max_concurrency = 16;
SELECT prompt_drex(body, 'Is this a complaint?', concurrency := 16) FROM tickets;Every API key on an account shares its limits (see Scope), so lower the concurrency when other clients use the same account.
The extension doesn't cap requests per minute by default. Drex answers an account over its rate limit with a 429 and the wait, and the extension retries after it. To stay further under the limit, for example to leave room for other clients, set a cap for the process (0, the default, means none):
SET drex_max_rpm = 60;Errors
401,402and422fail the query. A422message lists each issue'spathand message; a402links to Billing.429,529and5xxare retried afterretry-after-ms. A row whose retries run out returns NULL.- Each request times out after 60 seconds.
Only a 200 is billed, so a retried or failed row costs nothing. See Errors and retries.
What gets sent, and billed
Every row DuckDB evaluates prompt_drex() on is a request, and every request Drex answers is billed, whether or not the query returns that row.
- A plain
LIMIT nsends exactly n rows. ORDER BY ... LIMITsends every row,OFFSETsends whole chunks of 2,048 rows, and a filter on the answer withLIMITsends a chunk or more. Filter and limit the rows in a subquery, then callprompt_drex()on its result.- Every read of a view, a Python relation or a
NOT MATERIALIZEDCTE sends its rows again. Keep the answers withCREATE TABLE ... AS(or a relation'sto_table) and read the table.
CREATE TABLE ticket_teams AS
SELECT ticket_id, prompt_drex(body, 'Which team should handle this support ticket?', choice := [...]) AS team
FROM (SELECT * FROM tickets ORDER BY ticket_id DESC LIMIT 100);Cost
Each row is billed for its input tokens at the model's price, and output tokens aren't billed. For 10,000,000 rows:
| Input tokens per row | Input tokens for 10,000,000 rows | drex-v2.0 | drex-max |
|---|---|---|---|
| 50 | 500,000,000 | $30.00 | $15.00 |
| 250 | 2,500,000,000 | $150.00 | $75.00 |
| 1,000 | 10,000,000,000 | $600.00 | $300.00 |
The same row counts a different number of tokens on each model, so read usage.input_tokens for a sample of rows on the model you plan to use before running a whole table.
MotherDuck
MotherDuck Cloud doesn't run community extensions, so prompt_drex() runs in your local DuckDB client. The extension requires DuckDB 2.0, and MotherDuck supports DuckDB up to 1.5.x today, so it can't run in a client attached to MotherDuck yet.
Once both run on one version, copy the MotherDuck rows into a local table, then call prompt_drex() on it:
ATTACH 'md:my_db';
CREATE TABLE tickets AS SELECT ticket_id, body FROM my_db.tickets;
CREATE TABLE ticket_teams AS
SELECT ticket_id, prompt_drex(body, 'Which team should handle this support ticket?', choice := [...]) AS team
FROM tickets;Switching from prompt_jev
In a query that uses prompt_jev, rename the function and add a Nace secret:
-SELECT prompt_jev(body, 'Is this a complaint?') FROM tickets;
+SELECT prompt_drex(body, 'Is this a complaint?') FROM tickets;The arguments and return types are the same. multiple_choice := and box := aren't supported yet and are refused before any row is read.
Drex's own guide covers the API side: Migrate from TypeSafe.