Data usage and provenance

This document describes every table, view and column in the aveloxis_cve schema: what one row is, where each value comes from, which command writes it, and when it changes or is deleted. It also covers what aveloxis-cve reads from Aveloxis, the personal data it stores, and how to remove that data.

It describes the schema as of migrations 0001-0019, drawn in schema.md. Tested queries over it are in analysis/. TestDataDocCoversEverySchemaColumn (internal/db) fails when a table, view or column of the migrated schema is missing here, so a migration that adds one must also document it. Code is cited by file and function, not line number. Unless a section says otherwise, paths are relative to the repository root.

Where the data comes from

aveloxis-cve has five sources. It makes no forge API calls.

Source	What is taken	Through
Its own git clones	Commits (hashes, parents, author and committer names, emails and times, messages), per-file line counts, refs (branches), trees and blobs	git run by internal/gitx on <clone_dir>/repo_<id>.git, cloned and fetched from the repository URL Aveloxis has (aveloxis_data.repos.repo_git). Aveloxis's own clone directory is never read.
The Aveloxis database, read-only	Which repositories exist and are collected; the contributor Aveloxis attached to a commit; contributor emails, aliases and identities	internal/source only (enforced by internal/archtest). The role from sql/roles.sql can read, never write, Aveloxis schemas.
scc	Per-file language, line counts and complexity	internal/labor/scc.go, run on blob contents written to a temporary directory
The operator	Which repositories to track, enable or disable; recompute and retry requests; configuration (window size and step, scc options)	CLI commands (internal/cli) and the config file (docs/configuration.md)
The program and the database server	Timestamps (now()), tool and scc versions, bookkeeping flags	Constants and the database clock

Aveloxis objects read

These are exactly the objects and columns in source.RequiredColumns (internal/source/source.go); doctor fails when one is missing or unreadable. repos.repo_name and repos.repo_owner are selected but not used.

Aveloxis object	Columns	Used for
aveloxis_ops.schema_meta	schema_version	doctor reports the Aveloxis schema version
aveloxis_ops.collection_queue	repo_id, last_collected	Readiness: a repository is analysed only after Aveloxis has completed a collection of it
aveloxis_data.repos	repo_id, repo_group_id, repo_git, repo_name, repo_owner, repo_gone_at	repos add selection (by id, URL, group or query); the clone URL, re-read on every sync; readiness (not gone)
aveloxis_data.repo_groups	repo_group_id, rg_name	Checked by doctor only (Probe); no query reads it. repos add --repo-group-id filters aveloxis_data.repos.repo_group_id.
aveloxis_data.commits	repo_id, cmt_commit_hash, cmt_ght_author_id	Readiness (has commits); identity step 1 (the contributor Aveloxis attached to the same commit)
aveloxis_data.contributors	cntrb_id, cntrb_email, cntrb_deleted	Identity step 2, and the not-merged filter of every identity step
aveloxis_data.contributors_aliases	cntrb_id, alias_email, cntrb_active	Identity step 3
aveloxis_data.contributor_identities	cntrb_id, email	Identity step 4

doctor warns when Aveloxis lacks the requested index on lower(cntrb_email), lower(alias_email) or lower(email) (source.MissingEmailIndexes; a failed check is a warning, never read as missing): without them, every author resolution reads those tables whole. Only Aveloxis creates indexes in its schemas: Aveloxis 0.29.141 and later create all three (its migrate); never create them by hand or from aveloxis-cve. doctor then checks the role's privileges. It warns if the role can modify any Aveloxis table (aveloxis-cve never does, but it should run as the read-only role), and then skips the secret-table check. Only when the role can modify no Aveloxis table does doctor check secret tables: it fails if the role can read one (aveloxis_data.chaoss_user, or any aveloxis_ops table other than the two above).

Nothing in aveloxis_cve refers to an Aveloxis object. There are no foreign keys, views, functions or triggers across schemas (TestMigrationsNeverReferenceAveloxisSchemas). Aveloxis ids (repo_id, cntrb_id) are copied by value. They can therefore point at an Aveloxis row that has since changed or been deleted.

What writes what

