Skip to main content
Version: Next

DB Structure

Goal-Framework entities can be stored in the DB in 3 tables (see image):

Table navida_user_health_goal​

Tracks individual user health goals and their progress.

  • Key fields:
    • id: Database identifier (Primary key)
    • health_goal_id: Identifier for the specific goal (CMS id)
    • health_goal_session_id: ??? (seems to be not used anymore)
    • source_health_goal_id: ??? (seems to be not used anymore)
    • user_id: Foreign key linking to user id in navida_user.user_id table
    • is_active: Flag that indicates if the goal is currently active (enrolled and not expired yet)
    • enrolled_date_time: Time when the goal was enrolled (after self-assessment)
    • session_start_tp: Time when the goal was started (start of the first challenge)
    • session_end_tp: Time when the goal was completed or failed
    • created_tp: Time when the goal was enrolled (it should be the same as enrolled_date_time)
    • changed_tp: Last time when the goal's bonus status was changed (has the same value as bonus_transmitted_tp or point_earned_tp)
    • is_deleted: Flag that indicates if the goal deleted
    • delete_tp: Time when the goal is reset (soft-deletion)
    • points: Amount of bonus points for this goal (copied from CMS, usually it's 1500)
    • point_earned_tp: Time when MC Bonus returned success for bonification status request
    • bonus_transmitted_tp: Time when the bonus request was sent to MC Bonus
    • bonus_measure_uuid: UUID identifier generated when sending bonus.MC_BONUS_MEASURES_ENDPOINT request. Saved along with bonus_transmitted_tp
    • tenant_id: Tenant id of the goal (AOK identifier). For instance, aokplus, aoknw etc.
    • goal_status: Current goal status based on Crob Job evaluation (GoalStatus enum)
    • goal_type: Type of the goal (Sports, Mental or Mixed)
    • goal_uuid Goal's UUID identifier from CMS
    • goal_category_id: CMS integer identifier of goal type. Not used
    • goal_number_of_days: Time duration in days for the started goal (copied from CMS)
    • title: Title of the goal (copied from CMS, is used for easier data identification in database)
    • level: Self-assessment level (High, Medium or Low)
    • tags: Consolidated list of challenge tags for all assessment levels

Table navida_user_challenges​

Manages user challenges linked to health goals.

  • Key fields:
    • id: Database identifier (Primary key)
    • challenge_id: Identifier for the specific challenge (CMS id)
    • challenge_version: ?
    • challenge_status: Current challenge status based on Crob Job evaluation (ChallengeStatus enum)
    • is_active: ?
    • started_date_time: Time when the challenge was started
    • end_date_time:
    • session_health_goal_id: Foreign key linking to health goal id navida_user_health_goal.id
    • title: Title of the challenge (copied from CMS)
    • challenge_session_id: ?
    • source_challenge_id: ?
    • challenge_uuid: ?
    • is_health_gadget_supported: Boolean value that specifies if Fitness Tracker is required for this challenge
    • is_mandatory: Not used, always true
    • challenge_number_of_days: Number of days required to complete this challenge (copied from CMS)
    • level: Challenge complexity level: High, Medium or Low (copied from CMS)
    • min_time_needed: (deprecated) Minimum time required for the challenge, e.g. for "5-15 Minuten pro Lektion" it has value 5
    • max_time_needed: (deprecated) Maximum time required for the challenge, e.g. for "5-15 Minuten pro Lektion" it has value 15
    • validation_buffer_day: Buffering number of days to complete the challenge
    • enrolled_date_time: ?

Table navida_user_activity​

Records specific activities within challenges.

  • Key fields:
    • id: Database identifier (Primary key)
    • challenges_id: Foreign key linking to navida_user_challenges.`id
    • activity_id: CMS activity id
    • activity_title: Title of the activity (copied from CMS)
    • activity_status: Current activity status based on Crob Job evaluation (ActivityStatus enum)
    • is_active: ?
    • started_date_time: Time when activity was started
    • session_exercise_id: ?
    • source_activity_id: ?
    • from_met: FromMET value from CMS
    • to_met: ToMET value from CMS
    • execution_min_time_secs: Minimum execution time in seconds from CMS
    • level: Self-assessment level (LOW, MEDIUM or HIGH)
    • created_tp: Time when activity record was created
    • changed_tp: Last time when activity record was changed

Table structure​

alt text