Calling server side code

A workflow is a good start if you want to build an application. However, sometimes it’s still necessary to add control elements. In this section, you’ll learn to add buttons and to write server side code to make your application even more powerful.

CYPEX lets you write PostgreSQL functions directly in the admin panel. Those functions are served by PostgREST, the same component that serves your queries: a function in the exposed schema becomes an RPC endpoint at /rpc/<function_name>, called with POST and executed as the role in the caller’s JWT. Data and logic therefore travel the same path and obey the same PostgreSQL permissions.

Let’s start with a basic data model:

    BEGIN;

    CREATE SCHEMA inventory;

    CREATE TABLE inventory.t_inventory (
        id               serial PRIMARY KEY,
        name             text   NOT NULL,
        assigned_to_id   int8   NULL,
        hardware_details text   NULL,
        software_details text   NULL
    );

    CREATE TABLE inventory.t_users (
        id           serial PRIMARY KEY,
        first_name   text NOT NULL,
        last_name    text NOT NULL,
        salutation   text NOT NULL,
        email        text NULL,
        phone_number text NULL
    );

    COMMIT;

In the admin panel, open Builder → Database and switch to the Functions panel, next to Entities and Queries.

The + (Create New) button opens the function editor.

Warning

Functions live in the cypex_generated schema. That is the only schema PostgREST exposes (db-schema), and CYPEX rewrites any other schema qualification you type to cypex_generated — there is no way to publish a function from a different schema over the data API.

The editor takes a single SQL statement, and it must be a CREATE FUNCTION or CREATE OR REPLACE FUNCTION that declares a LANGUAGE. CREATE PROCEDURE is rejected outright; trigger and event-trigger functions save but are flagged, because PostgREST cannot expose them as RPC endpoints. PostgREST matches JSON body keys to parameter names, so name your parameters — the one exception is a single unnamed json/jsonb parameter, which receives the raw request body. Statement length is capped by the MAX_FUNCTION_SIZE setting (100,000 characters by default).

Before saving, assign the roles that should be associated with the function. This list is stored as CYPEX metadata and drives how the function is presented in the admin panel — it does not issue GRANT EXECUTE. PostgreSQL grants EXECUTE on new functions to PUBLIC by default, and PostgREST executes the call as the JWT role, so if a function must be restricted, revoke and grant execution rights explicitly. Use Manage Permissions from the function form to review the privileges actually in effect.

Once the function code is complete, click SAVE. The function then appears in the Functions panel; selecting it shows its definition and assigned permissions. CYPEX reloads the PostgREST schema cache automatically, so the new endpoint is callable straight away.

With the data model, queries, and permissions in place, go to Builder → Applications to generate the application. The result at this point is a basic application showing nothing more than tables.

Now the goal is to add a button calling the assign_inventory_to_user() function on the SQL side.

After these preparations have been completed, enter edit mode and add the Call Button element to your app. This element is designed for executing an existing database function; it issues the PostgREST RPC call for you.

Note the name of the function. Make sure that you choose the right function. Configure the button to call the inventory assignment function.

CYPEX provides various elements for gathering user input, such as text inputs and fields, dropdowns, date pickers, and tables. That input can be passed as arguments to the database function; the argument names must match the function’s parameter names. Use expressions to wire values from other elements into the call.

For instance, by using the selected rows from the Users and Inventory tables, we can assign inventory items to users:

Finally, add a label and choose an icon.

Upon saving these changes, clicking the Call Button executes the function, assigning the specified inventory to the designated user:

Call Buttons cover server-side logic a user invokes deliberately — aggregations, batch assignments, or a state change you want behind an explicit action. For logic that must run on every row change regardless of the UI, write a database trigger on the underlying table. See Workflows for state-driven enforcement and History Tracking for change capture.