← Files Codex SecurityARCHIVED FILE
scripts/workbench_native_indexes.py
18.1 KB · Nov 18, 2026 · 06:11 UTC
"""Read-only native findings and repository indexes for the Security workbench."""
import argparse
import os
import sqlite3
import sys
from collections.abc import Iterator
from functools import cache
from itertools import islice
from pathlib import Path
from typing import Any
# Some plugin hosts launch Python with safe-path isolation enabled.
sys.path.insert(0, str(Path(__file__).resolve().parent))
import workbench_scan_history as scan_history
from workbench_constants import (
FINDING_SUMMARY_BYTES,
FINDING_TITLE_BYTES,
FINDINGS_PAGE_MAX,
PRIMARY_FINDING_LOCATION_SQL,
)
from workbench_target_state import (
RepositoryIdentityCache,
RepositoryScanScope,
scan_repository_group,
)
from workbench_validation import bounded_output_text, timestamp_key
def list_global_findings(
connection: sqlite3.Connection,
args: argparse.Namespace,
) -> dict[str, Any]:
"""Return a page of indexed findings after applying the requested filters, with nextOffset for continuation."""
limit = min(args.limit, FINDINGS_PAGE_MAX)
query = args.query.strip().casefold() if args.query else ""
identities = RepositoryIdentityCache(connection)
repository = getattr(args, "repository", None)
requested = None
if repository is not None:
repository = identities.history_path(str(Path(repository).expanduser().absolute()))
requested = identities.for_path(repository)
scan_scope = identities.scope_for_path(repository)
else:
scan_scope = None if args.target_id is None else identities.scope(args.target_id)
target = identities.targets.get(args.target_id)
if identities.supports_identity and target is not None:
requested = identities.for_row(target)
findings = (
row
for row in _indexed_findings(connection, identities=identities, scan_scope=scan_scope)
if (args.severity is None or row["severity"] == args.severity)
and (args.status is None or row["status"] == args.status)
and (
not query
or any(
query in value.casefold()
for value in (
row["title"],
row["summary"],
row["target_path"],
row["location_path"],
)
if value is not None
)
)
)
rows = list(islice(findings, args.offset, args.offset + limit + 1))
has_more = len(rows) > limit
return {
"findings": [
{
"confirmedInLatestScan": row["confirmed_in_latest_scan"],
"createdAt": row["created_at"],
"findingId": row["finding_id"],
"knownSince": row["known_since"],
"knownScanIds": row["known_scan_ids"],
"locationPath": row["location_path"],
"matchedFindingIds": row["matched_finding_ids"],
"occurrenceCount": row["occurrence_count"],
"occurrenceId": row["occurrence_id"],
"scanId": row["scan_id"],
"scope": row["scope"],
"severity": {"level": row["severity"]},
"status": row["status"],
"summary": bounded_output_text(row["summary"], FINDING_SUMMARY_BYTES),
"targetId": row["target_id"],
"targetPath": row["target_path"],
"title": bounded_output_text(row["title"], FINDING_TITLE_BYTES),
"updatedAt": row["updated_at"],
}
for row in rows[:limit]
],
"limit": limit,
"nextOffset": args.offset + limit if has_more else None,
"offset": args.offset,
"projectionAvailable": requested is None or requested.ownership_matches,
}
def _legacy_generations(
connection: sqlite3.Connection, identities: RepositoryIdentityCache
) -> dict[str, str]:
return (
{
row["target_id"]: row["generation"]
for row in connection.execute(
"SELECT target_id, MIN(repository_generation) AS generation FROM scans "
"WHERE repository_generation IS NOT NULL GROUP BY target_id "
"HAVING COUNT(DISTINCT repository_generation) = 1"
)
}
if identities.supports_generation
else {}
)
def _indexed_findings(
connection: sqlite3.Connection,
*,
identities: RepositoryIdentityCache | None = None,
scan_scope: RepositoryScanScope | None = None,
legacy_generations: dict[str, str] | None = None,
) -> Iterator[dict[str, Any]]:
identities = identities or RepositoryIdentityCache(connection)
def scope_sql(alias: str) -> tuple[str, tuple[str, ...]]:
return (
scan_scope.sql(alias, supports_generation=identities.supports_generation)
if scan_scope is not None
else ("1", ())
)
def generation_sql(alias: str) -> str:
return f"{alias}.repository_generation" if identities.supports_generation else "NULL"
if legacy_generations is None:
legacy_generations = _legacy_generations(connection, identities)
def repository_group(scan: sqlite3.Row | dict) -> tuple[str, str]:
identity = scan_repository_group(scan)
generation = legacy_generations.get(scan["target_id"])
return (
("repository", generation)
if identity[0] == "target" and generation is not None
else identity
)
target_filter, target_values = scope_sql("scans")
before_filter, before_values = scope_sql("before_scans")
after_filter, after_values = scope_sql("after_scans")
parents: dict[tuple[tuple[str, str], str], tuple[tuple[str, str], str]] = {}
def group(identity: tuple[tuple[str, str], str]) -> tuple[tuple[str, str], str]:
while identity in parents:
identity = parents[identity]
return identity
for match in connection.execute(
f"""
SELECT before_scans.target_id AS before_target_id,
after_scans.target_id AS after_target_id,
{generation_sql("before_scans")} AS before_generation,
{generation_sql("after_scans")} AS after_generation,
before.finding_id AS before_finding_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 scans AS before_scans ON before_scans.id = before.scan_id
JOIN security_targets AS before_targets ON before_targets.id = before_scans.target_id
JOIN finding_occurrences AS after ON after.id = matches.after_occurrence_id
JOIN scans AS after_scans ON after_scans.id = after.scan_id
JOIN security_targets AS after_targets ON after_targets.id = after_scans.target_id
WHERE {before_filter} AND {after_filter}
""",
(*before_values, *after_values),
):
if match["before_target_id"] != match["after_target_id"] and (
match["before_generation"] is None or match["after_generation"] is None
):
continue
before_repository = repository_group(
{
"target_id": match["before_target_id"],
"repository_generation": match["before_generation"],
}
)
after_repository = repository_group(
{
"target_id": match["after_target_id"],
"repository_generation": match["after_generation"],
}
)
if before_repository != after_repository and (
match["before_target_id"] != match["after_target_id"]
or match["before_generation"] is not None
and match["after_generation"] is not None
):
continue
before = group(
(
before_repository,
match["before_finding_id"],
)
)
after = group(
(
after_repository,
match["after_finding_id"],
)
)
if before != after:
parents[after] = before
completion_column = (
"scans.completion_sequence" if "completion_sequence" in identities.scan_columns else "NULL"
)
completed_at_column = (
"scans.completed_at" if "completed_at" in identities.scan_columns else "NULL"
)
completed_scans = sorted(
connection.execute(
f"""
SELECT scans.target_id, scans.id, scans.started_at,
{generation_sql("scans")} AS repository_generation,
{completed_at_column} AS completed_at,
{completion_column} AS completion_sequence
FROM scans
JOIN security_targets AS targets ON targets.id = scans.target_id
WHERE scans.status = 'complete' AND {target_filter}
""",
target_values,
),
key=scan_history._scan_completion_order,
)
latest_scan_by_repository = {repository_group(row): row for row in completed_scans}
selected_latest_scan_id = completed_scans[-1]["id"] if completed_scans else None
grouped: dict[tuple[tuple[str, str], str], list[sqlite3.Row]] = {}
for row in connection.execute(
f"""
SELECT
occurrences.id AS occurrence_id,
occurrences.finding_id,
occurrences.severity,
occurrences.created_at,
scans.id AS scan_id,
scans.started_at AS scan_started_at,
scans.target_id,
{generation_sql("scans")} AS repository_generation,
targets.current_path AS target_path,
scans.scope,
scans.updated_at,
triage.status AS decision_status,
triage.close_reason,
triage.updated_at AS decision_updated_at,
occurrences.title,
occurrences.summary,
(SELECT relative_path {PRIMARY_FINDING_LOCATION_SQL}) AS location_path
FROM finding_occurrences AS occurrences
JOIN scans ON scans.id = occurrences.scan_id
JOIN security_targets AS targets ON targets.id = scans.target_id
LEFT JOIN finding_triage AS triage ON triage.occurrence_id = occurrences.id
WHERE {target_filter}
""",
target_values,
):
grouped.setdefault(
group(
(
repository_group(row),
row["finding_id"],
)
),
[],
).append(row)
# Global comparisons do not bind legacy history across current targets. A scoped
# projection already admits only its own target's legacy rows and decisions.
for identity, occurrences in list(grouped.items()):
current_targets = {
row["target_id"] for row in occurrences if row["repository_generation"] is not None
}
if len(current_targets) <= 1 or scan_scope is not None:
continue
current = []
for row in occurrences:
if row["repository_generation"] is None:
legacy_identity = (scan_repository_group(row), identity[1])
grouped.setdefault(legacy_identity, []).append(row)
else:
current.append(row)
grouped[identity] = current
findings = []
for occurrences in grouped.values():
latest = max(
occurrences,
key=lambda row: (timestamp_key(row["created_at"]), row["occurrence_id"]),
)
decision = max(
(row for row in occurrences if row["decision_status"] is not None),
key=lambda row: (timestamp_key(row["decision_updated_at"]), row["occurrence_id"]),
default=None,
)
status = decision["decision_status"] if decision is not None else "open"
if (
status == "closed"
and decision["close_reason"] == "already_fixed"
and timestamp_key(latest["created_at"]) > timestamp_key(decision["decision_updated_at"])
):
status = "open"
scans = sorted(
{(row["scan_started_at"], row["scan_id"]) for row in occurrences},
key=lambda scan: (timestamp_key(scan[0]), scan[1]),
)
component_latest_scan = max(
(
latest_scan_by_repository[repository]
for repository in {repository_group(row) for row in occurrences}
if repository in latest_scan_by_repository
),
key=scan_history._scan_completion_order,
default=None,
)
latest_scan_id = (
selected_latest_scan_id
if scan_scope is not None
else component_latest_scan["id"]
if component_latest_scan is not None
else None
)
findings.append(
{
**dict(latest),
"close_reason": decision["close_reason"] if decision is not None else None,
"confirmed_in_latest_scan": any(
row["scan_id"] == latest_scan_id for row in occurrences
),
"known_since": scans[0][0],
"known_scan_ids": [scan_id for _, scan_id in scans],
"matched_finding_ids": sorted({row["finding_id"] for row in occurrences}),
"matched_occurrence_ids": sorted({row["occurrence_id"] for row in occurrences}),
"occurrence_count": len(occurrences),
"status": status,
"updated_at": (
max(
latest["updated_at"],
decision["decision_updated_at"],
key=lambda value: (timestamp_key(value), value),
)
if decision is not None
else latest["updated_at"]
),
}
)
findings.sort(key=lambda finding: finding["occurrence_id"])
findings.sort(
key=lambda finding: (
finding["status"] == "open",
-scan_history.SEVERITY_ORDER.get(finding["severity"], 5),
timestamp_key(finding["created_at"]),
),
reverse=True,
)
yield from findings
def list_repositories(
connection: sqlite3.Connection,
args: argparse.Namespace | None = None,
) -> dict[str, Any]:
"""Return tracked targets with scan summaries, finding counts, and current checkout availability."""
by_target: dict[str, dict[str, Any]] = {}
latest_rows: dict[str, sqlite3.Row] = {}
for row in connection.execute(
f"""
SELECT targets.id AS repository_id, targets.display_name, targets.current_path,
{scan_history.SCAN_SUMMARY_COLUMNS}
FROM security_targets AS targets
LEFT JOIN scans ON scans.target_id = targets.id
AND scans.id IN (SELECT scan_id FROM scan_progress)
LEFT JOIN scan_progress AS progress ON progress.scan_id = scans.id
LEFT JOIN scan_lifecycles AS lifecycle ON lifecycle.scan_id = scans.id
ORDER BY julianday(upper(scans.started_at)) DESC, scans.id DESC, targets.id
"""
).fetchall():
target_id = row["repository_id"]
if target_id not in by_target:
by_target[target_id] = {
"displayName": row["display_name"],
"latestScan": None,
"openFindingsCount": 0,
"scanCount": 0,
"targetId": target_id,
"targetPath": row["current_path"],
}
if row["id"] is None:
continue
repository = by_target[target_id]
scan = scan_history.scan_summary(row)
repository["scanCount"] += 1
previous = latest_rows.get(target_id)
if previous is None or (
previous["status"] == row["status"] == "complete"
and scan_history._scan_completion_order(row)
> scan_history._scan_completion_order(previous)
):
latest_rows[target_id] = row
repository["latestScan"] = scan
identities = RepositoryIdentityCache(connection)
legacy_generations = _legacy_generations(connection, identities)
@cache
def open_findings_count(target_id: str) -> int:
if not by_target[target_id]["scanCount"]:
return 0
scope = identities.scope(target_id)
if not scope.available:
return 0
return sum(
row["status"] == "open"
for row in _indexed_findings(
connection,
identities=identities,
scan_scope=scope,
legacy_generations=legacy_generations,
)
)
repositories = list(by_target.values())
pagination = {}
if args is not None:
query = args.query.strip().casefold() if args.query else ""
repositories = [
repository
for repository in repositories
if (args.target_id is None or repository["targetId"] == args.target_id)
and (args.status != "scanned" or repository["scanCount"] > 0)
and (args.status != "not_scanned" or repository["scanCount"] == 0)
and (
not query
or query in repository["displayName"].casefold()
or query in repository["targetPath"].casefold()
)
and (args.status != "open_findings" or open_findings_count(repository["targetId"]) > 0)
]
if args.limit is not None or args.offset:
limit = args.limit or FINDINGS_PAGE_MAX
end = args.offset + limit
pagination = {
"limit": limit,
"nextOffset": end if end < len(repositories) else None,
"offset": args.offset,
}
repositories = repositories[args.offset : end]
for repository in repositories:
repository["openFindingsCount"] = open_findings_count(repository["targetId"])
path = os.path.expanduser(repository["targetPath"])
try:
repository["checkoutAvailable"] = Path(path).is_absolute() and os.path.isdir(
os.path.realpath(path)
)
except (OSError, RecursionError):
repository["checkoutAvailable"] = False
return {"repositories": repositories, **pagination}
if __name__ == "__main__":
argparse.ArgumentParser(description=__doc__).parse_args()
SHA-256: a40b4cfae313b6799541f70dc251508ce9d9a474a2298742c23a823d7819139d