Functions

TotalBase has no custom actions of its own. When an application needs an operation that isn't a plain create, update or delete (create an invoice from a work order, close a month, plan next month's maintenance), write a PostgreSQL function. It becomes a FUNCTION resource you can expose, grant and call.

POST /api/{schema}/function/{name}

The logic lives in the database, next to the data, inside a transaction. It runs the same way whether an application, an integration or a workflow calls it.

Creating one

Functions are written in SQL. In Database, choose a schema and New function: the SQL Console opens with a skeleton to fill in. Acme invoices a completed work order:

CREATE FUNCTION public.create_invoice_from_work_order(work_order uuid)
RETURNS uuid
LANGUAGE plpgsql AS $$
DECLARE
  wo      work_orders;
  invoice uuid;
BEGIN
  SELECT * INTO wo FROM work_orders WHERE id = work_order FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Work order not found';
  END IF;
  IF wo.status <> 'COMPLETED' THEN
    RAISE EXCEPTION 'Only a completed work order can be invoiced';
  END IF;

  INSERT INTO invoices (customer, work_order, issued_on, due_on, status, tax_rate)
  SELECT s.customer, wo.id, current_date, current_date + 30, 'DRAFT', 21
  FROM sites s WHERE s.id = wo.site
  RETURNING id INTO invoice;

  -- hours at the plan's rate, then the parts used
  INSERT INTO invoice_items (invoice, description, quantity, unit_price) …;

  RETURN invoice;
END $$;

TotalBase picks it up at once as a hidden resource.

Write it for callers

A function exposed through the API is an API of its own. Write it that way:

• Check its inputs, and refuse with RAISE EXCEPTION and a sentence a user can read. The message reaches the caller unchanged, as 422 RAISE_EXCEPTION, and a DataView shows it.
• Lock what it changes (FOR UPDATE), so two concurrent calls can't invoice the same order twice. An Idempotency-Key protects against a retried request too.
• Know who's calling. current_setting('totalbase.actor_user_id', true) is the calling user's id.

Exposing and granting

A function has one method, POST, and one action, EXECUTE. Enable POST in its Endpoints, then grant EXECUTE to the roles that may call it.

Row filters and field grants don't apply to functions: TotalBase can't see inside one. What it reads and writes is decided by PostgreSQL:

If a function must respect who may do what, check it inside the function: for example, compare the order's branch with the calling user's.

For a function that must never run twice for the same request, such as charging a payment, turn on Require an idempotency key under its Endpoints.

Classification

On a function's page, classify it:

The class doesn't change how the function runs. It's documentation: it's in the generated OpenAPI (x-totalbase-function-class), where developers and AI agents read it to decide what's safe to call.

Calling it

From the API, with the arguments by name:

POST /api/public/function/create_invoice_from_work_order
{ "data": { "work_order": "8f1c…" } }

{ "data": "c41e…" }

The call runs in one transaction, is recorded in the audit log with its arguments, and produces an event that workflows can react to. Calling functions covers results, overloads and asynchronous calls.

Workflows call functions too, with an Execute function step: Acme's Order completed workflow calls create_invoice_from_work_order as soon as an order is completed.

What can be a resource

Functions with OUT, INOUT or VARIADIC parameters can't be called through the API.

Changing and dropping

A function's page shows its definition. Edit opens it in the SQL Console as CREATE OR REPLACE FUNCTION.

• CREATE OR REPLACE keeps its identity: exposure and permissions stay.
• Changing its arguments creates a different overload, which is a new resource.
• DROP FUNCTION and CREATE FUNCTION again makes a new, hidden resource with no permissions.

Next steps

• Calling functions.
• Workflows: run a function when something happens.