Terminology

CYPEX builds applications from a PostgreSQL schema. The schema is the source of truth: it determines the generated API, the access model, and the default application layout. This page defines the terms that appear throughout the documentation and explains how they relate to each other.

Short definitions of individual terms are collected in the Glossary. This page is the narrative version.

An entity is a table that CYPEX tracks. CYPEX uses the same semantics as the relational model — an entity is a table, nothing more — but registration matters: only tracked tables participate in GUI prediction, the process by which CYPEX derives a default application layout from the data structure. An untracked table remains a normal PostgreSQL table and is invisible to the application layer.

Not every table should be an entity. Link tables, staging tables, and internal bookkeeping tables are usually better left untracked.

Entities are grouped by schema. In the CYPEX catalog this grouping is called a module, which is a deprecated synonym for Schema; see Module → Schema below.

You do not build screens directly against entities. An abstraction layer sits between the tables and what the application sees — the query.

A query is what an application page actually reads from. Tables rarely hold data in the shape a screen needs, so CYPEX renders from a query: a subset of columns, a join, an aggregate, or any other SQL statement that pre-processes the data. A query prepares the data; the application renders it.

CYPEX can generate a default query for a tracked entity, or you can write your own. Generated queries are created as views in the cypex_generated schema. Permissions, filters, and application bindings are attached at the query level, which keeps the underlying tables unchanged.

Every query that feeds an editable element needs to identify a row unambiguously. CYPEX calls this the identifying column, and it must be a single column. When a query is generated, CYPEX picks it from the primary key, falling back to a UNIQUE index on a NOT NULL column.

Composite keys are not supported. CYPEX will not identify a row by more than one column — a deliberate constraint that keeps generated URLs, form bindings, and chart drill-downs unambiguous. Give every entity a single-column surrogate key, even when the natural key would be sufficient for the data model alone.

This matters beyond forms. Selecting a point in a chart, opening a row in a sub-form, and linking between pages all depend on a single identifying value.

Workflows are the next step once the relational model exists. Three terms are involved:

• State column — the column on an entity that holds the current state
• State — one permitted value of that column
• State change — a permitted transition between two states

An offer is a typical example. It is created, edited, sent to the client and, with luck, signed. The entity carries a state column restricted to the valid entries created, sent, accepted, and rejected. Moving between two of them is a state change.

States and state changes are defined on the entity, not on the query. They belong to the database model, not to the presentation layer.

An entity has either no state column or exactly one. Combined or multiple state columns are not supported.

The state column must be a text-like or identifier-like type: text and its variants, uuid, the integer family, numeric, or a user-defined type such as an enum. Boolean, date and time, JSON, geometric, and interval columns cannot be used as state columns.

A workflow can be enforced or non-enforced. Enforcing it writes two things into the database:

1. A CHECK constraint on the state column, restricting it to the defined states.
2. A row-level trigger firing BEFORE INSERT OR UPDATE OR DELETE, which rejects any transition that has not been defined.

Enforcement therefore covers the whole row lifecycle, not just updates. A transition with no predecessor state defines which states a row may be created in; a transition with no successor state defines when a row may be deleted.

Enforcement is normally the right choice, because it holds regardless of how the row is written — through CYPEX, through psql, or through any other client. Choose a non-enforced workflow only when the underlying model must not be modified, for example when the table is owned by another system.

A state change is more than a permitted pair of values. Each one can define:

• An ACL — the PostgreSQL roles allowed to perform the transition. An empty ACL means any role may perform it. Role membership is evaluated inside the trigger with pg_has_role, so the restriction holds outside the application as well.
• A pre-function — a function evaluated before the transition, returning the state the row should end up in. This is how conditional routing is expressed.
• A post-function — a function executed after a successful transition, for side effects such as notifications or derived bookkeeping.
• A validity period — a time range outside of which the transition cannot be used, for approval windows and similar constraints.

Only one state change may exist per ordered pair of states.

CYPEX separates three access decisions. They compose: a request must pass all three.

A Capability is what a role is allowed to do — SELECT, INSERT, UPDATE, DELETE, EXECUTE. Capabilities are PostgreSQL grants, and CYPEX assigns them at the query level so that the underlying tables stay untouched. The admin panel provides visual tooling for this; the result is ordinary GRANT statements you can inspect in the catalog.

