DuckDB

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:

• choice picks one label from a set of options.
• score rates the input on ordered levels.
• noul gives 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.

InstallCopy Anchor Link

drex is a DuckDB community extension, so any DuckDB 2.0 client installs it:

INSTALL drex FROM community;
LOAD drex;

This installs release v0.1.0. It's built for DuckDB 2.0 only, on Linux and macOS (x86-64 and Arm) and Windows x86-64, and runs in any local DuckDB client, including the CLI and the Python and Node.js packages. DuckDB-Wasm in the browser isn't supported, since a browser can't call the Drex API. Its source is nace-ai/drex-duckdb on GitHub, under the MIT license.

To upgrade to a newer release, run UPDATE EXTENSIONS (drex);, then restart DuckDB.

Set your API keyCopy Anchor Link

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, so recreate it with CREATE OR REPLACE SECRET after changing either. 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.

UsageCopy Anchor Link

Ask one question per rowCopy Anchor Link

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:

probabilities always holds every option, sorted by label. Reading answers explains each field.

Criteria are a list of {'label': ..., 'description': ...} structs, as above, or plain labels: choice := ['billing', 'technical', 'sales']. A choice or score takes 2 to 255 unique labels. A score's levels take no descriptions, so describe the scale in the instructions. noul criteria labels are true and false. The instructions, criteria, questions and model must be constants, not columns.

Ask several questions at onceCopy Anchor Link

questions := sends every question in one request per row, so the row is sent and billed once instead of once per question. It takes 1 to 512 questions, as a SQL struct or a JSON object.

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), ...).

Get the raw responseCopy Anchor Link

drex_decide(state, questions [, model := ...]) sends JSON state and questions as they are and returns Drex's whole response as JSON, without mapping the answers to SQL types. A NULL state or questions returns NULL.

SELECT drex_decide(
  '{"message": "I was charged twice"}'::JSON,
  '{"refund": {"type": "noul", "instructions": "Is a refund needed?"}}'::JSON
);

Use thresholdsCopy Anchor Link

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 imagesCopy Anchor Link

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');

An image that isn't PNG, JPEG or WebP, or a row whose request would exceed the 4 MiB body limit, returns NULL without being sent, so resize large images first. Drex Max's image limits list the rest.

ModelsCopy Anchor Link

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.03 and Drex Max $0.03 per million input tokens; see Pricing.

The extension checks the query before it reads any row: the model must be one that GET /v1/models lists for your key (cached per database and endpoint), images need a model that reads them, and the labels and questions must follow the rules above and in Questions. A query that fails a check returns an error and sends nothing. A row over the model's token limit isn't checked in advance: Drex refuses it, and it returns NULL.

Concurrency and rate limitsCopy Anchor Link

The extension sends up to 8 requests at once, shared by every query and thread on the same database. Each database a process opens has its own limit. That fits the in-flight limit of a free account:

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 database (0, the default, means none):

SET drex_max_rpm = 60;

ErrorsCopy Anchor Link

• 401, 402, a 422 about the questions or model, and a TLS failure fail the query, and no further rows are sent. A 422 message lists each issue's path and message; a 402 links to Billing.
• No response, 408, 429, 500, 502, 503, 504 and 529 are retried after retry-after-ms (or retry-after, at most 30 seconds), at most 5 attempts within 2 minutes, each up to 60 seconds. A row whose retries run out returns NULL.
• A row Drex refuses on its own, such as one over the token limit or with an invalid image, and any other status, such as 403 or 404, return NULL with a warning in DuckDB's log. The warning never quotes the row.

Treat NULL as no answer, not as a "no". Only a 200 is billed, so a failed attempt or a row that returns NULL costs nothing; a retry is billed twice only if a 200 was lost on its way back. See Errors and retries.

What gets sent, and billedCopy Anchor Link

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 n sends exactly n rows.
• ORDER BY ... LIMIT sends every row, OFFSET sends whole chunks of 2,048 rows, and a filter on the answer with LIMIT sends a chunk or more. Filter and limit the rows in a subquery, then call prompt_drex() on its result.
• Every read of a view, a Python relation or a NOT MATERIALIZED CTE sends its rows again. Keep the answers with CREATE TABLE ... AS (or a relation's to_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);

CostCopy Anchor Link

Each row is billed for its input tokens at the model's price, and output tokens aren't billed. For 10,000,000 rows:

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.

MotherDuckCopy Anchor Link

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_jevCopy Anchor Link

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.