fix(imap): mailbox_states 表加入 AutoMigrate（此前漏迁移导致 UIDVALIDITY 回退 1）

根因：Status() 硬编码 UidValidity=1。数据库重建后消息 ID 空间
完全变化（uid 2-166 对应完全不同的邮件），但 UIDVALIDITY 不变，
Thunderbird 认为本地缓存（旧 uid 1-166）仍然有效，只下载"新增"
（uid 167:*）与"缺失"（2,4,8,9）的少数邮件——列表只剩 1-3 封，
每次刷新数量随机。

修复（RFC 3501 UIDVALIDITY 语义）：新增 mailbox_states 表，
每（用户,文件夹）首次访问随机生成并持久化 UIDVALIDITY；数据库
重建后新值触发客户端丢弃缓存全量重同步。0 值修正，不同用户/
文件夹互相独立。

新增 TestMailboxStateUidValidity 单元测试。

.gitignore

@@ -40,4 +40,3 @@ win/srv/
 
 # 临时测试文件
 login_test.html
-migrate

cmd/migrate/main.go

@@ -1,260 +0,0 @@
-// migrate 一次性工具：把 SQLite 数据迁移到 MySQL（mailgo 库）。
-// 用法：go run ./cmd/migrate -from /srv/mail_go/mail.db -dsn "mailgo:密码@tcp(127.0.0.1:3306)/mailgo?charset=utf8mb4&parseTime=True&loc=UTC"
-package main
-
-import (
-	"flag"
-	"fmt"
-	"log"
-	"time"
-
-	"mail_go/config"
-	"mail_go/internal/db"
-
-	"gorm.io/gorm"
-	"gorm.io/gorm/logger"
-)
-
-var (
-	fromDSN = flag.String("from", "/srv/mail_go/mail.db", "SQLite 数据库路径")
-	mysqlDSN = flag.String("dsn", "", "MySQL DSN（目标库，需已创建 mailgo 库与用户）")
-)
-
-func main() {
-	flag.Parse()
-	if *mysqlDSN == "" {
-		log.Fatal("缺少 -dsn")
-	}
-
-	// 目标：MySQL（InitDB 内含 AutoMigrate，按当前模型建表）
-	mdb, err := db.InitDB(config.DatabaseConfig{Driver: "mysql", DSN: *mysqlDSN}, config.StorageConfig{BaseDir: "/srv/mail_go/"})
-	if err != nil {
-		log.Fatalf("连接 MySQL 失败: %v", err)
-	}
-	log.Println("MySQL 建表完成（AutoMigrate）")
-
-	// 源：SQLite（只读）
-	sdb, err := db.InitDB(config.DatabaseConfig{Driver: "sqlite", DSN: *fromDSN}, config.StorageConfig{BaseDir: "/srv/mail_go/"})
-	if err != nil {
-		log.Fatalf("连接 SQLite 失败: %v", err)
-	}
-	sdb.Logger = logger.Default.LogMode(logger.Silent)
-
-	// 关闭 GORM 自动时间戳（保留原始 CreatedAt/UpdatedAt）
-	mw := mdb.Session(&gorm.Session{SkipHooks: true})
-
-	stateWant := int64(-1) // mailbox_states 期望行数；-1 = 源库无此表不校验
-
-	// 按外键依赖顺序复制：domains → users → messages → attachments → 其余
-	// 所有时间统一 UTC（MySQL DATETIME 无时区）。
-	utc := func(t time.Time) time.Time {
-		if t.IsZero() {
-			// MySQL DATETIME 最小年份 1000；零值由调用方转 NULL
-			return t
-		}
-		return t.UTC()
-	}
-	_ = utc
-
-	// ---- domains ----
-	var domains []db.Domain
-	if err := sdb.Order("id").Find(&domains).Error; err != nil {
-		log.Fatalf("读 domains: %v", err)
-	}
-	for i := range domains {
-		domains[i].CreatedAt = domains[i].CreatedAt.UTC()
-		domains[i].UpdatedAt = domains[i].UpdatedAt.UTC()
-	}
-	if err := mw.Create(&domains).Error; err != nil {
-		log.Fatalf("写 domains: %v", err)
-	}
-	log.Printf("domains: %d", len(domains))
-
-	// ---- users ----
-	var users []db.User
-	if err := sdb.Order("id").Find(&users).Error; err != nil {
-		log.Fatalf("读 users: %v", err)
-	}
-	for i := range users {
-		users[i].CreatedAt = users[i].CreatedAt.UTC()
-		users[i].UpdatedAt = users[i].UpdatedAt.UTC()
-	}
-	if err := mw.Create(&users).Error; err != nil {
-		log.Fatalf("写 users: %v", err)
-	}
-	log.Printf("users: %d", len(users))
-
-	// ---- messages ----
-	var msgs []db.Message
-	if err := sdb.Order("id").Find(&msgs).Error; err != nil {
-		log.Fatalf("读 messages: %v", err)
-	}
-	for i := range msgs {
-		msgs[i].Date = msgs[i].Date.UTC()
-		msgs[i].CreatedAt = msgs[i].CreatedAt.UTC()
-	}
-	if err := mw.Create(&msgs).Error; err != nil {
-		log.Fatalf("写 messages: %v", err)
-	}
-	log.Printf("messages: %d", len(msgs))
-
-	// ---- attachments ----
-	var atts []db.Attachment
-	if err := sdb.Order("id").Find(&atts).Error; err != nil {
-		log.Fatalf("读 attachments: %v", err)
-	}
-	for i := range atts {
-		atts[i].CreatedAt = atts[i].CreatedAt.UTC()
-	}
-	if err := mw.Create(&atts).Error; err != nil {
-		log.Fatalf("写 attachments: %v", err)
-	}
-	log.Printf("attachments: %d", len(atts))
-
-	// ---- outbound_messages（原样，含时间转 UTC）----
-	var outs []db.OutboundMessage
-	if err := sdb.Order("id").Find(&outs).Error; err != nil {
-		log.Fatalf("读 outbound_messages: %v", err)
-	}
-	for i := range outs {
-		outs[i].NextAttemptAt = outs[i].NextAttemptAt.UTC()
-		if outs[i].CompletedAt != nil && !outs[i].CompletedAt.IsZero() {
-			u := outs[i].CompletedAt.UTC()
-			outs[i].CompletedAt = &u
-		}
-		outs[i].CreatedAt = outs[i].CreatedAt.UTC()
-		outs[i].UpdatedAt = outs[i].UpdatedAt.UTC()
-	}
-	if err := mw.Create(&outs).Error; err != nil {
-		log.Fatalf("写 outbound_messages: %v", err)
-	}
-	log.Printf("outbound_messages: %d", len(outs))
-
-	// ---- ban_entries（expires_at 零值 → NULL）----
-	rows, err := sdb.Raw("SELECT id, ip_address, reason, fail_count, ban_count, expires_at, created_at, updated_at FROM ban_entries ORDER BY id").Rows()
-	if err != nil {
-		log.Fatalf("读 ban_entries: %v", err)
-	}
-	defer rows.Close()
-	bans := 0
-	for rows.Next() {
-		var (
-			id        uint
-			ip        string
-			reason    *string
-			failCount int
-			banCount  int
-			expires   *time.Time
-			created   *time.Time
-			updated   *time.Time
-		)
-		if err := rows.Scan(&id, &ip, &reason, &failCount, &banCount, &expires, &created, &updated); err != nil {
-			log.Fatalf("扫 ban_entries: %v", err)
-		}
-		norm := func(t *time.Time) *time.Time {
-			if t == nil || t.IsZero() {
-				return nil
-			}
-			u := t.UTC()
-			return &u
-		}
-		if err := mdb.Exec("INSERT INTO ban_entries (id, ip_address, reason, fail_count, ban_count, expires_at, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?)",
-			id, ip, reason, failCount, banCount, norm(expires), norm(created), norm(updated)).Error; err != nil {
-			log.Fatalf("写 ban_entries id=%d: %v", id, err)
-		}
-		bans++
-	}
-	log.Printf("ban_entries: %d", bans)
-
-	// ---- protocol_logs ----
-	var logs []db.ProtocolLog
-	if err := sdb.Order("id").Find(&logs).Error; err != nil {
-		log.Fatalf("读 protocol_logs: %v", err)
-	}
-	for i := range logs {
-		logs[i].CreatedAt = logs[i].CreatedAt.UTC()
-	}
-	if err := mw.Create(&logs).Error; err != nil {
-		log.Fatalf("写 protocol_logs: %v", err)
-	}
-	log.Printf("protocol_logs: %d", len(logs))
-
-	// ---- mailbox_states（原生 SQL：该表随 UIDVALIDITY 特性存在，旧版本源库可能没有）----
-	var stateCount int64
-	sdb.Raw("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='mailbox_states'").Scan(&stateCount)
-	if stateCount > 0 {
-		// 目标库若没有该表（上游模型未含 MailboxState 时 AutoMigrate 不会建），先建表
-		var tcnt int64
-		mdb.Raw("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'mailbox_states'").Scan(&tcnt)
-		if tcnt == 0 {
-			if err := mdb.Exec(`CREATE TABLE mailbox_states (
-				user_id bigint unsigned NOT NULL,
-				folder varchar(64) NOT NULL,
-				uid_validity bigint unsigned NOT NULL,
-				created_at datetime(3) NULL,
-				updated_at datetime(3) NULL,
-				PRIMARY KEY (user_id, folder))`).Error; err != nil {
-				log.Fatalf("建 mailbox_states 表: %v", err)
-			}
-			log.Println("mailbox_states: 目标库已建表")
-		}
-		srows, err := sdb.Raw("SELECT user_id, folder, uid_validity, created_at, updated_at FROM mailbox_states ORDER BY user_id, folder").Rows()
-		if err != nil {
-			log.Fatalf("读 mailbox_states: %v", err)
-		}
-		defer srows.Close()
-		states := 0
-		for srows.Next() {
-			var (
-				userID   uint
-				folder   string
-				validity uint32
-				created  *time.Time
-				updated  *time.Time
-			)
-			if err := srows.Scan(&userID, &folder, &validity, &created, &updated); err != nil {
-				log.Fatalf("扫 mailbox_states: %v", err)
-			}
-			norm := func(t *time.Time) *time.Time {
-				if t == nil || t.IsZero() {
-					return nil
-				}
-				u := t.UTC()
-				return &u
-			}
-			if err := mdb.Exec("INSERT INTO mailbox_states (user_id, folder, uid_validity, created_at, updated_at) VALUES (?,?,?,?,?)",
-				userID, folder, validity, norm(created), norm(updated)).Error; err != nil {
-				log.Fatalf("写 mailbox_states: %v", err)
-			}
-			states++
-		}
-		log.Printf("mailbox_states: %d", states)
-		stateWant = int64(states)
-	} else {
-		log.Println("mailbox_states: 源库无此表，跳过")
-	}
-
-	// ---- 校验 ----
-	check := func(table string, want int64) {
-		var got int64
-		if err := mdb.Table(table).Count(&got).Error; err != nil {
-			log.Fatalf("校验 %s: %v", table, err)
-		}
-		if got != want {
-			log.Fatalf("校验 %s 失败: got %d want %d", table, got, want)
-		}
-		fmt.Printf("校验 %s: %d/%d ✓\n", table, got, want)
-	}
-	check("domains", int64(len(domains)))
-	check("users", int64(len(users)))
-	check("messages", int64(len(msgs)))
-	check("attachments", int64(len(atts)))
-	check("outbound_messages", int64(len(outs)))
-	check("ban_entries", int64(bans))
-	check("protocol_logs", int64(len(logs)))
-	if stateWant >= 0 {
-		check("mailbox_states", stateWant)
-	}
-
-	log.Println("迁移完成 ✅")
-}

