bcgov / epic.track

Integration of formsflow.ai to EPIC for administration support

EAO Project Tracker

Environment Assessment Office Project Tracker

Solution Objectives

Prototype a modern and digital project tracker, compliance and enforcement tool using low code and open source solution.

Determine the value an feasibility of this solution.

Solution Constraints

Nbr	Constraints
1	The solution should be deployed in Openshift environment
2	The solution should use low code tehnology stack
3	The solution should use open source technologies

Solution

Use formsflow.ai which is a completely free and open-source framework that integrates intelligent forms, decision making workflows, and powerful analytics.

Solution Components

Here is a snapshot of various solution components for this solution

Nbr	Component	Description
1	Staff	The users who access the system for faily work.
2	Identity Service	Service that provides authentication and authorization for staff identity.
3	Electronics form	Provides the capability to create online forms quickly and easily with an intuitive low code form designer with advanced capabilities including data validation, conditional fields, file uploads, and signatures.
4	Decisions/Processing Workflows	Provides the capability to design and deploy automated and human-centric workflows.
5	Insights	Provides the capability to visualize data relevant with customizable dashboards. Track submission metrics and derive insights from submitted form data.
6	Line of Business Systems	These are the internal systems of the organization that is either created as part of the solution or solution integrate.

Solution Architecture

Here is the overall architecture for the solution

formsflow.ai components

This solution make use the full set of components provided by formsflow.ai

Nbr	Component	Description
1	Web	Web Portal for staff.
2	Analytics	Redash based Analytics component to provide insights and dashboards.
3	Electronics form	Form.io based form manager that provides the capability to create online forms quickly and easily with an intuitive low code form designer with advanced capabilities including data validation, conditional fields, file uploads, and signatures.
4	BPM	Camunda based process automation engine that provides the capability to design and deploy automated and human-centric workflows.
5	APIs	Python based APis integrate all other components with in the formsflow.ai.
6	Identity Service	Keycloak for identity management and SSO across formsflow.ai components.

Additional Components

