Phase A1 Design — Excel 樣板設計 + 下載

Phase：A1（Track A：Excel 匯入第一階段） 級別：中型（中型 phase — design 段落 + plan，跳過 brainstorm） 狀態：design draft 前置：A0.1 已 ship（ssp_system_implementations 三表結構穩定） 依賴本 phase：A2（Excel Parser）必須拿 A1 樣板對應；A5（預覽 UI Confirm）參考 A1 sheet 結構

Phase 2 目標是把「合規資源庫」9 大資料領域（metadata / 單位 / 人員 / 設備 / 資訊系統 / leveraged / 控制項 / AO / 程序書）打通 Excel 匯入匯出。A1 是入口：

1. 顧問訪談需要把客戶資料用 Excel 帶回填，目前既有 template 只覆蓋「控制項 + AO 現況」一張 sheet，不夠
2. 快照交付：客戶想拿 Excel 看 MF 當前內容，比翻 UI 快
3. A2~A5 的 prerequisite：parser、鉤稽、預覽 UI 都要對齊「樣板長什麼樣」

A1 範圍：產出可下載的 Excel 樣板（blank / filled 兩種），不包含上傳 / parse / 匯入回 MF。

In Scope

• 1 個下載 endpoint，query param mode=blank|filled 切換
• 9 個 sheet 結構（00_說明 + 01~08 資料領域）
• 必填欄位黃底 + enum 下拉 + 既有資料下拉（named range + 隱藏 lookup sheet）
• filled mode：以 MF profile 範圍為基準預填現有資料
• FE：MF 詳細頁加「下載樣板」按鈕 + mode 切換
• 寫入權限檢查（誰可下載）+ tenant 隔離

Out of Scope（後續 phase）

3.1 主下載 endpoint

GET /api/1.0/module-frame/<module_frame_uid>/ssp-import-template?mode=blank|filled

Response：

• Success：HTTP 200，Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet，Content-Disposition: attachment; filename=ssp_template_<mf_short_name>_<mode>_<yyyymmdd>.xlsx
• 404：MF 不存在 → GRC_MODULE_FRAME_NOT_FOUND（沿用既有）
• 403：tenant 越權 / 無讀取權限 → GRC_FORBIDDEN

3.2 權限檢查

• 讀取權限：tenant 內成員即可（不限 manager / auditor），與既有 MF 查看權限對齊
• RLS：依 module_frames 現有 tenant_id 過濾，自動隔離

為什麼下拉資料源也要走 tenant scope：避免使用者下載樣板時看到別 tenant 的 user / device。RLS 已保證。

3.3 Framework-version-scoped 下載 endpoint（T6 新增）

設計動機 — A1 原始設計只覆蓋 S2/S3 場景（MF 已存在後下載 blank/filled）。但顧問實務有 S1 訪談前場景：MF 尚未建立、要先依 framework_version 下載「全 catalog controls 樣板」帶去訪談，回來後再透過 A2 phase 上傳建 MF + profile。

GET /api/1.0/ssp-import-template?framework_version_uid=<uid>&mode=blank&locale=<locale>

Response：

• Success：HTTP 200，xlsx binary，Content-Disposition: attachment; filename=ssp_template_<fw>_<version>_blank_<yyyymmdd>.xlsx
• 400：framework_version_uid 缺 → GRC_TEMPLATE_FRAMEWORK_VERSION_REQUIRED
• 400：mode 不為 blank → GRC_TEMPLATE_INVALID_MODE
• 404：framework_version 不存在 → GRC_FRAMEWORK_VERSION_NOT_FOUND

控制項清單來源差異：

Lookup 差異：

A2 phase 對應計畫：訪談後上傳 Excel → parser 識別 superset → user 在 Excel 內標記「不適用 / 納入 profile」→ 系統依此產生 subset profile + 建 MF。A2 spec 寫此設計時要明確區分「Excel superset upload」vs「MF update upload」兩條 import flow。

