Table used for storing user profile details
| Column Name | Data Type | Description | Sample Value |
|---|---|---|---|
| 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 | [email protected] | |
| 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 | |
| list<text> | Not Used | ||
| l | 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 | |
| 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"] |
| 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 | |
| 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. | |
| text | Not used | ||
| text | Not used |
Table used for supporting user lookup based on email/phone/username.
| Column Name | Data Type | Description | Sample Data |
|---|---|---|---|
| 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 |
Table used for storing user information from the third party system (SSO integrated systems)
| Column Name | Data Type | Description | Sample Data |
|---|---|---|---|
| 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 |
| text | Not Used | ||
| timestamp | Not Used | ||
| text | Not Used | ||
| text | Not Used | ||
| text | Not Used | ||
| userid | text | UUID of the user in sunbird system | ee27b0bc-be5c-427f-9cc4-a87079ef3dd7 |
| Column Name | Data Type | Description | Sample Data |
|---|---|---|---|
| userid | text | UUID of the user | adc314fa-cd62-4d8c-8b06-5c30091d0009 |
| organisationid | text | RootOrg Identifier to which user is associated with | 01276175508980531215 |
| text | Not Used | ||
| text | Not Used | ||
| approvaldate | text | Date on which self declared user record was approved on | 2019-03-27 13:07:14:732+0000 |
| 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 |