Data Dictionary

List of tables in Cassandra database used in LMS service

sunbird_courses.question [PRIMARY KEY: id] - (User Defined Type - used in sunbird_courses.assessment_aggregator)

Column Name	Data Type	Description	Sample Data
id	text	identifier of the question	do_2129173993575219201147
assess_ts	timestamp	Time at which the question was attempted by the user	2020-02-12 05:02:18.172000+0000
max_score	double	Maximum score that can be secured on the Question	1
score	double	Score secured by the user on the Question	0
type	text	Type of the Question	mcq
title	text	Title of the Question	Explore Question 1
resvalues	list<frozen<map<text, text>>>	Question Response Value	[{'2': '{"text":"Chlorophyll\n"}'}]
params	list<frozen<map<text, text>>>	List of Question Parameters	[{'1': '{"text":"Stomata\n"}'}, {'2': '{"text":"Chlorophyll\n"}'}, {'3': '{"text":"Chloroplast\n"}'}, {'4': '{"text":"Guard cells (correct)\n"}'}, {'answer': '{"correct":["4"]}'}]
description	text	Question Description	Sample Question
duration	decimal	time in seconds	2.0

sunbird_courses.user_content_consumption [PRIMARY KEY (userid, contentid, batchid, courseid)]

Table used to store user content consumption status as part of Course consumption.

Column Name	Data Type	Description	Sample Data
userid	text	UUID of the user	e44c93fb-ca7f-4d14-a8cb-c10144fc34c4
contentid	text	Content Identifier whose consumption details is recorded in the row	do_2129175755621335041147
batchid	text	Batch Identifier enrolled by the user	01291957301247180833
courseid	text	Course Identifier to which Batch belongs to	do_2129195698820055041246
completedcount	text		1
contentversion	text	Not used
datetime	timestamp
grade	text	Not used
lastaccesstime	text	Last Content access time	2020-06-04 13:10:31:296+0000
lastcompletedtime	text	Last Content Completion time	2020-06-04 12:52:23:964+0000
lastupdatedtime	text	Latest record updated time	2020-06-04 13:10:31:307+0000
progress	int	Maximum Content Consumption Progress percentage irrespective of view	70
result	text
score	text	Not used
status	int	Content consumption completion status	2
viewcount	int	Number of times user has consumed the content	7

sunbird_courses.user_activity_agg [PRIMARY KEY ((activity_type, activity_id, user_id), context_id)]

Table used to store User's Course consumption progress aggregates

Column Name	Data Type	Description	Sample Data
activity_type	text	Type of the activity - Course, CourseUnit, Quiz etc	Course
activity_id	text	Id of the activity - course_id, content_id etc	do_21282054143234048014178
user_id	text	User Id	9ac843e9-47ee-4019-80ad-4ab23bf96045
context_id	text	Context in which the activity happened. Combination of type:value.	cb:01282120178297241653
agg	map<text, int>	Number of contents consumed by the user in the course Not used now. Migrated to aggregates column. aggregates column is created to get the attempts counts.	{'completedCount': 2}
agg_details	list<text>	Aggregate metrics for the user and activity combination	['{"max_score":5.0,"score":4.0,"type":"attempt_metrics","attempt_id":"19ac65ea5ec2e9ed9d29aacdbd46fddd","content_id":"do_212959942036119552130","attempted_on":1582086185}']
agg_last_updated	map<text, timestamp>	When did the aggregate metrics were last updated?	{'completedCount': '2020-08-19 05:55:48.000000+0000', 'max_score:do_212959942036119552130': '2021-12-02 06:42:17.037000+0000', 'score:do_212959942036119552130': '2021-12-02 06:42:17.037000+0000'}
aggregates	map<text, double>	consumption aggregate metrics of the assessments in the course	{'attempts_count:do_212959942036119552130': 1, 'completedCount': 2, 'max_score:do_212959942036119552130': 5, 'score:do_212959942036119552130': 4}

sunbird_courses.assessment_aggregator [PRIMARY KEY ((user_id, course_id), batch_id, content_id, attempt_id)]

Table used to store User's Assessment Contents' consumption aggregates

