# Aveloxis Schema Documentation

*Reference documentation for the Aveloxis database schema -- the Go-based open source community health metrics collection pipeline.*

---

## Overview

Aveloxis uses two PostgreSQL schemas to separate collected data from operational state:

- **`aveloxis_data`** -- Collected open source community health data. Contains tables for repositories, contributors, issues, pull requests, commits, releases, messages, dependency information, and aggregated data mart views. These tables hold the output of collection workers that talk to GitHub, GitLab, and local git clones.

- **`aveloxis_ops`** -- Operational and orchestration tables. Contains the collection queue, staging area, API credentials, user accounts, worker state, and configuration. These tables drive the collection pipeline itself.

Both schemas maintain full parity with Augur's `augur_data` and `augur_operations` schemas. All `CREATE TABLE` statements use `IF NOT EXISTS` and inserts use `ON CONFLICT DO NOTHING` for idempotent migrations.

---

## Metadata Columns

Most tables in `aveloxis_data` include four provenance columns. They are documented once here and referenced as **"Standard metadata columns"** in the per-table documentation below.

| Column | Type | Default | Description |
|--------|------|---------|-------------|
| `tool_source` | TEXT | `'aveloxis'` | Identifies which component created the row. Values: `'aveloxis'` (API collection), `'aveloxis-facade'` (git log parsing), `'aveloxis-commit-resolver'` (commit author resolution). Used for data provenance and debugging. |
| `tool_version` | TEXT | `''` | Version of the tool that created the row. Currently empty; reserved for future use. |
| `data_source` | TEXT | `''` | Where the raw data came from. Values: `'GitHub API'`, `'GitLab API'`, `'git'`. Distinguishes API-sourced from git-sourced data. |
| `data_collection_date` | TIMESTAMPTZ | `NOW()` | Timestamp of when this row was inserted or last updated. Auto-set to `NOW()` on insert. Used to track data freshness and identify stale records. |

---

## aveloxis_data Schema

### Core Tables

#### platforms

Lookup table for supported forge platforms. Seeded on schema creation with GitHub (1), GitLab (2), and Mailing List (6 — the transport platform for mailing-list-sourced rows; see [Mailing-list ingestion](architecture/mailing-list.md)).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `platform_id` | SMALLINT (PK) | Seeded | Primary key. `1` = GitHub, `2` = GitLab, `6` = Mailing List. |
| `platform_name` | TEXT NOT NULL UNIQUE | Seeded | Human-readable platform name. |

---

#### repo_groups

Logical groupings of repositories, typically representing a project, organization, or research cohort.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_group_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `rg_name` | TEXT NOT NULL | User input | Name of the repo group (e.g., "CHAOSS", "Linux Foundation"). |
| `rg_description` | TEXT | User input | Free-text description of the group. |
| `rg_website` | TEXT | User input | URL for the group's website. |
| `rg_recache` | SMALLINT | User input | Flag indicating whether the group should be re-cached. `1` = yes. |
| `rg_last_modified` | TIMESTAMPTZ | Auto-generated | Timestamp of last modification. Defaults to `NOW()`. |
| `rg_type` | TEXT | User input | Classification of the group type. |
| | | | *Standard metadata columns* |

---

#### repos

Central repository table. Every collected repository has exactly one row here. All entity tables (issues, PRs, commits, etc.) reference `repo_id`.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_id` | BIGSERIAL (PK) | Auto-generated | Primary key, referenced by nearly every other table. |
| `repo_group_id` | BIGINT (FK -> repo_groups) | User input | The group this repo belongs to. |
| `platform_id` | SMALLINT NOT NULL (FK -> platforms) | User input | `1` for GitHub, `2` for GitLab. |
| `repo_git` | TEXT NOT NULL UNIQUE | User input | Git clone URL. Serves as the natural unique key. |
| `repo_name` | TEXT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | Short repository name (e.g., `"augur"`). |
| `repo_owner` | TEXT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | Owner or namespace (e.g., `"chaoss"`). |
| `repo_path` | TEXT | Computed | Local filesystem path to the cloned repo (used by facade). |
| `repo_description` | TEXT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}?statistics=true` | Repository description from the forge. |
| `primary_language` | TEXT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | Primary programming language. |
| `forked_from` | TEXT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | URL of the parent repo if this is a fork. |
| `repo_archived` | BOOLEAN | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | Whether the repo is archived on the forge. |
| `platform_repo_id` | TEXT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | The forge's numeric ID for this repo, stored as text for cross-platform compatibility. |
| `created_at` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | When the repo was created on the forge. |
| `updated_at` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | When the repo was last updated on the forge. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(repo_git)`

---

#### repo_groups_list_serve

Mailing list registry + per-list collection state. Each row is a list the MailingListWorker collects; the `mlls_*` columns (v0.25.7) carry the claim/checkpoint/lock state. See [Mailing-list ingestion](architecture/mailing-list.md).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `rgls_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_group_id` | BIGINT NOT NULL (FK -> repo_groups) | User input | The repo group this mailing list belongs to. |
| `rgls_name` | TEXT | User input | Mailing list name. |
| `rgls_description` | TEXT | User input | Description of the mailing list. |
| `rgls_sponsor` | TEXT | User input | Organization sponsoring the list. |
| `rgls_email` | TEXT | User input | List address (e.g. `dev@kafka.apache.org`). |
| `mlls_system` | TEXT | Registration | Archive system definition that applies (`apache_ponymail`, `lore_public_inbox`). Non-empty = collectable by the worker. |
| `mlls_last_month` | TEXT | Worker | `yyyy-mm` backfill checkpoint (resume point). |
| `mlls_scan_complete` | BOOLEAN | Worker | Partial-scan flag; FALSE → re-eligible immediately. |
| `mlls_failed_attempts` | INTEGER | Worker | Consecutive failure counter (quadratic backoff; sideline at 10). |
| `mlls_last_failed_at` | TIMESTAMPTZ | Worker | Backoff gate input. |
| `mlls_last_run` | TIMESTAMPTZ | Worker | Last successful tail-refresh. |
| `mlls_locked_at` / `mlls_locked_pid` / `mlls_locked_boot_id` | TIMESTAMPTZ / INTEGER / TEXT | Worker | `(pid, boot_id)` crash-recovery lock; a lock older than 2h is presumed dead. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(repo_group_id, rgls_email)`

---

#### email_message

A mailing-list email as a first-class entity (v0.25.7) — peer to `issues` / `pull_requests` / `pull_request_reviews`. The body lives in `messages` (linked via `email_message_ref`); this table carries the classification (Axis A) and repo association (Axis B). Declared after `issues`/`pull_requests`/`messages` in `schema.sql` because it FK-references all three. See [Mailing-list ingestion](architecture/mailing-list.md).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `email_message_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` / `repo_group_id` / `rgls_id` | BIGINT (FKs) | Worker | The repo / group / list this message came from. |
| `platform_id` | SMALLINT NOT NULL (FK -> platforms) | Worker | Always `6` (Mailing List). |
| `ml_system` | TEXT | Worker | Archive system (`apache_ponymail`, `lore_public_inbox`). |
| `message_id_header` | TEXT NOT NULL UNIQUE | Email | RFC-822 Message-ID (idempotency key). |
| `list_address` / `list_id_header` | TEXT | Email | List the message came from. |
| `subject` / `sender_email` / `sent_at` | TEXT / TEXT / TIMESTAMPTZ | Email | Parsed header fields. |
| `in_reply_to` / `references_chain` / `thread_root_id` | TEXT | Email | Threading: In-Reply-To, References, resolved thread root. |
| `has_patch` | BOOLEAN | Classifier | Body contains a patch. |
| `msg_class` | TEXT | Classifier | Axis A: `issue_event`, `patch_submission`, `review`, `github_mirror`, `commit_notify`, `vote`, `announce`, `result`, `discuss`, `support`, `unclassified`. |
| `classification_source` | TEXT | Classifier | Which rule fired (subject_regex / body_url / sender / list_id / list_address). |
| `is_mirror` / `mirrors_url` | BOOLEAN / TEXT | Classifier | Mirror-list mail that echoes GitHub activity. |
| `signaled_repo_url` | TEXT | Classifier | Axis B: canonical repo URL the message signals (captured even if not in catalog). |
| `signaled_repo_id` | BIGINT (FK -> repos, ON DELETE SET NULL) | Resolver | Resolved repo FK (NULL until the URL matches a loaded repo). |
| `linked_issue_id` / `linked_pull_request_id` | BIGINT (FKs) | Router | Routed target when the message is an issue/PR event. |
| `linked_external_key` / `linked_commit_hash` | TEXT | Router | Jira/Bugzilla key / commit the message references. |
| | | | *Standard metadata columns* (`tool_source` defaults to `Aveloxis Mailing List Collector`) |

---

#### email_message_ref

Bridge from `email_message` to the shared `messages` table (the body), mirroring `issue_message_ref` / `pull_request_message_ref`.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `email_msg_ref_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `email_message_id` | BIGINT NOT NULL (FK -> email_message) | Worker | The email entity. |
| `msg_id` | BIGINT NOT NULL (FK -> messages) | Worker | The body row in `messages`. |
| `repo_group_id` | BIGINT (FK -> repo_groups) | Worker | Group context. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(email_message_id, msg_id)`

---

### Contributors

#### contributors

Platform-agnostic contributor identity. Each unique person across GitHub and GitLab maps to one row. Contains both canonical fields and platform-specific profile data.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `cntrb_id` | UUID (PK) | Auto-generated (`gen_random_uuid()`) | Primary key. Referenced by issues, PRs, commits, etc. |
| `cntrb_login` | TEXT NOT NULL | GitHub REST: `/users/{login}`, GitLab: `/projects/{id}/members/all` | Platform login/username. |
| `cntrb_email` | TEXT | GitHub REST: `/users/{login}`, GitLab: `/projects/{id}/members/all` | Email address (may be empty if private). |
| `cntrb_full_name` | TEXT | GitHub REST: `/users/{login}`, GitLab: `/projects/{id}/members/all` | Full display name. |
| `cntrb_company` | TEXT | GitHub REST: `/users/{login}` | Company affiliation from profile. |
| `cntrb_location` | TEXT | GitHub REST: `/users/{login}` | Location string from profile. |
| `cntrb_canonical` | TEXT | Computed / `aveloxis-commit-resolver` | Canonical email used for identity merging. |
| `cntrb_type` | TEXT | GitHub REST: `/users/{login}` | Account type (e.g., `"User"`, `"Bot"`, `"Organization"`). |
| `cntrb_fake` | SMALLINT | Computed | Flag for synthetic/placeholder contributors. `1` = fake. |
| `cntrb_deleted` | SMALLINT | Computed | Soft-delete flag. `1` = deleted. |
| `cntrb_long` | NUMERIC(11,8) | Computed | Longitude from geocoded location. |
| `cntrb_lat` | NUMERIC(10,8) | Computed | Latitude from geocoded location. |
| `cntrb_country_code` | CHAR(3) | Computed | ISO 3166 country code from geocoded location. |
| `cntrb_state` | TEXT | Computed | State/province from geocoded location. |
| `cntrb_city` | TEXT | Computed | City from geocoded location. |
| `cntrb_last_used` | TIMESTAMPTZ | Computed | Timestamp of most recent activity by this contributor. |
| `gh_user_id` | BIGINT | GitHub REST: `/users/{login}` | GitHub's numeric user ID. |
| `gh_login` | TEXT | GitHub REST: `/users/{login}` | GitHub login name. |
| `gh_url` | TEXT | GitHub REST: `/users/{login}` | GitHub API URL for this user. |
| `gh_html_url` | TEXT | GitHub REST: `/users/{login}` | GitHub profile URL. |
| `gh_node_id` | TEXT | GitHub REST: `/users/{login}` | GitHub GraphQL node ID. |
| `gh_avatar_url` | TEXT | GitHub REST: `/users/{login}` | GitHub avatar image URL. |
| `gh_gravatar_id` | TEXT | GitHub REST: `/users/{login}` | Gravatar ID (legacy). |
| `gh_followers_url` | TEXT | GitHub REST: `/users/{login}` | API URL for followers list. |
| `gh_following_url` | TEXT | GitHub REST: `/users/{login}` | API URL for following list. |
| `gh_gists_url` | TEXT | GitHub REST: `/users/{login}` | API URL for gists. |
| `gh_starred_url` | TEXT | GitHub REST: `/users/{login}` | API URL for starred repos. |
| `gh_subscriptions_url` | TEXT | GitHub REST: `/users/{login}` | API URL for subscriptions. |
| `gh_organizations_url` | TEXT | GitHub REST: `/users/{login}` | API URL for orgs membership. |
| `gh_repos_url` | TEXT | GitHub REST: `/users/{login}` | API URL for user's repos. |
| `gh_events_url` | TEXT | GitHub REST: `/users/{login}` | API URL for user's events. |
| `gh_received_events_url` | TEXT | GitHub REST: `/users/{login}` | API URL for received events. |
| `gh_type` | TEXT | GitHub REST: `/users/{login}` | GitHub account type string. |
| `gh_site_admin` | TEXT | GitHub REST: `/users/{login}` | Whether user is a GitHub site admin. |
| `gl_web_url` | TEXT | GitLab API v4: `/projects/{id}/members/all` | GitLab profile web URL. |
| `gl_avatar_url` | TEXT | GitLab API v4: `/projects/{id}/members/all` | GitLab avatar URL. |
| `gl_state` | TEXT | GitLab API v4: `/projects/{id}/members/all` | GitLab account state (e.g., `"active"`). |
| `gl_username` | TEXT | GitLab API v4: `/projects/{id}/members/all` | GitLab username. |
| `gl_full_name` | TEXT | GitLab API v4: `/projects/{id}/members/all` | GitLab display name. |
| `gl_id` | BIGINT | GitLab API v4: `/projects/{id}/members/all` | GitLab numeric user ID. |
| `cntrb_created_at` | TIMESTAMPTZ | GitHub REST: `/users/{login}`, GitLab API v4 | When the account was created on the forge. |
| | | | *Standard metadata columns* |

