NVD Deep-Dive Processor — Design Document

1. Overview

Purpose: Quantify what NVD’s enrichment narrowing actually means in counted records. The website article asserts that “the source of truth no longer exists for 80% of the catalogue” and that “100% of newly-disclosed CVEs that don’t pass the US KEV / federal-software / EO 14028 test will arrive as text-only advisories”; this processor turns those assertions into numbers from the actual record set.

Schedule: Daily at 11:00 UTC (cron 0 11 * * ? *) — one hour after summary-processor so the bulk processors and the summary aggregations have settled.

Timeout: 45 minutes (EXPECTED_DURATION_MINUTES=45, soft deadline at 40 minutes).

Resources: 256 CPU units, 1024 MB memory. The rawDataJSON jsonb cast for the no-remediation predicate is the dominant memory cost; everything else is index-driven.

What it reads:

• Read replica: CVEMetadata, filtered to state = 'PUBLISHED' AND cveId LIKE 'CVE-%' (excludes GHSA, PYSEC, RUSTSEC, etc. — those don’t claim NVD enrichment and would skew the story).
• Read replica: CVEMetric (CVSS scores).
• Read replica: CVEAffected, CVEAffectedVersion (affected-product structure).
• Read replica: CVEADP joined to AuthorizedDataPublisher (CISA ADP container presence).
• Read replica: BulkDataDumpTracker (freshness gate).

What it writes:

• Write endpoint: one SummaryLog row per run, keyed (label='nvd_deep_dive', timestamp) — ON CONFLICT (label, timestamp) DO UPDATE, so re-runs overwrite cleanly.
• Write endpoint: BulkDataDumpTracker upsert for the nvd_deep_dive tracker source — controls the freshness gate on subsequent runs.

Environment variables:

• DATABASE_URL — required (write pool).
• DATABASE_URL_READ — optional read replica.
• SNS_TOPIC_ARN — optional; enables overtime / error SNS notifications via internal/notify.
• EXPECTED_DURATION_MINUTES — optional (default 45); soft deadline at value − 5.
• READ_STATEMENT_TIMEOUT — set to 35min for this task. The master aggregation is an intentional full-dataset rollup that runs for minutes; this lifts the read pool’s protective 60 s statement_timeout (internal/db/pool.go) so the query is not cancelled with SQLSTATE 57014, while keeping the 60 s cap everywhere else.

Trigger gates:

• ECS scheduled task (EventBridge cron 0 11 * * ? *).
• Manual via just go-nvd-deepdive-processor (uses .env) — bypasses the soft deadline.
• Pass -force to bypass the daily freshness check.

2. Business Logic

For each published CVE record, the processor evaluates ten boolean predicates against the existing relational schema. Each predicate is a CASE WHEN <…> THEN 1 ELSE 0 END expression sharing the same missingPredicateSQL fragment, so the predicate semantics are identical across the three aggregation passes.

Aggregation passes

Each predicate fires three times, against three different GROUP BY shapes:

1. All-time — one bucket totalling every published CVE.
2. Yearly — one bucket per TO_CHAR(TO_TIMESTAMP(datePublished/1000.0), 'YYYY'), including a ""-period bucket for records without datePublished (older bulk imports). Full CVE history, ascending.
3. Monthly — one bucket per TO_CHAR(TO_TIMESTAMP(datePublished/1000.0), 'YYYY-MM'). Capped at the last 36 months so the chart axis stays readable and covers the NVD-darkness era (Feb 2024 → present) with a meaningful pre-period baseline.

Output payload

The three aggregations are bundled into one JSON document stored verbatim in SummaryLog.metadata (JSONB):

{
  "generatedAt": 1779279600000,
  "allTime":  { "totalRecords": 308920, "missingCvss": …, … },
  "yearly":   [ { "period": "1999", "totalRecords": 321, … }, …,
                { "period": "2026", "totalRecords": 25112, … } ],
  "monthly":  [ { "period": "2023-06", "totalRecords": 2300, … }, …,
                { "period": "2026-05", "totalRecords": 6153, … } ]
}

