Chicago Alderman Newsletter Tracker

Searchable archive of newsletters from Chicago aldermen. Built for Strong Towns Chicago.

Tech Stack

• Frontend: Astro v5 + Tailwind CSS v4 + Cloudflare Pages
• Backend: Python 3.x + uv package manager
• Database: Supabase PostgreSQL
• Email: Gmail IMAP polling
• LLM: Ollama (local) or OpenAI (cloud) for summarization/categorization

Features

• Email Ingestion: Gmail IMAP polling with automatic source matching
• Web Scraping: MailChimp archive scraping for historical newsletters
• LLM Processing: Topic extraction, summarization
• Full-Text Search: Search with filters (alderman/source, ward, topic, date range) and sorting (newest, oldest)
• Ward Pages: A page per ward (/ward/N) with the alderman's contact info and newsletters, plus a ward directory (/wards)
• Monthly Reports: Prerendered citywide pages (/reports) counting how many wards' newsletters mentioned each topic
• User Notifications: Daily digest emails for newsletters matching user-defined rules, or weekly topic reports
• One-Click Unsubscribe: Secure, JWT-based unsubscribe links in emails (RFC 8058 compliant)
• Privacy Protection: Automatic removal of tracking links, unsubscribe URLs, and sensitive content
• Testing Suite: Backend unit/integration tests, frontend unit/E2E tests (Playwright)

Database Schema

Core Tables:

• sources - Aldermen and officials (name, ward, email, archive URLs)
• email_source_mappings - Pattern matching for email → source identification
• newsletters - Full newsletter content with LLM-extracted metadata (topics, summary)

Notification Tables:

• user_profiles - User settings and notification preferences
• notification_rules - User-defined alert criteria (topics, search terms, wards)
• notification_queue - Pending notifications awaiting delivery
• notification_history - Audit log of sent emails

Full schema details: See backend/SCHEMA.md

Content Storage

Newsletters are stored in dual formats in the newsletters table:

• plain_text - Primary format used for search indexing, LLM processing (topics, summaries), and notification keyword matching
• raw_html - Preserves formatting for frontend display

Both formats are independently sanitized during ingestion to remove tracking links and privacy-sensitive content.

Quick Start

Backend Setup

cd backend
uv sync

# Create .env with:
# GMAIL_ADDRESS, GMAIL_APP_PASSWORD
# SUPABASE_URL, SUPABASE_SERVICE_KEY
# ENABLE_LLM=true, OLLAMA_MODEL=gpt-oss:20b

Backend Scripts

Email Ingestion

# Process unread Gmail newsletters (queues notifications if ENABLE_NOTIFICATIONS=true)
uv run python -m cli.process_emails

Web Scraping

# Scrape all aldermen with newsletter_archive_url set
uv run python -m cli.process_scraped_newsletters

# Scrape specific source (source_id, archive_url, optional limit)
uv run python -m cli.process_scraped_newsletters 1 "https://..." 10

Utilities

# Reprocess newsletters with current LLM prompts
uv run python -m cli.process_llm_metadata --latest 10

# Reapply privacy sanitization to existing newsletters
uv run python -m cli.reprocess_newsletters_privacy --all --update --quiet

# Refresh the /analytics dashboard, ward summaries and /reports: compute, check
# http://localhost:4321/analytics, then commit frontend/public/analytics-data/ and frontend/src/generated/
# and push; the deploy publishes them. The JSON files are public.
# (fetches only newsletters newer than the local snapshot in backend/data/; --offline skips the database,
#  --full-refresh rebuilds it from every newsletter: needed once on a fresh checkout; run it when the site is quiet)
uv run python -m cli.compute_analytics

Notifications

# Test rule matching against recent newsletters (dry run)
uv run python -m cli.try_rule_matcher

# Test and queue notifications
uv run python -m cli.try_rule_matcher --queue

# Send daily digest (dry run)
uv run python -m cli.process_notification_queue --daily-digest --dry-run

# Send today's daily digest
uv run python -m cli.process_notification_queue --daily-digest

# Send specific date's digest
uv run python -m cli.process_notification_queue --daily-digest --batch-id 2026-01-21

Weekly Reports

# Generate weekly reports for active topics
uv run python -m cli.process_weekly_reports

# Queue notifications for generated reports
uv run python -m cli.weekly_notification_queue

# Send weekly digest emails
uv run python -m cli.process_notification_queue --weekly-digest

Testing

# Backend tests
uv run python -m unittest discover -s tests

# Backend database tests, against a running local Supabase stack (see CLAUDE.md)
eval "$(bash ../scripts/supabase-test-env.sh)"
uv run python -m unittest discover -s tests/db_integration -t .

# Frontend tests
cd ../frontend
npm run test      # Unit tests
npm run test:e2e  # E2E tests (Playwright), on the local Supabase stack: npx supabase start --workdir .. (Docker)
npm run lint

Development checks

# From the repo root: CI's gates, in CI's order (no argument runs both)
bash scripts/check.sh [backend|frontend]

# Once per clone: fast checks on staged files before each commit
git config core.hooksPath .githooks

Frontend Setup

cd frontend
npm install
npm run dev

# Deploy: push to GitHub, connect to Cloudflare Pages

