数据库初始化文档

1. 文档概述

本文档为 tio-mail-wing IMAP 邮件系统项目提供完整的 PostgreSQL 数据库初始化方案。方案包含两部分：

1. 数据表结构定义 (DDL): 创建项目所需的所有核心数据表，其设计充分考虑了 IMAP 协议的特性、数据库性能优化以及未来的可扩展性。
2. 基础数据填充 (DML): 初始化系统所需的默认用户及其邮箱，方便开发和测试工作的快速启动。

核心设计理念:

• IMAP 协议兼容: 表结构严格遵循 IMAP RFC 规范，特别是对 UID, UIDVALIDITY, UIDNEXT 和 Flags 的处理。
• 性能优化: 将邮件元数据 (mw_mail) 与大容量的邮件原始内容 (mw_mail_message) 分离，极大提升了邮箱列表的查询效率。
• 存储去重: 通过对邮件内容计算哈希值 (content_hash)，实现了相同邮件只存储一份内容，有效节省了存储空间。
• 高效检索: 预先解析并存储了邮件的关键头部字段（如主题、发件人），并利用 PostgreSQL 的 TSVECTOR 类型为全文检索提供了强大的支持。

---

2. 数据库表结构 (DDL)

请按顺序执行以下 SQL 语句以创建所有必需的数据表。

2.1 用户表 (mw_user)

存储系统用户信息和用于登录认证的凭据。

-- ----------------------------
-- 1. 用户表 (mw_user)
-- 存储系统用户信息和认证凭据
-- ----------------------------
CREATE TABLE mw_user (
  "id" BIGINT NOT NULL PRIMARY KEY,
  "username" VARCHAR(255) NOT NULL UNIQUE, -- 邮箱地址，必须唯一
  "password_hash" VARCHAR(255) NOT NULL,   -- 存储加密后的密码哈希值
  "remark" VARCHAR(256),
  "creator" VARCHAR(64) DEFAULT '',
  "create_time" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updater" VARCHAR(64) DEFAULT '',
  "update_time" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deleted" SMALLINT DEFAULT 0,
  "tenant_id" BIGINT NOT NULL DEFAULT 0
);

COMMENT ON TABLE mw_user IS '用户表';
COMMENT ON COLUMN mw_user.id IS '主键ID';
COMMENT ON COLUMN mw_user.username IS '用户名 (邮箱地址)';
COMMENT ON COLUMN mw_user.password_hash IS '加密后的密码哈希';

2.2 邮箱目录表 (mw_mailbox)

存储每个用户的邮箱目录，如 Inbox, Trash 等。

设计要点:

• uid_validity: 邮箱的唯一标识符。我们采用邮箱自身的主键 id 作为其值，确保了全系统唯一，这是最健壮的设计实践。
• uid_next: 指示该邮箱中下一个新邮件将被分配的 UID，由业务逻辑维护。

-- ----------------------------
-- 2. 邮箱目录表 (mw_mailbox)
-- 存储用户的邮箱，如 INBOX, Sent, Drafts 等
-- ----------------------------
CREATE TABLE mw_mailbox (
  "id" BIGINT NOT NULL PRIMARY KEY,
  "user_id" BIGINT NOT NULL,               -- 所属用户ID
  "name" VARCHAR(255) NOT NULL,            -- 邮箱名称 (e.g., INBOX, Sent)
  "uid_validity" BIGINT NOT NULL,          -- IMAP UIDVALIDITY 值，用于客户端同步
  "uid_next" BIGINT NOT NULL DEFAULT 1,    -- IMAP UIDNEXT 值，下一个可用的UID
  "remark" VARCHAR(256),
  "creator" VARCHAR(64) DEFAULT '',
  "create_time" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updater" VARCHAR(64) DEFAULT '',
  "update_time" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deleted" SMALLINT DEFAULT 0,
  "tenant_id" BIGINT NOT NULL DEFAULT 0,
  UNIQUE (user_id, name)
);

COMMENT ON TABLE mw_mailbox IS '邮箱目录表';
COMMENT ON COLUMN mw_mailbox.id IS '主键ID';
COMMENT ON COLUMN mw_mailbox.user_id IS '所属用户ID (关联 mw_user.id)';
COMMENT ON COLUMN mw_mailbox.name IS '邮箱名称 (如 INBOX, Sent, Drafts)';
COMMENT ON COLUMN mw_mailbox.uid_validity IS 'IMAP UIDVALIDITY，创建时生成，用于客户端同步';
COMMENT ON COLUMN mw_mailbox.uid_next IS '下一个可用的邮件UID';

2.3 邮件消息表 (mw_mail_message)

