📁 officials

Federally elected and federally appointed officeholders. members covers every person who has served in the House or Senate (Congress.gov API, the modernized successor to the Biographical Directory of the U.S. Congress). nominations covers PAS nominations — the live pipeline of Senate-confirmed appointed officials, from receipt through committee action to confirmation/withdrawal. federal_judges covers every Article III judge ever commissioned, including appointing president and confirmation history, from the Federal Judicial Center's own biographical database. committees and committee_membership cover House/Senate/Joint committee structure, jurisdiction, and current rosters — the one table pair in this schema sourced from a community-maintained aggregator (unitedstates/congress-legislators) rather than a government system of record directly, because no official source exposes committee membership or jurisdiction in structured form; see the header comment above.

9 datasets · 135 columns

members · table

Every House/Senate member per Congress they served, from the Congress.gov API per-Congress member list endpoint (the modernized Biographical Directory of the U.S. Congress). One row per (bioguide_id, congress) pair — a member serving multiple congresses has multiple rows. terms_json is the member's full chamber service history (all congresses, not just this row's), as a JSON array of {chamber, start_year, end_year} — list-level terms carry chamber and year span only, not per-Congress party/state/district (that finer grain requires an N+1 detail call per member and is not pulled in this table). Coverage is bounded by congress_range (see dimension_values comment) — not full 1789-present history by default. Cross-references geo.state_ref via state_name (full name; geo.states is a year-vintaged boundary snapshot and not a valid FK target — see constraints below). Territorial delegates (Puerto Rico, Guam, etc.) won't resolve against state_ref, which covers 50 states + DC only.

Column	Type	Null	Description
bioguide_id	string	no	Bioguide identifier (Biographical Directory of the U.S. Congress), e.g. L000174
congress	INTEGER	yes
name_last_first	string	yes	Member name in "Last, First Middle" order as returned by the API
state_name	string	yes	Full state name represented (join key into geo.states.name, not FIPS/abbr)
party_name	string	yes	Democratic, Republican, Independent, Independent Democrat, or Libertarian
district	integer	yes	House district (0 = at-large/single-district state or territory); null for senators
current_member	boolean	yes	Whether the member currently holds office, per Congress.gov's /v3/member/{bioguideId} detail endpoint (currentMember is not present on the list endpoint this table otherwise fetches from — see CongressMemberCurrentEnricher). Populated by an N+1 detail call per row; null means the enrichment call failed for that row (network/parse error), not that the member's status is unknown by design — see CongressMemberTransformer/CongressMemberCurrentEnricher javadoc.
terms_json	string	yes	JSON array of {chamber, start_year, end_year} — this member's FULL chamber service history across all congresses (not scoped to this row's congress column), in chronological order. Kept as JSON rather than a flattened table (per one-source-one-table) pending confirmation that Calcite UNNEST works cleanly over it.
image_url	string	yes	Official portrait URL (depiction.imageUrl)
member_url	string	yes	Referrer URL to the member's Congress.gov API detail record

nominations · table

Presidential nominations requiring Senate confirmation (PAS), from the Congress.gov API nomination endpoint — this IS the appointed-officials pipeline: received -> committee referral -> confirmed/withdrawn/returned. Verified against a live keyed response (2026-08-01): organization is a real top-level field, not an N+1-only one as originally assumed. description is populated for individually -named nominees but is empty for isList=true batch nominations (e.g. Foreign Service class promotions covering many officers at once) — the detail endpoint's isList flag itself is not fetched here, so an empty description is the only MVP signal that a row is a class nomination rather than a named appointee. latest_action_text is the best single field for current status (e.g. "Confirmed by the Senate by Voice Vote", "Nomination Withdrawn").

Column	Type	Null	Description
citation	string	no	Nomination citation, e.g. PN1064 or PN78-4 for a partitioned nomination
congress	INTEGER	yes
number	integer	no	Assigned nomination number within the Congress
part_number	string	yes	Partition suffix when a multi-nominee nomination is split by confirmation path
organization	string	yes	Organization the position sits in, e.g. "Department of State", "Foreign Service"
description	string	yes	Free-text nominee name and position, e.g. "Jane Doe, of Ohio, to be...". Empty for isList=true batch/class nominations — see table comment.
is_civilian	boolean	yes	nominationType.isCivilian. Live responses only ever showed isCivilian=true (isMilitary appears to be omitted rather than sent as false) — unverified against an actual military nomination.
is_privileged	boolean	yes	Whether entitled to expedited Senate procedure. Never observed populated at list level in live samples; kept nullable pending a confirmed-present example.
received_date	string	yes	Date received from the President (YYYY-MM-DD)
latest_action_date	string	yes	Date of the latest Senate/President action (YYYY-MM-DD)
latest_action_text	string	yes	Text of the latest action — the primary status signal (confirmed/withdrawn/ returned/pending committee action)
update_date	string	yes	Last update timestamp on Congress.gov

