Thuộc Thiết Kế CSDL — Nhóm Tổ chức & User

Chi Tiết Entity — Tổ Chức & User¶

Bảng: organization_unit, app_user, user_role

organization_unit¶

CREATE TABLE organization_unit (
    id              BIGSERIAL PRIMARY KEY,
    public_id       UUID NOT NULL DEFAULT uuidv7(), -- ID public cho API/external integration
    code            TEXT UNIQUE NOT NULL,        -- IDxx.xx.xx (VD: ID01.01.01). Bất biến
    name            TEXT NOT NULL,               -- Tên đơn vị (VD: Phòng CS QLHC về TTXH)
    level           TEXT NOT NULL
                        CHECK (level IN ('TINH', 'SO', 'BAN', 'PHONG')),
    parent_id       BIGINT REFERENCES organization_unit(id),  -- Cây phân cấp (self-reference)
    successor_id    BIGINT REFERENCES organization_unit(id),  -- Đơn vị kế thừa (khi sáp nhập/chia tách)
    contact_info    TEXT,                        -- Địa chỉ, điện thoại
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_ou_public_id ON organization_unit (public_id);
CREATE INDEX idx_ou_parent ON organization_unit (parent_id);
CREATE INDEX idx_ou_level ON organization_unit (level);
CREATE INDEX idx_ou_active ON organization_unit (is_active) WHERE is_active = TRUE;

Business Rules: - Cây phân cấp: TINH → SO → BAN → PHONG - parent_id = NULL khi cấp TINH (gốc cây) - code bất biến sau khi tạo - Không xóa đơn vị nếu có đơn vị con, user, hoặc đang là chủ quản DE → dùng is_active = false - DE chỉ gán chủ quản ở cấp PHONG trở xuống — không gán cấp TINH hay SO

app_user¶

CREATE TABLE app_user (
    id                      BIGSERIAL PRIMARY KEY,
    public_id               UUID NOT NULL DEFAULT uuidv7(), -- ID public cho API/external integration
    email                   TEXT UNIQUE NOT NULL,       -- SSO mapping key
    full_name               TEXT NOT NULL,
    organization_unit_id    BIGINT NOT NULL REFERENCES organization_unit(id),
    status                  TEXT NOT NULL DEFAULT 'ACTIVE'
                                CHECK (status IN ('ACTIVE', 'INACTIVE', 'PENDING')),
    sso_linked              BOOLEAN NOT NULL DEFAULT FALSE,  -- Đã liên kết SSO chưa
    keycloak_subject_id     TEXT,                        -- Keycloak 'sub' claim (sau SSO link)
    last_login_at           TIMESTAMPTZ,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_user_public_id ON app_user (public_id);
CREATE INDEX idx_user_org ON app_user (organization_unit_id);
CREATE INDEX idx_user_status ON app_user (status);
CREATE UNIQUE INDEX idx_user_keycloak_unique ON app_user (keycloak_subject_id) WHERE keycloak_subject_id IS NOT NULL;

Business Rules: - Email là key mapping duy nhất với SSO identity - Pre-provisioning: Admin tạo user trước → user đăng nhập SSO lần đầu → hệ thống mapping bằng email - status = PENDING cho user chưa được gán vai trò (tạo tự động khi SSO login lần đầu mà chưa có user) - Admin không thể xóa/deactivate chính mình - Hệ thống không lưu mật khẩu — Keycloak quản lý

user_role¶

CREATE TABLE user_role (
    id          BIGSERIAL PRIMARY KEY,
    public_id   UUID NOT NULL DEFAULT uuidv7(), -- ID public cho API/external integration
    user_id     BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    role        TEXT NOT NULL
                    CHECK (role IN ('MANAGER', 'DATA_OWNER', 'APPROVER', 'STAFF', 'ADMIN')),
    assigned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    assigned_by BIGINT REFERENCES app_user(id),

    UNIQUE (user_id, role)  -- Mỗi user chỉ có 1 instance mỗi vai trò
);

CREATE UNIQUE INDEX idx_ur_public_id ON user_role (public_id);
CREATE INDEX idx_ur_user ON user_role (user_id);
CREATE INDEX idx_ur_role ON user_role (role);

Business Rules: - Một user có thể có nhiều vai trò (multi-role) - User chuyển đổi vai trò active qua sidebar (không cần đăng xuất) - Ít nhất 1 vai trò phải được gán - Thay đổi vai trò ghi audit log

delegation¶

Bảng quản lý Ủy quyền (Delegation) tạm thời các vai trò giữa các User. Bảng sử dụng UUID để chống lại Insecure Direct Object Reference (IDOR).

CREATE TABLE delegation (
    id              UUID PRIMARY KEY DEFAULT uuidv7(),
    public_id       UUID NOT NULL DEFAULT uuidv7(), -- ID public cho API/external integration
    delegator_id    BIGINT NOT NULL REFERENCES app_user(id),    -- Người ủy quyền
    delegatee_id    BIGINT NOT NULL REFERENCES app_user(id),    -- Người được ủy quyền
    role            TEXT NOT NULL
                        CHECK (role IN ('MANAGER', 'DATA_OWNER', 'APPROVER')),
    start_time      TIMESTAMPTZ NOT NULL,
    end_time        TIMESTAMPTZ NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT chk_time_range CHECK (end_time > start_time),
    -- Cần extension btree_gist để EXCLUDE hoạt động với BIGINT/TEXT + range
    CONSTRAINT ex_del_no_overlap EXCLUDE USING GIST (
        delegator_id WITH =,
        delegatee_id WITH =,
        role WITH =,
        tstzrange(start_time, end_time, '[)') WITH &&
    ) WHERE (is_active = TRUE)
);

CREATE UNIQUE INDEX idx_del_public_id ON delegation (public_id);
CREATE INDEX idx_del_delegator ON delegation (delegator_id);
CREATE INDEX idx_del_delegatee ON delegation (delegatee_id);
CREATE INDEX idx_del_time ON delegation (start_time, end_time);

Business Rules: - Delegator chỉ uỷ quyền được những Role mà họ đang sở hữu tại thời điểm tạo. - Cùng lúc không thể có 2 bản record uỷ quyền TRÙNG role và GIAO NHAU về mặt thời gian giữa cùng 1 cặp user. - UUIDv7 hoặc Random UUID đảm bảo link duyệt uỷ quyền (nếu có) gửi qua email không bị đoán.

Cập nhật lần cuối: 2026-04-11