Schema diagram

This page draws the relational model of the aveloxis_cve schema (migrations 0001-0019).

• Solid lines are declared foreign keys.

• Dotted lines are joins by value with no foreign key. Most of them carry repo_id, which is Aveloxis's repos.repo_id, copied by value so that nothing in aveloxis_cve depends on an Aveloxis object.

Every column is described in data.md. The analysis queries that use these joins are in analysis/. TestSchemaDiagramMatchesSchema (internal/db) fails when a table, column, foreign key or view dependency of the migrated schema is missing here.

Overview

Entities and relationships only. The attribute diagrams follow.

        erDiagram
    branch_repos ||--o{ branch_commits : "branch_id"
    commits ||--o{ branch_commits : "commit_id"
    commits ||--o{ branch_commits : "landed_via"
    commits ||--o{ commit_files : "commit_id"
    commits ||--o{ commit_facade_files : "commit_id"
    commits ||--o{ commit_cherry_picks : "commit_id (the copy)"
    commits ||--o{ commit_cherry_picks : "picked_from_id (the source)"
    commits ||--o{ commit_reverts : "commit_id (the revert)"
    commits ||--o{ commit_reverts : "reverted_id"
    commits ||--o{ commit_cve_undoes : "commit_id"
    repo_labor_windows ||--o{ repo_labor_rolling : "window_id"
    repo_labor_windows ||--o{ repo_labor_rolling_changed : "window_id"
    scc_blob_cache ||..o{ repo_labor_rolling : "blob_sha + file_name (no FK)"
    scc_blob_cache ||..o{ repo_labor_rolling_changed : "start_blob_sha, end_blob_sha (no FK)"
    labor_recompute_requests ||..o{ repo_labor_windows : "repo_id + window_days + step_months (no FK)"
    tracked_repos ||..o{ branch_repos : "repo_id (no FK)"
    tracked_repos ||..o{ commits : "repo_id (no FK)"
    tracked_repos ||..o{ repo_labor_windows : "repo_id (no FK)"
    tracked_repos ||..o{ labor_recompute_requests : "repo_id (no FK)"
    branch_repos ||..o{ repo_labor_windows : "repo_id + branch_name, default branch (no FK)"
    commits ||..o{ repo_labor_windows : "commit_hash = start_sha, end_sha (no FK)"
    commit_files }o..o{ repo_labor_rolling_changed : "file_path, window activity (no FK)"
    

The model has three parts:

• Commits and branches. commits holds every commit reachable from any branch, live or archived, once per repository. branch_repos holds each live branch and archived tip. branch_commits is full membership: one row per branch and commit, with when the change landed. commit_files and commit_facade_files hold per-file counts.

• CVE links. commit_cherry_picks and commit_reverts are resolved links between commits of one repository. commit_cve_undoes lists commits that undo a CVE fix. The CVE ids themselves are an array column, commits.cve_ids. The views on top match fixes and their propagation (see Views).

• Rolling labor. repo_labor_windows holds a window of the default branch. repo_labor_rolling is its end snapshot, file by file. repo_labor_rolling_changed covers the files changed in it. scc_blob_cache is shared by every repository and keyed by content, so it has no repo_id.

Commits, branches and CVE links

        erDiagram
    commits {
        bigint commit_id PK
        bigint repo_id UK "Aveloxis repo_id, by value"
        text commit_hash UK
        text[] parent_hashes
        text author_name
        text author_email
        timestamptz author_at
        text committer_name
        text committer_email
        timestamptz committed_at
        text subject
        text message
        integer files_changed
        integer lines_added
        integer lines_removed
        uuid cntrb_id "Aveloxis contributor, by value; nullable"
        text cntrb_resolution "nullable"
        text patch_id "git patch-id --stable; nullable"
        boolean patch_id_checked
        text[] cherry_picked_from
        text[] cve_ids "CVE ids named in the message"
        text[] ghsa_ids
        timestamptz ingested_at
        text tool_version
        boolean dates_invalid
        boolean cntrb_checked
        boolean is_revert
        boolean cherry_picks_checked
        text[] reverts_commits
        boolean reverts_checked
        boolean facade_checked
    }
    commit_files {
        bigint commit_id PK, FK
        text file_path PK
        integer lines_added "nullable"
        integer lines_removed "nullable"
    }
    commit_facade_files {
        bigint commit_id PK, FK
        text file_name PK
        integer lines_added
        integer lines_removed
        integer whitespace
    }
    commit_cherry_picks {
        bigint commit_id PK, FK
        bigint picked_from_id PK, FK "the source commit"
    }
    commit_reverts {
        bigint commit_id PK, FK
        bigint reverted_id PK, FK "the reverted commit"
    }
    commit_cve_undoes {
        bigint commit_id PK, FK
        text cve_id PK "CVE whose fix this commit undoes"
    }
    branch_repos {
        integer branch_id PK
        bigint repo_id UK "Aveloxis repo_id, by value"
        text ref_name UK
        text branch_name
        text head_sha
        timestamptz head_committed_at "nullable"
        boolean is_default "the forge's default branch"
        text archive_reason "NULL live; deleted or forced; nullable"
        timestamptz archived_at "nullable"
        text merge_base_with_default "nullable"
        integer ahead_of_default "nullable"
        integer behind_default "nullable"
        text membership_head_sha "nullable"
        integer commit_count "nullable"
        timestamptz first_seen_at
        timestamptz last_seen_at
        text tool_version
        text stats_default_sha "nullable"
        text stats_head_sha "nullable"
        timestamptz labor_dirty_since "nullable"
        integer membership_scheme
    }
    branch_commits {
        integer branch_id PK, FK
        bigint commit_id PK, FK
        boolean on_first_parent
        bigint landed_via FK "first-parent commit that brought it in"
        timestamptz landed_at "when the change reached the branch"
        timestamptz first_observed_at
    }
    branch_repos ||--o{ branch_commits : "branch_id"
    commits ||--o{ branch_commits : "commit_id"
    commits ||--o{ branch_commits : "landed_via"
    commits ||--o{ commit_files : "commit_id"
    commits ||--o{ commit_facade_files : "commit_id"
    commits ||--o{ commit_cherry_picks : "commit_id (the copy)"
    commits ||--o{ commit_cherry_picks : "picked_from_id (the source)"
    commits ||--o{ commit_reverts : "commit_id (the revert)"
    commits ||--o{ commit_reverts : "reverted_id"
    commits ||--o{ commit_cve_undoes : "commit_id"
    

Notes:

• commits is unique on (repo_id, commit_hash), and branch_repos on (repo_id, ref_name) (the UK columns).

• branch_commits.landed_via names the first-parent commit that made the row's commit reachable. For a commit merged in, that is the merge.

• Cherry-pick and revert links never cross repositories. Both of a link's ends are commits of the same repo_id.

Rolling labor

        erDiagram
    repo_labor_windows {
        bigint window_id PK
        bigint repo_id UK "Aveloxis repo_id, by value"
        text branch_name UK "the default branch"
        integer window_days UK
        integer step_months UK
        timestamptz window_start
        timestamptz window_end UK
        text start_sha "nullable"
        text end_sha "snapshot commit"
        boolean same_as_previous
        integer commits_in_window
        integer files_changed
        integer total_files
        bigint total_lines
        bigint code_lines
        bigint comment_lines
        bigint blank_lines
        bigint code_complexity
        text scc_version
        timestamptz computed_at
        text tool_version
        text count_scheme
        boolean needs_recompute "stale until replaced"
    }
    repo_labor_rolling {
        bigint window_id PK, FK
        text file_path PK
        text file_name
        text blob_sha "scc_blob_cache key, with file_name"
        text programming_language
        integer total_lines
        integer code_lines
        integer comment_lines
        integer blank_lines
        integer code_complexity
        text tool_source
    }
    repo_labor_rolling_changed {
        bigint window_id PK, FK
        text file_path PK
        text change_type "A D M N X"
        text start_blob_sha "nullable"
        text end_blob_sha "nullable"
        text programming_language "nullable"
        integer start_code_lines "nullable"
        integer end_code_lines "nullable"
        integer start_code_complexity "nullable"
        integer end_code_complexity "nullable"
        integer delta_code_lines "nullable"
        integer delta_code_complexity "nullable"
        integer lines_added
        integer lines_removed
        integer commits
        integer distinct_contributors
    }
    scc_blob_cache {
        text blob_sha PK
        text file_name PK
        text scc_version PK
        boolean counted
        text programming_language "nullable"
        integer total_lines "nullable"
        integer code_lines "nullable"
        integer comment_lines "nullable"
        integer blank_lines "nullable"
        integer code_complexity "nullable"
    }
    labor_recompute_requests {
        bigint repo_id PK "Aveloxis repo_id, by value"
        integer window_days PK
        integer step_months PK
        bigint requested
        bigint applied
    }
    repo_labor_windows ||--o{ repo_labor_rolling : "window_id"
    repo_labor_windows ||--o{ repo_labor_rolling_changed : "window_id"
    scc_blob_cache ||..o{ repo_labor_rolling : "blob_sha + file_name (no FK)"
    scc_blob_cache ||..o{ repo_labor_rolling_changed : "start_blob_sha, end_blob_sha (no FK)"
    labor_recompute_requests ||..o{ repo_labor_windows : "repo_id + window_days + step_months (no FK)"
    

Notes:

• repo_labor_windows is unique on (repo_id, branch_name, window_days, step_months, window_end).

• start_sha and end_sha hold commit hashes, joined to commits.commit_hash (with repo_id) by value.

• scc_blob_cache is keyed by (blob_sha, file_name, scc_version). Snapshot rows match it on blob and file name. The version is the window's scc_version plus a presentation suffix (+isolated1).

Operations

        erDiagram
    tracked_repos {
        bigint repo_id PK "Aveloxis repos.repo_id, by value"
        text repo_git
        boolean enabled
        timestamptz added_at
        timestamptz last_sync_at "nullable"
        text last_sync_error "nullable"
        timestamptz last_labor_at "nullable"
        text last_labor_error "nullable"
        text tool_version
    }
    stop_requests {
        bigint request_id PK
        timestamptz requested_at
        text tool_version
    }
    schema_migrations {
        integer version PK
        text name
        timestamptz applied_at
        text tool_version
    }
    

• tracked_repos is the set of repositories being analysed. Other tables reach it through repo_id, by value.

• stop_requests and schema_migrations stand alone.

Views

The four views are defined in aveloxis_cve over its own tables only. An arrow points from a table or view to a view that reads it.

        flowchart LR
    commits[(commits)]
    commit_files[(commit_files)]
    commit_cherry_picks[(commit_cherry_picks)]
    commit_cve_undoes[(commit_cve_undoes)]
    branch_commits[(branch_commits)]
    branch_repos[(branch_repos)]
    cve_fix_commits[/cve_fix_commits/]
    cve_fix_propagation[/cve_fix_propagation/]
    commit_equivalence[/commit_equivalence/]
    branch_commit_files[/branch_commit_files/]
    commits --> cve_fix_commits
    commit_cherry_picks --> cve_fix_commits
    commit_cve_undoes --> cve_fix_commits
    cve_fix_commits --> cve_fix_propagation
    branch_commits --> cve_fix_propagation
    branch_repos --> cve_fix_propagation
    commits --> commit_equivalence
    branch_commits --> branch_commit_files
    branch_repos --> branch_commit_files
    commits --> branch_commit_files
    commit_files --> branch_commit_files
    

View	One row is
cve_fix_commits	a commit that fixes a CVE, and how it was matched (message, cherry_pick, patch_id)
cve_fix_propagation	the first landing of a CVE's fix on one branch, with its lag behind the first branch
commit_equivalence	a group of commits of one repository sharing a patch-id
branch_commit_files	a file changed by a commit on a branch

What comes from Aveloxis

Nothing in aveloxis_cve references an Aveloxis object: no foreign key, view, function or trigger crosses schemas (TestMigrationsNeverReferenceAveloxisSchemas). aveloxis-cve copies Aveloxis ids by value when it writes. The dotted arrows show where each copied value comes from.

        flowchart LR
    subgraph aveloxis["Aveloxis schemas (read only)"]
        repos["aveloxis_data.repos"]
        queue["aveloxis_ops.collection_queue"]
        acommits["aveloxis_data.commits"]
        contributors["aveloxis_data.contributors<br/>contributors_aliases<br/>contributor_identities"]
    end
    subgraph cve["aveloxis_cve"]
        tracked_repos
        commits
        windows["repo_labor_windows"]
    end
    git[("own git clones")]
    scc[["scc"]]
    repos -. "repo_id, repo_git" .-> tracked_repos
    queue -. "readiness" .-> tracked_repos
    repos -. "clone URL" .-> git
    git --> commits
    acommits -. "author of the same commit" .-> commits
    contributors -. "cntrb_id by email" .-> commits
    git --> windows
    scc --> windows