snowflake-fetch-processor — Implementation Plan

Executive Summary

Build a fetch type processor to ingest Snowflake Security Bulletins from https://www.snowflake.com/en/why-snowflake/snowflake-security-hub/security-bulletins/. The source publishes HTML-only pages (no JSON API, no RSS feed) with native CVE-prefixed identifiers. Volume is very small (~14 bulletins currently) and cadence is infrequent.

Locked name: snowflake-fetch-processor (appears in 8 places — see §11)

Phase 0 — Source Reconnaissance (COMPLETED)

0.1 Reachability

• curl -sSI returns 200 OK, no Cloudflare challenge, no bot detection
• Standard browser User-Agent sufficient
• No auth or rate-limiting observed

0.2 Sample Payload

• Listing page: server-rendered HTML with <a> links to individual bulletins
• Individual page: HTML with structured sections (h4 + ul/li pattern)
• No JSON-LD, no __INITIAL_STATE__ with useful bulletin content (AEM page structure JSON only)

0.3 Identifier Inspection

• Natural key: CVE-YYYY-NNNNN (native, no source-prefixed IDs)
• All bulletins have CVE IDs in URL path: /security-bulletins/CVE-YYYY-NNNNN/

0.4 CVE Alias Presence

• Every bulletin is CVE-native; no aliases needed beyond same-cveId cross-source backfill via db.InsertAliases

0.5 Pagination / Freshness

• Listing page has pagination (2 pages, 14 total results currently)
• No Last-Modified or ETag freshness signal
• No incremental “since” parameter
• Resume strategy: db.LoadProcessedHashes on per-item hash

0.6 Source Contract Summary

Phase 1 — Processor Archetype

Archetype: fetch (HTML scraping) Representative reference: cmd/pwno-fetch-processor/main.go

Snowflake is simpler than pwno — no RSC payload extraction needed. Standard regex-based HTML parsing on both listing and detail pages is sufficient. No Chromium required (not Cloudflare-protected).

Phase 2 — Design Document

See scripts/go-processors/snowflake-fetch-processor.design.md (to be created). Key sections:

2.1 Overview

• Purpose: Ingest Snowflake Security Bulletins into CVEMetadata
• Source URL: https://www.snowflake.com/en/why-snowflake/snowflake-security-hub/security-bulletins/
• Owner: Snowflake Inc.
• Licence: Public security disclosures
• Schedule: cron(0 6 * * ? *) — daily 06:00 UTC
• ECS resources: cpu=256, memory=512, expected_duration_minutes=15 (very small volume, single-threaded fetches)
• Reads: External HTML pages, BulkDataDumpTracker, LoadProcessedHashes
• Writes: CVEMetadata, CVEAlias, CVEDescription, CVEReference, CVEAffected, CVEAffectedVersion, S3 archive

2.2 Source Contract

• HTML-only, no API, no RSS
• Standard HTTP/1.1 or HTTP/2 client with browser headers
• Pagination via page number links (?page=2 or similar — verify in Phase 3)
• Per-item hash for resume/deduplication

2.3 Architecture

Source (HTML listing) → HTTP fetch → Regex parse CVE links
  → For each CVE URL: HTTP fetch detail page → Regex parse fields
    → Map to osv.CVESourceData
      → processor.StoreCVESourceData (central pipeline)
        → CVEMetadata + CVEAlias + Descriptions + References + Affected
      → S3 archive (raw HTML per bulletin)

2.4 Source → DB Field Mapping

2.5 Identifier Policy

• CVEMetadata.cveId = CVE-YYYY-NNNNN (native)
• CVEAlias = same-cveId cross-source backfill only (no source-prefixed aliases)
• Call db.InsertAliases(ctx, tx, cveID, source, nil, logger) for every record

2.6 CRIT / VEX

• NOT APPLICABLE. Snowflake bulletins cover client-side connectors/drivers (C/C++, Node.js, Go, .NET, JDBC, Python, PHP PDO), not cloud resource (Provider, Service, ResourceType) triples. No CritRecord or VEX staging.