Column Name	Data Type	Description	Sample Data
user_id	text	UUID of the user	30b2571f-08f9-49ce-b97a-c643df0c82f7
course_id	text	Course Identifier	do_21295500947955712011099
batch_id	text	Batch Identifier	01295501508689100844
content_id	text	Assessment Content Identifier	do_2129493126207324161154
attempt_id	text	Attempt Identifier	638a8d6240f8df4b8cc5ef9b79fa0d67
created_on	timestamp	Record created on	2020-02-12 03:52:51.687000+0000
grand_total	text	Assessment aggregate score	1.0/8.0
last_attempted_on	timestamp	Last attempted timestamp of the assessment	2020-02-12 03:52:19.666000+0000
question	list<frozen<question>>	Question Details	[{id: 'do_2129173993575219201147', assess_ts: '2020-02-12 05:02:18.172000+0000', max_score: 1, score: 0, type: 'mcq', title: 'Explore Question 1', resvalues: [{'2': '{"text":"Chlorophyll\n"}'}], params: [{'1': '{"text":"Stomata\n"}'}, {'2': '{"text":"Chlorophyll\n"}'}, {'3': '{"text":"Chloroplast\n"}'}, {'4': '{"text":"Guard cells (correct)\n"}'}, {'answer': '{"correct":["4"]}'}], description: 'By kiruba', duration: 2.000000000000000000}, {id: 'do_2129174037204910081236', assess_ts: '2020-02-12 05:02:16.590000+0000', max_score: 1, score: 0, type: 'mcq', title: 'Explore Question 6', resvalues: [{'2': '{"text":"FeSO4 acts as an oxidising agent and oxidises KMnO4\n"}'}], params: [{'1': '{"text":"KMnO4 is an oxidising agent, it oxidises FeSO4 (correct)\n"}'}, {'2': '{"text":"FeSO4 acts as an oxidising agent and oxidises KMnO4\n"}'}, {'3': '{"text":"The colour disappears due to dilution, no reaction is involved\n"}'}, {'4': '{"text":"KMnO4 is an unstable compound and decomposes in the presence of FeSO4 to a colourless compound\n"}'}, {'answer': '{"correct":["1"]}'}], description: 'By kiruba', duration: 2.000000000000000000}, {id: 'do_2129174014605885441232', assess_ts: '2020-02-12 05:02:14.750000+0000', max_score: 1, score: 0, type: 'mcq', title: 'Explore Question 3', resvalues: [{'2': '{"text":"Breaking down water molecule\n"}'}], params: [{'1': '{"text":"Absorbing light (correct)\n"}'}, {'2': '{"text":"Breaking down water molecule\n"}'}, {'3': '{"text":"No function\n"}'}, {'4': '{"text":"Reduction of CO2\n"}'}, {'answer': '{"correct":["1"]}'}], description: 'By kiruba', duration: 2.000000000000000000}, {id: 'do_2129174030324285441235', assess_ts: '2020-02-12 05:02:13.160000+0000', max_score: 1, score: 1, type: 'mcq', title: 'Explore Question 5', resvalues: [{'2': '{"text":"a brown residue is left (correct)\xa0\n"}'}], params: [{'1': '{"text":"crystals immediately melt\n"}'}, {'2': '{"text":"a brown residue is left (correct)\xa0\n"}'}, {'3': '{"text":"white fumes appear in the test tube\n"}'}, {'4': '{"text":"a yellow residue is left\n"}'}, {'answer': '{"correct":["2"]}'}], description: 'By kiruba', duration: 2.000000000000000000}, {id: 'do_2129195303901757441628', assess_ts: '2020-02-12 05:02:11.348000+0000', max_score: 1, score: 0, type: 'mcq', title: 'Question 7', resvalues: [{'2': '{"text":"Ravivarman\n"}'}], params: [{'1': '{"text":"Narasimhavarman II (correct)\n"}'}, {'2': '{"text":"Ravivarman\n"}'}, {'3': '{"text":"Mahendravarman\n"}'}, {'4': '{"text":"Devavarman\n"}'}, {'answer': '{"correct":["1"]}'}], description: 'By kiruba', duration: 2.000000000000000000}, {id: 'do_2129195314499747841629', assess_ts: '2020-02-12 05:02:09.774000+0000', max_score: 1, score: 0, type: 'mcq', title: 'Question 8', resvalues: [{'2': '{"text":"Sabha\n"}'}], params: [{'1': '{"text":"Ur (correct)\n"}'}, {'2': '{"text":"Sabha\n"}'}, {'3': '{"text":"Nagaram\n"}'}, {'4': '{"text":"Kurram\n"}'}, {'answer': '{"correct":["1"]}'}], description: 'By kiruba', duration: 2.000000000000000000}, {id: 'do_2129174008785797121231', assess_ts: '2020-02-12 05:02:08.046000+0000', max_score: 1, score: 0, type: 'mcq', title: 'Explore Question 2', resvalues: [{'2': '{"text":"Water\n"}'}], params: [{'1': '{"text":"Chlorophyll\n"}'}, {'2': '{"text":"Water\n"}'}, {'3': '{"text":"Blood\n"}'}, {'4': '{"text":"haemoglobin (correct)\n"}'}, {'answer': '{"correct":["4"]}'}], description: 'By kiruba', duration: 3.000000000000000000}, {id: 'do_2129174021075271681233', assess_ts: '2020-02-12 05:02:05.443000+0000', max_score: 1, score: 0, type: 'mcq', title: 'Explore Question 4', resvalues: [{'2': '{"text":"Small globules\n"}'}], params: [{'1': '{"text":"Carbohydrates\n"}'}, {'2': '{"text":"Small globules\n"}'}, {'3': '{"text":"Amino acids (correct)\n"}'}, {'4': '{"text":"Starch\n"}'}, {'answer': '{"correct":["3"]}'}], description: 'By kiruba', duration: 3.000000000000000000}]
total_max_score	double	Total Maximum score of assessments of the course	8
total_score	double	Total score achieved by the user	1
updated_on	timestamp	record updated on	2020-02-12 05:02:33.088000+0000