Command	Writes
init-db	Creates the schema; schema_migrations, and the objects of each pending migration
migrate	schema_migrations, and the objects of each pending migration (only on an installed schema)
repos add, repos enable, repos disable	tracked_repos
sync (and run's first step)	tracked_repos (sync outcome), branch_repos, branch_commits, commits, commit_files, commit_facade_files, commit_cherry_picks, commit_reverts, commit_cve_undoes, and identity columns of commits
resolve (and run's second step)	Identity columns of commits; branch_repos.labor_dirty_since
labor (and run's last step)	repo_labor_windows, repo_labor_rolling, repo_labor_rolling_changed, scc_blob_cache, labor_recompute_requests.applied, tracked_repos (labor outcome), branch_repos.labor_dirty_since
labor --recompute, labor --retry-uncounted	labor_recompute_requests.requested; --retry-uncounted also deletes the current scc version's uncounted rows of scc_blob_cache before its run
stop	stop_requests
cves, status, doctor, repos list, version	Nothing (read only)

Writes made under a repository lock or the labor gate are fenced: each proves, in its own transaction, that the lock is still held (internal/store/fence.go). The exception is the recompute request, which --retry-uncounted records unfenced while holding the gate. stop takes no lock and writes unfenced. A command writes nothing more under a lock it has lost. A labor run that lost only the gate still records its outcome, which is fenced by the repository lock alone.

Personal data

aveloxis-cve stores personal data taken from git and linked to Aveloxis's contributor records:

• commits.author_name, author_email, committer_name, committer_email: copied as git records them (no .mailmap, not lowercased).

• commits.message and subject: free text, which can name people or contain email addresses.

• branch_repos.ref_name and branch_name: branch names, which often carry user names (jdoe/fix).

• commits.cntrb_id: a link to an Aveloxis contributor, which joins these rows to whatever Aveloxis holds about that person.

• repo_labor_rolling_changed.distinct_contributors: a count, not identities.

• File paths and names (commit_files.file_path, commit_facade_files.file_name, repo_labor_rolling.file_path, repo_labor_rolling_changed.file_path, scc_blob_cache.file_name), which occasionally contain a person's name.

• Error text (tracked_repos.last_sync_error, last_labor_error), which can quote branch names and paths.

• tracked_repos.repo_git: the repository URL as Aveloxis has it. If it carries credentials, they are stored here too. Error text (last_sync_error) has URL credentials redacted (procx.Redact, procx.RedactURL).

The on-disk clones under clone_dir hold the full history of each repository, including the same names, emails and messages.

Retention and removal

aveloxis-cve never deletes a commit, branch membership of an archived branch, or a tracked repository. repos disable drops a repository from the default selection and keeps its data. A disabled repository named with --repo-id is still processed. Branches deleted or force-pushed upstream are kept, as archive refs and archived branch_repos rows, because CVE history on end-of-life branches is the point. Rows are removed only as each section below describes (for example, a live branch row whose ref is gone, or windows a recompute replaces).

To remove everything:

DROP SCHEMA aveloxis_cve CASCADE;   -- touches no Aveloxis object

Then delete clone_dir.

To remove one repository, run the following with no aveloxis-cve command running, with :repo_id set to the repository's id (psql -v repo_id=123 -f remove.sql, or the script on stdin; psql does not substitute variables in -c). Foreign keys cascade to the child tables. Then delete <clone_dir>/repo_<id>.git, its lock file <clone_dir>/repo_<id>.git.lock, and any <clone_dir>/repo_<id>.git.tmp-* a killed first clone left.

BEGIN;
DELETE FROM aveloxis_cve.branch_repos WHERE repo_id = :repo_id;        -- branch_commits
DELETE FROM aveloxis_cve.commits WHERE repo_id = :repo_id;             -- commit_files, commit_facade_files, links, undoes
DELETE FROM aveloxis_cve.repo_labor_windows WHERE repo_id = :repo_id;  -- repo_labor_rolling, repo_labor_rolling_changed
DELETE FROM aveloxis_cve.labor_recompute_requests WHERE repo_id = :repo_id;
DELETE FROM aveloxis_cve.tracked_repos WHERE repo_id = :repo_id;
COMMIT;

scc_blob_cache is shared by all repositories and keyed by blob hash, file base name and scc cache version. It holds measurements only, never file contents or paths. The script above leaves a removed repository's cache rows (blob ids and base names), because other repositories may share them.

Contents

Object	One row is	Section
tracked_repos	a repository the operator tracks	Tracked repositories, migrations and rolling labor
schema_migrations	an applied migration	same
commits	a commit of a repository	Commits, per-file counts and author identity
commit_files	a file a commit changed	same
commit_facade_files	a file row as Aveloxis's facade would record it	same
branch_repos	a live branch or archived tip	Branches, commit links and CVE views
branch_commits	a commit's membership of a branch	same
commit_cherry_picks	a resolved cherry-pick trailer	same
commit_reverts	a resolved revert reference	same
commit_cve_undoes	a CVE whose fix a commit undoes	same
branch_commit_files (view)	a file changed by a commit on a branch	same
commit_equivalence (view)	a group of commits with the same patch-id	same
cve_fix_commits (view)	a commit that fixes a CVE, and how it was matched	same
cve_fix_propagation (view)	when a CVE's fix first landed on a branch	same
labor_recompute_requests	a recompute request for one window configuration	Tracked repositories, migrations and rolling labor
stop_requests	a stop request (aveloxis-cve stop)	same
repo_labor_windows	a rolling window of a repository's default branch	same
repo_labor_rolling	a file in a window's end snapshot	same
repo_labor_rolling_changed	a file changed within a window	same
scc_blob_cache	an scc measurement of a file content under a name	same

Column tables give: column, type, nullability, source, and how the value is derived and written.

Commits, per-file counts and author identity

Every row in these three tables is written by sync (on its own or as the first step of run). The identity columns of commits are also written by resolve (on its own or as the second step of run). labor only reads these tables. No code path deletes a commit row, a commit_files row or a commit_facade_files row. repos disable keeps the data, and the only commands are repos add/list/enable/disable, sync, run, resolve, labor, cves, status, stop, doctor, init-db, migrate and version. Every table that references commits does so with ON DELETE CASCADE, so deleting a commit by hand also deletes its commit_files, commit_facade_files and commit_cve_undoes rows, the link rows naming it on either side, and every branch_commits row that has it as commit_id or as landed_via (other commits' membership too). Since 0.4.22 every write is fenced: it runs in a transaction that proves the per-repository advisory lock is still held (internal/store/fence.go; internal/pipeline/sync.go). A sync or resolve that has lost its lock writes nothing more.

Which commits are ingested. aveloxis-cve keeps its own bare clone, <clone_dir>/repo_<id>.git (internal/pipeline/sync.go). It fetches +refs/heads/* into a private refs/remotes/origin/* staging namespace (--prune --no-tags; only that namespace is pruned), then updates refs/heads/* itself, archiving deleted and force-pushed tips (Runner.Sync, internal/gitx/clone.go). It lists refs/heads/* plus its own refs/cve-archive/* tips (the tips of deleted and force-pushed branches; internal/gitx/clone.go). Each sync streams every commit reachable from those tips and not from any tip recorded by the previous sync (internal/pipeline/sync.go). Tags and other refs are not followed. A repository is synced only after source.CheckReadiness reports it ready: it exists in aveloxis_data.repos and is not gone, aveloxis_ops.collection_queue.last_collected is set, and at least one aveloxis_data.commits row exists (internal/source/source.go, called at internal/pipeline/sync.go).

The order of the steps inside one sync is listed under Branches, commit links and CVE views.

Aveloxis data read for these tables

All reads go through internal/source and are read-only. internal/archtest enforces this. source.RequiredColumns lists every column doctor/Probe checks exists; all but repo_groups' are read (internal/source/source.go).

Aveloxis object	Columns read	Used for	Where
aveloxis_data.commits	repo_id, cmt_commit_hash, cmt_ght_author_id	Readiness: the repository has at least one row. Identity step 1: the contributor Aveloxis itself attached to this commit hash in this repository.	internal/source/source.go; internal/source/identity.go
aveloxis_data.contributors	cntrb_id, cntrb_email, cntrb_deleted	Identity step 2 (lower(cntrb_email) equals the key). Every step also joins it to keep only live contributors (COALESCE(cntrb_deleted,0) = 0).	internal/source/identity.go
aveloxis_data.contributors_aliases	cntrb_id, alias_email, cntrb_active	Identity step 3: lower(alias_email) equals the key, and the alias is active (COALESCE(cntrb_active,1) = 1, so NULL counts as active).	internal/source/identity.go
aveloxis_data.contributor_identities	cntrb_id, email	Identity step 4: lower(email) equals the key.	internal/source/identity.go

commits.repo_id is Aveloxis's aveloxis_data.repos.repo_id, stored by value with no foreign key. It reaches commits through tracked_repos.repo_id.

commit_facade_files reproduces, for every commit on every branch, the per-file values that Aveloxis's facade stores in aveloxis_data.commits (cmt_filename, cmt_added, cmt_removed, cmt_whitespace). It is computed from aveloxis-cve's own clone and never copied from Aveloxis.

commits

One row per git commit object reachable from a live branch or an archived tip in aveloxis-cve's clone of one repository. The grain is (repo_id, commit_hash). The primary key is commit_id (identity); commits_repo_hash_key is UNIQUE (repo_id, commit_hash). CHECK constraints:

• cntrb_resolution IN ('aveloxis_commit','email','alias','identity');

• commits_resolution_consistent: (cntrb_id IS NULL) = (cntrb_resolution IS NULL).

Indexes:

• (repo_id, committed_at);

• (repo_id, patch_id) WHERE patch_id IS NOT NULL;

• GIN indexes on cve_ids and ghsa_ids;

• partial indexes on (repo_id) WHERE NOT cherry_picks_checked, and WHERE NOT reverts_checked.

Lifecycle:

• Insert: sync inserts the row once, in Store.InsertCommits (internal/store/branches.go), with ON CONFLICT (repo_id, commit_hash) DO NOTHING. A commit seen again is never rewritten, so the git-derived columns, ingested_at and tool_version never change after insert. Migration backfills (0005, 0006, 0008, 0009) are the exception.

• Later updates: only the bookkeeping columns change afterwards:

  • the identity columns, in sync and resolve;

  • patch_id/patch_id_checked, in sync;

  • cherry_picks_checked/reverts_checked, in sync;

  • facade_checked, in sync.

• Deletes: rows are never deleted, even when the branch that held them is deleted or force-pushed. The archive refs keep those commits reachable.

Readers:

• sync:

  • CommitIDs and CommitIngestTimes, for membership (internal/store/branches.go);

  • link resolution and RefreshUndoes;

  • UnresolvedAuthors, PatchIDCandidates and FacadeCandidates.

• resolve: UnresolvedAuthors.

• labor:

  • DefaultBranchState reads committed_at and dates_invalid of each landing commit (internal/store/labor.go);

  • WindowActivity reads cntrb_id and author_email to count distinct contributors (internal/store/labor.go);

  • ChainLandings reads commit_hash (internal/store/labor.go).

• status: counts commits, and commits with a non-empty cve_ids (internal/store/status.go).

• cves: reads through the views cve_fix_commits, then cve_fix_propagation (internal/store/cves.go).

• Views: commit_equivalence (patch_id groups) and branch_commit_files also read this table.

git log command used for every git-derived column (internal/gitx/log.go, internal/gitx/runner.go): git -c core.quotepath=off -c log.showSignature=false -c color.ui=never log --stdin -z --no-renames --root --numstat --format=%x1e%H%x1f%P%x1f%at%x1f%ct%x1f%an%n%ae%n%cn%n%ce%n%B%x1f It runs with LC_ALL=C, GIT_TERMINAL_PROMPT=0 and the invoking user's other git configuration. The stream is parsed by parseCommitStream/parseHeader/parseNumstat (internal/gitx/log.go).

clean() (internal/store/branches.go) replaces each run of invalid UTF-8 bytes with one U+FFFD and each NUL with U+FFFD.

Column	Type	Null	Source	How it is derived / written
commit_id	bigint	NO	PostgreSQL identity	GENERATED ALWAYS AS IDENTITY (migration 0002). Assigned at insert. It is internal to aveloxis-cve, unrelated to any Aveloxis id, and not stable across databases. Conflicting inserts consume sequence values, so gaps are normal.
repo_id	bigint	NO	aveloxis_data.repos.repo_id, by value, through tracked_repos.repo_id	The repoID argument of InsertCommits (internal/store/branches.go). No FK. Never updated.
commit_hash	text	NO	git %H	The full object name as git prints it, lowercase hex. The parser rejects anything that is not hex or shorter than 40 characters, and that fails the sync (internal/gitx/log.go). It is stored unchanged. Never updated.
parent_hashes	text[]	NO	git %P	Space-separated parent hashes split with strings.Fields, in commit-object order (first parent first) (internal/gitx/log.go). A root commit stores {} (internal/store/branches.go). Never updated.
author_name	text	NO	git %an	The raw author ident name: no .mailmap (git's %aN would apply it), not trimmed, passed through clean() (internal/store/branches.go). Never updated.
author_email	text	NO	git %ae	The raw author email: not validated, not lowercased (it may be empty or not an email at all), passed through clean() (internal/store/branches.go). Identity resolution trims and lowercases it when it reads it. Labor's contributor count only lowercases it (lower(author_email), no trim), so " a@x" and "a@x" count as two unresolved contributors. Never updated.
author_at	timestamptz	NO	git %at (author time, Unix seconds)	Parsed as int64 and accepted only for years 1-9999 (internal/gitx/log.go). Stored as UTC; the original timezone offset is not kept. Invalid or out of range: the committer time stands in, or 1970-01-01 00:00:00 UTC if both are invalid, and dates_invalid is set. Never updated.
committer_name	text	NO	git %cn	As author_name, for the committer ident.
committer_email	text	NO	git %ce	As author_email, for the committer ident. Never used for identity resolution.
committed_at	timestamptz	NO	git %ct (committer time, Unix seconds)	As author_at. If the committer time is invalid, the author time stands in. branch_commits.landed_at is computed from the same committer time, read from git rev-list --timestamp (LoadGraph), and a stand-in author time is not used there. Labor reads committed_at only to tell a commit really made at the epoch from the synthetic epoch landing (DefaultBranchState, internal/store/labor.go). Never updated.
subject	text	NO	Derived from message	cve.Subject(clean(msg)): trim the whole message, take the text before the first \n, trim again (internal/cve/extract.go, internal/store/branches.go). This is only the first line, unlike git's %s, which joins the first paragraph. Never updated.
message	text	NO	git %B (raw body)	The full raw message, subject included, as git prints it after its usual re-encoding to the log output encoding. It is not trimmed and normally ends with \n (internal/gitx/log.go; test at internal/gitx/gitx_test.go). Passed through clean(). Never updated.
files_changed	integer	NO	--numstat records of the same git log	len(c.Files): the number of numstat records before clean() deduplication (internal/store/branches.go). With --no-renames a rename counts as a delete plus an add. A merge has 0, because git log prints no diff for merges by default. A root commit counts every file: the stream passes --root, so a log.showRoot=false setting cannot hide them. Binary files count. Never updated.
lines_added	integer	NO	--numstat	The sum of the numeric added counts. Binary files ("-") add 0 (internal/store/branches.go). It includes records that are later dropped as clean() duplicates from commit_files. Never updated.
lines_removed	integer	NO	--numstat	As lines_added, for removed counts.
cntrb_id	uuid	YES	Aveloxis contributor id (aveloxis_data.contributors.cntrb_id), by value	Identity resolution (see the identity section below). NULL at insert. Set or cleared by Store.ApplyResolutions (internal/store/branches.go) in sync and resolve. NULL means not yet checked, not found, or ambiguous.
cntrb_resolution	text	YES	Constant naming the step that matched	One of aveloxis_commit, email, alias, identity (internal/source/identity.go). Written together with cntrb_id, and NULL exactly when cntrb_id is NULL (CHECK).
ingested_at	timestamptz	NO	Database clock	DEFAULT now(), the start time of the InsertCommits transaction on the DB server. Never updated. Sync uses it as the plausibility ceiling for committer times: a committer time more than 24h after ingested_at does not count when computing landing times (internal/pipeline/sync.go).
tool_version	text	NO	Constant db.ToolVersion	The aveloxis-cve version that inserted the row (internal/db/version.go, internal/store/branches.go). It is not changed when later versions update the bookkeeping columns.
dates_invalid	boolean	NO	Parser flag	TRUE when git gave an author or committer time that is unparseable or outside years 1-9999 (for example a malformed ident). See author_at (internal/gitx/log.go). Default FALSE (migration 0005). Never updated. Labor ignores landings via such commits when it picks the first dated landing (internal/store/labor.go).
cntrb_checked	boolean	NO	Bookkeeping	Default FALSE (migration 0005, which backfilled TRUE where cntrb_id was set). ApplyResolutions sets TRUE on every commit in a resolution batch, matched or not (internal/store/branches.go). Sync resolves only commits where this is FALSE.
facade_checked	boolean	NO	Bookkeeping	Default FALSE (migration 0017, so the first sync after that migration counts every existing commit). SetFacadeFiles sets TRUE for every commit in the batch it asked git about, even one that produced no rows (internal/store/branches.go).

The patch-id, CVE, GHSA, cherry-pick and revert columns (patch_id, patch_id_checked, cherry_picked_from, cherry_picks_checked, cve_ids, ghsa_ids, is_revert, reverts_commits, reverts_checked) are described under commits (CVE and link columns).

Notes:

• Personal data:

  • author_name, author_email, committer_name and committer_email are personal data copied verbatim from git;

  • message and subject often contain more (Signed-off-by, Co-authored-by and Reported-by trailers with names and emails);

  • cntrb_id links a commit to an Aveloxis contributor profile.

• Timezones: author and committer timezone offsets are discarded; only the UTC instant is stored.

• Encoding: clean() collapses a run of invalid bytes into one U+FFFD, so stored text can differ from the git bytes. The CVE, GHSA, cherry-pick and revert regexes run on the uncleaned message, but those ids are ASCII, so the result is the same.

• Git configuration: the git log stream pins core.quotepath, log.showSignature and color.ui, and passes --no-renames and --root, so diff.renames and log.showRoot settings do not change it (since 0.4.23; TestStreamCommitsListsRootFilesUnderLogShowRootFalse). Other git settings are not pinned.

• Aveloxis parity: the project notes say default-branch commits match aveloxis_data.commits exactly on the repositories checked. Aveloxis covers the default branch only; this table covers every branch and archived tip.

commit_files

One row per (commit, path) from git log --numstat --no-renames -z of a non-merge commit against its parent; a root commit is compared with the empty tree. It is the per-path change list that labor windows use. The primary key is (commit_id, file_path). commit_id references commits(commit_id) with ON DELETE CASCADE, DEFERRABLE INITIALLY DEFERRED. There is an index on (file_path).

• Insert: written once, in the same transaction as the commit, by Store.InsertCommits (internal/store/branches.go), with ON CONFLICT (commit_id, file_path) DO NOTHING. It is written for every commit of the streamed batch, and rows already stored are kept. In normal operation a stored commit is not streamed again; one that is (a retry after a sync failed between ingest and its branch update) can gain missing paths, while its files_changed and line totals stay as first stored.

• Updates and deletes: never updated or deleted, except by cascade.

• Merges: a merge commit has no rows (git prints no numstat for it). Aveloxis's facade, and commit_facade_files, store one (no files) row for it instead.

• Readers: labor's WindowActivity (internal/store/labor.go) sums lines per path and counts commits and contributors; the view branch_commit_files joins it with branches.

Column	Type	Null	Source	How it is derived / written
commit_id	bigint	NO	commits.commit_id	Found by joining the staged rows to commits on (repo_id, commit_hash) (internal/store/branches.go).
file_path	text	NO	Path field of a --numstat -z record	NUL-terminated records give raw, unquoted paths, and core.quotepath=off is set too. With --no-renames a rename appears as a delete of the old path plus an add of the new one; a copy appears only as an add of the new path. An empty path fails the sync (internal/gitx/log.go). Stored through clean(). If two raw paths of one commit become the same string after clean(), only the first is kept (internal/store/branches.go).
lines_added	integer	YES	Added field of the numstat record	strconv.Atoi. Git prints "-" for a binary file, stored as NULL. A field that is neither a number nor "-" fails the sync (internal/gitx/log.go).
lines_removed	integer	YES	Removed field of the numstat record	As lines_added (internal/gitx/log.go).

Notes:

• Personal data: a path can occasionally contain a person's name; there is nothing else personal here.

• Difference from the facade table: these counts are raw numstat, with no whitespace adjustment and binaries as NULL. Renames are split into a delete and an add. They differ from commit_facade_files on purpose: labor needs per-path counts that match snapshot paths.

• Sum caveat: commits.lines_added, lines_removed and files_changed are computed before the clean() deduplication. In the rare collision case they can exceed the sum or count of this table's rows.

commit_facade_files

One row per (commit, file name), counted exactly as Aveloxis's facade counts files into aveloxis_data.commits (cmt_filename, cmt_added, cmt_removed, cmt_whitespace). It covers every commit on every branch, where Aveloxis covers the default branch only. A commit with no file stats (a merge, an empty commit) gets a single row with file_name = '(no files)' and 0/0/0. The primary key is (commit_id, file_name). commit_id references commits(commit_id) with ON DELETE CASCADE, DEFERRABLE INITIALLY DEFERRED.

• Insert: in sync, the last step (internal/pipeline/sync.go), for commits with facade_checked = FALSE. Batches hold 500 commits, selected by FacadeCandidates (internal/store/branches.go) and written by SetFacadeFiles with ON CONFLICT (commit_id, file_name) DO NOTHING. facade_checked is set in the same transaction.

• Updates and deletes: never updated or deleted, except by cascade.

• Readers: none inside aveloxis-cve (no view or query reads it). It exists for consumers who want Aveloxis-comparable counts on non-default branches.

Git command (internal/gitx/facade.go): git log --no-walk=unsorted --stdin --numstat -p --src-prefix=a/ --dst-prefix=b/ --submodule=short --no-color --no-ext-diff --no-textconv --format=%x1e%H. The batch's hashes go to stdin, and the environment is LC_ALL=C. Unlike every other git call here, it does not get the runner's -c core.quotepath=off overrides: git runs with the user's configuration and git's defaults, as Aveloxis's does. So rename detection is on (the git default) and core.quotepath is as configured (default on). Each commit is shown against its first parent, and merges show no diff. Each output line is cut to its first 64 KiB, as Aveloxis's readCappedLine does (internal/gitx/facade.go).

Column	Type	Null	Source	How it is derived / written
commit_id	bigint	NO	commits.commit_id	Found by joining on (repo_id, commit_hash) with the hash from the %x1e%H line (internal/store/branches.go).
file_name	text	NO	Name field of the numstat line, as git prints it	Kept verbatim: renames appear as git renders them (old => new, dir/{a => b}/f), and with core.quotepath on, non-ASCII names appear C-quoted. Files are listed in numstat order (internal/gitx/facade.go). Stored through aveloxisUTF8(): one U+FFFD per invalid byte (Aveloxis's db.safeUTF8 rule, unlike clean()), and NUL becomes U+FFFD (internal/store/branches.go). (no files) (constant gitx.NoFiles, internal/gitx/facade.go) when git gives no file. If a name repeats within a commit, the first row wins.
lines_added	integer	NO	Patch walk, or numstat	A file with a diff --git section is counted from its patch, starting from 0. Inside a hunk, each + line whose trimmed content is non-empty, and that is not a reformat, adds 1 (internal/gitx/facade.go). A file without a patch section keeps its numstat added count; "-" (binary) parses as 0. A binary file with a section but no hunks gets 0.
lines_removed	integer	NO	Patch walk, or numstat	Each - line inside a hunk adds 1. Each reformat (below) subtracts 1 (internal/gitx/facade.go). Without a patch section, the numstat removed count is kept ("-" becomes 0). It cannot go below 0, because every subtraction is matched by an earlier counted removal.
whitespace	integer	NO	Patch walk (Augur's whitespace rules)	Counts two kinds of added lines (internal/gitx/facade.go): 1. a + line whose content is empty after strings.TrimSpace; 2. a reformat: a + line whose trimmed content is longer than 8 runes and equals the trimmed content of a - line in the current removal run. The removal-run memory is cleared when a new run of - lines starts after a + line, and at each new file. It is not cleared by context lines or hunk headers (Augur's behaviour). A symlink-to-file type change (two sections, one numstat line) is counted as one file. Without a patch section the value is 0.

Notes:

• Personal data: only file names; nothing personal beyond what a path may contain.

• Aveloxis parity: per the project notes (0.4.18), on repos 123/132/281/377 this table matched aveloxis_data.commits exactly on the default branch, 42,640 rows. Aveloxis runs two passes (a numstat pass and a whitespace-walker pass); this runs the walker's command once and applies both.

• Pairing: patch sections are paired with numstat names by position. The code comment says Aveloxis also checks each header against its name; aveloxis-cve does not.

• Git configuration: because this command does not pin git configuration, the output depends on the invoking user's global and system git config. Examples are diff.renames, core.quotepath and log.showRoot (root-commit diffs). This is deliberate: Aveloxis's facade runs with its host's configuration, so the same settings give the same rows. Settings that differ between the Aveloxis host and the aveloxis-cve host break parity. The test fixtures isolate tests from global and system config.

• Unmatched hashes: a commit hash in the batch that git produced no output for would still be marked facade_checked and get no rows. This cannot normally happen, because every hash comes from commits of the same clone.

Identity resolution (commits.cntrb_id, cntrb_resolution, cntrb_checked)

This maps each commit's author to an Aveloxis contributor using only data Aveloxis already holds: read-only SQL, no forge API calls. The committer is never resolved.

When it runs:

• sync resolves commits with cntrb_checked = FALSE, that is, commits newly ingested or never looked at (ScopeUnchecked; internal/pipeline/sync.go).

• resolve re-checks every commit with cntrb_id IS NULL (ScopeUnresolved).

• resolve --all re-checks every commit and clears answers that no longer hold (ScopeAll; internal/pipeline/sync.go, internal/cli/sync.go).

• run does a sync, then a resolve of unresolved commits, before labor (internal/cli/sync.go), so labor counts newly resolved authors.

• labor only reads the result.

Each command reads its candidates with UnresolvedAuthors (internal/store/branches.go). It processes them in batches of 5000 (internal/pipeline/sync.go): source.ResolveAuthors for each batch (internal/source/identity.go), then Store.ApplyResolutions (internal/store/branches.go).

The lookup key is the commit hash plus the author email, trimmed of Unicode whitespace and lowercased in Go (strings.TrimSpace, strings.ToLower), and compared with PostgreSQL's lower(...) of Aveloxis's column (internal/source/identity.go). There are four steps, in order (internal/source/identity.go):

1. aveloxis_commit: the distinct aveloxis_data.commits.cmt_ght_author_id values for the same repo_id and cmt_commit_hash. Only ids that join to a live contributor count. This step ignores the email.

2. email: aveloxis_data.contributors with lower(cntrb_email) equal to the key, live only.

3. alias: aveloxis_data.contributors_aliases with lower(alias_email) equal to the key and COALESCE(cntrb_active,1) = 1, joined to a live contributor.

4. identity: aveloxis_data.contributor_identities with lower(email) equal to the key, joined to a live contributor.

"Live" means COALESCE(cntrb_deleted,0) = 0, so merged contributors are never chosen. Steps 2-4 are skipped when the email is empty.

How the steps decide:

• First match wins: the first step that finds any contributor decides.

• One contributor: that cntrb_id and the step's name are stored.

• More than one (ambiguous): resolution stops with no answer. It does not fall through to later steps (internal/source/identity.go).

• Zero contributors: the next step is tried. A step 1 whose Aveloxis author is a merged (cntrb_deleted = 1) contributor counts as zero, so resolution falls through to the email steps.

Column	Type	Null	Source	How it is derived / written
commits.cntrb_id	uuid	YES	aveloxis_data.contributors.cntrb_id, reached through one of the four steps	Written by ApplyResolutions with u.id::uuid (internal/store/branches.go). The id is read as text using min(...), which is unique because the step found exactly one contributor. NULL when unchecked, unmatched or ambiguous. Under --all, a previous id is cleared (set to NULL) when no step gives an answer any more. Under sync and plain resolve the candidate rows are already NULL or unchecked, so in practice nothing is cleared.
commits.cntrb_resolution	text	YES	Constant: the name of the step that matched	aveloxis_commit, email, alias or identity (internal/source/identity.go). Always written together with cntrb_id.
commits.cntrb_checked	boolean	NO	Bookkeeping	Set TRUE for every key in the batch, including unmatched and ambiguous ones. The UPDATE touches a row only when the (id, method) pair changes or the row was unchecked (internal/store/branches.go).

Notes:

• Side effect: when cntrb_id changes on a commit, ApplyResolutions also lowers branch_repos.labor_dirty_since to the earliest landed_at of that commit on each branch (internal/store/branches.go). The next labor run then recomputes the affected windows, because their distinct-contributor counts may change.

• Counting unresolved authors: labor counts a NULL cntrb_id by lower(author_email) (internal/store/labor.go).

• Personal data: cntrb_id is a pseudonymous key, but it joins to Aveloxis's contributor records (logins, names, emails). Without lower(...) indexes on Aveloxis's three email columns (Aveloxis creates them from 0.29.141; doctor warns while one is missing), each lookup reads those tables in full (resolveAuthorsSQL, internal/source/identity.go).

• Unclear points:

  • aveloxis-cve does not normalise or validate emails beyond trim and lowercase. Whether Aveloxis stores emails in a form that makes lower() equality exact, for example without surrounding whitespace, depends on Aveloxis.

  • In step 1 the hash comparison is exact text equality, so it relies on Aveloxis storing cmt_commit_hash as full lowercase hex.

Branches, commit links and CVE views

Conventions used below. Paths are relative to the repository root. "sync" means the sync command and the sync step of run. Both go through Syncer.SyncRepo (internal/pipeline/sync.go), which takes the per-repository advisory lock and makes every write fenced (sync.go, internal/store/fence.go). "resolve" means the resolve command and the resolve step of run (Syncer.Resolve, sync.go). "labor" means the labor command and the labor step of run. No code path in the program deletes a commits row, so the ON DELETE CASCADE foreign keys on commits only fire if someone deletes rows by hand. repo_id is Aveloxis's repos.repo_id, copied by value with no foreign key.

The order of the steps inside one sync (sync.go) matters for several columns:

1. Readiness check against Aveloxis.

2. Clone or fetch, then ref reconciliation and archiving (gitx.Runner.Sync).

3. Re-attach archived tips (restoreArchives).

4. List refs.

5. Ingest commits (ingest).

6. ResolveCherryPicks.

7. ResolveReverts.

8. RefreshUndoes.

9. Branch rows, membership and stats (updateBranches).

10. Identity resolution of unchecked commits.

11. Patch-ids.

12. Facade files.

Each store call runs in its own transaction. If a sync fails part-way, the steps already done stay committed, and the next sync finishes the rest.

branch_repos

One row per live branch (refs/heads/<name>) or archived tip (refs/cve-archive/<deleted|forced>/<UTC stamp>[-N]/<name>) in aveloxis-cve's own bare clone of a repository. The primary key is branch_id, an identity column. The natural key is UNIQUE (repo_id, ref_name). Two CHECK constraints apply: archive_reason IN ('deleted','forced'), and (archive_reason IS NULL) = (archived_at IS NULL). Sync creates, updates and deletes the row, through Syncer.updateBranches (sync.go); resolve and labor write only labor_dirty_since (below). It inserts or updates the row for every ref present in the clone (Store.UpsertBranch, internal/store/branches.go), renames a live row into an archive in place (ArchiveBranch, branches.go), and deletes live rows whose ref is gone (DeleteBranchesNotIn, branches.go). Membership columns are set by WriteMembership (branches.go) and stats by SetBranchStats (branches.go). labor_dirty_since is also set by identity resolution (sync and resolve; ApplyResolutions, branches.go) and cleared by labor (InvalidateWindows, internal/store/labor.go).

Lifecycle:

• A live row is deleted, with its branch_commits by cascade, when its ref is no longer in the clone and it was not renamed into an archive.

• Archived rows are never deleted. The database, not the clone, is the record of archived history.

• An archived row whose ref cannot be restored into a re-created clone (its objects are gone) is kept as it was. It is no longer upserted, so its last_seen_at stops advancing.

Readers:

• sync: Store.Branches, branches.go. It lists previous tips for the ingest exclusion and supplies the membership and stats state.

• labor: DefaultBranchState, labor.go. This is the default, live row with membership_head_sha = head_sha and membership_scheme current, plus labor_dirty_since.

• status: internal/store/status.go. It counts live and archived rows and shows the default branch name.

• The views branch_commit_files (for analysts; the program does not read it) and cve_fix_propagation (read by cves).

Column	Type	Null	Source	How it is derived / written
branch_id	integer	NO	Identity sequence	GENERATED ALWAYS AS IDENTITY; returned by the upsert (branches.go). It is kept when a live row is renamed into an archive (branches.go), so the archived row keeps its membership. A force-pushed or re-created live branch usually gets a new branch_id (see the archive-rename note below).
repo_id	bigint	NO	Aveloxis repos.repo_id (value copy)	Set on insert by UpsertBranch (branches.go). Never updated.
ref_name	text	NO	git ref name, from git for-each-ref --format=%(objectname) %(refname) over refs/heads/ and refs/cve-archive/ (internal/gitx/clone.go)	Full ref name, passed through clean() (invalid UTF-8 runs and NULs become U+FFFD; branches.go). Of two refs whose names sanitise alike, the later one is skipped (sync.go). Insert: UpsertBranch. Update: only ArchiveBranch rewrites it, from refs/heads/<b> to the archive ref (branches.go).
branch_name	text	NO	Parsed from the ref name	Live branch: the name after refs/heads/. Archive: everything after refs/cve-archive/<reason>/<stamp>/ (it may contain /), which is the original branch name (parseArchiveRef, clone.go). It is passed through clean(), set on insert only (the conflict path does not update it), and unchanged by an archive rename.
head_sha	text	NO	git ref tip (objectname)	Updated on every sync from the ref's current tip (branches.go). ArchiveBranch sets it to the archived old tip (branches.go).
head_committed_at	timestamptz	YES	Committer timestamp of the tip, from git rev-list --parents --timestamp --branches --glob=refs/cve-archive/* (LoadGraph, internal/gitx/graph.go)	The raw committer time of the tip as UTC seconds (sync.go). It is NULL when the time is outside years 1..9999 (gitx.ValidUnix, log.go; graph.go). It is not plausibility-capped, unlike landed_at. Updated on every upsert.
is_default	boolean	NO (default false)	The forge's default branch: git ls-remote --symref origin HEAD, applied to the clone's HEAD with git symbolic-ref and read back (clone.go)	TRUE when the ref is live and its branch name equals the HEAD branch (sync.go). Recomputed for every ref on every upsert (branches.go). ArchiveBranch forces FALSE. If ls-remote fails, the previous HEAD is kept (a warning, not a failure). After a successful sync at most one live row is TRUE. No constraint enforces it: a sync that fails part-way after the forge's default branch changed can leave two TRUE rows until the next sync succeeds.
archive_reason	text	YES	Ref reconciliation in gitx.Runner.Sync (clone.go)	NULL for a live branch. forced: the local tip was not an ancestor of the fetched tip (git merge-base --is-ancestor), so the old tip was kept under refs/cve-archive/forced/... before the update (clone.go). deleted: a local branch no longer advertised by the remote; its tip is archived and refs/heads/<b> is deleted (clone.go). A (branch, tip) pair already archived is not archived again (clone.go). If the remote advertises no branches while the clone has some, sync fails and nothing is archived (ErrRemoteEmpty, clone.go). Written on insert of an archive ref's row (from the ref name), or by ArchiveBranch. Never cleared.
archived_at	timestamptz	YES	Wall clock of the archiving sync, encoded in the archive ref name	now is Syncer.now() taken just before the fetch (sync.go). It is formatted 20060102T150405Z, UTC to the second, in the ref name (clone.go). A -N suffix makes the name unique within one second and is dropped when parsing (clone.go). Written on insert of an archive row, or by ArchiveBranch.
merge_base_with_default	text	YES	git merge-base <tip> <default tip> (graph.go)	For the default row, or any branch whose tip is the default tip, it is the tip itself (sync.go). NULL when the histories are unrelated (git exits 1). Written by SetBranchStats (branches.go) when the branch tip or the default tip moved since the last computation (sync.go). When the default branch's tip is not among the refs, it is not written (stale values stay). Archived rows get it too.
ahead_of_default	integer	YES	Commit graph	Number of commits reachable from the branch tip (all parents) and not from the default tip, computed in memory with bitsets (branches.AheadBehind, internal/branches/membership.go). Equivalent to git rev-list --count <default>..<tip>. 0 for the default row. Same write and refresh rule as merge_base_with_default.
behind_default	integer	YES	Commit graph	Commits reachable from the default tip and not from the branch tip (same function). Same write rule.
membership_head_sha	text	YES	The tip that membership was computed for	Set by WriteMembership to the tip in the same transaction as the branch_commits rows (branches.go). NULL until the first membership write succeeds. It is compared with the current tip on each sync to decide whether to recompute (sync.go), and ArchiveBranch renames a live row only if it equals the archived tip (branches.go).
commit_count	integer	YES	branch_commits	count(*) of the branch's branch_commits rows after each membership write (branches.go). Equals git rev-list --count <tip>. It is not refreshed when membership is not rewritten.
first_seen_at	timestamptz	NO (default now())	Wall clock (DB)	Set on insert only. A renamed archive keeps the live branch's value.
last_seen_at	timestamptz	NO (default now())	Wall clock (DB)	now() on every upsert (branches.go) and on ArchiveBranch (branches.go). It stops advancing for an archived row whose ref is missing from the clone.
tool_version	text	NO	Constant db.ToolVersion (internal/db/version.go)	Overwritten on every upsert. Not touched by ArchiveBranch, WriteMembership or SetBranchStats.
stats_default_sha	text	YES	Default tip SHA used for the stats	Written by SetBranchStats (branches.go). Used to skip recomputation when neither tip moved (sync.go).
stats_head_sha	text	YES	Branch tip SHA used for the stats	Same as above.
labor_dirty_since	timestamptz	YES	Derived from branch_commits.landed_at	Lowered with LEAST(existing, new), so NULL means "no mark". Two writers lower it: (1) WriteMembership uses the earliest landed_at among rows it deleted, rows it inserted, and both the old and new values of rows it changed. An identical recompute marks nothing (branches.go). A new branch is therefore marked from its oldest landing. (2) ApplyResolutions uses the earliest landed_at on each branch of commits whose cntrb_id changed (sync and resolve; branches.go). Labor reads it for the default branch only (labor.go), marks windows ending at or after it for recompute, and sets it back to NULL only if it still equals the value read (compare-and-clear, labor.go; called from internal/pipeline/labor.go). Non-default and archived rows can carry a mark that nothing clears; no code reads it there.
membership_scheme	integer	NO (default 1)	Constant store.MembershipScheme = 3 (branches.go)	Set by WriteMembership (branches.go). A row with an older scheme gets its membership recomputed in full by the next sync (sync.go). Labor refuses a default branch whose scheme is not current (labor.go). Scheme 1 is the column default, for rows written before migration 0007 (0.4.2: running maximum of raw committer times, per CHANGELOG). Scheme 2 (0.4.3/0.4.4) is the running maximum of plausible committer times. Scheme 3 (0.4.5 and later) judges plausibility against each commit's own ingest time, and an implausible commit lands with the commit beneath it.

Notes:

• Archive rename. When Sync archives a tip, the next updateBranches finds an archive ref with no row. If the live row refs/heads/<b> has membership_head_sha equal to the archived tip, that row is renamed in place (branches.go, sync.go). It keeps branch_id, first_seen_at and every branch_commits.first_observed_at. Otherwise the archive ref gets a new row, its membership is computed from scratch, and first_observed_at restarts. After a force-push the live branch name usually gets a new row (when the old row is renamed into the archive in place), with a new branch_id, and full membership, so first_observed_at on the live branch restarts for its whole history.

• Re-created clone. restoreArchives (sync.go) re-creates each archive ref the database knows, with git update-ref via RestoreRef (internal/gitx/tree.go), but only when the tip object is still present.

• Stats are left alone, not cleared, when the default branch's tip is not among the refs (sync.go).

branch_commits

One row per (branch row, commit reachable from its tip). This is full membership: the whole reachable history of every live and archived branch, not just the commits unique to it. The primary key is (branch_id, commit_id). Three deferrable foreign keys with ON DELETE CASCADE point to branch_repos(branch_id), commits(commit_id), and commits(commit_id) via landed_via. Indexes: (commit_id), (landed_via), (branch_id, landed_at) INCLUDE (commit_id). Only sync writes it, through Syncer.membership (sync.go) -> Store.WriteMembership (branches.go). Membership is recomputed when the branch has no membership_head_sha, its tip moved, or its membership_scheme is older than 3 (sync.go).

The write is incremental (full=false: rows are inserted or updated, none deleted) only when the previous membership tip is still in the graph and lies on the new tip's first-parent chain (OnFirstParentChain, membership.go). Otherwise it is a full recompute (full=true), which also deletes rows no longer reachable (branches.go). The full path covers a force-push onto the same row, a "foxtrot" merge, and a scheme upgrade. Rows are also deleted by cascade when a live branch_repos row is deleted.

Readers:

• labor: WindowActivity (labor.go) reads rows by landed_at, (start, end], or [start, end] for the first window. ChainLandings (labor.go) reads first-parent landings. DefaultBranchState (labor.go) reads the first dated landing.

• sync and resolve, through ApplyResolutions (dirty marks).

• The views branch_commit_files and cve_fix_propagation.

Column	Type	Null	Source	How it is derived / written
branch_id	integer	NO	branch_repos.branch_id	The branch row being computed (sync.go).
commit_id	bigint	NO	commits.commit_id	Looked up by hash (CommitIDs, branches.go). Sync fails if a reachable commit was not ingested (sync.go). The commit set is branches.Compute (membership.go): the commits reachable from the tip, excluding those reachable from the stop set (empty for full; {previous tip} for incremental).
on_first_parent	boolean	NO	Commit graph	TRUE when the commit is on the tip's first-parent chain, that is, committed or merged directly onto the branch. FALSE when it was brought in through a merge's non-first parent (membership.go). Rewritten on change (branches.go).
landed_via	bigint	NO	Commit graph	The first-parent-chain commit that first made this commit reachable. Compute walks the chain oldest first, and each chain commit C claims every not-yet-seen ancestor reachable from it through all parents (depth-first), so landed_via = C (membership.go). For a first-parent commit it is the commit itself. For a merged-in commit it is the merge commit on the chain. Rewritten on change.
landed_at	timestamptz	NO	Committer times of the first-parent chain, adjusted (branches.ChainTimes, membership.go)	The landing time of landed_via, at second precision, UTC (sync.go). Walk the full first-parent chain from the root up and keep a running maximum of plausible committer times. A time is plausible when it is valid (years 1..9999) and not later than that commit's own commits.ingested_at + 24h (plausibleFuture, sync.go; for a commit without an ingest time, now + 24h). An implausible commit takes the value of the commit beneath it. An implausible root takes 0, which is 1970-01-01T00:00:00Z, a synthetic epoch landing. So landings never run backwards along a branch, and they depend only on ancestors (incremental and full results agree). The cost: a commit whose clock runs more than one day ahead lands at the previous commit's time. Every commit brought in by one merge shares the merge's landed_at.
first_observed_at	timestamptz	NO (default now())	Wall clock (DB)	Set on insert only. The upsert updates only on_first_parent, landed_via and landed_at, and only when one of them differs (branches.go). It survives full recomputes and the archive rename of the branch row. It restarts when the branch row is new (a new branch, the new row after a force-push, or an archive ref that could not be renamed in place).

Notes:

• Because the history is shared, landed_at on a branch cut from another branch records when the change entered that branch's first-parent history. For commits before the fork point, that predates the branch's existence. It is not when the branch was created.

• The synthetic epoch landing (landed_at = 'epoch') can appear for an implausible root and for the commits that inherit its time. Labor skips it when anchoring windows: an epoch landing counts only when nothing lands after the epoch, and then only if its landing commit was really committed at the epoch (see repo_labor_windows.window_end).

• Size: every branch stores its full history, so the row count is roughly (branches x history length).

commits (CVE and link columns)

These columns live on the commits row (grain: one row per commit reachable from any live or archived ref in the clone; key commit_id; unique (repo_id, commit_hash)). Sync inserts every column below at ingest: Syncer.ingest (sync.go) streams git log --stdin -z --no-renames --root --numstat --format=... over all ref tips, excluding the tips of known branch_repos rows that still exist (Syncer.ingest, through gitx.Runner.Existing in internal/gitx/graph.go; the stream is StreamCommits, internal/gitx/log.go). Store.InsertCommits (branches.go) stores them with ON CONFLICT (repo_id, commit_hash) DO NOTHING, in batches of 2000. The message-derived columns are computed once, at insert, from the commit message by cve.Extract and cve.IsRevert (internal/cve/extract.go). They are never re-parsed later, except by the one-off migration backfills listed below. Only the *_checked flags and patch_id are updated afterwards, all by sync. Readers: the views below, RefreshUndoes, resolveLinks, and status (counts commits with cardinality(cve_ids) > 0, status.go).

Column	Type	Null	Source	How it is derived / written
patch_id	text	YES	git diff-tree --stdin --root -p --no-renames --no-color --no-ext-diff piped into git patch-id --stable (Runner.PatchIDs, graph.go)	Computed only for non-merge commits (cardinality(parent_hashes) <= 1) not yet checked, in batches of 500 by commit_id (PatchIDCandidates, branches.go; sync.go). This runs in the sync step after identity resolution. The diff is against the only parent, or against the empty tree for a root (--root). --stable makes the id independent of file order. patch-id also ignores whitespace and line numbers, so equal ids mean the same change up to those. NULL when git prints no id (an empty diff). Written by SetPatchIDs (branches.go). Never recomputed. Merge commits never get one.
patch_id_checked	boolean	NO (default false)	Flag	Set to TRUE by SetPatchIDs for every candidate in the batch, whether or not an id was produced (branches.go). Merge commits are never candidates, so it stays FALSE for them permanently.
cherry_picked_from	text[]	NO (default '{}')	Commit message, regex \(cherry picked from commit ([0-9a-f]{7,64})\) (extract.go)	Every match anywhere in the raw message (not only in the trailer block), in message order, de-duplicated (extract.go). Case-sensitive: only lowercase hex, 7 to 64 characters, with ) immediately after. This is what git cherry-pick -x writes. Set at insert.
cherry_picks_checked	boolean	NO (default false)	Flag	At insert: cardinality(cherry_picked_from) = 0 (branches.go), so a commit naming no SHA is born checked. Later set TRUE by resolveLinks (branches.go) once every SHA it names is at least 40 characters and resolves to exactly one commit in the repository. A commit with an abbreviated SHA, an ambiguous SHA, or a SHA not yet ingested stays FALSE and is re-resolved on every sync. Partial index commits_cherry_picks_pending_idx covers the pending ones.
cve_ids	text[]	NO (default '{}')	Commit message, regex (?i)\bCVE-(\d{4})-(\d{4,})\b (extract.go)	Case-insensitive match with ASCII word boundaries. Canonicalised to CVE-<year>-<number> with an upper-case prefix, de-duplicated, and sorted byte-wise, so CVE-2024-10000 sorts before CVE-2024-9999 (extract.go). It is computed from the raw message; the stored message is the clean()ed one. Set at insert. GIN index commits_cve_ids_idx.
ghsa_ids	text[]	NO (default '{}')	Commit message, regex (?i)\bGHSA-([23456789cfghjmpqrvwx]{4})-(...{4})-(...{4})\b, with the same alphabet in all three groups (extract.go)	Canonicalised to GHSA- plus a lower-case body, de-duplicated and sorted (extract.go). Set at insert. GIN index. It is not used by any view or by revert parity, which only use cve_ids.
is_revert	boolean	NO (default false)	Commit message (cve.IsRevert, extract.go)	The subject is the first line of the trimmed cleaned message, trimmed (cve.Subject, extract.go). The flag is FALSE if the subject starts with Reapply " or Revert "Revert . Otherwise it is TRUE if the subject starts with Revert " or the message contains This reverts commit . Set at insert (branches.go). It is for information only: no view and no Go reader uses it, and revert handling uses reverts_commits / commit_reverts. Migration 0006 backfilled it, and 0009 recomputed every row with the same rule in SQL.
reverts_commits	text[]	NO (default '{}')	Commit message, regex This reverts commit ([0-9a-f]{7,64}) (extract.go)	Every match in message order, de-duplicated (extract.go). Case-sensitive, lowercase hex. It is extracted whatever is_revert says, so a reapply (Revert "Revert ...", whose body says This reverts commit <the revert>) has an entry. That makes revert chains work. Set at insert. Migration 0008 backfilled pre-existing rows with the same regex in SQL (regexp_matches, not de-duplicated).
reverts_checked	boolean	NO (default false)	Flag	Same rule as cherry_picks_checked, for reverts_commits (ResolveReverts, branches.go; insert value branches.go). Partial index commits_reverts_pending_idx.

Notes:

• Migration 0009 set both *_checked flags TRUE for pre-existing rows naming no SHA.

• Because messages are parsed once, a later change to a regex does not reach commits already stored unless a migration backfills them.

• The commits of a vanished live branch stay in commits, because every removed tip is archived and the rows are never deleted.

commit_cherry_picks

One row per resolved cherry-pick link: the commit commit_id carries a (cherry picked from commit X) trailer, and X names exactly one commit picked_from_id in the same repository. The primary key is (commit_id, picked_from_id). Both columns are deferrable FKs to commits with ON DELETE CASCADE. Index on (picked_from_id). Only sync writes it: Store.ResolveCherryPicks -> resolveLinks (branches.go), called right after ingest (sync.go). Readers: cve_fix_commits (the cherry_pick match) and RefreshUndoes (the credit CTE).

Column	Type	Null	Source	How it is derived / written
commit_id	bigint	NO	commits.commit_id of a commit with NOT cherry_picks_checked and a non-empty cherry_picked_from	resolveLinks builds tmp_links (branches.go). It unnests each named SHA and range-scans commits of the same repo_id for commit_hash >= sha AND commit_hash < sha || 'g', a prefix match that relies on hex characters sorting below 'g', with LIMIT 2.
picked_from_id	bigint	NO	The one commit whose hash has that prefix	Inserted only when exactly one commit matched and it is not the commit itself (ON CONFLICT (commit_id, picked_from_id) DO NOTHING, branches.go). An ambiguous or unknown SHA is never guessed and gets no row.

Notes:

• Each sync deletes, for the commits being re-resolved, existing links that no longer resolve uniquely to the same target (branches.go). An abbreviation that later becomes ambiguous loses its link. A source ingested later is picked up.

• Once a commit is checked (all SHAs full-length and resolved), its links are never revisited.

• Links stay within one repository. A cherry-pick from another repository, or from a commit never ingested (for example on an unfetched ref), stays unresolved, and the commit is retried on every sync.

commit_reverts

One row per resolved revert link: the commit commit_id contains This reverts commit X, and X names exactly one commit reverted_id in the same repository. The primary key is (commit_id, reverted_id). Both columns are deferrable FKs to commits with ON DELETE CASCADE. Index on (reverted_id). Only sync writes it: Store.ResolveReverts -> resolveLinks (branches.go), called after ResolveCherryPicks (sync.go). The resolution, deletion and retry rules are exactly those of commit_cherry_picks, with reverts_commits / reverts_checked. The reader is RefreshUndoes (chain CTE). No view reads this table directly.

Column	Type	Null	Source	How it is derived / written
commit_id	bigint	NO	commits.commit_id of a commit with NOT reverts_checked and a non-empty reverts_commits	As for commit_cherry_picks.commit_id (branches.go). It includes reapplies and any message quoting the line, because is_revert is not consulted.
reverted_id	bigint	NO	The one commit whose hash has the named prefix	Inserted only for a unique match that is not the commit itself (branches.go).

commit_cve_undoes

One row per (commit, CVE) where the commit names the CVE in its own cve_ids and undoes that CVE's fix, so cve_fix_commits must not report it as a fix. The primary key is (commit_id, cve_id). commit_id is a deferrable FK to commits with ON DELETE CASCADE. cve_id is plain text taken from commits.cve_ids, with no FK. Only sync writes it, through Store.RefreshUndoes (branches.go), right after ResolveReverts (sync.go). Every sync deletes all rows of the repository (joined through commits.repo_id, branches.go) and rebuilds them in the same transaction. Migrations 0012, 0013, 0015 and 0016 rebuilt it across all repositories. 0016 is the current rule and must stay identical to RefreshUndoes (TestRefreshUndoesAgreesWithMigration16). Reader: cve_fix_commits.

Column	Type	Null	Source	How it is derived / written
commit_id	bigint	NO	commit_reverts.commit_id of a revert whose own cve_ids names the CVE	Recursive query (branches.go). (1) credit(via, source, cve): every commit naming the cve credits itself. A commit that does not name the cve but is cherry-picked (commit_cherry_picks) from a commit naming it credits that commit. (2) chain: it starts at each revert R and each cve R names, where R's reverted commit has credit for that cve. The depth is 1 and the current node is the credit's source. From the current node it follows further commit_reverts edges whose target has credit for the cve, adding 1 to the depth each time. It stops at depth 64, and UNION de-duplicates states. (3) A row is written when max(depth) over the chains for (R, cve) is odd: 1 is the revert of the fix, 3 the revert of a reapply. Even depths are fixes: 0 is the fix, 2 a reapply. ON CONFLICT (commit_id, cve_id) DO NOTHING.
cve_id	text	NO	commits.cve_ids of the reverting commit	The CVE whose chain parity is odd.

Notes:

• Revert parity, the "safe subset" (user decision 2026-11-07, migration 0016). Chains follow only named commits and cherry-pick trailers, never patch-id matches. Patch-id links have no direction and made chains loop (0.4.14-0.4.15). A revert's target and a cherry-pick's source always existed before the commit naming them, so the kept edges are acyclic in real data. Known gap, pinned by a test: a revert that names a CVE, of an unnamed backport matched to the fix only by patch-id (no trailer), is not an undo, so it is reported as a fix. A further revert of it is in turn counted as an undo.

• A revert that does not name the CVE in its own message is never an undo here. It is also never a fix candidate, since cve_fix_commits starts from cve_ids. The exception: if its diff equals a fix's patch-id, it can be matched as patch_id in that view.

• A revert of a commit that does not name the CVE and is not a trailer cherry-pick of one that does (for example, a fix made by reverting the vulnerable commit) has no chain and stays a fix.

• If a sync fails between ResolveReverts and RefreshUndoes, the undoes reflect the previous link state until the next sync. Disabled repositories keep their last rows.

branch_commit_files (view)

Commits by branch and file: branch_commits joined, all inner joins, to branch_repos, commits and commit_files. The grain is one row per (branch row, commit, commit_files.file_path). It was defined in migration 0002 and never changed. The program does not read it; it is for analysts.

Column	Type	Null	Source	How it is derived / written
repo_id	bigint	NO	branch_repos.repo_id	Join b.branch_id = bc.branch_id.
branch_id	integer	NO	branch_commits.branch_id
branch_name	text	NO	branch_repos.branch_name
ref_name	text	NO	branch_repos.ref_name
archive_reason	text	YES	branch_repos.archive_reason	NULL for live branches. Filter on it to exclude archived tips.
commit_id	bigint	NO	commits.commit_id
commit_hash	text	NO	commits.commit_hash
committed_at	timestamptz	NO	commits.committed_at	The raw committer time, not the landing time.
landed_at	timestamptz	NO	branch_commits.landed_at	When the change reached this branch (see branch_commits).
on_first_parent	boolean	NO	branch_commits.on_first_parent
file_path	text	NO	commit_files.file_path	Per-path git log --numstat --no-renames (log.go), so a rename appears as a delete plus an add.
lines_added	integer	YES	commit_files.lines_added	NULL for a binary file.
lines_removed	integer	YES	commit_files.lines_removed	NULL for a binary file.

Notes:

• Inner join to commit_files: merge commits (git log prints no numstat for merges) and empty commits have no commit_files rows, so they do not appear.

• These counts are not Aveloxis-parity. Use branch_commits joined to commit_facade_files for counts identical to aveloxis_data.commits.

commit_equivalence (view)

Groups of commits that are the same change by git patch-id --stable. The grain is one row per (repo_id, patch_id) shared by more than one commit (HAVING count(*) > 1), over every commit of the repository on any branch, live or archived. It was defined in migration 0004 and never changed. The program does not read it.

Column	Type	Null	Source	How it is derived / written
repo_id	bigint	NO	commits.repo_id
patch_id	text	NO	commits.patch_id	Only rows with patch_id IS NOT NULL.
commits	integer	NO	count(*)::int	Number of commits sharing the patch-id. Always 2 or more.
commit_hashes	text[]	NO	array_agg(commit_hash ORDER BY committed_at, commit_hash)	Ordered by raw committer time, then hash.
first_committed_at	timestamptz	NO	min(committed_at)	Raw committer time.
last_committed_at	timestamptz	NO	max(committed_at)	Raw committer time.

Notes:

• Merges and empty-diff commits have no patch-id and never appear. Commits whose patch-id has not been computed yet (the step runs late in sync) are missing until it has.

• Equal patch-ids have no direction. A reapply has the same patch-id as the original change, and a cherry-pick has the same patch-id as its source, so a group can mix fixes, reapplies and backports.

cve_fix_commits (view)

Commits that fix a CVE, with how each was identified. The grain is one row per (repo_id, cve_id, commit_id). The current definition comes from migration 0011. Logic:

• named: every commit times each element of its cve_ids.

• tagged: named minus the (commit, cve) pairs in commit_cve_undoes.

• matches: three ranked arms.

  1. message: the tagged commit itself.

  2. cherry_pick: every commit with a commit_cherry_picks row whose picked_from_id is a tagged commit. One hop, resolved trailers only.

  3. patch_id: every commit of the same repository with the same non-NULL patch_id as a tagged commit. This includes the tagged commit itself.

• The result drops any (commit, cve) present in commit_cve_undoes, and DISTINCT ON (repo_id, cve_id, commit_id) ... ORDER BY rank keeps the best arm: message, then cherry_pick, then patch_id.

All CTEs are NOT MATERIALIZED, so a repo_id or cve_id filter reaches the commits scan. Reader: cve_fix_propagation, and through it the cves command.

Column	Type	Null	Source	How it is derived / written
repo_id	bigint	NO	commits.repo_id of the tagged commit	Matches stay within one repository.
cve_id	text	NO	Element of the tagged commit's cve_ids	Canonical upper-case CVE-YYYY-N....
commit_id	bigint	NO	The matched commit
commit_hash	text	NO	commits.commit_hash of the matched commit
subject	text	NO	commits.subject	First line of the cleaned message.
committed_at	timestamptz	NO	commits.committed_at	Raw committer time, not a landing time.
patch_id	text	YES	commits.patch_id of the matched commit	NULL for merges, empty diffs, and commits not yet patch-id'd.
matched_by	text	NO	Constant per arm	message, cherry_pick or patch_id. A commit matched several ways shows the first.

Notes:

• Only cve_ids is used. GHSA ids never produce fixes.

• A revert that names the CVE is a fix unless its revert-chain parity is odd (see commit_cve_undoes). is_revert is not consulted.

• The cherry_pick and patch_id arms do not re-check undo status on the matched commit, except through the final filter, which only removes commits that themselves name the CVE and are undoes. In particular, a commit with the fix's patch-id is reported as a patch_id match whatever its history (even after it was reverted); this is also how manual backports are found. The known gap (see commit_cve_undoes) is different: a revert naming the CVE of such an unnamed backport is reported as a message fix.

• The cherry_pick arm follows one trailer hop from a tagged commit. A chain of unnamed cherry-picks of cherry-picks is not followed. In practice cherry-pick -x copies the message, so each copy usually names the CVE itself.

• Commits are listed whether or not they are on a live branch. Use cve_fix_propagation for per-branch landing.

cve_fix_propagation (view)

When each CVE's fix reached each branch. The grain is one row per (repo_id, cve_id, branch row) where the branch, live or archived, contains at least one commit from cve_fix_commits for that CVE. It inner-joins cve_fix_commits to branch_commits (by commit_id) and branch_repos, then groups by CVE and branch. It was defined in migration 0004 and never changed; it picks up the current cve_fix_commits. Reader: the cves --repo-id N [--cve ID] command (Store.CVEPropagations, internal/store/cves.go). That command filters by repo_id and an upper-cased, trimmed CVE id, and orders by cve, first_landed_at, branch name.

Column	Type	Null	Source	How it is derived / written
repo_id	bigint	NO	cve_fix_commits.repo_id
cve_id	text	NO	cve_fix_commits.cve_id
branch_id	integer	NO	branch_repos.branch_id
branch_name	text	NO	branch_repos.branch_name	For an archived row this is the original branch name, so several rows can share a name. Use ref_name or archive_reason to tell them apart.
ref_name	text	NO	branch_repos.ref_name
archive_reason	text	YES	branch_repos.archive_reason	NULL for live.
is_default	boolean	NO	branch_repos.is_default	As of the last sync.
first_landed_at	timestamptz	NO	min(branch_commits.landed_at) over the branch's fix commits for the CVE	Earliest landing of any fix commit (any matched_by) on that branch. Plausibility-capped and monotonic (see branch_commits.landed_at).
lag_behind_first	interval	NO	first_landed_at - min(first_landed_at) OVER (PARTITION BY repo_id, cve_id)	0 on the branch or branches where the fix landed first. Archived rows take part in the minimum.
fix_commits	text[]	NO	array_agg(DISTINCT commit_hash)	The distinct hashes of the fix commits present on the branch. The view does not specify an order.

Notes:

• Because membership is full, a branch created from another branch after the fix landed contains the fix with the landing time it had on the shared first-parent history. Its lag_behind_first is then 0 or close to it, which is not a measure of a backport delay. A real backport (a cherry-pick or a manual patch) lands at its landing time on that branch (branch_commits.landed_at: the running maximum of plausible committer times along the first-parent chain), or at the time of the merge that brought it in.

• A synthetic epoch landed_at (an implausible root clock) can surface here if a fix commit landed through such a chain. That is unlikely for real fixes.

• Branches whose live row was deleted (the ref is gone and the row was not archived) are absent. Archived tips stay included, which is how fixes on end-of-life or rewritten branches remain visible.

Open questions: branches and links

• What scheme 1 of membership_scheme computed is not in the current code, because it predates migration 0007. The description above ("running maximum of raw committer times", 0.4.2) comes from CHANGELOG entries for 0.4.2/0.4.3, not from code.

• resolveLinks uses a range prefix match (commit_hash >= sha AND commit_hash < sha || 'g'). The code comment says it relies on hex sorting below 'g'. Not verified: how it behaves under every database collation.

• PatchIDs runs git with the operator's global git configuration (the runner sets -c core.quotepath=off -c log.showSignature=false -c color.ui=never, and the environment LC_ALL=C, GIT_TERMINAL_PROMPT=0 and GIT_SSH_COMMAND=ssh -o BatchMode=yes; internal/gitx/runner.go). Not checked: whether any user diff setting could change diff-tree -p output enough to change a --stable patch-id.

• The order of cve_fix_propagation.fix_commits: PostgreSQL's array_agg(DISTINCT ...) in practice returns sorted values, but the view does not guarantee an order.

Tracked repositories, migrations and rolling labor

Conventions used below:

• Paths like store/labor.go are relative to internal/.

• now() is PostgreSQL's transaction start time, taken in the transaction that writes the row.

• clean(s) (store/branches.go) makes git text storable. Each run of invalid UTF-8 bytes becomes one U+FFFD, and each NUL becomes U+FFFD. store.StoredName is the same function.

• "Fenced" means the write runs in a transaction that first proves the caller still holds its advisory locks (store/fence.go). If a lock was lost, the transaction writes nothing and returns ErrLockLost. Labor-run writes are fenced by the repository lock (aveloxis_cve.repo:<id>) and by the labor gate (aveloxis_cve.labor-gate; shared, or exclusive under --retry-uncounted) (pipeline/labor.go). The run's outcome stamp is fenced by the repository lock only (pipeline/labor.go). Sync writes are fenced by the repository lock (pipeline/sync.go). repos add/enable/disable, the --recompute request and stop write unfenced (store/fence.go).

• ToolVersion is db.ToolVersion (db/version.go) of the binary that wrote the row.

---

tracked_repos

One row is one Aveloxis repository that the operator chose to track. The key is repo_id (PK tracked_repos_pkey). It holds Aveloxis's aveloxis_data.repos.repo_id by value only, with deliberately no foreign key (db/migrations/0001_init.sql). repos add inserts rows. Adding again re-enables the row and refreshes its URL and tool version. repos enable / repos disable flip enabled. Every sync (also inside run) stamps the sync outcome, and every labor run (also inside run) stamps the labor outcome. Nothing in the code ever deletes a row. Disabling a repository only drops it from the default selection, and its branches, commits and windows are kept. Readers:

• repos list and status (store/store.go, store/status.go);

• doctor, enabled rows only (cli/cli.go);

• sync, resolve, labor and run, through selectTracked (cli/sync.go). With no --repo-id, these select every enabled row. With --repo-id, any tracked id is accepted, disabled or not.

Column	Type	Null	Source	How it is derived / written
repo_id	bigint	NO	aveloxis_data.repos.repo_id	Comes from the Aveloxis row found by repos add, using --repo-id (source.ReposByIDs, source/source.go), --url or --url-file (one URL per line, - for stdin, repeatable; cli/urlfile.go), each URL looked up by source.RepoByURL, --repo-group-id (source.ReposByGroup) or --query (source.ReposByQuery, run in a READ ONLY transaction with a 60s statement timeout). The ids are deduplicated (cli/cli.go, dedupRepos) and inserted by store.TrackRepos (store/store.go). Any --repo-id missing from Aveloxis, or any --url or --url-file URL it does not have, aborts the add ("nothing added", cli/cli.go); a --url-file error names the file and line. A repository that is not ready (gone, never collected, or no commits) is still added, with a warning (cli/cli.go).
repo_git	text	NO	aveloxis_data.repos.repo_git	The value stored in Aveloxis, verbatim. For --url, the operator's URL is only used to find the row. A unique row whose repo_git equals the operator's URL with surrounding spaces, tabs, CRs and LFs removed (source.URLTrim; other whitespace, such as a no-break space, is kept) wins. Otherwise both URLs are normalised (the same surrounding characters, then one trailing /, then one trailing .git removed) and a unique exact match wins, then a unique case-insensitive one. Several matches fail the add as ambiguous, none as not found (RepoByURL, source/source.go). The column is written on insert and overwritten on re-add (ON CONFLICT (repo_id) DO UPDATE ... repo_git = EXCLUDED.repo_git, store/store.go). It is informational only. sync clones from the CURRENT aveloxis_data.repos.repo_git, which it re-reads every run (pipeline/sync.go), not from this column.
enabled	boolean	NO (default true)	operator (CLI)	TRUE on insert (column default) and on re-add (store/store.go). Set by repos enable / repos disable --repo-id ... through store.SetEnabled (store/store.go). That command changes all the given ids or none: if any id is untracked, it changes nothing.
added_at	timestamptz	NO (default now())	wall clock (DB)	The column default, set once on the first insert. A re-add does not change it.
last_sync_at	timestamptz	YES	wall clock (DB)	store.RecordSync (store/branches.go), called at the end of every sync run of the repository (pipeline/sync.go). On success it is set to now(); on failure it keeps its old value. It is NULL until the first successful sync.
last_sync_error	text	YES	error text	Written by the same statement. On failure it is clean(err.Error()) of the run's error (for example "repo N: repository not ready in Aveloxis: " when Aveloxis has not finished a collection, pipeline/sync.go, or an interrupt-prefixed error). Success sets it to NULL; a sync whose only problem is a failed git maintenance of its clone (a printed warning) succeeds and sets it to NULL. A run skipped as busy (the repository lock, or its clone's lock, not taken) or a failed attempt at the repository lock records nothing (pipeline/sync.go). A failed attempt at the clone's lock (a clone_dir fault) is recorded, since the repository lock is held. The write is fenced by the repository lock and uses a context that ignores cancellation, with a 30s timeout (store/branches.go). If the lock was lost, the outcome is not recorded.
last_labor_at	timestamptz	YES	wall clock (DB)	store.RecordLabor (store/labor.go), called at the end of every per-repository labor run (pipeline/labor.go) by labor and run. On success it is set to now(); otherwise it is unchanged.
last_labor_error	text	YES	error text	Written by the same statement: clean(err.Error()), or NULL on success. Some errors the CLI prints as "skipped" are still recorded here: ErrNotSynced ("no complete default-branch membership", no clone) and ErrNoDatedLanding (pipeline/labor.go, cli/sync.go). Nothing is recorded in these cases: busy (the repository lock, or its clone's lock, not taken), a failed attempt at the repository lock, a labor gate that cannot be taken or is lost before the repository starts, or not started after an interrupt (pipeline/labor.go). The write is fenced by the repository lock only (pipeline/labor.go), so a run that lost only the gate still records why it stopped.
tool_version	text	NO	db.ToolVersion	The version of the binary that last ran repos add for this row: set on insert and on re-add (store/store.go). Sync, labor, enable and disable do not update it.

Notes:

• RecordSync and RecordLabor use the expression CASE WHEN $2 IS NULL THEN now() ELSE last_*_at END. So last_*_at means "last success", and last_*_error describes the latest recorded attempt.

• run stamps both outcomes. Labor runs only for repositories whose sync succeeded (cli/sync.go).

---

schema_migrations

This is the migration ledger. One row is one embedded migration file internal/db/migrations/NNNN_name.sql applied to this database. The key is version (PK). Only init-db and migrate write it (cli/cli.go calls db.Migrate and db.Upgrade, db/migrate.go). Upgrade (migrate) first checks, under the lock below, that this table exists; without it, it returns db.ErrNotInitialized and creates nothing. Both refuse a ledger that holds a version newer than any this binary embeds (db.ErrSchemaNewer), applying nothing. Both wait for the session advisory lock aveloxis_cve.migrate by polling pg_try_advisory_lock every 250 ms, never with a blocking wait (db/migrate.go). Migrate alone creates the schema if absent, and creates this table with CREATE TABLE IF NOT EXISTS (db/migrate.go); Upgrade never runs either statement. The ledger table is not created by a migration file. Both then apply each migration whose version is not in the table, in version order. Each migration and its ledger row commit in one transaction (db/migrate.go). Rows are never updated or deleted. Readers:

• doctor compares the highest applied version with the highest embedded version (db.AppliedVersions, db/migrate.go; cli/cli.go);

• Migrate and Upgrade, to skip versions already applied and to refuse a ledger newer than the binary (db.ErrSchemaNewer, db/migrate.go).

Column	Type	Null	Source	How it is derived / written
version	integer	NO	migration file name	The NNNN prefix, parsed by the regex ^(\d{4})_([a-z0-9_]+)\.sql$ (db/migrate.go). Duplicate versions are refused at load. Inserted by applyOne with ON CONFLICT (version) DO NOTHING (db/migrate.go).
name	text	NO	migration file name	The part after NNNN_, without .sql (for example labor, needs_recompute).
applied_at	timestamptz	NO (default now())	wall clock (DB)	The column default: the start time of the migration's transaction.
tool_version	text	NO	db.ToolVersion	The version of the binary that applied that migration (db/migrate.go). Rows applied by different releases carry different versions.

Notes:

• The table records only versions 1 to 19 (0001 to 0019) at this tool version. It does not checksum the SQL, so an edited migration file is not detected.

---

labor_recompute_requests

One row is one pending or past request to recompute the labor windows of one repository for one window configuration. The key is (repo_id, window_days, step_months) (PK). It was added in migration 0018 (db/migrations/0018_needs_recompute.sql). The row is created or updated by labor --recompute, and by labor --retry-uncounted, which implies a recompute. These commands only record the request (requested goes up by 1; store.MarkRecompute, store/labor.go). --recompute takes no lock to record it. --retry-uncounted records it while holding the labor gate exclusively, after clearing the cache's uncounted rows. Neither marks a window. The request is recorded for every selected repository before any repository runs (cli/sync.go). Each later labor run (labor or run) applies a pending request, under the repository's lock, to the default branch it reads (store.ApplyRecompute, store/labor.go, called at pipeline/labor.go). That marks the configuration's windows needs_recompute and sets applied = requested, in one transaction. Rows are never deleted. Only labor runs read the table, through the UPDATE in ApplyRecompute.

Column	Type	Null	Source	How it is derived / written
repo_id	bigint	NO	CLI selection	Each distinct id selected by labor: the --repo-id values (which must be tracked), or every enabled tracked repository (cli/sync.go, selectTracked). With --retry-uncounted it is always every enabled repository, since --repo-id is refused (cli/sync.go).
window_days	integer	NO	config labor.window_days (default 60, config/config.go)	The window length configured when the request is made (pipeline/labor.go). A request is applied only by a run that uses the same configuration.
step_months	integer	NO	config labor.step_months (default 1)	As for window_days.
requested	bigint	NO (default 0)	request counter	Set to 1 on the first request. Each later request runs requested = requested + 1 (store/labor.go, ON CONFLICT (repo_id, window_days, step_months) DO UPDATE). Rows are taken in id order, so two requests cannot deadlock. The write is unfenced.
applied	bigint	NO (default 0)	request counter	ApplyRecompute runs UPDATE ... SET applied = requested WHERE ... AND requested > applied (store/labor.go). In the same transaction it marks every window of that repository, branch and configuration (store/labor.go). The write is fenced by the repository lock and the gate. The request is consumed even when no window exists yet.

Notes:

• A request is pending while requested > applied. A request made before a run's ApplyRecompute is applied by that run. One made after it (waiting on the row lock if it overlaps that transaction) counts past applied and stays pending for the next run (store/labor.go). That next run may be needed because the earlier run may have measured before the request.

• In a run, InvalidateWindows (dirty history) and DeleteWindowsNotIn (plan and count scheme) run first. ApplyRecompute runs next, then DoneWindows reads which windows are done (pipeline/labor.go). A run that fails before that point leaves the request pending. Example failures: not synced, no clone, scc --version failing, graph load, or lock checks.

• A request for a repository that never runs labor again, for example one disabled and not named with --repo-id, stays pending indefinitely.

---

stop_requests

One row is one stop request, made by aveloxis-cve stop (store.RequestStop, store/stop.go; cli/sync.go). The key is request_id (PK). It was added in migration 0019 (db/migrations/0019_stop_requests.sql). The write takes no advisory lock and is unfenced. Requests are recorded one at a time: RequestStop locks the table IN SHARE ROW EXCLUSIVE MODE before its INSERT. That waits for any request being recorded, so ids commit in the order they are taken, and a request with a smaller id cannot commit after a command read a larger one as its marker. Markers and checks only read, and never wait. Rows are never deleted. They hold no personal data.

Who reads it. Every sync, labor, resolve and run of 0.4.32 or later.

• Its stop marker. Once connected, each reads the highest request_id recorded so far (store.StopMarker).

• The check. Before each repository, it asks whether a later request_id exists (store.StopRequestedAfter, cli/sync.go, stopCheck).

• Ids, not clock times. A step of the database server's clock cannot hide a request.

• Where it runs. The check runs wherever the command starts a repository: the dispatch loops of SyncIDs and RunIDs (pipeline/sync.go, pipeline/labor.go), and the resolve loops of resolve and run (cli/sync.go).

When there is a request:

• The repositories in progress finish and record their outcomes. The command starts no other repository.

• It reports the rest as not started: stop requested (pipeline.ErrStopped) and exits 0 unless something failed.

• resolve --all fails instead, naming every repository it did not re-resolve, including those skipped as busy.

Requests that do not stop a command:

• one recorded before the command read its marker, which includes a command still connecting;

• any request, for a binary older than 0.4.32, which never checks.

Signals. A signal during a check is an interruption, whatever the check found.

When the check fails. A check that fails is not "no request". The command starts nothing more and fails, naming the check (pipeline.ErrStopCheck). run then starts neither resolve nor labor. When the table is missing, as on a schema before 0019, the error says to run migrate.

Column	Type	Null	Source	How it is derived / written
request_id	bigint	NO	identity	GENERATED ALWAYS AS IDENTITY. Commands compare it with their stop marker (request_id > marker; the primary key serves the lookup). The INSERT names ON CONFLICT (request_id) DO NOTHING (store/stop.go). stop prints it.
requested_at	timestamptz	NO (default now())	database clock	The default, now() of the INSERT's transaction. It is for people only: stop prints it, and no code compares it.
tool_version	text	NO	ToolVersion	The version of the binary that ran stop.

repo_labor_windows

One row is one complete sliding window on one repository's default branch, for one window configuration. The row holds:

• the window's bounds;

• the snapshot commits at the window's start and end;

• activity counts;

• totals of the full scc snapshot at the end.

The surrogate key is window_id (identity PK). The natural key is repo_labor_windows_key UNIQUE (repo_id, branch_name, window_days, step_months, window_end). A check enforces window_start < window_end.

The row is written only by labor runs (labor, and the labor step of run) in store.WriteWindow (store/labor.go, called at pipeline/labor.go). One transaction writes the window, its repo_labor_rolling rows and its repo_labor_rolling_changed rows, so a window is complete or absent. Rows are never updated in place, except for the needs_recompute flag.

A stale window is marked needs_recompute = TRUE and kept until a later run replaces it. Replacement deletes the marked row and inserts a new one, with a new window_id and computed_at, in that write's transaction (store/labor.go).

Rows are deleted only in two cases:

• replacement, as above;

• DeleteWindowsNotIn (store/labor.go, called at pipeline/labor.go). It deletes windows of the run's branch and configuration whose window_end is not in the current plan, for example after a re-rooted history or a moved first landing.

Deletes cascade to repo_labor_rolling and repo_labor_rolling_changed (FK ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED).

Some windows are never touched by labor: windows of other configurations, except the dirty-history mark, which covers every configuration; windows of a former default-branch name; windows of untracked repositories; and windows of disabled repositories, unless one is named with --repo-id. Readers:

• labor itself (DoneWindows, store/labor.go): windows with NOT needs_recompute count as done, mapping window_end to end_sha;

• external consumers. No other command reads this table.

Column	Type	Null	Source	How it is derived / written
window_id	bigint	NO	identity	GENERATED ALWAYS AS IDENTITY. A replaced window gets a new id.
repo_id	bigint	NO	tracked_repos.repo_id (Aveloxis repos.repo_id by value)	The repository the labor run was for (pipeline/labor.go).
branch_name	text	NO	branch_repos.branch_name of the default branch	DefaultBranchState (store/labor.go) returns the branch row with is_default set, not archived, whose stored membership is complete for its tip (membership_head_sha = head_sha) and uses the current MembershipScheme. Otherwise it returns ErrNotSynced and no window is written. The default branch is the forge's, from ls-remote --symref, recorded by sync. The name is stored with clean() (store/labor.go).
window_days	integer	NO	config labor.window_days (default 60, >= 1, config/config.go)	The length of every window in days (pipeline/labor.go).
step_months	integer	NO	config labor.step_months (default 1, >= 1)	Consecutive window ends are this many calendar months apart.
window_start	timestamptz	NO	derived	window_end minus window_days days (labor/windows.go). For the first window it equals the anchor F (see window_end). Membership semantics: a commit is in a window when start < landed_at <= end. The first window is the closed interval [start, end], so the first commit counts (store/labor.go).
window_end	timestamptz	NO	derived from branch_commits.landed_at + wall clock	Computed by labor.Plan (labor/windows.go). Anchor F = the earliest default-branch landed_at after the epoch, among commits whose landing commit (landed_via) has valid dates. Only when nothing lands after the epoch does an epoch landing count, and then only if its landing commit was really committed at the epoch (DefaultBranchState, store/labor.go). So a placeholder root dated at the epoch does not anchor decades of windows. F is returned as UTC. If there is no such landing, the result is ErrNoDatedLanding: the run computes nothing and leaves any stored windows as they are. The first end is end0 = F + window_days days. End k = end0 + k*step_months months, always computed from end0, with the day clamped to the target month's length (Jan 31 + 1 month = Feb 28/29, labor/windows.go). Only complete windows are planned (end <= now, where now is the run's time.Now(), pipeline/labor.go). A partial current window is never stored.
start_sha	text	YES	git first-parent chain + branch_commits.landed_at	fpAt (pipeline/labor.go) walks first parents back from the default-branch tip (branch_repos.head_sha). It returns the newest commit whose chain time is <= window_start. Chain time is that commit's stored landed_at for on_first_parent rows (store.ChainLandings, store/labor.go), so labor and membership agree. For the first window the test is strict (< start), so start_sha is normally NULL there. It is non-NULL only if a commit landed before F, for example a synthetic epoch landing. It can only be NULL for the first window, since later starts are after F.
end_sha	text	NO	same	The same rule applied to window_end, non-strict (<=). If no first-parent commit qualifies, the run fails with "no first-parent commit at or before it" and writes no window (pipeline/labor.go). The snapshot (repo_labor_rolling) is the tree of this commit.
same_as_previous	boolean	NO	derived	TRUE when end_sha equals the end_sha of the previous window in the plan, meaning no first-parent commit landed between the two ends (pipeline/labor.go). The previous window's end_sha is either the one computed earlier in this run or the stored one of a done window. It is FALSE for the first planned window. When TRUE and the previous window was computed in this run, the snapshot rows are reused instead of re-measured.
commits_in_window	integer	NO	aveloxis_cve.branch_commits	count(*) of the default branch's membership rows with landed_at in (start, end], or [start, end] for the first window (store.WindowActivity, store/labor.go). Every commit reachable on the branch counts, not only first-parent ones: side-branch commits land with their merge. Merges themselves count too. Windows overlap when window_days exceeds the step (60 days vs 1 month by default), so a commit usually counts in two consecutive windows.
files_changed	integer	NO	derived	len(changed): the number of repo_labor_rolling_changed rows of the window (store/labor.go). That is the number of distinct paths in commit_files of the window's commits, including paths the scc walk skips or scc does not count.
total_files	integer	NO	derived	len(files): the number of repo_labor_rolling rows of the window, meaning files the walk reaches that scc counted (store/labor.go).
total_lines	bigint	NO	scc Lines	The sum of repo_labor_rolling.total_lines over the window's snapshot rows, computed in Go before the insert (store/labor.go).
code_lines	bigint	NO	scc Code	Sum of the snapshot rows' code_lines.
comment_lines	bigint	NO	scc Comment	Sum of the snapshot rows' comment_lines.
blank_lines	bigint	NO	scc Blank	Sum of the snapshot rows' blank_lines.
code_complexity	bigint	NO	scc Complexity	Sum of the snapshot rows' code_complexity.
scc_version	text	NO	scc --version	The last whitespace field of the output (for example 3.7.0, labor/scc.go). It is read once per repository run (pipeline/labor.go). This is the raw version. The cache key in scc_blob_cache.scc_version adds +isolated1. Upgrading scc does not recompute stored windows: a series can mix scc versions until labor --recompute remeasures it.
computed_at	timestamptz	NO (default now())	wall clock (DB)	The column default: the start time of the WriteWindow transaction. A replacement gets a new value.
tool_version	text	NO	db.ToolVersion	The binary that wrote the window (store/labor.go).
count_scheme	text	NO	constant labor.CountScheme	Names which files a snapshot counts (labor/walk.go). The current value is scc-walk3: scc 3.7.0's checkout walk, with symlinked control files followed through labor.Links, up to MaxLinkHops (31 on macOS, 40 on Linux, labor/walk.go). Older values: tracked (every tracked regular file, 0.4.9 and earlier, backfilled by migration 0014), scc-walk1 (0.4.10, symlinked control files not followed), scc-walk2 (0.4.11, 32 hops on macOS). Migration 0014 set and then dropped the column default, so every insert must supply it, and WriteWindow refuses an empty value (store/labor.go). Windows of any other scheme are marked needs_recompute by the next run of that branch and configuration (store/labor.go).
needs_recompute	boolean	NO (default false)	derived (stale mark)	FALSE on insert. It is set TRUE, never cleared in place, by: (1) InvalidateWindows (store/labor.go, called at pipeline/labor.go). This marks windows of the branch, in every configuration, with window_end >= branch_repos.labor_dirty_since, then clears that mark if it is unchanged. Sync sets labor_dirty_since to the earliest landing a membership write changed (store/branches.go), and resolve sets it when a commit's resolved contributor changes (store/branches.go). (2) DeleteWindowsNotIn, for a different count_scheme. (3) ApplyRecompute, for a pending --recompute request. A marked window is not done (DoneWindows skips it), and the next labor run replaces it. Current windows only: WHERE NOT needs_recompute.

Notes:

• Nothing here has a column-level counterpart in Aveloxis. The totals of the latest window correspond to the sum of aveloxis_data.repo_labor for the same commit. The code claims this match, and CHANGELOG 0.4.x reports one real repository at 163 files, 16,941 code lines and complexity 277 (CHANGELOG.md). repo_labor holds only Aveloxis's latest snapshot (older ones move to aveloxis_data.repo_labor_history, which aveloxis-cve does not read) and records no commit id. A comparison must therefore pick the window whose end_sha is the default-branch commit at Aveloxis's clone date.

• Changing labor.window_days or labor.step_months starts a new series. The old configuration's windows stay, never deleted or recomputed by labor. They can still be marked by InvalidateWindows, which ignores the configuration.

• If the default branch is renamed, the old name's windows stay as they were.

• With Replace, the stored row is deleted only when marked. If an unmarked row with the same key exists, the insert reports ErrWindowExists and the run fails (store/labor.go).

• count_scheme names the walk rules but not the host OS. scc-walk3 rows written on macOS and on Linux can differ for repositories with chains of 32 to 40 symlinks.

---

repo_labor_rolling

One row is one file of a window's full-repository snapshot at end_sha: a file that scc's checkout walk reaches and that scc counts, with scc's measurement. The key is (window_id, file_path) (PK). FK window_id references repo_labor_windows, ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED. The row is inserted by COPY in store.WriteWindow (store/labor.go), in the window's transaction. It is never updated, and it is deleted only by cascade when its window is replaced or deleted. No command reads it. It is output for consumers.

Which files a snapshot counts (pipeline/labor.go):

1. git ls-tree -r -z --full-tree <end_sha> lists regular blobs only (modes 100644 and 100755). Symlinks (120000) are listed separately and never measured. Submodule gitlinks and other modes are dropped (gitx/tree.go).

2. labor.Skipped emulates the walk of scc 3.7.0 (gocodewalker, with its go-gitignore matcher at the version scc 3.7.0 pins) on a checkout of that tree (labor/walk.go, used by pipeline/labor.go):

   • hidden files are included;

   • .gitignore, .ignore and .sccignore rules of the file's directory and every ancestor apply, in that order, and the last match decides;

   • .gitmodules path = entries skip directories;

   • directories named .git, .hg or .svn are skipped;

   • a skipped directory is not entered.

   Control-file contents come from the clone's blobs, capped at 16 MiB and cut back to the last whole line (pipeline/labor.go). A control file that is a symlink is read through the link (labor.Links), except .gitmodules (labor.FollowedLink, labor/walk.go).

3. Each remaining file is looked up in, or measured into, scc_blob_cache by (blob, base name). A file scc does not count (counted = false) gets no row.

4. Rows are deduplicated by stored path. If two raw paths sanitise to the same text, the first is kept (pipeline/labor.go).

Column	Type	Null	Source	How it is derived / written
window_id	bigint	NO	repo_labor_windows.window_id	The id returned by the window's INSERT in the same transaction (store/labor.go).
file_path	text	NO	git tree path at end_sha	The full path from the repository root, with / separators, as ls-tree --full-tree gives it, passed through clean() (pipeline/labor.go, store/labor.go).
file_name	text	NO	derived	The part of file_path after the last /, through clean() (baseName, store/labor.go).
blob_sha	text	NO	git blob id	The blob object id of the file in the end_sha tree (pipeline/labor.go). It is not in Aveloxis's repo_labor.
programming_language	text	NO	scc JSON Files[].Language (fallback: the language group's Name)	From scc_blob_cache.programming_language (labor/scc.go).
total_lines	integer	NO	scc Lines	From the cache (store/labor.go) or a fresh measurement.
code_lines	integer	NO	scc Code	Same.
comment_lines	integer	NO	scc Comment	Same.
blank_lines	integer	NO	scc Blank	Same.
code_complexity	integer	NO	scc Complexity	Same.
tool_source	text	NO (default 'aveloxis-cve-scc')	constant	Never supplied by the COPY (store/labor.go), so it is always the column default aveloxis-cve-scc. Aveloxis's rows carry tool_source = 'aveloxis-scc' (its writer, ReplaceRepoLaborSnapshot, sets it; the column default 'aveloxis' is not used).

Notes (parity with aveloxis_data.repo_labor):

• The column names and meanings match repo_labor's per-file columns: file_path, file_name, programming_language, and the five line and complexity counts. Aveloxis's file_path is scc's Location relative to its checkout, and its file_name is filepath.Base(Location) (../aveloxis/internal/collector/analysis.go, relative to the repository root).

• These repo_labor columns have no counterpart here: repo_labor_id (its key), repo_id (join window_id to repo_labor_windows instead), repo_clone_date, rl_analysis_date, repo_url, tool_version, data_source and data_collection_date.

• Aveloxis runs scc once over a checkout. Here each file is measured alone, by name and content (--no-gitignore --no-ignore --no-scc-ignore), and the walk decides beforehand which files count. CLAUDE.md reports that, after the 0.4.10 recompute, the latest windows of redis and repos 132 and 281 equal repo_labor per file on "all 7 columns" (redis 1538/312685/51652), and that three historical snapshots equal a real scc run on git archive. Which 7 columns were compared is not stated in the code or changelog.

• For paths with invalid UTF-8, clean() replaces each run of bad bytes with one U+FFFD. Aveloxis's paths reach repo_labor with one U+FFFD per invalid byte: its encoding/json decoding of scc's output (streamSCCLabor, ../aveloxis/internal/collector/analysis.go) replaces each one, as its pgx tracer (safeUTF8, defined in ../aveloxis/internal/db/safe_utf8.go and called from utf8_tracer.go) would. So a path with a run of two or more invalid bytes differs between repo_labor_rolling and repo_labor.

---

repo_labor_rolling_changed

One row is one file path touched by at least one commit that landed on the default branch in the window. The row compares the path at the window's start snapshot (start_sha) and end snapshot (end_sha) and gives the window's activity on it. The key is (window_id, file_path) (PK), with FK window_id to repo_labor_windows (cascade, deferred) and a CHECK on change_type IN ('A','D','M','N','X'). Rows are built by Labor.changed (pipeline/labor.go) and inserted by COPY in store.WriteWindow (store/labor.go), in the window's transaction. They are never updated and are deleted only by cascade. No command reads them.

Activity comes from store.WindowActivity (store/labor.go). It joins:

• branch_commits of the default branch, with landed_at in (start, end], or [start, end] for the first window;

• commits;

• commit_files, which is sync's git log --no-renames --numstat of each non-merge commit against its parent. A rename is a delete plus an add. Merges have no files. Binary files have NULL counts (db/migrations/0002_branches.sql, gitx/log.go).

The rows are grouped by commit_files.file_path.

Column	Type	Null	Source	How it is derived / written
window_id	bigint	NO	repo_labor_windows.window_id	The id from the same transaction.
file_path	text	NO	commit_files.file_path (stored, cleaned)	Each distinct path in the window's activity, in sorted order (pipeline/labor.go). Tree entries are matched to it by stored name.
change_type	text	NO	derived from git trees	Decided on the raw trees at start and end (regular files only), not on the walk (pipeline/labor.go). A: absent at start, present at end. D: present at start, absent at end. N: present at both with the same blob (touched but net unchanged). M: present at both with different blobs. X: absent at both (created and removed within the window). X also covers a path that is a symlink or submodule at both ends, since those are not "present" as regular files. When start_sha is NULL (normally the first window), the start tree is empty, so touched paths present at the end are A.
start_blob_sha	text	YES	git blob id	The blob at the path in the start_sha tree. NULL when the path is absent there (pipeline/labor.go). Set even when the walk skips the file.
end_blob_sha	text	YES	git blob id	The blob in the end_sha tree. NULL when absent.
programming_language	text	YES	scc language (cache)	The end measurement's language if the end file is walked and counted, else the start's (pipeline/labor.go). NULL when neither side is measured: skipped by the walk, not counted by scc, or absent.
start_code_lines	integer	YES	scc Code of the start blob	Set only if the start file is reached by the start tree's walk and counted by scc (pipeline/labor.go). Otherwise NULL.
end_code_lines	integer	YES	scc Code of the end blob	Same rule, for the end tree.
start_code_complexity	integer	YES	scc Complexity of the start blob	Same as start_code_lines.
end_code_complexity	integer	YES	scc Complexity of the end blob	Same as end_code_lines.
delta_code_lines	integer	YES	derived	Both sides measured: end - start. Only the end measured and the path absent at start: end. Only the start measured and the path absent at end: -start. Otherwise NULL, for example when the path is present at both ends but one side is walk-skipped or not counted (pipeline/labor.go).
delta_code_complexity	integer	YES	derived	The same rule as delta_code_lines, using complexity.
lines_added	integer	NO	commit_files.lines_added	COALESCE(sum(lines_added), 0) over the window's commits touching the path. Binary (NULL) counts add 0 (store/labor.go).
lines_removed	integer	NO	commit_files.lines_removed	The same, for lines removed.
commits	integer	NO	branch_commits / commit_files	count(DISTINCT commit_id) of the window's commits that touch the path. Merges never count here, since they have no commit_files rows.
distinct_contributors	integer	NO	commits.cntrb_id, commits.author_email	count(DISTINCT COALESCE(cntrb_id::text, lower(author_email))) (store/labor.go). Each commit counts as its resolved Aveloxis contributor, the author's (not the committer's) cntrb_id that sync and resolve set. If unresolved, it counts as the lowercased author e-mail. One person with both resolved and unresolved commits counts twice. A later change of a commit's resolution marks the affected windows for recompute through labor_dirty_since (store/branches.go).

Notes:

• A file that the walk skips, or that scc does not count, still gets a row, with blobs and activity but NULL metrics (Labor.changed, pipeline/labor.go).

• Start and end metrics come from the same scc_blob_cache as the snapshot, so they equal the snapshot values for the same blob and name.

---

scc_blob_cache

One row is one scc measurement of one file content under one file name, for one scc version and presentation scheme. The key is (blob_sha, file_name, scc_version) (PK). The CHECK scc_blob_cache_counted_consistent requires counted = (programming_language IS NOT NULL). Rows are written by labor runs (labor, and run's labor step) in store.PutMetrics (store/labor.go, called at pipeline/labor.go) after each scc batch, with ON CONFLICT (blob_sha, file_name, scc_version) DO NOTHING. The write is fenced by the repository lock and the labor gate, because the cache is shared by every repository. Rows are never updated. The only delete is labor --retry-uncounted. Holding the labor gate exclusively, it deletes the counted = false rows of the current cache version (store.DeleteUncounted, store/labor.go; cli/sync.go), and then requests a recompute of every enabled repository. Rows of other scc versions are never deleted. Only labor reads the cache (store.CachedMetrics, store/labor.go), for both the snapshot and the changed-file metrics.

How a miss is measured (pipeline/labor.go):

• Each file not yet in the cache for its (blob, name, version) key is measured once per run.

• Its blob is streamed from the clone (git cat-file --batch) to its own scratch file <n>/<name> in a temp directory. Content and name are all scc sees, which is exactly the cache key.

• scc runs once per batch of scc.batch_files files (default 2000): scc -f json --by-file --no-cocomo --no-gitignore --no-ignore --no-scc-ignore . (labor/scc.go). It honours scc.path, scc.timeout_seconds (default 1800) and scc.gomaxprocs (default 2, set as GOMAXPROCS).

• A failing batch is bisected until the failing file is alone (pipeline/labor.go).

Column	Type	Null	Source	How it is derived / written
blob_sha	text	NO	git blob id	The blob from ls-tree of the snapshot or the start tree (pipeline/labor.go).
file_name	text	NO	git tree path	clean(path.Base(raw path)): the base name only, since scc classifies by name (pipeline/labor.go, store/labor.go). One blob under two names makes two rows. For names over 200 bytes, the scratch file scc measures is cut to 200 bytes and keeps an extension of up to 32 bytes (scratchName, pipeline/labor.go). The cached measurement is then of the shortened name, while the key keeps the full name.
scc_version	text	NO	scc --version + constant	CacheVersion(v) = v + "+isolated1", for example 3.7.0+isolated1 (pipeline/labor.go). The suffix versions how files are presented to scc: alone, by name, with ignore files disabled. Which files a snapshot counts is versioned separately, by count_scheme.
counted	boolean	NO	scc output	TRUE when scc's JSON output listed the scratch file, meaning scc classified it (pipeline/labor.go). FALSE in two cases. (a) scc did not list it: the language is not recognised, for example many binary or extensionless files. (b) The file is a "poison" file: scc failed deterministically on it alone, twice (exit status 1 to 127, not a signal, timeout or cancel), and a known-good control file still measures (pipeline/labor.go). A transient failure fails the run: the failing batch caches nothing, and batches measured before it stay cached. A FALSE row means "do not count, do not re-measure" until --retry-uncounted.
programming_language	text	YES	scc JSON Files[].Language (fallback: the group Name)	NULL exactly when counted is FALSE (the CHECK).
total_lines	integer	YES	scc Lines	NULL when not counted (store/labor.go).
code_lines	integer	YES	scc Code	Same.
comment_lines	integer	YES	scc Comment	Same.
blank_lines	integer	YES	scc Blank	Same.
code_complexity	integer	YES	scc Complexity	Same.

Notes:

• The key does not include the repository, so a blob and name measured for one repository is reused for every other.

• Upgrading scc produces a new scc_version key, so every file is measured again under it. Old rows remain. Stored windows are not recomputed by the upgrade (see repo_labor_windows.scc_version).

• A cached measurement does not depend on the count scheme. Changing count_scheme recomputes windows but rescans nothing (CHANGELOG 0.4.10).

• --retry-uncounted also re-measures files that scc legitimately does not classify. Those come back FALSE again (docs/configuration.md).