← Files Codex SecurityARCHIVED FILE

scripts/workbench_scan_history.py

59.4 KB · Nov 18, 2026 · 06:11 UTC

↓ Download file

See the change to this file →

"""Scan history projection for the native Codex Security workbench."""

import argparse
import fnmatch
import hashlib
import json
import os
import sqlite3
import sys
from collections.abc import Iterable, Iterator
from fractions import Fraction
from functools import cache
from itertools import chain
from pathlib import Path, PurePosixPath
from typing import Any, Callable

# Some plugin hosts launch Python with safe-path isolation enabled.
sys.path.insert(0, str(Path(__file__).resolve().parent))
from finalize_scan_contract import (
    ContractError,
    _prepare_scan_finalization,
    _read_scan_local_json_bytes,
    _read_sealed_scan,
    manifest_is_sealed,
)
from report_projection import SEVERITY_ORDER
from review_state import review_state
from workbench_constants import ARTIFACTS, FINDINGS_PAGE_MAX, PRIMARY_FINDING_LOCATION_SQL
from workbench_scan_usage import stored_scan_cost_fields
from workbench_source_scopes import public_scan_recipe
from workbench_target import (
    require_remediation_target,
    require_scan_target_identity,
    verified_windows_checkout,
)
from workbench_target_state import (
    RepositoryIdentityCache,
    _timestamp_ns,
    repository_relative_path,
    same_repository_worktree,
    scan_repository_generation,
)
from workbench_validation import timestamp_key


def reads_missing_history(args: argparse.Namespace, path: Path) -> bool:
    """Select an in-memory database only for history reads on a fresh profile."""
    if args.command not in {"list-scans", "list-repositories", "list-global-findings"} and not (
        args.command == "get-scan" and not (args.request_id or args.action_token)
    ):
        return False
    try:
        path.lstat()
    except FileNotFoundError:
        return True
    except OSError:
        # The ordinary connection reports inaccessible storage.
        pass
    return False


def rename_scan(connection: sqlite3.Connection, scan: sqlite3.Row, name: str) -> dict[str, Any]:
    name = name.strip()
    if not name:
        raise SystemExit("Scan name cannot be empty.")
    connection.execute("UPDATE scans SET name = ? WHERE id = ?", (name, scan["id"]))
    connection.commit()
    return {"scanId": scan["id"], "name": name}


def scan_recipe(scan: sqlite3.Row) -> dict[str, Any]:
    """Return the public launch recipe with its scan and parent IDs, raising SystemExit if the recipe was never saved."""
    if scan["recipe_json"] is None:
        raise SystemExit("This scan does not have a saved launch recipe.")
    return {
        "parentScanId": scan["parent_scan_id"],
        "recipe": public_scan_recipe(scan),
        "scanId": scan["id"],
    }


def expected_coverage_mode(scan: sqlite3.Row) -> str:
    """Return the coverage mode implied by the recorded scope, scan mode, and diff target kind."""
    if scan["mode"] == "diff":
        mode = {
            "commit": "commit",
            "range": "branch_diff",
            "working_tree": "working_tree",
        }.get(scan["diff_target_kind"])
        if mode is None:
            raise SystemExit("This migrated diff scan does not have a validated change set.")
        return mode
    if scan["scope"] != "." or (
        "recipe_json" in scan.keys()
        and scan["recipe_json"] is not None
        and json.loads(scan["recipe_json"])["target"]["kind"] == "paths"
    ):
        return "scoped_path"
    return "deep_repository" if scan["mode"] == "deep" else "repository"


def preserve_sealed_completion(
    binding: dict[str, Any], manifest: dict[str, Any] | None
) -> dict[str, Any]:
    """Copy the original timestamps and producer version from a sealed manifest into binding, then return it."""
    manifest_scan = manifest.get("scan") if manifest is not None else None
    if isinstance(manifest_scan, dict) and manifest_scan.get("sealedAt") is not None:
        # Keep the original producer; finalization still validates schema, seal and owner.
        binding["startedAt"] = manifest_scan.get("startedAt")
        binding["completedAt"] = manifest_scan.get("completedAt")
        producer = manifest_scan.get("producer")
        if isinstance(producer, dict):
            binding["producer"]["version"] = producer.get("version")
    return binding


def _windows_resume_checkout(scan: sqlite3.Row) -> Path:
    from workbench_scan_start import scan_target_identity

    replaced = "Cannot resume: the original checkout is missing or was replaced."
    try:
        repository = require_remediation_target(scan["target_path"])
    except SystemExit as exc:
        raise SystemExit(replaced) from exc
    with verified_windows_checkout(scan, repository, replaced_message=replaced):
        expected = (scan["target_revision"], scan["target_snapshot_digest"])
        current = scan_target_identity(
            repository, None, expected_digest=scan["target_snapshot_digest"]
        )
        if current[:2] != expected:
            raise SystemExit("Cannot resume: the original checkout revision or contents changed.")
    return repository


