GitHub Archive

Purpose: Query immutable GitHub event history via BigQuery to obtain tamper-proof forensic evidence for security investigations.

When to Use This Skill

• Investigating security incidents involving GitHub repositories
• Building threat actor attribution profiles
• Verifying claims about repository activity (media reports, incident reports)
• Reconstructing attack timelines with definitive timestamps
• Analyzing automation system compromises
• Detecting supply chain reconnaissance
• Cross-repository behavioral analysis
• Workflow execution verification (legitimate vs API abuse)
• Pattern-based anomaly detection
• Recovering deleted content: PRs, issues, branches, tags, entire repositories

GitHub Archive analysis should be your FIRST step in any GitHub-related security investigation. Start with the immutable record, then enrich with additional sources.

Core Principles

ALWAYS PREFER GitHub Archive as forensic evidence over:

• Local git command outputs (git log, git show) - commits can be backdated/forged
• Unverified claims from articles or reports - require independent confirmation
• GitHub web interface screenshots - can be manipulated
• Single-source evidence - always cross-verify

GitHub Archive IS your ground truth for:

• Actor attribution (who performed actions)
• Timeline reconstruction (when events occurred)
• Event verification (what actually happened)
• Pattern analysis (behavioral fingerprinting)
• Cross-repository activity tracking
• Deleted content recovery (issues, PRs, tags, commit references remain in archive)
• Repository deletion forensics (commit SHAs persist even after repo deletion and history rewrites)

What Persists After Deletion

Deleted Issues & PRs:

• Issue creation events (IssuesEvent) remain in archive
• Issue comments (IssueCommentEvent) remain accessible
• PR open/close/merge events (PullRequestEvent) persist
• Forensic Value: Recover deleted evidence of social engineering, reconnaissance, or coordination

Deleted Tags & Branches:

• CreateEvent records for tag/branch creation persist
• DeleteEvent records document when deletion occurred
• Forensic Value: Reconstruct attack staging infrastructure (e.g., malicious payload delivery tags)

Deleted Repositories:

• All PushEvent records to the repository remain queryable
• Commit SHAs are permanently recorded in archive
• Fork relationships (ForkEvent) survive deletion
• Forensic Value: Access commit metadata even after threat actor deletes evidence

Deleted User Accounts:

• All activity events remain attributed to deleted username
• Timeline reconstruction remains possible
• Limitation: Direct code access lost, but commit SHAs can be searched elsewhere

Quick Start

All queries go through the typed wrapper libexec/raptor-bq-query: one read-only statement in (SELECT/WITH only — DML/DDL and multi-statement input are rejected), one JSON envelope out. Write the SQL to a file first, then invoke the wrapper.

Investigate if user opened PRs in June 2025:

Write query.sql:

SELECT
    created_at,
    repo.name AS repo_name,
    actor.login AS actor_login,
    JSON_EXTRACT_SCALAR(payload, '$.pull_request.number') as pr_number,
    JSON_EXTRACT_SCALAR(payload, '$.pull_request.title') as pr_title,
    JSON_EXTRACT_SCALAR(payload, '$.action') as action
FROM `githubarchive.day.202506*`
WHERE
    actor.login = 'suspected-actor'
    AND repo.name = 'target/repository'
    AND type = 'PullRequestEvent'
ORDER BY created_at

Then run it:

libexec/raptor-bq-query --query-file query.sql --output rows.json

rows.json holds the envelope: {"rows": [...], "row_count": N, "job": {"job_id": ..., "total_bytes_processed": ..., "total_bytes_billed": ..., "cache_hit": ...}, "dry_run": false}. Without --output, the envelope prints on stdout.

Expected Output (if PR exists):

2025-06-15 14:23:11 UTC: PR #123 - opened
  Title: Add new feature
2025-06-20 09:45:22 UTC: PR #123 - closed
  Title: Add new feature

Interpretation:

• No results → Claim disproven (no PR activity found)
• Results found → Claim verified, proceed with detailed analysis

Setup

Prerequisites

1. Google Cloud Project:

   • Login to Google Developer Console
   • Create a project and activate BigQuery API
   • Create a service account with BigQuery User role
   • Download JSON credentials file

2. Install BigQuery Client (used by the wrapper under the hood):

pip install google-cloud-bigquery google-auth

Credentials

