chaoss / chaoss/CollectOSS

Request: Add index on data.commits.cmt_filename for file-path lookups

Open
#446 2 comments 0 reactions 0 assignees View on GitHub
database
Dominant language
Python
Stars
13
Forks
17
Avg merge
6h 59m
Merged PRs (30d)
1

Description

## Use Case

We're building a scanner that detects AI policies across open source projects (checking for files like AGENTS.md, CLAUDE.md, AI_POLICY.md, CONTRIBUTING.md, etc.). The scanner checks ~34 target file paths per repo via the GitHub API.

On a 2,000-repo scan using the CollectOSS database, 98% of repos came back as "silent" (no policy files). That means we're making ~40-50 API calls per repo on ~1,960 repos that have nothing — roughly 80k wasted API calls per batch, taking about 3 hours.

## Proposed Optimization

Pre-filter using the Augur DB before hitting the GitHub API:

1. Query `data.commits.cmt_filename` to check which repos have any of our 34 target files in their commit history
2. Skip the 98% that don't — zero API calls for those
3. Only fetch file contents via GitHub API for repos the DB confirms have relevant files

This would reduce API calls by ~20-30x and bring a 3-hour batch down to ~10-15 minutes.

## The Problem

Neither `data.commits.cmt_filename` nor `data.pull_request_files.pr_file_path` has an index. Every query does a full table scan.

I tested 12 repos — 11 out of 12 timed out at a 10-second query timeout. The one that completed (apache/airflow) took 78 seconds without a timeout.

## Requested Index

```sql
CREATE INDEX idx_commits_repo_filename ON data.commits (repo_id, cmt_filename);
```

And optionally:

```sql
CREATE INDEX idx_pr_files_repo_path ON data.pull_request_files (repo_id, pr_file_path);
```

The query pattern would be:

```sql
SELECT DISTINCT cmt_filename
FROM data.commits
WHERE repo_id = (SELECT repo_id FROM data.repo WHERE repo_git = %s)
AND cmt_filename = ANY(%s)
```

This should drop from 78s to sub-second per repo.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.