Tracewebstudio Dev 03376b9567 feat: Add database redesign documentation and Phase 1-2 migrations

- Add schema_audit.md documenting current schema issues
- Add target_schema.md with complete target schema design
- Add old_to_new_mapping.md with table mapping
- Add migration_plan.md with phased migration strategy
- Add Phase 1 migrations (core infrastructure):
  - user_sessions table
  - users missing columns
  - departments updates
  - designations updates
  - employees updates
- Add Phase 2 migrations (profile domain - CRITICAL):
  - create user_role_profiles root table
  - backfill user_role_profiles from existing profiles
  - add user_role_profile_id to extension tables
  - remove forbidden external portfolio links
- Add user_role_profile Rust model
- Update photographer model to use user_role_profile_id

2026-04-12 23:21:11 +02:00

19 KiB

Raw Permalink Blame History

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

19 KiB Raw Permalink Blame History