# 資料庫設計（Database Schema Design）

## 核心資料模型概覽

```
組織層
├── Organization (組織)
├── Department (部門)
└── User (使用者)

資產層
├── Asset (資產)
├── AssetType (資產類型)
└── AssetRelationship (資產關係)

文件層
├── Document (文件)
├── DocumentCategory (文件分類)
├── DocumentVersion (文件版本)
└── DocumentAcknowledgment (文件簽署)

控制措施層
├── Control (控制措施)
├── ControlCategory (控制措施分類)
└── ControlImplementation (控制措施實作)

風險層
├── Risk (風險)
├── RiskAssessment (風險評估)
└── RiskTreatment (風險處理)

稽核層
├── Audit (稽核)
├── AuditChecklist (稽核檢查清單)
├── AuditFinding (稽核發現)
└── CorrectiveAction (矯正措施)

事件層
├── Incident (事件)
└── IncidentResponse (事件處理)

訓練層
├── Training (訓練)
└── TrainingRecord (訓練記錄)
```

---

## 詳細表格設計

### 1. 組織與使用者

#### **organizations (組織)**
```sql
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(200) NOT NULL,
    code VARCHAR(50) UNIQUE,
    address TEXT,
    phone VARCHAR(50),
    email VARCHAR(100),
    website VARCHAR(200),
    
    -- 設定
    settings JSONB DEFAULT '{}',
    
    -- 時間戳記
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_organizations_code ON organizations(code);
```

#### **users (使用者)**
```sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    
    -- 帳號資訊
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    
    -- 個人資訊
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    employee_id VARCHAR(50),
    department_id UUID REFERENCES departments(id),
    job_title VARCHAR(100),
    phone VARCHAR(50),
    avatar_url VARCHAR(500),
    
    -- 權限
    role VARCHAR(50) NOT NULL, -- SuperAdmin, OrgAdmin, SecurityOfficer, Auditor, Employee, ReadOnly
    is_active BOOLEAN DEFAULT TRUE,
    is_superuser BOOLEAN DEFAULT FALSE,
    
    -- MFA
    mfa_enabled BOOLEAN DEFAULT FALSE,
    mfa_secret VARCHAR(100),
    
    -- 登入資訊
    last_login TIMESTAMP,
    login_attempts INTEGER DEFAULT 0,
    locked_until TIMESTAMP,
    
    -- 時間戳記
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP -- 軟刪除
);

CREATE INDEX idx_users_org ON users(organization_id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_dept ON users(department_id);
```

#### **departments (部門)**
```sql
CREATE TABLE departments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50),
    parent_id UUID REFERENCES departments(id), -- 支援階層
    manager_id UUID REFERENCES users(id),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_departments_org ON departments(organization_id);
CREATE INDEX idx_departments_parent ON departments(parent_id);
```

---

### 2. 資產管理

#### **assets (資產)**
```sql
CREATE TABLE assets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    
    -- 基本資訊
    asset_number VARCHAR(50) UNIQUE NOT NULL, -- AST-001
    name VARCHAR(200) NOT NULL,
    description TEXT,
    asset_type VARCHAR(50) NOT NULL, -- hardware, software, data, people, facility
    asset_subtype VARCHAR(50), -- server, workstation, database, etc
    
    -- 責任歸屬
    owner_id UUID REFERENCES users(id),
    custodian_id UUID REFERENCES users(id),
    department_id UUID REFERENCES departments(id),
    
    -- 位置
    location VARCHAR(200),
    network_segment VARCHAR(100),
    
    -- CIA 等級
    confidentiality VARCHAR(20) DEFAULT 'medium', -- low, medium, high
    integrity VARCHAR(20) DEFAULT 'medium',
    availability VARCHAR(20) DEFAULT 'medium',
    
    -- 狀態
    status VARCHAR(50) DEFAULT 'active', -- active, inactive, maintenance, decommissioned
    
    -- 技術細節（JSON 彈性儲存）
    technical_details JSONB DEFAULT '{}',
    -- 例如：{"os": "Ubuntu 22.04", "ip": "192.168.1.10", "cpu": "8 cores"}
    
    -- 時間戳記
    acquisition_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE INDEX idx_assets_org ON assets(organization_id);
CREATE INDEX idx_assets_number ON assets(asset_number);
CREATE INDEX idx_assets_type ON assets(asset_type);
CREATE INDEX idx_assets_owner ON assets(owner_id);
CREATE INDEX idx_assets_dept ON assets(department_id);
```

