T5 — Thiết kế cơ sở dữ liệu (ER Diagram) - congsinhv/fluxion GitHub Wiki

Thiết Kế Cơ Sở Dữ Liệu (ER Diagram)

Issue: #26 — Thiết kế cơ sở dữ liệu (ER Diagram) Tuần: 5 | 21/04 – 27/04/2026

Numbering chính thức: Mục 3.6 theo Master TOC

Revision 2026-04-19: Follow-up audit.

Bỏ column error_count trong batch_actions (errorCount COMPUTED ở GraphQL resolver, không store)

Clarify batch semantics: track DISPATCH outcome (action-trigger publish SNS), không track device execution callback

Bỏ timeout logic (không cần — dispatch synchronous)

Revision 2026-04-18: Bổ sung 4 bảng mới sau khi review wireframes T8b.

brands, tacs: Type Allocation Code management (wireframe configuration-tac)

batch_actions, batch_device_actions: Replace concept action_logs cũ — tracking batch-level summary + per-device status để generate CSV error report on-demand từ S3

message_templates: schema overhaul — thay locale bằng notification_type (FULLSCREEN/POPUP) + 6 cột lưu icons (3 slots × 2 fields) + is_active soft delete

Tracking issue: #55

3.6 Thiết Kế Cơ Sở Dữ Liệu

3.6.1 ER Diagram

