Redmine 資料庫架構與 Table / Column 分析報告
#

註記

本文件針對連線環境 nb-vm114-redmine 進行完整架構解析與欄位彙整。分析時間：2026-06-28。新增全模組 Mermaid ERD 關聯圖。

📊 架構概述 (Executive Summary)
#

資料表總數 (Total Tables): 58 個
欄位總數 (Total Columns): 440 個
主要模組領域: 專案成員、使用者權限、議題追蹤、自訂欄位、Wiki文件、討論區、版本庫整合、系統設定。

🗺️ Redmine 核心全局實體關聯圖 (Global Core ERD)
#

下列繪製 Redmine 核心模組間的主要實體關聯：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


erDiagram
    projects ||--o{ members : "has_members"
    users ||--o{ members : "belongs_to"
    members ||--o{ member_roles : "assigned"
    roles ||--o{ member_roles : "defines"
    projects ||--o{ issues : "contains"
    trackers ||--o{ issues : "classifies"
    issue_statuses ||--o{ issues : "states"
    users ||--o{ issues : "author_or_assignee"
    issues ||--o{ time_entries : "logs"
    issues ||--o{ journals : "tracks_history"
    projects ||--o| wikis : "has_wiki"
    projects ||--o{ repositories : "integrates"

📚 模組目錄 (Table of Contents)
#

1. 專案與成員管理 (Projects & Members)
#

管理 Redmine 專案層級架構、成員組織、權限角色與啟用模組。

📐 模組實體關聯圖 (Module ERD)
#

1
2
3
4
5
6
7
8


erDiagram
    projects ||--o{ members : "has"
    projects ||--o{ enabled_modules : "enables"
    projects ||--o{ projects_trackers : "uses"
    users ||--o{ members : "belongs_to"
    members ||--o{ member_roles : "has"
    roles ||--o{ member_roles : "defines"
    roles ||--o{ roles_managed_roles : "manages"

📋 `projects`
#

說明: 專案主表，記錄專案名稱、識別碼、描述、公開狀態與父子專案關係。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('projects_id_seq'::regclass)`
`name`	`character varying`	NO	`''::character varying`
`description`	`text`	YES	`-`
`homepage`	`character varying`	YES	`''::character varying`
`is_public`	`boolean`	NO	`true`
`parent_id`	`integer`	YES	`-`
`created_on`	`timestamp without time zone`	YES	`-`
`updated_on`	`timestamp without time zone`	YES	`-`
`identifier`	`character varying`	YES	`-`
`status`	`integer`	NO	`1`
`lft`	`integer`	YES	`-`
`rgt`	`integer`	YES	`-`
`inherit_members`	`boolean`	NO	`false`
`default_version_id`	`integer`	YES	`-`
`default_assigned_to_id`	`integer`	YES	`-`
`default_issue_query_id`	`integer`	YES	`-`

📋 `members`
#

說明: 專案與使用者/群組的關聯表，表示成員加入該專案。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('members_id_seq'::regclass)`
`user_id`	`integer`	NO	`0`
`project_id`	`integer`	NO	`0`
`created_on`	`timestamp without time zone`	YES	`-`
`mail_notification`	`boolean`	NO	`false`

📋 `member_roles`
#

說明: 紀錄專案成員在該專案中擁有的角色權限（多對多關聯）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('member_roles_id_seq'::regclass)`
`member_id`	`integer`	NO	`-`
`role_id`	`integer`	NO	`-`
`inherited_from`	`integer`	YES	`-`

📋 `roles`
#

說明: 角色定義表（如 Manager, Developer, Reporter），設定權限細項與工作流程身分。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('roles_id_seq'::regclass)`
`name`	`character varying`	NO	`''::character varying`
`position`	`integer`	YES	`-`
`assignable`	`boolean`	YES	`true`
`builtin`	`integer`	NO	`0`
`permissions`	`text`	YES	`-`
`issues_visibility`	`character varying`	NO	`'default'::character varying`
`users_visibility`	`character varying`	NO	`'members_of_visible_projects'::character varying`
`time_entries_visibility`	`character varying`	NO	`'all'::character varying`
`all_roles_managed`	`boolean`	NO	`true`
`settings`	`text`	YES	`-`
`default_time_entry_activity_id`	`integer`	YES	`-`

📋 `roles_managed_roles`
#

說明: 角色可管理的次要角色關聯表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`role_id`	`integer`	NO	`-`
`managed_role_id`	`integer`	NO	`-`

📋 `enabled_modules`
#

說明: 專案中啟用的模組清單（如 issue_tracking, wiki, time_tracking 等）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('enabled_modules_id_seq'::regclass)`
`project_id`	`integer`	YES	`-`
`name`	`character varying`	NO	`-`

2. 使用者、帳號與認證 (Users & Authentication)
#

儲存系統使用者、群組、驗證來源、存取權杖與 OAuth 授權。

📐 模組實體關聯圖 (Module ERD)
#

1
2
3
4
5
6
7
8


erDiagram
    users ||--o{ email_addresses : "owns"
    users ||--o{ tokens : "generates"
    users ||--o| user_preferences : "has"
    users ||--o{ groups_users : "member_of"
    auth_sources ||--o{ users : "authenticates"
    users ||--o{ oauth_access_tokens : "authorizes"
    oauth_applications ||--o{ oauth_access_tokens : "issues"

📋 `users`
#

說明: 使用者與群組主表（type 欄位區分 User 或 Group），包含登入帳號、密碼雜湊、姓名與狀態。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('users_id_seq'::regclass)`
`login`	`character varying`	NO	`''::character varying`
`hashed_password`	`character varying`	NO	`''::character varying`
`firstname`	`character varying`	NO	`''::character varying`
`lastname`	`character varying`	NO	`''::character varying`
`admin`	`boolean`	NO	`false`
`status`	`integer`	NO	`1`
`last_login_on`	`timestamp without time zone`	YES	`-`
`language`	`character varying`	YES	`''::character varying`
`auth_source_id`	`integer`	YES	`-`
`created_on`	`timestamp without time zone`	YES	`-`
`updated_on`	`timestamp without time zone`	YES	`-`
`type`	`character varying`	YES	`-`
`mail_notification`	`character varying`	NO	`''::character varying`
`salt`	`character varying`	YES	`-`
`must_change_passwd`	`boolean`	NO	`false`
`passwd_changed_on`	`timestamp without time zone`	YES	`-`
`twofa_scheme`	`character varying`	YES	`-`
`twofa_totp_key`	`character varying`	YES	`-`
`twofa_totp_last_used_at`	`integer`	YES	`-`
`twofa_required`	`boolean`	YES	`false`

📋 `email_addresses`
#

說明: 使用者電子郵件地址表，支援多信箱綁定與通知設定。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('email_addresses_id_seq'::regclass)`
`user_id`	`integer`	NO	`-`
`address`	`character varying`	NO	`-`
`is_default`	`boolean`	NO	`false`
`notify`	`boolean`	NO	`true`
`created_on`	`timestamp without time zone`	NO	`-`
`updated_on`	`timestamp without time zone`	NO	`-`

📋 `tokens`
#

說明: 系統 Token 表，用於 RSS/API 存取、重設密碼與自動登入驗證。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('tokens_id_seq'::regclass)`
`user_id`	`integer`	NO	`0`
`action`	`character varying`	NO	`''::character varying`
`value`	`character varying`	NO	`''::character varying`
`created_on`	`timestamp without time zone`	NO	`-`
`updated_on`	`timestamp without time zone`	YES	`-`

📋 `user_preferences`
#

說明: 使用者個人偏好設定（如語系、時區、通知頻率等）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('user_preferences_id_seq'::regclass)`
`user_id`	`integer`	NO	`0`
`others`	`text`	YES	`-`
`hide_mail`	`boolean`	YES	`true`
`time_zone`	`character varying`	YES	`-`

📋 `groups_users`
#

說明: 群組與使用者的多對多關聯表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`group_id`	`integer`	NO	`-`
`user_id`	`integer`	NO	`-`

📋 `auth_sources`
#

說明: 外部認證來源（如 LDAP / Active Directory）設定表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('auth_sources_id_seq'::regclass)`
`type`	`character varying`	NO	`''::character varying`
`name`	`character varying`	NO	`''::character varying`
`host`	`character varying`	YES	`-`
`port`	`integer`	YES	`-`
`account`	`character varying`	YES	`-`
`account_password`	`character varying`	YES	`''::character varying`
`base_dn`	`character varying`	YES	`-`
`attr_login`	`character varying`	YES	`-`
`attr_firstname`	`character varying`	YES	`-`
`attr_lastname`	`character varying`	YES	`-`
`attr_mail`	`character varying`	YES	`-`
`onthefly_register`	`boolean`	NO	`false`
`tls`	`boolean`	NO	`false`
`filter`	`text`	YES	`-`
`timeout`	`integer`	YES	`-`
`verify_peer`	`boolean`	NO	`true`

📋 `oauth_applications`
#

說明: OAuth 2.0 應用程式註冊資訊表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`bigint`	NO	`nextval('oauth_applications_id_seq'::regclass)`
`name`	`character varying`	NO	`-`
`uid`	`character varying`	NO	`-`
`secret`	`character varying`	NO	`-`
`redirect_uri`	`text`	NO	`-`
`scopes`	`text`	NO	`-`
`confidential`	`boolean`	NO	`true`
`created_at`	`timestamp without time zone`	NO	`-`
`updated_at`	`timestamp without time zone`	NO	`-`

📋 `oauth_access_grants`
#

說明: OAuth 2.0 授權碼紀錄表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`bigint`	NO	`nextval('oauth_access_grants_id_seq'::regclass)`
`resource_owner_id`	`integer`	NO	`-`
`application_id`	`bigint`	NO	`-`
`token`	`character varying`	NO	`-`
`expires_in`	`integer`	NO	`-`
`redirect_uri`	`text`	NO	`-`
`created_at`	`timestamp without time zone`	NO	`-`
`revoked_at`	`timestamp without time zone`	YES	`-`
`scopes`	`text`	YES	`-`
`code_challenge`	`character varying`	YES	`-`
`code_challenge_method`	`character varying`	YES	`-`

📋 `oauth_access_tokens`
#

說明: OAuth 2.0 存取權杖紀錄表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`bigint`	NO	`nextval('oauth_access_tokens_id_seq'::regclass)`
`resource_owner_id`	`integer`	YES	`-`
`application_id`	`bigint`	YES	`-`
`token`	`character varying`	NO	`-`
`refresh_token`	`character varying`	YES	`-`
`expires_in`	`integer`	YES	`-`
`revoked_at`	`timestamp without time zone`	YES	`-`
`created_at`	`timestamp without time zone`	NO	`-`
`scopes`	`text`	YES	`-`
`previous_refresh_token`	`character varying`	NO	`''::character varying`

3. 議題、追蹤與工作流程 (Issues & Workflows)
#

Redmine 的核心功能，涵蓋議題追蹤、狀態轉換、變更歷程、工時紀錄與版本里程碑。

📐 模組實體關聯圖 (Module ERD)
#

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


erDiagram
    projects ||--o{ issues : "contains"
    trackers ||--o{ issues : "classifies"
    issue_statuses ||--o{ issues : "defines_status"
    users ||--o{ issues : "author_or_assigned"
    versions ||--o{ issues : "target_version"
    issues ||--o{ journals : "logs_changes"
    journals ||--o{ journal_details : "details"
    issues ||--o{ time_entries : "logs_time"
    issues ||--o{ issue_relations : "relates_to"
    roles ||--o{ workflows : "dictates"
    trackers ||--o{ workflows : "applies_to"

📋 `issues`
#

說明: 議題（任務/Bug/Feature）主表，記錄標題、描述、指派人、狀態、優先級、起止日期與進度。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('issues_id_seq'::regclass)`
`tracker_id`	`integer`	NO	`-`
`project_id`	`integer`	NO	`-`
`subject`	`character varying`	NO	`''::character varying`
`description`	`text`	YES	`-`
`due_date`	`date`	YES	`-`
`category_id`	`integer`	YES	`-`
`status_id`	`integer`	NO	`-`
`assigned_to_id`	`integer`	YES	`-`
`priority_id`	`integer`	NO	`-`
`fixed_version_id`	`integer`	YES	`-`
`author_id`	`integer`	NO	`-`
`lock_version`	`integer`	NO	`0`
`created_on`	`timestamp without time zone`	YES	`-`
`updated_on`	`timestamp without time zone`	YES	`-`
`start_date`	`date`	YES	`-`
`done_ratio`	`integer`	NO	`0`
`estimated_hours`	`double precision`	YES	`-`
`parent_id`	`integer`	YES	`-`
`root_id`	`integer`	YES	`-`
`lft`	`integer`	YES	`-`
`rgt`	`integer`	YES	`-`
`is_private`	`boolean`	NO	`false`
`closed_on`	`timestamp without time zone`	YES	`-`

📋 `trackers`
#

說明: 追蹤標籤定義表（如 Defect, Feature, Support）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('trackers_id_seq'::regclass)`
`name`	`character varying`	NO	`''::character varying`
`position`	`integer`	YES	`-`
`is_in_roadmap`	`boolean`	NO	`true`
`fields_bits`	`integer`	YES	`0`
`default_status_id`	`integer`	YES	`-`
`description`	`character varying`	YES	`-`

📋 `projects_trackers`
#

說明: 專案與追蹤標籤的關聯表，決定專案可用哪些議題類型。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`project_id`	`integer`	NO	`0`
`tracker_id`	`integer`	NO	`0`

📋 `issue_statuses`
#

說明: 議題狀態定義表（如 New, In Progress, Resolved, Closed）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('issue_statuses_id_seq'::regclass)`
`name`	`character varying`	NO	`''::character varying`
`is_closed`	`boolean`	NO	`false`
`position`	`integer`	YES	`-`
`default_done_ratio`	`integer`	YES	`-`
`description`	`character varying`	YES	`-`

📋 `issue_categories`
#

說明: 專案內議題的分類目錄表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('issue_categories_id_seq'::regclass)`
`project_id`	`integer`	NO	`0`
`name`	`character varying`	NO	`''::character varying`
`assigned_to_id`	`integer`	YES	`-`

📋 `issue_relations`
#

說明: 議題之間的關聯表（如 relates, duplicates, blocks, precedes 等）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('issue_relations_id_seq'::regclass)`
`issue_from_id`	`integer`	NO	`-`
`issue_to_id`	`integer`	NO	`-`
`relation_type`	`character varying`	NO	`''::character varying`
`delay`	`integer`	YES	`-`

📋 `workflows`
#

說明: 工作流程權限矩陣表，定義特定角色在特定狀態下可轉移至哪些新狀態與欄位權限。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('workflows_id_seq'::regclass)`
`tracker_id`	`integer`	NO	`0`
`old_status_id`	`integer`	NO	`0`
`new_status_id`	`integer`	NO	`0`
`role_id`	`integer`	NO	`0`
`assignee`	`boolean`	NO	`false`
`author`	`boolean`	NO	`false`
`type`	`character varying`	YES	`-`
`field_name`	`character varying`	YES	`-`
`rule`	`character varying`	YES	`-`

📋 `journals`
#

說明: 議題與變更操作日誌主表，紀錄留言評註與異動時間點。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('journals_id_seq'::regclass)`
`journalized_id`	`integer`	NO	`0`
`journalized_type`	`character varying`	NO	`''::character varying`
`user_id`	`integer`	NO	`0`
`notes`	`text`	YES	`-`
`created_on`	`timestamp without time zone`	NO	`-`
`private_notes`	`boolean`	NO	`false`
`updated_on`	`timestamp without time zone`	YES	`-`
`updated_by_id`	`integer`	YES	`-`

📋 `journal_details`
#

說明: 日誌詳細異動紀錄表，紀錄具體欄位變更前後的值（old_value, value）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('journal_details_id_seq'::regclass)`
`journal_id`	`integer`	NO	`0`
`property`	`character varying`	NO	`''::character varying`
`prop_key`	`character varying`	NO	`''::character varying`
`old_value`	`text`	YES	`-`
`value`	`text`	YES	`-`

📋 `time_entries`
#

說明: 工時紀錄表，紀錄成員在議題或專案上花費的時間、活動類型與備註。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('time_entries_id_seq'::regclass)`
`project_id`	`integer`	NO	`-`
`user_id`	`integer`	NO	`-`
`issue_id`	`integer`	YES	`-`
`hours`	`double precision`	NO	`-`
`comments`	`character varying`	YES	`-`
`activity_id`	`integer`	NO	`-`
`spent_on`	`date`	NO	`-`
`tyear`	`integer`	NO	`-`
`tmonth`	`integer`	NO	`-`
`tweek`	`integer`	NO	`-`
`created_on`	`timestamp without time zone`	NO	`-`
`updated_on`	`timestamp without time zone`	NO	`-`
`author_id`	`integer`	YES	`-`

📋 `versions`
#

說明: 專案版本/里程碑目標表，用於版本規劃與 Roadmaps。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('versions_id_seq'::regclass)`
`project_id`	`integer`	NO	`0`
`name`	`character varying`	NO	`''::character varying`
`description`	`character varying`	YES	`''::character varying`
`effective_date`	`date`	YES	`-`
`created_on`	`timestamp without time zone`	YES	`-`
`updated_on`	`timestamp without time zone`	YES	`-`
`wiki_page_title`	`character varying`	YES	`-`
`status`	`character varying`	YES	`'open'::character varying`
`sharing`	`character varying`	NO	`'none'::character varying`

📋 `watchers`
#

說明: 關注者表，紀錄哪些使用者關注特定議題、文件或 Wiki。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('watchers_id_seq'::regclass)`
`watchable_type`	`character varying`	NO	`''::character varying`
`watchable_id`	`integer`	NO	`0`
`user_id`	`integer`	YES	`-`

4. 自訂欄位與列舉選單 (Custom Fields & Enumerations)
#

提供 Redmine 強大的擴充能力，允許在各式物件上增設自訂欄位與選單清單。

📐 模組實體關聯圖 (Module ERD)
#

1
2
3
4
5
6


erDiagram
    custom_fields ||--o{ custom_values : "defines_value_for"
    custom_fields ||--o{ custom_fields_projects : "applies_to_project"
    custom_fields ||--o{ custom_fields_trackers : "applies_to_tracker"
    custom_fields ||--o{ custom_fields_roles : "visible_to_role"
    custom_fields ||--o{ custom_field_enumerations : "has_options"

📋 `custom_fields`
#

說明: 自訂欄位元資料定義表，設定欄位名稱、資料類型、驗證規則與套用物件。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('custom_fields_id_seq'::regclass)`
`type`	`character varying`	NO	`''::character varying`
`name`	`character varying`	NO	`''::character varying`
`field_format`	`character varying`	NO	`''::character varying`
`possible_values`	`text`	YES	`-`
`regexp`	`character varying`	YES	`''::character varying`
`min_length`	`integer`	YES	`-`
`max_length`	`integer`	YES	`-`
`is_required`	`boolean`	NO	`false`
`is_for_all`	`boolean`	NO	`false`
`is_filter`	`boolean`	NO	`false`
`position`	`integer`	YES	`-`
`searchable`	`boolean`	YES	`false`
`default_value`	`text`	YES	`-`
`editable`	`boolean`	YES	`true`
`visible`	`boolean`	NO	`true`
`multiple`	`boolean`	YES	`false`
`format_store`	`text`	YES	`-`
`description`	`text`	YES	`-`

📋 `custom_fields_projects`
#

說明: 自訂欄位與專案關聯表（限定專案啟用）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`custom_field_id`	`integer`	NO	`0`
`project_id`	`integer`	NO	`0`

📋 `custom_fields_trackers`
#

說明: 自訂欄位與議題追蹤標籤關聯表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`custom_field_id`	`integer`	NO	`0`
`tracker_id`	`integer`	NO	`0`

📋 `custom_fields_roles`
#

說明: 自訂欄位與角色的權限可見度關聯表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`custom_field_id`	`integer`	NO	`-`
`role_id`	`integer`	NO	`-`

📋 `custom_values`
#

說明: 自訂欄位實際儲存數值的表，連結至各目標物件（customized_id, customized_type）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('custom_values_id_seq'::regclass)`
`customized_type`	`character varying`	NO	`''::character varying`
`customized_id`	`integer`	NO	`0`
`custom_field_id`	`integer`	NO	`0`
`value`	`text`	YES	`-`

📋 `custom_field_enumerations`
#

說明: 自訂選單型欄位的可選項內容表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('custom_field_enumerations_id_seq'::regclass)`
`custom_field_id`	`integer`	NO	`-`
`name`	`character varying`	NO	`-`
`active`	`boolean`	NO	`true`
`position`	`integer`	NO	`1`

📋 `enumerations`
#

說明: 系統通用列舉選單值（如議題優先級、工時活動類型、文件分類等）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('enumerations_id_seq'::regclass)`
`name`	`character varying`	NO	`''::character varying`
`position`	`integer`	YES	`-`
`is_default`	`boolean`	NO	`false`
`type`	`character varying`	YES	`-`
`active`	`boolean`	NO	`true`
`project_id`	`integer`	YES	`-`
`parent_id`	`integer`	YES	`-`
`position_name`	`character varying`	YES	`-`

5. Wiki 與文件系統 (Wiki & Documents)
#

專案協作知識庫、文件庫與檔案附件管理。

📐 模組實體關聯圖 (Module ERD)
#

1
2
3
4
5
6


erDiagram
    projects ||--o| wikis : "has"
    wikis ||--o{ wiki_pages : "contains"
    wiki_pages ||--o| wiki_contents : "current_content"
    wiki_contents ||--o{ wiki_content_versions : "history"
    projects ||--o{ documents : "stores"

📋 `wikis`
#

說明: 專案 Wiki 系統主表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('wikis_id_seq'::regclass)`
`project_id`	`integer`	NO	`-`
`start_page`	`character varying`	NO	`-`
`status`	`integer`	NO	`1`

📋 `wiki_pages`
#

說明: Wiki 頁面節點表，記錄頁面標題與層級父子關係。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('wiki_pages_id_seq'::regclass)`
`wiki_id`	`integer`	NO	`-`
`title`	`character varying`	NO	`-`
`created_on`	`timestamp without time zone`	NO	`-`
`protected`	`boolean`	NO	`false`
`parent_id`	`integer`	YES	`-`

📋 `wiki_contents`
#

說明: Wiki 頁面當前最新內容表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('wiki_contents_id_seq'::regclass)`
`page_id`	`integer`	NO	`-`
`author_id`	`integer`	YES	`-`
`text`	`text`	YES	`-`
`comments`	`character varying`	YES	`''::character varying`
`updated_on`	`timestamp without time zone`	NO	`-`
`version`	`integer`	NO	`-`

📋 `wiki_content_versions`
#

說明: Wiki 頁面歷史版本控制表，支援版本比對與回滾。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('wiki_content_versions_id_seq'::regclass)`
`wiki_content_id`	`integer`	NO	`-`
`page_id`	`integer`	NO	`-`
`author_id`	`integer`	YES	`-`
`data`	`bytea`	YES	`-`
`compression`	`character varying`	YES	`''::character varying`
`comments`	`character varying`	YES	`''::character varying`
`updated_on`	`timestamp without time zone`	NO	`-`
`version`	`integer`	NO	`-`

📋 `wiki_redirects`
#

說明: Wiki 頁面重新命名或搬移後的重新導向紀錄。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('wiki_redirects_id_seq'::regclass)`
`wiki_id`	`integer`	NO	`-`
`title`	`character varying`	YES	`-`
`redirects_to`	`character varying`	YES	`-`
`created_on`	`timestamp without time zone`	NO	`-`
`redirects_to_wiki_id`	`integer`	NO	`-`

📋 `documents`
#

說明: 專案文件分類與標題紀錄表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('documents_id_seq'::regclass)`
`project_id`	`integer`	NO	`0`
`category_id`	`integer`	NO	`0`
`title`	`character varying`	NO	`''::character varying`
`description`	`text`	YES	`-`
`created_on`	`timestamp without time zone`	YES	`-`

📋 `attachments`
#

說明: 實體上傳檔案附件紀錄表，紀錄檔名、檔案大小、MD5/SHA256 與儲存路徑。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('attachments_id_seq'::regclass)`
`container_id`	`integer`	YES	`-`
`container_type`	`character varying`	YES	`-`
`filename`	`character varying`	NO	`''::character varying`
`disk_filename`	`character varying`	NO	`''::character varying`
`filesize`	`bigint`	NO	`0`
`content_type`	`character varying`	YES	`''::character varying`
`digest`	`character varying`	NO	`''::character varying`
`downloads`	`integer`	NO	`0`
`author_id`	`integer`	NO	`0`
`created_on`	`timestamp without time zone`	YES	`-`
`description`	`character varying`	YES	`-`
`disk_directory`	`character varying`	YES	`-`

6. 討論區與新聞 (Forums, News & Social)
#

專案團隊溝通管道、社群討論區與動態互動。

📐 模組實體關聯圖 (Module ERD)
#

1
2
3
4
5


erDiagram
    projects ||--o{ boards : "has"
    boards ||--o{ messages : "contains"
    projects ||--o{ news : "publishes"
    news ||--o{ comments : "receives"

📋 `boards`
#

說明: 討論區版塊主表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('boards_id_seq'::regclass)`
`project_id`	`integer`	NO	`-`
`name`	`character varying`	NO	`''::character varying`
`description`	`character varying`	YES	`-`
`position`	`integer`	YES	`-`
`topics_count`	`integer`	NO	`0`
`messages_count`	`integer`	NO	`0`
`last_message_id`	`integer`	YES	`-`
`parent_id`	`integer`	YES	`-`

📋 `messages`
#

說明: 討論區主題與回覆文章表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('messages_id_seq'::regclass)`
`board_id`	`integer`	NO	`-`
`parent_id`	`integer`	YES	`-`
`subject`	`character varying`	NO	`''::character varying`
`content`	`text`	YES	`-`
`author_id`	`integer`	YES	`-`
`replies_count`	`integer`	NO	`0`
`last_reply_id`	`integer`	YES	`-`
`created_on`	`timestamp without time zone`	NO	`-`
`updated_on`	`timestamp without time zone`	NO	`-`
`locked`	`boolean`	YES	`false`
`sticky`	`integer`	YES	`0`

📋 `news`
#

說明: 專案新聞發布表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('news_id_seq'::regclass)`
`project_id`	`integer`	YES	`-`
`title`	`character varying`	NO	`''::character varying`
`summary`	`character varying`	YES	`''::character varying`
`description`	`text`	YES	`-`
`author_id`	`integer`	NO	`0`
`created_on`	`timestamp without time zone`	YES	`-`
`comments_count`	`integer`	NO	`0`

📋 `comments`
#

說明: 新聞或特定物件下方留言評論表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('comments_id_seq'::regclass)`
`commented_type`	`character varying`	NO	`''::character varying`
`commented_id`	`integer`	NO	`0`
`author_id`	`integer`	NO	`0`
`content`	`text`	YES	`-`
`created_on`	`timestamp without time zone`	NO	`-`
`updated_on`	`timestamp without time zone`	NO	`-`

📋 `reactions`
#

說明: 對文章、留言或議題的表情符號按讚/反應紀錄表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`bigint`	NO	`nextval('reactions_id_seq'::regclass)`
`reactable_type`	`character varying`	NO	`-`
`reactable_id`	`bigint`	NO	`-`
`user_id`	`bigint`	NO	`-`
`created_at`	`timestamp without time zone`	NO	`-`
`updated_at`	`timestamp without time zone`	NO	`-`

7. 版本控制整合 (Repositories & Changesets)
#

與 Git, SVN, Mercurial 等程式碼儲存庫整合與 Commit 紀錄。

📐 模組實體關聯圖 (Module ERD)
#

1
2
3
4
5
6


erDiagram
    projects ||--o{ repositories : "connects"
    repositories ||--o{ changesets : "records"
    changesets ||--o{ changesets_issues : "links"
    issues ||--o{ changesets_issues : "linked_by"
    changesets ||--o{ changes : "details"

📋 `repositories`
#

說明: 版本庫連線設定表（Git/SVN 等路徑與驗證）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('repositories_id_seq'::regclass)`
`project_id`	`integer`	NO	`0`
`url`	`character varying`	NO	`''::character varying`
`login`	`character varying`	YES	`''::character varying`
`password`	`character varying`	YES	`''::character varying`
`root_url`	`character varying`	YES	`''::character varying`
`type`	`character varying`	YES	`-`
`path_encoding`	`character varying`	YES	`NULL::character varying`
`log_encoding`	`character varying`	YES	`NULL::character varying`
`extra_info`	`text`	YES	`-`
`identifier`	`character varying`	YES	`-`
`is_default`	`boolean`	YES	`false`
`created_on`	`timestamp without time zone`	YES	`-`

📋 `changesets`
#

說明: 程式碼提交紀錄（Commit / Revision）主表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('changesets_id_seq'::regclass)`
`repository_id`	`integer`	NO	`-`
`revision`	`character varying`	NO	`-`
`committer`	`character varying`	YES	`-`
`committed_on`	`timestamp without time zone`	NO	`-`
`comments`	`text`	YES	`-`
`commit_date`	`date`	YES	`-`
`scmid`	`character varying`	YES	`-`
`user_id`	`integer`	YES	`-`

📋 `changesets_issues`
#

說明: Commit 與議題的關聯表（透過 Commit Message 自動關聯）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`changeset_id`	`integer`	NO	`-`
`issue_id`	`integer`	NO	`-`

📋 `changes`
#

說明: 單次 Commit 所影響的檔案變更明細表（新增/修改/刪除與分支）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('changes_id_seq'::regclass)`
`changeset_id`	`integer`	NO	`-`
`action`	`character varying`	NO	`''::character varying`
`path`	`text`	NO	`-`
`from_path`	`text`	YES	`-`
`from_revision`	`character varying`	YES	`-`
`revision`	`character varying`	YES	`-`
`branch`	`character varying`	YES	`-`

📋 `changeset_parents`
#

說明: Commit 節點分支父子關係紀錄表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`changeset_id`	`integer`	NO	`-`
`parent_id`	`integer`	NO	`-`

8. 系統設定、查詢與匯入 (Settings, Queries & Imports)
#

個人化自訂查詢、系統全域設定與批次資料匯入。

📐 模組實體關聯圖 (Module ERD)
#

1
2
3
4


erDiagram
    users ||--o{ queries : "creates"
    queries ||--o{ queries_roles : "shared_with"
    imports ||--o{ import_items : "contains"

📋 `settings`
#

說明: Redmine 全域系統設定鍵值對表（如系統名稱、郵件發送設定等）。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('settings_id_seq'::regclass)`
`name`	`character varying`	NO	`''::character varying`
`value`	`text`	YES	`-`
`updated_on`	`timestamp without time zone`	YES	`-`

📋 `queries`
#

說明: 自訂議題/工時/專案過濾器與報表查詢儲存表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('queries_id_seq'::regclass)`
`project_id`	`integer`	YES	`-`
`name`	`character varying`	NO	`''::character varying`
`filters`	`text`	YES	`-`
`user_id`	`integer`	NO	`0`
`column_names`	`text`	YES	`-`
`sort_criteria`	`text`	YES	`-`
`group_by`	`character varying`	YES	`-`
`type`	`character varying`	YES	`-`
`visibility`	`integer`	YES	`0`
`options`	`text`	YES	`-`
`description`	`character varying`	YES	`-`

📋 `queries_roles`
#

說明: 自訂查詢的權限角色可見度關聯表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`query_id`	`integer`	NO	`-`
`role_id`	`integer`	NO	`-`

📋 `imports`
#

說明: 批次 CSV 資料匯入任務表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('imports_id_seq'::regclass)`
`type`	`character varying`	YES	`-`
`user_id`	`integer`	NO	`-`
`filename`	`character varying`	YES	`-`
`settings`	`text`	YES	`-`
`total_items`	`integer`	YES	`-`
`finished`	`boolean`	NO	`false`
`created_at`	`timestamp without time zone`	NO	`-`
`updated_at`	`timestamp without time zone`	NO	`-`

📋 `import_items`
#

說明: 批次匯入任務之單項資料驗證與執行紀錄表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`id`	`integer`	NO	`nextval('import_items_id_seq'::regclass)`
`import_id`	`integer`	NO	`-`
`position`	`integer`	NO	`-`
`obj_id`	`integer`	YES	`-`
`message`	`text`	YES	`-`
`unique_id`	`character varying`	YES	`-`

9. 系統框架內部資料 (Framework Metadata)
#

Ruby on Rails 框架與 ActiveRecord 內部架構遷移紀錄。

📋 `schema_migrations`
#

說明: Rails 資料庫 Migration 版本紀錄表。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`version`	`character varying`	NO	`-`

📋 `ar_internal_metadata`
#

說明: ActiveRecord 內部環境與架構狀態元資料。

欄位名稱 (Column)	資料型態 (Data Type)	允許 Null	預設值 (Default)
`key`	`character varying`	NO	`-`
`value`	`character varying`	YES	`-`
`created_at`	`timestamp without time zone`	NO	`-`
`updated_at`	`timestamp without time zone`	NO	`-`

Redmine 資料庫架構與 Table / Column 分析報告 #

📊 架構概述 (Executive Summary) #

🗺️ Redmine 核心全局實體關聯圖 (Global Core ERD) #

📚 模組目錄 (Table of Contents) #

1. 專案與成員管理 (Projects & Members) #

📐 模組實體關聯圖 (Module ERD) #

📋 projects #

📋 members #

📋 member_roles #

📋 roles #

📋 roles_managed_roles #

📋 enabled_modules #

2. 使用者、帳號與認證 (Users & Authentication) #

📐 模組實體關聯圖 (Module ERD) #

📋 users #

📋 email_addresses #

📋 tokens #

📋 user_preferences #

📋 groups_users #

📋 auth_sources #

📋 oauth_applications #

📋 oauth_access_grants #

📋 oauth_access_tokens #

3. 議題、追蹤與工作流程 (Issues & Workflows) #

📐 模組實體關聯圖 (Module ERD) #

📋 issues #

📋 trackers #

📋 projects_trackers #

📋 issue_statuses #

📋 issue_categories #

📋 issue_relations #

📋 workflows #

📋 journals #

📋 journal_details #

📋 time_entries #

📋 versions #

📋 watchers #

4. 自訂欄位與列舉選單 (Custom Fields & Enumerations) #

📐 模組實體關聯圖 (Module ERD) #

📋 custom_fields #

📋 custom_fields_projects #

📋 custom_fields_trackers #

📋 custom_fields_roles #

📋 custom_values #

📋 custom_field_enumerations #

📋 enumerations #

5. Wiki 與文件系統 (Wiki & Documents) #

📐 模組實體關聯圖 (Module ERD) #

📋 wikis #

📋 wiki_pages #

📋 wiki_contents #

📋 wiki_content_versions #

📋 wiki_redirects #

📋 documents #

📋 attachments #

6. 討論區與新聞 (Forums, News & Social) #

📐 模組實體關聯圖 (Module ERD) #

📋 boards #

📋 messages #

📋 news #

📋 comments #

📋 reactions #

7. 版本控制整合 (Repositories & Changesets) #

📐 模組實體關聯圖 (Module ERD) #

📋 repositories #

📋 changesets #

📋 changesets_issues #

📋 changes #

📋 changeset_parents #

8. 系統設定、查詢與匯入 (Settings, Queries & Imports) #

📐 模組實體關聯圖 (Module ERD) #

📋 settings #

📋 queries #

📋 queries_roles #

📋 imports #

📋 import_items #

9. 系統框架內部資料 (Framework Metadata) #

📋 schema_migrations #

📋 ar_internal_metadata #

Redmine 資料庫架構與 Table / Column 分析報告
#

📊 架構概述 (Executive Summary)
#

🗺️ Redmine 核心全局實體關聯圖 (Global Core ERD)
#

📚 模組目錄 (Table of Contents)
#

1. 專案與成員管理 (Projects & Members)
#

📐 模組實體關聯圖 (Module ERD)
#

📋 `projects`
#

📋 `members`
#

📋 `member_roles`
#

📋 `roles`
#

📋 `roles_managed_roles`
#

📋 `enabled_modules`
#

2. 使用者、帳號與認證 (Users & Authentication)
#

📐 模組實體關聯圖 (Module ERD)
#

📋 `users`
#

📋 `email_addresses`
#

📋 `tokens`
#

📋 `user_preferences`
#

📋 `groups_users`
#

📋 `auth_sources`
#

📋 `oauth_applications`
#

📋 `oauth_access_grants`
#

📋 `oauth_access_tokens`
#

3. 議題、追蹤與工作流程 (Issues & Workflows)
#

📐 模組實體關聯圖 (Module ERD)
#

📋 `issues`
#

📋 `trackers`
#

📋 `projects_trackers`
#

📋 `issue_statuses`
#

📋 `issue_categories`
#

📋 `issue_relations`
#

📋 `workflows`
#

📋 `journals`
#

📋 `journal_details`
#

📋 `time_entries`
#

📋 `versions`
#

📋 `watchers`
#

4. 自訂欄位與列舉選單 (Custom Fields & Enumerations)
#

📐 模組實體關聯圖 (Module ERD)
#

📋 `custom_fields`
#

📋 `custom_fields_projects`
#

📋 `custom_fields_trackers`
#

📋 `custom_fields_roles`
#

📋 `custom_values`
#

📋 `custom_field_enumerations`
#

📋 `enumerations`
#

5. Wiki 與文件系統 (Wiki & Documents)
#

📐 模組實體關聯圖 (Module ERD)
#

📋 `wikis`
#

📋 `wiki_pages`
#

📋 `wiki_contents`
#

📋 `wiki_content_versions`
#

📋 `wiki_redirects`
#

📋 `documents`
#

📋 `attachments`
#

6. 討論區與新聞 (Forums, News & Social)
#

📐 模組實體關聯圖 (Module ERD)
#

📋 `boards`
#

📋 `messages`
#

📋 `news`
#

📋 `comments`
#

📋 `reactions`
#

7. 版本控制整合 (Repositories & Changesets)
#

📐 模組實體關聯圖 (Module ERD)
#

📋 `repositories`
#

📋 `changesets`
#

📋 `changesets_issues`
#

📋 `changes`
#

📋 `changeset_parents`
#

8. 系統設定、查詢與匯入 (Settings, Queries & Imports)
#

📐 模組實體關聯圖 (Module ERD)
#

📋 `settings`
#

📋 `queries`
#

📋 `queries_roles`
#

📋 `imports`
#

📋 `import_items`
#

9. 系統框架內部資料 (Framework Metadata)
#

📋 `schema_migrations`
#

📋 `ar_internal_metadata`
#