#### **asset_relationships (資產關係)**
```sql
CREATE TABLE asset_relationships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    from_asset_id UUID REFERENCES assets(id) ON DELETE CASCADE,
    to_asset_id UUID REFERENCES assets(id) ON DELETE CASCADE,
    
    relationship_type VARCHAR(50) NOT NULL, -- depends_on, connected_to, hosts, uses
    description TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(from_asset_id, to_asset_id, relationship_type)
);

CREATE INDEX idx_asset_rel_from ON asset_relationships(from_asset_id);
CREATE INDEX idx_asset_rel_to ON asset_relationships(to_asset_id);
```

---

### 3. 文件管理

#### **documents (文件)**
```sql
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    
    -- 基本資訊
    document_number VARCHAR(50) UNIQUE NOT NULL, -- DOC-POL-001
    title VARCHAR(300) NOT NULL,
    category VARCHAR(50) NOT NULL, -- policy, procedure, instruction, form
    
    -- 內容
    content TEXT, -- 富文本內容
    template_variables JSONB DEFAULT '{}', -- 範本變數
    
    -- 版本資訊
    version VARCHAR(20) DEFAULT '1.0',
    
    -- 狀態
    status VARCHAR(50) DEFAULT 'draft', -- draft, review, approved, published, archived
    
    -- 生命週期
    created_by UUID REFERENCES users(id),
    reviewed_by UUID REFERENCES users(id),
    approved_by UUID REFERENCES users(id),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reviewed_at TIMESTAMP,
    approved_at TIMESTAMP,
    published_at TIMESTAMP,
    
    -- 審查週期
    review_cycle_months INTEGER DEFAULT 12,
    next_review_date DATE,
    
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE INDEX idx_documents_org ON documents(organization_id);
CREATE INDEX idx_documents_number ON documents(document_number);
CREATE INDEX idx_documents_status ON documents(status);
CREATE INDEX idx_documents_category ON documents(category);
```

#### **document_versions (文件版本)**
```sql
CREATE TABLE document_versions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    
    version VARCHAR(20) NOT NULL,
    content TEXT,
    change_summary TEXT,
    
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_doc_versions_doc ON document_versions(document_id);
```

#### **document_acknowledgments (文件簽署)**
```sql
CREATE TABLE document_acknowledgments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    
    acknowledged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address INET,
    signature_data TEXT, -- Base64 電子簽章
    
    UNIQUE(document_id, user_id)
);

CREATE INDEX idx_doc_ack_doc ON document_acknowledgments(document_id);
CREATE INDEX idx_doc_ack_user ON document_acknowledgments(user_id);
```

#### **document_control_mappings (文件-控制措施關聯)**
```sql
CREATE TABLE document_control_mappings (
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    control_id UUID REFERENCES controls(id) ON DELETE CASCADE,
    
    PRIMARY KEY (document_id, control_id)
);
```

---

### 4. 控制措施

#### **controls (控制措施)**
```sql
CREATE TABLE controls (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- ISO 27001 控制措施資訊
    control_number VARCHAR(20) UNIQUE NOT NULL, -- A.5.1, A.8.9
    name VARCHAR(300) NOT NULL,
    category VARCHAR(50) NOT NULL, -- organizational, people, physical, technological
    description TEXT,
    
    -- 實作指引
    implementation_guide TEXT,
    
    -- 適用性
    is_mandatory BOOLEAN DEFAULT FALSE,
    
    -- ISO 標準對應
    iso_version VARCHAR(20) DEFAULT '2022',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_controls_number ON controls(control_number);
CREATE INDEX idx_controls_category ON controls(category);
```

