~ knotless Search/
↑↓ move↵ open⇧↵ all results

johnandersen777.bsky.social/vuln-notif

0
0
main
458 lines · 14.7 KB Raw History
#!/usr/bin/env python3
"""Build static viewer assets for the vuln-notif record viewer.

Reads the local SQLite cache, reconstructs the atproto records the service
created for one repo DID, writes dist/graph.json plus dist/config.json, and
copies the viewer assets next to the output.

The SPA seeds its IndexedDB mirror from the PDS listRecords endpoint using the
repo DID in config.json; graph.json is kept for backward compatibility and
offline tooling.

Usage:
    python3 spa/build.py --db .state/prod-week.db
    python3 spa/build.py --db .state/prod-week.db --out /tmp/graph.json

No network access and no third-party Python packages.
"""

from __future__ import annotations

import argparse
import hashlib
import json
import os
import shutil
import sqlite3
import sys
from datetime import datetime, timezone
from pathlib import Path

NVD_COLLECTION = "com.nahdig.cve.nvdV2"
NVD_TYPE = "com.nahdig.cve.nvdV2"
ATPROTO_COLLECTION = "com.nahdig.cve.atprotoV1"
ATPROTO_TYPE = "com.nahdig.cve.atprotoV1"
PROJECT_COLLECTION = "software.release.project"
PROJECT_TYPE = "software.release.project"
VERSION_COLLECTION = "software.release.version"
VERSION_TYPE = "software.release.version"
CHANNEL_COLLECTION = "software.release.channel"
CHANNEL_TYPE = "software.release.channel"
STATUS_COLLECTION = "software.release.status"
STATUS_TYPE = "software.release.status"

ASSETS = ("index.html", "main.js", "graph.js", "db.js", "jetstream.js")
REQUIRED_TABLES = (
    "publications",
    "cves",
    "cve_atproto",
    "software",
    "software_purls",
    "release_projects",
    "release_versions",
    "release_channels",
    "release_status",
    "links",
)


def die(message: str) -> "None":
    print(f"error: {message}", file=sys.stderr)
    raise SystemExit(1)


def parse_at_uri(uri: str):
    """Return (did, collection, rkey) for an at:// record URI, else None."""
    if not isinstance(uri, str) or not uri.startswith("at://"):
        return None
    parts = uri[5:].split("/")
    if len(parts) < 3 or not parts[0] or not parts[1]:
        return None
    return parts[0], parts[1], parts[2]


def at_uri(did: str, collection: str, rkey: str) -> str:
    return f"at://{did}/{collection}/{rkey}"


def strong_ref(uri: str, cid: str | None):
    ref = {"uri": uri}
    if cid:
        ref["cid"] = cid
    return ref


class GraphBuilder:
    def __init__(self) -> None:
        self.nodes: dict[str, dict] = {}
        self.edges: list[dict] = []
        self.edge_keys: set[tuple[str, str, str]] = set()

    def add_node(self, uri: str, record: dict) -> None:
        if not uri or uri in self.nodes:
            return
        parsed = parse_at_uri(uri)
        if parsed is None:
            return
        did, collection, rkey = parsed
        self.nodes[uri] = {
            "id": uri,
            "uri": uri,
            "did": did,
            "collection": collection,
            "rkey": rkey,
            "label": rkey,
            "record": record,
        }

    def add_placeholder(self, uri: str) -> None:
        parsed = parse_at_uri(uri)
        if parsed is None:
            return
        self.add_node(uri, {"$type": parsed[1], "uri": uri})

    def add_edge(self, source: str, target: str, label: str, collection: str) -> None:
        if parse_at_uri(source) is None or parse_at_uri(target) is None:
            return
        key = (source, target, label)
        if key in self.edge_keys:
            return
        self.edge_keys.add(key)
        self.add_placeholder(source)
        self.add_placeholder(target)
        self.edges.append(
            {
                "source": source,
                "target": target,
                "label": label,
                "collection": collection,
            }
        )


