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 Quản lý Tệp tin (cơ sở dữ liệu: file_db).

1.2 Yêu cầu được Giải quyết

1.3 Sơ đồ ERD

Hình 1: ERD Quản lý Tệp tin

2. Thiết kế Schema

2.1 Chiến lược Cơ sở dữ liệu

2.2 Metadata Tệp tin & Thư mục

CREATE TABLE sys_files (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  employer_id     UUID REFERENCES sys_employers(id) ON DELETE SET NULL,
  user_id         UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  parent_folder_id UUID REFERENCES sys_files(id) ON DELETE SET NULL,
  type            VARCHAR(10) NOT NULL DEFAULT 'FILE',
  original_name   VARCHAR(500) NOT NULL,
  file_path       VARCHAR(1000),
  mime_type       VARCHAR(100),
  file_size       BIGINT DEFAULT 0,
  category        VARCHAR(50) NOT NULL DEFAULT 'OTHER',
  is_public       BOOLEAN NOT NULL DEFAULT FALSE,
  status          VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  search_vector   TSVECTOR,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  deleted_at      TIMESTAMP,
  CONSTRAINT sys_files_type_check CHECK (type IN ('FILE', 'FOLDER')),
  CONSTRAINT sys_files_category_check CHECK (category IN ('RESUME', 'AVATAR', 'COMPANY_DOC', 'JOB_ATTACHMENT', 'OTHER')),
  CONSTRAINT sys_files_status_check CHECK (status IN ('TEMP', 'ACTIVE', 'DELETED', 'REPLACED'))
);

-- Thư mục: file_path, mime_type là NULL
-- Tệp tin: file_path, mime_type không NULL

2.3 Chiến lược Đường dẫn Lưu trữ

Các tệp tin được tổ chức trên local filesystem bằng cấu trúc thư mục phân cấp hỗ trợ đa tenancy và kiểm soát truy cập:

Các Thành phần Đường dẫn:

• Thư mục gốc uploads/: Thư mục gốc lưu trữ tất cả tệp tin
• Tiền tố UUID: Tên tệp tin được tạo để ngăn chặn xung đột (ví dụ: a1b2c3d4-resume.pdf)
• Thư mục danh mục: Nhóm tệp tin theo mục đích (RESUME, AVATAR, v.v.)
• Thư mục Người dùng: Cách ly tệp tin theo từng user để kiểm soát truy cập

2.4 Giá trị Enum Danh mục

2.5 Vòng đời Trạng thái

2.6 Quyền truy cập Tệp tin

CREATE TABLE sys_file_permissions (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  file_id         UUID NOT NULL REFERENCES sys_files(id) ON DELETE CASCADE,
  user_id         UUID REFERENCES auth_users(id) ON DELETE CASCADE,
  role            VARCHAR(50),
  org_id          UUID,
  permission      VARCHAR(20) NOT NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT sys_file_permissions_permission_check CHECK (permission IN ('VIEW', 'DOWNLOAD', 'EDIT', 'DELETE')),
  CONSTRAINT sys_file_permissions_target_check CHECK (
    (user_id IS NOT NULL AND role IS NULL AND org_id IS NULL) OR
    (user_id IS NULL AND role IS NOT NULL AND org_id IS NULL) OR
    (user_id IS NULL AND role IS NULL AND org_id IS NOT NULL)
  )
);

-- Một trong ba: user_id, role, hoặc org_id phải được đặt (không phải nhiều)

2.7 Thống kê Sử dụng Lưu trữ

CREATE TABLE sys_storage_usage (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id             UUID REFERENCES auth_users(id) ON DELETE CASCADE,
  org_id              UUID,
  total_size          BIGINT NOT NULL DEFAULT 0,
  file_count          INTEGER NOT NULL DEFAULT 0,
  last_calculated_at  TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT sys_storage_usage_scope_check CHECK (
    (user_id IS NOT NULL AND org_id IS NULL) OR
    (user_id IS NULL AND org_id IS NOT NULL)
  )
);

CREATE UNIQUE INDEX sys_storage_usage_user_idx ON sys_storage_usage(user_id) WHERE user_id IS NOT NULL;
CREATE UNIQUE INDEX sys_storage_usage_org_idx ON sys_storage_usage(org_id) WHERE org_id IS NOT NULL;

2.8 Hạn ngạch Lưu trữ