#### **control_implementations (控制措施實作)**
```sql
CREATE TABLE control_implementations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    control_id UUID REFERENCES controls(id) ON DELETE CASCADE,
    
    -- 實作狀態
    status VARCHAR(50) DEFAULT 'not_started', -- not_started, in_progress, completed
    progress_percentage INTEGER DEFAULT 0,
    
    -- 適用性聲明
    is_applicable BOOLEAN DEFAULT TRUE,
    justification TEXT, -- 不適用時的理由
    
    -- 實作資訊
    implementation_notes TEXT,
    responsible_user_id UUID REFERENCES users(id),
    
    -- 日期
    target_date DATE,
    completion_date DATE,
    last_audit_date DATE,
    next_audit_date DATE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(organization_id, control_id)
);

CREATE INDEX idx_control_impl_org ON control_implementations(organization_id);
CREATE INDEX idx_control_impl_control ON control_implementations(control_id);
CREATE INDEX idx_control_impl_status ON control_implementations(status);
```

#### **asset_control_mappings (資產-控制措施關聯)**
```sql
CREATE TABLE asset_control_mappings (
    asset_id UUID REFERENCES assets(id) ON DELETE CASCADE,
    control_id UUID REFERENCES controls(id) ON DELETE CASCADE,
    
    -- 自動或手動對應
    mapping_type VARCHAR(20) DEFAULT 'automatic', -- automatic, manual
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (asset_id, control_id)
);
```

---

### 5. 風險管理

#### **risks (風險)**
```sql
CREATE TABLE risks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    
    -- 基本資訊
    risk_number VARCHAR(50) UNIQUE NOT NULL, -- RSK-001
    title VARCHAR(300) NOT NULL,
    description TEXT,
    
    -- 威脅與弱點
    threat_type VARCHAR(100),
    vulnerability TEXT,
    
    -- 風險評估
    likelihood INTEGER CHECK (likelihood BETWEEN 1 AND 5), -- 1-5
    impact INTEGER CHECK (impact BETWEEN 1 AND 5), -- 1-5
    risk_score INTEGER GENERATED ALWAYS AS (likelihood * impact) STORED,
    risk_level VARCHAR(20), -- low, medium, high, critical
    
    -- 處理方式
    treatment_option VARCHAR(50), -- accept, mitigate, transfer, avoid
    
    -- 殘餘風險
    residual_likelihood INTEGER,
    residual_impact INTEGER,
    residual_risk_score INTEGER,
    
    -- 責任人
    owner_id UUID REFERENCES users(id),
    
    -- 狀態
    status VARCHAR(50) DEFAULT 'identified', -- identified, assessed, treated, monitored
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE INDEX idx_risks_org ON risks(organization_id);
CREATE INDEX idx_risks_number ON risks(risk_number);
CREATE INDEX idx_risks_level ON risks(risk_level);
```

#### **risk_asset_mappings (風險-資產關聯)**
```sql
CREATE TABLE risk_asset_mappings (
    risk_id UUID REFERENCES risks(id) ON DELETE CASCADE,
    asset_id UUID REFERENCES assets(id) ON DELETE CASCADE,
    
    PRIMARY KEY (risk_id, asset_id)
);
```

#### **risk_control_mappings (風險-控制措施關聯)**
```sql
CREATE TABLE risk_control_mappings (
    risk_id UUID REFERENCES risks(id) ON DELETE CASCADE,
    control_id UUID REFERENCES controls(id) ON DELETE CASCADE,
    
    effectiveness VARCHAR(50), -- low, medium, high
    
    PRIMARY KEY (risk_id, control_id)
);
```

---

### 6. 稽核管理

#### **audits (稽核)**
```sql
CREATE TABLE audits (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    
    -- 基本資訊
    audit_number VARCHAR(50) UNIQUE NOT NULL, -- AUD-2024-001
    name VARCHAR(300) NOT NULL,
    audit_type VARCHAR(50) NOT NULL, -- document_review, technical_audit, physical_security, interview
    
    -- 範圍
    scope TEXT,
    
    -- 負責人
    lead_auditor_id UUID REFERENCES users(id),
    
    -- 日期
    planned_date DATE,
    actual_date DATE,
    completed_date DATE,
    
    -- 狀態
    status VARCHAR(50) DEFAULT 'planned', -- planned, in_progress, completed, cancelled
    
    -- 結果
    result VARCHAR(50), -- pass, fail, partial, n_a
    summary TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audits_org ON audits(organization_id);
CREATE INDEX idx_audits_status ON audits(status);
CREATE INDEX idx_audits_date ON audits(planned_date);
```

