[GitHub Audit-CLI] - Reduce repeated data calls and overlap

👀 Why are we Doing this?

• As a - auditor of the GitHub estate
• I want - to enable the audit-cli scripts to utilise a pull-once model for shared repo data across scripts
• So That - The API call overhead is reduced and the scripts operate more efficiently

🔧 Engineering Context and Background (Optional)

• The solution should align with our teams agreed Ways of Engineering.
• Currently each script independently pulls data to separate SQLite storage layers, this has led to some data, such as basic repository metadata (repo details, visibility, default branch, etc) being repeatedly pulled, even though it's already been collected.
• This creates unnecessary API overhead at full estate scale, and means scripts cannot benefit from data already stored.
• Investigate and implement an pull-once model where core/common metadata is collected once, and stored in a shared table e.g. repositories in a unified database - the individual scripts should then be able to read from this, rather than re-pulling.

📝 Notes (Optional)

• This is a follow-up from @spennymoj's work with DevX github_api_call_overlap_analysis.docx

Recommended pull-once model - This should be a starting point, re-assess the data the scripts pull to verify the document's findings are still accurate.

1. repo_inventory: Pull once per repo: org, repo, full_name, private, archived, fork, pushed_at, default_branch, language, open_issues, stargazers, created_at, updated_at, size, description, is_template.

2. repo_security: Pull once per repo: branch protection, rulesets, CODEOWNERS, Dependabot status, Dependabot alerts, code scanning alerts, secret scanning alerts, dependency graph, security_and_analysis settings.

3. repo_workflows: Pull once per repo: workflow files, workflow count, and enough workflow contents to detect actions/tests/linting.

4. repo_alerts: Pull once per repo/type: Dependabot, code scanning and secret scanning alert details, including created_at, remediated_at, state and severity.

5. org_security: Pull once per org: 2FA requirement, default permissions, members without 2FA, outside collaborators, teams, webhooks, installed apps, self-hosted runners, Actions policy and workflow permissions.

6. repo_large_files: Pull once per repo only if needed: largest blob details and size thresholds.

Implementation note

Main practical change: treat list_repos.py / repo inventory as the base collection layer, and stop archive_repos.py from re-pulling basic repository metadata. Archive output should consume the cached inventory and add only archive-specific fields or derived values.

Suggested architecture: separate collection from reporting. First collect normalised data into a repo_data table/cache. Then run scripts as report generators over that shared data. This keeps the same output coverage while reducing duplicate API calls.

✅ Definition of Done

• Document outcome, where appropriate
• Shared repositories table defined and populated by list_repos as the primary collector
• archive_repos updated to read core repo metadata from shared table, and only re-pull if necessary
• API call reduction quantified - before/after script execution times documented
• Pattern documented for extension to other scripts, and follow-up tickets raised where applicable

Start by reading list_repos.py and archive_repos.py, then trace how each script collects and stores shared repository metadata in SQLite. Compare their current API calls and storage paths against the recommended pull-once model. Done means a shared repositories table is populated by list_repos, archive_repos reads it, API reduction is quantified, and the extension pattern is documented.