Set GOOGLE_APPLICATION_CREDENTIALS to the service-account key file path (or the inline JSON itself). Scope the service account to the read-only BigQuery User role — that credential boundary, not the wrapper's statement validation, is what makes this surface read-only.

Egress posture

By default the wrapper runs the BigQuery client in a network-pinned sandbox: the only reachable hosts are {bigquery.googleapis.com, oauth2.googleapis.com, www.googleapis.com} plus the token_uri host declared in the key file. The operator can replace the allowlist via ~/.config/raptor/bq-proxy-hosts.json ({"hosts": [...]}), and --no-sandbox falls back to the host's ambient network (needed for gcloud ADC / metadata-server credentials, which are unreachable inside the sandbox).

Free Tier: Google provides 1 TB of data processed per month free.

Cost Management & Query Optimization

Understanding GitHub Archive Costs

BigQuery charges $6.25 per TiB of data scanned (after the 1 TiB free tier). GitHub Archive tables are large - a single month table can be 50-100 GB, and yearly wildcards can scan multiple TiBs. Unoptimized queries can cost $10-100+, while optimized versions of the same query cost $0.10-1.00.

Key Cost Principle: BigQuery uses columnar storage - you pay for ALL data in the columns you SELECT, not just matching rows. A query with SELECT * on one day of data scans ~3 GB even with LIMIT 10.

ALWAYS Estimate Costs Before Querying

CRITICAL RULE: Run a dry run to estimate costs before executing any query against GitHub Archive production tables.

libexec/raptor-bq-query --query-file query.sql --dry-run

Output:

{"dry_run": true, "total_bytes_processed": 128849018880, "gigabytes_processed": 120.0, "estimated_cost_usd": 0.7324}

If estimated_cost_usd exceeds $1.00, review the optimization techniques below before proceeding (and see the ask-the-user thresholds in the next section).

When to Ask the User About Costs

ASK USER BEFORE RUNNING if any of these conditions apply:

1. Estimated cost > $1.00 - Always confirm with user for queries over $1
2. Wildcard spans > 3 months - Queries like githubarchive.day.2025* scan entire year (~400 GB)
3. No partition filter - Queries without date/time filters scan entire table range
4. SELECT * used - Selecting all columns dramatically increases cost
5. Cross-repository searches - Queries without repo.name filter scan all GitHub activity

Example user confirmation:

Query estimate: 120 GB ($0.75)
Scanning: githubarchive.day.202506* (June 2025, 30 days)
Reason: Cross-repository search for actor 'suspected-user'

This exceeds typical query cost ($0.10-0.30). Proceed? [y/n]

DON'T ASK if:

• Estimated cost < $0.50 AND query is well-scoped (specific repo + date range)
• User explicitly requested broad analysis (e.g., "scan all of 2025")

Cost Optimization Techniques for GitHub Archive

1. Select Only Required Columns (50-90% cost reduction)

-- ❌ EXPENSIVE: Scans ALL columns (~3 GB per day)
SELECT * FROM `githubarchive.day.20250615`
WHERE actor.login = 'target-user'

-- ✅ OPTIMIZED: Scans only needed columns (~0.3 GB per day)
SELECT
    type,
    created_at,
    repo.name,
    actor.login,
    JSON_EXTRACT_SCALAR(payload, '$.action') as action
FROM `githubarchive.day.20250615`
WHERE actor.login = 'target-user'

Never use SELECT * in production queries. Always specify exact columns needed.

2. Use Specific Date Ranges (10-100x cost reduction)

-- ❌ EXPENSIVE: Scans entire year (~400 GB)
SELECT ... FROM `githubarchive.day.2025*`
WHERE actor.login = 'target-user'

-- ✅ OPTIMIZED: Scans specific month (~40 GB)
SELECT ... FROM `githubarchive.day.202506*`
WHERE actor.login = 'target-user'

-- ✅ BEST: Scans single day (~3 GB)
SELECT ... FROM `githubarchive.day.20250615`
WHERE actor.login = 'target-user'

Strategy: Start with narrow date ranges (1-7 days), then expand if needed. Use monthly tables (githubarchive.month.202506) for multi-month queries instead of daily wildcards.

3. Filter by Repository Name (5-50x cost reduction)

-- ❌ EXPENSIVE: Scans all GitHub activity
SELECT ... FROM `githubarchive.day.202506*`
WHERE actor.login = 'target-user'

