PR Tracker

A web and desktop application to track and visualize GitHub pull request metrics over time.

• Track open PRs, unassigned PRs, and PRs older than 30 days
• Traffic light indicators (🟢🟡🔴) based on approval status
• Compare snapshots to see what changed between two points in time
• Auto-loads the most recent snapshot on startup
• Store snapshots in DuckDB with automatic checkpoint on shutdown
• Visualize trends with interactive charts
• Reviewer workload breakdown with comments and approvals
• Deep linking URLs for sharing specific views
• Approval status filters (All, Ready, Partial, Needs Review)
• React frontend with Vite
• Flask backend with REST API
• Electron desktop app with bundled backend and persistent database

.
├── track_open_prs.py                  # Main script to fetch and store PR data
├── backend/
│   ├── db/
│   │   └── schema.sql                # Database schema
│   ├── local/
│   │   ├── server.py                 # Flask API server
│   │   ├── test_server.py            # Backend tests
│   │   └── test_server_properties.py # Property-based tests
│   └── requirements.txt
├── frontend/
│   ├── src/
│   │   ├── App.jsx                   # Main React component
│   │   ├── App.test.jsx              # Frontend tests
│   │   ├── api.js                    # API fetch helper (web + Electron)
│   │   ├── main.jsx
│   │   └── index.css
│   ├── package.json
│   ├── vite.config.js
│   └── index.html
├── electron/
│   ├── main.js                       # Electron main process
│   ├── preload.js                    # Context bridge for renderer
│   └── package.json                  # Electron + electron-builder config
└── skills/
    └── pr_tracker_skill.md           # AI agent skill for Claude Code and Kiro

This application requires sensitive credentials that should never be committed to git:

1. Copy the example environment file:

   cp .env.example .env

