Skip to main content

Install and use a package

The easiest way to add capabilities to ScramDB is to install a published package and call its functions. You do not write any code.

Install from the registry​

Install a package by name. ScramDB fetches it from the package registry, verifies it, catalogs it and registers its functions, all in one statement:

sql
SELECT scram.install('scramdb/embeddings_cosine_similarity');

Pin a specific version with @:

sql
SELECT scram.install('scramdb/embeddings_cosine_similarity@0.1.0');

The registry is zero-config (the default is the public afterburner registry). ScramDB fetches the package, recomputes its content digest, and verifies it against the registry manifest before installing. A mismatch fails loudly and installs nothing. scram.install upserts: installing the same content again is a no-op, never an "already exists" error. On a cluster, the verified package replicates to every node.

scram.install returns the package's hex content digest. You can also upload raw bytes directly with an explicit name using scram.register(name, bytes), which skips manifest parsing entirely; see Install a local or private package below.

Only a superuser installs, registers, uninstalls, pins or upgrades a package, or asks a registry for its versions: INSTALL MODULE, DROP MODULE, scram.install, scram.register, scram.uninstall, scram.pin, scram.upgrade and scram.versions all refuse any other role with 42501, permission denied: INSTALL MODULE/DROP MODULE requires superuser, before any bytes are read or any registry is dialled, and change nothing.

Call the function​

Installing a package also registers the functions it declares in its manifest (the [[metadata.sql.function]] entries, see Writing a package), each under its natural SQL signature. They are callable at once; there is no code to write and no second statement to run. Call one like any built-in function:

sql
SELECT embeddings_cosine_similarity('[1, 0, 0]', '[1, 0, 0]'); -- 1.0

Or across a table, ranking the closest matches:

sql
SELECT id, embeddings_cosine_similarity(query_vec, doc_vec) AS score
FROM documents
ORDER BY score DESC
LIMIT 10;

Because ScramDB is UTAP, this runs on your live data, including rows a transaction committed a moment ago.

Bind an installed package by hand​

CREATE OR REPLACE FUNCTION ... AS 'namespace/pkg' binds an installed package to a SQL function under a name of your own choosing. The package supplies the body, so there is no code to write. The argument names must match the ones the package reads:

sql
CREATE OR REPLACE FUNCTION cosine(a text, b text)
RETURNS double precision
LANGUAGE js
AS 'scramdb/embeddings_cosine_similarity';

A plain CREATE FUNCTION of a name the install already registered is refused with SQLSTATE 42723 (the function already exists). CREATE OR REPLACE FUNCTION works only for the function's own owner or a superuser; anyone else's replace attempt is refused, naming the ownership reason.

LANGUAGE js is the keyword to use here for a package built from JavaScript, TypeScript, Rust, Go, C, or C++: it names the package-reference binding form, and the package runs as its compiled script. A package whose source is Python or Ruby is bound with its own keyword, LANGUAGE python or LANGUAGE ruby, and runs the entry source its manifest names: the entry function is the SQL function's own name, so the entry file defines a top-level function of that name (see Writing a package). A function declared in another language than its package is refused, naming the language to use. The quote style does not matter: AS 'namespace/pkg' and AS $$namespace/pkg$$ bind the same package. A js package is one whole callable unit, so you cannot bind just one named export out of it (AS 'pkg', 'export' is refused).

Call without registering a function​

For a one-off call, or from an agent or script that wants to invoke a UDF without composing a full SELECT fn(...) query, scram.invoke and its batch and columnar variants call an already-registered function directly:

sql
-- one row at a time; STRICT functions short-circuit a NULL argument without running
SELECT scram.invoke('embeddings_cosine_similarity', '[1,0,0]', '[1,0,0]');

-- many rows in one call: rows_json is a JSON array of argument arrays, positional
SELECT scram.invoke_batch(
'embeddings_cosine_similarity',
'[["[1,0,0]","[1,0,0]"], ["[1,0,0]","[0,1,0]"]]'
);

