Request: Add index on data.commits.cmt_filename for file-path lookups
- 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
Assessment
This issue has not been assessed yet.