存储唯一的邮件消息体及其可检索的元数据，是实现去重和高效搜索的核心。

-- ----------------------------
-- 3. 邮件消息表 (mw_mail_message)
-- 存储唯一的邮件消息体及其可检索的元数据，支持去重
-- ----------------------------
CREATE TABLE mw_mail_message (
  "id" BIGINT NOT NULL PRIMARY KEY,
  "content_hash" VARCHAR(64) NOT NULL UNIQUE, -- 邮件原始内容的SHA-256哈希值，用于去重
  "message_id_header" VARCHAR(512),          -- 邮件头中的 Message-ID
  "subject" VARCHAR(1024),                   -- 邮件主题
  "from_address" TEXT,                       -- 发件人地址
  "to_address" TEXT,                         -- 收件人地址 (可以是多个，用逗号分隔)
  "cc_address" TEXT,                         -- 抄送地址
  "sent_date" TIMESTAMP WITH TIME ZONE,      -- 邮件头中的原始发送日期
  "has_attachment" BOOLEAN DEFAULT FALSE,    -- 是否包含附件
  "size_in_bytes" INT NOT NULL,              -- 邮件大小 (字节)
  "raw_content" TEXT NOT NULL,               -- 邮件的完整原始内容 (MIME格式)
  "search_vector" TSVECTOR,                  -- 用于全文检索的 tsvector
  "remark" VARCHAR(256),
  "creator" VARCHAR(64) DEFAULT '',
  "create_time" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updater" VARCHAR(64) DEFAULT '',
  "update_time" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deleted" SMALLINT DEFAULT 0,
  "tenant_id" BIGINT NOT NULL DEFAULT 0
);

COMMENT ON TABLE mw_mail_message IS '邮件消息表 (支持去重和检索)';
COMMENT ON COLUMN mw_mail_message.content_hash IS '原始内容的SHA-256哈希，用于去重';
COMMENT ON COLUMN mw_mail_message.search_vector IS '全文检索向量';

-- 为常用检索字段创建索引
CREATE INDEX idx_message_subject ON mw_mail_message USING GIN (to_tsvector('simple', subject)); -- 主题搜索
CREATE INDEX idx_message_from ON mw_mail_message(from_address);
-- 为全文检索向量创建 GIN 索引，这是最高效的方式
CREATE INDEX idx_message_search_vector ON mw_mail_message USING GIN (search_vector);

2.4 邮件实例表 (mw_mail)

作为关联表，将一封唯一的邮件消息 (mw_mail_message) 与一个用户的特定邮箱 (mw_mailbox) 关联起来，形成一封用户可见的邮件实例。

设计要点:

• UNIQUE (mailbox_id, uid): 复合唯一键，精确实现了 IMAP 协议中“UID 在单个邮箱内必须唯一”的核心规定。

-- ----------------------------
-- 4. 邮件实例表 (mw_mail)
-- 将一封唯一的邮件消息(mw_mail_message)与一个用户的邮箱(mw_mailbox)关联起来
-- ----------------------------
CREATE TABLE mw_mail (
  "id" BIGINT NOT NULL PRIMARY KEY,
  "user_id" BIGINT NOT NULL,               -- 所属用户ID
  "mailbox_id" BIGINT NOT NULL,            -- 所属邮箱ID
  "message_id" BIGINT NOT NULL,            -- 邮件消息ID (关联 mw_mail_message.id)
  "uid" BIGINT NOT NULL,                   -- IMAP UID，在单个邮箱内唯一
  "internal_date" TIMESTAMP WITH TIME ZONE NOT NULL, -- 服务器内部接收日期
  "remark" VARCHAR(256),
  "creator" VARCHAR(64) DEFAULT '',
  "create_time" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updater" VARCHAR(64) DEFAULT '',
  "update_time" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deleted" SMALLINT DEFAULT 0,
  "tenant_id" BIGINT NOT NULL DEFAULT 0,
  UNIQUE (mailbox_id, uid)
);

COMMENT ON TABLE mw_mail IS '邮件实例表 (关联用户、邮箱和消息)';
COMMENT ON COLUMN mw_mail.uid IS 'IMAP UID，在 mailbox_id 内唯一';

-- 为常用查询创建索引
CREATE INDEX idx_mail_user_id ON mw_mail(user_id);
CREATE INDEX idx_mail_mailbox_id ON mw_mail(mailbox_id);
CREATE INDEX idx_mail_message_id ON mw_mail(message_id);

2.5 邮件标志表 (mw_mail_flag)

采用规范化的多对多关系设计，存储每封邮件实例的 IMAP 标志（如 \Seen, \Answered 等）。

