[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_role text,
   user_type 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_updated_by text,
   last_content_played text,
   products_list list<text>,
   last_device_accessed text,
   total_ts bigint,
   PRIMARY KEY (userid)
);

#Schema of Device Profile

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

Computation Table:

Field	Type	Profile	Description	Source
device_id	String	Device	Unique id of device
total_ts	Double	Device	Total Time spent on device	Device profile updater data product
total_launches	Long	Device	Number app/portal launches from the device	Device profile updater data product
avg_ts	Double	Device	Average time spent on device	Device profile updater data product
first_access	Timestamp	Device	Timestamp on which device was first accessed	Device profile updater data product
last_access	Timestamp	Device	Timestamp on which device was last accessed	Device profile updater data product
state	String	Device	State from which device is accessed	Device Register API
state_code	String	Device	Device state code	Device Register API
state_code_custom	String	Device	Device custom state code	Device Register API
state_custom	String	Device	Device custom state name	Device Register API
city	String	Device	City from which device is accessed	Device Register API
country	String	Device	Country from which device is accessed	Device Register API
country_code	String	Device	Device country code	Device Register API
district_custom	String	Device	Device custom district name	Device Register API
device_spec	Map<String,String>	Device	Device Specification	Device Register API
uaspec	Map<String,String>	Device	Device user agent data	Device Register API
fcm_token	String	Device	FCM token for app devices	Device Register API
producer_id	String	Device	Producer id for which device belongs to(App/Portal)	Device Register API
producer_ver	String	Device	Version of producer	Device profile updater data product
user_ids	List<String>	User	List of unique users accessed from the device	Device profile updater data product
user_count	Long	Device	Unique users accessed from the device	Device profile updater data product
last_updated_by	String	Device	Source from which record was last updated	Source which is updating the record
products_list	List<String>	Device	List of sources from which record gets updated	From all the sources
user_id	String	User	Unique id for user
user_type	String	User	Type of user(Anonymous/SelfSignedIn/ValidatedUser)	Samza job
user_role	String	User	Role of user(Student/Teacher)	Samza job
sign_in_type	String	User	Type of sign-in(GoogleAuth/SingleSignIn)	Samza job
user_state	String	User	State name of the User
user_district	String	User	District name of the User
user_block	String	User	Block name of the User
first_access	Timestamp	User	Timestamp on which user was first seen	User Profile Updater data product
last_access	Timestamp	User	Timestamp on which user was last seen	User Profile Updater data product
last_device_accessed	String	User	Device id on which user was last seen	User Profile Updater data product
device_ids	List<String>	User	List of unique devices on which user was seen	User Profile Updater data product
last_updated_by	String	User	Source from which record was last updated	Source which is updating the record
products_list	List<String>	User	List of sources from which record gets updated	From all the sources
last_content_played	String	User	Last content played by user	User Profile Updater data product
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.