1# Chapter 2: postjevsql, the functions you call from SQL
2
3This crate is the extension itself: the part Postgres loads when you say
4`CREATE EXTENSION postjevsql`. It declares every SQL function, every `jev.*`
5setting, the `jev_cache` table and the result types, and it holds the
6*judge*, the code that turns a call into a request, a cache row and an
7answer.
8
9Here is the surprise: **the SQL functions do nothing.** Look at `jev_prob`
10in `src/lib.rs` and its body is one line, which raises an error. The work is
11done by a plan node (chapter 7) that finds every call to these functions in
12a query and answers them itself, many rows at once. The function body runs
13only if a call escaped that node, and then it refuses rather than quietly
14sending one request per row. The functions are, in effect, a vocabulary the
15planner recognises.
16
17## The functions
18
19Every function takes the row first: a table alias for the whole row
20(`jev(t, …)`, which sends every column), or a column list (`jev((t.title,
21t.body), …)`, which sends only those, costs fewer tokens and is usually what
22you want). Every one is `STRICT`, so a NULL argument gives NULL and sends
23nothing.
24
25| Function | Returns | What |
26| --- | --- | --- |
27| `jev(row, q)`, `jev(row, q, threshold)` | `bool` | True when the yes-probability is at least the threshold: the argument, else `jev.threshold`, else 0.5. |
28| `jev_prob(row, q)` | `float8` | The probability that the answer to yes/no `q` is yes (a Noul). |
29| `jev_eval(row, q)` | `jev_noul_result` | The same judgment in full: probability, the model that answered, and the request's tokens. |
30| `jev_score(row, q, levels)` | `float8` | The probability-weighted level, 0 to n − 1, on a rubric of 2–10 `levels` given lowest first. |
31| `jev_score_norm(row, q, levels)` | `float8` | That level over n − 1, so 0 to 1. |
32| `jev_score_full(row, q, levels)` | `jev_score_result` | Score, confidence, per-level probabilities, legend, model, tokens. |
33| `jev_choice(row, q, options)` | `text` | The likeliest of 1–255 `options`, sent in your order. One option is returned without asking. |
34| `jev_choice_full(row, q, options)` | `jev_choice_result` | Choice, confidence, `[label, p]` pairs in your order, model, tokens. |
35| `jev_confidence(row, q, kind, options)` | `float8` | The vendor's confidence in a `'choice'` or `'score'`, exactly as returned. A Noul has none, so `'noul'` is refused. |
36| `jev_choice_tree(row, q, paths [, tau])` | `text` | A choice among any number of labels arranged as a tree, each path written `A > B > leaf`. With `tau`, the deepest group at least that likely. |
37| `jev_choice_tree_full(row, q, paths [, tau])` | `jev_tree_result` | Path, probability, separation from the runner-up, depth, leaf or not, and `fit`. |
38| `jev_choice_shortlist(row, q, options [, descriptions])` | `text` | A choice among any number of flat labels, in two requests: chunks of up to 254, then a final over the top 3 of each. |
39| `jev_choice_shortlist_full(…)` | `jev_shortlist_result` | The final Choice in full, and `fit`. |
40| `jev_stats()` | one row | This session's requests, retries, redials, connections, cache hits and misses, `dedupe_hits`, tokens, cost and streams in flight. |
41
42Calls that ask the same question share one judgment: `jev` and `jev_prob`
43on the same question, `jev_score` and `jev_score_norm`, a scalar function and
44its `_full` twin, `jev_confidence` and the Choice or Score it is about. One
45request, one cache row. `fit` is the probability, asked as a separate yes/no
46question in the first request, that any of the labels fits at all, since a
47Choice always picks one even when none is right.
48
49> **Aside.** Why must options keep your order? Because order changes the
50> answer. One measurement moved a correct label from 0.34 to 0.83 just by
51> listing it last instead of first. So the extension never shuffles: your
52> order is sent, your order is part of the cache key, and reordering a list
53> is, correctly, a different question.
54
55## The settings
56
57| Setting | Who may set it | Default | What |
58| --- | --- | --- | --- |
59| `jev.model` | anyone | (none; required) | The pinned model, such as `jev-1.13.0`. Aliases like `jev-latest` are refused when set, and an answer from any other model fails the statement. |
60| `jev.endpoint` | superuser | `https://api.typesafe.ai/v1/systemone` | Where requests go. Must be `https`, always; the tests' mock serves TLS too, trusted through `jev.ca_file`. Whoever sets it receives the API key. |
61| `jev.api_key_file` | superuser | | A file holding the API key. Exactly one of this and `TYPESAFE_API_KEY` in the server's environment; both is an error. |
62| `jev.ca_file` | superuser | | Extra CA certificates to trust, for tests and private endpoints. |
63| `jev.dns_servers` | superuser | empty: `/etc/resolv.conf` | `ip[:port], …` to resolve the endpoint's host with. |
64| `jev.keepalive_interval`, `jev.keepalive_timeout` | superuser | 20 s, 10 s | HTTP/2 pings while a scan runs, and how long one may go unanswered. |
65| `jev.concurrency` | anyone | 100 | Rows a scan judges at once, each one HTTP/2 stream; clamped to what the server accepts. |
66| `jev.price_per_mtok` | superuser | 0.042 | Dollars per million input tokens, jev-1.13.0's launch price. Output is not billed. |
67| `jev.max_rows`, `jev.max_cost` | anyone | -1 (no limit) | Rows and dollars one statement may send or spend, checked before the first request and while it runs. 0 sends nothing. |
68| `jev.max_tokens_per_second`, `jev.max_requests_per_minute` | config file | 250,000, 1,200 | The account's limits, shared by every backend of the cluster; 0 is no limit. |
69| `jev.cache_namespace` | anyone | empty | Part of every cache key: changing it re-judges everything. |
70| `jev.cache_only` | anyone | off | Serve answers from the cache only, and fail a miss without sending. Needs no API key. |
71| `jev.on_error` | anyone | `error` | `unsure` turns a judgment that failed at the remote into NULL, so a `jev` filter skips that row instead of failing the statement. |
72| `jev.threshold` | anyone | 0.5 | Where `jev()` turns true when the call gives no threshold. Yes/no questions only. |
73
74## What it keeps, and what it refuses
75
76Every answer is a row in `jev_cache`, keyed by the hash of exactly what was
77sent, and each row is a receipt: the request bytes, the requested and the
78answering model, TypeSafe's request id, the tokens billed, the full answer,
79the namespace and the time. The table is revoked from `PUBLIC`; the scan
80reaches it as its owner, because a role that could write it could forge
81answers.
82
83Every function is revoked from `PUBLIC` at install, so spending the API key
84takes an explicit `GRANT EXECUTE`. And every failure carries a SQLSTATE you
85can branch on, with the request id and attempt count in its DETAIL:
86
87| Failure | SQLSTATE |
88| --- | --- |
89| a setting, or an invalid request | 22023 |
90| a call left outside the batch scan | 0A000 |
91| no API key, two, or HTTP 401/403 | 28000 |
92| DNS, connect, TLS | 08001 |
93| interrupted, timed out | 08006 |
94| 429 or 529 | 53000 |
95| a miss under `jev.cache_only` | 55000 |
96| a row a concurrent update changed, in a recheck | 40001 |
97| over `jev.max_rows` or `jev.max_cost`, context overflow, a response over 8 MB | 54000 |
98| any other API error, or a wrong answer | 38000 |
99
100> **Try it.** `EXPLAIN SELECT * FROM people p WHERE jev(p, 'Could this
101> person work from home?');` sends nothing and needs no API key. The plan
102> shows a `Custom Scan (JevScan)` with `Candidate Rows`, `Estimated
103> Requests`, `Estimated Input Tokens` and `Worst-Case Cost`: the price,
104> before it is paid. `EXPLAIN ANALYZE` runs it and adds `Cache Hits`, `Cache
105> Misses`, `Shared Judgments`, `Requests`, `Input Tokens` and `Cost`.
106
107## For the people who maintain it
108
109| Path | What |
110| --- | --- |
111| [src/](src/) | The extension's code: settings, SQL, the judge, the failures. Chapter 3. |
112| [BUCK](BUCK) | The library, and `:ext`, the installable tree (`share/extension/postjevsql.control`, the install script generated from the `.so` by `tools/pgrx-schema`, and `lib/postjevsql.so`) that the tests and the nix package install. |
113| [postjevsql.control](postjevsql.control) | The control file. Its version is pgrx's `@CARGO_VERSION@` placeholder, filled from `build/defs.bzl` at build. |
114| [Cargo.toml](Cargo.toml) | Generated by `tools/cargo-gen` (chapter 20), with a `pg17` and a `pg18` feature. |
115
116← Previous: [Chapter 1, crates/](../) · Up: [crates](../) · Next: [Chapter 3, postjevsql/src/](src/) →