更新站資料表

另有 wrangler 管理的 d1_migrations（已套 0001_init.sql～0004_agent_version_reports.sql）。

D1 的型別慣例（0001_init.sql:1-2）：

• SQLite 沒有陣列型別，陣列欄位存 JSON 文字，以 CHECK (json_valid(...)) 守住；讀取用 json_each。
• 時間一律 ISO 8601 UTC 字串，Worker 以 new Date().toISOString() 寫（帶毫秒與 Z）。
• 布林存 INTEGER 0／1，以 CHECK (x IN (0, 1)) 守住。
• 版號存 TEXT。不可用 SQL 字串比大小（"1.10.0" < "1.9.0"）；排序與比較都在 Worker 用 src/versions.ts 做。

表之間沒有宣告外鍵，都是邏輯關聯：

%%{init: {'theme':'base','themeVariables':{'primaryColor':'#E2F0F1','primaryTextColor':'#14201F','primaryBorderColor':'#0E7C86','secondaryColor':'#EEF2F3','secondaryTextColor':'#14201F','tertiaryColor':'#FBFCFC','tertiaryTextColor':'#14201F','lineColor':'#4A5A5C','textColor':'#14201F','mainBkg':'#E2F0F1','nodeBorder':'#0E7C86','nodeTextColor':'#14201F','edgeLabelBackground':'#FBFCFC','titleColor':'#14201F','clusterBkg':'#FBFCFC','clusterBorder':'#E4EAEB','actorBkg':'#E2F0F1','actorTextColor':'#14201F','actorBorder':'#0E7C86','actorLineColor':'#C7D1D2','signalColor':'#4A5A5C','signalTextColor':'#14201F','labelBoxBkgColor':'#EEF2F3','labelBoxBorderColor':'#C7D1D2','labelTextColor':'#14201F','loopTextColor':'#14201F','noteBkgColor':'#F6EBD5','noteTextColor':'#14201F','noteBorderColor':'#9C6B12','activationBkgColor':'#EEF2F3','activationBorderColor':'#C7D1D2','sequenceNumberColor':'#FFFFFF'}}}%%
erDiagram
  product_releases {
    TEXT version PK
    INTEGER major
    TEXT min_base_version
    TEXT r2_key UK
    TEXT status
    TEXT internal_fingerprints "JSON 陣列"
    TEXT signed_manifest
    TEXT kid
    TEXT min_agent_version
  }
  instance_version_reports {
    TEXT license_id PK
    TEXT fingerprint PK
    TEXT current_version
    TEXT last_upgrade_to
  }
  agent_version_reports {
    TEXT license_id PK
    TEXT agent_fingerprint PK
    TEXT host_fingerprint
    TEXT current_version
    TEXT last_upgrade_to
  }
  release_download_log {
    INTEGER id PK
    TEXT license_id
    TEXT fingerprint
    TEXT version
  }
  license_denylist {
    TEXT license_id PK
    TEXT reason
  }
  signing_keys {
    TEXT kid PK
    TEXT status
  }
  ci_tokens {
    INTEGER id PK
    TEXT name UK
    TEXT token_hash UK
  }
  product_releases ||..o{ release_download_log : "version"
  product_releases ||..o{ instance_version_reports : "version = current_version／last_upgrade_to"
  product_releases ||..o{ agent_version_reports : "Agent 版號，非主系統版號"
  product_releases }o..o{ instance_version_reports : "internal_fingerprints 含 fingerprint"
  signing_keys |o..o{ product_releases : "kid"
  license_denylist |o..o{ instance_version_reports : "license_id"
  instance_version_reports ||..o{ agent_version_reports : "license_id＋host_fingerprint"

注意 agent_version_reports.current_version 是 Agent 自己的版號（例如 1.4.8），不是主系統版號。後台「Agent（在版／退回／失敗）」那欄拿它和 product_releases.version 比對（releaseAdoption，src/releases.ts:370-385），只有兩者版號剛好相同時才會計到數；這是現有統計的語意，看那欄時要知道它比對的是什麼。

用途：一版一列。CI 登記成草稿，決策者蓋章後變已發布，撤回是終點。同一版號只會有一列：重登記草稿是覆蓋不是新增。

表層 CHECK：status <> 'published' OR (signed_manifest IS NOT NULL AND kid IS NOT NULL)（0001:26）——已發布的版一定有簽好的清單與 kid，連直接寫 D1 也繞不過。

索引：idx_product_releases_major_status (major, status)（0001:28），給 visibleReleases 的 WHERE major = ? AND status … 用；另有 PK 與 r2_key UNIQUE 的自動索引。

狀態轉換：

%%{init: {'theme':'base','themeVariables':{'primaryColor':'#E2F0F1','primaryTextColor':'#14201F','primaryBorderColor':'#0E7C86','secondaryColor':'#EEF2F3','secondaryTextColor':'#14201F','tertiaryColor':'#FBFCFC','tertiaryTextColor':'#14201F','lineColor':'#4A5A5C','textColor':'#14201F','mainBkg':'#E2F0F1','nodeBorder':'#0E7C86','nodeTextColor':'#14201F','edgeLabelBackground':'#FBFCFC','titleColor':'#14201F','clusterBkg':'#FBFCFC','clusterBorder':'#E4EAEB','actorBkg':'#E2F0F1','actorTextColor':'#14201F','actorBorder':'#0E7C86','actorLineColor':'#C7D1D2','signalColor':'#4A5A5C','signalTextColor':'#14201F','labelBoxBkgColor':'#EEF2F3','labelBoxBorderColor':'#C7D1D2','labelTextColor':'#14201F','loopTextColor':'#14201F','noteBkgColor':'#F6EBD5','noteTextColor':'#14201F','noteBorderColor':'#9C6B12','activationBkgColor':'#EEF2F3','activationBorderColor':'#C7D1D2','sequenceNumberColor':'#FFFFFF'}}}%%
stateDiagram-v2
  [*] --> draft: register-draft（CI）
  draft --> draft: register-draft 同版號（覆蓋；包換了清內部先裝結果）
  draft --> published: publish（內部先裝全成功）
  published --> published: publish 同版重簽
  draft --> withdrawn: withdraw
  published --> withdrawn: withdraw
  withdrawn --> [*]

現況（2026-11-01）：10 列（1.27.1～1.27.10），全部 published、kid 皆為測試章 b91819f86eb4b4f8、min_base_version 皆 1.0.0、min_agent_version 皆 1.0.0、has_security_fix 皆 0、internal_fingerprints 皆 []、最早 retain_until 為 2036-10-30。

用途：每台客戶主機一列（同一台換過授權會多一列），記「現在幾版、最後一次來問是什麼時候、最近一次升級結果」。刻意沒有客戶名稱欄位：不要順手加 customer_code／issued_to，那會讓更新站變成客戶名單（0001:30-31）。

主鍵：(license_id, fingerprint)（0001:41）。沒有另建索引；internalTargetStatus 依 fingerprint IN (…) 查，筆數小，未建索引。

讀取：internalTargetStatus（發布前核內部先裝）、releaseAdoption（on_version＝current_version 等於該版；rolled_back／failed＝last_upgrade_to 等於該版且結果相符；major_total＝current_version LIKE '<major>.%'）。

現況：3 列——兩台 current_version = 1.27.8、一台 1.27.10，last_upgrade_result 皆 succeeded。

用途：主系統代 Agent 回報升級結果，一台 Agent 一列。Agent 沒有自己的授權，身分是主機的授權。同樣不存客戶名稱與 Agent 主機名稱（0004:2-3）。

主鍵：(license_id, agent_fingerprint)（0004:15），不含 host_fingerprint：Agent 換掛到同授權另一台主機時覆寫同一列，不是多一台。

寫入：只有 /v1/report target=agent 的 recordAgentUpgrade（src/instances.ts:47-68），upsert 全部欄位。current_version 只在成功時寫 to：退回／失敗／略過時 Agent 還停在 from，寫 to 會讓後台把沒升上去的 Agent 算進「在這版」。

讀取：releaseAdoption 的 agent_on_version、agent_rolled_back、agent_failed。

BE 送出端欄位對應：agent_rollout_service._unreported（app/product_update/service/agent_rollout_service.py:428-438）送 agent_fingerprint、result（rollout 列 status）、from_version、to_version（rollout 列 target_version）；送達才寫 reported_at。

現況：2 列——一台 succeeded、current_version = 1.4.8；一台 failed、current_version = 1.4.7（失敗時停在 from，符合上述規則）。

用途：稽核「誰在什麼時候拿了哪一版的下載網址」。只記發出網址；R2 實際有沒有下載完不在這裡（0001:44）。

索引：idx_release_download_log_version (version, at)（0001:52）。

寫入：logDownload（src/instances.ts:70-75），只在 /v1/download-url 回整包網址時；artifact=ops 小檔不記。目前沒有任何端點讀這張表（後台未顯示），供直接查 D1 稽核。

現況：15 列；依版號 1.27.8 四筆、1.27.1 三筆，其餘八版各一筆。

用途：被停止提供更新的授權（D12：由決策者在後台手動加）。在名單上的授權打三個客戶端端點一律 403 license_denied，連有沒有新版都不回。

現況：0 列。

用途：發布印樣（公鑰）的登記表，只給後台與 GET /admin/keys 顯示。更新站不用它驗任何東西：客戶端只信自己機器上 /etc/guidant/release-keys/*.pub，這張表改錯也騙不了客戶端（src/keys.ts:1-3）。私鑰永遠不進這裡。

寫入：upsertKey（src/keys.ts:41-52）。登記前 kidMatches 核 kid 與公鑰對得上；作廢單向：已 revoked 的 kid 再送任何狀態都回 409 kid_revoked。CLI sync-keys 把本機角色對應成站上狀態：主力 → primary、備用 → backup、換鑰中新章 → backup、作廢 → revoked。

讀取：listKeys（依 primary、backup、revoked 排）；publish 端點查「這個 kid 是不是 revoked」，是就 409（只擋作廢、不要求必須登記過）。

現況：0 列（尚未跑過 sync-keys）。

用途：GitLab CI 打 register-draft 的 scoped token。只存 sha256 雜湊，明文只在建立當下回一次（0001:72-73）。

scope 的 CHECK 讓「開一支能打別的端點的 token」在資料庫層就做不到；Worker 驗 token 時也再帶 AND scope = 'register-draft'（src/access.ts:134）。

現況：0 列（CI 自動登記草稿尚未啟用）。

套用：

cd ~/Projects/Billows/Audit-Manager/update-center
npm run db:migrate:local                                        # 本機 env.dev 模擬庫
npx wrangler d1 migrations apply guidant-update-center --remote # 正式庫（首腦做）

wrangler 在 d1_migrations 記已套過的檔名，重跑只套新的。測試由 test/apply-migrations.ts 每次對測試庫套全部 migration。

改 schema 的規則：

• 只加不改：新欄位用 ALTER TABLE … ADD COLUMN，NOT NULL 欄位要給預設值（0002 的寫法），讓既有列直接合法。
• 先套 migration、再部署 Worker。
• 不加客戶名稱類欄位：client.spec.ts「D1 schema 守衛」會擋 instance_version_reports 出現這類欄位，並核六張主表都在。

唯讀查詢範例：

npx wrangler d1 execute guidant-update-center --remote --json \
  --command "SELECT version, status, kid, published_at FROM product_releases"

D1 對 compound SELECT 的項數有上限，多張表計數請用子查詢寫在同一個 SELECT 裡，不要用一長串 UNION ALL。

• 更新站規格：端點、身分、planPath、R2、部署
• 發布簽章規格：signed_manifest 的格式與驗法
• 更新中心 API 規格
• 升級指令規格
• Agent 跟進規格
• 時序圖
• 發版操作手冊
• 設計文件 §5.4