4.1 命名規則

• Sheet 名前綴序號（00_ ~ 08_）保證 Excel 開啟時 tab 順序穩定
• 隱藏 lookup sheet 以 _lookup_ 前綴，openpyxl 設 sheet_state="hidden"
• 必填欄位 header 用 PatternFill(fgColor="FFEB9C") 黃底（沿用既有 module_frame_import_template.xlsx 慣例）

4.2 sheet 詳細欄位

00_說明

純說明用 sheet，A2 parser 完全忽略此 sheet。

01_基本資料（Module Frame Metadata）

對應 compliance.module_frames（合規資源庫表本體）

filled mode：直接從 module_frames 取對應 MF 一筆 row 預填；blank mode 留 1 row 空白等使用者填。

設計取捨（T0.1 verify 結論）：MF 表沒有 OSCAL system-characteristics 的 FIPS 199 / authorization_boundary / deployment_model 等欄位 — 那些是 SSP-scoped concept。A1 第一版 01 sheet 對齊 MF 自有欄位即可，OSCAL 完整 system metadata 由 SSP 創建階段 UI 處理，不擠在 Excel 樣板。詳見 §14.5「寧多勿少」也是建議刪掉這些不存在的欄位，避免使用者誤以為可以從 Excel 設定。

02_單位（Parties Type = Organization）

filled：取 MF 既有 oscal_parties (type=organization) + oscal_responsible_parties (context_type='module_frame', context_id=mf.id) 預填

03_參與人員（Parties Type = Person）

filled：取 MF 既有 oscal_parties (type=person) + responsible_parties

04_設備（Devices）

filled：取 MF 對應的 ssp_system_implementation_items (scope_type='module_frame', implementation_type='hardware')。A0.1 已建好結構，A1 透過 system_implementation_main 鉤回 items。

05_資訊系統（Information Systems / OSCAL Components）

filled：取 MF 對應 ssp_system_implementation_items (scope_type='module_frame', implementation_type IN ('component','system','subsystem','service','software'))

06_外部利用服務（Leveraged Authorizations）

filled：取 MF 對應 ssp_system_implementation_items (implementation_type='leveraged-authorization')

07_控制項與AO（Control + Objective Implementations）

核心決策（拍板 B + profile-scoped 範圍）：以 MF 的 profile 範圍列出該 profile 內全部 control（不是整個 catalog 也不是只填過的），每 control 對應其下 AO 各一 row。

filled 預填邏輯（拍板 B）：

1. 從 MF.oscal_profile_uid → profile → 該 profile 的 controls list（透過 jedi-oscal ProfileService / CatalogService）
2. 每 control 一個父 row，control 下的每個 AO 一個子 row（用空白縮排或父 row control_id 留空的方式區分）
3. 已有 module_frame_control_default.implementation_statement 的 control row 預填 statement / impl_status；未填的留空但保留 control_id
4. AO 同邏輯（module_frame_control_objective_default.statement）

filled 預期 row 數：對齊 profile 的 control 數量（CMMC L1 約 17 個 control + 各 control 下 3-5 個 AO ≈ 70-100 row；NIST 800-53 動輒 200+ 則接近 300-500 row）。Excel 可處理。

08_程序書（Reference Document Pool）

filled：取 MF 既有 module_frame_reference_document（已 verify 存在，infra/module_frame/models/module_frame_reference_document.py）

5.1 下拉選單（Data Validation）

from openpyxl.worksheet.datavalidation import DataValidation

# enum 直接列舉（< 255 字元）
dv_status = DataValidation(
    type="list",
    formula1='"implemented,partial,planned,alternative,not_applicable"',
    allow_blank=True,
)
ws.add_data_validation(dv_status)
dv_status.add(f"F2:F{max_row}")  # 對應「實作狀態」欄