**Unique index:** `(cntrb_login) WHERE cntrb_login != ''`

---

#### contributor_identities

Maps a contributor to per-platform identities. One `cntrb_id` may have identities on both GitHub and GitLab.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `identity_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `cntrb_id` | UUID NOT NULL (FK -> contributors) | Computed | The canonical contributor this identity belongs to. |
| `platform_id` | SMALLINT NOT NULL (FK -> platforms) | Computed | Which platform this identity is on. |
| `platform_user_id` | BIGINT NOT NULL | GitHub REST: `/users/{login}`, GitLab API v4: `/projects/{id}/members/all` | Numeric user ID on the platform. |
| `login` | TEXT NOT NULL | GitHub REST: `/users/{login}`, GitLab API v4 | Username on the platform. |
| `name` | TEXT | GitHub REST: `/users/{login}`, GitLab API v4 | Display name. |
| `email` | TEXT | GitHub REST: `/users/{login}`, GitLab API v4 | Email address. |
| `avatar_url` | TEXT | GitHub REST: `/users/{login}`, GitLab API v4 | Avatar image URL. |
| `profile_url` | TEXT | GitHub REST: `/users/{login}`, GitLab API v4 | Profile page URL. |
| `node_id` | TEXT | GitHub REST: `/users/{login}` | GitHub GraphQL node ID (empty for GitLab). |
| `user_type` | TEXT | GitHub REST: `/users/{login}`, GitLab API v4 | Account type. Default `'User'`. |
| `is_admin` | BOOLEAN | GitHub REST: `/users/{login}`, GitLab API v4 | Whether the user is a site/instance admin. |

**Unique constraint:** `(platform_id, platform_user_id)`

---

#### contributors_aliases

Maps alternate email addresses to a contributor's canonical email. Used by the commit resolver to unify git commit emails with API identities.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `cntrb_alias_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `cntrb_id` | UUID NOT NULL (FK -> contributors) | `aveloxis-commit-resolver` | The contributor this alias belongs to. |
| `canonical_email` | TEXT NOT NULL | `aveloxis-commit-resolver` | The contributor's canonical email. |
| `alias_email` | TEXT NOT NULL UNIQUE | `aveloxis-commit-resolver` | An alternate email that maps to this contributor. |
| `cntrb_active` | SMALLINT NOT NULL | `aveloxis-commit-resolver` | Whether this alias is active. `1` = active. |
| `cntrb_last_modified` | TIMESTAMPTZ | Auto-generated | Last modification timestamp. |
| | | | *Standard metadata columns* |

---

#### contributor_affiliations

Maps email domains to organizational affiliations. Used to attribute commits and activity to companies/organizations.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `ca_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `ca_domain` | TEXT NOT NULL UNIQUE | User input / Augur import | Email domain (e.g., `"redhat.com"`). |
| `ca_start_date` | DATE | User input | Date the affiliation began. Default `'1970-01-01'`. |
| `ca_last_used` | TIMESTAMPTZ | Computed | When this affiliation was last matched. |
| `ca_affiliation` | TEXT | User input | Organization name (e.g., `"Red Hat"`). |
| `ca_active` | SMALLINT | User input | Whether this mapping is active. `1` = active. |
| | | | *Standard metadata columns* |

---

#### contributor_repo

Records contributor activity events tied to specific repositories. Tracks which contributors interact with which repos and how.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `cntrb_repo_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `cntrb_id` | UUID NOT NULL (FK -> contributors) | GitHub REST: `/issues/events`, `/repos/{o}/{r}/contributors` | The contributor involved. |
| `repo_git` | TEXT NOT NULL | GitHub REST: `/repos/{o}/{r}` | Git URL of the repo. |
| `repo_name` | TEXT NOT NULL | GitHub REST: `/repos/{o}/{r}` | Repository name. |
| `gh_repo_id` | BIGINT NOT NULL | GitHub REST: `/repos/{o}/{r}` | GitHub's numeric repository ID. |
| `cntrb_category` | TEXT | Computed | Category of the contribution (e.g., event type). |
| `event_id` | BIGINT | GitHub REST: `/issues/events` | Platform event ID that triggered this record. |
| `created_at` | TIMESTAMPTZ | GitHub REST: `/issues/events` | Timestamp of the event. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(event_id, tool_version)`

---

#### contributors_old

Legacy backup table for contributor data. Holds a snapshot of contributor records before a migration or schema change. Structure mirrors the `contributors` table.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `cntrb_id` | UUID (PK) | Augur import | Primary key (not auto-generated; copied from contributors). |
| `cntrb_login` | TEXT | Augur import | Platform login. |
| `cntrb_email` | TEXT | Augur import | Email address. |
| `cntrb_full_name` | TEXT | Augur import | Full display name. |
| `cntrb_company` | TEXT | Augur import | Company affiliation. |
| `cntrb_created_at` | TIMESTAMPTZ | Augur import | Account creation timestamp. |
| `cntrb_type` | TEXT | Augur import | Account type. |
| `cntrb_fake` | SMALLINT | Augur import | Fake flag. |
| `cntrb_deleted` | SMALLINT | Augur import | Soft-delete flag. |
| `cntrb_long` | NUMERIC(11,8) | Augur import | Longitude. |
| `cntrb_lat` | NUMERIC(10,8) | Augur import | Latitude. |
| `cntrb_country_code` | CHAR(3) | Augur import | ISO country code. |
| `cntrb_state` | TEXT | Augur import | State/province. |
| `cntrb_city` | TEXT | Augur import | City. |
| `cntrb_location` | TEXT | Augur import | Location string. |
| `cntrb_canonical` | TEXT | Augur import | Canonical email. |
| `cntrb_last_used` | TIMESTAMPTZ | Augur import | Last activity timestamp. |
| `gh_user_id` | BIGINT | Augur import | GitHub user ID. |
| `gh_login` | TEXT | Augur import | GitHub login. |
| `gh_url` | TEXT | Augur import | GitHub API URL. |
| `gh_html_url` | TEXT | Augur import | GitHub profile URL. |
| `gh_node_id` | TEXT | Augur import | GitHub node ID. |
| `gh_avatar_url` | TEXT | Augur import | GitHub avatar URL. |
| `gh_gravatar_id` | TEXT | Augur import | Gravatar ID. |
| `gh_followers_url` | TEXT | Augur import | Followers API URL. |
| `gh_following_url` | TEXT | Augur import | Following API URL. |
| `gh_gists_url` | TEXT | Augur import | Gists API URL. |
| `gh_starred_url` | TEXT | Augur import | Starred API URL. |
| `gh_subscriptions_url` | TEXT | Augur import | Subscriptions API URL. |
| `gh_organizations_url` | TEXT | Augur import | Organizations API URL. |
| `gh_repos_url` | TEXT | Augur import | Repos API URL. |
| `gh_events_url` | TEXT | Augur import | Events API URL. |
| `gh_received_events_url` | TEXT | Augur import | Received events API URL. |
| `gh_type` | TEXT | Augur import | GitHub account type. |
| `gh_site_admin` | TEXT | Augur import | Site admin flag. |
| `gl_web_url` | TEXT | Augur import | GitLab web URL. |
| `gl_avatar_url` | TEXT | Augur import | GitLab avatar URL. |
| `gl_state` | TEXT | Augur import | GitLab account state. |
| `gl_username` | TEXT | Augur import | GitLab username. |
| `gl_full_name` | TEXT | Augur import | GitLab full name. |
| `gl_id` | BIGINT | Augur import | GitLab user ID. |
| | | | *Standard metadata columns* |

---

#### unresolved_commit_emails

Holds email addresses found in git commits that could not be resolved to a known contributor. The commit resolver processes these to attempt matching.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `email_unresolved_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `email` | TEXT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | The unresolved email from a commit. |
| `name` | TEXT | Git: `git log --numstat` (default branch only, per v0.16.4) | The name associated with the email in the commit. |
| | | | *Standard metadata columns* |

---

### Issues

#### issues

Issue tracker records from GitHub Issues or GitLab Issues. Each row represents one issue in one repository.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `issue_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository this issue belongs to. |
| `platform_issue_id` | BIGINT NOT NULL | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | The platform's numeric ID for this issue. |
| `issue_number` | INT NOT NULL | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | Human-readable issue number (e.g., `#42`). |
| `node_id` | TEXT | GitHub REST: `/repos/{o}/{r}/issues` | GitHub GraphQL node ID (empty for GitLab). |
| `issue_title` | TEXT | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | Issue title. |
| `issue_body` | TEXT | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | Issue body/description text. |
| `issue_state` | TEXT | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | Current state: `'open'`, `'closed'`. |
| `issue_url` | TEXT | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | API URL for the issue. |
| `html_url` | TEXT | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | Web URL for the issue. |
| `reporter_id` | UUID (FK -> contributors) | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | Contributor who opened the issue. |
| `closed_by_id` | UUID (FK -> contributors) | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | Contributor who closed the issue (null if open). |
| `pull_request` | BIGINT | GitHub REST: `/repos/{o}/{r}/issues` | Non-null if this issue is actually a PR (GitHub conflates the two). |
| `pull_request_id` | BIGINT | Computed | Foreign key to `pull_requests` if linked. |
| `created_at` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | When the issue was created. |
| `updated_at` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | When the issue was last updated. |
| `closed_at` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | When the issue was closed (null if open). |
| `due_on` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | Due date from milestone. |
| `comment_count` | INT | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | Number of comments on the issue. |
| `external_key` | TEXT | `backfill-issue-external-keys` | Bracketed `[KEY-N]` Jira/Bugzilla key from the title (Apache Jira → GitHub imports). Lets mailing-list `issue_event` mail bridge to the imported issue. Partial unique `(repo_id, external_key) WHERE external_key <> ''`. v0.25.7. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(repo_id, platform_issue_id)`

---

#### issue_labels

Labels attached to issues.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `issue_label_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `issue_id` | BIGINT NOT NULL (FK -> issues) | Computed | The issue this label is on. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository for denormalized querying. |
| `platform_label_id` | BIGINT | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | The platform's numeric ID for this label. |
| `node_id` | TEXT | GitHub REST: `/repos/{o}/{r}/issues` | GitHub GraphQL node ID. |
| `label_text` | TEXT NOT NULL | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | Label name text (e.g., `"bug"`, `"enhancement"`). |
| `label_description` | TEXT | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | Label description. |
| `label_color` | TEXT | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | Hex color code (e.g., `"fc2929"`). |
| | | | *Standard metadata columns* |

**Unique constraint:** `(issue_id, label_text)`

---

#### issue_assignees

Users assigned to an issue.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `issue_assignee_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `issue_id` | BIGINT NOT NULL (FK -> issues) | Computed | The issue. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository for denormalized querying. |
| `cntrb_id` | UUID (FK -> contributors) | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | The assigned contributor. |
| `platform_assignee_id` | BIGINT | GitHub REST: `/repos/{o}/{r}/issues`, GitLab: `/projects/{id}/issues` | Platform's numeric ID for the assignee. |
| `platform_node_id` | TEXT | GitHub REST: `/repos/{o}/{r}/issues` | GitHub GraphQL node ID. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(issue_id, platform_assignee_id)`

---

#### issue_events

Timeline events on issues (labeled, closed, assigned, referenced, etc.).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `issue_event_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `issue_id` | BIGINT NOT NULL (FK -> issues) | Computed | The issue this event occurred on. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository for denormalized querying. |
| `cntrb_id` | UUID (FK -> contributors) | GitHub REST: `/issues/events`, GitLab: `/projects/{id}/issues` | The contributor who triggered the event. |
| `platform_id` | SMALLINT NOT NULL (FK -> platforms) | Computed | Platform where the event occurred. |
| `platform_event_id` | BIGINT NOT NULL | GitHub REST: `/issues/events`, GitLab: `/projects/{id}/issues` | Platform's numeric event ID. |
| `node_id` | TEXT | GitHub REST: `/issues/events` | GitHub GraphQL node ID. |
| `action` | TEXT NOT NULL | GitHub REST: `/issues/events`, GitLab: `/projects/{id}/issues` | Event type (e.g., `"closed"`, `"labeled"`, `"assigned"`). |
| `action_commit_hash` | TEXT | GitHub REST: `/issues/events` | Commit SHA if the event references a commit. |
| `created_at` | TIMESTAMPTZ | GitHub REST: `/issues/events`, GitLab: `/projects/{id}/issues` | When the event occurred. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(repo_id, platform_event_id)`

