# Database Schema Comparison Report

**Generated:** 2026-03-20 07:00:33
**Source:** UAT (AWS RDS) (`mission_even_uat`)
**Target:** PROD (AWS RDS) (`me_prod`)

## Summary

| Metric | Count |
|--------|-------|
| Tables in Source | 217 |
| Tables in Target | 222 |
| Tables in Common | 216 |
| Missing in Source | 6 |
| Missing in Target | 1 |
| Tables with Column Diffs | 4 |
| Tables Matching Exactly | 212 |

## Missing in Source (UAT (AWS RDS))

These tables exist in **PROD (AWS RDS)** but NOT in **UAT (AWS RDS)**.

| Table | Classification | Reason | Row Count (Target) |
|-------|---------------|--------|-------------------|
| `candidate_communication_log` | **INVESTIGATE** | Not mapped — may be used by org-recruiter or superadmin | 11 |
| `candidate_outreach_log` | **INVESTIGATE** | Not mapped — may be used by org-recruiter or superadmin | 3 |
| `chat_history` | **INVESTIGATE** | Not mapped — may be used by org-recruiter or superadmin | 158 |
| `digest_send_log` | **INVESTIGATE** | Not mapped — may be used by org-recruiter or superadmin | 1460 |
| `digest_settings` | **INVESTIGATE** | Not mapped — may be used by org-recruiter or superadmin | 84 |
| `stakeholder_digest_settings` | **INVESTIGATE** | Not mapped — may be used by org-recruiter or superadmin | 1 |

## Missing in Target (PROD (AWS RDS))

These tables exist in **UAT (AWS RDS)** but NOT in **PROD (AWS RDS)**.

| Table | Classification | Reason | Row Count (Source) |
|-------|---------------|--------|-------------------|
| `ats_login_tokens` | **REQUIRED** | One-time SSO login tokens from org-recruiter | 8 |

## Column Differences

Tables that exist in both databases but have schema differences.

### `ats_settings` (REQUIRED)

**Column type/attribute differences:**

| Column | Attribute | UAT (AWS RDS) | PROD (AWS RDS) |
|--------|-----------|---------------|----------------|
| `setting_value` | type | `varchar(255)` | `varchar(100)` |
| `setting_value` | default | `NULL` | `0` |
| `updated_at` | type | `timestamp` | `datetime` |

### `audit_log` (REQUIRED)

**Column type/attribute differences:**

| Column | Attribute | UAT (AWS RDS) | PROD (AWS RDS) |
|--------|-----------|---------------|----------------|
| `user_id` | nullable | `YES` | `NO` |
| `created_at` | type | `datetime` | `timestamp` |
| `created_at` | nullable | `NO` | `YES` |

**Indexes only in PROD (AWS RDS):** `idx_action_type`, `idx_company`

### `rb_activity_log` (INVESTIGATE)

**Column type/attribute differences:**

| Column | Attribute | UAT (AWS RDS) | PROD (AWS RDS) |
|--------|-----------|---------------|----------------|
| `user_id` | nullable | `NO` | `YES` |
| `action` | type | `varchar(50)` | `varchar(100)` |

### `rb_user_sessions` (INVESTIGATE)

**Columns only in PROD (AWS RDS):**
- `is_active` (tinyint(1))

**Column type/attribute differences:**

| Column | Attribute | UAT (AWS RDS) | PROD (AWS RDS) |
|--------|-----------|---------------|----------------|
| `expires_at` | nullable | `YES` | `NO` |

**Indexes only in PROD (AWS RDS):** `idx_session_token`

## Matching Tables (No Differences)