federal_judges · table

Every Article III federal judge ever commissioned, from the Federal Judicial Center's own Biographical Directory of Article III Federal Judges export. Verified against a live download (2026-08-01): the real CSV is 201 columns wide — up to 6 repeating groups of ~33 court/appointment columns each (a judge elevated District -> Circuit -> retired-then-recalled etc. gets multiple groups), plus separate "Other Federal Judicial Service", "School"/"Degree", and free-text career columns. This table maps a curated 12-of-33 fields per group (the appointment-identity and confirmation-timeline columns) and drops the education/career/committee-vote detail for MVP scope — not a parsing limitation, a deliberate cut. Ingested as one row per judge (one source, minimal reshape); a future federal_judgeships view could UNION ALL the 6 groups into one row per appointment if downstream queries need that grain.

Column	Type	Null	Description
nid	string	no	FJC judge name ID
jid	string	no	FJC judge ID (stable identifier, one per judge)
last_name	string	yes
first_name	string	yes
middle_name	string	yes
suffix	string	yes
birth_year	integer	yes
gender	string	yes
race_or_ethnicity	string	yes
court_type_1	string	yes	Court Type
court_name_1	string	yes	Court Name
appointment_title_1	string	yes	Appointment Title (e.g. Judge, Chief Judge)
appointing_president_1	string	yes	Appointing President
party_of_appointing_president_1	string	yes	Party of Appointing President
aba_rating_1	string	yes	American Bar Association rating at nomination
nomination_date_1	string	yes	Nomination Date (YYYY-MM-DD)
confirmation_date_1	string	yes	Confirmation Date (YYYY-MM-DD)
commission_date_1	string	yes	Commission Date (YYYY-MM-DD)
senior_status_date_1	string	yes	Senior Status Date (YYYY-MM-DD), if applicable
termination_date_1	string	yes	Termination Date (YYYY-MM-DD), if applicable
termination_reason_1	string	yes	Termination reason (e.g. Resignation, Death, Elevation)
court_type_2	string	yes
court_name_2	string	yes
appointment_title_2	string	yes
appointing_president_2	string	yes
party_of_appointing_president_2	string	yes
aba_rating_2	string	yes
nomination_date_2	string	yes
confirmation_date_2	string	yes
commission_date_2	string	yes
senior_status_date_2	string	yes
termination_date_2	string	yes
termination_reason_2	string	yes
court_type_3	string	yes
court_name_3	string	yes
appointment_title_3	string	yes
appointing_president_3	string	yes
party_of_appointing_president_3	string	yes
aba_rating_3	string	yes
nomination_date_3	string	yes
confirmation_date_3	string	yes
commission_date_3	string	yes
senior_status_date_3	string	yes
termination_date_3	string	yes
termination_reason_3	string	yes
court_type_4	string	yes
court_name_4	string	yes
appointment_title_4	string	yes
appointing_president_4	string	yes
party_of_appointing_president_4	string	yes
aba_rating_4	string	yes
nomination_date_4	string	yes
confirmation_date_4	string	yes
commission_date_4	string	yes
senior_status_date_4	string	yes
termination_date_4	string	yes
termination_reason_4	string	yes
court_type_5	string	yes
court_name_5	string	yes
appointment_title_5	string	yes
appointing_president_5	string	yes
party_of_appointing_president_5	string	yes
aba_rating_5	string	yes
nomination_date_5	string	yes
confirmation_date_5	string	yes
commission_date_5	string	yes
senior_status_date_5	string	yes
termination_date_5	string	yes
termination_reason_5	string	yes
court_type_6	string	yes
court_name_6	string	yes
appointment_title_6	string	yes
appointing_president_6	string	yes
party_of_appointing_president_6	string	yes
aba_rating_6	string	yes
nomination_date_6	string	yes
confirmation_date_6	string	yes
commission_date_6	string	yes
senior_status_date_6	string	yes
termination_date_6	string	yes
termination_reason_6	string	yes

committees · table

House, Senate, and Joint standing/select committees and their subcommittees, from the unitedstates/congress-legislators project's committees-current.yaml — a CC0-licensed, near-continuously-updated mirror of official House/Senate committee pages. The one community-aggregator source in this schema: verified live that the Congress.gov API's /v3/committee endpoint carries no jurisdiction text and no member roster (see the schema header). One row per full committee and one row per subcommittee, linked via parent_committee_id; committee_id is the join key into committee_membership.

