The CYPEX Model Builder: Database Design and Workflows
CYPEX's model builder provides a single interface for managing data models, workflows, and permissions. It automates several database tasks such as view generation, query creation, and audit logging, all of which are directly applied to the underlying PostgreSQL database.
Default and Custom Queries
A foundational step in the model builder is generating a default query for a table. This action creates a database view with a 1:1 mapping to the table's structure, which is then used to assign permissions and expose the data through the CYPEX API. This makes the table available both as a data source in the GUI and to external integrations. In the admin panel, relations with a default view are clearly marked with an icon.
For more specific data needs, the query builder allows you to define custom SQL queries. The editor includes features like auto-complete and validation that run against the live PostgreSQL backend, and it can preview the resulting data (limited to a few rows for performance).
Once a query is defined, you assign permissions to it. These permissions are enforced directly at the database level, and the query and its data source are available in the API and visual editor immediately, with no intermediate rendering step. Correct permission assignment is crucial, as an incorrect setup will make the data source unavailable in the graphical editor.
Understanding Workflow Mechanics
Workflows in CYPEX define the valid states and transitions for data in a table, using a designated column as the state column. Data integrity is enforced at the database layer using PostgreSQL triggers, which act as gatekeepers. These triggers only permit UPDATE statements that follow the workflow's defined transitions, ensuring no data can ever move to an invalid state.
CYPEX associates specific state transitions with user roles. This role-based control determines what different users can do within the application, and the GUI is generated from these permissions. For example, a job applicant may have the ability to create a new application (an INSERT), placing it in a "pending" state, and edit it (a transition from "pending" to "pending"). A hiring manager, however, is given the permission to approve or reject that application.
Since workflows are a constraint on the data model, they are versioned with the ER model, not with the GUI. This means that, unlike the visual layout, changes to a workflow are not tied to release management in the graphical editor. The data model and API always reflect the current, single version of the workflow, a deliberate design choice to maintain consistency and safety. This also implies that a workflow cannot be reverted by restoring an older GUI version.
Creating and Modifying Workflows
To create a new workflow, you begin by selecting a column to act as the state column. This column then becomes subject to database-side constraints. An option is available to make the workflow "fully connected," which analyzes the column's values to derive all states and automatically generate every possible transition, providing a quick starting point.
Iterating on workflow changes is a key part of the business analysis and design process, as changes take effect in real-time. A practical recommendation is to test workflow changes on smaller tables, as each change re-validates the constraints against all existing data.
The implementation details of the triggers are tied to PostgreSQL's mechanics. A trigger is based on a function, which can be shared by multiple triggers for better code abstraction. It is also important to note that PostgreSQL fires triggers on the same table in alphabetical order. For row-level triggers, the operation determines the return value: an INSERT must return NEW or NULL, a DELETE must return OLD or NULL, and an UPDATE typically returns NEW. Returning NULL from an INSERT or DELETE trigger causes PostgreSQL to ignore the operation.
Displaying Data and Auditing Changes
Displaying raw foreign keys is often not user-friendly. To address this, CYPEX introduces the concept of a "default lookup." You can designate a field on a table, such as a name, to be displayed in place of its ID throughout the GUI. When the renderer encounters a foreign key, it follows the relation and uses the associated lookup to show the human-readable value. This eliminates the need for custom joins or update triggers. For more complex display needs, "custom expressions" are recommended.
For tracking data history, auditing can be enabled per table. Enabling this feature deploys a changelog trigger on the table that records all INSERT, UPDATE, DELETE, and TRUNCATE operations into a central CYPEX system table. The audit data is stored in JSON format, which supports generic storage and simplifies search. The audit trail for these changes can be inspected in the admin panel.
The table details view provides a final point of overview, showing a list of the table's columns and a preview of the first 100 rows to give insight into the nature of the data it contains.
