PROMPT_JEV

Preview

prompt_jev is in preview. Requests from every cloud region are processed by TypeSafe's servers in the United States. The function name, parameters, and return types may change. Avoid workloads with regulated or sensitive data while in Preview.

prompt_jev function

The prompt_jev function sends a text value and a question to a TypeSafe Jev model and returns a typed decision: a probability, a choice from a fixed list, or a position on an ordered scale. Every answer carries calibrated probabilities, so you can act on the decision and on how certain the model is about it.

prompt_jev answers closed questions. It never returns free text. Use prompt when you need generated text, open-ended extraction, or a schema the model fills in itself.

Consumption is measured in AI Units. Only input tokens are billed; output tokens are free. The following row estimates account for text, instructions, and question tokens:

Question	Input length	Rows per AI Unit
noul	50 characters	~345,000
choice	50 characters	~260,000
choice, batch_size := 1	50 characters	~60,000
choice	1,000 characters	~60,000
score	1,000 characters	~60,000

These estimates assume the default batch of 32 rows per request, with a fixed overhead of about 250 input tokens per request, shared across the rows in the batch. With batch_size := 1, each row carries the full overhead. They also assume 40-character instructions and four 10-character labels for choice and score. Batching and multiple questions change the input tokens used per source row, so text length alone doesn't determine the cost. Use billed input-token usage to estimate a workload.

Syntax

prompt_jev(input, instructions)
prompt_jev(input, instructions, noul := criteria)
prompt_jev(input, instructions, choice := criteria)
prompt_jev(input, instructions, score := criteria)
prompt_jev(input, questions := questions)

input and instructions are positional. choice, score, noul, questions, and batch_size must be named:

SELECT prompt_jev(
    message,
    'Which team should handle this?',
    choice := ['billing', 'technical', 'sales']
)
FROM support_messages;

choice, score, and noul are mutually exclusive, and none of them can be combined with questions. Omitting all of choice, score, and noul runs a noul question.

Parameters

Parameter	Type	Required	Description
input	VARCHAR	Yes	The text to evaluate. This is the only argument that can vary per row. Must be the first argument.
instructions	VARCHAR	Conditional	The question the model answers. A non-empty constant. Required unless questions is supplied.
noul	VARCHAR[] or STRUCT(label VARCHAR, description VARCHAR)[]	No	Runs a yes/no question. The default when choice, score, and noul are all omitted. If supplied, must contain exactly the labels true and false.
choice	VARCHAR[] or STRUCT(label VARCHAR, description VARCHAR)[]	Conditional	Between 2 and 255 unique, non-empty labels to pick from.
score	VARCHAR[] or STRUCT(label VARCHAR, description VARCHAR)[]	Conditional	Between 2 and 10 unique, non-empty levels. Array position defines the order, lowest to highest.
questions	STRUCT or JSON	Conditional	Runs several questions against the same input in one request. See Multiple questions. Not combined with instructions, choice, score, or noul.
batch_size	INTEGER	No	How many non-NULL input rows to send per request, from 1 to 64. Defaults to 32. Only valid in single-question mode. See Automatic batching.

Every argument except input must be a constant for the query. MotherDuck validates them and resolves the return type while binding the query, before the first row is read.

Labels must be unique and non-empty. Descriptions may be NULL or a non-empty string:

-- Labels only
['low', 'medium', 'high']

-- Labels with optional descriptions
[
    {label: 'low', description: 'Can wait several days'},
    {label: 'medium', description: NULL},
    {label: 'high', description: 'Requires immediate action'}
]

Descriptions are input metadata that helps the model tell similar labels apart. They don't change the returned value, which is always the label.

Question types

Each question type has its own return type.

noul

Answers "is this true?" and returns a DOUBLE between 0 and 1: the calibrated probability that the statement in instructions holds for input.

SELECT prompt_jev(
    'The payment has failed for three days and nobody has replied.',
    'Does this describe an urgent problem?'
) AS urgency;

0.93

Pass noul with explicit labels only to attach descriptions to true and false; the labels-only form noul := ['true', 'false'] is equivalent to omitting noul entirely:

SELECT prompt_jev(
    message,
    'Does this request a refund?',
    noul := [
        {label: 'true', description: 'The customer asks for money back'},
        {label: 'false', description: 'No refund is requested'}
    ]
) AS refund_probability;

choice

Answers "which of these options?" and returns a STRUCT:

STRUCT(
    choice        VARCHAR,
    probabilities STRUCT(value VARCHAR, probability DOUBLE)[],
    confidence    DOUBLE
)

• choice is one of the labels you passed in choice.
• probabilities has one entry per label, in the order you supplied them.
• confidence is between 0 and 1 and describes how certain the model is about the winning option.

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

