Contents

• pg_typesafe
  • Install
    • One line
    • Docker
    • PGXN
    • From source
    • Enable
  • API key
  • Demo (NYC 311)
  • Functions
  • Tests without the network
  • Releasing
  • License

pg_typesafe

Pre-alpha. A PostgreSQL extension that calls TypeSafe AI (System One / Jev) from SQL for categorical work: Choice, Noul, and Score.

Not affiliated with TypeSafe AI or the PostgreSQL Global Development Group.

Supports PostgreSQL 15, 16, 17 and 18 on Linux and macOS, libcurl 7.61+.

Install

One line

curl -fsSL https://raw.githubusercontent.com/giuliosmall/pg_typesafe/main/install.sh | sh

The installer finds every PostgreSQL 15+ server on the machine, installs a checksum-verified prebuilt binary (Linux x86_64/arm64, glibc) or builds from source (macOS, Alpine, anything else), and installs any missing build dependencies with the system package manager (apt, dnf/yum, zypper, apk, pacman, Homebrew). It uses sudo only where it has to.

Install, enable in a database, and hand the server your API key in one step, with no restart:

curl -fsSL https://raw.githubusercontent.com/giuliosmall/pg_typesafe/main/install.sh \
  | sudo TYPESAFE_API_KEY=tsk_... sh -s -- --db mydb

--db runs CREATE EXTENSION and a load test. With TYPESAFE_API_KEY set, the key is sent to the server over COPY ... FROM STDIN (never in statement text or logs), written to typesafe.key in the data directory, and typesafe.api_key_file is pointed at it.

Option
--db NAME	enable in NAME (connects as the postgres OS user when run as root, else via PGHOST/PGUSER)
--pg-config PATH	install for one PostgreSQL only
--version TAG	a specific release instead of the latest
--source	always build from source
--no-deps	do not install build dependencies

Docker

docker run -d -p 5432:5432 \
  -e POSTGRES_PASSWORD=pw \
  -e TYPESAFE_API_KEY=tsk_... \
  ghcr.io/giuliosmall/pg_typesafe:17

The official postgres image with the extension preinstalled and already created in POSTGRES_DB and template1 (so every new database has it). Tags: 15, 16, 17, 18, latest (= 17), and <major>-<version>; amd64 and arm64. To keep the key out of docker inspect, mount it as a secret and set TYPESAFE_API_KEY_FILE=/run/secrets/typesafe_key instead.

# compose.yaml
services:
  db:
    image: ghcr.io/giuliosmall/pg_typesafe:17
    environment:
      POSTGRES_PASSWORD: pw
      TYPESAFE_API_KEY_FILE: /run/secrets/typesafe_key
    secrets: [typesafe_key]
    ports: ["5432:5432"]
secrets:
  typesafe_key:
    file: ./typesafe.key

PGXN

pgxn install typesafe

From source

git clone https://github.com/giuliosmall/pg_typesafe.git
cd pg_typesafe
make
make install   # needs write access to pkglibdir (often sudo)

Needs the PostgreSQL server headers (postgresql-server-dev-<major> / postgresql<major>-devel) and libcurl headers.

Enable

CREATE EXTENSION typesafe;

EXECUTE is revoked from PUBLIC. The owner (usually a superuser) can run the functions. To let an app role call them:

GRANT EXECUTE ON FUNCTION typesafe_noul(text, text, text, text, text) TO app;
GRANT EXECUTE ON FUNCTION typesafe_detect_many(text[], text, text, text, text) TO app;

API key

The installer (--db with TYPESAFE_API_KEY) and the Docker image set this up for you. Manually: set TYPESAFE_API_KEY on the Postgres server process. Do not put it in SQL.

export TYPESAFE_API_KEY=tsk_...
pg_ctl restart

Alternatively point typesafe.api_key_file (superuser-only GUC) at a file whose first line is the key — this keeps the key out of SQL, logs, and postgresql.auto.conf:

ALTER SYSTEM SET typesafe.api_key_file = '/etc/postgresql/typesafe.key';
SELECT pg_reload_conf();

SELECT typesafe_noul(
    'Help! My payouts have been failing for 3 days.',
    'Does this convey urgency?'
);

SET typesafe.api_key works for a session (superuser only) but appears in query logs.

typesafe.endpoint must be https://; plain http:// is allowed only toward localhost (used by the test suite). Responses are capped at 8 MB. HTTP 429/529 are retried up to 3 times per request with exponential backoff, honoring Retry-After. Requests remain cancellable (Ctrl-C, statement_timeout) while in flight.

Demo (NYC 311)

From the repo root, with the key in the server environment:

psql -d postgres -v ON_ERROR_STOP=1 -f examples/311.sql

1,000 closed NYC 311 complaints, 38 unique resolution strings. typesafe_detect_many classifies those 38 in two TypeSafe requests at the default typesafe.batch_size = 32 (not 1,000 HTTP calls).

Measured on a laptop against live Jev:

Method	Time
typesafe_detect once per distinct text	23 s
typesafe_detect_many	0.86 s

Functions

Function	Primitive	Use
typesafe_noul(text, text)	Noul	yes/no probability (scalar)
typesafe_detect	Noul	same, plus model / tokens
typesafe_classify / typesafe_label	Choice	one category
typesafe_score	Score	ordered rubric
typesafe_ask	mixed	several questions, one request
typesafe_detect_many / typesafe_classify_many	batched	many texts, few HTTP round trips

Scalars issue one HTTP call per row. For a table, batch distinct values:

SELECT resolution, typesafe_noul(resolution, 'Could the condition not be found?')
FROM (SELECT DISTINCT resolution FROM complaints) s;

-- Faster for a set:
SELECT state AS resolution, noul
FROM typesafe_detect_many(
    ARRAY(SELECT DISTINCT resolution FROM complaints),
    'Could the condition not be found?'
);

typesafe.batch_size (default 32) is how many texts share one TypeSafe request. Extra chunks overlap (typesafe.http_concurrency, default 4).

In *_many results, input_tokens/output_tokens are per HTTP request and reported on each chunk’s first row only (NULL on the rest), so SUM(input_tokens) over the result is the true total.

Tests without the network

typesafe.mock_response short-circuits the HTTP call and returns the given body verbatim. It is superuser-only: a granted app role must not be able to forge classification answers.

SET typesafe.mock_response = $${
  "model": "jev-latest",
  "answers": {
    "flag": {"type": "noul", "noul": 0.92}
  },
  "usage": {"input_tokens": 1, "output_tokens": 1}
}$$;

SELECT typesafe_noul('anything', 'Is this urgent?');

Mock regression: sql/typesafe.sql / expected/typesafe.out.

Releasing

Bump default_version in typesafe.control and version in META.json, add the matching typesafe--<version>.sql (plus an upgrade script), then push a tag:

git tag v0.0.2 && git push origin v0.0.2

.github/workflows/release.yml builds and smoke-tests prebuilt binaries for every supported major on amd64 and arm64, publishes the GitHub release that install.sh downloads from, pushes the Docker images to ghcr.io, and uploads to PGXN when the PGXN_USERNAME / PGXN_PASSWORD secrets are set.

License

MIT. See LICENSE.

TypeSafe API · Choice