Analysis guide

aveloxis-cve collects two kinds of data for a chosen set of repositories:

  • CVE fixes across every branch. Which commits fix which CVEs, and when each fix reached each branch, including release, stable, LTS and since-deleted branches that Aveloxis never sees.

  • Complexity over time. A full scc snapshot of the default branch at the end of every sliding window (60 days, advanced monthly by default), plus the files changed within each window.

This directory holds the queries that answer the project's questions from that data. Each query has a stable ID, states the question it answers, and says how to read its result.

Every query is executed by TestAnalysisQueriesRun (internal/db/analysisdoc_test.go) against a migrated schema seeded with a small hand-built dataset (internal/db/testdata/analysis_seed.sql). The test runs each query read-only and compares its full result with a reviewed expected result (internal/db/testdata/analysis_golden.txt). The seed includes the cases that make naive queries double-count:

  • a commit naming two CVEs;

  • archived copies of a live branch;

  • a fix completed on a branch by its own commit.

A query that stops matching the schema, or whose answer changes, fails the build.

File

Queries

Covers

cve.md

CVE-01 to CVE-17

Fix commits, propagation, backports, missing fixes, reverted fixes, authors, footprint, code fixes versus dependency updates

complexity.md

LAB-01 to LAB-14

Window series, deltas, density, trend, languages, files, churn, decomposition, generated code

cve-complexity.md

X-01 to X-05

CVE fixes in their complexity context

data-quality.md

DQ-01 to DQ-09

Whether the data is complete and consistent enough to trust

findings.md

none

What the first collection of 14 repositories shows: CVE mentions, backports, complexity trends, generated code, agreement with Aveloxis, open questions

The schema is described table by table in ../data.md, and drawn in ../schema.md.

The questions

#

Question

Queries

1

Which CVEs has a project fixed, and by which commits?

CVE-01, CVE-02, CVE-11, CVE-14

2

On which branches did each fix land, and when?

CVE-03, CVE-04, CVE-15

3

How long do backports take?

CVE-04, CVE-06, CVE-07, CVE-13

4

Which maintained branches never got a fix, or lost one to a revert?

CVE-05, CVE-12

5

Who fixes CVEs, and what code do fixes touch?

CVE-08, CVE-09, CVE-10

5a

Did a fix change the project's own code, or only a dependency version, build, tests or docs?

CVE-16, CVE-17

6

How did size and complexity rise or fall across the windows?

LAB-01, LAB-02, LAB-03

7

Which languages and files drive the change?

LAB-04, LAB-05, LAB-06, LAB-07, LAB-12, LAB-13, LAB-14

8

What happened inside each window: churn, added, removed and modified files?

LAB-08, LAB-09, LAB-10, LAB-11

9

How do CVE fixes relate to complexity?

X-01, X-02, X-03, X-04, X-05

10

Is the data complete enough to trust?

DQ-01, DQ-02, DQ-03, DQ-04, DQ-05, DQ-06, DQ-07, DQ-08, DQ-09

Running a query

The queries are PostgreSQL with psql variables for their parameters:

Variable

Meaning

Example

:repo_id

Aveloxis's repo_id of a tracked repository

-v repo_id=123

:'cve_id'

A CVE id, canonical upper case

-v cve_id=CVE-2024-1234

:window_days, :step_months

The labor window configuration of the series (defaults 60 and 1)

-v window_days=60 -v step_months=1

:'file_path'

A path from the repository root (LAB-07)

-v file_path=src/server.c

Copy a query into a file and run it with every variable it uses. psql substitutes variables only in scripts and standard input, not in -c.

psql "$AVELOXIS_DSN" -v repo_id=123 -v cve_id=CVE-2024-1234 \
     -v window_days=60 -v step_months=1 -f query.sql

Run as a role that can read aveloxis_cve: the aveloxis_cve role from sql/roles.sql owns it. Queries marked Reads Aveloxis also read an aveloxis_data table, which that role can read too. Every query only reads. To be sure, run it inside BEGIN READ ONLY; ... ROLLBACK;.

Timestamps are timestamptz. Run SET TIME ZONE 'UTC'; first to see them as aveloxis-cve stores and compares them.