def cli_scan_resume(
    wb: Any,
    connection: sqlite3.Connection,
    scan: sqlite3.Row,
    workspace: sqlite3.Row,
) -> dict[str, Any]:

    scan = scan_with_progress(connection, scan["id"])
    if scan["mode"] not in {"standard", "deep"} or scan["recipe_json"] is None:
        raise SystemExit("Resume requires a Standard or Deep Scan with a saved CLI launch recipe.")
    if scan["status"] != "running" or scan["canceled_at"] is not None:
        raise SystemExit(
            "Resume requires a running scan; completed, failed, and canceled scans cannot resume."
        )
    thread_id = scan["continuation_thread_id"]
    owner = scan["deep_scan_owner_thread_id"] or workspace["thread_id"]
    if (
        not thread_id
        or (owner is not None and owner != thread_id)
        or scan["handoff_status"] != "delivered"
        or scan["handoff_claim_token"] is not None
    ):
        raise SystemExit("Resume requires the original owning CLI session.")
    run = connection.execute(
        "SELECT status, cancel_requested FROM deep_scan_runs WHERE scan_id = ?", (scan["id"],)
    ).fetchone()
    if run is not None and (
        run["status"] not in {"running", "succeeded"} or run["cancel_requested"]
    ):
        raise SystemExit("This Deep Scan has stopped and cannot resume.")
    if os.name == "nt":
        try:
            repository = _windows_resume_checkout(scan)
        except OSError as exc:
            raise SystemExit(f"Cannot verify the original Windows checkout: {exc}") from exc
    else:
        from workbench_scan_start import scan_target_identity

        try:
            repository = require_scan_target_identity(scan)
        except SystemExit as exc:
            raise SystemExit(
                "Cannot resume: the original checkout is missing or was replaced."
            ) from exc
        if scan_target_identity(
            repository, None, expected_digest=scan["target_snapshot_digest"]
        ) != (
            scan["target_revision"],
            scan["target_snapshot_digest"],
            scan["target_device"],
            scan["target_inode"],
        ):
            raise SystemExit("Cannot resume: the original checkout revision or contents changed.")
    recipe = wb.parse_scan_recipe(scan["recipe_json"], repository)
    scan_dir = wb.require_canonical_scan_directory(Path(scan["scan_dir"]))
    if os.name == "nt":
        from windows_scan_local_files import DEEP_RESUME_DIRECTORY, deep_resume_output_identity

        identity = scan["windows_resume_output_identity"]
        if identity is not None:
            try:
                current = deep_resume_output_identity(scan_dir)
            except OSError as exc:
                raise SystemExit(
                    f"Cannot verify the registered Windows resume output: {exc}"
                ) from exc
            if current != json.loads(identity):
                raise SystemExit(
                    "Cannot resume: the original Windows output directory was replaced."
                )
        elif not any(entry != scan_dir / DEEP_RESUME_DIRECTORY for entry in scan_dir.iterdir()):
            raise SystemExit(
                "Cannot resume this empty Windows output because its original directory identity "
                f"was not saved. Start a new scan with 'codex-security scans rerun {scan['id']}'."
            )
    result = {
        "contract": wb.scan_contract(scan),
        "lifecycle": json.loads(scan["lifecycle_json"]),
        "recipe": recipe,
        "scanDir": str(scan_dir),
        "scanId": scan["id"],
        "scopeFileCount": scan["scope_file_count"],
        "startedAt": scan["started_at"],
        "targetId": scan["target_id"],
        "targetRevision": scan["target_revision"],
        "threadId": thread_id,
        "userContext": scan["user_context"],
    }
    # Active coordinators may still be writing drafts. Validate sealed results
    # before attaching to a coordinator that has finished.
    if run is not None and run["status"] == "succeeded":
        manifest_path = wb.artifact_path(scan_dir, ARTIFACTS["manifest"], required=False)
        if manifest_path is not None:
            manifest = wb.read_json_object(manifest_path)
            manifest_scan = manifest.get("scan")
            if isinstance(manifest_scan, dict) and manifest_is_sealed(manifest_scan):
                try:
                    binding = wb.workbench_completion_binding(scan, scan["started_at"], manifest)
                    _prepare_scan_finalization(
                        scan_dir,
                        expected_coverage_mode=binding["coverageMode"],
                        completion_binding=binding,
                    )
                    result["sealedProducerVersion"] = manifest_scan["producer"]["version"]
                except ContractError as exc:
                    raise SystemExit(f"Cannot resume sealed scan: {exc}") from exc
    return result


def coverage_summary_for_history(
    wb: Any, scan: sqlite3.Row, canonical_scan_directory: Callable[[Path], Path]
) -> dict[str, Any]:
    """Return the coverage mode and path filters for history after verifying the sealed scan data."""
    scan_dir = canonical_scan_directory(Path(scan["scan_dir"]))
    coverage_ref = "coverage.json"
    try:
        if scan["seal_manifest_digest"] is None:
            # Older workbench rows can have valid seals without a pinned digest.
            manifest, _, coverage, _ = _read_sealed_scan(scan_dir, None, "history coverage")
            wb.verify_manifest_binding(scan, manifest)
            if coverage["mode"] != wb.expected_coverage_mode(scan):
                raise ContractError("coverage.mode: must match the workbench scan")
        else:
            # Completion already validated the full scan. History only needs the
            # pinned manifest and any legacy coverage artifact, not findings or receipts.
            manifest, manifest_bytes = _read_scan_local_json_bytes(
                scan_dir, ARTIFACTS["manifest"], ARTIFACTS["manifest"]
            )
            if (
                f"sha256:{hashlib.sha256(manifest_bytes).hexdigest()}"
                != scan["seal_manifest_digest"]
            ):
                raise ContractError("The sealed scan manifest changed after completion.")
            sealed_scan = manifest["scan"]
            if sealed_scan["id"] != scan["id"] or not sealed_scan.get("sealedAt"):
                raise ContractError("The sealed coverage does not belong to this scan.")
            if "coverageRef" not in sealed_scan:
                coverage = review_state(sealed_scan)
                return {
                    key: coverage[key]
                    for key in ("mode", "includePaths", "excludePaths", "explicitExclusions")
                }
            if sealed_scan["coverageRef"] != coverage_ref:
                raise ContractError("The sealed coverage does not belong to this scan.")
            coverage, coverage_bytes = _read_scan_local_json_bytes(
                scan_dir, coverage_ref, coverage_ref
            )
            expected = next(
                (
                    artifact["sha256"]
                    for artifact in sealed_scan["artifacts"]
                    if artifact["path"] == coverage_ref
                ),
                None,
            )
            if (
                hashlib.sha256(coverage_bytes).hexdigest() != expected
                or coverage.get("scanId") != scan["id"]
            ):
                raise ContractError("The sealed coverage changed after completion.")
    except ContractError as exc:
        raise SystemExit(str(exc)) from exc
    return {
        key: coverage[key]
        for key in (
            "mode",
            "includePaths",
            "excludePaths",
            "explicitExclusions",
        )
    }


def _windows_path_key(value: str) -> str:
    return os.path.normcase(os.path.realpath(value))


def _same_repository(
    connection: sqlite3.Connection,
    before: sqlite3.Row,
    after: sqlite3.Row,
    *,
    identities: RepositoryIdentityCache | None = None,
) -> bool:
    before_stored = scan_repository_generation(before)
    after_stored = scan_repository_generation(after)
    if before_stored is not None and before_stored == after_stored:
        return True
    if before["target_id"] and before["target_id"] == after["target_id"]:
        return before_stored is None or after_stored is None
    identities = identities or RepositoryIdentityCache(connection)
    before_state = identities.for_row(before)
    after_state = identities.for_row(after)
    if (
        not before_state.ownership_matches
        or not after_state.ownership_matches
        or before_stored is not None
        and before_stored != before_state.live_identity
        or after_stored is not None
        and after_stored != after_state.live_identity
    ):
        return False
    if (
        before_state.resolved_path is not None
        and before_state.resolved_path == after_state.resolved_path
    ):
        return before_stored is None or after_stored is None
    if before_state.repository is None or after_state.repository is None:
        if same_repository_worktree(Path(before_state.target_path), Path(after_state.target_path)):
            return True
        before_relative = repository_relative_path(Path(before_state.target_path))
        after_relative = repository_relative_path(Path(after_state.target_path))
        after_origin = identities.origin(after_state)
        return (
            before_relative is not None
            and before_relative == after_relative
            and after_origin is not None
            and identities.origin(before_state) == after_origin
        )
    before_identity = before_state.live_identity
    after_identity = after_state.live_identity
    if before_identity is not None and before_identity == after_identity:
        return True
    if before_identity is None or after_identity is None:
        return False
    after_origin = identities.origin(after_state)
    return (
        after_origin is not None
        and identities.origin(before_state) == after_origin
        and before_state.repository.relative_path == after_state.repository.relative_path
    )


