In this section we’ll discuss known bugs as well as features which are still missing and which might be implemented in the foreseeable future.

This section will discuss missing security related features which will be added to CYPEX in the future to make the product more comprehensive.

Currently it’s possible to create database roles in the admin panel. However, it’s not possible to assign those roles to other roles - yet. We’ll fix this in the future and make the feature more complete.

In the future we’ll provide an easy-to-use overview, to give developers a better way to keep track of permissions. In addition to a complete list, we’re planning to create a diagram.

The following view-related issues are known and should be kept in mind to ensure smooth operation of CYPEX. Note that “queries” in CYPEX are stored as views on the database level to ensure dependency tracking as well as security abstractions.

Currently CYPEX doesn’t use “WITH CHECK OPTION”. Why does it matter? Suppose you get a query that only shows “data in your country”. In case the query is simple, PostgreSQL will make it “auto-updatable” which means that you can INSERT, UPDATE, and DELETE. However, without the “WITH CHECK”-option you can theoretically insert data which cannot be seen later anymore (by adding data not in your country). This will be fixed in the future.

Since we’re using views to abstract the underlying data model from the GUI side of the app, you need to be aware that PostgreSQL will drop cascading objects. This is relevant because it can remove the data source needed by your app.

We are currently working on code to make dependency tracking easier and more transparent.

At the moment we don’t use “security barriers” views for efficiency reasons. We therefore recommend not using stored procedures which make extensive use of RAISE NOTICE. Also make sure that functions which have side effects or use RAISE NOTICE are checked and marked as NOT LEAKPROOF.

Currently the “interval” data type isn’t fully supported under every circumstance. Therefore “interval” is mostly seen as text and does not offer any additional functionality which might be desired.

We’re working to remove this limitation.

CYPEX supports GIS data. However, to render GIS data in the GUI, the query you are using has to provide the frontend with a GeoJSON.

There are currently two options to handle this:

• Create a GeoJSON as part of a query
• Use a ready-made GeoJSON column inside the underlying tables

Since GeoJSONs aren’t automatically generated by the default rendering process, you need to generate them as part of the query. The following queries contain examples which show how this can be done:

select
    inspection.id,
    tv.license_plate,
      tv.vehicle_model_id,
      tv.registered_country,
      ST_AsGeoJSON(gps_pos)::jsonb gps_pos,
      x.server_tstamp tracked_at,
      inspection.tstamp inspected_at,
      inspection.base_station_id,
      inspection.inspection_type_id,
      inspection.aggregate_status
    from backoffice.t_vehicle tv
        left join backoffice.t_vehicle_gps_device tvd
        on (tv.id=tvd.vehicle_id)
    left join lateral
      select
        g.id,
        g.gps_pos,
        g.server_tstamp
      from gps_track.t_track AS g
      where
        g.device_code = tvd.gps_device_id and
        g.server_tstamp > g.server_tstamp - interval ''1 week''
      order by g.server_tstamp desc LIMIT 1) x on true,
    lateral (
      select
        ti.id,
        ti.base_station_id,
        ti.tstamp,
        ti.inspection_type_id,
        ti.aggregate_status
      from backoffice.t_inspection ti
      where
        ti.vehicle_id = tv.id and
        ti.inspection_state = ''clean''
      order by ti.tstamp desc limit 1
    ) as inspection,
    backoffice.t_inspection_type tit
    where
      inspection.inspection_type_id=tit.id and
      tit.inspection_type = ''rent_out'' and
      (tvd.active is null or tvd.active)
    order by inspection.tstamp desc
SELECT
    json_build_object(
      'type', 'FeatureCollection',
      'features', data_danger_zone.tracks || ST_AsGeoJSON(data_danger_zone.*)::jsonb
    ) danger_zone_tracks
  from (
    select py.area_name as name,
      py.polygon as geom,
      jsonb_agg(ST_AsGeoJSON(pt.*)::jsonb) as tracks
    from (
      select vlt.license_plate, vlt.server_tstamp, vlt.gps_pos
  from  cypex_generated.v_vehicle_latest_gps_track vlt
      ) as pt
    JOIN (
      select a.*
      from
        backoffice.t_danger_area a,
        backoffice.t_area_type b
      where a.area_type_id=b.id and b.area_type = 'danger_area'
      and now() between a.active_from and a.active_until
    ) py ON ST_Intersects(py.polygon, pt.gps_pos)
    group by py.area_name, py.polygon
  ) as data_danger_zone

In this case, the ST_AsGeoJSON function did the magic. But here’s one more example:

with stations_fence as (
        select
          ST_Union(ST_Buffer(ta.gps, 50000)::geometry) as fence
          FROM backoffice.t_base_station tbs inner join backoffice.t_address ta
    on (tbs.address_id=ta.id)
      ), violated_tracks as (
        select
          jsonb_agg(json_build_object(
              ''type'', ''Feature'',
              ''geometry'', ST_AsGeoJSON(tt.gps_pos)::jsonb,
              ''properties'', json_build_object(
              ''license_plate'', tv.license_plate,
              ''registered_country'', tv.registered_country,
              ''tracked_at'', tt.server_tstamp
            )))::jsonb as tracks
        from
          gps_track.t_track tt inner join
          backoffice.t_vehicle_gps_device vd on (tt.device_code = vd.gps_device_id)
          inner join backoffice.t_vehicle tv on(vd.vehicle_id=tv.id),
          stations_fence
        where
          not ST_Intersects(stations_fence.fence, tt.gps_pos)
          and  tt.server_tstamp > tt.server_tstamp - interval ''1 month''
      )
      select
        violated_tracks.tracks || ST_AsGeoJSON(stations_fence.fence)::jsonb
    as violated_tracks
      from stations_fence, violated_tracks

Take note that in case of a default query (= “SELECT * FROM tab”), you don’t have to worry about INSERT, UPDATE, and DELETE. PostgreSQL knows that the view is auto-updatable and you don’t have to add additional code. However, this isn’t true in case of a query that generates a GeoJSON.

A trigger has to be added on top of the query (= view) to teach PostgreSQL how to modify data. Note this view isn’t auto-updatable anymore, and therefore the way back to the table has to be defined by developers.

The following listing shows how such triggers can be made:

CREATE OR REPLACE FUNCTION danger_area_fn() RETURNS TRIGGER
AS $$
  DECLARE
    _polygon backoffice.t_danger_area.polygon%type;
    new_return record;
  BEGIN
    if new.polygon isn't null then
      select ST_GeomFromGeoJSON(coalesce(new.polygon::json->'features'->0->'geometry', new.polygon::json)) into _polygon;
    end if;
    IF (TG_OP = 'INSERT') THEN
      INSERT INTO backoffice.t_danger_area(
        area_name,
        polygon,
        reason,
        active_from,
        active_until,
        area_type_id
      ) VALUES (
        new.area_name,
        _polygon,
        new.reason,
        new.active_from,
        new.active_until,
        new.area_type_id
      );
      select
        * into new_return
      from cypex_generated.v_backoffice_t_danger_area
      where id = currval('backoffice.t_danger_area_id_seq'::regclass);
      return new_return;
    END IF;
    IF (TG_OP = 'UPDATE') THEN
      UPDATE backoffice.t_danger_area SET
        area_name = new.area_name,
        polygon = _polygon,
        reason = new.reason,
        active_from = new.active_from,
        active_until = new.active_until,
        area_type_id = new.area_type_id
      WHERE id=new.id;
      return new;
    END IF;
    IF (TG_OP = 'DELETE') then
      delete from backoffice.t_danger_area where id = old.id;
    END IF;
    return null;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_danger_area_trigger
  INSTEAD OF INSERT OR UPDATE OR DELETE ON cypex_generated.v_backoffice_t_danger_area
  FOR EACH ROW
  EXECUTE PROCEDURE danger_area_fn();

If you want to know more about triggers in PostgreSQL, check out the PostgreSQL documentation..

At this point it’s not yet possible to create data models from scratch inside the admin panel. We’re working hard to fix this issue in the next release.

You’ll soon be able to create models from scratch and you’ll be able to define triggers on “queries” to make it easier to insert and update more complicated operations. You will also make better use of the information available inside the data model during default rendering.

At the moment, foreign keys are defined on columns. In case states are derived from a column, take all existing values from this column. However, you often may be in a 1:n relationship, with the workflow playing out on the “n” side of the foreign key relation.

In future releases we’ll allow the workflow to take all possible keys from the “1” side of the join as the “n” side might not contain all values known to the “1” side of the relation.

Alternatively you should be able to specify some kind of data source to fetch all possible states from the existing model (especially important in case you’re dealing with more transitive or more complex models in general).

At this point you can transition from one state to the other. However, what if state changes should only happen under certain conditions? Let’s take a look at an example: A person applies for a bank loan. The loan is only granted in case some more complicated calculations give the OK. Currently it’s possible to do this using triggers. However, in the future our team will integrate this with workflows more tightly. The idea is to use “pre-funcs” and “post-funcs”. A pre-func will be called shortly after leaving a state to determine the target state (= loan will be granted or rejected based on some other data).

In this section we’ll discuss bugs and missing features related to the graphical user interface designer.

At this point files have to be uploaded one by one. Multi-file uploads are currently not working. However, we’ll support this feature in the future.

At the moment CYPEX does not support HTML password fields. HTML Password fields do not show “letters” but use “dots” to hide the password. This will most likely be changed in the future.