sunbird_courses.course_batch [PRIMARY KEY (courseid, batchid)]

Table used to store Batch information created against Courses.

Column Name	Data Type	Description	Sample Data
courseid	text	Course Identifier	do_2134405168915005441176
batchid	text	Batch Identifier	013440524934594560116
cert_templates	map<text, frozen<map<text, text>>>	Certificate template details linked to the Batch	{'do_2134278778780631041124': {'criteria': '{"enrollment":{"status":2},"assessment":{"score":{">=":50}}}', 'identifier': 'do_2134278778780631041124', 'issuer': '{"name":"Gujarat Council of Educational Research and Training","url":"https://gcert.gujarat.gov.in/gcert/"}', 'name': 'sandhya', 'previewUrl': 'https://sunbirdstagingpublic.blob.core.windows.net/sunbird-content-staging/content/do_2134278778780631041124/artifact/do_2134278778780631041124_1639145248983_certificate_2021-12-10_19_37.svg', 'signatoryList': '[{"name":"sadndy","image":"data:image/png;base64,/9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAIBAQIBAQICAgICAgICAwUDAwMDAwYEBAMFBwYHBwcGBwcICQsJCAgKCAcHCg0KCgsMDAwMBwkODw0MDgsMDAz/wAALCAAuAHABAREA/8QAHAABAAIDAAMAAAAAAAAAAAAAAAYHAwUIAgQJ/8QAMBAAAQQBAwMCBAYCAwAAAAAAAgEDBAUABgcREhMhCEEUIjEyI0JRYXFyFTOBgpL/2gAIAQEAAD8A+/mMYxjNBrPcGr28qEmW0n4dtxwWGgECcekOl9rTYCikZr7CKKuR/TXqI03qXV7GnzK0pruYBORYNxWv1700R8krPdEUc6ffpVeMn+MYxjGMZFd0twWdt9MuTljPTJrpjGgQ2v8AZOkmvS20P8r9V/KPK5odgr3UdzJ1YzqOwi2L1ZahFbWNHRpmOXwkdx1pv3IAdcMUIvmXjzlkZUu8bHY9Q20suUqDWjLs448/ak1yGvYX+3bCSKfzmDfOAuut+9qaKMXz0Vs/quaQfVlhmG/FBF/Y3ZYp/wBVy4cZBd690JG21ZTM1sVqwvNR28apr4zhqIkply64vHnpbZF1xf6ZOsYxjGVrpeS3ubvXd2R9LtfoV1KiAP5fjDaByS7/ACIONtJ+n4v65DNIeqrT0DV17U0Gm7D/AA8Kej0mzR1sWrB+VLVlXGBUlJ7qf608ceUyzIW8+nLS1r4cWyCVItJ0uvjoy2RIbsXqSR54+1shUSL7erhOcgG9m6+mNf8ApomX0h+dFizJDiafdjohS5Mth4vhpMcff52u6Kr47fzFwnPHKcT1b7lX2zeoLiugXVbuJqqbHpbC6rqX4xtiWbgMRIdcLn4aRhF9HhfeL8TuuGIefHZWjt+Y7Op4ekJlZqBq2g1AWM16xdhibEVFJtJD/Q8vT1k2a/T2XJtN15S1hmki1r2VbfZil1yBHpde4Rlvyv3H1J0p785BZzAa49YEESQXGNB6cOT/AFlWD3bBf5RmI7/w7lrYxjGMp2w2H1U+GoaWs1W1p/T2obKTYvyoMVf8s38R5cbacJe23wSrw50kX7Z46e9I1fpfRdDWQLmXFmUrzLiTmojIkYtRzjtgIdPSCALhEHHPSa9XnINvXsDo/wBPexdhduXWs3JGn6CZVxO1NQpc+MTan8EKC2n3E2hKYILnKKRH45SNbN7AG3N0JW7ly6loKXSsaQtcLvTWxgDpbCCx18IQB20cfcX5nS7X5E4y0aXQ8T1CTtVXUOQdfUyLGsco7FhtFV1+vXuDLESTpNtXCRtPYhaX6eMyTPRbTXjtxOsr7Ur19qeIcC9to77caRaxyFRFgkEOltsEX5BDjjleVXNRbeiuwK8YsK/XM+U5GvWtSCxdQGprUiaDAsCTqh2yIAEepsAUBAvKftLvT9s7cbZax3Cn3M1+3k6mtY0xuxedTrlCEJhpRRsfDLYOC4ggnt598tbGMYxjPUsrBmphOSpDgMR2AVx1wy6RbFPKquc3atu5O+VdBF0Xhm7kIUGhhLx3KqhUg+LnuD+U3mv/AD3Wg5+udDX+j6jVUdpi0q66yZYLraCXGB4W1T3RCReF/fNk20LQIIiIiP2pmTGMYxjGMZo9w9Exdx9A3VBON5uFeQnoD5Ml0uI26CgXC/rwq5qtudmKrbSRImtOzLK5nADMmymmJSHmw+xtOkRBtsfYGxEU/TJjjGMYxjP/2Q==","designation":"CEO","id":"CEO/CEO"}]', 'url': 'https://sunbirdstagingpublic.blob.core.windows.net/sunbird-content-staging/content/do_2134278778780631041124/artifact/do_2134278778780631041124_1639145248983_certificate_2021-12-10_19_37.svg'}}
created_date	timestamp	Batch Creation Date	2021-12-15 12:19:07.049000+0000
createdby	text	Batch Created By	fca2925f-1eee-4654-9177-fece3fd6afc9
createddate	text	Not used
createdfor	list<text>	Batch creator user Organisations	['01269878797503692810', '01275629407654707220300']
description	text	Batch Description	Sample Batch
end_date	timestamp	Batch End Date
enddate	text	Not used
enrollment_enddate	timestamp	Batch Enrolment End Date	2022-06-20
enrollmentenddate	text	Not used
enrollmenttype	text	Batch Enrolment Type	open
mentors	list<text>	Mentors of the batch
name	text	Name of the Batch	Sample Batch
start_date	timestamp	Batch Enrolment Start Date	2021-12-15 12:19:07.049000+0000
startdate	text	Not used
status	int	status of the Batch (running or closed)	1
tandc	boolean	Not used
updated_date	timestamp	record last updated date	2021-12-15 12:19:07.049000+0000
updateddate	text	Not used