SCAN_SUMMARY_COLUMNS = """
    scans.*,
    lifecycle.snapshot AS lifecycle_json,
    progress.reportable_findings_count,
    progress.scope_file_count,
    progress.review_items_completed,
    progress.review_items_total,
    progress.updated_at AS progress_updated_at,
    (
        SELECT COUNT(*)
        FROM finding_occurrences AS occurrences
        WHERE occurrences.scan_id = scans.id
    ) AS finding_count
"""
SCAN_SUMMARY_QUERY = f"""
SELECT {SCAN_SUMMARY_COLUMNS}
FROM scans
JOIN scan_progress AS progress ON progress.scan_id = scans.id
JOIN scan_lifecycles AS lifecycle ON lifecycle.scan_id = scans.id
"""


def scan_with_progress(connection: sqlite3.Connection, scan_id: str) -> sqlite3.Row:
    return connection.execute(
        """
        SELECT scans.*, lifecycle.snapshot AS lifecycle_json, progress.updated_at AS progress_updated_at,
               progress.scope_file_count, progress.reportable_findings_count,
               progress.review_items_total, progress.review_items_completed, progress.deep_review_pass,
               progress.phase_items_total, progress.phase_items_completed, progress.phase_progress_unit,
               progress.preflight_checks_total, progress.preflight_checks_completed,
               progress.preflight_issues_json
        FROM scans
        JOIN scan_progress AS progress ON progress.scan_id = scans.id
        JOIN scan_lifecycles AS lifecycle ON lifecycle.scan_id = scans.id
        WHERE scans.id = ?
        """,
        (scan_id,),
    ).fetchone()


def scan_summary(row: sqlite3.Row) -> dict[str, Any]:
    return {
        "completedAt": row["completed_at"],
        "logCompletedAt": row["log_completed_at"],
        "continuationThreadId": row["continuation_thread_id"],
        **stored_scan_cost_fields(row["cost_json"]),
        "findingCount": row["finding_count"],
        "lifecycle": json.loads(row["lifecycle_json"]),
        "handoffStatus": row["handoff_status"],
        "mode": row["mode"],
        "model": row["model"],
        "name": row["name"],
        "parentScanId": row["parent_scan_id"],
        "progress": {
            "candidates": {"reportable": row["reportable_findings_count"]},
            "coverage": {
                "closedRows": row["review_items_completed"],
                "filesTotal": row["scope_file_count"],
                "worklistRows": row["review_items_total"],
            },
            "phase": row["phase"],
            "status": "canceled" if row["canceled_at"] else row["status"],
            "updatedAt": row["progress_updated_at"],
        },
        "recipeAvailable": row["recipe_json"] is not None,
        "reasoningEffort": row["reasoning_effort"],
        "scanDir": row["scan_dir"],
        "scanId": row["id"],
        "scope": row["scope"],
        "startedAt": row["started_at"],
        "targetId": row["target_id"],
        "targetPath": row["target_path"],
        "targetRevision": row["target_revision"],
        "targetSummary": row["target_summary"],
        "updatedAt": max(
            row["updated_at"],
            row["progress_updated_at"],
            key=lambda value: (timestamp_key(value), value),
        ),
        **(
            {"warnings": json.loads(row["completion_warnings_json"])}
            if row["completion_warnings_json"] != "[]"
            else {}
        ),
    }


def list_scans(
    connection: sqlite3.Connection, args: argparse.Namespace | None = None
) -> dict[str, Any]:
    connection.create_function("casefold", 1, str.casefold, deterministic=True)
    if os.name == "nt":
        connection.create_function("codex_security_path_key", 1, _windows_path_key)
    clauses: list[str] = []
    values: list[Any] = []
    worktree_order = ""
    if args is not None and args.repository:
        repository = Path(args.repository).expanduser().absolute()
        identities = RepositoryIdentityCache(connection)
        repository = Path(identities.history_path(str(repository)))
        clause, scope_values = identities.scope_for_path(str(repository)).sql(
            supports_generation=identities.supports_generation
        )
        clauses.append(clause)
        values.extend(scope_values)
        if getattr(args, "prefer_worktree", False):

            @cache
            def in_worktree(target_path: str) -> bool:
                try:
                    return repository.samefile(target_path)
                except OSError:
                    return False

            connection.create_function("in_worktree", 1, in_worktree)
            worktree_order = "in_worktree(scans.target_path) DESC,"
    if args is not None and args.scan_root:
        scan_root = str(Path(args.scan_root).expanduser().resolve())
        prefix = scan_root.rstrip(os.sep) + os.sep
        if os.name == "nt":
            scan_root = _windows_path_key(scan_root)
            prefix = scan_root.rstrip(os.sep) + os.sep
            clauses.append(
                "(codex_security_path_key(scans.scan_dir) = ? "
                "OR substr(codex_security_path_key(scans.scan_dir), 1, ?) = ?)"
            )
        else:
            clauses.append("(scans.scan_dir = ? OR substr(scans.scan_dir, 1, ?) = ?)")
        values.extend((scan_root, len(prefix), prefix))
    if args is not None and args.target_id:
        clauses.append("scans.target_id = ?")
        values.append(args.target_id)
    if args is not None and args.mode:
        clauses.append("scans.mode = ?")
        values.append(args.mode)
    if args is not None and args.status:
        if args.status == "canceled":
            clauses.append("scans.canceled_at IS NOT NULL")
        else:
            clauses.append("scans.status = ? AND scans.canceled_at IS NULL")
            values.append(args.status)
    if args is not None and args.query:
        query = args.query.strip().casefold()
        if query:
            clauses.append(
                "(instr(casefold(scans.target_path), ?) > 0 "
                "OR instr(casefold(COALESCE(scans.name, '')), ?) > 0 "
                "OR instr(casefold(COALESCE(scans.target_summary, '')), ?) > 0 "
                "OR instr(casefold(scans.scope), ?) > 0 "
                "OR instr(casefold(scans.mode), ?) > 0)"
            )
            values.extend((query, query, query, query, query))
    where = f"WHERE {' AND '.join(clauses)}" if clauses else ""
    paginated = args is not None and (args.limit is not None or args.offset != 0)
    limit = (args.limit or FINDINGS_PAGE_MAX) if paginated else None
    pagination = "LIMIT ? OFFSET ?" if paginated else ""
    if limit is not None:
        values.extend((limit + 1, args.offset))
    rows = connection.execute(
        f"""
        {SCAN_SUMMARY_QUERY}
        {where}
        ORDER BY {worktree_order}
            CASE WHEN scans.status = 'running' AND scans.canceled_at IS NULL THEN 0 ELSE 1 END,
            MAX(julianday(upper(scans.updated_at)), julianday(upper(progress.updated_at))) DESC,
            julianday(upper(scans.started_at)) DESC,
            scans.id
        {pagination}
        """,
        values,
    ).fetchall()
    scans = [scan_summary(row) for row in rows[:limit]]
    if worktree_order:
        for scan in scans:
            scan["inCurrentWorktree"] = in_worktree(scan["targetPath"])
    result = {"scans": scans}
    if limit is not None:
        result.update(
            {
                "limit": limit,
                "nextOffset": args.offset + limit if len(rows) > limit else None,
                "offset": args.offset,
            }
        )
    return result


