This section will describe the most important aspects of CYPEX terminology.

In CYPEX you use existing SQL models to build new and powerful applications. The first term we need to discuss is the idea of an “entity”. We use the same semantics as in a standard relational model: an entity is a table which is tracked by CYPEX. All tracked entities will be part of the GUI prediction which is made based on the data structure. If an entity isn’t tracked by CYPEX, it won’t be included in that prediction.

It’s important to understand that you do not work directly with entities. You use an abstraction layer between entities and what CYPEX sees.

As already stated, entities are essentially tables in a relational model. However, this isn’t what you work with in the CYPEX GUI. Usually, a table doesn’t contain data the way you need it in the GUI. You need to define a query which will be in charge of fetching data from the table and then sending data to the end user. A query can be a subset of columns, a join or any other complex SQL statement needed to pre-preprocess data. In short: A query processes the data so that it can be shown in the GUI.

Workflows are the next step once the initial relational model has been built. The following terms are relevant:

• State columns
• States
• State changes

Let’s take a look at an example: An offer might follow a typical workflow. It’s created, edited, sent to the client and, hopefully, signed. An object has an optional “state column” which is only allowed to contain valid state entries (in our case “accepted”, “rejected”, “sent” and “created”). Changing between 2 states is what is called a “state change” - it’s any kind of action associated with an object.

Please note that states and state changes occur on the entity - not on the query - level. States are deeply associated with the underlying database model.

States and state changes can be either enforced or non-enforced. In case of enforced state changes, CYPEX will create triggers on the underlying tables to make sure that only valid changes can be made. Usually, enforced state changes should be chosen because they make sure that a data model cannot contain faulty data. However, in some cases it might also make sense to work with states that aren’t enforced by CYPEX. This is especially true if the underlying data model must not be modified for some reason.

An entity may have either no state column, or one state column. Combined or multiple state columns aren’t supported.

Database permissions are of great importance and are usually assigned on the query level to ensure that tables remain mostly unchanged.

CYPEX offers visual tooling to define these query permissions. However, setting permissions might not be enough. PostgreSQL supports “Row Level Security” which is an easy way to filter rows: A table might contain 1 million people (500.000 women and 500.000 men). User A might only be allowed to see women while user B is only allowed to see men. Depending on who you are, PostgreSQL will only return the subset of data you are permitted to see. Row-Level-Security can therefore be seen as a kind of mandatory filter added for a user.** In case you are using RLS (= Row Level Security), make sure that your policy is assigned to PUBLIC rather than to a normal user.** The reason for this is that a view will only honour an RLS policy if it’s assigned to “public”. This is because views in PostgreSQL are basically a separate security context. Assigning policies to the wrong entity on PostgreSQL is a fairly common mistake.

Permissions heavily impact default rendering. In CYPEX a GUI is created for a user or a group of users. In case a group of users doesn’t have access to a query, the GUI won’t contain those elements at all. In other words: Permissions drive the way default rendering is done at the most basic level. This also implies that 2 people accessing the same database might see totally different applications, depending on their permissions and security settings.

As previously stated, a relational model is the foundation of every CYPEX application. However, not all relational models are created equal. Some are more suitable for application prediction than others. In this section, you’ll learn more about how to write suitable relational models and what to avoid.

CYPEX contains the concept of “identity columns”. If you want to build an online form, CYPEX has to uniquely identify a row to ensure that the right things are updated. Identity columns usually represent some kind of ID. It’s important to understand that these keys must be single-column keys. In order to manage complexity and maintain good performance, CYPEX doesn’t handle composite keys. It therefore makes sense to ensure that every table (entity) has a synthetic key. Single-column keys aren’t only important if you want to create forms. They can also be important if you’re creating a dashboard: You can’t click into a chart if you can’t easily identify what it was that you clicked on.

We therefore suggest that you ALWAYS add an ID column (even if it’s not strictly necessary), for easier handling.

NULLs are a bit tricky. Often, a web GUI can’t distinguish between NULL and empty strings. This is especially difficult in the case of checkboxes.

The following types of modelling should therefore be avoided:

  ...
  column	boolean		NOT NULL,
  ...

In these cases, if CYPEX does not see any input, it sends NULL to the backend. This is also true for text fields. In order to be consistent across the platform, the same behavior is used for boolean fields.

Circular dependencies aren’t perfectly suited for web applications. The first question is: What is a circular dependency? Here’s an example:

    test=# CREATE TABLE a (id int UNIQUE);
    CREATE TABLE
    test=# CREATE TABLE b (id int UNIQUE);
    CREATE TABLE

In this case we have two tables. If these two tables reference each other, we’ll end up with a problem:

    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

The problem here is that you can’t insert data into any table without violating the other table’s constraint:

    test=# INSERT INTO a VALUES (1);
    ERROR:  insert or update on table "a" violates foreign key constraint "a_id_fkey"
    DETAIL:  Key (id)=(1) isn't 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) isn't present in table "a".

This problem can be solved by marking a constraint as INITIALLY DEFERRED or marking the entire transaction as such. However, CYPEX changes the content of a query. That means that if you want to use circular dependencies, you’ll need to adjust the code behind the scenes on your own. However, it’s usually better to avoid circular dependencies entirely, if possible.

Data types are the backbone of every relational model. CYPEX maps data types used by the relational model to GUI elements. Be aware that data types used by extensions are generally mapped to “text” because CYPEX doesn’t support obscure types.

Intervals are basically treated as text fields by CYPEX. This is important, as CYPEX does not yet contain full support for this data type.

Mind that CYPEX enables you to create applications quickly and efficiently - that doesn’t mean that applications will perform “quickly” by default. If you want to achieve good database performance, work through the following CYPEX performance recommendations:

• Index columns you are searching on
• Index both sides of a join
• Enable pg_stat_statements
• Deploy proper pgwatch2 monitoring
• Materialize large aggregations
• Avoid expensive live queries

CYPEX tries to avoid expensive queries whenever possible. Tables will only fetch a handful of rows - which greatly improves performance. However, if a table is fed by a fairly complicated query, performance might still suffer, particularly if indexing isn’t done properly. For that reason, we highly recommend that you frequently check up on pg_stat_statements.

Furthermore, we recommend testing CYPEX applications using representative amounts of data to ensure that performance is close to what you can expect in production.

Many people use PostgreSQL partitioning. It’s important to understand how this feature can be used in CYPEX. The following listing shows how partitions can be created:

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;

Please note that the model builder is only going to show the parent table in this structure:

Keep in mind that not all versions of PostgreSQL behave the same way when dealing with partitions. This is also true for index creation. You need to understand how your version of PostgreSQL behaves when it comes to partitioning and index creation. Thus we recommend carefully testing the application, and limiting the use of workflows in combination with partitions in general. Future versions of PostgreSQL may exhibit further differences, so it’s important to stay up-to-date on the changes in partitioning behavior in the latest version.

CYPEX supports PostgreSQL-style FDWs. However, there are some special cases which have to be taken into account when using FDWs. You should keep several limitations in mind:

• No support for workflows
  • Unable to enforce constraints on the remote side
  • Unable to deploy reliable triggers
  • Can’t rely on constant data structures on the rez|mote side
• No support for advanced auditing
  • Unable to reliably track changes on the remote side
• No support for reliable foreign keys

Therefore the only useful situation is to use FDWs as data sources, or as target tables (if this is supported by the FDW in general).

In the model builder, FDWs are shown as normal tables:

In this case, a FDW was created as follows:

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;

More features will be added in this area in future versions of CYPEX.