Skip to Content

Server Functions

Server routines are transactional handlers declared with CREATE FUNCTION or CREATE PROCEDURE. Functions return values through SELECT; procedures use CALL and OUT / INOUT parameters. They run in a sandboxed V8 isolate, share the same types and indexes as tables, and can read or write data on the request transaction.

User-defined functions can appear in SQL expressions. For built-in functions such as LOWER() and NOW(), see Built-in Functions.

You write one procedure contract. How it runs depends on the invocation origin:

What you wantHow it runsctx.httpctx.source.kind
Call it from SQL or PGWireCALL schema.name(...)null"call"
Call it over HTTPPOST /v1/functions/{schema}/{name}present"call"
Wake it when a topic has a new messageCREATE TRIGGER ... ON TOPIC ... EXECUTE PROCEDUREnull"topic"
Run it on a cron or intervalCREATE SCHEDULE ... EXECUTE PROCEDUREnull"schedule"

The same procedure can be invoked from SQL and from HTTP. Topic handlers usually take the topic PAYLOAD. Table AFTER ROW triggers are not supported — route writes through a topic instead.

Typed arguments and returns use CREATE TYPE (composites, enums, table row types, topic payloads). Do not fall back to JSON when the shape is known.

Shared host context

Every origin gets the same ctx shape. Inline LANGUAGE JAVASCRIPT bodies and project-deployed TypeScript share it.

HostPurpose
ctx.db.query / ctx.db.executeNested SQL on the same request transaction
ctx.ormProject TypeScript only: typed Drizzle handle
ctx.functions.call(name, args)Nested procedure call (same transaction, same origin)
ctx.topics.publish(topic, payload)Stage a topic publish; commit flushes it, rollback drops it
ctx.log.info / debug / warn / errorStructured logs in system.procedure_logs
ctx.actor / ctx.principalCaller vs effective identity (SECURITY INVOKER vs DEFINER)
ctx.sourceHost-built origin metadata. Clients cannot send this.
ctx.httpHTTP-root only. null for SQL CALL, topic triggers, and schedules.
ctx.sleep(ms)Pause, capped at 60s and the remaining deadline

Clients must not send context, ctx, source, actor, or tx. The host builds those from the authenticated session (or the trigger / schedule principal).

CREATE PROCEDURE, DROP PROCEDURE, GRANT EXECUTE, and REVOKE EXECUTE require DBA or System. CALL requires EXECUTE on that routine (owner, DBA, and System always may). New procedures are not PUBLIC.

1. SQL procedures

Use SQL when the caller is already in a SQL session: CLI, Admin UI SQL Studio, PGWire, or POST /v1/api/sql with a CALL statement.

SQL
CREATE OR REPLACE FUNCTION app.echo(msg TEXT)RETURNS TEXTLANGUAGE JAVASCRIPTAS $$  return input.msg;$$; GRANT EXECUTE ON FUNCTION app.echo TO user; SELECT app.echo('hello');

The function returns its declared result. Procedures return their declared output arguments. Calls support parameter binding; use $1, $2, … placeholders for application input. Nested ctx.db work and ctx.topics.publish share the request transaction:

SQL
BEGIN;CALL app.place_order(99);ROLLBACK;  -- drops nested inserts and staged publishes

Rules that matter at the SQL edge:

  • New procedures have no PUBLIC execute privilege. Grant EXECUTE deliberately.
  • Owner, DBA, and System can always CALL.
  • SECURITY INVOKER (default) runs as the caller for RLS and CURRENT_USER.
  • SECURITY DEFINER runs as the procedure owner for that frame.
  • LANGUAGE JAVASCRIPT is executable immediately. LANGUAGE TYPESCRIPT is stored until a project deploy supplies compiled JavaScript.
  • Project-backed procedures omit LANGUAGE / AS. kalam schema gen scaffolds functions/src/... and kalam deploy (or kalam dev) activates the module.
  • input is always a named object matching the SQL parameters ({ x } for inc(x INT)).
  • CALL rejects arity mismatches, null for NOT NULL, invalid enum labels, and missing composite NOT NULL fields (INVALID_ARGUMENTS).

PGWire uses the same CALL path as SQL HTTP. ctx.http is still null.

SQL and PLpgSQL routines

