GPZ 0day ITW Google Sheets Processor — Design Document

1. Overview

Purpose: Fetch the Google Project Zero 0day in-the-wild (ITW) tracking spreadsheet and store exploit intelligence + CVE enrichment records.

Data source: Google Sheets CSV export — https://docs.google.com/spreadsheets/d/1lkNJ0uQwbeC1ZTRrxdtuPLCIl7mlUreoKfSIgajnSyY/export?format=csv&gid=1190662839

Schedule: Daily at 05:00 UTC (cron 0 5 * * *)

Timeout: 15 minutes

Resources: 256 CPU units, 512 MB memory

What it reads:

  • Google Sheets CSV export (~555 entries, full re-fetch each run)
  • Read replica: BulkDataDumpTracker row for google_project_zero_0day_itw (SHA256 freshness)
  • Read replica: CVEMetadata for the clone-source priority lookup
  • Read replica: Known CVE IDs for ExploitCVE junction creation

What it writes (all CVE-side rows use source slug gpz; the google_project_zero_0day_itw string is the BulkDataDumpTracker key only):

  • CVEMetadata (source=gpz — cloned from the best available source, else created minimal)
  • Exploit (source=gpz, category=0day-itw)
  • ExploitCVE (junctions to priority-ordered sources + one under gpz)
  • ExploitAffectedProduct (vendor + product)
  • CVEMetadataReferences (advisory, analysis, root cause URLs; referenceSource="gpz")
  • CVEProblemType (Type → CWE mapping)
  • CVEDescription (combined description + 0day context)
  • CVEImpact + CVEImpactDescription (“Exploited in the wild”)
  • BulkDataDumpTracker (source=google_project_zero_0day_itw)

What it does not write: no CVEAlias. db.EnsureCVEMetadataForSource is a direct CVEMetadata writer that never calls db.InsertAliases, so the gpz row for a CVE is not linked into the alias graph and the same-cveId cross-source backfill never runs for it (contrary to the AGENTS.md alias contract).

Environment variables:

  • DATABASE_URL — required
  • DATABASE_URL_READ — optional

2. Business Logic

Freshness Check (SHA256-Based)

  1. Fetch CSV from Google Sheets
  2. Compute SHA256 hash of response body
  3. Compare against BulkDataDumpTracker.sha256 for google_project_zero_0day_itw
  4. If match → exit with “data unchanged”
  5. First run (no tracker row) always proceeds

GPZ CVEMetadata Cloning

For each CVE in the spreadsheet, ensure a CVEMetadata record exists with source=gpz (db.EnsureCVEMetadataForSource, internal/db/cvemetadata.go:161-218):

  1. Check if (cveId, "gpz") already exists → return skipped (no field refresh)
  2. If not, find best available source in priority order: cve.org → nist-nvd → github → anchore_adp → any other
  3. Clone key fields (dataVersion, state, datePublished, title, vectorString, affected vendor/product)
  4. If no source exists at all → create minimal record (state=“PUBLISHED”, dataVersion=“5.1”) using the fallback date, title and vendor/product passed by the processor

Known bug — step 4’s fallbackDatePublished is passed in milliseconds (main.go:274-284 prefers DateDiscovered, then DatePatched, both Unix ms, and multiplies the CVE-id-derived date by 1000), but the parameter is written straight into CVEMetadata.datePublished, which is int4 seconds. A millisecond value overflows int4, so the insert fails for any CVE that no other source knows. In practice GPZ CVEs are almost always already known to cve.org, so the clone path at step 3 hides the defect.

Data Type Classification

New type: gsheet — data is fetched as a CSV export from a public Google Sheets spreadsheet at runtime.

Date Handling

  • Date Patched (column G): Primary temporal field → Exploit.datePublished (YYYY-MM-DD → Unix ms)
  • Date Discovered (column F): Often “???” or blank (~66% unknown) → included in CVEDescription when known
  • Sentinel value “???” treated as nil/unknown

Type → CWE Mapping

Case-insensitive static map with Description-based refinement for “Memory Corruption”:

  • Memory Corruption → CWE-119 (refined to CWE-416/CWE-122/CWE-787 etc. from Description)
  • Logic/Design Flaw → CWE-840
  • Information Leak/Disclosure → CWE-200
  • XSS/UXSS → CWE-79
  • Integer overflow → CWE-190
  • Feature Bypass → CWE-863
  • Unmapped types stored as description-only (no CWE ID)

Batch Processing