#### **audit_checklist_items (稽核檢查項目)**
```sql
CREATE TABLE audit_checklist_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    audit_id UUID REFERENCES audits(id) ON DELETE CASCADE,
    control_id UUID REFERENCES controls(id),
    
    -- 檢查項目
    item_number VARCHAR(20),
    item_description TEXT NOT NULL,
    
    -- 結果
    result VARCHAR(50), -- pass, fail, n_a, pending
    notes TEXT,
    evidence_files JSONB DEFAULT '[]', -- 證據檔案路徑
    
    -- 檢查人員
    checked_by UUID REFERENCES users(id),
    checked_at TIMESTAMP,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_checklist_audit ON audit_checklist_items(audit_id);
```

#### **audit_findings (稽核發現/不符合項)**
```sql
CREATE TABLE audit_findings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    audit_id UUID REFERENCES audits(id) ON DELETE CASCADE,
    
    -- 發現資訊
    finding_number VARCHAR(50) UNIQUE NOT NULL, -- NCR-2024-001
    title VARCHAR(300) NOT NULL,
    description TEXT,
    
    -- 分類
    severity VARCHAR(50) NOT NULL, -- major, minor, observation
    finding_type VARCHAR(50), -- non_conformity, observation, opportunity
    
    -- 關聯
    control_id UUID REFERENCES controls(id),
    asset_id UUID REFERENCES assets(id),
    
    -- 責任人
    responsible_user_id UUID REFERENCES users(id),
    
    -- 狀態
    status VARCHAR(50) DEFAULT 'open', -- open, in_progress, resolved, closed
    
    -- 日期
    due_date DATE,
    resolved_date DATE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_findings_audit ON audit_findings(audit_id);
CREATE INDEX idx_findings_status ON audit_findings(status);
CREATE INDEX idx_findings_severity ON audit_findings(severity);
```

#### **corrective_actions (矯正措施)**
```sql
CREATE TABLE corrective_actions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    finding_id UUID REFERENCES audit_findings(id) ON DELETE CASCADE,
    
    -- 矯正措施
    action_description TEXT NOT NULL,
    root_cause_analysis TEXT,
    
    -- 責任人
    responsible_user_id UUID REFERENCES users(id),
    
    -- 狀態
    status VARCHAR(50) DEFAULT 'planned', -- planned, in_progress, completed, verified
    
    -- 日期
    target_date DATE,
    completion_date DATE,
    verification_date DATE,
    
    -- 驗證
    verified_by UUID REFERENCES users(id),
    verification_notes TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_corrective_actions_finding ON corrective_actions(finding_id);
```

---

### 7. 事件管理

#### **incidents (事件)**
```sql
CREATE TABLE incidents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    
    -- 基本資訊
    incident_number VARCHAR(50) UNIQUE NOT NULL, -- INC-2024-001
    title VARCHAR(300) NOT NULL,
    description TEXT,
    
    -- 分類
    incident_type VARCHAR(50), -- malware, phishing, data_breach, ddos, unauthorized_access
    severity VARCHAR(50) NOT NULL, -- low, medium, high, critical
    
    -- 影響
    affected_assets JSONB DEFAULT '[]',
    impact_description TEXT,
    
    -- 時間
    discovered_at TIMESTAMP,
    occurred_at TIMESTAMP,
    reported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 責任人
    reported_by UUID REFERENCES users(id),
    assigned_to UUID REFERENCES users(id),
    
    -- 狀態
    status VARCHAR(50) DEFAULT 'new', -- new, investigating, contained, resolved, closed
    
    -- 處理
    containment_actions TEXT,
    resolution_summary TEXT,
    lessons_learned TEXT,
    
    -- 關閉
    closed_at TIMESTAMP,
    closed_by UUID REFERENCES users(id),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_incidents_org ON incidents(organization_id);
CREATE INDEX idx_incidents_status ON incidents(status);
CREATE INDEX idx_incidents_severity ON incidents(severity);
CREATE INDEX idx_incidents_reported ON incidents(reported_at);
```

