What is an Organization?

An Organization is the unit of multi-tenancy in CYPEX. It is the data boundary that decides which rows a user can see, insert, update, or delete.

Organizations are not an authorization system on their own. Capabilities — what a user is allowed to do — come from PostgreSQL role grants. An Organization adds a second, independent layer: a per-request data scope on top of those capabilities.

This page is the conceptual reference. For the post-upgrade procedure for assigning clients to organizations, see Detailed organization setup.

Warning

What organization isolation covers. The RLS policies installed in v2.0.0 protect CYPEX’s own catalog — the cypex, cypex_log, and sso_gateway schemas. They do not apply to the business tables you model in. Isolating your own tables by organization is your policy to write; see Capabilities vs Data Scope for the template.

An Organization is a row in cypex.t_organization, created by the Organizations bootstrap migration during the v2.0.0 upgrade. The columns that matter when creating one:

The Default Organization (organization_domain = 'default') is created by the upgrade and is the canonical starting state. Every pre-existing schema and every cypex_user / cypex_admin member role is mapped to it, so data from before the upgrade stays visible without manual remediation.

Info

is_active is not an access control. Setting it to false removes an organization from the active lists the admin panel and API present. No RLS policy references the column, so it does not by itself revoke access to rows already scoped to that organization. To remove access, remove the role-to-organization mapping in cypex.t_role_organization.

Every authenticated request carries a signed JWT. The claims the database reads are:

The two admin flags are mutually exclusive by construction. Because organization_admin inherits cypex_admin, membership alone is not enough to make someone a system administrator — isSuperAdmin is true only for cypex_admin members who are not also organization_admin members.

Info

Visibility is never driven by org_id alone. org_id is the active organization and is what most row checks compare against. The policies also read organization_ids through cypex.current_user_organization_ids() and both admin flags. An organization administrator working across several organizations is resolved through organization_ids, not org_id.

Four helper functions expose the claims to SQL. All are STABLE and read request.jwt.claims, which the backend sets on the session before running the request:

• cypex.is_admin(v_ignore_error boolean) — reads isSuperAdmin.
• cypex.is_organization_admin(v_ignore_error boolean) — reads isOrganizationAdmin.
• cypex.current_organization_id(v_ignore_error boolean) — reads org_id and casts it to BIGINT.
• cypex.current_user_organization_ids(v_ignore_error boolean) — reads organization_ids and returns BIGINT[].

Tables that carry an organization_id column — cypex.t_ui, cypex.t_file, cypex.t_report and others — follow this pattern:

1
2
3
4
5
6
7
8
9

CREATE POLICY ui_user_access ON cypex.t_ui FOR ALL USING (
  organization_id IS NULL
  OR cypex.is_admin()
  OR organization_id = cypex.current_organization_id()
  OR (
    cypex.is_organization_admin()
    AND organization_id = ANY (cypex.current_user_organization_ids())
  )
);

Tables that do not store organization_id are scoped through a parent. For example, cypex.t_object resolves its scope through its module’s organization mapping:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE POLICY object_user_access ON cypex.t_object FOR ALL USING (
    cypex.is_admin()
    OR EXISTS (
      SELECT 1 FROM cypex.t_module m
      JOIN cypex.t_module_organization om ON om.module_id = m.id
      WHERE m.id = cypex.t_object.module_id
        AND (
          om.organization_id = cypex.current_organization_id()
          OR (cypex.is_organization_admin() AND om.organization_id = ANY(cypex.current_user_organization_ids()))
        )
    )
);

Note what the first pattern does and the second does not: where an organization_id column exists, organization_id IS NULL makes the row visible to everyone. That clause preserves visibility for system-wide rows and for data not yet assigned to an organization. It also means a NULL organization is not a private organization — if a row must belong to one tenant, it needs a value.

Warning

Assigning organization_id is the application’s job, not the database’s.

There is no database trigger that stamps organization_id onto new rows or rejects a write aimed at another organization. cypex.t_organization has no such trigger attached, and the helper cypex.validate_root_table_organization() that would perform the check exists in the schema but is not bound to any table.

Rows written through CYPEX get their organization_id from the application layer. If you insert into a CYPEX catalog table directly — from psql, a migration, or an ETL job — set organization_id yourself. A row left NULL will be visible to every organization.

