Postgres

Drex for Postgres is a set of SQL functions that call Drex, Nace's decision models, from Postgres. They answer typed questions about each row and return calibrated probabilities. See Questions for how to write them. There are three types:

• drex_choice picks one label from a set of options.
• drex_score rates the input on ordered levels.
• drex_noul gives the probability that a yes/no statement is true.

drex_decide asks several questions in one request, drex_decide_image adds images, and drex_decide_batch sends many rows at once.

InstallCopy Anchor Link

The functions and their install scripts are nace-ai/drex-postgres on GitHub, under the MIT license. Clone it first; the paths below are relative to it:

git clone https://github.com/nace-ai/drex-postgres.git
cd drex-postgres

There are two variants with the same functions, arguments and results. Pick the one your Postgres allows:

plpython3u needs a superuser, so no managed service we checked (Amazon RDS and Aurora, Google Cloud SQL, Supabase, Neon) allows it. Of those, only Supabase offers http.

PL/PythonCopy Anchor Link

Install PL/Python for your Postgres version and the package into the Python it runs, which must be Python 3.10 or later. Put the key in a root-owned file with mode 0600, such as /etc/postgresql/drex.env holding NACE_API_KEY=nace_sk_..., load it where the server starts, and restart it. Then run the script as a superuser:

sudo apt-get install postgresql-plpython3-17
sudo pip3 install --break-system-packages .   # pip older than 23: leave out --break-system-packages
sudo systemctl edit postgresql@17-main        # add: [Service] EnvironmentFile=/etc/postgresql/drex.env
sudo systemctl restart postgresql@17-main
psql -v ON_ERROR_STOP=1 -c 'SET search_path = public' -f sql/drex.sql

The key is never a function argument or a setting, since any user can read those. NACE_BASE_URL optionally names another endpoint. Create a key as shown in Authentication.

pgsql-httpCopy Anchor Link

Enable the extension, run the script as the role that will own the functions, and store the key without typing it into SQL:

CREATE EXTENSION IF NOT EXISTS http;   -- on Supabase: CREATE EXTENSION IF NOT EXISTS http WITH SCHEMA extensions;
SET search_path = public;
\i sql/drex_http.sql
\getenv key NACE_API_KEY
INSERT INTO drex_settings (api_key) VALUES (:'key');

\getenv needs psql 15 or later, and UPDATE drex_settings SET api_key = :'key'; replaces the key. The functions run as their owner to read the key, so whoever you grant them to spends it and no one else can read it. Nor can they loosen how pgsql-http checks Drex's certificate: a call from a session that changed it with http_set_curlopt fails before anything is sent.

Grant accessCopy Anchor Link

Both scripts revoke the functions from PUBLIC, and from any role that default privileges grant them to, such as Supabase's anon, authenticated and service_role. Grant them to the roles that may call Drex:

GRANT EXECUTE ON FUNCTION drex_choice(text, text, jsonb, text) TO analyst;

On Supabase, a function granted to anon or authenticated in a schema the Data API exposes can be called with your project's API keys.

NACE_BASE_URL (or drex_settings.base_url) must use https://. http:// is accepted only for localhost, 127.0.0.1 and [::1], since the key would otherwise cross the network in the clear.

UsageCopy Anchor Link

Ask one question per rowCopy Anchor Link

Each non-NULL row is one Drex request. A NULL input returns NULL and isn't sent or billed.

SELECT
  ticket_id,
  drex_choice(
    body,
    'Which team should handle this support ticket?',
    '[{"label": "billing", "description": "Payments, invoices, refunds and charges"},
      {"label": "technical", "description": "Bugs, crashes and errors in the product"},
      {"label": "sales", "description": null}]'
  ) AS team
FROM tickets;

The labels take three JSON forms: a list of labels, a list of {"label", "description"} objects, or an object of label to description. The result depends on the function:

probabilities always holds every option, a choice's sorted by label and a score's by index. Reading answers explains each field. A text input is sent as text and a jsonb input as structured state, so a whole row works too: drex_noul(to_jsonb(tickets), ...).

Ask several questions at onceCopy Anchor Link

drex_decide sends every question in one request per row, so the row is sent and billed once instead of once per question:

SELECT
  ticket_id,
  drex_decide(to_jsonb(body), '{
    "urgency": {"type": "score", "instructions": "How urgent is this ticket?", "criteria": ["can wait", "this week", "today"]},
    "refund": {"type": "noul", "instructions": "Is the customer asking for a refund?"}
  }') -> 'answers' AS answers
