Capitol Trades Data Pipeline

An automated data engineering pipeline that scrapes, processes, and serves congressional stock trading disclosures through a normalized database and RESTful API.

📖 Overview

This project is a fully automated web scraping system that collects trade disclosure data from Capitol Trades, enriches it with real-time market data from financial APIs, and stores it in a normalized PostgreSQL database. The data is exposed through a secure REST API with role-based access control, and visualized through an interactive web dashboard.

The system runs as a distributed scraping network across multiple Raspberry Pi devices, with scheduled jobs ensuring data freshness and comprehensive logging for monitoring and debugging.

✨ Key Features

• 🔄 Automated Web Scraping — Playwright-based browser automation with retry logic and error handling
• 📡 API Integration — Real-time market data from FinnHub and CoinMarketCap APIs
• 🗃️ Normalized Database — Star schema design with dimension tables and fact tables
• 🔐 Secure REST API — Role-based access with API key authentication
• 📊 Interactive Dashboard — Searchable, filterable, paginated data views
• 📅 Scheduled Execution — Cron-based automation with comprehensive logging

🛠️ Tech Stack

🏗️ System Architecture

┌─────────────────────────────────────────────────────────────────────────┐
│                        DATA COLLECTION LAYER                            │
│  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────────────┐  │
│  │   Playwright    │  │    FinnHub      │  │    CoinMarketCap        │  │
│  │   Scraper       │  │    Stock API    │  │    Crypto API           │  │
│  │ (Capitol Trades)│  │   (Prices)      │  │   (Prices/Tickers)      │  │
│  └────────┬────────┘  └────────┬────────┘  └────────────┬────────────┘  │
│           │                    │                        │               │
│           └────────────────────┼────────────────────────┘               │
│                                ▼                                        │
├─────────────────────────────────────────────────────────────────────────┤
│                        DATA PROCESSING LAYER                            │
│  ┌──────────────────────────────────────────────────────────────────┐   │
│  │                     Python ETL Pipeline                          │   │
│  │  • Date parsing & normalization    • Ticker validation           │   │
│  │  • Fuzzy matching (difflib)        • Duplicate detection         │   │
│  │  • Options parsing (calls/puts)    • Error handling & retries    │   │
│  └──────────────────────────────────────────────────────────────────┘   │
│                                ▼                                        │
├─────────────────────────────────────────────────────────────────────────┤
│                        DATA STORAGE LAYER                               │
│  ┌──────────────────────────────────────────────────────────────────┐   │
│  │                    PostgreSQL Database                           │   │
│  │  ┌────────────┐  ┌────────────┐  ┌────────────┐  ┌────────────┐  │   │
│  │  │ politicians│  │  parties   │  │  chambers  │  │  tickers   │  │   │
│  │  │ (dimension)│  │ (dimension)│  │ (dimension)│  │ (dimension)│  │   │
│  │  └────────────┘  └────────────┘  └────────────┘  └────────────┘  │   │
│  │  ┌────────────────────────────────────────────────────────────┐  │   │
│  │  │              normalized_trades (fact table)                │  │   │
│  │  │  politician_id │ party_id │ ticker_id │ traded_date │ ...  │  │   │
│  │  └────────────────────────────────────────────────────────────┘  │   │
│  │  ┌────────────────────────────────────────────────────────────┐  │   │
│  │  │                    prices (time-series)                    │  │   │
│  │  └────────────────────────────────────────────────────────────┘  │   │
│  └──────────────────────────────────────────────────────────────────┘   │
│                                ▼                                        │
├─────────────────────────────────────────────────────────────────────────┤
│                         API & PRESENTATION                              │
│  ┌─────────────────────────┐      ┌─────────────────────────────────┐   │
│  │      FastAPI REST       │      │        Flask Dashboard          │   │
│  │  • GET/POST endpoints   │      │  • Interactive tables           │   │
│  │  • API key auth         │      │  • Charts & visualizations      │   │
│  │  • Role-based access    │      │  • Pagination & filtering       │   │
│  └─────────────────────────┘      └─────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────────┘

🗄️ Database Schema

The database follows a normalized star schema design, separating dimension tables (politicians, parties, chambers, tickers) from the central fact table (normalized_trades). This architecture enables efficient queries, reduces data redundancy, and supports complex analytics.

Dimension Tables

Fact Tables

🕷️ Scraper Pipeline

The scraper uses Playwright to automate a headless Chromium browser, navigating through paginated trade listings and extracting structured data. The pipeline includes intelligent error handling, duplicate detection, and automatic ticker validation.

# Scraper configuration
MAX_RETRIES = 3
BASE_URL = "https://www.capitoltrades.com/trades?pageSize=100&page="
STOP_ON_DUPLICATE = False  # Continue or halt on duplicate detection

# Logging setup
capitol_scraper_logger = logging.getLogger("capitol_scraper_normalized")
db_inserts_logger = logging.getLogger("db_inserts")
debug_logger = logging.getLogger("normalized_insert")

🔐 REST API

The data is exposed through a secure FastAPI backend with role-based access control. API keys grant different permission levels, enabling both read-only dashboards and read-write integrations for automated systems.

Authentication

# All requests require an API key header
headers = {
    "Content-Type": "application/json",
    "api-key": "YOUR_API_KEY"
}

# Roles: admin, read-write, read-only

Available Endpoints

Query Examples

# Get trades by a specific politician
GET /get_capitol_trades?fields=politician,trade_ticker&filter=politician=Nancy Pelosi

# Get trades within a date range
GET /get_capitol_trades?fields=trade_ticker,traded_date&date_from=2024-01-01&date_to=2024-12-31

# Get trades by type with pagination
GET /get_capitol_trades?filter=type=Purchase&page=1&limit=50

📊 Technical Specifications

✅ Implemented Features

🚀 Future Enhancements

• ⬜ Real-time WebSocket price feeds
• ⬜ Machine learning trade pattern detection
• ⬜ Politician portfolio tracking over time
• ⬜ Automated alert system for significant trades
• ⬜ Integration with additional data sources (SEC filings)
• ⬜ GraphQL API endpoint
• ⬜ Data export (CSV, JSON, Excel)