Component Diagram

Program Exhaust is a service that generates CSV file containing user details for those who has joined a program.

1. Database Layer:

• PostgreSQL Database (job_request): This is where job requests are stored. These requests include information about which users have requested reports.

• Cassandra Database (user_consent): This is where user consent data is stored. It contains information about whether users have agreed to share their PII data.

• Cassandra Database (program_enrollment): This is where program enrollment data is stored when a user joins new program.

• Redis Cache (user): Contains cached user data that can be quickly retrieved. Data provider

Database	Table
PostgreSQL	job_request
Redis Cache	user
Cassandra	program_enrolment, user_consent

1. Data Processing Layer: Apache Spark is used to perform transformations, sort columns, eliminate duplicates, and replace unknown values with null. This process enhances data quality, organizes data logically before storing to CSV.

User Interaction Diagram

This interaction diagram details the complete process of requesting and generating reports. The user can request a user info exhaust report through SunbirdEd from the program dashboard. Using exhaust APIs, this will map the request to SunbirdLern Which internally calls SunbirdObsrv service. userInfoExhaust data-product will be triggered by a scheduled cron task, which will query cassandra and redis to get data and process it using Spark to transform data and generate the report. The user receives the same report once it has been created.

CSV File Structure

Format	Nomenclature	Example	Security Levels
CSV zip (Password protected)	program-user-exhaust/<request_id>_<UpdatedDate>.zip	9CD107F48B5AF0D163F8AE8410829674_20230622.zip	L3 - Data encrypted with a user provided encryption key. Generally applicable to non PII data but can contain sensitive information which may not be considered open

CSV Columns Contents

Column Label	Column Type	Data Type	Description
User UUID	Static	String	The system generated unique user ID
User Name	Static	String	Name of the user(Firstname and Lastname)
Mobile Number	Static	String	User declared unmasked mobile number
Email ID	Static	String	User declared unmasked email ID
Consent Provided	Static	String	Yes/No. Flag to denote whether user has consented to the data sharing.
Consent Provided Date	Static	Date	Date when the user has consented to share the data
Program Name	Static	String	Program title
Program ID	Static	String	Unique Program Identifier.
State	Static	String	User declared state for self signed up users. If the user is a org validated user then the state as passed from org SSO or derived from sub-org ID.
District	Static	String	User declared district for self signed up users. If the user is a org validated user then the district as passed from org SSO or derived from sub-org ID.
Block	Static	String	Block name mapped to the user’s org/sub-org id
Cluster	Static	String	Cluster name mapped to the user’s org/sub-org id
School Id	Static	String	User declared school id for self signed up users. If the user is a org validated user then the school id as passed from org SSO or derived from sub-org ID
School Name	Static	String	School name mapped to the user’s org/sub-org id
User Type	Static	String	Type of the user
User Sub Type	Static	String	Sub Type of the user
Org Name	Static	String	Name of user org - Custodian for self signed up users and respective org name for org validated users

Consent Fields

Following are the fields/columns that will be available in the CSV file only when the user consented for the data sharing.

User Name	Mobile number	Email ID
On user consent	On user consent	On user consent

Sample CSV Data

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
ef3733df-41e7-4bf1-b67c-091fd0762ee	opa5	7272272777	[e-mail]	false	01/01/23	'Prerak Head Teacher of the Block 19-20'	PGM-Prerak-Head-Teacher-of-the-Block-19-20-Feb2021	Uttar Pradesh	AGRA	ACHHNERA	ZPHS AGALI	9150206302	JHS NAGLA SADLE COMPOSITE	administrator	hm,dikshapreprodcustodian