def _scan_completion_order(scan: sqlite3.Row) -> tuple[int, int | Fraction, str]:
    sequence = scan["completion_sequence"] if "completion_sequence" in scan.keys() else None
    if sequence is not None:
        return (1, sequence, scan["id"])
    completed_at = scan["completed_at"] if "completed_at" in scan.keys() else None
    timestamp = _timestamp_ns(completed_at)
    if timestamp is None:
        timestamp = _timestamp_ns(scan["started_at"])
    return (0, timestamp if timestamp is not None else 0, scan["id"])


_FindingSites = dict[tuple[str, str | None, PurePosixPath, int, int], dict[str, Any] | None]
# Match the first location shown by compactFinding in finding-catalogue.ts.
_COMPARISON_LOCATION_RANK = {"root_control": 0, "expected_control": 1, "concrete_implementation": 1}


def _unique_finding_sites(findings: list[dict[str, Any]]) -> _FindingSites:
    sites = {}
    for finding in findings:
        if not finding.get("locations"):
            return {}
        location = min(
            finding["locations"],
            key=lambda site: _COMPARISON_LOCATION_RANK.get(site.get("role"), 2),
        )
        key = (
            finding["ruleId"],
            finding["identity"].get("instance"),
            PurePosixPath(location["path"]),
            location["startLine"],
            location.get("endLine", location["startLine"]),
        )
        sites[key] = None if key in sites else finding
    return sites


def _cross_mode_context(
    before: sqlite3.Row,
    after: sqlite3.Row,
    findings: dict[str, list[dict[str, Any]]],
    sites: dict[str, _FindingSites],
) -> dict[str, Any]:
    # Different repository subdirectories can share both the Git revision and clean digest.
    if (
        {before["mode"], after["mode"]} != {"standard", "deep"}
        or before["target_id"] is None
        or before["target_id"] != after["target_id"]
        or before["target_revision"] != after["target_revision"]
        or before["target_snapshot_digest"] is None
        or before["target_snapshot_digest"] != after["target_snapshot_digest"]
        or not findings[before["id"]]
        or not findings[after["id"]]
    ):
        return {}
    for scan_id in (before["id"], after["id"]):
        if scan_id not in sites:
            sites[scan_id] = _unique_finding_sites(findings[scan_id])
    later = sites[after["id"]]
    occurrences = []
    for site, old in sites[before["id"]].items():
        new = later.get(site)
        if old and new and old["identity"]["anchor"] != new["identity"]["anchor"]:
            occurrences.extend((old["occurrenceId"], new["occurrenceId"]))
    return {"crossModeOccurrenceIds": occurrences} if occurrences else {}


def list_unmatched_scan_pairs(
    connection: sqlite3.Connection,
    args: argparse.Namespace,
    *,
    backfill_finding_details: Callable[[sqlite3.Connection, sqlite3.Row], None],
    read_coverage: Callable[[sqlite3.Row], dict[str, Any]],
) -> dict[str, Any]:
    requested_repository = Path(args.repository).expanduser().absolute()
    repository = requested_repository.resolve()
    identities = RepositoryIdentityCache(connection)
    requested = identities.for_path(str(requested_repository))
    if identities.supports_identity:
        requested.require_owner()
    clause, values = identities.scope_for_path(str(requested_repository)).sql(
        supports_generation=identities.supports_generation
    )
    selected = connection.execute(
        f"SELECT * FROM scans WHERE status = 'complete' AND {clause}",
        values,
    ).fetchall()

    selected.sort(key=lambda scan: (timestamp_key(scan["started_at"]), scan["id"]))

    available = []
    for scan in selected:
        try:
            read_coverage(scan)
        except SystemExit:
            continue
        available.append(scan)

    saved_pairs = {
        frozenset((row["before_scan_id"], row["after_scan_id"]))
        for row in connection.execute("SELECT before_scan_id, after_scan_id FROM scan_comparisons")
    }
    focus_scan_id = getattr(args, "after_scan_id", None)
    if focus_scan_id is not None and not any(scan["id"] == focus_scan_id for scan in selected):
        raise SystemExit("The scan to match is not a completed scan in this repository.")
    if focus_scan_id is not None:
        available.sort(key=_scan_completion_order)
    batches = []
    skipped = 0
    matching_findings: dict[str, list[dict[str, Any]]] = {}
    sites: dict[str, _FindingSites] = {}
    known_links: list[sqlite3.Row] | None = None
    for index, after in enumerate(available):
        if focus_scan_id is not None and after["id"] != focus_scan_id:
            continue
        candidates = [
            before
            for before in available[:index]
            if _same_repository(connection, before, after, identities=identities)
        ]
        previous = [
            before
            for before in candidates
            if args.force or frozenset((before["id"], after["id"])) not in saved_pairs
        ]
        skipped += len(candidates) - len(previous)
        if not previous:
            continue
        if known_links is None:
            known_links = (
                []
                if args.force
                else _saved_finding_links(connection, {scan["id"] for scan in selected})
            )
        for scan in (*previous, after):
            if scan["id"] not in matching_findings:
                backfill_finding_details(connection, scan)
                matching_findings[scan["id"]] = [
                    _matching_input(row) for row in _scan_findings(connection, scan["id"]).values()
                ]
        known_groups = _known_finding_groups(
            known_links,
            {
                scan["id"]
                for scan in selected
                if (
                    _scan_completion_order(scan) <= _scan_completion_order(after)
                    if focus_scan_id is not None
                    else (timestamp_key(scan["started_at"]), scan["id"])
                    <= (timestamp_key(after["started_at"]), after["id"])
                )
            },
        )
        batches.append(
            {
                "afterFindings": matching_findings[after["id"]],
                "afterScanId": after["id"],
                "beforeScans": [
                    {
                        **_cross_mode_context(before, after, matching_findings, sites),
                        "findings": matching_findings[before["id"]],
                        "scanId": before["id"],
                    }
                    for before in previous
                ],
                **({"knownFindingGroups": known_groups} if known_groups else {}),
            }
        )
    return {
        "batches": batches,
        "repository": str(repository),
        "scanCount": len(selected),
        "skippedPairs": skipped,
        "unavailableScans": len(selected) - len(available),
    }


def _saved_finding_links(connection: sqlite3.Connection, scan_ids: set[str]) -> list[sqlite3.Row]:
    return [
        row
        for row in _rows_for_ids(
            connection,
            """
            SELECT before.scan_id AS before_scan_id, before.finding_id AS before_finding_id,
                after.scan_id AS after_scan_id, after.finding_id AS after_finding_id
            FROM scan_comparison_matches AS matches
            JOIN finding_occurrences AS before ON before.id = matches.before_occurrence_id
            JOIN finding_occurrences AS after ON after.id = matches.after_occurrence_id
            WHERE matches.before_scan_id IN (SELECT value FROM json_each(?))
            ORDER BY matches.before_scan_id, after.scan_id, before.finding_id, after.finding_id
            """,
            sorted(scan_ids),
        )
        if row["before_scan_id"] in scan_ids and row["after_scan_id"] in scan_ids
    ]