100 entries per transaction; ~406 rows currently parse cleanly, so ~5 batches. Soft deadline checked between batches — note the fallback is a hardcoded 14 minutes applied even when EXPECTED_DURATION_MINUTES is unset (main.go:62-66), so a local backfill is truncated at 14 minutes too.

Idempotency

  • Exploit: ON CONFLICT (exploitId, source) DO UPDATE
  • ExploitCVE: ON CONFLICT DO NOTHING
  • CVEMetadataReferences: ON CONFLICT DO NOTHING
  • CVEProblemType: ON CONFLICT DO NOTHING
  • CVEDescription: DELETE + re-INSERT (replace stale descriptions)
  • CVEImpact: ON CONFLICT DO NOTHING
  • ExploitAffectedProduct: DELETE + re-INSERT per exploit

3. Architecture Diagram

graph TD subgraph "cmd/gpz-0day-itw-gsheet-processor/" MAIN[main.go] end subgraph "internal/gpz/" PARSER[parser.go — ParseCSV, Entry] CWEMAP[cwemap.go — TypeToCWE] MAPPER[mapper.go — MapToExploitRow, MapToReferences, etc.] end subgraph "internal/db/" POOL[pool.go — Pool] TRACK[tracker.go — GetTracker, UpsertTracker] CVEMETA[cvemetadata.go — EnsureCVEMetadataForSource, UpsertCVEMetadata] EXPLOIT[exploit.go — UpsertExploit, InsertExploitCVE, etc.] REFS[cvereference.go — InsertReferences] PROB[cveproblemtype.go — InsertProblemTypes] DESC[cvedescription.go — InsertDescriptions] IMPACT[cveimpact.go — InsertImpacts] end MAIN --> PARSER MAIN --> MAPPER MAPPER --> CWEMAP MAIN --> POOL MAIN --> TRACK MAIN --> CVEMETA MAIN --> EXPLOIT MAIN --> REFS MAIN --> PROB MAIN --> DESC MAIN --> IMPACT

4. Deployment Diagram

flowchart TD GHA[GitHub Actions
go-ecr-deploy.yml] -->|push ARM64 image| ECR[ECR: go-processors
tag: go-gpz-0day-itw-gsheet-processor-sha-xxx] ECR --> TASKDEF[ECS Task Definition
go-gpz-0day-itw-gsheet-processor] TASKDEF --> EB[EventBridge Schedule
go-gpz-0day-itw-gsheet-processor
cron 0 5 * * ? *] EB -->|trigger| FARGATE[ECS Fargate Task
vdb-scheduler cluster
ARM64 ap-southeast-2] FARGATE --> CW[CloudWatch Logs
/ecs/vdb-scheduler/gpz-0day-itw-gsheet-processor] FARGATE -->|HTTPS GET| GSHEET[Google Sheets
CSV Export] FARGATE --> WRITE[RDS Write Proxy] FARGATE --> READ[RDS Read Replica]

5. Processing Flow

flowchart TD START([Start]) --> ENV{DATABASE_URL set?} ENV -->|no| FAIL([Exit 1]) ENV -->|yes| CONNECT[Connect to DB pool] CONNECT --> FETCH[HTTP GET Google Sheets CSV] FETCH -->|error| FAIL FETCH -->|ok| SHA[Compute SHA256 hash] SHA --> FRESH{SHA256 matches
tracker?} FRESH -->|yes, not forced| EXIT0([Exit 0 — unchanged]) FRESH -->|no or forced| PARSE[gpz.ParseCSV → ~555 entries] PARSE -->|error| FAIL PARSE --> LOAD[Load known CVE IDs] LOAD --> BATCH[For each batch of 100 entries] BATCH --> ENTRY[For each entry in transaction] ENTRY --> VULNETIX[EnsureCVEMetadataForSource
clone or create source=gpz] VULNETIX --> EXPLOIT[UpsertExploit
source=gpz] EXPLOIT --> AFFECTED[Delete + Insert
ExploitAffectedProduct] AFFECTED --> JUNCTIONS[InsertExploitCVE
priority sources + gpz] JUNCTIONS --> REFINS[InsertReferences
advisory, analysis, root cause] REFINS --> PROBLEM[InsertProblemTypes
Type → CWE mapping] PROBLEM --> DESCINS[InsertDescriptions
combined description + context] DESCINS --> IMPACTINS[InsertImpacts
Exploited in the wild] IMPACTINS --> ENTRY ENTRY -->|done| DEADLINE{Soft deadline
reached?} DEADLINE -->|yes| TRACKER[UpsertTracker] DEADLINE -->|no| BATCH BATCH -->|done| TRACKER TRACKER --> ERRCHECK{errors > 0?} ERRCHECK -->|yes| FAIL2([Exit 1]) ERRCHECK -->|no| DONE([Exit 0])