erDiagram
    services ||--o{ policies : "service_type_id"
    services ||--o{ actions : "service_type_id"
    states ||--o{ policies : "state_id"
    states ||--o{ actions : "from_state_id"
    policies ||--o{ actions : "apply_policy_id"
    policies ||--o{ devices : "current_policy_id"
    policies ||--o{ milestones : "policy_id"

    devices ||--|| device_informations : "device_id"
    devices ||--|| device_tokens : "device_id"
    devices ||--o{ action_executions : "device_id"
    devices ||--o{ milestones : "device_id"
    actions ||--o{ devices : "assigned_action_id"
    actions ||--o{ milestones : "assigned_action_id"
    actions ||--o{ action_executions : "action_id"

    users ||--o{ chat_sessions : "user_id"
    chat_sessions ||--o{ chat_messages : "session_id"

    brands ||--o{ tacs : "brand_id"
    actions ||--o{ batch_actions : "action_id"
    batch_actions ||--o{ batch_device_actions : "batch_id"
    devices ||--o{ batch_device_actions : "device_id"

    services {
        smallint id PK
        varchar name UK
        bool is_enabled
        timestamptz created_at
    }

    states {
        smallint id PK
        varchar name UK
        timestamptz created_at
    }

    policies {
        smallint id PK
        varchar name
        smallint service_type_id FK
        smallint state_id FK
        varchar color
        timestamptz created_at
    }

    actions {
        uuid id PK
        varchar name
        smallint action_type_id
        smallint from_state_id FK
        smallint service_type_id FK
        smallint apply_policy_id FK
        jsonb configuration
        jsonb ext_fields
        timestamptz created_at
    }

    devices {
        uuid id PK
        smallint state_id FK
        smallint current_policy_id FK
        uuid assigned_action_id FK
        timestamptz created_at
        timestamptz updated_at
    }

    device_informations {
        uuid id PK
        uuid device_id FK
        varchar serial_number UK
        varchar udid UK
        varchar name
        varchar model
        varchar os_version
        float battery_level
        varchar wifi_mac
        bool is_supervised
        timestamptz last_checkin_at
        timestamptz created_at
        timestamptz updated_at
        jsonb ext_fields
    }

    device_tokens {
        uuid id PK
        uuid device_id FK
        bytea push_token
        varchar push_magic
        bytea unlock_token
        varchar topic
        timestamptz created_at
        timestamptz updated_at
    }

    action_executions {
        uuid id PK
        uuid device_id FK
        uuid action_id FK
        uuid command_uuid UK
        varchar status
        timestamptz created_at
        timestamptz updated_at
        jsonb ext_fields
    }

    milestones {
        uuid id PK
        uuid device_id FK
        uuid assigned_action_id FK
        smallint policy_id FK
        timestamptz created_at
        jsonb ext_fields
    }

    users {
        uuid id PK
        varchar email UK
        varchar name
        varchar role
        varchar cognito_sub UK
        bool is_active
        timestamptz created_at
        timestamptz updated_at
    }

    chat_sessions {
        uuid id PK
        uuid user_id FK
        timestamptz created_at
        timestamptz updated_at
    }

    chat_messages {
        uuid id PK
        uuid session_id FK
        varchar role
        text content
        jsonb tool_calls
        jsonb tool_result
        timestamptz created_at
    }

    message_templates {
        uuid id PK
        varchar name
        text content
        varchar notification_type
        bool is_active
        varchar notification_icon_path
        varchar notification_icon_name
        varchar header_icon_path
        varchar header_icon_name
        varchar additional_icon_path
        varchar additional_icon_name
        timestamptz created_at
        timestamptz updated_at
    }

    brands {
        serial id PK
        varchar name UK
        timestamptz created_at
        timestamptz updated_at
    }

    tacs {
        uuid id PK
        varchar tac_code UK
        varchar provisioning_type
        int brand_id FK
        varchar model
        varchar marketing_name
        timestamptz created_at
        timestamptz updated_at
    }

    batch_actions {
        uuid id PK
        uuid batch_id UK
        uuid action_id FK
        varchar created_by
        int total_devices
        varchar status
        timestamptz created_at
        timestamptz updated_at
    }

    batch_device_actions {
        uuid id PK
        uuid batch_id FK
        uuid device_id FK
        varchar status
        varchar error_code
        text error_message
        timestamptz started_at
        timestamptz finished_at
        timestamptz created_at
    }

3.6.2 Table Definitions

3.6.2.1 FSM Tables (services/states/policies/actions)

Đã định nghĩa chi tiết trong T5 — DeviceFSM mục 3.4 (DB-driven FSM).

Table	Rows	Mô tả
`services`	~3	Service types: Inventory, Supply Chain, Postpaid
`states`	6	FSM states: Idle → Registered → Enrolled → Active ⇄ Locked → Released
`policies`	~6	Mỗi policy gắn với 1 state + 1 service
`actions`	~12	Transitions implicit: `from_state_id` → `apply_policy_id`
`milestones`	Growing	Device history — mỗi state change tạo 1 record

3.6.2.2 devices (Fluxion business data)

CREATE TABLE devices (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    state_id            SMALLINT NOT NULL REFERENCES states(id) DEFAULT 1,
    current_policy_id   SMALLINT REFERENCES policies(id),
    assigned_action_id  UUID REFERENCES actions(id),  -- NULL = not busy
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Chỉ chứa Fluxion business fields: FSM state, policy, assigned action. Không chứa Apple MDM data.

3.6.2.3 device_informations (Apple MDM query data)

CREATE TABLE device_informations (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id       UUID NOT NULL UNIQUE REFERENCES devices(id) ON DELETE CASCADE,
    serial_number   VARCHAR(50) UNIQUE NOT NULL,
    udid            VARCHAR(50) UNIQUE NOT NULL,
    name            VARCHAR(200),
    model           VARCHAR(100),           -- iPhone 15 Pro, iPad Air M2
    os_version      VARCHAR(20),            -- 17.4.1, 18.0
    battery_level   REAL,                   -- 0.0 → 1.0
    wifi_mac        VARCHAR(20),
    is_supervised   BOOLEAN DEFAULT FALSE,
    last_checkin_at TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ext_fields      JSONB                   -- additional MDM query fields
);

Data source: Response từ DeviceInformation MDM command (server chủ động gửi command hỏi device).

3.6.2.4 device_tokens (Apple MDM protocol tokens)

CREATE TABLE device_tokens (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id       UUID NOT NULL UNIQUE REFERENCES devices(id) ON DELETE CASCADE,
    push_token      BYTEA NOT NULL,         -- APNS Token (binary)
    push_magic      VARCHAR(200) NOT NULL,  -- PushMagic string for APNS payload
    unlock_token    BYTEA,                  -- UnlockToken for ClearPasscode (optional)
    topic           VARCHAR(200) NOT NULL,  -- Topic device subscribes to
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Data source: Device TokenUpdate check-in message. Updated khi device enrollment hoặc iOS rotate push token.

Event: DEVICE_TOKEN_UPDATE → checkin-handler UPSERT.

3.6.2.5 action_executions

CREATE TABLE action_executions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id       UUID NOT NULL REFERENCES devices(id),
    action_id       UUID NOT NULL REFERENCES actions(id),
    command_uuid    UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),  -- match MDM CommandUUID
    status          VARCHAR(20) NOT NULL DEFAULT 'ACTION_PENDING',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ext_fields      JSONB       -- { "failed_reason": "...", "error_chain": [...] }
);
-- status values: ACTION_PENDING, ACTION_COMPLETED, ACTION_FAILED

command_uuid: unique ID gửi tới device trong MDM command. Device trả result kèm CommandUUID → OEM match và publish event → BE checkin-handler tìm action_executions by command_uuid.

3.6.2.6 users

CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email           VARCHAR(255) UNIQUE NOT NULL,
    name            VARCHAR(200) NOT NULL,
    role            VARCHAR(20) NOT NULL DEFAULT 'operator',  -- admin, operator
    cognito_sub     VARCHAR(100) UNIQUE NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

3.6.2.7 chat_sessions & chat_messages

CREATE TABLE chat_sessions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE chat_messages (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id      UUID NOT NULL REFERENCES chat_sessions(id) ON DELETE CASCADE,
    role            VARCHAR(20) NOT NULL,    -- user, assistant, tool
    content         TEXT,
    tool_calls      JSONB,                   -- [{id, name, arguments}]
    tool_result     JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

3.6.2.8 message_templates

CREATE TABLE message_templates (
    id                       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name                     VARCHAR(255) NOT NULL,
    content                  TEXT NOT NULL,                 -- plain text, no rendering
    notification_type        VARCHAR(20) NOT NULL,          -- FULLSCREEN | POPUP
    is_active                BOOLEAN NOT NULL DEFAULT TRUE, -- soft delete
    notification_icon_path   VARCHAR(512) NOT NULL,         -- S3 key / CDN URL
    notification_icon_name   VARCHAR(255) NOT NULL,
    header_icon_path         VARCHAR(512) NOT NULL,
    header_icon_name         VARCHAR(255) NOT NULL,
    additional_icon_path     VARCHAR(512) NOT NULL,
    additional_icon_name     VARCHAR(255) NOT NULL,
    created_at               TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at               TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Resolver flow khi action có messageTemplateId: resolver load content → gán vào payload.messageContent (plain string, as-is) → enqueue. Worker không rendering.

3.6.2.9 brands + tacs (Type Allocation Code)

CREATE TABLE brands (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL UNIQUE,   -- "iPhone", "Galaxy", ...
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE tacs (
    id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tac_code           VARCHAR(20) NOT NULL UNIQUE,     -- 8-digit code
    provisioning_type  VARCHAR(50) NOT NULL,            -- "Apple", "Android"
    brand_id           INT REFERENCES brands(id) ON DELETE SET NULL,
    model              VARCHAR(100),                    -- "A2650"
    marketing_name     VARCHAR(255),                    -- "iPhone 14 Pro"
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Seed
INSERT INTO brands (name) VALUES ('iPhone');
INSERT INTO tacs (tac_code, provisioning_type, brand_id, model, marketing_name)
  VALUES ('35387910', 'Apple', 1, 'A2650', 'iPhone 14 Pro');

3.6.2.10 batch_actions + batch_device_actions

Batch-level tracking cho bulk assign action. 1 row trong batch_actions / batch; N rows trong batch_device_actions / batch (chỉ device có error khi dispatch). FE query batch_actions → summary; mutation generateActionLogErrorReport(batchId) tạo CSV từ batch_device_actions WHERE error_code IS NOT NULL → upload S3 → pre-signed URL (TTL 5 phút). errorCount ở GraphQL được COMPUTED dynamic từ COUNT, không store column trong batch_actions.

CREATE TABLE batch_actions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    batch_id        UUID NOT NULL UNIQUE,
    action_id       UUID NOT NULL REFERENCES actions(id),
    created_by      VARCHAR(255) NOT NULL,           -- userId
    total_devices   INT NOT NULL,
    status          VARCHAR(20) NOT NULL,            -- IN_PROGRESS | COMPLETED | FAILED
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE batch_device_actions (
    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    batch_id       UUID NOT NULL REFERENCES batch_actions(batch_id) ON DELETE CASCADE,
    device_id      UUID NOT NULL REFERENCES devices(id),
    status         VARCHAR(20) NOT NULL,             -- PENDING | SUCCESS | FAILED (dispatch outcome)
    error_code     VARCHAR(100),                     -- NULL nếu dispatch thành công
    error_message  TEXT,
    started_at     TIMESTAMPTZ,
    finished_at    TIMESTAMPTZ,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Status semantics (dispatch-only, không track device callback):

action-resolver INSERT batch_actions.status = IN_PROGRESS + N rows batch_device_actions.status = PENDING
action-trigger worker pick up từng device SQS message:
- Valid (publish SNS → apple-process-action-sqs thành công) → batch_device_actions.status = SUCCESS
- Invalid (device offline, không có push_token, invalid transition…) → batch_device_actions.status = FAILED + error_code + error_message
Khi tất cả rows done:
- 0 errors → batch_actions.status = COMPLETED
- All errors → batch_actions.status = FAILED
- Mixed → batch_actions.status = COMPLETED (không có PARTIAL_ERROR)
Không cần timeout — dispatch là synchronous, không phụ thuộc device callback. Device execution status track riêng qua action_executions + milestones.

3.6.3 Indexes

-- devices
CREATE INDEX idx_devices_state_id ON devices(state_id);
CREATE INDEX idx_devices_current_policy_id ON devices(current_policy_id);
CREATE INDEX idx_devices_assigned ON devices(assigned_action_id) WHERE assigned_action_id IS NOT NULL;

-- device_informations
CREATE UNIQUE INDEX idx_di_device_id ON device_informations(device_id);

-- device_tokens
CREATE UNIQUE INDEX idx_dt_device_id ON device_tokens(device_id);

-- action_executions
CREATE INDEX idx_ae_device_id ON action_executions(device_id);
CREATE UNIQUE INDEX idx_ae_command_uuid ON action_executions(command_uuid);
CREATE INDEX idx_ae_active ON action_executions(status) WHERE status NOT IN ('ACTION_COMPLETED', 'ACTION_FAILED');

-- milestones
CREATE INDEX idx_milestones_device_id ON milestones(device_id);
CREATE INDEX idx_milestones_created_at ON milestones(created_at);

-- chat
CREATE INDEX idx_chat_sessions_user_id ON chat_sessions(user_id);
CREATE INDEX idx_chat_messages_session ON chat_messages(session_id, created_at);

-- message_templates
CREATE INDEX idx_message_templates_notification_type ON message_templates(notification_type);
CREATE INDEX idx_message_templates_is_active ON message_templates(is_active);

-- tacs
CREATE INDEX idx_tacs_tac_code ON tacs(tac_code);
CREATE INDEX idx_tacs_brand_id ON tacs(brand_id);

-- batch_actions
CREATE INDEX idx_batch_actions_status ON batch_actions(status);
CREATE INDEX idx_batch_actions_created_at ON batch_actions(created_at DESC);
CREATE INDEX idx_batch_device_actions_batch_id ON batch_device_actions(batch_id);
CREATE INDEX idx_batch_device_actions_status ON batch_device_actions(status);

3.6.4 Table Summary

Table	Type	Separation	Growth
`services`	Config	—	Static
`states`	Config	—	Static
`policies`	Config	—	Static
`actions`	Config	—	Rare
`devices`	Core	Fluxion business	Slow
`device_informations`	Core	Apple MDM query data	Slow
`device_tokens`	Core	Apple MDM protocol tokens	Slow
`action_executions`	Transactional	—	Medium
`milestones`	History	—	Medium
`users`	Core	—	Rare
`chat_sessions`	Transactional	—	Slow
`chat_messages`	Transactional	—	Medium
`message_templates`	Config	—	Rare
`brands`	Config	—	Static
`tacs`	Config	—	Rare
`batch_actions`	Transactional	—	Medium
`batch_device_actions`	Transactional	—	Fast

3.6.4.1 Device Data Separation

devices              → Fluxion business: state, policy, assigned_action
device_informations  → Apple MDM: serial, udid, model, os_version, battery
device_tokens        → Apple MDM protocol: push_token, push_magic, unlock_token

Lý do tách:

Separation of concerns: Fluxion data vs Apple MDM data
Update frequency: tokens update thường xuyên, device info update khi query, business data update khi action
Security: device_tokens chứa sensitive data (unlock_token)
OEM independence: Nếu đổi 3rd party (iOS → Android), chỉ thay device_informations + device_tokens, không đụng devices

Kết Luận

Schema cơ sở dữ liệu Fluxion MDM gồm 17 bảng phân chia thành 4 nhóm chức năng với ranh giới rõ ràng. Nhóm FSM Config (services, states, policies, actions) mã hóa toàn bộ logic chuyển trạng thái thiết bị — đây là trái tim của DB-driven FSM, cho phép thay đổi transitions qua SQL mà không cần deploy lại code. Nhóm Device Data (devices, device_informations, device_tokens, action_executions, milestones) áp dụng nguyên tắc tách biệt Fluxion business data khỏi Apple MDM protocol data, tạo điều kiện cho OEM independence. Nhóm User & Chat (users, chat_sessions, chat_messages) hỗ trợ RBAC và conversation memory cho NLP chatbot.

Các quyết định chuẩn hóa (normalization) đáng chú ý: device_informations và device_tokens là bảng riêng biệt (1-1 với devices) thay vì columns trong devices — phân tách tần suất cập nhật và nhóm quyền truy cập. milestones là append-only history table, không bao giờ UPDATE — đảm bảo audit trail bất biến. action_executions.command_uuid có UNIQUE constraint để match với Apple MDM CommandUUID, cho phép idempotent processing của duplicate SQS messages.

Bộ indexes được thiết kế theo access patterns thực tế: partial index trên action_executions(status) chỉ index các records đang active (loại bỏ completed/failed), composite index trên chat_messages(session_id, created_at) tối ưu sliding window query. Schema đạt chuẩn 3NF cho tất cả bảng, sử dụng JSONB (ext_fields, configuration) cho dữ liệu semi-structured linh hoạt mà không phá vỡ normalization của core fields.

Tài Liệu Tham Khảo

[1] Elmasri, R. & Navathe, S. Fundamentals of Database Systems, 7th ed. Pearson, 2015.

[2] Date, C. J. An Introduction to Database Systems, 8th ed. Addison-Wesley, 2003.

[3] PostgreSQL Documentation. https://www.postgresql.org/docs/16/