2.7 S3 Archive

• Bucket: standard VDB archive bucket
• Key prefix: snowflake/{cveId}/{timestamp}.html
• Payload: raw HTML of individual bulletin page
• Quarantine reasons: parse failure, missing CVE ID, missing title

2.8 Error Handling & Slack

• Per-record failure semantics (continue on individual parse errors)
• Retry: 3 attempts per HTTP request, exponential backoff
• Transient: connection errors, timeouts, 5xx
• Fatal: 4xx (except 429), unparseable page structure
• Slack events: Started, Completed, Errored, NoWork
• Stats dict: {"fetched": N, "stored": N, "skipped": N, "failed": N}

2.9 Performance

• Concurrency: 1 worker (very small volume, polite to source)
• Per-request timeout: 30s
• Rate-limit pacing: 1s delay between detail page fetches
• Soft deadline: honour EXPECTED_DURATION_MINUTES with 5-min grace
• Resume: LoadProcessedHashes at startup

2.10 Backfill

• No separate backfill binary warranted. Full archive is ~14 pages, accessible via paginated listing. The scheduled processor with --force covers backfill.

Phase 3 — Code Structure

scripts/go-processors/
├── cmd/snowflake-fetch-processor/
│   └── main.go               # CLI flags, env, pool init, orchestration
├── internal/snowflake/
│   ├── client.go             # HTTP fetch: listing + detail pages
│   ├── parse.go              # Regex-based HTML parsing
│   ├── map.go                # Map parsed structs → osv.CVESourceData
│   └── parse_test.go         # Golden-file tests
└── snowflake-fetch-processor.design.md

3.1 internal/snowflake/client.go

package snowflake

const (
    listingURL = "https://www.snowflake.com/en/why-snowflake/snowflake-security-hub/security-bulletins/"
    baseURL    = "https://www.snowflake.com"
)

// FetchListing retrieves all CVE URLs from paginated listing pages.
func FetchListing(ctx context.Context, httpClient *http.Client) ([]string, error)

// FetchDetail retrieves and parses a single bulletin detail page.
func FetchDetail(ctx context.Context, httpClient *http.Client, url string) (*Bulletin, error)

3.2 internal/snowflake/parse.go

package snowflake

import "regexp"

// Regex patterns for HTML parsing
var (
    // Extract CVE URLs from listing page
    cveLinkRe = regexp.MustCompile(`href="(/en/why-snowflake/snowflake-security-hub/security-bulletins/CVE-\d{4}-\d+)/"`)

    // Extract fields from detail page
    titleRe         = regexp.MustCompile(`<title>([^<]+)</title>`)
    pubDateRe       = regexp.MustCompile(`Publication date:&nbsp;(\d{4}-\d{2}-\d{2})`)
    descriptionRe   = regexp.MustCompile(`<li>(CVE-\d{4}-\d+)&nbsp;-&nbsp;([^<]+)</li>`)
    cweRe           = regexp.MustCompile(`<li>(CWE-\d+)[^<]*</li>`)
    cpeRe           = regexp.MustCompile(`<li><b>(cpe:2\.3:[^<]+)</b></li>`)
    resolutionRe    = regexp.MustCompile(`<h4[^>]*>Resolution</h4>\s*<ul>\s*<li>([^<]+)</li>`)
)

type Bulletin struct {
    CVEID       string
    Title       string
    PubDate     string // YYYY-MM-DD
    Description string
    CWEID       string
    CPE         string
    Resolution  string
    URL         string
    RawHTML     []byte
}

func ParseListing(html []byte) []string
func ParseDetail(html []byte) (*Bulletin, error)

3.3 internal/snowflake/map.go

package snowflake

import (
    "crypto/sha256"
    "encoding/hex"
    "time"

    "github.com/vulnetix/vdb-manager/go-processors/internal/osv"
)

