sqlite-jev

September 18, 2026 · View on GitHub

Natural-language predicates, classification, and scoring for SQLite, powered by TypeSafe Jev. It is inspired by pg-jev, but shaped around SQLite's loadable-extension and virtual-table APIs.

.load ./build/jev

SELECT t.id, t.subject, round(j.probability, 3) AS urgency
FROM jev_rows(
  'tickets',
  'The customer explicitly expresses urgency or says work is blocked',
  'noul',
  NULL,
  json_array('subject', 'message')
) AS j
JOIN tickets AS t ON t.rowid = j.source_rowid
WHERE j.probability >= 0.6;

jev_rows reads the selected columns, puts up to 40 rows in one shared state, asks one question per row, and returns a virtual table that can be joined to the source by rowid. Results are cached for the lifetime of the SQLite connection.

Build

Requirements:

SQLite 3.45 or newer, built with JSON support and loadable extensions
A C11 compiler and make
The libcurl runtime (libcurl.so.4 on Linux or libcurl.4.dylib on macOS)
A TypeSafe API key

The current development machine already has all of these; no extra system package is needed.

make
export TYPESAFE_API_KEY=...
sqlite3 my.db

Inside SQLite:

.load ./build/jev
SELECT jev_version();

TYPESAFE_API_KEY is the only environment variable used for the API key.

Batched table queries

The signature is:

jev_rows(table_name, question [, kind [, criteria [, columns]]])

It returns source_rowid, answer, probability, choice, score, and confidence. Columns that do not apply to the chosen primitive are NULL.

Boolean judgment (Noul)

SELECT
  t.id,
  j.probability AS yes_probability,
  1.0 - j.probability AS no_probability,
  CASE WHEN j.probability >= 0.7 THEN 'yes' ELSE 'no' END AS answer
FROM jev_rows(
  'tickets',
  'The ticket explicitly asks for a refund',
  'noul',
  NULL,
  json_array('subject', 'message')
) AS j
JOIN tickets AS t ON t.rowid = j.source_rowid
ORDER BY t.id;

A Noul returns the probability of yes; no is its complement. Choosing the larger one is equivalent to a 0.5 threshold. Keep the threshold in SQL so it can rise with the cost of a false positive. The scalar jev(state, condition) uses 0.5 unless given a third argument.

Classification (Choice)

SELECT t.id, j.choice AS team, j.confidence
FROM jev_rows(
  'tickets',
  'Which team should handle the main request?',
  'choice',
  json_object(
    'billing', 'Charges, invoices, payment methods, or refunds',
    'technical', 'Bugs, failures, or integrations',
    'sales', 'Pricing, plans, demos, or new purchases',
    'other', 'Anything outside those teams'
  ),
  json_array('subject', 'message')
) AS j
JOIN tickets AS t ON t.rowid = j.source_rowid;

Ordered rating (Score)

SELECT t.id, j.score, j.confidence
FROM jev_rows(
  'tickets',
  'How frustrated is the customer?',
  'score',
  json_array('Calm and factual', 'Frustrated but civil', 'Very angry or abusive'),
  json_array('message')
) AS j
JOIN tickets AS t ON t.rowid = j.source_rowid;

For accuracy and cost, include only the columns the judgment needs. jev_rows requires a rowid table. To prefilter a large data set, materialize the filtered rows into a temporary table and evaluate that table.

Scalar functions

Scalar functions are convenient for one record. When scanning a table, prefer jev_rows so Jev can evaluate many questions in a single request.

Function	Result
`jev(state, condition [, threshold])`	Boolean Noul predicate; default threshold is `0.5`
`jev_prob(state, condition)`	Noul probability from 0 to 1
`jev_choice(state, question, criteria_json)`	Most likely Choice key
`jev_score(state, question, levels_json)`	Probability-weighted Score level
`jev_score_norm(state, question, levels_json)`	Score normalized to 0 through 1
`jev_confidence(state, question, kind, criteria_json)`	Choice or Score confidence
`jev_eval(state, question [, kind [, criteria_json]])`	Full answer JSON
`jev_stats()`	Connection-local usage and cache statistics
`jev_cache_clear()`	Clears the connection-local answer cache
`jev_version()`	Extension version

Pass structured state with SQLite JSON functions:

SELECT jev_prob(
  json_object('subject', subject, 'message', message),
  'The customer explicitly expresses urgency'
)
FROM tickets
WHERE id = 42;

Configuration

Configuration is connection-local:

SELECT jev_config('model', 'jev-latest');
SELECT jev_config('batch_size', 40);
SELECT jev_config('max_rows', 500);
SELECT jev_config('timeout', 90);
SELECT jev_config('api_url', 'https://api.typesafe.ai/v1/systemone');
SELECT jev_config('api_key', '...');

max_rows is a spend guard. A jev_rows scan above the limit fails before sending any data. The API key is never returned by jev_config; it reports only set or unset.

Python

Add the platform package to a project and load it with the Python wrapper:

uv add sqlite-jev

import sqlite3
import sqlite_jev

connection = sqlite_jev.load(sqlite3.connect(":memory:"))

The package contains the same native extension as the standalone release, has no runtime Python dependencies, and supports maintained Python versions starting with Python 3.10. The interpreter's sqlite3 module must have loadable-extension support enabled.