# 大量資料用 named range（避開 255 字元上限）
dv_users = DataValidation(type="list", formula1="=_lookup_users", allow_blank=True)
ws.add_data_validation(dv_users)
dv_users.add(f"D2:D{max_row}")

5.2 隱藏 lookup sheet 設計

ws_lookup = wb.create_sheet("_lookup_users")
ws_lookup.sheet_state = "hidden"   # Excel 視為隱藏 sheet
ws_lookup["A1"] = "user_dropdown"   # column header
for i, user in enumerate(users, start=2):
    ws_lookup.cell(row=i, column=1, value=f"{user.nickname} <{user.email}>")

# 建 named range（注意：openpyxl 用 DefinedName）
from openpyxl.workbook.defined_name import DefinedName
wb.defined_names["_lookup_users"] = DefinedName(
    name="_lookup_users",
    attr_text=f"_lookup_users!$A$2:$A${len(users)+1}"
)

6 個 lookup sheet：_lookup_users / _lookup_org_units / _lookup_devices / _lookup_info_systems / _lookup_orgs / _lookup_ref_docs_mf

前 5 個是 tenant scope；_lookup_ref_docs_mf 是 MF own（compliance.module_frame_reference_documents CASCADE FK to MF，沒 tenant-wide pool — T0.4 verify 結果）。

5.3 必填欄位黃底

from openpyxl.styles import PatternFill
HEADER_REQUIRED_FILL = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")

# 每 sheet 第一 row 為 header，依「必填」標記黃底
for col_idx, col_def in enumerate(sheet_def.columns, start=1):
    cell = ws.cell(row=1, column=col_idx, value=col_def.header_i18n[locale])
    if col_def.required:
        cell.fill = HEADER_REQUIRED_FILL

5.4 大檔效能

• tenant 全部 users 上千筆 → lookup sheet 寫入時用 write_only mode 或一次 ws.append(row)，避免 cell-by-cell
• 樣板總大小估算：8 sheet × 平均 100 row + 6 lookup × 1000 row ≈ 7000 cell，openpyxl < 500ms 可產出
• 不用 streaming（write_only=True）because 需要 DataValidation 和 defined_names，這些不支援 write_only mode

6.1 整體流程

ExcelTemplateAppService.generate_filled(module_frame_uid, locale, curr_user)
  │
  │  @transaction
  ▼
  1. 取 MF（domain_service.get_one + tenant check）
  2. 取 profile / framework_version (透過 oscal_profile_uid)
  3. 並行取：
     - parties (oscal_parties + responsible_parties for MF context)
     - devices (ssp_system_implementation_items scope=module_frame, type=hardware)
     - info_systems (同上, type IN component family)
     - leveraged (同上, type=leveraged-authorization)
     - control_defaults (module_frame_control_default by mf_id)
     - ao_defaults (module_frame_control_objective_default by mf_id)
     - ref_docs (module_frame_reference_document by mf_id)
  4. profile-scoped control list (jedi-oscal ProfileService / CatalogService)
  5. 取下拉資料源（tenant scope）：
     - users (jedi_auth user repo, status > -2)
     - org_units (org_unit repo)
     - devices (public.devices)
     - info_systems (compliance.information_systems)
     - reference docs (compliance.reference_documents 若有 tenant pool；或 fallback 用 MF own)
  6. 呼叫 ExcelTemplateGenerator.generate(data_bundle, locale) → BytesIO
  7. return BytesIO + filename

6.2 Blank mode

走同一個 generator path，但跳過 step 3-4（不撈 MF 既有資料），只保留 step 5（下拉資料源仍需 tenant scope）。

6.2.1 Framework-version-scoped blank mode（T6 新增）

走 SspImportTemplateAppService.generate_by_framework_version(framework_version_uid, locale) 路徑：

1. fw_version = oscal_framework_version_domain_service.get_by_uid(uid)
   → None or no catalog → raise NotFound(GRC_FRAMEWORK_VERSION_NOT_FOUND)
