GAPS: store custom fields

mark-prins commented 4 months ago

Part of epic #3660

Acceptance criteria

[ ] Custom fields created in mSupply syncs out to remote sites
[ ] Populated custom fields for a name record sync out to remote sites
[ ] Remote sites can see all defined custom fields when editing a store/name
[ ] Remote sites can see the populated fields and can edit values

Is your feature request related to a problem? Please describe 👀

There are several properties that we'd like to collect which are associated to a store. The proposal is to add these as custom fields in mSupply - this issue is to save that data in open mSupply.

User story	B.1.1
As a…	National EPI manager
I want…	to be able to classify facilities into different 'administrative levels' (e.g. Primary, Sub-National, Lowest Distribution, Service Point etc)
So that…	I can assess the current cold storage for each administrative level

Solution B.1.1 The system will allow central users to categorize facilities into ‘n’ customisable administrative levels. Each facility will only be able to belong to one administrative level.

User story	B.1.2
As a…	National EPI manager
I want…	to be able to classify facilities into different 'facility types' (e.g. National Vaccine Store, Regional Vaccine Store, Municipal Warehouse, Referral Hospital, Maternity Clinic etc)
So that…	I can assess the current cold storage by type of facility

Solution B.1.2 The system will allow central users to categorize facilities into ‘n’ customisable facility types. Each facility will only be able to belong to one facility type.

User story	B.1.3
As a…	National EPI manager
I want…	to be able to classify facilities into different 'ownership types' (e.g. Government, NGO, Private, Faith-based etc)
So that…	I can assess the current cold storage for each ownership type

Solution B.1.3 The system will allow central users to categorize facilities into ‘n’ customisable ownership types. Each facility will only be able to belong to one ownership type.

User story	B.1.5
As a…	National EPI manager
I want…	all facilities to provide the central EPI office with details of the type (electricity, solar, gas etc) and availability (No availability, < 8 hours, 8-16 hours, > 16 hours) of energy infrastructure they have access to
So that…	I have an idea of the available energy infrastructure situation

Solution B.1.5 The system will aggregate energy infrastructure data that has been captured by facilities to the central level. If the system is not distributed then all facility energy infrastructure data will be input by the central user(s).

User story	B.1.5.1
As a…	Health Care Worker (HCW) or warehouse worker
I want…	to be able to detail the current energy infrastructure of my facility
So that…	I can respond to the request for the collection of this data

Solution B.1.5.1 The system will allow facility users to indicate their current energy infrastructure by choosing each type of energy source (electricity, solar, gas, kerosene) and the availability The options to choose from will resemble:

Electricity
- No availability
- < 8 hours
- 8-16 hours
- > 16 hours
Solar
- No availability
- < 8 hours
- 8-16 hours
- > 16 hours
Gas
- TBD
Kerosene
- TBD

Describe the solution you'd like 🎁

Create a table to keep custom field descriptions (this includes list definitions, if applicable)
Add custom_data column to name table
Sync the custom field table to open mSupply Central Server
Implement a transformation to take the prefs JSON object and save into custom field table
Add the custom_data field to the name transformation

Sample pref object:

{
  "store": {
    "Electricity": {
      "type": "List",
      "items": { "No availability": "", "< 8 hours": "", "8-16 hours": "", "> 16 hours": "" }
    },
    "Solar": {
      "type": "List",
      "items": { "No availability": "", "< 8 hours": "", "8-16 hours": "", "> 16 hours": "" }
    },
    "CCE Donor": { "type": "List", "items": { "UNICEF": "", "GAVI": "" } },
    "Facility type": { "type": "List", "items": { "Government": "", "NGO": "" } },
    "Text field": { "type": "Text", "items": {} },
    "Nuber field": { "type": "Number", "items": {} },
    "Boolean field": { "type": "Boolean", "items": {} },
    "Date field": { "type": "Date", "items": {} },
    "List field": { "type": "List", "items": { "List item one": "", "List item two": "" } }
  }
}

and a populated custom data record looks like this:

{
    "usesDispensaryModule": { "data": "True" },
    "monthlyConsumptionLookBackPeriod": { "data": "0" },
    "monthlyConsumptionEnforceLookBackPeriod": { "data": "False" },
    "monthsLeadTime": { "data": "0" },
    "usesVaccineModule": { "data": "True" },
    "usesDashboardModule": { "data": "False" },
    "usesCashRegisterModule": { "data": "False" },
    "usesPaymentModule": { "data": "False" },
    "usesPatientTypes": { "data": "False" },
    "usesHideSnapshotColumn": { "data": "False" },
    "Electricity": { "data": "> 16 hours" },
    "Solar": { "data": "No availability" },
    "CCE Donor": { "data": "UNICEF" },
    "Facility type": { "data": "NGO" }
  }

This will then allow an administrator working on the mSupply central server to add custom fields, edit the data saved against names and have this sync out to remote sites. Remote sites can then see the populated fields and can edit values, which would then sync to mSupply central. This does bend sync rules, in the same way that editing patients does. It does allow reporting / grafana to run from the mSupply central server if required.

Describe alternatives you've considered 💭