---

#### issue_message_ref

Join table linking issues to messages (comments). Each row maps one comment to one issue.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `issue_msg_ref_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `issue_id` | BIGINT NOT NULL (FK -> issues) | Computed | The issue. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository for denormalized querying. |
| `msg_id` | BIGINT NOT NULL (FK -> messages) | Computed | The message/comment. |
| `platform_src_id` | BIGINT | GitHub (REST `/issues/comments` in rest mode; inline via GraphQL `Issue.comments` in graphql mode — see `messages` table for the per-mode source breakdown) ; GitLab `/projects/{id}/issues/{iid}/notes` | Platform's comment ID. |
| `platform_node_id` | TEXT | GitHub (REST `/issues/comments` or GraphQL inline) | GitHub GraphQL node ID. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(issue_id, msg_id)`

---

### Pull Requests

#### pull_requests

Pull requests (GitHub) or merge requests (GitLab). Each row represents one PR/MR in one repository.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pull_request_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository this PR belongs to. |
| `platform_pr_id` | BIGINT NOT NULL | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Platform's numeric PR/MR ID. |
| `node_id` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | GitHub GraphQL node ID. |
| `pr_number` | INT NOT NULL | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Human-readable PR number. |
| `pr_url` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | API URL. |
| `pr_html_url` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Web URL. |
| `pr_diff_url` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | URL to the diff. |
| `pr_title` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | PR title. |
| `pr_body` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | PR body/description. |
| `pr_state` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | State: `'open'`, `'closed'`, `'merged'`. |
| `pr_locked` | BOOLEAN | GitHub REST: `/repos/{o}/{r}/pulls` | Whether the PR conversation is locked. |
| `created_at` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Creation timestamp. |
| `updated_at` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Last update timestamp. |
| `closed_at` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Close timestamp (null if open). |
| `merged_at` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Merge timestamp (null if not merged). |
| `merge_commit_sha` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | SHA of the merge commit. |
| `author_id` | UUID (FK -> contributors) | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | PR author. |
| `author_association` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | Author's association to the repo (e.g., `"MEMBER"`, `"CONTRIBUTOR"`). |
| `meta_head_id` | BIGINT | Computed | FK to `pull_request_meta` for the head branch. |
| `meta_base_id` | BIGINT | Computed | FK to `pull_request_meta` for the base branch. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(repo_id, platform_pr_id)`

---

#### pull_request_labels

Labels attached to pull requests.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pr_label_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pull_request_id` | BIGINT NOT NULL (FK -> pull_requests) | Computed | The PR this label is on. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository for denormalized querying. |
| `platform_label_id` | BIGINT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Platform's label ID. |
| `node_id` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | GitHub GraphQL node ID. |
| `label_name` | TEXT NOT NULL | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Label name text. |
| `label_description` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Label description. |
| `label_color` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Hex color code. |
| `is_default` | BOOLEAN | GitHub REST: `/repos/{o}/{r}/pulls` | Whether this is a default label. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(pull_request_id, label_name)`

---

#### pull_request_assignees

Users assigned to a pull request.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pr_assignee_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pull_request_id` | BIGINT NOT NULL (FK -> pull_requests) | Computed | The PR. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `cntrb_id` | UUID (FK -> contributors) | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | The assigned contributor. |
| `platform_assignee_id` | BIGINT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Platform's assignee ID. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(pull_request_id, platform_assignee_id)`

---

#### pull_request_reviewers

Requested reviewers on a pull request.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pr_reviewer_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pull_request_id` | BIGINT NOT NULL (FK -> pull_requests) | Computed | The PR. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `cntrb_id` | UUID (FK -> contributors) | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/merge_requests/{n}/approvals` | The reviewer. |
| `platform_reviewer_id` | BIGINT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/merge_requests/{n}/approvals` | Platform's reviewer ID. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(pull_request_id, platform_reviewer_id)`

---

#### pull_request_reviews

Submitted reviews on a pull request (approved, changes requested, commented).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pr_review_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pull_request_id` | BIGINT NOT NULL (FK -> pull_requests) | Computed | The PR being reviewed. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `cntrb_id` | UUID (FK -> contributors) | GitHub REST: `/pulls/{n}/reviews`, GitLab: `/merge_requests/{n}/notes` | The reviewer. |
| `platform_id` | SMALLINT NOT NULL (FK -> platforms) | Computed | Platform. |
| `platform_review_id` | BIGINT NOT NULL | GitHub REST: `/pulls/{n}/reviews`, GitLab: `/merge_requests/{n}/notes` | Platform's review ID. |
| `node_id` | TEXT | GitHub REST: `/pulls/{n}/reviews` | GitHub GraphQL node ID. |
| `review_state` | TEXT | GitHub REST: `/pulls/{n}/reviews`, GitLab: `/merge_requests/{n}/notes` | State: `"APPROVED"`, `"CHANGES_REQUESTED"`, `"COMMENTED"`, `"DISMISSED"`. |
| `review_body` | TEXT | GitHub REST: `/pulls/{n}/reviews`, GitLab: `/merge_requests/{n}/notes` | Review body text. |
| `submitted_at` | TIMESTAMPTZ | GitHub REST: `/pulls/{n}/reviews`, GitLab: `/merge_requests/{n}/notes` | When the review was submitted. |
| `author_association` | TEXT | GitHub REST: `/pulls/{n}/reviews` | Reviewer's association to the repo. |
| `commit_id` | TEXT | GitHub REST: `/pulls/{n}/reviews` | SHA of the commit the review was made against. |
| `html_url` | TEXT | GitHub REST: `/pulls/{n}/reviews` | Web URL of the review. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(pull_request_id, platform_review_id)`

---

#### pull_request_meta

Branch metadata for the head and base of a pull request.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pr_meta_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pull_request_id` | BIGINT NOT NULL (FK -> pull_requests) | Computed | The PR. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `cntrb_id` | UUID (FK -> contributors) | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Owner of the head/base repo. |
| `head_or_base` | TEXT NOT NULL | Computed | `'head'` or `'base'`. |
| `meta_label` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Label string (e.g., `"owner:branch"`). |
| `meta_ref` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Branch name. |
| `meta_sha` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls`, GitLab: `/projects/{id}/merge_requests` | Commit SHA at the tip of the branch. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(pull_request_id, head_or_base)`

---

#### pull_request_commits

Commits included in a pull request.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pr_commit_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pull_request_id` | BIGINT NOT NULL (FK -> pull_requests) | Computed | The PR. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `author_cntrb_id` | UUID (FK -> contributors) | GitHub REST: `/pulls/{n}/commits`, GitLab: `/merge_requests/{n}/commits` | Commit author. |
| `pr_cmt_sha` | TEXT NOT NULL | GitHub REST: `/pulls/{n}/commits`, GitLab: `/merge_requests/{n}/commits` | Commit SHA. |
| `pr_cmt_node_id` | TEXT | GitHub REST: `/pulls/{n}/commits` | GitHub GraphQL node ID. |
| `pr_cmt_message` | TEXT | GitHub REST: `/pulls/{n}/commits`, GitLab: `/merge_requests/{n}/commits` | Commit message. |
| `pr_cmt_author_email` | TEXT | GitHub REST: `/pulls/{n}/commits`, GitLab: `/merge_requests/{n}/commits` | Author email from the commit. |
| `pr_cmt_timestamp` | TIMESTAMPTZ | GitHub REST: `/pulls/{n}/commits`, GitLab: `/merge_requests/{n}/commits` | Commit timestamp. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(pull_request_id, pr_cmt_sha)`

---

#### pull_request_files

Files changed in a pull request.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pr_file_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pull_request_id` | BIGINT NOT NULL (FK -> pull_requests) | Computed | The PR. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `pr_file_path` | TEXT NOT NULL | GitHub REST: `/pulls/{n}/files`, GitLab: `/merge_requests/{n}/diffs` | Path of the changed file. |
| `pr_file_additions` | INT | GitHub REST: `/pulls/{n}/files`, GitLab: `/merge_requests/{n}/diffs` | Lines added. |
| `pr_file_deletions` | INT | GitHub REST: `/pulls/{n}/files`, GitLab: `/merge_requests/{n}/diffs` | Lines removed. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(pull_request_id, pr_file_path)`

---

#### pull_request_events

Timeline events on pull requests (labeled, closed, merged, review_requested, etc.).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pr_event_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pull_request_id` | BIGINT NOT NULL (FK -> pull_requests) | Computed | The PR. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `cntrb_id` | UUID (FK -> contributors) | GitHub REST: `/issues/events`, GitLab: `/projects/{id}/merge_requests` | Contributor who triggered the event. |
| `platform_id` | SMALLINT NOT NULL (FK -> platforms) | Computed | Platform. |
| `platform_event_id` | BIGINT NOT NULL | GitHub REST: `/issues/events`, GitLab: `/projects/{id}/merge_requests` | Platform's event ID. |
| `node_id` | TEXT | GitHub REST: `/issues/events` | GitHub GraphQL node ID. |
| `action` | TEXT NOT NULL | GitHub REST: `/issues/events`, GitLab: `/projects/{id}/merge_requests` | Event type (e.g., `"merged"`, `"closed"`, `"review_requested"`). |
| `action_commit_hash` | TEXT | GitHub REST: `/issues/events` | Commit SHA if the event references a commit. |
| `created_at` | TIMESTAMPTZ | GitHub REST: `/issues/events`, GitLab: `/projects/{id}/merge_requests` | When the event occurred. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(repo_id, platform_event_id)`

---

#### pull_request_repo

Fork repository metadata referenced in pull requests. Records source repos for PR head/base branches that point to forks.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pr_repo_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pr_repo_meta_id` | BIGINT | Computed | FK to `pull_request_meta`. |
| `pr_repo_head_or_base` | TEXT | Computed | `'head'` or `'base'`. |
| `pr_src_repo_id` | BIGINT | GitHub REST: `/repos/{o}/{r}/pulls` | Platform's numeric ID for the source repo. |
| `pr_src_node_id` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | GitHub GraphQL node ID of the source repo. |
| `pr_repo_name` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | Short name of the fork repo. |
| `pr_repo_full_name` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | Full name (e.g., `"user/repo"`). |
| `pr_repo_private_bool` | BOOLEAN | GitHub REST: `/repos/{o}/{r}/pulls` | Whether the fork is private. |
| `pr_cntrb_id` | UUID (FK -> contributors) | GitHub REST: `/repos/{o}/{r}/pulls` | Owner of the fork repo. |
| | | | *Standard metadata columns* |

---

#### pull_request_review_message_ref

Bridge table linking a pull request **review** to its review-body message in the `messages` table. One row per review whose author submitted body text alongside their Approve / Request Changes / Comment action. Reviews submitted without body text (the common "Approved" with no comment case) do NOT create a row here.

This is NOT the inline-diff-comment table — those live in `review_comments` (below) and have their own messages-table row each. The schema retains a number of diff-position columns (`pr_review_msg_diff_hunk`, `pr_review_msg_path`, `pr_review_msg_position`, etc.) from the Augur-era schema, but Aveloxis writes ONLY `pr_review_id`, `repo_id`, `msg_id`, `pr_review_src_id`, and `pr_review_msg_node_id`. The diff-position columns are kept for schema parity / 8Knot compatibility but are not populated.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pr_review_msg_ref_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pr_review_id` | BIGINT NOT NULL (FK -> pull_request_reviews) | Computed | The review whose body is linked. |
| `repo_id` | BIGINT (FK -> repos) | Computed | Repository. |
| `msg_id` | BIGINT NOT NULL | Computed | FK to messages table; carries the review body text. |
| `pr_review_src_id` | BIGINT | GitHub REST: `/pulls/{n}/reviews` (or GraphQL `reviews` connection on PullRequest in graphql mode) | Platform's review ID. |
| `pr_review_msg_node_id` | TEXT | GitHub REST: `/pulls/{n}/reviews` | GitHub GraphQL node ID for the review. |
| `pr_review_msg_url`, `pr_review_msg_src_id`, `pr_review_msg_diff_hunk`, `pr_review_msg_path`, `pr_review_msg_position`, `pr_review_msg_original_position`, `pr_review_msg_commit_id`, `pr_review_msg_original_commit_id`, `pr_review_msg_updated_at`, `pr_review_msg_html_url`, `pr_url`, `pr_review_msg_author_association`, `pr_review_msg_start_line`, `pr_review_msg_original_start_line`, `pr_review_msg_start_side`, `pr_review_msg_line`, `pr_review_msg_original_line`, `pr_review_msg_side` | (various) | Not populated by Aveloxis | Vestigial Augur-era columns. Use `review_comments` (below) for inline-diff comment data. |
| | | | *Standard metadata columns* |

---

#### pull_request_teams

Teams requested to review a pull request.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pr_team_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pull_request_id` | BIGINT (FK -> pull_requests) | Computed | The PR. |
| `pr_src_team_id` | BIGINT | GitHub REST: `/repos/{o}/{r}/pulls` | Platform's team ID. |
| `pr_src_team_node` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | GitHub GraphQL node ID. |
| `pr_src_team_url` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | API URL for the team. |
| `pr_team_name` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | Team name. |
| `pr_team_slug` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | Team slug (URL-safe name). |
| `pr_team_description` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | Team description. |
| `pr_team_privacy` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | Privacy level (e.g., `"closed"`, `"secret"`). |
| `pr_team_permission` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | Permission level (e.g., `"push"`, `"admin"`). |
| `pr_team_src_members_url` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | API URL for team members. |
| `pr_team_src_repositories_url` | TEXT | GitHub REST: `/repos/{o}/{r}/pulls` | API URL for team repos. |
| `pr_team_parent_id` | BIGINT | GitHub REST: `/repos/{o}/{r}/pulls` | Parent team ID (for nested teams). |
| | | | *Standard metadata columns* |

