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