Column	Type	Null	Description
committee_id	string	no	thomas_id (full committee) or parent thomas_id + subcommittee thomas_id (subcommittee, e.g. HSAG + 15 = HSAG15) — PK, and the join key into committee_membership.
chamber	string	yes	house, senate, or joint
name	string	yes
parent_committee_id	string	yes	NULL for a full committee; the parent's committee_id for a subcommittee
is_subcommittee	boolean	yes
url	string	yes
minority_url	string	yes
jurisdiction	string	yes	Legislative jurisdiction, free text (populated for most House committees; sparse elsewhere)
jurisdiction_source	string	yes
address	string	yes
phone	string	yes
rss_url	string	yes

committee_membership · table

Current committee/subcommittee rosters, from the unitedstates/congress-legislators project's committee-membership-current.yaml. One row per (committee, member) assignment for the sitting Congress. committee_id joins to committees.committee_id; bioguide_id joins to members.bioguide_id — this file has no congress number of its own (it is always the current roster), so join against the member's most recent congress rather than a specific one (see current_members).

Column	Type	Null	Description
committee_id	string	no
bioguide_id	string	yes
member_name	string	yes
party	string	yes	majority or minority
rank	integer	yes	Seniority rank within the committee for this party (1 = chair/ranking member)
title	string	yes	e.g. Chairman, Ranking Member; null for other members

electoral_college_votes · table

Electoral College vote allocation by state, for President and Vice President, from the National Archives (NARA) official per-election results page (archives.gov/electoral-college/{year}) — the government's own record of the Electoral College outcome, not a poll or projection. One row per (year, state, office, candidate). Verified stable across 200 years of live pages (1824, 1876, 1960, 1980, 2000, 2020, 2024): each page has a state-by-state table whose "For President"/"For Vice-President" header groups carry a colspan equal to however many candidates received at least one elector that year — parsed dynamically, not a fixed column count. candidate_party is populated only for the winner and the top losing candidate (the only two NARA names on the page's own summary block); other candidates' party is null — NARA's per-election page does not state it for anyone else, including write-in/faithless-elector recipients. Coverage defaults to 1976-2024 (presidential_year_range) — see that dimension's comment for why 1789 isn't the floor. Cross-references geo.state_ref via state_name (same caveat as members: DC has 3 electoral votes since the 23rd Amendment but is not itself a state, so 3-elector DC rows will not resolve against a strict state_ref join in years before 1961 if the range is widened).

Column	Type	Null	Description
year	INTEGER	yes
state_name	string	no	Full state name as published by NARA (e.g. "California"); join key into geo.state_ref.state_name
state_electoral_votes	integer	yes	Total electoral votes allocated to this state for this election
office	string	no	PRESIDENT or VICE_PRESIDENT
candidate_name	string	no	Candidate name as published by NARA (format varies by era, e.g. "Kamala D. Harris")
candidate_home_state	string	yes	Candidate's home state as published ("... of California"); null when NARA's text doesn't follow that pattern
candidate_party	string	yes	Party abbreviation, populated ONLY for the page's designated winner and main opponent (the only two candidates NARA's summary block names with a party) — null for every other candidate on the page, including anyone who received faithless-elector or write-in votes.
electoral_votes_won	integer	yes	Electoral votes this candidate won in this state ("-" in the source becomes 0, not null)

presidential_election_results · table

Popular vote totals by state and candidate for the presidential general election, from the FEC's official post-election results workbook (fec.gov, "{year} Presidential General Election Results", published as XLSX). Richer than electoral_college_votes for the years it covers — actual vote counts, not just elector allocation — but coverage is NOT full-history: FEC only publishes this specific XLSX format for recent cycles (confirmed: 2024; unconfirmed further back — the source page lists only PDF, not XLSX, for cycles further back at the time this table was built, 2026-08-02). No predictable per-year URL exists (each year's file lives behind an opaque numeric document ID), so this transformer discovers the URL per year by scraping FEC's static results-and-voting-information listing page rather than templating a URL directly — a year with no matching link on that page yields zero rows for this table, not an error; check the ETL log for "no results link found" to distinguish "not published this way" from a real failure. One row per (year, state, candidate). Cross-references geo.state_ref via state_name.