---

#### pull_request_analysis

ML-based merge prediction results for pull requests. Schema parity table; not yet populated by Aveloxis workers.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pull_request_analysis_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pull_request_id` | BIGINT (FK -> pull_requests) | Not yet populated | The PR analyzed. |
| `merge_probability` | NUMERIC(256,250) | Not yet populated | Predicted probability of merge. |
| `mechanism` | TEXT | Not yet populated | ML model or heuristic used. |
| | | | *Standard metadata columns* |

---

### Messages and Comments

#### messages

Unified comment/message table shared by issues and pull requests. Each row is one piece of text. The four bridge tables (`issue_message_ref`, `pull_request_message_ref`, `pull_request_review_message_ref`, `review_comments`) link messages to their semantic origin, enabling cross-type text analysis. The four kinds of text stored here:

- **Issue conversation comments** — comments under an issue. Linked via `issue_message_ref`.
- **PR conversation comments** — comments under a PR (the IssueComment-shaped nodes GitHub serves under `/issues/{n}/comments` on REST, even for PRs). Linked via `pull_request_message_ref`.
- **PR review bodies** — text submitted alongside an Approve / Request Changes / Comment review action, when non-empty. Linked via `pull_request_review_message_ref`.
- **Inline PR review comments** — diff-anchored comments attached to a specific file/line. Linked via `review_comments` (carries the diff position metadata).

**Source endpoints by mode (GitHub):**
- `pr_child_mode=rest` AND `listing_mode=rest`: comments fetched via `/issues/comments` (covers both issue and PR conversation comments), `/pulls/comments` (inline review comments), `/pulls/{n}/reviews` (review bodies).
- `pr_child_mode=graphql` AND `listing_mode=graphql` (v0.18.5+ Phase 4): issue and PR conversation comments arrive **inline** via the GraphQL listing + PR-batch queries. `/issues/comments` is skipped entirely by the staged collector's `fullGraphQLMode()` gate. Review bodies are inline via `reviews` connection on PullRequest. The `/pulls/comments` REST iterator continues to run regardless of mode because GraphQL's PullRequestReviewComment type does not expose `side` / `startSide`.

GitLab: all four kinds come from `/projects/{id}/merge_requests/{n}/notes` and `/projects/{id}/merge_requests/{n}/discussions` (REST composition; no GraphQL path on GitLab).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `msg_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `rgls_id` | BIGINT | Computed | Optional FK to `repo_groups_list_serve` for mailing list messages. |
| `platform_msg_id` | BIGINT NOT NULL | GitHub (REST or GraphQL inline, see modes above) ; GitLab `/merge_requests/{n}/notes`, `/merge_requests/{n}/discussions` | Platform's comment / review ID. For review bodies this is the platform review ID. |
| `platform_id` | SMALLINT NOT NULL (FK -> platforms) | Computed | Platform. |
| `node_id` | TEXT | GitHub (REST or GraphQL inline) | GitHub GraphQL node ID. |
| `msg_text` | TEXT | GitHub (REST or GraphQL inline) ; GitLab `/merge_requests/{n}/notes` | Comment / review body text. |
| `msg_timestamp` | TIMESTAMPTZ | GitHub (REST or GraphQL inline) ; GitLab `/merge_requests/{n}/notes` | When the message was posted (or review submitted). |
| `msg_sender_email` | TEXT | Computed | Email of the comment author (resolved from contributor). |
| `msg_header` | TEXT | Not yet populated | Message header (for mailing list messages). |
| `cntrb_id` | UUID (FK -> contributors) | Computed (resolved from platform user data) | Comment / review author. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(platform_msg_id, platform_id)`

---

#### pull_request_message_ref

Join table linking pull requests to their **conversation** comments in the messages table. These are the IssueComment-shaped nodes GitHub serves under `/issues/{n}/comments` on REST (even for PRs); they are NOT diff-anchored inline review comments — those live in `review_comments`.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `pr_msg_ref_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pull_request_id` | BIGINT NOT NULL (FK -> pull_requests) | Computed | The PR. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `msg_id` | BIGINT NOT NULL (FK -> messages) | Computed | The message/comment. |
| `platform_src_id` | BIGINT | GitHub (REST `/issues/comments` in rest mode; inline via GraphQL `PullRequest.comments` in graphql mode) ; GitLab `/merge_requests/{n}/notes` | Platform's comment ID. |
| `platform_node_id` | TEXT | GitHub (REST or GraphQL inline) | GitHub GraphQL node ID. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(pull_request_id, msg_id)`

---

#### review_comments

Inline code review comments with full diff positioning. One row per diff-anchored review comment. Each row links to both a `pull_request_reviews` row (the parent review) and a `messages` row (the comment body text).

**Always populated via REST `/repos/{o}/{r}/pulls/comments` regardless of `pr_child_mode` / `listing_mode` configuration.** GitHub's GraphQL `PullRequestReviewComment` type does not expose the `side` / `startSide` fields aveloxis requires for diff-side fidelity, so the v0.18.5 Phase 4 GraphQL-inline-comments work explicitly carved this endpoint out and kept the REST iterator running. See CLAUDE.md's "Inline REVIEW comments NOT fetched via GraphQL — deliberate" subsection for the schema-validation history.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `review_comment_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `pr_review_id` | BIGINT (FK -> pull_request_reviews) | Computed | The parent review. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `msg_id` | BIGINT NOT NULL (FK -> messages) | Computed | The message body. |
| `platform_src_id` | BIGINT | GitHub REST: `/pulls/comments` | Platform's comment ID. |
| `node_id` | TEXT | GitHub REST: `/pulls/comments` | GitHub GraphQL node ID. |
| `diff_hunk` | TEXT | GitHub REST: `/pulls/comments` | Diff hunk context. |
| `file_path` | TEXT | GitHub REST: `/pulls/comments` | File path the comment is on. |
| `position` | INT | GitHub REST: `/pulls/comments` | Line position in the diff. |
| `original_position` | INT | GitHub REST: `/pulls/comments` | Original line position. |
| `commit_id` | TEXT | GitHub REST: `/pulls/comments` | Commit SHA the comment is on. |
| `original_commit_id` | TEXT | GitHub REST: `/pulls/comments` | Original commit SHA. |
| `line` | INT | GitHub REST: `/pulls/comments` | End line. |
| `original_line` | INT | GitHub REST: `/pulls/comments` | Original end line. |
| `side` | TEXT | GitHub REST: `/pulls/comments` | Diff side (`"LEFT"` or `"RIGHT"`). |
| `start_line` | INT | GitHub REST: `/pulls/comments` | Multi-line start. |
| `original_start_line` | INT | GitHub REST: `/pulls/comments` | Original multi-line start. |
| `start_side` | TEXT | GitHub REST: `/pulls/comments` | Diff side for start line. |
| `author_association` | TEXT | GitHub REST: `/pulls/comments` | Author's association to the repo. |
| `html_url` | TEXT | GitHub REST: `/pulls/comments` | Web URL. |
| `updated_at` | TIMESTAMPTZ | GitHub REST: `/pulls/comments` | Last update timestamp. |

**Unique constraint:** `(repo_id, platform_src_id)`

---

### Commits (Git/Facade)

#### commits

Commit data from `git log --numstat` on the repo's **default branch only** (resolved via `git symbolic-ref HEAD`). One row per file per commit (the same commit hash appears multiple times, once for each file touched). Populated by the facade worker.

> **Note (v0.16.4+):** earlier versions of aveloxis walked `git log --all --numstat` across every ref. That over-counted commits relative to GitHub's metadata `commit_count`, which reflects only the default branch. The facade was changed in v0.16.4 to use the default branch exclusively so the gathered count matches the metadata. Commits on side branches that never merged to default are NOT in this table by design.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `cmt_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `cmt_commit_hash` | TEXT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | Full SHA-1 hash. |
| `cmt_author_name` | TEXT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | Author name from the commit. |
| `cmt_author_raw_email` | TEXT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | Author email exactly as it appears in the commit. |
| `cmt_author_email` | TEXT NOT NULL | `aveloxis-commit-resolver` | Resolved/canonical author email. |
| `cmt_author_date` | TEXT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | Author date string. |
| `cmt_author_affiliation` | TEXT | `aveloxis-commit-resolver` | Resolved organizational affiliation of the author. |
| `cmt_committer_name` | TEXT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | Committer name. |
| `cmt_committer_raw_email` | TEXT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | Committer email exactly as in the commit. |
| `cmt_committer_email` | TEXT NOT NULL | `aveloxis-commit-resolver` | Resolved/canonical committer email. |
| `cmt_committer_date` | TEXT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | Committer date string. |
| `cmt_committer_affiliation` | TEXT | `aveloxis-commit-resolver` | Resolved organizational affiliation of the committer. |
| `cmt_added` | INT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | Lines added in this file. |
| `cmt_removed` | INT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | Lines removed in this file. |
| `cmt_whitespace` | INT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | Whitespace-only changes in this file. |
| `cmt_filename` | TEXT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | Path of the file changed. |
| `cmt_date_attempted` | TIMESTAMPTZ NOT NULL | Auto-generated | When this row was first processed. |
| `cmt_ght_committer_id` | INT | Augur import | Legacy GHTorrent committer ID. |
| `cmt_ght_committed_at` | TIMESTAMPTZ | Augur import | Legacy GHTorrent commit timestamp. |
| `cmt_committer_timestamp` | TIMESTAMPTZ | Git: `git log --numstat` (default branch only, per v0.16.4) | Parsed committer timestamp. |
| `cmt_author_timestamp` | TIMESTAMPTZ | Git: `git log --numstat` (default branch only, per v0.16.4) | Parsed author timestamp. |
| `cmt_author_platform_username` | TEXT | `aveloxis-commit-resolver` | Platform username resolved from the commit email. |
| `cmt_ght_author_id` | UUID | `aveloxis-commit-resolver` | FK to contributors (resolved author). |
| | | | *Standard metadata columns* |

**Indexes:** `(repo_id, cmt_commit_hash)`, `(cmt_author_email)`, `(cmt_author_raw_email)`, `(cmt_committer_raw_email)`, `(cmt_author_affiliation)`

---

#### commit_parents

Parent-child relationships between commits. Used to reconstruct commit DAGs and identify merge commits.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `cmt_id` | BIGINT NOT NULL (PK part 1) | Git: `git log --numstat` (default branch only, per v0.16.4) | The child commit ID. |
| `parent_id` | BIGSERIAL NOT NULL (PK part 2) | Git: `git log --numstat` (default branch only, per v0.16.4) | Auto-incrementing parent ordinal. |
| | | | *Standard metadata columns* |

**Primary key:** `(cmt_id, parent_id)`

---

#### commit_messages

Stores the full commit message text, deduplicated per repo and commit hash.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `cmt_msg_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `cmt_msg` | TEXT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | Full commit message text. |
| `cmt_hash` | TEXT NOT NULL | Git: `git log --numstat` (default branch only, per v0.16.4) | Commit SHA. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(repo_id, cmt_hash)`

---

#### commit_comment_ref

Comments made on specific commits (not PR review comments, but commit-level comments from the forge).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `cmt_comment_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `cmt_id` | BIGINT NOT NULL | Computed | The commit this comment is on. |
| `repo_id` | BIGINT (FK -> repos) | Computed | Repository. |
| `msg_id` | BIGINT NOT NULL | Computed | FK to messages table. |
| `user_id` | BIGINT NOT NULL | GitHub REST: `/repos/{o}/{r}/commits/{sha}` | Platform user ID of the commenter. |
| `body` | TEXT | GitHub REST: `/repos/{o}/{r}/commits/{sha}` | Comment body text. |
| `line` | BIGINT | GitHub REST: `/repos/{o}/{r}/commits/{sha}` | Line number in the file. |
| `position` | BIGINT | GitHub REST: `/repos/{o}/{r}/commits/{sha}` | Position in the diff. |
| `commit_comment_src_node_id` | TEXT | GitHub REST: `/repos/{o}/{r}/commits/{sha}` | GitHub GraphQL node ID. |
| `cmt_comment_src_id` | BIGINT NOT NULL UNIQUE | GitHub REST: `/repos/{o}/{r}/commits/{sha}` | Platform's comment ID. |
| `created_at` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}/commits/{sha}` | When the comment was created. |
| | | | *Standard metadata columns* |

