Organization hierarchy and assignment

An Organization sits at the centre of two independent mappings, not at the end of a chain. One mapping says which roles belong to the organization; the other says which schemas it may use. Both point at cypex.t_organization, and neither knows about the other:

pg_roles                          cypex.t_module
(login identity, grants)          (schema registry)
    │                                   │
    │  cypex.t_role_organization        │  cypex.t_module_organization
    │  (role_name ↔ organization)       │  (module_id ↔ organization)
    │                                   │
    └──────────►  cypex.t_organization  ◄──────────┘
                  (the tenant boundary)

A user sees a row only when both mappings agree: their role is mapped to the organization, and the schema holding the object is granted to that same organization. Satisfying one without the other produces an empty result, not a partial one.

This page describes each part and the order in which to wire them up. For the post-upgrade procedure that walks a real deployment through client assignment, see Detailed organization setup.

PostgreSQL roles hold the login identity and the capability grants. This is unchanged from v1.x. Every CYPEX user maps to a role, and after the v2.0.0 upgrade every existing role is also mapped to at least one organization.

Roles are where capabilities live. Organizations are where data scope lives. See Capabilities vs Data Scope.

The primary key is (role_name, organization_id). A role may be mapped to several organizations; each mapping is one row.

Info

The role is stored by name, and PostgreSQL does not enforce it. There is no foreign key to pg_roles — a catalog table cannot be referenced. Renaming or dropping a PostgreSQL role therefore leaves its mappings behind as orphans. Remove the mapping when you remove the role.

There is no is_primary column on this table. When a user’s active organization has to be resolved, CYPEX takes the earliest mapping for their role by created_at. The first organization a role is mapped to is the one it defaults to.

Super-administrator roles are deliberately absent from this table. cypex_admin reaches every organization through the cypex.is_admin() clause in the policies, so it needs no mapping.

“Module” is a deprecated synonym for “Schema”; the identifier survives in the table and column names. Granting a schema to an organization is what makes its tables, views, and functions reachable by that organization’s users. Schema Access is the admin page for granting, revoking, and setting the primary schema.

Info

Granting a schema to an organization is necessary but not sufficient. The user’s role must also be mapped to that organization in t_role_organization. Both mappings must agree before rows appear.

A row here is the tenant. Its id flows into the org_id JWT claim and is compared by the RLS policies.

The Default Organization (organization_domain = 'default') is created by the upgrade, and every pre-existing schema and role is mapped to it.

Warning

Only some tables carry organization_id directly. The upgrade adds the column to twelve existing tables: eight in cypex — t_ui, t_file, t_report, t_notification, t_query_group, t_function_metadata, t_export_job, t_import_job — and four in cypex_log — t_log, t_log_api, t_permission_audit_log, t_user. Tables introduced by v2.0.0 are created with the column already present: the mapping tables (t_role_organization, t_module_organization), the connector tables, cypex_log.t_sso_audit_log, and the four sso_gateway tables.

Everything else, including cypex.t_object, t_object_view, and t_state_change, has no such column and is scoped indirectly through its module’s entry in t_module_organization.

If you write your own queries or policies against CYPEX tables, do not assume organization_id exists on all of them.

See What is an Organization? for the full column list and the JWT-to-RLS plumbing.

Multi-tenancy arrives in three migration steps, applied together:

Across the v2.0.0 migration set, 37 tables have Row-Level Security enabled: 28 in cypex, 5 in cypex_log, and 4 in sso_gateway. The full evolution from single-tenant to multi-tenant is described in Conceptual overview.

When splitting a single-tenant deployment into several organizations after the upgrade, work in this order. This is the post-upgrade tenant-split sequence (schemas before role mapping so membership is never empty-handed). First-run Setup Guide order is different: Organizations → Roles & Capabilities → Schema Access → Data Model → Users → Applications.

