[Design] Delete Account Functionality

Introduction

This wiki explains the design for delete user account feature. As of now there is no hard delete of user profile flow available in Sunbird. At present, we have functionality to BLOCK/UNBLOCK users.

Background & Problem Statement

Sunbird supports the mobile app for Android and iOS. As per the latest policy update of the Apple App Store and Google Play Store, mandates the user deletion from the app, if the app is having the signup from app. The specific policy mandates can be found using the following links:

Apple App Store Policy - https://developer.apple.com/app-store/review/guidelines/#data-collection-and-storage
Apple App Store Policy - https://developer.apple.com/support/offering-account-deletion-in-your-app
Google Play Store Policy - https://support.google.com/googleplay/android-developer/answer/13327111?hl=en

Registered users must have access to a "Delete Account" option on both the app and the portal. This option will allow them to initiate the account deletion process themselves.

Key Design Problems

User should not able to do the following things after successful deletion of account:

User should not be able to login by using the existing login credentials post account deletion.
Any of the Personally Identifiable Information (PII) of the user, such as name, email, and phone number should not be available in any DB in any format (even encrypted format).
Other than PII data should not be deleted. User transactional data and user created contents (usage, rating etc) are to be retained.
Certificates issued to the deleted users should not be accessible, but should be verifiable. (Storing only the name of the user in Sunbird RC to display in certificate).
External id of the SSO user should be removed.
List the deleted user list to admin dashboard.
Deleted user’s asset should be transferred to the other user after successful deletion.

Design

Delete User

Send the OTP to the user’s email/phone to validate the user. (OTP templates for user account deletion verification)
Delete login credentials and sessions from Keycloak. (async)
Update the sunbird.user table. Set the following fields as empty: (redis data for the following fields will become empty for that user) (sync)
- firstName
- lastName
- email
- dob
- phone
- maskedEmail
- maskedPhone
- prevUsedEmail
- prevUsedPhone
- recoveryEmail
- recoveryPhone
Update the status from ACTIVE to DELETED. (sync)
Remove the user entry from sunbird.user_lookup table. (sync)
Remove the SSO user entry from sunbird.user_external_identity (sync)
Update the user entry in sunbird.user_organisation (async) - May not be required.
- isdeleted - True
- orgleftdate - system date
Update the user’s name in nodeBB as Deleted User to display in discussion forum.
Insert the entry in sunbird.user_deletion_status table for each type. Note: If user has only PUBLIC role than entry for type userOwnershipTransfer will not required.

Sync the user deletion status in user index in below format:

"userDeletionStatus" : {
 	"userLookUp": false,
 	"userExtIdnt": true,
 	"keycloak": false
 	"user": true,
    "discussionForum": true,
 	"userOwnershipTransfer": false
 }

Insert the entry in sunbird.user_ownership_transfer table after successful deletion. Note: If user has only PUBLIC role than status will be directly marked as COMPLETED.

User deletion status table

This table is required for any audit purpose in future by anyone.

CREATE TABLE sunbird.user_deletion_status(
	userId text,
	type text,	// userLookUp, userExtIdnt, keycloak, user, discussionForum, userOwnershipTransfer
	status boolean,
	createdDate timestamp,
	upatedDate timestamp,
 	PRIMARY KEY ((userId, type))
 );

Sample curl for user delete API:

curl --location --request DELETE '{{host}}/api/user/v1/delete/{{userId}}' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer {{api_key}}' \
--header 'x-authenticated-user-token: {{user_token}}'

Sample Response:

{
    "id": "api.user.delete",
    "ver": "1.0",
    "ts": "2023-08-28T13:54:45Z+05:30",
    "params": {
        "resmsgid": "a638c46e-63a5-47de-bf00-029cbe435e5e",
        "msgid": null,
        "err": null,
        "status": "successful",
        "errmsg": null
    },
    "responseCode": "OK",
    "result": {
        "response": "SUCCESS",
        "userId": "{{userId}}"
    }
}

Transfer Ownership

