# Database ERD — SaaS Job Portal Platform

Full relational schema (implemented in `03-database-schema.sql`). Grouped by domain below with a Mermaid ERD per group (Mermaid renders directly in GitHub/most markdown viewers, and you can paste any block into mermaid.live to view it).

## Group A — Identity, Tenancy & Access

```mermaid
erDiagram
    AGENCIES ||--o{ USERS : employs
    AGENCIES ||--o{ AGENCY_VERIFICATION_DOCUMENTS : uploads
    ROLES ||--o{ USERS : assigned
    ROLES ||--o{ ROLE_PERMISSIONS : has
    PERMISSIONS ||--o{ ROLE_PERMISSIONS : grants
    USERS ||--o{ USER_DEVICES : logs_in_from
    USERS ||--o{ LOGIN_HISTORY : has
    USERS ||--o{ ACTIVITY_LOGS : performs
    USERS ||--o{ AUDIT_LOGS : triggers

    AGENCIES {
        bigint id PK
        varchar company_name
        varchar slug UK
        varchar logo_path
        varchar website
        varchar email
        varchar phone
        varchar industry
        varchar company_size
        text description
        varchar registration_number
        varchar tax_number
        text address
        enum verification_status
        enum status
        bigint owner_user_id FK
        timestamp created_at
        timestamp updated_at
    }
    USERS {
        bigint id PK
        bigint agency_id FK "nullable for candidates/super-admin"
        bigint role_id FK
        varchar name
        varchar email UK
        varchar phone
        varchar password_hash
        enum status
        boolean two_factor_enabled
        timestamp email_verified_at
        timestamp last_login_at
        timestamp created_at
    }
    ROLES { bigint id PK  varchar name UK  varchar slug UK }
    PERMISSIONS { bigint id PK  varchar name UK  varchar module }
    ROLE_PERMISSIONS { bigint role_id FK  bigint permission_id FK }
```

## Group B — Candidate Profile Domain

```mermaid
erDiagram
    USERS ||--|| CANDIDATE_PROFILES : has
    CANDIDATE_PROFILES ||--o{ CANDIDATE_EXPERIENCES : has
    CANDIDATE_PROFILES ||--o{ CANDIDATE_EDUCATION : has
    CANDIDATE_PROFILES ||--o{ CANDIDATE_CERTIFICATIONS : has
    CANDIDATE_PROFILES ||--o{ CANDIDATE_SKILLS : has
    CANDIDATE_PROFILES ||--o{ CANDIDATE_LANGUAGES : has
    CANDIDATE_PROFILES ||--o{ CANDIDATE_PROJECTS : has
    CANDIDATE_PROFILES ||--o{ CANDIDATE_PORTFOLIO_LINKS : has
    CANDIDATE_PROFILES ||--o{ CANDIDATE_REFERENCES : has
    CANDIDATE_PROFILES ||--o{ RESUMES : uploads
    SKILLS ||--o{ CANDIDATE_SKILLS : referenced_by

    CANDIDATE_PROFILES {
        bigint id PK
        bigint user_id FK
        varchar profile_picture_path
        text cover_letter
        decimal salary_expectation_min
        decimal salary_expectation_max
        varchar salary_currency
        json preferred_locations
        enum availability_status "open_to_work|actively_looking|not_looking|available_immediately"
        date notice_period_end
        boolean is_public
        boolean hide_email
        boolean hide_mobile
        boolean hide_employer
        tinyint profile_completion_pct
        timestamp last_active_at
    }
    RESUMES {
        bigint id PK
        bigint candidate_profile_id FK
        varchar file_path
        varchar original_filename
        boolean is_primary
        int download_count
        timestamp uploaded_at
    }
```

## Group C — Agency / Recruiter / ATS Domain

```mermaid
erDiagram
    AGENCIES ||--o{ JOBS : posts
    USERS ||--o{ JOBS : "posted_by (recruiter)"
    JOBS ||--o{ JOB_APPLICATIONS : receives
    CANDIDATE_PROFILES ||--o{ JOB_APPLICATIONS : submits
    JOB_APPLICATIONS ||--o{ APPLICATION_STATUS_HISTORY : has
    JOB_APPLICATIONS ||--o{ APPLICATION_NOTES : has
    AGENCIES ||--o{ CANDIDATE_UNLOCKS : purchases
    AGENCIES ||--o{ SHORTLISTS : creates
    SHORTLISTS ||--o{ SHORTLIST_CANDIDATES : contains
    AGENCIES ||--o{ SAVED_SEARCHES : creates
    AGENCIES ||--o{ INTERVIEWS : schedules
    JOB_APPLICATIONS ||--o| INTERVIEWS : has

    JOBS {
        bigint id PK
        bigint agency_id FK
        bigint posted_by_user_id FK
        varchar title
        text description
        varchar department
        varchar category
        varchar experience_level
        decimal salary_min
        decimal salary_max
        varchar location
        boolean is_remote
        boolean is_hybrid
        json required_skills
        enum employment_type
        varchar education_requirement
        text benefits
        enum status "draft|published|expired|closed"
        boolean is_featured
        boolean is_urgent
        date expires_at
        varchar seo_slug UK
        timestamp created_at
    }
    JOB_APPLICATIONS {
        bigint id PK
        bigint job_id FK
        bigint candidate_profile_id FK
        enum status "applied|under_review|shortlisted|interview_scheduled|interview_completed|rejected|hired"
        bigint assigned_recruiter_id FK
        text cover_note
        bigint resume_id FK
        timestamp applied_at
    }
    CANDIDATE_UNLOCKS {
        bigint id PK
        bigint agency_id FK
        bigint recruiter_id FK
        bigint candidate_profile_id FK
        enum unlock_type "contact_reveal|resume_download|full_profile"
        int credits_spent
        timestamp unlocked_at
    }
    INTERVIEWS {
        bigint id PK
        bigint job_application_id FK
        bigint scheduled_by_user_id FK
        datetime scheduled_at
        int duration_minutes
        enum mode "zoom|google_meet|onsite|phone"
        varchar meeting_link
        enum status "scheduled|rescheduled|cancelled|completed"
        text feedback
        int feedback_rating
    }
```