-- run the function over every row a query produces, one column of results back
SELECT scram.invoke_columnar(
'embeddings_cosine_similarity',
'SELECT query_vec, doc_vec FROM documents'
);

All three return the result under a single column literally named result; the function you call must itself return exactly one output column, or the call is rejected.

VerbCallNotes
scram.invokeinvoke(function_name, arg1, arg2, ...)One row in, one row out.
scram.invoke_batchinvoke_batch(function_name, rows_json)rows_json is a JSON array of positional argument arrays; the whole batch runs as one call into the function. Capped at 100,000 rows and 16 MiB of JSON text per call.
scram.invoke_columnarinvoke_columnar(function_name, source_sql)source_sql is a single SELECT statement (no semicolons, no multiple statements); results are paginated internally. Capped at 10,000,000 total rows per call.

scram.invoke_batch's JSON arguments support the same types as CREATE FUNCTION (see Supported types below), except decimal/numeric, which has no JSON wire form yet and is rejected with its own message. A bytea argument in the JSON is a hex string, with or without a leading \x.

See what is installed​

scram_modules is a live catalog view of every installed module:

sql
SELECT * FROM scram_modules;
ColumnMeaning
nameThe installed module's qualified name
versionThe registry version, NULL for a module installed from raw bytes with no registry provenance
digestContent digest
size_bytesArtifact size
installed_byThe installing role
installed_atInstall timestamp
pinThe active version pin, NULL if unpinned
invocationsCalls made through scram.invoke / invoke_batch / invoke_columnar since install

invocations only counts calls through the scram.invoke* verbs above; ordinary SELECT fn(...) calls through a CREATE FUNCTION-registered name are not counted.

A CREATE FUNCTION with an inline body never creates a module, so scram_modules lists only what was installed. A function and a module may share a name: creating or dropping the function leaves the module as it was installed, and the reverse.

scram_functions is the matching view of the functions in the four guest languages: name, language, module (the installed package it runs, NULL for an inline body), caps (always sealed), strict, owner and oid.

Daemon packages​

Most packages are functions you call. Some are servers that need to be running, and those declare it in their own manifest:

toml
[metadata.daemon]
autostart = true

A package carrying that declaration and granting listen in its manifold is started in-process after the preinstall sweep, on the engine's own reactor rather than a second runtime, and supervised from then on. Nothing to launch, no separate process to babysit, no init script. The shipped example is scramdb/semantics, the Semantic AI MCP server on port 9191.

scram_daemons is the live view, computed from the in-memory registry on every scan rather than read from a stored snapshot. A superuser or a member of pg_read_all_stats reads it; it is empty for any other role, because a daemon belongs to the node and last_error can carry a path or the text of a failed start:

sql
SELECT package, state, ports, shards_alive, restarts, last_error, started_at, digest
FROM scram_daemons;
ColumnMeaning
packageThe installed package running as a daemon
statestarting, running, failed, or disabled
portsComma-joined granted listen ports; an empty string means the manifold granted Any
shards_aliveLive shards right now. Only HTTP daemons expand past one
restartsSupervised restarts since boot
last_errorThe reason it last failed, NULL while healthy
started_atFirst successful start, NULL until there has been one
digestContent digest of the package actually running

Two of those columns are deliberately honest about what they do not know. last_error is NULL while healthy rather than an empty string dressed up as an answer, and started_at stays NULL until a start has actually succeeded instead of reporting the epoch.

A package that fails to start is a loud ERROR and a failed row. Boot is never wedged by it, and a daemon that died is never silently absent from the view. A pool whose shards all die is restarted with bounded exponential backoff, base 2 seconds and doubling, up to 5 restarts; past that it stays failed with the reason attached, so a crash loop terminates in something you can query rather than churning forever.

A daemon starts when its package is installed, not only at boot. scram.install of a package that declares autostart and grants listen starts its daemon at once, and scram.uninstall stops it, closes its HTTP port and removes its row from scram_daemons. Installing the package that is already running, at the same digest, changes nothing; installing a changed package stops the running daemon, which frees its old port, and starts the new one. Two installs of one package at the same time leave one daemon, the one of the digest the catalog holds. A start that fails leaves a failed row that names the reason. scram.register, scram.pin and scram.upgrade of a daemon package, INSTALL MODULE and DROP MODULE, and a node that only applies a replicated statement do not start or stop a daemon, and neither does a boot with an empty preinstall_dir.

