CivicLens
An open dashboard for US federal legislative activity — bills, roll-call votes,
floor speeches, districts and candidates — assembled entirely from official
public-domain sources. It provides raw records with a link back to the source
for every fact, and does not rate, score, or evaluate legislators or
legislation.
Status: P2 complete and loaded (2026-08-19). Members, bills, actions,
sponsorships and roll calls collect from Congress.gov and senate.gov, and the
House backfill is finished: every roll call from 1990 to 2016 — 17,433 of
them, 7.85M individual casts — plus the 119th Congress from the daily cron.
All 18,348 have been cross-checked against Voteview: 17,909 agree and are
published, 247 disagree and are withheld for review, 192 have no comparable
counterpart and say so on the page. The interface is the thin P5 slice: a
dashboard and member profiles. See Where this stands.
CivicLens is a working name.
What is here
apps/web/ Next.js 16 (App Router, TypeScript) + Tailwind 4 + shadcn/ui
pipelines/etl/ Python 3.12 collectors (uv-managed), SQLAlchemy Core + psycopg3
packages/db/ dbmate SQL migrations — the single source of truth for the schema
infra/ local dev compose; notes on what is provisioned by hand
.github/workflows/ path-filtered CI, plus a gated manual migration job
The schema is the contract between the TypeScript and Python sides. packages/db
owns it; the app regenerates Drizzle types with drizzle-kit pull and the ETL
reflects the live database. Neither side declares the schema itself.
Requirements
| Tool | Version | Notes |
|---|
| Node | ≥ 20.9 (.nvmrc pins 20.18.0) | Next.js 16 minimum |
| pnpm | 10.x | corepack enable |
| uv | ≥ 0.5 | for pipelines/etl |
| Docker | any recent | local Postgres + PostGIS |
Getting started
# 1. install
pnpm install
(cd pipelines/etl && uv sync)
# 2. environment
cp .env.example .env # defaults point at the dev container
cp pipelines/etl/.env.example pipelines/etl/.env
# 3. database
docker compose -f infra/docker/docker-compose.dev.yml up -d
pnpm db:up # apply migrations
pnpm --filter @civiclens/web run db:pull # generate Drizzle types from the live DB
# 4. run
pnpm dev # http://localhost:3000
The dashboard (/) and member profiles (/members/[bioguide]) render real
collected data. The other ten routes are still placeholders — see
Where this stands.
Pointing the app at a different database
The app reads DATABASE_URL and nothing else, so the same build serves the
local Docker Postgres or Neon. To look at production data WITHOUT writing a
credential to disk, set it for that one command only:
DATABASE_URL="<neon pooled url>" pnpm --filter @civiclens/web dev
Use the pooled (-pooler) Neon host here: the app opens a connection per
request and PgBouncer is what keeps that from exhausting Postgres. The
unpooled host is for migrations and the ETL.
Nothing is written to .env by that command, and .env stays pointed at the
local container — which is the point. Neon credentials belong in GitHub
Actions secrets, not in a developer's working tree.
Collecting data
cd pipelines/etl
uv run civiclens-etl --help
uv run civiclens-etl members --congress 119 --limit 20
uv run civiclens-etl bills --congress 119 --limit 10
uv run civiclens-etl votes --congress 119 --chamber house --limit 5
--limit bounds a run, which is how to smoke-test without pulling a whole
Congress. Jobs are idempotent: re-running the same command upserts in place and
skips roll calls already stored.
The House backfill (1990–2016) — run this off-runner
cd pipelines/etl
# Smoke-test one year, five roll calls.
uv run civiclens-etl backfill --from-year 1990 --to-year 1990 --limit 5
# The real thing. ~26,000 roll calls; hours, not minutes.
uv run civiclens-etl backfill
This is the one job that must NOT run on a GitHub-hosted runner: a hosted job is
capped at 6 hours (Deployment-Architecture-Report §1b). Run it locally or on a
temporary VPS, supplying the target database for that one invocation rather than
writing it into .env:
DATABASE_URL="<neon unpooled url>" uv run civiclens-etl backfill
Use the unpooled Neon host — bulk upserts must not go through PgBouncer
transaction pooling.
It is restartable. Roll calls already stored are skipped before they are
fetched, and each year gets its own dataset_sync_state row, so an interrupted
run resumes with the same command and the database says how far it got:
select dataset, last_status, rows_upserted, message
from dataset_sync_state where dataset like 'house_backfill_%' order by dataset;
Speeches — the Congressional Record
cd pipelines/etl
# Incremental: everything GovInfo has MODIFIED in the last week.
uv run civiclens-etl speeches
uv run civiclens-etl speeches --since-days 30 --limit 5
The incremental job asks which packages changed, not which were published, and
that is the point: GPO revises the Record for a long time after a sitting — 35%
of the 119th Congress's packages were last modified more than 30 days after
their issue date, and the longest gap measured was 470 days. A
publication-date query would never see those corrections. collect-speeches.yml
runs a 7-day window nightly and a 400-day sweep on Sundays.
Backfilling one Congress — run this off-runner
# Smoke-test: two sittings.
uv run civiclens-etl backfill-speeches --congress 119 --limit 2
# The real thing. 351 packages, 52,265 granules, ~3.5 hours.
DATABASE_URL="<neon unpooled url>" uv run civiclens-etl backfill-speeches --congress 119
Same rule as the House backfill: one HTTP request per granule puts this past
what a 6-hour hosted runner should be asked to do, so it runs locally or on a
temporary VPS with the target database supplied for that one invocation.
It is restartable, and cheaply so. A stored granule whose retrieved_at is at
or after its package's upstream lastModified is left alone, so resuming costs
one metadata request per finished package and no text at all:
select dataset, last_status, rows_upserted, message
from dataset_sync_state where dataset like 'speech%';
--from-year/--to-year take the run a slice at a time; --refresh re-fetches
text that the freshness check would have skipped; --include-digest also stores
Daily Digest and front-matter granules, which are indexes and mastheads rather
than statements and are skipped by default.
The Record starts in 1994. Earlier years are the bound edition (CRECB),
which is packaged per volume-part rather than per day and is not collected —
see docs/P3-source-verification.md.
Cross-checking against Voteview
# Compare and report, writing nothing. Do this first after a backfill.
uv run civiclens-etl reconcile --report-only
# For real: publish what agrees, withhold and queue what does not.
uv run civiclens-etl reconcile
# One Congress, tallies only (skips a multi-megabyte download per Congress).
uv run civiclens-etl reconcile --congress 119 --skip-positions
The daily workflow reconciles the sitting Congress. A full-range pass is a
manual step after a backfill — it re-downloads one file per Congress, for data
that has not moved in thirty years.
Checks
pnpm lint && pnpm typecheck && pnpm build # web
cd pipelines/etl
uv run ruff check . && uv run ruff format --check . && uv run mypy
uv run pytest # unit + parser tests, no network
# integration tests additionally need a migrated database
createdb civiclens_test # or: docker exec civiclens-pg createdb -U postgres civiclens_test
CIVICLENS_TEST_DATABASE_URL=postgres://postgres:postgres@localhost:55432/civiclens_test \
uv run pytest
No test touches the network — every upstream response is served from fixtures
captured during P1 verification.
Where this stands
Milestones follow PRD-US-Political-Tracker-v1.md §14.
| Milestone | Status |
|---|
| P0 | Repo, CI, DB schema, ETL skeleton | Done |
| P1 | Members, bills, actions, votes (House 2017~, Senate) | Done, green on GitHub Actions |
| P2 | Clerk XML backfill 1990–2016 + Voteview reconciliation | Done and loaded. 17,433 roll calls, 7,849,148 casts, 1.31 GiB; reconciled over all 18,348 stored roll calls |
| P3 | GovInfo Congressional Record speeches + full-text search | Done and loaded. 49,171 granules, 40.3M words, 333 MB; 57.0% attributed across 546 members |
| P4 | Census geocoding, district boundaries, MapLibre, FEC candidates | Not started |
| P5 | Dashboard, profiles, search, rankings — the real interface | Dashboard + member profile done; 10 routes still placeholders |
| P6 | Consistency, freshness, observability, accessibility | Not started |
What P1 delivered
Live collection from Congress.gov into the database, verified by running it —
see docs/P1-source-verification.md for the
endpoint-by-endpoint evidence.
- Members →
member, term. Two-pass, because the roster endpoint gives
full state names and no congress numbers; only member detail has both.
- Bills →
bill, bill_action, sponsorship, committee, with HTML
summaries flattened into the search vector.
- House roll calls →
vote, vote_cast, partition-routed by Congress.
- Senate roll calls → parser and loader complete, fixture-verified end to
end. Live access is blocked from the development network (below).
- Rate-limit awareness read from the response header, bounded retry with
backoff,
provenance on every fact, and dataset_sync_state freshness.
Verified on GitHub Actions as well as locally — ci-web, ci-etl and ci-db
are all green on ubuntu-latest, with ci-etl running the same 76 tests and
ci-db applying both migrations to a fresh PostGIS container, rolling back and
re-applying.
Verified against a real dev Postgres, not mocks:
| Check | Result |
|---|
| Partition routing | 873 casts → vote_cast_c118, 1,291 → vote_cast_c119 |
| Tally integrity | reported yea/nay/not-voting matches counted vote_cast rows on all 5 roll calls |
| Idempotency | re-running every job left all table counts unchanged |
| Provenance | 590 rows, 590 distinct checksums, 0 leaking credentials |
| R2 absent | logs r2.not_configured, skips the snapshot, keeps collecting |
Three bugs surfaced by doing this rather than by reading the schema:
bill_action's natural key was wrong — H.R. 3746 publishes one referral
14 times, once per committee, and floor debates repeat within a day. The P0
key collapsed them and aborted the upsert. Fixed in migration 0002.provenance's natural key never matched — a nullable field meant
ON CONFLICT never fired, so audit rows would duplicate on every re-run.
Rebuilt with NULLS NOT DISTINCT in the same migration.- The API key leaked into
source_url — a column published to users as a
"view original source" link. Now redacted before storing or logging, with
regression tests.
Resolved: Senate access works from CI, with the honest User-Agent
www.senate.gov sits behind Akamai and returns 403 to this project's
User-Agent from the development network, at every path — which is why P1 could
only fixture-verify the Senate collector. Congress.gov has no Senate vote
endpoint (/senate-vote 404s), so senate.gov is the only official source.
The verify-senate-live workflow settled it. Running the same collector code
with the same shipped default User-Agent on a GitHub Actions ubuntu-latest
runner:
User-Agent in use: 'CivicLens/0.1 (open civic data; +https://github.com/)'
MENU 200 153,178 bytes parsed 231 roll calls
VOTE 200 29,769 bytes 119-2 roll 231 — 'Cloture on the Motion to Proceed Rejected' (52-46, 3/5 required)
CASTS resolved 100, unresolved 0 positions: {'Yea': 52, 'Nay': 46, 'NotVoting': 2}
All 100 senators resolved through the LIS→Bioguide crosswalk with none left
over, and the counted positions match the roll call's own 52-46 tally.
So the block is network-scoped, not User-Agent-scoped. No User-Agent
spoofing is needed, and none was added — SENATE_USER_AGENT still exists as an
override but the honest default is what works. Scheduled Senate collection
therefore runs on GitHub Actions, not from a developer machine.
verify-senate-live.yml stays as a manual (workflow_dispatch) reachability
probe; it writes nothing to a database. The recurring collection workflow is
not built yet — when it is, it should schedule the existing P1 jobs
(civiclens-etl votes --chamber senate) on cron rather than duplicate any of
this logic.
What P3 delivered
The Congressional Record, collected from GovInfo at granule level — one row per
statement, which is both the natural key and the unit search has to return.
Evidence for every claim below is in
docs/P3-source-verification.md.
- Granules →
speech, speech_speaker, with the section
(House / Senate / Extensions of Remarks) taken from granuleClass and the
chamber from MODS.
- Full-text search at
/speeches, on the tsvector column that has been in
the schema since migration 0001.
- The profile Speeches tab, populated.
Four things the probe changed about the plan:
- The speaker resolver has no name matching, because CREC never needs one.
The P0 stub expected older granules to carry only a printed name. Every
<congMember> in the daily Record carries a bioGuideId, back to the
collection's 1994 start. That deletes the component P2 found hardest to get
right.
- A granule can have several speakers — 7.2% do, and a colloquy can name
nine.
speech.bioguide_id is one column, so migration 0005 adds
speech_speaker and the profile reads the join. The column now means "the
speaker when the granule named exactly one", and never holds a guess.
- 47% of the Record is nobody's statement. Prayers, the Pledge, the reading
of the Journal, adjournments, and 160 Constitutional Authority Statements per
five sitting days. They are stored with a NULL speaker, not dropped, and the
UI says "No speaker named in the record" rather than leaving a blank.
- The Record starts in 1994, not "the 1990s". Earlier years are the bound
edition, packaged per volume-part, and are not collected.
Scope, decided from the measurement rather than by assumption: the 119th
Congress only. Loaded on 2026-08-19/20: 49,171 granules, 40.3M words,
333 MB, 57.0% attributed to a named member across 546 of them, in about six
hours. The full 1994– history extrapolates to roughly 15× that, which is a
storage decision to take separately (PRD OQ-8);
civiclens-etl backfill-speeches --congress N already accepts any Congress.
One operational lesson generalises beyond P3: Neon closes an
idle-in-transaction session after five minutes AND suspends an idle compute,
so a multi-hour job must never hold a transaction across network I/O, and must
retry its writes without re-running the fetch that preceded them. Finding 10 in
the verification doc has the detail; the P4 backfills will want the same shape.
What P1 deliberately did not do
- No GovInfo, FEC or Census calls — P3/P4. Those collectors remained stubs
and their keys were left untouched. (GovInfo is now implemented; see P3.)
- No Clerk XML backfill — P2. Note the gap is now 1990–2016, not 1990–2022:
the House Votes beta turned out to serve the 115th Congress onward, not the
118th.
- No reconciliation. Every
vote was written is_published = false and
nothing ever set it true. P2 settled which reading of FC-3 that was meant to
implement — publish unless contradicted — in migration 0004.
- No real UI. Routes are still placeholders; the interface is P5.
What the interface shows today
A deliberately thin slice of P5 — two pages against real data, rather than a
half-built version of all twelve:
/ — freshness line, recently passed bills, recent roll calls with
tallies, recent legislative actions, and a members sample./members/[bioguide] — unified profile header with a neutral party chip,
and tabs for overview, sponsorship, voting history, speeches and committees.
Unknown Bioguide IDs 404./speeches — full-text search of the Congressional Record, with the
operators Postgres websearch_to_tsquery supports (quoted phrase, or,
-exclusion), keyword-highlighted snippets, and speaker cards linking back
to profiles.
Two things it deliberately does not do:
- No participation percentage. Collection covers a bounded slice of roll
calls, so a rate computed against that denominator would understate every
member. Raw counts are shown instead, with the reason stated on the page.
- No score, rating or ranking of any kind (PRD N1/FC-4).
Sections with no data yet — committee membership — say so explicitly rather
than being hidden, so "not collected" can never read as "nothing to report".
The Speeches tab now distinguishes two cases that are not the same thing:
nothing collected at all, versus collected and this member does not appear in
the range.
To continue
- The remaining nine routes.
- A review queue UI for the 398 open reconciliation flags across 247 roll
calls. They are currently readable only in
vote_reconciliation_flag, and
139 of them are one member — see finding 16 in
docs/P2-source-verification.md.
- The
/speeches facets, typed result tabs and per-statement permalinks the
UIUX report specifies — P3 shipped the thin version (search box, ranked
granule-level results, speaker cards).
Confirmed decisions
Settled before implementation; the open questions in PRD §17 were resolved as:
| Decision |
|---|
| Product name | CivicLens (working name) |
| House vote backfill | From 1990, via Clerk XML. P1 finding: the Congress.gov beta covers the 115th (2017) onward, so the Clerk gap is 1990–2016, not 1990–2022 |
| News tier | Deferred to v2; not built in this phase |
| Deployment | Stack A — Vercel + Neon Postgres/PostGIS + GitHub Actions + Cloudflare R2 |
| ETL orchestration | GitHub Actions cron (the historical backfill runs off-runner; a hosted job is capped at 6 hours) |
| Migrations | dbmate — plain SQL, minimal dependencies |
Measured, not assumed. Congress.gov documents a 5,000 req/hour limit; the
live response header reports X-Ratelimit-Limit: 20000. The client hard-codes
neither and reads X-Ratelimit-Remaining at runtime. Where a live service
contradicts the design documents, the live service wins — the differences are
listed in docs/P1-source-verification.md.
Design documents
These are the confirmed specification. Implementation follows them; they are
not re-litigated in code review.
Where a live service contradicts these documents, the live service wins and the
difference is recorded in
docs/P1-source-verification.md.
The rules this project is built around
Taken from the PRD, and enforced in the schema and the code rather than left to
editorial discipline:
- Official primary sources are the baseline. Congress.gov, senate.gov,
clerk.house.gov, GovInfo, FEC, Census. News never creates a fact.
- Everything is traceable. Every fact table carries
source_url and
retrieved_at; raw payloads are snapshotted to R2 with a provenance row.
- No interpretation. No ideology scores, no ratings, no "voted against the
intent of" labels. Counts, statuses, source text, links. Voteview is used to
cross-check tallies, and its NOMINATE columns are explicitly excluded.
- Disagreement means silence — but silence is not disagreement. When
sources disagree, the value is flagged for review and withheld rather than
shown with a caveat. When the independent source simply has not caught up —
Voteview republishes weeks behind the chamber, and never indexes quorum
calls at all — the official record is still shown, captioned "not yet
cross-checked". Settled in P2; the argument is in
packages/db/migrations/0004_fc3_publish_unless_contradicted.sql.
- Coverage limits are stated, not implied. FEC misses unregistered minor
candidates; the Congressional Record holds floor statements only.