func (b *Bulletin) ToSourceData() *osv.CVESourceData {
    // Map Bulletin fields to osv.CVESourceData
    // Parse dates, build references, affected, problem types
}

func sha256Hex(b []byte) string {
    h := sha256.Sum256(b)
    return hex.EncodeToString(h[:])
}

3.4 cmd/snowflake-fetch-processor/main.go

Follow the blueprint skeleton (§4) with these specifics:

const (
    processorName = "snowflake-fetch-processor"
    sourceSlug    = "snowflake"
    frequencySecs = 86_400 // daily
)

func main() {
    // Standard flags: force, limit
    // Standard setup: logger, notify, pool, tracker freshness
    // 1. Fetch listing → []cveURLs
    // 2. LoadProcessedHashes
    // 3. For each URL: fetch detail → parse → map → processor.RunOne
    // 4. Tracker bump on clean exit
    // 5. Slack notify
}

Phase 4 — Central Pipeline Contract

Every advisory MUST flow through processor.StoreCVESourceData.

The osv.CVESourceData fields to populate:

&osv.CVESourceData{
    CveID:             bulletin.CVEID,
    Source:            sourceSlug,
    DataVersion:       "1.0",
    State:             "PUBLISHED",
    DatePublished:     &pubDateUnix,
    Title:             bulletin.Title,
    SourceAdvisoryRef: bulletin.URL,
    RawDataJSON:       rawJSON,
    SourceFileHash:    sha256Hex(bulletin.RawHTML),
    Aliases:           nil, // CVE-native, no aliases

    Descriptions: []osv.DescriptionData{{
        ContainerType: "cna",
        Lang:          "en",
        Value:         bulletin.Description,
    }},

    References: []osv.ReferenceData{{
        URL:             bulletin.URL,
        Type:            "advisory",
        ReferenceSource: "snowflake",
    }},

    ProblemTypes: []osv.ProblemTypeData{{
        ContainerType: "cna",
        CweID:         bulletin.CWEID,
    }},

    Affected: []osv.AffectedData{{
        Vendor:  "snowflake",
        Product: productFromCPE(bulletin.CPE),
        Package: osv.Package{
            Ecosystem: ecosystemFromTitle(bulletin.Title),
            Name:      packageFromTitle(bulletin.Title),
        },
    }},
}

Phase 5 — Identifier Policy

No bundle suppression needed (each bulletin = 1 CVE). No minted IDs needed.

Phase 6 — CRIT and VEX

NOT APPLICABLE — Snowflake bulletins are client library/driver advisories, not cloud resource (Provider, Service, ResourceType) triples. No critutil or critpublisher involvement.

Phase 7 — S3 / Archive Layout

• Bucket: standard VDB archive bucket (from env)
• Key prefix: snowflake/{cveId}/{timestamp}.html
• Payload: raw HTML of individual bulletin detail page
• Quarantine reasons:
  1. parse_error — detail page structure unrecognised
  2. missing_cve — no CVE ID extracted
  3. missing_title — no title extracted

Phase 8 — Niche Tables

None. This processor writes standard CVE tables only:

• CVEMetadata, CVEAlias, CVEDescription, CVEReference, CVEProblemType, CVEAffected, CVEAffectedVersion

Phase 9 — Schemas

No JSON Schema needed. The source is HTML, not JSON/YAML. Tests use golden-file fixtures (recorded HTML snippets) instead.

Phase 10 — Database Migrations

No schema changes required. Existing tables sufficient.

Phase 11 — Infrastructure Wiring

Every file edit listed here is required.

11.1 Containerfile.go-processors

Append final-target stage:

# ---- Snowflake Fetch Processor (final target) ----
FROM scratch AS snowflake-fetch-processor
COPY --from=cert-builder /etc/ssl/ /etc/ssl/
COPY --from=builder --chmod=0755 /out/snowflake-fetch-processor /app/snowflake-fetch-processor
ENTRYPOINT ["/app/snowflake-fetch-processor"]

