Classify text with prompt_jev

Add decisions to your SQL results: a category, a yes/no probability or a score. Work through real public chart descriptions, inspect uncertain answers and save decisions locally.

Jev is a TypeSafe decision model accessed through OpenRouter. Ordinary SQL runs in DuckDB inside your browser. Jev inference runs remotely on the input texts and questions you choose to send.

1. Check access and enable Jev

You need beta access and a RawQL server configured for Jev. The operator sets OPEN_ROUTER_JEV_API in the server environment and restarts that server. The key must have OpenRouter credits and access to Jev. There is no browser key-entry screen. Never paste a key into SQL, an imported file or a snapshot.

For a local installation, an environment variable saved in Windows must be inherited by the process that starts RawQL. A running server does not automatically pick up a newly saved variable. RawQL uses the Decisions API and ~typesafe/jev-latest; you cannot select a different model in the SQL function.

1. Open a SQL worksheet in the editor.
2. Run a Jev statement. The first run previews its distinct input texts and questions.
3. Choose Enable Jev and run. Subsequent Jev queries run directly for this editor session, including different texts or questions.
4. Choose Disable Jev for this session in the status bar to revoke authorization. Leaving the editor session or reloading the tab also revokes it.

Canceling the initial dialog sends nothing. Once enabled, every Jev run may consume OpenRouter credits. The raw file is not uploaded; the selected input text values and questions travel through RawQL to OpenRouter and TypeSafe. See the privacy policy for data handling.

2. Classify real HTTP data

Use Vega's public gallery-examples.json. It contains chart names, descriptions and source categories. On 2 October 2026 it had 471 records, of which 371 had a non-empty description longer than 40 characters. This URL follows the upstream main branch, so its content can change.

Download the worked SQL script, or copy each block below. Execute one block at a time with Run Query. These examples create named tables; on a second attempt, use fresh table names or reuse the tables already created.

Load a bounded source locally

This first statement reads public JSON over HTTPS and creates a local table. It makes no Jev call. Keep the limit in this source step to bound what will be sent.

CREATE TABLE jev_gallery_source AS
SELECT example_name, description, categories
FROM read_json_auto(
  'https://raw.githubusercontent.com/vega/vega-datasets/main/data/gallery-examples.json'
)
WHERE description IS NOT NULL
  AND length(trim(description)) > 40
ORDER BY hash(example_name), example_name
LIMIT 32;

Ask for an analytical goal

Classify descriptions into six goals. Only description goes to Jev. Keep the original categories beside the decision for human review; they describe chart families and are not exact reference labels for this new task.

CREATE TABLE jev_gallery_results AS
SELECT example_name, description, categories AS categories_source,
       prompt_jev(description,
         'Classify the main analytical goal of this chart. evolution: change over time; distribution: spread, frequencies or proportions; relation: association between variables; geographie: spatial patterns on a map; comparaison: comparing or ranking groups; autre: unclear or none of these. Use only the description.',
         choice := ['comparaison', 'evolution', 'distribution',
                    'relation', 'geographie', 'autre'],
         batch_size := 32) AS objectif
FROM jev_gallery_source;

The new objectif column is a STRUCT. Inspect the selected label, confidence and full probability distribution without calling the model again:

SELECT example_name,
       objectif.choice AS category,
       objectif.confidence AS confidence,
       objectif.probabilities AS probabilities,
       categories_source, description
FROM jev_gallery_results
ORDER BY objectif.confidence ASC NULLS FIRST;

A live run on 2 October 2026 returned 32 rows from 31 distinct descriptions in one provider request, with a reported cost of $0.000313. Two descriptions were identical and shared one decision. These are observations from that run, not fixed prices or promised accuracy.

Confidence summarizes the provider's decision distribution. It is not an observed percentage of correct answers for your dataset. Review vague descriptions, competing labels and NULL results. Keep an autre option when your categories may not fit the input.

Summarize and save

SELECT objectif.choice AS category, count(*) AS rows
FROM jev_gallery_results
GROUP BY objectif.choice
ORDER BY rows DESC;

