HANDOFF — OSCAL v1.2.2 關聯式 Schema 全新設計（FR-037）

給審查者/下個 session：這份 handoff 自包含。先讀 §2（鎖定規則）→ §4（判斷點，重點審這裡）→ §5（待決策）。SQL 檔本身每表每欄都有中文 COMMENT。

把 OSCAL v1.2.2 八大 model 從巢狀 JSON 攤平成 PostgreSQL 關聯式 schema，全寫進單一檔 oscal-catalog-and-roots-schema.sql（schema 命名空間 oscal）。45 表涵蓋：共用核心、完整 Catalog 樹、8 root、Profile imports、SSP 完整子樹、Component-Def、AP、AR、POA&M、Mapping。深層宣告式/leaf 結構依判準留 JSONB。必填/選填全部對著官方 v1.2.2 JSON Schema 的 required 陣列逐欄校對（非憑記憶）。

這是純設計產物，沒碰任何產品 code，沒 commit。 不要當成已實作。

1.1 主檔（repo 內、未追蹤）

docs/features/FR-037-2606-oscal-schema-gap-audit/oscal-catalog-and-roots-schema.sql

• 單一完整檔，dbdiagram.io 一次匯入。
• SECTION A（共用核心）/ B（Catalog 樹）/ C（8 root）/ C2（profile_imports）/ D（SSP）/ E（Component-Def）/ F（AP）/ G+G2（AR + assessment-common）/ H（POA&M）/ I（Mapping）。

1.2 官方 schema（repo 內、未追蹤）

docs/reference/oscal_model_schemas/ — 本次補齊到 8 份（新增 component/poam/mapping，從 OSCAL v1.2.2 release 下載）。

1.3 OSCAL skill 更新（~/.claude/skills/oscal-knowledge/，非版控）

• references/json-schemas/ — 8 份原始 v1.2.2 JSON Schema。
• references/model-schemas/<model>-outline.md — 8 份逐欄 outline（required/cardinality/說明，程式化從 schema 產出，anyOf/allOf 分支已併入）+ README.md + gen_oscal_outlines.py（可重產）。
• SKILL.md 頂端新增「⭐ 精確 Schema / 必填欄位」索引段。

1.4 已棄用 / 不要混淆

• oscal-relational-schema-shared-and-catalog.sql（同夾，舊版藍本，本次未動）— 設計慣例與新檔不同，以新檔 oscal-catalog-and-roots-schema.sql 為準。

1. PK 一律 id，BIGSERIAL，autoincrement。鐵律。
2. OSCAL token id（NCName，如 ac-2）存成 {model}_id（control_id/group_id/part_id/param_id/role_id）。只有 id 前綴；name/class 等其他欄維持 OSCAL 原名。Root 帶 uuid（非 token）。
3. 每個 FK 指向目標表 id（serial PK），不指 token。
4. Audit mixin：created_at/updated_at/created_user/updated_user，每表都有。
5. 保留字：OSCAL class → DB "class"（加引號）；values/select/end 等保留字改名或加引號（param_values、"end"）。OSCAL metadata → ORM attr metadata_，FK 欄 metadata_id。
6. JSONB vs 關聯化判準：「會不會對它內部欄位做 WHERE/JOIN/FK/約束/排序聚合？」會→表；不會→JSONB。
   • props/links → JSONB（在 owner 上）。⚠️ 文件 root 沒有 props/links（它們在 metadata + 子物件上）；resource 有 props 但無 links（用 rlinks）。
   • parts → 關聯化（被 evidence/SSP/AP 綁定）。
7. 必填鐵則：OSCAL required [1]/[1..*] → 欄位 NOT NULL；選填 → nullable。系統/結構/產品欄（id/FK/sort_order/status/audit）的 NOT NULL 與 OSCAL 無關，註解標明。
8. 參照原則（重要）：OSCAL 的 href/uuid 參照（指向別的物件）→ 關聯式換成 FK 指 target.id，不存 href/uuid 字串（匯出時由 target.uuid 衍生 #uuid）。三種情況：
   • 指向「本 DB 內」物件 → FK（import_profile_id、by-component.component-uuid→component_id…）。
   • 指向「別份文件 / 跨框架 / 本 DB 外」→ 保留 token/href text（control-id、statement-id、mapping source/target、source_resource_href…）。
   • 物件「自己的」uuid（身分，匯出要用）→ 存（component.uuid、statement.uuid…）。
   • party-uuid 例外：依政策維持軟式 uuid（不建 FK，因到處被引用）。

catalogs 同時列在 B 與 C（同一張表），故獨立表數 = 45。

AO（Assessment Object）定案：catalog 層 AO = catalog_control_parts 中 name IN (assessment-objective, assessment-method, assessment-objects, objective) 的列（OSCAL 把 AO 建模為 part，無獨立 assembly）。專案 runtime AO 屬 AP 層（objectives-and-methods，ap_local_objectives）。idx_catalog_parts_ao (catalog_control_id, name) 供快查。

4.1 user 在過程中抓到、已修正的錯（值得審查者複驗是否真修對）