To load a standalone build manually instead:

import sqlite3

connection = sqlite3.connect(":memory:")
connection.enable_load_extension(True)
connection.load_extension("/path/to/jev.so")  # use jev.dylib on macOS
connection.enable_load_extension(False)

version = connection.execute("select jev_version()").fetchone()[0]

Comparison

The projects below all bring TypeSafe Jev into SQL, but target different databases and workflows. This is a feature comparison based on their public documentation and repositories on 2026-09-18; not documented means the capability was not described there, not that it cannot be built by an application around the project.

Capability	`sqlite-jev`	`mattn/sqlite3-jev`	`EugeneBoondock/jevsql`	`realZachi/pg-jev`
Primary target	SQLite loadable extension + Python package	SQLite loadable extension	Node.js library and CLI over SQLite	PostgreSQL extension
Batched work over a table	Yes — `jev_rows` batches up to 40 source rows in one API state	No — each SQL row invokes a scalar function; `jev(state, questions_json)` can batch questions for one state	Yes — collects distinct judgments before dispatching requests	Yes — executor read-ahead batches rows (20 by default)
Table-valued / virtual-table interface	Yes — `jev_rows(...)` yields rows that join through `source_rowid`	No — registers scalar functions only	No — runs/re-writes SQL from the Node library	No — ordinary PostgreSQL functions plus executor read-ahead
SQL primitives	Noul, Choice, Score; full answer, probability/confidence helpers	Noul, Choice, Score; raw and multi-question JSON calls	Noul, Choice, Score, match, candidate extraction/pick, review bands	Noul, Choice, Score; full answer and probability/confidence helpers
Cache and spend control	Connection-local cache; `max_rows` guard	Connection-local configuration; retry/backoff	In-memory or file cache; judgment and estimated-cost caps	Session cache; row and character guards
Persisted decisions, refresh history, and audit receipts	No	No	Yes	No
Python distribution	Yes — `sqlite-jev` wheel and `sqlite_jev.load()`	Not documented (can be loaded from bindings that support SQLite extensions)	No	No — PostgreSQL / PL-Python dependency
Local-model endpoint documented	TypeSafe endpoint configurable	Yes — documented `tensai`-compatible local endpoint	TypeSafe API client	TypeSafe endpoint configurable
Automated CI	Yes — mock tests plus wheel build/test on Linux x86_64/arm64 and macOS Intel/Apple Silicon; scheduled live smoke test	No — has local `make test` / mock-server tests, but no GitHub Actions workflow in the current repository	Yes — mock-server tests on Ubuntu and Windows with Node 22/24	Yes — GitHub Actions and deterministic regression tests
Prebuilt native artifacts	Yes — GitHub Release archives and platform Python wheels for Linux/macOS x86_64/arm64	No — build the extension from source with `make`	Not applicable — Node.js library/CLI, not a native SQLite extension	No prebuilt library documented; PGXN installs from its source distribution
Platform claim	Tested Linux and macOS; release archives and Python wheels for x86_64/arm64	Linux, macOS, and Windows (MinGW-w64)	Node 22.16+; CI covers Ubuntu and Windows	PostgreSQL 14–17; requires `plpython3u` and a superuser

sqlite-jev is the SQLite option when the desired query shape is a relational batch: select source columns, evaluate them together, and join typed results back in SQL. JevSQL is stronger when decision tables, refreshes, and audits are the primary workflow. sqlite3-jev currently has the broader documented native-platform and local-model story; pg-jev offers the most mature PostgreSQL-specific batching and execution integration. All four send evaluated row data to a configured model endpoint, so normal data-sharing and cost controls still apply.

Demo and tests

The deterministic suite uses a local mock server and never calls TypeSafe:

make test

The ticket-triage demo makes two live API requests, one for urgency and one for routing:

make live-test

See examples/ticket_triage.sql for the complete query.

make integration-test runs a smaller live smoke test. In GitHub Actions it runs once every two months and on manual dispatch using the TYPESAFE_API_KEY repository secret. It checks the API contract, batching, and answer shapes; semantic expectations remain in the deterministic mock suite so normal model variation cannot make pull requests flaky.

Releases

Tags named vX.Y.Z build and publish four archives through GitHub Actions:

Linux x86_64 and arm64
macOS Intel and Apple Silicon

Each archive contains the native extension, this README, and its pyproject.toml. The release also includes a py3-none-<platform> Python wheel and SHA256SUMS. CI installs each wheel with cibuildwheel and verifies that sqlite_jev.load() enables the SQL API. The same tag publishes the wheels and source distribution to PyPI through trusted publishing.

Important limits

Row contents are sent to TypeSafe. Do not evaluate data you are not allowed to share.
This is a semantic full scan, not an index. Apply deterministic SQLite filters first and materialize a small candidate table.
Cache entries live only for the current database connection and are keyed by row content, model, question, primitive, and criteria.
Jev should make narrow judgments. Keep counting, arithmetic, and date comparison in SQL.
Text stored in a row can steer model behavior. Test adversarial content and use conservative probability or confidence thresholds before automating consequential actions.
libcurl is loaded dynamically so building does not require the curl development headers. Linux and macOS are the currently tested platforms.