2. catalog = fw_version.catalog（eager-loaded）
3. lookups via _fetch_lookups_without_mf()（REF_DOCS_MF 強制 []）
4. controls_with_aos via _build_controls_with_aos_from_catalog(catalog.id)：
   for g in catalog_group_domain_service.get_all(catalog_id=catalog.id):
     for c in catalog_control_domain_service.get_all(group_id=g.id):
       父 row (statement_id=None)
       for ao in catalog_control_assessment_domain_service.get_all(catalog_control_id=c.id):
         子 row (statement_id=str(ao.uid))
5. 父子 row 全 impl_status/statement=None（superset 無 MF defaults）
6. generator.generate(bundle) → bytes
7. filename via _build_filename_by_framework_version(fw_name, version, 'blank')

控制項去重：同 control_id 跨多 group 出現只保留第一次（seen_control_ids set）。

6.3 跨 schema 來源彙整

要 verify 的事：基本資料對 MF 的鉤稽。A0.1 design §2.1 提到 system_characteristics 是 SSP-scoped；MF 是否有對應 system_characteristics？或 MF 自己有獨立 metadata 欄位？這項需在 implementation plan 的 Phase 0 step 補確認。

7.1 Layer

Generator 拆成獨立檔，testable without DB — 給 data bundle dict / dataclass，直接驗 BytesIO 內容。

7.2 DI

# di_containers/module_frame/module_frame_containers.py 加：
excel_template_app_service = providers.Factory(
    ExcelTemplateAppService,
    module_frame_domain_service=module_frame_domain_service,
    party_domain_service=party_domain_service,
    system_implementation_main_domain_service=...,
    system_implementation_item_domain_service=...,
    control_default_domain_service=control_default_domain_service,
    ao_default_domain_service=ao_default_domain_service,
    ref_doc_domain_service=ref_doc_domain_service,
    user_domain_service=user_domain_service,  # tenant scope lookup
    org_unit_domain_service=org_unit_domain_service,
    device_domain_service=device_domain_service,
    info_system_domain_service=info_system_domain_service,
    profile_domain_service=profile_domain_service,
    catalog_domain_service=catalog_domain_service,
)

7.3 i18n

Header 文字走 babel _() 翻譯函式 + zh_Hant_TW / en .po 檔。lookup 值（user nickname / device name）不翻譯，原樣輸出。

按鈕 disable 條件：MF.oscal_profile_uid 為 null（理論上不會，必填）。

寫 implementation plan 前要 verify：

每項在 implementation plan task 0 列具體 grep / read 步驟。

• GET /api/1.0/module-frame/<uid>/ssp-import-template?mode=blank 回傳合法 xlsx，9 個 sheet 結構正確
• ?mode=filled 同樣回傳 9 sheet，MF 既有資料正確預填到對應 sheet
• T6 GET /api/1.0/ssp-import-template?framework_version_uid=<uid>&mode=blank 回傳合法 xlsx，控制項列 framework_version 全 catalog controls（superset）
• T6 framework_version_uid 缺 → 400 GRC_TEMPLATE_FRAMEWORK_VERSION_REQUIRED；mode 非 blank → 400；fw_version 不存在 → 404 GRC_FRAMEWORK_VERSION_NOT_FOUND
• T6 REF_DOCS_MF lookup 在 framework-version-scoped 模式下為空 list
• 7 個 lookup sheet 為 hidden + named range 可被 sheet 內 DataValidation 引用
• 必填欄位 header 黃底
• enum 欄位下拉可選
• tenant 隔離（不同 tenant 下載同 MF 應 403）
• FE 按鈕觸發下載成功，filename 含 mf name / fw name + mode + date
• BE unit test：generator 純函式測試 + app service mock domain 測試 ≥ 15 個 case（T6 後 35+ case）
• BE smoke：起 BE → curl 兩個 endpoint × 各 mode → 都得到 xlsx
• E2E：FE 點按鈕 → 下載 → openpyxl 讀回驗 sheet 數 + 必填 header colour
• Changelog 完成（feat 類）

