Store git commit metadata (author, parents) for hashes we already ingest

[felipebergamin]

Context

checkouts stores CI submissions, not git objects. For each checkout we currently keep:

• git_commit_hash / git_commit_name / git_commit_tags
• git_commit_message (often empty; submitters rarely send it)
• git_repository_url / git_repository_branch

KCIDB has no author, committer, or parent fields, so we cannot get this from ingestion. We also have no parent chain, which is why #1958 / #2054 had to approximate “next checkout” with start_time.

The same git_commit_hash can appear on many checkouts (origins, labs). Author / parents are properties of the git object, not of a checkout row. Storing them only on checkouts would duplicate data and go stale independently per origin.

Goal

Persist git metadata for commits we already see in checkouts, filled from the real repositories (not from KCIDB submissions).

Store at least

• author name, email, date
• committer name, email, date
• parent hashes (ordered; first parent = mainline parent on merges)
• subject (first line of the message)
• backfill git_commit_message when we have it from git and the checkout row is null

Nice to have later: tree hash, merge flag (array_length(parents) > 1).

Proposed shape

New commits table keyed by git_commit_hash (the object is globally unique). Keep a fetched_from_url of the repo we used. Join from checkouts.git_commit_hash.

Do not add these columns onto checkouts.

Filling it

Background command / cron, incremental:

1. Distinct hashes in checkouts that are missing from commits
2. Resolve a fetch URL from an existing checkout (prefer a known-good tree URL; same mapping treeproof already uses)
3. git cat-file / git log -1 --format against a local mirror (fetch, do not clone from scratch every run)
4. Upsert; skip / log hashes we cannot resolve (bad URL, object not on that remote)

Must tolerate missing objects, private remotes, and the malformed git_repository_url cases from #1952 / #1953 without crashing the job.

Why this is useful

• Real first-parent history instead of ordering checkouts by start_time
• Author on tree / commit / issue views
• Search / filter by author
• Honest merge vs first-parent walks

Out of scope

• Changing the KCIDB submission schema or waiting for origins to send this
• UI (follow-up once the table is populated)
• Rewriting treeproof / tree-name mapping (#1953)

Related

• #1958 next checkout after last seen
• #1509 reject checkouts without commit_hash

[alanpeixinho]

A few things to consider.

1. We might benefit from adding a surrogate (incremental integer) key to commits. To make it easier (and cheaper) to add many denormalized references. We could LATER have commit_id as FK columns on checkouts, builds and tests, which could help avoid some joins from builds/tests/checkouts.
2. Maybe we could also add a tree_heads table (tree_name / branch / hash), again to be referenced as a simple integer FK for larger tables.
3. I wonder if keeping a list of parents could harm some queries on the future. For detecting merge it should be fine (just checking if len > 1). But maybe moving this to a commit_parent table (id, parent_id) could be also helpful. But I am uncertain about this one.

[alanpeixinho]

@mentonin , I believe this issue might interest you, and you might have some ideas to bring to the table.

[alanpeixinho]

Opened schema sub-issue: #2089 (A — commits table + parent storage decision).

[alanpeixinho]

Opened fetch-library sub-issue: #2090 (B — SHA-only partial clone on tmpfs).