Data quality queries¶

Run these before trusting an analysis. They show whether each repository is synced and measured, how complete the identity, link and patch-id data is, and whether a labor series is whole and consistent.

Read the analysis guide first. Queries without :repo_id cover every repository. Each is tested against a seeded schema by TestAnalysisQueriesRun (internal/db).

DQ-01 Tracked repository status¶

Question: Is every tracked repository synced and measured, and what failed last?

Reads Aveloxis: aveloxis_data.repos for the repository name.

SELECT t.repo_id, ar.repo_owner || '/' || ar.repo_name AS repo, t.enabled,
       t.last_sync_at, t.last_sync_error,
       t.last_labor_at, t.last_labor_error,
       (SELECT count(*) FROM aveloxis_cve.commits c WHERE c.repo_id = t.repo_id)               AS commits,
       (SELECT max(window_end) FROM aveloxis_cve.repo_labor_windows w
        WHERE w.repo_id = t.repo_id AND NOT w.needs_recompute
          AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos b
                               WHERE b.repo_id = t.repo_id AND b.is_default AND b.archive_reason IS NULL
                               ORDER BY b.last_seen_at DESC, b.branch_id DESC LIMIT 1)) AS latest_window_end
FROM aveloxis_cve.tracked_repos t
LEFT JOIN aveloxis_data.repos ar ON ar.repo_id = t.repo_id
ORDER BY t.repo_id;

Reading the result:

last_*_at is the last success.
last_*_error describes the latest recorded attempt, and is NULL after a success.
A repository skipped as busy records nothing.

DQ-02 Branch inventory¶

Question: How many live and archived branches does each repository have, and is every branch's membership complete and current?

SELECT b.repo_id,
       count(*) FILTER (WHERE b.archive_reason IS NULL)       AS live_branches,
       count(*) FILTER (WHERE b.archive_reason = 'deleted')   AS archived_deleted,
       count(*) FILTER (WHERE b.archive_reason = 'forced')    AS archived_forced,
       (array_agg(b.branch_name ORDER BY b.last_seen_at DESC, b.branch_id DESC)
          FILTER (WHERE b.is_default AND b.archive_reason IS NULL))[1]           AS default_branch,
       count(*) FILTER (WHERE b.is_default AND b.archive_reason IS NULL)           AS default_rows,
       count(*) FILTER (WHERE b.membership_head_sha IS DISTINCT FROM b.head_sha)  AS membership_behind_tip,
       count(*) FILTER (WHERE b.membership_scheme <> 3)                            AS old_membership_scheme,
       count(*) FILTER (WHERE b.is_default AND b.labor_dirty_since IS NOT NULL)    AS default_dirty_for_labor
FROM aveloxis_cve.branch_repos b
GROUP BY b.repo_id
ORDER BY b.repo_id;

Reading the result:

default_branch is the row every query takes as the default: the live default row seen last.
default_rows should be 1. It is 2 only after a sync that failed part-way while the forge's default branch changed.
membership_behind_tip and old_membership_scheme should be 0 after a successful sync.
default_dirty_for_labor is 1 until the next labor run recomputes the affected windows.

DQ-03 Identity resolution coverage¶

Question: What share of commit authors resolve to an Aveloxis contributor, and by which step?

SELECT c.repo_id,
       count(*)                                                       AS commits,
       count(*) FILTER (WHERE NOT c.cntrb_checked)                    AS not_yet_checked,
       round(100.0 * count(c.cntrb_id) / count(*), 1)                 AS resolved_pct,
       count(*) FILTER (WHERE c.cntrb_resolution = 'aveloxis_commit') AS by_aveloxis_commit,
       count(*) FILTER (WHERE c.cntrb_resolution = 'email')           AS by_email,
       count(*) FILTER (WHERE c.cntrb_resolution = 'alias')           AS by_alias,
       count(*) FILTER (WHERE c.cntrb_resolution = 'identity')        AS by_identity,
       count(DISTINCT lower(c.author_email)) FILTER (WHERE c.cntrb_id IS NULL) AS unresolved_emails
FROM aveloxis_cve.commits c
GROUP BY c.repo_id
ORDER BY c.repo_id;

Reading the result: unresolved authors stay NULL rather than being guessed. That covers unknown and ambiguous emails, and contributors merged in Aveloxis. aveloxis-cve resolve retries them as Aveloxis's contributor data improves.

DQ-04 Pending cherry-pick and revert links¶

Question: Which commits name a cherry-pick source or a reverted commit that sync keeps retrying, and how many of those references did resolve?

SELECT c.repo_id, c.commit_hash, c.subject,
       CASE WHEN NOT c.cherry_picks_checked THEN c.cherry_picked_from END AS pending_cherry_pick_refs,
       (SELECT count(*) FROM aveloxis_cve.commit_cherry_picks l WHERE l.commit_id = c.commit_id) AS cherry_pick_links,
       CASE WHEN NOT c.reverts_checked THEN c.reverts_commits END         AS pending_revert_refs,
       (SELECT count(*) FROM aveloxis_cve.commit_reverts l WHERE l.commit_id = c.commit_id)      AS revert_links
FROM aveloxis_cve.commits c
WHERE NOT c.cherry_picks_checked OR NOT c.reverts_checked
ORDER BY c.repo_id, c.committed_at, c.commit_hash;

Reading the result:

