# 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`