Capabilities drive default rendering. If a role has no access to a query, the generated application contains no elements for it — not hidden elements, but none at all. Two people opening the same database may therefore see substantially different applications.

A Data Scope is which rows a user may act on. It is decided by Organization membership and enforced by PostgreSQL Row-Level Security, keyed on the org_id claim in the request JWT.

Grants alone give no row-level control. RLS acts as a mandatory filter: a table might hold a million people, and one policy can restrict user A to a subset that user B never sees. PostgreSQL applies the filter regardless of the query issued.

Capability and Data Scope do not substitute for one another. A role with broad capabilities but no organization mapping sees no tenant rows. An organization member with read-only capabilities sees every row in their organization and can change none of them.

Warning

Assign RLS policies to PUBLIC, not to individual roles.

CYPEX reads through views, and a view is evaluated with the privileges of its owner. A policy that names a specific role is checked against the view owner rather than the requesting user, so it appears to have no effect. Assigning the policy to PUBLIC makes it apply on every read path. Attaching a policy to the wrong grantee is one of the most common mistakes when adding RLS to an existing PostgreSQL schema.

See Capabilities vs Data Scope for the full mental model, the generated RLS SQL, and the roleType API contract.

v2.0.0 introduced terms with no v1.9.x equivalent. Each is defined once, precisely, in the Glossary; this section explains how they fit together.

“Module” is a deprecated synonym for Schema. Both refer to the same PostgreSQL concept: a namespace grouping tables, views, and functions. Schema Access is new in v2.0.0: it is the admin page that grants schemas to organizations. The catalog column cypex.t_module.schema_name and related route paths still use the older “module” identifier; new prose should say “schema”.

See Schema Access for that admin page.

An Organization is the unit of multi-tenancy: one PostgreSQL database serving several tenants, isolated by RLS rather than by application filtering. Roles are mapped to organizations, and the resulting organization identifiers travel in the JWT. Upgrading from v1.9.x creates a Default Organization and assigns every existing schema and role to it, so single-tenant installations keep working without a backfill.

See Organizations for the conceptual reference and the JWT-to-RLS plumbing.

A Connector is CYPEX’s governed mechanism for calling an External API — a third-party REST API outside CYPEX’s own database — and mapping its response into a shape an application page can render. Every connector is gated by explicit enablement, an outbound host allowlist, and organization-scoped credentials, and every execution is audited.

A connector is a sibling data source to CYPEX’s own PostgREST-backed views, not a replacement for them. See Connectors for the architecture, and the REST API for CYPEX’s own generated API.

SSO (Single Sign-On) is federated login to an external IdP (Identity Provider), handled by a standalone SSO Gateway service that runs alongside — not instead of — local username and password login. OIDC (OpenID Connect) is the protocol the gateway speaks. A single generic configuration federates Google, Microsoft Entra, Auth0, Keycloak, or any spec-compliant provider, with no provider-specific code.

See SSO for the architecture and the OIDC setup guide for the walkthrough.

The relational model is the foundation of every CYPEX application, and not all models generate equally well. This section covers what to do and what to avoid.

Give every entity a single-column key. See Identifying columns above for the reasoning: composite keys cannot identify a row, and row identity is what forms, sub-forms, links, and chart interaction all depend on.

Add an id column even where the data model does not strictly require one.

A three-valued column needs a three-valued input. CYPEX handles this for booleans: when a boolean column is nullable, the generated checkbox cycles through unset → true → false and renders the unset state as indeterminate. When the column is NOT NULL, the same element behaves as a plain two-state checkbox and never writes NULL.

The decision that matters is therefore in the schema, not in the interface. Mark a boolean NOT NULL — with a default — when “unknown” is not a meaningful value for it, and leave it nullable when it is. Text columns follow the same rule: an empty text input submits NULL, so a column that must distinguish “empty” from “not provided” needs an explicit constraint or a default.

Circular foreign keys are awkward in web applications. Consider two tables that reference each other:

1
2
3
4
5
6
7
8

