BPD Daily Activity Scraper

Automated scraper for the Bellingham Police Department's Daily Activity Log. Scrapes incidents from the police press summary form, parses them, geocodes locations, and displays on a live dashboard.

Status

LIVE (May 16, 2026) - Landing page: https://apps.tlehane.com/ - Dashboard (LAN): http://192.168.68.100/dashboard/ - Dashboard (public): https://apps.tlehane.com/dashboard/ - Data: 19,582+ incidents (Jan 1, 2025 – present) - Geocoding: 97.9% coverage - Automation: Daily cron at 7:30 AM, CI/CD via GitLab on push to master - Access: Cloudflare Turnstile gate (12-hour session)

Quick Links

• Setup & Deployment: setup/README.md
• Deployment Guide (manual): docs/DEPLOYMENT_GUIDE.md
• API schema: https://apps.tlehane.com/openapi.yaml (OpenAPI 3.0.3)
• Development: bpd_scraper/QUICK_START.md

Scraper Usage

# Yesterday's log (default)
python scraper.py

# Specific date
python scraper.py --date 2026-04-23

# Date range
python scraper.py --start 2026-04-01 --end 2026-04-30

# Normalize and write to PostgreSQL
python parse_logs.py --postgres

# Geocode ungeocode incidents
python geocode_locations.py

Architecture

Police Form (police.cob.org)
      ↓
  Playwright form interaction
      ↓
  HTML response → BeautifulSoup
      ↓
   JSONL (audit trail in bpd_scraper/data/)
      ↓
  Normalize/validate (parse_logs.py)
      ↓
PostgreSQL incidents table
      ↓
Nominatim geocoding (geocode_locations.py)
      ↓
