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

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

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}

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

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

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

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

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

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

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

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

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 7 months ago