Tuning and the master switch live in [udf.daemon].

Manage versions​

List what a registry offers, move to a newer version, or pin to an exact one:

sql
SELECT scram.versions('scramdb/embeddings_cosine_similarity'); -- available versions
SELECT scram.upgrade('scramdb/embeddings_cosine_similarity'); -- move to the newest allowed version
SELECT scram.pin('scramdb/embeddings_cosine_similarity', '@0.1.0'); -- hold at a version

scram.versions returns one row per version the registry advertises: version, digest, yanked, size_bytes, published_at (nullable), and installed (whether that exact version is currently installed locally). scram.pin accepts either '@<version>' or 'sha256:<digest>' and makes no network call, it just re-affirms the module's current bytes under that pin. scram.upgrade resolves the latest version allowed by any pin (or by an optional range argument, scram.upgrade(name, '^1')) and is a no-op if you are already there.

Install a local or private package​

For a package that is not in a registry, your own build, an air-gapped deployment, or an agent uploading bytes over the wire, hand the .afb bytes to scram.install directly. The package is self-describing, so ScramDB derives its name and digest from the bytes:

sql
SELECT scram.install('\x<the .afb file bytes as hex>'::bytea);

To install under a name you choose yourself, skipping manifest parsing entirely, use scram.register:

sql
SELECT scram.register('my-namespace/my-package', '\x<the .afb file bytes as hex>'::bytea);

In a real application you send the bytes through your client's parameter binding rather than typing them out. In offline mode (air-gapped deployments), the registry name form fails by design and this direct upload is the intended route, since it never dials out.

To remove an installed package, scram.uninstall('namespace/pkg') drops it; uninstalling a name that is not installed is an error, not a silent no-op. The one package the server itself runs, the self-driving package scramdb/ejder, is not removed by any role: scram.uninstall and DROP MODULE answer 42501 (permission denied: the self-driving package "scramdb/ejder" is part of the engine and cannot be removed), and an install of a package of another kind over its name answers 42501 too (permission denied: the self-driving package "scramdb/ejder" cannot be replaced by a package of another kind), while installing a newer version of it is an upgrade and stays.

Configure the registry​

Registry behavior lives under [udf.registry] in the server configuration:

KeyDefaultMeaning
urlhttps://registry.afterburner.shBase registry URL for the name-form verbs.
token_fileunset (anonymous)Path to a file holding a bearer token, read fresh on every registry call. Must be readable only by the server's own user (mode 0600 or tighter); a group- or world-readable token file is refused at config load.
allow["*"]Glob allow-list over "namespace/name", checked before any network call.
deny[]Glob deny-list, checked before allow. A match is refused loudly, naming the pattern, even if allow would have permitted it.
offlinefalsetrue refuses every name-form verb (scram.install('ns/pkg'), scram.upgrade, scram.versions) instead of dialing out. The bytes-upload form (scram.install(bytea) / scram.register) still works, since it never makes a network call; it is the supported path for air-gapped deployments.
auto_update"off""off": no background activity. "check": a background task resolves available upgrades and logs them, never installs. "apply": the same background task installs upgrades on its own cadence, honoring any pin.
update_interval"24h"Cadence for the auto_update background task; inert while auto_update is "off".

The parent [udf] table also carries the execution limits mentioned earlier: fuel_per_batch, memory_bytes, timeout_ms, and output_bytes bound a single invocation (fuel_per_batch and memory_bytes apply to JavaScript functions and timeout_ms counts processor time; see Writing a package), and enabled (default true) turns the whole afterburner UDF substrate off if set to false. preinstall_dir (default /opt/scramdb/dist) is where the server looks for .afb files to install automatically on startup; see Preinstalled packages below. Full field-by-field defaults live in Configuration.

Preinstalled packages​