-- ✅ OPTIMIZED: Filter by repo (BigQuery can prune data blocks)
SELECT ... FROM `githubarchive.day.202506*`
WHERE
    repo.name = 'target-org/target-repo'
    AND actor.login = 'target-user'

Rule: Always include repo.name filter when investigating a specific repository.

4. Avoid SELECT * with Wildcards (Critical)

-- ❌ CATASTROPHIC: Can scan 1+ TiB ($6.25+)
SELECT * FROM `githubarchive.day.2025*`
WHERE type = 'PushEvent'

-- ✅ OPTIMIZED: Scans ~50 GB ($0.31)
SELECT
    created_at,
    actor.login,
    repo.name,
    JSON_EXTRACT_SCALAR(payload, '$.ref') as branch
FROM `githubarchive.day.2025*`
WHERE type = 'PushEvent'

5. Use LIMIT Correctly (Does NOT reduce cost on GHArchive)

IMPORTANT: LIMIT does not reduce BigQuery costs on non-clustered tables like GitHub Archive. BigQuery must scan all matching data before applying LIMIT.

-- ❌ MISCONCEPTION: Still scans full dataset
SELECT * FROM `githubarchive.day.20250615`
LIMIT 100  -- Cost: ~3 GB scanned

-- ✅ CORRECT: Use WHERE filters and column selection
SELECT type, created_at, actor.login
FROM `githubarchive.day.20250615`
WHERE repo.name = 'target/repo'  -- Cost: ~0.2 GB scanned
LIMIT 100

Safe Query Execution Template

Use this sequence for all GitHub Archive queries in production:

# Step 1: dry-run estimate (validates the query, scans nothing)
libexec/raptor-bq-query --query-file query.sql --dry-run

# Step 2: check the printed estimated_cost_usd against your budget
#         (ask the user per the thresholds above if it's high)

# Step 3: execute with a bytes-billed safety cap — the job FAILS
#         rather than bills more than this
libexec/raptor-bq-query --query-file query.sql --max-bytes-billed 100000000000 --output rows.json

The wrapper always applies a maximum_bytes_billed cap — the default is 200 GB (~$1.14); tighten it to the dry-run estimate plus ~20% headroom, or raise it explicitly for deliberately broad scans.

Common Investigation Patterns: Cost Comparison

Development vs Production Queries

During investigation/development:

1. Start with single-day queries to test pattern: githubarchive.day.20250615
2. Verify query returns expected results
3. Expand to date range only after validation: githubarchive.day.202506*

Production checklist:

• Used specific column names (no SELECT *)
• Included narrowest possible date range
• Added repo.name filter if investigating specific repository
• Ran dry run and verified cost < $1.00 (or got user approval)
• Set maximum_bytes_billed in query config

Cost Monitoring

Track your BigQuery spending with this query:

-- View GitHub Archive query costs (last 7 days)
SELECT
    DATE(creation_time) as query_date,
    COUNT(*) as queries,
    ROUND(SUM(total_bytes_billed) / (1024*1024*1024), 2) as total_gb,
    ROUND(SUM(total_bytes_billed) / (1024*1024*1024*1024) * 6.25, 2) as cost_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
    creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
    AND job_type = 'QUERY'
    AND REGEXP_CONTAINS(query, r'githubarchive\.')
GROUP BY query_date
ORDER BY query_date DESC

Schema Reference

Table Organization

Dataset: githubarchive

Table Patterns:

• Daily tables: githubarchive.day.YYYYMMDD (e.g., githubarchive.day.20250713)
• Monthly tables: githubarchive.month.YYYYMM (e.g., githubarchive.month.202507)
• Yearly tables: githubarchive.year.YYYY (e.g., githubarchive.year.2025)

Wildcard Patterns:

• All days in June 2025: githubarchive.day.202506*
• All months in 2025: githubarchive.month.2025*
• All data in 2025: githubarchive.year.2025*

Data Availability: February 12, 2011 to present (updated hourly)

Schema Structure

Top-Level Fields:

type              -- Event type (PushEvent, IssuesEvent, etc.)
created_at        -- Timestamp when event occurred (UTC)
actor.login       -- GitHub username who performed the action
actor.id          -- GitHub user ID
repo.name         -- Repository name (org/repo format)
repo.id           -- Repository ID
org.login         -- Organization login (if applicable)
org.id            -- Organization ID
payload           -- JSON string with event-specific data

