Dattavani Backend: Data Model, Family and Progress

Dattavani Backend: Data Model, Family and Progress An entity-relationship diagram generated by Archify. parents kids learning progress quiz attempts daily limits levels & points library rewards parental_consent_audit_log · Entity · parents parental_consent_audit_log PK id bigint FK parent_id bigint action_type parental_consent_action_type consent_version text verification_method varchar(50) client_ip inet device_info text reason_text varchar(200) created_at timestamptz device_sessions · Entity · parents device_sessions PK id bigint FK parent_id bigint device_id varchar(255) device_name varchar(100) platform varchar(20) session_token text last_active_at timestamptz is_active boolean created_at timestamptz updated_at timestamptz device_token text parent_profile · Entity · parents parent_profile PK id bigint UK supabase_uid uuid → auth.users.id UK email varchar(255) UK phone varchar(20) name varchar(100) city varchar(100) pin_hash text role varchar(20) credits_balance integer consent_accepted boolean is_active boolean created_at timestamptz updated_at timestamptz consent_accepted_at timestamptz consent_revoked_at timestamptz FK city_id bigint is_premium_user boolean deletion_requested_at timestamptz deletion_type varchar(30) deletion_scheduled_purge_at timestamptz deletion_reason_text varchar(200) consent_revoke_reason_text varchar(200) analytics_id varchar(64) daily_usage_limit_minutes integer store_reviews_prompt_disabled boolean cities · Entity · parents cities PK id bigint name varchar(255) state_name varchar(255) state_code varchar(255) country_name varchar(100) country_code char(3) created_at timestamptz credit_allocations · Entity · parents credit_allocations PK id bigint FK parent_id bigint FK kid_id bigint amount integer allocated_at timestamptz kid_sessions · Entity · kids kid_sessions PK id bigint FKUK session_id bigint FK kid_id bigint activated_at timestamptz is_active boolean deactivate_at timestamp kid_profiles · Entity · kids kid_profiles PK id bigint FK parent_id bigint name varchar(100) FK avatar_id bigint gender varchar(10) age integer city varchar(100) pronunciation_url text coins_balance integer credits_balance integer current_xp integer current_level integer onboarding_complete boolean onboarding_step varchar(30) streak integer is_beta boolean is_active boolean created_at timestamptz updated_at timestamptz leaderboard_visit_count integer analytics_id varchar(64) tataji_sensitive_count integer tataji_off_topic_count integer avatars · Entity · kids avatars PK id bigint name varchar(50) gender varchar(10) image_url text is_active boolean display_order integer created_at timestamptz kid_goals · Entity · kids kid_goals PK id bigint FK kid_id bigint goal_type varchar(50) goal_value integer is_active boolean starts_at timestamptz ends_at timestamptz created_at timestamptz updated_at timestamptz kid_settings · Entity · kids kid_settings PK id bigint FKUK kid_id bigint daily_credit_cap integer voice_preference varchar(10) bedtime_story_limit integer timezone varchar(64) cap_override boolean updated_at timestamptz weekly_summary · Entity · kids weekly_summary PK id bigint FK kid_id bigint FK avatar_id bigint week_id integer total_coins integer total_xp integer created_at timestamptz updated_at timestamptz chapter_completions · Entity · learning progress chapter_completions PK id bigint FKUK kid_id bigint UK chapter_id bigint → chapters.id content_type varchar(20) status varchar(100) list_of_quizz_id text last_position double precision completed_at timestamptz created_at timestamptz updated_at timestamptz chapter_scene_chunk_completions · Entity · learning progress chapter_scene_chunk_completions PK id bigint FKUK kid_id bigint UK content_id bigint UK content_type varchar(20) status varchar(100) completed_at timestamptz created_at timestamptz updated_at timestamptz section_completions · Entity · learning progress section_completions PK id bigint FKUK kid_id bigint UK node_id bigint → learning_nodes.id coins_earned integer xp_earned integer mystery_gift_id bigint completed_at timestamptz story_completions · Entity · learning progress story_completions PK id bigint FKUK kid_id bigint UK story_id bigint → bedtime_stories.id completed_at timestamptz created_at timestamptz updated_at timestamptz story_scene_chunk_completions · Entity · learning progress story_scene_chunk_completions PK id bigint FKUK kid_id bigint UK content_id bigint UK content_type varchar(20) status varchar(20) completed_at timestamptz created_at timestamptz updated_at timestamptz media_resumes · Entity · learning progress media_resumes PK id bigint FK kid_id bigint media_type media_type_enum media_id integer pause_time_sec double precision is_active boolean updated_at timestamptz created_at timestamptz quiz_sessions · Entity · quiz attempts quiz_sessions PK id bigint FKUK kid_id bigint UK quiz_id bigint → quizzes.id current_question_id bigint → quiz_questions.id score integer is_completed boolean answers jsonb created_at timestamptz updated_at timestamptz quiz_submissions · Entity · quiz attempts quiz_submissions PK id bigint FK kid_id bigint quiz_id bigint → quizzes.id FK session_id bigint score double precision medal varchar(10) coins_earned integer xp_earned integer used_hint boolean attempt_number integer answers jsonb created_at timestamptz wrong_question_ids integer[] kid_daily_usage · Entity · daily limits kid_daily_usage PK id bigint FKUK kid_id bigint UK usage_date date minutes_used numeric(10,2) updated_at timestamptz active_trial_daily_usage · Entity · daily limits active_trial_daily_usage UK parent_id bigint (no FK) UK content_type varchar(15) UK content_id text UK created_date date created_at timestamptz points_config · Entity · levels & points points_config PK id bigint UK activity_type points_config_activity_type_enum UK key varchar(100) default_points text description text FK updated_by bigint updated_at timestamptz xp_levels · Entity · levels & points xp_levels PK id bigint UK level_number integer tier varchar(20) title_name varchar(50) min_xp integer max_xp integer reward_coins integer free_shields_per_month integer perks text created_at timestamptz downloads · Entity · library downloads PK id bigint FK kid_id bigint chapter_id bigint → chapters.id content_type varchar(20) content_id bigint manifest_json jsonb retention_days integer delete_after_days integer expires_at timestamptz downloaded_at timestamptz status varchar(20) favourites · Entity · library favourites PK id bigint FKUK kid_id bigint UK content_type varchar(50) UK content_id bigint created_at timestamptz updated_at timestamptz shares · Entity · library shares PK id bigint FK kid_id bigint share_type varchar(30) content_id bigint platform varchar(20) created_at timestamptz badge_definitions · Entity · rewards badge_definitions PK id bigint UK name varchar(100) description text image_url text category varchar(50) trigger_type varchar(50) trigger_value integer coins_reward integer xp_reward integer frequency varchar(20) is_active boolean display_order integer created_at timestamptz group_key varchar(50) rule_scope varchar(50) rule_variant varchar(50) rule_interval integer rule_start integer is_repeatable boolean display_title varchar(100) display_template text metadata jsonb user_badges · Entity · rewards user_badges PK id bigint FKUK kid_id bigint FKUK badge_id bigint earned_at timestamptz UK milestone_value integer UK content_type varchar(50) UK content_id bigint display_label text metadata jsonb kid_streaks · Entity · rewards kid_streaks PK id bigint FKUK kid_id bigint UK streak_type text current_streak integer longest_streak integer coin_multiplier double precision last_activity_date date updated_at timestamptz daily_stats · Entity · rewards daily_stats PK id bigint FKUK kid_id bigint UK date date coins_earned integer xp_earned integer learning_done boolean quiz_done boolean story_done boolean ask_tataji_done boolean bonus_task_done boolean checklist_complete boolean created_at timestamptz updated_at timestamptz login_done boolean activity_logs · Entity · rewards activity_logs PK id bigint FK kid_id bigint week_id integer activity_type activity_type_enum coins_earned integer xp_earned integer metadata jsonb created_at timestamptz parent_id Legend PK Primary key FK Foreign key UK Unique key One Many Optional