Issues in CK migration

[mahalakshme]

As part of migration run for this in prerelease, the below issues have been raised:
Issue 1
Issue 2

As part of this card, the above issues need to be fixed.

[himeshr]

Discussion Notes related to Issue 1:

1. Currently, ETL expects 1 instance of a concept to be in a table's columns, multiple columns with same name (as concept is same) result in this error.?
   a. More than one FormElement uses same Concept for Read-Only fields (Concept name: 'Placeholder for counselling form element')
   b. More than one FormElement uses same Concept, but based on rules, we might end up showing only 1 to the User
2. From ETL Table, for a Form where concept is repeated, what to infer from the Concept, as to which FormElement it belongs to?
3. ANC discussion item 1, are all read-only , so might be working..

[himeshr]

The root cause of the issues 1 and 2 mentioned in the description of this card for instances where the fields are not Read-only are as follows

During CK migration, we have not migrated the non_applicable_form_element table entries to point their form_element_id column to new values after migration. For an Org extending Openchs Org (Org1), "non_applicable_form_element" table is used to selectively void a Form_element of a form inherited from Organisation_id 1 from not being applicable for the Child Organisation.

This non_applicable_form_element(NAFE) is made use of in both Avni-server and ETL-server to weed-out form-elements which have this mapping present, so that they are either shown as voided(in Avni-server forms API response) Or ignored during Table generation (in ETL-server).

Executing the below command, on Prod and Pre-release environment return the Outputs Listed below, which are useful in demonstrating the root-cause.

set role calcutta_kids;
select c.id,
       c.name,
       fe.id,
       f.name,
       fe.name,
       feg.name,
       (nafe is null or nafe.is_voided = true) as is_shown
from form_element fe
         join form_element_group feg on fe.form_element_group_id = feg.id
         join form f on f.id = feg.form_id
         join concept c on fe.concept_id = c.id
         left join non_applicable_form_element nafe on fe.id = nafe.form_element_id
where c.name = 'Cried soon after birth'
  and f.name = 'Birth form'
  and fe.is_voided = false
  and feg.is_voided = false
order by 4, 2;

SQL Commands Output

Prerelease

id	name	id	name	name	name	is_shown
211148	Cried soon after birth	110018	Birth form	Did the baby cry soon after birth?	General	true
211148	Cried soon after birth	1425	Birth form	Did the baby cry soon after birth?	General	true

Prod

id	name	id	name	name	name	is_shown
704	Cried soon after birth	389	Birth form	Did the baby cry soon after birth?	General	false
704	Cried soon after birth	1425	Birth form	Did the baby cry soon after birth?	General	true

[himeshr]

Post the fix ETL ran successfully for Calcutta_kids, after having dropped the ETL schema for that org in Pre-release env..

ubuntu@ip-172-1-2-108:/var/log/avni-etl-service$ grep -i "ETL for schema" /var/log/avni-etl-service/etl-service.log | grep cal
2023-12-29 08:05:43,890 INFO  Avni ETL_Worker-1 [etl.service.EtlService] Running ETL for Schema: calcutta_kids, DB User: calcutta_kids, Schema User: calcutta_kids
2023-12-29 08:06:00,434 INFO  Avni ETL_Worker-1 [etl.service.EtlService] Completed ETL for schema calcutta_kids with dbUser calcutta_kids and schemaUser calcutta_kids
2023-12-29 08:44:39,524 INFO  Avni ETL_Worker-1 [etl.service.EtlService] Running ETL for Schema: calcutta_kids, DB User: calcutta_kids, Schema User: calcutta_kids
2023-12-29 08:53:17,164 INFO  Avni ETL_Worker-1 [etl.service.EtlService] Completed ETL for schema calcutta_kids with dbUser calcutta_kids and schemaUser calcutta_kids

[himeshr]

The only concept which is duplicated across multiple form_elements in a single form is the Placeholder Concept 'Placeholder for counselling form element'.
In ETL, to avoid including this column in tables, there is an explicit filter on its uuid, set in SchemaMetadataRepository.PLACEHOLDER_CONCEPT_UUID.

set role calcutta_kids;
select c.uuid = 'b4e5a662-97bf-4846-b9b7-9baeab4d89c4' is_placeholder_concept, f.name as form, c.name as concept , c.id concept_id ,count(*) repetitions, min(fe.id)
from form f
         join form_element_group feg on f.id = feg.form_id
         join form_element fe on feg.id = fe.form_element_group_id
         join concept c on fe.concept_id = c.id
         left join non_applicable_form_element nafe on fe.id = nafe.form_element_id
where f.is_voided = false
  and feg.is_voided = false
  and fe.is_voided = false
  and (nafe is null or nafe.is_voided = true)
--   and c.name != 'Placeholder for counselling form element'
group by f.name,c.id
having count(*) > 1;

Output:

is_placeholder_concept	form	concept	concept_id	repetitions	min
true	ANC Home Visit	Placeholder for counselling form element	210804	8	1367
true	Child PNC	Placeholder for counselling form element	210804	8	1445
true	Doctor Visit Followup at Home	Placeholder for counselling form element	210804	2	1578
true	Home Visit	Placeholder for counselling form element	210804	11	1461
true	Mother Home Visit	Placeholder for counselling form element	210804	3	1515
true	PNC	Placeholder for counselling form element	210804	12	1360

[himeshr]

@AchalaBelokar , as part of QA, test that

1. ETL runs successfully for Calcutta_kids on pre-release (Dev testing was successful) //Issue 1

2. Check that duplicate form-elements are not shown in a form on avni-client or avni-webapp for CK //Issue 2

[himeshr]

Discussion Notes related to Issue 1:

1. Currently, ETL expects 1 instance of a concept to be in a table's columns, multiple columns with same name (as concept is same) result in this error.?
   a. More than one FormElement uses same Concept for Read-Only fields (Concept name: 'Placeholder for counselling form element')
   b. More than one FormElement uses same Concept, but based on rules, we might end up showing only 1 to the User
2. From ETL Table, for a Form where concept is repeated, what to infer from the Concept, as to which FormElement it belongs to?
3. ANC discussion item 1, are all read-only , so might be working..

@mahalakshme and @petmongrels

Answers for these questions are as below:

1. a Regarding the read-only concept 'Placeholder for counselling form element', please see this comment on how it is handled in ETL as of today.
   1.b We did not find more than one FormElement using same Concept in Calcutta_kids org (except for Placeholder concept)

2. Regarding the question on if concept is repeated, what to infer from ETL table.? Currently, ETL doesnot allow multiple form_elements with same Concept. Therefore, we can always point to the FE that a ETL column's concept refers to.

3. See answer for 1.a