The CYPEX administration panel gives users an easy way to administer and manage CYPEX as a whole. Many functionalities such as …

• The generation of applications
• Security management
• Data model definitions
• Workflow management
• Extension handling

… and a whole lot more are all handled by this important and easy-to-use interface. In this section you’ll:

1) learn how to use the tool
2) understand how it works and
3) find out how to achieve your goals easily and efficiently.

Once you have logged into CYPEX, you’ll find yourself on the dashboard. It gives you an overview of what’s going on inside your CYPEX deployment:

A single CYPEX deployment can handle a large set of applications running inside the same database. If you click on “Applications” in the menu on the left hand side, CYPEX will present you with a list of all those apps currently deployed on your system.

You can easily manage your applications from this menu. This includes but isn’t limited to:

• Generating new applications
• Incremental rendering for existing applications
• Launching applications
• Deleting obsolete apps
• Importing entire applications from other systems

Let’s walk through this important page:

The most important moment in the life-cycle of a CYPEX application is its creation. CYPEX will predict the application out of the underlying data module. To start the process, click on “GENERATE”. This will open the following screen:

The first thing you have to define is the name as well as the description of the application. Then, you need to configure which user the app has to be predicted for. This is important because depending on who you are, you’ll end up with a different application. CYPEX will only render elements you have access to. If you aren’t allowed to perform certain operations in your application (e.g. “sign contract”) CYPEX won’t generate tables, forms, buttons, etc. for that purpose. Therefore, selecting the right user is of vital importance. You also have to keep in mind that it’s often necessary to create many applications for the very same database. Just imagine a simple online shop: The backoffice application and the front app might operate on the same database, but those applications will be totally different because of permissions, requirements and so on.

Once you have decided on the user who will own the application, you need to select the “queries” you want to use in your application. Those queries that are selected will be used by the default rendering and app prediction code. Often, it’s necessary to render all existing objects. However, this is far from certain, which is why you have the option to selectively decide about what you want to render.

Finally, you can choose a layout, which is a vital part of the process: In large companies, the style of an app is of critical importance.

Once the new application has been generated, it will show up in the list as displayed below:

Next to each application in the list, you’ll find a couple of icons which are needed to manage the application and handle its life-cycle. Let’s walk through those icons step by step, and see how to make use of their functionality:

Edit icon

The name of your application can be easily added after its creation. You can also define the default language of the application:

Generate and add new page icon

Applications aren’t static. During a project, the first incarnation of an application might not be the final version. This is true for the graphical user interface as well as for the underlying data structure. It happens more often than not that after an application has been used for a while, new tables are added and it’s necessary to work incrementally on the application. The “generate and add new pages” icon can help you to predict pages and add them to your existing application. It’s possible to use the icon after the app has been used, or after it has already been heavily modified. Incremental rendering is an important method to reduce the effort needed to add new components to your solution. Incremental rendering will dramatically improve the life-cycle of your application. No application is ever static and therefore it’s vital to have the capability to add data sources after an app has become productive:

Note that you can only render pages which aren’t part of your application as yet. CYPEX will only provide you with those queries which have not been used, reducing the manual input needed to an absolute minimum.

Start application icon

The “play button” will launch the application you’ve just created. You can use this button to …

• Use the application
• Open the application to run the WYSIWYG editor

Export icon

CYPEX allows you to export an application. Why is this important? Development is generally not carried out on a production system. Therefore apps have to be transported from one CYPEX deployment to other systems. Import and export are needed to achieve exactly that.

Export format in JSON

Note that the entire definition of the graphical user interface (GUI) is a single JSON document which can easily be stored, read and modified as needed. You can also put this under version control (for Git) to ensure that changes are tracked.

Delete icon

If you don’t need your application anymore you can simply delete it:

What this does is to remove the JSON documents from the database: Note that it does NOT delete queries, tables, constraints, workflows, etc. - all you delete here is the JSON definition representing the graphical user interface. This is important to understand because CYPEX will not put your data at risk.