This aggregation uses saved decisions and makes no provider request. Export this table or save a .rawql workspace snapshot to keep the results beyond the current session. To try all eligible descriptions, remove LIMIT 32 from the source statement and use new table names. Duplicate descriptions still share decisions within that call.

3. Add a category to an Excel worksheet

Download jev-gallery.xlsx, a 32-row extract of the same public descriptions. It is real source data, not generated support tickets. The extract is a fixed sample, separate from the HTTP query's hash-ordered sample. Source revision and license accompany the file.

1. Open Databases → Data files and import the workbook.
2. Find its actual view name in the catalog. For this filename, use jev_gallery_xlsx.
3. Inspect the columns before sending their values.

DESCRIBE jev_gallery_xlsx;
SELECT example_name, description FROM jev_gallery_xlsx LIMIT 5;

Create a local table containing every imported column plus a category. The source workbook remains unchanged.

CREATE TABLE jev_excel_categorized AS
SELECT *, prompt_jev(description,
  'Which chart family does this description describe? Choose other if unclear.',
  choice := ['bar', 'line', 'scatter', 'map', 'histogram', 'other'],
  batch_size := 32).choice AS category
FROM jev_gallery_xlsx;

SELECT * FROM jev_excel_categorized;

For your own file, replace the view name, text column, instructions and labels. A 1000-row worksheet of short, distinct texts needs 32 provider requests with batch_size := 32 before retries or payload splitting. Begin with a small local source table and review its decisions before increasing the volume. See the Excel guide for import behavior and formula limitations.

4. Detect, score and rank

Yes/no detection with Noul

The default mode returns a DOUBLE from 0 to 1, the probability of yes. Here we ask whether the description explicitly mentions a user action. This is a new paid run; it does not reuse the earlier category decision.

CREATE TABLE jev_interaction AS
SELECT example_name, description,
       prompt_jev(description,
         'Does this description explicitly mention interaction by the user, such as hovering, clicking, selecting, filtering or zooming? Do not infer interaction from chart type alone.',
         batch_size := 32) AS interaction_probability
FROM jev_gallery_source;

A value near 0.5 means uncertainty about yes versus no. It does not mean a chart is halfway interactive. You can filter the saved table with a threshold such as interaction_probability >= 0.8, but choose and evaluate that threshold against examples you have checked yourself. Noul can also verify a claim such as whether a description explicitly mentions a map.

Ordered scoring

Ask how much information a description provides about its data variables. Define levels in ascending order. Score returns the probability-weighted position, so three levels produce a score between 0 and 2, including fractional values.

CREATE TABLE jev_description_scores AS
SELECT example_name, description,
       prompt_jev(description,
         'How much detail does this description provide about the data variables?',
         score := ['no variables identified', 'some variables mentioned',
                   'variables and visual encodings explained'],
         batch_size := 32) AS detail
FROM jev_gallery_source;

Rank the saved scores locally. A score is relative to your chosen levels; it does not establish an objective quality standard.

SELECT example_name, detail.score AS score,
       detail.confidence AS confidence, detail.probabilities AS probabilities
FROM jev_description_scores
ORDER BY score DESC NULLS LAST;

Several decisions about one input

Named questions return a STRUCT containing one typed result per name. This mode makes one provider request per distinct input. Do not combine questions with instructions, noul, choice, score or batch_size.

SELECT example_name, prompt_jev(description, questions := {
  mentions_map: {type: 'noul', instructions: 'Does the description explicitly mention a map?'},
  location: {type: 'choice', instructions: 'Which location is explicitly named?',
             criteria: ['Seattle', 'another location', 'no location named']}
}) AS decisions
FROM jev_gallery_source;

The location question demonstrates extraction into predefined values. Read decisions.mentions_map as a DOUBLE and decisions.location.choice as a label. Generating an arbitrary city name, summary or explanation requires open-ended text generation, which prompt_jev does not provide.

5. Arguments and return types

prompt_jev(input, instructions,
  noul := ..., choice := ..., score := ..., batch_size := ...)
-- Choose at most one of noul, choice and score.

prompt_jev(input, questions := ...)