Every query at once. run.py, at the repository root, runs every query in this directory for each tracked repository (or the repo_ids given as arguments) and writes the results to analysis_results.txt next to itself:

  • It connects as aveloxis-cve does, from $AVELOXIS_CVE_CONFIG or the aveloxis-cve.json next to it:

    • documented defaults for omitted keys;

    • the aveloxis_config_path import when there is no database block, which must set user and dbname; a relative path is read from the working directory, as aveloxis-cve reads it, while the default configuration is the one next to run.py (aveloxis-cve reads ./aveloxis-cve.json): run both from the repository root, or set an absolute $AVELOXIS_CVE_CONFIG (with an absolute aveloxis_config_path) for both;

    • PGHOST, PGUSER, PGDATABASE and PGPASSWORD (or ~/.pgpass) when those values are empty, and sslmode prefer when it is empty.

    Where it might read the configuration differently from aveloxis-cve, it refuses instead: a key in another case, a key given twice, a host list, PGSERVICE (libpq would let a service file override the configuration, while pgx lets one fill only empty values). It ignores PGHOSTADDR, as pgx does. It checks only the database and labor blocks; aveloxis-cve doctor validates the whole configuration.

  • Every session is read only (default_transaction_read_only=on), in UTC, with client encoding UTF-8 whatever PGCLIENTENCODING it inherits.

  • It reads psql's output byte for byte, so a sample path keeps any carriage return or non-ASCII letter and reaches the queries unchanged. In a SQL_ASCII database, bytes that are not UTF-8 fail the query on the server; when they are in the sample :file_path or :cve_id, the run stops at that repository. It refuses a filesystem encoding that is not UTF-8, which would send psql the path in another encoding; run it as python3 -X utf8 run.py there. That happens only under a non-UTF-8 locale (such as en_US.ISO8859-1), with Python 3.14 or older or PYTHONUTF8=0, and not on macOS, whose filesystem encoding is always UTF-8. For the C locale it happens only with PYTHONUTF8=0 and when Python does not coerce the locale to C.UTF-8 (LC_ALL=C, PYTHONCOERCECLOCALE=0, or no C.UTF-8 locale installed). Its own files are read and written as UTF-8 under any locale.

  • For each repository it picks a sample :cve_id (the CVE fixed on the most branches) and :file_path (the most complex file at the latest window end of the current default branch's series).

  • Each result keeps its first 80 lines; set MAX_LINES for more, and QUERY_TIMEOUT (default 600s) for the statement timeout.

  • A query that fails is marked ERROR in the report, the run goes on, and run.py then exits with status 1, listing the failed queries.

What to keep in mind

These points apply to every query. Each is explained in full in ../data.md.

Time.

  • landed_at (branch_commits) is when a change reached a branch. It is the committer time of the first-parent commit that brought it in, made monotonic and capped for implausible clocks. Use it for anything "per branch".

  • committed_at and author_at (commits) are the raw git times, which can predate landing by months, as a cherry-pick keeps its original author time.

Membership is full. Every branch row holds its whole reachable history, not only the commits unique to it. A branch cut from the default branch after a fix contains that fix, with the landing time it had on the shared history. CVE-04 separates such inherited fixes from real backports.

Archived refs.

  • A branch deleted or force-pushed upstream keeps a row with archive_reason set, deleted or forced. Its branch_name is the original name, and its ref_name is refs/cve-archive/....

  • Include archived refs for history, such as fixes on end-of-life branches. Exclude them (archive_reason IS NULL) for "branches that exist now".

What counts as a fix.

  • A commit is a fix when its message names the CVE, when it is a resolved cherry-pick of a commit that does, or when it has the same patch-id as one (a manual backport).

  • A commit that undoes a fix (a revert naming the CVE) is not a fix.

  • The views do not tell whether a fix is still in effect on a branch; CVE-12 does.

  • GHSA ids are recorded but never produce fixes (CVE-14).

  • A commit naming a CVE is often a dependency upgrade that fixes a library's vulnerability, not the project's own. In a large project these can be most of the "fixes". CVE-16 classifies each CVE by the paths its fix touched, and CVE-17 counts the classes.

  • The CVE ids come only from commit messages: aveloxis-cve consults no advisory database (NVD, OSV, GitHub advisories).

  • A fix that names no id and is no copy of one that does is invisible.

A fix is not a vulnerability. aveloxis-cve knows where fixes are, not where the vulnerable code is. A branch without a fix may never have had the bug.

Windows.

  • A window is (window_start, window_end] on the default branch. The first window of a series is closed, [start, end].

  • Windows overlap (60 days, a month apart), so activity counts repeat across consecutive windows: never sum them.

  • Snapshot totals are points in time and compare cleanly.

  • Use the current series only: one window_days/step_months configuration, the current default branch, NOT needs_recompute (complexity.md).

Measurement.

  • Snapshots count the files scc's own checkout walk reaches, as Aveloxis's repo_labor does.

  • scc complexity approximates cyclomatic complexity per language. Compare it within a language and within one scc version (DQ-06).

  • Generated and vendored files (parsers, protocol stubs, bundled JavaScript) are counted like hand-written code and can dominate the totals. LAB-13 separates them, and LAB-14 shows which files the patterns catch.

Identity.

  • Authors resolve to Aveloxis contributors (cntrb_id) only from Aveloxis's own data, never by guessing.

  • An unresolved author is counted by lowercased email, so one person can count twice. DQ-03 shows the coverage.

Adding a query

  1. Add it under a ### <ID> <title> heading in the right file, with its question, one sql block and how to read the result.

  2. Use only the variables above. Read only.

  3. If the seed has no rows for it, or lacks a case that would show the query wrong, extend internal/db/testdata/analysis_seed.sql. Give the query a total ORDER BY (and array_agg(... ORDER BY ...)), so its result is deterministic.

  4. Record its result with AVELOXIS_CVE_TEST_DB=... go test ./internal/db -run TestAnalysisQueriesRun -update, and check the new section of internal/db/testdata/analysis_golden.txt by hand against the seed. The golden result is only as right as that check.

  5. Add it to the question map above.