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. The steps below install release v0.3.1; Releases lists each one with its changes. Clone the release first; the paths below are relative to it:

git clone --branch v0.3.1 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. With log_statement = 'all' (or 'mod') the server log records the INSERT, so turn it off for the session first if you can. 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: for a caller who can't read drex_settings, a session that set a curl option other than timeouts, keepalive, IP resolution, the user agent or (to an https:// endpoint) a proxy or DNS servers fails with 42501 before anything is sent; http_reset_curlopt() clears them.

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. Each typed function has a text and a jsonb overload, so grant both, or grant every function in the schema if it holds nothing else:

GRANT EXECUTE ON FUNCTION drex_choice(text, text, jsonb, text), drex_choice(jsonb, text, jsonb, text) TO analyst;
-- or: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public 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, set with UPDATE drex_settings SET 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.

UpgradeCopy Anchor Link

Check out the new release's tag, reinstall the package for the PL/Python variant, and run the same script again as when you installed it. It replaces the functions and keeps drex_settings and your grants; sessions opened afterwards use the new version.

git fetch --tags
git checkout vX.Y.Z   # the new release

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. A choice takes 2 to 255 unique labels. A score's levels are a plain list in scale order, with no descriptions, and a noul's criteria labels are exactly true and false. 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 NULL image or an empty array returns NULL and sends nothing. A row with more than 8 images, an image that isn't PNG, JPEG or WebP, or a request that 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.03 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 follow Drex's request limits: 1 to 512 questions with unique ids, and 2 to 255 unique labels or levels. A call that fails a check returns an error and sends nothing. A row over the model's token limit isn't checked in advance: Drex refuses it, and it returns NULL with a WARNING.

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

• These fail the statement, with a SQLSTATE to catch: a broken argument, an unknown model, images with a model that doesn't read them, or a 422 about the questions or model (22023); no key, a 401, or a base URL that isn't https:// (28000); a 402 (53000); a TLS failure, or Drex unable to list its models (38000). 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 (or retry-after, at most 30 seconds), 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, and any other status, such as 403 or 404, return 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 failed attempt or a row that returns NULL costs nothing. Every attempt sends the same Idempotency-Key, and a retry is billed twice only if a 200 was lost on its way back. 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.