SQL
CREATE FUNCTION app.add_one(value INT)RETURNS INT LANGUAGE SQL AS $$ SELECT value + 1 $$;SELECT app.add_one(41); CREATE PROCEDURE app.bump(INOUT value INT, OUT doubled INT)LANGUAGE PLPGSQL AS $$BEGIN  doubled := value * 2;  value := value + 1;END$$;CALL app.bump(7);

Routines support IN, OUT, INOUT, and VARIADIC arguments, defaults, overloads, and PostgreSQL CREATE, ALTER, DROP, and COMMENT operations. Use the argument types when identifying an overloaded routine, for example DROP FUNCTION app.add_one(INT).

CREATE PROCEDURE ... RETURNS is rejected. Use a function for a return value. SQL, PLpgSQL and JavaScript are executable routine languages. Inline TypeScript requires project compilation and deployment.

2. HTTP procedures

Every executable procedure is also available over REST. This is the same runtime as CALL, not a second controller contract.

HTTP
POST /v1/functions/{schema}/{name}Authorization: Bearer <token>Content-Type: application/json

The body is a named JSON object, a positional JSON array, or empty for a procedure with no arguments. Success is the SQL return value itself — not { "status": "success", "result": ... }.

On this origin only, ctx.http is present:

javascript
ctx.http.response.status(201);ctx.http.response.header('Location', '/orders/42');ctx.http.response.contentType('application/json'); return {  method: ctx.http.request.method,  path: ctx.http.request.path,  version: ctx.http.request.headers.get('x-client-version'),  lang: ctx.http.request.query.get('lang')};

HTTP-root rules:

  • Readable request metadata: method, path, selected headers, query parameters.
  • Authorization, Proxy-Authorization, and Cookie are not readable.
  • Root procedures may set status, safe response headers, and content type.
  • Nested procedures may read the root request. They cannot mutate the root response.
  • Connection, Transfer-Encoding, Content-Length, and Host response headers are rejected.
  • Unauthenticated access requires GRANT EXECUTE ... TO anonymous. PUBLIC does not include anonymous.

When the same procedure is invoked with SQL CALL, ctx.http is null. Branch on that if one handler serves both edges:

javascript
if (ctx.http) {  ctx.http.response.status(201);}return input;

See HTTP API Reference for auth on /v1/functions/.... The same catalog is published as OpenAPI 3.1 at GET /v1/api/openapi.json.

3. Topic-trigger procedures (in-process consumers)

This is the in-database equivalent of a topic consumer. A table write (or an explicit ctx.topics.publish) appends a durable topic message. An enabled trigger wakes the procedure for that message, runs it as the trigger principal, and ACKs after a successful commit.

SQL
CREATE TABLE chat.messages (  id BIGSERIAL PRIMARY KEY,  author TEXT NOT NULL,  content TEXT NOT NULL) WITH (TYPE = 'SHARED'); CREATE TOPIC chat.ai_inbox PARTITIONS 1;ALTER TOPIC chat.ai_inbox  ADD SOURCE chat.messages  ON INSERT  WITH (payload = 'full'); CREATE PROCEDURE chat.on_user_message(payload chat.ai_inbox NOT NULL)LANGUAGE JAVASCRIPTSECURITY DEFINERAS $$  var row = input.payload;  ctx.log.info('topic wake', {    table: row._table,    kind: ctx.source.kind,    topic: ctx.source.topicName,    offset: ctx.source.offset  });  return row;$$; CREATE TRIGGER chat.process_user_message  ON TOPIC chat.ai_inbox  EXECUTE PROCEDURE chat.on_user_message(PAYLOAD)  WITH (    principal = 'system',    start = 'latest',    retries = 5,    retry_backoff = '1s',    concurrency = 1  );

What happens on INSERT INTO chat.messages ...:

  1. The write path publishes one topic message (payload = 'full' includes the row plus _table).
  2. The trigger dispatcher consumes the next offset for group trigger:{trigger_id}.
  3. It invokes chat.on_user_message as a topic-origin root. ctx.source.kind is "topic".
  4. On success, the attempt is recorded as succeeded in system.trigger_attempts and the offset is ACKed.
  5. On failure, the dispatcher retries with backoff, then writes status dlq.
OptionDefaultMeaning
principalcreating sessionIdentity used for RLS / CURRENT_USER inside the handler
startlatestlatest or earliest, captured when the trigger is created
retries5Attempts before DLQ
retry_backoff1sDelay between retries (250ms, 1s)
concurrency1In-flight deliveries for this trigger
SQL
ALTER TRIGGER chat.process_user_message DISABLE;ALTER TRIGGER chat.process_user_message ENABLE;DROP TRIGGER IF EXISTS chat.process_user_message;