---

### 8. 訓練管理

#### **trainings (訓練課程)**
```sql
CREATE TABLE trainings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    
    -- 課程資訊
    training_number VARCHAR(50) UNIQUE NOT NULL,
    title VARCHAR(300) NOT NULL,
    description TEXT,
    training_type VARCHAR(50), -- awareness, technical, compliance, onboarding
    
    -- 內容
    duration_hours DECIMAL(4,2),
    content_url VARCHAR(500), -- 線上課程連結
    
    -- 要求
    is_mandatory BOOLEAN DEFAULT FALSE,
    passing_score INTEGER DEFAULT 80,
    
    -- 有效期
    validity_months INTEGER DEFAULT 12,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_trainings_org ON trainings(organization_id);
```

#### **training_records (訓練記錄)**
```sql
CREATE TABLE training_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    training_id UUID REFERENCES trainings(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    
    -- 完成資訊
    completed_at TIMESTAMP,
    score INTEGER,
    passed BOOLEAN,
    
    -- 證書
    certificate_url VARCHAR(500),
    
    -- 有效期
    valid_until DATE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(training_id, user_id, completed_at)
);

CREATE INDEX idx_training_records_training ON training_records(training_id);
CREATE INDEX idx_training_records_user ON training_records(user_id);
```

---

### 9. 系統功能表

#### **audit_logs (稽核日誌)**
```sql
CREATE TABLE audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- 使用者資訊
    user_id UUID REFERENCES users(id),
    username VARCHAR(100),
    
    -- 動作資訊
    action VARCHAR(100) NOT NULL, -- create, read, update, delete, login, logout
    resource_type VARCHAR(100), -- asset, document, audit, etc
    resource_id UUID,
    
    -- 詳細資訊
    description TEXT,
    changes JSONB, -- 變更前後的值
    
    -- 請求資訊
    ip_address INET,
    user_agent TEXT,
    
    -- 結果
    success BOOLEAN DEFAULT TRUE,
    error_message TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_logs_user ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_action ON audit_logs(action);
CREATE INDEX idx_audit_logs_resource ON audit_logs(resource_type, resource_id);
CREATE INDEX idx_audit_logs_created ON audit_logs(created_at);
```

#### **notifications (通知)**
```sql
CREATE TABLE notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    
    -- 通知內容
    type VARCHAR(50) NOT NULL, -- info, warning, error, success
    title VARCHAR(300) NOT NULL,
    message TEXT,
    
    -- 關聯資源
    resource_type VARCHAR(100),
    resource_id UUID,
    
    -- 狀態
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_notifications_user ON notifications(user_id);
CREATE INDEX idx_notifications_read ON notifications(is_read);
CREATE INDEX idx_notifications_created ON notifications(created_at);
```

---

## 關鍵設計要點

### 1. UUID vs 自增 ID
- 使用 UUID 作為主鍵（更安全、支援分散式）
- 業務編號（如 AST-001）另外建立唯一索引

### 2. 軟刪除
- 關鍵表使用 `deleted_at` 欄位
- 查詢時加上 `WHERE deleted_at IS NULL`

### 3. JSONB 彈性欄位
- 用於存放非結構化或可變動的資料
- 例如：`technical_details`、`settings`、`changes`

### 4. 時間戳記
- `created_at`：建立時間
- `updated_at`：更新時間（需 trigger 自動更新）
- 統一使用 `TIMESTAMP` 或 `TIMESTAMPTZ`

### 5. 索引策略
- 外鍵欄位建立索引
- 常用查詢條件建立索引
- 唯一性約束

### 6. 關聯表命名
- 多對多關聯：`{table1}_{table2}_mappings`
- 例如：`asset_control_mappings`

---

## 估計資料量（1000 人企業）

```
Table                     估計筆數
--------------------------------
organizations             1
users                     1,000
departments              50
assets                    5,000
documents                 200
controls                  93 (固定)
control_implementations   93
risks                     100
audits                    50/年
incidents                 20/年
trainings                 20
training_records          20,000
audit_logs               100,000/年
notifications            50,000/年
```

**總計：約 50 萬筆記錄/年**

這個設計應該能支撐到 10,000 人規模的企業！