{'choice': billing, 'probabilities': [{'value': billing, 'probability': 0.94}, {'value': technical, 'probability': 0.04}, {'value': sales, 'probability': 0.02}], 'confidence': 0.91}

Descriptions help the model disambiguate labels that read as similar:

SELECT prompt_jev(
    message,
    'Which team should handle this?',
    choice := [
        {label: 'billing', description: 'Payments, invoices, and refunds'},
        {label: 'technical', description: 'Errors, outages, and integrations'},
        {label: 'sales', description: 'Pricing, trials, and upgrades'}
    ]
) AS routing
FROM support_messages;

score

Answers "which level?" against an ordered rubric and returns a STRUCT:

STRUCT(
    score         DOUBLE,
    probabilities STRUCT(index UINTEGER, value VARCHAR, probability DOUBLE)[],
    confidence    DOUBLE
)

• score is a weighted position on the scale, between 0 and length(score) - 1. A rubric of ['low', 'medium', 'high'] returns a value between 0 and 2, and a value of 1.4 sits between medium and high.
• probabilities has one entry per level, ordered by index, with index the zero-based position of the level and value its label.
• confidence is between 0 and 1.

Order the levels from lowest to highest. The array position defines the scale.

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

{'score': 1.82, 'probabilities': [{'index': 0, 'value': low, 'probability': 0.02}, {'index': 1, 'value': medium, 'probability': 0.14}, {'index': 2, 'value': high, 'probability': 0.84}], 'confidence': 0.88}

Which type to use

TypeSafe groups decisions into six task categories. Each maps onto one of the three question types.

Task	Type	Example
Classification	choice	Route a ticket to a team, label a topic, pick a risk category
Detection	noul	Flag spam, fraud, or a prompt injection attempt
Scoring	score	Rate severity, content quality, or customer frustration
Ranking	score or noul	Order candidates by semantic fit using the returned number
Verification	noul	Check a citation, a policy rule, or an answer for a known failure mode
Structured extraction	choice or score per field	One questions call per row, where the possible values are known up front

For extraction where the values are not known up front, use prompt with a struct or json_schema instead.

Multiple questions

Ask several questions about the same input in a single request with questions. Every question needs a non-empty instructions string, a type of noul, choice, or score, and valid criteria when its type requires them:

SELECT prompt_jev(
    message,
    questions := {
        refund: {
            type: 'noul',
            instructions: 'Does this request a refund?'
        },
        category: {
            type: 'choice',
            instructions: 'Classify the primary subject.',
            criteria: ['billing', 'technical', 'other']
        },
        urgency: {
            type: 'score',
            instructions: 'How urgent is this?',
            criteria: ['low', 'medium', 'high']
        }
    }
) AS answer
FROM support_messages;

The result is a named STRUCT with one field per question, each in its question type's normal return shape:

SELECT
    answer.refund,
    answer.category.choice,
    answer.category.confidence,
    answer.urgency.score
FROM classified;

batch_size is only available in single-question mode; questions calls send one request per input row. That request carries input once for the whole set, so asking twenty things at once costs far fewer input tokens than twenty single-question queries over the same table, at the price of losing row batching.

JSON escape hatch

Pass questions as a JSON string to reach parts of the TypeSafe API that the native STRUCT form doesn't cover, such as structured or omitted instructions, or criteria described with arbitrary nested JSON:

SELECT prompt_jev(
    message,
    questions := '{
      "category": {
        "type": "choice",
        "instructions": {
          "question": "Classify the primary subject",
          "focus": ["primary intent", "requested action"]
        },
        "criteria": {
          "billing": {
            "description": "Charges and invoices",
            "examples": ["duplicate charge", "missing invoice"]
          },
          "technical": "Bugs and integrations"
        }
      }
    }'::JSON
) AS answer
FROM support_messages;

MotherDuck inspects the constant JSON only to determine the SQL return type; the question configuration itself is passed to TypeSafe for validation. For score criteria described as arbitrary JSON, probabilities comes back as JSON rather than the native STRUCT(index, value, probability)[] shape. Native score criteria — a plain array of labels or label/description structs — always return probabilities in the native shape with labels as VARCHAR.

A TypeSafe validation failure, including an HTTP 422 response, fails the query as an error rather than returning NULL for the row.

Automatic batching

Native single-question calls automatically batch input rows into fewer requests. The default is 32 rows per request. The optional batch_size argument can be used to adjust the batch size:

SELECT prompt_jev(
    message,
    'Does this request a refund?',
    batch_size := 16
)
FROM messages;

