Create App backend functions

A guarded synchronous App backend function is the App-runtime equivalent of a Windmill Script: one named, author-fixed SQL operation over the App’s bound database, invoked over the App’s per-deployment callback as a typed request/response. It runs synchronously under the same database guard the workflow db_query / db_execute nodes use — there is no runs row and no polling. Reserve workflows for real orchestration (multi-step, external calls, durable, retries, human gates); reach for a backend function for a read or a simple write.

How it works

The function is declared in the App’s manifest_yaml and parsed into BackendFunctionDecl (server/crates/sc-svc-projects/src/apps/manifest.rs). Two endpoints surface it (server/crates/sc-iface-http/src/interfaces/http/apps_backend_routes.rs):

Endpoint	Purpose
`GET /api/apps/{id}/backend`	The function catalog — name, `operation`, and argument metadata. Never the SQL.
`POST /api/apps/{id}/backend/{function_name}`	Invoke — body `{ "args": [...] }`, positional, bound to `$1..$n`.

Both are authenticated by either a per-deployment App token (Bearer scwa_…) or a logged-in session — resolved by resolve_submit_auth (apps_runs_auth.rs), the same path the run-submit endpoint uses. A deployed App’s server-side code calls them with its scwa_ token; the operator browses them from the App detail Execute tab (web/src/lib/components/apps/AppBackendTab.svelte).

Steps

Bind an app_db: resource.

A backend function needs a database to run against, so declaring any function requires the App to bind a postgresql resource via app_db: in the manifest. app_db: accepts the resource name (the ergonomic form) or its UUID — services::apps::resolve_app_db_ref resolves it. On save the App’s per-App app_<id> schema is ensured and the RLS provisioner is run (ADR 0023 v4).

Instead of a resource you own, app_db: also accepts the reserved token builtin — the server-managed built-in App database. The first App that uses it lazily creates one builtin_app_db resource for the workspace, shared by every App (each App is isolated in its own schema and least-privilege role under RLS). It is derived from platform credentials and owned by the server, so it appears in Connections/Resources as read-only (a System badge; Test, Edit, Delete and RLS-reprovision are disabled) — you never configure or test it yourself.
Declare the function in backend_functions:.

Add one entry per function. operation is query (READ-ONLY SELECT/WITH) or execute (INSERT/UPDATE/DELETE/MERGE, no DDL). sql is the author-fixed, parameterised statement with $1..$n placeholders. params is descriptive metadata for the calling UI (a label + an optional text | number | boolean hint); when present, its length is the expected positional argument count.
```
app_db: workspace_app_db
backend_functions:
  - name: overview
    operation: query
    sql: "SELECT id, total FROM ledger WHERE region = $1"
    params:
      - name: region
        kind: text
  - name: mark_paid
    operation: execute
    sql: "UPDATE ledger SET paid = true WHERE id = $1"
    returning: false
```
For an execute function, set returning: true to return the DML’s RETURNING rows to the caller; it is ignored for a query.
Save the App.

On save, validate::validate_backend_functions (server/crates/sc-svc-projects/src/apps/validate.rs) enforces, in order: every name is non-empty and unique within the App; every sql passes the matching allowlist for its operation; and — if any function is declared — the App must bind an app_db: resource. A malformed or dangerous function can therefore never be persisted.
List the catalog.
Terminal window
```
curl -s https://app.example.com/api/apps/$APP_ID/backend \
  -H "Authorization: Bearer $SCWA_TOKEN"
```
Returns the declared functions as BackendFnInfo — name, operation, and params — with the SQL withheld.

Invoke a function.

Pass the positional arguments in args, in declaration order:

curl -s -X POST https://app.example.com/api/apps/$APP_ID/backend/overview \
  -H "Authorization: Bearer $SCWA_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{ "args": ["EU"] }'

The response is a BackendFnResponse. operation tells the caller how to read it: a query returns rows with row_count = rows.len(); an execute returns the affected-row count in row_count and any RETURNING rows in rows.

{ "function": "overview", "operation": "query",
  "rows": [{ "id": 1, "total": 940 }], "row_count": 1 }

Call it from the App frontend.

A deployed App calls the same endpoints with its per-deployment scwa_ token (mint / rotate it owner-only at POST /api/apps/{id}/deployment/token). The token is used server-side by the App’s backend — never embedded in the public frontend bundle — so the App’s own server invokes overview() and returns the rows to its UI. From the operator side you can run any function ad-hoc on the App detail Execute tab, which calls appsApi.listBackendFns and appsApi.invokeBackendFn against the same two endpoints.

The guard and safety model

The guard is decoupled from its packaging — one decision point, enforced identically whether the SQL is packaged as a workflow node or invoked as a synchronous function. It is layered:

Statement allowlist (services::workflows::security::sql_guard, sqlparser): a query must parse to a single SELECT/WITH and contain no write/DDL tokens; an execute must parse to a single INSERT/UPDATE/DELETE/MERGE with no DDL tokens. Enforced at save time and re-confirmed at call time (defense-in-depth against manifest drift).
Positional binding only. Arguments bind to $1..$n; the SQL itself is fixed in the manifest. There is no string interpolation, so the wire carries no injectable SQL.
READ-ONLY runtime backstop. A query runs in a BEGIN READ ONLY transaction with default_transaction_read_only = on (db_guarded_exec::exec_guarded_query), so even a mis-declared query whose SQL is actually DML is rejected by Postgres at the transaction level.
RLS supacloud.app_id backstop. Before the statement runs, the kernel injects SET LOCAL supacloud.app_id from the App identity, never the request, so the App’s RLS policies filter rows by App and a caller cannot spoof which App’s data it sees. RLS / app_db is the always-on backstop regardless of access path.
Bounded blast radius. A per-call statement timeout (10000ms) and a hard row cap (1000 rows) keep a synchronous read sub-second and prevent an unbounded payload streaming back over the callback.