def _finding_aliases(links: Iterable[tuple[str, str]]) -> dict[str, str]:
    parents: dict[str, str] = {}

    def root(value: str) -> str:
        parents.setdefault(value, value)
        while parents[value] != value:
            parents[value] = parents[parents[value]]
            value = parents[value]
        return value

    for before_id, after_id in links:
        parents[root(after_id)] = root(before_id)
    return {finding_id: root(finding_id) for finding_id in parents}


def _known_finding_groups(links: list[sqlite3.Row], scan_ids: set[str]) -> list[list[str]]:
    aliases = _finding_aliases(
        (link["before_finding_id"], link["after_finding_id"])
        for link in links
        if link["before_scan_id"] in scan_ids and link["after_scan_id"] in scan_ids
    )
    groups: dict[str, list[str]] = {}
    for finding_id, identity in aliases.items():
        groups.setdefault(identity, []).append(finding_id)
    return sorted(sorted(group) for group in groups.values() if len(group) > 1)


def compare_scans(
    connection: sqlite3.Connection,
    args: argparse.Namespace,
    *,
    require_scan: Callable[[sqlite3.Connection, str], sqlite3.Row],
    read_coverage: Callable[[sqlite3.Row], dict[str, Any]],
    backfill_finding_details: Callable[[sqlite3.Connection, sqlite3.Row], None] | None = None,
    include_matching_inputs: bool = False,
    require_matches: bool = False,
) -> dict[str, Any]:
    """Compare completed scans of the same repository, returning finding groups and any inputs needed for matching."""
    before = require_scan(connection, args.before_scan_id)
    after = require_scan(connection, args.after_scan_id)
    if before["id"] == after["id"]:
        raise SystemExit("Select two different scans to compare.")
    if before["status"] != "complete" or after["status"] != "complete":
        raise SystemExit("Only completed scans can be compared.")
    if not _same_repository(connection, before, after):
        raise SystemExit("Semantic scan comparisons require the same repository target.")
    cached = connection.execute(
        "SELECT result_json FROM scan_comparisons WHERE before_scan_id = ? AND after_scan_id = ?",
        (before["id"], after["id"]),
    ).fetchone()
    if cached is None and require_matches:
        reversed_comparison = connection.execute(
            "SELECT 1 FROM scan_comparisons WHERE before_scan_id = ? AND after_scan_id = ?",
            (after["id"], before["id"]),
        ).fetchone()
        if reversed_comparison is not None:
            raise SystemExit(
                "These scans are in the wrong order. Run "
                f"'codex-security scans compare {after['id']} {before['id']}'."
            )
        raise SystemExit(
            "No saved matches for these scans. Run 'codex-security scans compare BEFORE AFTER' first."
        )
    if include_matching_inputs and backfill_finding_details is not None:
        backfill_finding_details(connection, before)
        backfill_finding_details(connection, after)
    after_coverage = read_coverage(after)
    before_findings = _scan_findings(connection, before["id"])
    after_findings = _scan_findings(connection, after["id"])
    matches = json.loads(cached["result_json"]) if cached is not None else None
    saved_matches = matches["matches"] if matches is not None else []
    occurrences = before_findings | after_findings
    aliases = _confirmed_finding_aliases(connection, occurrences)
    groups = _finding_groups(before_findings, after_findings, saved_matches, aliases)
    uncertain = (
        {
            (side, match[f"{side}OccurrenceId"]): match["reason"]
            for match in matches.get("uncertain", [])
            for side in ("before", "after")
        }
        if matches is not None
        else {}
    )
    findings: list[dict[str, Any]] = []
    summary = {
        status: 0
        for status in ("new", "persisting", "resolved", "not_observed", "reopened", "unknown")
    }

    for previous_rows, current_rows, match_reason in groups:
        selected_rows = current_rows or previous_rows
        selected = min(selected_rows, key=lambda row: SEVERITY_ORDER[row["severity"]])
        item = {
            "findingId": selected["finding_id"],
            "path": selected["relative_path"],
            "severity": selected["severity"],
            "title": selected["title"],
        }
        side = "after" if current_rows else "before"
        uncertain_reason = next(
            (
                uncertain[(side, row["id"])]
                for row in selected_rows
                if (side, row["id"]) in uncertain
            ),
            None,
        )
        if previous_rows and current_rows:
            status = (
                "reopened"
                if any(
                    row["triage_status"] == "closed" and row["close_reason"] == "already_fixed"
                    for row in previous_rows
                )
                and any(row["triage_status"] == "open" for row in current_rows)
                else "persisting"
            )
            if match_reason is not None:
                item["matchReason"] = match_reason
        elif uncertain_reason is not None:
            status = "unknown"
            item["reason"] = uncertain_reason
        elif current_rows:
            status = "new"
        elif not all(
            scan_covers_path(
                after,
                target_id=after["target_id"],
                path=row["relative_path"],
                coverage=after_coverage,
            )
            for row in previous_rows
        ):
            status = "unknown"
            item["reason"] = "The affected path was excluded or outside the later scope."
        elif all(
            row["triage_status"] == "closed" and row["close_reason"] == "already_fixed"
            for row in previous_rows
        ):
            status = "resolved"
        else:
            status = "not_observed"
            item["reason"] = "Not observed in the later scan; remediation has not been verified."
        if len(previous_rows) == 1:
            item["beforeOccurrenceId"] = previous_rows[0]["id"]
        elif previous_rows:
            item["beforeOccurrenceIds"] = [row["id"] for row in previous_rows]
        if len(current_rows) == 1:
            item["afterOccurrenceId"] = current_rows[0]["id"]
        elif current_rows:
            item["afterOccurrenceIds"] = [row["id"] for row in current_rows]
        if current_rows:
            item["triage"] = {
                "closeReason": selected["close_reason"],
                "status": selected["triage_status"],
            }
        item["status"] = status
        findings.append(item)
        summary[status] += 1

    result = {
        "afterScanId": after["id"],
        "beforeScanId": before["id"],
        "findings": findings,
        "repository": before["target_path"],
        "summary": summary,
    }
    if matches is not None and matches.get("related"):
        related = _separate_finding_pairs(matches["related"], occurrences, aliases)
        if related:
            result["related"] = [
                {
                    **pair,
                    "beforeTitle": occurrences[pair["beforeOccurrenceId"]]["title"],
                    "afterTitle": occurrences[pair["afterOccurrenceId"]]["title"],
                }
                for pair in related
            ]
    if include_matching_inputs:
        identities = RepositoryIdentityCache(connection)
        after_key = (timestamp_key(after["started_at"]), after["id"])
        known_scan_ids = {
            scan["id"]
            for scan in connection.execute("SELECT * FROM scans WHERE status = 'complete'")
            if (timestamp_key(scan["started_at"]), scan["id"]) <= after_key
            and _same_repository(connection, scan, after, identities=identities)
        }
        excluded_pairs = {(before["id"], after["id"]), (after["id"], before["id"])}
        known_groups = _known_finding_groups(
            [
                link
                for link in _saved_finding_links(connection, known_scan_ids)
                if (link["before_scan_id"], link["after_scan_id"]) not in excluded_pairs
            ],
            known_scan_ids,
        )
        inputs = {
            before["id"]: [_matching_input(row) for row in before_findings.values()],
            after["id"]: [_matching_input(row) for row in after_findings.values()],
        }
        result["matchingCached"] = cached is not None
        result["matchingInputs"] = {
            "before": inputs[before["id"]],
            "after": inputs[after["id"]],
            **_cross_mode_context(before, after, inputs, {}),
            **({"knownFindingGroups": known_groups} if known_groups else {}),
        }
    return result