ScramDB's starter packages ship as .afb files in [udf].preinstall_dir (the image's /opt/scramdb/dist). The server installs every file there at startup, in the background, after it is already accepting connections, so a slow install never delays the first query. This is exactly the same scram.install path you would run by hand, so it is idempotent: restarting an already-provisioned server re-installs nothing new. A missing directory is skipped, and a file that fails to install is logged as an error and never stops the server from starting.

Preinstalling does what a manual scram.install does, so each starter function is callable under its natural signature as soon as the background install has reached its package; there is nothing to bind. Check what is installed with SELECT * FROM scram_modules.

The image carries 29 packages. 26 are functions, in 16 families; the other three are scramdb/semantics and scramdb/semantic-compiler (the Semantic AI server) and scramdb/ejder (the self-driving agent). Every function package is JavaScript source run as LANGUAGE js, except rust_sha256, which is compiled ahead of time from Rust:

FamilyFunctions (each is scramdb/<function>)
anomalyanomaly_mad_score, anomaly_zscore
anonymizeanonymize_hash_sha256, anonymize_mask
bm25bm25_term_score
dbscandbscan_neighbor_count
embeddingsembeddings_cosine_similarity, embeddings_dot
ftsfts_term_frequency
geogeo_geohash_encode, geo_haversine_distance
h3h3_cell_center, h3_cell_id
jsonschemajsonschema_validate
kmeanskmeans_assign
quantilequantile_p2_add, quantile_p2_estimate
regressionregression_ols_add, regression_ols_result
rustrust_sha256
sketchsketch_hll_add, sketch_hll_estimate
thetatheta_add, theta_estimate
topktopk_add, topk_result

The h3 functions are a hexagonal grid index of their own. Their cell ids are not the ids of the H3 index format, so they do not match ids made by other H3 tools and cannot be joined with data indexed by them.

Supported types​

CREATE FUNCTION arguments and return types, CREATE PROCEDURE parameters, and scram.invoke* arguments, share the same type list:

boolean, smallint/int/bigint (and their unsigned variants), real, double precision, text, date, timestamp, bytea, jsonb.

A real or double precision result that is NaN is PostgreSQL's one NaN whatever bits the function wrote: NaN = NaN is true, it sorts above every number, and every NaN groups, joins, and hashes as one value.

Not supported, for any afterburner language, as either an argument or a return type: decimal/numeric, uuid, interval, any array type, and the vector and bit-string types (vector, halfvec, sparsevec, bit, varbit). ScramDB does have native vector columns (see Vector Search); a function just cannot take one as an argument. The packages that work with vectors, such as embeddings_cosine_similarity above, take a vector as a JSON array inside a text argument.

A UDF is scalar-only: RETURNS SETOF ... is rejected, and so are TEMPORARY functions and IF NOT EXISTS. A UDF runs as the calling role unless it is SECURITY DEFINER (see Run as the owner, with settings of its own). It is created in the first schema of the search_path that exists, or in the schema its name gives (CREATE FUNCTION myapp.f(...)). These gaps are also listed on Not yet supported.

A name holds one function of these four languages in its schema, so a second CREATE FUNCTION of the name is refused with SQLSTATE 42723 unless it is CREATE OR REPLACE. LANGUAGE sql and plpgsql routines overload by argument types, but a name that a sql or plpgsql routine holds cannot be given to a js, ts, python or ruby function in the same schema.

Output parameters and defaults​

A function in any of the four guest languages (js, ts, python, ruby) takes OUT and INOUT parameters and argument DEFAULT expressions the way a PostgreSQL function does. A call can leave out the trailing arguments that have a default and can name them (f(b => 2)), and scram.invoke fills an omitted default too.

With one output the guest answers that value as the output's type. With several outputs the function answers a record, and the guest answers one JSON text per row: an object read by the outputs' names, or an array read by position, or JSON null for a NULL record. An answer without a value for an output fails with SQLSTATE 38000 and names the function and the output.

