How I built a dependency risk scanner with Coral in 7 days
— Captain's Log entry for the Pirates of the Coral-bean Hackathon. Why this project Every developer has 5-10 side projects with rotting dependencies and doesn't know it. The 2024 xz-utils backdoor was caught by accident — one engineer noticed SSH was 500 ms slower than usual. That's how close it came. Tools like Snyk and Dependabot catch known CVEs after they're published. Nothing checks the three signals that together predict a future supply-chain attack: active CVEs · abandoned maintainer · collapsing downloads . That three-way signal only exists if you can JOIN across OSV (Google's vulnerability database), the npm registry , and the npm download API . Which is exactly what Coral does. The query that took me 6 days to earn WITH pkg AS ( SELECT name , latest_version , repository__url , time__modified AS last_publish_at FROM npm . packages WHERE package_name = : pkg ), cves AS ( SELECT affected__package__name AS package_name , COUNT ( * ) AS cve_count , MAX ( CASE database_specific__severity WHEN 'CRITICAL' THEN 4 WHEN 'HIGH' THEN 3 WHEN 'MODERATE' THEN 2 WHEN 'LOW' THEN 1 ELSE 0 END ) AS worst_sev_rank FROM osv . vulnerabilities WHERE package_name = : pkg AND ecosystem = 'npm' AND withdrawn IS NULL GROUP BY affected__package__name ), dl_month AS ( SELECT downloads FROM npm_downloads . downloads_last_month WHERE package_name = : pkg ) SELECT pkg . * , COALESCE ( cves . cve_count , 0 ) AS cve_count , COALESCE ( cves . worst_sev_rank , 0 ) AS worst_severity_rank , dl_month . downloads FROM pkg LEFT JOIN cves ON cves . package_name = pkg . name LEFT JOIN dl_month ON 1 = 1 ; One query. Three live systems — three different hosts ( registry.npmjs.org , api.osv.dev , api.npmjs.org ). Zero glue code. No ChatGPT instance on earth can run this. Verified against minimist : 2 CVEs, worst severity CRITICAL, 531M downloads/month. Day 1 — The OSV source spec OSV is a public REST API. The Coral source spec is a single YAML file, and the skeleton came together quickly. The hard part