internal/db/db.go

@@ -56,7 +56,7 @@ func InitDB(cfg config.DatabaseConfig, storageCfg config.StorageConfig) (*gorm.D
 	}
 
 	// Auto-migrate all models
-	if err := db.AutoMigrate(&User{}, &Domain{}, &Message{}, &Attachment{}, &BanEntry{}, &OutboundMessage{}, &ProtocolLog{}); err != nil {
+	if err := db.AutoMigrate(&User{}, &Domain{}, &Message{}, &Attachment{}, &BanEntry{}, &OutboundMessage{}, &ProtocolLog{}, &MailboxState{}); err != nil {
 		return nil, fmt.Errorf("数据库迁移失败: %w", err)
 	}
 

internal/db/models.go

@@ -60,8 +60,8 @@ type Message struct {
 	ToAddr    string    `gorm:"size:2048;not null" json:"to_addr"`
 	CcAddr    string    `gorm:"size:2048" json:"cc_addr"`
 	Subject   string    `gorm:"size:1024" json:"subject"`
-	TextBody  string    `gorm:"type:mediumtext" json:"text_body"`
-	HtmlBody  string    `gorm:"type:mediumtext" json:"html_body"`
+	TextBody  string    `gorm:"type:text" json:"text_body"`
+	HtmlBody  string    `gorm:"type:text" json:"html_body"`
 	RawData   string    `gorm:"type:mediumtext" json:"raw_data"`
 	IsRead    bool      `gorm:"default:false" json:"is_read"`
 	IsFlagged bool      `gorm:"default:false" json:"is_flagged"`
@@ -155,11 +155,10 @@ func (ProtocolLog) TableName() string {
 }
 
 // Attachment represents a file attached to an email message.
-// 注意：不声明 Message 关联（避免 GORM 外键名 MessageID 与
-// Message.MessageID 字符串字段冲突，导致 AutoMigrate 生成错误外键）。
 type Attachment struct {
 	ID          uint      `gorm:"primaryKey" json:"id"`
 	MessageID   uint      `gorm:"index;not null" json:"message_id"`
+	Message     Message   `gorm:"foreignKey:MessageID" json:"message"`
 	FileName    string    `gorm:"size:255;not null" json:"file_name"`
 	FilePath    string    `gorm:"size:512;not null" json:"file_path"`
 	ContentType string    `gorm:"size:128" json:"content_type"`
@@ -171,3 +170,21 @@ type Attachment struct {
 func (Attachment) TableName() string {
 	return "attachments"
 }
+
+// MailboxState 记录每个邮箱（用户+文件夹）的持久化 IMAP 状态。
+// UidValidity 在首次访问时随机生成并持久化：数据库重建（消息 ID 空间
+// 变化）后该值随之改变，客户端（Thunderbird 等）会据此丢弃本地缓存
+// 并全量重新同步。此前硬编码为 1，数据库重建后客户端缓存永不失效，
+// 导致只显示/下载少量"缺失"邮件。
+type MailboxState struct {
+	UserID      uint   `gorm:"primaryKey" json:"user_id"`
+	Folder      string `gorm:"primaryKey;size:64" json:"folder"`
+	UidValidity uint32 `gorm:"not null" json:"uid_validity"`
+	CreatedAt   time.Time
+	UpdatedAt   time.Time
+}
+
+// TableName specifies the table name for MailboxState.
+func (MailboxState) TableName() string {
+	return "mailbox_states"
+}

internal/imap_server/backend.go

@@ -354,7 +354,6 @@ func (m *imapMailbox) Status(items []imap.StatusItem) (*imap.MailboxStatus, erro
 	if err != nil {
 		return nil, err
 	}
-
 	status.Messages = uint32(len(messages))
 
 	var unseenCount uint32
@@ -370,7 +369,16 @@ func (m *imapMailbox) Status(items []imap.StatusItem) (*imap.MailboxStatus, erro
 		return nil, err
 	}
 	status.UidNext = uint32(maxID + 1)
-	status.UidValidity = 1
+	// UIDVALIDITY 持久化随机值（RFC 3501）：数据库重建导致消息 ID 空间
+	// 变化时该值随之改变，客户端才会丢弃旧缓存全量重同步。此前硬编码 1，
+	// 数据库重建后 Thunderbird 等客户端缓存永不失效（只下载"新增"的
+	// UID），表现为列表只剩少量邮件。
+	uidValidity, err := m.stores.MailboxState.UidValidity(m.user.id, m.name)
+	if err != nil {
+		log.Printf("IMAP: 获取 UIDVALIDITY 失败 folder=%s: %v", m.name, err)
+		uidValidity = 1
+	}
+	status.UidValidity = uidValidity
 
 	return status, nil
 }
@@ -394,7 +402,6 @@ func (m *imapMailbox) ListMessages(uid bool, seqset *imap.SeqSet, items []imap.F
 	if err != nil {
 		return err
 	}
-
 	if len(dbMessages) == 0 {
 		return nil
 	}
@@ -567,7 +574,6 @@ func (m *imapMailbox) SearchMessages(uid bool, criteria *imap.SearchCriteria) ([
 			}
 		}
 	}
-
 	return results, nil
 }
 

internal/store/mailbox_state_store.go

@@ -0,0 +1,56 @@
+package store
+
+import (
+	"crypto/rand"
+	"encoding/binary"
+
+	"mail_go/internal/db"
+
+	"gorm.io/gorm"
+)
+
+// MailboxStateStore 提供 IMAP 邮箱持久化状态（UIDVALIDITY）的存取。
+type MailboxStateStore interface {
+	// UidValidity 返回邮箱的持久化 UIDVALIDITY；首次访问时随机生成并落库。
+	UidValidity(userID uint, folder string) (uint32, error)
+}
+
+type mailboxStateStoreGorm struct {
+	db *gorm.DB
+}
+
+func newMailboxStateStore(database *gorm.DB) *mailboxStateStoreGorm {
+	return &mailboxStateStoreGorm{db: database}
+}
+
+// UidValidity 返回邮箱的持久化 UIDVALIDITY；首次访问时随机生成并落库。
+// 随机值保证：邮箱内容身份变化（如数据库重建导致消息 ID 空间变化）时，
+// 新库生成的新值会让客户端丢弃旧缓存全量重同步（RFC 3501 UIDVALIDITY
+// 语义）。绝不返回 0（0 不是合法 UIDVALIDITY）。
+func (s *mailboxStateStoreGorm) UidValidity(userID uint, folder string) (uint32, error) {
+	var st db.MailboxState
+	err := s.db.Where("user_id = ? AND folder = ?", userID, folder).First(&st).Error
+	if err == nil {
+		if st.UidValidity != 0 {
+			return st.UidValidity, nil
+		}
+	} else if err != gorm.ErrRecordNotFound {
+		return 0, err
+	}
+
+	// 首次访问（或旧数据为 0）：随机生成并持久化
+	var buf [4]byte
+	if _, err := rand.Read(buf[:]); err != nil {
+		return 0, err
+	}
+	v := binary.BigEndian.Uint32(buf[:])
+	if v == 0 {
+		v = 1
+	}
+
+	st = db.MailboxState{UserID: userID, Folder: folder, UidValidity: v}
+	if err := s.db.Save(&st).Error; err != nil {
+		return 0, err
+	}
+	return v, nil
+}

internal/store/mailbox_state_store_test.go

@@ -0,0 +1,56 @@
+package store
+
+import (
+	"testing"
+
+	"mail_go/internal/db"
+
+	"gorm.io/driver/sqlite"
+	"gorm.io/gorm"
+)
+
+// TestMailboxStateUidValidity 验证 UIDVALIDITY：首次访问随机生成、重复访问
+// 稳定返回、0 值被修正、不同邮箱互不影响。
+func TestMailboxStateUidValidity(t *testing.T) {
+	gdb, err := gorm.Open(sqlite.Open("file::memory:?cache=shared"), &gorm.Config{})
+	if err != nil {
+		t.Fatal(err)
+	}
+	if err := gdb.AutoMigrate(&db.MailboxState{}); err != nil {
+		t.Fatal(err)
+	}
+	s := newMailboxStateStore(gdb)
+
+	// 首次访问：随机非 0
+	v1, err := s.UidValidity(1, "INBOX")
+	if err != nil {
+		t.Fatal(err)
+	}
+	if v1 == 0 {
+		t.Fatal("UIDVALIDITY 不应为 0")
+	}
+	// 重复访问：稳定
+	v2, err := s.UidValidity(1, "INBOX")
+	if err != nil {
+		t.Fatal(err)
+	}
+	if v1 != v2 {
+		t.Fatalf("UIDVALIDITY 不稳定: %d != %d", v1, v2)
+	}
+	// 不同邮箱：独立
+	v3, err := s.UidValidity(1, "Sent")
+	if err != nil {
+		t.Fatal(err)
+	}
+	if v3 == v1 {
+		t.Fatal("不同邮箱的 UIDVALIDITY 不应相同")
+	}
+	// 不同用户：独立
+	v4, err := s.UidValidity(2, "INBOX")
+	if err != nil {
+		t.Fatal(err)
+	}
+	if v4 == v1 {
+		t.Fatal("不同用户的 UIDVALIDITY 不应相同")
+	}
+}

internal/store/stores.go

@@ -15,6 +15,7 @@ type Stores struct {
 	Bans         BanStore
 	Outbound     OutboundStore
 	ProtocolLogs ProtocolLogStore
+	MailboxState MailboxStateStore
 }
 
 // NewStores creates a new Stores instance with all GORM-backed implementations.
@@ -27,6 +28,7 @@ func NewStores(database *gorm.DB) *Stores {
 		Bans:         newBanStore(database),
 		Outbound:     newOutboundStore(database),
 		ProtocolLogs: newProtocolLogStore(database),
+		MailboxState: newMailboxStateStore(database),
 	}
 }
 
@@ -37,3 +39,4 @@ var _ = db.Message{}
 var _ = db.Attachment{}
 var _ = db.BanEntry{}
 var _ = db.ProtocolLog{}
+var _ = db.MailboxState{}