batch_size must be between 1 and 64; MotherDuck may still send a smaller batch when a request would otherwise exceed TypeSafe's request-size limit.

Larger batches improve throughput and lower cost. Batching can introduce extra variance from sharing a context window across multiple rows, though the effect is typically small. For strict per-row isolation, set batch_size := 1.

Example usage

Build an analytical dimension from free text

Classification turns a text column into a column you can group by. Declare the CTE AS MATERIALIZED: without it, DuckDB may re-evaluate the CTE, and call the model again, for every field you read from the result.

WITH classified AS MATERIALIZED (
    SELECT prompt_jev(
        resolution,
        'What outcome does this closure note document?',
        choice := ['fixed', 'no_issue_found', 'access_failed', 'referred', 'unclear']
    ) AS result
    FROM complaints
)
SELECT
    result.choice AS outcome,
    count(*) AS complaints
FROM classified
GROUP BY outcome
ORDER BY complaints DESC;

outcome	complaints
fixed	420
no_issue_found	280
access_failed	160
referred	90
unclear	50

Filter rows with a probability

A noul question returns a plain DOUBLE, so it works in a WHERE or QUALIFY clause. Materialize the score first so the model runs once per row rather than once per predicate evaluation.

CREATE TABLE refund_requests AS
SELECT
    message_id,
    body,
    prompt_jev(body, 'Does this message request a refund?') AS refund_probability
FROM support_messages;

SELECT message_id, body
FROM refund_requests
WHERE refund_probability > 0.8;

Read the probability distribution

Unnest probabilities when you need the full distribution rather than the winning answer.

WITH routed AS MATERIALIZED (
    SELECT prompt_jev(
        body,
        'Which team should handle this?',
        choice := ['billing', 'technical', 'sales']
    ) AS routing
    FROM support_messages
)
SELECT
    p.value AS team,
    avg(p.probability) AS mean_probability
FROM routed, unnest(routing.probabilities) AS t(p)
GROUP BY team
ORDER BY mean_probability DESC;

Store results in a table

Write results to a table so later queries read the stored value instead of calling the model again.

ALTER TABLE support_messages ADD COLUMN severity STRUCT(
    score DOUBLE,
    probabilities STRUCT(index UINTEGER, value VARCHAR, probability DOUBLE)[],
    confidence DOUBLE
);

UPDATE support_messages
SET severity = prompt_jev(
    body,
    'Rate the severity of this message.',
    score := ['low', 'medium', 'high']
)
WHERE severity IS NULL;

Composing the input

input is a single text value. To evaluate several fields together, concatenate them into one string with labels that tell the model what each part is.

SELECT prompt_jev(
    'Subject: ' || subject || E'\n' ||
    'Plan: ' || plan_name || E'\n' ||
    'Message: ' || body,
    'Does this customer describe a billing problem?'
) AS billing_problem
FROM support_messages;

Keep everything in input relevant to the question. Unrelated text dilutes the signal.

Error handling

Argument problems fail the query while it's being bound, before any row is processed:

Error	Cause
prompt_jev requires instructions	instructions was omitted and questions was not supplied
prompt_jev "choice", "score", and "noul" cannot be combined	More than one of choice, score, or noul was supplied
prompt_jev requires at least two criteria for type "choice"	Fewer than two labels for choice
prompt_jev requires at least two criteria for type "score"	Fewer than two levels for score
prompt_jev Noul criteria require exactly the labels "true" and "false"	noul was supplied with labels other than true/false
prompt_jev criteria labels must be unique	choice, score, or noul contains a duplicate label
prompt_jev "instructions" parameter must be a constant value	An argument other than input references a column
AI functions are not available for your organization.	The organization is on the Free plan, has AI functions disabled, or has asked support to disable prompt_jev on request

Per-row failures do not fail the query. A row returns NULL when:

• input is NULL. No request is sent for that row.
• The request still fails after retries, or the query hits the AI function timeout.

A questions call with a TypeSafe validation failure fails the query rather than returning NULL. See JSON escape hatch.

Check for NULL to find rows that did not get an answer, and fill them in with a second pass:

SELECT count(*)
FROM support_messages
WHERE severity IS NULL AND body IS NOT NULL;

Notes

These capabilities are provided by MotherDuck's integration with TypeSafe. Text you pass as input, along with your instructions and criteria, is sent to the TypeSafe API for processing. See the TypeSafe integration page for what MotherDuck sends and how to turn the function off for your organization.

prompt_jev runs against the jev-latest model alias. There is no model parameter.

A valid response can still be an incorrect decision. See Jev model limitations before selecting tasks and confidence thresholds.

Admins can change the AI session cap on the AI Features settings page.