def open_database(path: Path) -> sqlite3.Connection:
    if not path.is_file():
        die(f"database not found: {path}")
    try:
        connection = sqlite3.connect(f"{path.resolve().as_uri()}?mode=ro", uri=True)
    except sqlite3.Error as exc:
        die(f"could not open database {path}: {exc}")
    connection.row_factory = sqlite3.Row
    return connection


def check_schema(connection: sqlite3.Connection, path: Path) -> None:
    existing = {
        row[0]
        for row in connection.execute(
            "SELECT name FROM sqlite_master WHERE type = 'table'"
        )
    }
    missing = [table for table in REQUIRED_TABLES if table not in existing]
    if missing:
        die(
            f"{path} is not a vuln-notif cache (missing tables: {', '.join(missing)})"
        )


def discover_repo_did(connection: sqlite3.Connection) -> str | None:
    for table in ("publications", "software", "cve_atproto"):
        row = connection.execute(
            f"SELECT repo_did FROM {table} "
            "WHERE repo_did IS NOT NULL AND repo_did != '' "
            "ORDER BY rowid LIMIT 1"
        ).fetchone()
        if row is not None:
            return row[0]
    return None


def load_links(connection: sqlite3.Connection, repo_did: str) -> list[sqlite3.Row]:
    return connection.execute(
        "SELECT src_uri, src_collection, path, subject_uri, subject_cid "
        "FROM links WHERE repo_did = ? "
        "ORDER BY src_uri, path, subject_uri",
        (repo_did,),
    ).fetchall()


def add_nvd_nodes(
    builder: GraphBuilder, connection: sqlite3.Connection, repo_did: str
) -> dict[str, dict]:
    """Add NVD record nodes and return uri -> record for nesting into wrappers."""
    records: dict[str, dict] = {}
    rows = connection.execute(
        "SELECT p.cve_id, p.uri, c.data "
        "FROM publications p JOIN cves c ON c.id = p.cve_id "
        "WHERE p.repo_did = ? AND p.status IN ('present', 'created') "
        "ORDER BY p.cve_id",
        (repo_did,),
    ).fetchall()
    for row in rows:
        try:
            record = json.loads(row["data"])
        except (TypeError, ValueError) as exc:
            print(
                f"warning: skipping NVD record {row['cve_id']}: invalid JSON ({exc})",
                file=sys.stderr,
            )
            continue
        uri = row["uri"] or at_uri(repo_did, NVD_COLLECTION, row["cve_id"])
        builder.add_node(uri, record)
        records[uri] = record
    return records


def add_atproto_nodes(
    builder: GraphBuilder,
    connection: sqlite3.Connection,
    repo_did: str,
    nvd_records: dict[str, dict],
) -> set[str]:
    """Add atproto CVE nodes, nesting the referenced NVD record inside each.

    Returns the NVD URIs that were folded into an atproto wrapper; those nodes
    are removed from the graph so a wrapped CVE is not shown twice.
    """
    embedded: set[str] = set()
    rows = connection.execute(
        "SELECT a.cve_id, a.uri, p.uri AS nvd_uri, p.cid AS nvd_cid "
        "FROM cve_atproto a "
        "LEFT JOIN publications p "
        "  ON p.repo_did = a.repo_did AND p.cve_id = a.cve_id "
        "WHERE a.repo_did = ? AND a.status IN ('present', 'created') "
        "ORDER BY a.cve_id",
        (repo_did,),
    ).fetchall()
    for row in rows:
        record = {"$type": ATPROTO_TYPE, "cve": row["cve_id"]}
        if row["nvd_uri"]:
            nvd_ref = strong_ref(row["nvd_uri"], row["nvd_cid"])
            nvd_record = nvd_records.get(row["nvd_uri"])
            if nvd_record is not None:
                record["nvd"] = {**nvd_ref, "record": nvd_record}
                embedded.add(row["nvd_uri"])
            else:
                record["nvd"] = nvd_ref
        uri = row["uri"] or at_uri(repo_did, ATPROTO_COLLECTION, row["cve_id"])
        builder.add_node(uri, record)
    return embedded