Payload Field: JSON-encoded string containing event-specific details. Must be parsed with JSON_EXTRACT_SCALAR() in SQL or json.loads() in Python.

Event Types Reference

Repository Events

PushEvent - Commits pushed to a repository

-- Payload fields:
JSON_EXTRACT_SCALAR(payload, '$.ref')        -- Branch (refs/heads/master)
JSON_EXTRACT_SCALAR(payload, '$.before')     -- SHA before push
JSON_EXTRACT_SCALAR(payload, '$.after')      -- SHA after push
JSON_EXTRACT_SCALAR(payload, '$.size')       -- Number of commits
-- payload.commits[] contains array of commit objects with sha, message, author

PullRequestEvent - Pull request opened, closed, merged

-- Payload fields:
JSON_EXTRACT_SCALAR(payload, '$.action')              -- opened, closed, merged
JSON_EXTRACT_SCALAR(payload, '$.pull_request.number')
JSON_EXTRACT_SCALAR(payload, '$.pull_request.title')
JSON_EXTRACT_SCALAR(payload, '$.pull_request.merged') -- true/false

CreateEvent - Branch or tag created

-- Payload fields:
JSON_EXTRACT_SCALAR(payload, '$.ref_type')   -- branch, tag, repository
JSON_EXTRACT_SCALAR(payload, '$.ref')        -- Name of branch/tag

DeleteEvent - Branch or tag deleted

-- Payload fields:
JSON_EXTRACT_SCALAR(payload, '$.ref_type')   -- branch or tag
JSON_EXTRACT_SCALAR(payload, '$.ref')        -- Name of deleted ref

ForkEvent - Repository forked

-- Payload fields:
JSON_EXTRACT_SCALAR(payload, '$.forkee.full_name')  -- New fork name

Automation & CI/CD Events

WorkflowRunEvent - GitHub Actions workflow run status changes

-- Payload fields:
JSON_EXTRACT_SCALAR(payload, '$.action')               -- requested, completed
JSON_EXTRACT_SCALAR(payload, '$.workflow_run.name')
JSON_EXTRACT_SCALAR(payload, '$.workflow_run.path')    -- .github/workflows/file.yml
JSON_EXTRACT_SCALAR(payload, '$.workflow_run.status')  -- queued, in_progress, completed
JSON_EXTRACT_SCALAR(payload, '$.workflow_run.conclusion') -- success, failure, cancelled
JSON_EXTRACT_SCALAR(payload, '$.workflow_run.head_sha')
JSON_EXTRACT_SCALAR(payload, '$.workflow_run.head_branch')

WorkflowJobEvent - Individual job within workflow CheckRunEvent - Check run status (CI systems) CheckSuiteEvent - Check suite for commits

Issue & Discussion Events

IssuesEvent - Issue opened, closed, edited

-- Payload fields:
JSON_EXTRACT_SCALAR(payload, '$.action')        -- opened, closed, reopened
JSON_EXTRACT_SCALAR(payload, '$.issue.number')
JSON_EXTRACT_SCALAR(payload, '$.issue.title')
JSON_EXTRACT_SCALAR(payload, '$.issue.body')

IssueCommentEvent - Comment on issue or pull request PullRequestReviewEvent - PR review submitted PullRequestReviewCommentEvent - Comment on PR diff

Other Events

WatchEvent - Repository starred ReleaseEvent - Release published MemberEvent - Collaborator added/removed PublicEvent - Repository made public

Investigation Patterns

Deleted Issue & PR Text Recovery

Scenario: Issue or PR was deleted from GitHub (by author, maintainer, or moderation) but you need to recover the original title and body text for investigation, compliance, or historical reference.

Step 1: Recover Deleted Issue Content

SELECT
    created_at,
    actor.login,
    JSON_EXTRACT_SCALAR(payload, '$.action') as action,
    JSON_EXTRACT_SCALAR(payload, '$.issue.number') as issue_number,
    JSON_EXTRACT_SCALAR(payload, '$.issue.title') as title,
    JSON_EXTRACT_SCALAR(payload, '$.issue.body') as body
FROM `githubarchive.day.20250713`
WHERE
    repo.name = 'aws/aws-toolkit-vscode'
    AND actor.login = 'lkmanka58'
    AND type = 'IssuesEvent'
ORDER BY created_at

Step 2: Recover Deleted PR Description

