The unvarnished public record index.
Avanguardia Publica is a public-facing political-data transparency tool. It aggregates, classifies, and displays publicly available information about U.S. politicians across the Federal, State, and Local levels, presenting it as a clean, read-only encyclopedia.
The project follows a decoupled, zero-cost architecture: a Python ETL pipeline pushes
data into Supabase on a schedule, and a statically-exported Next.js frontend reads from it.
The render model is static export with live client data: home/search, /directory,
/profile?id=<uuid>, and the profile contact / financial / donor / voting / media /
connections spokes query Supabase live in the browser. The legacy pretty
/[politician_id] route list is still generated at build time for GitHub Pages, but its
profile spokes now hydrate from the browser once the page exists. Read
AGENTS.md → "Render model" before touching any data-fetching code.
┌────────────────────┐ ┌──────────────────┐ ┌─────────────────────┐
│ Scraper (Python) │ ──▶ │ Supabase (Postgres)│ ◀── │ Frontend (Next.js) │
│ GitHub Actions │ │ REST API │ │ Static export → │
│ (nightly sync) │ │ │ │ GitHub Pages │
└────────────────────┘ └──────────────────┘ └─────────────────────┘
| Layer | Tech | Notes |
|---|---|---|
| Database/API | Supabase (PostgreSQL) | Auto-generated REST API; "Hub-and-Spoke" schema |
| Ingestion | Python + GitHub Actions | Free-tier / open-source data sources only |
| Frontend | Next.js (App Router) + React 19 + Tailwind CSS 4 | output: 'export' — home/search, /directory, /profile?id=<uuid>, and profile spokes read live in the browser; pretty dynamic route availability is build-time |
| Hosting | GitHub Pages (frontend) + GitHub Actions (ETL) | Zero-cost |
See spec.md for the full product/technical spec,
docs/canonical_data_and_analytics_plan.md
for the active remaining roadmap, and AGENTS.md for architecture handoff
notes.
.
├── frontend/ # Next.js app (statically exported to GitHub Pages)
│ └── src/
│ ├── app/ # Routes: / (search), /directory, /[politician_id]
│ └── lib/ # Supabase client + data helpers
├── scraper/ # Python ETL pipeline
│ ├── main.py # Entry point
│ ├── loader.py # Supabase upsert logic
│ └── extractors/ # Per-source extractors (fec, federal, govtrack, news, …)
├── migrations/ # SQL migrations
├── schema.sql # Database schema blueprint
└── .github/workflows/ # nextjs.yml (deploy) + scraper.yml (nightly ETL)
- Node.js 20.19+ LTS (or 22.13+) and npm (frontend)
- Python 3.10+ and pip (scraper)
- A Supabase project (free tier) — or run with the built-in mock data for a quick look
cd frontend
npm install
# Configure environment (see example.env)
cp example.env .env.local
# NEXT_PUBLIC_SUPABASE_URL=https://<your-project>.supabase.co
# NEXT_PUBLIC_SUPABASE_ANON_KEY=<your-anon-key>
npm run dev # http://localhost:3000Without Supabase credentials the app falls back to a small set of mock politicians, so
npm run dev works out of the box.
Useful scripts:
| Command | Description |
|---|---|
npm run dev |
Start the dev server |
npm run build |
Production build + static export to out/ |
npm run lint |
Run ESLint |
npm run typecheck |
Run the TypeScript compiler without emitting files |
cd scraper
python -m venv .venv && source .venv/bin/activate # Windows: .venv\Scripts\activate
pip install -r requirements.txt
# Configure environment (see scraper/example.env)
cp example.env .env
# Fill in SUPABASE_URL, SUPABASE_KEY (service role), and any data-source API keys
python main.py
# Validate the live schema and latest migration marker without starting the ETL
python main.py --preflight-onlyThe preflight-only command requires SUPABASE_URL and SUPABASE_KEY. It runs the same
non-mutating table, migration-marker, and RPC checks used at the start of a normal run,
prints ETL_SUMMARY_JSON, and exits before extractors, source quotas, or ETL writes.
Use it after applying a migration and before starting a long scraper run.
All scraper data sources are free-tier or open-source (no paid APIs). The news aggregator uses a multi-tier circuit-breaker strategy (Currents → NewsData.io → approved TheNewsAPI usage → GDELT URL discovery) so it degrades gracefully under rate limits without scraping article bodies.
The run summary reports each provider's actual outbound attempts, requests suppressed after a breaker opens, resulting in-process demand, its per-process local safety cap, the breaker cause, and numeric upstream quota headers when the provider supplies them. Local counters do not claim to represent account usage by other workflows or earlier runs; upstream plan allowances and the time window represented by each header should be verified with the provider.
Free access and republication rights are different questions. Production extractors must
follow docs/source_usage_policy.md: retain stable provenance,
store only the fields permitted by the provider, keep required attribution, and keep
ambiguous terms disabled until a maintainer records approval.
Financial disclosures are sourced from the official U.S. House Clerk bulk feed
(disclosures-clerk.house.gov, keyless). That feed publishes the filing index — who filed
which disclosure (Periodic Transaction Report / Annual) on what date, plus a link to the
official PDF — but not the per-transaction asset/value rows, which live inside the PDF. The
profile tab therefore lists filings with a link to each official document. Senate and state
financial disclosures are not yet covered.
| Variable | Used by | Required | Purpose |
|---|---|---|---|
NEXT_PUBLIC_SUPABASE_URL |
frontend | yes* | Supabase project URL |
NEXT_PUBLIC_SUPABASE_ANON_KEY |
frontend | yes* | Supabase anon (public) key |
ALLOW_MOCK_BUILD |
frontend | no | Explicit local/CI fixture build opt-in |
SUPABASE_URL |
scraper | yes | Supabase project URL |
SUPABASE_KEY |
scraper | yes | Service-role key (writes) |
FEC_API_KEY |
scraper | no | data.gov key for campaign-donor enrichment |
OPENSTATES_API_KEY |
scraper | no | OpenStates key for state roll-call votes |
CONGRESS_GOV_API_KEY |
scraper | no | Free data.gov key for bounded exact-detail Congress.gov metadata |
STATE_UNVERIFIED_ENRICHMENT_LIMIT |
scraper | no | Bounded count of state profiles to enrich via LittleSis |
STATE_UNVERIFIED_ENRICHMENT_OFFSET |
scraper | no | Zero-based start offset for rotating state LittleSis batches |
HOUSE_ROLL_CALL_WRITE_MODE |
scraper | no | disabled by default; enabled opts into the separately DB-gated House RPC |
SENATE_ROLL_CALL_WRITE_MODE |
scraper | no | disabled by default; reviewed nightly workflow schedules explicitly select enabled for the DB-gated Senate RPC |
CONGRESS_GOV_METADATA_WRITE_MODE |
scraper | no | disabled by default; reviewed nightly schedules explicitly select enabled after migration 0036 |
CURRENTS_API_KEY |
scraper | no | News tier 1 |
NEWSDATA_API_KEY |
scraper | no | News tier 2 (requires attribution) |
THENEWSAPI_KEY |
scraper | no | News tier 3 credential |
THENEWSAPI_PRODUCTION_APPROVED |
scraper | no | Set true only after terms/republication review |
* Local development can use mock fixtures when these are absent; static fixture builds
must explicitly set ALLOW_MOCK_BUILD=true. Production builds and runtime pages fail
visibly instead of presenting fixtures as live data. The news aggregator works with no
keys at all (it degrades to keyless GDELT URL discovery).
The official House Clerk roll-call extractor always fetches one bounded window for aggregate
reconciliation. HOUSE_ROLL_CALL_WRITE_MODE=enabled permits that same in-memory normalized
snapshot to call the private atomic House RPC only when listing, XML parsing, exact Bioguide
identity coverage, GovTrack reconciliation, and source health are all complete. Overlapping
listing pages fail closed, and parsed vote categories must exactly match the Clerk XML's
totals-by-vote.
The official Senate roll-call extractor follows the same one-fetch rule. It hashes the raw
official XML bytes, validates member rows against the XML count totals, resolves each LIS ID
only through the trusted congress-legislators LIS-to-Bioguide crosswalk, and retains the
normalized snapshot in memory. SENATE_ROLL_CALL_WRITE_MODE=enabled permits a run to call the
atomic Senate RPC only when the complete bounded listing, every official XML document, every
exact identity, every GovTrack comparison snapshot, and source health are complete. Code,
example-environment, and manual-input defaults remain disabled. After the reviewed canary and
audit, the nightly schedule explicitly selects enabled; unknown events still fail closed.
Congress.gov metadata uses exact bill and amendment identifiers already present in those two
official snapshots. It never calls collection endpoints and caps each run at 100 distinct detail
requests. CONGRESS_GOV_METADATA_WRITE_MODE=enabled permits one complete fetched batch to call
the migration 0035 RPC, which atomically writes private verified source records, normalized
measure facts, and exact roll-call links only when both upstream official snapshots and the
detail-fetch counters reconcile exactly. The successful manual canary wrote 18 measures and 43
exact links with healthy fetch and write trackers; its post-canary audit found zero provenance,
fact, link, ACL, or legacy-isolation violations, and an exact replay changed no row image or
transaction ID. Migration 0036 records that review and the nightly schedule explicitly
selects enabled; code, example-environment, and manual-input defaults remain disabled, and
unknown events fail closed. The path retains no raw JSON, writes no legacy votes, and has no
public read surface.
Migration 0030_senate_roll_call_production_enablement.sql verifies migration 0029's exact
public barrier, owner-only helper, constraint, ACL, dependency, identity-index, zero-fact, and
service-role read-only contracts. It replaces the same public function OID with a thin guarded
wrapper and enables both strict JSON-boolean database gates atomically. The helper still owns all
payload, identity, monotonic-observation, complete-snapshot, and gate locking checks. A
non-mutating preflight remains available independently of the gates. No transaction-drain phase
is needed: the prior public Senate function was always a non-mutating barrier, could not call the
helper, and both gates were false. This path never writes legacy voting_records.
Migration 0031_official_voting_records_read_surface.sql adds the first public read surface
over those normalized facts. Its person-aware get_canonical_voting_records_v2 RPC exposes only
active, verified House Clerk and Senate LIS votes from approved source contracts; the underlying
provenance tables, payload hashes, and metadata remain private. The RPC keeps existing state and
historical voting_records coverage alongside the normalized facts. The live Voting Record tab
labels official rows and links directly to the source record.
Migration 0032_official_voting_records_query_repair.sql preserves that RPC contract while
repairing its production query plan. It resolves one canonical person before reading facts,
constrains the existing indexed person/profile branches before normalizing vote labels, and
pushes vote filters into those branches. Exact before/after result comparisons passed for
representative House and Senate profiles; live calls dropped from roughly 1.5–8 seconds to
17–46 milliseconds during rollout validation.
Migration 0033_official_voting_records_deduplication_repair.sql narrows the legacy GovTrack
fallback after a review found that two same-day official roll calls could share the same
normalized question and member cast. Exact roll-call-key matches still deduplicate. A
date/question/cast fallback now deduplicates only when it identifies one distinct official roll
call; ambiguous signatures retain the legacy row. The forward repair preserves the RPC signature,
security boundary, indexed query plan, and public presentation contract.
The enabled manual production canary
passed schema preflight and wrote 25 roll calls with 2,498 exact-LIS member votes. All 25
GovTrack comparison snapshots and all 2,498 casts agreed, and the Senate fetch and write
trackers had no failures or skips. The post-canary database audit confirmed exact provenance
and normalized counts, trusted LIS/Bioguide ownership, valid retirement state, service-role
table/column DML closure, zero canonical Senate keys in legacy voting_records, and an exact
same-timestamp replay that left full stored row images and transaction IDs unchanged.
Migration 0027_house_roll_call_production_enablement.sql wraps migration 0026's reviewed
writer with a per-roll-call monotonic guard: an older fetched_at is rejected before the
private writer can mutate facts. An exact same-timestamp retry is non-mutating only after its
stored parent, controlled metadata, and complete active member-vote set match in both directions.
Before cloning the 0026 writer as an owner-only helper, the migration verifies its exact body,
owner, security mode, search path, return contract, ACLs, and zero reverse dependencies. It
replaces the same public function OID with a fail-closed barrier and commits while both gates
remain false. A second transaction drains every client transaction that could have observed the
old body before installing the final wrapper. It requires strict
JSON-boolean gates, enforces case-normalized Bioguide uniqueness, reduces service-role
table/column access to read-only, and preserves only controlled security-definer mutation paths.
A null-safe House event-prefix namespace constraint
also prevents the unrelated generic profile and retirement RPCs from colliding with House
provenance. The migration then enables the reviewed source-catalog database gates atomically.
Runtime writes still default to disabled in code and the example environment. After the
successful bounded production canary and post-canary database audit, the GitHub Actions nightly
schedule explicitly passes enabled for the same bounded write path. Manual workflow runs keep
their required disabled/enabled choice with disabled as the default, and any unrecognized
event fails closed. The database gates remain independently disableable. This path never writes
legacy voting_records.
Apply migration 0027 with ON_ERROR_STOP=1 and without psql's external
--single-transaction option. Its two checked-in transactions are the database-enforced cutover:
The installer must be a superuser or have pg_read_all_stats, otherwise preflight fails with
42501 before installing the barrier.
Phase one commits the fail-closed public barrier and removes direct service-role table/column
mutation privileges. Phase two uses a fixed pg_stat_activity cutoff to drain pre-barrier client
transactions, locks the House fact tables, and requires the reviewed zero-fact rollout baseline
before any gate can become true. If phase two fails, the exact barrier/helper/read-only state
remains resumable and both gates remain false.
Never commit secrets. .env and .env.* are gitignored (except .env.example). Templates
live in frontend/example.env and
scraper/example.env.
Both pipelines run from GitHub Actions:
.github/workflows/nextjs.yml— builds the static frontend and deploys it to GitHub Pages. SetNEXT_PUBLIC_SUPABASE_URL/NEXT_PUBLIC_SUPABASE_ANON_KEYas repository secrets (embedded in the client bundle; the anon key is public by design, protected by Supabase RLS). The home/search,/directory,/profile?id=<uuid>, and profile spoke views read Supabase live in the browser, including contact, financial disclosures, campaign donors, official federal and legacy voting records, media mentions, and connections. The legacy pretty/[politician_id]route availability is still tied to static generation, so brand-new rows should be linked through/profile?id=<uuid>until a deploy creates the SEO route. The deploy is wired to re-run automatically after a successful nightly ETL via aworkflow_runtrigger. (A failed scraper run does not trigger the deploy, so the live site keeps the last good build rather than shipping nothing.).github/workflows/scraper.yml— runs the Python ETL on a nightly schedule, writing fresh data into Supabase.
The scraper prints identity-health totals and includes them in ETL_SUMMARY_JSON.
For identity-resolution triage, focus on:
pending_identity_observer_candidates: all pending identity candidates.pending_identity_observer_blocked_candidates: newly detected pending candidates blocked by deterministic conflicts (identity_observer_blocked_*).pending_identity_observer_blocked_candidate_reasons: map of blocked-pending candidate counts by reason suffix (foridentity_observer_blocked_<reason>).blocked_identity_observer_candidates: candidates already reviewed and markedblocked, waiting for maintainer action.blocked_identity_observer_candidate_reasons: map of blocked review candidates by reason suffix.pending_identity_observer_review_candidates: pending non-conflict review work (identity_observer_pending_*, e.g. missing deterministic identity).pending_openstates_federal_duplicate_candidates/approved_openstates_federal_duplicate_candidates: queue and resolution state for OpenStates federal-legacy duplicates.openstates_federal_legacy_profiles_total/openstates_federal_legacy_profiles_refreshed_this_run: stale-legacy federal-like profile count and how many refreshed since the ETL started.
Quick triage:
pending_identity_observer_blocked_candidates > 0means the latest run introduced fresh deterministic conflicts. Includepending_identity_observer_blocked_candidate_reasonsto triage by reason.blocked_identity_observer_candidates > 0means existing maintainer-blocked conflicts are still waiting. Includeblocked_identity_observer_candidate_reasonsto see what needs review.
Optional maintainer SQL checks (run in the Supabase SQL editor):
-- Newest unresolved identity triage queue
select
status,
candidate_type,
source_legacy_politician_id as source_legacy_id,
candidate_legacy_politician_id as candidate_legacy_id,
evidence
from identity_resolution_candidates
where status in ('pending', 'blocked')
and candidate_type like 'identity_observer_%'
order by status, candidate_type, created_at desc
limit 200;-- Top blocked-reason families waiting on maintainer action
select candidate_type, count(*) as cnt
from identity_resolution_candidates
where status = 'blocked'
and candidate_type like 'identity_observer_%'
group by candidate_type
order by cnt desc;There is no migration runner. Neither workflow applies SQL to Supabase — scraper.yml
only runs the ETL and nextjs.yml only builds.
- Brand-new database: run
schema.sqlonce, then apply every numbered migration once in filename order.schema.sqldeliberately refuses to run after the canonicalpeoplelayer exists. - Existing database: apply only the next unapplied numbered migration. Starting with
migration
0022, applied versions are recorded inpublic.schema_migrationsand checked by scraper preflight. psql: run each migration with--single-transactionunless that migration explicitly documents different handling. This keeps temporary tables alive for the whole file and prevents half-applied data changes. Migration0027is the explicit exception: useON_ERROR_STOP=1but omit--single-transactionso its committed cutover barrier can become visible before its second transaction drains old callers and atomically enables both gates. Migrations0028through0036use the normal single-transaction rule.0028is the private Senate source-review decision.0029installs the write-disabled Senate provenance contract and hard preflight-only barrier.0030verifies that exact disabled state, installs the guarded wrapper, and enables the database gates.0031installs the narrow public, person-aware official-vote read RPC without granting direct access to the private fact tables.0032preserves that API while repairing the query plan so indexed person predicates are applied before legacy vote normalization.0033keeps that plan while retaining ambiguous GovTrack signature collisions unless exactly one official roll call matches.0034corrects the Congress.gov API catalog coordinates and records the bounded metadata-shadow contract; it adds no writer and deliberately leaves scraper preflight on0033.0035approves only the reviewed exact-detail use, creates private source-record-backed measure facts and exact official-roll-call links, installs one bounded atomic service-role writer, and advances scraper preflight to0035. It retains no raw API JSON and creates no browser read path.0036validates and records the successful manual canary and private database audit, advances scraper preflight, and permits the nightly workflow to select the same bounded writer. Runtime and manual-input defaults remain disabled, and unknown events fail closed.
Do not replay the full historical migration directory against an upgraded database.
Some migrations contain guarded data decisions and review-state transitions, not just
idempotent DDL. In particular, replaying 0011 after the approved 0015 identity cleanup
can reconstruct stale mappings, while replaying the original 0016 seeds can overwrite
maintainer review state. Use a new forward repair migration instead of editing live history.
Symptom of un-applied migrations: the scraper log fills with PGRST204 errors like
Could not find the 'district' column of 'politicians' in the schema cache, and profile pages show stale/empty data. Recover in this order:
- Apply the pending migrations (e.g.
0002–0007) against the live database. If a freshly-added column still isn't found right after, reload PostgREST's schema cache:NOTIFY pgrst, 'reload schema';- Run the Nightly ETL Scraper and confirm it succeeds — look for
[+] Updated/Inserted Hublines and no PGRST204 errors. This is the step that actually writes data; a drifted run writes nothing.- Re-run Deploy Next.js to GitHub Pages so the profile pages re-bake. A successful scraper run triggers this automatically, but you can run it manually too.
Do not skip straight from step 1 to step 3: the deploy only bakes whatever is already in the database, so redeploying before a successful scrape just re-freezes the same stale/empty data.
See CONTRIBUTING.md. Key rules for this repo:
- No paid APIs — all scraper sources must be free-tier or open source.
- Label unconfirmed data — third-party/unverified data must be visibly marked in the UI (the "Visual Firewall").
- Classification is data-first — the directory should prefer normalized
government_level,government_branch,office_type, andjurisdictioncolumns. The legacy keyword classifier is only a compatibility fallback; if you edit it, State/Federal rules must still sit above generic Local rules. - Sign off every commit (DCO) — append
--signoff(-s) to everygit commit. - New work branches off
mainand lands via PR; maintainers handle merges.
See AUTHORS for contributors. This repository is licensed under the
GNU General Public License v3.0. See LICENSE.