---

### Releases

#### releases

Software releases and tags from the forge.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `release_id` | TEXT NOT NULL (PK part 1) | GitHub REST: `/repos/{o}/{r}/releases`, GitLab: `/projects/{id}/releases` | Platform's release identifier. |
| `repo_id` | BIGINT NOT NULL (PK part 2, FK -> repos) | Computed | Repository. |
| `release_name` | TEXT | GitHub REST: `/repos/{o}/{r}/releases`, GitLab: `/projects/{id}/releases` | Release title. |
| `release_description` | TEXT | GitHub REST: `/repos/{o}/{r}/releases`, GitLab: `/projects/{id}/releases` | Release notes/body. |
| `release_author` | TEXT | GitHub REST: `/repos/{o}/{r}/releases`, GitLab: `/projects/{id}/releases` | Author login. |
| `release_tag_name` | TEXT | GitHub REST: `/repos/{o}/{r}/releases`, GitLab: `/projects/{id}/releases` | Git tag name (e.g., `"v1.0.0"`). |
| `release_url` | TEXT | GitHub REST: `/repos/{o}/{r}/releases`, GitLab: `/projects/{id}/releases` | Web URL. |
| `created_at` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}/releases`, GitLab: `/projects/{id}/releases` | Creation timestamp. |
| `published_at` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}/releases`, GitLab: `/projects/{id}/releases` | Publication timestamp. |
| `updated_at` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}/releases`, GitLab: `/projects/{id}/releases` | Last update timestamp. |
| `is_draft` | BOOLEAN | GitHub REST: `/repos/{o}/{r}/releases`, GitLab: `/projects/{id}/releases` | Whether the release is a draft. |
| `is_prerelease` | BOOLEAN | GitHub REST: `/repos/{o}/{r}/releases`, GitLab: `/projects/{id}/releases` | Whether the release is a pre-release. |
| `tag_only` | BOOLEAN | Computed | `TRUE` if this is a lightweight tag with no release body. |
| | | | *Standard metadata columns* |

**Primary key:** `(repo_id, release_id)`

---

### Repository Metadata

#### repo_info

Point-in-time snapshots of repository metadata and statistics. A new row is inserted on each collection run, creating a time series.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_info_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `last_updated` | TIMESTAMPTZ | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}?statistics=true` | When the repo was last pushed to. |
| `issues_enabled` | TEXT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | Whether issues are enabled. |
| `prs_enabled` | TEXT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | Whether PRs/MRs are enabled. |
| `wiki_enabled` | TEXT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | Whether the wiki is enabled. |
| `pages_enabled` | TEXT | GitHub REST: `/repos/{o}/{r}` | Whether GitHub Pages is enabled. |
| `fork_count` | INT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}?statistics=true` | Number of forks. |
| `star_count` | INT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}?statistics=true` | Number of stars. |
| `watcher_count` | INT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}?statistics=true` | Number of watchers. |
| `open_issues` | INT | GitHub REST: `/repos/{o}/{r}` | Number of open issues (GitHub includes PRs). |
| `committer_count` | INT | Computed | Distinct committers. |
| `commit_count` | BIGINT | GitLab: `/projects/{id}?statistics=true` / Computed | Total commits. |
| `issues_count` | BIGINT | Computed | Total issues. |
| `issues_closed` | BIGINT | Computed | Closed issues. |
| `pr_count` | BIGINT | Computed | Total PRs. |
| `prs_open` | BIGINT | Computed | Open PRs. |
| `prs_closed` | BIGINT | Computed | Closed (not merged) PRs. |
| `prs_merged` | BIGINT | Computed | Merged PRs. |
| `default_branch` | TEXT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | Default branch name (e.g., `"main"`). |
| `license` | TEXT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | SPDX license identifier. |
| `issue_contributors_count` | TEXT | Computed | Distinct issue contributors. |
| `changelog_file` | TEXT | Computed | Whether a CHANGELOG file exists. |
| `contributing_file` | TEXT | Computed | Whether a CONTRIBUTING file exists. |
| `license_file` | TEXT | Computed | Whether a LICENSE file exists. |
| `code_of_conduct_file` | TEXT | Computed | Whether a CODE_OF_CONDUCT file exists. |
| `security_issue_file` | TEXT | Computed | Whether a SECURITY file exists. |
| `security_audit_file` | TEXT | Computed | Whether a security audit file exists. |
| `status` | TEXT | Computed | Repository health status. |
| `keywords` | TEXT | GitHub REST: `/repos/{o}/{r}`, GitLab: `/projects/{id}` | Repository topics/tags. |
| | | | *Standard metadata columns* |

---

#### repo_clones

Clone traffic data. Only available for repos where you have push access (GitHub-only feature).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_clone_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `clone_timestamp` | TIMESTAMPTZ NOT NULL | GitHub REST: `/repos/{o}/{r}/traffic/clones` | Date of the clone data point. |
| `total_clones` | INT | GitHub REST: `/repos/{o}/{r}/traffic/clones` | Total clones. |
| `unique_clones` | INT | GitHub REST: `/repos/{o}/{r}/traffic/clones` | Unique cloners. |
| | | | *Standard metadata columns* |

**Unique constraint:** `(repo_id, clone_timestamp)`

---

#### repo_badging

CII Best Practices badge data stored as raw JSON. Each row is a snapshot from the CII badge API.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `badge_collection_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Computed | Repository. |
| `created_at` | TIMESTAMPTZ | Auto-generated | When this snapshot was taken. |
| `data` | JSONB | Not yet populated | Raw badge data JSON. |
| | | | *Standard metadata columns* |

---

#### dei_badging

DEI (Diversity, Equity, and Inclusion) badging levels for repositories.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `id` | SERIAL NOT NULL (PK part 1) | Auto-generated | Primary key part 1. |
| `badging_id` | INT NOT NULL | Not yet populated | External badging system ID. |
| `level` | TEXT NOT NULL | Not yet populated | Badge level achieved. |
| `repo_id` | BIGINT NOT NULL (PK part 2, FK -> repos) | Computed | Repository. |

**Primary key:** `(id, repo_id)`

---

#### repo_insights

Computed insight records for repositories. Each row is one metric observation at one point in time.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `ri_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Computed | Repository. |
| `ri_metric` | TEXT | Computed | Metric name (e.g., `"commit-count"`). |
| `ri_value` | TEXT | Computed | Metric value. |
| `ri_date` | TIMESTAMPTZ | Computed | Date of the observation. |
| `ri_fresh` | BOOLEAN | Computed | Whether this insight is current. |
| `ri_score` | NUMERIC | Computed | Numeric score for ranking. |
| `ri_field` | TEXT | Computed | Field name within the metric. |
| `ri_detection_method` | TEXT | Computed | How the insight was detected (e.g., anomaly detection). |
| | | | *Standard metadata columns* |

---

#### repo_insights_records

Archived insight records. Same structure as `repo_insights` but for historical data.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `ri_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Computed | Repository. |
| `ri_metric` | TEXT | Computed | Metric name. |
| `ri_field` | TEXT | Computed | Field name. |
| `ri_value` | TEXT | Computed | Metric value. |
| `ri_date` | TIMESTAMPTZ | Computed | Observation date. |
| `ri_score` | FLOAT | Computed | Numeric score. |
| `ri_detection_method` | TEXT | Computed | Detection method. |
| | | | *Standard metadata columns* |

---

#### repo_group_insights

Aggregated insights at the repo-group level.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `rgi_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_group_id` | BIGINT (FK -> repo_groups) | Computed | Repo group. |
| `rgi_metric` | TEXT | Computed | Metric name. |
| `rgi_value` | TEXT | Computed | Metric value. |
| `cms_id` | BIGINT | Computed | FK to `chaoss_metric_status`. |
| `rgi_fresh` | BOOLEAN | Computed | Whether this insight is current. |
| | | | *Standard metadata columns* |

---

### Dependencies and SBOM

#### repo_dependencies

High-level dependency counts per language for a repository.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_dependencies_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Computed | Repository. |
| `dep_name` | TEXT | Not yet populated | Dependency name. |
| `dep_count` | INT | Not yet populated | Number of times this dependency appears. |
| `dep_language` | TEXT | Not yet populated | Language of the dependency. |
| | | | *Standard metadata columns* |

---

#### repo_deps_libyear

Libyear analysis results. Measures how out-of-date each dependency is by comparing current vs. latest versions.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_deps_libyear_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Computed | Repository. |
| `name` | TEXT | Not yet populated | Dependency name. |
| `requirement` | TEXT | Not yet populated | Version requirement string. |
| `type` | TEXT | Not yet populated | Dependency type (e.g., `"runtime"`, `"development"`). |
| `package_manager` | TEXT | Not yet populated | Package manager (e.g., `"npm"`, `"pip"`). |
| `current_version` | TEXT | Not yet populated | Currently used version. |
| `latest_version` | TEXT | Not yet populated | Latest available version. |
| `current_release_date` | TEXT | Not yet populated | Release date of current version. |
| `latest_release_date` | TEXT | Not yet populated | Release date of latest version. |
| `libyear` | FLOAT | Not yet populated | Libyear score (years between current and latest). |
| | | | *Standard metadata columns* |

---

#### repo_deps_scorecard

OpenSSF Scorecard check results for a repository.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_deps_scorecard_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Computed | Repository. |
| `name` | TEXT | Not yet populated | Scorecard check name (e.g., `"Code-Review"`, `"Branch-Protection"`). |
| `score` | TEXT | Not yet populated | Check score. |
| `scorecard_check_details` | JSONB | Not yet populated | Detailed check results as JSON. |
| | | | *Standard metadata columns* |

---

#### repo_sbom_scans

Raw SBOM (Software Bill of Materials) scan results.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `rsb_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Computed | Repository. |
| `sbom_scan` | JSON | Not yet populated | Raw SBOM scan output as JSON. |

---

### Libraries

#### libraries

Metadata about software libraries/packages associated with a repository.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `library_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Computed | Repository. |
| `platform` | TEXT | Not yet populated | Package registry platform (e.g., `"npm"`, `"pypi"`). |
| `name` | TEXT | Not yet populated | Package name. |
| `created_timestamp` | TIMESTAMPTZ | Not yet populated | When the package was first published. |
| `updated_timestamp` | TIMESTAMPTZ | Not yet populated | Last update timestamp. |
| `library_description` | TEXT | Not yet populated | Package description. |
| `keywords` | TEXT | Not yet populated | Package keywords. |
| `library_homepage` | TEXT | Not yet populated | Homepage URL. |
| `license` | TEXT | Not yet populated | License identifier. |
| `version_count` | INT | Not yet populated | Total number of versions published. |
| `latest_release_timestamp` | TEXT | Not yet populated | When the latest version was released. |
| `latest_release_number` | TEXT | Not yet populated | Latest version number. |
| `package_manager_id` | TEXT | Not yet populated | ID on the package manager. |
| `dependency_count` | INT | Not yet populated | Number of dependencies this library has. |
| `dependent_library_count` | INT | Not yet populated | Number of libraries that depend on this one. |
| `primary_language` | TEXT | Not yet populated | Primary language. |
| | | | *Standard metadata columns* |

---

#### library_dependencies

Manifest-level dependency declarations for libraries.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `lib_dependency_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `library_id` | BIGINT (FK -> libraries) | Computed | The library. |
| `manifest_platform` | TEXT | Not yet populated | Platform of the manifest. |
| `manifest_filepath` | TEXT | Not yet populated | Path to the manifest file. |
| `manifest_kind` | TEXT | Not yet populated | Kind of manifest (e.g., `"lockfile"`, `"manifest"`). |
| `repo_id_branch` | TEXT NOT NULL | Not yet populated | Repository ID and branch identifier. |
| | | | *Standard metadata columns* |

---

#### library_version

Published versions of a library.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `library_version_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `library_id` | BIGINT (FK -> libraries) | Computed | The library. |
| `library_platform` | TEXT | Not yet populated | Package registry platform. |
| `version_number` | TEXT | Not yet populated | Version string (e.g., `"2.1.0"`). |
| `version_release_date` | TIMESTAMPTZ | Not yet populated | When this version was published. |
| | | | *Standard metadata columns* |

---

### Facade Aggregates (Data Mart)

These tables contain pre-aggregated commit statistics computed from the `commits` table. They exist at annual, monthly, and weekly granularity for both per-repo and per-repo-group levels. All are populated by SQL aggregation over the `commits` table.

#### dm_repo_annual

