Phase 2 + Phase 3 SQL Apply 交接（下個 session）

收尾後切回 main 部署用的 SQL apply 順序 + 主專案 pyproject.toml 收尾流程 起源 branch：feature/ssp-import-export-phase3 收尾日期：2026-05-23

本 handoff 假設「目標環境（dev / staging / production）尚未 apply Phase 2 + Phase 3 的任何 schema 改動」。

如果目標環境已部分執行過 SQL（如 dev 機已跑過 phase2 / 部分 phase3）：

• 不要重跑 STEP 1 ~ STEP 4 — DDL 用 IF NOT EXISTS 大致安全，但 backfill / seed DML 不冪等
• 對照下方「跑完驗證查詢」確認哪些 step 已完成，跳過已完成段

切勿盲跑。

Phase 2（已 consolidated，照舊跑）

scripts/sql/2026-05-22-ssp-import-export-phase2-upgrade.sql

涵蓋：

1. A0 (2026-05-18) — ssp_system_implementation_items 擴 9 欄
2. A0.1 (2026-05-19) — 改名 + 新 main / join 表 + backfill FK
3. Backfill (2026-05-19) — items.device_id soft FK
4. A2 (2026-05-20) — 新建 oscal.ssp_excel_parse_jobs

Phase 3（新 consolidated）

scripts/sql/2026-05-23-phase3-consolidated.sql

涵蓋（按執行順序）：

1. C1 (2026-05-22) — seed 9 個 OSCAL SSP 角色到 system_menus + oscal_roles
2. C8 (2026-05-22) — 刪除 26 筆 broken role_id（誤入的專案角色）
3. C3 PR1 (2026-05-23) — backfill ssp_system_implementation_items 從舊 mapping 表
4. C7 / Track B (2026-05-23) — items 加 provider + category 兩欄位
5. C3 PR2 (2026-05-23) — DROP compliance.project_device_mapping + project_information_systems

跑法（範本）

# 連線資訊：host / port / db 見 .env DB_HOST / DB_PORT / DB_NAME
# 密碼：請查 .env DB_SECRET JSON `rds_master_password`（cmmgr 跟 cm_app 同密碼）

# Phase 2
PGPASSWORD='<查 .env>' psql -h <DB_HOST> -p <DB_PORT> -U cmmgr -d <DB_NAME> \
    -f scripts/sql/2026-05-22-ssp-import-export-phase2-upgrade.sql

# Phase 3
PGPASSWORD='<查 .env>' psql -h <DB_HOST> -p <DB_PORT> -U cmmgr -d <DB_NAME> \
    -f scripts/sql/2026-05-23-phase3-consolidated.sql

⚠️ 帳號必須是 cmmgr — cm_app 受 RLS 擋，寫 system_menus / oscal_roles 等系統層級 table 會 INSERT 0 rows 靜默失敗。

Phase 2 驗證

-- A0/A0.1 結構
\d oscal.ssp_system_implementation_items
-- 看到: scope_type, scope_id, device_id, system_characteristic_id,
--       system_implementation_id, title, purpose, status, party_uuid, date_authorized

-- A0.1 main + join 建出來
SELECT COUNT(*) FROM oscal.ssp_system_implementations;             -- ≥ 1
SELECT COUNT(*) FROM oscal.ssp_inventory_item_components;           -- ≥ 0

-- A0.1 backfill 完整
SELECT COUNT(*) FROM oscal.ssp_system_implementation_items
 WHERE system_implementation_id IS NULL;                            -- 0

-- A2 parse_jobs 新表
\d oscal.ssp_excel_parse_jobs
SELECT polname FROM pg_policy
 WHERE polrelid = 'oscal.ssp_excel_parse_jobs'::regclass;          -- 4 條 RLS policy

Phase 3 驗證

-- C1: 9 個 OSCAL 角色 seed
SELECT COUNT(*) FROM system_menus WHERE "group" = 'ssp_party_role';   -- 9
SELECT COUNT(*) FROM oscal.oscal_roles WHERE role_id IN (
    'responsible-organization', 'system-owner', 'system-developer',
    'authorizing-official', 'authorizing-official-poc', 'asset-administrator',
    'asset-owner', 'maintainer', 'isso'
);                                                                    -- 9

