realZachi/pg-jev

pg-jev — plain-language queries inside PostgreSQL

pg-jev is a PostgreSQL extension that lets you filter, rank, and classify rows in your tables using natural-language conditions, powered by an AI model called Jev (from TypeSafe). Instead of writing WHERE age > 40 AND country = 'France', you can write WHERE jev(people, 'the name is European').

What it does

It adds SQL functions that judge each row against a condition you write in plain English:

• jev(row, 'condition') — returns true/false, usable in WHERE clauses
• jev_prob(row, 'condition') — returns a probability 0–1 that the row matches
• jev_choice(row, 'question', options) — picks the most likely option from a list (e.g., which team should handle a ticket)
• jev_score(row, 'question', levels) — ranks a row on an ordered scale (e.g., how luxurious is this product)

Because these are ordinary SQL functions, they compose with everything else: joins, GROUP BY, ORDER BY, LIMIT, and other WHERE predicates.

How it works

1. When you call jev() on a table, the extension starts read-ahead: it streams the table in physical order, batching rows.
2. Rows are packed 20 per API request into a single shared state, with one yes/no question per row. The model evaluates all 20 questions in parallel, which amortizes the per-request overhead (~175 tokens per row in batches of 20 vs ~435 for a single row).
3. Multiple requests run concurrently over persistent HTTPS connections. A LIMIT stops the read-ahead as soon as enough rows are answered, and cheaper predicates in the same WHERE run first so rejected rows are never sent to the API.
4. Answers are cached per row content and question for the session, so re-running a query or sorting by probability is nearly free the second time.

Measured on a 2,000-row table: first run ≈ 3.5 s and ≈ $0.012 in API cost; second run ≈ 50 ms (cached); LIMIT 3 on a new condition ≈ 0.6 s.

Key design choices

• No index, no embeddings, no vector column — it's a full scan by design; every row the executor asks about goes to the API.
• Batch size of 20 is a deliberate accuracy trade-off: against ground-truth tests, batches of 1–20 rows were 100% correct, but batches of 40 dropped to 92–98% and batches of 80 to 77–94%.
• Calibrated probabilities, not generated text — the model returns a probability for each row, so ambiguous cases land near 0.5 and you can tune a threshold.
• Local-server support — jev.api_url can point at a local Jev-compatible server, so rows never leave your network and there's no per-row billing.

Installation & requirements

Requires PostgreSQL 14–17 with plpython3u, a superuser, and a TypeSafe API key. Managed hosts that withhold superuser or plpython3u (Supabase, Neon, RDS) can't run it. Install via PGXN, from source, Docker, or an AI-agent skill that automates setup.

Caveats

• Row contents are sent to a third-party API (unless you run a local server) — don't use it on data you can't share.
• The answer cache lives per backend session and can grow with unique row/question pairs; jev_cache_clear() resets it.
• plpython3u is an untrusted language, so functions run with the server's OS privileges.
• Spend guards exist (jev.max_rows_per_statement, jev.max_chars_per_statement) for shared deployments.

Bottom line

pg-jev is a practical, well-engineered bridge between natural language and SQL — useful when you want to filter or rank data by meaning (sentiment, category, tone) rather than by exact values, and you're willing to pay a small per-row API cost and accept a full scan.