PII User Consent Data Extraction for ML

[Shakthieshwari]

Hi @reshmi-nair @amit-tarento ,

As part of 5.2 release, In the Manage Learn we are enabling PII Consent at the program Level for the users. So If user select share in the PII Consent, then we need to provide the User Level Data(user name, email, mobile no ...) in the form of CSV to the Program Manager users in the program dashboard.

Jira Ticket :- https://project-sunbird.atlassian.net/browse/ED-543, https://project-sunbird.atlassian.net/browse/ED-433
CSV Data Format :- https://docs.google.com/spreadsheets/d/1aipO6Lp9bmB3v_iHQROM-6FI7ixRlXHdQ8uDtu6cHco/edit?usp=sharing

At the time of submission, our backend captures the userProfile data and push into kafka as a events/store into mongodb. From this userProfile data, we need to know how do we decrypt these data columns :-

1. email_id
2. mobile no
3. user_name

Do we have any existing components/services where we can extract these data ? how do we decrypt the data columns ?

Attaching below the sample data :-
{ "webPages" : null, "maskedPhone" : null, "tcStatus" : null, "loginId" : null, "subject" : null, "channel" : "dikshapreprodcustodian", "profileUserTypes" : [ { "type" : "teacher" } ], "language" : null, "updatedDate" : "2021-02-18 09:43:09:635+0000", "password" : null, "managedBy" : null, "flagsValue" : 0, "id" : "e85060c8-a76b-4e79-a36b-95836491baf9", "recoveryEmail" : "er***@yopmail.com", "identifier" : "e85060c8-a76b-4e79-a36b-95836491baf9", "thumbnail" : null, "profileVisibility" : null, "updatedBy" : "e85060c8-a76b-4e79-a36b-95836491baf9", "accesscode" : null, "externalIds" : [], "registryId" : null, "roleList" : [ { "name" : "Book Creator", "id" : "BOOK_CREATOR" }, { "name" : "Membership Management", "id" : "MEMBERSHIP_MANAGEMENT" }, { "name" : "Flag Reviewer", "id" : "FLAG_REVIEWER" }, { "name" : "Report Viewer", "id" : "REPORT_VIEWER" }, { "name" : "Program Manager", "id" : "PROGRAM_MANAGER" }, { "name" : "Program Designer", "id" : "PROGRAM_DESIGNER" }, { "name" : "System Administration", "id" : "SYSTEM_ADMINISTRATION" }, { "name" : "Content Curation", "id" : "CONTENT_CURATION" }, { "name" : "Book Reviewer", "id" : "BOOK_REVIEWER" }, { "name" : "Content Creator", "id" : "CONTENT_CREATOR" }, { "name" : "Org Management", "id" : "ORG_MANAGEMENT" }, { "name" : "Course Admin", "id" : "COURSE_ADMIN" }, { "name" : "Org Moderator", "id" : "ORG_MODERATOR" }, { "name" : "Public", "id" : "PUBLIC" }, { "name" : "Admin", "id" : "ADMIN" }, { "name" : "Course Mentor", "id" : "COURSE_MENTOR" }, { "name" : "Content Reviewer", "id" : "CONTENT_REVIEWER" }, { "name" : "Report Admin", "id" : "REPORT_ADMIN" }, { "name" : "Org Admin", "id" : "ORG_ADMIN" } ], "rootOrgId" : "0126796199493140480", "prevUsedEmail" : "le*@yopmail.com", "firstName" : "Lea", "tncAcceptedOn" : 1612153339245.0, "allTncAccepted" : { "groupsTnc" : { "tncAcceptedOn" : "2021-02-04 07:18:40:389+0000", "version" : "3.5.0" } }, "profileDetails" : null, "phone" : "", "dob" : null, "grade" : null, "currentLoginTime" : null, "userType" : "teacher", "status" : 1, "lastName" : "", "tncLatestVersion" : "v12", "gender" : null, "prevUsedPhone" : "", "stateValidated" : false, "encEmail" : null, "isDeleted" : false, "organisations" : [ { "organisationId" : "0126796199493140480", "approvedBy" : null, "channel" : "dikshapreprodcustodian", "updatedDate" : null, "approvaldate" : null, "isSystemUpload" : false, "isDeleted" : false, "id" : "01320676218927513612", "isApproved" : null, "orgjoindate" : "2021-02-01 04:22:10:020+0000", "isSelfDeclaration" : true, "updatedBy" : null, "orgName" : "Staging Custodian Organization", "addedByName" : null, "addedBy" : null, "associationType" : 2, "locationIds" : [ "027f81d8-0a2c-4fc6-96ac-59fe4cea3abf", "8250d58d-f1a2-4397-bfd3-b2e688ba7141" ], "orgLocation" : [ { "type" : "state", "id" : "027f81d8-0a2c-4fc6-96ac-59fe4cea3abf" }, { "type" : "district", "id" : "8250d58d-f1a2-4397-bfd3-b2e688ba7141" } ], "externalId" : "101010", "userId" : "e85060c8-a76b-4e79-a36b-95836491baf9", "isSchool" : false, "hashTagId" : "0126796199493140480", "isSSO" : false, "isRejected" : null, "locations" : [ { "code" : "29", "name" : "Karnataka", "id" : "027f81d8-0a2c-4fc6-96ac-59fe4cea3abf", "type" : "state", "parentId" : null }, { "code" : "2901", "name" : "BELAGAVI", "id" : "8250d58d-f1a2-4397-bfd3-b2e688ba7141", "type" : "district", "parentId" : "027f81d8-0a2c-4fc6-96ac-59fe4cea3abf" } ], "position" : null, "orgLeftDate" : null } ], "provider" : null, "countryCode" : "+91", "tncLatestVersionUrl" : "https://sunbirdstagingpublic.blob.core.windows.net/termsandcondtions/terms-and-conditions-v12.html", "maskedEmail" : null, "tempPassword" : null, "email" : "", "rootOrg" : { "dateTime" : null, "preferredLanguage" : null, "keys" : {}, "organisationSubType" : null, "channel" : "dikshapreprodcustodian", "approvedBy" : null, "description" : "Pre-prod Custodian Organization", "updatedDate" : "2022-02-18 09:50:42:752+0000", "organisationType" : 5, "addressId" : null, "orgType" : null, "isTenant" : true, "provider" : null, "locationId" : null, "orgCode" : null, "theme" : null, "id" : "0126796199493140480", "communityId" : null, "isApproved" : null, "isBoard" : true, "email" : null, "slug" : "dikshapreprodcustodian", "isSSOEnabled" : null, "thumbnail" : null, "orgName" : "Staging Custodian Organization", "updatedBy" : null, "locationIds" : [ "027f81d8-0a2c-4fc6-96ac-59fe4cea3abf", "8250d58d-f1a2-4397-bfd3-b2e688ba7141" ], "externalId" : "101010", "orgLocation" : [ { "type" : "state", "id" : "027f81d8-0a2c-4fc6-96ac-59fe4cea3abf" }, { "type" : "district", "id" : "8250d58d-f1a2-4397-bfd3-b2e688ba7141" } ], "isRootOrg" : true, "rootOrgId" : "0126796199493140480", "imgUrl" : null, "approvedDate" : null, "homeUrl" : null, "orgTypeId" : null, "isDefault" : null, "createdDate" : "2019-01-18 09:48:13:428+0000", "createdBy" : "system", "parentOrgId" : null, "hashTagId" : "0126796199493140480", "noOfMembers" : null, "status" : 1 }, "phoneVerified" : true, "profileSummary" : null, "tcUpdatedDate" : null, "userLocations" : [ { "code" : "2822", "name" : "ANANTAPUR", "id" : "2f76dcf5-e43b-4f71-a3f2-c8f19e1fce03", "type" : "district", "parentId" : "bc75cc99-9205-463e-a722-5326857838f8" }, { "code" : "282262", "name" : "AGALI", "id" : "966c3be4-c125-467d-aaff-1eb1cd525923", "type" : "block", "parentId" : "2f76dcf5-e43b-4f71-a3f2-c8f19e1fce03" }, { "code" : "28", "name" : "Andhra Pradesh", "id" : "bc75cc99-9205-463e-a722-5326857838f8", "type" : "state", "parentId" : null } ], "recoveryPhone" : "", "avatar" : null, "userName" : "lea_nw2m", "userId" : "e85060c8-a76b-4e79-a36b-95836491baf9", "userSubType" : null, "declarations" : [ { "persona" : "default", "errorType" : null, "orgId" : "1269934121990553633", "status" : "SUBMITTED", "info" : { "declared-email" : "[email protected]", "declared-phone" : "9611609631", "declared-school-name" : "APMS AGALI", "declared-school-udise-code" : "28226200815" } }, { "persona" : "default", "errorType" : null, "orgId" : "do_21311761957152358411670", "status" : null, "info" : {} }, { "persona" : "default", "errorType" : null, "orgId" : "do_2131976657413980161102", "status" : null, "info" : {} }, { "persona" : "default", "errorType" : null, "orgId" : "do_2132119550705909761228", "status" : null, "info" : {} }, { "persona" : "default", "errorType" : null, "orgId" : "do_2132124484162600961245", "status" : null, "info" : {} } ], "promptTnC" : true, "emailVerified" : true, "lastLoginTime" : 0, "createdDate" : "2021-02-01 04:22:09:963+0000", "framework" : { "board" : [ "State (Uttar Pradesh)" ], "id" : [ "up_k-12" ] }, "createdBy" : null, "profileUserType" : { "type" : "teacher" }, "encPhone" : null, "location" : null, "tncAcceptedVersion" : "3.5.0" }

