How to adopt Skills Matrix boards to account for multiple final statuses post release 140

In the past, a single "Final Status" was used for each Flow, stored in the final_status column. Now, you can select multiple statuses as the 'Final Status' for a Flow. To support this change, we’ve introduced a new column called final_statuses in the lr.report_status_flow table.

This column is in jsonb format and can contain none, null, one, or multiple values. If you're used to querying the final_status column, you’ll now need to adjust your queries to join with the final_statuses column instead.

Example: Retrieving Final Statuses from the Old Column

If you were previously using the final_status column, your query might have looked like this:

SELECT RS.workflow, 
       RS.id AS status 
FROM lr.report_status_flow RSF 
JOIN lr.report_status RS ON RS.uuid = RSF.final_status AND RS.workflow = RSF.workflow 
WHERE RSF.final_status ISNOTNULL

Example: Retrieving Final Statuses from the New Column

With the new final_statuses column, your query should be adjusted as follows:

SELECT RS.workflow, 
RS.id AS status 
FROM lr.report_status_flow RSF 
JOIN lr.report_status RS ON RS.workflow = RSF.workflow 
JOIN jsonb_array_elements_text(RSF.final_statuses) S ON RS.uuid::text = S.value 
WHERE RSF.final_status IS NOT NULL

This query will still return the list of Flows and their associated Final Statuses, but keep in mind that the new query can now return multiple results per Flow if there are multiple Final Statuses selected.

Important Notes:

When you modify existing queries or create new ones, be mindful of this change to avoid unintentionally duplicating data.
Ensure your queries correctly handle the multiple possible values in the final_statuses column to maintain accurate results.