1. Tổng Quan

1.1 Mục đích

Tài liệu này định nghĩa thiết kế cơ sở dữ liệu cho module Tuyển Dụng (cơ sở dữ liệu: recr_db).

1.2 Phụ Thuộc Module

1.3 Yêu Cầu Được Giải Quyết

1.4 Sơ Đồ ERD

Hình 1: ERD Module Tuyển Dụng (3 Vai Trò)

2. Thiết Kế Schema

2.1 Chiến Lược Cơ Sở Dữ Liệu

2.2 Bảng

Bảng: recr_users — Bảng Người Dùng Cơ Bản

Lưu ý: Xác thực người dùng (email, mật khẩu, vai trò) được xử lý bởi AUTH module. Bảng này chỉ lưu dữ liệu đặc thù của tuyển dụng và liên kết với auth_users qua user_id.

CREATE TABLE recr_users (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL UNIQUE REFERENCES auth_users(id),
  avatar_url  VARCHAR(500),
  created_at  TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

Bảng: recr_candidates — Hồ Sơ Ứng Viên

Lưu ý: Cột resume_url đã ngưng sử dụng. Tham chiếu file hồ sơ được lưu trong recr_candidate_resumes qua file_id (UUID → sys_files.id trong FILE module).

CREATE TABLE recr_candidates (
  id               BIGSERIAL PRIMARY KEY,
  user_id          BIGINT NOT NULL UNIQUE REFERENCES recr_users(id),
  resume_url       VARCHAR(500),              -- DEPRECATED
  linkedin_url     VARCHAR(300),
  skills           JSONB DEFAULT '[]',
  experience_years INT DEFAULT 0,
  current_company  VARCHAR(200),
  expected_salary  DECIMAL(12,2),
  source           VARCHAR(20) CHECK (source IN ('website','referral','linkedin','job_board','other')),
  created_at       TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at       TIMESTAMP NOT NULL DEFAULT NOW()
);

Bảng: recr_candidate_resumes — Danh Sách Hồ Sơ Ứng Viên

Mô Tả: Lưu các tham chiếu file hồ sơ ứng viên được quản lý bởi FILE module. Mỗi ứng viên có thể có nhiều hồ sơ. file_id tham chiếu sys_files.id.

CREATE TABLE recr_candidate_resumes (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  candidate_id    BIGINT NOT NULL REFERENCES recr_candidates(id) ON DELETE CASCADE,
  file_id         UUID NOT NULL REFERENCES sys_files(id) ON DELETE CASCADE,
  is_primary      BOOLEAN NOT NULL DEFAULT FALSE,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_recr_candidate_resumes_candidate_id ON recr_candidate_resumes(candidate_id);
CREATE INDEX idx_recr_candidate_resumes_file_id ON recr_candidate_resumes(file_id);
CREATE INDEX idx_recr_candidate_resumes_primary ON recr_candidate_resumes(candidate_id, is_primary) WHERE is_primary = TRUE;

Bảng: recr_employers — Hồ Sơ Nhà Tuyển Dụng

CREATE TABLE recr_employers (
  id                  BIGSERIAL PRIMARY KEY,
  user_id             BIGINT NOT NULL UNIQUE REFERENCES recr_users(id),
  company_name        VARCHAR(200) NOT NULL,
  company_website     VARCHAR(300),
  company_description TEXT,
  industry            VARCHAR(100),
  company_size        VARCHAR(20) CHECK (company_size IN ('1-10','11-50','51-200','201-500','501-1000','1000+')),
  logo_url            VARCHAR(500),
  is_verified         BOOLEAN DEFAULT false,
  created_at          TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at          TIMESTAMP NOT NULL DEFAULT NOW()
);

Bảng: recr_hr_profiles — Hồ Sơ HR

CREATE TABLE recr_hr_profiles (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL UNIQUE REFERENCES recr_users(id),
  department  VARCHAR(100),
  position    VARCHAR(100),
  created_at  TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

Bảng: recr_jobs — Tin Tuyển Dụng

Lưu ý: Cả HR và Nhà Tuyển Dụng đều có thể tạo tin tuyển dụng. Trường created_by ghi nhận ai đã tạo tin.

CREATE TABLE recr_jobs (
  id              BIGSERIAL PRIMARY KEY,
  employer_id     BIGINT REFERENCES recr_employers(id),
  created_by      BIGINT NOT NULL REFERENCES recr_users(id),
  title           VARCHAR(200) NOT NULL,
  description     TEXT,
  department      VARCHAR(100),
  location        VARCHAR(200),
  salary_min      DECIMAL(12,2),
  salary_max      DECIMAL(12,2),
  employment_type VARCHAR(20) NOT NULL CHECK (employment_type IN ('full_time','part_time','contract','internship')),
  status          VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','pending_approval','open','closed','archived')),
  approved_by     BIGINT REFERENCES recr_users(id),
  approved_at     TIMESTAMP,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

Bảng: recr_applications — Hồ Sơ Ứng Tuyển

Lưu ý: resume_file_id lưu bản sao khóa tại thời điểm nộp — đảm bảo hồ sơ HR xét duyệt là hồ sơ ứng viên đã nộp.

Ràng buộc: UNIQUE(job_id, candidate_id) — Mỗi ứng viên chỉ nộp 1 lần cho 1 việc làm.

CREATE TABLE recr_applications (
  id              BIGSERIAL PRIMARY KEY,
  job_id          BIGINT NOT NULL REFERENCES recr_jobs(id),
  candidate_id    BIGINT NOT NULL REFERENCES recr_candidates(id),
  resume_file_id  UUID REFERENCES sys_files(id),
  cover_letter    TEXT,
  status          VARCHAR(20) NOT NULL DEFAULT 'submitted' CHECK (status IN ('submitted','screening','interview','offer','rejected','hired')),
  applied_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  UNIQUE(job_id, candidate_id)
);

Bảng: recr_interviews — Lịch Phỏng Vấn

CREATE TABLE recr_interviews (
  id               BIGSERIAL PRIMARY KEY,
  application_id   BIGINT NOT NULL REFERENCES recr_applications(id),
  interviewer_id   BIGINT NOT NULL REFERENCES recr_users(id),
  scheduled_at     TIMESTAMP NOT NULL,
  duration_minutes INT DEFAULT 60,
  location         VARCHAR(200),
  meeting_link     VARCHAR(500),
  status           VARCHAR(20) NOT NULL DEFAULT 'scheduled' CHECK (status IN ('scheduled','completed','cancelled','no_show')),
  notes            TEXT,
  created_at       TIMESTAMP NOT NULL DEFAULT NOW()
);

Bảng: recr_evaluations — Đánh Giá Phỏng Vấn

CREATE TABLE recr_evaluations (
  id                     BIGSERIAL PRIMARY KEY,
  interview_id           BIGINT NOT NULL REFERENCES recr_interviews(id),
  evaluator_id           BIGINT NOT NULL REFERENCES recr_users(id),
  score                  INT CHECK (score >= 1 AND score <= 10),
  technical_score        INT CHECK (technical_score >= 1 AND technical_score <= 10),
  communication_score    INT CHECK (communication_score >= 1 AND communication_score <= 10),
  cultural_fit_score     INT CHECK (cultural_fit_score >= 1 AND cultural_fit_score <= 10),
  recommendation         VARCHAR(20) CHECK (recommendation IN ('strong_yes','yes','neutral','no','strong_no')),
  comments               TEXT,
  submitted_at           TIMESTAMP NOT NULL DEFAULT NOW()
);

Bảng: recr_job_requirements — Yêu Cầu Việc Làm

CREATE TABLE recr_job_requirements (
  id                BIGSERIAL PRIMARY KEY,
  job_id            BIGINT NOT NULL REFERENCES recr_jobs(id),
  requirement_type  VARCHAR(20) NOT NULL CHECK (requirement_type IN ('skill','experience','education','certification')),
  description       TEXT NOT NULL,
  is_mandatory      BOOLEAN DEFAULT true,
  sort_order        INT DEFAULT 0
);

Bảng: recr_subscription_plans — Gói Đăng Ký

CREATE TABLE recr_subscription_plans (
  id                BIGSERIAL PRIMARY KEY,
  name              VARCHAR(50) NOT NULL UNIQUE,
  display_name      VARCHAR(100) NOT NULL,
  duration_months   INT NOT NULL,
  base_price        DECIMAL(12,2) NOT NULL,
  discount_percent  INT DEFAULT 0 CHECK (discount_percent >= 0 AND discount_percent <= 100),
  free_months       INT DEFAULT 0,
  final_price       DECIMAL(12,2) NOT NULL,
  is_active         BOOLEAN DEFAULT true,
  features          JSONB DEFAULT '[]',
  created_at        TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO recr_subscription_plans (name, display_name, duration_months, base_price, discount_percent, free_months, final_price, features) VALUES
('1_month', '1 Month', 1, 500000, 0, 0, 500000, '{"candidate": ["priority_application", "profile_boost"], "hr": ["advanced_analytics"], "employer": ["featured_listing"]}'),
('6_months', '6 Months', 6, 3000000, 10, 0, 2700000, '{"candidate": ["priority_application", "profile_boost", "resume_review"], "hr": ["advanced_analytics", "priority_support"], "employer": ["featured_listing", "premium_analytics"]}'),
('1_year', '1 Year', 12, 6000000, 0, 2, 5000000, '{"candidate": ["priority_application", "profile_boost", "resume_review", "career_coaching"], "hr": ["advanced_analytics", "priority_support", "dedicated_account_manager"], "employer": ["featured_listing", "premium_analytics", "dedicated_account_manager"]}');

Bảng: recr_subscriptions — Đăng Ký Người Dùng

Ràng buộc: CHECK (end_date > start_date)

CREATE TABLE recr_subscriptions (
  id                BIGSERIAL PRIMARY KEY,
  user_id           BIGINT NOT NULL REFERENCES recr_users(id),
  plan_id           BIGINT NOT NULL REFERENCES recr_subscription_plans(id),
  status            VARCHAR(20) NOT NULL DEFAULT 'active' CHECK (status IN ('active','expired','cancelled','pending')),
  start_date        TIMESTAMP NOT NULL,
  end_date          TIMESTAMP NOT NULL,
  payment_amount    DECIMAL(12,2) NOT NULL,
  payment_method    VARCHAR(50),
  payment_reference VARCHAR(100),
  auto_renew        BOOLEAN DEFAULT false,
  created_at        TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMP NOT NULL DEFAULT NOW(),
  CHECK (end_date > start_date)
);

CREATE INDEX idx_recr_subscriptions_user_id ON recr_subscriptions(user_id);
CREATE INDEX idx_recr_subscriptions_status ON recr_subscriptions(status);
CREATE UNIQUE INDEX idx_recr_subscriptions_active ON recr_subscriptions(user_id) WHERE status = 'active';

Bảng: recr_subscription_history — Lịch Sử Đăng Ký

CREATE TABLE recr_subscription_history (
  id                BIGSERIAL PRIMARY KEY,
  subscription_id   BIGINT NOT NULL REFERENCES recr_subscriptions(id),
  action            VARCHAR(50) NOT NULL CHECK (action IN ('created','renewed','upgraded','cancelled','expired','payment_received')),
  old_status        VARCHAR(20),
  new_status        VARCHAR(20),
  old_plan_id       BIGINT REFERENCES recr_subscription_plans(id),
  new_plan_id       BIGINT REFERENCES recr_subscription_plans(id),
  notes             TEXT,
  created_at        TIMESTAMP NOT NULL DEFAULT NOW()
);

Bảng: recr_job_reviews — Đánh Giá Việc Làm

Ràng buộc: UNIQUE(job_id, user_id)

CREATE TABLE recr_job_reviews (
  id                BIGSERIAL PRIMARY KEY,
  job_id            BIGINT NOT NULL REFERENCES recr_jobs(id),
  user_id           BIGINT NOT NULL REFERENCES recr_users(id),
  overall_rating    INT NOT NULL CHECK (overall_rating >= 1 AND overall_rating <= 5),
  work_life_balance INT CHECK (work_life_balance >= 1 AND work_life_balance <= 5),
  compensation      INT CHECK (compensation >= 1 AND compensation <= 5),
  career_growth     INT CHECK (career_growth >= 1 AND career_growth <= 5),
  company_culture   INT CHECK (company_culture >= 1 AND company_culture <= 5),
  title             VARCHAR(200) NOT NULL,
  description       TEXT,
  is_anonymous      BOOLEAN DEFAULT false,
  status            VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected')),
  created_at        TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMP NOT NULL DEFAULT NOW(),
  UNIQUE(job_id, user_id)
);

Bảng: recr_candidate_reviews — Đánh Giá Ứng Viên

Ràng buộc: UNIQUE(interview_id, interviewer_id)

CREATE TABLE recr_candidate_reviews (
  id                  BIGSERIAL PRIMARY KEY,
  candidate_id        BIGINT NOT NULL REFERENCES recr_candidates(id),
  interviewer_id      BIGINT NOT NULL REFERENCES recr_users(id),
  interview_id        BIGINT NOT NULL REFERENCES recr_interviews(id),
  technical_skills    INT CHECK (technical_skills >= 1 AND technical_skills <= 5),
  communication_skills INT CHECK (communication_skills >= 1 AND communication_skills <= 5),
  cultural_fit        INT CHECK (cultural_fit >= 1 AND cultural_fit <= 5),
  problem_solving     INT CHECK (problem_solving >= 1 AND problem_solving <= 5),
  team_collaboration  INT CHECK (team_collaboration >= 1 AND team_collaboration <= 5),
  title               VARCHAR(200) NOT NULL,
  description         TEXT,
  recommendation      VARCHAR(20) CHECK (recommendation IN ('strong_yes','yes','neutral','no','strong_no')),
  created_at          TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at          TIMESTAMP NOT NULL DEFAULT NOW(),
  UNIQUE(interview_id, interviewer_id)
);

Bảng: recr_invitations — Quản Lý Lời Mời

CREATE TABLE recr_invitations (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email           VARCHAR(255) NOT NULL,
  token_hash      VARCHAR(64) NOT NULL,
  role            VARCHAR(50) NOT NULL,
  inviter_id      UUID NOT NULL REFERENCES auth_users(id),
  employer_id     UUID REFERENCES recr_employers(id),
  status          VARCHAR(20) NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'ACCEPTED', 'EXPIRED', 'REVOKED')),
  expired_at      TIMESTAMP NOT NULL,
  accepted_at     TIMESTAMP,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT recr_invitations_token_hash_unique UNIQUE (token_hash)
);

CREATE INDEX idx_recr_invitations_email ON recr_invitations(email);
CREATE INDEX idx_recr_invitations_token_hash ON recr_invitations(token_hash);
CREATE INDEX idx_recr_invitations_status ON recr_invitations(status);
CREATE INDEX idx_recr_invitations_inviter_id ON recr_invitations(inviter_id);
CREATE INDEX idx_recr_invitations_employer_id ON recr_invitations(employer_id);

3. Chỉ Mục (Indexes)

recr_users / recr_candidates

CREATE INDEX idx_recr_users_user_id ON recr_users(user_id);

CREATE INDEX idx_recr_candidates_user_id ON recr_candidates(user_id);
CREATE INDEX idx_recr_candidates_skills ON recr_candidates USING GIN (skills);

recr_employers

CREATE INDEX idx_recr_employers_user_id ON recr_employers(user_id);
CREATE INDEX idx_recr_employers_company ON recr_employers(company_name);

recr_jobs

CREATE INDEX idx_recr_jobs_employer_id ON recr_jobs(employer_id);
CREATE INDEX idx_recr_jobs_created_by ON recr_jobs(created_by);
CREATE INDEX idx_recr_jobs_status ON recr_jobs(status);
CREATE INDEX idx_recr_jobs_location ON recr_jobs(location);

recr_applications

CREATE INDEX idx_recr_applications_job_id ON recr_applications(job_id);
CREATE INDEX idx_recr_applications_candidate_id ON recr_applications(candidate_id);
CREATE INDEX idx_recr_applications_status ON recr_applications(status);
CREATE INDEX idx_recr_applications_resume_file_id ON recr_applications(resume_file_id);

recr_interviews / recr_evaluations

CREATE INDEX idx_recr_interviews_application_id ON recr_interviews(application_id);
CREATE INDEX idx_recr_interviews_interviewer_id ON recr_interviews(interviewer_id);
CREATE INDEX idx_recr_interviews_scheduled_at ON recr_interviews(scheduled_at);

CREATE INDEX idx_recr_evaluations_interview_id ON recr_evaluations(interview_id);

recr_subscription_plans / recr_subscriptions

CREATE INDEX idx_recr_subscription_plans_name ON recr_subscription_plans(name);
CREATE INDEX idx_recr_subscription_plans_active ON recr_subscription_plans(is_active);

CREATE INDEX idx_recr_subscriptions_plan_id ON recr_subscriptions(plan_id);
CREATE INDEX idx_recr_subscriptions_end_date ON recr_subscriptions(end_date);
CREATE INDEX idx_recr_subscriptions_user_status ON recr_subscriptions(user_id, status);

recr_subscription_history

CREATE INDEX idx_recr_subscription_history_subscription_id ON recr_subscription_history(subscription_id);
CREATE INDEX idx_recr_subscription_history_created_at ON recr_subscription_history(created_at);

recr_job_reviews / recr_candidate_reviews

CREATE INDEX idx_recr_job_reviews_job_id ON recr_job_reviews(job_id);
CREATE INDEX idx_recr_job_reviews_user_id ON recr_job_reviews(user_id);
CREATE INDEX idx_recr_job_reviews_status ON recr_job_reviews(status);

CREATE INDEX idx_recr_candidate_reviews_candidate_id ON recr_candidate_reviews(candidate_id);
CREATE INDEX idx_recr_candidate_reviews_interviewer_id ON recr_candidate_reviews(interviewer_id);
CREATE INDEX idx_recr_candidate_reviews_interview_id ON recr_candidate_reviews(interview_id);

recr_invitations

CREATE INDEX idx_recr_invitations_email ON recr_invitations(email);
CREATE INDEX idx_recr_invitations_token_hash ON recr_invitations(token_hash);
CREATE INDEX idx_recr_invitations_status ON recr_invitations(status);
CREATE INDEX idx_recr_invitations_inviter_id ON recr_invitations(inviter_id);
CREATE INDEX idx_recr_invitations_employer_id ON recr_invitations(employer_id);

4. Di Chuyển Dữ Liệu (Migrations)

4.1 Chiến Lược Di Chuyển

4.2 Thứ Tự Di Chuyển

5. Từ Điển Dữ Liệu

6. Sao Lưu & Phục Hồi

Tham khảo chiến lược sao lưu cấp hệ thống. Dữ liệu của module này là một phần của recr_db.

7. Tính Toán Hiệu Suất