# Old to New Mapping — Nxtgauge Database Migration This document maps the current schema to the target schema. --- ## 1. Tables to CREATE (New) ### Core Infrastructure | New Table | Purpose | Priority | | ------------------------- | --------------------------- | -------- | | `user_sessions` | Session tracking | High | | `user_role_profiles` | Root profile for all roles | Critical | | `verification_requests` | Verification workflow | High | | `verification_documents` | Verification documents | High | | `approval_requests` | Approval workflow | High | | `approval_logs` | Approval audit trail | High | | `audit_logs` | Comprehensive audit logging | High | | `audit_log_changes` | Field-level audit changes | Medium | | `dashboard_widgets` | Dashboard widget configs | Medium | | `kb_sections` | Knowledge base sections | Low | | `kb_article_feedback` | KB article feedback | Low | | `notification_templates` | Notification templates | Medium | | `smtp_configs` | SMTP configurations | Low | | `orders` | Order management | Medium | | `order_items` | Order line items | Medium | | `payment_gateway_configs` | Payment gateway configs | Low | | `payment_transactions` | Payment transaction log | Low | | `tax_rules` | Tax rules | Low | ### Extension Tables (New FK Reference) | New Table | References | | ------------------------------- | ----------------------------------------------- | | `photographer_profiles` | `user_role_profiles` (via user_role_profile_id) | | `tutor_profiles` | `user_role_profiles` | | `makeup_artist_profiles` | `user_role_profiles` | | `developer_profiles` | `user_role_profiles` | | `video_editor_profiles` | `user_role_profiles` | | `graphic_designer_profiles` | `user_role_profiles` | | `social_media_manager_profiles` | `user_role_profiles` | | `fitness_trainer_profiles` | `user_role_profiles` | | `catering_service_profiles` | `user_role_profiles` | | `ugc_content_creator_profiles` | `user_role_profiles` | --- ## 2. Tables to RENAME | Current Name | Target Name | Notes | | -------------- | -------------------- | --------------------- | | `applications` | `job_applications` | Job applications | | `requirements` | `leads` | Customer leads | | `coupon_uses` | `coupon_redemptions` | Coupon usage tracking | --- ## 3. Tables to UPDATE (Schema Changes) ### users | Action | Changes | | ------ | --------------------------------------------------- | | ADD | `account_type` (TEXT) | | ADD | `last_login_at` (TIMESTAMP) | | RENAME | `full_name` → Remove (use profiles) | | RENAME | `email_verified` → Remove (use verification_status) | | RENAME | `phone_verified` → Remove (use verification_status) | ### refresh_tokens | Action | Changes | | ------ | ------------------- | | ADD | `revoked` (BOOLEAN) | ### roles | Action | Changes | | ------ | -------------------------------------- | | ADD | `description` (TEXT) | | ADD | `department_id` (UUID) | | ADD | `can_approve_requests` (BOOLEAN) | | ADD | `can_manage_system_settings` (BOOLEAN) | ### departments | Action | Changes | | ------ | ----------------------------- | | ADD | `code` (TEXT) | | ADD | `description` (TEXT) | | ADD | `department_head` (TEXT) | | ADD | `department_email` (TEXT) | | ADD | `visibility` (TEXT) | | ADD | `transfers_enabled` (BOOLEAN) | | ADD | `updated_at` (TIMESTAMP) | ### designations | Action | Changes | | ------ | --------------------------- | | ADD | `code` (TEXT) | | ADD | `department_id` (UUID) | | ADD | `description` (TEXT) | | ADD | `level` (TEXT) | | ADD | `can_manage_team` (BOOLEAN) | | ADD | `can_approve` (BOOLEAN) | | ADD | `is_active` (BOOLEAN) | | ADD | `updated_at` (TIMESTAMP) | ### employees | Action | Changes | | ------ | ---------------------------------- | | ADD | `joining_date` (DATE) | | ADD | `employment_status` (TEXT) | | ADD | `manager_employee_id` (UUID) | | ADD | `updated_at` (TIMESTAMP) | | RENAME | `user_id` → Keep (points to users) | --- ## 4. Extension Tables — Update FK Reference **Current State:** Extension tables reference `users.id` via `user_id` **Target State:** Extension tables reference `user_role_profiles.id` via `user_role_profile_id` ### photographer_profiles | Action | Changes | | ------ | ------------------------------------------- | | RENAME | `user_id` → `user_role_profile_id` | | REMOVE | `portfolio_url` | | REMOVE | `equipment_list` | | REMOVE | `years_of_experience` (use root profile) | | REMOVE | `hourly_rate` (use `starting_price_inr`) | | REMOVE | `custom_data` (preserve to JSONB if needed) | ### tutor_profiles | Action | Changes | | ------ | --------------------------------------- | | RENAME | `user_id` → `user_role_profile_id` | | RENAME | `subjects_taught` → `subjects` | | REMOVE | `education_level` (use `qualification`) | | REMOVE | `custom_data` | ### makeup_artist_profiles | Action | Changes | | ------ | ---------------------------------- | | RENAME | `user_id` → `user_role_profile_id` | | REMOVE | `custom_data` | ### developer_profiles | Action | Changes | | ------ | ---------------------------------- | | RENAME | `user_id` → `user_role_profile_id` | | REMOVE | `github_url` (FORBIDDEN) | | REMOVE | `portfolio_url` (FORBIDDEN) | | REMOVE | `custom_data` | ### video_editor_profiles | Action | Changes | | ------ | ---------------------------------- | | RENAME | `user_id` → `user_role_profile_id` | | REMOVE | `reel_url` (FORBIDDEN) | | REMOVE | `custom_data` | ### graphic_designer_profiles | Action | Changes | | ------ | ---------------------------------- | | RENAME | `user_id` → `user_role_profile_id` | | REMOVE | `portfolio_url` (FORBIDDEN) | | REMOVE | `custom_data` | ### social_media_manager_profiles | Action | Changes | | ------ | ---------------------------------- | | RENAME | `user_id` → `user_role_profile_id` | | REMOVE | `custom_data` | ### fitness_trainer_profiles | Action | Changes | | ------ | ---------------------------------- | | RENAME | `user_id` → `user_role_profile_id` | | REMOVE | `custom_data` | ### catering_service_profiles | Action | Changes | | ------ | ---------------------------------- | | RENAME | `user_id` → `user_role_profile_id` | | REMOVE | `custom_data` | ### ugc_content_creator_profiles | Action | Changes | | ------ | ---------------------------------- | | RENAME | `user_id` → `user_role_profile_id` | --- ## 5. Other Profile Tables ### company_profiles | Action | Changes | | ------ | ----------------------------------------- | | ADD | `verification_status` (TEXT) | | ADD | `approval_status` (TEXT) | | RENAME | `website_url` → Remove | | RENAME | `registration_number` → Remove | | RENAME | `employee_count` → Remove | | REMOVE | Legacy fields merged into JSONB if needed | ### customer_profiles | Action | Changes | | ------ | ---------------------------------------------------- | | ADD | `email` (TEXT) | | RENAME | `experience_years` → Remove (use candidate_profiles) | | RENAME | `custom_data` → Remove | ### job_seeker_profiles | Action | Changes | | ------ | ---------------------- | | RENAME | → `candidate_profiles` | | REMOVE | `custom_data` | --- ## 6. Portfolio Domain ### portfolio_items | Action | Changes | | ------ | ------------------------------------------------ | | RENAME | `professional_id` → `user_role_profile_id` | | ADD | `display_order` (INTEGER) | | REMOVE | `profession_key` (derive from user_role_profile) | ### services | Action | Changes | | ------ | ------------------------------------------------ | | RENAME | `professional_id` → `user_role_profile_id` | | REMOVE | `profession_key` (derive from user_role_profile) | --- ## 7. Marketplace Domain ### jobs | Action | Changes | | ------ | -------------------------------------------------- | | ADD | `posted_by_user_id` (UUID) | | ADD | `mode_of_work` (TEXT) | | ADD | `budget_inr` (INTEGER) | | ADD | `salary_range_json` (JSONB) | | RENAME | `company_id` → `company_profile_id` | | REMOVE | `category` (use tags) | | REMOVE | `skills` (use tags) | | REMOVE | `salary_min`, `salary_max` (use salary_range_json) | | REMOVE | `experience_years` | | REMOVE | `rejection_reason` (use approval_requests) | ### job_applications | Action | Changes | | ------ | ----------------------------------------------------- | | RENAME | `applications` → `job_applications` | | RENAME | `job_seeker_id` → Remove (use `applicant_user_id`) | | ADD | `applicant_user_id` (UUID) | | RENAME | `cover_letter` → `cover_note` | | REMOVE | `resume_url` (use candidate_profiles.resume_file_url) | | REMOVE | `contact_viewed` | ### leads | Action | Changes | | ------ | ------------------------------------------ | | RENAME | `requirements` → `leads` | | RENAME | `customer_id` → `created_by_user_id` | | ADD | `required_date` (DATE) | | REMOVE | `profession_key` (use leads directly) | | REMOVE | `extra_data_json` | | REMOVE | `rejection_reason` (use approval_requests) | | REMOVE | `request_count`, `accepted_count` | | REMOVE | `expires_at` | ### lead_requests | Action | Changes | | ------ | ------------------------------------------ | | RENAME | `professional_id` → `user_role_profile_id` | | ADD | `remarks` (TEXT) | | REMOVE | `tracecoins_reserved` | | REMOVE | `resolved_at` | --- ## 8. Verification & Approval ### Current: onboarding_submissions | Action | Migration | | ------ | ---------------------------------------------- | | Map to | `verification_requests` OR `approval_requests` | ### Current: verification_requests (exists) | Action | Changes | | ------ | ----------------------------- | | UPDATE | Align fields to target schema | ### Current: verification_logs | Action | Changes | | ------ | ----------------------------- | | UPDATE | Align fields to target schema | --- ## 9. Reviews ### reviews | Action | Changes | | ------ | -------------------------------------------------- | | ADD | `entity_type` (TEXT) | | ADD | `entity_id` (UUID) | | RENAME | `professional_id` → `entity_id` (with entity_type) | | RENAME | `customer_id` → `reviewer_user_id` | | REMOVE | `lead_request_id` (reference through entity_id) | | ADD | `status` (TEXT) | --- ## 10. Finance Domain ### tracecoin_wallets | Action | Changes | | ------ | ----------------------------- | | RENAME | `balance` → `current_balance` | | ADD | `updated_at` (TIMESTAMP) | ### tracecoin_ledger | Action | Changes | | ------ | --------------------------- | | ADD | `balance_after` (INTEGER) | | RENAME | `type` → `transaction_type` | | RENAME | `reason` → `reference_type` | | ADD | `remarks` (TEXT) | ### payments | Action | Changes | | ------ | ---------------------------------- | | ADD | `payment_gateway_config_id` (UUID) | | ADD | `payment_method` (TEXT) | | ADD | `currency_code` (TEXT) | | RENAME | `verified_at` → Remove | | REMOVE | `package_id` (use order_items) | ### invoices | Action | Changes | | ------ | --------------------------- | | ADD | `order_id` (UUID) | | RENAME | `subtotal` → `subtotal_inr` | | RENAME | `gst_amount` → `tax_inr` | | RENAME | `total` → `total_inr` | | ADD | `discount_inr` (INTEGER) | | ADD | `due_at` (TIMESTAMP) | | ADD | `paid_at` (TIMESTAMP) | ### coupons | Action | Changes | | ------ | ----------------------------------------- | | ADD | `max_discount_inr` (INTEGER) | | ADD | `min_order_value_inr` (INTEGER) | | ADD | `valid_from` (TIMESTAMP) | | ADD | `valid_to` (TIMESTAMP) | | RENAME | `applies_to` → Keep | | REMOVE | `max_uses` (use `usage_limit`) | | REMOVE | `uses_count` (tracked in redemptions) | | REMOVE | `per_user_limit` (tracked in redemptions) | ### coupon_redemptions | Action | Changes | | ------ | ------------------------------------ | | RENAME | `coupon_uses` → `coupon_redemptions` | | ADD | `order_id` (UUID) | | RENAME | `used_at` → `redeemed_at` | | ADD | `discount_amount_inr` (INTEGER) | --- ## 11. Knowledge Base ### kb_articles | Action | Changes | | ------ | ------------------------------------------- | | ADD | `section_id` (UUID) | | RENAME | `body` → `content_markdown` | | ADD | `article_type` (TEXT) | | ADD | `audience_type` (TEXT) | | RENAME | `is_published` → `status` | | RENAME | `created_by` → `author_user_id` | | REMOVE | `target_roles` | | REMOVE | `views` (can add back as separate tracking) | --- ## 12. Support ### support_tickets | Action | Changes | | ------ | ------------------------------------- | | RENAME | `user_id` → `created_by_user_id` | | RENAME | `assigned_to` → `assigned_to_user_id` | | RENAME | `description` → Keep | | ADD | `related_entity_type` (TEXT) | | ADD | `related_entity_id` (UUID) | | ADD | `closed_at` (TIMESTAMP) | ### support_ticket_messages | Action | Changes | | ------ | ------------------------------ | | RENAME | `sender_id` → `sender_user_id` | | RENAME | `body` → `message_body` | | ADD | `attachment_url` (TEXT) | --- ## 13. Notifications ### notifications | Action | Changes | | ------ | ------------------------------------ | | ADD | `channel` (TEXT) | | RENAME | `type` → Keep (add channel) | | RENAME | `reference_id` → `related_entity_id` | | ADD | `related_entity_type` (TEXT) | | RENAME | `is_read` → `status` | --- ## 14. Tables to DEPRECATE (Keep for Backward Compatibility) | Table | Reason | Action | | ------------------------ | ------------------------------- | ----------------------------- | | `onboarding_submissions` | Legacy onboarding flow | Map to verification/approval | | `onboarding_configs` | Legacy onboarding flow | Map to dashboard_configs | | `onboarding_states` | Legacy onboarding flow | Remove after migration | | `submission_documents` | Legacy onboarding flow | Map to verification_documents | | `professionals` | Replaced by user_role_profiles | Keep until migration complete | | `user_settings` | Already exists, align structure | Align columns | --- ## 15. Tables to DROP (After Migration) | Table | Condition | | ------------------------ | --------------------------------------------- | | `professionals` | After all data migrated to user_role_profiles | | `onboarding_submissions` | After verification_requests populated | | `onboarding_configs` | After dashboard_configs populated | | `onboarding_states` | After migration complete | | `submission_documents` | After verification_documents populated | --- ## 16. Foreign Key Changes Summary ### From users.id to user_role_profiles.id **Tables changing FK:** - `photographer_profiles.user_id` → `user_role_profile_id` - `tutor_profiles.user_id` → `user_role_profile_id` - `makeup_artist_profiles.user_id` → `user_role_profile_id` - `developer_profiles.user_id` → `user_role_profile_id` - `video_editor_profiles.user_id` → `user_role_profile_id` - `graphic_designer_profiles.user_id` → `user_role_profile_id` - `social_media_manager_profiles.user_id` → `user_role_profile_id` - `fitness_trainer_profiles.user_id` → `user_role_profile_id` - `catering_service_profiles.user_id` → `user_role_profile_id` - `ugc_content_creator_profiles.user_id` → `user_role_profile_id` - `portfolio_items.professional_id` → `user_role_profile_id` - `services.professional_id` → `user_role_profile_id` - `lead_requests.professional_id` → `user_role_profile_id`