CREATE TABLE sys_storage_quotas (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID REFERENCES auth_users(id) ON DELETE CASCADE,
  org_id          UUID,
  max_total_size  BIGINT NOT NULL,
  max_file_count  INTEGER NOT NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT sys_storage_quotas_scope_check CHECK (
    (user_id IS NOT NULL AND org_id IS NULL) OR
    (user_id IS NULL AND org_id IS NOT NULL)
  )
);

CREATE UNIQUE INDEX sys_storage_quotas_user_idx ON sys_storage_quotas(user_id) WHERE user_id IS NOT NULL;
CREATE UNIQUE INDEX sys_storage_quotas_org_idx ON sys_storage_quotas(org_id) WHERE org_id IS NOT NULL;

2.9 Chính sách Lưu trữ

CREATE TABLE sys_storage_policies (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  category            VARCHAR(50) NOT NULL UNIQUE,
  allowed_mime_types  JSONB NOT NULL,
  max_file_size       BIGINT NOT NULL,
  retention_days      INTEGER,
  encryption_enabled  BOOLEAN NOT NULL DEFAULT FALSE,
  created_at          TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at          TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Chính sách mặc định
INSERT INTO sys_storage_policies (category, allowed_mime_types, max_file_size) VALUES
  ('RESUME', '["application/pdf", "application/msword", "application/vnd.openxmlformats-officedocument.wordprocessingml.document"]', 10485760),
  ('AVATAR', '["image/jpeg", "image/png", "image/webp"]', 5242880),
  ('COMPANY_DOC', '["image/*", "application/pdf"]', 10485760),
  ('JOB_ATTACHMENT', '["application/pdf", "application/msword", "image/*"]', 10485760),
  ('OTHER', '["*/*"]', 10485760);

3. Chỉ mục

-- sys_files
CREATE INDEX sys_files_user_id_idx ON sys_files(user_id);
CREATE INDEX sys_files_employer_id_idx ON sys_files(employer_id);
CREATE INDEX sys_files_category_idx ON sys_files(category);
CREATE INDEX sys_files_status_idx ON sys_files(status);
CREATE INDEX sys_files_created_at_idx ON sys_files(created_at);
CREATE INDEX sys_files_type_idx ON sys_files(type);
CREATE INDEX sys_files_parent_folder_id_idx ON sys_files(parent_folder_id);

-- Chỉ mục tổng hợp cho mẫu truy vấn phổ biến (liệt kê tệp tin đang hoạt động theo người dùng và danh mục)
CREATE INDEX sys_files_user_category_status_idx ON sys_files(user_id, category, status);

-- Chỉ mục tổng hợp cho truy vấn theo phạm vi nhà tuyển dụng
CREATE INDEX sys_files_employer_category_status_idx ON sys_files(employer_id, category, status);

-- Chỉ mục tổng hợp cho liệt kê thư mục (các mục con của một thư mục)
CREATE INDEX sys_files_parent_folder_type_idx ON sys_files(parent_folder_id, type, status);

-- Chỉ mục tìm kiếm toàn văn
CREATE INDEX sys_files_search_idx ON sys_files USING gin(search_vector);

-- Ràng buộc duy nhất trên file_path để ngăn trùng lặp (chỉ cho tệp tin)
CREATE UNIQUE INDEX sys_files_file_path_unique ON sys_files(file_path) WHERE file_path IS NOT NULL;

-- sys_file_permissions
CREATE INDEX sys_file_permissions_file_id_idx ON sys_file_permissions(file_id);
CREATE INDEX sys_file_permissions_user_id_idx ON sys_file_permissions(user_id);
CREATE INDEX sys_file_permissions_role_idx ON sys_file_permissions(role);
CREATE INDEX sys_file_permissions_org_id_idx ON sys_file_permissions(org_id);

-- sys_storage_usage
CREATE INDEX sys_storage_usage_user_id_idx ON sys_storage_usage(user_id);
CREATE INDEX sys_storage_usage_org_id_idx ON sys_storage_usage(org_id);

-- sys_storage_quotas
CREATE INDEX sys_storage_quotas_user_id_idx ON sys_storage_quotas(user_id);
CREATE INDEX sys_storage_quotas_org_id_idx ON sys_storage_quotas(org_id);

Mẫu Sử dụng Chỉ mục

4. Di chuyển Dữ liệu

4.1 Chiến lược Di chuyển

4.2 Thứ tự Di chuyển

5. Từ điển Dữ liệu

Ước tính Tăng trưởng Dòng