input is a text column, literal or supported deterministic text expression such as concat, coalesce, trim or a text cast. Every other argument must be a literal constant. NULL input returns NULL without a provider call. Identical input texts share a decision within each function call; this is not a cache across executions.

Choice probabilities are a LIST of STRUCTs with value VARCHAR and probability DOUBLE. Native score probabilities add index UINTEGER; these indexes start at zero. DuckDB SQL LIST subscripts start at one. Both modes expose confidence DOUBLE, which stays NULL if the provider omits it.

Native criteria accept a VARCHAR list or a list of {label: ..., description: ...} STRUCTs. Descriptions may be NULL or non-empty text. Explicit Noul criteria must have exactly the labels true and false. Use descriptions to distinguish overlapping labels:

SELECT prompt_jev('The plot compares revenue across regions.',
  'Choose the analytical goal.', choice := [
    {label: 'comparison', description: 'Compare groups without mapping their locations.'},
    {label: 'geography', description: 'Show a spatial pattern on a map.'},
    {label: 'other', description: 'Neither goal is stated.'}
  ]) AS decision;

questions also accepts a literal JSON string for structured instructions and nested JSON criteria. Score probabilities for arbitrary JSON criteria can be JSON instead of the native LIST. Invalid provider configurations, including HTTP 422, fail the statement; RawQL does not turn them into invented decisions.

6. Batching, costs and current limits

Actual provider batches

For a single question, batch_size accepts 1–64 and defaults to 32. RawQL groups several text rows into one OpenRouter request, binds a question to each row and maps the keyed answers back to the original rows. Large payloads can reduce the effective batch size. Missing or invalid row answers retry only the failed rows, up to three attempts.

The batch shares a JSON context. Instructions bind each question to its row, but other rows may influence inference. Use batch_size := 1 when row isolation matters and compare the decisions on your own labeled sample. This adapter does not promise MotherDuck's exact inference behavior, throughput, prices or data residency.

Bound the input before Jev

A trailing LIMIT on a Jev query only limits the final SQL output. It does not bound the texts sent to the model. Filter or limit a source table or a static source CTE first, as in this guide. A predicate containing Jev must evaluate the candidate inputs before it can filter them.

The status bar shows progress, provider request count, failed texts and reported cost. Unknown cost means the provider or transport did not supply complete usage; it does not mean free. Retries and reruns may be billed. Cancel stops pending work but cannot undo a request already processed by the provider.

Supported SQL and failure handling

Use Jev in one outer SELECT or CREATE TABLE AS SELECT, including static source CTEs and joins. Materialize decisions before grouping, ranking, using set operations or joining them into further calculations. Jev inside a CTE or subquery, GROUP BY in the Jev statement, HAVING, QUALIFY, sampling, volatile input expressions, UPDATE, CALL and PRAGMA are unsupported. A Jev request accepts one statement; execute script blocks separately.

Invalid arguments fail before transmission. NULL inputs are skipped. Row errors or timeouts exhausted after retries become NULL; provider configuration errors fail the statement. Missing key, credits or model access makes Jev unavailable. A valid response can still be wrong. Evaluate decisions against manually checked examples rather than treating confidence as measured accuracy.

Save results explicitly in a local table. CSV export refuses to rerun a paid Jev expression; export the saved table instead. Query Profile does not measure remote inference and is unavailable for the combined Jev execution. There is no automatic result cache or automatic permanent storage.

When a run fails

• Unavailable: sign in and ask the operator to check the server environment, OpenRouter credits and Jev access.
• Quota or another request running: wait for the active run or quota window. Repeated attempts can still consume quota.
• Request too large: shorten the input, reduce batch size or process smaller source tables.
• Table already exists: inspect the saved table or change the output name. Rerunning inference is a new paid operation.
• HTTP source cannot be read: the server may lack compatible CORS or the URL may have changed. Use the downloadable Excel extract to continue with local data.

RawQL adapts the MotherDuck prompt_jev contract to local DuckDB WASM and the OpenRouter Decisions API. The guide describes RawQL's behavior, including its differences. Other AI functions such as text generation, embeddings and SQL assistants remain planned.