Project 1: BNS and NRGT Survey Integrations
Kobo Toolbox template forms to integrate
- BNS Survey –> OpenFn job
bns/survey.js
- BNS Price –> OpenFn job
bns/price.js
- NRGT Historical –> OpenFn job
nrgt/2017.js
- NRGT Current –> OpenFn job
nrgt/2019.js
OpenFn jobs to fetch Kobo data
- Ongoing, timer-based fetch –> OpenFn job
getKoboData.js
- Historical, once-off fetch –> OpenFn job
historical.js
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-genericlanguage-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
- For idempotency, the jobs create a new Kobo
uuid
to map to the DBDatasetUuidId
. 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. - 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. - In the
bns/survey.js
job, we utiilize some of the Kobo form metadata to create data for thebns_matrix
L52-L65 andnr
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. - 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 usinghistorical.js
, and which forms should be synced on an ongoing basis usinggetKoboData.js
.
Data Flows Supported
(1) Scheduled Data Integration (Ongoing Sync)
- On a scheduled-basis (e.g., every 3 hours), the OpenFn job
1A. Get Kobo Data
(akagetKoboData.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 stringaopf2bJ4cVqEXCrjnwAoHd
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
- This job will post each individual Kobo survey back to the OpenFn inbox as an individual Message.
- Message filter triggers will execute the relevant jobs (see above list) to process & load the data into the connect DB.
- View Activity History to monitor the success of these integration flows.
- 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.
- At any time, the OpenFn job
1B. Get Historical Kobo Data
(akahistorical.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 stringaopf2bJ4cVqEXCrjnwAoHd
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
.
- When ready to sync the historical data, click “Run job” button.
(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.
- 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).
- To configure the Kobo REST service for real-time integration, see the instructions here.
- 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…
- Delete any test submissions used in training/ testing.
- Check if the form already has real submission data collected. If yes, add the
formId
to the1B. Get Historical Kobo Data
job and run it to first migrate the historical data to the database (see more in data flow #2 above). - Add the new
formId
and relevanttag
to the job1A. 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
- 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.
- 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?