Scoped SQL on your own database

Your data already lives in a Postgres database you run: an ERP replica, a reporting database, a warehouse. You want the people who sign in to your app to read their rows from it, without giving the browser a connection string and without letting one customer see another's data.

Scoped SQL does this with one workflow, sql.query.run-scoped. You write the query. Your users can only supply filter values. Orkestia picks the database login from the user's active workspace, and your database decides what that login can see.

AppData or your own database?

If you can move the data into App Data, do: the platform then enforces the boundary on its own. Scoped SQL is for data that has to stay in your database.

How the scope works

end user signs in  ──►  starts your exposed composition
                                │
              Orkestia reads the user's active workspace (from the token, never from the request)
                                │
              workspace + slot  ──►  the SQL connection you bound to that workspace
                                │
              your database login  ──►  only the rows that login may see
                                │
              your static SELECT, narrowed by the user's filter values

• A workspace is a tenant inside your app: a company, a farm, an office. See Workspaces.
• A slot is a name your composition uses for "the database connection", such as reporting. The composition names the slot, never a connection.
• A binding maps one workspace and one slot to one SQL connection. Each workspace gets its own connection, and each connection uses its own database login.

The scope is the workspace, not the individual person. Everyone in the same workspace reads through the same login and sees the same rows. If each person must see only their own rows, give each person their own workspace and login.

What Orkestia guarantees, and what your database must do

Every check on the left runs again on every run, from a fresh lookup: the user and the app are active, the workspace is still the user's active one and their membership is active, the binding exists and points at a live SQL connection of your organization. Nothing is cached from bind time or from when the run started.

Set it up

1. Create one database login per workspace (DBA)

Each workspace needs its own Postgres login that can read only that workspace's data. Rules:

• not a superuser, and not BYPASSRLS (the run is refused);
• never shared by two workspaces (the run is refused if two workspaces in a slot use the same host, port, database and username);
• never the login your sync or ETL job writes with: grant SELECT only.

Two common ways to do it.

Option A: row-level security. One table maps each login to the rows it may see:

-- once
CREATE ROLE orkestia_readers NOLOGIN;
GRANT USAGE ON SCHEMA sales TO orkestia_readers;

CREATE TABLE sales.reader_scope (login name PRIMARY KEY, customer_id bigint NOT NULL);
ALTER TABLE sales.reader_scope ENABLE ROW LEVEL SECURITY;
CREATE POLICY own_row ON sales.reader_scope
  FOR SELECT TO orkestia_readers USING (login = current_user);
GRANT SELECT ON sales.reader_scope TO orkestia_readers;

ALTER TABLE sales.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY by_customer ON sales.orders
  FOR SELECT TO orkestia_readers
  USING (customer_id IN (SELECT customer_id FROM sales.reader_scope));
GRANT SELECT ON sales.orders TO orkestia_readers;

-- per workspace
CREATE ROLE orkestia_ws_acme LOGIN PASSWORD '…' IN ROLE orkestia_readers;
INSERT INTO sales.reader_scope VALUES ('orkestia_ws_acme', 42);

Each login sees only its own row of reader_scope, so the policy on orders resolves to that customer's rows.

Option B: one schema per workspace. Put each workspace's tables in its own schema, grant the login only that schema, and set its search_path so the same unqualified SQL works for every workspace:

CREATE ROLE orkestia_ws_acme LOGIN PASSWORD '…';
GRANT USAGE ON SCHEMA acme TO orkestia_ws_acme;
GRANT SELECT ON ALL TABLES IN SCHEMA acme TO orkestia_ws_acme;
ALTER ROLE orkestia_ws_acme SET search_path = acme;

2. Add one SQL connection per login

Create an Orkestia connection of type sql for each login with connection.setup (console or MCP):

Postgres is the only supported database. Orkestia must be able to reach host:port over the network. sql.connection.validate checks the connection.

3. Bind each connection to its workspace

Pick a slot name: lowercase letters, digits and _, starting with a letter, up to 41 characters (^[a-z][a-z0-9_]{0,40}$). Bind every workspace's connection under that slot:

identity.app-organization.bind-connection({
  identity_app_uuid: "…",
  app_organization_uuid: "…",      // the workspace
  slot: "reporting",
  cloud_connection_uuid: "…",      // that workspace's connection
})
// → { app_organization_uuid, slot, cloud_connection_uuid, bound: true }

• The connection must be a live SQL connection of your organization.
• Binding a connection that already serves another workspace in the same slot is refused.
• Binding a slot again replaces the previous connection.

List what is bound with identity.app-organization.connection-bindings (no credentials are returned). A workspace you add later has no binding until you bind one; its users get SCOPE_UNRESOLVED until then.

4. Allow the step for your app

sql.query.run-scoped is not open to end users by default. Turn it on for your app only:

identity.app.set-workflow-eligibility({
  identity_app_uuid: "…",
  workflow_type: "sql.query.run-scoped",
  enabled: true,
})

