What's new in CYPEX v2.0.0: tenancy moves from the query to the catalog

CYPEX 2.0.0 moves the isolation guarantee out of application code and into the PostgreSQL catalog. What used to be a repeated WHERE organization_id = ... in every query, report and export script now lives where no connection can bypass it.

The shape of the boundary

In v2.0.0 an Organization is the unit of tenancy, and it exists as a row in cypex.t_organization. Row-level security policies applied to 35+ tables during migration read the request's JWT claims. Separation is enforced by the engine on every session, regardless of which application opened the connection — two connections carrying different tenants in their JWT run the same SELECT and receive different rows. psql is not exempt, because nothing in the mechanism depends on the client.

Twelve existing tables gain an organization_id column directly; the remaining tables inherit their scope by joining to a parent module or object, so one assignment propagates. The upgrade creates a "Default Organization" and assigns pre-existing data to it, so existing installations are not left stranded.

The policy itself

The policy on the organization table is the pattern in four lines, verbatim from the migration. It is one of the set the migrations install:

PgSQL

Both helper functions read from the request context. cypex.is_admin() extracts the isSuperAdmin claim from request.jwt.claims; cypex.current_user_organization_ids() reads the organization_ids array from the same place. Administrative reach is a predicate, not a BYPASSRLS attribute on a role — no CYPEX role carries that attribute.

Proving it in one transaction

The practical result is a check a reviewer can run: same table, same query, two sets of claims.

PgSQL

Where another organization holds rows, the second count comes back larger. The upgrade guide asks for this check before and after migration.

Access as three independent controls

The single access switch was replaced by three controls, configured separately and evaluated independently:

• Schema Access — which database schemas exist for an organization at all. Stored as a mapping in cypex.t_module_organization, managed from a System Administrator page.
• Capabilities — which operations a role may perform. Plain PostgreSQL grants.
• Data Scope — which rows within those schemas a role may see, via the policies above. Capabilities and Data Scope are documented as separate concerns.

All three must agree, and PostgreSQL evaluates them in order. Without a schema mapping, the schemas are absent. Without a table grant, the query stops at permission denied. A grant with no matching policy row executes and returns nothing, which is why a correctly configured user facing an empty screen is usually looking at a grant problem rather than a role problem. An organization administrator holds all three within their own boundary and nothing outside it.

SSO and connectors

Two adjacent features sit inside the same boundary. Single sign-on providers are rows in sso_gateway.t_sso_provider, each keyed to one organization, and a first sign-in through a federated identity stays in a pending row until an administrator approves it or a mapping rule they wrote applies. The new connector platform restricts outbound REST calls to a host allowlist, keeps each credential as one encrypted version in the catalog away from the public REST surface, and ships with execution off — an organization with no enablement row gets none, so an upgrade sends no traffic anywhere.

The boundary of the boundary

The policies shipped in v2.0.0 cover CYPEX's own catalog only: the cypex, cypex_log and sso_gateway schemas. Business tables are not touched. Isolating those is a separate migration on your own database, and CYPEX deliberately does not run it. The work is identical on each nominated table: add an organization_id column, backfill existing rows, enable and force row-level security, install a baseline policy. That is schema-wide DDL against live data, so the job belongs to whoever owns the database. The client-table guide gives the order; the SQL it shows is an example to adapt rather than a script to run, because what each table admits is a decision about your data. Tables created after the migration inherit nothing.

NULL means shared, in the catalog

Inside the catalog schemas, a NULL organization is a shared organization, not a private one. Catalog policies admit organization_id IS NULL deliberately, so an upgraded installation does not come up looking empty — which also means an unassigned catalog row is readable by every organization. Each catalog policy states its write rule explicitly, and that rule is the read rule, admitting organization_id IS NULL. A writer supplying no organization therefore passes the check and creates a row every organization can read. cypex.validate_root_table_organization() ships with the migration and would reject a row whose organization falls outside the caller's claims, but no trigger invokes it. Rows written through CYPEX get their organization from the application, so this is invisible in normal use; rows written from psql or a migration receive whatever the writer supplies, and nothing in the database objects.

Client tables, once migrated, do not carry that gap. Their column is NOT NULL and defaults to cypex.current_organization_id(), so an insert that omits it lands in the caller's organization and a session without claims is rejected. The baseline policy spells out WITH CHECK, so naming another organization fails on the write instead of going quiet on the read.

Where the model does not apply

Row-level tenancy presupposes a schema to attach it to. CYPEX reads tables, keys and views and serves them over PostgREST, so a project with no data model — or one whose data lives in another engine — offers nothing to read. Foreign data wrappers widen that reach and CYPEX detects a foreign table, but workflow and auditing require DDL on a local relation and are not offered there.

The element registry marks the second boundary: tables, forms and sub-forms, charts, GeoJSON fields, file storage, markdown, code, and several dozen further field types. That inventory covers registers, approvals, case handling and dashboards over a schema someone already maintains. It does not cover mobile-first or public-facing consumer applications, or any interface needing an element CYPEX does not ship, because no SDK exists for writing one.

The third boundary is a trade. A bespoke build returns exactly what was specified, with every line of it to maintain, and an organization with the engineers and budget for that should take it. Large-scale analytics and cross-system joins are a separate job again, and both run on top of a system of record.

The audience the design assumes

CYPEX is built for people who already know PostgreSQL and want the application sooner: database administrators, data engineers, and consultants who arrive with the schema in hand. Anyone moving from Oracle APEX to PostgreSQL has worked this way before — an application kept close to the data, with the database carrying the rules.

What gets built stays with the builder: applications export as versioned JSON packages carrying SHA-256 checksums, data is reached through SQL and REST, and the cluster is one the organization operates. CYPEX ships as a Structured Infrastructure Component within CYBERTEC ScaleField.

Checking the answer

What stops one entity's administrator from reading another entity's rows is now nameable. That administrator carries their own organization identifiers in the JWT and nothing else, cypex.is_admin() is false for them, and both halves of the policy predicate fail on every row belonging to anyone else.

The difference from the old answer is that it can be verified by someone other than its authors. The policy is stored with the table, and \d cypex.t_organization prints it. Anyone reading the assessment can run that command on their own cluster, long after the people who configured it have moved on. On your own business tables the check is identical, once the boundary has been installed there. Either way, the answer no longer depends on who is asked.

The Organizations documentation sets out the tenancy model in full, limits included, and the release notes list every breaking change with the upgrade path. To put it to the test, bring your schema and your tenancy question to the database team.