Please do help us out on this...

Cc- @aishwaryashikshalokam @Prateek-slokam @aks30 @vijiurs @kiranharidas187

[Shakthieshwari]

@reshmi-nair @amit-tarento please help us out on this

Thanks

[aishwaryashikshalokam]

@rhwarrier it is a need from the user's end. All the reports in the program dashboard have filter capability applicable to them. Thus I recommend having filters applicable to the PII report too to maintain consistency.
In the course dashboard also a filter of the batch is selected in order to request the reports.
In the Program dashboard, we have more options for filters that help the Program managers to analyze their data easily and get only the needed data based on the filters.

[kumarks1122]

@Shakthieshwari @aishwaryashikshalokam : Since course's user info exhaust report is generated for course batch, filters are not available for this report.

[Shakthieshwari]

yes thanks @kumarks1122 .

Course Page User CSV Columns are below :-
"Collection Id", "Collection Name", "Batch Id", "Batch Name", "User UUID", "User Name", "User Type", "User Sub Type", "State", "District", "Block", "Cluster", "School Id", "School Name", "Org Name","Email ID", "Mobile Number", "Consent Provided", "Consent Provided Date"
Cassandra Table :-
user_table
user_enrolments
user_consent

ML Page User CSV Columns are below :-
User UUID, User Name, Mobile number, Email ID, Consent Provided, Consent Provided Date, Program Name, Program ID, State, District, Block, Cluster, School Id, School Name, Usertype, Usersubtype, Org Name