def save_scan_comparison(
    connection: sqlite3.Connection,
    args: argparse.Namespace,
    *,
    now: Callable[[], str],
    require_scan: Callable[[sqlite3.Connection, str], sqlite3.Row],
    read_coverage: Callable[[sqlite3.Row], dict[str, Any]],
) -> dict[str, Any]:
    before = require_scan(connection, args.before_scan_id)
    after = require_scan(connection, args.after_scan_id)
    if before["id"] == after["id"]:
        raise SystemExit("Select two different scans to compare.")
    if before["status"] != "complete" or after["status"] != "complete":
        raise SystemExit("Only completed scans can be compared.")
    if not _same_repository(connection, before, after):
        raise SystemExit("Semantic scan comparisons require the same repository target.")
    read_coverage(after)
    allowed = {
        "before": set(_scan_findings(connection, before["id"])),
        "after": set(_scan_findings(connection, after["id"])),
    }
    matches_json = (
        sys.stdin.read() if getattr(args, "matches_json_stdin", False) else args.matches_json
    )
    try:
        payload = json.loads(matches_json)
    except (TypeError, ValueError) as exc:
        raise SystemExit("Scan comparison matches must be a valid JSON object.") from exc
    if (
        not isinstance(payload, dict)
        or not {"matches", "uncertain"}.issubset(payload)
        or set(payload) - {"matches", "uncertain", "related"}
    ):
        raise SystemExit("Scan comparison matches must contain matches and uncertain arrays.")
    if any(
        not isinstance(payload.get(key, []), list) for key in ("matches", "uncertain", "related")
    ):
        raise SystemExit("Scan comparison matches must contain matches and uncertain arrays.")
    consumed: dict[str, dict[str, int]] = {"before": {}, "after": {}}
    for group, match in enumerate(payload["matches"]):
        if (
            not isinstance(match, dict)
            or match.get("confidence") != "high"
            or not isinstance(match.get("reason"), str)
            or not match["reason"].strip()
        ):
            raise SystemExit("Scan comparison matches must have high confidence and a reason.")
        for side in ("before", "after"):
            occurrences = match.get(f"{side}OccurrenceIds")
            if not isinstance(occurrences, list) or any(
                not isinstance(value, str) for value in occurrences
            ):
                raise SystemExit("Scan comparison matches must identify distinct scan findings.")
            unique = set(occurrences)
            if (
                not occurrences
                or len(unique) != len(occurrences)
                or not unique.issubset(allowed[side])
                or not unique.isdisjoint(consumed[side])
            ):
                raise SystemExit("Scan comparison matches must identify distinct scan findings.")
            consumed[side].update((occurrence_id, group) for occurrence_id in unique)
    uncertain_pairs = set()
    for match in payload["uncertain"]:
        if not _valid_finding_pair(match):
            raise SystemExit("Uncertain scan comparison matches must identify distinct findings.")
        pair = (match["beforeOccurrenceId"], match["afterOccurrenceId"])
        if (
            pair[0] not in allowed["before"]
            or pair[0] in consumed["before"]
            or pair[1] not in allowed["after"]
            or pair[1] in consumed["after"]
            or pair in uncertain_pairs
        ):
            raise SystemExit("Uncertain scan comparison matches must identify distinct findings.")
        uncertain_pairs.add(pair)
    related_pairs = set()
    for match in payload.get("related", []):
        if not _valid_finding_pair(match):
            raise SystemExit("Related scan comparison findings must identify distinct findings.")
        pair = (match["beforeOccurrenceId"], match["afterOccurrenceId"])
        group = consumed["before"].get(pair[0])
        if (
            pair[0] not in allowed["before"]
            or pair[1] not in allowed["after"]
            or (group is not None and group == consumed["after"].get(pair[1]))
            or pair in uncertain_pairs
            or pair in related_pairs
        ):
            raise SystemExit("Related scan comparison findings must identify distinct findings.")
        related_pairs.add(pair)
    timestamp = now()
    with connection:
        connection.execute("BEGIN IMMEDIATE")
        connection.execute(
            "DELETE FROM scan_comparisons "
            "WHERE (before_scan_id = ? AND after_scan_id = ?) "
            "OR (before_scan_id = ? AND after_scan_id = ?)",
            (before["id"], after["id"], after["id"], before["id"]),
        )
        connection.execute(
            """
            INSERT INTO scan_comparisons (
                before_scan_id, after_scan_id, result_json, created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?)
            """,
            (
                before["id"],
                after["id"],
                json.dumps(payload, allow_nan=False, sort_keys=True),
                timestamp,
                timestamp,
            ),
        )
        connection.executemany(
            """
            INSERT INTO scan_comparison_matches (
                before_scan_id, after_scan_id, before_occurrence_id, after_occurrence_id, reason
            ) VALUES (?, ?, ?, ?, ?)
            """,
            (
                (before["id"], after["id"], previous, current, match["reason"])
                for match in payload["matches"]
                for previous in match["beforeOccurrenceIds"]
                for current in match["afterOccurrenceIds"]
            ),
        )
    return compare_scans(connection, args, require_scan=require_scan, read_coverage=read_coverage)


def _valid_finding_pair(value: Any) -> bool:
    return (
        isinstance(value, dict)
        and set(value) == {"beforeOccurrenceId", "afterOccurrenceId", "reason"}
        and all(isinstance(item, str) and item.strip() for item in value.values())
    )


def _rows_for_ids(
    connection: sqlite3.Connection, query: str, ids: Iterable[str]
) -> Iterator[sqlite3.Row]:
    values = tuple(dict.fromkeys(ids))
    if values:
        yield from connection.execute(query, (json.dumps(values),))


# Stable finding IDs already include the target identity. Follow their indexed
# occurrences instead of resolving repository paths or scanning every saved link.
_FINDING_NEIGHBORS_SQL = """
    FROM linked
    CROSS JOIN finding_occurrences AS source
        ON source.finding_id = linked.finding_id
    CROSS JOIN scan_comparison_matches AS matches
        ON matches.before_occurrence_id = source.id OR matches.after_occurrence_id = source.id
    CROSS JOIN finding_occurrences AS neighbor ON neighbor.id = CASE
        WHEN matches.before_occurrence_id = source.id THEN matches.after_occurrence_id
        ELSE matches.before_occurrence_id END
"""
# Traverse only the selected findings' components, including recurring stable IDs.
_LINKED_FINDINGS_SQL = f"""
    WITH RECURSIVE linked(finding_id) AS (
        SELECT occurrences.finding_id
        FROM finding_occurrences AS occurrences
        WHERE occurrences.id IN (SELECT value FROM json_each(?))
        UNION
        SELECT neighbor.finding_id
        {_FINDING_NEIGHBORS_SQL}
    )
"""


