● Zion Boggan
repos/Docket/commits/52d7c5c
zionboggan.com ↗
Code Commits Tags

Photos + full roster (12.7k members, official headshots) + congressional meetings tracker (166 mtgs)

52d7c5c   Zion Boggan committed on Jun 26, 2026 (1 month ago)
PHOTOS_NOTES.md +113 -0
@@ -0,0 +1,113 @@
+# Docket - Member Photos: Source, Attribution, and Implementation Notes
+
+## Image Source
+
+**Project:** [unitedstates/images](https://github.com/unitedstates/images)
+**License:** Public domain / CC0 - these are official congressional headshots
+released without restriction.
+**Hosted at:** `https://unitedstates.github.io/images/congress/`
+
+The unitedstates/images project aggregates official headshots provided by
+the Office of the Clerk (House) and the Senate for every member who has
+served. The images are maintained by the same open-data community that
+produces the congress-legislators roster used by Docket's member ingest.
+
+## URL Pattern
+
+```
+https://unitedstates.github.io/images/congress/{size}/{bioguide_id}.jpg
+```
+
+Available sizes:
+- `225x275` - thumbnail
+- `450x550` - standard (Docket stores this size)
+- `original` - full resolution
+
+**Verified:** HEAD-checked against `A000055` (Robert Aderholt) on 2026-06-26.
+All three sizes return HTTP 200. A non-existent bioguide returns HTTP 404.
+
+## Implementation
+
+### Schema addition (`members` table)
+- Column: `photo_url TEXT` (nullable)
+- Added to `ensure_votes_schema()` in `members.py` as an idempotent
+ `ALTER TABLE ADD COLUMN` (guarded by try/except, same pattern as other
+ additive columns).
+- Column ownership: `member_photos.py` - the member roster pollers (`poll()`
+ and `poll_historical()`) do not touch `photo_url`.
+
+### Pollers
+
+**`pollers/members.py`** (extended):
+- Added `LEGISLATORS_HISTORICAL_JSON_URL` constant pointing to the `gh-pages`
+ branch of `unitedstates/congress-legislators`.
+- Added `poll_historical()` - fetches `legislators-historical.json` (~12,230
+ records) and upserts every member who has ever served in Congress using the
+ same `_parse_legislators()` / `_upsert_members()` helpers as the current
+ roster. Idempotent via `ON CONFLICT(bioguide_id) DO UPDATE`.
+- Extracted shared `_upsert_members()` helper to avoid duplicating the upsert
+ SQL between `poll()` and `poll_historical()`.
+- CLI extended: `--historical` flag triggers `poll_historical()` after the
+ current roster poll.
+
+**`pollers/member_photos.py`** (new):
+- For every member with a `bioguide_id`, builds the 450x550 candidate URL
+ and sends a HEAD request to confirm existence (HTTP 200).
+- Sets `photo_url` on confirmation; leaves it NULL on 404 / network error.
+- Progress-commits every 500 rows; logs progress at each checkpoint.
+- `--reset` flag clears all photo_url values before re-checking (useful when
+ the upstream image set has changed).
+- Rate courtesy: 50 ms default delay between HEAD requests (configurable with
+ `--delay`).
+
+### Not doing (intentional scope limits)
+- **No local caching.** Images are not downloaded or hosted. `photo_url` stores
+ the external GitHub Pages URL, and the frontend renders `<img src={photo_url}>`.
+ Local mirroring (e.g. to object storage) is a future hardening step if GitHub
+ Pages availability becomes a concern.
+- **No fabrication.** If HEAD returns non-200, `photo_url` stays NULL. The UI
+ must show an initials placeholder for members with `photo_url IS NULL`.
+- **State legislators.** OpenStates `person.image` is noted in code as the
+ source when/if state members are ingested. Not yet implemented (no state
+ members in the current DB).
+
+## Running
+
+```bash
+# 1. Ingest current 537 members (fast, ~5s)
+python -m ingest.pollers.members --once
+
+# 2. Ingest all historical members (~12,230 records, ~60s for download)
+python -m ingest.pollers.members --once --historical
+
+# 3. Set photo_url for every member (HEAD-checks, ~10-15 min for full roster)
+python -m ingest.pollers.member_photos --once
+```
+
+## Test command
+
+```bash
+# On CT247 (live DB):
+ssh root@REDACTED-IP "pct exec 247 -- bash -lc \
+ 'cd /shared/projects/docket/services/ingest && \
+ DOCKET_DB_PATH=/opt/docket/data/docket.db \
+ /opt/docket/services/ingest/.venv/bin/python \
+ -m ingest.pollers.member_photos --once'"
+```
+
+## Expected outcomes
+
+| Milestone | Count |
+|-----------|-------|
+| Current members (before historical poll) | ~537 |
+| Total members after `poll_historical()` | ~12,767 |
+| Members with `photo_url` (current only) | ~530+ |
+| Members with `photo_url` (after historical) | varies - unitedstates/images covers most post-1900 members; pre-1900 coverage is sparse |
+
+## Attribution requirement
+
+Docket does not redistribute the images. The `photo_url` is a hotlink to
+GitHub Pages, which is the same URL the unitedstates/images project publishes.
+No license notice is required for CC0 / public domain works, but any
+about/credits page should note: "Official congressional headshots from
+[unitedstates/images](https://github.com/unitedstates/images) (public domain)."
_ops/glm-f4.txt +22 -0
@@ -0,0 +1,22 @@
+You are GLM-5.2, frontend lead for Docket (neutral US gov transparency app, PUBLIC-FACING). Work in /opt/docket. Be RAG-first; read ONLY: docs/API.md, docs/BRAND.md, docs/NEUTRALITY.md, docs/ACCESSIBILITY.md. Reuse your lib/db.ts + lib/feed.ts + components.
+
+New DB data now available (verify via sqlite read of /opt/docket/data/docket.db):
+- members.photo_url - official headshots (1,179 members have one; rest NULL). Total members now 12,767 (current + ALL historical).
+- meetings table (id, chamber, committee, date, time, datetime_iso, title, subject, type[hearing|markup|business_meeting|floor], location, status, source_url) - 166 rows, congressional committee + floor meetings.
+
+WAVE F4 - faces + schedule + fill the gaps:
+
+1. PHOTOS EVERYWHERE: surface members.photo_url (expose it in the member API + vote/roster queries in lib). Show the headshot:
+ - /members roster: avatar thumbnail per row.
+ - /members/[id]: a profile header with the photo (and their party/state/chamber).
+ - /votes/[id] roster: a small face next to each member's name + position.
+ - Anywhere a member name appears, link it to /members/[id].
+ - When photo_url is NULL: a clean INITIALS placeholder (not a broken image). Lazy-load images (loading="lazy") since rosters have ~400-500. Keep it fast.
+
+2. /schedule PAGE (new - the meetings tracker): build /api/meetings (or /api/schedule) reading the meetings table (?from=&to=&chamber=&committee=&type=). Then a /schedule page that shows congressional meetings in an intuitive, all-ages format: a calendar OR a clear day-grouped list/side-panel - your call, but it must answer "what is Congress meeting about, and when" at a glance. Show date + time + chamber + committee + subject + type, link to source_url. Support upcoming AND past meetings, filterable by chamber/committee. Add /schedule to the nav. Must be legible in Plain (high-contrast) mode for older users.
+
+3. FILL EMPTY FIELDS: audit every page for empty/unpopulated sections and populate them from available API data (e.g. bill sponsor/cosponsors links, status timeline, vote→bill linkage, member→votes, categories/subjects on bills, summaries where present). If a field is genuinely empty in the data, show an honest "Not available" rather than a blank - never a broken/empty UI element.
+
+HARD RULES: keep BRAND (brass rationed, custody-stamp, mono, dark default), NEUTRALITY (Source link on every fact, no adjectives, muted vote colors, no comment sections, honest "no record"/"not available"), and the 3-mode accessibility toggle on ALL pages incl. the new /schedule. This is PUBLIC-FACING - be polished and correct.
+
+BUILD: `npm run build` MUST pass. Do NOT restart the server (handled separately). Update FRONTEND_NOTES.md. Definition of done: photos render across members/roster/votes with initials fallback; /schedule page + /api/meetings live with real meetings; empty fields filled or honestly labeled; brand + neutrality + a11y intact.
services/ingest/MEETINGS_NOTES.md +164 -0
@@ -0,0 +1,164 @@
+# Meetings Ingest - Notes
+
+Module: `ingest/pollers/meetings.py`
+Table: `meetings`
+Tests: `tests/test_meetings.py` (58 tests, all pass)
+
+## Run
+
+```bash
+DOCKET_DB_PATH=/opt/docket/data/docket.db \
+ python -m ingest.pollers.meetings --once
+```
+
+Options:
+- `--days-back N` - days of past House committee meetings to scan (default 30)
+- `--days-ahead N` - days of future House committee meetings to scan (default 90)
+- `--no-detail` - skip per-event detail fetches (faster; meeting type omitted for new events)
+
+## What it does
+
+Ingests congressional committee + floor activity from three keyless sources.
+
+### Sources and counts (2026-06-26, 30-back / 90-ahead window)
+
+| Source | Chamber | Kind | Count |
+|---|---|---|---|
+| senate.gov XML | Senate | hearings | 1 |
+| senate.gov XML | Senate | business_meeting | 1 |
+| docs.house.gov | House | hearing | 68 |
+| docs.house.gov | House | markup | 19 |
+| docs.house.gov | House | floor | 66 |
+| docs.house.gov | House | other | 11 |
+| **TOTAL** | | | **166** |
+
+### Source 1 - Senate committee meetings XML
+
+**URL:** `https://www.senate.gov/general/committee_schedules/hearings.xml`
+**Status:** Working. Returns clean structured XML with all upcoming meetings.
+**Update cadence:** Regenerated multiple times daily.
+**Fields extracted:** identifier, committee, subcommittee, date, time, location (room), subject (matter), type.
+**Source URL stored:** `https://www.senate.gov/committees/hearings_meetings.htm`
+
+The XML provides a `last_update_iso_8601` field used for change-detection in the `seen` table. Meetings are only re-fetched when they change.
+
+**Note:** On days with no Senate committee meetings, the XML returns a single placeholder entry with no `<identifier>` element. This is correctly skipped.
+
+### Source 2 - House committee meetings (HTML)
+
+**Calendar day index:** `https://docs.house.gov/Committee/Calendar/ByDay.aspx?DayID=MMDDYYYY`
+**Event detail:** `https://docs.house.gov/Committee/Calendar/ByEvent.aspx?EventID=N`
+**Status:** Working. Data-rich HTML tables.
+
+Two-phase approach:
+1. **Phase 1 (parallel):** Fetch all `ByDay.aspx` pages for the date window using `ThreadPoolExecutor` (8 workers). Extracts `EventID`, title, committee, time, location, status (Rescheduled/Canceled/Postponed).
+2. **Phase 2 (parallel):** For new `EventID`s only, fetch the event detail page (4 workers) to extract meeting type (`Hearing:` / `Markup:` / `Business Meeting:` prefix in `<h1>`).
+
+Change-detection key: `status|date` - re-processes a meeting if its status or scheduled date changes.
+
+**Source URL per row:** `https://docs.house.gov/Committee/Calendar/ByEvent.aspx?EventID={N}` (docs.house.gov is .gov ✓)
+
+**Noted:** Individual meeting XML download on ByEvent.aspx requires JavaScript postback (`__doPostBack`) - inaccessible without a browser. The HTML scrape provides all fields needed.
+
+### Source 3 - House floor schedule XML
+
+**URL pattern:** `https://docs.house.gov/billsthisweek/YYYYMMDD/YYYYMMDD.xml` (Monday date)
+**Status:** Working. Scans current week + 4 prior weeks (weeks with no XML return 404 and are skipped).
+**Fields extracted:** bill number (legis-num), short description (floor-text), floor category (suspension/rule/etc.), week date.
+**Type:** always `floor`
+**Source URL per row:** `https://docs.house.gov/floor/Default.aspx?date=YYYY-MM-DD`
+
+**Not a source:** Congress.gov committee schedule - key-gated. The `docs.house.gov` committee repository is the keyless equivalent.
+**Not a source:** Federal Register Sunshine Act notices - FR poller (`executive_actions`) owns those; committee meetings here focus on congressional activity only.
+
+## Schema
+
+```sql
+CREATE TABLE meetings (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ source TEXT NOT NULL,
+ external_id TEXT NOT NULL,
+ chamber TEXT NOT NULL CHECK(chamber IN ('Senate','House')),
+ committee TEXT,
+ date TEXT NOT NULL, -- YYYY-MM-DD
+ time TEXT, -- as-published (e.g. "10:00 AM")
+ datetime_iso TEXT, -- YYYY-MM-DDTHH:MM:SS
+ title TEXT,
+ subject TEXT,
+ type TEXT CHECK(type IN (
+ 'hearing','markup','business_meeting','floor','other')),
+ location TEXT,
+ status TEXT NOT NULL DEFAULT 'scheduled'
+ CHECK(status IN ('scheduled','held','canceled','postponed')),
+ source_url TEXT NOT NULL,
+ UNIQUE (source, external_id)
+);
+CREATE INDEX idx_meetings_date ON meetings(date);
+CREATE INDEX idx_meetings_committee ON meetings(committee);
+```
+
+## 3 Real example meetings (from production run 2026-06-26)
+
+**Example 1 - Senate committee (upcoming)**
+```
+Chamber: Senate
+Committee: Personnel Subcommittee (Armed Services)
+Date/Time: 2026-07-15 02:30 PM
+Type: hearing
+Title: Hearings to examine the TRICARE pharmacy program.
+Location: SR-222
+Source: https://www.senate.gov/committees/hearings_meetings.htm
+```
+
+**Example 2 - House committee hearing (upcoming)**
+```
+Chamber: House
+Committee: Europe Subcommittee (Committee on Foreign Affairs)
+Date/Time: 2026-06-30 10:00 AM
+Type: hearing
+Title: Arms Control and Nonproliferation in an Era of Great Power Competition
+Location: 2172 RHOB
+Source: https://docs.house.gov/Committee/Calendar/ByEvent.aspx?EventID=119256
+```
+
+**Example 3 - House committee hearing (past)**
+```
+Chamber: House
+Committee: Select Committee on the Strategic Competition Between the United States and the Chinese Communist Party
+Date/Time: 2026-06-25 10:30 AM
+Type: hearing
+Title: China's Economic Espionage and Subnational Influence in the...
+Location: (varies)
+Source: https://docs.house.gov/Committee/Calendar/ByEvent.aspx?EventID=...
+```
+
+## Sources that are blocked / unavailable (keyless)
+
+| Source | Status | Reason |
+|---|---|---|
+| Congress.gov committee schedule API | Key-gated | Requires `DOCKET_CONGRESS_KEY` |
+| docs.house.gov Meeting XML download | JS-only | `__doPostBack` - no direct URL |
+| Individual Senate committee pages | No structured feed | Per-committee HTML only |
+| CHRG (hearing transcripts) | Key-gated (GovInfo API) | Available in bulk with key |
+
+## Future /api/schedule endpoint
+
+Suggested query pattern for the frontend:
+
+```sql
+SELECT date, chamber, committee, time, datetime_iso, title, type, location, status, source_url
+FROM meetings
+WHERE date BETWEEN :start AND :end
+ AND (:chamber IS NULL OR chamber = :chamber)
+ AND (:committee IS NULL OR committee LIKE :committee)
+ AND (:type IS NULL OR type = :type)
+ORDER BY datetime_iso NULLS LAST, date, committee
+LIMIT :limit OFFSET :offset
+```
+
+This supports the target UX: "Mon 2026-08-25, 4:55 PM - 4 committee meetings on X, Y, Z."
+The `date` index makes date-range queries fast. The `committee` index supports committee-specific filtering.
+
+## Neutrality
+
+All rows carry factual scheduled activity data only: committee name, date, time, subject. No adjectives, no editorial characterization of hearing importance or significance. Source URLs point directly to official `.gov` pages. Meeting subjects are reproduced verbatim from official sources.
services/ingest/ingest/pollers/meetings.py +737 -0
@@ -0,0 +1,737 @@
+"""
+Congressional meetings/schedule poller.
+
+Writes to: meetings table (created if absent - does not touch db.py core).
+Idempotent on (source, external_id).
+
+Sources
+-------
+1. Senate committee meetings
+ URL: https://www.senate.gov/general/committee_schedules/hearings.xml
+ Auth: none. Structured XML, upcoming meetings.
+ Source URL stored: https://www.senate.gov/committees/hearings_meetings.htm
+
+2. House committee meetings
+ Day index: https://docs.house.gov/Committee/Calendar/ByDay.aspx?DayID=MMDDYYYY
+ Event detail: https://docs.house.gov/Committee/Calendar/ByEvent.aspx?EventID={N}
+ Auth: none. HTML scrape. Both past and upcoming.
+
+3. House floor schedule
+ Weekly XML: https://docs.house.gov/billsthisweek/YYYYMMDD/YYYYMMDD.xml
+ Auth: none. Bills scheduled for House floor consideration by week.
+
+Neutrality: factual meeting data only - title, committee, date, time, location, type.
+No adjectives, no editorial weighting. Source URLs all .gov.
+
+Run with:
+ python -m ingest.pollers.meetings --once
+"""
+
+import argparse
+import html as html_module
+import json
+import logging
+import re
+import sys
+from concurrent.futures import ThreadPoolExecutor, as_completed
+from datetime import datetime, date, timedelta
+from typing import Iterator, List, Optional, Tuple
+import xml.etree.ElementTree as ET
+
+import requests
+
+from ingest.change_detection import is_new_event, mark_seen
+from ingest.db import get_connection, init_schema
+from ingest.source_validator import validate_source_url
+
+logger = logging.getLogger(__name__)
+
+# ── Source identifiers ────────────────────────────────────────────────────────
+
+SOURCE_SENATE = "senate_committee"
+SOURCE_HOUSE_CMT = "house_committee"
+SOURCE_HOUSE_FLR = "house_floor"
+
+# ── Endpoint URLs (all .gov - allowlisted) ────────────────────────────────────
+
+SENATE_XML_URL = "https://www.senate.gov/general/committee_schedules/hearings.xml"
+SENATE_PAGE_URL = "https://www.senate.gov/committees/hearings_meetings.htm"
+
+HOUSE_BYDAY_URL = "https://docs.house.gov/Committee/Calendar/ByDay.aspx"
+HOUSE_EVENT_URL = "https://docs.house.gov/Committee/Calendar/ByEvent.aspx"
+HOUSE_FLOOR_BASE = "https://docs.house.gov/billsthisweek"
+HOUSE_FLOOR_PAGE = "https://docs.house.gov/floor/Default.aspx"
+
+_REQUEST_TIMEOUT = 30
+_MAX_WORKERS = 8 # concurrent day-page fetches
+_MAX_DETAIL_WORKERS = 4 # concurrent event-detail fetches
+
+# ── Meeting-type normalization ────────────────────────────────────────────────
+
+_SENATE_TYPE_MAP: dict = {
+ "open hearing": "hearing",
+ "closed hearing": "hearing",
+ "field hearing": "hearing",
+ "open markup": "markup",
+ "closed markup": "markup",
+ "open business meeting": "business_meeting",
+ "closed business meeting": "business_meeting",
+ "executive session": "business_meeting",
+ "open meeting": "business_meeting",
+ "closed meeting": "business_meeting",
+}
+
+_HOUSE_TYPE_PREFIXES: List[Tuple[str, str]] = [
+ ("hearing", "hearing"),
+ ("markup", "markup"),
+ ("business meeting", "business_meeting"),
+ ("full committee", "hearing"),
+ ("subcommittee hearing", "hearing"),
+ ("subcommittee markup", "markup"),
+]
+
+def _norm_senate_type(raw: str) -> str:
+ return _SENATE_TYPE_MAP.get(raw.strip().lower(), "other")
+
+def _norm_house_type(h1_text: str) -> str:
+ """Extract type from the leading label in House event page <h1>."""
+ low = h1_text.strip().lower()
+ for prefix, typ in _HOUSE_TYPE_PREFIXES:
+ if low.startswith(prefix):
+ return typ
+ return "other"
+
+def _detect_status(muted_text: str) -> str:
+ """Infer status from parenthetical in House ByDay HTML."""
+ t = muted_text.lower()
+ if "cancel" in t:
+ return "canceled"
+ if "postponed" in t:
+ return "postponed"
+ if "rescheduled" in t:
+ return "postponed"
+ return "scheduled"
+
+# ── Datetime helpers ──────────────────────────────────────────────────────────
+
+_12H = re.compile(r'^(\d{1,2}:\d{2})\s*([APap][Mm])$')
+_24H = re.compile(r'^\d{2}:\d{2}:\d{2}$')
+
+
+def _build_datetime_iso(date_str: str, time_str: Optional[str]) -> Optional[str]:
+ """
+ Combine ISO date (YYYY-MM-DD) and time string into ISO 8601.
+ Accepts both 12-hour (10:00 AM) and 24-hour (14:30:00) formats.
+ Returns None when either input is missing or unparseable.
+ """
+ if not date_str or not time_str:
+ return None
+ t = time_str.strip()
+ try:
+ if _24H.match(t):
+ return f"{date_str}T{t}"
+ m = _12H.match(t)
+ if m:
+ hhmm, ampm = m.group(1), m.group(2).upper()
+ dt = datetime.strptime(f"{date_str} {hhmm} {ampm}", "%Y-%m-%d %I:%M %p")
+ return dt.strftime("%Y-%m-%dT%H:%M:00")
+ except Exception:
+ pass
+ return None
+
+# ── Schema ────────────────────────────────────────────────────────────────────
+
+def _ensure_tables(conn) -> None:
+ """Create meetings table if it does not exist. Idempotent."""
+ conn.executescript("""
+ CREATE TABLE IF NOT EXISTS meetings (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ source TEXT NOT NULL,
+ external_id TEXT NOT NULL,
+ chamber TEXT NOT NULL
+ CHECK(chamber IN ('Senate','House')),
+ committee TEXT,
+ date TEXT NOT NULL,
+ time TEXT,
+ datetime_iso TEXT,
+ title TEXT,
+ subject TEXT,
+ type TEXT CHECK(type IN
+ ('hearing','markup','business_meeting','floor','other')),
+ location TEXT,
+ status TEXT NOT NULL DEFAULT 'scheduled'
+ CHECK(status IN ('scheduled','held','canceled','postponed')),
+ source_url TEXT NOT NULL,
+ UNIQUE (source, external_id)
+ );
+
+ CREATE INDEX IF NOT EXISTS idx_meetings_date ON meetings(date);
+ CREATE INDEX IF NOT EXISTS idx_meetings_committee ON meetings(committee);
+ """)
+ conn.commit()
+
+# ── DB write helpers ──────────────────────────────────────────────────────────
+
+def _upsert_meeting(conn, m: dict) -> None:
+ """INSERT or UPDATE a meetings row. Also writes to raw_events."""
+ conn.execute("""
+ INSERT INTO meetings
+ (source, external_id, chamber, committee, date, time, datetime_iso,
+ title, subject, type, location, status, source_url)
+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+ ON CONFLICT(source, external_id) DO UPDATE SET
+ chamber = excluded.chamber,
+ committee = excluded.committee,
+ date = excluded.date,
+ time = excluded.time,
+ datetime_iso = excluded.datetime_iso,
+ title = excluded.title,
+ subject = excluded.subject,
+ type = excluded.type,
+ location = excluded.location,
+ status = excluded.status,
+ source_url = excluded.source_url
+ """, (
+ m["source"], m["external_id"], m["chamber"],
+ m.get("committee"), m["date"], m.get("time"), m.get("datetime_iso"),
+ m.get("title"), m.get("subject"), m.get("type"),
+ m.get("location"), m.get("status", "scheduled"), m["source_url"],
+ ))
+ # Raw provenance record
+ conn.execute("""
+ INSERT INTO raw_events (source, external_id, payload, fetched_at)
+ VALUES (?, ?, ?, ?)
+ ON CONFLICT(source, external_id) DO UPDATE SET
+ payload = excluded.payload,
+ fetched_at = excluded.fetched_at
+ """, (
+ m["source"], m["external_id"],
+ json.dumps({k: v for k, v in m.items()
+ if k not in ("source", "external_id")}),
+ datetime.utcnow().isoformat(),
+ ))
+
+
+def _meeting_has_type(conn, source: str, external_id: str) -> bool:
+ """Return True if this meeting already has a non-null type stored."""
+ row = conn.execute(
+ "SELECT type FROM meetings WHERE source=? AND external_id=?",
+ (source, external_id),
+ ).fetchone()
+ return row is not None and row["type"] is not None
+
+
+# ══════════════════════════════════════════════════════════════════════════════
+# 1. Senate committee meetings
+# ══════════════════════════════════════════════════════════════════════════════
+
+def _parse_senate_xml(xml_bytes: bytes) -> List[dict]:
+ """
+ Parse Senate hearings XML into a list of normalized meeting dicts.
+ The XML at senate.gov/general/committee_schedules/hearings.xml
+ publishes all upcoming (and recent) Senate committee meetings.
+ Skips placeholder entries that have no <identifier> element.
+ """
+ meetings: List[dict] = []
+ try:
+ root = ET.fromstring(xml_bytes)
+ except ET.ParseError as exc:
+ logger.error("senate_xml: parse error: %s", exc)
+ return meetings
+
+ for mtg in root.findall("meeting"):
+ identifier = (mtg.findtext("identifier") or "").strip()
+ if not identifier:
+ continue # placeholder "no meetings scheduled" entry
+
+ committee = (mtg.findtext("committee") or "").strip()
+ sub_cmte = (mtg.findtext("sub_cmte") or "").strip()
+ date_iso = (mtg.findtext("date_iso_8601") or "").strip()
+ time_iso = (mtg.findtext("time_iso_8601") or "").strip()
+ time_raw = (mtg.findtext("time") or "").strip()
+ room = (mtg.findtext("room") or "").strip()
+ matter = (mtg.findtext("matter") or "").strip()
+ raw_type = (mtg.findtext("type") or "").strip()
+ last_upd = (mtg.findtext("last_update_iso_8601") or "").strip()
+
+ if not date_iso:
+ continue
+
+ # Build committee label (include subcommittee when present)
+ if sub_cmte and committee:
+ committee_str = f"{sub_cmte} ({committee})"
+ elif sub_cmte:
+ committee_str = sub_cmte
+ else:
+ committee_str = committee or None
+
+ # ISO datetime
+ dt_iso = f"{date_iso}T{time_iso}" if date_iso and time_iso else None
+
+ meetings.append({
+ "source": SOURCE_SENATE,
+ "external_id": identifier,
+ "chamber": "Senate",
+ "committee": committee_str,
+ "date": date_iso,
+ "time": time_raw or time_iso or None,
+ "datetime_iso": dt_iso,
+ "title": matter or None,
+ "subject": matter or None,
+ "type": _norm_senate_type(raw_type),
+ "location": room or None,
+ "status": "scheduled",
+ "source_url": SENATE_PAGE_URL,
+ "_update_marker": last_upd or date_iso, # for seen-table dedupe
+ })
+
+ return meetings
+
+
+def poll_senate(conn) -> int:
+ """Fetch and store Senate committee meetings. Returns count of new rows."""
+ logger.info("senate: fetching %s", SENATE_XML_URL)
+ try:
+ resp = requests.get(SENATE_XML_URL, timeout=_REQUEST_TIMEOUT)
+ resp.raise_for_status()
+ except requests.RequestException as exc:
+ logger.error("senate: request failed: %s", exc)
+ return 0
+
+ try:
+ validate_source_url(SENATE_PAGE_URL)
+ except Exception as exc:
+ logger.error("senate: source_url not allowlisted: %s", exc)
+ return 0
+
+ meetings = _parse_senate_xml(resp.content)
+ logger.info("senate: parsed %d meeting(s)", len(meetings))
+
+ stored = 0
+ for m in meetings:
+ marker = m.pop("_update_marker", date.today().isoformat())
+ if not is_new_event(conn, SOURCE_SENATE, m["external_id"], marker):
+ logger.debug("senate: skipping seen %s", m["external_id"])
+ continue
+ _upsert_meeting(conn, m)
+ mark_seen(conn, SOURCE_SENATE, m["external_id"], marker)
+ stored += 1
+
+ conn.commit()
+ logger.info("senate: stored %d new row(s)", stored)
+ return stored
+
+
+# ══════════════════════════════════════════════════════════════════════════════
+# 2. House committee meetings
+# ══════════════════════════════════════════════════════════════════════════════
+
+_HTML_TAG = re.compile(r'<[^>]+>')
+_WHITESPACE = re.compile(r'\s+')
+
+
+def _strip_tags(s: str) -> str:
+ return html_module.unescape(_HTML_TAG.sub(' ', s))
+
+
+def _clean(s: str) -> str:
+ return _WHITESPACE.sub(' ', s).strip()
+
+
+def _fetch_text(url: str) -> Optional[str]:
+ """HTTP GET with timeout; return text or None."""
+ try:
+ r = requests.get(url, timeout=_REQUEST_TIMEOUT)
+ r.raise_for_status()
+ return r.text
+ except requests.RequestException as exc:
+ logger.debug("http_get failed %s: %s", url, exc)
+ return None
+
+
+def _parse_house_day_page(html: str, day: date) -> List[dict]:
+ """
+ Parse ByDay.aspx HTML for one calendar day.
+ Returns partial meeting dicts (type=None; filled by event-detail fetch).
+
+ HTML structure per row:
+ <a href="ByEvent.aspx?EventID=N" title="TITLE">TITLE</a>
+ <span class='muted'>(Status)</span> ← optional
+ <span class="text-tiny" title="COMMITTEE">COMMITTEE (PARENT)</span>
+ <td><span class="text-small">TIME</span></td>
+ <td><span class="text-small">LOCATION</span></td>
+ """
+ date_iso = day.strftime("%Y-%m-%d")
+ meetings: List[dict] = []
+
+ table_match = re.search(
+ r'id="MainContent_GridViewMeetings"(.*?)</table>',
+ html, re.DOTALL,
+ )
+ if not table_match:
+ return meetings
+
+ rows = re.findall(r'<tr[^>]*>(.*?)</tr>', table_match.group(1), re.DOTALL)
+
+ for row in rows[1:]: # skip header
+ # EventID
+ eid_m = re.search(r'ByEvent\.aspx\?EventID=(\d+)', row)
+ if not eid_m:
+ continue
+ event_id = eid_m.group(1)
+
+ # Title (from anchor title= attribute - already HTML-decoded by server)
+ title_m = re.search(r'<a[^>]+title="([^"]+)"', row)
+ title = html_module.unescape(title_m.group(1).strip()) if title_m else ""
+
+ # Committee / subcommittee
+ cmte_m = re.search(r'class="text-tiny"[^>]*>(.*?)</span>', row, re.DOTALL)
+ committee = _clean(_strip_tags(cmte_m.group(1))) if cmte_m else ""
+
+ # Time + location from <td> cells (cells[0]=title, [1]=time, [2]=loc)
+ cells = re.findall(r'<td>(.*?)</td>', row, re.DOTALL)
+ time_raw = _clean(_strip_tags(cells[1])) if len(cells) > 1 else ""
+ location = _clean(_strip_tags(cells[2])) if len(cells) > 2 else ""
+
+ # Status annotation
+ muted_m = re.search(r"class='muted'>\(([^)]+)\)", row)
+ status = _detect_status(muted_m.group(1)) if muted_m else "scheduled"
+
+ source_url = f"{HOUSE_EVENT_URL}?EventID={event_id}"
+ try:
+ validate_source_url(source_url)
+ except Exception:
+ continue
+
+ meetings.append({
+ "source": SOURCE_HOUSE_CMT,
+ "external_id": event_id,
+ "chamber": "House",
+ "committee": committee or None,
+ "date": date_iso,
+ "time": time_raw or None,
+ "datetime_iso": _build_datetime_iso(date_iso, time_raw),
+ "title": title or None,
+ "subject": title or None,
+ "type": None, # filled by event-detail fetch if needed
+ "location": location or None,
+ "status": status,
+ "source_url": source_url,
+ })
+
+ return meetings
+
+
+def _parse_house_event_type(html: str) -> str:
+ """
+ Extract meeting type from ByEvent.aspx HTML.
+ The <h1> contains "Type: Title" e.g. "Hearing: A Review of..."
+ """
+ h1_m = re.search(r'<h1[^>]*>(.*?)</h1>', html, re.DOTALL)
+ if h1_m:
+ h1_text = _clean(_strip_tags(h1_m.group(1)))
+ return _norm_house_type(h1_text)
+ return "other"
+
+
+def _most_recent_monday(ref: date = None) -> date:
+ """Return the most recent Monday (including today if today is Monday)."""
+ if ref is None:
+ ref = date.today()
+ return ref - timedelta(days=ref.weekday())
+
+
+def poll_house_committee(conn, days_back: int = 30, days_ahead: int = 90,
+ fetch_detail: bool = True) -> int:
+ """
+ Scan House committee calendar for [today-days_back, today+days_ahead].
+
+ Phase 1: Fetch ByDay.aspx for each day (parallel; _MAX_WORKERS threads).
+ Phase 2: For new EventIDs without a stored type, fetch ByEvent.aspx
+ (parallel; _MAX_DETAIL_WORKERS threads).
+
+ Returns count of new/updated rows.
+ """
+ logger.info("house_cmt: scanning past=%d future=%d days", days_back, days_ahead)
+ today = date.today()
+ all_days = [
+ today - timedelta(days=i) for i in range(days_back, 0, -1)
+ ] + [
+ today + timedelta(days=i) for i in range(days_ahead + 1)
+ ]
+
+ # Phase 1 - collect all (EventID, basic_data) from day pages
+ all_partials: List[dict] = []
+
+ def _fetch_day(d: date) -> Tuple[date, List[dict]]:
+ day_str = d.strftime("%m%d%Y")
+ url = f"{HOUSE_BYDAY_URL}?DayID={day_str}"
+ html = _fetch_text(url)
+ if html:
+ return d, _parse_house_day_page(html, d)
+ return d, []
+
+ with ThreadPoolExecutor(max_workers=_MAX_WORKERS) as pool:
+ futures = {pool.submit(_fetch_day, d): d for d in all_days}
+ for future in as_completed(futures):
+ d, partials = future.result()
+ if partials:
+ logger.debug("house_cmt: %s - %d event(s)", d, len(partials))
+ all_partials.extend(partials)
+
+ logger.info("house_cmt: found %d raw event(s) across all days", len(all_partials))
+
+ # Dedupe by external_id: keep the entry with richer committee data
+ by_id: dict = {}
+ for m in all_partials:
+ eid = m["external_id"]
+ if eid not in by_id:
+ by_id[eid] = m
+ else:
+ # Prefer the version with committee data
+ if not by_id[eid].get("committee") and m.get("committee"):
+ by_id[eid] = m
+
+ logger.info("house_cmt: %d unique event(s) after dedup", len(by_id))
+
+ # Phase 2 - for new events without stored type, fetch event-detail pages
+ needs_detail: List[str] = []
+ if fetch_detail:
+ for eid in by_id:
+ marker = by_id[eid]["status"] + "|" + by_id[eid]["date"]
+ if is_new_event(conn, SOURCE_HOUSE_CMT, eid, marker):
+ if not _meeting_has_type(conn, SOURCE_HOUSE_CMT, eid):
+ needs_detail.append(eid)
+
+ if needs_detail:
+ logger.info("house_cmt: fetching detail for %d new event(s)", len(needs_detail))
+
+ def _fetch_detail(eid: str) -> Tuple[str, str]:
+ url = f"{HOUSE_EVENT_URL}?EventID={eid}"
+ html = _fetch_text(url)
+ typ = _parse_house_event_type(html) if html else "other"
+ return eid, typ
+
+ with ThreadPoolExecutor(max_workers=_MAX_DETAIL_WORKERS) as pool:
+ futures = {pool.submit(_fetch_detail, eid): eid for eid in needs_detail}
+ for future in as_completed(futures):
+ eid, typ = future.result()
+ if eid in by_id:
+ by_id[eid]["type"] = typ
+
+ # Write to DB
+ stored = 0
+ for eid, m in by_id.items():
+ marker = m["status"] + "|" + m["date"]
+ if not is_new_event(conn, SOURCE_HOUSE_CMT, eid, marker):
+ continue
+ _upsert_meeting(conn, m)
+ mark_seen(conn, SOURCE_HOUSE_CMT, eid, marker)
+ stored += 1
+
+ conn.commit()
+ logger.info("house_cmt: stored %d new/updated row(s)", stored)
+ return stored
+
+
+# ══════════════════════════════════════════════════════════════════════════════
+# 3. House floor schedule
+# ══════════════════════════════════════════════════════════════════════════════
+
+def _floor_xml_url(monday: date) -> str:
+ """Construct the weekly floor schedule XML URL for a given Monday."""
+ ds = monday.strftime("%Y%m%d")
+ return f"{HOUSE_FLOOR_BASE}/{ds}/{ds}.xml"
+
+
+def _floor_page_url(monday: date) -> str:
+ ds = monday.strftime("%Y-%m-%d")
+ return f"{HOUSE_FLOOR_PAGE}?date={ds}"
+
+
+def _parse_floor_xml(xml_bytes: bytes, monday: date, source_url: str) -> List[dict]:
+ """
+ Parse House weekly floor schedule XML into meeting rows.
+
+ XML root: <floorschedule week-date="YYYY-MM-DD" ...>
+ <category type="Items that may be considered under suspension of the rules">
+ <floor-items>
+ <floor-item id="N">
+ <legis-num>H.R. X</legis-num>
+ <floor-text>Short title...</floor-text>
+ </floor-item>
+ </floor-items>
+ </category>
+ </floorschedule>
+ """
+ meetings: List[dict] = []
+ try:
+ root = ET.fromstring(xml_bytes)
+ except ET.ParseError as exc:
+ logger.error("house_floor: XML parse error: %s", exc)
+ return meetings
+
+ week_date_iso = root.get("week-date", monday.strftime("%Y-%m-%d"))
+ week_str = monday.strftime("%Y%m%d")
+
+ for category in root.findall("category"):
+ cat_type = category.get("type", "").strip()
+
+ for item in category.findall(".//floor-item"):
+ item_id = item.get("id", "").strip()
+ if not item_id:
+ continue
+
+ legis_num = (item.findtext("legis-num") or "").strip()
+ floor_text = (item.findtext("floor-text") or "").strip()
+
+ if not (legis_num or floor_text):
+ continue
+
+ title = f"{legis_num}: {floor_text}" if legis_num else floor_text
+ subject = f"{cat_type} - {floor_text}" if cat_type else floor_text
+
+ external_id = f"{week_str}_{item_id}"
+
+ meetings.append({
+ "source": SOURCE_HOUSE_FLR,
+ "external_id": external_id,
+ "chamber": "House",
+ "committee": None,
+ "date": week_date_iso,
+ "time": None,
+ "datetime_iso": None,
+ "title": title,
+ "subject": subject,
+ "type": "floor",
+ "location": "House Floor",
+ "status": "scheduled",
+ "source_url": source_url,
+ })
+
+ return meetings
+
+
+def poll_house_floor(conn, weeks_back: int = 4) -> int:
+ """
+ Fetch House floor schedule for current week and up to `weeks_back` prior weeks.
+ Returns count of new rows stored.
+ """
+ today = date.today()
+ mondays = [
+ _most_recent_monday(today) - timedelta(weeks=i)
+ for i in range(weeks_back + 1)
+ ]
+
+ stored = 0
+ for monday in mondays:
+ xml_url = _floor_xml_url(monday)
+ page_url = _floor_page_url(monday)
+
+ try:
+ validate_source_url(xml_url)
+ validate_source_url(page_url)
+ except Exception as exc:
+ logger.error("house_floor: source_url not allowlisted: %s", exc)
+ continue
+
+ logger.info("house_floor: fetching %s", xml_url)
+ try:
+ r = requests.get(xml_url, timeout=_REQUEST_TIMEOUT)
+ if r.status_code == 404:
+ logger.debug("house_floor: no XML for week %s (404)", monday)
+ continue
+ r.raise_for_status()
+ except requests.RequestException as exc:
+ logger.warning("house_floor: fetch failed for %s: %s", monday, exc)
+ continue
+
+ meetings = _parse_floor_xml(r.content, monday, page_url)
+ logger.info("house_floor: %s - %d floor item(s)", monday, len(meetings))
+
+ week_marker = monday.strftime("%Y%m%d")
+ for m in meetings:
+ if not is_new_event(conn, SOURCE_HOUSE_FLR, m["external_id"], week_marker):
+ continue
+ _upsert_meeting(conn, m)
+ mark_seen(conn, SOURCE_HOUSE_FLR, m["external_id"], week_marker)
+ stored += 1
+
+ conn.commit()
+ logger.info("house_floor: stored %d new row(s)", stored)
+ return stored
+
+
+# ══════════════════════════════════════════════════════════════════════════════
+# Main entry point
+# ══════════════════════════════════════════════════════════════════════════════
+
+def poll(
+ days_back: int = 30,
+ days_ahead: int = 90,
+ db_path: Optional[str] = None,
+ fetch_detail: bool = True,
+) -> dict:
+ """
+ Run all three meeting pollers.
+
+ Parameters
+ ----------
+ days_back : Days of past House committee meetings to scan.
+ days_ahead : Days of future House committee meetings to scan.
+ db_path : Override SQLite path (default: env DOCKET_DB_PATH or /var/tmp/docket/docket.db).
+ fetch_detail : If False, skip per-event detail fetches (faster; type=None for new events).
+
+ Returns
+ -------
+ dict with keys: senate, house_committee, house_floor, total
+ """
+ conn = get_connection(db_path) if db_path else get_connection()
+ init_schema(conn)
+ _ensure_tables(conn)
+
+ counts: dict = {}
+ counts["senate"] = poll_senate(conn)
+ counts["house_committee"] = poll_house_committee(
+ conn, days_back=days_back, days_ahead=days_ahead, fetch_detail=fetch_detail
+ )
+ counts["house_floor"] = poll_house_floor(conn)
+ counts["total"] = sum(counts.values())
+
+ conn.close()
+ return counts
+
+
+if __name__ == "__main__":
+ logging.basicConfig(
+ level=logging.INFO,
+ format="%(asctime)s [%(levelname)s] %(name)s: %(message)s",
+ stream=sys.stdout,
+ )
+ parser = argparse.ArgumentParser(
+ description="Congressional meetings/schedule poller",
+ formatter_class=argparse.ArgumentDefaultsHelpFormatter,
+ )
+ parser.add_argument("--once", action="store_true",
+ help="Run once and exit (always true when invoked directly)")
+ parser.add_argument("--days-back", type=int, default=30,
+ help="Days of past House committee meetings to scan")
+ parser.add_argument("--days-ahead", type=int, default=90,
+ help="Days of future House committee meetings to scan")
+ parser.add_argument("--no-detail", action="store_true",
+ help="Skip per-event detail fetches (faster; no meeting type)")
+ args = parser.parse_args()
+
+ counts = poll(
+ days_back=args.days_back,
+ days_ahead=args.days_ahead,
+ fetch_detail=not args.no_detail,
+ )
+ print(
+ f"Meetings stored - "
+ f"senate: {counts['senate']} "
+ f"house_committee: {counts['house_committee']} "
+ f"house_floor: {counts['house_floor']} "
+ f"total: {counts['total']}"
+ )
+ sys.exit(0)
services/ingest/ingest/pollers/member_photos.py +201 -0
@@ -0,0 +1,201 @@
+"""
+Member photo URL poller - unitedstates/images project.
+
+For every member in the `members` table with a known bioguide_id, performs
+a HEAD request to confirm the official headshot exists on GitHub Pages, then
+writes photo_url if the server returns HTTP 200. photo_url is left NULL for
+members with no confirmed image - the frontend renders an initials placeholder.
+
+Image URL pattern (public domain / CC0):
+ https://unitedstates.github.io/images/congress/450x550/{bioguide}.jpg
+
+Available sizes:
+ 225x275 - thumbnail
+ 450x550 - standard (this poller uses this size)
+ original - full resolution
+
+Source attribution:
+ github.com/unitedstates/images - CC0 / public domain.
+ These are official congressional headshots released without restriction.
+ Images are NOT downloaded or hosted locally; photo_url stores only the
+ external URL. Local caching/mirroring is a future hardening step.
+
+Run once (CLI):
+ python -m ingest.pollers.member_photos --once
+
+Optional flags:
+ --db PATH Override DB path.
+ --delay SECONDS Sleep between HEAD requests (default 0.05 s, rate courtesy).
+ --reset Clear all existing photo_url values before re-checking
+ (useful when the upstream image set has changed significantly).
+"""
+
+import argparse
+import logging
+import time
+from typing import Optional
+
+import requests
+
+from ingest.db import get_connection, init_schema
+from ingest.pollers.members import ensure_votes_schema
+
+logger = logging.getLogger(__name__)
+
+SOURCE = "member_photos"
+
+# Official headshots at 450x550 resolution (public domain / CC0).
+PHOTO_BASE = "https://unitedstates.github.io/images/congress/450x550"
+
+
+# ─── URL builder ──────────────────────────────────────────────────────────────
+
+def build_photo_url(bioguide_id: str) -> str:
+ """
+ Return the candidate 450x550 headshot URL for a given bioguide_id.
+
+ The unitedstates/images project stores files as:
+ {bioguide_id}.jpg (e.g. A000055.jpg)
+
+ >>> build_photo_url("A000055")
+ 'https://unitedstates.github.io/images/congress/450x550/A000055.jpg'
+ """
+ return f"{PHOTO_BASE}/{bioguide_id}.jpg"
+
+
+# ─── Network check ────────────────────────────────────────────────────────────
+
+def head_check(url: str, session: requests.Session, timeout: int = 10) -> bool:
+ """
+ Return True if a HEAD request to `url` returns HTTP 200.
+
+ Follows redirects (allow_redirects=True). Any network error or non-200
+ status returns False - the caller treats this as "no image available."
+ """
+ try:
+ resp = session.head(url, timeout=timeout, allow_redirects=True)
+ return resp.status_code == 200
+ except requests.RequestException:
+ return False
+
+
+# ─── Poll entry point ─────────────────────────────────────────────────────────
+
+def poll(
+ db_path: Optional[str] = None,
+ delay: float = 0.05,
+ reset: bool = False,
+) -> dict:
+ """
+ For every member in the `members` table, HEAD-check the unitedstates/images
+ URL and set photo_url when the image is confirmed to exist (HTTP 200).
+
+ Idempotent: re-running safely overwrites photo_url with the same verified
+ URL. Members whose HEAD check returns non-200 are left at NULL (first run)
+ or retain their existing value (re-run) - we never clear a previously
+ confirmed URL unless `reset=True`.
+
+ Parameters
+ ----------
+ db_path : optional DB path override
+ delay : seconds to sleep between HEAD requests (GitHub CDN rate courtesy)
+ reset : if True, clear all photo_url values before re-checking
+
+ Returns
+ -------
+ dict with keys: checked, updated, skipped
+ checked - total members evaluated
+ updated - photo_url set (HTTP 200 confirmed)
+ skipped - no image found (HTTP 404 / other non-200)
+ """
+ conn = get_connection(db_path) if db_path else get_connection()
+ init_schema(conn)
+ ensure_votes_schema(conn)
+
+ if reset:
+ logger.info("member_photos: resetting all photo_url values …")
+ conn.execute("UPDATE members SET photo_url = NULL")
+ conn.commit()
+
+ rows = conn.execute(
+ "SELECT id, bioguide_id FROM members WHERE bioguide_id IS NOT NULL"
+ ).fetchall()
+
+ total = len(rows)
+ logger.info("member_photos: checking %d member(s) …", total)
+
+ session = requests.Session()
+ session.headers["User-Agent"] = "docket-ingest/1.0 (+https://github.com/docket)"
+
+ checked = updated = skipped = 0
+ for row in rows:
+ bioguide_id = row["bioguide_id"]
+ candidate = build_photo_url(bioguide_id)
+
+ if head_check(candidate, session):
+ conn.execute(
+ "UPDATE members SET photo_url = ? WHERE bioguide_id = ?",
+ (candidate, bioguide_id),
+ )
+ updated += 1
+ else:
+ skipped += 1
+
+ checked += 1
+
+ if checked % 500 == 0:
+ conn.commit()
+ logger.info(
+ "member_photos: progress %d/%d (updated=%d skipped=%d)",
+ checked, total, updated, skipped,
+ )
+
+ if delay > 0:
+ time.sleep(delay)
+
+ conn.commit()
+ conn.close()
+ logger.info(
+ "member_photos: done. checked=%d updated=%d skipped=%d",
+ checked, updated, skipped,
+ )
+ return {"checked": checked, "updated": updated, "skipped": skipped}
+
+
+# ─── CLI ──────────────────────────────────────────────────────────────────────
+
+def main() -> None:
+ logging.basicConfig(
+ level=logging.INFO,
+ format="%(asctime)s [%(levelname)s] %(name)s: %(message)s",
+ )
+ parser = argparse.ArgumentParser(
+ description="Docket member photo URL poller (unitedstates/images)"
+ )
+ parser.add_argument("--once", action="store_true", help="Run once and exit.")
+ parser.add_argument("--db", help="Override DB path.")
+ parser.add_argument(
+ "--delay",
+ type=float,
+ default=0.05,
+ help="Seconds between HEAD requests (default: 0.05).",
+ )
+ parser.add_argument(
+ "--reset",
+ action="store_true",
+ help="Clear all photo_url values before re-checking.",
+ )
+ args = parser.parse_args()
+ if not args.once:
+ parser.print_help()
+ return
+ result = poll(db_path=args.db, delay=args.delay, reset=args.reset)
+ print(
+ f"member_photos: checked={result['checked']} "
+ f"updated={result['updated']} "
+ f"skipped={result['skipped']}"
+ )
+
+
+if __name__ == "__main__":
+ main()
services/ingest/ingest/pollers/members.py +123 -41
@@ -1,19 +1,20 @@
"""
Member roster poller - unitedstates/congress-legislators.
-Fetches `legislators-current.json` from the keyless GitHub dataset and
-populates the `members` table with full metadata. source_url for every
-member row is the official Bioguide page at bioguide.congress.gov (a .gov
-domain, allowlisted by rule #1).
+Fetches `legislators-current.json` (and optionally `legislators-historical.json`)
+from the keyless GitHub dataset and populates the `members` table with full
+metadata. source_url for every member row is the official Bioguide page at
+bioguide.congress.gov (a .gov domain, allowlisted by rule #1).
Also owns the shared schema additions used by votes_house.py and
votes_enrich.py:
- • members table
+ • members table (including photo_url column - see member_photos.py)
• vote_positions table (+ indexes)
• Extended columns on the votes table (congress, session, roll_number, …)
Run once (CLI):
python -m ingest.pollers.members --once
+ python -m ingest.pollers.members --once --historical # also ingests historical roster
Imported by:
votes_house.poll() - calls ensure_votes_schema() before inserting
@@ -38,6 +39,11 @@ LEGISLATORS_JSON_URL = (
"/gh-pages/legislators-current.json"
)
+LEGISLATORS_HISTORICAL_JSON_URL = (
+ "https://raw.githubusercontent.com/unitedstates/congress-legislators"
+ "/gh-pages/legislators-historical.json"
+)
+
# Official bioguide page - ends in .gov, always allowlisted.
BIOGUIDE_BASE = "https://bioguide.congress.gov/search/bio"
@@ -62,7 +68,8 @@ def ensure_votes_schema(conn) -> None:
lis_member_id TEXT,
govtrack_id INTEGER,
icpsr_id INTEGER,
- source_url TEXT NOT NULL
+ source_url TEXT NOT NULL,
+ photo_url TEXT
);
CREATE INDEX IF NOT EXISTS idx_members_lis
@@ -104,6 +111,12 @@ def ensure_votes_schema(conn) -> None:
except Exception:
pass # column already present - ignore
+ # Additive column on members - photo_url set by member_photos.py.
+ try:
+ conn.execute("ALTER TABLE members ADD COLUMN photo_url TEXT")
+ except Exception:
+ pass # column already present - ignore
+
conn.commit()
@@ -159,44 +172,17 @@ def _parse_legislators(data: list) -> list[dict]:
return members
-# ─── Poll entry point ─────────────────────────────────────────────────────────
-
-def poll(db_path: Optional[str] = None) -> int:
- """
- Fetch the full current member roster and upsert into `members`.
+# ─── Shared upsert helper ─────────────────────────────────────────────────────
- Returns
- -------
- Number of members upserted (created or updated).
+def _upsert_members(conn, members: list[dict]) -> int:
"""
- conn = get_connection(db_path) if db_path else get_connection()
- init_schema(conn)
- ensure_votes_schema(conn)
+ Upsert a list of parsed member dicts into the members table.
- logger.info("members: fetching legislators-current.json …")
- try:
- resp = requests.get(LEGISLATORS_JSON_URL, timeout=30)
- resp.raise_for_status()
- except requests.RequestException as exc:
- logger.error("members: fetch error: %s", exc)
- conn.close()
- return 0
-
- try:
- data = resp.json()
- except ValueError as exc:
- logger.error("members: JSON parse error: %s", exc)
- conn.close()
- return 0
-
- if not isinstance(data, list):
- logger.error("members: unexpected JSON shape (expected list, got %s)", type(data))
- conn.close()
- return 0
-
- members = _parse_legislators(data)
- logger.info("members: parsed %d legislator(s) from roster", len(members))
+ photo_url is intentionally excluded from the SET clause so that
+ member_photos.py can own that column independently without conflicts.
+ Returns the number of rows written.
+ """
upserted = 0
for m in members:
validate_source_url(m["source_url"]) # gate - must not raise for bioguide.congress.gov
@@ -231,13 +217,104 @@ def poll(db_path: Optional[str] = None) -> int:
),
)
upserted += 1
+ return upserted
+
+
+# ─── Poll entry points ────────────────────────────────────────────────────────
+
+def poll(db_path: Optional[str] = None) -> int:
+ """
+ Fetch the full current member roster and upsert into `members`.
+
+ Returns
+ -------
+ Number of members upserted (created or updated).
+ """
+ conn = get_connection(db_path) if db_path else get_connection()
+ init_schema(conn)
+ ensure_votes_schema(conn)
+
+ logger.info("members: fetching legislators-current.json …")
+ try:
+ resp = requests.get(LEGISLATORS_JSON_URL, timeout=30)
+ resp.raise_for_status()
+ except requests.RequestException as exc:
+ logger.error("members: fetch error: %s", exc)
+ conn.close()
+ return 0
+
+ try:
+ data = resp.json()
+ except ValueError as exc:
+ logger.error("members: JSON parse error: %s", exc)
+ conn.close()
+ return 0
+ if not isinstance(data, list):
+ logger.error("members: unexpected JSON shape (expected list, got %s)", type(data))
+ conn.close()
+ return 0
+
+ members = _parse_legislators(data)
+ logger.info("members: parsed %d legislator(s) from current roster", len(members))
+
+ upserted = _upsert_members(conn, members)
conn.commit()
conn.close()
logger.info("members: upserted %d member(s)", upserted)
return upserted
+def poll_historical(db_path: Optional[str] = None) -> int:
+ """
+ Fetch the full historical member roster (every person who has ever served
+ in Congress) from legislators-historical.json and upsert into `members`.
+
+ The bioguide_id key is shared with the current roster, so current members
+ already present are simply refreshed (not duplicated). photo_url is left
+ untouched - member_photos.py owns that column.
+
+ Returns
+ -------
+ Number of historical members upserted.
+ """
+ conn = get_connection(db_path) if db_path else get_connection()
+ init_schema(conn)
+ ensure_votes_schema(conn)
+
+ logger.info("members: fetching legislators-historical.json …")
+ try:
+ resp = requests.get(LEGISLATORS_HISTORICAL_JSON_URL, timeout=60)
+ resp.raise_for_status()
+ except requests.RequestException as exc:
+ logger.error("members: historical fetch error: %s", exc)
+ conn.close()
+ return 0
+
+ try:
+ data = resp.json()
+ except ValueError as exc:
+ logger.error("members: historical JSON parse error: %s", exc)
+ conn.close()
+ return 0
+
+ if not isinstance(data, list):
+ logger.error(
+ "members: unexpected historical JSON shape (expected list, got %s)", type(data)
+ )
+ conn.close()
+ return 0
+
+ members = _parse_legislators(data)
+ logger.info("members: parsed %d historical legislator(s)", len(members))
+
+ upserted = _upsert_members(conn, members)
+ conn.commit()
+ conn.close()
+ logger.info("members: upserted %d historical member(s)", upserted)
+ return upserted
+
+
# ─── CLI ──────────────────────────────────────────────────────────────────────
def main() -> None:
@@ -247,13 +324,18 @@ def main() -> None:
)
parser = argparse.ArgumentParser(description="Docket member roster poller")
parser.add_argument("--once", action="store_true", help="Run once and exit.")
+ parser.add_argument("--historical", action="store_true",
+ help="Also ingest legislators-historical.json (every member ever).")
parser.add_argument("--db", help="Override DB path.")
args = parser.parse_args()
if not args.once:
parser.print_help()
return
count = poll(db_path=args.db)
- print(f"members: {count} upserted")
+ print(f"members (current): {count} upserted")
+ if args.historical:
+ h_count = poll_historical(db_path=args.db)
+ print(f"members (historical): {h_count} upserted")
if __name__ == "__main__":
services/ingest/tests/test_meetings.py +594 -0
@@ -0,0 +1,594 @@
+"""
+Tests for ingest/pollers/meetings.py
+
+Coverage:
+ • _parse_senate_xml() - full XML, empty identifier skipped
+ • _parse_house_day_page() - normal row, subcommittee row, rescheduled row
+ • _parse_house_event_type() - hearing / markup / business_meeting / unknown
+ • _parse_floor_xml() - typical floor XML structure
+ • _build_datetime_iso() - 12h and 24h formats, missing inputs
+ • _norm_senate_type() - all known types + unknown fallback
+ • _norm_house_type() - prefix matching
+ • _detect_status() - rescheduled / canceled / normal
+ • _ensure_tables() - idempotent schema creation
+ • _upsert_meeting() idempotency - double-upsert does not duplicate
+ • poll_senate() with mocked HTTP
+"""
+
+import os
+import sqlite3
+import tempfile
+from datetime import date
+from unittest.mock import patch, MagicMock
+
+import pytest
+
+from ingest.db import get_connection, init_schema
+from ingest.pollers.meetings import (
+ _parse_senate_xml,
+ _parse_house_day_page,
+ _parse_house_event_type,
+ _parse_floor_xml,
+ _build_datetime_iso,
+ _norm_senate_type,
+ _norm_house_type,
+ _detect_status,
+ _ensure_tables,
+ _upsert_meeting,
+ _most_recent_monday,
+ SOURCE_SENATE,
+ SOURCE_HOUSE_CMT,
+ SOURCE_HOUSE_FLR,
+ SENATE_PAGE_URL,
+)
+
+
+# ---------------------------------------------------------------------------
+# Fixtures
+# ---------------------------------------------------------------------------
+
+def _make_db() -> tuple:
+ """Return (conn, path) for a fresh temp DB."""
+ path = tempfile.mktemp(suffix=".db")
+ conn = get_connection(path)
+ init_schema(conn)
+ _ensure_tables(conn)
+ return conn, path
+
+
+# ---------------------------------------------------------------------------
+# _build_datetime_iso
+# ---------------------------------------------------------------------------
+
+@pytest.mark.parametrize("date_str,time_str,expected", [
+ ("2026-06-03", "10:00 AM", "2026-06-03T10:00:00"),
+ ("2026-07-15", "02:30 PM", "2026-07-15T14:30:00"),
+ ("2026-07-15", "09:45 AM", "2026-07-15T09:45:00"),
+ ("2026-07-15", "14:30:00", "2026-07-15T14:30:00"),
+ ("2026-06-03", None, None),
+ (None, "10:00 AM", None),
+ ("", "10:00 AM", None),
+ ("2026-06-03", "", None),
+ ("2026-06-03", "bad time", None),
+])
+def test_build_datetime_iso(date_str, time_str, expected):
+ assert _build_datetime_iso(date_str, time_str) == expected
+
+
+# ---------------------------------------------------------------------------
+# _norm_senate_type
+# ---------------------------------------------------------------------------
+
+@pytest.mark.parametrize("raw,expected", [
+ ("Open Hearing", "hearing"),
+ ("Closed Hearing", "hearing"),
+ ("Field Hearing", "hearing"),
+ ("Open Markup", "markup"),
+ ("Closed Markup", "markup"),
+ ("Open Business Meeting", "business_meeting"),
+ ("Closed Business Meeting", "business_meeting"),
+ ("Executive Session", "business_meeting"),
+ ("Unknown Type", "other"),
+ ("", "other"),
+])
+def test_norm_senate_type(raw, expected):
+ assert _norm_senate_type(raw) == expected
+
+
+# ---------------------------------------------------------------------------
+# _norm_house_type
+# ---------------------------------------------------------------------------
+
+@pytest.mark.parametrize("h1,expected", [
+ ('Hearing: "The Budget Request"', "hearing"),
+ ("Markup: FY 2027 NDAA", "markup"),
+ ("Business Meeting to Consider Pending Business", "business_meeting"),
+ ("Full Committee Hearing on Defense", "hearing"),
+ ("Unknown prefix: Something", "other"),
+ ("", "other"),
+])
+def test_norm_house_type(h1, expected):
+ assert _norm_house_type(h1) == expected
+
+
+# ---------------------------------------------------------------------------
+# _detect_status
+# ---------------------------------------------------------------------------
+
+@pytest.mark.parametrize("text,expected", [
+ ("Rescheduled", "postponed"),
+ ("Postponed", "postponed"),
+ ("Canceled", "canceled"),
+ ("Cancelled", "canceled"),
+ ("", "scheduled"),
+ ("Some other note", "scheduled"),
+])
+def test_detect_status(text, expected):
+ assert _detect_status(text) == expected
+
+
+# ---------------------------------------------------------------------------
+# _most_recent_monday
+# ---------------------------------------------------------------------------
+
+def test_most_recent_monday_on_monday():
+ mon = date(2026, 6, 22) # Known Monday
+ assert _most_recent_monday(mon) == mon
+
+def test_most_recent_monday_on_thursday():
+ thu = date(2026, 6, 25) # Thursday
+ assert _most_recent_monday(thu) == date(2026, 6, 22)
+
+def test_most_recent_monday_on_sunday():
+ sun = date(2026, 6, 28) # Sunday
+ assert _most_recent_monday(sun) == date(2026, 6, 22)
+
+
+# ---------------------------------------------------------------------------
+# _parse_senate_xml
+# ---------------------------------------------------------------------------
+
+SENATE_XML_SAMPLE = b"""<?xml version="1.0" encoding="UTF-8"?>
+<css_meetings_scheduled>
+ <meeting>
+ <cmte_code/>
+ <committee/>
+ <type/>
+ <date>26-JUN-2026</date>
+ <date_iso_8601>2026-06-26</date_iso_8601>
+ <day_of_week>Today</day_of_week>
+ <time>12:00:00</time>
+ <room/>
+ <matter>No committee hearings scheduled</matter>
+ <video_url/>
+ <senate_cable_channel/>
+ </meeting>
+ <meeting>
+ <identifier>338613</identifier>
+ <last_update>06-26-2026 11:26:27 AM</last_update>
+ <last_update_iso_8601>2026-06-26T11:26:27.000000Z-04:00</last_update_iso_8601>
+ <cmte_code>SSHR00</cmte_code>
+ <committee>Health, Education, Labor, and Pensions</committee>
+ <type>Open Business Meeting</type>
+ <date>15-JUL-2026 09:45 AM</date>
+ <date_iso_8601>2026-07-15</date_iso_8601>
+ <day_of_week>Wednesday</day_of_week>
+ <time>09:45 AM</time>
+ <time_iso_8601>09:45:00</time_iso_8601>
+ <room>SD-430</room>
+ <matter>Business meeting to consider pending calendar business.</matter>
+ </meeting>
+ <meeting>
+ <identifier>338612</identifier>
+ <last_update>06-24-2026 05:53:16 PM</last_update>
+ <last_update_iso_8601>2026-06-24T17:53:16.000000Z-04:00</last_update_iso_8601>
+ <cmte_code>SSAS17</cmte_code>
+ <committee>Armed Services</committee>
+ <sub_cmte>Personnel Subcommittee</sub_cmte>
+ <type>Open Hearing</type>
+ <date>15-JUL-2026 02:30 PM</date>
+ <date_iso_8601>2026-07-15</date_iso_8601>
+ <day_of_week>Wednesday</day_of_week>
+ <time>02:30 PM</time>
+ <time_iso_8601>14:30:00</time_iso_8601>
+ <room>SR-222</room>
+ <matter>Hearings to examine the TRICARE pharmacy program.</matter>
+ </meeting>
+</css_meetings_scheduled>
+"""
+
+def test_parse_senate_xml_skips_placeholder():
+ """The first entry (no <identifier>) is a placeholder and must be skipped."""
+ meetings = _parse_senate_xml(SENATE_XML_SAMPLE)
+ assert len(meetings) == 2
+
+def test_parse_senate_xml_business_meeting():
+ meetings = _parse_senate_xml(SENATE_XML_SAMPLE)
+ bm = next(m for m in meetings if m["external_id"] == "338613")
+ assert bm["chamber"] == "Senate"
+ assert bm["committee"] == "Health, Education, Labor, and Pensions"
+ assert bm["date"] == "2026-07-15"
+ assert bm["time"] == "09:45 AM"
+ assert bm["datetime_iso"] == "2026-07-15T09:45:00"
+ assert bm["type"] == "business_meeting"
+ assert bm["location"] == "SD-430"
+ assert bm["status"] == "scheduled"
+ assert bm["source_url"] == SENATE_PAGE_URL
+
+def test_parse_senate_xml_subcommittee_hearing():
+ meetings = _parse_senate_xml(SENATE_XML_SAMPLE)
+ h = next(m for m in meetings if m["external_id"] == "338612")
+ assert h["type"] == "hearing"
+ assert h["committee"] == "Personnel Subcommittee (Armed Services)"
+ assert h["title"] == "Hearings to examine the TRICARE pharmacy program."
+ assert h["datetime_iso"] == "2026-07-15T14:30:00"
+
+def test_parse_senate_xml_bad_xml():
+ result = _parse_senate_xml(b"not xml at all <broken>")
+ assert result == []
+
+
+# ---------------------------------------------------------------------------
+# _parse_house_day_page
+# ---------------------------------------------------------------------------
+
+HOUSE_DAY_HTML = """
+<html><body>
+<table id="MainContent_GridViewMeetings">
+<tr><th>Title</th><th>Time</th><th>Location</th></tr>
+<tr>
+ <td>
+ <a href="ByEvent.aspx?EventID=119243"
+ title="&quot;A Review of the FY2027 DHS Budget&quot;">
+ "A Review of the FY2027 DHS Budget"</a>
+ <br />
+ <span class="text-tiny" title="Committee on Homeland Security">
+ Committee on Homeland Security
+ </span>
+ </td>
+ <td><span class="text-small">10:00 AM</span></td>
+ <td><span class="text-small">310 CHOB</span></td>
+</tr>
+<tr>
+ <td>
+ <a href="ByEvent.aspx?EventID=119340"
+ title="Future of United States Maritime">
+ Future of United States Maritime</a>
+ <br />
+ <span class="text-tiny" title="Subcommittee on Coast Guard">
+ Subcommittee on Coast Guard
+ (Committee on Transportation and Infrastructure)
+ </span>
+ </td>
+ <td><span class="text-small">10:00 AM</span></td>
+ <td><span class="text-small">2167 RHOB</span></td>
+</tr>
+<tr>
+ <td>
+ <a href="ByEvent.aspx?EventID=118474"
+ title="Crypto Capital of the World">
+ Crypto Capital of the World</a>
+ <span class='muted'>(Rescheduled)</span>
+ <br />
+ <span class="text-tiny" title="Subcommittee on Oversight">
+ Subcommittee on Oversight
+ (Committee on Ways and Means)
+ </span>
+ </td>
+ <td><span class="text-small">9:00 AM</span></td>
+ <td><span class="text-small">1100 LHOB</span></td>
+</tr>
+</table>
+</body></html>
+"""
+
+def test_parse_house_day_page_count():
+ d = date(2026, 6, 3)
+ meetings = _parse_house_day_page(HOUSE_DAY_HTML, d)
+ assert len(meetings) == 3
+
+def test_parse_house_day_page_basic():
+ d = date(2026, 6, 3)
+ meetings = _parse_house_day_page(HOUSE_DAY_HTML, d)
+ m = next(m for m in meetings if m["external_id"] == "119243")
+ assert m["chamber"] == "House"
+ assert m["date"] == "2026-06-03"
+ assert m["time"] == "10:00 AM"
+ assert m["location"] == "310 CHOB"
+ assert m["status"] == "scheduled"
+ assert m["type"] is None # not yet fetched from detail page
+ assert "docs.house.gov" in m["source_url"]
+ assert m["datetime_iso"] == "2026-06-03T10:00:00"
+
+def test_parse_house_day_page_subcommittee():
+ d = date(2026, 6, 3)
+ meetings = _parse_house_day_page(HOUSE_DAY_HTML, d)
+ m = next(m for m in meetings if m["external_id"] == "119340")
+ assert "Subcommittee on Coast Guard" in m["committee"]
+ assert "Transportation" in m["committee"]
+
+def test_parse_house_day_page_rescheduled():
+ d = date(2026, 6, 3)
+ meetings = _parse_house_day_page(HOUSE_DAY_HTML, d)
+ m = next(m for m in meetings if m["external_id"] == "118474")
+ assert m["status"] == "postponed"
+
+def test_parse_house_day_page_no_table():
+ """Page without GridView returns empty list."""
+ d = date(2026, 6, 3)
+ meetings = _parse_house_day_page("<html><body>No meetings found.</body></html>", d)
+ assert meetings == []
+
+
+# ---------------------------------------------------------------------------
+# _parse_house_event_type
+# ---------------------------------------------------------------------------
+
+HOUSE_EVENT_HEARING = """
+<html><body>
+<div id="previewPanel">
+ <div class="well">
+ <h1>Hearing: "A Review of the FY2027 DHS Budget"
+ <small><p>Committee on Homeland Security</p></small>
+ </h1>
+ </div>
+</div>
+</body></html>
+"""
+
+HOUSE_EVENT_MARKUP = """
+<html><body>
+<div id="previewPanel">
+ <h1>Markup: FY2027 NDAA
+ <small><p>Committee on Armed Services</p></small>
+ </h1>
+</div>
+</body></html>
+"""
+
+HOUSE_EVENT_BUSINESS = """
+<html><body>
+<h1>Business Meeting to Consider Pending Business</h1>
+</body></html>
+"""
+
+def test_parse_house_event_type_hearing():
+ assert _parse_house_event_type(HOUSE_EVENT_HEARING) == "hearing"
+
+def test_parse_house_event_type_markup():
+ assert _parse_house_event_type(HOUSE_EVENT_MARKUP) == "markup"
+
+def test_parse_house_event_type_business_meeting():
+ assert _parse_house_event_type(HOUSE_EVENT_BUSINESS) == "business_meeting"
+
+def test_parse_house_event_type_fallback():
+ assert _parse_house_event_type("<html><h1>Unknown Header</h1></html>") == "other"
+
+
+# ---------------------------------------------------------------------------
+# _parse_floor_xml
+# ---------------------------------------------------------------------------
+
+FLOOR_XML = b"""<?xml version="1.0" encoding="UTF-8"?>
+<floorschedule congress-num="119" week-date="2026-06-22"
+ create-date="2026-06-16T17:55:16.977">
+ <category type="Items that may be considered under suspension of the rules"
+ sort-order="1">
+ <floor-items>
+ <floor-item id="409612">
+ <legis-num>H.R. 7396</legis-num>
+ <floor-text>Native American Entrepreneurial Opportunity Act</floor-text>
+ </floor-item>
+ <floor-item id="409613">
+ <legis-num>H.R. 7401</legis-num>
+ <floor-text>Small Business Lending Fraud Prevention Act</floor-text>
+ </floor-item>
+ </floor-items>
+ </category>
+ <category type="Items considered pursuant to a rule" sort-order="2">
+ <floor-items>
+ <floor-item id="409620">
+ <legis-num>H.R. 1</legis-num>
+ <floor-text>Big Beautiful Bill Act</floor-text>
+ </floor-item>
+ </floor-items>
+ </category>
+</floorschedule>
+"""
+
+def test_parse_floor_xml_count():
+ monday = date(2026, 6, 22)
+ source_url = "https://docs.house.gov/floor/Default.aspx?date=2026-06-22"
+ meetings = _parse_floor_xml(FLOOR_XML, monday, source_url)
+ assert len(meetings) == 3
+
+def test_parse_floor_xml_structure():
+ monday = date(2026, 6, 22)
+ source_url = "https://docs.house.gov/floor/Default.aspx?date=2026-06-22"
+ meetings = _parse_floor_xml(FLOOR_XML, monday, source_url)
+
+ first = meetings[0]
+ assert first["chamber"] == "House"
+ assert first["type"] == "floor"
+ assert first["date"] == "2026-06-22"
+ assert first["location"] == "House Floor"
+ assert first["status"] == "scheduled"
+ assert "H.R. 7396" in first["title"]
+ assert first["source_url"] == source_url
+ assert first["external_id"].startswith("20260622_")
+
+def test_parse_floor_xml_external_id_unique():
+ monday = date(2026, 6, 22)
+ source_url = "https://docs.house.gov/floor/Default.aspx?date=2026-06-22"
+ meetings = _parse_floor_xml(FLOOR_XML, monday, source_url)
+ ids = [m["external_id"] for m in meetings]
+ assert len(ids) == len(set(ids)), "External IDs must be unique"
+
+def test_parse_floor_xml_bad_xml():
+ result = _parse_floor_xml(b"not xml", date(2026, 6, 22), "https://docs.house.gov/floor/")
+ assert result == []
+
+
+# ---------------------------------------------------------------------------
+# Schema and upsert
+# ---------------------------------------------------------------------------
+
+def test_ensure_tables_idempotent():
+ conn, path = _make_db()
+ _ensure_tables(conn) # second call must not raise
+ _ensure_tables(conn) # third call
+ tables = {r[0] for r in conn.execute(
+ "SELECT name FROM sqlite_master WHERE type='table'"
+ ).fetchall()}
+ assert "meetings" in tables
+ conn.close()
+ os.unlink(path)
+
+
+def _sample_meeting(**kwargs) -> dict:
+ base = {
+ "source": SOURCE_SENATE,
+ "external_id": "338613",
+ "chamber": "Senate",
+ "committee": "Health, Education, Labor, and Pensions",
+ "date": "2026-07-15",
+ "time": "09:45 AM",
+ "datetime_iso": "2026-07-15T09:45:00",
+ "title": "Business meeting to consider pending calendar business.",
+ "subject": "Business meeting to consider pending calendar business.",
+ "type": "business_meeting",
+ "location": "SD-430",
+ "status": "scheduled",
+ "source_url": SENATE_PAGE_URL,
+ }
+ base.update(kwargs)
+ return base
+
+
+def test_upsert_meeting_inserts_row():
+ conn, path = _make_db()
+ m = _sample_meeting()
+ _upsert_meeting(conn, m)
+ conn.commit()
+ row = conn.execute(
+ "SELECT * FROM meetings WHERE source=? AND external_id=?",
+ (m["source"], m["external_id"])
+ ).fetchone()
+ assert row is not None
+ assert row["committee"] == "Health, Education, Labor, and Pensions"
+ assert row["type"] == "business_meeting"
+ conn.close()
+ os.unlink(path)
+
+
+def test_upsert_meeting_idempotent():
+ """Double-upsert must not create duplicate rows."""
+ conn, path = _make_db()
+ m = _sample_meeting()
+ _upsert_meeting(conn, m)
+ _upsert_meeting(conn, m)
+ conn.commit()
+ count = conn.execute(
+ "SELECT COUNT(*) FROM meetings WHERE source=? AND external_id=?",
+ (m["source"], m["external_id"])
+ ).fetchone()[0]
+ assert count == 1
+ conn.close()
+ os.unlink(path)
+
+
+def test_upsert_meeting_updates_status():
+ """Upsert with changed status overwrites the row."""
+ conn, path = _make_db()
+ m = _sample_meeting(status="scheduled")
+ _upsert_meeting(conn, m)
+ conn.commit()
+
+ m2 = _sample_meeting(status="canceled")
+ _upsert_meeting(conn, m2)
+ conn.commit()
+
+ row = conn.execute(
+ "SELECT status FROM meetings WHERE source=? AND external_id=?",
+ (m["source"], m["external_id"])
+ ).fetchone()
+ assert row["status"] == "canceled"
+ conn.close()
+ os.unlink(path)
+
+
+def test_upsert_meeting_house_chamber():
+ conn, path = _make_db()
+ m = _sample_meeting(
+ source=SOURCE_HOUSE_CMT,
+ external_id="119243",
+ chamber="House",
+ committee="Committee on Homeland Security",
+ type="hearing",
+ source_url="https://docs.house.gov/Committee/Calendar/ByEvent.aspx?EventID=119243",
+ )
+ _upsert_meeting(conn, m)
+ conn.commit()
+ row = conn.execute(
+ "SELECT chamber, type FROM meetings WHERE source=? AND external_id=?",
+ (SOURCE_HOUSE_CMT, "119243")
+ ).fetchone()
+ assert row["chamber"] == "House"
+ assert row["type"] == "hearing"
+ conn.close()
+ os.unlink(path)
+
+
+# ---------------------------------------------------------------------------
+# poll_senate with mocked HTTP
+# ---------------------------------------------------------------------------
+
+def test_poll_senate_mock(tmp_path):
+ """poll_senate() must store 2 meetings from sample XML and be idempotent."""
+ from ingest.pollers.meetings import poll_senate
+
+ db_path = str(tmp_path / "test.db")
+ conn = get_connection(db_path)
+ init_schema(conn)
+ _ensure_tables(conn)
+
+ mock_resp = MagicMock()
+ mock_resp.raise_for_status.return_value = None
+ mock_resp.content = SENATE_XML_SAMPLE
+
+ with patch("ingest.pollers.meetings.requests.get", return_value=mock_resp):
+ count1 = poll_senate(conn)
+
+ assert count1 == 2, f"Expected 2 new rows, got {count1}"
+
+ # Second call: same data, nothing new
+ with patch("ingest.pollers.meetings.requests.get", return_value=mock_resp):
+ count2 = poll_senate(conn)
+
+ assert count2 == 0, f"Expected 0 on re-run, got {count2}"
+
+ # Verify rows in DB
+ rows = conn.execute("SELECT * FROM meetings WHERE source=?",
+ (SOURCE_SENATE,)).fetchall()
+ assert len(rows) == 2
+ dates = {r["date"] for r in rows}
+ assert "2026-07-15" in dates
+
+ conn.close()
+
+
+# ---------------------------------------------------------------------------
+# Source URL is allowlisted
+# ---------------------------------------------------------------------------
+
+def test_source_urls_are_allowlisted():
+ """Confirm that all stored source_urls pass the validator."""
+ from ingest.source_validator import is_allowlisted
+
+ urls = [
+ SENATE_PAGE_URL,
+ "https://docs.house.gov/Committee/Calendar/ByEvent.aspx?EventID=119243",
+ "https://docs.house.gov/floor/Default.aspx?date=2026-06-22",
+ ]
+ for url in urls:
+ assert is_allowlisted(url), f"URL not allowlisted: {url}"
services/ingest/tests/test_member_photos.py +321 -0
@@ -0,0 +1,321 @@
+"""
+Tests for member_photos poller.
+
+Coverage:
+ • build_photo_url() - URL format and bioguide substitution
+ • head_check() - 200/404/exception handling (mocked network)
+ • poll() - DB writes, idempotency, stats (mocked network)
+ • Schema - photo_url column exists after ensure_votes_schema()
+"""
+
+import os
+import tempfile
+from unittest.mock import MagicMock, patch
+
+import pytest
+import requests
+
+from ingest.db import get_connection, init_schema
+from ingest.pollers.members import ensure_votes_schema
+from ingest.pollers.member_photos import (
+ PHOTO_BASE,
+ build_photo_url,
+ head_check,
+ poll,
+)
+
+
+# ─── build_photo_url() ────────────────────────────────────────────────────────
+
+class TestBuildPhotoUrl:
+ def test_known_bioguide(self):
+ """A000055 is Robert Aderholt - canonical test bioguide."""
+ url = build_photo_url("A000055")
+ assert url == f"{PHOTO_BASE}/A000055.jpg"
+
+ def test_url_contains_bioguide(self):
+ url = build_photo_url("Z999999")
+ assert "Z999999" in url
+
+ def test_url_ends_with_jpg(self):
+ url = build_photo_url("B000574")
+ assert url.endswith(".jpg")
+
+ def test_url_uses_450x550_size(self):
+ url = build_photo_url("A000055")
+ assert "congress/450x550" in url
+
+ def test_url_host_is_github_pages(self):
+ url = build_photo_url("A000055")
+ assert "unitedstates.github.io" in url
+
+ def test_url_format_is_stable(self):
+ """Verify the full URL matches the documented pattern exactly."""
+ bioguide = "S000033"
+ url = build_photo_url(bioguide)
+ expected = (
+ "https://unitedstates.github.io/images/congress/450x550/S000033.jpg"
+ )
+ assert url == expected
+
+
+# ─── head_check() ─────────────────────────────────────────────────────────────
+
+class TestHeadCheck:
+ def _make_session(self, status_code=200):
+ session = MagicMock()
+ resp = MagicMock()
+ resp.status_code = status_code
+ session.head.return_value = resp
+ return session
+
+ def test_200_returns_true(self):
+ session = self._make_session(200)
+ assert head_check("https://example.com/photo.jpg", session) is True
+
+ def test_404_returns_false(self):
+ session = self._make_session(404)
+ assert head_check("https://example.com/photo.jpg", session) is False
+
+ def test_301_after_redirect_returns_true(self):
+ # allow_redirects=True means the final status is what matters
+ session = self._make_session(200) # 200 after following redirect
+ assert head_check("https://example.com/photo.jpg", session) is True
+
+ def test_500_returns_false(self):
+ session = self._make_session(500)
+ assert head_check("https://example.com/photo.jpg", session) is False
+
+ def test_request_exception_returns_false(self):
+ session = MagicMock()
+ session.head.side_effect = requests.RequestException("connection timeout")
+ assert head_check("https://example.com/photo.jpg", session) is False
+
+ def test_connection_error_returns_false(self):
+ session = MagicMock()
+ session.head.side_effect = requests.ConnectionError("refused")
+ assert head_check("https://example.com/photo.jpg", session) is False
+
+ def test_head_called_with_allow_redirects(self):
+ session = self._make_session(200)
+ head_check("https://example.com/photo.jpg", session)
+ call_kwargs = session.head.call_args
+ # allow_redirects must be passed as True
+ assert call_kwargs.kwargs.get("allow_redirects") is True or \
+ (call_kwargs.args and True in call_kwargs.args)
+
+
+# ─── Schema ───────────────────────────────────────────────────────────────────
+
+class TestSchema:
+ def _make_db(self):
+ tmp = tempfile.mktemp(suffix=".db")
+ conn = get_connection(tmp)
+ init_schema(conn)
+ ensure_votes_schema(conn)
+ return conn, tmp
+
+ def test_photo_url_column_exists(self):
+ conn, tmp = self._make_db()
+ info = {row[1] for row in conn.execute("PRAGMA table_info(members)")}
+ assert "photo_url" in info, "photo_url column should be in members table"
+ conn.close()
+ os.unlink(tmp)
+
+ def test_photo_url_is_nullable(self):
+ """photo_url must be nullable - many historical members have no image."""
+ conn, tmp = self._make_db()
+ conn.execute(
+ "INSERT INTO members (bioguide_id, full_name, source_url) "
+ "VALUES (?, ?, ?)",
+ ("T000001", "Test Member",
+ "https://bioguide.congress.gov/search/bio/T000001"),
+ )
+ conn.commit()
+ row = conn.execute(
+ "SELECT photo_url FROM members WHERE bioguide_id='T000001'"
+ ).fetchone()
+ assert row is not None
+ assert row["photo_url"] is None
+ conn.close()
+ os.unlink(tmp)
+
+ def test_ensure_votes_schema_idempotent_with_photo_url(self):
+ """Calling ensure_votes_schema twice should not raise."""
+ conn, tmp = self._make_db()
+ ensure_votes_schema(conn) # second call - should be a no-op
+ info = {row[1] for row in conn.execute("PRAGMA table_info(members)")}
+ assert "photo_url" in info
+ conn.close()
+ os.unlink(tmp)
+
+
+# ─── poll() ───────────────────────────────────────────────────────────────────
+
+def _make_db_with_members():
+ """Helper: create a temp DB seeded with two members."""
+ tmp = tempfile.mktemp(suffix=".db")
+ conn = get_connection(tmp)
+ init_schema(conn)
+ ensure_votes_schema(conn)
+ conn.execute(
+ "INSERT INTO members (bioguide_id, full_name, party, state, chamber, source_url) "
+ "VALUES (?, ?, ?, ?, ?, ?)",
+ (
+ "A000055",
+ "Robert Aderholt",
+ "Republican",
+ "AL",
+ "house",
+ "https://bioguide.congress.gov/search/bio/A000055",
+ ),
+ )
+ conn.execute(
+ "INSERT INTO members (bioguide_id, full_name, party, state, chamber, source_url) "
+ "VALUES (?, ?, ?, ?, ?, ?)",
+ (
+ "Z999999",
+ "Historical Member (no photo)",
+ "Democratic",
+ "NY",
+ "senate",
+ "https://bioguide.congress.gov/search/bio/Z999999",
+ ),
+ )
+ conn.commit()
+ conn.close()
+ return tmp
+
+
+def _mock_session_factory(hits: set):
+ """
+ Return a context-manager patch for requests.Session that returns 200 for
+ bioguide_ids in `hits` and 404 for all others.
+ """
+ def fake_head(url, timeout=10, allow_redirects=True):
+ resp = MagicMock()
+ resp.status_code = 200 if any(h in url for h in hits) else 404
+ return resp
+
+ mock_session = MagicMock()
+ mock_session.head.side_effect = fake_head
+ return mock_session
+
+
+class TestPoll:
+ def test_photo_url_set_for_200(self):
+ tmp = _make_db_with_members()
+
+ with patch("ingest.pollers.member_photos.requests.Session") as cls:
+ cls.return_value = _mock_session_factory({"A000055"})
+ poll(db_path=tmp, delay=0)
+
+ conn = get_connection(tmp)
+ row = conn.execute(
+ "SELECT photo_url FROM members WHERE bioguide_id='A000055'"
+ ).fetchone()
+ assert row["photo_url"] is not None
+ assert "A000055" in row["photo_url"]
+ assert row["photo_url"].endswith(".jpg")
+ conn.close()
+ os.unlink(tmp)
+
+ def test_photo_url_null_for_404(self):
+ tmp = _make_db_with_members()
+
+ with patch("ingest.pollers.member_photos.requests.Session") as cls:
+ cls.return_value = _mock_session_factory({"A000055"})
+ poll(db_path=tmp, delay=0)
+
+ conn = get_connection(tmp)
+ row = conn.execute(
+ "SELECT photo_url FROM members WHERE bioguide_id='Z999999'"
+ ).fetchone()
+ assert row["photo_url"] is None
+ conn.close()
+ os.unlink(tmp)
+
+ def test_stats_returned(self):
+ tmp = _make_db_with_members()
+
+ with patch("ingest.pollers.member_photos.requests.Session") as cls:
+ cls.return_value = _mock_session_factory({"A000055"})
+ result = poll(db_path=tmp, delay=0)
+
+ assert result["checked"] == 2
+ assert result["updated"] == 1
+ assert result["skipped"] == 1
+ os.unlink(tmp)
+
+ def test_idempotent_rerun_preserves_url(self):
+ """Second poll() with same hits must not clear a previously set URL."""
+ tmp = _make_db_with_members()
+
+ for _ in range(2):
+ with patch("ingest.pollers.member_photos.requests.Session") as cls:
+ cls.return_value = _mock_session_factory({"A000055"})
+ poll(db_path=tmp, delay=0)
+
+ conn = get_connection(tmp)
+ row = conn.execute(
+ "SELECT photo_url FROM members WHERE bioguide_id='A000055'"
+ ).fetchone()
+ assert row["photo_url"] is not None
+ conn.close()
+ os.unlink(tmp)
+
+ def test_reset_clears_then_repopulates(self):
+ """reset=True should clear photo_url before re-checking."""
+ tmp = _make_db_with_members()
+
+ # First run: set photo_url
+ with patch("ingest.pollers.member_photos.requests.Session") as cls:
+ cls.return_value = _mock_session_factory({"A000055"})
+ poll(db_path=tmp, delay=0)
+
+ # Second run with reset=True: clear then repopulate
+ with patch("ingest.pollers.member_photos.requests.Session") as cls:
+ cls.return_value = _mock_session_factory({"A000055"})
+ result = poll(db_path=tmp, delay=0, reset=True)
+
+ conn = get_connection(tmp)
+ row = conn.execute(
+ "SELECT photo_url FROM members WHERE bioguide_id='A000055'"
+ ).fetchone()
+ assert row["photo_url"] is not None
+ assert result["updated"] == 1
+ conn.close()
+ os.unlink(tmp)
+
+ def test_empty_members_table(self):
+ tmp = tempfile.mktemp(suffix=".db")
+ conn = get_connection(tmp)
+ init_schema(conn)
+ ensure_votes_schema(conn)
+ conn.close()
+
+ with patch("ingest.pollers.member_photos.requests.Session") as cls:
+ cls.return_value = _mock_session_factory(set())
+ result = poll(db_path=tmp, delay=0)
+
+ assert result["checked"] == 0
+ assert result["updated"] == 0
+ assert result["skipped"] == 0
+ os.unlink(tmp)
+
+ def test_photo_url_points_to_correct_base(self):
+ """Stored URL must use the 450x550 base path."""
+ tmp = _make_db_with_members()
+
+ with patch("ingest.pollers.member_photos.requests.Session") as cls:
+ cls.return_value = _mock_session_factory({"A000055"})
+ poll(db_path=tmp, delay=0)
+
+ conn = get_connection(tmp)
+ row = conn.execute(
+ "SELECT photo_url FROM members WHERE bioguide_id='A000055'"
+ ).fetchone()
+ assert "congress/450x550" in row["photo_url"]
+ assert "unitedstates.github.io" in row["photo_url"]
+ conn.close()
+ os.unlink(tmp)