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 NameData TypeDescriptionSample 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 NameData TypeDescriptionSample 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 NameData TypeDescriptionSample 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 NameData TypeDescriptionSample 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 NameData TypeDescriptionSample 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 NameData TypeDescriptionSample 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 NameData TypeDescriptionSample 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

Last updated