# Publishing CVE information as structured data

We publish vulnerability fixes and matching CVE documentation nearly every month. These are [documented on GitHub in HTML](https://github.com/dotnet/announcements/issues?q=is%3Aissue%20state%3Aopen%20label%3ASecurity) for people to read. It is well understood that HTML is a presentation format and not appropriate for structured processing. We intend to offer CVE information in structured JSON format (and likely markdown, too) to enable a broader set of scenarios and to make consumption cheaper and less error prone.

We expect that many if not most users consume the CVE information we publish through [GitHub](https://github.com/dotnet/announcements/issues/315) as opposed to [cve.org](https://www.cve.org/CVERecord?id=CVE-2024-30105) or [MSRC](https://msrc.microsoft.com/update-guide/vulnerability/CVE-2024-30105) even though those latter sources are consider more authoritative by security professionals. Those latter CVE posting platforms appear even further down the presentation spectrum than our github.com pages. This is (I'm guessing) part of the reason that [MSRC has adopted CSAF](https://msrc.microsoft.com/blog/2024/11/toward-greater-transparency-publishing-machine-readable-csaf-files/). It should be trivial for us to convert this format into [CSAF](https://www.csaf.io/) should MSRC need us to.

This schema has been designed around a "product + packages" model that reflects how many modern developer-focused platforms are structured - with product components and an ecosystem of packages. This pattern is prevalent across the industry (editors with extensions, runtimes with packages, apps with plugins), making this schema broadly applicable beyond its initial .NET use case.

We are adopting this schema over CSAF because it enables us to tailor the schema to our goals, without giving up on interchange with CSAF. We're happy if security professionals use the CVE information that we publish in this new schema, however, it is primarily intended to be consumed by our broad user base that wants straightforward access for operational security. It also makes sense for the MSRC organization to be the sole publisher of official CSAF files at Microsoft.

This new CVE format will be part of a larger queryable information graph that we'll document soon. For now, we'll focus on the CVE format.

Additional documents:

- [.NET CVE Schema Implementation Guidance](./dotnet-guidance.md) provides insight and guidance on how this schema will be used by the .NET Team at Microsoft.
- [CVE Query Catalog](./cve_queries.md) provices a extensive list of `jq` queries.

## Schema

- [Schema](./dotnet-cves-schema.json)
- [Example](./cve.json)

## Design philosophy

We've taken a significant departure with this schema design, battle-testing it with `jq` and some other tools as part of the development process. This approach has resulted in significant schema evolution relative to a more conventional fully normalized approach. The typically fully-normalized approach has production and aesthetic purity as goals, while the philosophy adopted here is more focused on consumption while continuing to appreciate [DRY principles](https://en.wikipedia.org/wiki/Don%27t_repeat_yourself).

The new intuition:

- `jq` query ease is a proxy for LLM consumption ease.
- Bad `jq` queries == bad schema
- Good `jq` querties ==  good LLM consumption

We intend to design all schemas with `jq` battle-testing going forward.

## Quick pitch

Common queries answered by this schema:

- "Which CVEs were reported this month?" → `.disclosures[].id`
- "Which CVEs affect .NET 8.0?" → `release_cves["8.0"]`
- "Which commits fix CVE-2024-38095?" → `cve_commits["CVE-2024-38095"]`
- "Which products need updates?" → Browse `products[]` array
- "Which packages need updates?" → Browse `packages[]` array
- "What's the fix for System.Text.Json?" → Filter `packages[]` by name

## Prior art

[CVE Assignment Information Format](https://cve.mitre.org/cve/list_rules_and_guidance/cve_assignment_information_format.html) tackles much the same problem.

The vocabulary of the cve.org schema and the proposed one (per this spec) match closely. Key alignments:

- `[CVEID]` maps to our `id` and `cve_id` fields
- `[PRODUCT]` is represented through our `products[]` array and `product_cves` index
- `[VERSION]` is represented in our `products[]` and `packages[]` version fields
- `[PROBLEMTYPE]` maps to our `problem` field
- `[REFERENCES]` maps to our `references` array
- `[DESCRIPTION]` maps to our `description` array
- `[ASSIGNINGCNA]` maps to our optional `cna` object

That means that interchange should be trivial.

See Appendix for more information on the cve.org format.

## Key Design Decisions

The primary value add of the proposed schema is:

- Treats queryability and automation as a primary design concern.
- High-level CVE descriptions are separated from applicability (products, packages, and the version dimension for both).
- Enables limiting CVE applicability to one or more platforms or architectures
- Commits are a first-class concept, referenced from products and packages
- Biases to a normalized structure while tactically breaking it for queryability
- Limit properties that are allowed to be absent or `null` as they complicate queries.
- Uses snake_case_lower to align with best-practice `jq` ergomics
- Aligns with CVSS specification standards for interoperability

The design successfully balances:

- Human readability with machine parseability
- Theoretical correctness with practical usability
- Specific .NET needs with general applicability
- Current requirements with future extensibility

### 1. Arrays vs Dictionaries

Data structures are chosen based on access patterns:

- **Arrays for data (discovery)**: `disclosures[]`, `products[]`, and `packages[]` contain the actual CVE and affected component data as arrays because users discover this information rather than knowing it a priori. The naming directly reflects the "products + packages" model.
- **Dictionaries for indices (lookup)**: `commits{}`, `cve_commits{}`, and `release_cves{}` provide fast lookups for known keys.

"known keys" refers to information that would be known ahead of time (like a version number) or that would be discovered by navigating one of the arrays.

This clear separation - arrays for what happened, dictionaries for finding things - creates a consistent mental model and emphasizes the role of indices as the primary lookup mechanism.

### 2. Commit Data

Commit details are stored once in a top-level `commits{}` dictionary, with other sections referencing commits by key.

Benefits:

- Eliminates data duplication
- Ensures consistency
- Reduces file size
- Maintains a single source of truth

#### Commit URL Format – Prefer `.diff` for Tool/LLM Consumption

We intentionally publish commit URLs in their `raw diff` form by appending `.diff` (e.g., `https://github.com/dotnet/runtime/commit/<hash>.diff`). This design choice is based on practical constraints observed with current LLM and automated tooling environments:

- Most hosted LLM services cannot synthesize or mutate outbound URLs (they cannot reliably "add .diff" or alter paths); they can only access URLs explicitly present in the user‑supplied context/files. Surfacing the machine‑optimized form directly ensures the diff is immediately retrievable.
- The GitHub commit HTML view contains significant presentational markup that adds noise for machine processing, while the `.diff` variant provides a concise, structured, line-oriented representation ideal for parsing, summarization, and semantic embedding.
- Relying on clients to “add `.diff` themselves” fails in environments where outbound request mutation is disallowed or brittle.
- Making the data file immediately linkable to machine-friendly content fulfills the schema’s goal of end-to-end automation readiness.

Human-facing tools (web dashboards, docs generators) that prefer the rich HTML commit page can trivially derive it by stripping the trailing `.diff` segment.

Policy:

- All `commits[key].url` values SHOULD use the `.diff` form.
- Downstream tools MAY normalize to the HTML form by stripping `.diff` for end-user hyperlinks.
- If a hosting forge provides multiple raw formats (e.g., `.patch`), `.diff` is chosen for minimal size and sufficient context; `.patch` includes mail-style headers that only add value for niche analysis.

This pattern exemplifies a broader principle: if a resource is intended to be machine-consumed, publish the machine-optimized representation directly instead of assuming transformation steps the consumer might be unable to perform.

#### Commit Key Format

The schema does not prescribe a specific format for commit dictionary keys, allowing flexibility for different repository structures. The full 40-character commit hash is an obvious choice. However, Microsoft uses a compact `repo@shortcommit` format that is both smaller and more informative—see [.NET CVE Schema Implementation Guidance](./dotnet-guidance.md#commits) for details on this approach and its benefits.

### 3. Two-Layer Denormalization Strategy

The schema employs a two-layer approach to denormalization:

#### Layer One: Core Data Structure (Flat Arrays)

The fundamental data lives in four structures:

- `disclosures[]` - The CVE metadata (array)
- `products[]` - Product vulnerabilities (flat, denormalized array)
- `packages[]` - Package vulnerabilities (flat, denormalized array)  
- `commits{}` - Commit details (normalized dictionary)

The `products[]` and `packages[]` arrays intentionally use a flat structure where each entry represents a unique combination of (component + CVE + release). This means a product like ".NET" appears multiple times - once for each vulnerability. This structural denormalization eliminates complex joins at query time.

**Note on terminology**: `release` refers to major.minor version families (e.g., "8.0") that group all patch versions (8.0.0, 8.0.1, 8.0.7, etc.). This aligns with existing .NET release schemas and makes the format reusable by other versioned products. `release` is the same as `channel_version` used in other schemas.

#### Layer Two: Computed Indices (Query Accelerators)

On top of the core data, we provide pre-computed indices that serve as "join as a service":

#### `cve_commits`

Maps CVE IDs to commit keys:

```json
"cve_commits": {
  "CVE-2024-38095": ["runtime@979135d", "runtime@4a8d5a0"]
}
```

Enables simple lookup: "Which commits fix this CVE?"

#### `release_cves`

Maps release families to affected CVEs:

```json
"release_cves": {
  "8.0": ["CVE-2024-30105", "CVE-2024-35264", "CVE-2024-38095"]
}
```

Answers: "Which CVEs affect .NET 8.0?" (including products and packages with `release: "8.0"`)

#### `cve_releases`

Maps CVE IDs to affected release families:

```json
"cve_releases": {
  "CVE-2024-38095": ["6.0", "8.0"]
}
```

Answers: "Which releases are affected by this CVE?" (reverse lookup of release_cves)

#### `product_cves`

Maps product identifiers to CVE IDs:

```json
"product_cves": {
  "dotnet-runtime": ["CVE-2024-30105", "CVE-2024-38081", "CVE-2024-38095"],
  "dotnet-aspnetcore": ["CVE-2024-35264"]
}
```

Answers: "Which CVEs affect product X?"

#### `product_name`

Maps canonical product slugs (used in `products[].name` and related indices) to human-display names. Keys are stable identifiers; values are presentation labels. Consumers SHOULD use the slug for joins and the mapped value for UI.

```json
"product_name": {
  "dotnet-runtime": ".NET Runtime Libraries",
  "dotnet-aspnetcore": "ASP.NET Core Runtime"
}
```

This index serves a different purpose than the others - it's not about relationships but about presentation. Many queries work fine with just the product identifiers (dotnet-runtime, dotnet-aspnetcore), but when generating human-readable reports or markdown tables, this lookup provides the properly formatted names.

#### `package_cves`

Maps package names to CVE IDs:

```json
"package_cves": {
  "System.Text.Json": ["CVE-2024-30105"],
  "System.Formats.Asn1": ["CVE-2024-38095"],
  "Microsoft.IO.Redist": ["CVE-2024-38081"]
}
```

Answers: "Which CVEs affect package X?" This complements `product_cves` for package-level vulnerability tracking.

#### `severity_cves`

Maps CVSS severity levels to CVE IDs:

```json
"severity_cves": {
  "CRITICAL": [],
  "HIGH": ["CVE-2024-30105", "CVE-2024-35264", "CVE-2024-38081", "CVE-2024-38095"],
  "MEDIUM": [],
  "LOW": []
}
```

Answers: "Which CVEs have HIGH severity?" This enables quick filtering by severity without scanning all disclosures.

### 4. Indices as Extensible Value-Adds

The indices are distinct from the core schema in important ways:

1. **Optional but Valuable**: The core schema (disclosures, products, packages, commits) contains all the source data. The indices are computed projections that accelerate common queries. A consumer could use the schema without the indices by computing the lookups themselves.

2. **Extensible by Design**: New indices can be added in a backward-compatible way as new query patterns emerge. For example:
   - `date_cves`: Map dates/months to CVEs for temporal queries for cve.json files that describe longer time periods
   - `commit_products`: Map commits to affected products
   - `weakness_cves`: Map CWE identifiers to CVE lists

3. **Implementation Flexibility**: While we provide these indices in the JSON, consumers could:
   - Build them dynamically on ingestion
   - Store them in a database as materialized views
   - Ignore them entirely and query the raw arrays

The indexes are a design tradeoff. The intent of the schema is to provide a fully normalized design (in a relational database sense) and also enable ergonomic `jq` queries. Those were found (unsurprisingly) to be at odds. The calculated indexes tactically break the normalization principle by introducing redundancy, but they transform complex multi-stage joins into simple lookups. Without them, common queries would require nested loops and variable juggling that make both human and LLM consumption difficult. We chose pragmatism over purity - the indices are a small storage compromise (just arrays of IDs) that provide enormous value in query simplicity. They serve as pre-computed views that make the schema actually usable.

### 5. Structural Invariant: Products and Packages Identity

The `products[]` and `packages[]` arrays have **identical structure** - every field that exists in one exists in the other with the same meaning and data type. This structural invariant is a key design principle that enables uniform query patterns.

**Common structure:**

- `cve_id`: CVE identifier this entry relates to (primary / foreign key model)
- `name`: Component identifier for lookup and grouping
- `min_vulnerable`, `max_vulnerable`, `fixed`: Version range fields
- `commits`: Array of commit keys referencing the commits dictionary (optional)
- `release`: Release family like "8.0" (optional for some packages)

**Query Benefits:**
This identical structure allows queries to treat both arrays uniformly using `[.products[], .packages[]]` patterns, eliminating the need for separate logic paths or duplicate query code.

**Invariant Maintenance:**
Any new field added to one array must be added to the other (with appropriate defaults/null values where not applicable) to maintain this structural consistency.

Notes:

- The `release` property is optional for packages that aren't tied to a specific .NET release family (e.g., Microsoft.IO.Redist).
- The `commits` property is optional, but encouraged for open source projects.

### 6. Query Optimization

The schema prioritizes common query patterns and uses **snake_case_lower** field naming for optimal `jq` ergonomics.

#### Optimized Query Patterns

```bash
# Simple CVE lookup
jq -r '.disclosures[] | select(.cvss.severity == "HIGH")'

# Direct version access  
jq -r '.products[] | select(.release == "8.0")'

# Clean commit lookups
jq -r '. as $root | .cve_commits["CVE-2024-38095"][] | $root.commits[.].url'
```

#### Field Naming Convention

The schema uses `snake_case_lower` for all field names to enable clean dot notation in `jq` queries:

```bash
# snake_case enables clean dot notation for field names
jq -r '.packages[] | select(.cve_id == "CVE-2024-38095") | .min_vulnerable'

# kebab-case would require verbose bracket notation for field names  
jq -r '.packages[] | select(.["cve-id"] == "CVE-2024-38095") | .["min-vulnerable"]'
```

**Design rationale:**
- **snake_case_lower**: Enables clean `jq` dot notation (`.field_name`)
- **camelCase**: Would also work with dot notation but is less common in JSON APIs and considered less aesthetic for this domain
- **kebab-case-lower**: Requires verbose bracket notation (`.["field_name"]`) making queries harder to read and write

Since queryability is a primary design concern, the ergonomic advantage of dot notation significantly improves the developer experience for the most common operations.

#### Brand Names vs Schema Fields: Mixed Naming Strategy

The schema uses a **semantic distinction** in naming conventions:

- **Schema fields**: `snake_case_lower` (e.g., `cve_id`, `min_vulnerable`, `release_cves`)
- **Brand identifiers**: `kebab-case-lower` (e.g., `dotnet-runtime`, `dotnet-sdk`)

This creates different ergonomic trade-offs depending on context:

**When brand names appear as data values** (most common case), naming convention has no impact on query syntax:

```bash
# Works identically regardless of product name convention
jq -r '.products[] | select(.name == "dotnet-runtime") | .fixed'
jq -r '.packages[] | map(select(.name | startswith("dotnet")))'
```

**When brand names appear as dictionary keys**, kebab-case requires bracket notation but enables ecosystem consistency:

```bash
# Current approach (kebab-case keys) - requires brackets but matches ecosystem conventions
jq -r '.product_cves["dotnet-runtime"][]'
jq -r '.product_cves["dotnet-aspnetcore"][]'

# Alternative (snake_case keys) - would enable dot notation but break ecosystem alignment  
# jq -r '.product_cves.dotnet_runtime_libraries[]'  # hypothetical
```

**Design rationale for keeping kebab-case brand identifiers:**

1. **Ecosystem consistency**: Product names like `dotnet-runtime` align with how these components appear in Docker images, GitHub repositories, package managers, and CI/CD systems
2. **Semantic clarity**: The naming distinction helps distinguish between "schema concerns" (snake_case) and "domain entities" (kebab-case)  
3. **Limited impact**: Most operational queries work against data arrays where naming convention doesn't affect syntax
4. **Brand integrity**: Product identifiers should match their representation across the broader ecosystem

The slight ergonomic cost for index-based queries is outweighed by ecosystem consistency and semantic clarity.

#### CVSS Specification Alignment

The schema aligns with official CVSS standards for maximum interoperability with security tooling and databases.

**CVSS Field Structure:**
- **`version`**: CVSS version ("3.1", "4.0", etc.)
- **`vector`**: Standard CVSS vector string format (e.g., "CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:N/I:N/A:H")
- **`score`**: Numeric base score (e.g., 7.5)
- **`severity`**: Qualitative severity rating derived from score ("CRITICAL", "HIGH", "MEDIUM", "LOW")

The `score` and `severity` fields provide pre-computed values that would otherwise require parsing and calculating from the vector string. This follows the schema's principle of making common data immediately accessible rather than requiring client-side computation.

**Standards Compliance:**
- Follows [CVSS v3.1 Specification](https://www.first.org/cvss/specification-document) field naming
- Compatible with NVD JSON schema and CVE Project standards
- Enables seamless integration with CVSS calculators and risk assessment tools
- Future-compatible with CVSS v4.0 when adopted

**Example:**
```json
{
  "cvss": {
    "version": "3.1",
    "vector": "CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:N/I:N/A:H",
    "score": 7.5,
    "severity": "HIGH"
  }
}
```

This alignment ensures the schema integrates seamlessly with existing vulnerability management workflows and security tools that expect standard CVSS formatting.

#### Timeline Structure

The `timeline` field captures key dates in the vulnerability lifecycle with structured event objects:

```json
{
  "timeline": {
    "disclosure": {
      "date": "2024-07-09",
      "description": "Publicly disclosed"
    },
    "fixed": {
      "date": "2024-07-09",
      "description": "Fix released"
    }
  }
}
```

Each timeline event contains:
- **`date`**: ISO 8601 date string
- **`description`**: Human-readable description of what occurred

This structured approach (versus simple date strings) allows for richer metadata and supports future timeline events beyond disclosure and fix dates.

#### CNA (CVE Numbering Authority) Structure

The `cna` field captures information from the CVE Numbering Authority that assigned the CVE:

```json
{
  "cna": {
    "name": "microsoft",
    "severity": "Important",
    "impact": "Denial of Service",
    "acknowledgments": ["Researcher Name"],
    "faq": [
      {
        "question": "How could an attacker exploit this vulnerability?",
        "answer": "An attacker could..."
      }
    ]
  }
}
```

**Fields:**
- **`name`**: CNA identifier (e.g., "microsoft")
- **`severity`**: CNA's severity rating (may differ from CVSS severity)
- **`impact`**: Brief impact description (e.g., "Remote Code Execution", "Denial of Service")
- **`acknowledgments`**: Optional array of credited researchers/reporters
- **`faq`**: Optional array of Q&A pairs providing additional context

The `faq` field is particularly valuable for complex vulnerabilities where additional context helps users understand exploitation scenarios and mitigation steps.

**Why record CNA classifications separately?** CNA severity and impact assessments don't always align with industry databases like cve.org or NVD. Different organizations may evaluate the same vulnerability differently based on their specific context, threat models, or assessment methodologies. Recording both the CNA's view (in `cna.severity` and `cna.impact`) and the standardized CVSS assessment (in `cvss`) preserves both perspectives. This is valuable whether the assessments agree (providing corroboration) or disagree (highlighting differing interpretations that users may want to investigate).

#### Weakness (CWE) Field

The `weakness` field provides the Common Weakness Enumeration (CWE) identifier:

```json
{
  "weakness": "CWE-400"
}
```

This links the vulnerability to the standardized weakness taxonomy, enabling:
- Classification-based analysis across vulnerabilities
- Integration with tools that consume CWE identifiers
- Trend analysis of weakness types over time

### 7. Explicit Value Requirements

The schema avoids null values and implicit assumptions to ensure predictable queries and eliminate ambiguity. All fields that could reasonably have "unknown" or "not applicable" states must instead use explicit values that clearly indicate their meaning.

This design principle simplifies query patterns by eliminating null-handling complexity and prevents different interpretations of missing data.

#### 7.1. Platform Specification

The `platforms` field in CVE entries demonstrates this principle. Rather than allowing implicit "all platforms" assumptions through omitted fields, the schema requires explicit specification:

- **Explicit specification required**: All CVE entries MUST include a `platforms` field
- **Use "all" for cross-platform vulnerabilities**: When a vulnerability affects all supported platforms, use `["all"]` rather than omitting the field  
- **Specific platforms when limited**: Use specific platform names (e.g., `["windows"]`, `["linux"]`, `["macos"]`) when the vulnerability affects only those platforms
- **Multiple specific platforms**: Use arrays like `["linux", "windows"]` when affecting multiple but not all platforms

This approach ensures consistent queryability - tools can reliably filter platform-specific CVEs without needing to interpret missing fields as implicit "all platforms" assumptions.

#### 7.2. Release Field Specification

The `release` field in products and packages entries follows the same principle to simplify query patterns:

- **Always present**: All product entries MUST include a `release` field (optional on package entries)
- **Empty string for release-independent components**: Packages not tied to a specific .NET release family use `""` rather than `null`
- **Specific release families**: Use standard release identifiers like `"6.0"`, `"8.0"` for components tied to those releases

**Query simplification example:**

Without this requirement, queries need complex null-handling:

```bash
# Complex: handle both null and empty string cases
jq -r '.packages[] | select(.release == null or .release == "") | .name'
```

With explicit empty strings:

```bash
# Simple: direct string comparison
jq -r '.packages[] | select(.release == "") | .name'
```

#### 7.3. Architecture Specification

The `architectures` field in CVE entries follows the same explicit specification principle as platforms:

- **Explicit specification required**: All CVE entries MUST include an `architectures` field
- **Use "all" for cross-architecture vulnerabilities**: When a vulnerability affects all supported architectures, use `["all"]` rather than omitting the field
- **Specific architectures when limited**: Use specific architecture names (e.g., `["x64"]`, `["arm64"]`, `["x86"]`) when the vulnerability affects only those architectures
- **Multiple specific architectures**: Use arrays like `["x64", "arm64"]` when affecting multiple but not all architectures

This ensures consistent queryability for architecture-specific vulnerability analysis and deployment planning.

### 8. Challenges

The schema attempts to offer clear currency for queries, with well-defined primary/foreign key relationships. `cve[].id` is a good example. There are some properties in the schema that don't work as well and may cause problems..

- `products[].name` is a nice token (no spaces) but doesn't define its scope even with the benefit of `product_name` lookups. For monolithic products, this may not be an issue, but for more componentized offerings, it could be.

This property suffers from not knowing the complete set and some accompanying prose to describe any required detail or nuance.

## Query Design Proof Points

The following queries demonstrate how the schema design successfully achieves its primary goal: **exceptional jq ergonomics for security operations**. These examples showcase clean snake_case dot notation, pre-computed indices, and table-ready output.

**Note**: All examples assume the JSON data is saved as `cve.json`. For comprehensive query catalog, see [cve_queries.md](./cve_queries.md).

### Clean Syntax Demonstration

Snake_case field names enable elegant dot notation throughout:

```bash
# Simple field access - no bracket notation needed
$ jq -r '.disclosures[] | select(.cvss.severity == "HIGH") | .id' cve.json
CVE-2024-30105
CVE-2024-35264
CVE-2024-38081
CVE-2024-38095
```

### Index-Based Query Demonstration

Pre-computed indices eliminate expensive joins:

```bash
# Get CVEs affecting a specific product - O(1) lookup
$ jq -r '.product_cves["dotnet-aspnetcore"][]' cve.json
CVE-2024-35264

# Get releases affected by a CVE - O(1) lookup
$ jq -r '.cve_releases["CVE-2024-38095"][]' cve.json
6.0
8.0
```

### Defensive Query Patterns

The schema supports robust error handling:

```bash
# Safely handle missing data with // operator
$ jq -r '.release_cves["9.0"] // "No CVEs reported"' cve.json
No CVEs reported

# Safe containment checks
$ jq -r '(.cve_releases["CVE-2024-XXXXX"] // []) | contains(["8.0"])' cve.json
false
```

### Bidirectional Query Symmetry

The schema provides symmetric indices for efficient queries in both directions:

```bash
# CVE → Releases (typical security workflow)
$ jq -r '.cve_releases["CVE-2024-38095"] | contains(["8.0"])' cve.json
true

# Release → CVEs (deployment planning workflow)  
$ jq -r '.release_cves["8.0"] | contains(["CVE-2024-38095"])' cve.json
true
```

Both queries have O(1) index lookups plus O(n) array containment checks, where n is typically small (few releases per CVE, few CVEs per release).

### Multi-Level Joins Demonstration

Complex queries chain indices efficiently:

```bash
# Get commit URLs for a CVE - demonstrates normalized dictionary lookups
$ jq -r '. as $root | .cve_commits["CVE-2024-38095"][] | $root.commits[.].url' cve.json
https://github.com/dotnet/runtime/commit/979135d5ca4efaf6436ee13539cc3f1e039d570a.diff
https://github.com/dotnet/runtime/commit/4a8d5a007971d19f389ca17f7b8eb4f9bb199991.diff

# Chain multiple indices: Release → CVEs → Commits
# This would be expensive without pre-computed indices
$ jq -r '. as $root | .release_cves["8.0"][] as $cve | $root.cve_commits[$cve][] | $root.commits[.].url' cve.json | head -2
https://github.com/dotnet/runtime/commit/fa5b0d8f4a8b424732cc992158aa92842f8a2846.diff
https://github.com/dotnet/aspnetcore/commit/c5721fb7a65ddc13d1b445c2c08c27b72ab57cdc.diff
```

## Additional Query Capabilities

The schema supports comprehensive analysis across all data dimensions. The design proof points above demonstrate the core ergonomic principles, while the complete query catalog in [cve_queries.md](./cve_queries.md) provides detailed examples for:

### Operational Security Workflows
- **Component Analysis**: Enumerate affected products, packages, releases
- **Risk Assessment**: CVE impact analysis with severity and platform filtering
- **Patch Planning**: Fixed version tracking across releases and components
- **Emergency Response**: Critical vulnerability identification and rapid assessment

### Advanced Queries
- **Cross-component Impact**: Identifying CVEs that affect multiple products or packages
- **Temporal Analysis**: Vulnerability age tracking for compliance monitoring  
- **Commit Analysis**: Repository-level commit summaries and CVE-to-fix mapping
- **Defensive Operations**: Error-handling patterns for missing data and edge cases

### Table-Ready Output
Most queries generate markdown-formatted tables suitable for:
- Security reports and documentation
- Automated dashboards and tooling integration  
- Command-line operations and CI/CD pipelines

## Appendix: Schema Design and Query Tradeoff Analysis

This schema was designed with "queryability as a primary design concern" - optimizing for common security operations workflows over theoretical data modeling purity. This appendix analyzes the key design decisions and their impact on query complexity.

### Design Philosophy: Optimizing for Query Simplicity

The schema employs **strategic denormalization** - intentionally duplicating data to make queries simpler. For example, `System.Formats.Asn1` appears twice in the packages array - once for each CVE/release combination it's affected by.

This denormalization trades storage efficiency for query simplicity:

- **Normalized approach**: One object per component, nested ranges for different CVEs
- **Denormalized approach**: Separate objects for each CVE/component combination

Since CVE data is queried far more often than it's updated, this trade-off optimizes for the common case of reading and analyzing the data.

### Current Schema vs Alternative Approaches

We evaluated three main approaches:

### 1. Current Schema (Flat Denormalized)

```json
{
  "packages": [
    {"cve_id": "CVE-2024-38095", "name": "System.Formats.Asn1", "release": "6.0", "fixed": "6.0.1"},
    {"cve_id": "CVE-2024-38095", "name": "System.Formats.Asn1", "release": "8.0", "fixed": "8.0.1"}
  ]
}
```

### 2. Hybrid Schema (Package-Centric with Ranges)

```json
{
  "packages": [
    {
      "name": "System.Formats.Asn1",
      "ranges": [
        {"cve_id": "CVE-2024-38095", "release": "6.0", "fixed": "6.0.1"},
        {"cve_id": "CVE-2024-38095", "release": "8.0", "fixed": "8.0.1"}
      ]
    }
  ]
}
```

### 3. Ranges Schema (OSV-Style)

```json
{
  "affected": [
    {
      "package": {"name": "System.Formats.Asn1"},
      "ranges": [
        {"type": "ECOSYSTEM", "events": [{"introduced": "6.0.0", "fixed": "6.0.1"}]}
      ]
    }
  ]
}
```

### Query Complexity Analysis

The following table compares query complexity across schemas for common operations:

| Query Pattern | Current Schema | Hybrid Schema | Ranges Schema | Primary Use Case |
|---------------|----------------|---------------|---------------|------------------|
| **CVE → Components** | ✅ Simple filter | ❌ Nested select | ❌ Double nested | **High priority** |
| **Component → CVEs** | ✅ Direct field | ❌ Array traversal | ✅ Direct field | **High priority** |
| **Version vulnerability check** | ✅ Top-level fields | ❌ Nested access | ❌ Event parsing | **High priority** |
| **Release-specific patches** | ✅ Simple filter | ❌ Nested filter | ❌ Complex logic | **Medium priority** |
| **Components with multiple CVEs** | ❌ Group-by needed | ✅ Direct check | ✅ Direct field | **Low priority** |
| **Package deduplication** | ❌ Duplicates exist | ✅ No duplicates | ✅ No duplicates | **Low priority** |

**Result**: Current schema wins 4/6 query patterns, optimizing for the highest-priority security operations.

### Detailed Query Examples

#### "Which components are affected by CVE-2024-38095?"

**Current Schema (Winner):**

```bash
jq -r '[.products[], .packages[]] | 
  map(select(.cve_id == "CVE-2024-38095")) | 
  .[].name'
```

**Hybrid Schema:**

```bash
jq -r '[.products[], .packages[]] | 
  map(select(.ranges[]? | .cve_id == "CVE-2024-38095")) | 
  .[].name'
```

**Ranges Schema:**

```bash
jq -r '.affected[] | 
  select(.ranges[] | .events[] | has("cve_id") and .cve_id == "CVE-2024-38095") | 
  .package.name'
```

The current schema requires the simplest filter logic for this critical security operation.

#### "Which components have multiple vulnerabilities?"

**Current Schema:**

```bash
jq -r '[.products[], .packages[]] | 
  group_by(.name) | 
  map(select(length > 1)) | 
  .[] | "\(.[0].name): \([.[] | .cve_id] | unique | length) CVEs"'
```

**Hybrid Schema (Winner):**

```bash
jq -r '[.products[], .packages[]] | 
  map(select(.ranges | map(.cve_id) | unique | length > 1)) |
  .[] | "\(.name): \(.ranges | map(.cve_id) | unique | length) CVEs"'
```

The hybrid approach eliminates the need for grouping operations in this less common query.

### Join Indices: Additional Denormalization

The schema includes pre-computed join indices that further optimize queries:

```json
{
  "cve_releases": {
    "CVE-2024-38095": ["6.0", "8.0"]
  },
  "release_cves": {
    "8.0": ["CVE-2024-30105", "CVE-2024-35264", "CVE-2024-38095"]
  },
  "cve_commits": {
    "CVE-2024-38095": ["runtime@979135d", "runtime@4a8d5a0"]
  },
  "package_cves": {
    "System.Formats.Asn1": ["CVE-2024-38095"]
  },
  "severity_cves": {
    "HIGH": ["CVE-2024-30105", "CVE-2024-35264", "CVE-2024-38095"]
  }
}
```

These indices break normalization principles by introducing redundancy, but transform complex multi-stage joins into simple lookups:

**Without Indices:**

```bash
# Find releases affected by a CVE - requires scanning all components
jq -r '[.products[], .packages[]] | 
  map(select(.cve_id == "CVE-2024-38095")) | 
  map(.release) | unique'
```

**With Indices:**

```bash
# Direct lookup - no scanning required
jq -r '.cve_releases["CVE-2024-38095"][]'
```

### Comparison to Industry Standards

### OSV Schema

The [OSV Schema](https://ossf.github.io/osv-schema/) prioritizes package-centric operations:

- **Strength**: Natural for dependency scanning tools
- **Weakness**: Complex for CVE-centric security analysis  
- **Best for**: Software composition analysis workflows

### CVE.org JSON Format  

If the [CVE Assignment Information Format](https://cve.mitre.org/cve/list_rules_and_guidance/cve_assignment_information_format.html) were JSON:

- **Strength**: CVE-centric structure matches security workflows
- **Weakness**: No structured component/version relationships
- **Best for**: Vulnerability disclosure and tracking

### Our Schema

Bridges both approaches:

- **CVE metadata**: Separate for disclosure workflows  
- **Component impact**: Denormalized for analysis workflows
- **Join indices**: Pre-computed for common cross-cutting queries
- **Best for**: Security operations and patch management

### Storage vs Query Performance Trade-offs

| Aspect | Current Schema | Alternative Approaches |
|--------|----------------|----------------------|
| **File size** | ~15% larger due to duplication | Smaller, more normalized |
| **Query complexity** | Simple for 80% of use cases | Complex for common operations |
| **Parse performance** | Fast (flat structure) | Slower (nested traversal) |
| **Maintainability** | Indices require updates | Single source of truth |
| **LLM consumption** | Optimized (simple patterns) | Harder to process |

### Design Validation Through Testing

We validated this design by implementing actual `jq` queries for all documented use cases. The principle was: **"If the `jq` queries look bad, the schema is bad."**

Key findings:

- Simple queries = better human comprehension
- Simple queries = better LLM processing  
- Flat structures = faster parsing
- Pre-computed indices = predictable performance

### Conclusion

The current schema prioritizes **queryability over normalization** because:

1. **Access patterns** are heavily read-biased and CVE-centric
2. **Query simplicity** directly impacts adoption and tooling
3. **Storage cost** of duplication is negligible compared to query complexity cost
4. **Performance predictability** matters more than theoretical efficiency

This represents a **pragmatic over pure** approach - choosing usability over textbook normalization principles.

The small storage overhead buys significant simplification in the queries that security teams run daily, making this a worthwhile trade-off for a security-focused analytical schema.