New jobs for mapping Kobo form to Wildmeat Transitional DB

[aleksa-krolls]

Overview

WCS Wildmeat team wants to automatically sync Kobo data for its Rural Consumption survey Rural Consumption with 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 'Rural Consumption' Kobo form. 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 historical.js and price.js for reference).

Flow: Kobo survey --> Postgres DB

Job 1: Get Kobo data Trigger: Timer-based (every 3 hours)

1. OpenFn will fetch new 'Rural Consumption' surveys from Kobo Toolbox. 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 created since the last fetch.

   Your GET request should return submissions for this Rural Consumption form: https://kf.kobotoolbox.org/#/forms/afCPRfan8JdBt9sy9q48Jp/ Q: In historical.js we fetch Kobo forms based on survey id. Is it possible to query the Kobo API using form name (i.e., request to return forms where name contains 'Rural Consumption' rather than searching using the specific form id afCPRfan8JdBt9sy9q48Jp?)

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

Job 2: Map Kobo data to Postgres _Trigger: Message filter --> use this state.json_

3. Load the Kobo data into the transitional database leveraging language-postgresql. See these mapping specifications. --> Leave any questions for me in the 'Comments' column.

Credentials:

1. Kobo Credentials: See LP 'OpenFn Kobo Toolbox'
2. Postgres DB: See LP 'Wildmeat Postgres Transitional DB (WCS)'

[aleksa-krolls]

@taylordowns2000 Here are the specs for the WCS Wildmeat jobs. Some creds need to be shared over LP, so see my slack comments here.

[taylordowns2000]

@lakhassane , this job getKoboData.js is what we'll want to use to model the cron job for WCS consocsci

[lakhassane]

@aleksa-krolls A bunch of comments on WCS Execution

1. No unique constraint on site_id for upsert consideration (on table tbl_site).
2. No unique constraint on external_id/household_id (on table tbl_household)
3. household_id is a string in state.json and is noted as a integer on database if I understand the output (on table tbl_sample).
4. No unique constraint on sample_id (on table tbl_sample).
5. table tbl_swm_transaction does not exist (2nd sheet).

[aleksa-krolls]

@lakhassane Any updates on my question for step 1? Is it possible to query forms from Kobo by filtering on form name rather than form id? Ideally any survey with "Rural Consumption" in the form name can be returned in the first job.

[lakhassane]

@aleksa-krolls I checked the api docs for Kobo, but still the id is the only filter used. No sign of anything other on that.

[aleksa-krolls]

@lakhassane and no way to fetch ALL forms if we send a GET request to /assets and then iterate through this list to find those with the matching name "Rural Consumption"? @taylordowns2000 any ideas for how to achieve the above mentioned requirement so that users don't have to repeatedly update the OpenFn jobs with relevant Kobo form Ids every time a new survey is created?

[aleksa-krolls]

@lakhassane The client has asked that we implement any database configuration changes needed. Please proceed with these and track any DB changes made in the mapping doc.

1. No unique constraint on site_id for upsert consideration (on table tbl_site) --> please configure
2. No unique constraint on external_id/household_id (on table tbl_household) --> please configure
3. household_id is a string in state.json and is noted as a integer on database if I understand the output (on table tbl_sample). --> please change the data type/ configure as varchar as needed.
4. No unique constraint on sample_id (on table tbl_sample). --> please configure
5. table tbl_swm_transaction does not exist (2nd sheet). --> Use swm_transaction

[taylordowns2000]

Just noting that I'll handle the DB changes once we get access (IP needs whitelisting) and then Mamadou can roll on the job changes.

On Tue, Sep 1, 2020 at 2:59 AM Aleksa Krolls notifications@github.com wrote:

@lakhassane https://github.com/lakhassane The client has asked that we implement any database configuration changes needed. Please proceed with these and track any DB changes made in the mapping doc https://docs.google.com/spreadsheets/d/15VRibnaglShF3oNNLMbiyGopTJrYbP02aQ04cz4Qt-k/edit#gid=1538544891 .

1. No unique constraint on site_id for upsert consideration (on table tbl_site) --> please configure
2. No unique constraint on external_id/household_id (on table tbl_household) --> please configure
3. household_id is a string in state.json and is noted as a integer on database if I understand the output (on table tbl_sample). --> please change the data type/ configure as varchar as needed.
4. No unique constraint on sample_id (on table tbl_sample). --> please configure
5. table tbl_swm_transaction does not exist (2nd sheet). --> Use swm_transaction

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/OpenFn/wcs-consocsci/issues/3#issuecomment-684493846, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACCUBLNLHLGLUKHOXQ6JMB3SDSLUXANCNFSM4QGHLGAA .

-- Taylor Downs | Head of Product Open Function Group https://www.openfn.org/ | How Data Integration Makes ICT4D Work https://youtu.be/GVB9voGp6T4