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 Xác thực (cơ sở dữ liệu: auth_db).

1.2 Yêu cầu được đề cập

1.3 Sơ đồ ERD

Hình 1: ERD Module Xác thực

2. Thiết kế Schema

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

2.2 Bảng: auth_users

CREATE TABLE auth_users (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email           VARCHAR(255) UNIQUE,
  phone           VARCHAR(20) UNIQUE,
  name            VARCHAR(100) NOT NULL,
  password_hash   VARCHAR(255),
  email_verified  BOOLEAN NOT NULL DEFAULT false,
  phone_verified  BOOLEAN NOT NULL DEFAULT false,
  status          VARCHAR(20) NOT NULL DEFAULT 'active'
                  CHECK (status IN ('active', 'inactive', 'banned')),
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT auth_users_email_or_phone CHECK (email IS NOT NULL OR phone IS NOT NULL)
);

2.3 Bảng: auth_sessions

CREATE TABLE auth_sessions (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  token           VARCHAR(500) NOT NULL,
  refresh_token   VARCHAR(500),
  ip_address      VARCHAR(45),
  user_agent      TEXT,
  expires_at      TIMESTAMP NOT NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

2.4 Bảng: auth_accounts

CREATE TABLE auth_accounts (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  provider        VARCHAR(50) NOT NULL,
  provider_id     VARCHAR(255) NOT NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT auth_accounts_provider_unique UNIQUE (provider, provider_id)
);

2.5 Bảng: auth_verification

CREATE TABLE auth_verification (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  code            VARCHAR(10) NOT NULL,
  type            VARCHAR(20) NOT NULL
                  CHECK (type IN ('email', 'phone', 'password_reset')),
  expires_at      TIMESTAMP NOT NULL,
  used            BOOLEAN NOT NULL DEFAULT false,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

2.6 Bảng: auth_roles

CREATE TABLE auth_roles (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name            VARCHAR(50) NOT NULL UNIQUE,
  description     TEXT,
  permissions     JSONB DEFAULT '[]',
  created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

Vai trò Hệ thống Mặc định

Lưu ý: Tài khoản super_admin chỉ có thể được tạo bởi admin hiện tại qua POST /admin/create-user. Endpoint đăng ký (POST /sign-up/email) chỉ gán vai trò user.

2.7 Bảng: auth_user_roles

CREATE TABLE auth_user_roles (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  role_id         UUID NOT NULL REFERENCES auth_roles(id) ON DELETE CASCADE,
  assigned_at     TIMESTAMP NOT NULL DEFAULT NOW(),
  assigned_by     UUID REFERENCES auth_users(id),
  CONSTRAINT auth_user_roles_unique UNIQUE (user_id, role_id)
);

2.8 Bảng: auth_organizations

CREATE TABLE auth_organizations (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name            VARCHAR(100) NOT NULL,
  slug            VARCHAR(100) NOT NULL UNIQUE,
  logo            VARCHAR(500),
  metadata        JSONB,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

2.9 Bảng: auth_members

CREATE TABLE auth_members (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES auth_organizations(id) ON DELETE CASCADE,
  user_id         UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  role            VARCHAR(50) NOT NULL DEFAULT 'member',
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT auth_members_unique UNIQUE (organization_id, user_id)
);

2.10 Bảng: auth_teams

CREATE TABLE auth_teams (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name            VARCHAR(100) NOT NULL,
  organization_id UUID NOT NULL REFERENCES auth_organizations(id) ON DELETE CASCADE,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

2.11 Bảng: auth_team_members

CREATE TABLE auth_team_members (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  team_id         UUID NOT NULL REFERENCES auth_teams(id) ON DELETE CASCADE,
  user_id         UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT auth_team_members_unique UNIQUE (team_id, user_id)
);

2.12 Bảng: auth_invitations

CREATE TABLE auth_invitations (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES auth_organizations(id) ON DELETE CASCADE,
  email           VARCHAR(255) NOT NULL,
  token_hash      VARCHAR(64) UNIQUE,
  role            VARCHAR(50) DEFAULT 'member',
  team_id         UUID REFERENCES auth_teams(id) ON DELETE SET NULL,
  employer_id     UUID,
  status          VARCHAR(20) NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending', 'accepted', 'rejected', 'canceled', 'expired', 'revoked')),
  expires_at      TIMESTAMP NOT NULL,
  accepted_at     TIMESTAMP,
  inviter_id      UUID NOT NULL REFERENCES auth_users(id),
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

Định nghĩa Cột (cột mới):

Giá trị Trạng thái:

2.13 Bảng: auth_organization_roles

CREATE TABLE auth_organization_roles (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES auth_organizations(id) ON DELETE CASCADE,
  role            VARCHAR(50) NOT NULL,
  description     VARCHAR(500),
  permission      VARCHAR(255) NOT NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT auth_org_roles_unique UNIQUE (organization_id, role)
);

Định nghĩa Cột:

Định dạng Quyền hạn: resource:action (ví dụ: job:read, member:update)

Vai trò Tổ chức Mặc định

Mỗi tổ chức có thể định nghĩa vai trò riêng. Dưới đây là các vai trò tổ chức tiêu chuẩn thường được sử dụng:

Lưu ý: Vai trò tổ chức độc lập với vai trò cấp hệ thống (user, super_admin). Người dùng có vai trò hệ thống user có thể có các vai trò tổ chức khác nhau trong các tổ chức khác nhau.

3. Chỉ mục

-- auth_users
CREATE INDEX idx_auth_users_email ON auth_users(email);
CREATE INDEX idx_auth_users_phone ON auth_users(phone);
CREATE INDEX idx_auth_users_status ON auth_users(status);

-- auth_sessions
CREATE INDEX idx_auth_sessions_user_id ON auth_sessions(user_id);
CREATE INDEX idx_auth_sessions_token ON auth_sessions(token);
CREATE INDEX idx_auth_sessions_expires ON auth_sessions(expires_at);

-- auth_verification
CREATE INDEX idx_auth_verification_user_id ON auth_verification(user_id);
CREATE INDEX idx_auth_verification_code ON auth_verification(code);
CREATE INDEX idx_auth_verification_type ON auth_verification(type);

-- auth_user_roles
CREATE INDEX idx_auth_user_roles_user_id ON auth_user_roles(user_id);
CREATE INDEX idx_auth_user_roles_role_id ON auth_user_roles(role_id);

-- auth_organizations
CREATE INDEX idx_auth_organizations_slug ON auth_organizations(slug);

-- auth_members
CREATE INDEX idx_auth_members_org_id ON auth_members(organization_id);
CREATE INDEX idx_auth_members_user_id ON auth_members(user_id);

-- auth_teams
CREATE INDEX idx_auth_teams_org_id ON auth_teams(organization_id);

-- auth_team_members
CREATE INDEX idx_auth_team_members_team_id ON auth_team_members(team_id);
CREATE INDEX idx_auth_team_members_user_id ON auth_team_members(user_id);

-- auth_invitations
CREATE UNIQUE INDEX idx_auth_invitations_token_hash ON auth_invitations(token_hash);
CREATE INDEX idx_auth_invitations_org_id ON auth_invitations(organization_id);
CREATE INDEX idx_auth_invitations_email ON auth_invitations(email);
CREATE INDEX idx_auth_invitations_status ON auth_invitations(status);
CREATE INDEX idx_auth_invitations_inviter_id ON auth_invitations(inviter_id);
CREATE INDEX idx_auth_invitations_employer_id ON auth_invitations(employer_id);

-- auth_organization_roles
CREATE INDEX idx_auth_org_roles_org_id ON auth_organization_roles(organization_id);

4. Di chuyển dữ liệu

4.1 Chiến lược Di chuyển dữ liệu

4.2 Thứ tự Di chuyển dữ liệu

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