Annual commit statistics per contributor per repository.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_id` | BIGINT NOT NULL | Computed: SQL aggregation | Repository. |
| `email` | TEXT NOT NULL | Computed: SQL aggregation | Contributor email. |
| `affiliation` | TEXT | Computed: SQL aggregation | Organizational affiliation. |
| `year` | SMALLINT NOT NULL | Computed: SQL aggregation | Calendar year. |
| `added` | BIGINT NOT NULL | Computed: SQL aggregation | Total lines added. |
| `removed` | BIGINT NOT NULL | Computed: SQL aggregation | Total lines removed. |
| `whitespace` | BIGINT NOT NULL | Computed: SQL aggregation | Total whitespace changes. |
| `files` | BIGINT NOT NULL | Computed: SQL aggregation | Distinct files changed. |
| `patches` | BIGINT NOT NULL | Computed: SQL aggregation | Number of commits/patches. |
| | | | *Standard metadata columns* |

**Index:** `(repo_id, affiliation)`

---

#### dm_repo_monthly

Monthly commit statistics per contributor per repository.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_id` | BIGINT NOT NULL | Computed: SQL aggregation | Repository. |
| `email` | TEXT NOT NULL | Computed: SQL aggregation | Contributor email. |
| `affiliation` | TEXT | Computed: SQL aggregation | Organizational affiliation. |
| `month` | SMALLINT NOT NULL | Computed: SQL aggregation | Calendar month (1-12). |
| `year` | SMALLINT NOT NULL | Computed: SQL aggregation | Calendar year. |
| `added` | BIGINT NOT NULL | Computed: SQL aggregation | Total lines added. |
| `removed` | BIGINT NOT NULL | Computed: SQL aggregation | Total lines removed. |
| `whitespace` | BIGINT NOT NULL | Computed: SQL aggregation | Total whitespace changes. |
| `files` | BIGINT NOT NULL | Computed: SQL aggregation | Distinct files changed. |
| `patches` | BIGINT NOT NULL | Computed: SQL aggregation | Number of commits/patches. |
| | | | *Standard metadata columns* |

---

#### dm_repo_weekly

Weekly commit statistics per contributor per repository.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_id` | BIGINT NOT NULL | Computed: SQL aggregation | Repository. |
| `email` | TEXT NOT NULL | Computed: SQL aggregation | Contributor email. |
| `affiliation` | TEXT | Computed: SQL aggregation | Organizational affiliation. |
| `week` | SMALLINT NOT NULL | Computed: SQL aggregation | ISO week number (1-53). |
| `year` | SMALLINT NOT NULL | Computed: SQL aggregation | Calendar year. |
| `added` | BIGINT NOT NULL | Computed: SQL aggregation | Total lines added. |
| `removed` | BIGINT NOT NULL | Computed: SQL aggregation | Total lines removed. |
| `whitespace` | BIGINT NOT NULL | Computed: SQL aggregation | Total whitespace changes. |
| `files` | BIGINT NOT NULL | Computed: SQL aggregation | Distinct files changed. |
| `patches` | BIGINT NOT NULL | Computed: SQL aggregation | Number of commits/patches. |
| | | | *Standard metadata columns* |

---

#### dm_repo_group_annual

Annual commit statistics per contributor per repo group.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_group_id` | BIGINT NOT NULL | Computed: SQL aggregation | Repo group. |
| `email` | TEXT NOT NULL | Computed: SQL aggregation | Contributor email. |
| `affiliation` | TEXT | Computed: SQL aggregation | Organizational affiliation. |
| `year` | SMALLINT NOT NULL | Computed: SQL aggregation | Calendar year. |
| `added` | BIGINT NOT NULL | Computed: SQL aggregation | Total lines added. |
| `removed` | BIGINT NOT NULL | Computed: SQL aggregation | Total lines removed. |
| `whitespace` | BIGINT NOT NULL | Computed: SQL aggregation | Total whitespace changes. |
| `files` | BIGINT NOT NULL | Computed: SQL aggregation | Distinct files changed. |
| `patches` | BIGINT NOT NULL | Computed: SQL aggregation | Number of commits/patches. |
| | | | *Standard metadata columns* |

---

#### dm_repo_group_monthly

Monthly commit statistics per contributor per repo group.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_group_id` | BIGINT NOT NULL | Computed: SQL aggregation | Repo group. |
| `email` | TEXT NOT NULL | Computed: SQL aggregation | Contributor email. |
| `affiliation` | TEXT | Computed: SQL aggregation | Organizational affiliation. |
| `month` | SMALLINT NOT NULL | Computed: SQL aggregation | Calendar month (1-12). |
| `year` | SMALLINT NOT NULL | Computed: SQL aggregation | Calendar year. |
| `added` | BIGINT NOT NULL | Computed: SQL aggregation | Total lines added. |
| `removed` | BIGINT NOT NULL | Computed: SQL aggregation | Total lines removed. |
| `whitespace` | BIGINT NOT NULL | Computed: SQL aggregation | Total whitespace changes. |
| `files` | BIGINT NOT NULL | Computed: SQL aggregation | Distinct files changed. |
| `patches` | BIGINT NOT NULL | Computed: SQL aggregation | Number of commits/patches. |
| | | | *Standard metadata columns* |

---

#### dm_repo_group_weekly

Weekly commit statistics per contributor per repo group.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_group_id` | BIGINT NOT NULL | Computed: SQL aggregation | Repo group. |
| `email` | TEXT NOT NULL | Computed: SQL aggregation | Contributor email. |
| `affiliation` | TEXT | Computed: SQL aggregation | Organizational affiliation. |
| `week` | SMALLINT NOT NULL | Computed: SQL aggregation | ISO week number (1-53). |
| `year` | SMALLINT NOT NULL | Computed: SQL aggregation | Calendar year. |
| `added` | BIGINT NOT NULL | Computed: SQL aggregation | Total lines added. |
| `removed` | BIGINT NOT NULL | Computed: SQL aggregation | Total lines removed. |
| `whitespace` | BIGINT NOT NULL | Computed: SQL aggregation | Total whitespace changes. |
| `files` | BIGINT NOT NULL | Computed: SQL aggregation | Distinct files changed. |
| `patches` | BIGINT NOT NULL | Computed: SQL aggregation | Number of commits/patches. |
| | | | *Standard metadata columns* |

---

### Code Complexity

#### repo_labor

Per-file code complexity and line count metrics, typically from `scc` (Sloc Cloc and Code) analysis of cloned repositories.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_labor_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Computed | Repository. |
| `repo_clone_date` | TIMESTAMPTZ | Computed | When the repo was cloned for analysis. |
| `rl_analysis_date` | TIMESTAMPTZ | Computed | When the analysis was run. |
| `programming_language` | TEXT | Computed | Language of the file. |
| `file_path` | TEXT | Computed | Path within the repo. |
| `file_name` | TEXT | Computed | File name. |
| `total_lines` | INT | Computed | Total lines in the file. |
| `code_lines` | INT | Computed | Lines of code (excluding comments and blanks). |
| `comment_lines` | INT | Computed | Lines of comments. |
| `blank_lines` | INT | Computed | Blank lines. |
| `code_complexity` | INT | Computed | Cyclomatic complexity score. |
| `repo_url` | TEXT | Computed | Git URL of the repo. |
| | | | *Standard metadata columns* |

---

#### repo_meta

Key-value metadata store for arbitrary repository attributes.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `rmeta_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `rmeta_name` | TEXT | Computed | Metadata key name. |
| `rmeta_value` | TEXT | Computed | Metadata value. Default `'0'`. |
| | | | *Standard metadata columns* |

---

#### repo_stats

Numeric statistics for repositories, stored as key-value pairs.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `rstat_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository. |
| `rstat_name` | TEXT | Computed | Statistic name. |
| `rstat_value` | BIGINT | Computed | Statistic value. |
| | | | *Standard metadata columns* |

---

#### repo_test_coverage

Per-file test coverage data.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_id` | BIGSERIAL (PK) | Auto-generated | Primary key (also serves as repo reference). |
| `repo_clone_date` | TIMESTAMPTZ | Not yet populated | When the repo was cloned. |
| `rtc_analysis_date` | TIMESTAMPTZ | Not yet populated | When coverage analysis was run. |
| `programming_language` | TEXT | Not yet populated | Language of the file. |
| `file_path` | TEXT | Not yet populated | File path. |
| `file_name` | TEXT | Not yet populated | File name. |
| `testing_tool` | TEXT | Not yet populated | Test framework used. |
| `file_statement_count` | BIGINT | Not yet populated | Total statements in the file. |
| `file_subroutine_count` | BIGINT | Not yet populated | Total subroutines. |
| `file_statements_tested` | BIGINT | Not yet populated | Statements covered by tests. |
| `file_subroutines_tested` | BIGINT | Not yet populated | Subroutines covered by tests. |
| | | | *Standard metadata columns* |

---

### CHAOSS Metrics

#### chaoss_metric_status

Registry of CHAOSS metrics and their implementation status in Aveloxis.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `cms_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `cm_group` | TEXT | User input / Augur import | Metric group. |
| `cm_source` | TEXT | User input / Augur import | Data source for the metric. |
| `cm_type` | TEXT | User input / Augur import | Metric type. |
| `cm_backend_status` | TEXT | User input / Augur import | Backend implementation status. |
| `cm_frontend_status` | TEXT | User input / Augur import | Frontend implementation status. |
| `cm_defined` | BOOLEAN | User input / Augur import | Whether the metric is formally defined by CHAOSS. |
| `cm_api_endpoint_repo` | TEXT | User input / Augur import | API endpoint for per-repo queries. |
| `cm_api_endpoint_rg` | TEXT | User input / Augur import | API endpoint for per-repo-group queries. |
| `cm_name` | TEXT | User input / Augur import | Metric name. |
| `cm_working_group` | TEXT | User input / Augur import | CHAOSS working group. |
| `cm_info` | JSON | User input / Augur import | Additional metric information as JSON. |
| `cm_working_group_focus_area` | TEXT | User input / Augur import | Focus area within the working group. |
| | | | *Standard metadata columns* |

---

#### chaoss_user

User accounts for the CHAOSS metrics interface.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `chaoss_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `chaoss_login_name` | TEXT | User input | Login name. |
| `chaoss_login_hashword` | TEXT | User input | Hashed password. |
| `chaoss_email` | TEXT UNIQUE | User input | Email address. |
| `chaoss_text_phone` | TEXT | User input | Phone number for text notifications. |
| `chaoss_first_name` | TEXT | User input | First name. |
| `chaoss_last_name` | TEXT | User input | Last name. |
| | | | *Standard metadata columns* |

---

### Analysis and ML (schema parity, not yet populated)

#### message_analysis

Per-message sentiment analysis results from ML workers.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `msg_analysis_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `msg_id` | BIGINT | Not yet populated | FK to messages table. |
| `worker_run_id` | BIGINT | Not yet populated | ID of the worker run that produced this result. |
| `sentiment_score` | FLOAT | Not yet populated | Sentiment score (-1.0 to 1.0). |
| `reconstruction_error` | FLOAT | Not yet populated | Autoencoder reconstruction error (anomaly score). |
| `novelty_flag` | BOOLEAN | Not yet populated | Whether the message was flagged as novel. |
| `feedback_flag` | BOOLEAN | Not yet populated | Whether the message was flagged as feedback. |
| | | | *Standard metadata columns* |

---

#### message_analysis_summary

Aggregated sentiment analysis summaries per repository per time period.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `msg_summary_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Not yet populated | Repository. |
| `worker_run_id` | BIGINT | Not yet populated | Worker run ID. |
| `positive_ratio` | FLOAT | Not yet populated | Ratio of positive messages. |
| `negative_ratio` | FLOAT | Not yet populated | Ratio of negative messages. |
| `novel_count` | BIGINT | Not yet populated | Count of novel messages. |
| `period` | TIMESTAMPTZ | Not yet populated | Time period for the summary. |
| | | | *Standard metadata columns* |

---

#### message_sentiment

Duplicate of `message_analysis` for schema parity with Augur. Not yet populated.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `msg_analysis_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `msg_id` | BIGINT | Not yet populated | FK to messages. |
| `worker_run_id` | BIGINT | Not yet populated | Worker run ID. |
| `sentiment_score` | FLOAT | Not yet populated | Sentiment score. |
| `reconstruction_error` | FLOAT | Not yet populated | Autoencoder reconstruction error. |
| `novelty_flag` | BOOLEAN | Not yet populated | Novelty flag. |
| `feedback_flag` | BOOLEAN | Not yet populated | Feedback flag. |
| | | | *Standard metadata columns* |

---

#### message_sentiment_summary

Aggregated sentiment summaries. Duplicate of `message_analysis_summary` for schema parity.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `msg_summary_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Not yet populated | Repository. |
| `worker_run_id` | BIGINT | Not yet populated | Worker run ID. |
| `positive_ratio` | FLOAT | Not yet populated | Positive message ratio. |
| `negative_ratio` | FLOAT | Not yet populated | Negative message ratio. |
| `novel_count` | BIGINT | Not yet populated | Novel message count. |
| `period` | TIMESTAMPTZ | Not yet populated | Summary time period. |
| | | | *Standard metadata columns* |

---

#### discourse_insights

Discourse act classification for messages. Categorizes messages by their communicative function.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `msg_discourse_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `msg_id` | BIGINT | Not yet populated | FK to messages. |
| `discourse_act` | TEXT | Not yet populated | Discourse act label (e.g., `"question"`, `"answer"`, `"agreement"`). |
| | | | *Standard metadata columns* |

---

#### lstm_anomaly_models

