lcbp3/specs/02-architecture/02-03-data-model.md

Data Model Architecture

---

title: 'Data Model Architecture' version: 1.5.0 status: first-draft owner: Nattanin Peancharoen last_updated: 2025-11-30 related:

• specs/01-requirements/02-architecture.md
• specs/01-requirements/03-functional-requirements.md
• docs/4_Data_Dictionary_V1_4_5.md
• docs/8_lcbp3_v1_4_5.sql

---

📋 Overview

เอกสารนี้อธิบายสถาปัตยกรรมของ Data Model สำหรับระบบ LCBP3-DMS โดยครอบคลุมโครงสร้างฐานข้อมูล, ความสัมพันธ์ระหว่างตาราง, และหลักการออกแบบที่สำคัญ

🎯 Design Principles

1. Separation of Concerns

• Master-Revision Pattern: แยกข้อมูลที่ไม่เปลี่ยนแปลง (Master) จากข้อมูลที่มีการแก้ไข (Revisions)
  • correspondences (Master) ↔ correspondence_revisions (Revisions)
  • rfas (Master) ↔ rfa_revisions (Revisions)
  • shop_drawings (Master) ↔ shop_drawing_revisions (Revisions)

2. Data Integrity

• Foreign Key Constraints: ใช้ FK ทุกความสัมพันธ์เพื่อรักษาความสมบูรณ์ของข้อมูล
• Soft Delete: ใช้ deleted_at แทนการลบข้อมูลจริง เพื่อรักษาประวัติ
• Optimistic Locking: ใช้ version column ใน document_number_counters ป้องกัน Race Condition

3. Flexibility & Extensibility

• JSON Details Field: เก็บข้อมูลเฉพาะประเภทใน correspondence_revisions.details
• Virtual Columns: สร้าง Index จาก JSON fields สำหรับ Performance
• Master Data Tables: แยกข้อมูล Master (Types, Status, Codes) เพื่อความยืดหยุ่น

4. Security & Audit

• RBAC (Role-Based Access Control): ระบบสิทธิ์แบบ Hierarchical Scope
• Audit Trail: บันทึกผู้สร้าง/แก้ไข และเวลาในทุกตาราง
• Two-Phase File Upload: ป้องกันไฟล์ขยะด้วย Temporary Storage

🗂️ Database Schema Overview

Entity Relationship Diagram