SELECT
    created_at,
    actor.login,
    JSON_EXTRACT_SCALAR(payload, '$.action') as action,
    JSON_EXTRACT_SCALAR(payload, '$.pull_request.number') as pr_number,
    JSON_EXTRACT_SCALAR(payload, '$.pull_request.title') as title,
    JSON_EXTRACT_SCALAR(payload, '$.pull_request.body') as body,
    JSON_EXTRACT_SCALAR(payload, '$.pull_request.merged') as merged
FROM `githubarchive.day.202506*`
WHERE
    repo.name = 'target/repository'
    AND actor.login = 'target-user'
    AND type = 'PullRequestEvent'
ORDER BY created_at

Evidence Recovery:

• Issue/PR Title: Full title text preserved in $.issue.title or $.pull_request.title
• Issue/PR Body: Complete body text preserved in $.issue.body or $.pull_request.body
• Comments: IssueCommentEvent preserves comment text in $.comment.body
• Actor Attribution: actor.login identifies who created the content
• Timestamps: Exact creation time in created_at

Real Example: Amazon Q investigation recovered deleted issue content from lkmanka58. The issue titled "aws amazon donkey aaaaaaiii aaaaaaaiii" contained a rant calling Amazon Q "deceptive" and "scripted fakery". The full issue body was preserved in GitHub Archive despite deletion from github.com, providing context for the timeline reconstruction.

Deleted PRs

Scenario: Media claims attacker submitted a PR in "late June" containing malicious code, but PR is now deleted and cannot be found on github.com.

Step 1: Query Archive — write the SQL, then run it through the wrapper:

SELECT
    type,
    created_at,
    repo.name AS repo_name,
    JSON_EXTRACT_SCALAR(payload, '$.action') as action,
    JSON_EXTRACT_SCALAR(payload, '$.pull_request.number') as pr_number,
    JSON_EXTRACT_SCALAR(payload, '$.pull_request.title') as pr_title
FROM `githubarchive.day.202506*`
WHERE
    actor.login = 'suspected-actor'
    AND repo.name = 'target/repository'
    AND type = 'PullRequestEvent'
ORDER BY created_at

libexec/raptor-bq-query --query-file q-deleted-prs.sql --output rows.json

Step 2: Analyze Results — read rows.json:

• "row_count": 0 → Claim disproven: no PR activity found in June 2025
• rows present → Verified: each row's pr_number / action / created_at / pr_title documents the PR lifecycle

Evidence Validation:

• Claim TRUE: Archive shows PullRequestEvent with action='opened'
• Claim FALSE: No events found → claim disproven
• Investigation Outcome: Definitively verify or refute timeline claims

Real Example: Amazon Q investigation verified no PR from attacker's account in late June 2025, disproving media's claim of malicious code committed via deleted PR.

Deleted Repository Forensics

Scenario: Threat actor creates staging repository, pushes malicious code, then deletes repo to cover tracks.

Step 1: Find Repository Activity

SELECT
    type,
    created_at,
    JSON_EXTRACT_SCALAR(payload, '$.ref') as ref,
    repo.name AS repo_name,
    payload
FROM `githubarchive.day.2025*`
WHERE
    actor.login = 'threat-actor'
    AND type IN ('CreateEvent', 'PushEvent')
    AND (
        JSON_EXTRACT_SCALAR(payload, '$.repository.name') = 'staging-repo'
        OR repo.name LIKE 'threat-actor/staging-repo'
    )
ORDER BY created_at

libexec/raptor-bq-query --query-file q-staging-repo.sql --output rows.json

Step 2: Extract Commit SHAs — unnest in SQL rather than post-processing, so the SHAs land directly in the output rows:

SELECT
    created_at,
    JSON_EXTRACT_SCALAR(commit, '$.sha') as commit_sha,
    JSON_EXTRACT_SCALAR(commit, '$.message') as commit_message
FROM `githubarchive.day.2025*`,
UNNEST(JSON_EXTRACT_ARRAY(payload, '$.commits')) as commit
WHERE
    actor.login = 'threat-actor'
    AND type = 'PushEvent'
    AND repo.name LIKE 'threat-actor/staging-repo'
ORDER BY created_at

Evidence Recovery:

• CreateEvent reveals repository creation timestamp
• PushEvent records contain commit SHAs and metadata
• Commit SHAs can be used to recover code content via other archives or forks
• Investigation Outcome: Complete reconstruction of attacker's staging infrastructure

