Additive changes

This page lists the purely additive changes in CYPEX v2.0.0. These do not modify the behavior of existing functionality — they add new tables, functions, indexes, and roles that integrate with the Organizations model.

The central tenant table. Created by the v2.0.0 upgrade with one row already inserted: the Default Organization (organization_domain = 'default').

Key columns:

• id — bigint, primary key. Inherited from cypex.t_global; not a UUID.
• organization_domain — short identifier used in URLs and JWTs.
• name — human-readable display name.
• created_at / created_by — inherited from cypex.t_global. There is no updated_at column.
• company_name and organization_domain are both NOT NULL, so a manual INSERT must supply them alongside name.

Mapping table between modules and their owning organization.

For the v2.0.0 upgrade, every existing module is mapped to the Default Organization. After the upgrade, admins can re-map modules to non-default organizations through the admin panel.

Mapping table between roles and the organizations they can access.

For the v2.0.0 upgrade, every existing role is mapped to the Default Organization, ensuring existing users keep seeing all data.

Audit log for permission decisions made by the backend. Each entry records:

• The user who made the request.
• The action attempted.
• Whether the action was allowed.
• The organization context at decision time.

Use this table for compliance reviews and incident investigation.

The following SQL functions read the current JWT context and expose it to policies and triggers:

These functions are STABLE and read the JWT claims from the request.jwt.claims GUC that PostgREST sets from the caller’s token. That GUC is the only session input the policies consult. Each takes an optional v_ignore_error boolean DEFAULT true argument, so they return NULL rather than raising when the GUC is unset.

The migration creates this function, but does not attach it to any table. No CREATE TRIGGER statement references it, so it never runs.

Practical consequence: on the cypex and cypex_log tables nothing in the database stamps or validates organization_id. The application supplies the value on insert; direct SQL writes must supply it too, or set a column default of DEFAULT cypex.current_organization_id(). Rows left with a NULL organization_id are visible to every organization through the organization_id IS NULL clause in the baseline policies.

The four sso_gateway tables do have such a trigger (sso_gateway.validate_organization_access()), so they stamp and validate the column themselves.

A virtual module is created automatically and mapped as primary to the Default Organization. This module:

• Has no physical schema.
• Acts as a fallback for queries that do not specify a module.
• Lets the backend resolve a “default” view of metadata without consulting t_module_organization for every request.

Admins do not normally need to interact with cypex_default; it exists so that pre-Organizations code paths continue to work after the upgrade.

Indexes are created on every new organization_id column to keep RLS predicate evaluation cheap:

1
2
3

CREATE INDEX idx_ui_organization ON cypex.t_ui(organization_id);
CREATE INDEX idx_file_organization ON cypex.t_file(organization_id);
-- ... and so on for every tenant-scoped table

These are non-unique B-tree indexes. They are added in the same migration that adds the column, so the migration does not require a separate ANALYZE pass.

1
2

CREATE ROLE organization_admin NOLOGIN;
GRANT cypex_admin TO organization_admin WITH ADMIN OPTION;

This role is NOLOGIN and is intended to be granted to existing roles that should have organization-admin scope. Membership in organization_admin is what makes CYPEX put isOrganizationAdmin: true in the token; cypex.is_organization_admin() then reads that claim. It does not inspect role membership, so a direct database session that sets no claims gets false no matter which roles it belongs to.

See Roles and permissions for the full role model.