sunbird_courses.user_enrolments [PRIMARY KEY (userid, courseid, batchid)]

Table used to capture User's information who have enrolled to Course Batch

Column Name	Data Type	Description	Sample Data
userid	text	UUID of the user	02b8bade-c8ed-44c7-87fd-ebabeaed98c2
courseid	text	Course Identifier	do_213432103242072064167
batchid	text	Batch Identifier	0134321075692503049
active	boolean	User Batch Enrolment status	True
addedby	text		abf44b3d-877d-7de8-1da3-1603a727cb10
certificates	list<frozen<map<text, text>>>	Not Used
certstatus	int	Not Used
completedon	timestamp	Time at which user has completed the course consumption	2023-03-23 12:38:05.055000+0000
completionpercentage	int	Course Consumption percentage
contentstatus	map<text, int>	Course's Content consumption status	{'do_21337188080177971211': 2, 'do_21353023582218649615542': 2}
datetime	timestamp	record last updated on	2023-03-23 12:38:05.063000+0000
enrolled_date	timestamp	Time at which user enrolled to Course Batch	2023-03-23 12:37:12.105000+0000
enrolleddate	text	Not Used
issued_certificates	list<frozen<map<text, text>>>	Certificate details issued to the user upon course completion	[{'identifier': '1-ee112467-bea6-4218-82c6-692ad62d3f76', 'lastIssuedOn': '2023-03-23T12:38:05.981+0000', 'name': 'KA merit cert', 'templateUrl': 'https://sunbirdstagingpublic.blob.core.windows.net/sunbird-content-staging/content/do_213550147041189888119/artifact/do_213550147041189888119_1654070683979_certificate_2022-06-01_13_34.svg', 'token': '', 'type': 'TrainingCertificate'}]
lastcontentaccesstime	timestamp	Latest Timestamp at which user accessed the course	2023-03-23 12:37:57.451000+0000
lastreadcontentid	text	Latest content id consumed by the user from the course	do_21337188080177971211
lastreadcontentstatus	int	Consumption completion status of the recently consumed content by the user from the course	2
progress	int	Count of contents part of the Course that are consumed completely	3
status	int	User Course consumption completion status (1 -> in progress, 2 -> completed)	2