• [follow-up] 控制項 sheet 父子 row 視覺優化（縮排 / outline / merge）— 第一版用簡單空白標示，使用者反饋再優化
• [follow-up] 大 profile（1000+ controls）的分 sheet 拆解策略
• [follow-up] 樣板版本欄位（00_說明 R1）的後續向後相容機制 — 詳見 §14
• [follow-up] system_characteristic 鉤稽路徑釐清（pre-flight verification 結果）

A1 樣板上線後，未來必然會因為實務反饋調整欄位（加 / 刪 / 改）。這節定義變更類型、成本、與 A2 parser 的相容性 contract。

14.1 設計支持彈性的兩個 anchor

1. sheet_definitions.py 集中定義（§7.1）— 全部欄位 spec 在一個 dataclass tuple，加減改不必散落動 generator / app service / route
2. 樣板版本欄位 00_說明 R1（§4.2）— 標 v<MAJOR>.<MINOR>.<PATCH>，作為 A2 parser 判斷如何解析的 contract anchor

14.2 三種變更類型

14.3 SemVer 規則對齊

14.4 變更落地 SOP

不論哪種變更，落地步驟：

1. 改 sheet_definitions.py 對應 ColumnDef / SheetDef
2. 改樣板版本字串（generator 內 TEMPLATE_VERSION = "v1.x.x" 常數）
3. i18n — 新 header / enum value 補 .po 翻譯（zh_Hant_TW + en）
4. A2 parser 同步（A2 上線後才有此步）：
   • PATCH：跳過
   • MINOR：parser unit test 加新欄位的解析 case
   • MAJOR：parser 增分支處理 v1 / v2，舊版打 deprecation log 或拒絕
5. changelog 註明版號 bump — 新增一條 feat 或 tweak，模組 [ssp-import-template]
6. 使用者通知（MAJOR only）— 上線前知會顧問端，避免拿舊樣板填資料卡關

14.5 第一版的建議策略

• 寧多勿少：A1 第一版欄位可以開稍寬（如所有 OSCAL 標準欄位都列出，部分設 optional）
• 不知道要不要的設 optional + 不下拉：灰色低調存在，使用者不填也不會影響匯入
• 收使用者反饋再下版刪：minor bump 刪 optional 欄位成本低
• 避免過度設計：不要為了「將來可能不用」現在不放某欄位 — 將來真的不用就刪（minor bump）

14.6 不在本期支持

• ❌ 樣板版本自動偵測升級（A2 parser 自動把 v1 樣板轉 v2 結構）— 太複雜，要 user 重下
• ❌ 多版本並存 endpoint（?template_version=v1）— YAGNI，第一版單版號即可
• ❌ Sheet 順序變動（拆 sheet / 合 sheet）視為 MAJOR，第一版不準備此情境

14.7 與 A2 parser 的 handover contract

A2 parser 開工時必須做的事（implementation-plan-A2 會涵蓋）：

1. 讀 00_說明 R1 取樣板版本字串
2. 比對 parser 支援的版本範圍（如 >=1.0.0, <2.0.0）
3. 不支援 → 拋 BadRequestError + 提示重下對應版本
4. 支援 → 走對應分支解析

下一步：implementation-plan-A1.md 已產出，準備進 Task 0 (Pre-flight Verification)。

實作過程中對原始 design 的偏離 / 擴增紀錄（保留決策軌跡）。

15.3 03_參與人員 role enum 跨域不一致（2026-05-19，A2 phase 必看）

Finding：T6 fix2-C 把 SHEET_PERSONS role enum 從 OSCAL 字串改 ParticipantRole 4 值（manager/reviewer/auditor/viewer），但這暴露了既有跨 domain 不一致問題：