def add_release_nodes(
    builder: GraphBuilder, connection: sqlite3.Connection, repo_did: str
) -> None:
    projects: dict[str, str] = {}
    project_rows = connection.execute(
        "SELECT slug, project_uri FROM release_projects "
        "WHERE repo_did = ? AND status = 'created' ORDER BY slug",
        (repo_did,),
    ).fetchall()
    for row in project_rows:
        uri = row["project_uri"] or at_uri(repo_did, PROJECT_COLLECTION, row["slug"])
        projects[row["slug"]] = uri
        builder.add_node(uri, {"$type": PROJECT_TYPE, "name": row["slug"]})

    version_rows = connection.execute(
        "SELECT v.project_slug, v.version, v.rkey, v.uri, v.manifest_uri, v.manifest_cid, "
        "       p.project_uri "
        "FROM release_versions v "
        "LEFT JOIN release_projects p "
        "  ON p.repo_did = v.repo_did AND p.slug = v.project_slug "
        "WHERE v.repo_did = ? AND v.status = 'created' "
        "ORDER BY v.project_slug, v.version",
        (repo_did,),
    ).fetchall()
    for row in version_rows:
        project_uri = (
            row["project_uri"]
            or projects.get(row["project_slug"])
            or at_uri(repo_did, PROJECT_COLLECTION, row["project_slug"])
        )
        record = {
            "$type": VERSION_TYPE,
            "project": project_uri,
            "version": row["version"],
        }
        if row["manifest_uri"]:
            record["manifest"] = strong_ref(row["manifest_uri"], row["manifest_cid"])
        uri = row["uri"] or at_uri(repo_did, VERSION_COLLECTION, row["rkey"])
        builder.add_node(uri, record)

    channel_rows = connection.execute(
        "SELECT c.project_slug, c.name, c.rkey, c.uri, c.version_uri, c.version_cid, "
        "       p.project_uri "
        "FROM release_channels c "
        "LEFT JOIN release_projects p "
        "  ON p.repo_did = c.repo_did AND p.slug = c.project_slug "
        "WHERE c.repo_did = ? AND c.status = 'created' "
        "ORDER BY c.project_slug, c.name",
        (repo_did,),
    ).fetchall()
    for row in channel_rows:
        project_uri = (
            row["project_uri"]
            or projects.get(row["project_slug"])
            or at_uri(repo_did, PROJECT_COLLECTION, row["project_slug"])
        )
        record = {
            "$type": CHANNEL_TYPE,
            "project": project_uri,
            "name": row["name"],
        }
        if row["version_uri"]:
            record["version"] = strong_ref(row["version_uri"], row["version_cid"])
        uri = row["uri"] or at_uri(repo_did, CHANNEL_COLLECTION, row["rkey"])
        builder.add_node(uri, record)

    status_rows = connection.execute(
        "SELECT subject_uri, status, rkey, uri FROM release_status "
        "WHERE repo_did = ? ORDER BY rkey",
        (repo_did,),
    ).fetchall()
    for row in status_rows:
        uri = row["uri"] or at_uri(repo_did, STATUS_COLLECTION, row["rkey"])
        builder.add_node(
            uri,
            {
                "$type": STATUS_TYPE,
                "subject": row["subject_uri"],
                "status": row["status"],
            },
        )


