User
unbird.user (PRIMARY KEY: id)
Table used for storing user profile details
id
text
UUID
9b774c71-6034-4de7-aa38-5382fc673b14
alltncaccepted
map<text, text>
Terms and Conditions that the user has accepted.
1) Org Admin TNC if the user has ORG_ADMIN role
2) Report Viewer role TNC if the user has REPORT_VIEWER role
3)Groups tnc - if the user has groups.
"allTncAccepted": { "reportViewerTnc": { "tncAcceptedOn": "2023-01-02 05:25:21:586+0000", "version": "4.0.0" }, "orgAdminTnc": { "tncAcceptedOn": "2020-11-26 08:17:59:547+0000", "version": "3.5.0" }, "groupsTnc": { "tncAcceptedOn": "2020-12-03 09:14:47:437+0000", "version": "3.5.0" } }
channel
text
Tenant Organisation channel value
TN, AP
countrycode
text
Country code of the user
+91
createdby
text
uuid of the created user, null in case user has signed up by himself
8c774c71-6034-4de7-aa38-5382fc673b14
createddate
text
Date on which the user was created
2020-09-28 15:47:15:919+0000
dob
text
Date of Birth of the user. Only year is provided by the user. Month and date(12-31) is appended to it by the system
1987-12-31
text
Email id of the user in encrypted format
testdoc@yopmail.com
emailverified
boolean
Email is verified or not using OTP. This flag is not used anymore
true or false
firstname
text
First name of the user
flagsvalue
int
Value will by 4 , if the user is uploaded by the tenant or registered through tenant login page. ( Earlier there were different values updated as per the user email verified, phone verified and tenant verified. But currently only tenant verification is stored.)
4
framework
map<text, frozen<list<text>>>
User chosen framework
{ "board": ["State (Tamil Nadu)"], "gradeLevel": ["Class 1"], "id": ["tn_k-12_5"], "medium": ["English"], "subject": ["Mathematics"] }
isdeleted
boolean
User is soft deleted or not. Scenarios:
1) Merge one user to another, then the first one gets deleted.
2) User Block will update the is_deleted flag to true and status to 0 (inactive)
true or false
lastname
text
Last Name of the user
locationids
list<text>
Not Used
loginid
text
Not Used
managedby
text
Logged in user/Parent uuid of the managed user. If managedby column has value( parent uuid), that means this row of record is child user's and email and phone number will be blank for this user.
7c784c71-9034-4de7-aa38-5382fc673b14
maskedemail
text
Masked email id value
te*****@yopmail.com
maskedphone
text
Masked phone number value
98******09
phone
text
Phone number of the user in encrypted format
phoneverified
boolean
Email is verified or not using OTP. This flag is not used anymore
prevusedemail
text
Previously used email id in encrypted format
prevusedphone
text
Previously used phone number in encrypted format
profilelocation
text
Used to store location data of the user from release-3.9.0. This stores the location types and code from sunbird.location table. This is validated against the configuration of location types in properties file "sunbird_valid_location_types" and also to ED form api configuration "profileconfig_v2"
"profileLocation": [ { "code": "32", "type": "state" }, { "code": "3210", "type": "district" },
{"code": "321001", "type": "block"} ,{"code": "32100123", "type": "cluster"]
profileusertype
text
Not Used from release-4.4.0.This field was in use from release-3.9.0 to 4.4.0.
{ "type": "administrator", "subType":"hm" }
profileusertypes
text
Used to store user type (role) and subusertype (subrole) from release-4.4.0. This is validated against the ED form api configuration "profileconfig_v2"
"profileUserTypes": [ { "type": "administrator", "subType":"hm" }, { "type": "administrator", "subType":"deo" }],
recoveryemail
text
Recovery email of the user in encrypted form
recoveryphone
text
Recovery phone number of the user in encrypted form
roles
list<text>
Not used
rootorgid
text
Tenant Org id of the user
0126796199493140480
status
int
User is active or not. '1' means active, '0' is inactive
1
tncacceptedon
timestamp
terms and conditions accepted time in long format
1687933998587
tncacceptedversion
text
Version of the terms and conditions. If any change in terms and conditions it is added with a new version and link is updated in system configuration.
v13
updatedby
text
UUID of the user who updated the profile.
7c784c71-9034-4de7-aa38-5382fc673b14
updateddate
text
Date in which this table record is updated last.
2023-06-28 06:34:02:020+0000
userid
text
UUID of the user, same as the id column value
9b774c71-6034-4de7-aa38-5382fc673b14
username
text
Username of the user, this can be given via create api or if not given its automatically created by appending firstname and random text.
usersubtype
text
Not used
usertype
text
Not used
sunbird.user_lookup [PRIMARY KEY (type, value)]
Table used for supporting user lookup based on email/phone/username.
type
text
field/column type
username/email/phone
value
text
encrypted value corresponding to the field of the user in 'userid' column
0D8+4tIadSIevBPI5TRVpz01bytYGtT59Fm6X1hbXTxLMh873Ibbq1Orv9HTxpM3orvEZRVVOy3E\n56xU3yrF/qtCQr8582HbLJKeRdHyl+sKLJ7enl6IgnHREqS+/HVeT6a+wzaAmCWueMEdPmZuRg==
userid
text
UUID of the user
51b39c1d-a021-4ce8-9487-4dcf843eb925
sunbird.usr_external_identity [PRIMARY KEY (provider, idtype, externalid)
Table used for storing user information from the third party system (SSO integrated systems)
provider
text
Organisation of the user
0132818330295992324
idtype
text
Type of the ID
UDAI
externalid
text
ID of the user in the third party system
ckc971
createdby
text
UUID of the admin who onboarded the user
1405f334-ee59-42fc-befb-51986221881e
createdon
timestamp
Timestamp at which user record was created on
2021-05-18 07:35:22.646000+0000
lastupdatedby
text
Not Used
lastupdatedon
timestamp
Not Used
originalexternalid
text
Not Used
originalidtype
text
Not Used
originalprovider
text
Not Used
userid
text
UUID of the user in sunbird system
ee27b0bc-be5c-427f-9cc4-a87079ef3dd7
sunbird.user_organisation [PRIMARY KEY (userid, organisationid)]
userid
text
UUID of the user
adc314fa-cd62-4d8c-8b06-5c30091d0009
organisationid
text
RootOrg Identifier to which user is associated with
01276175508980531215
addedby
text
Not Used
addedbyname
text
Not Used
approvaldate
text
Date on which self declared user record was approved on
2019-03-27 13:07:14:732+0000
approvedby
text
Not Used
associationtype
int
Stores information about how user to organsiation association was established
1/2/4 SSO -> 1 Self Declaation -> 2 System Upload -> 4
hashtagid
text
hashtagid of the RootOrg to which user is associated with
01276175508980531215
id
text
isapproved
boolean
Used to indicate whether user record was approved for association with organisation
True
isdeleted
boolean
Used to indicate whether user record is deleted
False
isrejected
boolean
Used to indicate whether user record was rejected for association with organisation
False
orgjoindate
text
Timestamp at which user was associated with organisation
2019-03-27 13:07:14:732+0000
orgleftdate
text
Timestamp at which user was disassociated with organisation
2019-06-18 11:24:03:263+0000
position
text
Not used
roles
list<text>
Roles associated with the user
['CONTENT_CREATOR', 'COURSE_MENTOR']
updatedby
text
Org Admin user UUID who updated the user information
59177b28-14a1-41e3-a5f9-87bff98443b3
updateddate
text
Timestamp at which user record was updated on
2019-04-10 10:32:09:999+0000
Last updated