SSP 概念（OSCAL）                      GRC 概念（系統權限）
─────────────                         ─────────────
oscal_responsible_parties              project_participants
.role_id (free string)                 .role (ParticipantRole enum)
   ↑                                       ↑
   docx import 寫入                       project 建立 / 編輯時寫入
   無 validation / normalize              強制 4 值之一

現況問題：

1. module_frame_write_strategy.py:303 + ssp_write_strategy.py:316 直接吃 parsed.role or "" 寫入，無 enum validation
2. dev DB oscal_responsible_parties.role_id 可能是雜亂混合：OSCAL 字串 / 中文 / 隨便填
3. SSP 匯入的 person 跟 GRC project_participant 兩條獨立資料流，沒 mapping

A2 parser contract（必做）：

• Excel 03_參與人員 row 的 role 欄位匯入時 強制 enum validation：值必須是 manager / reviewer / auditor / viewer 之一
• 不在 enum 內 → 報錯（或 A2 預覽 UI 提示 user 修正）
• 寫入 oscal_responsible_parties.role_id 仍為字串，但保證內容對齊系統 enum

未來考量（不在 A2 範圍，更大設計議題）：

• OSCAL party ↔︎ project_participant 雙寫機制
• 既有 docx parser 補 role normalize / validate（mapping OSCAL 字串 → ParticipantRole）
• dev DB 髒資料清理（看 audit 結果）

對應 issue：docs/issues/pending/2026-05-19-person-role-cross-domain-inconsistency.md

15.2 T6 fix — UX polish 5 項（2026-05-19）

偏離項：T6 BE+FE shipped 後使用者反饋 5 點 UX 問題，1 輪 polish 解決。

TEMPLATE_VERSION bump v1.0.0 → v1.1.0：lookup sheet 多欄擴增（parser 介面不變仍兼容）+ 08 sheet 刪欄位（pre-prod 可接受）。

對 A2 parser 影響：

• Hidden columns 行為對 parser 透明 — 仍是 column，仍可讀
• Autofill 公式：openpyxl load_workbook(data_only=True) 讀 cached value；以 cell 純值為主，不解析公式
• 08 sheet 少 3 欄 — A2 parser 直接對齊新 schema

15.1 T6 — 新增 framework-version-scoped endpoint（2026-05-19）

偏離項：原始 §3 只設計 MF-scoped 一個 endpoint，T6 實作時擴增為兩個 endpoint。

驅動原因：FE 整合 T6 時釐清使用者實際工作流程，發現顧問訪談前場景（S1）需要未綁定 MF 的樣板下載。原始 design 假設「先有 MF 才下載」不符實務。

改動範圍：

• 新 endpoint GET /api/1.0/ssp-import-template?framework_version_uid=&mode=blank
• TemplateDataBundle.mf_uid / mf_name 改 Optional[str]（無 MF context 時為 None）
• 00_說明 sheet 在 mf_uid/mf_name None 時顯示「—」
• App service 加 generate_by_framework_version method
• 加 helper _fetch_lookups_without_mf (REF_DOCS_MF 空 list) + _build_controls_with_aos_from_catalog (catalog 全 controls superset) + _build_filename_by_framework_version
• DI 加 4 個新 dep (oscal_framework_version + catalog_group + catalog_control + catalog_control_assessment)
• error code 加 GRC_FRAMEWORK_VERSION_NOT_FOUND (404032) + GRC_TEMPLATE_FRAMEWORK_VERSION_REQUIRED (400064)
• 11 個新 unit test

對 A2 phase 的影響：A2 parser 必須區分兩條 import flow：

1. Superset upload（顧問訪談後上傳 framework_version-scoped 樣板）→ 建新 MF + 從 superset 篩 subset 為 profile
2. MF update upload（既有 MF 下載 filled 後上傳）→ 更新既有 MF（既有設計）

A2 implementation plan 必須涵蓋這兩條 path（建議 Excel 樣板 07 sheet 加「是否納入 profile」欄位給顧問標記，或 row 留空判定為不納入）。