3.1 Database section (model builder)

The CYPEX model builder offers a comprehensive solution to manage your data models, workflows and permissions with a single, easy-to-use tool. We guide you through all the basic steps– learn how to make your first applications quickly and efficiently.

CYPEX database section overview

3.2 Generate default query

Behind the scenes, CYPEX will generate a view and assign permissions. In case a table has a default view, this part of your ER model will be available in the CYPEX API. CYPEX therefore uses a default view not only to assign permissions and make the desired data set available as a data source in your GUI, but also to make data readily available in the API so that external tools can hook up to CYPEX.

A default view is a 1:1 representation of the underlying data. It has a name as well as a description. In the admin panel, CYPEX marks relations which carry a default view with an icon. The default view is also visible on the right side of the screen.

Generate a default query (database section)

3.3 Generate query

The query builder allows you to define custom queries. It provides a variety of features, which include auto-complete and automatic query validation against the real PostgreSQL backend. In the editor you will also be able to preview the data. Note that we only display the first handful of rows for efficiency reasons.

After the query has been completed, you can assign permissions to queries. CYPEX will assign them directly in the database to guarantee consistency between the API and the GUI.

Make sure to assign permissions correctly – otherwise the data source will not be available in the graphical editor. Also note that the query will be available instantly – you don’t need any incremental rendering to make it show up in the API and the visual editor.

Generate a query

3.4 Workflows

3.4.1 Workflow overview

A workflow is a description of a process made up of states and the transitions between those states. A state is a valid entry in a table. In CYPEX, we only allow column entries that correspond to valid states. The same is true for state changes (transitions). PostgreSQL uses triggers as gatekeepers. The triggers allow only UPDATE statements that satisfy the workflow to get through. This guarantees that data cannot escape the workflow in any way.

Limiting UPDATE statements according to the workflow ensures data integrity as well as data quality at all levels.

CYPEX associates state changes with roles. Why is that important? Let’s take a look at a practical example: Suppose person A applies for a job. Technically this means that this person can INSERT his application. It will then be in state “pending”. In case pending can have a state change from “pending” to “pending” we generally call it “edit”. Some other person might then approve the application or not.

GUI prediction

When CYPEX generates/ predicts the GUI, it takes those permissions and translates them to pages. Depending on who you are, you will see a different type of application based on the very same data model.

This kind of behavior is important: Just imagine a web shop. The backend application is totally different from the frontend side (which might just use the API and not utilize the CYPEX GUI builder all together).

Workflow general overview

3.4.2 Change workflow and see the update in the app

We allow users to quickly change workflows. The reason is that we have to keep the time needed to adapt to change as short as possible. Round trip times matter, therefore all changes happen in real time. Note that changes to the workflows are NOT related to the release management inside the graphical editor.

What you revert in case you bring an older release back is the visual representation – the data model is always available in a single incarnation.

It’s technically impossible to handle many different versions of the data model in a safe, consistent way. Workflows are part of the data model and therefore have to conform to the laws of the ER model rather than to the GUI. The same is true for the API which is always a reflection of the workflow. Keep in mind that the workflow has been implemented on the lowest level possible to ensure data integrity above all else.

Change the workflow and see the updates in the app

3.4.3 Creating a new workflow

The first thing you need to do is to select a column which can serve as a state column. This column will then be subjected to database-side constraints. Once you select the column, you may decide to make the workflow “fully connected”, which will derive all values from the column, determine the states and automatically create all possible state changes in one go. You can easily work from there.

Generate a workflow

3.4.4 Change an existing workflow

Changing workflows and testing them with the client is an integral part of the business analysis process. Especially during the design phase, quick iterations are of key importance. Ideally, you should build models with relatively small tables, as constraints keep being validated. It is therefore advisable to not make changes with billions of rows in your table.

How to change a workflow

3.4.5 Trigger function integration

PostgreSQL always bases a trigger on a function, which means that it can use the same trigger to serve multiple triggers at once. This feature allows for a great deal of code abstraction not available in other database engines.

Also keep in mind that PostgreSQL fires triggers in alphabetical order, which is important if you have more than one trigger defined on the same table. A standard row-level trigger has to return NEW or OLD depending on the operation.

Those are predefined variables which carry the row the trigger operates on. INSERT has to return NEW or NULL. If PostgreSQL returns NULL, it ignores the operation. DELETE has to return OLD (= row to be deleted) or NULL (in case nothing should happen). In the case of UPDATE, it usually returns NEW – which represents the row as it is, after the desired changes.

Workflow trigger functions

3.5 Database section default lookup

Displaying ID’s instead of human readable names is far from ideal. In a real application, users want to see text instead of ID’s. However, in a large data model it’s pretty cumbersome to manually change everything to readable text in the end user GUI, write joins or come up with some other tricks to work around this inherent relational idea.

The solution in the CYPEX world is the concept of “default lookups”. You can define a field for each table which CYPEX displays instead of the ID. The default renderer will inspect your model and automatically put names wherever it finds ID’s that do have a default lookup. By following your foreign key, the relation will be inspected.

In general, the GUI resolves the ID to ensure that you do not need any UPDATE triggers on joins.  In case the GUI needs to process the name in a more sophisticated way, we recommend the use of “custom expressions”.

Default lookups

3.6 Database section auditing

In case you enable auditing for a relation, CYPEX will deploy a changelog trigger on the desired table. We will track INSERT, UPDATE, DELETE as well as TRUNCATE. All changes found by the mechanism will end up in a CYPEX system table for later inspection. CYPEX stores data in JSON format to ensure generic storage and to make searching on the audit tables easier.

Check out the audit trail in the admin panel. The audit section
reveals your history and allows for efficient searches.

Auditing (Database section)

3.7 Database section table details

The table details section of your relation will reveal a list of columns. However, you can also take a look at the data itself. We display the first 100 rows in your table to give you a brief insight into which data is available in your database. It provides you with a short overview of your data.

Database section table details