The next important feature of the admin panel is the ER editor. It allows you to check your ER model, define queries, handle workflows and a lot more. It will be the backbone to handle the data side of your database infrastructure:

You can zoom in and out of your ER model easily. Tables residing within the same schema will be grouped together within a box. Also: You can click on tables and views on the right hand side to navigate through the ER model quickly and easily:

When looking at a schema there are a couple of things to consider: First of all each relation has “three dots” on the right side of the box. Click on those dots to configure the relation (create workflows, audit the table and so on). All those features will be explained later in this document.

You can also see that CYPEX displays the relations between those tables. But there is more. Let’s take a look at those other icons in more detail:

The following symbols are used by the ER tool:

A primary key represents a unique-constraint which prevents NULL entries inside the table.

The next symbol indicates that a certain column is used by a CYPEX workflow. Remember, workflows are always defined on a column. The existence of the workflow is represented by the round symbol.

The “foreign key” symbol is on the “n” side of a “1:n” relationship. Please keep in mind that you should always index both sides of a foreign key relation to maintain efficiency.

The “finger print” column represents a unique field. In case a field is unique it can be used as an identification column.

Finally there is the “default lookup” symbol. It defines that the column in question will be used as the default text representation of the entity in question.

The core of every relational database is the concept of a table. If you have created a table, you’ll be able to see it in your ER window. Here you see that you can configure various aspects of a table:

The following entries are available (in case you’re dealing with a local table:

• Query generation
• Workflow management
• Default lookups
• Auditing
• Table details

Let’s walk through those components one by one.

The first thing to focus on is the idea of a default query: Often you simply want to see and edit a table. A default query is the best way to make that happen quickly. Fill out the form and assign the following permissions:

The list of permissions is important because it will provide vital information used during default rendering. If a user isn’t allowed to perform certain actions, the rendering process knows that those elements should not be generated in the first place. Without “INSERT” permissions, you won’t see input forms.

In CYPEX, you can have exactly one default query per relation. In case one exists already, the element will be disabled:

The next important menu entry is used to open the workflow editor. As previously stated, workflows are a core component of CYPEX. In a simplified world, tables translate to forms and tables - workflows are in charge of buttons, etc. One could argue that workflows actually add “life” to your otherwise pretty static application.

In CYPEX, workflows are associated with tables and are defined for a state column. Go to the ER model and click on the three dots. There you’ll find a menu entry allowing you to define a workflow:

To create a new workflow you need to choose the column which will contain the status information of the workflow. How does it work? The first important thing to understand is that a workflow basically (but not only) consists of states as well as state changes. States are valid entries inside the state column, while state changes are UPDATEs moving states from one value to another. The validity of those changes are guaranteed by the database engine.

First select the column which is going to be used to store states. CYPEX will provide you with values that are currently in the table and offer them as valid state entries.

Once you have selected the state field you can create the workflow without any additional precautions.

Let’s take a look and see what the workflow looks like. Note that if you didn’t click the “transitions to all” you’ll see the following picture:

States are listed but aren’t connected yet. You need to do that by hand.

However, you might want all states to be connected with each other. In that case you click the “transitions to all” checkbox. CYPEX will then automatically produce state changes for you.

As you can see, the flow chart is more complicated in this case, since all those state changes will be represented using directional arrows. The arrows can be modified easily.

Let’s get an overview of all action items available in the editor:

The headline section shows the name of the relation the workflow is being defined for.

The alignment icons are arranged as workflow elements horizontally or vertically on the playground.

“ACTIVATE” & “DEACTIVATE” are of key importance: Suppose you are changing a workflow as part of a design process. As long as a workflow is deactivated, you make all the changes to the CYPEX configuration tables (meta data) - the workflow isn’t enforced on the core relations. Only by pressing “ACTIVATE” will you enable PostgreSQL to apply CHECK constraints and triggers to actually ensure that what you see is actually in the data table. Keep in mind that activating the workflow can fail, in case invalid entries are included in the table. In that case, the data has to be cleaned first before the workflow can be enforced.

We have two ways to add new states to the workflow: The “NEW STATE” button and the plus icon shown in the state overview section.

The delete icon allows us to delete a state from the workflow.

Symbolizes the start of a workflow (preliminary state). This element isn’t a real state but shows the point when the non-existing element comes into existence.

The delete icon allows us to delete a state from the workflow.

Shows the end of the workflow (basically when the object is deleted)

Workflow states

Edit the state inside a workflow

To create state changes, you have to draw an arrow from one state to your desired target state. Make your changes visually, as shown in the image below:

Make sure that your state changes are documented and configured properly. By adding texts to your state, the system will know how to label your buttons and so on. Therefore it makes a lot of sense to put effort into properly describing your workflow. Naturally, changes can be made later. However, it’s good practice to use proper texts early in the process.

Once the work has been done it will look something like this: As you can see the business process as been properly modeled and can already be feedbacked by the end customer:

Often work has to be modified later. In such cases, use the “Edit state” machinery. It allows you to change texts later. Note that while it’s easy to change the texts, PostgreSQL enforces these workflows and therefore making changes on excessively long tables can be quite time-consuming (changing the text itself doesn’t matter):

Workflows serve a purpose. They are important to ensure that the application actually does what it’s supposed to do by limiting possible changes of values along the way. In the listing below, “pending” can result in “accept” or “reject”. Active can only be deactivated and deleted:

As you can see the GUI does not allow for changes that aren’t supported by the workflow. The way data is changed is therefore rigorously restricted to what may happen.

The existence of a workflow for a certain relation can easily be determined by looking at the symbols associated with the relation. The green symbol will show us the way:

Existing inactive workflow on column “status”

Existing active workflow on column “status”

Every relational model will contain a significant amount of “id” columns. The trouble is, nobody wants to see those numbers in the GUI. To ameliorate the situation, we enriched the data model by introducing the concept of “default lookups”:

CYPEX relies heavily on foreign keys and constraints. Select the column you want to see instead of ID’s. CYPEX will inspect your ER model and key relations so that the default renderer can automatically generate the app the way you want things to be.

The CYPEX development team has put a heavy emphasis on security as well as tracking. Our database experience tells us that security is a key concern for most enterprise customers. To reflect this need, CYPEX offers an easy way to audit tables and track all changes.

Enable the audit trail:

CYPEX will automatically deploy all the infrastructure to track changes made to your table. Those changes aren’t only tracked when the GUI or the GUI is used - even changes made to the underlying tables directly will be tracked to guarantee that no changes are lost.

Turning off this kind of tracking is equally easy:

Just looking at an ER model might not provide you with all the information you need. The “table details” features allows you to take a look at your data and inspect the relation in more detail:

In addition to the data model, you can also take a look at the data inside your table. Note that only a subset of data is displayed, to ensure good performance. The CYPEX model builder isn’t a replacement for a normal database client. If you want to search the table, modify it, etc., a standard database client such as DBeaver should be utilized.

The “data preview” section allows you to inspect the data and get a feeling of what is inside the database. Often this is needed to get a handle on things before writing a query.

Now we’ll focus on the entity section and discuss which icons are available and what those icons can be used for:

Focus on selected table in schema overview

Do not focus on the selected table in schema overview

Filter table overview

Schema is visible in schema overview

Schema isn’t visible in schema overview

If you don’t want the twitter_posts schema to be visible inside the schema overview, click on the toggle icon on the right side. The schema with the tables and views will disappear from the schema overview. Both tables and views will always appear and disappear when the toggle icon is used.

The next important section on the screen is related to queries. Raw tables are often what’s needed to build applications. In most cases you must pre-process data before it can be fed to the graphical user interface. This is done using queries. You will find a handful of icons within the editor which can be used to control this feature:

Add new query

Filter queries

Default queries are not editable

Edit custom query

Delete query

The following image contains an example showing what those menu entries might look like:

Let’s move on to a crucial topic: authentication. Security is of the utmost importance, which is why the CYPEX team has put great emphasis on protecting your data and applications.

On the CYPEX side users and roles are mapped to “login names”. This can be done in the “Users” section of the admin panel:

It’s important to mention that if a user is marked as “admin” it’s possible to use the application designer (WYSIWYG editor) to modify applications. In production this isn’t desirable and therefore you need to be careful with this setting.

A default language can be assigned to a user. Usually this language is English, but almost every language is possible, assuming that translations are provided. Also note that users are mapped to a database role. This is important, since the database role is what controls access to data. The login name (= email) is merely intended to handle CYPEX logins; permissions to data are managed on the lowest possible level (= PostgreSQL) to ensure consistency between the API and the app, and with direct database access.

Users are listed in a table. What you see below is the email address you can use to log in. Then you see the underlying username as well as the PostgreSQL role assigned to this specific CYPEX user. Finally, you see the default language of a user and can figure out if the user is active, or is marked as an admin user. Users can be edited using the “pen” symbol:

Users can easily be created in the GUI. Make sure that your user is properly mapped to the database role of your choice:

Users are available instantly - there is no synchronization of any sort needed to make this work.

Editing an existing user is equally simple. Click the edit icon and make the changes:

You can change the password and quickly set the user as active / inactive.

The next important step is to define roles. Remember, roles are connected to CYPEX users and represent real database side users:

Roles can easily be created in CYPEX:

While it’s possible to create roles quickly, it’s the task of the DBA to assign actual permissions to those roles. At the moment, this is done at the “query” level. Once a query is created, you can assign permissions to roles.

In the “Login Settings” you can define the logo shown during login:

You can upload any suitable logo and easily change the name of the page to adjust CYPEX to your company CI’s needs. Note that the logo you are pointing to has to be in the “public” folder of your webserver:

These settings will directly translate to the way the start page looks like. The following screenshot shows what the default layout is like in the standard configuration shipped to customers:

So far you have used local users and local authentication. While this is perfect for small scale deployment, it’s not viable for large companies featuring hundreds and maybe thousands of users.

The solution to this problem is “Single-Sign-On”. In CYPEX you can achieve this functionality using LDAP:

Create a new LDAP configuration and connect CYPEX to your LDAP infrastructure. Here’s how it works:

You need to fill out a form containing the settings. The LDAP configuration requires the following settings to establish a connection (The list also contains examples of each setting):

• URL ldap://ldap:10389
• Bind dn cn=admin,dc=cybertec,dc=at
• Bind Password **********
• Base dn ou=people.cd=cybertec, dc=at
• Search Attribute uid

To use LDAP in order to authenticate users and manage rights inside the application, you have three options:

DEFAULT ROLE:

means only one role on the CYPEX side for all LDAP users. If the username and password are correct, the user can log in, and gets assigned this default role.

POSTGRES ROLE IN LDAP ATTRIBUTE: is the LDAP entity attribute name for the CYPEX role name. If the user logs in and is defined as a cypex_admin in LDAP, the user is set for the attribute cypex_admin in CYPEX

MAP LDAP ROLE TO POSTGRES ROLE:

If the username and password are correct, then the user gets the first match from the mapped roles. In case the user has the LDAP user role and this is mapped to cypex_user, the user has the role cypex_user in CYPEX.

LDAP-Configuration option POSTGRES ROLE In LDAP ATTRIBUTE

The role attribute where the role is saved on the LDAP side.

You can map the CYPEX role to the LDAP role.

If the LDAP user is assigned to the “Application Designer” role in LDAP, map this role to the CYPEX cypex_admin role. Check the LDAP group mapping on login to make sure that mapping exists and the CYPEX role is allowed to login.

Mapping also has an effect on the way CYPEX handles things: If the CYPEX role mapped is an admin role, the user is considered a CYPEX admin.

This same mapping and authentication process works for the API as well:

“LDAP Role” is a text input field, and “Postgres Role” is a drop-down containing CYPEX user roles.

Finally, you can define the repositories you want to use to handle CYPEX extensions. Basically, you give CYPEX access to a Git repository which contains all of the extensions in a format accessible to CYPEX:

The configuration is straightforward - add the Git data and test the connection. CYPEX is then fully connected and you can easily add extensions to the system as needed:

What is of vital importance here is the use of the personal access token. Github has recently added some security precautions which make this feature necessary.

CYPEX allows users to audit tables. This menu entry will facilitate exactly that:

When a table is audited, the audit trail will be visible in the “Audit -> Tables” section. The following screenshot shows what that might look like:

PostgreSQL will capture all changes and display them in an easy-to-read format. One can see a “diff” of what has changed which allows you to gain an overview quickly:

Keep in mind that the audit trail can accumulate large amounts of data, and thus keeping an eye on storage usage is of vital importance to the system.

CYPEX not only audits the changes made to tables, it also keeps an eye on how users behave and which login activity can be observed. The “Users” section does exactly that. It contains vital information about who has logged in successfully, and who has failed.

The goal is to give you a quick yet comprehensive overview of the login activity related to your application:

The next big feature of CYPEX we want to focus on is the ability to upload files. Files are stored in the database. Storing files in the database has always been controversial. However, in this case it’s done to ensure that all data including the application itself can be saved using standard PostgreSQL backups. There is no need to back up the database, the application and those files separately - everything is in the same backup. In addition to that, files are handled in a transparent manner which brings countless advantages if you are dealing with workflows.

The file upload screen is easy to understand:

The core question is: Why would anybody use this feature? What are the benefits of such infrastructure? Here are some typical use cases:

• Display images
• Offer downloads
• Send as email attachments

Once a file has been uploaded, you can define user permissions to define who is allowed to access the file. As with all other data, permissions are handled by the database directly and are therefore identical within the entire stack (API, GUI, etc.).

Inside the WYSIWYG editor the application designer can use of such files:

Developers have direct access to CYPEX storage and files can be taken from there.

Here’s what the list of files might look like in your admin panel:

The upload facility is capable of handling reasonably sized files. In general the infrastructure is usually used for pictures as well as documents (PDFs, etc.) which are static in nature:

Various types of binary files are supported. The following list contains an overview of what is possible:

• audio
• document
• image
• other
• text

In the future, additional file types will most likely be added, in order to make this feature even more powerful.

The CYPEX data API is an integral part of the system. Every query is automatically exposed as an API endpoint. The infrastructure will honor access privileges and automatically keep the API up to date.

The general purpose of the API is to allow users to build custom apps which are hard to create with the builtin-WYSIWYG editor. In addition to that it allows for easier integration with other infrastructure components. It’s important to understand in this context that CYPEX isn’t “all or nothing” - it’s perfectly feasible to only use the API.

The purpose of the “Data API” section is to give users a simple method to test the API generated by CYPEX. As you can see in the screenshot below, a list of all endpoints is generated automatically:

These endpoints can be tested directly.

**Note: **When you’re testing the API, keep in mind that you aren’t working in a sandboxed environment. This is the real thing and changes will make it to the underlying database (assuming the API call is successful). Therefore caution is advised.

In this section we’ll dig into CYPEX extensibility and learn what can be done to make CYPEX even more powerful by adding code from external sources.

CYPEX allows users to define ready-to-use PostgreSQL extensions. Those extensions can be integrated into existing applications to simplify the model creation process and to automate as many steps as possible.

The way external extensions are supported is as follows:

CYPEX allows users to define a set of Git repositories which can be used to fetch extensions. It allows you to deploy everything from small SQL fragments and simple procedures all the way up to full-fledged complex data models.

The “Repository Applications” menu entry allows you to quickly load entire applications from the Git repository.

The search field allows you to search simultaneously in all available repositories. The numbers beside the repository name show how many applications are found, out of the total number of applications.

For each application, several different releases can be installed and uninstalled. That means you may decide whether to use the latest release of the application or not.

It’s easy to learn more about the application which has been deployed. CYPEX provides you with metadata concerning your application, as shown in the next image:

To install SQL fragments you can use the “Extensions” entry. Click on the “action” button and to easily deploy extensions.

There are countless extensions inside the default repository which can be used:

By default, the CYBERTEC repository for CYPEX is enabled. However, you can easily add more repositories as needed. For more information, see the section on Repository Configuration.