# New Database Schema Structure ## Overview Migration from separate `master_employers`, `companies`, `members` tables to unified `companies` and `users` structure. --- ## 1. companies - Primary Tenant Entity ```javascript { "_id": ObjectId, "id": Integer, // Auto-increment PK // Profile "name": String, // Company name (from company_name in registration) "name_ar": String, // Arabic name (optional) "slug": String, // Unique URL identifier (generated from name) "logo": String, // Logo file path (from logo upload) "logo_url": String, // Full URL to logo "profile_image": String, // Profile/cover image path "profile_image_url": String, // Full URL to profile image "cover_image_url": String, // Alternative cover image "website": String, // Company website "industry": String, // Industry/sector "type": String, // Company type (from registration step 2) "no_of_employees": Integer, // Employee count (from no_of_employees) "employee_count": Integer, // Alternative field name "founded_year": Integer, // Year company was founded "about": String, // Company description "description": String, // Alternative description field "description_ar": String, // Arabic description // Location "location": Integer, // Country ID (for backward compatibility) "country_id": Integer, // Country ID "city": Integer, // City ID (for backward compatibility) "city_id": Integer, // City ID // Contact Information (Owner/Primary Contact) "contact_no": String, // Primary contact number "phone": String, // Alternative phone field "phone_code": String, // Country code for phone "phone_country_code": String, // Alternative phone code field "email": String, // Primary company email // Recruiter/Primary Contact Details "recruiter_name": String, // Primary recruiter name "recruiter_email": String, // Recruiter email "recruiter_mobile": String, // Recruiter mobile "recruiter_country_code": String, // Recruiter phone code "recruiter_designation": String, // Recruiter job title // Social & External Links "social_link": String, // Social media links (JSON or comma-separated) // Acquisition & Marketing "how_did_you_hear_about_us": String, // Marketing source "referral_source": String, // Referral source "referred_by": String, // Who referred this company // Status & Verification "status": Integer, // 0=inactive, 1=active, 2=suspended "registration_step_completed": Boolean, // Registration completion status "is_verified": Boolean, // Email verification status "verify_email": Integer, // 0=not verified, 1=verified "verified_at": ISODate, // When email was verified "verification_token": String, // Email verification token "token_expiry": ISODate, // Token expiration time // Subscription & Billing "subscription_plan_id": String, // Current subscription plan "subscription_status": String, // Subscription status // Audit "created_at": ISODate, "updated_at": ISODate, "deleted_at": ISODate // Soft delete } // Indexes db.companies.createIndex({ "id": 1 }, { unique: true }) db.companies.createIndex({ "slug": 1 }, { unique: true }) db.companies.createIndex({ "email": 1 }, { unique: true }) db.companies.createIndex({ "status": 1 }) db.companies.createIndex({ "is_verified": 1 }) db.companies.createIndex({ "country_id": 1 }) db.companies.createIndex({ "city_id": 1 }) ``` --- ## 2. users - All Users (Owners + Members) ```javascript { "_id": ObjectId, "id": Integer, // Auto-increment PK // Company Relationship "company_id": Integer, // FK → companies.id (REQUIRED) "role_id": Integer, // FK → roles.id "is_owner": Boolean, // true = company owner, false = member // Authentication "email": String, // Unique email address "password": String, // Hashed password (password_hash) "password_hash": String, // Alternative field name "phone": String, // Phone number "contact_no": String, // Alternative phone field "mobile_number": String, // Alternative mobile field "phone_code": String, // Country code for phone "phone_country_code": String, // Alternative phone code field "recruiter_country_code": String, // For owner/recruiter // Profile - Basic "name": String, // Full name "first_name": String, // First name (can be parsed from name) "last_name": String, // Last name (can be parsed from name) "avatar_url": String, // Profile picture URL "profile_photo": String, // Profile photo path "profile_image": String, // Alternative profile image field "designation": String, // Job title within company "recruiter_name": String, // For owner (from registration) "recruiter_designation": String, // For owner (from registration) "recruiter_email": String, // For owner (from registration) "recruiter_mobile": String, // For owner (from registration) // Profile - Extended (for Members) "employee_code": String, // Internal employee ID/code "contact_number": String, // Contact number "gender": String, // "male" | "female" | "no-preference" "date_of_birth": Date, // Date of birth "home_town": String, // Hometown/place of origin "language": String, // Preferred language(s) "marital_status": String, // Marital status "nationality": String, // Nationality "religion": String, // Religion "permanent_address": String, // Permanent address "current_address": String, // Current residential address "assign_workspace": String, // Assigned workspace/department // User Type & Role "user_type": String, // "employer" | "member" // Status & Verification "status": Integer, // 0=inactive, 1=active, 2=suspended "is_email_verified": Boolean, // Email verification status "verify_email": Integer, // 0=not verified, 1=verified "is_deleted": Boolean, // Soft delete flag "verification_token": String, // Email verification token "token_expiry": ISODate, // Token expiration // 2FA (Two-Factor Authentication) "has_2fa_enabled": Boolean, // Is 2FA enabled "two_fa_secret": String, // 2FA secret key // Permissions (for Members) "permissions": Array, // Array of permission IDs or codes // Session & Security "remember_token": String, // Remember me token "last_login_at": ISODate, // Last login timestamp // Audit & Tracking "invited_by_id": Integer, // FK → users.id (who invited/created this user) "creator_user_id": Integer, // Company owner who created this member "joined_at": ISODate, // When user joined/accepted invite "created_at": ISODate, "updated_at": ISODate, "deleted_at": ISODate // Soft delete timestamp } // Indexes db.users.createIndex({ "id": 1 }, { unique: true }) db.users.createIndex({ "email": 1 }, { unique: true }) db.users.createIndex({ "company_id": 1, "status": 1 }) db.users.createIndex({ "company_id": 1, "is_owner": 1 }) db.users.createIndex({ "company_id": 1, "is_deleted": 1 }) db.users.createIndex({ "company_id": 1, "user_type": 1 }) db.users.createIndex({ "role_id": 1 }) db.users.createIndex({ "employee_code": 1 }) db.users.createIndex({ "is_owner": 1 }) ``` --- ## 3. Data Migration Notes ### Company Registration Flow **Old:** master_employers + companies **New:** companies + users (with is_owner=true) 1. **Step 1 (register):** - Create `companies` record with basic info (name, email, phone, how_did_you_hear_about_us) - Create `users` record with company_id, is_owner=true, email, password, phone 2. **Step 2 (completeRegistration):** - Update `companies` with full profile (logo, industry, location, about, etc.) - Update `users` with recruiter details (recruiter_name, designation, etc.) ### Member Creation Flow **Old:** master_employers + members + user_member_mappings **New:** users (with is_owner=false) 1. Create `users` record with: - company_id (from creator/owner) - is_owner=false - user_type="member" - All member-specific fields - invited_by_id/creator_user_id (owner's user.id) --- ## 4. Key Differences | Old Structure | New Structure | |--------------|---------------| | master_employers (owners) | users (is_owner=true) | | members | users (is_owner=false) | | companies | companies (enhanced) | | user_member_mappings | Replaced by company_id + invited_by_id in users | --- ## 5. Backward Compatibility Fields Keep these fields during transition: - `mobile_number` → migrate to `phone` - `contact_no` → migrate to `phone` - `location` → migrate to `country_id` - `city` → migrate to `city_id` - `verify_email` → migrate to `is_email_verified` - `status` (integer) → can keep or migrate to string enum --- ## 6. Foreign Key Relationships ``` users.company_id → companies.id users.role_id → roles.id users.invited_by_id → users.id users.creator_user_id → users.id (owner) company_subscriptions.company_id → companies.id payments.company_id → companies.id employer_jobs.company_id → companies.id ``` --- ## 7. Query Examples ### Get Company with Owner ```javascript // Get company const company = await db.companies.findOne({ id: companyId }); // Get owner const owner = await db.users.findOne({ company_id: companyId, is_owner: true }); ``` ### Get All Company Members ```javascript const members = await db.users.find({ company_id: companyId, is_owner: false, is_deleted: false, status: 1 }); ``` ### Get User with Company Info ```javascript const user = await db.users.findOne({ id: userId }); const company = await db.companies.findOne({ id: user.company_id }); ``` --- ## 8. Required Model Changes ### New Models: 1. `Company` model (replace old Company model) 2. `User` model (replace MasterEmployer + Member models) ### Update References In: - AuthController - CompanyController - MemberController - All job/interview/subscription controllers - Middleware (JwtMiddleware) - Helpers --- ## 9. API Response Structure ### Profile API Response ```json { "status": 200, "message": "Profile retrieved successfully", "data": { "user": { "id": 123, "email": "user@example.com", "name": "John Doe", "is_owner": true, "company_id": 456 }, "company": { "id": 456, "name": "Tech Corp", "logo_url": "...", "industry": "Technology" }, "subscription": { "plan": "Essential", "status": "active" } } } ```