CM-1077.1 檢測基準「是否被使用過」判定服務

Repo：BE　｜　前置：無　｜　母卡：CM-1077

母卡 CM-1077 要做的事是「版本與基準的修正與刪除——未被使用過才可改可刪，用過即凍結」。

這件事真正的難點與風險不在動作本身（改名、刪版本、刪主檔都是常規 CRUD），而在判定：

• 「被使用過」要查三個不同的引用來源，漏一個就等於放行了不該放行的刪除
• 專案是軟刪的，照字面判定會產生「被一個看不到的專案擋住、且無法自救」的死結
• 主檔與版本兩種層級的嚴格度不一樣，混用會誤擋或誤放

所以拆卡：判定先獨立落地並可被單獨驗證，後續的動作卡（.2 / .3）就只是消費它。判定寫錯是資料安全問題，動作寫錯只是 UI 問題——兩者風險等級不同，不該綁在同一次驗收裡。

BE，唯讀，零破壞性。

1. 一支 domain / app service 方法，回答「這個版本被誰用過」與「這支主檔被誰用過」
2. 一支唯讀端點，讓 FE 在開「編輯／刪除」對話框前先問

🔴 本卡不含任何寫入或刪除動作。 不要順手把 .2 / .3 的刪除端點一起做掉——判定要能被單獨驗證，混進寫入就驗不乾淨了。

版本層 —— 三個引用來源，全部要查

① config.job_execution_detection_tools.tool_params->>'profile' = 'profile:<version_uid>'

② compliance.agent_tasks.params->'_profile'->>'uid' = <version_uid>
   🔴 只有 source_type='url' 時，這個 uid 才是 profile 的 uid；
      file 型存的是 upload_files 的 uid（見下方「相關程式碼」的 build_payload）

③ compliance.detection_executions → agent_task_uid → agent_tasks（判準同 ②）

三個來源代表三個階段：任務綁定（還沒跑）、已派工單、已執行歷史。任何一個階段出現過，就代表這份基準已經進入稽核軌跡。

🔴 軟刪穿透：每一條都要 JOIN 回專案並排除軟刪

JOIN compliance.job_executions je ON je.id = jedt.job_execution_id
JOIN compliance.task_assignees ta ON ta.task_id = je.id
JOIN compliance.project_extensions pe ON pe.project_id = ta.project_id
WHERE pe.deleted_at IS NULL

為什麼要穿透（決策者親自定的，不可自行簡化）：

專案刪除走軟刪（compliance.project_extensions.deleted_at），但全系統 11 處查詢一律把軟刪專案濾掉——沒有任何角色（含 admin）看得到已刪除專案，也沒有任何復原端點。DEV 現況：209 個專案中有 204 個已軟刪。

若照字面判定（不管專案死活，有引用就擋），會出現死結：使用者被一個他看不到、無從查證、也無法處理的專案擋住，且沒有任何自救途徑。

決策者裁示：軟刪專案的引用直接不計入。 核心原則是「擋下的理由必須是使用者看得見的」。這在 GRC 語意上也站得住——專案已刪除，代表那份稽核工作已被放棄。

⚠️ 絕對不可用 projects.status = 'archived' 當軟刪判準。 協調者實查發現：

• status 與 deleted_at 是兩套不同步的真相（8 筆已軟刪但 status 仍 in_progress）
• archived 這個 status 在現行流程中沒有任何寫入端，那 196 筆是歷史遺留

唯一可靠判準是 project_extensions.deleted_at IS NOT NULL。

（軟刪專案完全無法檢視或復原這件事本身是個問題，已另開 Notion follow-up 卡記錄，優先權低，不在本卡範圍。）

主檔層 —— 判準更嚴

• 底下任一版本被用過，主檔就算已使用
• 主檔層不做軟刪穿透

不穿透的理由：刪主檔會連帶刪掉所有版本與控制項，而任務可能已被刪，但報告與證據還在——那時這支 profile 仍然是稽核軌跡的一部分。母卡原話是「從未產生過任何掃描結果」，主檔的門檻就照這句話走，寧嚴勿寬。

{
  in_use: bool,
  projects: [{uid, name}],
  version_refs: [...]
}

帶專案名稱是為了讓「擋下」的訊息說得出理由（.3 的 tooltip 要用）。

⚠️ projects 只列活著的專案。 軟刪專案既然不參與判定，自然也不該出現在訊息裡——否則就是秀一個使用者查不到的東西給他，比不給理由更糟。

四筆引用全部指向專案 301「Agent 測試專用專案」（in_progress、未軟刪） → 上表 4 個版本應判 in_use = true（不可改不可刪），其餘 11 個判 in_use = false（可改可刪）。

原始筆數（供交叉檢查）：

• job_execution_detection_tools 帶 profile ref：4 筆
• agent_tasks 帶 _profile：7 筆（其中 url 型 2 筆才指向 profile uid）

查 agent_tasks 要用 jsonb 存在運算子：

WHERE params ? '_profile'          -- ✅ 正確
WHERE params::text LIKE '%_profile%'  -- ❌ 絕對不可

_ 在 SQL LIKE 裡是單字元萬用字元，會多撈到一堆無關的列。協調者實測：錯誤寫法回 32 筆，正確寫法只有 7 筆。