FROM tickets;

Use thresholdsCopy Anchor Link

Every answer carries its probabilities, so a query decides what is confident enough and leaves the rest for a person or another model:

SELECT ticket_id, team ->> 'choice' AS team
FROM ticket_teams
WHERE (team ->> 'confidence')::float >= 0.8;

Choose the threshold from a labelled sample.

Read imagesCopy Anchor Link

Drex Max reads images. drex_decide_image takes them as a bytea[], uses drex-max by default, and prompts refer to them as "Image 1", "Image 2" and so on. PNG, JPEG and WebP are accepted, and Drex never fetches a URL:

SELECT
  scan_id,
  drex_decide_image('""', ARRAY[content], '{
    "kind": {"type": "choice", "instructions": "What kind of document is Image 1?", "criteria": ["invoice", "receipt", "other"]}
  }') -> 'answers' -> 'kind' ->> 'choice' AS kind
FROM scans;

A row with more than 8 images, or whose request would exceed the 4 MiB body limit, returns NULL with a WARNING, so resize large images first. Drex Max's image limits list the rest.

ModelsCopy Anchor Link

The default model is drex-v2.0, which reads text and JSON. Pass model => 'drex-max' for Drex Max, which also reads images. drex-v2.0 costs $0.06 and Drex Max $0.03 per million input tokens; see Pricing.

Before it sends anything, a function checks the arguments and the model: the model must be one that GET /v1/models lists for your key, images need a model that reads them, and the questions must fit the model's limits. A call that fails a check returns an error and sends nothing.

Concurrency and rate limitsCopy Anchor Link

A Postgres backend runs one function call at a time, so drex_noul, drex_choice, drex_score and drex_decide send one request per row, one after another. For throughput, drex_decide_batch (PL/Python variant) sends a chunk of rows with up to concurrency requests at once (default 8) and returns one result per row, in order:

WITH chunk AS (
  SELECT array_agg(ticket_id ORDER BY ticket_id) AS ids, array_agg(to_jsonb(body) ORDER BY ticket_id) AS states
  FROM tickets WHERE ticket_id BETWEEN 1 AND 500
)
INSERT INTO ticket_answers (ticket_id, answer)
SELECT chunk.ids[r.n], r.answer
FROM chunk, unnest(drex_decide_batch(chunk.states,
    '{"refund": {"type": "noul", "instructions": "Is the customer asking for a refund?"}}',
    concurrency => 8)) WITH ORDINALITY AS r(answer, n);

concurrency is per call, so the requests in flight are the sessions running it times their concurrency. 8 fits the in-flight limit of a free account:

Every API key on an account shares its limits (see Scope). The pgsql-http variant sends one request at a time whatever concurrency says.

ErrorsCopy Anchor Link

• 401, 402 and a 422 about the questions or model fail the statement. A 422 message lists each issue's path and message; a 402 links to Billing.
• No response, 408, 429, 500, 502, 503, 504 and 529 are retried after retry-after-ms, at most 5 attempts within 2 minutes. A row whose retries run out returns NULL with a WARNING.
• A row Drex refuses on its own account, such as one over the token limit, returns NULL with a WARNING. The warning never quotes the row.
• Each request times out after 60 seconds, and a cancel or statement_timeout stops a waiting call.

Only a 200 is billed, so a retried or failed row costs nothing. See Errors and retries.

What gets sent, and billedCopy Anchor Link

Every row Postgres evaluates a function on is a request, and every request Drex answers is billed, whether or not the statement returns that row. A statement that runs again sends its rows again, and so does a plan that evaluates the function before a filter or LIMIT. Filter and limit the rows in a subquery, and keep the answers in a table:

CREATE TABLE ticket_teams AS
SELECT ticket_id, drex_choice(body, 'Which team should handle this support ticket?', '["billing", "technical", "sales"]') AS team
FROM (SELECT * FROM tickets ORDER BY ticket_id DESC LIMIT 100) recent;

CostCopy Anchor Link

Each row is billed for its input tokens at the model's price, and output tokens aren't billed. For 10,000,000 rows:

The same row counts a different number of tokens on each model, so read usage.input_tokens from drex_decide for a sample of rows on the model you plan to use before running a whole table.