Flask dashboard (/dashboard/*)
  └─ Turnstile gate (/dashboard/gate)
      ↓
Nginx (port 80)
  ├─ /           → /var/www/landing/ (static HTML + Turnstile widget)
  └─ /dashboard/ → gunicorn :8000
      ↓
Cloudflare Tunnel → apps.tlehane.com
  ├─ /turnstile-verify  → Worker (token validation for landing page)
  └─ /openapi.yaml      → Worker (API schema, edge-cached)

Stack: - Scraper: Playwright + BeautifulSoup, runs as cron job at 7:30 AM - Database: PostgreSQL, bpd DB, bpd_user - Dashboard: Flask + Bootstrap 5, served under SCRIPT_NAME=/dashboard - Auth: Cloudflare Turnstile — gate on Flask (12-hour session), widget on landing page verified via Worker - Server: Rocky Linux 10, gunicorn on port 8000, nginx on port 80 - CI/CD: GitLab pipeline, push to master auto-deploys to server (Flask app + landing page) - Public access: Cloudflare Tunnel — no open inbound ports - Cloudflare Workers: turnstile-verify (token siteverify), bpd-openapi (schema)

Configuration

All production env vars are set as Environment= directives in /etc/systemd/system/flaskapp.service:

To update a var: edit the service file, then sudo systemctl daemon-reload && sudo systemctl restart flaskapp.

bpd_scraper/ scripts load .env from their own directory first, then fall back to the parent (/opt/flaskapp/.env). On the production server these scripts inherit env vars from the systemd service — do not create a stale bpd_scraper/.env.

Database Schema

CREATE TABLE incidents (
    id                SERIAL PRIMARY KEY,
    incident_date     TEXT,
    incident_time     TEXT,
    case_number       TEXT,
    location          TEXT,
    incident_type     TEXT,
    description       TEXT,
    scraped_at        TEXT,
    query_start       TEXT,
    query_end         TEXT,
    parse_status      TEXT,
    raw_extra         TEXT,
    location_lat      NUMERIC(11,8),
    location_lon      NUMERIC(11,8),
    geocode_status    VARCHAR(20) DEFAULT 'unknown',
    CONSTRAINT uq_incidents UNIQUE (case_number, incident_date)
);

Useful Queries

-- Row count and latest date
SELECT COUNT(*), MAX(incident_date) FROM incidents;

-- Geocoding coverage
SELECT ROUND(100.0 * COUNT(location_lat) / COUNT(*), 1) AS pct FROM incidents;

-- Recent incidents
SELECT incident_date, incident_time, incident_type, location
FROM incidents ORDER BY incident_date DESC LIMIT 10;

File Structure

bpd_scraper/
├── scraper.py              # Fetch incidents from police form
├── parse_logs.py           # Normalize JSONL → PostgreSQL
├── geocode_locations.py    # Nominatim geocoding
├── run_daily_win.py        # Cron entry point (scrape + parse + geocode)
├── requirements.txt
├── .env.example
└── data/
    ├── raw/                # Raw HTML (audit trail)
    ├── parsed/             # Normalized JSONL
    └── output/             # CSV exports

dashboard/
├── app.py                  # Flask application factory + before_request gate hook
├── db.py                   # Database connection pool
├── config.py               # Config from environment (DB, Turnstile, session)
├── routes/
│   ├── gate.py             # /dashboard/gate — Turnstile access gate
│   ├── incidents.py        # /dashboard/incidents table + CSV export
│   ├── stats.py            # /dashboard/stats + /dashboard/map pages
│   └── api.py              # /dashboard/api/* JSON endpoints
├── templates/
│   ├── gate.html           # Turnstile challenge page
│   └── ...
└── static/

landing/
├── index.html              # Landing page (dark theme, Turnstile-gated cards)
└── openapi.yaml            # Copy of docs/openapi.yaml (served by nginx)

docs/
└── openapi.yaml            # OpenAPI 3.0.3 schema for all dashboard endpoints

setup/
├── bootstrap_rocky_v2.sh   # One-time server provisioning
├── install_gitlab_runner.sh # Register CI/CD runner on server
└── redeploy.sh             # Full fresh-deploy from scratch

app.py                      # Gunicorn entry point (imports dashboard.app)
.gitlab-ci.yml              # CI/CD pipeline (push to master → deploy Flask + landing)

Logs

Troubleshooting

Dashboard not loading:

ssh -i ~/.ssh/id_rsa [email protected]
sudo systemctl status flaskapp nginx
sudo journalctl -xeu flaskapp.service -n 50

Data not updating (cron):

ssh -i ~/.ssh/id_rsa [email protected]
sudo -u flaskapp crontab -l                         # verify entry exists
tail -50 /opt/flaskapp/bpd_scraper/logs/cron.log   # check last run

# Check today's scraper log specifically:
cat /opt/flaskapp/bpd_scraper/logs/scraper_$(date +%Y-%m-%d).log

Scraper runs but records 0 incidents (Playwright not installed):

Symptom in scraper_YYYY-MM-DD.log:

Playwright failed: BrowserType.launch: Executable doesn't exist at .../chromium_headless

Fix:

ssh -i ~/.ssh/id_rsa [email protected]
sudo -u flaskapp /opt/flaskapp/.venv/bin/playwright install chromium

The CI/CD pipeline now runs playwright install chromium on every deploy to prevent this recurring after version bumps.

PostgreSQL connection failed:

PGPASSWORD=$(ssh [email protected] "sudo cat /root/.flaskapp_db_password") \
  psql -U bpd_user -d bpd -h 192.168.68.100 -c "\dt"

Backfill missing dates:

ssh -i ~/.ssh/id_rsa [email protected]
cd /opt/flaskapp
sudo -u flaskapp .venv/bin/python bpd_scraper/scraper.py --start 2026-05-10 --end 2026-05-13
sudo -u flaskapp .venv/bin/python bpd_scraper/parse_logs.py --postgres
sudo -u flaskapp .venv/bin/python bpd_scraper/geocode_locations.py