• props/links 誤加在 8 個 root：OSCAL root 沒有 props/links（在 metadata + 子物件上）→ 已從 8 root 全移除。
• import href 冗餘：原本 import_*_href NOT NULL + import_*_id nullable（顛倒）→ 改 import_*_id NOT NULL 為真實 anchor、href 整欄移除（關聯式裡關係＝FK，href 是序列化概念，匯出時衍生）。POA&M 的 import-ssp OSCAL 選填 → import_ssp_id nullable。
• outline 產生器漏 anyOf：group/parameter/party 等用頂層 anyOf 包欄位，產生器原本跳過 → 已修（collect() 併 anyOf/allOf 分支）並重產 8 outline。這影響過設計依據，審查者若用 skill outline 請確認是修正後版本。
• 參數命名：catalog_control_parameters→catalog_control_params、part_name→name（規則 2 只前綴 id）。

4.2 仍待 user 拍板的設計判斷（審查者請評估是否合理）

• 深層 leaf → JSONB（規則 6）：risk 的 characterizations/facets/risk-log、observation 的 methods/subjects/relevant-evidence、task 的 timing/dependencies、SSP by-component 的 export/inherited/satisfied 責任鏈、profile merge/modify、catalog parameter 的 constraints/guidelines/select 等。主要實體都開表，深 leaf 留 JSONB。審查點：哪些 JSONB 其實該關聯化（取決於要不要查/匯出 round-trip）。
• observation/risk/finding 共用一套表（G2）：AR 與 POA&M 共用，用 ar_result_id / poam_id 兩個 nullable owner FK（擇一）。替代方案：各開一套（6 表）。
• component-def 的 control-impl/impl-req/statement 與 SSP 的「不共用表」（owner/語意不同）。
• system_ids / set-parameters / select_choices 等 [1..*] 小陣列用 JSONB（非開子表）。

1. status 欄（8 root 的產品生命週期欄）：user 說「重新設計、不考慮現行產品」→ 傾向移除（OSCAL 無此欄；要表達放 metadata.props）。尚未移除，等 user 最終確認。
2. assessment-common 共用 vs 各 model 各一套（§4.2 第二點）。
3. AP 未開的子結構：terms-and-conditions / assessment-subjects / assessment-assets / local-definitions(components/users/inventory) — 標註 deferred，要不要補。
4. mapping：provenance 已補（JSONB NOT NULL）；mapping 是 OSCAL 實驗性 model，確認是否真要做。

• 深層 leaf 全 JSONB（見 §4.2）—— 不是完整 OSCAL round-trip 的關聯化，是「主要實體關聯化 + leaf JSONB」的折衷。
• AP 部分子結構未開（§5.3）。
• 無 CHECK 約束 / 無 ENUM 值域鎖定（status_state / verdict / type 等只用 varchar + 註解列值域，未加 CHECK）。審查/實作時可考慮加。
• 無 RLS / tenant 欄（OSCAL 表本就不需，產品靠 PostgreSQL RLS；本設計未含）。
• 未驗證可實際執行建表：只做了「FK 順序無 forward-reference」+「無 double-comma/comma-before-paren」靜態檢查，沒真的 psql 跑過。下個 session 若要落地，先在空白 schema 試跑。
• dbdiagram.io 匯入未實測："class"/"end" 加引號欄、多型 nullable FK 等，dbdiagram 解析行為未驗。

cd ~/Projects/Billows/Audit-Manager/compliance-manager-be/docs/features/FR-037-2606-oscal-schema-gap-audit/
f=oscal-catalog-and-roots-schema.sql

# 表數（應 45）
grep -cE '^CREATE TABLE oscal\.' $f

# FK 順序（應無 forward-reference）+ 全欄註解（應 0 缺）：見本 arc 用過的 python 檢查
#   （或直接 psql 在空白 schema 試跑驗證可建表）

# 對照官方 required：每個 model 的逐欄 outline 在 skill
ls ~/.claude/skills/oscal-knowledge/references/model-schemas/
#   例：某表某欄該不該 NOT NULL → 查對應 <model>-outline.md 的 required 標記

審查建議：拿 ~/.claude/skills/oscal-knowledge/references/model-schemas/<model>-outline.md（authoritative，從官方 schema 程式化產出）逐表對 SQL —— 重點查 (a) 每個 OSCAL required [1] 是否 NOT NULL、(b) props/links 是否只在有的物件上、(c) 參照欄是 FK 還是 token（依 §2 規則 8）、(d) JSONB 折衷是否合理。

• ❌ 沒寫任何產品 code（無 SQLAlchemy model / mapper / repo / service）。
• ❌ 沒 commit（設計 WIP，全在 working tree）。
• ❌ 沒跑 migration、沒套到任何 DB 環境。
• ❌ 沒寫 changelog / Notion 任務（純設計、未 ship）。
• ❌ 沒做對話紀錄歸檔（user 未要求；要的話 scripts/extract_claude_sessions.py）。

1. 讀本 handoff §2（規則）+ §4/§5（判斷點/待決策）。
2. user 若已對 §5 拍板 → 套用（最可能：移除 status、定 assessment-common 共用法、補/不補 AP deferred）。
3. 拿 skill 的 <model>-outline.md 逐表複驗 required/props-links/參照（§7）。
4. 若要落地：空白 schema psql --single-transaction -v ON_ERROR_STOP=1 -f <檔> 試跑驗證可建表；再轉 SQLAlchemy 2.0 model（含 AuditMixin、PartColumnsMixin 之類共用欄 mixin）。
5. 命名一律對齊 OSCAL（規則 2）；新欄沿用「參照原則」（規則 8）。

本 handoff 自包含。SQL 檔每表每欄有中文 COMMENT，dbdiagram.io 匯入後 hover 可見。