11.2 scripts/task-manager.toml

[tasks.snowflake-fetch-processor]
name        = "Snowflake Fetch Processor"
description = "Ingests Snowflake Security Bulletins from HTML pages"
category    = "go-standalone"
container   = true
runner_cmd  = "go snowflake-fetch-processor"
aws_schedule = "go-snowflake-fetch-processor"
cron_match  = "0 6 * * *"
cpu_units   = 256
memory_mb   = 512
expected_duration_minutes = 15
design_doc  = "scripts/go-processors/snowflake-fetch-processor.design.md"

[[tasks.snowflake-fetch-processor.args]]
name = "force"
type = "flag"
required = false
default = "false"

11.3 terraform/go-schedules.tf

module "snowflake_fetch_processor" {
  source = "./modules/ecs-go-task"

  task_name                 = "snowflake-fetch-processor"
  schedule_expression       = "cron(0 6 * * ? *)"
  command                   = ["/app/snowflake-fetch-processor"]
  cpu                       = 256
  memory                    = 512
  schedule_enabled          = true
  expected_duration_minutes = 15

  cluster_arn        = aws_ecs_cluster.vdb.arn
  container_image    = "${aws_ecr_repository.go_processors.repository_url}:go-snowflake-fetch-processor-${var.go_container_image_tag}"
  execution_role_arn = aws_iam_role.ecs_execution.arn
  task_role_arn      = aws_iam_role.ecs_task.arn
  scheduler_role_arn = aws_iam_role.scheduler.arn
  subnet_ids         = var.subnet_ids
  security_group_ids = [aws_security_group.ecs_tasks.id]
  secrets            = local.go_task_secrets
  environment        = local.go_task_environment
  dlq_arn            = aws_sqs_queue.scheduler_dlq.arn
  aws_region         = var.aws_region
  log_retention_days = var.log_retention_days
}

11.4 .claude/hooks/post-push-ecr.sh

Add snowflake-fetch-processor to TARGETS (alphabetical order).

11.5 scripts/task-dashboard/cmd/ecr-build/targets.go

Add to simpleTargets (no data stage):

{Name: "snowflake-fetch-processor", DataStage: false},

11.6 justfile

Add backfill recipe:

go-snowflake-fetch-backfill TARGET="local" FORCE="false" LIMIT="0":
    #!/usr/bin/env bash
    set -euo pipefail
    if [ "{{TARGET}}" = "prod" ]; then ENV_FILE=".env.production"; else ENV_FILE=".env"; fi
    [ -f "$ENV_FILE" ] || { echo "ERROR: $ENV_FILE not found"; exit 1; }
    set -a; source "$ENV_FILE"; set +a
    unset EXPECTED_DURATION_MINUTES
    cd scripts/go-processors && go run ./cmd/snowflake-fetch-processor \
        --force={{FORCE}} --limit={{LIMIT}}

11.7 .github/workflows/go-ecr-deploy.yml

No edit required — matrix sourced from task-manager.toml.

11.8 scripts/go-processors/entrypoint.sh

No edit required.

Phase 12 — Slack Notifications

Standard notify package usage:

notif := notify.New(logger)
notif.Started(processorName)
notif.SetOvertimeCancel(ctx, cancel)
// ... per-record: notif.RecordError(...)
// ... final: notif.Completed / notif.Errored / notif.NoWork

Phase 13 — Performance & Resilience Checklist

• HTTP client: httpclient.New() (HTTP/2 default)
• Per-request timeout: 30s
• Single-worker concurrency (polite to small source)
• 1s delay between detail page fetches
• Per-request retry: 3 attempts, exponential backoff
• Transient errors: connection errors, timeouts, 5xx
• Resume set: db.LoadProcessedHashes at startup
• Tracker freshness: skipped under --force
• Soft deadline: checked before dispatching new work
• Pool: db.NewPool defaults
• S3 archive: after transaction commit