LSTM neural network model definitions for anomaly detection.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `model_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `model_name` | TEXT | Not yet populated | Model name. |
| `model_description` | TEXT | Not yet populated | Model description. |
| `look_back_days` | BIGINT | Not yet populated | Number of days of history the model considers. |
| `training_days` | BIGINT | Not yet populated | Number of days of data used for training. |
| `batch_size` | BIGINT | Not yet populated | Training batch size. |
| `metric` | TEXT | Not yet populated | Which metric this model targets. |
| | | | *Standard metadata columns* |

---

#### lstm_anomaly_results

Results from LSTM anomaly detection runs.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `result_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Not yet populated | Repository. |
| `repo_category` | TEXT | Not yet populated | Category of the repository. |
| `model_id` | BIGINT (FK -> lstm_anomaly_models) | Not yet populated | Model used. |
| `metric` | TEXT | Not yet populated | Metric analyzed. |
| `contamination_factor` | FLOAT | Not yet populated | Anomaly detection contamination factor. |
| `mean_absolute_error` | FLOAT | Not yet populated | MAE of the model predictions. |
| `remarks` | TEXT | Not yet populated | Human-readable remarks. |
| `metric_field` | TEXT | Not yet populated | Specific field within the metric. |
| `mean_absolute_actual_value` | FLOAT | Not yet populated | Mean of actual values. |
| `mean_absolute_prediction_value` | FLOAT | Not yet populated | Mean of predicted values. |
| | | | *Standard metadata columns* |

---

### Topic Modeling (schema parity, not yet populated)

#### topic_model_meta

Metadata for trained topic models. Stores model configuration, quality metrics, and file paths.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `model_id` | UUID (PK) | Auto-generated (`gen_random_uuid()`) | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Not yet populated | Repository the model was trained on. |
| `model_method` | TEXT NOT NULL | Not yet populated | Topic modeling method (e.g., `"LDA"`, `"NMF"`). |
| `num_topics` | INT NOT NULL | Not yet populated | Number of topics. |
| `num_words_per_topic` | INT NOT NULL | Not yet populated | Words per topic. |
| `training_parameters` | JSON NOT NULL | Not yet populated | Full training parameters as JSON. |
| `model_file_paths` | JSON NOT NULL | Not yet populated | Paths to saved model files. |
| `parameters_hash` | TEXT NOT NULL | Not yet populated | Hash of parameters for deduplication. |
| `coherence_score` | FLOAT NOT NULL | Not yet populated | Topic coherence score. |
| `perplexity_score` | FLOAT NOT NULL | Not yet populated | Perplexity score. |
| `topic_diversity` | FLOAT NOT NULL | Not yet populated | Topic diversity score. |
| `quality` | JSON NOT NULL | Not yet populated | Quality metrics as JSON. |
| `training_message_count` | BIGINT NOT NULL | Not yet populated | Number of messages used for training. |
| `data_fingerprint` | JSON NOT NULL | Not yet populated | Fingerprint of the training data. |
| `visualization_data` | JSON | Not yet populated | Pre-computed visualization data. |
| `training_start_time` | TIMESTAMPTZ NOT NULL | Not yet populated | When training started. |
| `training_end_time` | TIMESTAMPTZ NOT NULL | Not yet populated | When training finished. |
| | | | *Standard metadata columns* |

---

#### topic_model_event

Event log for topic model training and inference runs.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `event_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `ts` | TIMESTAMPTZ NOT NULL | Auto-generated | Event timestamp. Default `NOW()`. |
| `repo_id` | INT | Not yet populated | Repository. |
| `model_id` | UUID | Not yet populated | FK to topic_model_meta. |
| `event` | TEXT NOT NULL | Not yet populated | Event description. |
| `level` | TEXT NOT NULL | Not yet populated | Log level (e.g., `'INFO'`, `'ERROR'`). |
| `payload` | JSONB NOT NULL | Not yet populated | Event payload as JSON. |

---

#### topic_words

Words and their probabilities within discovered topics.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `topic_words_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `topic_id` | BIGINT | Not yet populated | Topic number. |
| `word` | TEXT | Not yet populated | Word. |
| `word_prob` | FLOAT | Not yet populated | Probability of this word in the topic. |
| | | | *Standard metadata columns* |

---

#### repo_cluster_messages

Cluster assignments for repository messages (content and mechanism clusters).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `msg_cluster_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Not yet populated | Repository. |
| `cluster_content` | INT | Not yet populated | Content-based cluster assignment. |
| `cluster_mechanism` | INT | Not yet populated | Mechanism-based cluster assignment. |
| | | | *Standard metadata columns* |

---

#### repo_topic

Topic distribution for repositories (which topics are associated with a repo).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_topic_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT (FK -> repos) | Not yet populated | Repository. |
| `topic_id` | INT | Not yet populated | Topic number. |
| `topic_prob` | FLOAT | Not yet populated | Probability of this topic for the repo. |
| | | | *Standard metadata columns* |

---

### Network Analysis (schema parity, not yet populated)

#### network_beyond_augur

Cross-repository contributor activity network data. Records how contributors act across multiple repos.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `cntrb_id` | UUID | Not yet populated | Contributor. |
| `repo_git` | TEXT | Not yet populated | Git URL of the repo. |
| `repo_name` | TEXT | Not yet populated | Repository name. |
| `action` | TEXT | Not yet populated | Action type (e.g., `"commit"`, `"issue"`, `"pr"`). |
| `action_year` | FLOAT | Not yet populated | Year of the action. |
| `action_quarter` | NUMERIC | Not yet populated | Quarter of the action (1-4). |
| `counter` | BIGINT | Not yet populated | Count of actions. |

*No primary key defined.*

---

#### network_beyond_augur_dependencies

Cross-repository dependency network data. Tracks how repos depend on each other via contributor overlap.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `cntrb_id` | UUID | Not yet populated | Contributor. |
| `repo_git` | TEXT | Not yet populated | Git URL. |
| `repo_name` | TEXT | Not yet populated | Repository name. |
| `action` | TEXT | Not yet populated | Action type. |
| `action_year` | FLOAT | Not yet populated | Year. |
| `action_quarter` | NUMERIC | Not yet populated | Quarter (1-4). |
| `counter` | BIGINT | Not yet populated | Count. |

*No primary key defined.*

---

### Miscellaneous

#### exclude

Exclusion rules for filtering out certain email addresses or domains from analysis.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `id` | INT (PK) | User input | Primary key. |
| `projects_id` | INT NOT NULL | User input | Project/repo group to apply the exclusion to. |
| `email` | TEXT | User input | Email address to exclude. |
| `domain` | TEXT | User input | Email domain to exclude. |

---

#### historical_repo_urls

Tracks URL changes for repositories over time (e.g., when a repo is renamed or transferred).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_id` | BIGINT NOT NULL (PK part 1) | Computed | Repository. |
| `git_url` | TEXT NOT NULL (PK part 2) | Computed | A historical git URL for this repo. |
| `date_collected` | TIMESTAMPTZ | Auto-generated | When this URL was recorded. |

**Primary key:** `(repo_id, git_url)`

---

#### repos_fetch_log

Log of repository fetch attempts and their outcomes.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repos_id` | INT NOT NULL | Computed | Repository ID. |
| `status` | TEXT NOT NULL | Computed | Fetch status (e.g., `"success"`, `"error"`). |
| `date` | TIMESTAMPTZ NOT NULL | Auto-generated | When the fetch was attempted. |

*No primary key defined.*

---

#### settings

Application settings for the data schema (separate from ops config).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `id` | INT (PK) | User input | Primary key. |
| `setting` | TEXT NOT NULL | User input | Setting name. |
| `value` | TEXT NOT NULL | User input | Setting value. |
| `last_modified` | TIMESTAMPTZ NOT NULL | Auto-generated | Last modification timestamp. |

---

#### unknown_cache

Cache for contributor emails that could not be resolved to an affiliation. Prevents repeated lookups.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `type` | TEXT NOT NULL | Computed | Type of unknown entry. |
| `repo_group_id` | INT NOT NULL | Computed | Repo group context. |
| `email` | TEXT NOT NULL | Computed | Unresolved email. |
| `domain` | TEXT | Computed | Email domain. |
| `added` | BIGINT NOT NULL | Computed | Lines added by this email. |
| | | | *Standard metadata columns* |

*No primary key defined.*

---

#### utility_log

General-purpose utility log for miscellaneous operations.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `level` | TEXT NOT NULL | Computed | Log level. |
| `status` | TEXT NOT NULL | Computed | Status message. |
| `attempted` | TIMESTAMPTZ NOT NULL | Auto-generated | When the operation was attempted. |

---

#### working_commits

Tracks the current working commit for facade processing per repository.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repos_id` | INT NOT NULL | Computed | Repository ID. |
| `working_commit` | TEXT | Git: `git log --numstat` (default branch only, per v0.16.4) | The commit hash currently being processed. |

*No primary key defined.*

---

## aveloxis_ops Schema

### Collection Pipeline

#### staging

Raw API responses land here before being parsed and inserted into `aveloxis_data` tables. Acts as a durable inbox for the ETL pipeline.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `staging_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT NOT NULL (FK -> repos) | Computed | Repository this payload is for. |
| `platform_id` | SMALLINT NOT NULL (FK -> platforms) | Computed | Platform the data came from. |
| `entity_type` | TEXT NOT NULL | Computed | Type of entity (e.g., `"issues"`, `"pull_requests"`, `"releases"`). |
| `payload` | JSONB NOT NULL | GitHub REST API / GitLab API v4 | Raw API response body. |
| `created_at` | TIMESTAMPTZ | Auto-generated | When the payload was staged. |
| `processed` | BOOLEAN | Computed | Whether the payload has been parsed into data tables. Default `FALSE`. |

**Index:** `(repo_id, entity_type) WHERE NOT processed`

---

#### collection_queue

Postgres-backed priority queue that drives the collection pipeline. Each repo has at most one row. Workers claim repos by locking rows.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_id` | BIGINT (PK, FK -> repos) | Computed | Repository. |
| `priority` | INT NOT NULL | Computed | Priority (lower = higher priority). Default `100`. |
| `status` | TEXT NOT NULL | Computed | Queue status: `'queued'`, `'running'`, `'done'`, `'error'`. |
| `due_at` | TIMESTAMPTZ NOT NULL | Computed | When this repo is next due for collection. |
| `locked_by` | TEXT | Computed | Worker instance ID that holds the lock. |
| `locked_at` | TIMESTAMPTZ | Computed | When the lock was acquired. |
| `last_collected` | TIMESTAMPTZ | Computed | When collection last completed. |
| `last_error` | TEXT | Computed | Error message from last failed run. |
| `last_issues` | INT | Computed | Issues collected in the last run. |
| `last_prs` | INT | Computed | PRs collected in the last run. |
| `last_messages` | INT | Computed | Messages collected in the last run. |
| `last_events` | INT | Computed | Events collected in the last run. |
| `last_releases` | INT | Computed | Releases collected in the last run. |
| `last_contributors` | INT | Computed | Contributors collected in the last run. |
| `last_duration_ms` | BIGINT | Computed | Duration of the last collection run in milliseconds. |
| `updated_at` | TIMESTAMPTZ | Auto-generated | Last update timestamp. |

**Index:** `(priority, due_at) WHERE status = 'queued'`

---

#### collection_status

Tracks the overall collection status for each repo across multiple pipeline stages (core, secondary, facade, ML).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_id` | BIGINT (PK, FK -> repos) | Computed | Repository. |
| `core_status` | TEXT | Computed | Status of core data collection (issues, PRs). Default `'Pending'`. |
| `core_task_id` | TEXT | Computed | Task ID for the core collection worker. |
| `core_data_last_collected` | TIMESTAMPTZ | Computed | When core data was last collected. |
| `core_weight` | BIGINT | Computed | Weight/size of core data workload. |
| `secondary_status` | TEXT | Computed | Status of secondary data collection (reviews, files). Default `'Pending'`. |
| `secondary_task_id` | TEXT | Computed | Task ID for the secondary worker. |
| `secondary_data_last_collected` | TIMESTAMPTZ | Computed | When secondary data was last collected. |
| `secondary_weight` | BIGINT | Computed | Weight of secondary data workload. |
| `facade_status` | TEXT | Computed | Status of facade/git log collection. Default `'Pending'`. |
| `facade_task_id` | TEXT | Computed | Task ID for the facade worker. |
| `facade_data_last_collected` | TIMESTAMPTZ | Computed | When facade data was last collected. |
| `facade_weight` | BIGINT | Computed | Weight of facade workload. |
| `event_last_collected` | TIMESTAMPTZ | Computed | When events were last collected. |
| `issue_pr_sum` | BIGINT | Computed | Total issues + PRs (used for workload estimation). |
| `commit_sum` | BIGINT | Computed | Total commits (used for workload estimation). |
| `ml_status` | TEXT | Computed | Status of ML pipeline. Default `'Pending'`. |
| `ml_task_id` | TEXT | Computed | Task ID for the ML worker. |
| `ml_data_last_collected` | TIMESTAMPTZ | Computed | When ML data was last produced. |
| `ml_weight` | BIGINT | Computed | Weight of ML workload. |
| `updated_at` | TIMESTAMPTZ | Auto-generated | Last update timestamp. |

---

### API Credentials

#### worker_oauth

OAuth tokens and API keys used by collection workers to authenticate with GitHub and GitLab APIs.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `oauth_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `name` | TEXT NOT NULL | User input | Descriptive name for this credential. |
| `consumer_key` | TEXT NOT NULL | User input | OAuth consumer key (for OAuth 1.0 flows). |
| `consumer_secret` | TEXT NOT NULL | User input | OAuth consumer secret. |
| `access_token` | TEXT NOT NULL | User input | Access token (PAT or OAuth token). |
| `access_token_secret` | TEXT NOT NULL | User input | OAuth access token secret. |
| `repo_directory` | TEXT | User input | Local directory for git operations using this credential. |
| `platform` | TEXT NOT NULL | User input | Platform name: `'github'` or `'gitlab'`. Default `'github'`. |
| `rate_limit` | INT | User input | Rate limit for this token. Default `5000`. |
| `created_at` | TIMESTAMPTZ | Auto-generated | When this credential was added. |

