Connecting KoboToolbox to CouchDB for Real Time Data
I have recommended KoboToolbox (and KoboCollect) for nonprofits, and we also use it at DataMeet to collect all kinds of data, including IDVC. For IDVC, I pull the data from KoboToolbox, do some massaging, and then upload it to CouchDB. It works very well. But what if I want to make this whole process real-time?
KoboToolbox Supports webhooks, called REST Services, which can push the data to a web endpoint as soon as the records get submitted. So we can send that data to the CouchDB's POST endpoint to get the data inserted. But there is an issue. Many fields in the KoboCollect JSON payload start with _ ( underscore), for example, _id, _uuid, _submission_time, etc. In CouchDB, the areas starting with _ are considered internal. Except for _id, and _rev; you can't send any other attributes starting with _ for insertion. CouchDB will throw an error. But we do have some possible options.
- Wrap the KobolCollect data inside an attribute before sending it to CouchDB. For this, you can use Custom Wrapper - {"raw":"%SUBMISSION%"}. On the CouchDB side, this will auto-add the _id and put the whole data under raw. This won't work if you want to remove some of the attributes or want it directly under.
- Don't send the fields that start with _, or the ones that don't have _ prefixes. This won't work if you're going to use some of those attributes.
- Send everything, and use update function on CouchDB to reformat the data before inserting.
All three options are useful and will work based on your requirements. I prefer the third option as I would like to use some of the data attributes that start with _ as well. For this, I use the CouchDB update functions in the design document. In the example, I am also saving the raw data, but I don't have to.
Table of Contents
KoboToolbox Payload
{
"start": "2022-05-26 07:00:00.247000+05:30",
"end": "2022-05-26 07:01:51.391000+05:30",
"Name_of_Restaurant": "",
"Idly_Two": 50,
"Masala_Dosa": 50,
"Vada": 25,
"Is_Coffee": "regular",
"Regular_Coffee": 12,
"Mini_Coffee": "",
"AC": "no",
"Type_of_Restaurant": "standing_sitting_inside",
"Type_of_Restaurant/standing_sitting_outside": 0,
"Type_of_Restaurant/standing_sitting_inside": 1,
"Type_of_Restaurant/standing_sitting_roadside": 0,
"Any_comments": "",
"Location": "12.834402 77.694378 0 0",
"_Location_latitude": 12.834402,
"_Location_longitude": 77.694378,
"_Location_altitude": 0,
"_Location_precision": 0,
"_id": 159196131,
"_uuid": "15435e6e-4334-440d-92cd-734f9b2671d7",
"_submission_time": "2022-05-26 01:40:45",
"_validation_status": "validation_status_approved",
"_notes": "",
"_status": "submitted_via_web",
"_submitted_by": "",
"__version__": "vGM53aixR2imyfqE48a2hp",
"_tags": "",
"_index": 1
}
Update Function
We picked some attributes from the body and set them to the doc. We return an array; the first object is doc, which is inserted into db. The second object is a stringified version of data, which is returned as the response to the call. We can also return any other string, like "doc inserted."
//Update function
function(doc, req) {
var body = JSON.parse(req.body);
//extract fields from the body and
//create the document
if (!doc) doc = {_id: String(body._id)};
doc["submitted_by"] = body._submitted_by;
doc["submission_time"] = body._submission_time;
doc["notes"] = body._notes;
// Add others, format them if required, set default values etc
doc["Name_of_Restaurant"] = body.Name_of_Restaurant
//store all the raw data if required
doc["raw"] = body;
return [doc, JSON.stringify(doc)];
}
CouchDB Design Document
We will create a design document called _design/webhooks and then set the updates function called kobocollect. Remember to escape function ( or stringify ) content before placing it in JSON.
{
"_id": "_design/webhooks",
"updates": {
"kobocollect": "function(doc, req) {\n var body = JSON.parse(req.body);\n if (!doc) doc = {_id: String(body._id)};\n doc[\"submitted_by\"] = body._submitted_by;\n doc[\"submission_time\"] = body._submission_time; \n doc[\"notes\"] = body._notes;\n doc[\"Name_of_Restaurant\"] = body.Name_of_Restaurant\n doc[\"raw\"] = body;\n return [doc, JSON.stringify(doc)];\n}"
}
}
URL for the Webhook
We have to use the url of the updates function to do the submission. It will look like this.
https://{{db_credentials}}@{{db_url}}/_design/webhooks/_update/kobocollect
Inserted record
This is how the document looks like in CouchDB.
{
"_id": "159196131",
"submitted_by": "",
"submission_time": "2022-05-26 01:40:45",
"notes": "",
"Name_of_Restaurant": "",
"raw":
{
"start": "2022-05-26 07:00:00.247000+05:30",
"end": "2022-05-26 07:01:51.391000+05:30",
"Name_of_Restaurant": "",
"Idly_Two": 50,
"Masala_Dosa": 50,
"Vada": 25,
"Is_Coffee": "regular",
"Regular_Coffee": 12,
"Mini_Coffee": "",
"AC": "no",
"Type_of_Restaurant": "standing_sitting_inside",
"Type_of_Restaurant/standing_sitting_outside": 0,
"Type_of_Restaurant/standing_sitting_inside": 1,
"Type_of_Restaurant/standing_sitting_roadside": 0,
"Any_comments": "",
"Location": "12.834402 77.694378 0 0",
"_Location_latitude": 12.834402,
"_Location_longitude": 77.694378,
"_Location_altitude": 0,
"_Location_precision": 0,
"_id": 159196131,
"_uuid": "15435e6e-4334-440d-92cd-734f9b2671d7",
"_submission_time": "2022-05-26 01:40:45",
"_validation_status": "validation_status_approved",
"_notes": "",
"_status": "submitted_via_web",
"_submitted_by": "",
"__version__": "vGM53aixR2imyfqE48a2hp",
"_tags": "",
"_index": 1
}
}
This way, we can receive the document submitted at KoboToolbox at CouchDB in real-time. A downstream application uses that to show the data or do further analysis. This is a simple architecture to build survey apps that can deliver the survey results directly to a website or a real-time dashboard of real-time survey status display, etc.
1 Response
[…] survey results or dashboards using KoboToolbox and CouchDB. I wrote a blog post about using webhooks to push data from KoboToolbox to CouchDB in real-time, which is part of this […]