LLM Setup

Option A: Ollama (local inference)

curl -fsSL https://ollama.com/install.sh | sh
ollama pull gpt-oss:20b
# Use with: --model gpt-oss:20b (default) or --model ollama:gpt-oss:20b

Option B: OpenAI (cloud)

# Add to backend/.env:
# OPENAI_API_KEY=sk-...
# LLM_MODEL=openai:gpt-5

Project Structure

backend/
  ├── ingest/           # Email and web scraping ingestion
  ├── processing/       # LLM topic extraction and summarization
  ├── notifications/    # User alerts and daily digest delivery
  ├── cli/              # Command-line entry points (ingestion, digests, reprocess, migrate, analytics)
  ├── tests/            # Test suite
  │   ├── unit/         # Unit tests
  │   ├── integration/  # Integration tests
  │   ├── db_integration/ # Tests against the local Supabase stack
  │   └── fixtures/     # Test data and fixtures
  ├── config/           # Privacy patterns configuration
  ├── shared/           # Shared database client and date helpers
  └── migrations/       # Database migration SQL files

frontend/
  ├── src/pages/        # Astro pages (index, search, wards, ward pages, preferences, newsletter detail)
  ├── src/pages/api/    # API routes for notification management
  ├── src/lib/          # Supabase client
  └── tests/            # Frontend unit tests

LLM Processing

Two LLM calls per newsletter for topic extraction and summarization. Supports Ollama (local) and OpenAI (cloud) via provider-prefixed model strings. See backend/processing/llm_client.py for provider dispatch and backend/processing/llm_processor.py for topic definitions.

Notification System

The system supports two types of notifications:

1. Daily Digest: Immediate updates when newsletters match user rules (topics, search phrases, wards).
2. Weekly Topic Report: AI-synthesized summary of all activity for a subscribed topic over the past week.

Users configure these preferences via the frontend. Email ingestion automatically queues daily notifications. Weekly reports are generated via a scheduled process.

Key files:

• backend/notifications/rule_matcher.py - Matching logic
• backend/processing/weekly_report_generator.py - Weekly report LLM synthesis
• backend/notifications/email_sender.py - Email delivery via Resend
• frontend/src/pages/preferences.astro - User preference management
• frontend/src/pages/unsubscribe.astro - One-click unsubscribe

Privacy & Content Sanitization

Newsletter content is automatically sanitized to remove tracking links, unsubscribe URLs, and sensitive content. Privacy rules are defined as Python constants in backend/config/privacy_patterns.py with URL patterns, text patterns, and CSS selectors.

Implementation: backend/ingest/email/email_parser.py:sanitize_content() Tests: backend/tests/test_sanitization*.py and test_user_cases.py

Deployment

• Email Ingestion: Automated via GitHub Actions (see .github/workflows/email_ingestion.yml)
• Daily Notifications: Automated via GitHub Actions (see .github/workflows/send_notifications.yml)
• Weekly Reports: Automated via GitHub Actions (see .github/workflows/send_weekly_digests.yml)
• LLM Processing: Manual local execution with Ollama (see backend/docs/LOCAL_LLM_PROCESSING.md)
• Frontend: Auto-deploy via Cloudflare Pages on push to main

Search indexing: The production URL is set as site in frontend/astro.config.mjs. The build emits /sitemap-index.xml, which references the static-route sitemap and the dynamic /sitemap-newsletters.xml (one entry per newsletter) and /sitemap-wards.xml (one entry per ward). Submit https://chicago-newsletter-aggregator.open-advocacy.com/sitemap-index.xml in Google Search Console; a Domain property on open-advocacy.com covers the subdomain. Requests on any other host (*.pages.dev, preview deployments) get X-Robots-Tag: noindex from frontend/src/middleware.ts.

GitHub Actions: Both workflows support manual triggering and require secrets configured in repository Settings → Secrets (see workflow files for details).

Environment Variables

Backend (.env):

# Gmail IMAP
GMAIL_ADDRESS=
GMAIL_APP_PASSWORD=

# Supabase
SUPABASE_URL=
SUPABASE_SERVICE_KEY=

# LLM Processing (optional)
ENABLE_LLM=true
LLM_MODEL=gpt-oss:20b              # Supports provider prefix: openai:gpt-5, ollama:gpt-oss:20b
OLLAMA_MODEL=gpt-oss:20b           # Legacy fallback (LLM_MODEL takes precedence)
OPENAI_API_KEY=sk-...              # Required when using openai: provider prefix

# Notifications (optional)
ENABLE_NOTIFICATIONS=true
RESEND_API_KEY=re_xxxxx
[email protected]
FRONTEND_BASE_URL=yourbaseurl  # Optional
UNSUBSCRIBE_SECRET_KEY=your_secret_key # For signing unsubscribe JWTs

# Privacy (optional)
PRIVACY_STRIP_PHRASES=  # Comma-separated phrases to redact

Frontend (.env):

PUBLIC_SUPABASE_URL=
PUBLIC_SUPABASE_ANON_KEY=
PUBLIC_ENABLE_NOTIFICATIONS=

# Server-side Only
SUPABASE_SERVICE_KEY=
UNSUBSCRIBE_SECRET_KEY=