| Table | Columns | Source Rows | Target Rows |
|-------|---------|------------|-------------|
| `applied_job_status_master` | 8 | 5 | 5 |
| `articles` | 13 | 6 | 12 |
| `ats_admin_users` | 10 | 1 | 0 |
| `ats_password_audit` | 6 | 0 | 30 |
| `ats_password_resets` | 6 | 0 | 8 |
| `benifits_master` | 12 | 50 | 47 |
| `bio_category_master` | 3 | 3 | 3 |
| `bio_master` | 8 | 10 | 10 |
| `bio_master_category` | 6 | 20 | 20 |
| `campaign` | 9 | 10 | 8 |
| `chat_message` | 19 | 2486 | 395 |
| `chat_request` | 8 | 134 | 92 |
| `city_master` | 12 | 1727 | 1977 |
| `company_benifits` | 6 | 1273 | 1259 |
| `company_document_master` | 3 | 5 | 5 |
| `company_emp_range_master` | 8 | 7 | 7 |
| `company_job_payment_milestone` | 7 | 0 | 0 |
| `company_job_slots` | 7 | 0 | 0 |
| `company_jobs` | 50 | 863 | 1112 |
| `company_jobs_automatch` | 6 | 186547 | 104209 |
| `company_jobs_benefits` | 6 | 1469 | 3549 |
| `company_jobs_core_requirement` | 6 | 0 | 0 |
| `company_jobs_education` | 7 | 946 | 1430 |
| `company_jobs_industries` | 6 | 0 | 0 |
| `company_jobs_location` | 10 | 929 | 1576 |
| `company_jobs_questions` | 9 | 331 | 847 |
| `company_jobs_skills` | 7 | 4033 | 7726 |
| `company_jobs_soft_skills` | 6 | 3626 | 6639 |
| `company_jobs_supplement_pay_offer` | 7 | 156 | 572 |
| `company_jobs_types` | 6 | 935 | 1412 |
| `company_master` | 32 | 269 | 636 |
| `company_media` | 10 | 140 | 174 |
| `company_offices` | 11 | 0 | 0 |
| `company_sub_admin_access` | 6 | 0 | 0 |
| `company_sub_admin_access_master` | 3 | 4 | 4 |
| `core_requirement_master` | 14 | 0 | 0 |
| `coupon_company_master` | 7 | 0 | 0 |
| `coupon_master` | 15 | 0 | 0 |
| `custom_web_pages` | 14 | 52 | 259 |
| `custom_web_pages_faq` | 4 | 47 | 757 |
| `daily_report` | 6 | 292 | 401 |
| `department_master` | 11 | 67 | 56 |
| `device_tokens` | 4 | 3996 | 371647 |
| `education_master` | 11 | 27 | 16 |
| `email_providers_master` | 3 | 154 | 154 |
| `experience_master` | 9 | 21 | 21 |
| `faqs` | 8 | 9 | 58 |
| `field_study_master` | 10 | 1167 | 1167 |
| `field_study_skill_mapping` | 6 | 133 | 133 |
| `form` | 14 | 41 | 157 |
| `future_job_category` | 8 | 15 | 15 |
| `future_job_training` | 16 | 31 | 31 |
| `future_job_training_video` | 16 | 86 | 86 |
| `future_job_training_video_skills` | 6 | 186 | 186 |
| `image_master` | 7 | 7 | 0 |
| `industry_master` | 11 | 73 | 73 |
| `institute_master` | 9 | 995 | 988 |
| `interview_reject_reason` | 7 | 13 | 13 |
| `job_duration_master` | 3 | 4 | 4 |
| `job_enrollment_type` | 4 | 2 | 2 |
| `job_feedbacks` | 11 | 377 | 191 |
| `job_location_type_master` | 4 | 4 | 4 |
| `job_question_type_master` | 4 | 3 | 3 |
| `job_reaction_type` | 4 | 4 | 4 |
| `job_role_group_mapping` | 6 | 4305 | 4394 |
| `job_role_group_master` | 9 | 57 | 54 |
| `job_role_screening_question` | 6 | 1551 | 1552 |
| `job_role_statistics` | 5 | 139 | 1139 |
| `job_role_tags_mapping` | 6 | 46 | 59 |
| `job_slot_master` | 10 | 18 | 18 |
| `job_status_master` | 8 | 7 | 7 |
| `job_type_master` | 4 | 5 | 5 |
| `kaampe_help_videos` | 13 | 2 | 2 |
| `kaampe_videos` | 12 | 15 | 14 |
| `language_master` | 9 | 4 | 4 |
| `lead_capture` | 5 | 0 | 0 |
| `legal_company_master` | 7 | 245 | 556 |
| `lms_activity_log` | 8 | 1103 | 390 |
| `lms_admin_users` | 10 | 3 | 3 |
| `lms_badges` | 9 | 9 | 9 |
| `lms_categories` | 10 | 15 | 15 |
| `lms_course_modules` | 8 | 92 | 105 |
| `lms_course_reviews` | 8 | 2 | 0 |
| `lms_courses` | 24 | 44 | 52 |
| `lms_enrollment_prompts` | 6 | 0 | 0 |
| `lms_enrollments` | 10 | 19 | 135 |
| `lms_instructors` | 10 | 16 | 16 |
| `lms_otp_verifications` | 7 | 2 | 0 |
| `lms_quiz_attempts` | 10 | 4 | 3 |
| `lms_quiz_questions` | 15 | 46 | 48 |
| `lms_timestamp_backup` | 4 | 42 | 0 |
| `lms_user_badges` | 5 | 13 | 19 |
| `lms_user_sessions` | 21 | 0 | 0 |
| `lms_user_wishlist` | 4 | 6 | 23 |
| `lms_users` | 16 | 4 | 106 |
| `lms_validation_rules` | 6 | 5 | 5 |
| `lms_video_notes` | 8 | 0 | 0 |
| `lms_video_progress` | 12 | 31 | 147 |
| `lms_video_resources` | 9 | 0 | 0 |
| `lms_videos` | 16 | 221 | 244 |
| `message_read_status` | 5 | 4055 | 466 |
| `mock_interview_rate_limits` | 8 | 0 | 0 |
| `mock_interview_role_config` | 9 | 22 | 22 |
| `mock_interview_sessions` | 20 | 8 | 17 |
| `modules_master` | 4 | 3 | 3 |
| `notification_routs_master` | 3 | 6 | 6 |
| `notifications` | 13 | 0 | 0 |
| `partner_admins` | 8 | 6 | 25 |
| `partner_master` | 11 | 3 | 17 |
| `pathfinder_activity_logs` | 9 | 0 | 0 |
| `pathfinder_assessment_results` | 16 | 16 | 114 |
| `pathfinder_career_profiles` | 22 | 0 | 0 |
| `pathfinder_config` | 5 | 0 | 0 |
| `pathfinder_education_levels` | 5 | 0 | 0 |
| `pathfinder_quiz_responses` | 7 | 322 | 1787 |
| `pathfinder_quiz_sessions` | 9 | 47 | 210 |
| `pathfinder_rate_limit_cache` | 6 | 0 | 719 |
| `pathfinder_training_programs` | 9 | 0 | 0 |
| `pathfinder_users` | 8 | 5 | 112 |
| `permission_groups` | 7 | 9 | 9 |
| `permission_module_groups` | 5 | 11 | 11 |
| `permission_value_master` | 3 | 3 | 3 |
| `pledge` | 15 | 6 | 1 |
| `profession_industries` | 6 | 0 | 0 |
| `profession_master` | 12 | 4457 | 4531 |
| `radius_master` | 9 | 7 | 7 |
| `rate_limiter` | 3 | 626 | 0 |
| `rb_chat_sessions` | 14 | 9 | 4 |
| `rb_otp_logs` | 11 | 0 | 0 |
| `rb_resume_awards` | 9 | 0 | 0 |
| `rb_resume_certifications` | 13 | 2 | 7 |
| `rb_resume_education` | 16 | 27 | 12 |
| `rb_resume_experience` | 15 | 46 | 8 |
| `rb_resume_languages` | 10 | 0 | 0 |
| `rb_resume_projects` | 16 | 2 | 10 |
| `rb_resume_references` | 12 | 0 | 0 |
| `rb_resume_skills` | 10 | 117 | 116 |
| `rb_resume_views` | 8 | 14 | 2 |
| `rb_templates` | 10 | 12 | 12 |
| `rb_user_preferences` | 10 | 0 | 0 |
| `rb_user_resumes` | 42 | 21 | 14 |
| `rb_users` | 11 | 0 | 0 |
| `roles` | 4 | 11 | 11 |
| `salary_master` | 10 | 10 | 10 |
| `sample_table` | 3 | 4 | 4 |
| `schedule_callback_slot_master` | 10 | 6 | 6 |
| `schedule_callback_utils` | 9 | 7 | 7 |
| `schedule_slot_master` | 10 | 13 | 13 |
| `skill_level_type` | 4 | 3 | 3 |
| `skill_mapping` | 8 | 206094 | 216280 |
| `skill_profession` | 6 | 0 | 0 |
| `skills_master` | 15 | 2556 | 2554 |
| `sms_hashkey` | 2 | 0 | 0 |
| `soft_skills_master` | 10 | 146 | 146 |
| `static_otp_mobile_no` | 3 | 167 | 190 |
| `status` | 3 | 3 | 3 |
| `subscription_package_type` | 16 | 0 | 0 |
| `supplement_pay_offer` | 4 | 4 | 4 |
| `table_states` | 4 | 1660 | 1660 |
| `tags_master` | 7 | 33 | 29 |
| `temp_user_award` | 8 | 0 | 0 |
| `temp_user_experiences` | 17 | 0 | 0 |
| `temp_user_key_accomplishments` | 9 | 0 | 0 |
| `temp_user_portfolio` | 14 | 0 | 0 |
| `temp_user_roles` | 6 | 0 | 2 |
| `temp_user_willing_to_relocate` | 8 | 0 | 0 |
| `temp_users` | 56 | 0 | 2 |
| `temp_users_educations` | 12 | 0 | 2 |
| `temp_users_industries` | 6 | 0 | 0 |
| `temp_users_skills` | 9 | 0 | 2 |
| `temp_users_soft_skills` | 6 | 0 | 6 |
| `testimonials` | 12 | 11 | 5 |
| `token` | 4 | 570 | 41128 |
| `unlimited_job_post_date` | 2 | 0 | 0 |
| `user_auth_token` | 5 | 10530 | 177747 |
| `user_award` | 8 | 5 | 2 |
| `user_certifications` | 12 | 31 | 126 |
| `user_companies` | 26 | 261 | 731 |
| `user_company_followed` | 5 | 56 | 409 |
| `user_experiences` | 17 | 873 | 19103 |
| `user_future_job_training_video` | 5 | 0 | 0 |
| `user_interview_location` | 9 | 50 | 17 |
| `user_job_questioner_answers` | 9 | 307 | 7821 |
| `user_job_reaction` | 8 | 9 | 11 |
| `user_job_role` | 6 | 882 | 87752 |
| `user_job_type` | 6 | 858 | 67574 |
| `user_job_wishlist` | 5 | 178 | 3969 |
| `user_kaampe_video` | 5 | 4 | 392 |
| `user_key_accomplishments` | 9 | 12 | 51 |
| `user_location` | 7 | 0 | 0 |
| `user_notes` | 7 | 23 | 6 |
| `user_portfolio` | 14 | 212 | 4587 |
| `user_role_permissions` | 5 | 1119 | 76987 |
| `user_roles` | 7 | 3338 | 125563 |
| `user_schedule_callback` | 8 | 0 | 0 |
| `user_skiiling_reaction` | 6 | 2 | 61 |
| `user_skiiling_reaction_type` | 3 | 0 | 0 |
| `user_susbcription_job_counting` | 15 | 192 | 378 |
| `user_susbcription_order` | 29 | 192 | 379 |
| `user_type_category` | 8 | 4 | 4 |
| `user_video_bio` | 9 | 0 | 0 |
| `user_video_bio_gallery` | 8 | 0 | 0 |
| `user_willing_to_relocate` | 8 | 2938 | 125390 |
| `user_work_availability` | 8 | 0 | 0 |
| `users` | 63 | 3652 | 132365 |
| `users_educations` | 12 | 182 | 29794 |
| `users_industries` | 6 | 0 | 0 |
| `users_job` | 22 | 808 | 26513 |
| `users_notification` | 11 | 33993 | 102249 |
| `users_skills` | 9 | 1559 | 206808 |
| `users_soft_skills` | 6 | 322 | 26838 |
| `video_bio_sample_video` | 4 | 0 | 0 |

## Action Items

### REQUIRED Tables Missing

These tables are used by the recruiter ATS and must be created:

- **`ats_login_tokens`** — One-time SSO login tokens from org-recruiter (missing in PROD (AWS RDS))