A commit stays pending while any SHA it names is abbreviated (shorter than 40 characters), ambiguous, or names a commit not in the clone (another repository, or an unfetched ref). Sync retries it every run.
An abbreviated SHA that matches exactly one commit is linked anyway, so *_links can be non-zero while the commit is pending.
A reference with no link row cannot be followed by the cherry_pick match of cve_fix_commits, or by the undo rules.

DQ-05 Patch-id coverage¶

Question: How many non-merge commits still lack a computed patch-id?

SELECT c.repo_id,
       count(*) FILTER (WHERE cardinality(c.parent_hashes) <= 1)                          AS non_merge_commits,
       count(*) FILTER (WHERE cardinality(c.parent_hashes) <= 1 AND NOT c.patch_id_checked) AS patch_id_pending,
       count(*) FILTER (WHERE c.patch_id_checked AND c.patch_id IS NULL)                   AS empty_diffs
FROM aveloxis_cve.commits c
GROUP BY c.repo_id
ORDER BY c.repo_id;

Reading the result: pending patch-ids are computed late in each sync. Until then the patch_id match cannot find manual backports of those commits.

DQ-06 Labor series health¶

Question: For each repository and window configuration, is the series complete, current and measured consistently?

SELECT w.repo_id, w.branch_name, w.window_days, w.step_months,
       count(*)                                         AS windows,
       count(*) FILTER (WHERE w.needs_recompute)        AS stale_windows,
       min(w.window_end)::date                          AS first_end,
       max(w.window_end)::date                          AS last_end,
       array_agg(DISTINCT w.scc_version)                AS scc_versions,
       array_agg(DISTINCT w.count_scheme)               AS count_schemes,
       (SELECT count(*) FROM generate_series(min(w.window_end), max(w.window_end),
                                              make_interval(months => w.step_months)))
         - count(*) FILTER (WHERE NOT w.needs_recompute) AS missing_windows
FROM aveloxis_cve.repo_labor_windows w
GROUP BY w.repo_id, w.branch_name, w.window_days, w.step_months
ORDER BY w.repo_id, w.branch_name, w.window_days, w.step_months;

Reading the result:

stale_windows are replaced by the next labor run. Leave them out of analyses (every query here filters NOT needs_recompute).
More than one entry in scc_versions or count_schemes means a series measured inconsistently. labor --recompute remeasures it.
missing_windows is an estimate. Window ends step by calendar months from the first end, with the day clamped to shorter months, so a series whose first end falls on the 29th to 31st can be off by a little.
Rows for a configuration or branch name you no longer use are old series: labor keeps them and never updates them.

DQ-07 Files scc did not count¶

Question: How many cached file contents did scc not count, per scc version?

SELECT scc_version,
       count(*)                          AS cached_measurements,
       count(*) FILTER (WHERE NOT counted) AS not_counted,
       round(100.0 * count(*) FILTER (WHERE NOT counted) / count(*), 1) AS not_counted_pct
FROM aveloxis_cve.scc_blob_cache
GROUP BY scc_version
ORDER BY scc_version;

Reading the result:

Most uncounted entries are files scc does not recognise, such as binaries and extensionless files.
An entry scc failed on deterministically ("poison") is also stored as not counted. labor --retry-uncounted measures them again.

DQ-08 Pending recompute requests¶

Question: Which labor --recompute requests have not been applied yet?

SELECT r.repo_id, r.window_days, r.step_months, r.requested, r.applied,
       r.requested - r.applied AS pending
FROM aveloxis_cve.labor_recompute_requests r
WHERE r.requested > r.applied
ORDER BY r.repo_id, r.window_days, r.step_months;

Reading the result: a request is applied by the next labor run of that repository and configuration that gets past its checks. Until then, the windows it covers still hold the old values.

DQ-09 Latest window against Aveloxis's repo_labor¶

Question: Does the latest window's snapshot agree with Aveloxis's own scc snapshot of the same repository?

Reads Aveloxis: aveloxis_data.repo_labor.

WITH latest AS (
    SELECT w.window_id, w.end_sha, w.window_end
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
    ORDER BY w.window_end DESC
    LIMIT 1
), ours AS (
    SELECT r.file_path, r.code_lines, r.code_complexity
    FROM aveloxis_cve.repo_labor_rolling r
    WHERE r.window_id = (SELECT window_id FROM latest)
), theirs AS (
    SELECT rl.file_path, rl.code_lines, rl.code_complexity
    FROM aveloxis_data.repo_labor rl
    WHERE rl.repo_id = :repo_id
)
SELECT (SELECT window_end::date FROM latest)                              AS window_end,
       (SELECT left(end_sha, 12) FROM latest)                             AS end_sha,
       (SELECT max(rl_analysis_date) FROM aveloxis_data.repo_labor WHERE repo_id = :repo_id) AS aveloxis_analysis_date,
       count(o.file_path)                                                 AS our_files,
       count(t.file_path)                                                 AS aveloxis_files,
       count(*) FILTER (WHERE o.file_path IS NOT NULL AND t.file_path IS NOT NULL
                          AND o.code_lines = t.code_lines
                          AND o.code_complexity = t.code_complexity)      AS identical_files,
       COALESCE(sum(o.code_complexity), 0)                                AS our_complexity,
       COALESCE(sum(t.code_complexity), 0)                                AS aveloxis_complexity
FROM ours o
FULL JOIN theirs t ON t.file_path = o.file_path;

Reading the result:

repo_labor holds only Aveloxis's latest snapshot, taken at its own clone date, and records no commit. The two agree only when the latest window's end_sha is the commit Aveloxis measured.
Otherwise the gap is the code that changed in between. Pick a window ending at Aveloxis's clone date for a strict comparison.