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

| | |
|---|---|
| 日期 | 2026-06-12 |
| 階段 | **設計（design-only）**，無 code、無 commit、未部署 |
| branch | `feature/oscal-refactor` |
| 主產出 | [`docs/features/FR-037-2606-oscal-schema-gap-audit/oscal-catalog-and-roots-schema.sql`](../oscal-catalog-and-roots-schema.sql)（**1828 行 / 45 表 / 673 欄**，未追蹤）|
| 次產出 | OSCAL skill 補逐欄 outline（`~/.claude/skills/oscal-knowledge/`，**家目錄、非版控**）|
| 用途 | user 要把 SQL 匯入 dbdiagram.io 檢視 + 請另一個 AI 審查 |

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

---

## §0 TL;DR

把 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.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` 為準**。

---

## §2 鎖定的設計規則（user 拍板，審查時以此為準）

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，因到處被引用）。

---

## §3 45 表總覽

| Section | Model | 表 | 數 |
|---|---|---|---|
| A | 共用核心 | metadata / roles / parties / resources | 4 |
| B | Catalog | catalogs / catalog_groups / catalog_controls / catalog_control_parts / catalog_control_params | 5 |
| C | 8 root | catalogs(同上) / profiles / component_definitions / system_security_plans / assessment_plans / assessment_results / poams / mapping_collections | 8 |
| C2 | Profile | profile_imports | 1 |
| D | SSP | ssp_system_characteristics / ssp_information_types / ssp_diagrams / ssp_system_implementations / ssp_leveraged_authorizations / ssp_system_users / ssp_components / ssp_inventory_items / ssp_control_implementations / ssp_implemented_requirements / ssp_statements / ssp_by_components | 12 |
| E | Component-Def | cd_components / cd_capabilities / cd_control_implementations / cd_implemented_requirements / cd_statements | 5 |
| F | Assessment Plan | ap_reviewed_controls / ap_local_objectives / ap_assessment_activities / ap_tasks | 4 |
| G | Assessment Results | ar_results | 1 |
| G2 | assessment-common | assessment_observations / assessment_risks / assessment_findings（AR+POA&M 共用）| 3 |
| H | POA&M | poam_items | 1 |
| I | Mapping | mappings / mapping_maps | 2 |

> 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 本 arc 的判斷點 / 已修的系統性錯（審查重點）

### 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**（非開子表）。

---

## §5 待 user 決策（下個 session 開工前先確認）

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，確認是否真要做。

---

## §6 已知限制 / 刻意未做

- **深層 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 解析行為未驗。

---

## §7 如何審查 / 驗證（命令）

```bash
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 折衷是否合理。

---

## §8 沒做的事（明確聲明，避免誤會）

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

---

## §9 下個 session 開工建議順序

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 可見。*