Column	Type	Null	Description
year	INTEGER	yes
state_name	string	no	Full state name (converted from the source's 2-letter postal code); join key into geo.state_ref.state_name
state_electoral_votes	integer	yes	Total electoral votes allocated to this state (also carried on electoral_college_votes)
candidate_name	string	no	Candidate surname/label as published in the workbook's column header (format varies by year)
popular_votes	long	yes	Popular votes received by this candidate in this state
state_total_votes	long	yes	Total votes cast for President in this state, all candidates + write-ins

current_members · view

Members Congress.gov's per-member detail endpoint reports as currently holding office (members.current_member = true, populated by CongressMemberCurrentEnricher's N+1 detail call — see that column's comment). Restricted to the latest congress present in members (bounded by GOVDATA_START_CONGRESS/GOVDATA_END_CONGRESS — not necessarily the literal current Congress if those knobs haven't been bumped for a new session) so a member's row from an older congress they also served doesn't duplicate their current one. voting_member is a structural derivation, not a Congress.gov field: the House's 6 territorial/DC seats (American Samoa, DC, Guam, N. Mariana Islands, Puerto Rico's Resident Commissioner, Virgin Islands) are non-voting delegate seats, and no territory or DC has Senate representation at all — so state_name being one of the 50 states is both necessary and sufficient for floor voting rights, for House and Senate rows alike. The 6-name exclusion list is fixed by the Constitution/statute, not annually vintaged data, so it is inlined here rather than sourced from geo (whose state_ref is a 50-states+DC list that would still need this same non-DC-state test, and members' territorial state_names don't resolve against it at all — see the members table comment).

View — columns are resolved by the query engine at runtime.

state_political_index · view

Composite Political Index (CPI) per state PER CONGRESS: 0.50I_Pres + 0.30I_Senate + 0.20I_House, on a -100 (max Republican) to +100 (max Democratic) scale. One row per (congress, state) — a full historical time series, not just the latest congress. congress/congress_start_year identify which congress each row describes; join congress_start_year to any other schema's year for a same-period comparison (House/Senate composition is constant across both years of a congress, so a caller wanting an exact calendar year should pick whichever congress covers it, not assume a 1:1 congress-to-year mapping). Converted from a single "current only" snapshot to this per-congress series 2026-08-23: the prior version hardcoded congress = (SELECT MAX(congress) FROM members) for House/Senate and always used the two most recent presidential cycles globally. Both members (bounded by its own congress_range, not full 1789-present — see that table's own comment) and electoral_college_votes (1976-2024) already carried everything needed for a real series over that bounded range — the only change is computing every congress's index using the data available AS OF that congress's start year, instead of collapsing to "now". This view's own coverage is therefore no wider than members' configured range: a congress older than members' floor produces no row here either. The presidential component for congress N is resolved via congress_start_year = 1787 + 2congress (inverse of OfficialsSchemaFactory.deriveCongressRange's N = (year-1789)/2 + 1), then taking the two most recent electoral_college_votes years <= that start year — the same 2-cycle blend as before, just anchored per-congress instead of to today. DEVIATION FROM THE ORIGINAL SPEC (deliberate, decided 2026-08-02): I_Pres was specified as a two-party POPULAR vote share margin, blended 2:1 across the two most recent presidential cycles. presidential_election_results only has 2024 (FEC hasn't published this XLSX format further back yet) and carries no party column, so that could not be computed for two cycles today. Substituted a two-party ELECTORAL vote share margin from electoral_college_votes instead — it has full 1976-2024 history AND a real candidate_party column, so both cycles resolve with no fuzzy join. This is a different statistic (EV share, not popular-vote share) — do not treat i_pres as a popular-vote number. Known caveats, not silently absorbed: (1) candidate_party on electoral_college_votes is populated only for the page's winner/main-opponent — reliable as 'D'/'R' in modern two-party-dominant elections, but a state whose top-2 was NOT the two major parties would be excluded from the margin calc for that year, not misattributed. (2) party_name on members reflects Congress.gov's literal reported affiliation, NOT caucus alignment — an independent who caucuses with a party (e.g. a Sanders/King-style seat) is counted as neither s_d nor s_r, understating that state's I_Senate versus its practical governing-coalition lean. (3) i_pres degrades gracefully to M_t alone (via COALESCE) for a state with only one presidential cycle on record instead of two — verified against a synthetic single-cycle case, not just the two-cycle case. (4) H_D/H_R (and S_D/S_R) denominators are exactly as the original formula specified — vacant seats and non-D/R members are excluded from the denominator entirely, not counted as a third bucket. (5) I_Senate's /2 denominator assumes exactly 2 senators per state per congress; a mid-congress death/resignation plus appointed/special-election replacement (both rows present in members for that congress, e.g. California's 118th: Feinstein died in office, Butler was appointed to finish her term, alongside the two full-term senators) inflates s_d+s_r past 2 for that congress, which can push cpi/i_senate past the documented +-100 scale. Real turnover, not a bug — a single "current" snapshot could land on either side of a mid-congress swap and never surface this; a full series makes every such congress visible.

View — columns are resolved by the query engine at runtime.