List the users to whom admin wanted to transfer ownership using user ownership transfer list API (new API).
create the new elastic search index transfer-ownership to filter the data based on different fields.

Sample curl for user ownership transfer list API:

curl --location --request POST '{{host}}/api/user/v1/ownership/transfer/list' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer {{api_key}}' \
--header 'x-authenticated-user-token: {{user_token}}'\
--data-raw '{
    "request":{
      "organisationId": ["<organisationId>"], // Mandatory
      "status": ["0", "1"] // Optional. If not passed in request body 
                           // then by default all the status will return.
    }
}'

Sample Response:

{
    "id": "api.user.ownership.transfer.list",
    "ver": "1.0",
    "ts": "2023-08-28T13:54:45Z+05:30",
    "params": {
        "resmsgid": "a638c46e-63a5-47de-bf00-029cbe435e5e",
        "msgid": null,
        "err": null,
        "status": "successful",
        "errmsg": null
    },
    "responseCode": "OK",
    "result": {
        "count": 1,
        "user": [
            {
                "organisationId": <organisationId>,
                "userId": "<userId>",
                "username": "<username>",
                "status": 0,
                "roles": ["Content Creator"],
                "toUserId": "<transferred_userId>",
                "toUsername": "<toUsername>"
				"createdDate": "<createdDate>",
				"createdBy": "<created_userId>",
				"updatedDate": "<updatedDate>",
				"updatedBy": "<updatedBy_userId>"
			}
		]
    }
}

Transfer ownership API: This API will trigger the kafka event to transfer-ownership flink job, to precess the things asynchronously. This API will validate if “toUserId“ has all the roles of from user.

API Curl for ownership transfer

curl --location --request POST '{{host}}/api/user/v1/ownership/transfer' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer {{api_key}}' \
--header 'x-authenticated-user-token: {{user_token}}' \
--data-raw '{
    "request":{
      "organisationId": "{{organisationId}}"
      "fromUserId": "{{FROM_UUID}}",
      "toUserId": "{{TO_UUID}}",
      "asset": []   // If array is empty then transfer all the assets fromUserId to toUserId
    }
}'

API response:

{
    "id": "api.user.ownership.transfer",
    "ver": "1.0",
    "ts": "2023-08-28T13:54:45Z+05:30",
    "params": {
        "resmsgid": "a638c46e-63a5-47de-bf00-029cbe435e5e",
        "msgid": null,
        "err": null,
        "status": "successful",
        "errmsg": null
    },
    "responseCode": "OK",
    "result": {
        "status": "Ownership transfer event is pushed successfully!"
    }
}

Flink Job

Transfer ownership:

Update the status to PROCESSING in sunbird.user_ownership_transfer table.
Asset
- Fetch the list of created content/course/batch by fromUserId, using the composite search API.
- Update the createdBy and creator using content system update API.
Batch
- Fetch the list of open and ongoing batches created by fromUserId from sunbird_courses.course_batch table.
- Update the user entry in sunbird_courses.course_batch table for open and ongoing batches only.
  - createdby
- If the user is added as a course mentors to any batch, than user the search API to find out the batches and than remove the userId from mentors column in sunbird_courses.course_batch table.
Groups - Transfer the ownership to any other group admin if any other group admin is available else 1st assign the admin to any member and then transfer the ownership.
Update the status to COMPLETED.

Ownership Transfer Table

CREATE TABLE sunbird.user_ownership_transfer(
    organisationId text,
	userId text,
	username text,
	roles list<text>,
	toUserId text,
	toUsername text,
	status int,
	createdDate text,
	createdBy text,
	updatedDate text,
	updatedBy text,
	summary text,
 PRIMARY KEY (organisationId, userId)
 );

status
- 0 - SUBMITTED
- 1 - PROCESSING
- 2 - COMPLETED

Check list:

Check with Cokreat regarding user PII information stored as part of program - No PII data is stored as part of program program.
Check in all the services, where user PII data is logged.
Check discussion forum for PII data