1# postjevsql: a guide in twenty-eight chapters
2
3**Ask your database a question it cannot answer.** "Could this person work
4from home?" is not in any column. No `WHERE` clause can say it, no index can
5find it, and yet it is a perfectly good thing to want to filter on. This
6repository is a PostgreSQL extension that lets you write it anyway:
7
8```sql
9SELECT name, jev_prob(people, 'Could this person work from home?') AS p
10FROM people
11WHERE country = 'PT' AND jev(people, 'Could this person work from home?')
12ORDER BY p DESC, name LIMIT 20;
13```
14
15The question goes to [Jev](https://docs.typesafe.ai), TypeSafe AI's "System
16One" model. Jev does not write. You cannot ask it to explain anything. You
17hand it a question whose answers are already written down (yes or no, which
18is a *Noul*; one of these labels, a *Choice*; somewhere on this scale, a
19*Score*) and it tells you how likely each one is, with numbers you can trust.
20That shape fits SQL like a glove: a probability is a `float8`, a label is
21`text`, and both can go in a `WHERE` or an `ORDER BY`, or be counted and
22averaged, like any other value.
23
24The extension is written in Rust with [pgrx](https://github.com/pgcentralfoundation/pgrx),
25and most of what is interesting about it is what happens between your `SELECT`
26and Jev: one plan node judges many rows at once over one HTTP/2 connection,
27every answer is kept in a table so it is never paid for twice, and nothing is
28sent at all until the price has been checked against your budget.
29
30> **Try it.** Open [tests/fixtures/ranking.json](tests/fixtures/ranking.json).
31> Those are two real requests this extension sent on 2026-09-26, byte for
32> byte, and what came back. Ana, a backend engineer in Portugal: could she
33> work from home? `0.87`. Rui, a nurse: `0.28`. Each row went alone, as its
34> own request, and the tests replay them from then on without spending a
35> cent (chapter 15 says why).
36
37## How to read this
38
39Every folder of this project's own code is a chapter (the few that are not
40are listed at the end of this page), and each one ends with a link to the
41next. Read them in order and you will know how all of it works; jump
42in anywhere and the chapter tells you what it is and what is beside it. Each
43chapter starts with the idea, in plain words, and ends with the parts a
44maintainer needs (files, invariants, commands). The `CLAUDE.md` beside each
45README is what an AI agent working here is told on top of the chapter; the
46one at the root holds the whole design contract.
47
48| Chapter | Folder | What you learn |
49| --- | --- | --- |
50| 1 | [crates/](crates/) | Four crates, and the wall that keeps `unsafe` in one of them. |
51| 2 | [crates/postjevsql/](crates/postjevsql/) | The extension: every SQL function and every setting. |
52| 3 | [crates/postjevsql/src/](crates/postjevsql/src/) | The judge: how a call becomes a request, a cache row and an answer. |
53| 4 | [crates/postjevsql/src/bin/](crates/postjevsql/src/bin/) | One generated line that cargo-pgrx needs. |
54| 5 | [crates/postjevsql-pg/](crates/postjevsql-pg/) | Where the `unsafe` lives, and why it all has to. |
55| 6 | [crates/postjevsql-pg/src/](crates/postjevsql-pg/src/) | An async runtime built out of Postgres's own wait. |
56| 7 | [crates/postjevsql-pg/src/scan/](crates/postjevsql-pg/src/scan/) | One plan node that judges every call in a query. |
57| 8 | [crates/postjevsql-core/](crates/postjevsql-core/) | The decisions that need no database. |
58| 9 | [crates/postjevsql-core/src/](crates/postjevsql-core/src/) | Layouts, cache keys, rate limits, and choosing among thousands. |
59| 10 | [crates/postjevsql-sidecar/](crates/postjevsql-sidecar/) | Using it with a database that will not let you install anything. |
60| 11 | [crates/postjevsql-sidecar/src/](crates/postjevsql-sidecar/src/) | The sidecar's config, its secrets, and its sync plan. |
61| 12 | [crates/postjevsql-sidecar/cli/](crates/postjevsql-sidecar/cli/) | `converge`, `sync` and `serve`. |
62| 13 | [tests/](tests/) | A real Postgres and a fake Jev for every test. |
63| 14 | [tests/support/](tests/support/) | The harness that starts them. |
64| 15 | [tests/fixtures/](tests/fixtures/) | Live answers, recorded once and replayed forever. |
65| 16 | [nix/](nix/) | The packages, the NixOS module and the container image. |
66| 17 | [tools/](tools/) | The helpers that write files so nobody has to. |
67| 18 | [tools/pgrx-schema/](tools/pgrx-schema/) | The install SQL, read out of the built library. |
68| 19 | [tools/pgrx-schema/src/](tools/pgrx-schema/src/) | Its one file. |
69| 20 | [tools/cargo-gen/](tools/cargo-gen/) | A Cargo workspace written from the buck graph. |
70| 21 | [tools/cargo-gen/src/](tools/cargo-gen/src/) | Its one file. |
71| 22 | [tools/third-party-notices/](tools/third-party-notices/) | Every licence the binaries carry, collected for you. |
72| 23 | [tools/third-party-notices/src/](tools/third-party-notices/src/) | Its one file. |
73| 24 | [tools/record-gates/](tools/record-gates/) | Pricing, then recording, the release gates. |
74| 25 | [tools/record-gates/src/](tools/record-gates/src/) | Its two files. |
75| 26 | [toolchains/](toolchains/) | The compilers buck uses, all from nix. |
76| 27 | [platforms/](platforms/) | One build graph for every Postgres major. |
77| 28 | [third-party/](third-party/) | What came from elsewhere, jevcrates included. |
78
79## If you know a web framework
80
81You do not need to know Postgres's insides or Rust to follow this guide.
82Most of the pieces have a counterpart in a web stack.
83
84| Here | What it is | In web terms |
85| --- | --- | --- |
86| A Postgres extension | A shared library (`postjevsql.so`) the database server loads, and the SQL that declares its functions. | A plugin loaded into the server process, where the server is the database. |
87| pgrx | The Rust framework for writing extensions. It writes the C glue and the install SQL. | The framework, as Nuxt is to a Vue app. |
88| A CustomScan | A node an extension may put into a query's plan. | A middleware inserted into the request pipeline; here the pipeline is a query. |
89| A GUC (the `jev.*` settings) | A setting Postgres knows by name, set in `postgresql.conf` or with `SET` for one session. | Environment variables that one connection can change for itself. |
90| SPI | How an extension runs SQL from inside the server. | A database call from inside a route handler. |
91| `jev_cache` | A table holding every answer, keyed by exactly what was sent. | A response cache that is also an audit log. |
92| buck2 | The build system: one graph of every target, built and tested together. | Bazel, or Turborepo. |
93| mise | Every tool at an exact version, and every task, from `mise.toml`. | `package.json` plus a Node version manager. |
94
95## The whole story in one picture
96
97Here is what happens between Enter and the rows coming back. Every arrow is
98a chapter somewhere in this guide.
99
100```mermaid
101sequenceDiagram
102  participant C as Client (psql)
103  participant P as Planner (with postjevsql's hooks)
104  participant S as JevScan (one plan node)
105  participant T as The table's own scan
106  participant K as jev_cache
107  participant J as Jev (api.typesafe.ai)
108  C->>P: SELECT … WHERE jev(t, 'question')
109  P-->>S: wrap the table's scan in a JevScan
110  Note over S: ExecutorStart: price the plan, refuse it over jev.max_cost
111  loop up to jev.concurrency rows at a time
112    S->>T: next row (every other WHERE clause already applied)
113    S->>K: an answer for these exact bytes?
114    alt kept
115      K-->>S: the answer, for nothing
116    else not kept
117      S->>J: one row, one HTTP/2 stream
118      J-->>S: probabilities
119      S->>K: store it, with the receipt
120    end
121  end
122  S-->>C: the rows, in the order the table gave them
123```
124
125In words:
126
1271. **The planner hooks wrap the scan** (chapter 7). Every call to a `jev*`
128   function over one table is claimed by one plan node, wherever it appears:
129   `WHERE`, the select list, `ORDER BY`, under an aggregate, inside a CTE.
1302. **The price comes first.** Before a single row is read, the plan's row
131   estimates are priced at three billed attempts per request, and a
132   statement over `jev.max_rows` or `jev.max_cost` is refused with nothing
133   spent (chapter 3).
1343. **Rows the SQL filters out are never judged.** The node sits above every
135   ordinary condition, so only rows that pass them reach Jev, and a `LIMIT`
136   stops the judging a window past the last row it returned.
1374. **Each row is judged alone**, in its own request, so no row can sway
138   another's answer (chapter 9 has the measurement), and many go at once, as
139   streams on the backend's one connection (chapter 6).
1405. **Every answer is kept** in `jev_cache`, under the hash of exactly what
141   was sent. Ask again and nothing is sent at all.
142
143> **Aside.** Why one request per row, when a request could carry twenty? It
144> was tried, and measured. With 40 rows in one request, rows in the last
145> slots had their probabilities moved by about 0.42 from what they got
146> alone, just for where they sat. A Postgres extension that answered differently depending on
147> a row's neighbours would be a strange sort of database. So the contract
148> forbids it, and the code cannot build such a request.
149
150## Where it runs
151
152The same extension ships two ways, and the code does not fork.
153
154```mermaid
155flowchart LR
156  subgraph indb["In-database"]
157    A1["your app"] --> D1[("your Postgres + postjevsql")]
158  end
159  subgraph side["Sidecar"]
160    A2["your app"] --> S2[("a small Postgres + postjevsql")]
161    S2 -->|"postgres_fdw: rows that pass the filters"| D2[("your managed database (RDS, Supabase, Neon, …)")]
162  end
163  D1 --> J["Jev"]
164  S2 --> J
165```
166
167- **In-database**: installed into the Postgres that holds the data. This is
168  the default whenever you control that server.
169- **Sidecar**: a local Postgres runs the extension and reaches the real
170  database through `postgres_fdw`. Nothing is installed on the target, so it
171  works with managed hosts, which do not allow custom compiled extensions.
172  Filters still run on the target, and only the `jev` judgements run
173  locally. Writes through the sidecar are not two-phase committed.
174  Chapter 10 is about this mode.
175
176## The fine print
177
178Everything here is true of the code today, and some of it will bite.
179
180- **`jev_prob` returns two-decimal values, and ties are common.** Add your
181  own tie-break column to `ORDER BY jev_prob(…)`, as the example at the top
182  does with `name`.
183- **Supported PostgreSQL majors: 17 and later** (17 and 18 today). The nix
184  package set is `postgresqlNNPackages.postjevsql` for each.
185- **Not compatible with pg_duckdb** loaded in the same server. pg_duckdb
186  calls Postgres from its own threads, and this extension's planner hooks
187  refuse to run off the backend thread (pgrx #2228).
188- **TypeSafe's terms bind how the results are used.** Cached answers may not
189  be used to train a model that imitates Jev (MCA §2.3(b)), and one API key
190  may not serve third parties through this extension (§2.3(a)).
191- **Row contents are sent to TypeSafe.** Do not point this at data you
192  cannot share with them. TypeSafe states that it does not train on customer
193  requests. Zero data retention is offered to enterprise customers only
194  (docs.typesafe.ai/models, "Data handling").
195- **A query shape it cannot batch yet is refused, never run row by row**: a
196  call over two relations, or one in `RETURNING`, `ON CONFLICT` or a `MERGE`
197  action. The error comes before anything is sent.
198
199> **Aside: where the ideas came from.** Three earlier projects asked Jev from
200> SQL, and this one takes the strongest part of each.
201> [realZachi/pg-jev](https://github.com/realZachi/pg-jev) (a plpython3u
202> extension) gave the whole-row surface, `jev(alias, …)`; its 20-rows-per-state
203> batching was *not* taken, for the reason in the aside above.
204> [kylemclaren/jevql](https://github.com/kylemclaren/jevql) (a Go query
205> rewriter outside the server) gave canonical row JSON, the column-list form,
206> the price shown before spending, the spend guards, and its list of refused
207> queries, read as a to-do list.
208> [giuliosmall/pg_typesafe](https://github.com/giuliosmall/pg_typesafe) (a C
209> extension) gave cancellable in-backend HTTP, typed composite results, the
210> privilege and secret model, and offline mock testing. On top of those
211> come batching owned by the query plan, a durable cache that doubles as an
212> audit trail, and Choice over more than 255 options.
213
214## Running it yourself
215
216The repository is served by the lmjtfy site, with
217[jevcrates](https://lmjtfy.fun/jevcrates.git) (the shared Jev
218client) as a submodule beside it. No GitHub account is needed:
219
220    git clone --recurse-submodules https://lmjtfy.fun/postjevsql.git
221    cd postjevsql
222
223<img src="https://mise.jdx.dev/logo.svg" alt="mise" height="22"> **Use [mise](https://mise.jdx.dev)**
224(`curl https://mise.run | sh` puts it in `~/.local/bin`: no root, no nix). It installs
225everything else at the versions in [mise.toml](mise.toml), and from then on you run only `mise` commands:
226
227    mise trust && mise install    # rustc, buck2, python, a Postgres server of every supported major, clang, hk
228    mise tasks                    # everything that can be run
229    mise run check                # versions, and the crates that do no I/O (a second, no database, no network, no key)
230    mise run test                 # the whole suite through buck2, on Postgres 17 and 18
231    mise run build                # the extension, built by buck2
232    mise run pgrx:install         # the extension into a Postgres you already run, with cargo-pgrx 0.19.2
233
234All the machine needs is git and curl (and the C library the system came with): the compiler and linker
235(clang, with its own sysroot), the servers and libclang are mise's, from conda-forge, installed per
236user. `mise run submodules` fetches jevcrates if the clone forgot `--recurse-submodules`. The first
237`mise install` is large (about 3 GB). Chapter 13 explains what `test` runs, and chapter 16 how the
238packages are built. The git hooks are `mise run hooks:install` (hk).
239
240> **With nix.** `nix develop` is the same thing with mise provided for you, and changes nothing about
241> the commands above. The flake's packages and the sidecar module are built with nix and need none of
242> this.
243
244To use it, install the package for your major, put the API key in the
245server's environment as `TYPESAFE_API_KEY` (or name a file in
246`jev.api_key_file`), and then:
247
248```sql
249CREATE EXTENSION postjevsql;
250SET jev.model = 'jev-1.13.0';   -- a pinned version; aliases are refused
251GRANT EXECUTE ON FUNCTION jev_prob(anyelement, text) TO app;  -- spending is granted, never public
252```
253
254## Files at the top
255
256| File | What |
257| --- | --- |
258| [CLAUDE.md](CLAUDE.md) | The design contract: every rule, where it came from, and the evidence for it. |
259| [mise.toml](mise.toml) | The toolchain with its versions, the environment and the tasks (`check`, `test`, `build`, ...). The one place a version lives. |
260| [hk.pkl](hk.pkl), [fnox.toml](fnox.toml) | The git hooks; the one secret the release gates need, declared with no value. |
261| [flake.nix](flake.nix) | The packages, the sidecar module and its VM checks (chapter 16), and a devshell that only wraps mise. |
262| [flake.lock](flake.lock) | The flake's pinned inputs. |
263| [.buckconfig](.buckconfig) | buck2's cells and platforms. `mise run configure` writes `.buckconfig.local` beside it with the tool paths; that file is not committed. |
264| [BUCK](BUCK) | Exports the generated root files to their drift tests. |
265| [Cargo.toml](Cargo.toml) | The Cargo workspace, generated from the buck targets (chapter 20). Do not edit. |
266| [Cargo.lock](Cargo.lock) | Cargo's lock for that workspace. buck's own versions are locked in `third-party/Cargo.lock`. |
267| [deny.toml](deny.toml) | `cargo deny check licenses`: the allowed licences; AGPL and GPL are denied. |
268| [THIRD-PARTY](THIRD-PARTY), [THIRD-PARTY-sidecar](THIRD-PARTY-sidecar) | The licence notices of every crate linked into the extension and into the sidecar CLI, generated (chapter 22). |
269| [LICENSE-MIT](LICENSE-MIT), [LICENSE-APACHE](LICENSE-APACHE) | The licence. |
270| [.gitmodules](.gitmodules) | jevcrates, by the relative URL `../jevcrates.git`, so a clone from the site finds it beside this one. |
271| [.gitignore](.gitignore) | buck's and Cargo's output, and `.buckconfig.local`. |
272
273Some folders are not chapters, on purpose:
274
275- [build/](build/) holds the shared Starlark: `VERSION`, `PG_MAJORS`, and the
276  `first_party_*` macros that give every Rust target its lint test. It is
277  buck's own plumbing, read in chapters 26 and 27 where it is used, and its
278  README says what each line is for.
279- [none/](none/) is an empty buck cell. The buck prelude refers to Meta's
280  internal cells (`fbcode`, `fbsource`, …), and `.buckconfig` points them all
281  here so those references resolve to nothing.
282- `.claude/` is an agent's memory, `buck-out/`, `target/` and `result` are
283  build output, and the trees under `third-party/` came from elsewhere
284  (chapter 28).
285
286## Licence
287
288Licensed under either of the MIT licence ([LICENSE-MIT](LICENSE-MIT)) or the
289Apache Licence 2.0 ([LICENSE-APACHE](LICENSE-APACHE)), at your option.
290Copyright The postjevsql Authors.
291
292Next: [Chapter 1, crates/](crates/) →