-- C8: broken role_id 清理
SELECT role_id, COUNT(*) FROM oscal.oscal_responsible_parties
 WHERE role_id IN ('manager', 'reviewer', 'auditor', 'viewer')
 GROUP BY role_id;                                                    -- 0 rows

-- C3 PR1: backfill 結果
SELECT scope_type, COUNT(*)
  FROM oscal.ssp_system_implementation_items
 WHERE scope_type IN ('device', 'information_system')
 GROUP BY scope_type;
-- 預期: 對應原 mapping 表內非孤兒（有 latest AP）的資料筆數

-- C7 / Track B: items 加欄位
\d oscal.ssp_system_implementation_items
-- 看到: provider VARCHAR(255), category VARCHAR(100)

-- C3 PR2: 舊表 DROP 完成
SELECT to_regclass('compliance.project_device_mapping');              -- NULL
SELECT to_regclass('compliance.project_information_systems');         -- NULL

現況（本 session 結束時的狀態）

• jedi-oscal v0.0.18 已發 Nexus（wheel + sdist 上傳完成）
• jedi-oscal repo：commit 6f04da7 已 commit（branch feature/ssp-import-export-phase3），尚未 push origin
• 主專案 pyproject.toml 已 edit（未 commit）：
  • L80 #"jedi-oscal==0.0.17" → "jedi-oscal==0.0.18"（取消註解 + bump）
  • L91 jedi-oscal = { path = ... develop = true} → 整行註解掉
• poetry update jedi-oscal 由 user 手動執行 — 完成後 poetry.lock 會 update 到 0.0.18

接手後第一件事

cd /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be
git status     # 確認 M pyproject.toml + M poetry.lock
git diff pyproject.toml | head -30
poetry show jedi-oscal | head -5   # 確認版本 0.0.18

如果 lock 還沒更新（user 沒跑 poetry update）→ 跑 poetry update jedi-oscal。

確認 OK 後 commit：

git add pyproject.toml poetry.lock
git commit -m "chore(deps): pin jedi-oscal 0.0.18 (was path dev dep)"

⚠️ CLAUDE.md 規範：claude 一律不切 branch。下列 step user 自己執行：

# user 自己跑（claude 禁止）
cd /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be
git checkout main
git merge feature/ssp-import-export-phase3       # 或 rebase，看團隊規範

merge 後告訴 claude，claude 才能在 main branch 上協助接後續工作。

按下面順序部署可避免「BE 跑舊 schema 失敗」：

1. 跑 SQL：先 phase2 consolidated，後 phase3 consolidated
2. 更新主專案 code：merge / pull feature/ssp-import-export-phase3（含 pyproject.toml + poetry.lock）
3. 拉 jedi-oscal 0.0.18：poetry update jedi-oscal（如 deploy 環境用 lock，已固定到 0.0.18）
4. 重啟 BE：

   lsof -ti :8000 | xargs kill -9 && python main_socketio.py

   （nohup 留 orphan 是已知問題，kill -9 強制）
5. smoke 驗證：
   • GET /ssp/<uid>/system-characteristic 回 200 + payload
   • GET /ssp/<uid>/leveraged 回 200 + items 有 provider / category 欄位
   • GET /ssp/<uid>/excel-template?mode=blank 下載 xlsx
   • GET /module-frame/<uid>/system-characteristic 回 200
   • GET /module-frame/<uid>/leveraged 回 200
   • 啟動新專案後檢查新 SSP 的 SC + leveraged 有從 template clone

• 既有專案 leveraged row 缺 provider / category：因前期 clone 流程沒帶；新 round / 新專案會自然修復。要立刻補可在 SSP UI 內 edit dialog 手動填。
• MF SC / leveraged endpoint 沒有 BE unit test：A1/A2 是 thin delegate，test 留下一輪補。
• 預覽頁 confirm 後 ?section=ssp deep-link 沒實作：跳回 project-planning-ap route 但 SSP tab 切換需 user 手動點。

⚠️ CLAUDE.md 規範：push 永遠要 user 明確指示，不自動 push。

待 push：

• jedi-oscal repo：commit 6f04da7（branch feature/ssp-import-export-phase3）
• 主專案：包含 Track B/A/C 所有 commits + pyproject.toml/lock 收尾 commit

⚠️ jedi-oscal 雖然 Nexus 已發 v0.0.18，但 git 還沒 push。建議 push 前先 review branch 跟 main 的差異有沒有什麼遺漏。