epss-csv-backfill

Status: Live (local-only — no ECS task definition, no EventBridge schedule) Source: FIRST EPSS daily archive — a local clone of the epss_scores archive repository Type: csv (per-day epss_scores-YYYY-MM-DD.csv.gz) Source slug: epss (S3 archive prefix only — this processor writes no CVEMetadata rows, so no CVEMetadata.source value is minted) Schedule: None. On-demand: just go-epss-csv-backfill.

Overview

EPSS (Exploit Prediction Scoring System) publishes one CSV per day containing an exploitation probability and percentile for every published CVE. The scheduled enrich-first-epss-json task keeps today’s scores current; this backfill loads the history so EPSS can be read as a time series — “was this CVE’s score already climbing before the KEV listing?” rather than only “what is it now?”.

Each run walks --repo recursively for files matching epss_scores-(\d{4}-\d{2}-\d{2})\.csv\.gz, sorts them by name (which sorts them by date), and imports every file whose date is newer than MAX(EpssScore.dateString) already in the database. --force disables that filter and reprocesses every file found.

Two CSV generations are handled:

Generation	First line	Columns	`modelVersion` written
Early (pre-2022)	header row	`cve,epss`	synthesised as `v{YYYY-MM-DD}`
Current (2022+)	`#model_version:…,score_date:…` comment	`cve,epss,percentile`	parsed from the comment

Rows whose first field does not start with CVE-, or whose score does not parse as a float, are dropped silently — the daily files carry trailing blank lines and occasional malformed rows.

Records produced

Condition	Records
Every imported CSV row	`EpssScore` (`cve`, `dateString`, `score`, `percentile`, `modelVersion`, `fetchedAt`/`createdAt` in Unix ms) — one row per `(cve, dateString)`, upserted in 5 000-row `COPY` + merge batches
Successful file import	S3 archive object at `epss/files/{sha256}/{filename}`
Parse failure	S3 quarantine object at `failed-feeds/epss-csv-backfill/{YYYY-MM-DD}/parse-error/{filename}`
Store failure	S3 quarantine object at `failed-feeds/epss-csv-backfill/{YYYY-MM-DD}/store-error/{filename}`

No CVEMetadata, CVEAlias, or advisory-side rows are written. EpssScore is the sole target table.

Batch sizing

batchRows = 5000 (cmd/epss-csv-backfill/main.go:38). The daily files arrive already sorted by CVE, so sequential chunks land in CVE order regardless of batch size — the limiting factor is index maintenance IO on the 380M-row EpssScore table, not sort locality. 5 000 keeps each merge inside the 120 s write statement_timeout configured in internal/db/pool.go; a whole-file merge (~340 k rows) blows past it.

Resume

Two independent resume mechanisms:

Database-driven (authoritative) — db.GetLatestEpssDate returns MAX(dateString), an O(log n) index probe. Files at or before that date are skipped unless --force.
Statefile (local runs only) — .repo/epss.state records the last file completed so an interrupted run picks up where it stopped. Skipped entirely when processor.IsRunningInECS() is true, and deleted on a clean zero-error run.

Because mechanism 1 compares against the maximum date, a file added to the archive for a date older than the newest imported day is never picked up by an incremental run. Filling a historical gap requires --force.

Failure modes

Symptom	Cause
`no EPSS files found, exiting`	`--repo` does not point at the archive clone, or the clone is empty. The justfile default is `../../epss_scores` relative to the repo root and is resolved to an absolute path before the binary is invoked.
`insert failed … statement timeout`	A merge batch exceeded the 120 s write statement timeout — usually concurrent write pressure on `EpssScore`. The file is quarantined under `store-error` and the run continues; re-run to retry.
Run exits 1 with `completed with N errors`	At least one file failed to parse or store. The statefile is intentionally not deleted so a re-run resumes rather than restarting.

Flags

Flag	Default	Description
`--repo`	`/data/epss_scores` (justfile passes `../../epss_scores`)	Archive directory walked recursively for `epss_scores-*.csv.gz`
`--state-dir`	`.repo`	Directory holding the local resume statefile
`--force`	`false`	Ignore both the database watermark and the statefile; reprocess every file found

No deadline is applied under any condition — a backfill runs to completion. The binary never reads EXPECTED_DURATION_MINUTES, and the justfile recipe unsets it before invoking go run.

Local invocation

# Incremental: pull the archive, import only days newer than the DB watermark
just go-epss-csv-backfill

# Against production
just go-epss-csv-backfill TARGET=prod

# Skip the git pull (offline / already synced)
just go-epss-csv-backfill NO_PULL=true

# Re-import every file (fills historical gaps)
just go-epss-csv-backfill FORCE=true

S3 Persistence

Archive path: epss/files/{sha256}/{filename} ✓
Quarantine path: failed-feeds/epss-csv-backfill/{YYYY-MM-DD}/{reason}/{filename} ✓
Failure reasons emitted: parse-error, store-error

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

flowchart LR SRC[Source feed] --> PROC[epss-csv-backfill] PROC -->|success| ARCHIVE[("S3: epss/files/{sha256}/{filename}")] PROC -->|failure| Q[("S3: failed-feeds/epss-csv-backfill/{date}/{reason}/{filename}")] PROC --> DB[(PostgreSQL)]

See the S3 Persistence Contract for the full reason taxonomy.