2. Edit .env and add your credentials:

   • GITHUB_TOKEN - Create a personal access token at https://github.com/settings/tokens
     • For public repos: public_repo scope
     • For private repos: repo scope
   • Never commit your .env file (it's already in .gitignore)

3. Load environment variables:

   # Option 1: Export manually
   export GITHUB_TOKEN="your_token_here"
   
   # Option 2: Use a tool like direnv or python-dotenv
   source .env

• ✅ Use read-only GitHub tokens when possible
• ✅ Rotate tokens regularly
• ✅ Never commit tokens or .env files to git
• ✅ Use environment-specific tokens (dev vs prod)
• ⚠️ In production, disable Flask debug mode
• ⚠️ In production, restrict CORS to specific origins
• ⚠️ Use HTTPS in production deployments

1. Install Python dependencies:

cd backend
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

2. Set up environment variables (see Security section above)

3. Start the local server:

cd local
python server.py

The API will be available at http://localhost:5001

1. Install Node dependencies:

cd frontend
npm install

2. Start the development server:

npm run dev

The frontend will be available at http://localhost:5173

The Electron app bundles both the frontend and backend into a single distributable application. The Flask server runs as a child process inside the app.

cd electron
npm install
npm run dev

In dev mode, Electron will check if the Vite dev server is running on localhost:5173. If it is, it uses that (with hot reload). If not, it falls back to the pre-built frontend in electron/frontend-dist/. The Flask backend is spawned using the project's backend/.venv/bin/python3.

To rebuild the frontend for Electron after making changes:

cd frontend
npx vite build --mode electron

Prerequisites:

• Node.js and npm
• Python 3.12+ with PyInstaller (pip install pyinstaller)

cd electron
npm install

# Build everything: frontend + backend binary + packaged app
npm run build

The output will be in electron/release/.

If you prefer to run each step individually:

cd electron

# 1. Build the React frontend
npm run build:frontend

# 2. Bundle the Flask backend into a standalone executable
npm run build:backend

# 3. Package the Electron app
npm run dist

To test the packaged app without creating a distributable installer:

npm run pack    # creates unpacked app in electron/release/

# Standard build (current architecture only)
npm run build

# Universal binary (Intel + Apple Silicon)
npm run build:backend:mac
npm run dist

Output in electron/release/:

• .dmg — disk image installer
• .zip — zipped app bundle

To open the unpacked app: open electron/release/mac*/Pull\ Request\ Tracker\ Dashboard.app

Note: The app is not code-signed by default. On first launch, right-click the app and select "Open" to bypass Gatekeeper, or sign it with your Apple Developer certificate.

npm run build

Output in electron/release/:

• .exe (NSIS installer) — standard Windows installer
• Portable .exe — no installation required

Note: Building Windows targets on macOS/Linux requires Wine. For best results, build on a Windows machine. PyInstaller must also run on Windows to produce a Windows binary.

npm run build

Output in electron/release/:

• .AppImage — portable, runs on most distros without installation
• .deb — Debian/Ubuntu package

To run the AppImage:

chmod +x electron/release/*.AppImage
./electron/release/*.AppImage

Note: PyInstaller must run on Linux to produce a Linux binary. Cross-compilation is not supported.

1. Electron's main process finds an available port (starting at 5001)
2. Spawns the Flask backend as a child process (PyInstaller binary in production, venv Python in dev)
3. Waits for the backend to respond to health checks
4. Injects window.__BACKEND_PORT__ into the HTML so the frontend knows where to send API calls
5. Loads the React frontend in a BrowserWindow
6. On startup, the most recent snapshot is automatically loaded and displayed
7. On quit, DuckDB is checkpointed (CHECKPOINT command) to flush all data to disk

The database (pr_tracker.duckdb) is stored in the OS user data directory and persists across app restarts:

• macOS: ~/Library/Application Support/Pull Request Tracker Dashboard/
• Linux: ~/.config/Pull Request Tracker Dashboard/
• Windows: %APPDATA%/Pull Request Tracker Dashboard/

To start fresh, use File → Reset Database... from the menu bar. This deletes the database file, restarts the backend, and reloads the frontend with a clean state.

Set up environment variables:

export GITHUB_TOKEN="your-github-token"
export GITHUB_REPO="awslabs/aws-athena-query-federation"

Run the script to store a snapshot:

python track_open_prs.py --store

This will:

1. Fetch all open PRs from GitHub
2. Get reviewer information for each PR
3. Store the snapshot in the database via the API

1. Make sure both backend and frontend servers are running (or use the Electron app)
2. Open http://localhost:5173 in your browser
3. The most recent snapshot loads automatically on startup
4. Click on any snapshot to see the PRs from that point in time
5. Use approval filters (🟢🟡🔴) to focus on PRs by review status
6. Use the Compare Snapshots tab to diff two snapshots

To publish a report to Quip instead of storing:

export QUIP_TOKEN="your-quip-token"
python track_open_prs.py

To update an existing Quip doc:

export QUIP_DOC_ID="doc-id"
python track_open_prs.py

Set up a cron job to collect data regularly:

# Run every 6 hours
0 */6 * * * cd /path/to/pr-tracker && /path/to/.venv/bin/python track_open_prs.py --store

The skills/pr_tracker_skill.md file defines an agent skill named pr-tracker. The skill builds an HTML report of open PRs for a GitHub repository. The report includes summary cards, an age histogram, and a table color-coded by approval status. The skill can also publish the report to a SharePoint page.

The skill calls GitHub and SharePoint through Model Context Protocol (MCP) tools. Configure these MCP servers in your agent before you use the skill:

• GitHub MCP server (required). The skill lists open PRs and reads their reviews.
• SharePoint MCP server (optional). The skill uses it only to publish the report.

The skill file names tools such as github_pat__list_pull_requests and run_python. Your agent might expose these tools under different names. If a tool name does not match, edit the skill to use the names that your agent exposes.

Claude Code loads each skill from <skills-dir>/<skill-name>/SKILL.md. Install the skill for all your projects:

mkdir -p ~/.claude/skills/pr-tracker
cp skills/pr_tracker_skill.md ~/.claude/skills/pr-tracker/SKILL.md

To install it for this project only, use .claude/skills/pr-tracker/SKILL.md in the repository root instead.

Start a new Claude Code session and run /skills to confirm that pr-tracker appears. Invoke the skill with /pr-tracker, or ask for it directly:

/pr-tracker https://github.com/awslabs/aws-athena-query-federation

Kiro uses the same SKILL.md layout. Install the skill for all your workspaces:

mkdir -p ~/.kiro/skills/pr-tracker
cp skills/pr_tracker_skill.md ~/.kiro/skills/pr-tracker/SKILL.md

To install it for this workspace only, use .kiro/skills/pr-tracker/SKILL.md in the repository root instead.

Restart Kiro (IDE or kiro-cli chat) so that it discovers the skill. Then ask Kiro to run it:

Use the pr-tracker skill to report on open PRs in https://github.com/awslabs/aws-athena-query-federation

The copy in your skills directory does not track changes to this repository. Re-run the cp command after you pull updates. To track changes automatically, replace the copy with a symbolic link:

ln -sf "$(pwd)/skills/pr_tracker_skill.md" ~/.claude/skills/pr-tracker/SKILL.md
ln -sf "$(pwd)/skills/pr_tracker_skill.md" ~/.kiro/skills/pr-tracker/SKILL.md

• GET /api/stats - Get latest snapshot, 30-day trend, and reviewer workload
• GET /api/stats?threshold=14 - Custom age threshold for "old PRs" metric
• GET /api/snapshots?days=30 - Get snapshots from last N days
• GET /api/snapshots/<id>/prs - Get PRs for a specific snapshot
• GET /api/snapshots/<id>/reviewers - Get reviewer workload for a snapshot
• GET /api/snapshots/compare?snapshot1=X&snapshot2=Y - Compare two snapshots
• POST /api/snapshots - Create a new snapshot
• DELETE /api/snapshots/<id> - Delete a snapshot and its associated data
• POST /api/import - Trigger a live data import

The database has three main tables:

• pr_snapshots - Stores summary metrics for each snapshot
• prs - Stores individual PR details linked to snapshots
• pr_comments - Stores comment counts per reviewer per PR

DuckDB is used instead of SQLite for better analytical query performance. The schema uses CREATE IF NOT EXISTS so it is safe to run on an existing database.

Backend tests (22 tests):

cd backend/local
python -m pytest test_server.py -v

Frontend tests (20 tests):

cd frontend
npm test
# or for UI mode
npm run test:ui
# or for coverage
npm run test:coverage

The Flask server supports these environment variables:

• Backend: Modify backend/local/server.py
• Frontend: Edit React components in frontend/src/
• Database: Update backend/db/schema.sql and reinitialize
• Electron: Modify electron/main.js for main process changes

Apache-2.0