sql
CREATE FUNCTION next_and_label(a int DEFAULT 1, OUT s int, OUT p text)
LANGUAGE js AS $$module.exports = (batch) => {
const a = batch.columns.a, r = [];
for (let i = 0; i < batch.row_count; i++) r.push(JSON.stringify({ s: a[i] + 1, p: 'v' + a[i] }));
return { row_count: batch.row_count, columns: { r } };
};$$;

SELECT * FROM next_and_label(3);

Create, change and rename a function​

A function or a procedure in js, ts, python or ruby is a routine like any other: the statements and the rights that make, change and drop a plpgsql routine make, change and drop it, and it comes back after a restart.

Who may create one​

CREATE FUNCTION and CREATE PROCEDURE judge the role before they read the body, in PostgreSQL's order: CREATE on the schema (42501, permission denied for schema name), USAGE on the language (42501, permission denied for language python) and, for CREATE OR REPLACE, ownership of the routine it replaces (42501, must be owner of function name, or of procedure name). Only then does the statement compile the source or look for the package it names. A role that lacks a right is told so whatever the body holds, never that a package is missing or that a script does not compile; a role that holds every right and names a package that is not installed is told that the package is not installed.

Run as the owner, with settings of its own​

A function takes SECURITY DEFINER and a SET clause as a plpgsql function does, and ALTER FUNCTION changes them. A call enters the clauses once for each batch of rows and leaves them when the guest has answered, normally or with an error. scram.invoke, scram.invoke_batch and scram.invoke_columnar enter them too. A function with neither clause enters nothing.

The guest runs sealed whoever calls it and whoever owns it, so the clauses decide what the call is judged by and nothing the guest can do. A setting that only a superuser may change is judged for the role the call runs as, the function's owner for SECURITY DEFINER and the caller otherwise, and a role that may not change it fails the call with 42501 (permission denied to set parameter "log_statement"). The settings are as they were after every call, a refused one included. pg_proc.prosecdef and pg_proc.proconfig show the clauses and pg_get_functiondef prints them.

Change, rename and move one​

ALTER FUNCTION and ALTER PROCEDURE take RENAME TO, SET SCHEMA, OWNER TO and the attributes of a routine (IMMUTABLE, STABLE, VOLATILE, STRICT, CALLED ON NULL INPUT, LEAKPROOF, PARALLEL, COST, SECURITY DEFINER, SECURITY INVOKER, SET and RESET) for these languages as they do for a routine in plpgsql. A view that calls the function keeps working under its new name, a call runs the source it ran before, and the EXECUTE privilege stays with the function, so a REVOKE made before a rename still holds after it. OWNER TO passes the privileges the owner held on the function to the new owner.

The refusals are PostgreSQL's: a name another function holds in the schema is 42723, a schema that is not there 3F000, a signature no function has 42883, ALTER PROCEDURE of a function 42809 (is not a procedure), a role that does not own the function 42501 (must be owner of function name), ROWS on a function that returns no set 22023 and COST 0 22023. Two alters of one function that run at once both hold, and an alter that meets a function a DROP removed first answers 42883.

sql
CREATE FUNCTION next_value(a bigint) RETURNS bigint LANGUAGE python AS $$
def next_value(batch):
return {'row_count': batch['row_count'], 'columns': {'r': [v + 1 for v in batch['columns']['a']]}}
$$;

ALTER FUNCTION next_value(bigint) RENAME TO following_value;

ALTER FUNCTION following_value(bigint) SECURITY DEFINER SET search_path = public;

SELECT following_value(1);

The call answers 2 under the new name.

The entry function of a python or ruby routine​

A python or ruby guest is entered by a top-level def or method of the source. When the routine is created that is its own name, and the engine records it with the routine, so a rename, a move to another schema and a restart leave it running by the name its source defines. CREATE OR REPLACE records it again: the name the routine is replaced under.

A second string after the body names the entry explicitly, as PostgreSQL's AS 'body', 'symbol' does for a routine whose body and entry are two things. The symbol must be an identifier the guest can define (0A000 otherwise, naming it). A js or ts routine is a whole script with no entry to name, so it takes one item (42P13, only one AS item needed for language "js").