6. Data Mapping

erDiagram BulkDataDumpTracker { string source PK "google_project_zero_0day_itw" bigint lastProcessedAt "Unix ms" int frequency "86400 seconds = 24 hours" string sha256 "SHA256 of CSV body" int totalCVEs "entries processed" } CVEMetadata { string cveId PK "CVE-YYYY-NNNN" string source PK "gpz" string state "PUBLISHED" string dataVersion "5.1 or cloned" int datePublished "Unix SECONDS (cloned); ms on the minimal path — bug" string title "cloned or from sheet" } Exploit { string exploitId "CVE ID" string source "gpz" string title "Description (Vendor Product)" string author "Reported By" bigint datePublished "Date Patched as Unix ms" string category "0day-itw" string originalUrl "Advisory URL" jsonb cveIds "array with one CVE" } ExploitCVE { string exploitUuid FK string cveId "CVE-YYYY-NNNN" string source "gpz or priority source" } ExploitAffectedProduct { string exploitUuid FK string vendor "Google, Microsoft, Apple..." string product "Chrome, Windows, iOS..." } CVEMetadataReferences { string cveId FK string source "gpz" string url "Advisory / Analysis / Root Cause URL" string type "ADVISORY or ARTICLE" string referenceSource "gpz" } CVEProblemType { string cveId FK string source "gpz" string cweId "CWE-119, CWE-416, etc." string description "CWE description" string containerType "cna" } CVEDescription { string cveId FK string source "gpz" string value "Description + 0day context" string lang "en" } CVEImpact { string cveId FK string source "gpz" string containerType "cna" } CVEImpactDescription { string impactId FK string value "Exploited in the wild as 0day" string lang "en" } Exploit ||--o{ ExploitCVE : has Exploit ||--o{ ExploitAffectedProduct : has CVEMetadata ||--o{ CVEMetadataReferences : has CVEMetadata ||--o{ CVEProblemType : has CVEMetadata ||--o{ CVEDescription : has CVEMetadata ||--o{ CVEImpact : has CVEImpact ||--o{ CVEImpactDescription : has

7. Google Sheets Columns

ColumnHeaderMaps to
ACVEExploit.exploitId, ExploitCVE.cveId, CVEMetadata lookup
BVendorExploitAffectedProduct.vendor, CVEMetadata.affectedVendor
CProductExploitAffectedProduct.product, CVEMetadata.affectedProduct
DTypeCVEProblemType (CWE mapping), description-based refinement
EDescriptionExploit.title component, CVEDescription.value
FDate DiscoveredCVEDescription (when known, ~34% of rows)
GDate PatchedExploit.datePublished, CVEMetadata.datePublished
HAdvisoryCVEMetadataReferences (type=“ADVISORY”)
IAnalysis URLCVEMetadataReferences (type=“ARTICLE”)
JRoot Cause AnalysisCVEMetadataReferences (type=“ARTICLE”, title=“Root Cause Analysis”)
KReported ByExploit.author, CVEImpactDescription attribution

S3 filenames

The archive unit is the whole daily snapshot — gpz/files/{sha256}/gpz-0day-itw.csv, uploaded once per changed spreadsheet after the batch loop (main.go:186), same rationale as epss-csv-backfill. Quarantine has two granularities: the full CSV body under fetch-error / parse-error (main.go:97, 116), and a synthesised single-row CSV named {CVE}.csv under store-error / map-error for each failed row, uploaded only after the transaction resolves (main.go:374-386).

S3 Persistence

  • Archive path: gpz/files/{sha256}/{filename} ✓
  • Quarantine path: failed-feeds/gpz-0day-itw-gsheet-processor/{YYYY-MM-DD}/{reason}/{filename} ✓
  • Failure reasons emitted: fetch-error, parse-error, store-error, map-error

Uses s3client.Uploader from internal/s3client/uploader.go. Skipped when S3_BUCKET_NAME is unset (local dev).

flowchart LR SRC[Source feed] --> PROC[gpz-0day-itw-gsheet-processor] PROC -->|success| ARCHIVE[("S3: gpz/files/{sha256}/{filename}")] PROC -->|failure| Q[("S3: failed-feeds/gpz-0day-itw-gsheet-processor/{date}/{reason}/{filename}")] PROC --> DB[(PostgreSQL)]

See the S3 Persistence Contract for the full reason taxonomy.