ADR 0018 — 排程信件改走 twork_skd_mailqueue + Mailgun，AutoPilot 與 Notify 職責分離¶

日期：2026-05-09
狀態：Accepted
決策者：使用者

Context¶

兩個彼此牽連的痛點，必須一起解：

痛點 1：AutoPilot SP 想寄結果信，但寄信通道很難談¶

部分客戶把 AutoPilot SP（如 TWORK_MOLD_SENDMAIL）寫成「跑檢查 + 自己用 EXEC msdb.dbo.sp_send_dbmail 寄信」的混合 SP。HTML 樣板用 FOR XML PATH 在 T-SQL 內組得很順手，但每家客戶 SQL Server 都得自己設定 Database Mail（Profile、SMTP、帳密），維運成本高、外連治理也混亂。

痛點 2：Notify 流程被誤用來寄結果信，職責混淆¶

SchedulerJob.NotifyAsync 的本意是「靜態提醒」（在 X 時間有 Y 待辦），body 是事先寫好的純文字。把 AutoPilot 跑出來的動態結果硬塞進這條路徑，要嘛把 Notify 改成知道結果表 schema 的「智慧」流程，要嘛接受信件死板看不到結果。兩者都壞。

ADR 0015 才剛把 SP 拆成兩支單一職責，C# 端兩個 phase（Notify / AutoPilot）也已經是分離的。再把它們混回去等於倒車。

Decision¶

D1：新增 `twork_skd_mailqueue` 資料表 + `DequeueMailAsync` Phase 3¶

SchedulerJob.RunOnceAsync 對每個 DB 從兩階段擴成三階段：

NotifyAsync：靜態提醒（完全不變）
AutoPilotAsync：跑 SP，SP 內部寫結果 + INSERT mailqueue
DequeueMailAsync（新增）：撈 mailqueue → Mailgun → 更新狀態

三個階段各自單一職責，順序也對：AutoPilot 跑完當輪就 enqueue，同一輪 Phase 3 直接寄出，不必等下一輪。

D2：mailqueue 表欄位用中文 + 狀態碼用 N/R/Y/F/D¶

對齊既有 已通知/已完成 的單字代碼風格：

N = 待寄（Pending）
R = 寄送中（Running）
Y = 已寄出（Sent）
F = 失敗可重試（Failed）
D = 死信放棄（Dead Letter）

刻意不用 X，避免重蹈 ADR 0013 的覆轍（當時 X→R 遷移留下 caller 漏改的殭屍判定 bug）。

D3：AutoPilot SP 撰寫規範改為「組信 + 入列」¶

舊範本：

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'qq',
    @recipients   = ...,
    @subject      = @subject,
    @body         = @body,
    @body_format  = N'HTML';

新範本：

INSERT dbo.twork_skd_mailqueue (收件人, 主旨, 內文, 是否HTML, 來源預存程序, 執行guid)
VALUES (@recipients, @subject, @body, 'Y', OBJECT_NAME(@@PROCID), @execguid);

DBA / 顧問不必會 C#、不必懂 Mailgun、不必設定 Database Mail。HTML 組裝（FOR XML PATH 那套）整段保留。

D4：寄信通道仍走既有 `IMailService.SendAsync`，補 `isHtml` 旗標¶

MailGunService 內部把 text 參數換成 html 欄位（Mailgun API 兩個都支援），其他 caller 預設 isHtml=false，行為不變。

D5：兩支新 SP，職責互補¶

SP	職責
`TWORK_SKD_MAILQUEUE_DEQUEUE`	殭屍回收（>10 分鐘 R 回 N）→ 鎖一批標 R → 回傳 result set
`TWORK_SKD_MAILQUEUE_UPDATE_STATUS`	`@QueryType=1` 標 Y、`@QueryType=2` 失敗+1 retry、達 MaxRetry 改 D，並計算下次嘗試時間（指數退避 30/60/120/240/480 秒）

殭屍回收與 dequeue 在同一支 SP 內（單一交易、避免多 worker 與清掃任務搶鎖）。

Consequences¶

Migration¶

對每個目標 DB順序執行：

SqlBI/twork_skd_mailqueue.sql（建表 + index）
SqlBI/TWORK_SKD_MAILQUEUE_DEQUEUE.sql
SqlBI/TWORK_SKD_MAILQUEUE_UPDATE_STATUS.sql
Build & deploy 新版 TsERP.SchedulerWorker
把現存呼叫 sp_send_dbmail 的 AutoPilot SP（如 TWORK_MOLD_SENDMAIL）改寫成 INSERT mailqueue（漸進式，PoC 一支一支來，舊的留著繼續用 Database Mail 不會壞）

Breaking Changes¶

無 breaking change：IMailService.SendAsync 加的是 optional 參數
AutoPilot SP 改寫是選擇性的，舊的 EXEC msdb.dbo.sp_send_dbmail 還能跑（前提是該客戶 DB 已設好 Database Mail）

對使用者影響¶

終端使用者無感：信件還是會收到，內容更豐富（HTML 樣板）
DBA：未來新通知信件不再需要 Database Mail 設定，HTML 組裝改寫 INSERT mailqueue 即可
開發者：測試新的 AutoPilot SP 不必模擬 SMTP，直接看 mailqueue 寫入內容

監控建議¶

SELECT 狀態, COUNT(*) FROM twork_skd_mailqueue GROUP BY 狀態 看待寄/失敗/死信分布
殭屍判定門檻 10 分鐘寫死在 SP 內，必要時改參數化（之後再評估）
DeadLetter（D）需人工檢視 最後錯誤 後決定 retry 還是丟棄

Alternatives Considered¶

(拒絕) 繼續沿用 Database Mail：每家客戶 SMTP 都要設、外連治理散亂。TWORK_MOLD_SENDMAIL 的痛點源頭。
(拒絕) 把結果渲染搬到 C#，HTML 樣板用 Razor / Scriban：HTML 組裝在 SQL 裡用 FOR XML PATH 已經很順手，DBA 寫得動；搬到 C# 反而要學新樣板語言、deploy 才能改樣板，靈活性下降。
(拒絕) 把 mail composition 改成 SP 吐 result set 給 C# 寄：曾在會議中討論過。技術上可行，但會讓 Notify 與 AutoPilot 兩條路徑被迫互相認識（C# 要去呼叫 compose SP），違反 ADR 0015 剛確立的單一職責原則。
(拒絕) 中央集中式 mailqueue（跨 DB 共用一張表）：mailqueue 是 per-DB 的會比較簡單——每個客戶 DB 自己有自己的佇列，SchedulerWorker 用既有的 ChangeParameter 切換 DB 機制就能 dequeue，不需要新增跨 DB 的協調邏輯。
(拒絕) 沿用 NotifyAsync 路徑、把動態結果塞 body：明確違反兩個 phase 的職責分離，已在 D1 / Context 拒絕。