def build_graph(connection: sqlite3.Connection, repo_did: str) -> GraphBuilder:
    builder = GraphBuilder()
    link_rows = load_links(connection, repo_did)

    nvd_records = add_nvd_nodes(builder, connection, repo_did)
    add_atproto_nodes(builder, connection, repo_did, nvd_records)

    # NVD records are pointers/raw payloads for the atproto CVE wrapper. Never
    # render them as standalone graph nodes; the wrapper embeds `nvd.record`.
    nvd_uris = {
        uri
        for uri, node in builder.nodes.items()
        if node["collection"] == NVD_COLLECTION
    }
    for uri in nvd_uris:
        builder.nodes.pop(uri, None)

    add_release_nodes(builder, connection, repo_did)

    for row in link_rows:
        if row["src_uri"] in nvd_uris or row["subject_uri"] in nvd_uris:
            continue
        if any(
            marker in row["src_uri"] or marker in row["subject_uri"]
            for marker in ("/com.nahdig.software/", "/com.nahdig.purl/")
        ) or row["src_collection"] in ("com.nahdig.software", "com.nahdig.purl"):
            continue
        builder.add_edge(
            row["src_uri"],
            row["subject_uri"],
            row["path"],
            row["src_collection"],
        )
    return builder


def write_output(builder: GraphBuilder, repo_did: str, out_path: Path) -> None:
    if not builder.nodes:
        die(
            "no uploaded records found for repo DID "
            f"{repo_did}; check --db and --repo-did"
        )

    nodes = list(builder.nodes.values())
    generated_at = (
        datetime.now(timezone.utc)
        .isoformat(timespec="seconds")
        .replace("+00:00", "Z")
    )
    payload = {
        "generated_at": generated_at,
        "repo_did": repo_did,
        "counts": {"nodes": len(nodes), "edges": len(builder.edges)},
        "nodes": nodes,
        "edges": builder.edges,
    }

    out_path.parent.mkdir(parents=True, exist_ok=True)
    out_path.write_text(
        json.dumps(payload, ensure_ascii=False, indent=2) + "\n", encoding="utf-8"
    )

    config_path = out_path.parent / "config.json"
    config_path.write_text(
        json.dumps(
            {"repo_did": repo_did, "generated_at": generated_at},
            ensure_ascii=False,
            indent=2,
        )
        + "\n",
        encoding="utf-8",
    )


def copy_assets(out_dir: Path) -> None:
    source_dir = Path(__file__).resolve().parent
    for asset in ASSETS:
        source = source_dir / asset
        if not source.is_file():
            die(f"missing viewer asset: {source}")
        shutil.copy2(source, out_dir / asset)


def main() -> None:
    parser = argparse.ArgumentParser(
        description="Build spa/dist/graph.json from a vuln-notif SQLite cache."
    )
    parser.add_argument(
        "--db",
        default=os.environ.get("CACHE_DB", ".state/prod-week.db"),
        help="SQLite cache path (default: $CACHE_DB or .state/prod-week.db)",
    )
    parser.add_argument(
        "--out",
        default=str(Path(__file__).resolve().parent / "dist" / "graph.json"),
        help="output graph.json path (default: spa/dist/graph.json)",
    )
    parser.add_argument(
        "--repo-did",
        default=None,
        help="repo DID to export (default: first one in publications, software, or cve_atproto)",
    )
    args = parser.parse_args()

    db_path = Path(args.db)
    out_path = Path(args.out)

    connection = open_database(db_path)
    try:
        check_schema(connection, db_path)
        repo_did = args.repo_did or discover_repo_did(connection)
        if not repo_did:
            die(
                "no repo DID found in publications, software, or cve_atproto; "
                "pass --repo-did"
            )
        builder = build_graph(connection, repo_did)
    except sqlite3.Error as exc:
        die(f"could not read {db_path}: {exc}")
    finally:
        connection.close()

    write_output(builder, repo_did, out_path)
    copy_assets(out_path.parent)
    print(
        f"wrote {out_path} ({len(builder.nodes)} nodes, "
        f"{len(builder.edges)} edges, repo {repo_did})"
    )
    print(f"wrote {out_path.parent / 'config.json'} (repo {repo_did})")
    print(f"copied {', '.join(ASSETS)} to {out_path.parent}")


if __name__ == "__main__":
    main()