Have the [store] custom_data field synchronised and editable on central and remote ( it's a much more complex solution )
Only edit custom fields on central ( does not meet client requirements )
Do not use mSupply custom fields ( we would need to edit on omSupply central then in order to meet centralised editing requirements )

Additional context 💌

New table:

erDiagram
    custom_field {
        TEXT id PK "NOT NULL"
        TEXT table_name "NOT NULL ENUM 'name','store'"
        TEXT name "NOT NULL"
        TEXT type "ENUM 'TEXT','NUMBER','BOOLEAN','DATE','LIST'"
        TEXT list
    }

The name table has a column added for custom_data which is TEXT and nullable.

Moneyworks Jobcode 🧰

CCEIDEV

Chris-Petty commented 4 months ago

Just checking in the pref object, is the success: true just from the silly pattern in 4D desktop where we inadvertently put that property in pref objects, but it was really just indicating the pref was "retrieved successfully" from the database?

Is quote_line intentional? Are we doing quotes in custom data? May lead to confusion once tendering+quotes are in the OMS mix.

I may only have a small part of the picture, but is this basically facility surveying functionality? Broadly speaking if there may be demand for surveying sites in mSupply then we could formally table it out as configurable surveying questions rather than custom data.

On that note, Legacy mSupply regimens is basically surveying and at the time I wish we called it surveys rather than regimens - just as a more laymen naming scheme 😁. I don't think we should reuse the regimen system, though.

adamdewey commented 4 months ago

@mark-prins - not sure if relevant at this stage, but just wanted to point out that CCE Donor will apply at the individual CCE level, not the facility level

adamdewey commented 4 months ago

Also I think we'll need both Facility type (Clinic, Referral Hospital, National Vaccine Store etc) and Facility ownership (Private, Government, NGO etc)

andreievg commented 4 months ago

I only had a quick glance at this, have some preliminary questions:

Would it be better to have this data (for this use case) against a name record ? Can we only collect data against a store ?

The mSupply central server WILL NOT see the edited values! we will have to run reporting from the omSupply central server - the advantage though is that we don't need to have messages sent to mSupply to update the store record, or have records updated by multiple sites.

This would mean that data needs to be available on omSupply central, but we can always push that data to omSupply central as well.

This will then allow an administrator working on the mSupply central server to add custom fields, edit the data saved against stores and have this sync out to remote sites.

Similar to the previous comment. If we can edit data for this store in mSupply, would we expect it available in omSupply central ? If so it becomes tricky.

In terms of JSON data vs typed data (stored in optional typed columns), the former is a bit trickier for reporting in sqlite, but since reporting will be done on omSupply central, which will be postgres, then it should be possible.

I assume, for list type, the key would be the stored value and the value is what is shown in UI ?

Do we currently allow editing of custom data for remote site on central in mSupply ? Maybe we should look at doing it in omSupply central ?

adamdewey commented 4 months ago

Would it be better to have this data (for this use case) against a name record ? Can we only collect data against a store ?

Good point @andreievg

Some more context:

The things we need to collect are:

This facility 'meta' data as detailed above
CCEI data (from the CCEI asset module)

There are 3 modes of operation (that I can think of) in which the GAPS tool will need to operate:

Centralized mode (all data is input by the central user via bulk import)
Distributed mode (all data is input by facilities)
Partially distributed mode (those facilities using Open mSupply input themselves, central users bulk import for the others)

Using Stores would work for all of the above modes of operation BUT if the country is also using Open mSupply as their eLMIS then in mode 3 there may possibly be implications if Stores are created for those facilities that are not using Open mSupply?

Although the only thing I can think of is that supplier invoices build up - I can't really see how that's much of an issue to be honest?

mark-prins commented 4 months ago

Thanks @Chris-Petty - the success: true and quote_line were both artefacts from mSupply, have updated the example. Thanks @adamdewey - noted re the donor and the other fields.

mark-prins commented 4 months ago

Would it be better to have this data (for this use case) against a name record?

Seems about the same to me; the specs talk of 'facility' so I went with 'store'. Don't really mind either way;

This would mean that data needs to be available on omSupply central, but we can always push that data to omSupply central

one of those omSupply central should be mSupply central.. but which one? 🤔

Working with Adam's comment above, the 'editing on OG central' use case becomes only 'import on OG central' which is a relief! that's much better to accomodate, isn't it?

I assume, for list type, the key would be the stored value and the value is what is shown in UI ?

oh, yeah. That's what OG is giving us - afaics this is only set to an empty string in OG and not used as a value

Do we currently allow editing of custom data for remote site on central in mSupply ? Maybe we should look at doing it in omSupply central ?

The user story has the remote site entering their own data, I'm not sure how editing on omSupply central would help in this case.

jmbrunskill commented 4 months ago

The user story has the remote site entering their own data, I'm not sure how editing on omSupply central would help in this case.

Even thought that's the user story, I think we definitely need to manage/handle the central server only config scenario, as many of the target users won't have an existing mSupply installation...

adamdewey commented 4 months ago

Even thought that's the user story, I think we definitely need to manage/handle the central server only config scenario, as many of the target users won't have an existing mSupply installation...

Yes I agree, I was sort of thinking they could log into the Store and edit the data there - but that might possibly be too cumbersome?

But it does seem like a possible way to do it without any extra work on our part at this stage? Hmm

msupply-foundation / open-msupply