flowchart TD
    s1["1. Create the organization<br/>on the Organizations page.<br/>name, company_name, and organization_domain are all required.<br/>A new organization is active by default."]

    subgraph schemaAccess["Schema Access"]
        direction TB
        s2["2. Grant schemas<br/>to the organization on Schema Access.<br/>This decides which PostgreSQL schemas the organization#39;s users can reach."]
        s3["3. Set the primary schema<br/>on the same page.<br/>It is the default target for custom queries created inside the organization."]
        s2 --> s3
    end

    s4["4. Map roles<br/>to the organization, from the Organizations or Users screens.<br/>This writes to t_role_organization."]
    s5["5. Choose the role type<br/>where a role needs administrative reach.<br/>Setting a role to organization_administrator causes CYPEX to issue the<br/>GRANT organization_admin itself, inside the same operation - do not run<br/>the GRANT by hand."]
    s6["6. Verify<br/>with the queries below and in RLS impact on existing data."]

    s1 --> s2
    s3 --> s4 --> s5 --> s6

    classDef create fill:#2a9dad33,stroke:#2a9dad,stroke-width:2px
    classDef schema fill:#3d8b5f33,stroke:#3d8b5f,stroke-width:2px
    classDef roles fill:#b8956c33,stroke:#b8956c,stroke-width:2px
    classDef caution fill:#d4a01740,stroke:#d4a017,stroke-width:2px
    classDef verify fill:#6b7c8a33,stroke:#6b7c8a,stroke-width:2px

    class s1 create
    class s2,s3 schema
    class s4 roles
    class s5 caution
    class s6 verify

Schema Access · RLS impact on existing data

Warning

Order matters, and rehearse it on staging. Mapping roles before granting schemas leaves users with organization membership but nothing to see: both mappings must agree, so an incomplete sequence looks identical to a permissions bug. Complete steps 2 and 3 before step 4.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- The organization exists and is active
SELECT id, organization_domain, is_active
FROM cypex.t_organization
WHERE organization_domain = '<your-domain>';

-- Role mappings. Note that the role is stored by name, not by OID.
SELECT ro.role_name, o.organization_domain
FROM cypex.t_role_organization ro
JOIN cypex.t_organization o ON o.id = ro.organization_id
ORDER BY ro.role_name, o.organization_domain;

-- Schema (module) mappings, including which one is primary
SELECT m.module_name, m.schema_name, o.organization_domain, mo.is_primary
FROM cypex.t_module_organization mo
JOIN cypex.t_module m ON m.id = mo.module_id
JOIN cypex.t_organization o ON o.id = mo.organization_id
ORDER BY m.module_name, o.organization_domain;

-- Orphaned role mappings: rows naming a role that no longer exists
SELECT ro.role_name, ro.organization_id
FROM cypex.t_role_organization ro
WHERE NOT EXISTS (
    SELECT 1 FROM pg_roles r WHERE r.rolname = ro.role_name
);

The first three queries should each return at least one row for the new organization; the fourth should return none. If any of them disagrees, do not cut over traffic — investigate first.

The following hold when you create, update, or delete an Organization through the API. Automation can rely on them.

Creating an organization provisions an organization-private PostgreSQL schema and its primary module mapping in the same transaction as the organization row. If provisioning fails, the organization is not created. A freshly created organization therefore never has zero schemas and never has zero primary schemas.

The provisioned schema is named cypex_<domain>, where the domain is sanitized to a valid PostgreSQL identifier — characters outside [A-Za-z0-9_] become underscores, and a leading digit is prefixed with one.

Assigning a schema to an organization that already has it is a no-op (ON CONFLICT DO NOTHING) and returns success. Automation can retry without checking first.

The domain is the stable, URL-safe identifier. On both create and update it must:

• be non-empty;
• match ^[a-z0-9-]+$ — lowercase letters, digits, and hyphens only;
• be unique across all organizations, enforced by a database constraint.

DELETE /admin/organizations/{id} removes the organization’s rows in t_module_organization and t_role_organization by foreign-key cascade. Mappings belonging to other organizations are untouched.

Rows in cypex_log.t_permission_audit_log survive, because that table has no foreign key to t_organization — the organization identifier is carried inside its context JSONB column. The audit trail outlives the organization it describes, which is the intended behaviour for governance evidence.

The organization list and detail responses include user_count and roles, computed by the backend. A client does not need a second request to render either.

• What is an Organization? — the tenancy contract and the JWT-to-RLS plumbing.
• Capabilities vs Data Scope — what a role may do versus which rows it may touch.
• Schema Access — granting schemas to organizations.
• Detailed organization setup — the post-upgrade procedure end to end.
• Roles and permissions — the role table and grant/revoke SQL.