Real Example: lkmanka58/code_whisperer repository deleted after attack, but GitHub Archive revealed June 13 creation with 3 commits containing AWS IAM role assumption attempts.

Deleted Tag Analysis

Scenario: Malicious tag used for payload delivery, then deleted to hide evidence.

Step 1: Search for Tag Events

SELECT
    type,
    created_at,
    actor.login,
    JSON_EXTRACT_SCALAR(payload, '$.ref') as tag_name,
    JSON_EXTRACT_SCALAR(payload, '$.ref_type') as ref_type
FROM `githubarchive.day.20250713`
WHERE
    repo.name = 'target/repository'
    AND type IN ('CreateEvent', 'DeleteEvent')
    AND JSON_EXTRACT_SCALAR(payload, '$.ref_type') = 'tag'
ORDER BY created_at

Timeline Reconstruction:

2025-07-13 19:41:44 UTC | CreateEvent | aws-toolkit-automation | tag 'stability'
2025-07-13 20:30:24 UTC | PushEvent   | aws-toolkit-automation | commit references tag
2025-07-14 08:15:33 UTC | DeleteEvent | aws-toolkit-automation | tag 'stability' deleted

Analysis: 48-hour window between tag creation and deletion reveals staging period for attack infrastructure.

Real Example: Amazon Q attack used 'stability' tag for malicious payload delivery. Tag was deleted, but CreateEvent in GitHub Archive preserved creation timestamp and actor, proving 48-hour staging window.

Deleted Branch Reconstruction

Scenario: Attacker creates development branch with malicious code, pushes commits, then deletes branch after merging or to cover tracks.

Step 1: Find Branch Lifecycle

SELECT
    type,
    created_at,
    actor.login,
    JSON_EXTRACT_SCALAR(payload, '$.ref') as branch_name,
    JSON_EXTRACT_SCALAR(payload, '$.ref_type') as ref_type
FROM `githubarchive.day.2025*`
WHERE
    repo.name = 'target/repository'
    AND type IN ('CreateEvent', 'DeleteEvent')
    AND JSON_EXTRACT_SCALAR(payload, '$.ref_type') = 'branch'
ORDER BY created_at

Step 2: Extract All Commit SHAs from Deleted Branch

SELECT
    created_at,
    actor.login as pusher,
    JSON_EXTRACT_SCALAR(payload, '$.ref') as branch_ref,
    JSON_EXTRACT_SCALAR(commit, '$.sha') as commit_sha,
    JSON_EXTRACT_SCALAR(commit, '$.message') as commit_message,
    JSON_EXTRACT_SCALAR(commit, '$.author.name') as author_name,
    JSON_EXTRACT_SCALAR(commit, '$.author.email') as author_email
FROM `githubarchive.day.2025*`,
UNNEST(JSON_EXTRACT_ARRAY(payload, '$.commits')) as commit
WHERE
    repo.name = 'target/repository'
    AND type = 'PushEvent'
    AND JSON_EXTRACT_SCALAR(payload, '$.ref') = 'refs/heads/deleted-branch-name'
ORDER BY created_at

Evidence Recovery:

• Commit SHAs: All commit identifiers permanently recorded in PushEvent payload
• Commit Messages: Full commit messages preserved in commits array
• Author Metadata: Name and email from commit author field
• Pusher Identity: Actor who executed the push operation
• Temporal Sequence: Exact timestamps for each push operation
• Branch Lifecycle: Complete creation-to-deletion timeline

Forensic Value: Even after branch deletion, commit SHAs can be used to:

• Search for commits in forked repositories
• Check if commits were merged into other branches
• Search external code archives (Software Heritage, etc.)
• Reconstruct complete attack development timeline

Automation vs Direct API Attribution

Scenario: Suspicious commits appear under automation account name. Determine if they came from legitimate GitHub Actions workflow execution or direct API abuse with compromised token.

Step 1: Search for Workflow Events During Suspicious Window

SELECT
    type,
    created_at,
    actor.login AS actor_login,
    JSON_EXTRACT_SCALAR(payload, '$.workflow_run.name') as workflow_name,
    JSON_EXTRACT_SCALAR(payload, '$.workflow_run.head_sha') as commit_sha,
    JSON_EXTRACT_SCALAR(payload, '$.workflow_run.conclusion') as conclusion