Disabling or dropping a trigger keeps committed offsets. Nested ctx.functions.call from a topic root keeps ctx.source.kind = "topic" and sets ctx.parent to the trigger procedure.

Declare the handler argument as the topic payload type (payload chat.ai_inbox), not as untyped JSON. Full payloads include _table (namespace:table) so the handler can tell which source table produced the event.

Use a procedure trigger when the handler should share the database transaction, run next to the data, and ACK automatically after commit. Use an external consumer when the worker lives in another process.

4. Scheduled procedures

KalamDB extends its PostgreSQL-shaped dialect with CREATE SCHEDULE. Do not use MySQL CREATE EVENT.

SQL
CREATE SCHEDULE reports.daily_summary  CRON '0 9 * * *' TIME ZONE 'UTC'  EXECUTE PROCEDURE reports.generate_daily_summary()  WITH (principal = 'system', overlap = 'skip', misfire = 'skip'); CREATE SCHEDULE reports.frequent_summary  INTERVAL '30 seconds'  EXECUTE PROCEDURE reports.generate_daily_summary(); ALTER SCHEDULE reports.daily_summary DISABLE;ALTER SCHEDULE reports.daily_summary ENABLE;DROP SCHEDULE IF EXISTS reports.daily_summary;

DBA/System manage schedules. Targets must be existing zero-parameter procedures. Cron uses five fields and an IANA time zone (UTC by default). Intervals are at least one second. Overlaps and missed firings are skipped; there is no catch-up queue.

Scheduled calls use the normal transactional runtime, with ctx.http = null and ctx.source.kind = "schedule". Monitor system.schedules and system.procedure_logs filtered by origin = 'schedule'.

Project TypeScript (schema-first)

For application modules, declare a bodyless function or procedure in schema.sql and implement it in functions/src:

SQL
CREATE TYPE chat.message_target AS ENUM ('room', 'direct'); CREATE FUNCTION chat.send_message(  target chat.message_target NOT NULL,  target_id TEXT NOT NULL,  content TEXT NOT NULL)RETURNS chat.ai_inboxSECURITY INVOKER; GRANT EXECUTE ON FUNCTION chat.send_message TO user;

Then:

BASH
kalam schema gen    # writes procedure.chat.sendMessage + functions/src/chat/send_message.ts# edit the scaffoldkalam deploy        # or kalam dev — builds and activates the module
TS
import { procedure } from "../generated/contracts"; export const sendMessage = procedure.chat.sendMessage(async (ctx, input) => {  ctx.log.info("send_message", { target: input.target });  // ctx.orm / ctx.db.query share the request transaction  return result;});

Existing implementation files are preserved on regeneration. .unimplemented() typechecks and builds, but those handlers are omitted from the compiled registry until you replace them — CALL then reports the procedure as not implemented.

kalam dev builds functions/ after the schema pipeline and activates the project module. Build failures print as errors and do not stop the app process. CLI details: CLI: Server Functions.

Catalog and logs

SQL
SELECT * FROM system.procedures;SELECT * FROM system.triggers;SELECT * FROM system.schedules ORDER BY schedule_id;SELECT status, error FROM system.trigger_attemptsWHERE event_id LIKE '%chat.ai_inbox%'ORDER BY updated_at DESC;SELECT * FROM system.procedure_logsWHERE procedure_id = 'chat.on_user_message'ORDER BY timestamp DESC;

system.procedure_logs records outcomes (ok, error, log) without request bodies, arguments, results, or tokens.

Authoring options

StyleWhen to use
Inline LANGUAGE JAVASCRIPT AS $$ ... $$Small handlers, tests, one-file examples
Bodyless CREATE FUNCTION / CREATE PROCEDURE + functions/src TypeScriptApp modules, shared helpers, kalam schema gen / kalam deploy
Inline LANGUAGE TYPESCRIPTStored for introspection; not executed until a project build compiles it

Typed SDK invocation

Generated TypeScript and Dart clients call invokeRoutine through the HTTP routine endpoint. See typed routine calls and JSON / KDOM encoding. JavaScript routines can also use ctx.db.queryView and ctx.db.queryBatches for native read views rather than materializing every row as a JavaScript object. Keep views within the routine lifetime.

Built-in Functions