Nbr	Component	Description
A	IDIR Identity Servuce	BC Gov SSO via OIDC's Keycloak for authentication. Staff users can authenticate with IDIR.
B	EAO Reporting API	Python](https://www.python.org/) based APis used to integrate between formsflow.ai solution components and the reporting db. Access to APIs are secured by OIDC tokens from Keycloak.
C	Central Database	PostgreSQL based database store information.

Technologies Overview

formsflow.ai

Use formsflow.ai which is a completely free and open-source framework that integrates intelligent forms, decision making workflows, and powerful analytics as the core solutoin component. This will be further customized and extended to meet the prototype requiremnts.

Central Database

PostgreSQL is used as central database for the project due to the large adoption on BC Gov projects.

This decision is only applicable to the additional solution components. formsflow.ai comes with its own set of databases.

APIs

Python was selected as the technology to develop APIs required for integration between formsflow and additional solution components.

Staff Identity

BC Govt uses IDIR as the identity for their staff. BCdevexchange already setup a Keycloak to provide IDIR based authentication for SSO. The Keycloak instance installed as part of formsflow.ai is updated to add BCdevexchange Keycloak instane as a Identity Provider.

epic.track's Issues

As a admin user, I want to View Only details on a project work.

Sync API issue when updating engagements

When updating engagements using sync API it causes AttributeError: 'InstrumentedList' object has no attribute 'as_dict' exception.

Project tracker gantt chart - handle empty data set

Project tracker gantt chart - handle the case where no data is available. Right now it is showing an error.

Indigenous Grant Tracker

Event Date Caculation to be implemented

Ops Tracker

Correct the staff_id relationship in engagements to staff table instread of work_staff_role

Optimize the form data sync functionality in reports-api to process nested structure

Overview
The current function handles two levels of nesting of json structure. This can handle inserting data to a parent table and multiple insertion to the child tables with the parent's primary key.
The change we are looking for is that the parent itself will be a collection of items and each item in that collection will have a sub object which has to be inserted to the child table based on the immediate parent table entry's primary key.

eg:
{ "works": { "project_id": 1, "id": "", "current_phase_id": 11, "ministry_id": 1, "title": "Test1", "federal_involvement_id": 1, "start_date": "2022-06-09T00:00:00+05:30", "anticipated_decision_date": "", "decision_date": "", "short_description": "", "long_description": "", "is_pcep_required": False, "is_cac_recommended": False, "work_short_status": "", "work_status_stoplight": "", "is_active": False, "is_completed": False, "eao_team_id": "", "responsible_epd_id": "", "work_lead_id": "", "ea_act_id": 3, "work_type_id": "6" }, "engagements": [ { "title": "Early Engagement - PECP", "start_date": "2022-06-08", "end_date": "2022-07-08", "work_engagements": { "phase_id": 11, "id": "", "project_id": 1, "work_type_id": "6", "engagement_id": "" }, "id": "" }, { "title": "Proponent Time: Project Description - PECP", "start_date": "2022-09-07", "end_date": "2022-10-07", "work_engagements": { "phase_id": 12, "id": "", "project_id": 1, "work_type_id": "6", "engagement_id": "" }, "id": "" }, { "title": "Readiness Decision - PECP", "start_date": "2023-09-08", "end_date": "2023-10-08", "work_engagements": { "phase_id": 13, "id": "", "project_id": 1, "work_type_id": "6", "engagement_id": "" }, "id": "" }, { "title": "Process Planning - PECP", "start_date": "2023-11-08", "end_date": "2023-12-08", "work_engagements": { "phase_id": 14, "id": "", "project_id": 1, "work_type_id": "6", "engagement_id": "" }, "id": "" }, { "title": "Proponent Time: Application Development - PECP", "start_date": "2024-03-08", "end_date": "2024-04-07", "work_engagements": { "phase_id": 15, "id": "", "project_id": 1, "work_type_id": "6", "engagement_id": "" }, "id": "" }, { "title": "Application Development & Review - PECP", "start_date": "2027-03-09", "end_date": "2027-04-08", "work_engagements": { "phase_id": 16, "id": "", "project_id": 1, "work_type_id": "6", "engagement_id": "" }, "id": "" }, { "title": "Proponent Time: Revised Application - PECP", "start_date": "2027-09-06", "end_date": "2027-10-06", "work_engagements": { "phase_id": 17, "id": "", "project_id": 1, "work_type_id": "6", "engagement_id": "" }, "id": "" }, { "title": "Effects Assessment - PECP", "start_date": "2028-09-06", "end_date": "2028-10-06", "work_engagements": { "phase_id": 18, "id": "", "project_id": 1, "work_type_id": "6", "engagement_id": "" }, "id": "" }, { "title": "Recommendation - PECP", "start_date": "2028-12-26", "end_date": "2029-01-25", "work_engagements": { "phase_id": 19, "id": "", "project_id": 1, "work_type_id": "6", "engagement_id": "" }, "id": "" }, { "title": "Referral/Decision - PECP", "start_date": "2029-02-05", "end_date": "2029-03-07", "work_engagements": { "phase_id": 20, "id": "", "project_id": 1, "work_type_id": "6", "engagement_id": "" }, "id": "" } ] }

Here the engagements itself is a parent data entry and each engagement should have another child data entry for the "work_engagement" table with the engagement_id.

Engagement Calendar

Custom event calendar

Overview
Custom event calendar needs to be created. It should look like the one given below

Integrate EventCalendar visualization type with engagements populated in database

Overview
Show engagements populated in database in the redash. Show today's events always with the ability to switch to different views such as month, week or day. Try to show a tooltip kind of UI for showing more information on events.

Analyse a suitable package for implementing event calendar for showing Engagements

Show the engagement in a Calendar like google calendar where we can see the various events on a day or span across multiple day. It should be shown along with other reports in redash.
This calendar report should be additional visualization in redash.

Build Formflow component container images to Openshift Tools environment.

Build formflow-bpm, formsflow-forms, formsflow-anlaytics, formsflow-web, formsflow-webapi container images to EPICFLOW Tools Namespace

Add migration script of new tables for Engagements

This require two tables to be created

• engagements :- required to store all engagements.

• work_engagements :- stores the engagement work phase relation

Given below is the initial draft of the schema

Set Up Prod Environment

Events - Prevent unwanted updates

Overview

Completed events are not supposed to be updated afterwards. In order to prevent it, we can add a additional backend validations to check the is_complete flag of the events in database against the ones that are coming from frontend.

Highlight the current stage in the bpm diagram when events are all updated at once

Overview
Currently, the expectation is that when each event is updated the stage will be changed to accordingly. But if the update happen all at once, the diagram will be stuck at the first phase as it checks for the actionType parameter value.

Expectation
When bulk update happen, the diagram should point to the current phase according to the actionType parameter value

Lets check for greater than at each stage and if it is pass it to the next phase and check the same at each phase.

Submitted By in the history is empty

Review / Resolve Res Allocation

PECP Event related logics to be implemented in the Event Grid

Overview
Given below are some of the rules to be added specific to PECP

• If you add PECP and additional box to enter the number of days to be shown and end dates should be visible but not editable
• It won't have any impact on the start or end date of the start and event event respectively.
• If the PECP end dates goes beyond the end date of the phase, show a warning saying the situation.
• Whenever you add a PECP event, a PECP Close event would automatically be added with approximate date of PECP end

Lets use common phrasing

TL;DR 🏎️

Teams are encouraged to favour modern inclusive phrasing both in their communication as well as in any source checked into their repositories. You'll find a table at the end of this text with preferred phrasing to socialize with your team.

Words Matter

We're aligning our development community to favour inclusive phrasing for common technical expressions. There is a table below that outlines the phrases that are being retired along with the preferred alternatives.

During your team scrum, technical meetings, documentation, the code you write, etc. use the inclusive phrasing from the table below. That's it - it really is that easy.

For the curious mind, the Public Service Agency (PSA) has published a guide describing how Words Matter in our daily communication. Its an insightful read and a good reminder to be curious and open minded.

What about the master branch?

The word "master" is not inherently bad or non-inclusive. For example people get a masters degree; become a master of their craft; or master a skill. It's generally when the word "master" is used along side the word "slave" that it becomes non-inclusive.

Some teams choose to use the word main for the default branch of a repo as opposed to the more commonly used master branch. While it's not required or recommended, your team is empowered to do what works for them. If you do rename the master branch consider using main so that we have consistency among the repos within our organization.

Preferred Phrasing

Non-Inclusive		Inclusive
Whitelist	=>	Allowlist
Blacklist	=>	Denylist
Master / Slave	=>	Leader / Follower; Primary / Standby; etc
Grandfathered	=>	Legacy status
Sanity check	=>	Quick check; Confidence check; etc
Dummy value	=>	Placeholder value; Sample value; etc

Pro Tip 🤓

This list is not comprehensive. If you're aware of other outdated nomenclature please create an issue (PR preferred) with your suggestion.

Add Extension and Suspension milestone to every phase

Restructure the milestones model in database for integrating engagements

Overview
The milestones table contains milestones per phase. If we can incorporate the potential engagements per phase as milestone and differentiate those from events, we will be able to auto populate events and engagement from the milestones when new work is created.

Set Up Test Environment

Completed

Analyse the dev environment to prepare a resource utilization metrics

Overview
Make use of Sysdig tool to analyse the current dev environment to understand detailed resource requirement of the solution in both Test and Prod environments.

The loading text disappeared when submitting the task. It shows [Object Object] instead

Overview

When you submit a task from the "Task" menu, it shows [Object Object] instead "Loading".

Fix the issue with camunda workflow to change the phases based on the actionType parameter from frontend

Handle workflow failure to be created as incidents

Overview
Currently when the workflow fails at some point, no task will be created an there is no way to retry it. There is a feature in camunda called "Asynchronous Continuation" using which we can resolved the above mentioned issue.

Audit log of form submission shows the same data in all logs

Overview
Currently in the formsflow-ai, we can see the history of submission under Tasks-> {Individual Task}. Here, I can see the status, modified time and modified by changes in each submission, but the submission data remains the latest in all rows.
The possible cause is that is each submission, internally it needs to create separate submission. Need to take care of that in the camunda side.

Implement backend validations for forms for sync-api

Overivew
Implement back end validation for the form submission. We can create validation classes for each forms which can be triggered upon calling the sync-api. If this validation fails, the failure will trigger an incident in camunda. we can traced later to analyse what went wrong.

This is primarily to prevent tampering the html or javascript to change values in the front end which is prevented by front end validations.

Timeline extension event

Overview
At times when more events are added or in certain situations the phase anticipated end date may not be the date in which the activities are actually going to end, we may have to extend the end date of a phase by adding a new type of event called TimeLine Extension(Extension).

• When you add "Extension", the number of days from the current end of the phase to be added should be mentioned in a box
• The Extension event will be the end event of the phase from then
• You can add multiple Extension events to extend the phase end date
• This will have impact on the rest of the events that comes after this one

Change to be made in the way currently camunda writes the submission history

Currently whenever a phase change happens, camunda create a new submission. But we will have to change it so that whenever an actual end_date gets added, it needs to trigger a new submission and record the status field like the one below

"Eearly Engagement(Accept IPD & Engagement Plan)"

Misalignment in engagement calendar

Overview

Please check the Calendar image given below. There row are not equally sized.

Timeline Suspension Event

Overview

Similar to Timeline extension event this event will also cause the subsequent dates of events to be pushed to later dates. When Suspension event is added, it is implied that the the EAO would be waiting for some external matters like Proponent taking some more time for certain things to be completed. EAO will be idle during the time.

Integrate the new FormBPMFilteredDataPipeline listener

We had an issue previously that camunda stores entire form data in database and some of the database fields were not having enough storage to hold the entire data.
With this listener, we will be able to pass the variables which we want the camunda workflow to hold in memory throughout the lifecycle. This will enable the camunda engine to avoid occupying unwanted memory and storage.

Add missing topics

TL;DR

Topics greatly improve the discoverability of repos; please add the short code from the table below to the topics of your repo so that ministries can use GitHub's search to find out what repos belong to them and other visitors can find useful content (and reuse it!).

Why Topic

In short order we'll add our 800th repo. This large number clearly demonstrates the success of using GitHub and our Open Source initiative. This huge success means it's critical that we work to make our content as discoverable as possible. Through discoverability, we promote code reuse across a large decentralized organization like the Government of British Columbia as well as allow ministries to find the repos they own.

What to do

Below is a table of abbreviation a.k.a short codes for each ministry; they're the ones used in all @gov.bc.ca email addresses. Please add the short codes of the ministry or organization that "owns" this repo as a topic.

That's it, you're done!!!

How to use

Once topics are added, you can use them in GitHub's search. For example, enter something like org:bcgov topic:citz to find all the repos that belong to Citizens' Services. You can refine this search by adding key words specific to a subject you're interested in. To learn more about searching through repos check out GitHub's doc on searching.

Pro Tip 🤓

• If your org is not in the list below, or the table contains errors, please create an issue here.

• While you're doing this, add additional topics that would help someone searching for "something". These can be the language used javascript or R; something like opendata or data for data only repos; or any other key words that are useful.

• Add a meaningful description to your repo. This is hugely valuable to people looking through our repositories.

• If your application is live, add the production URL.

Ministry Short Codes

Short Code	Organization Name
AEST	Advanced Education, Skills & Training
AGRI	Agriculture
ALC	Agriculture Land Commission
AG	Attorney General
MCF	Children & Family Development
CITZ	Citizens' Services
DBC	Destination BC
EMBC	Emergency Management BC
EAO	Environmental Assessment Office
EDUC	Education
EMPR	Energy, Mines & Petroleum Resources
ENV	Environment & Climate Change Strategy
FIN	Finance
FLNR	Forests, Lands, Natural Resource Operations & Rural Development
HLTH	Health
IRR	Indigenous Relations & Reconciliation
JEDC	Jobs, Economic Development & Competitiveness
LBR	Labour Policy & Legislation
LDB	BC Liquor Distribution Branch
MMHA	Mental Health & Addictions
MAH	Municipal Affairs & Housing
BCPC	Pension Corporation
PSA	Public Service Agency
PSSG	Public Safety and Solicitor General
SDPR	Social Development & Poverty Reduction
TCA	Tourism, Arts & Culture
TRAN	Transportation & Infrastructure

NOTE See an error or omission? Please create an issue here to get it remedied.

work_engagement ids are not updating back in formio from camunda

As an admin user, I'd like to log in and land on a 'soft' landing page that tells me how to use the site

Add two more milestone types for timeline extension and suspension

Overview
Add two more milestone types as given below

• Extension
• Suspension

Add migration for this.

Backend Validations

Engagement Calendar - Engagements are being shown under different projects

Overview
Please check the image given below

Highlighted entry should be shown for FRTP, instead it is shown under different project

Preflight requests were not served properly for /staff/position endpoint

Set Up Dev Environment

Build Formsflow Container Images

Provision/ Adjust Resource Allocation for Development environment

Resource allocation adjustment to meet the perquisites of installing forms flow.ai

Add authentication, profiling to report-api

Reports API in the prototype didn't have proper authentication or profiling in place. This task is to add the authentication and profiling to the reports-api.

Setting up the dev instance for the purpose of demo

Optimize Customizations

Auto populate engagements while creating a new work

Overivew
Currently when you create a new work(by selecting the EAAct, WorkType and StartDate), the phases and events will get auto populated. Here, we should add another tab in the UI to show planned engagements. The logic that can be used to forecast the engagement is given below
Engagement title: phase_title + PECP
StartDate: phase_start_date+7 days
EndDate: StartDate+30 days
Status: Planned

There will be a staff who will be assigned to the corresponding engagements. This staff will be populated from the team members added in the "Team" tab.

Integrate Sysdig

Use the following Directions https://developer.gov.bc.ca/?q=sysdig

Design tuning to the Event Calendar tool

Overview

• Add more css styling to the event calendar to make it looks good.

• Show abbreviation of the engagement title instead of showing full title and show the full title on hover.