Project 1: BNS and NRGT Survey Integrations

Kobo Toolbox template forms to integrate

OpenFn jobs to fetch Kobo data

Mappings

See here for the mapping specifications defined by WCS.

  • These jobs map to a MSSql database and leverage OpenFn/language-mssql and available helper functions.
  • If WCS would like to connect with a Postgres database, it can leverage OpenFn/language-postgresql, however we would need to add more helper functions (e.g., upsert(), insertMany()) so that syntax from these initial jobs can easily be transferred. We could look into building some sort of hyper-generic language-sql package that worked across various sql DBs. This would be very under-powered (as it would struggle to reach the lowest-common-denominator for things like upsert) but might ultimately save WCS time if they plan to use two different kinds of database concurrently. (Can WCS share feedeback here and confirm whether the Postgres DB structure will be the same as the Mssql DB?)

Assumptions

  1. For idempotency, the jobs create a new Kobo uuid to map to the DB DatasetUuidId. This is a concatenation of Kobo _id + _submission_time + _xform_id_string. We cannot use Kobo _uuid because this is refreshed every time a Kobo submission is cleaned.
  2. All data cleaning will be done in Kobo Toolbox. Every time Kobo data is synced with the DB, it will overwrite the records saved there and use the above uuid to upsert existing records.
  3. In the bns/survey.js job, we utiilize some of the Kobo form metadata to create data for the bns_matrix L52-L65 and nr L42-L50 question groups. It is therefore important that future versions of this form follow the same Kobo question naming conventions, otherwise the data will not map as expected and the job may need to be modified.
  4. OpenFn will only automatically fetch the Kobo surveys where the form Ids have been specified in the job getKoboData.js. This is to allow WCS to identify which forms have historical data to be migrated one time using historical.js, and which forms should be synced on an ongoing basis using getKoboData.js.

Data Flows Supported

(1) Scheduled Data Integration (Ongoing Sync)

  1. On a scheduled-basis (e.g., every 3 hours), the OpenFn job 1A. Get Kobo Data (aka getKoboData.js) will run to fetch Kobo form data in bulk for the specified form Ids. Before running the job, WCS should…
  • 1a. Update the survey Ids to fetched from Kobo toolbox (these can be copied from the URL of a Kobo form). In https://kf.kobotoolbox.org/#/forms/aopf2bJ4cVqEXCrjnwAoHd/landing then the string aopf2bJ4cVqEXCrjnwAoHd is the survey Id.
  • 1b. Add the appropriate survey tag to indicate which mappings should be used to process the data. Tag options include: bns_survey, bns_price, nrgt_current, nrgt_historical.
surveys: [
      //** Specify new forms to fetch here **//
      //** Tag options: bns_survey, bns_price, nrgt_current, nrgt_historical  **//
      { id: 'aMpW7wwRBRbtCfidtK2rRn', tag: 'bns_survey' }, //BNS Ndoki 2019
      { id: 'new-form-id', tag: 'form_tag' }, //New Form Name
  1. This job will post each individual Kobo survey back to the OpenFn inbox as an individual Message.
  2. Message filter triggers will execute the relevant jobs (see above list) to process & load the data into the connect DB.
  3. View Activity History to monitor the success of these integration flows.
  4. If any Kobo data is cleaned, it will be fetched in the next job run (see step #1) and will overwrite* the matching record in the DB.

*Note these jobs have built-in transformations to create a custom unique identifier to map to the DB column DatasetUuidId, which can be used in the OpenFn job upsert() operations to ensure idempotency.

  cleanedSubmission.durableUUID = `${_submission_time}-${_xform_id_string}-${_id}`;

(2) Historical Kobo Migrations (Once-off)

Note: OpenFn jobs support historical migrations for a total of 5,000 Kobo submissions. If WCS wishes to migrate a list of forms where the total submissions exceed 5k, it should either (a) break up the migration and repeat the following steps for smaller subsets of the forms, or (b) contact OpenFn for support running larger bulk migrations locally.

  1. At any time, the OpenFn job 1B. Get Historical Kobo Data (aka historical.js) can be run on-demand to manually fetch historical Kobo data in bulk. Before running the job, WCS should…
  • 1a. Update the survey Ids to fetched from Kobo toolbox (these can be copied from the URL of a Kobo form). In https://kf.kobotoolbox.org/#/forms/aopf2bJ4cVqEXCrjnwAoHd/landing then the string aopf2bJ4cVqEXCrjnwAoHd is the survey Id.
  • 1b. Add the appropriate survey tag to indicate which mappings should be used to process the data. Tag options include: bns_survey, bns_price, nrgt_current, nrgt_historical.
  1. When ready to sync the historical data, click “Run job” button. run-job

(3) Real-Time Integration (Not used, available as needed)

Note: This approach is not expected to be used because it is redundant to data flow #1, where data is already being fetched every 3 hours. However, this integration option remains available for scenarios where real-time data flow is important.

  1. For some forms, WCS may prefer to configure a *REST service** in Kobo Toolbox to forward Kobo surveys to OpenFn for real-time processing (rather than having the above job sync the data on a timed basis).
  2. To configure the Kobo REST service for real-time integration, see the instructions here.
  3. Every time WCS submits a new Kobo survey, the data will be forwarded automatically to the OpenFn Inbox. If the OpenFn jobs are “on”, this data will be processed and forwarded onto the database automatically.

*Note that this REST Service will not re-send Kobo data after it has been cleaned (only the initial submission). This is why the timer-based jobs are needed to sync cleaned Kobo data.

Kobo Form Management

Integrating New Kobo Forms

If WCS would like to integrate a new BNS or NRGT Kobo form with the database…

  1. Delete any test submissions used in training/ testing.
  2. Check if the form already has real submission data collected. If yes, add the formId to the 1B. Get Historical Kobo Data job and run it to first migrate the historical data to the database (see more in data flow #2 above).
  3. Add the new formId and relevant tag to the job 1A. Get Kobo Data to fetch data for this form on an ongoing basis.

Disconnecting Kobo Forms

To remove a Kobo form from the integration flow, edit the job 1A. Get Kobo Data (aka getKoboData.js) to remove the relevant form Id.

Open Questions

  1. Can WCS test to confirm the jobs are mapping data to Mssql as expected? See this video for guidance. Please pay special attention to the BNS Survey job, where the mapping logic is more complex and based on the form metadata.
  2. Can WCS confirm the Postgres database we should also map these to? And should we map the exact same data to Postgres as we are to Mssql? Or will different surveys be mapped to different DBs?