sql
CREATE FUNCTION named_apart(a bigint) RETURNS bigint LANGUAGE python AS $$
def next_value(batch):
return {'row_count': batch['row_count'], 'columns': {'r': [v + 1 for v in batch['columns']['a']]}}
$$, 'next_value';

pg_get_functiondef prints the second string exactly when the entry differs from the routine's name, so the text of a renamed routine, run again after the routine is dropped, makes one that runs and prints the same text, and a routine never renamed prints as it was written.

Procedures​

CREATE PROCEDURE takes the same four languages as CREATE FUNCTION, js, ts, python and ruby, with inline source or the name of an installed package (AS 'namespace/pkg'). The guest is the one a function runs and answers the way it does (see Output parameters and defaults); CALL runs it in the caller's transaction under the same fuel, memory, timeout, queue and deadline as a function call, and a package is found by its name at every call. A parameter of a type the guests cannot carry is refused where the procedure is created, as for a function (0A000, procedure "name" argument: type "uuid" is not supported).

A role needs USAGE on the language and CREATE on the schema, as for a function: without the first the statement is 42501 permission denied for language js, without the second 42501 permission denied for schema name.

sql
CREATE PROCEDURE proc_next(INOUT a int) LANGUAGE js AS $$module.exports = (batch) => {
const a = batch.columns.a, r = new Int32Array(batch.row_count);
for (let i = 0; i < batch.row_count; i++) r[i] = a[i] + 1;
return { row_count: batch.row_count, columns: { r } };
};$$;

CALL proc_next(5);

CALL answers one row of the procedure's outputs, 6 for the procedure above. With one OUT or INOUT parameter the row holds that value; with several the guest answers one JSON text, an object read by the outputs' names or an array read by position, and the row holds the fields; an output the answer has no value for fails with 38000 and names the output. A procedure with no output, a guest that answers no rows and an answer of JSON null give no row, and the prepared CALL of a procedure with outputs describes their columns. A guest that fails answers XX000 with its own words.

A procedure of these languages cannot end the transaction it is called in, because its guest has no COMMIT or ROLLBACK: CALL leaves the caller's transaction open, so a ROLLBACK after it takes back what the transaction wrote before it, and a COMMIT after it commits that. A PL/pgSQL body or DO block that is allowed to end its transaction (see Transaction Control Inside a Procedure or a DO Block) may call one and go on.

The procedure is a routine like any other: CREATE OR REPLACE PROCEDURE replaces it (42723 without OR REPLACE), ALTER PROCEDURE ... RENAME TO, SET SCHEMA and OWNER TO and DROP PROCEDURE work, pg_proc lists it with prokind p and its language, EXECUTE decides who may call it, and it comes back after a restart. A procedure takes SECURITY DEFINER and a SET clause as a function of these languages does: the owner's role and the settings are entered around the call, and the guest still runs sealed, whoever calls it and whoever owns it, so they give it nothing a function lacks.

A python or ruby procedure renamed with ALTER PROCEDURE ... RENAME TO, moved to another schema or given away keeps running by the entry function it was made with, as a function does (see The entry function of a python or ruby routine), and the second string of AS 'body', 'entry' names that function for a procedure too.

Durability​

Installed modules (what INSTALL MODULE and scram.install registered) and the functions CREATE FUNCTION registered are saved to the catalog before the statement returns, and come back after a restart, a crash included. A JS or TS function compiles again the first time it is called after the restart. If a change cannot be saved (a full or failing disk), the statement fails with 58030 and changes nothing. [udf].preinstall_dir still installs the starter packages at every start, each over its saved copy.

Write and publish your own package​

Packages are polyglot. You can author one in JavaScript, TypeScript, Rust, Go, C, or C++, and afterburner compiles it to a single sealed .afb. Build it with the burn toolchain and publish it so others can install it by name:

sh
burn package # build the content-addressed .afb
burn publish # publish it to a registry

Once published, anyone installs it with scram.install('your-namespace/your-package'), exactly as above. To write the function body itself, see Writing a package.