Describe the task

The Status and Activity Reporting changes (per #809 and #685) are a major change to the functionality of the application. As such the rollout needs to be carefully managed, ensuring that the data in the system after the rollout matches the data present prior to the rollout.

We will have to nominate some key accounts to check before and after the rollout. Immediately before the upgrade we will capture activity reports (and potentially other reports) and use this data to verify the reports after the rollout.

NB there was a bug in the Activity Report that might cause the pre- and post-rollout reports to not line up (see #605 for details). In this we will have to exercise our best judgement, and compare reports against the activity logs.

Success Criteria

Pre rollout:

[ ] MUST ensure there are roll back procedures
[ ] MUST nominate accounts and specific Transition / SDGs cards per account to check Activity Reports for before and after the rollout
[ ] MUST capture Activity Reports for Transition / SDGs cards in nominated accounts prior to rollout

Post rollout:

[ ] MUST generate Activity Reports for nominated accounts with data matching the captured Activity Reports from before the rollout

Related Issues

809

685

Selection of Accounts and Transition Cards:

Account 1: South West Food Community Transition Card 1: Food Security South West Western Australia

Account 2: Moving Feast Transition Card 2: Moving Feast SDG Alignment Card Moving Feast

Account 3: Demonstration Account Transition Card 3: Disaster Resilience in the city of Greenville SDG Alignment Card 3 Disaster Resilience in the city of Greenville

Additional criteria: MUST matched User Documentation for reports @SishaMish how are you going with these? MUST convert all characteristics "ticks + no comment" to "no tick + More information"

DEV NOTE

Deploy at 19 of November, 11 am

DEV NOTE

Deployed. Working through changes in Activity Reports.

Case 1: Food Security in South West Western Australia

Characteristics beginning of period have changed value. Case in point, "1.2 Cultivate a passion for action" has changed from 40 entries to 41.

Verifying the count by hand (using the new changes log) returns 41 - matching the new value:

select count(*) 
from checklist_items 
inner join checklist_item_changes on checklist_items.id = checklist_item_changes.checklist_item_id
inner join initiatives on initiatives.id = checklist_items.initiative_id
where characteristic_id = 2
and initiatives.scorecard_id = 87
and starting_status <> 'actual'
and ending_status = 'actual'
and checklist_item_changes.created_at < '2022-01-01';

Running the count by hand against the old data model (using the activity log) return 40 - matching the old value:

select
  sum(case when to_status = 'checked' then 1 else 0 end) as checked_count
from (
  select
    distinct on (focus_area_groups.id, focus_areas.id, characteristics.id, initiatives.id)
    focus_area_groups.id as focus_area_group_id,
    focus_areas.id as focus_area_id,
    characteristics.id as characteristic_id,
    initiatives.id as initiative_id,
    events_checklist_item_checkeds.occurred_at,
    events_checklist_item_checkeds.to_status
  from characteristics
  inner join focus_areas on focus_areas.id = characteristics.focus_area_id
  inner join focus_area_groups on focus_area_groups.id = focus_areas.focus_area_group_id
  inner join checklist_items on checklist_items.characteristic_id = characteristics.id
  inner join initiatives on initiatives.id = checklist_items.initiative_id
  left join events_checklist_item_checkeds
    on events_checklist_item_checkeds.checklist_item_id = checklist_items.id
    and events_checklist_item_checkeds.occurred_at < '2022-01-01'
    and events_checklist_item_checkeds.to_status in ('checked', 'unchecked')
  where initiatives.scorecard_id = 87
  and initiatives.deleted_at is null
  and characteristics.id = 2
  order by focus_area_groups.id, focus_areas.id, characteristics.id, initiatives.id, events_checklist_item_checkeds.occurred_at asc
) as t;

The notable difference here is that the old report data is based on status going from unchecked to checked, whereas the new data is based on the status changing to 'actual'. We now need to work out which one is correct.

Inspecting the actual changes recorded indicates that the underlying data matches the reported, e.g. the following sql:

select
  checklist_item_changes.created_at,
  initiatives.name,
  checklist_item_changes.starting_status,
  checklist_item_changes.ending_status
from checklist_items
inner join checklist_item_changes on checklist_items.id = checklist_item_changes.checklist_item_id
inner join initiatives on initiatives.id = checklist_items.initiative_id
where characteristic_id = 2
and initiatives.scorecard_id = 87
and starting_status <> 'actual'
and ending_status = 'actual'
and checklist_item_changes.created_at < '2022-01-01'
order by initiatives.name, checklist_item_changes.created_at asc

returns:

         created_at         |                                                       name                                                        | starting_status | ending_status
----------------------------+-------------------------------------------------------------------------------------------------------------------+-----------------+---------------
 2018-10-19 04:30:50.737399 | Boyanup Farmers' Markets                                                                                          | no_comment      | actual
 2019-08-06 00:06:26.68205  | Brunswick Junction emergency food relief and and community garden                                                 | no_comment      | actual
 2018-08-01 08:26:56.60329  | Bunbury Community Garden Withers                                                                                  | no_comment      | actual
 2018-09-15 12:05:11.100354 | Bunbury Soup Van                                                                                                  | no_comment      | actual
 2018-08-01 08:19:15.836571 | Bunbury Urban Growers (BUG)                                                                                       | no_comment      | actual
 2018-10-19 04:23:50.536183 | Capel Twilight Markets                                                                                            | no_comment      | actual
 2018-09-26 03:44:18.830186 | City of Busselton, Community and Commercial services                                                              | no_comment      | actual
 2018-10-17 23:16:46.986914 | City of Busselton 'I'm Alert' Food safety training for Business                                                   | no_comment      | actual
 2018-08-24 04:59:06.575392 | Crunch&Sip                                                                                                        | no_comment      | actual
 2018-10-18 01:38:11.657373 | Dunsborough Anglican Church                                                                                       | no_comment      | actual
 2018-08-21 09:24:37.561393 | Dunsborough Community Garden Inc                                                                                  | no_comment      | actual
 2018-08-24 08:07:18.297921 | Dunsborough Swap, Shuffle and Share                                                                               | no_comment      | actual
 2019-08-06 00:52:22.818205 | ECU Community Garden                                                                                              | no_comment      | actual
 2018-06-02 03:00:04.967594 | Food Sensations for Schools                                                                                       | no_comment      | actual
 2018-09-06 09:26:06.084327 | Garden to Plate                                                                                                   | no_comment      | actual
 2018-09-15 09:19:35.073347 | Healthy Active by Design                                                                                          | no_comment      | actual
 2018-08-01 08:34:54.468783 | Jenoshuas Organic Orchard stall                                                                                   | no_comment      | actual
 2018-10-18 00:53:56.436707 | Just Eat It Movie Night Fundraiser                                                                                | no_comment      | actual
 2018-10-18 00:46:28.827871 | Leavers' Week Fruit and Veg                                                                                       | no_comment      | actual
 2018-08-24 05:21:46.548795 | Live Lighter                                                                                                      | no_comment      | actual
 2018-09-15 09:39:17.789632 | Manjimup Farmers Market                                                                                           | no_comment      | actual
 2018-08-21 09:18:32.054713 | Manjimup Produce Swap                                                                                             | no_comment      | actual
 2018-08-21 09:32:13.280875 | Margaret River Community Centre                                                                                   | no_comment      | actual
 2018-09-15 11:13:03.472917 | Margaret River Dried Grapes and Cowtown Enterprises                                                               | no_comment      | actual
 2018-09-15 11:41:09.211911 | Margaret River Farmers' Market                                                                                    | no_comment      | actual
 2018-08-21 09:47:34.29289  | Margaret River Organic Farm                                                                                       | no_comment      | actual
 2018-08-01 08:42:39.499574 | Pint Sized Plots                                                                                                  | no_comment      | actual
 2018-09-03 08:33:57.642279 | School Breakfast Program                                                                                          | no_comment      | actual
 2018-10-17 23:23:24.432338 | Shire of Augusta Margaret River Public Health Planning and Sustainable Economy                                    | no_comment      | actual
 2018-10-17 03:10:28.625315 | Shire of Collie Public Health Planning                                                                            | no_comment      | actual
 2018-09-15 12:24:19.819252 | Shire of Harvey projects                                                                                          | no_comment      | actual
 2018-10-18 01:45:07.666786 | Shire of Nannup                                                                                                   | no_comment      | actual
 2018-08-21 09:10:11.44918  | South West Aboriginal Medical Service nutrition and food security in Indigenous people, maternal and child health | no_comment      | actual
 2018-10-19 04:50:20.240526 |  South West Food Bowl                                                                                             | no_comment      | actual
 2018-08-24 08:14:22.097703 | Stellar Violets Life library living museum & gallery                                                              | no_comment      | actual
 2018-09-26 04:02:21.036657 | Stephanie Alexander Kitchen Garden Program                                                                        | no_comment      | actual
 2018-08-24 08:38:08.674554 | Swap Shuffle Share (SSS) Harvey                                                                                   | no_comment      | actual
 2018-09-15 10:59:46.253321 | The Bunbury Markets                                                                                               | no_comment      | actual
 2018-10-18 06:29:05.856805 | The People's Garden                                                                                               | no_comment      | actual
 2018-10-09 08:35:40.158895 | Thommo's Community Garden                                                                                         | no_comment      | actual
 2018-09-26 03:55:57.338572 | WA School Canteens Association                                                                                    | no_comment      | actual
(41 rows)

whereas a query using the old activity-based model:

select
  occurred_at,
  initiatives.name,
  to_status
from (
  select
    distinct on (focus_area_groups.id, focus_areas.id, characteristics.id, initiatives.id)
    focus_area_groups.id as focus_area_group_id,
    focus_areas.id as focus_area_id,
    characteristics.id as characteristic_id,
    initiatives.id as initiative_id,
    events_checklist_item_checkeds.occurred_at,
    events_checklist_item_checkeds.to_status
  from characteristics
  inner join focus_areas on focus_areas.id = characteristics.focus_area_id
  inner join focus_area_groups on focus_area_groups.id = focus_areas.focus_area_group_id
  inner join checklist_items on checklist_items.characteristic_id = characteristics.id
  inner join initiatives on initiatives.id = checklist_items.initiative_id
  left join events_checklist_item_checkeds
    on events_checklist_item_checkeds.checklist_item_id = checklist_items.id
    and events_checklist_item_checkeds.occurred_at < '2022-01-01'
    and events_checklist_item_checkeds.to_status in ('checked', 'unchecked')
  where initiatives.scorecard_id = 87
  and initiatives.deleted_at is null
  and characteristics.id = 2
  order by focus_area_groups.id, focus_areas.id, characteristics.id, initiatives.id, events_checklist_item_checkeds.occurred_at asc
) as t
inner join initiatives on initiatives.id = t.initiative_id
where to_status = 'checked'
order by initiatives.name, occurred_at asc

returns

        occurred_at         |                                                       name                                                        | to_status
----------------------------+-------------------------------------------------------------------------------------------------------------------+-----------
 2018-10-19 04:43:39.836    | Blackpoint Beef                                                                                                   | checked
 2018-10-19 04:30:48.478    | Boyanup Farmers' Markets                                                                                          | checked
 2018-09-15 12:05:04.273    | Bunbury Soup Van                                                                                                  | checked
 2018-08-01 08:19:15.836    | Bunbury Urban Growers (BUG)                                                                                       | checked
 2018-10-19 04:23:43.239    | Capel Twilight Markets                                                                                            | checked
 2018-09-26 03:44:09.984    | City of Busselton, Community and Commercial services                                                              | checked
 2018-10-17 23:16:44.724    | City of Busselton 'I'm Alert' Food safety training for Business                                                   | checked
 2018-10-09 08:50:44.505576 | City of Busselton Waste Strategy                                                                                  | checked
 2018-08-24 04:58:56.62     | Crunch&Sip                                                                                                        | checked
 2018-10-18 01:38:09.715    | Dunsborough Anglican Church                                                                                       | checked
 2018-08-24 08:07:15.851    | Dunsborough Swap, Shuffle and Share                                                                               | checked
 2018-10-09 09:12:28.293649 | Edible Garden Group                                                                                               | checked
 2018-06-02 03:00:04.967    | Food Sensations for Schools                                                                                       | checked
 2018-09-06 09:26:03.744    | Garden to Plate                                                                                                   | checked
 2018-09-15 09:19:34.253    | Healthy Active by Design                                                                                          | checked
 2018-08-01 08:34:51.672    | Jenoshuas Organic Orchard stall                                                                                   | checked
 2018-10-18 00:53:53.863    | Just Eat It Movie Night Fundraiser                                                                                | checked
 2018-10-18 00:46:26.1      | Leavers' Week Fruit and Veg                                                                                       | checked
 2018-08-24 05:21:43.634    | Live Lighter                                                                                                      | checked
 2018-09-15 09:39:15.323    | Manjimup Farmers Market                                                                                           | checked
 2018-08-21 09:18:29.509    | Manjimup Produce Swap                                                                                             | checked
 2018-08-21 09:32:09.804    | Margaret River Community Centre                                                                                   | checked
 2018-09-15 11:13:00.907    | Margaret River Dried Grapes and Cowtown Enterprises                                                               | checked
 2018-09-15 11:41:07.317    | Margaret River Farmers' Market                                                                                    | checked
 2018-08-21 09:47:31.69     | Margaret River Organic Farm                                                                                       | checked
 2018-09-15 11:26:12.118565 | Margaret River Organic Garden Trail                                                                               | checked
 2018-08-01 08:42:37.124    | Pint Sized Plots                                                                                                  | checked
 2018-09-03 08:33:55.464    | School Breakfast Program                                                                                          | checked
 2018-10-17 23:23:15.231    | Shire of Augusta Margaret River Public Health Planning and Sustainable Economy                                    | checked
 2018-10-17 03:10:28.094    | Shire of Collie Public Health Planning                                                                            | checked
 2018-09-15 12:24:17.506    | Shire of Harvey projects                                                                                          | checked
 2018-10-18 01:45:04.426    | Shire of Nannup                                                                                                   | checked
 2018-08-21 09:10:08.785    | South West Aboriginal Medical Service nutrition and food security in Indigenous people, maternal and child health | checked
 2018-10-19 04:50:17.488    |  South West Food Bowl                                                                                             | checked
 2018-08-24 08:14:13.532    | Stellar Violets Life library living museum & gallery                                                              | checked
 2018-09-26 04:02:18.521    | Stephanie Alexander Kitchen Garden Program                                                                        | checked
 2018-09-15 10:59:42.336    | The Bunbury Markets                                                                                               | checked
 2018-10-18 06:29:05.856805 | The People's Garden                                                                                               | checked
 2018-10-09 08:35:37.89     | Thommo's Community Garden                                                                                         | checked
 2018-09-26 03:55:55.057    | WA School Canteens Association                                                                                    | checked

Notably, there are differences between the list of initiatives represented in both lists.

Orginal	New
Blackpoint Beef
Boyanup Farmers' Markets	Boyanup Farmers' Markets
	Brunswick Junction emergency food relief and and community garden
	Bunbury Community Garden Withers
Bunbury Soup Van	Bunbury Soup Van
Bunbury Urban Growers (BUG)	Bunbury Urban Growers (BUG)
Capel Twilight Markets	Capel Twilight Markets
" City of Busselton	Community and Commercial services"	" City of Busselton	Community and Commercial services"
City of Busselton 'I'm Alert' Food safety training for Business	City of Busselton 'I'm Alert' Food safety training for Business
City of Busselton Waste Strategy
Crunch&Sip	Crunch&Sip
Dunsborough Anglican Church	Dunsborough Anglican Church
	Dunsborough Community Garden Inc
" Dunsborough Swap	Shuffle and Share"	" Dunsborough Swap	Shuffle and Share"
Edible Garden Group
	ECU Community Garden
Food Sensations for Schools	Food Sensations for Schools
Garden to Plate	Garden to Plate
Healthy Active by Design	Healthy Active by Design
Jenoshuas Organic Orchard stall	Jenoshuas Organic Orchard stall
Just Eat It Movie Night Fundraiser	Just Eat It Movie Night Fundraiser
Leavers' Week Fruit and Veg	Leavers' Week Fruit and Veg
Live Lighter	Live Lighter
Manjimup Farmers Market	Manjimup Farmers Market
Manjimup Produce Swap	Manjimup Produce Swap
Margaret River Community Centre	Margaret River Community Centre
Margaret River Dried Grapes and Cowtown Enterprises	Margaret River Dried Grapes and Cowtown Enterprises
Margaret River Farmers' Market	Margaret River Farmers' Market
Margaret River Organic Farm	Margaret River Organic Farm
Margaret River Organic Garden Trail
Pint Sized Plots	Pint Sized Plots
School Breakfast Program	School Breakfast Program
Shire of Augusta Margaret River Public Health Planning and Sustainable Economy	Shire of Augusta Margaret River Public Health Planning and Sustainable Economy
Shire of Collie Public Health Planning	Shire of Collie Public Health Planning
Shire of Harvey projects	Shire of Harvey projects
Shire of Nannup	Shire of Nannup
" South West Aboriginal Medical Service nutrition and food security in Indigenous people	maternal and child health"	" South West Aboriginal Medical Service nutrition and food security in Indigenous people	maternal and child health"
South West Food Bowl	South West Food Bowl
Stellar Violets Life library living museum & gallery	Stellar Violets Life library living museum & gallery
Stephanie Alexander Kitchen Garden Program	Stephanie Alexander Kitchen Garden Program
	Swap Shuffle Share (SSS) Harvey
The Bunbury Markets	The Bunbury Markets
The People's Garden	The People's Garden
Thommo's Community Garden	Thommo's Community Garden
WA School Canteens Association	WA School Canteens Association

NB The difference matches expectation, with the old data missing 5 entries present in the new and the new data missing 4 that are present in the old - a difference of 1 (hence a count of 40 in the original report and 41 in the new report).

If we take an instance of this, e.g. the "Blackpoint Beef" initiative that is present in the old report data not in the new and analyse what should be present:

Locate the actual checklist item

select id, checked from checklist_items where initiative_id = 1604 and characteristic_id = 2;

  id   | checked
-------+---------
 61946 | f

List old history for checklist item

select event, object from versions where item_id = 61946 and item_type = 'ChecklistItem';

 event  |                                                                                                 object
--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 create |
 update | {"id": 61946, "checked": true, "comment": null, "created_at": "2018-10-19T04:43:23.290Z", "deleted_at": null, "updated_at": "2018-10-19T04:43:39.836Z", "initiative_id": 1604, "characteristic_id": 2}
 update | {"id": 61946, "checked": null, "comment": null, "created_at": "2018-10-19T04:43:23.290Z", "deleted_at": null, "updated_at": "2018-10-19T04:43:23.290Z", "initiative_id": 1604, "characteristic_id": 2}

List new history for checklist item

select * from checklist_item_changes where checklist_item_id = 61946;

 id | checklist_item_id | user_id | starting_status | ending_status | comment | action | activity | created_at
----+-------------------+---------+-----------------+---------------+---------+--------+----------+------------

i.e. no records.

From here we can determine that the item was marked as "checked", but never flagged as "actual". As such, it should never have been included in the tally.

Taking the reverse, 'Bunbury Community Garden Withers' as an initiative present in the new data but not found in the old, we find the following:

Locate the actual checklist item

select id, checked from checklist_items where initiative_id = 1383and characteristic_id = 2;

  id   | checked
-------+---------
 55148 |

List old history for checklist item

select object from versions where item_id = 55148 and item_type = 'ChecklistItem';

                                                                                                 object
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 {"id": 55148, "checked": null, "comment": null, "created_at": "2018-05-26T04:50:36.014Z", "deleted_at": null, "updated_at": "2018-05-26T04:50:36.014Z", "initiative_id": 1383, "characteristic_id": 2}

List new history for checklist item

select * from checklist_item_changes where checklist_item_id =  55148;

  id   | checklist_item_id | user_id | starting_status | ending_status |                    comment                    |      action      | activity |        created_at
-------+-------------------+---------+-----------------+---------------+-----------------------------------------------+------------------+----------+---------------------------
 25317 |             55148 |      32 | no_comment      | actual        | not as yet but hoping to as the group evolves | save_new_comment | addition | 2018-08-01 08:26:56.60329

Thanks Tom, is this the only issue? Was this the only one?

Can we talk this through in person during the week

On Sat, 19 Nov 2022 at 12:47 pm, Tom Tuddenham @.***> wrote:

DEV NOTE

Deployed. Working through changes in Activity Reports. Case 1: Food Security in South West Western Australia

Characteristics beginning of period have changed value. Case in point, "1.2 Cultivate a passion for action" has changed from 40 entries to 41.

Verifying the count by hand (using the new changes log) returns 41 - matching the new value:

select count(*) from checklist_items inner join checklist_item_changes on checklist_items.id = checklist_item_changes.checklist_item_idinner join initiatives on initiatives.id = checklist_items.initiative_idwhere characteristic_id = 2and initiatives.scorecard_id = 87and starting_status <> 'actual'and ending_status = 'actual'and checklist_item_changes.created_at < '2022-01-01';

Running the count by hand against the old data model (using the activity log) return 40 - matching the old value:

select sum(case when to_status = 'checked' then 1 else 0 end) as checked_countfrom ( select distinct on (focus_area_groups.id, focus_areas.id, characteristics.id, initiatives.id) focus_area_groups.id as focus_area_group_id, focus_areas.id as focus_area_id, characteristics.id as characteristic_id, initiatives.id as initiative_id, events_checklist_item_checkeds.occurred_at, events_checklist_item_checkeds.to_status from characteristics inner join focus_areas on focus_areas.id = characteristics.focus_area_id inner join focus_area_groups on focus_area_groups.id = focus_areas.focus_area_group_id inner join checklist_items on checklist_items.characteristic_id = characteristics.id inner join initiatives on initiatives.id = checklist_items.initiative_id left join events_checklist_item_checkeds on events_checklist_item_checkeds.checklist_item_id = checklist_items.id and events_checklist_item_checkeds.occurred_at < '2022-01-01' and events_checklist_item_checkeds.to_status in ('checked', 'unchecked') where initiatives.scorecard_id = 87 and initiatives.deleted_at is null and characteristics.id = 2 order by focus_area_groups.id, focus_areas.id, characteristics.id, initiatives.id, events_checklist_item_checkeds.occurred_at asc ) as t;

The notable difference here is that the old report data is based on status going from unchecked to checked, whereas the new data is based on the status changing to 'actual'. We now need to work out which one is correct.

— Reply to this email directly, view it on GitHub https://github.com/ferrisoxide/wicked_software/issues/817#issuecomment-1320753735, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADFVZOVRY54VJMATVHLBLWLWJA2CPANCNFSM6AAAAAARGZ6QAI . You are receiving this because you commented.Message ID: @.***>

@emily-humphreys I'm still working through it.

DEV NOTES

@emily-humphreys @SishaMish

Per Lisa's review of the activity reports, there are small changes between the old and new reporting data. From the limited set I've examined in depth, it appears that the old reports were erroneously counting activities that were just "checked" (i.e. not actually going to the "actual" status), and where not counting activity using the new checklist_item_changes records.

From what I can determine, the new reports are more accurate than the old. There may be some finessing down the track - e.g. determining how to tally initiative characteristics that have been "toggled" back and forth from the "actual" state repeatedly, but for now the activity report is a more accurate record of what has happened in the system.

QA NOTES

@emily-humphreys @SishaMish

List of successfully scenarios covered during smoke test:

Created new Account
Created a new Transition card
- created new Organisation
- created new Subsystem Tags
- created new Community
- created new Wicked Problem
- created Initiative
Added new comments to initiative characteristics
Edited comments
Saved new comments
As above, but for SDGs Alignment Card
Linked SDGs and Transition Card
Test view of Ecosystem Maps (on both Transition and SDGs Cards)
Tested inviting a new user to an account

NB We did encounter an issue with saving comments in newly created transition cards - now fixed and rechecked.

@emily-humphreys @SishaMish

Just to summarise the release:

Smoke-tested core features successully
Checked the activity report in detail - believe this is now a more accurate representation of the data.

We can go over all this in detail on Wednesday.

Look forward to discussing this - is AM or PM better for you?

Ps There is one component that has been missed in conversion and that is this

MUST convert all characteristics "ticks + no comment" to "no tick + More information" this hasn't been converted in the transition.

For example:

Account: Moving Feast Transition Card: Moving Feast See attached pre-deployment picture - previously ticked by no comment highlighted. This is to ensure that the mapping that has previously been done is not lost for clients

Another example: South West Food Community None of these have been converted to "no tick + more information"

More info in #809 DEV note https://github.com/ferrisoxide/wicked_software/issues/809#issuecomment-1229401301 Insert "More information" comment status for each checklist item without a comment for items with no comment, but have been checked, insert the "More information" status as occurring at the time of checking

@emily-humphreys When did you capture these screenshots? They don't seem to line up the data in the system.

Take "Green Job Pathways" from "Moving Feast" as an example. The checklist item "1.1 Highlight the need to organise communities differently" does have a comment against it and the most recent status was "actual". What has happened since is the checklist item has been subsequently unchecked.

I don't know how I'm meant to interpret that data. What does it mean for an item to be marked as "unchecked" but have a comment and a status. I suspect the original "No comment" was incorrect - or was at least a side-effect of the complexity of the checked/unchecked/comment status combo.

Checklist items in other cards have updated to "more information" where appropriate. Take for example, the "Kick It Out Campaign" initiative in the "Time for Change" transition card for the "Weston Community Clubs" account. This has been correctly transitioned to the "more information" status.

I'll need to try and figure out what is going on with the "Moving Feast" data, but I need some time to piece together exactly what is going on here.

@emily-humphreys @SishaMish

Look forward to discussing this - is AM or PM better for you?

Either is fine, though Lisa won't be able to attend (jury duty). Shall we say 10:30 am?

DEV NOTE

Further analysis of the data in Transition Card: Moving Feast, focussing on the history of the characteristic "1.1 Highlight the need to organise communities differently" in Green Job Pathways:

History of checklist item comments "1.1 Highlight the need to organise communities differently"

Query

select created_at as time_stamp, left(object->>'comment', 25) as comment_text, object->>'status' as status
from versions 
where item_type = 'ChecklistItemComment' and item_id in (
  select id from checklist_item_comments where checklist_item_id = 111547
)
order by created_at

Results

time_stamp	comment_text	status
2021-07-29 02:11:15.582538
2021-07-29 02:15:35.903147	commentsvoffffffffff
2021-07-29 02:20:16.270037	We are meeting this chara
2021-08-03 20:14:00.57655	We are meeting this chara
2021-08-03 20:39:16.196785	The Green Job Pathways bu
2021-08-03 20:40:16.914545	The Green Job Pathways (G
2021-08-04 01:10:12.832438	The Green Job Pathways (G
2021-11-10 23:53:04.133629	The Green Job Pathways (G	actual
2021-11-10 23:53:42.85045
2021-11-10 23:55:00.176158	These are the future oppo	suggestion
2021-11-10 23:56:38.323008	The Green Job Pathways (G	actual
2022-05-23 01:55:12.943976	The Green Job Pathways (G	actual
2022-05-23 02:36:41.803038	The Green Job Pathways (G	planned
2022-05-27 05:35:39.879737
2022-05-27 05:37:59.819031	The Green Job Pathways (G	actual
2022-06-10 01:19:13.652507	The Green Job Pathways (G	actual
2022-06-13 23:53:32.822655	The Green Job Pathways (G	actual

History of checklist item checked states

Query

select created_at as time_stamp, object->>'checked' as checked
from versions 
where item_type = 'ChecklistItem' and item_id = 111547
order by created_at

Results

time_stamp	checked
2021-07-29 01:48:56.959282
2021-07-29 02:05:04.357226
2021-07-29 02:09:48.811286	true
2021-07-29 02:09:48.819489	true
2021-07-29 02:09:48.824118	true
2021-07-29 02:09:48.825035	true
2021-07-29 02:10:12.67672	false
2021-07-29 02:10:12.681739	false
2021-07-29 02:10:12.698056	false
2021-07-29 02:10:12.700988	false
2021-07-29 02:11:16.904723	true
2021-07-29 02:11:16.906033	true
2021-07-29 02:11:16.906052	true
2021-07-29 02:11:16.907889	true
2021-07-29 02:11:16.945044	true
2021-07-29 02:11:19.047843	false
2021-07-29 02:11:19.05513	false
2021-07-29 02:11:19.059338	false
2021-07-29 02:11:19.071133	false
2021-07-29 02:11:19.072178	false
2021-07-29 04:17:05.087048	true
2021-07-29 04:18:37.684416	false
2021-07-29 04:18:37.68817	false
2021-08-03 20:14:05.217607	true
2021-08-03 20:14:14.309724	false
2021-08-03 20:14:14.353181	false
2021-08-04 01:05:15.924285	true
2021-08-04 01:05:17.216534	false
2021-08-04 01:05:17.218042	false
2021-08-04 01:05:17.237211	false
2021-08-04 01:09:36.086815	true
2021-08-04 01:09:36.092401	true
2021-08-04 01:09:37.608784	false
2021-08-04 01:09:37.62164	false
2021-08-04 01:09:37.631573	false
2021-08-04 01:09:37.635275	false
2021-11-10 23:51:02.227575	true
2021-11-10 23:51:02.259856	true
2021-11-10 23:51:02.743037	false
2021-11-10 23:51:03.045249	true
2021-11-10 23:51:04.254888	false
2021-11-10 23:51:04.262547	false
2021-11-10 23:51:04.264704	false
2021-11-10 23:51:04.29221	false
2021-11-10 23:53:42.828451	true
2021-11-10 23:53:49.835751	false
2021-11-10 23:53:49.840918	false
2021-11-10 23:54:19.234756	true
2021-11-10 23:55:00.134675	false
2021-11-14 21:12:38.473664	true
2021-11-14 21:12:38.503081	true
2021-11-14 21:12:38.54468	true
2021-11-14 21:12:41.032043	false
2021-11-14 21:12:41.035986	false
2021-11-14 21:22:07.240036	true
2021-11-14 21:22:07.30829	true
2021-11-14 21:22:09.598812	false
2022-05-03 03:04:44.104222	true
2022-05-03 03:04:44.10439	true
2022-05-03 03:04:44.222778	true
2022-05-03 03:04:44.224311	true
2022-05-03 03:04:44.767072	false
2022-05-03 03:04:44.768476	false
2022-05-03 03:23:02.978948	true
2022-05-03 03:23:03.735347	false
2022-05-03 03:23:03.737881	false
2022-05-03 03:23:03.747992	false
2022-05-03 03:24:16.952017	true
2022-05-03 03:24:17.748248	false
2022-05-03 03:24:17.777341	false
2022-05-05 06:08:48.850026	true
2022-05-05 06:09:39.930821	false
2022-05-09 01:55:35.690865	true
2022-05-09 01:55:35.690876	true
2022-05-09 01:55:35.870327	false
2022-05-11 23:05:53.463843	true
2022-05-11 23:05:53.550123	true
2022-05-11 23:05:53.551249	true
2022-05-11 23:05:54.566381	false
2022-05-19 04:19:31.259185	true
2022-05-19 04:19:32.24575	false
2022-05-19 06:53:51.83411	true
2022-05-19 06:53:51.835097	true
2022-05-19 06:53:51.836001	true
2022-05-19 06:53:51.839529	true
2022-05-19 06:53:51.840342	true
2022-05-19 06:53:52.678388	false
2022-05-19 06:53:52.686301	false
2022-05-25 05:19:31.038706	true
2022-05-25 05:19:31.928357	false
2022-05-25 05:19:31.931105	false
2022-05-25 05:19:31.939029	false
2022-05-27 05:35:11.00843	true
2022-05-27 05:35:39.863718	false
2022-05-31 04:12:55.61062	true
2022-05-31 04:12:55.61519	true
2022-05-31 04:12:55.62974	true
2022-05-31 04:12:56.212333	false
2022-05-31 04:12:56.234878	false
2022-06-07 23:56:10.771174	true
2022-06-07 23:56:10.782922	true
2022-06-07 23:56:16.631767	false
2022-06-07 23:56:16.633108	false
2022-06-07 23:56:16.634844	false
2022-06-07 23:56:16.636087	false
2022-06-07 23:56:16.638015	false
2022-06-08 05:39:07.666753	true
2022-06-08 05:39:08.255347	false
2022-06-10 01:11:14.684461	true
2022-06-10 01:11:15.571444	false
2022-06-10 01:11:15.657923	false
2022-06-10 01:11:39.987906	true
2022-06-10 01:11:39.989299	true
2022-06-10 01:11:39.991	true
2022-06-10 01:11:39.992202	true
2022-06-10 01:11:39.993227	true
2022-06-10 01:11:40.004544	true
2022-06-10 01:11:40.58425	false
2022-06-10 01:11:40.585333	false
2022-06-10 01:11:40.588158	false
2022-06-10 01:11:40.592769	false
2022-06-10 01:11:40.593437	false
2022-10-18 00:51:11.789499	true
2022-10-18 00:51:14.776667	false
2022-10-18 00:51:14.778373	false
2022-10-18 00:51:14.779654	false
2022-10-18 00:51:14.77979	false
2022-10-18 00:51:14.783168	false

Summary

I'm not sure what I can make of this data. The final state of the checklist comment was "actual" - with a concrete comment assigned to it. I can't see how this was ever validly in the quasi state of "no comment" - though I suspect the toggling of the checked state has something to do with it.

The SQL code that tried to bind the checked state of the checklist item to the status of the most recent comment was always problematic - and has probably created "false negatives" in the data. Either that, or I'm interpreting the historical data incorrectly.

DEV NOTE

Even rurther analysis of the data in Transition Card: Moving Feast, focussing on the history of the characteristic "8.1 Encourage and assist street level workers to exploit the knowledge, ideas and innovations of citizens" in Green Job Pathways:

History of checklist item comments "8.1 Encourage and assist street level workers to exploit the knowledge, ideas and innovations of citizens"

Query

select created_at as time_stamp, left(object->>'comment', 25) as comment_text, object->>'status' as status
from versions 
where item_type = 'ChecklistItemComment' and item_id in (
  select id from checklist_item_comments where checklist_item_id = 111578
)
order by created_at

Results

time_stamp	comment_text	status
2021-08-04 20:57:52.913573
2022-05-27 06:11:22.466559
2022-06-13 02:42:12.067168	The Green Job Pathways (G	actual
2022-06-14 00:03:37.403336	The Green Job Pathways (G	more_information
2022-06-14 00:05:25.868552	The Green Job Pathways (G	more_information

History of checklist item checked states

Query

select created_at as time_stamp, object->>'checked' as checked
from versions 
where item_type = 'ChecklistItem' and item_id = 111578
order by created_at

Results

time_stamp	checked
2021-07-29 01:48:57.175196
2021-07-29 04:23:48.583366
2021-07-29 04:23:48.593122
2021-07-29 04:23:48.601071
2021-07-29 04:23:49.607663	true
2021-07-29 04:23:49.619255	true
2021-07-29 04:23:49.625949	true
2021-07-29 04:23:49.638028	true
2021-07-29 04:23:49.638963	true
2021-07-29 04:23:49.654037	true
2021-08-04 20:55:15.618439	false
2021-08-04 20:55:15.620679	false
2021-08-04 20:55:15.635037	false
2021-08-04 20:55:15.637425	false
2021-08-04 20:55:15.640904	false
2022-05-10 05:01:37.090539	true
2022-05-10 05:01:37.102078	true
2022-05-10 05:01:37.11624	true
2022-05-10 05:01:38.155662	false
2022-05-10 05:01:38.158987	false
2022-05-10 05:01:38.162139	false
2022-05-10 05:01:39.130906	true
2022-05-10 05:04:55.643617	false
2022-05-10 05:04:55.644935	false
2022-05-10 05:04:55.65588	false
2022-06-08 05:39:28.478576	true
2022-06-08 05:39:28.493624	true
2022-06-08 05:39:28.495083	true
2022-06-08 05:39:28.837063	false
2022-06-08 05:39:28.846291	false
2022-06-08 05:39:28.848739	false
2022-06-08 05:39:28.861233	false
2022-06-13 02:41:29.866329	true
2022-06-14 00:03:32.215209	false
2022-06-14 00:03:32.220946	false
2022-06-14 00:03:37.395202	true
2022-06-14 00:04:21.92089	false
2022-06-14 00:04:21.92225	false
2022-06-14 00:04:21.936924	false
2022-06-14 00:04:21.941258	false
2022-06-14 00:05:24.385178	true
2022-06-14 00:05:24.385986	true
2022-06-14 00:05:24.386879	true
2022-06-14 00:05:24.387193	true
2022-06-14 00:05:24.392989	true

Summary

Here we also have several toggles of the checked state, but the final comment status ("more_information") has resolved to that status being presented in the UI:

So, checklist items that should be in the status of "more information" because someone has specifically set it appear to be correct. This leaves the case of items that have been checked, but have never had a comment. That will require more investigation.

DEV NOTE

Finding checklist items in the state of 'more_information' with no comments

select checklist_items.id, checklist_item_comments.count from checklist_items 
left join checklist_item_comments on checklist_item_comments.checklist_item_id = checklist_items.id
where checklist_items.status = 'more_information'
group by checklist_items.id
having count(checklist_item_comments) = 0;

id	count
198493	0
198531	0

Taking checklist list item '198493' as an example

Check history:

select created_at as time_stamp, object->>'checked' as checked
from versions 
where item_type = 'ChecklistItem' and item_id = 198493 
order by created_at

Results:

         time_stamp         | checked
----------------------------+---------
 2022-11-19 04:57:11.379426 |

As expected, only an entry of the creation of the record. The actual record is marked as 'unchecked':

select case checked when true then 'checked' else 'unchecked' end from checklist_items where id = 198493;

which returns the expected "unchecked" result:

   case
-----------
 unchecked

Per the spec "Insert "More information" comment status for each checklist item without a comment", the status of the item should be "more information" (which it is).

We should also not expect there to be any new comment records associated with the item:

select count(*) from checklist_item_comments where checklist_item_id = 198493;

and that's exactly what we get:

 count
-------
     0

Thanks Tom for doing all this investigation.

These screenshots I took on the 28/10 when we decided on the reference accounts and data sets But it has made me think that it's connected to this old ticket #808 and that error Looking forward to discussing this together tomorrow

On Tue, Nov 22, 2022 at 8:37 PM Tom Tuddenham @.***> wrote:

DEV NOTE Finding checklist items in the state of 'more_information' with no comments

select checklist_items.id, checklist_item_comments.count from checklist_items left join checklist_item_comments on checklist_item_comments.checklist_item_id = checklist_items.idwhere checklist_items.status = 'more_information'group by checklist_items.idhaving count(checklist_item_comments) = 0;

id count 198493 0 198531 0 Taking checklist list item '198493' as an example

Check history:

select created_at as time_stamp, object->>'checked' as checkedfrom versions where item_type = 'ChecklistItem' and item_id = 198493 order by created_at

Results:
     time_stamp         | checked
----------------------------+--------- 2022-11-19 04:57:11.379426 |

As expected, only an entry of the creation of the record. The actual record is marked as 'unchecked':

select case checked when true then 'checked' else 'unchecked' end from checklist_items where id = 198493;

Per the spec "Insert "More information" comment status for each checklist item without a comment", the status of the item should be "more information" (which it is).

We should also not expect there to be any new comment records associated with the item:

select count(*) from checklist_item_comments where checklist_item_id = 198493;

and that's exactly what we get:

count
 0
— Reply to this email directly, view it on GitHub https://github.com/ferrisoxide/wicked_software/issues/817#issuecomment-1323411496, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADFVZOT5I6IL4OUSV4R7OQDWJSLMZANCNFSM6AAAAAARGZ6QAI . You are receiving this because you were mentioned.Message ID: @.***>

@emily-humphreys It's highly likely that's what's causing the difference. The old data model was - to put it mildly - mad.

Ha ha ha ha! Well thanks for all your effort on this.

On Tue, Nov 22, 2022 at 9:05 PM Tom Tuddenham @.***> wrote:

@emily-humphreys https://github.com/emily-humphreys It's highly likely that's what's causing the difference. The old data model was - to put it mildly - mad.

— Reply to this email directly, view it on GitHub https://github.com/ferrisoxide/wicked_software/issues/817#issuecomment-1323456160, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADFVZOQ4JWJC74BBATUEF3DWJSOXJANCNFSM6AAAAAARGZ6QAI . You are receiving this because you were mentioned.Message ID: @.***>

Deployed to production. Closing

EventideSystems / tool_for_systemic_change

Roll out Status and Activity Reporting changes #817

Describe the task

Success Criteria

Related Issues

809

685

DEV NOTE

DEV NOTE

Case 1: Food Security in South West Western Australia

DEV NOTES

QA NOTES

DEV NOTE

History of checklist item comments "1.1 Highlight the need to organise communities differently"

Query

Results

History of checklist item checked states

Query

Results

Summary

DEV NOTE

History of checklist item comments "8.1 Encourage and assist street level workers to exploit the knowledge, ideas and innovations of citizens"

Query

Results

History of checklist item checked states

Query

Results

Summary

DEV NOTE

Finding checklist items in the state of 'more_information' with no comments

Taking checklist list item '198493' as an example

count