@rhwarrier As our products team, confirmed. Filtering capability is needed for ML PII CSV.

And also I checked the Course User Exhaust (UserInfoExhaust) Data Product of course page, Code looks like it is specially written only to support course's user report, hence the same data product cannot be used for our ML Use-Case, But from this data product we can re-use few logic:-

1. Fetching the data from cassandra
2. Fetching User Info from Redis Cache
3. Decrypting the User Info
   ...

These logic can be re-used from Course User Exhaust (UserInfoExhaust), But we need to create new data product for PII of ML Capability by extending the UserInfoExhaust to re-use few logic...

In the New Data Product We will,

1. Re-use few logic of Course User Exhaust
2. Develop Dynamic filtering Capability
3. Get the Updated User Personal Info (User Name, Email Id, Mobile No) at the time of CSV Generation, But Entity Level Data Like State,District,Block,School,User Type,User SubType at the time of join program/consumption.

[SanthoshVasabhaktula]

@Shakthieshwari @aishwaryashikshalokam - I am not sure I understand clearly what is meant by filtering. Is it that the job will be submitted with a filter? If so, filters can be added during exhaust job submission and the data product can process it.

But if it is to access via the UI, I am not sure how a password protected file can be displayed in UI. Can anyone elaborate on this approach?

Another question - How is the consent information captured and recorded? Is the current consent API going to be used for it?

It is fine to capture user details during joining the program and storing it in Cassandra or any other transactional DB. However, the PII data should be encrypted before storing.

In addition, if it is a very specific use case targeted to be only used in DIK, create the job in DIK repository but not SB-LERN.

@rhwarrier

[Shakthieshwari]

@SanthoshVasabhaktula @rhwarrier Lets continue the conversation in this #58 forum, because multiples threads is leading to lot of confusion's for everyone.

Thanks