User Migration Analysis

Database Exploration Results

Databases Available

• dermadetect - Production database (32,354 requests)
• dd_api - Another production database with additional tables
• annotations_db, clinic_test, milabs - Other databases

User Data Found

1. Patient Users (from rn_requests)

Source: rn_requests.user_id field

Statistics:

• Total requests: 32,354
• Unique users: 4,518
• Unique vendors: 19

User ID Format: Firebase-style UIDs

ViqT0zpUOpUFT0x0WRQ0BLGG8JV2
Z6wLmXFvdaSbTGjfLadcCeCp8a03

Top Vendors:

• yeledoctor: 13,584 requests
• maccabi: 13,077 requests
• Individual emails (jonmidi@gmail.com, hlandov@gmail.com, etc.)

Data Available:

• user_id (Firebase UID)
• vendor_id (tenant/vendor identifier)
• user_identifier (mostly empty)
• user_crypt (mostly empty)
• No email addresses
• No names
• No authentication credentials

Conclusion: These are anonymous or OAuth-authenticated users (likely Firebase Auth). No direct migration possible without external user database.

2. Physician Users (from groups table in dd_api)

Source: dd_api.groups table

Structure:

doctor_email     | text (unique)
vendor_id        | text (e.g., 'maccabi')
password         | text (encrypted - starts with \xc30d04...)
roles            | text[] (array of roles)
permission       | text ('user', 'superuser')
is_active        | boolean
employee_id      | bigint
settings         | jsonb
admin_settings   | jsonb

Statistics:

• 44 physicians with passwords (encrypted, not bcrypt)
• All physicians associated with vendors (mostly ‘maccabi’)

Sample Physicians:

ghaben_a@mac.org.il    | maccabi | user      | active
gertman_h@mac.org.il   | maccabi | user      | active
lavi_iz@mac.org.il     | maccabi | superuser | active

Conclusion: CAN MIGRATE - We have physician emails, roles, and encrypted passwords.

3. User Validation Logs (from check_user_log)

Source: check_user_log table

Structure:

• request (jsonb) - Contains user validation requests
• response (jsonb) - Contains validation results
• user_type (‘power’, etc.)
• is_valid (boolean)

Sample Data:

Request:  {"ID": "11111"}
Response: {"uuid": "...", "success": true, "user_type": "power"}

Conclusion: Validation logs only, no user credentials.

Migration Strategy

Option 1: Migrate Physicians Only (Recommended for MVP)

What We Can Migrate: ✅ Physician emails (44 users) ✅ Vendor associations ✅ Roles (user, superuser) ✅ Active status

What We CANNOT Migrate: ❌ Existing passwords (encrypted with unknown algorithm - starts with \xc30d04...) ❌ Patient user data (Firebase Auth UIDs with no email/password)

Migration Steps:

1. Create users and physicians tables

2. Import physicians from dd_api.groups:

   • Email → users.email
   • Generate UUID → users.uuid
   • Role mapping → users.role (‘physician’)
   • Require password reset (cannot decrypt old passwords)
   • vendor_id → create tenants table, link to tenant
   • permission → map to roles
   • employee_id, settings → physicians table

3. Link existing cases:

   • Keep rn_requests.user_id as-is (Firebase UID reference)
   • No direct link to new users table
   • Future cases use new users FK

Result: 44 physicians ready to log in (after password reset)

Option 2: Start Fresh + Keep Legacy References

Strategy:

1. Create new authentication system
2. Keep legacy user_id field in rn_requests
3. Add new created_by_user_id FK to new users table
4. Support BOTH:
   • Legacy cases (with Firebase UID in user_id)
   • New cases (with FK to users table)

Migration for Physicians:

• Same as Option 1
• Send password reset emails to all 44 physicians
• They create new accounts in new system

Migration for Patients:

• Cannot migrate (no contact info)
• Firebase UIDs remain as anonymous identifiers
• New patient registrations use new auth system

Option 3: Hybrid with External Auth

Strategy:

1. Implement new JWT auth
2. Also support Firebase Auth (if Firebase project still exists)
3. Map Firebase UIDs to new users when they first log in
4. Physicians use new system

Pros:

• Existing patients can still log in via Firebase
• Smooth transition

Cons:

• Requires Firebase project access
• Complex dual authentication

Recommended Approach

Phase 1: Physician Migration

-- Create migration script
INSERT INTO users (uuid, email, role, is_active, created_at)
SELECT
  gen_random_uuid(),
  doctor_email,
  CASE
    WHEN permission = 'superuser' THEN 'admin'
    ELSE 'physician'
  END,
  is_active,
  NOW()
FROM dd_api.groups
WHERE doctor_email IS NOT NULL;
 
-- Create physicians records
INSERT INTO physicians (uuid, user_id, language_preference, ...)
SELECT ...
FROM dd_api.groups g
JOIN users u ON u.email = g.doctor_email;
 
-- Send password reset emails to all physicians

Phase 2: Patient Support

-- Keep legacy user_id field
ALTER TABLE rn_requests
  ADD COLUMN created_by_user_id UUID REFERENCES users(uuid);
 
-- New cases use created_by_user_id
-- Old cases keep user_id as-is (anonymous Firebase UID)

Phase 3: Vendor/Tenant Migration

-- Create tenants from unique vendor_ids
INSERT INTO tenants (name, slug, created_at)
SELECT DISTINCT vendor_id, vendor_id, NOW()
FROM rn_requests
WHERE vendor_id IS NOT NULL;
 
-- Link users to tenants
UPDATE users u
SET tenant_id = (
  SELECT t.id FROM tenants t
  JOIN dd_api.groups g ON g.vendor_id = t.slug
  WHERE g.doctor_email = u.email
);

Password Reset Strategy

Since we cannot decrypt the old passwords (\xc30d04... format is unknown), we must:

1. Create password reset tokens for all migrated physicians

2. Send email notifications:

   Subject: DermaDetect Platform Upgrade - Password Reset Required
   
   We've upgraded our authentication system. Please reset your password:
   [Reset Password Link with token]

3. Implement password reset flow:

   • POST /auth/request-reset - Send reset email
   • POST /auth/reset-password - Set new password with token

Questions to Answer

1. Do you have access to the Firebase project?

   • If yes: We can support dual auth (Firebase + JWT)
   • If no: Patients become anonymous (keep UID reference only)

2. Can we send emails to the 44 physicians?

   • Need email service configured (SendGrid, AWS SES, etc.)
   • Or manually send reset links

3. What’s the encryption format for passwords?

   • \xc30d04... - PGP? Custom encryption?
   • If we can decrypt, migration is easier
   • If not, password reset required

4. Should we create tenant records now?

   • 19 unique vendors/tenants
   • Map physicians and patients to tenants

Next Steps

1. Create users, physicians, tenants tables
2. Write migration script from dd_api.groups
3. Implement password reset functionality
4. Test migration with 1-2 physicians
5. Run full migration
6. Send password reset emails

Data Preservation

Keep these tables for reference:

• dd_api.groups (original physician data)
• rn_requests with original user_id (patient Firebase UIDs)
• check_user_log (validation history)

Add new fields:

• rn_requests.created_by_user_id (FK to new users)
• rn_requests.migrated_from (note: ‘firebase_uid’ or ‘groups_table’)