erDiagram
    %% Core Entities
    organizations ||--o{ users : "employs"
    projects ||--o{ contracts : "contains"
    projects ||--o{ correspondences : "manages"

    %% RBAC
    users ||--o{ user_assignments : "has"
    roles ||--o{ user_assignments : "assigned_to"
    roles ||--o{ role_permissions : "has"
    permissions ||--o{ role_permissions : "granted_by"

    %% Correspondences
    correspondences ||--o{ correspondence_revisions : "has_revisions"
    correspondence_types ||--o{ correspondences : "categorizes"
    correspondence_status ||--o{ correspondence_revisions : "defines_state"
    disciplines ||--o{ correspondences : "classifies"

    %% RFAs
    rfas ||--o{ rfa_revisions : "has_revisions"
    rfa_types ||--o{ rfas : "categorizes"
    rfa_status_codes ||--o{ rfa_revisions : "defines_state"
    rfa_approve_codes ||--o{ rfa_revisions : "defines_result"
    disciplines ||--o{ rfas : "classifies"

    %% Drawings
    shop_drawings ||--o{ shop_drawing_revisions : "has_revisions"
    shop_drawing_main_categories ||--o{ shop_drawings : "categorizes"
    shop_drawing_sub_categories ||--o{ shop_drawings : "sub_categorizes"

    %% Attachments
    attachments ||--o{ correspondence_attachments : "attached_to"
    correspondences ||--o{ correspondence_attachments : "has"

📊 Data Model Categories

1. 🏢 Core & Master Data

1.1 Organizations & Projects

Tables:

• organization_roles - บทบาทขององค์กร (OWNER, DESIGNER, CONSULTANT, CONTRACTOR)
• organizations - องค์กรทั้งหมดในระบบ
• projects - โครงการ
• contracts - สัญญาภายใต้โครงการ
• project_organizations - M:N ระหว่าง Projects และ Organizations
• contract_organizations - M:N ระหว่าง Contracts และ Organizations พร้อม Role

Key Relationships:

projects (1) ──→ (N) contracts
projects (N) ←→ (N) organizations [via project_organizations]
contracts (N) ←→ (N) organizations [via contract_organizations]

Business Rules:

• Organization code ต้องไม่ซ้ำกันในระบบ
• Contract ต้องผูกกับ Project เสมอ (ON DELETE CASCADE)
• Soft delete ใช้ is_active flag

---

2. 👥 Users & RBAC

2.1 User Management

Tables:

• users - ผู้ใช้งานระบบ
• roles - บทบาทพร้อม Scope (Global, Organization, Project, Contract)
• permissions - สิทธิ์การใช้งาน (49 permissions)
• role_permissions - M:N mapping
• user_assignments - การมอบหมายบทบาทพร้อม Scope Context

Scope Hierarchy:

Global (ทั้งระบบ)
  ↓
Organization (ระดับองค์กร)
  ↓
Project (ระดับโครงการ)
  ↓
Contract (ระดับสัญญา)

Key Features:

• Hierarchical Scope: User สามารถมีหลาย Role ในหลาย Scope
• Scope Inheritance: สิทธิ์ระดับบนครอบคลุมระดับล่าง
• Account Security: Failed login tracking, Account locking, Password hashing (bcrypt)

Example User Assignment:

-- User A เป็น Editor ในองค์กร TEAM
INSERT INTO user_assignments (user_id, role_id, organization_id)
VALUES (1, 4, 3);

-- User B เป็น Project Manager ในโครงการ LCBP3
INSERT INTO user_assignments (user_id, role_id, project_id)
VALUES (2, 6, 1);

---

3. ✉️ Correspondences (เอกสารโต้ตอบ)

3.1 Master-Revision Pattern

Master Table: correspondences

เก็บข้อมูลที่ไม่เปลี่ยนแปลง:

• correspondence_number - เลขที่เอกสาร (Unique per Project)
• correspondence_type_id - ประเภทเอกสาร (RFA, RFI, TRANSMITTAL, etc.)
• discipline_id - สาขางาน (GEN, STR, ARC, etc.) [NEW v1.4.5]
• project_id, originator_id - โครงการและองค์กรผู้ส่ง

Revision Table: correspondence_revisions

เก็บข้อมูลที่เปลี่ยนแปลงได้:

• revision_number - หมายเลข Revision (0, 1, 2...)
• is_current - Flag สำหรับ Revision ปัจจุบัน (UNIQUE constraint)
• title, description - เนื้อหาเอกสาร
• correspondence_status_id - สถานะ (DRAFT, SUBOWN, REPCSC, etc.)
• details - JSON field สำหรับข้อมูลเฉพาะประเภท
• Virtual Columns: v_ref_project_id, v_ref_type, v_doc_subtype (Indexed)

Supporting Tables:

• correspondence_types - Master ประเภทเอกสาร (10 types)
• correspondence_status - Master สถานะ (23 status codes)
• correspondence_sub_types - ประเภทย่อยสำหรับ Document Numbering [NEW v1.4.5]
• disciplines - สาขางาน (GEN, STR, ARC, etc.) [NEW v1.4.5]
• correspondence_recipients - M:N ผู้รับ (TO/CC)
• correspondence_tags - M:N Tags
• correspondence_references - M:N Cross-references

Example Query - Get Current Revision:

SELECT c.correspondence_number, cr.title, cr.revision_label, cs.status_name
FROM correspondences c
JOIN correspondence_revisions cr ON c.id = cr.correspondence_id
JOIN correspondence_status cs ON cr.correspondence_status_id = cs.id
WHERE cr.is_current = TRUE
  AND c.deleted_at IS NULL;

---

4. 📐 RFAs (Request for Approval)

4.1 RFA Structure

Master Table: rfas

• rfa_type_id - ประเภท RFA (DWG, DOC, MAT, SPC, etc.)
• discipline_id - สาขางาน [NEW v1.4.5]

Revision Table: rfa_revisions

• correspondence_id - Link กับ Correspondence (RFA เป็น Correspondence ประเภทหนึ่ง)
• rfa_status_code_id - สถานะ (DFT, FAP, FRE, FCO, ASB, OBS, CC)
• rfa_approve_code_id - ผลการอนุมัติ (1A, 1C, 1N, 1R, 3C, 3R, 4X, 5N)
• approved_date - วันที่อนุมัติ

Supporting Tables:

• rfa_types - 11 ประเภท (Shop Drawing, Document, Material, etc.)
• rfa_status_codes - 7 สถานะ
• rfa_approve_codes - 8 รหัสผลการอนุมัติ
• rfa_items - M:N เชื่อม RFA (ประเภท DWG) กับ Shop Drawing Revisions

RFA Workflow States:

DFT (Draft)
  ↓
FAP (For Approve) / FRE (For Review)
  ↓
[Approval Process]
  ↓
FCO (For Construction) / ASB (As-Built) / 3R (Revise) / 4X (Reject)

---

5. 📐 Drawings (แบบก่อสร้าง)

5.1 Contract Drawings (แบบคู่สัญญา)

Tables:

• contract_drawing_volumes - เล่มแบบ
• contract_drawing_cats - หมวดหมู่หลัก
• contract_drawing_sub_cats - หมวดหมู่ย่อย
• contract_drawing_subcat_cat_maps - M:N Mapping
• contract_drawings - แบบคู่สัญญา

Hierarchy:

Volume (เล่ม)
  └─ Category (หมวดหมู่หลัก)
      └─ Sub-Category (หมวดหมู่ย่อย)
          └─ Drawing (แบบ)

5.2 Shop Drawings (แบบก่อสร้าง)

Tables:

• shop_drawing_main_categories - หมวดหมู่หลัก (ARCH, STR, MEP, etc.)
• shop_drawing_sub_categories - หมวดหมู่ย่อย
• shop_drawings - Master แบบก่อสร้าง
• shop_drawing_revisions - Revisions
• shop_drawing_revision_contract_refs - M:N อ้างอิงแบบคู่สัญญา

Revision Tracking:

-- Get latest revision of a shop drawing
SELECT sd.drawing_number, sdr.revision_label, sdr.revision_date
FROM shop_drawings sd
JOIN shop_drawing_revisions sdr ON sd.id = sdr.shop_drawing_id
WHERE sd.drawing_number = 'SD-STR-001'
ORDER BY sdr.revision_number DESC
LIMIT 1;

---

6. 🔄 Circulations & Transmittals

6.1 Circulations (ใบเวียนภายใน)

Tables:

• circulation_status_codes - สถานะ (OPEN, IN_REVIEW, COMPLETED, CANCELLED)
• circulations - ใบเวียน (1:1 กับ Correspondence)

Workflow:

OPEN → IN_REVIEW → COMPLETED
         ↓
    CANCELLED

6.2 Transmittals (เอกสารนำส่ง)

Tables:

• transmittals - ข้อมูล Transmittal (1:1 กับ Correspondence)
• transmittal_items - M:N รายการเอกสารที่นำส่ง

Purpose Types:

• FOR_APPROVAL
• FOR_INFORMATION
• FOR_REVIEW
• OTHER

---

7. 📎 File Management

7.1 Two-Phase Storage Pattern

Table: attachments

Phase 1: Temporary Upload

INSERT INTO attachments (
  original_filename, stored_filename, file_path,
  mime_type, file_size, is_temporary, temp_id,
  uploaded_by_user_id, expires_at, checksum
)
VALUES (
  'document.pdf', 'uuid-document.pdf', '/temp/uuid-document.pdf',
  'application/pdf', 1024000, TRUE, 'temp-uuid-123',
  1, NOW() + INTERVAL 1 HOUR, 'sha256-hash'
);

Phase 2: Commit to Permanent

-- Update attachment to permanent
UPDATE attachments
SET is_temporary = FALSE, expires_at = NULL
WHERE temp_id = 'temp-uuid-123';

-- Link to correspondence
INSERT INTO correspondence_attachments (correspondence_id, attachment_id, is_main_document)
VALUES (1, 123, TRUE);

Junction Tables:

• correspondence_attachments - M:N
• circulation_attachments - M:N
• shop_drawing_revision_attachments - M:N (with file_type)
• contract_drawing_attachments - M:N (with file_type)

Security Features:

• Checksum validation (SHA-256)
• Automatic cleanup of expired temporary files
• File type validation via mime_type

---

8. 🔢 Document Numbering

8.1 Format & Counter System

Tables:

• document_number_formats - Template รูปแบบเลขที่เอกสาร
• document_number_counters - Running Number Counter with Optimistic Locking

Format Template Example:

{ORG_CODE}-{TYPE_CODE}-{DISCIPLINE_CODE}-{YEAR}-{SEQ:4}
→ TEAM-RFA-STR-2025-0001

Counter Table Structure:

CREATE TABLE document_number_counters (
  project_id INT,
  originator_organization_id INT,
  correspondence_type_id INT,
  discipline_id INT DEFAULT 0,  -- NEW v1.4.5
  current_year INT,
  version INT DEFAULT 0,        -- Optimistic Lock
  last_number INT DEFAULT 0,
  PRIMARY KEY (project_id, originator_organization_id, correspondence_type_id, discipline_id, current_year)
);

Optimistic Locking Pattern:

-- Get next number with version check
UPDATE document_number_counters
SET last_number = last_number + 1,
    version = version + 1
WHERE project_id = 1
  AND originator_organization_id = 3
  AND correspondence_type_id = 1
  AND discipline_id = 2
  AND current_year = 2025
  AND version = @current_version;  -- Optimistic lock check

-- If affected rows = 0, retry (conflict detected)

---

🔐 Security & Audit

1. Audit Logging

Table: audit_logs

บันทึกการเปลี่ยนแปลงสำคัญ:

• User actions (CREATE, UPDATE, DELETE)
• Entity type และ Entity ID
• Old/New values (JSON)
• IP Address, User Agent

2. User Preferences

Table: user_preferences

เก็บการตั้งค่าส่วนตัว:

• Language preference
• Notification settings
• UI preferences (JSON)

3. JSON Schema Validation

Table: json_schemas

เก็บ Schema สำหรับ Validate JSON fields:

• correspondence_revisions.details
• user_preferences.preferences

---

📈 Performance Optimization

1. Indexing Strategy

Primary Indexes:

• Primary Keys (AUTO_INCREMENT)
• Foreign Keys (automatic in InnoDB)
• Unique Constraints (business keys)

Secondary Indexes:

-- Correspondence search
CREATE INDEX idx_corr_type_status ON correspondence_revisions(correspondence_type_id, correspondence_status_id);
CREATE INDEX idx_corr_date ON correspondence_revisions(document_date);

-- Virtual columns for JSON
CREATE INDEX idx_v_ref_project ON correspondence_revisions(v_ref_project_id);
CREATE INDEX idx_v_doc_subtype ON correspondence_revisions(v_doc_subtype);

-- User lookup
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_org ON users(primary_organization_id, is_active);

2. Virtual Columns

ใช้ Virtual Columns สำหรับ Index JSON fields:

ALTER TABLE correspondence_revisions
ADD COLUMN v_ref_project_id INT GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(details, '$.ref_project_id'))) VIRTUAL,
ADD INDEX idx_v_ref_project(v_ref_project_id);

3. Partitioning (Future)

พิจารณา Partition ตาราง audit_logs ตามปี:

ALTER TABLE audit_logs
PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

---

🔄 Migration Strategy

1. TypeORM Migrations

ใช้ TypeORM Migration สำหรับ Schema Changes:

// File: backend/src/migrations/1234567890-AddDisciplineToCorrespondences.ts
import { MigrationInterface, QueryRunner } from 'typeorm';

export class AddDisciplineToCorrespondences1234567890
  implements MigrationInterface
{
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      ALTER TABLE correspondences
      ADD COLUMN discipline_id INT NULL COMMENT 'สาขางาน (ถ้ามี)'
      AFTER correspondence_type_id
    `);

    await queryRunner.query(`
      ALTER TABLE correspondences
      ADD CONSTRAINT fk_corr_discipline
      FOREIGN KEY (discipline_id) REFERENCES disciplines(id)
      ON DELETE SET NULL
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `ALTER TABLE correspondences DROP FOREIGN KEY fk_corr_discipline`
    );
    await queryRunner.query(
      `ALTER TABLE correspondences DROP COLUMN discipline_id`
    );
  }
}

2. Data Seeding

ใช้ Seed Scripts สำหรับ Master Data:

// File: backend/src/seeds/1-organizations.seed.ts
export class OrganizationSeeder implements Seeder {
  public async run(dataSource: DataSource): Promise<void> {
    const repository = dataSource.getRepository(Organization);
    await repository.save([
      {
        organization_code: 'กทท.',
        organization_name: 'Port Authority of Thailand',
      },
      {
        organization_code: 'TEAM',
        organization_name: 'TEAM Consulting Engineering',
      },
      // ...
    ]);
  }
}

---

📚 Best Practices

1. Naming Conventions

• Tables: snake_case, plural (e.g., correspondences, users)
• Columns: snake_case (e.g., correspondence_number, created_at)
• Foreign Keys: {referenced_table_singular}_id (e.g., project_id, user_id)
• Junction Tables: {table1}_{table2} (e.g., correspondence_tags)

2. Timestamp Columns

ทุกตารางควรมี:

• created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
• updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

3. Soft Delete

ใช้ deleted_at DATETIME NULL แทนการลบจริง:

-- Soft delete
UPDATE correspondences SET deleted_at = NOW() WHERE id = 1;

-- Query active records
SELECT * FROM correspondences WHERE deleted_at IS NULL;

4. JSON Field Guidelines

• ใช้สำหรับข้อมูลที่ไม่ต้อง Query บ่อย
• สร้าง Virtual Columns สำหรับ fields ที่ต้อง Index
• Validate ด้วย JSON Schema
• Document structure ใน Data Dictionary

---

🔗 Related Documentation

• System Architecture - สถาปัตยกรรมระบบโดยรวม
• API Design - การออกแบบ API
• Data Dictionary v1.4.5 - รายละเอียดตารางทั้งหมด
• SQL Schema v1.4.5 - SQL Script สำหรับสร้างฐานข้อมูล
• Functional Requirements - ความต้องการด้านฟังก์ชัน

---

📝 Version History

Version	Date	Author	Changes
1.5.0	2025-11-30	Nattanin Peancharoen	Initial data model documentation
1.4.5	2025-11-29	System	Added disciplines and correspondence_sub_types