id: deps-dev-dependency-license-gate
namespace: company.team
description: |
Gate a lockfile on licence policy and known advisories with deps.dev. The flow downloads a
package-lock.json, requirements.txt, poetry.lock, uv.lock or Pipfile.lock from a raw URL,
resolves every pinned (system, name, version), looks all of them up with the deps.dev
`versionbatch` endpoint, scores the advisories it reports, evaluates the allowed SPDX
licences and the severity ceiling in DuckDB and writes a CSV report. CI calls the webhook
per pull request; a weekly schedule re-checks the main branch for advisories published
since merge. Violations already reported for the same lockfile URL are not posted again.
inputs:
- id: lockfile_url
type: STRING
displayName: Lockfile URL
description: Raw URL of the lockfile or pinned requirements file. A
`github.com/<owner>/<repo>/blob/<ref>/<path>` link is rewritten to
`raw.githubusercontent.com`. The default is OWASP NodeGoat pinned to a
commit, an intentionally outdated Node app.
defaults: https://raw.githubusercontent.com/OWASP/NodeGoat/c5cb68a7084e4ae7dcc60e6a98768720a81841e8/package-lock.json
- id: lockfile_file
type: FILE
required: false
description: Optional upload instead of the URL, for private repositories or
local testing. The file name (for example `requirements.txt`) drives
format detection.
- id: lockfile_format
type: SELECT
values:
- AUTO
- NPM_PACKAGE_LOCK
- PIP_REQUIREMENTS
- POETRY_LOCK
- UV_LOCK
- PIPFILE_LOCK
defaults: AUTO
description: AUTO detects the format from the file name (package-lock.json,
npm-shrinkwrap.json, *.txt, poetry.lock, uv.lock, Pipfile.lock).
- id: allowed_licenses
type: ARRAY
itemType: STRING
description: SPDX licence identifiers your policy allows (case-insensitive). An
`OR` expression passes when one alternative is fully allowed; an `AND`
expression needs every part. A `WITH` exception must be listed as written,
e.g. `GPL-2.0-only WITH Classpath-exception-2.0`.
defaults:
- MIT
- Apache-2.0
- BSD-2-Clause
- BSD-3-Clause
- ISC
- 0BSD
- Unlicense
- CC0-1.0
- Python-2.0
- PSF-2.0
- Zlib
- BlueOak-1.0.0
- id: max_allowed_severity
type: SELECT
values:
- NONE
- LOW
- MEDIUM
- HIGH
- CRITICAL
defaults: MEDIUM
description: Highest advisory severity tolerated (CVSS 3 bands, LOW < 4.0 <=
MEDIUM < 7.0 <= HIGH < 9.0 <= CRITICAL). `NONE` blocks on any advisory,
`CRITICAL` turns the CVSS check off. Advisories without a CVSS 3 score
count as MEDIUM; OSV malicious-package entries (`MAL-...`) always block.
- id: include_dev
type: BOOL
defaults: false
description: Also evaluate dev dependencies (npm `dev`/`devOptional`, Poetry
non-main groups, Pipfile `develop`). Optional dependencies are always
evaluated.
- id: allow_unknown_license
type: BOOL
defaults: false
description: Accept packages whose licence deps.dev cannot determine (no
licence, `non-standard`) or which deps.dev does not know at all (private
or yanked packages). They are still reported.
- id: state_id
type: STRING
required: false
description: Groups runs whose violations should be compared, for example
`owner/repo#123` for every push to one pull request, whose lockfile URL
changes with each commit SHA. Empty means one state per lockfile URL (per
file name for uploads).
- id: fail_on_violation
type: BOOL
defaults: false
description: true fails the execution when any violation exists (the CI gate;
the webhook sets it). false ends in WARNING so a scheduled sweep reports
without turning red.
triggers:
- id: pull_request_gate
type: io.kestra.plugin.core.trigger.Webhook
description: |
CI POSTs `{"lockfile_url": "https://raw.githubusercontent.com/<owner>/<repo>/<pr_head_sha>/package-lock.json"}` to
`/api/v1/main/executions/webhook/company.team/deps-dev-dependency-license-gate/<key>`. With `wait` and `returnOutputs`
the response carries the outputs (`verdict`, `violations`, `new_violations`, report URIs); a blocked gate answers HTTP 500.
key: "{{ secret('DEPS_DEV_GATE_WEBHOOK_KEY') }}"
wait: true
returnOutputs: true
inputs:
lockfile_url: "{{ trigger.body.lockfile_url }}"
lockfile_format: "{{ trigger.body.lockfile_format ?? 'AUTO' }}"
include_dev: "{{ trigger.body.include_dev ?? false }}"
state_id: "{{ trigger.body.state_id ?? '' }}"
fail_on_violation: "{{ trigger.body.fail_on_violation ?? true }}"
- id: weekly_main_branch_sweep
type: io.kestra.plugin.core.trigger.Schedule
description: New advisories are published against versions you already ship. A
weekly re-check of the main-branch lockfile (set `lockfile_url` to it)
posts only what is new since the last run.
cron: "0 7 * * 1"
inputs:
fail_on_violation: false
tasks:
- id: download_lockfile
type: io.kestra.plugin.core.http.Download
runIf: "{{ inputs.lockfile_file is null }}"
description: Plain HTTPS download, no Git checkout needed. A 404 (wrong ref or
path) fails here with the status code.
uri: "{{ (inputs.lockfile_url contains '://github.com/' and inputs.lockfile_url
contains '/blob/') ? (inputs.lockfile_url | replace({'://github.com/':
'://raw.githubusercontent.com/', '/blob/': '/'})) : inputs.lockfile_url
}}"
headers:
User-Agent: kestra-deps-dev-dependency-license-gate/1.0
(+https://kestra.io/blueprints)
- id: parse_lockfile
type: io.kestra.plugin.scripts.python.Script
description: |
Standard library only. Reads npm lockfile v1 (nested `dependencies`) and v2/v3 (`packages` map, alias names from
`name`, `link` workspaces skipped, git and file sources skipped), pip requirements (`==`/`===` pins only; extras,
markers, `--hash` continuations and `-r` lines handled, unpinned and URL requirements counted as skipped), and
poetry.lock, uv.lock and Pipfile.lock. PyPI names are normalised per PEP 503, which is what deps.dev expects.
A package nested at the same version in several places counts once.
taskRunner:
type: io.kestra.plugin.core.runner.Process
containerImage: python:3.13-slim
inputFiles:
lockfile: "{{ inputs.lockfile_file ?? outputs.download_lockfile.uri }}"
outputFiles:
- dependencies.csv
env:
LOCKFILE_SOURCE: "{{ inputs.lockfile_file ?? inputs.lockfile_url }}"
LOCKFILE_FORMAT: "{{ inputs.lockfile_format }}"
STATE_ID: "{{ inputs.state_id ?? '' }}"
UPLOADED: "{{ inputs.lockfile_file is not null }}"
script: |
import csv, hashlib, json, os, re, tomllib
source = os.environ["LOCKFILE_SOURCE"]
fmt = os.environ["LOCKFILE_FORMAT"]
text = open("lockfile", "rb").read().decode("utf-8-sig", errors="replace")
if text.lstrip()[:15].lower().startswith(("<!doctype", "<html")):
raise SystemExit("The URL returned an HTML page, not a lockfile. Use the raw file URL.")
if fmt == "AUTO":
base = source.split("?")[0].rstrip("/").rsplit("/", 1)[-1].lower()
if base.endswith("package-lock.json") or base == "npm-shrinkwrap.json":
fmt = "NPM_PACKAGE_LOCK"
elif base == "poetry.lock":
fmt = "POETRY_LOCK"
elif base == "uv.lock":
fmt = "UV_LOCK"
elif base == "pipfile.lock":
fmt = "PIPFILE_LOCK"
elif base.endswith((".txt", ".in")):
fmt = "PIP_REQUIREMENTS"
elif text.lstrip().startswith("{") and '"lockfileVersion"' in text:
fmt = "NPM_PACKAGE_LOCK"
else:
raise SystemExit(f"Cannot detect the lockfile format from '{base}'; set lockfile_format explicitly.")
def pep503(name):
return re.sub(r"[-_.]+", "-", name).lower()
REGISTRY_VERSION = re.compile(r"^[0-9][0-9A-Za-z.+_-]*$")
rows, skipped = [], {"unpinned": 0, "non_registry": 0, "workspace_or_link": 0, "includes_not_followed": 0}
if fmt == "NPM_PACKAGE_LOCK":
lock = json.loads(text)
label = f"npm package-lock v{lock.get('lockfileVersion', 1)}"
if "packages" in lock:
for key, meta in lock["packages"].items():
if key == "":
continue
if meta.get("link") or "node_modules/" not in key:
skipped["workspace_or_link"] += 1
continue
name = meta.get("name") or key.rsplit("node_modules/", 1)[-1]
ver, resolved = meta.get("version", ""), meta.get("resolved", "")
if not REGISTRY_VERSION.match(ver) or (resolved and not resolved.startswith("https://")):
skipped["non_registry"] += 1
continue
scope = "dev" if meta.get("dev") or meta.get("devOptional") else ("optional" if meta.get("optional") else "runtime")
rows.append(("NPM", name, ver, scope, key))
else:
def walk(deps, prefix):
for name, meta in (deps or {}).items():
key = f"{prefix}node_modules/{name}"
if REGISTRY_VERSION.match(meta.get("version", "")):
scope = "dev" if meta.get("dev") else ("optional" if meta.get("optional") else "runtime")
rows.append(("NPM", name, meta["version"], scope, key))
else:
skipped["non_registry"] += 1
walk(meta.get("dependencies"), key + "/")
walk(lock.get("dependencies"), "")
elif fmt == "PIP_REQUIREMENTS":
label = "pip requirements"
logical, buf = [], ""
for line in text.splitlines():
line = re.sub(r"(^|\s)#.*$", "", line).rstrip()
if line.endswith("\\"):
buf += line[:-1] + " "
continue
logical.append(buf + line)
buf = ""
for n, line in enumerate(logical, 1):
line = line.strip()
if not line or line.startswith("-"):
skipped["non_registry"] += line.startswith(("-e", "--editable"))
skipped["includes_not_followed"] += line.startswith(("-r", "-c", "--requirement", "--constraint"))
continue
req = re.split(r"\s--hash", line.split(";")[0])[0].strip()
m = re.match(r"^([A-Za-z0-9][A-Za-z0-9._-]*)\s*(\[[^\]]*\])?\s*===?\s*([^\s,;*]+)\s*$", req)
if m:
rows.append(("PYPI", pep503(m.group(1)), m.group(3), "runtime", f"line {n}"))
elif " @ " in req or "://" in req:
skipped["non_registry"] += 1
else:
skipped["unpinned"] += 1
elif fmt in ("POETRY_LOCK", "UV_LOCK"):
label = "poetry.lock" if fmt == "POETRY_LOCK" else "uv.lock"
for pkg in tomllib.loads(text).get("package", []):
src = pkg.get("source") or {}
if src.get("type") in ("git", "directory", "file", "url") or any(k in src for k in ("git", "editable", "virtual", "path", "directory")) or ("url" in src and "type" not in src):
skipped["non_registry"] += 1
continue
groups = pkg.get("groups") or [pkg.get("category", "main")]
scope = "runtime" if fmt == "UV_LOCK" or "main" in groups else "dev"
rows.append(("PYPI", pep503(pkg["name"]), str(pkg["version"]), scope, label))
elif fmt == "PIPFILE_LOCK":
label = "Pipfile.lock"
lock = json.loads(text)
for section, scope in (("default", "runtime"), ("develop", "dev")):
for name, meta in (lock.get(section) or {}).items():
ver = (meta.get("version") or "").lstrip("=")
if ver:
rows.append(("PYPI", pep503(name), ver, scope, f"{section}/{name}"))
else:
skipped["non_registry"] += 1
seen, unique = set(), []
for r in rows:
if r[:4] not in seen:
seen.add(r[:4])
unique.append(r)
with open("dependencies.csv", "w", newline="", encoding="utf-8") as f:
w = csv.writer(f)
w.writerow(["system", "name", "version", "scope", "lockfile_path"])
w.writerows(unique)
scopes = {s: sum(1 for r in unique if r[3] == s) for s in ("runtime", "optional", "dev")}
if not unique:
raise SystemExit(f"No pinned registry packages found in this {label} (skipped: {skipped}).")
print(f"Parsed {label}: {len(unique)} resolved packages ({scopes['runtime']} runtime, {scopes['optional']} optional, {scopes['dev']} dev); skipped {skipped}")
# state per lockfile URL; an upload is keyed on its file name, since its storage URI changes every execution
identity = os.environ.get("STATE_ID") or ("upload:" + source.rsplit("/", 1)[-1] if os.environ["UPLOADED"] == "true" else source)
state_key = "deps_dev_gate_" + hashlib.sha256(identity.encode()).hexdigest()[:16]
print("::" + json.dumps({"outputs": {"format": label, "packages": len(unique), **scopes, **{"skipped_" + k: v for k, v in skipped.items()}, "state_key": state_key}}) + "::")
- id: previous_violations
type: io.kestra.plugin.core.kv.Get
key: "{{ outputs.parse_lockfile.vars.state_key }}"
errorOnMissing: false
description: JSON array of the violation fingerprints reported for this lockfile
URL last time. Missing on the first run (BASELINE), so every violation is
new once.
- id: advisory_cache
type: io.kestra.plugin.core.kv.Get
key: deps_dev_advisory_cache
errorOnMissing: false
description: Advisory details (CVSS 3 score, title, aliases) shared by every
lockfile, so a re-run or a second repository only fetches advisories not
seen in the last 7 days.
- id: query_deps_dev
type: io.kestra.plugin.scripts.python.Script
description: |
One `POST /v3alpha/versionbatch` per 1,000 package versions (the endpoint pages its responses; every `nextPageToken`
is followed) returns licences and advisory keys for all packages at once. Each distinct advisory not in the cache is
then read from `GET /v3/advisories/{id}` with a small thread pool and pacing. 429 and 5xx responses back off and
retry. Licence expressions are converted to disjunctive normal form here so DuckDB can evaluate the policy with list
functions. Versions deps.dev does not know come back without a `version` and are kept as not found.
taskRunner:
type: io.kestra.plugin.core.runner.Process
containerImage: python:3.13-slim
inputFiles:
dependencies.csv: "{{ outputs.parse_lockfile.outputFiles['dependencies.csv'] }}"
advisory_cache.json: "{{ outputs.advisory_cache.value ?? '{}' }}"
outputFiles:
- versions.csv
- advisories.csv
- advisory_cache_out.json
env:
SCOPES: "{{ inputs.include_dev ? 'runtime,optional,dev' : 'runtime,optional' }}"
ADVISORY_CONCURRENCY: "6"
ADVISORY_CACHE_DAYS: "7"
script: |
import csv, json, os, re, time, urllib.error, urllib.parse, urllib.request
from concurrent.futures import ThreadPoolExecutor
from datetime import datetime, timedelta, timezone
API = "https://api.deps.dev"
HEADERS = {"User-Agent": "kestra-deps-dev-dependency-license-gate/1.0 (+https://kestra.io/blueprints)", "Content-Type": "application/json"}
now = datetime.now(timezone.utc)
def call(url, body=None, attempts=5):
for i in range(attempts):
req = urllib.request.Request(url, data=json.dumps(body).encode() if body is not None else None, headers=HEADERS, method="POST" if body is not None else "GET")
try:
with urllib.request.urlopen(req, timeout=60) as r:
return json.load(r)
except urllib.error.HTTPError as e:
if e.code == 404:
return None
if e.code not in (429, 500, 502, 503, 504) or i == attempts - 1:
raise
except urllib.error.URLError:
if i == attempts - 1:
raise
time.sleep(2 ** i)
def dnf(expr):
toks, pos = re.findall(r"\(|\)|[^\s()]+", expr), 0
def peek():
return toks[pos].upper() if pos < len(toks) else None
def term():
nonlocal pos
if peek() == "(":
pos += 1
r = disj()
pos += peek() == ")"
return r
lic = toks[pos]; pos += 1
if peek() == "WITH" and pos + 1 < len(toks):
lic = f"{lic} WITH {toks[pos + 1]}"; pos += 2
return [[lic]]
def conj():
nonlocal pos
r = term()
while peek() == "AND":
pos += 1
rhs = term()
r = [a + b for a in r for b in rhs][:64]
return r
def disj():
nonlocal pos
r = conj()
while peek() == "OR":
pos += 1
r = r + conj()
return r[:64]
return disj() if toks else []
def licence_dnf(licences):
if not licences:
return None
alts = [[]]
for expr in licences: # several declared licences must all hold
if expr.strip().lower() in ("", "non-standard", "unknown", "noassertion", "none"):
return None
alts = [a + b for a in alts for b in dnf(expr)][:64]
return alts
scopes = set(os.environ["SCOPES"].split(","))
deps = [d for d in csv.DictReader(open("dependencies.csv", encoding="utf-8")) if d["scope"] in scopes]
keys = sorted({(d["system"], d["name"], d["version"]) for d in deps})
versions, calls = {}, 0
for i in range(0, len(keys), 1000):
body = {"requests": [{"versionKey": {"system": s, "name": n, "version": v}} for s, n, v in keys[i:i + 1000]]}
while True:
resp = call(f"{API}/v3alpha/versionbatch", body)
calls += 1
for r in resp.get("responses", []):
k = r["request"]["versionKey"]
versions[(k["system"], k["name"], k["version"])] = r.get("version")
if not resp.get("nextPageToken"):
break
body["pageToken"] = resp["nextPageToken"]
time.sleep(0.2)
try:
cache = json.load(open("advisory_cache.json", encoding="utf-8"))
except ValueError:
cache = {}
cache = cache if isinstance(cache, dict) else {}
ids = sorted({a["id"] for v in versions.values() if v for a in v.get("advisoryKeys", [])})
stale = (now - timedelta(days=int(os.environ["ADVISORY_CACHE_DAYS"]))).isoformat()
todo = [a for a in ids if a not in cache or cache[a].get("fetched_at", "") < stale]
def fetch(adv_id):
time.sleep(0.05)
return adv_id, call(f"{API}/v3/advisories/{urllib.parse.quote(adv_id, safe='')}") or {}
with ThreadPoolExecutor(max_workers=int(os.environ["ADVISORY_CONCURRENCY"])) as pool:
for adv_id, d in pool.map(fetch, todo):
cache[adv_id] = {"title": d.get("title") or adv_id, "cvss3_score": d.get("cvss3Score") or 0.0, "cvss3_vector": d.get("cvss3Vector") or "",
"aliases": d.get("aliases") or [], "url": d.get("url") or f"https://osv.dev/vulnerability/{adv_id}", "fetched_at": now.isoformat()}
with open("versions.csv", "w", newline="", encoding="utf-8") as f:
w = csv.writer(f)
w.writerow(["system", "name", "version", "found", "licenses", "license_dnf", "is_deprecated", "published_at", "advisory_ids"])
for key in keys:
info = versions.get(key) or {}
alts = licence_dnf(info.get("licenses") or [])
w.writerow([*key, bool(info), " AND ".join(info.get("licenses") or []), json.dumps(alts) if alts is not None else "",
info.get("isDeprecated", False), info.get("publishedAt", ""), json.dumps([a["id"] for a in info.get("advisoryKeys", [])])])
with open("advisories.csv", "w", newline="", encoding="utf-8") as f:
w = csv.writer(f)
w.writerow(["advisory_id", "title", "cvss3_score", "cvss3_vector", "aliases", "url"])
for a in ids:
c = cache[a]
w.writerow([a, c["title"], c["cvss3_score"], c["cvss3_vector"], json.dumps(c["aliases"]), c["url"]])
keep = (now - timedelta(days=90)).isoformat()
json.dump({k: v for k, v in cache.items() if v.get("fetched_at", "") >= keep}, open("advisory_cache_out.json", "w", encoding="utf-8"), separators=(",", ":"))
found = sum(1 for v in versions.values() if v)
print(f"deps.dev versionbatch: {len(keys)} package versions ({', '.join(sorted(scopes))}) in {calls} request(s), {found} found, {len(keys) - found} not found")
print(f"deps.dev advisories: {len(ids)} distinct ids, {len(todo)} fetched from /v3/advisories, {len(ids) - len(todo)} from the KV cache")
print("::" + json.dumps({"outputs": {"queried": len(keys), "found": found, "not_found": len(keys) - found, "batch_requests": calls,
"advisories": len(ids), "advisories_fetched": len(todo), "advisories_cached": len(ids) - len(todo)}}) + "::")
- id: save_advisory_cache
type: io.kestra.plugin.core.kv.Set
description: Writes the refreshed advisory cache back (entries older than 90
days are dropped, so it stays small).
key: deps_dev_advisory_cache
value: "{{ read(outputs.query_deps_dev.outputFiles['advisory_cache_out.json']) }}"
- id: evaluate_policy
type: io.kestra.plugin.jdbc.duckdb.Queries
description: |
Applies the policy in SQL. Licence: an expression is ALLOWED when one of its alternatives has every licence in the
allowed list, DISALLOWED otherwise, UNKNOWN when deps.dev has no usable licence and NOT_FOUND when deps.dev does not
know the version. Advisories: CVSS 3 score to severity band, `MAL-` (malicious package) above every band; when a package carries the same vulnerability under two
ids (a PYSEC and its GHSA alias), only the better-scored one is kept. One row per violation gets a fingerprint; the
fingerprints from the last run split them into new and already reported, and show which were resolved.
url: "jdbc:duckdb:"
fetchType: FETCH_ONE
inputFiles:
dependencies.csv: "{{ outputs.parse_lockfile.outputFiles['dependencies.csv'] }}"
versions.csv: "{{ outputs.query_deps_dev.outputFiles['versions.csv'] }}"
advisories.csv: "{{ outputs.query_deps_dev.outputFiles['advisories.csv'] }}"
previous.json: "{{ outputs.previous_violations.value ?? '[]' }}"
policy.json: "{{ {'allowed': inputs.allowed_licenses, 'max_severity':
inputs.max_allowed_severity, 'allow_unknown':
inputs.allow_unknown_license} | toJson }}"
outputFiles:
- report_csv
- violations_csv
sql: |
CREATE TABLE policy AS
SELECT list_transform(allowed, lambda l: upper(trim(l))) AS allowed,
CASE max_severity WHEN 'NONE' THEN 0 WHEN 'LOW' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'HIGH' THEN 3 ELSE 4 END AS max_rank,
allow_unknown
FROM read_json('policy.json', columns = {'allowed': 'VARCHAR[]', 'max_severity': 'VARCHAR', 'allow_unknown': 'BOOLEAN'});
CREATE TABLE deps AS
SELECT system, name, version,
CASE WHEN bool_or(scope = 'runtime') THEN 'runtime' WHEN bool_or(scope = 'optional') THEN 'optional' ELSE 'dev' END AS scope,
count(*) AS lockfile_entries, min(lockfile_path) AS lockfile_path
FROM read_csv('dependencies.csv', header = true, all_varchar = true)
GROUP BY system, name, version;
CREATE TABLE packages AS
SELECT d.system, d.name, d.version, d.scope, d.lockfile_path,
CAST(v.found AS BOOLEAN) AS found, nullif(v.licenses, '') AS licenses,
CASE WHEN NOT CAST(v.found AS BOOLEAN) THEN 'NOT_FOUND'
WHEN nullif(v.license_dnf, '') IS NULL THEN 'UNKNOWN'
WHEN list_bool_or(list_transform(from_json(v.license_dnf, '[["VARCHAR"]]'),
lambda alt: list_bool_and(list_transform(alt, lambda l: list_contains(p.allowed, upper(l)))))) THEN 'ALLOWED'
ELSE 'DISALLOWED' END AS licence_status,
CAST(v.is_deprecated AS BOOLEAN) AS is_deprecated,
from_json(v.advisory_ids, '["VARCHAR"]') AS advisory_ids
FROM deps d
JOIN read_csv('versions.csv', header = true, all_varchar = true) v USING (system, name, version)
CROSS JOIN policy p;
CREATE TABLE package_advisories AS
WITH raw AS (
SELECT p.system, p.name, p.version, a.advisory_id, a.title, a.url,
CAST(a.cvss3_score AS DOUBLE) AS cvss3_score,
from_json(a.aliases, '["VARCHAR"]') AS aliases
FROM (SELECT system, name, version, unnest(advisory_ids) AS advisory_id FROM packages) p
JOIN read_csv('advisories.csv', header = true, all_varchar = true) a USING (advisory_id)
)
SELECT r.*,
CASE WHEN advisory_id LIKE 'MAL-%' THEN 'MALICIOUS' WHEN cvss3_score >= 9 THEN 'CRITICAL' WHEN cvss3_score >= 7 THEN 'HIGH'
WHEN cvss3_score >= 4 THEN 'MEDIUM' WHEN cvss3_score > 0 THEN 'LOW' ELSE 'UNSCORED' END AS severity,
CASE WHEN advisory_id LIKE 'MAL-%' THEN 9 WHEN cvss3_score >= 9 THEN 4 WHEN cvss3_score >= 7 THEN 3 WHEN cvss3_score >= 4 THEN 2
WHEN cvss3_score > 0 THEN 1 ELSE 2 END AS severity_rank
FROM raw r
WHERE NOT EXISTS (
SELECT 1 FROM raw o
WHERE o.system = r.system AND o.name = r.name AND o.version = r.version AND o.advisory_id <> r.advisory_id
AND (list_contains(o.aliases, r.advisory_id) OR list_contains(r.aliases, o.advisory_id) OR len(list_intersect(o.aliases, r.aliases)) > 0)
AND (o.cvss3_score > r.cvss3_score OR (o.cvss3_score = r.cvss3_score AND (o.advisory_id LIKE 'GHSA-%') AND NOT (r.advisory_id LIKE 'GHSA-%'))
OR (o.cvss3_score = r.cvss3_score AND (o.advisory_id LIKE 'GHSA-%') = (r.advisory_id LIKE 'GHSA-%') AND o.advisory_id < r.advisory_id))
);
CREATE TABLE violations AS
SELECT k.system, k.name, k.version, k.scope, 'LICENSE_DISALLOWED' AS rule, k.licenses AS detail, k.licenses AS fp_detail, 5 AS rank, 0.0 AS score
FROM packages k WHERE licence_status = 'DISALLOWED'
UNION ALL
SELECT k.system, k.name, k.version, k.scope, 'LICENSE_UNKNOWN',
CASE WHEN licence_status = 'NOT_FOUND' THEN 'version not found on deps.dev' ELSE coalesce(k.licenses, 'no licence declared') END,
licence_status, 5, 0.0
FROM packages k CROSS JOIN policy p WHERE licence_status IN ('UNKNOWN', 'NOT_FOUND') AND NOT p.allow_unknown
UNION ALL
SELECT a.system, a.name, a.version, k.scope, 'ADVISORY',
a.advisory_id || ' ' || a.severity || CASE WHEN a.cvss3_score > 0 THEN ' ' || a.cvss3_score ELSE '' END || ' ' ||
CASE WHEN a.title = a.advisory_id THEN 'aka ' || array_to_string(a.aliases, ', ') ELSE a.title END,
a.advisory_id, a.severity_rank, a.cvss3_score
FROM package_advisories a JOIN packages k USING (system, name, version) CROSS JOIN policy p
WHERE a.severity_rank > p.max_rank;
CREATE TABLE previous AS
SELECT unnest(from_json(content, '["VARCHAR"]')) AS fingerprint FROM read_text('previous.json');
CREATE TABLE flagged AS
SELECT *, fingerprint NOT IN (SELECT fingerprint FROM previous) AS is_new
FROM (SELECT v.*, lower(v.system) || ':' || v.name || '@' || v.version || '|' || v.rule || '|' || v.fp_detail AS fingerprint FROM violations v);
CREATE TABLE report AS
SELECT k.system, k.name, k.version, k.scope, k.lockfile_path, k.licenses, k.licence_status, k.is_deprecated,
count(DISTINCT a.advisory_id) AS advisories,
coalesce(max(a.cvss3_score), 0) AS max_cvss3,
coalesce(arg_max(a.severity, a.severity_rank * 100 + a.cvss3_score), '') AS worst_severity,
coalesce(string_agg(DISTINCT a.advisory_id, ' '), '') AS advisory_ids,
(SELECT count(*) FROM flagged f WHERE f.system = k.system AND f.name = k.name AND f.version = k.version) AS violations,
(SELECT count(*) FROM flagged f WHERE f.system = k.system AND f.name = k.name AND f.version = k.version AND f.is_new) AS new_violations,
'https://deps.dev/' || lower(k.system) || '/' || replace(k.name, '/', '%2F') || '/' || k.version AS deps_dev_url
FROM packages k
LEFT JOIN package_advisories a USING (system, name, version)
GROUP BY ALL;
COPY (SELECT CASE WHEN violations > 0 THEN 'FAIL' ELSE 'PASS' END AS verdict, *
FROM report ORDER BY violations > 0 DESC, max_cvss3 DESC, system, name, version)
TO '{{ outputFiles.report_csv }}' (HEADER, DELIMITER ',');
COPY (SELECT system, name, version, scope, rule, detail, is_new, fingerprint FROM flagged ORDER BY is_new DESC, rank DESC, score DESC, name)
TO '{{ outputFiles.violations_csv }}' (HEADER, DELIMITER ',');
SELECT
(SELECT count(*) FROM packages) AS evaluated,
(SELECT count(*) FILTER (WHERE licence_status = 'ALLOWED') FROM packages) AS licence_allowed,
(SELECT count(*) FILTER (WHERE licence_status = 'DISALLOWED') FROM packages) AS licence_disallowed,
(SELECT count(*) FILTER (WHERE licence_status = 'UNKNOWN') FROM packages) AS licence_unknown,
(SELECT count(*) FILTER (WHERE licence_status = 'NOT_FOUND') FROM packages) AS not_found,
(SELECT count(DISTINCT (system, name, version)) FROM package_advisories) AS packages_with_advisories,
(SELECT count(*) FROM package_advisories) AS advisory_findings,
(SELECT count(*) FILTER (WHERE severity = 'MALICIOUS') FROM package_advisories) AS malicious,
(SELECT count(*) FILTER (WHERE severity = 'CRITICAL') FROM package_advisories) AS critical,
(SELECT count(*) FILTER (WHERE severity = 'HIGH') FROM package_advisories) AS high,
(SELECT count(*) FILTER (WHERE severity = 'MEDIUM') FROM package_advisories) AS medium,
(SELECT count(*) FILTER (WHERE severity = 'LOW') FROM package_advisories) AS low,
(SELECT count(*) FILTER (WHERE severity = 'UNSCORED') FROM package_advisories) AS unscored,
(SELECT count(*) FROM packages WHERE is_deprecated) AS deprecated,
(SELECT count(*) FILTER (WHERE violations > 0) FROM report) AS failing_packages,
(SELECT count(*) FROM flagged) AS violations,
(SELECT count(*) FILTER (WHERE rule = 'LICENSE_DISALLOWED') FROM flagged) AS licence_violations,
(SELECT count(*) FILTER (WHERE rule = 'LICENSE_UNKNOWN') FROM flagged) AS unknown_licence_violations,
(SELECT count(*) FILTER (WHERE rule = 'ADVISORY') FROM flagged) AS advisory_violations,
(SELECT count(*) FILTER (WHERE is_new) FROM flagged) AS new_violations,
(SELECT count(*) FROM previous WHERE fingerprint NOT IN (SELECT fingerprint FROM flagged)) AS resolved_violations,
(SELECT count(*) FROM previous) = 0 AS is_baseline,
(SELECT coalesce(string_agg('- ' || lower(system) || ' `' || name || '@' || version || '` (' || scope || ') ' || rule || ': ' || left(detail, 110), chr(10) ORDER BY rank DESC, score DESC, name), '')
|| CASE WHEN (SELECT count(*) FROM flagged WHERE is_new) > 15 THEN chr(10) || '... and ' || ((SELECT count(*) FROM flagged WHERE is_new) - 15) || ' more in the violations CSV' ELSE '' END
FROM (SELECT * FROM flagged WHERE is_new ORDER BY rank DESC, score DESC, name LIMIT 15)) AS new_violation_lines,
(SELECT coalesce(CAST(to_json(list(fingerprint ORDER BY fingerprint)) AS VARCHAR), '[]') FROM flagged) AS fingerprints;
- id: save_violations
type: io.kestra.plugin.core.kv.Set
description: Current violation fingerprints for this lockfile URL. A violation
fixed and later reintroduced is reported again.
key: "{{ outputs.parse_lockfile.vars.state_key }}"
value: "{{ outputs.evaluate_policy.outputs[0].row.fingerprints }}"
- id: summary
type: io.kestra.plugin.core.log.Log
message: |
{{ outputs.parse_lockfile.vars.format }} from {{ inputs.lockfile_file ?? inputs.lockfile_url }}: {{ outputs.parse_lockfile.vars.packages }} resolved packages, {{ outputs.evaluate_policy.outputs[0].row.evaluated }} evaluated ({{ inputs.include_dev ? 'dev included' : 'dev excluded' }}){{ outputs.evaluate_policy.outputs[0].row.is_baseline ? ', BASELINE run' : '' }}.
Licences: {{ outputs.evaluate_policy.outputs[0].row.licence_allowed }} allowed, {{ outputs.evaluate_policy.outputs[0].row.licence_disallowed }} disallowed, {{ outputs.evaluate_policy.outputs[0].row.licence_unknown }} unknown, {{ outputs.evaluate_policy.outputs[0].row.not_found }} not found on deps.dev.
Advisories: {{ outputs.evaluate_policy.outputs[0].row.advisory_findings }} on {{ outputs.evaluate_policy.outputs[0].row.packages_with_advisories }} packages ({{ outputs.evaluate_policy.outputs[0].row.malicious }} malicious-package, {{ outputs.evaluate_policy.outputs[0].row.critical }} critical, {{ outputs.evaluate_policy.outputs[0].row.high }} high, {{ outputs.evaluate_policy.outputs[0].row.medium }} medium, {{ outputs.evaluate_policy.outputs[0].row.low }} low, {{ outputs.evaluate_policy.outputs[0].row.unscored }} unscored); {{ outputs.evaluate_policy.outputs[0].row.deprecated }} deprecated versions.
Policy (max severity {{ inputs.max_allowed_severity }}): {{ outputs.evaluate_policy.outputs[0].row.violations }} violations on {{ outputs.evaluate_policy.outputs[0].row.failing_packages }} packages ({{ outputs.evaluate_policy.outputs[0].row.licence_violations }} licence, {{ outputs.evaluate_policy.outputs[0].row.unknown_licence_violations }} unknown licence, {{ outputs.evaluate_policy.outputs[0].row.advisory_violations }} advisory); {{ outputs.evaluate_policy.outputs[0].row.new_violations }} new, {{ outputs.evaluate_policy.outputs[0].row.resolved_violations }} resolved since the last run.
Report: {{ outputs.evaluate_policy.outputFiles.report_csv }}
- id: gate
type: io.kestra.plugin.core.flow.If
condition: "{{ outputs.evaluate_policy.outputs[0].row.violations > 0 }}"
then:
- id: notify_new
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
runIf: "{{ outputs.evaluate_policy.outputs[0].row.new_violations > 0 }}"
allowFailure: true
description: Posts only violations not reported before for this lockfile URL.
allowFailure keeps the gate verdict intact when the webhook secret is
not configured.
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{
"text": {{ ('deps.dev gate: ' ~ outputs.evaluate_policy.outputs[0].row.new_violations ~ ' new policy violations in ' ~ (inputs.lockfile_file ?? inputs.lockfile_url)) | toJson }},
"blocks": [
{"type": "section", "text": {"type": "mrkdwn", "text": {{ (':no_entry: *deps.dev licence and advisory gate*: ' ~ outputs.evaluate_policy.outputs[0].row.new_violations ~ ' new of ' ~ outputs.evaluate_policy.outputs[0].row.violations ~ ' violations on ' ~ outputs.evaluate_policy.outputs[0].row.failing_packages ~ ' packages in `' ~ (inputs.lockfile_file ?? inputs.lockfile_url) ~ '` (max severity ' ~ inputs.max_allowed_severity ~ ', ' ~ (inputs.fail_on_violation ? 'blocking' : 'report only') ~ ')') | toJson }} }},
{"type": "section", "text": {"type": "mrkdwn", "text": {{ outputs.evaluate_policy.outputs[0].row.new_violation_lines | toJson }} }},
{"type": "context", "elements": [{"type": "mrkdwn", "text": {{ ('Execution ' ~ (kestra.url ?? '') ~ '/ui/executions/' ~ flow.namespace ~ '/' ~ flow.id ~ '/' ~ execution.id) | toJson }} }]}
]
}
- id: block_merge
type: io.kestra.plugin.core.execution.Fail
runIf: "{{ inputs.fail_on_violation }}"
errorMessage: "{{ outputs.evaluate_policy.outputs[0].row.violations }} policy
violations on {{
outputs.evaluate_policy.outputs[0].row.failing_packages }} packages;
see {{ outputs.evaluate_policy.outputFiles.violations_csv }}"
- id: warn_only
type: io.kestra.plugin.core.execution.Fail
runIf: "{{ not inputs.fail_on_violation }}"
allowFailure: true
description: Report mode. The failure is allowed, so the execution ends in
WARNING instead of FAILED.
errorMessage: "{{ outputs.evaluate_policy.outputs[0].row.violations }} policy
violations on {{
outputs.evaluate_policy.outputs[0].row.failing_packages }} packages
(fail_on_violation is false)"
else:
- id: passed
type: io.kestra.plugin.core.log.Log
message: "Gate passed: {{ outputs.evaluate_policy.outputs[0].row.evaluated }}
packages within policy ({{
outputs.evaluate_policy.outputs[0].row.resolved_violations }} earlier
violations resolved)."
outputs:
- id: report_csv
type: FILE
value: "{{ outputs.evaluate_policy.outputFiles.report_csv }}"
- id: violations_csv
type: FILE
value: "{{ outputs.evaluate_policy.outputFiles.violations_csv }}"
- id: verdict
type: STRING
value: "{{ outputs.evaluate_policy.outputs[0].row.violations > 0 ? 'FAIL' :
'PASS' }}"
- id: violations
type: INT
value: "{{ outputs.evaluate_policy.outputs[0].row.violations }}"
- id: new_violations
type: INT
value: "{{ outputs.evaluate_policy.outputs[0].row.new_violations }}"