FROM `githubarchive.day.20250713`
WHERE
    repo.name = 'org/repository'
    AND type IN ('WorkflowRunEvent', 'WorkflowJobEvent')
    AND created_at >= '2025-07-13T20:25:00Z'
    AND created_at <= '2025-07-13T20:35:00Z'
ORDER BY created_at

libexec/raptor-bq-query --query-file q-workflow-window.sql --output workflow-window.json

Step 2: Establish Baseline Pattern

SELECT
    type,
    created_at,
    actor.login AS actor_login,
    JSON_EXTRACT_SCALAR(payload, '$.workflow_run.name') as workflow_name
FROM `githubarchive.day.20250713`
WHERE
    repo.name = 'org/repository'
    AND actor.login = 'automation-account'
    AND type = 'WorkflowRunEvent'
ORDER BY created_at

libexec/raptor-bq-query --query-file q-workflow-baseline.sql --output workflow-baseline.json

Step 3: Analyze Results

• workflow-window.json has "row_count": 0 → direct API attack: no WorkflowRunEvent during the suspicious commit window, so the commit was NOT from legitimate workflow execution
• rows present → legitimate workflow execution; each row's workflow_name / conclusion / created_at documents the run
• compare against the baseline file's row count and timing cluster

Expected Results if Legitimate Workflow:

2025-07-13 20:30:15 UTC | WorkflowRunEvent | deploy-automation | requested
2025-07-13 20:30:24 UTC | PushEvent        | aws-toolkit-automation | refs/heads/main
2025-07-13 20:31:08 UTC | WorkflowRunEvent | deploy-automation | completed

Expected Results if Direct API Abuse:

2025-07-13 20:30:24 UTC | PushEvent | aws-toolkit-automation | refs/heads/main
[NO WORKFLOW EVENTS IN ±10 MINUTE WINDOW]

Investigation Outcome: Absence of WorkflowRunEvent = Direct API attack with stolen token

Real Example: Amazon Q investigation needed to determine if malicious commit 678851bbe9776228f55e0460e66a6167ac2a1685 (pushed July 13, 2025 20:30:24 UTC by aws-toolkit-automation) came from compromised workflow or direct API abuse. GitHub Archive query showed ZERO WorkflowRunEvent or WorkflowJobEvent records during the 20:25-20:35 UTC window. Baseline analysis revealed the same automation account had 18 workflows that day, all clustered in 20:48-21:02 UTC. The temporal gap and complete workflow absence during the malicious commit proved direct API attack, not workflow compromise.

Troubleshooting

Wrapper errors (raptor-bq-query prints one structured JSON line on stderr: {"error": "<kind>", "message": ..., "exit_code": N}):

• exit 3 validation — query rejected (not SELECT/WITH, or multi-statement); the wrapper is read-only by design
• exit 5 dependency — pip install google-cloud-bigquery google-auth
• exit 6 credentials — set GOOGLE_APPLICATION_CREDENTIALS; in sandboxed (default) mode gcloud ADC is unavailable, use a key file
• exit 7 query — BigQuery API error, including the --max-bytes-billed cap firing; dry-run and re-size the cap
• exit 8 timeout — raise --timeout or narrow the query
• exit 9 sandbox — sandbox could not launch; --no-sandbox runs unpinned as a fallback
• a 403 Forbidden from inside the sandbox that names a host means the egress allowlist denied it — check ~/.config/raptor/bq-proxy-hosts.json

Permission denied errors:

• Verify service account has BigQuery User role
• Check credentials file path is correct
• Ensure BigQuery API is enabled in Google Cloud project

Query exceeds free tier (>1TB):

• Use daily tables instead of wildcard: githubarchive.day.20250615
• Add date filters: WHERE created_at >= '2025-06-01' AND created_at < '2025-07-01'
• Limit columns: Select only needed fields, not SELECT *
• Use monthly tables for broader searches: githubarchive.month.202506

No results for known event:

• Verify date range (archive starts Feb 12, 2011)
• Check timezone (GitHub Archive uses UTC)
• Confirm actor.login spelling (case-sensitive)
• Some events may take up to 1 hour to appear (hourly updates)

Payload extraction returns NULL:

• Verify JSON path exists with JSON_EXTRACT() before using JSON_EXTRACT_SCALAR()
• Check event type has that payload field (not all events have all fields)
• Inspect raw payload: SELECT payload FROM ... LIMIT 1

Query timeout or slow performance:

• Add repo.name filter when possible (significantly reduces data scanned)
• Use specific date ranges instead of wildcards
• Consider using monthly aggregated tables for long-term analysis
• Partition queries by date and run in parallel

Force Push Recovery (Zero-Commit PushEvents)

Scenario: Developer accidentally commits secrets, then force pushes to "delete" the commit. The commit remains accessible on GitHub, but finding it requires knowing the SHA.

Background: When a developer runs git reset --hard HEAD~1 && git push --force, Git removes the reference to that commit from the branch. However:

• GitHub stores these "dangling" commits indefinitely
• GitHub Archive records the before SHA in PushEvent payloads
• Force pushes appear as PushEvents with zero commits (empty commits array)

Step 1: Find All Zero-Commit PushEvents (Organization-Wide)

SELECT
    created_at,
    actor.login,
    repo.name,
    JSON_EXTRACT_SCALAR(payload, '$.before') as deleted_commit_sha,
    JSON_EXTRACT_SCALAR(payload, '$.head') as current_head,
    JSON_EXTRACT_SCALAR(payload, '$.ref') as branch
FROM `githubarchive.day.2025*`
WHERE
    repo.name LIKE 'target-org/%'
    AND type = 'PushEvent'
    AND JSON_EXTRACT_SCALAR(payload, '$.size') = '0'
ORDER BY created_at DESC

Step 2: Search for Specific Repository

SELECT
    created_at,
    actor.login,
    JSON_EXTRACT_SCALAR(payload, '$.before') as deleted_commit_sha,
    JSON_EXTRACT_SCALAR(payload, '$.head') as after_sha,
    JSON_EXTRACT_SCALAR(payload, '$.ref') as branch
FROM `githubarchive.day.202506*`
WHERE
    repo.name = 'org/repository'
    AND type = 'PushEvent'
    AND JSON_EXTRACT_SCALAR(payload, '$.size') = '0'
ORDER BY created_at

Step 3: Bulk Recovery Query

SELECT
    created_at,
    actor.login AS actor_login,
    repo.name AS repo_name,
    JSON_EXTRACT_SCALAR(payload, '$.before') as deleted_sha,
    JSON_EXTRACT_SCALAR(payload, '$.ref') as branch
FROM `githubarchive.year.2024`
WHERE
    type = 'PushEvent'
    AND JSON_EXTRACT_SCALAR(payload, '$.size') = '0'
    AND repo.name LIKE 'target-org/%'

libexec/raptor-bq-query --query-file q-force-pushes.sql --dry-run
libexec/raptor-bq-query --query-file q-force-pushes.sql --output force-pushes.json

The envelope's row_count is the number of force-pushed commits to investigate; each row carries the recoverable deleted_sha. (Year tables are large — always dry-run first.)

Evidence Recovery:

• before SHA: The commit that was "deleted" by the force push
• head SHA: The commit the branch was reset to
• ref: Which branch was force pushed
• actor.login: Who performed the force push
• Commit Access: Use recovered SHA to access commit via GitHub API or web UI

Forensic Applications:

• Secret Scanning: Scan recovered commits for leaked credentials, API keys, tokens
• Incident Timeline: Identify when secrets were committed and when they were "hidden"
• Attribution: Determine who committed secrets and who attempted to cover them up
• Compliance: Prove data exposure window for breach notifications

Real Example: Security researcher Sharon Brizinov scanned all zero-commit PushEvents since 2020 across GitHub, recovering "deleted" commits and scanning them for secrets. This technique uncovered credentials worth $25k in bug bounties, including an admin-level GitHub PAT with access to all Istio repositories (36k stars, used by Google, IBM, Red Hat). The token could have enabled a massive supply-chain attack.

Important Notes:

• Force pushing does NOT delete commits from GitHub - they remain accessible via SHA
• GitHub Archive preserves the before SHA indefinitely
• Zero-commit PushEvents are the forensic fingerprint of history rewrites
• This technique provides 100% coverage of "deleted" commits (vs brute-forcing 4-char SHA prefixes)

Learn More

• GH Archive Documentation: https://www.gharchive.org/
• GitHub Event Types Schema: https://docs.github.com/en/rest/using-the-rest-api/github-event-types
• BigQuery Documentation: https://cloud.google.com/bigquery/docs
• BigQuery SQL Reference: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
• Force Push Scanner Tool: https://github.com/trufflesecurity/force-push-scanner