Phase 14 — Tests

14.1 Golden-file fixtures

Place under internal/snowflake/testdata/:

• listing_page_1.html — recorded listing page (page 1)
• listing_page_2.html — recorded listing page (page 2)
• detail_cve_2025_46330.html — recorded detail page

14.2 Test files

• parse_test.go: test ParseListing and ParseDetail against fixtures
• map_test.go: test ToSourceData produces correct osv.CVESourceData
• client_test.go: test HTTP fetch with mock server

14.3 Run tests

cd scripts/go-processors && go test ./internal/snowflake/... ./cmd/snowflake-fetch-processor/...

Phase 15 — Build & Deploy Verification

# 1. Compile
cd scripts/go-processors
go build ./...
go vet ./...

# 2. Local container build
cd ../..
podman build -f Containerfile.go-processors --target snowflake-fetch-processor \
    -t local/snowflake-fetch-processor:dev .

# 3. Dashboard TUI
just dashboard

Phase 16 — End-to-End Run/Verify

16.1 Local run against production DB

just go-snowflake-fetch-backfill TARGET=prod LIMIT=5 FORCE=true

Expected log shape:

{"time":"…","level":"INFO","msg":"started","processor":"snowflake-fetch-processor"}
{"time":"…","level":"INFO","msg":"fetched","count":14}
{"time":"…","level":"INFO","msg":"stored","cveId":"CVE-2025-46330","source":"snowflake"}
{"time":"…","level":"INFO","msg":"completed","stats":{"fetched":14,"stored":N,"skipped":M}}

16.2 Database inspection

set -a; source .env.production; set +a
psql "$DATABASE_URL" <<'SQL'
SELECT "cveId","source","datePublished","title"
  FROM "CVEMetadata" WHERE "source" = 'snowflake'
  ORDER BY "datePublished" DESC NULLS LAST LIMIT 10;

SELECT "primaryCveId","primarySource","aliasCveId","aliasSource","discoveredFrom"
  FROM "CVEAlias"
 WHERE "primarySource" = 'snowflake' OR "aliasSource" = 'snowflake'
 ORDER BY "discoveredAt" DESC LIMIT 20;

SELECT count(*) FROM "CVEReference" r
  JOIN "CVEMetadata" m ON r."cveId"=m."cveId" AND r."source"=m."source"
 WHERE m."source" = 'snowflake';

SELECT * FROM "BulkDataDumpTracker" WHERE "source" = 'snowflake';
SQL

16.3 ECS smoke run

After first ECR push, manually invoke EventBridge target once.

16.4 Re-run idempotency

just go-snowflake-fetch-backfill TARGET=prod LIMIT=5 FORCE=false

Should report NoWork or zero new rows.

Phase 17 — Definition of Done

• Phase 0 recon documented in design doc § 2 (with curl evidence)
• cmd/snowflake-fetch-processor/main.go compiles and go vet is clean
• internal/snowflake/ package with parse_test.go golden test
• processor.StoreCVESourceData is the only path to CVEMetadata writes
• Containerfile.go-processors, task-manager.toml, terraform/go-schedules.tf, .claude/hooks/post-push-ecr.sh, scripts/task-dashboard/cmd/ecr-build/targets.go, justfile all updated with locked name
• No migration needed (confirmed — existing schema sufficient)
• Slack Started/Completed/Errored/NoWork events confirmed
• § 16 run/verify executed end-to-end (logs, DB rows, Slack, idempotency)
• One Fargate fire observed in CloudWatch with exit code 0

Task Breakdown

Total estimated effort: ~8 hours

Risk Register

S3 Persistence

• Archive path: snowflake/files/{sha256}/{filename} ✓
• Quarantine path: failed-feeds/snowflake-fetch-processor-implementation-plan/{YYYY-MM-DD}/{reason}/{filename} ✓
• Failure reasons emitted: store-error

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

See the S3 Persistence Contract for the full reason taxonomy.