[Design]Enhancing Device Profile and Creating Extended User Profile

Introduction:

This document describes the enhanced/derived Device and Extended User Profile schemas, design of how the profile data is captured and how it can be used across various systems.

Design Flow

Schema Design for both User and Device Profile

#Schema of User Profile

TABLE user_db.user_profile (
   user_id text,
   user_type text,
   user_role text,
   sign_in_type text,
   user_state text,
   user_district text,
   user_block text,
   first_access timestamp,
   last_access timestamp,
   device_ids list<text>,
   last_device_accessed text,
   last_updated_by text,
   sources list<text>,
   last_content_played text,
   total_ts bigint,
   updated_date timestamp,
   PRIMARY KEY (userid)
);

#Schema of Device Profile

TABLE device_db.device_profile (
    device_id text,
    total_ts double,
    total_launches bigint,
    avg_ts double,
    first_access timestamp,
    last_access timestamp,
    device_spec map<text, text>,
    uaspec map<text, text>,
    country text,
    country_code text,
    state text,
    state_code text,
    state_code_custom text,
    state_custom text,
    city text,
    district_custom text,
    last_updated_by text,
    sources list<text>,
    user_ids list<text>,
    user_count bigint,
    producer_id text,
    producer_ver text,
    fcm_token text,
    updated_date timestamp,
   PRIMARY KEY (device_id)
);

Computation Table:

	Field	Type	Profile	Description	Source
1	device_id	String	Device	Unique id of device	From first source which inserts a record
2	total_ts	Double	Device	Total Time spent on device	Device profile updater data product
3	total_launches	Long	Device	Number app/portal launches from the device	Device profile updater data product
4	avg_ts	Double	Device	Average time spent on device	Device profile updater data product
5	first_access	Timestamp	Device	Timestamp on which device was first accessed	Device profile updater data product
6	last_access	Timestamp	Device	Timestamp on which device was last accessed	Device profile updater data product
7	state	String	Device	State from which device is accessed	Device Register API
8	state_code	String	Device	Device state code	Device Register API
9	state_code_custom	String	Device	Device custom state code	Device Register API
10	state_custom	String	Device	Device custom state name	Device Register API
11	city	String	Device	City from which device is accessed	Device Register API
12	country	String	Device	Country from which device is accessed	Device Register API
13	country_code	String	Device	Device country code	Device Register API
14	district_custom	String	Device	Device custom district name	Device Register API
15	device_spec	Map<String,String>	Device	Device Specification	Device Register API
16	uaspec	Map<String,String>	Device	Device user agent data	Device Register API
17	fcm_token	String	Device	FCM token for app devices	Device Register API
18	producer_id	String	Device	Producer id for which device belongs to(App/Portal)	Device Register API
19	producer_ver	String	Device	Version of producer	Device profile updater data product
20	user_ids	List<String>	Device	List of unique users accessed from the device	Device profile updater data product
21	user_count	Long	Device	Unique users accessed from the device	Device profile updater data product
22	last_updated_by	String	Device	Source from which record was last updated	Source which is updating the record
23	sources	List<String>	Device	List of sources from which record gets updated	From all the sources
24	user_id	String	User	Unique id for user	From first source which inserts a record
25	user_type	String	User	Type of user(Anonymous/SelfSignedIn/ValidatedUser)	User_Org Cassandra via Samza job
26	user_role	String	User	Role of user(Student/Teacher)	AUDIT events via Samza job
27	sign_in_type	String	User	Type of sign-in(GoogleAuth/SingleSignIn)	User_Org Cassandra via Samza job
28	user_state	String	User	State name of the User	User_Org Cassandra via Samza job
29	user_district	String	User	District name of the User	User_Org Cassandra via Samza job
30	user_block	String	User	Block name of the User	User_Org Cassandra via Samza job
31	first_access	Timestamp	User	Timestamp on which user was first seen	User Profile Updater data product
32	last_access	Timestamp	User	Timestamp on which user was last seen	User Profile Updater data product
33	last_device_accessed	String	User	Device id on which user was last seen	User Profile Updater data product
34	device_ids	List<String>	User	List of unique devices on which user was seen	User Profile Updater data product
35	last_updated_by	String	User	Source from which record was last updated	Source which is updating the record
36	sources	List<String>	User	List of sources from which record gets updated	From all the sources
37	last_content_played	String	User	Last content played by user	User Profile Updater data product
38	total_ts	Double	User	Total time spent by user	User Profile Updater data product

Questions:

Possibility of switch in user_role for APP users, Is it ok to have multiple values for user_role in the telemetry for same user_id?
Can we enhance the existing user table or create new one in analytics database.