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

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

score

double

Score secured by the user on the Question

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

~~contentversion~~

text

Not used

datetime

timestamp

~~gra~~de

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

result

text

~~score~~

text

Not used

status

int

Content consumption completion status

viewcount

int

Number of times user has consumed the content

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

total_score

double

Total score achieved by the user

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)

~~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

progress

int

Count of contents part of the Course that are consumed completely

status

int

User Course consumption completion status (1 -> in progress, 2 -> completed)

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

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

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

PreviousCassandra NextElasticSearch

Last updated 1 year ago