**Unique constraint:** `(access_token, platform)`

---

### Users and Auth

#### users

User accounts for the Aveloxis web interface and API.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `user_id` | SERIAL (PK) | Auto-generated | Primary key. |
| `login_name` | TEXT NOT NULL UNIQUE | User input | Username. |
| `login_hashword` | TEXT NOT NULL | User input | Hashed password. |
| `email` | TEXT NOT NULL UNIQUE | User input | Email address. |
| `text_phone` | TEXT UNIQUE | User input | Phone number for notifications. |
| `first_name` | TEXT NOT NULL | User input | First name. |
| `last_name` | TEXT NOT NULL | User input | Last name. |
| `admin` | BOOLEAN NOT NULL | User input | Whether this user is an admin. Default `FALSE`. |
| `email_verified` | BOOLEAN NOT NULL | Computed | Whether the email has been verified. Default `FALSE`. |
| | | | *Standard metadata columns* |

---

#### user_groups

Named groups of repositories curated by users. Users can organize repos into groups for easier management.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `group_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `user_id` | INT NOT NULL (FK -> users) | Computed | Owning user. |
| `name` | TEXT NOT NULL | User input | Group name. |
| `favorited` | BOOLEAN NOT NULL | User input | Whether this group is favorited. Default `FALSE`. |

**Unique constraint:** `(user_id, name)`

---

#### user_repos

Join table linking repos to user groups.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_id` | BIGINT NOT NULL (PK part 1) | User input | Repository. |
| `group_id` | BIGINT NOT NULL (PK part 2, FK -> user_groups) | User input | User group. |

**Primary key:** `(group_id, repo_id)`

---

#### client_applications

Registered API client applications (for OAuth flows).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `id` | TEXT (PK) | Auto-generated | Application ID. |
| `api_key` | TEXT NOT NULL | Auto-generated | API key for the application. |
| `user_id` | INT NOT NULL (FK -> users) | Computed | User who owns this application. |
| `name` | TEXT NOT NULL | User input | Application name. |
| `redirect_url` | TEXT NOT NULL | User input | OAuth redirect URL. |

---

#### user_session_tokens

Active session tokens for authenticated users.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `token` | TEXT (PK) | Auto-generated | Session token string. |
| `user_id` | INT NOT NULL (FK -> users) | Computed | User this session belongs to. |
| `created_at` | BIGINT | Auto-generated | Creation timestamp (Unix epoch). |
| `expiration` | BIGINT | Computed | Expiration timestamp (Unix epoch). |
| `application_id` | TEXT (FK -> client_applications) | Computed | Client application that initiated this session. |

---

#### refresh_tokens

Refresh tokens for renewing expired session tokens.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `id` | TEXT (PK) | Auto-generated | Refresh token string. |
| `user_session_token` | TEXT NOT NULL UNIQUE (FK -> user_session_tokens) | Computed | The session token this refresh token can renew. |

---

#### subscription_types

Types of event subscriptions available (for webhook/notification systems).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `name` | TEXT NOT NULL UNIQUE | User input | Subscription type name. |

---

#### subscriptions

Links client applications to the event types they are subscribed to.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `application_id` | TEXT NOT NULL (PK part 1, FK -> client_applications) | Computed | Client application. |
| `type_id` | BIGINT NOT NULL (PK part 2, FK -> subscription_types) | Computed | Subscription type. |

**Primary key:** `(application_id, type_id)`

---

### Configuration

#### augur_settings

Legacy settings store, maintained for compatibility with Augur tooling.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `setting` | TEXT | User input / Augur import | Setting name. |
| `value` | TEXT | User input / Augur import | Setting value. |
| `last_modified` | TIMESTAMPTZ | Auto-generated | Last modification timestamp. |

---

#### config

Structured configuration store with section/setting hierarchy.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `id` | SMALLSERIAL (PK) | Auto-generated | Primary key. |
| `section_name` | TEXT NOT NULL | User input | Configuration section (e.g., `"Database"`, `"Server"`). |
| `setting_name` | TEXT NOT NULL | User input | Setting name within the section. |
| `value` | TEXT | User input | Setting value. |
| `type` | TEXT | User input | Value type hint (e.g., `"str"`, `"int"`, `"bool"`). |

**Unique constraint:** `(section_name, setting_name)`

---

### GitHub Users (Affiliation Data)

#### github_users

Cached GitHub user data used for affiliation resolution. Populated by scanning commit authors and looking up their GitHub profiles.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `login` | TEXT | GitHub REST: `/search/users`, `/users/{login}` | GitHub login. |
| `email` | TEXT | GitHub REST: `/users/{login}` | Email address. |
| `affiliation` | TEXT | `aveloxis-commit-resolver` | Resolved organizational affiliation. |
| `source` | TEXT | `aveloxis-commit-resolver` | How the affiliation was determined. |
| `commits` | TEXT | Computed | Commit count. |
| `location` | TEXT | GitHub REST: `/users/{login}` | Profile location. |
| `country_id` | TEXT | Computed | Resolved country code. |

*No primary key defined.*

---

### Network Weighted Tables

Pre-computed weighted contributor-action matrices used for network analysis.

#### network_weighted_commits

Weighted commit activity per contributor per repository.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_id` | BIGINT | Computed | Repository. |
| `cntrb_id` | UUID | Computed | Contributor. |
| `weight` | FLOAT | Computed | Weight/score of the activity. |
| `action_type` | TEXT | Computed | Type of commit action. |
| `user_collection` | TEXT | Computed | Collection method used. |
| `data_collection_date` | TIMESTAMPTZ | Auto-generated | When this record was computed. |

*No primary key defined.*

---

#### network_weighted_issues

Weighted issue activity per contributor per repository.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_id` | BIGINT | Computed | Repository. |
| `cntrb_id` | UUID | Computed | Contributor. |
| `weight` | FLOAT | Computed | Weight/score of the activity. |
| `action_type` | TEXT | Computed | Type of issue action. |
| `user_collection` | TEXT | Computed | Collection method used. |
| `data_collection_date` | TIMESTAMPTZ | Auto-generated | When this record was computed. |

*No primary key defined.*

---

#### network_weighted_pr_reviews

Weighted PR review activity per contributor per repository.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_id` | BIGINT | Computed | Repository. |
| `cntrb_id` | UUID | Computed | Contributor. |
| `weight` | FLOAT | Computed | Weight/score of the activity. |
| `action_type` | TEXT | Computed | Type of review action. |
| `user_collection` | TEXT | Computed | Collection method used. |
| `data_collection_date` | TIMESTAMPTZ | Auto-generated | When this record was computed. |

*No primary key defined.*

---

#### network_weighted_prs

Weighted pull request activity per contributor per repository.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repo_id` | BIGINT | Computed | Repository. |
| `cntrb_id` | UUID | Computed | Contributor. |
| `weight` | FLOAT | Computed | Weight/score of the activity. |
| `action_type` | TEXT | Computed | Type of PR action. |
| `user_collection` | TEXT | Computed | Collection method used. |
| `data_collection_date` | TIMESTAMPTZ | Auto-generated | When this record was computed. |

*No primary key defined.*

---

### Worker Management

#### worker_history

Audit log of worker task executions. One row per worker run.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `history_id` | BIGSERIAL (PK) | Auto-generated | Primary key. |
| `repo_id` | BIGINT | Computed | Repository that was processed. |
| `worker` | TEXT NOT NULL | Computed | Worker name/identifier. |
| `job_model` | TEXT NOT NULL | Computed | Job model name. |
| `oauth_id` | INT | Computed | OAuth credential used. |
| `timestamp` | TIMESTAMPTZ NOT NULL | Auto-generated | When the worker ran. |
| `status` | TEXT NOT NULL | Computed | Outcome status (e.g., `"Success"`, `"Error"`). |
| `total_results` | INT | Computed | Number of records processed. |

---

#### worker_job

Persistent state for each worker job model. Tracks progress across restarts.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `job_model` | TEXT (PK) | Computed | Job model name (e.g., `"issues"`, `"pull_requests"`). |
| `state` | INT NOT NULL | Computed | Current state code. Default `0`. |
| `zombie_head` | INT | Computed | For zombie detection: expected head position. |
| `since_id_str` | TEXT NOT NULL | Computed | Cursor/since-ID for incremental collection. Default `'0'`. |
| `description` | TEXT | Computed | Human-readable description. |
| `last_count` | INT | Computed | Records processed in the last run. |
| `last_run` | TIMESTAMPTZ | Computed | When the job last ran. |
| `analysis_state` | INT | Computed | State of any post-collection analysis. |
| `oauth_id` | INT NOT NULL | Computed | Default OAuth credential for this job. |

---

#### worker_settings_facade

Configuration settings specific to the facade (git log) worker.

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `id` | INT (PK) | User input | Primary key. |
| `setting` | TEXT NOT NULL | User input | Setting name. |
| `value` | TEXT NOT NULL | User input | Setting value. |
| `last_modified` | TIMESTAMPTZ NOT NULL | Auto-generated | Last modification timestamp. |

---

### Fetch Log and Working Commits (ops)

#### repos_fetch_log (aveloxis_ops)

Operational fetch log (mirrors `aveloxis_data.repos_fetch_log` for the ops schema).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repos_id` | INT NOT NULL | Computed | Repository ID. |
| `status` | TEXT NOT NULL | Computed | Fetch status. |
| `date` | TIMESTAMPTZ NOT NULL | Auto-generated | Fetch attempt timestamp. |

*No primary key defined.*

---

#### working_commits (aveloxis_ops)

Operational working commit tracker (mirrors `aveloxis_data.working_commits` for the ops schema).

| Column | Type | Source | Description |
|--------|------|--------|-------------|
| `repos_id` | INT NOT NULL | Computed | Repository ID. |
| `working_commit` | TEXT | Git: `git log --numstat` (default branch only, per v0.16.4) | Current working commit hash. |

*No primary key defined.*

---

## Indexes

The schema defines the following additional indexes for query performance:

### aveloxis_data indexes

| Index | Table | Columns | Condition |
|-------|-------|---------|-----------|
| `idx_contributors_login` | contributors | `(cntrb_login)` | `WHERE cntrb_login != ''` |
| `idx_commits_repo_hash` | commits | `(repo_id, cmt_commit_hash)` | |
| `idx_commits_author_email` | commits | `(cmt_author_email)` | |
| `idx_commits_author_raw_email` | commits | `(cmt_author_raw_email)` | |
| `idx_commits_committer_raw_email` | commits | `(cmt_committer_raw_email)` | |
| `idx_commits_author_affiliation` | commits | `(cmt_author_affiliation)` | |
| `idx_dm_repo_annual_repo_aff` | dm_repo_annual | `(repo_id, affiliation)` | |
| `idx_issues_repo_id` | issues | `(repo_id)` | |
| `idx_issues_updated_at` | issues | `(updated_at)` | |
| `idx_pull_requests_repo_id` | pull_requests | `(repo_id)` | |
| `idx_pull_requests_updated_at` | pull_requests | `(updated_at)` | |
| `idx_messages_repo_id` | messages | `(repo_id)` | |
| `idx_issue_events_repo_id` | issue_events | `(repo_id)` | |
| `idx_pr_events_repo_id` | pull_request_events | `(repo_id)` | |
| `idx_releases_repo_id` | releases | `(repo_id)` | |
| `idx_repo_info_repo_id` | repo_info | `(repo_id)` | |
| `idx_contributor_identities_cntrb` | contributor_identities | `(cntrb_id)` | |
| `idx_commit_parents_cmt` | commit_parents | `(cmt_id)` | |
| `idx_repo_labor_repo_id` | repo_labor | `(repo_id)` | |
| `idx_repo_deps_libyear_repo_id` | repo_deps_libyear | `(repo_id)` | |
| `idx_repo_deps_scorecard_repo_id` | repo_deps_scorecard | `(repo_id)` | |
| `idx_repo_dependencies_repo_id` | repo_dependencies | `(repo_id)` | |

### aveloxis_ops indexes

| Index | Table | Columns | Condition |
|-------|-------|---------|-----------|
| `idx_staging_unprocessed` | staging | `(repo_id, entity_type)` | `WHERE NOT processed` |
| `idx_queue_due` | collection_queue | `(priority, due_at)` | `WHERE status = 'queued'` |