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)
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.
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
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
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.
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
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.
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
Last updated
Was this helpful?