def _confirmed_finding_aliases(
    connection: sqlite3.Connection, occurrence_ids: Iterable[str]
) -> dict[str, str]:
    query = f"""
        {_LINKED_FINDINGS_SQL}
        SELECT DISTINCT linked.finding_id AS before_finding_id,
            neighbor.finding_id AS after_finding_id
        {_FINDING_NEIGHBORS_SQL}
    """
    return _finding_aliases(
        (row["before_finding_id"], row["after_finding_id"])
        for row in _rows_for_ids(connection, query, occurrence_ids)
    )


def _separate_finding_pairs(
    pairs: list[dict[str, Any]],
    occurrences: dict[str, sqlite3.Row],
    aliases: dict[str, str],
) -> list[dict[str, Any]]:
    def identity(occurrence_id: str) -> str:
        finding_id = occurrences[occurrence_id]["finding_id"]
        return aliases.get(finding_id, finding_id)

    return [
        pair
        for pair in pairs
        if identity(pair["beforeOccurrenceId"]) != identity(pair["afterOccurrenceId"])
    ]


def finding_relations(
    connection: sqlite3.Connection, scan_id: str, occurrence_ids: Iterable[str]
) -> dict[str, list[dict[str, Any]]]:
    selected = set(occurrence_ids)
    if not selected:
        return {}
    pairs = []
    for comparison in connection.execute(
        "SELECT before_scan_id, after_scan_id, result_json FROM scan_comparisons "
        "WHERE before_scan_id = ? OR after_scan_id = ? "
        "ORDER BY before_scan_id, after_scan_id",
        (scan_id, scan_id),
    ):
        side = "before" if comparison["before_scan_id"] == scan_id else "after"
        other = "after" if side == "before" else "before"
        for pair in json.loads(comparison["result_json"]).get("related", []):
            if pair[f"{side}OccurrenceId"] in selected:
                pairs.append(
                    {
                        "beforeOccurrenceId": pair[f"{side}OccurrenceId"],
                        "afterOccurrenceId": pair[f"{other}OccurrenceId"],
                        "afterScanId": comparison[f"{other}_scan_id"],
                        "reason": pair["reason"],
                    }
                )
    occurrences = {
        row["id"]: row
        for row in _rows_for_ids(
            connection,
            "SELECT id, finding_id, scan_id, title FROM finding_occurrences "
            "WHERE id IN (SELECT value FROM json_each(?))",
            (pair[key] for pair in pairs for key in ("beforeOccurrenceId", "afterOccurrenceId")),
        )
    }
    pairs = [
        pair
        for pair in pairs
        if pair["beforeOccurrenceId"] in occurrences
        and occurrences[pair["beforeOccurrenceId"]]["scan_id"] == scan_id
        and pair["afterOccurrenceId"] in occurrences
        and occurrences[pair["afterOccurrenceId"]]["scan_id"] == pair["afterScanId"]
    ]
    result: dict[str, list[dict[str, Any]]] = {}
    aliases = _confirmed_finding_aliases(connection, (pair["beforeOccurrenceId"] for pair in pairs))
    for pair in _separate_finding_pairs(pairs, occurrences, aliases):
        finding = occurrences[pair["afterOccurrenceId"]]
        result.setdefault(pair["beforeOccurrenceId"], []).append(
            {
                "findingId": finding["finding_id"],
                "occurrenceId": finding["id"],
                "reason": pair["reason"],
                "scanId": pair["afterScanId"],
                "title": finding["title"],
            }
        )
    return result


def finding_management_updated_at(connection: sqlite3.Connection, scan_id: str) -> str | None:
    return connection.execute(
        """
        SELECT MAX(updated_at)
        FROM (
            SELECT triage.updated_at
            FROM finding_triage AS triage
            JOIN finding_occurrences AS occurrences ON occurrences.id = triage.occurrence_id
            WHERE occurrences.scan_id = ?
            UNION ALL
            SELECT remediation.updated_at
            FROM finding_remediation_attempts AS remediation
            JOIN finding_occurrences AS occurrences ON occurrences.id = remediation.occurrence_id
            WHERE occurrences.scan_id = ?
        )
        """,
        (scan_id, scan_id),
    ).fetchone()[0]


def finding_triage_result(connection: sqlite3.Connection, occurrence_id: str) -> dict[str, Any]:
    row = connection.execute(
        "SELECT status, close_reason, note, updated_at FROM finding_triage WHERE occurrence_id = ?",
        (occurrence_id,),
    ).fetchone()
    if row is None:
        return {"status": "open"}
    return {
        "closeReason": row["close_reason"],
        "note": row["note"],
        "status": row["status"],
        "updatedAt": row["updated_at"],
    }


def finding_matches(
    connection: sqlite3.Connection, occurrence_id: str, scan_id: str, started_at: str
) -> tuple[list[dict[str, Any]], str, list[str]]:
    rows = connection.execute(
        """
        SELECT matches.after_scan_id AS scan_id, occurrences.id AS occurrence_id, occurrences.finding_id,
            occurrences.title, matches.reason
        FROM scan_comparison_matches AS matches
        JOIN finding_occurrences AS occurrences ON occurrences.id = matches.after_occurrence_id
        WHERE matches.before_occurrence_id = ?
        UNION
        SELECT matches.before_scan_id AS scan_id, occurrences.id AS occurrence_id, occurrences.finding_id,
            occurrences.title, matches.reason
        FROM scan_comparison_matches AS matches
        JOIN finding_occurrences AS occurrences ON occurrences.id = matches.before_occurrence_id
        WHERE matches.after_occurrence_id = ?
        ORDER BY scan_id, occurrence_id
        """,
        (occurrence_id, occurrence_id),
    ).fetchall()
    linked_rows = list(
        _rows_for_ids(
            connection,
            f"""
            {_LINKED_FINDINGS_SQL}
            SELECT occurrences.id AS occurrence_id, occurrences.finding_id, occurrences.title,
                scans.started_at, scans.id AS scan_id
            FROM linked
            CROSS JOIN finding_occurrences AS occurrences
                ON occurrences.finding_id = linked.finding_id
            CROSS JOIN scans ON scans.id = occurrences.scan_id
            """,
            (occurrence_id,),
        )
    )
    known_scans = sorted(
        {(started_at, scan_id)} | {(row["started_at"], row["scan_id"]) for row in linked_rows},
        key=lambda scan: (timestamp_key(scan[0]), scan[1]),
    )
    included = {occurrence_id, *(row["occurrence_id"] for row in rows)}
    rows.extend(
        {
            **row,
            "reason": "The findings share a stable identity or a previously confirmed link.",
        }
        for row in linked_rows
        if row["occurrence_id"] not in included
    )
    rows.sort(key=lambda row: (row["scan_id"], row["occurrence_id"]))
    known_scan_ids = [known_scans[0][1]]
    if len(known_scans) > 1:
        known_scan_ids.append(known_scans[-1][1])
    return (
        [
            {
                "findingId": row["finding_id"],
                "occurrenceId": row["occurrence_id"],
                "reason": row["reason"],
                "scanId": row["scan_id"],
                "title": row["title"],
            }
            for row in rows
        ],
        known_scans[0][0],
        known_scan_ids,
    )


