# Caching and Denormalising User Metadata

Various data products in sunbird depends on the user metadata which are generated on joining different tables from core cassandra and LP cassandra. The logic for joining the table and fetching the information per user type is complex.

#### Solution : <a href="#solution-1" id="solution-1"></a>

Fetch the information from cassandra table, compute all the required fields in the flink job (user-cache-updater job) and update all the fields to the redis cache.

<figure><img src="/files/dQuYip0Xws73vXlnSUyB" alt=""><figcaption></figcaption></figure>

#### Design for the fields to be fetched: <a href="#design-for-the-fields-to-be-fetched" id="design-for-the-fields-to-be-fetched"></a>

<table><thead><tr><th width="68"></th><th width="135">Field Name</th><th width="79">Type</th><th width="69">Table Name</th><th>Description</th></tr></thead><tbody><tr><td>1</td><td><strong>User-ID</strong></td><td>String</td><td>User</td><td>It indicates user unique Identifier</td></tr><tr><td>2</td><td><strong>Mobile Number</strong></td><td>String</td><td>User</td><td>User phone number in an encrypted format</td></tr><tr><td>3</td><td><strong>Email ID</strong></td><td>String</td><td>User</td><td>User mail id in an encrypted format</td></tr><tr><td>4</td><td>ID</td><td>String</td><td>User</td><td>User unique identifier</td></tr><tr><td>5</td><td>First Name</td><td>String</td><td>User</td><td>User first name</td></tr><tr><td>6</td><td>Last Name</td><td>String</td><td>User</td><td>User Last Name</td></tr><tr><td>7</td><td>Phone Verified</td><td>String</td><td>User</td><td>It indicates whether user is verified the phone number or not</td></tr><tr><td>8</td><td>Email Verified</td><td>String</td><td>User</td><td>It indicates whether user is verified the email or not</td></tr><tr><td>9</td><td>Flags Value</td><td>Int</td><td>User</td><td> </td></tr><tr><td>10</td><td>Framework</td><td>Map[String, List[String]]</td><td>User</td><td>User framework</td></tr><tr><td>11</td><td>Rootorgid</td><td>String</td><td>User</td><td>User root org id (can be used to differentiate between custodian and state user)</td></tr><tr><td>12</td><td>CreatedBy</td><td>String</td><td>User</td><td>User created By</td></tr><tr><td>13</td><td>Subject</td><td>List[String]</td><td>User</td><td>User subjects</td></tr><tr><td>14</td><td>Language</td><td>List[String]</td><td>User</td><td>User Language</td></tr><tr><td>15</td><td>Grade</td><td>List[String]</td><td>User</td><td>User grades</td></tr><tr><td>16</td><td>Roles</td><td>List[String]</td><td>User</td><td>User roles</td></tr><tr><td>17</td><td>Status</td><td>Int</td><td>User</td><td>User status</td></tr><tr><td>18</td><td>Webpages</td><td>List[Map[String, String]]</td><td>User</td><td> </td></tr><tr><td>19</td><td>Createddate</td><td>String</td><td>User</td><td>User created date</td></tr><tr><td>20</td><td>Emailverified</td><td>Boolean</td><td>User</td><td>User email is verified or not</td></tr><tr><td>21</td><td>Isdeleted</td><td>Boolean</td><td>User</td><td>User is deleted or not</td></tr><tr><td>22</td><td>Locationids</td><td>List[Strings]</td><td>User</td><td><ol><li>If the user is <strong>Self Signed Up (custodian) user</strong>: USER.locationids</li><li>If the user is <strong>tenant user:</strong> ORGANISATION.locationids</li></ol></td></tr><tr><td>23</td><td>Updateddate</td><td>String</td><td>User</td><td>User last updated date</td></tr><tr><td>24</td><td>Profilevisibility</td><td>String</td><td>User</td><td>User profile visibility</td></tr><tr><td>25</td><td>Loginid</td><td>String</td><td>User</td><td>User login id</td></tr><tr><td>26</td><td><strong>Username</strong></td><td>String</td><td>User</td><td>It’s a combination of user first name and last name columns</td></tr><tr><td>27</td><td><strong>External-ID</strong></td><td>String</td><td>User_external_identity</td><td><ol><li><p>If the user is a <strong>self signed up user in the custodian org</strong> then the user’s self declared  ID will be the value to the field.</p><ol><li> Filter the User_external_identity.idtype='<code>declared-ext-id</code>'</li><li>Join with ORG table with condition User_external_identity.provider=ORG.channel and fetch User_external_identity.userid , User_external_identity.externalID</li></ol></li><li><p>If the user is a <strong>state user</strong> then the tenant provided External ID will be the value to the field.</p><ol><li>Join USER and User_external_identity table with User_external_identity.idType =User.channel and User_external_identity.provider=User.channel and fetch User_external_identity.userid , User_external_identity.externalID</li></ol></li></ol></td></tr><tr><td>28</td><td><strong>School Name</strong></td><td>String</td><td>Custodian User: User_external_identity<br>State User: Organisation</td><td><ol><li><p>If the user is <strong>Self Signed Up (custodian) user</strong> then user’s self declared sub org Information code will be the value to the field.</p><ol><li>User_external_identity.idtype='declared-school-name' anf fetch User_external_identity.externalid,userid</li></ol></li><li><p>If the user is <strong>state user</strong> then tenant provided sub org Information will be the values.</p><ol><li>Join on User.userId = User_org.userId and then on get User_org.orgId and then join with <a href="http://organisation.id/">Organisation.id</a> where isRootOrg = false and fetch the Organisation.orgcode and ORG.orgname as school_name</li></ol></li></ol></td></tr><tr><td>29</td><td><strong>School UDISE Code</strong></td><td>String</td><td>Custodian User: User_external_identity<br>State User: Organisation</td><td><ol><li><p>If the user is <strong>Self Signed Up (custodian) user</strong> then user’s self declared sub org Information code will be the value to the field.</p><ol><li>User_external_identity.idtype='declared-school-udise-code' and fetch User_external_identity.externalid,userid</li></ol></li><li><p>If the user is <strong>tenant user</strong> then tenant provided sub org Information will be the values.</p><ol><li>Join on User.userId = User_org.userId and then on get User_org.orgId and then join with <a href="http://organisation.id/">ORGANISATION.id</a> where isRootOrg = false and fetch the ORGANISATION.orgcode and ORG.orgname as school_name</li></ol></li></ol></td></tr><tr><td>30</td><td><strong>State Name</strong></td><td>String</td><td>Location</td><td><ol><li><p>If the user is <strong>Self Signed Up (custodian) user</strong> then user’s self declared location will be the value to the field</p><ol><li><a href="http://user.locationids%3Dlocation.id/">USER.locationids=LOCATION.id</a> and LOCATION.type='state/' and fetch the name as LOCATION.{state_name},USER.userid</li></ol></li><li><p>If the user is <strong>tenant user</strong> then tenant location will be selected</p><ol><li>Join ORGANISATION and location table having condition:<br><a href="http://org.locationids%3Dlocation.id/">ORG.locationids=Location.id</a> &#x26;&#x26; Location.type='state and fetch the <a href="http://location.name/">Location.name</a>, <a href="http://org.id/">ORG.id</a></li><li>Join the Dataframe given in (i) and user table with condition:<br><a href="http://org.id/">ORG.id</a> = USER.rootorgid &#x26;&#x26; ORG.isrootorg=true and get the USER.userid, <a href="http://location.name/">Location.name</a></li></ol></li></ol></td></tr><tr><td>31</td><td><strong>District Name</strong></td><td>String</td><td>Location</td><td><ol><li><p>If the user is <strong>Self Signed Up (custodian) user</strong> then user’s self declared location will be the value to the field</p><ol><li><a href="http://user.locationids%3Dlocation.id/">USER.locationids=LOCATION.id</a> and LOCATION.type='district' and fetch the name as LOCATION.{district_name},USER.userid</li></ol></li><li><p>If the user is <strong>tenant user</strong> then then tenant location will be selected</p><ol><li>Join ORGANISATION and location table having condition:<br><a href="http://org.locationids%3Dlocation.id/">ORG.locationids=Location.id</a> &#x26;&#x26; Location.type='district and fetch the <a href="http://location.name/">Location.name</a>, <a href="http://org.id/">ORG.id</a></li><li>Join the Dataframe given in (i) and user table with condition:<br><a href="http://org.id/">ORG.id</a> = USER.rootorgid &#x26;&#x26; ORG.isrootorg=true and get the USER.userid, <a href="http://location.name/">Location.name</a></li></ol></li></ol></td></tr><tr><td>32</td><td><strong>Block Name</strong></td><td>String</td><td>Location</td><td><ol><li><p>If the user is <strong>Self Signed Up (custodian) user</strong> then user’s self declared location will be the value to the field</p><ol><li><a href="http://user.locationids%3Dlocation.id/">USER.locationids=LOCATION.id</a> and LOCATION.type='block' and fetch the name as LOCATION.{block_name},USER.userid</li></ol></li><li><p>If the user is <strong>tenant user</strong> then tenant location will be the value to the field</p><ol><li>Join ORGANISATION and location table having condition:<br><a href="http://org.locationids%3Dlocation.id/">ORG.locationids=Location.id</a> &#x26;&#x26; Location.type='block' and fetch the <a href="http://location.name/">Location.name</a>, <a href="http://org.id/">ORG.id</a></li><li>Join the Dataframe given in (i) and user table with condition:<br><a href="http://org.id/">ORG.id</a> = USER.rootorgid &#x26;&#x26; ORG.isrootorg=true and get the USER.userid, <a href="http://location.name/">Location.name</a></li></ol></li></ol></td></tr></tbody></table>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://lern.sunbird.org/use/developer-guide/user-and-org-service/caching-and-denormalising-user-metadata.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.