Kospex Data Schemas

This document describes the database schemas used by Kospex for storing git repository analytics data.

Kospex uses two database systems:

• SQLite (kospex.db) - Primary database for all kospex data
• DuckDB (kospex-git.duckdb) - High-performance analytics database for git commit data

Database Locations

DuckDB Schema

The DuckDB database is optimized for high-performance analytics on git commit and file change data. It contains two tables focused on git history analysis.

Source: /src/kospex/git_duckdb.py

commits

Stores git commit metadata. Schema inspired by Mergestat git-commits sync.

Primary Key: (hash, _repo_id)

Indexes:

• idx_commits_when on committer_when
• idx_commits_repo on _repo_id

commit_files

Stores file-level changes per commit. Schema inspired by Mergestat git-commit-stats sync.

Primary Key: (hash, file_path, _repo_id)

Indexes:

• idx_files_path on file_path

SQLite Schema

The SQLite database is the primary data store for all kospex data including commits, file metadata, dependencies, and configuration.

Source: /src/kospex_schema.py

Baseline Schema Version: 2 (frozen in kospex_schema.py)

Columns added by later migrations are marked (migration NNNN) below. A DB that has had kospex upgrade-db -apply run against it is at the latest migrated version. For the authoritative list of changes since the baseline, see src/kospex/db/migrations/.

Core Tables

commits

Git commit history data.

Primary Key: (_repo_id, hash)

commit_files

File changes per commit.

Primary Key: (hash, file_path, _repo_id)

repos

Repository metadata and sync status.

Primary Key: (_repo_id)

The last_sync_hash / last_panopticas_version / last_scc_version columns are the provenance of the last successful file_metadata sync, used by the sync skip-guard to decide whether a rebuild is needed. last_fetch is distinct from last_sync (when kospex last read the clone into the DB) and last_seen (the newest commit date).

File Analysis Tables

file_metadata

File metadata from external tools (primarily scc).

Primary Key: (Provider, hash, _repo_id)

repo_hotspots

Hotspot indicators for repositories.

Primary Key: (_repo_id, hash)

Dependency Analysis Tables

dependency_data

Software composition analysis (SCA) data.

Primary Key: (_repo_id, hash, file_path, package_type, package_name, package_version)

Branch Tables

branches

Current branch data for repositories.

Primary Key: (_repo_id)

branch_history

Historical branch data for repositories.

Primary Key: (_repo_id, hash)

Metadata and Observations Tables

commit_metadata

Additional metadata about commits.

Primary Key: None defined

observations

Tool observations and analysis results.

Primary Key: (_repo_id, hash, file_path, observation_key, latest)

krunner

Command output storage from batch processing.

Primary Key: (_repo_id, hash, file_path)

Identity Management Tables

email_map

Maps email aliases to canonical email addresses.

Primary Key: (alias_email)

Configuration Tables

kospex_config

Key-value configuration storage.

Primary Key: (key, latest)

kospex_groups

Grouping configuration for repositories and users.

Primary Key: (group_name, _repo_id, email)

Utility Tables

url_cache

HTTP response caching.

Primary Key: (url)

Views

commits_view

Joins commits with email_map to provide canonical email addresses.

SELECT
    c.*,
    COALESCE(m.main_email, c.author_email) as canonical_email
FROM commits c
LEFT JOIN email_map m ON c.author_email = m.alias_email;

Repository Identifier Format

The _repo_id field uses the format: GIT_SERVER~OWNER~REPO

Examples:

• github.com~kospex~kospex
• bitbucket.org~myorg~myrepo

This format is used consistently across both databases and all tables containing repository data.

Tables with Repository Context

The following tables include _repo_id for repository-scoped queries:

• commits
• commit_files
• commit_metadata
• file_metadata
• repo_hotspots
• dependency_data
• krunner
• observations
• repos
• kospex_groups
• branches
• branch_history