## Group D — SaaS Billing Domain

```mermaid
erDiagram
    SUBSCRIPTION_PLANS ||--o{ AGENCY_SUBSCRIPTIONS : subscribed_to
    AGENCIES ||--o{ AGENCY_SUBSCRIPTIONS : has
    AGENCIES ||--|| WALLETS : owns
    WALLETS ||--o{ WALLET_TRANSACTIONS : records
    AGENCIES ||--o{ CREDIT_BALANCES : has
    CREDIT_BALANCES ||--o{ CREDIT_TRANSACTIONS : records
    AGENCIES ||--o{ INVOICES : billed
    INVOICES ||--o{ INVOICE_ITEMS : contains
    AGENCIES ||--o{ PAYMENTS : makes
    PAYMENTS ||--o| INVOICES : settles

    SUBSCRIPTION_PLANS {
        bigint id PK
        varchar name
        varchar slug UK
        decimal price_monthly
        decimal price_yearly
        int candidate_view_limit "−1 = unlimited"
        int contact_reveal_limit
        int resume_download_limit
        int recruiter_limit
        int job_posting_limit
        boolean is_active
    }
    AGENCY_SUBSCRIPTIONS {
        bigint id PK
        bigint agency_id FK
        bigint plan_id FK
        enum billing_cycle "monthly|yearly"
        enum status "trialing|active|past_due|cancelled|expired"
        date current_period_start
        date current_period_end
        boolean auto_renew
    }
    CREDIT_BALANCES {
        bigint id PK
        bigint agency_id FK UK
        int balance
        timestamp last_updated
    }
    WALLETS {
        bigint id PK
        bigint agency_id FK UK
        decimal balance
        varchar currency
    }
    INVOICES {
        bigint id PK
        bigint agency_id FK
        varchar invoice_number UK
        decimal subtotal
        decimal tax_amount
        decimal total
        enum status "draft|sent|paid|overdue|void"
        date due_date
    }
    PAYMENTS {
        bigint id PK
        bigint agency_id FK
        bigint invoice_id FK
        enum gateway "stripe|paypal|jazzcash|easypaisa|bank_transfer"
        varchar gateway_transaction_id
        decimal amount
        enum status "pending|success|failed|refunded"
        json gateway_response
    }
```

## Group E — Messaging, Notifications, Audit

```mermaid
erDiagram
    USERS ||--o{ CONVERSATIONS : participates
    CONVERSATIONS ||--o{ MESSAGES : contains
    MESSAGES ||--o{ MESSAGE_ATTACHMENTS : has
    USERS ||--o{ NOTIFICATIONS : receives
    USERS ||--o{ AUDIT_LOGS : triggers
    USERS ||--o{ ACTIVITY_LOGS : performs

    CONVERSATIONS { bigint id PK  bigint job_application_id FK "nullable"  timestamp created_at }
    MESSAGES {
        bigint id PK
        bigint conversation_id FK
        bigint sender_id FK
        text body
        boolean is_read
        timestamp read_at
        timestamp created_at
    }
    NOTIFICATIONS {
        bigint id PK
        bigint user_id FK
        varchar type
        json payload
        enum channel "email|sms|in_app|whatsapp"
        boolean is_read
        timestamp sent_at
    }
    AUDIT_LOGS {
        bigint id PK
        bigint user_id FK
        varchar module
        varchar action
        json old_value
        json new_value
        timestamp created_at
    }
    ACTIVITY_LOGS {
        bigint id PK
        bigint user_id FK
        bigint agency_id FK
        varchar role
        varchar action
        varchar ip_address
        varchar device
        varchar browser
        timestamp created_at
    }
```

Full column-level DDL with types, indexes, foreign keys and constraints is in **`03-database-schema.sql`** — that file is the source of truth; diagrams above are for quick visual reference.