flowchart TB
    user["User login"]
    auth["Auth service"]
    jwt["JWT token<br/>org_id, organization_ids,<br/>isSuperAdmin, isOrganizationAdmin"]
    api["Backend request handler"]
    pg["PostgreSQL session<br/>SET LOCAL request.jwt.claims"]
    policy["RLS policy<br/>cypex.t_*_access"]
    helper["cypex.current_organization_id<br/>cypex.current_user_organization_ids<br/>cypex.is_admin / is_organization_admin"]
    rows["Visible rows for this request"]

    user -->|"submits credentials"| auth
    auth -->|"mints"| jwt
    jwt -->|"bearer token"| api
    api -->|"sets session variable"| pg
    pg -->|"evaluates"| policy
    policy -->|"calls helpers"| helper
    helper -->|"returns filtered set"| rows

Permission changes are written to cypex_log.t_permission_audit_log: role and organization creation, grants and revocations, schema and query permission edits, connector executions, and explicitly logged access decisions. Recorded actions are CREATE, UPDATE, DELETE, GRANT, REVOKE, ASSIGN, UNASSIGN, ACCESS_GRANTED, ACCESS_DENIED, and the connector EXECUTE_* family.

Each row carries the entity type and identifier, the acting user, the before and after state as JSONB, the request IP address and user agent, and a context JSONB column that holds the organization identifier and other situational detail.

Two read endpoints are available to administrators:

GET /admin/audit/permissions        list, filterable by user, action,
                                    entity type, entity ID, and date range
GET /admin/audit/permissions/{id}   a single audit row

Info

RLS filtering is silent. When a policy hides a row, nothing is written to the audit log — the row simply does not appear in the result. The permission audit log answers “who changed this permission, and when”, not “why was this particular row not returned”. For the latter, use Access Preview, which resolves capability and data scope together for a given role and organization.

Tip

Checking that RLS is enabled

1 2 3 4

SELECT schemaname, tablename, rowsecurity FROM pg_tables WHERE schemaname IN ('cypex', 'cypex_log') ORDER BY schemaname, tablename;

Organization-scoped tables report rowsecurity = t. Note that this confirms RLS is enabled, not that a policy is correct — read the policies themselves in pg_policy, or use Access Preview. The Upgrade to v2.0.0 page lists the full post-upgrade verification queries.

An Organization’s data scope is a combination of two things: the organization_id filter described above, and the set of schemas granted to that organization. Grant and revoke them on the Schema Access page. An organization with no grant on a schema sees nothing in it, whatever its roles and policies say.

“Module” is a deprecated synonym for “Schema” and still appears in database identifiers such as cypex.t_module.schema_name and cypex.t_module_organization. See the Glossary.

• Not an authorization system. Capabilities come from PostgreSQL role grants. An organization decides which rows, not which operations.
• Not a substitute for query-level permissions. Query and function permissions still apply inside an organization.
• Not a soft delete. is_active filters administrative listings; it neither revokes access nor frees storage.
• Not nested. Organizations do not contain other organizations. They sit at the bottom of a role → organization → schema chain, which Organization hierarchy and assignment sets out in full.

GET /admin/user-context returns an adminLevel field alongside the user and their resolved permissions. It takes three values:

This is the discriminator the admin panel branches on to decide whether to render the global organization picker or the organization-scoped one. When integrating from your own front end, branch on adminLevel rather than re-deriving it from raw JWT claims — the precedence between the two flags is not obvious, and organization_admin inherits cypex_admin.

The endpoint itself requires administrative authorization, so "regular" appears only where the response type is reused for non-administrative callers. adminLevel is not part of the login response; obtain it from /admin/user-context.

• Glossary — the one-line Organization definition.
• Capabilities vs Data Scope — the two-dimensional permission model, and how to scope your own tables.
• Organization hierarchy and assignment — roles, schemas, and organizations, and how to assign clients.
• Governance evidence — where evidence of an organization-boundary decision is recorded.
• Detailed organization setup — the post-upgrade assignment procedure.
• Upgrade to v2.0.0 — the operator upgrade entry point.
• Roles and permissions — the full role table and verification queries.
• User management — how the user concept interacts with Organizations.