test=# CREATE TABLE a (id int UNIQUE);
CREATE TABLE
test=# CREATE TABLE b (id int UNIQUE);
CREATE TABLE
test=# ALTER TABLE a ADD FOREIGN KEY (id) REFERENCES b (id);
ALTER TABLE
test=# ALTER TABLE b ADD FOREIGN KEY (id) REFERENCES a (id);
ALTER TABLE

Neither table can now accept a row:

test=# INSERT INTO a VALUES (1);
ERROR:  insert or update on table "a" violates foreign key constraint "a_id_fkey"
DETAIL:  Key (id)=(1) is not present in table "b".
test=# INSERT INTO b VALUES (1);
ERROR:  insert or update on table "b" violates foreign key constraint "b_id_fkey"
DETAIL:  Key (id)=(1) is not present in table "a".

PostgreSQL solves this with INITIALLY DEFERRED constraints, but CYPEX rewrites query text, so a deferred insert order has to be arranged by hand. Avoid circular dependencies where the model allows it.

CYPEX maps PostgreSQL types to element types. The mapping determines which input and display element a column receives by default:

Two consequences are worth noting.

interval has dedicated element types. It is not rendered as free text, and values round-trip through the interval input without manual formatting.

PostGIS geometry and geography columns are recognised as spatial data and bound to GeoJSON elements, not degraded to text. See GIS data for what CYPEX does with them.

Array columns are supported: an array of a text-like type is bound to the array text elements rather than the scalar ones.

CYPEX generates applications quickly. It does not make slow queries fast. The usual PostgreSQL discipline applies:

• Index the columns you filter and sort on
• Index both sides of a join
• Enable pg_stat_statements and review it regularly
• Deploy monitoring — pgwatch covers this
• Materialize large aggregations rather than recomputing them per request
• Avoid expensive queries behind interactive elements

CYPEX limits result sets aggressively — tables fetch a page of rows at a time, not the whole relation — which handles the common case. A table fed by an expensive query is still slow if the underlying indexes are missing.

Test with representative data volumes. A model that behaves well against a thousand rows tells you very little about its behaviour against ten million.

Partitioning works with CYPEX, with one thing to know about how it is presented:

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

BEGIN;

CREATE TABLE t_timeseries (
	d		timestamptz	NOT NULL DEFAULT now(),
	sensor		text		NOT NULL,
	temperature	numeric		NOT NULL
) PARTITION BY RANGE (d);

CREATE TABLE t_timeseries_2020
	PARTITION OF t_timeseries
	FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

CREATE TABLE t_timeseries_2021
	PARTITION OF t_timeseries
	FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE t_timeseries_2022
	PARTITION OF t_timeseries
	FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

COMMIT;

The model builder shows only the partitioned parent table. Individual partitions are filtered out of the entity list and the ERD, which is what you want — you model against t_timeseries, not against each yearly child.

PostgreSQL versions differ in their partitioning behaviour, particularly around index creation and constraint propagation. Verify the behaviour of the version you run, and be conservative about combining workflows with partitioned tables: enforcement writes a constraint and a trigger onto the table, and how those propagate to partitions depends on the server version.

CYPEX works with PostgreSQL FDWs and detects them — a foreign table is flagged as such in the model builder rather than being mistaken for a local table. Several capabilities do not extend across the wrapper:

• No workflow support. Constraints cannot be enforced on the remote side, triggers cannot be reliably deployed there, and the remote structure can change without notice.
• No history tracking. Changes made on the remote side cannot be captured.
• No reliable foreign keys.

FDWs are therefore best used as read sources, or as write targets where the wrapper supports writes and you accept that integrity is the remote system’s responsibility.

The foreign table above was created as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE EXTENSION postgres_fdw;

CREATE SERVER pgserver
	FOREIGN DATA WRAPPER postgres_fdw
	OPTIONS (host 'localhost', dbname 'cypex');

CREATE USER MAPPING FOR public
	SERVER pgserver
	OPTIONS (user 'postgres');

CREATE SCHEMA sample;

IMPORT FOREIGN SCHEMA public
	FROM SERVER pgserver
	INTO sample;

SELECT * FROM sample.t_vendor;