Assessment DB Schema

UCI uses postgreSQL for storing data of conversations. ODK transformer uses this database to store its assessment data for each question & answer.

Schema

Assessment data consists of 4 tables, which are listed below.

Question

This table keeps the details of question asked to user.

Field	Type	Description
id	uuid
form_id	varchar(100)	odk form id
form_version	varchar(10)	odk form version
x_path	varchar(500)	odk question xpath
question_type	varchar(500)
meta	jsonb	extra meta data in form of json
created	timestamp
updated	timestamp

Assessment

This table keeps the details of answer submitted by user for a specific question.

Field	Type	Description
id	uuid
question	uuid	id of table question
answer	text	answer given by user for question
bot_id	uuid	bot id
user_id	uuid	fusion auth returned user id
device_id	uuid	fusion auth returned user id
meta	jsondb	extra meta data in form of json
created	timestamp
updated	timestamp

Xmessage

This table keeps the records of messages sent to user against his/her phone number.

Field	Type	Description
id	bigint
phone_no	varchar(15)	phone number of user
updated_at	timestamp
message	varchar(1000)	message sent to user
is_last_message	boolean

XMessage State

This table keeps the odk instance xml state against the user phone number and form name.

Field	Type	Description
id	bigint
phone_no	varchar(15)	phone number of user
state	text	current xml state of form
previous_path	varchar(100)	previous question xpath
bot_form_name	varchar(50)	odk form name
updated_at	timestamp