-- ----------------------------
-- 5. 邮件标志表 (mw_mail_flag)
-- 存储邮件的IMAP标志 (多对多关系)
-- ----------------------------
drop table if exists mw_mail_flag;
CREATE TABLE mw_mail_flag (
  "id" bigint primary key,
  "mail_id" BIGINT NOT NULL,
  "flag" VARCHAR(64) NOT NULL,
  UNIQUE(mail_id, flag)
);

COMMENT ON TABLE mw_mail_flag IS '邮件标志关联表';
COMMENT ON COLUMN mw_mail_flag.mail_id IS '邮件ID (关联 mw_mail.id)';
COMMENT ON COLUMN mw_mail_flag.flag IS 'IMAP标志 (e.g., \Seen, \Answered, \Flagged)';

-- 为按标志查询创建索引
CREATE INDEX idx_mail_flag_flag ON mw_mail_flag(flag);

---

3. 初始数据填充 (DML)

执行以下脚本以创建初始用户和邮箱。

重要说明:

• 用户: 创建 user1@tio.com, user2@tio.com, error@tio.com 三个测试用户。
• 密码: 为安全起见，数据库中存储的是经过哈希处理的密码。此脚本中提供的哈希值均对应以下明文密码：
  • user1@tio.com 的密码是: pass1
  • user2@tio.com 的密码是: pass2
  • error@tio.com 的密码是: error
• 邮箱: 为每个新创建的用户，自动生成 Inbox 和 Trash 两个标准邮箱。

-- =================================================================
-- 初始化基础数据
-- =================================================================

WITH new_users AS (
  -- 步骤1: 插入新用户，并提供预先计算好的密码哈希值
  INSERT INTO mw_user (id, username, password_hash, creator, updater, tenant_id) VALUES
  (1001, 'user1@tio.com', '600000$4JRI3BfykBwVVHwSXuUYmA==$qefBA/+M2pcr9o6p4ycojdHNnLAhTs9+7cmSjp664ww=', 'system', 'system', 1), -- 对应密码: pass1
  (1002, 'user2@tio.com', '600000$4JRI3BfykBwVVHwSXuUYmA==$qefBA/+M2pcr9o6p4ycojdHNnLAhTs9+7cmSjp664ww=', 'system', 'system', 1), -- 对应密码: pass2
  (1003, 'error@tio.com', '600000$4JRI3BfykBwVVHwSXuUYmA==$qefBA/+M2pcr9o6p4ycojdHNnLAhTs9+7cmSjp664ww=', 'system', 'system', 1)  -- 对应密码: error
  RETURNING id, username
),
mailbox_names (name) AS (
  -- 步骤2: 定义需要为每个用户创建的邮箱名称列表
  VALUES ('Inbox'), ('Trash')
),
mailboxes_to_create AS (
  -- 步骤3: 使用 CROSS JOIN 生成用户和邮箱名的所有组合，并为每个组合生成唯一的邮箱ID
  SELECT
    -- 动态生成邮箱ID (示例策略)
    (new_users.id * 100 + ROW_NUMBER() OVER (PARTITION BY new_users.id ORDER BY mailbox_names.name)) AS mailbox_id,
    new_users.id AS user_id,
    mailbox_names.name AS mailbox_name
  FROM new_users
  CROSS JOIN mailbox_names
)
-- 步骤4: 插入邮箱数据，并将邮箱自己的ID用作UIDVALIDITY，确保其唯一性
INSERT INTO mw_mailbox (id, user_id, name, uid_validity, creator, updater, tenant_id)
SELECT
  mailbox_id,
  user_id,
  mailbox_name,
  mailbox_id,
  'system',
  'system',
  1
FROM mailboxes_to_create;

---

4. 如何使用

1. 连接数据库: 使用任意 PostgreSQL 客户端连接到您的目标数据库。
2. 执行 DDL: 复制并执行 第 2 节 中的所有 CREATE TABLE 和 COMMENT 语句，以创建表结构。
3. 执行 DML: 复制并执行 第 3 节 中的 INSERT 语句，以填充初始用户和邮箱数据。
4. 验证结果 (可选): 执行以下查询，检查数据是否已成功创建。

SELECT
  u.id AS user_id,
  u.username,
  m.id AS mailbox_id,
  m.name AS mailbox_name,
  m.uid_validity,
  m.uid_next
FROM mw_user u
JOIN mw_mailbox m ON u.id = m.user_id
ORDER BY u.id, m.name;

执行完毕后，您的数据库环境即已准备就绪，可以开始进行 tio-mail-wing 项目的开发工作。