New jobs for mapping Kobo forms to Wildmeat DB

[daissatou2]

Background, context, and business value

WCS Wildmeat wants to automatically sync data from 8 new Kobo forms to a "Transitional Database", which they will later access to load data into an even more structured format. The destination database has a series of tables (i.e., tbl_site, swm_transction) that we will use as targets for the Kobo forms. Note:

• Kobo survey data will need to be mapped to these different target tables, but there will be very few automated data cleaning requirements.
• We assume that WCS users will perform all data cleaning in Kobo Toolbox. Therefore we need to implement a similar solution as with the BNS surveys where we (1) fetch Kobo surveys on a timer-basis and post the survey data back to the OpenFn inbox, and then (2) load the surveys into the destination databases (see fetch-rc-submissions.js and rural-consumption-to-postgres.js for reference).

There are four survey types and two of each type:

1. Urban Consumption
2. Rural Consumption
3. Market
4. Offtake

Survey list:

1. SWM Consommation Urbaine - https://kf.kobotoolbox.org/#/forms/a9eJJ2hrRSMCJZ95WMc93j/summary
2. SWM Urban Consumption Survey 2019 - https://kf.kobotoolbox.org/#/forms/aJxTqQSF7VRLYbMGfeTHfd/summary
3. SWM Consommation Rurale - https://kf.kobotoolbox.org/#/forms/aUrUbD6C9hB3y8XjfQ9CLc/summary
4. SWM_Rural_Consumption_Form - https://kf.kobotoolbox.org/#/forms/aUtBrSsVRkZrjkpodB6TW7/summary
5. SWM Suivi Marché 2020 - Congo Market - https://kf.kobotoolbox.org/#/forms/aDVDagX8TE9NUY7xmvAUpv/summary
6. SWM Suivi points de vente protéines animales - DRC Market - https://kf.kobotoolbox.org/#/forms/aem28HL45vkQKyhB22xn8Q/summary
7. SWM_Offtake_DRC_202104 - https://kf.kobotoolbox.org/#/forms/aYcthFvuwgvUn89aBoedgT/summary
8. Prélèvement de chasse SWM - https://kf.kobotoolbox.org/#/forms/a3hX3ZvVm4BanZDeis9AFj/summary

The specific request, in as few words as possible

Job 1: Get Kobo data Trigger: Timer Create a duplicate version of this job that can fetch data from all surveys: https://github.com/OpenFn/wcs-wildmeat/blob/master/jobs/fetch-rc-submissions.js

1. OpenFn will fetch new surveys from Kobo Toolbox (see list of surveys above). To do this, send a GET request to the Kobo API (see api docs) using the openfn_kobo account, and filter by end date to request only submissions modified since the last fetch.

2. Post every Kobo form submission received in step 1 response back to the OpenFn inbox as an individual Message.

4 Jobs: Map Kobo data to Postgres Trigger: Message filter

3. Create 1 job per survey type to load the Kobo data into the transitional database leveraging language-postgresql. See these mapping specifications for each form type.

state.json

Kobo Credentials: See LP OpenFn Kobo Toolbox Postgres DB: See LP Wildmeat Postgres Transitional DB (WCS)

Sample states for each form type... paths should be the same, but let's test with both states. Urban Consumption

1. SWM Consommation Urbaine - https://www.openfn.org/projects/p5yaxp/messages/mv7vp9rg
2. SWM Urban Consumption Survey 2019 - https://www.openfn.org/projects/p5yaxp/messages/m3pdkqep

Rural Consumption

3. SWM Consommation Rurale - https://www.openfn.org/projects/p5yaxp/messages/mbp5w963
4. SWM_Rural_Consumption_Form - https://www.openfn.org/projects/p5yaxp/messages/mpqknbnv

Market

5. SWM Suivi Marché 2020 - Congo Market - https://www.openfn.org/projects/p5yaxp/messages/m3pdkq6e
6. SWM Suivi points de vente protéines animales - DRC Market - https://www.openfn.org/projects/p5yaxp/messages/m3pdkqae

Offtake

7. SWM_Offtake_DRC_202104 - https://www.openfn.org/projects/p5yaxp/messages/mz79bmnj
8. Prélèvement de chasse SWM - https://www.openfn.org/projects/p5yaxp/messages/mj437p3a

adaptor

GET: language-kobotoolbox or language-http? LOAD: language-postgresql

output.json

This is the destination DB data model.

[aleksa-krolls]

@daissatou2 Issue is looking good, but going to dig more into the mappings. A couple of thoughts...

1. Did Usman shared a DB ERD/ data model diagram we can also share here? Maybe you can link to this in the output section and confirm to Mamadou which tables we expect to upsert data in.
2. Are the mappings going to be very similar across certain forms? Should we recommend that Mamadou start by draft a job for 1 form, and then copy/ modify for the others? Or do you think it might even be more time efficient to draft 1 job for every form time... test & iterate... and then replicate for other surveys of that same form type? Let's discuss!

[aleksa-krolls]

@lakhassane moving to the bottom of the backlog... let @daissatou2 know if you have any questions on the mappings!

[daissatou2]

@lakhassane you can link your jobs to the 5 jobs which are turned off here: https://www.openfn.org/projects/p5yaxp/jobs

[aleksa-krolls]

@lakhassane how is this going? Do you have a rough estimate for when @daissatou2 can test later today? (fyi, she will have some info re: species mappings to add this afternoon)

[lakhassane]

@aleksa-krolls @daissatou2 either by end of the day or tomorrow morning should be safer. Extrapolating considering the backlog can change often.

[aleksa-krolls]

@lakhassane moving back to backlog so you're clear on priority. Please leave @daissatou2 an update on how things went today, thanks!

[lakhassane]

@daissatou2 so far, I made the job that fetch kobo submissions and tested it. some submissions (3) are in the inbox.

You might not be done yet but I still have some troubles with the mappings. I finished the one for urban consumption but awaiting potential changes.

If you look at the column L in that tab I added some questions for you.

[daissatou2]

@lakhassane I answered your questions in column L urban_consumption tab, let me know if any more!

[lakhassane]

@daissatou2 all the jobs are mapped and pushed.

However, I left comment for some of the columns in the mapping specifications for job 2 and 3.