def _finding_groups(
    before_findings: dict[str, sqlite3.Row],
    after_findings: dict[str, sqlite3.Row],
    matches: list[dict[str, Any]],
    aliases: dict[str, str],
) -> list[tuple[list[sqlite3.Row], list[sqlite3.Row], str | None]]:
    rows = {"before": before_findings, "after": after_findings}
    groups: dict[str, tuple[list[sqlite3.Row], list[sqlite3.Row], list[str]]] = {}

    def group(row: sqlite3.Row) -> tuple[list[sqlite3.Row], list[sqlite3.Row], list[str]]:
        finding_id = row["finding_id"]
        return groups.setdefault(aliases.get(finding_id, finding_id), ([], [], []))

    for match in matches:
        group(rows["before"][match["beforeOccurrenceIds"][0]])[2].append(match["reason"])
    for index, side in enumerate(("before", "after")):
        occurrence_ids = dict.fromkeys(
            chain(
                (value for match in matches for value in match[f"{side}OccurrenceIds"]),
                rows[side],
            )
        )
        for occurrence_id in occurrence_ids:
            row = rows[side][occurrence_id]
            group(row)[index].append(row)
    result = [
        (
            previous,
            current,
            (
                " ".join(dict.fromkeys(reasons))
                if reasons
                else "The findings share a stable identity or a previously confirmed link."
            )
            if previous and current
            else None,
        )
        for previous, current, reasons in groups.values()
    ]
    return sorted(
        result,
        key=lambda group: (
            (group[1] or group[0])[0]["finding_id"],
            (group[1] or group[0])[0]["id"],
        ),
    )


def _matching_input(row: sqlite3.Row) -> dict[str, Any]:
    finding = json.loads(row["details_json"])
    return {
        **finding,
        "findingId": row["finding_id"],
        "occurrenceId": row["id"],
        "remediation": row["remediation"],
        "severity": {"level": row["severity"], **finding.get("severity", {})},
        "summary": row["summary"],
        "title": row["title"],
    }


def finding_occurrence_rows(
    connection: sqlite3.Connection,
    scan_id: str,
    *,
    offset: int,
    limit: int,
    query: str | None = None,
    severity: str | None = None,
    status: str | None = None,
) -> list[sqlite3.Row]:
    connection.create_function("casefold", 1, str.casefold, deterministic=True)
    conditions, values = finding_occurrence_conditions(
        scan_id, query=query, severity=severity, status=status
    )
    severity_order = " ".join(
        f"WHEN '{level}' THEN {rank}" for level, rank in SEVERITY_ORDER.items()
    )
    return connection.execute(
        f"""
        SELECT
            occurrences.id,
            occurrences.finding_id,
            occurrences.title,
            occurrences.summary,
            occurrences.severity,
            occurrences.confidence,
            occurrences.remediation,
            occurrences.details_json,
            occurrences.created_at
        FROM finding_occurrences AS occurrences
        LEFT JOIN finding_triage AS triage ON triage.occurrence_id = occurrences.id
        WHERE {conditions}
        ORDER BY
            CASE occurrences.severity {severity_order} ELSE 5 END,
            occurrences.created_at,
            occurrences.id
        LIMIT ? OFFSET ?
        """,
        (*values, limit, offset),
    ).fetchall()


def finding_occurrence_conditions(
    scan_id: str,
    *,
    query: str | None,
    severity: str | None,
    status: str | None,
) -> tuple[str, list[str]]:
    conditions = ["occurrences.scan_id = ?"]
    values = [scan_id]
    if severity is not None:
        conditions.append("occurrences.severity = ?")
        values.append(severity)
    if status is not None:
        conditions.append("COALESCE(triage.status, 'open') = ?")
        values.append(status)
    if query:
        search = query.strip().casefold()
        if search:
            conditions.append(
                "(instr(casefold(occurrences.title), ?) > 0 "
                "OR instr(casefold(occurrences.summary), ?) > 0 "
                "OR EXISTS ("
                "SELECT 1 FROM finding_locations AS locations "
                "WHERE locations.occurrence_id = occurrences.id "
                "AND instr(casefold(locations.relative_path), ?) > 0))"
            )
            values.extend((search, search, search))
    return " AND ".join(conditions), values


def _scan_findings(connection: sqlite3.Connection, scan_id: str) -> dict[str, sqlite3.Row]:
    """Index one scan's finding rows by the occurrence IDs used in comparisons."""
    rows = connection.execute(
        f"""
        SELECT occurrences.*,
            COALESCE(triage.status, 'open') AS triage_status, triage.close_reason,
            (SELECT relative_path {PRIMARY_FINDING_LOCATION_SQL}) AS relative_path
        FROM finding_occurrences AS occurrences
        LEFT JOIN finding_triage AS triage ON triage.occurrence_id = occurrences.id
        WHERE occurrences.scan_id = ?
        """,
        (scan_id,),
    )
    return {row["id"]: row for row in rows}


def scan_covers_path(
    scan: sqlite3.Row,
    *,
    target_id: str,
    path: str | None,
    coverage: dict[str, Any],
) -> bool:
    """Check whether a completed scan of this target included the path after applying all recorded exclusions."""
    if scan["status"] != "complete" or scan["target_id"] != target_id:
        return False
    if not isinstance(path, str) or not path:
        return False
    included = coverage.get("includePaths")
    if not isinstance(included, list) or not any(
        isinstance(scope, str) and _path_within(path, scope) for scope in included
    ):
        return False
    excluded = coverage.get("excludePaths")
    if not isinstance(excluded, list):
        return False
    if any(isinstance(scope, str) and _path_matches(path, scope) for scope in excluded):
        return False
    exclusions = coverage.get("explicitExclusions")
    if not isinstance(exclusions, list):
        return False
    if any(
        isinstance(exclusion, dict)
        and isinstance(exclusion.get("pattern"), str)
        and _path_matches(path, exclusion["pattern"])
        for exclusion in exclusions
    ):
        return False
    return True


def _path_within(path: str, scope: str) -> bool:
    candidate = PurePosixPath(path)
    parent = PurePosixPath(scope)
    return parent == PurePosixPath(".") or candidate == parent or parent in candidate.parents


def _path_matches(path: str, pattern: str) -> bool:
    return _path_within(path, pattern) or fnmatch.fnmatchcase(path, pattern)


if __name__ == "__main__":
    argparse.ArgumentParser(description=__doc__).parse_args()

SHA-256: 375425ddd2afcaff361905a6c2d9882e99453ece79aa75068d5c6121cd3aa81a