SummaryLog.value carries allTime.totalRecords as a headline number; SummaryLog.expr is "json".

3. Data flow

                 ┌──────────────────────────────────────────────┐
                 │ EventBridge cron: 0 11 * * ? *               │
                 └────────────────────┬─────────────────────────┘
                                      ▼
                ┌──────────────────────────────────────┐
                │ ECS task: nvd-deepdive-processor     │
                │   1. read replica connect            │
                │   2. BulkDataDumpTracker freshness   │
                │   3. computeAllTime  (≤ 20 min)      │
                │   4. computeYearly   (≤ 20 min)      │
                │   5. computeMonthly  (≤ 20 min)      │
                │   6. marshal Payload → JSON          │
                │   7. INSERT SummaryLog (ON CONFLICT) │
                │   8. UpsertTracker                   │
                └────────────────────┬─────────────────┘
                                     ▼
                ┌──────────────────────────────────────┐
                │ vdb-api GET /v2/nvd-deep-dive        │
                │   reads latest SummaryLog row        │
                │   24h in-memory cache, CDN cacheable │
                └────────────────────┬─────────────────┘
                                     ▼
                ┌──────────────────────────────────────┐
                │ website /articles/just-patch         │
                │   NVD deep-dive section: stat cards  │
                │   + yearly stacked bar + monthly bar │
                └──────────────────────────────────────┘

4. Configuration surface

The processor name appears character-identical in all of the following per the blueprint’s “8-place rule”:

BulkDataDumpTracker.source for freshness tracking is nvd_deep_dive (underscored — Postgres-friendly key).

5. Run + verify

Local dry-run via the justfile (uses .env):

just go-nvd-deepdive-processor
# or against production read replica:
just go-nvd-deepdive-processor prod

Inspect the result:

SELECT label, timestamp, value, jsonb_pretty(metadata)
FROM "SummaryLog"
WHERE label = 'nvd_deep_dive'
ORDER BY timestamp DESC
LIMIT 1;

Expected shape: one row, value ≈ the total published-CVE count, metadata ≈ the JSON document above with non-zero counts in each missing* key.

End-to-end against the API:

TOKEN=$(curl -s https://www.vulnetix.com/api/vdb/token | jq -r .token)
curl -s -H "Authorization: Bearer $TOKEN" \
  https://www.vulnetix.com/api/vdb/v2/nvd-deep-dive | jq '.allTime'

Until the first scheduled run completes, the endpoint returns HTTP 503 with {"success": false, "error": "NVD deep-dive not yet available, please retry shortly"} — exactly what the website’s article shows as a “Live data unavailable” skeleton.

6. Operational notes

• Backfill is not needed. There is no historical state; every run computes counts against the current full record set.
• Re-runs within the same timestamp overwrite (ON CONFLICT (label, timestamp) DO UPDATE). Different daily timestamps coexist; the API + website always read the most recent.
• The processor is read-heavy / write-tiny: ten predicates × three aggregations × one CTE = ~10s SQL plans; the only write is a single SummaryLog row.
• No S3, no SNS payloads, no notification on success beyond the standard notifier.Completed() call. Failures route through internal/notify to Google Chat / Slack via notify-dispatcher.
• The predicates intentionally exclude rejected CVEs and non-CVE identifiers — see § 2 for the rationale.

S3 Persistence

Not used. This processor does not currently archive payloads or quarantine failures to S3. Per the S3 Persistence Contract this is non-compliant — see the compliance matrix for the implementation roadmap.

Expected paths when implemented:

• Archive: nvd-deepdive/files/{sha256}/{filename}
• Quarantine: failed-feeds/nvd-deepdive-processor/{YYYY-MM-DD}/{reason}/{filename}
• Likely reasons: (none documented)