（若走 SQLAlchemy，注意 ? 運算子與 text() 的參數綁定衝突——text() 內的 ? / : 都有特殊意義，必要時改用 .op('?') 或 jsonb_exists() 函式形式。）

common/util/detection_profile_ref.py
    profile uid 怎麼被寫進派工參數。🔴 必讀 build_payload()：
    file 型 payload 的 uid 是 upload_files 的 uid、url 型才是 profile 列的 uid
    —— 這就是判定規則 ② 那條註記的來源。
    extract_profile_uid() 是 'profile:<uid>' 前綴的 canonical 解析，
    判定規則 ① 應該 import 它，不要自己寫 startswith("profile:")。

domain/detection_tools/service/detection_profile_domain_service.py
    已有 get_by_uid / get_version_by_uid / get_version_with_profile

infra/detection_tools/repository/detection_profile_repo_impl.py
    repo 實作

api/detection_tools/routes/detection_profile_route.py
    11 個 Resource；URL 註冊在 api/detection_tools/__init__.py

既有可參照的 ref-count pattern（同形狀，優先照抄不要另創）：

app/detection_tools/service/detection_tool_service.py:249   count_referencing_tasks()
domain/detection_tools/service/job_execution_detection_tool_domain_service.py:30
api/detection_tools/routes/detection_tool_route.py:155
端點命名慣例：/detection-tools/configs/<uid>/referencing-tasks-count
              /detection-profile-taxonomies/<uid>/referencing-profiles-count

⚠️ 端點註冊順序有意義：帶尾綴的路徑必須註冊在 /<string:uid> 之前，否則會被當成 uid 吃掉（Flask 按註冊順序比對）。既有註解就寫在 api/detection_tools/__init__.py 裡。

⚠️ 相關表的 schema 不同，JOIN 要寫對： config.job_execution_detection_tools、compliance.agent_tasks、compliance.detection_executions、compliance.job_executions、compliance.task_assignees、compliance.project_extensions。

• DDD 分層：route 不做 DB 查詢、不 import ORM model，只解 request → 呼叫 app service → 序列化 response；app service 每個 public method 掛 @transaction；只有 infra 層能碰 session / ORM。repo 的 session 必須 lazy（繼承 BaseRepositoryImpl，或自己寫 @property session），__init__ 內寫 self.session = get_session() 會直接 500。
• 新增 method 前先按「行為」grep 既有能力（不是按名字）——禁止重複造輪子。上面已列出 count_referencing_tasks 這條既有 pattern，優先 reuse / extend。
• 🔴 只動 DEV。STG / POC 一律等決策者當次明示（環境異動鐵律）。本卡是唯讀查詢，理論上不需要任何 migration；若你認為需要動 schema，先停下回報。
• 不切 branch，就在當下 branch 工作。branch 不對就停下問，不要自己 fix。
• commit 顯式 git add <檔名>，禁用 -am（多 session 併發是本專案常態）。
• 不 push（等 user 明示）。
• 收尾類動作等 user 明確下令：spec / SUMMARY / Notion 母卡回寫都不要自己做。本卡子卡狀態可在交接時回寫「修正待驗證」。
• 卡住兩次就停下回報，不要第三次重試。
• 錯誤一律用 jedi_common.handler.exception + GrcErrorCode，不用裸 ValueError。
• BE 行為異常先自己看 log/app.log 找 stack trace，不要先問 user。

• domain / app service 有一支方法回答「版本是否被使用」，涵蓋 ①②③ 三個引用來源，全部帶軟刪穿透
• domain / app service 有一支方法回答「主檔是否被使用」，語意為「底下任一版本被用過即算」，且不做軟刪穿透
• 判定規則 ① 走 common/util/detection_profile_ref.py 的 extract_profile_uid() / PROFILE_REF_PREFIX，沒有自行寫死 startswith("profile:")
• 判定規則 ② 對 agent_tasks.params 用 jsonb 存在運算子（? '_profile'），沒有任何 LIKE '%_profile%'
• 軟刪判準用 project_extensions.deleted_at IS NULL，程式碼中沒有出現 status = 'archived' 當軟刪判準
• 唯讀端點可用，回傳形狀為 {in_use, projects: [{uid, name}], version_refs}，projects 只含未軟刪專案
• 端點路徑遵循既有 referencing-* 命名慣例，且註冊在 /<string:uid> 之前（不被當成 uid 吃掉）
• DEV 實測斷言：42a8368f-… / e43b0535-… / ff79e896-… / de5087b9-… 四個版本判 in_use = true；其餘 11 個版本判 in_use = false
• DEV 實測斷言：上述四個版本回傳的 projects 皆只含專案 301「Agent 測試專用專案」一筆
• DEV 實測斷言：主檔層判定對「底下有上述任一版本」的主檔回 in_use = true
• 有測試覆蓋三個引用來源各自單獨命中的情況（不是只測 happy path 一條）
• 有測試覆蓋「引用存在但專案已軟刪 → 版本層判 in_use = false、主檔層仍判 true」這條兩層嚴格度差異
• route 層沒有 DB 查詢、沒有 import ORM model；app service public method 都有 @transaction
• 全程零寫入、零刪除——本卡不動任何一筆既有資料
• commit 用顯式 git add，未 push