Bind, unbind and this switch must be run by a person on your team (in the console, over MCP, or with an API token they created). AI agent actors are refused, so an agent cannot widen what your users can read.

5. Write the composition

Everything is static except filter values. Each filter declares a column of your query's output, an operator and a type; only value may come from the caller.

{
  "name": "my-orders",
  "layers": [
    {
      "name": "read",
      "steps": [
        {
          "name": "orders",
          "workflow_type": "sql.query.run-scoped",
          "input_mapping": {
            "slot": { "source": "static", "value": "reporting" },
            "sql": { "source": "static", "value": "SELECT id, status, placed_on, total FROM sales.orders" },
            "filters": {
              "source": "object",
              "fields": {
                "status": {
                  "source": "object",
                  "fields": {
                    "column": { "source": "static", "value": "status" },
                    "op": { "source": "static", "value": "in" },
                    "type": { "source": "static", "value": "text" },
                    "value": { "source": "input", "field": "statuses" }
                  }
                },
                "period": {
                  "source": "object",
                  "fields": {
                    "column": { "source": "static", "value": "placed_on" },
                    "op": { "source": "static", "value": "between" },
                    "type": { "source": "static", "value": "date" },
                    "value": { "source": "input", "field": "period" }
                  }
                }
              }
            },
            "max_rows": { "source": "static", "value": 500 }
          }
        }
      ]
    }
  ]
}

Save it with composition.validate and composition.save (Compositions).

If a user leaves a filter out, that filter is skipped: they get every row of their own scope, never more.

6. Expose it to your app

identity.app.expose-virtual-workflow({
  identity_app_uuid: "…",
  composition_uuid: "…",
  version: 1,
})

Expose refuses the composition, and lists the offending paths under unsealed_steps, if caller input reaches anything other than filters.<name>.value. That includes input passed through another step first. Turning the switch from step 4 off again un-exposes every composition that depended on it.

7. Call it as the signed-in user

import { createOrkestiaAuth } from "@orkestia/auth"
import { LtIntegWorkflowsClient } from "@ltinteg/workflows-sdk"

const session = createOrkestiaAuth({ clientKey: "orkestia_…" }).getSession()
const client = new LtIntegWorkflowsClient({
  baseUrl: "https://workflow-api.orkestia.dev",
  token: session.token,
})

const run = await client.start("virtual.<composition_uuid>@1", {
  statuses: ["open", "paid"],
  period: ["2026-10-01", "2026-10-31"],
})
const { state_data } = await run.wait()
// state_data.orders → { rows: [...], row_count, truncated, slot, app_organization_uuid, ... }

The step's result sits under the step's name. truncated: true means there were more rows than max_rows.

Filters

• integer: a whole number (or a string of digits). uuid: a UUID. date: an ISO date such as 2026-10-31. text: a non-empty string of at most 200 characters, with eq or in only, compared exactly (no wildcards).
• A null, empty, wrongly typed or out-of-range value fails with FILTER_INVALID.
• At most 16 filters per step. All supplied filters are combined with AND around your query, so they can only remove rows.

Rules for your SQL

Orkestia wraps your query as SELECT * FROM (<your SQL>) AS q WHERE <filters> LIMIT <max_rows + 1>. Your SQL must be:

• exactly one SELECT statement (CTEs are fine);
• free of placeholders: values only arrive through filters. Write %% for a literal %, for example LIKE 'INV-%%';
• read-only: no INSERT, UPDATE, DELETE, MERGE, SELECT INTO, FOR UPDATE or FOR SHARE, even inside a CTE;
• free of session, file, sleep and cross-database functions: set_config, current_setting, pg_sleep*, pg_read_file and similar, dblink*, lo_*, advisory locks, backend termination.

A query that breaks a rule fails with SQL_REJECTED. A filter on a column that is not in your query's output fails with QUERY_FAILED.

Limits

Both are static. The timeout is applied as the transaction's statement_timeout on your server, so make sure your database can answer within it: index the columns you filter on. Every run opens a new connection with the workspace's login and closes it at the end.

Values come back JSON-safe: dates and timestamps as ISO strings, numeric as strings (precision kept), UUIDs as strings, bytea as hex.

Errors

The user's run fails with a code only. Database error text is never returned to the end user.

Check and roll back

• Sign in as a user of workspace A and run the composition: only A's rows come back.
• Switch that user to workspace B (identity.end-user.organization.switch) and run again: only B's rows.
• Unbind A (identity.app-organization.unbind-connection): A's next run fails with SCOPE_UNRESOLVED.

To stop everything at once, run identity.app.set-workflow-eligibility with enabled: false. Every composition that uses the step is un-exposed.

Not supported

• Databases other than Postgres.
• Scheduled runs: a schedule carries no workspace, so it is refused.
• Writes: the step only reads.
• Choosing a connection, SQL or column at run time.

Related