sunbird_courses.report_user_enrolments [PRIMARY KEY (userid, courseid, batchid)]

Table same as that of 'sunbird_courses.user_enrolments' used for certificate migration

sunbird_courses.bulk_upload_process [PRIMARY KEY: id]

Table used to store information about the bulk user enrolments file upload process.

Column Name	Data Type	Description	Sample Data
id	text	Bulk Upload process Id	0128261582844723205
data	text	Bulk Enrolment request Data	[{"userIds":"eda95496-75db-4c98-bdcf-9812d62d49a8","batchId":"01282120178297241000"},{"userIds":"bfbd3ce2-d55e-45d2-9ca7-939dfaf18db4","batchId":"01282120178297241653"},{"userIds":"46cde213-a7fd-4f3d-a38b-f0b0b147ef60","batchId":"01282120178297241653"},{"userIds":"f8db86ba-bfe5-4f40-bfb5-c16a402beac2","batchId":"01282120178297241653"},{"userIds":"4a58a3ff-cfc2-445b-b961-3cb5b630ef90","batchId":"01282120178297241653"},{"userIds":"526f9591-c023-46cc-9c80-e8d3ec89b6b9","batchId":"01282120178297241653"},{"userIds":"e2187344-27f7-4683-8376-52181bffc4b4","batchId":"01282120178297241653"},{"userIds":"e905d310-f559-4dfc-a856-ff50a85c5e55","batchId":"01282120178297241653"},{"userIds":"872cdf0d-df7b-4845-be50-eb9d8aa37fcc","batchId":"01282120178297241653"},{"userIds":"1295ef17-38aa-48d3-ac08-e8eb9db00bd8","batchId":"01282120178297241653"},{"userIds":"e507427b-2bde-4aed-bc9a-b2b0f9020305","batchId":"01282120178297241653"},{"batchId":"0128212017829724165391350a7c-56ec-42d0-ba5e-75b2ab943d81"},{"userIds":"85fc81c7-7ac0-4416-b1c0-c8591a88fc8d","batchId":"01282120178297241653"},{"userIds":"162e2865-461c-4793-beb0-9012800b11f2","batchId":"01282120178297241653"},{"userIds":"10b11cbe-f328-4e4e-a350-bdcd54d2ba97","batchId":"01282120178297241653"},{"userIds":"ced03895-9fce-4db2-9cfe-db097ca3e224","batchId":"01282120178297241653"},{"userIds":"9cdaea2e-c7c5-4a44-b846-b4379682abd2","batchId":"01282120178297241653"}]
failureresult	text	Enrolment Failure Data	[{"userIds":"eda95496-75db-4c98-bdcf-9812d62d49a8","batchId":"01282120178297241000","err_msg":"Invalid course batch id "},{"01282120178297241653":[]},{"01282120178297241653":[]},{"01282120178297241653":[]},{"01282120178297241653":[]},{"01282120178297241653":[]},{"01282120178297241653":[]},{"01282120178297241653":[]},{"01282120178297241653":[]},{"01282120178297241653":[]},{"01282120178297241653":[]},{"batchId":"0128212017829724165391350a7c-56ec-42d0-ba5e-75b2ab943d81","err_msg":"Invalid course batch id "},{"01282120178297241653":[]},{"01282120178297241653":[]},{"01282120178297241653":[]},{"01282120178297241653":[]},{"01282120178297241653":[]}]
objecttype	text	Bulk Upload Process Type	batchLearnerEnrol
processendtime	text	Time at which record processing was completed	2019-08-13 10:40:32:476+0000
processstarttime	text	Time at which records processing was started	2019-08-13 10:40:31:178+0000
status	int	Status of the Process	3
successresult	text	Enrolment Success Data	[{"01282120178297241653":[{"bfbd3ce2-d55e-45d2-9ca7-939dfaf18db4":"SUCCESS"}]},{"01282120178297241653":[{"46cde213-a7fd-4f3d-a38b-f0b0b147ef60":"SUCCESS"}]},{"01282120178297241653":[{"f8db86ba-bfe5-4f40-bfb5-c16a402beac2":"SUCCESS"}]},{"01282120178297241653":[{"4a58a3ff-cfc2-445b-b961-3cb5b630ef90":"SUCCESS"}]},{"01282120178297241653":[{"526f9591-c023-46cc-9c80-e8d3ec89b6b9":"SUCCESS"}]},{"01282120178297241653":[{"e2187344-27f7-4683-8376-52181bffc4b4":"SUCCESS"}]},{"01282120178297241653":[{"e905d310-f559-4dfc-a856-ff50a85c5e55":"SUCCESS"}]},{"01282120178297241653":[{"872cdf0d-df7b-4845-be50-eb9d8aa37fcc":"SUCCESS"}]},{"01282120178297241653":[{"1295ef17-38aa-48d3-ac08-e8eb9db00bd8":"SUCCESS"}]},{"01282120178297241653":[{"e507427b-2bde-4aed-bc9a-b2b0f9020305":"SUCCESS"}]},{"01282120178297241653":[{"85fc81c7-7ac0-4416-b1c0-c8591a88fc8d":"SUCCESS"}]},{"01282120178297241653":[{"162e2865-461c-4793-beb0-9012800b11f2":"SUCCESS"}]},{"01282120178297241653":[{"10b11cbe-f328-4e4e-a350-bdcd54d2ba97":"SUCCESS"}]},{"01282120178297241653":[{"ced03895-9fce-4db2-9cfe-db097ca3e224":"SUCCESS"}]},{"01282120178297241653":[{"9cdaea2e-c7c5-4a44-b846-b4379682abd2":"SUCCESS"}]}]
uploadedby	text	UUID of the user who initiated Bulk upload process	4707a109-edee-455b-9e6c-a9a45e7f331c
uploadeddate	text	Time at which Bulk upload was done	2019-08-13 10:40:31:163+0000