scripts/distinct_legislators/schema.py

"""Schema definitions for distinct legislators output."""

import pyarrow as pa

# =============================================================================
# SOURCE CONFIGURATION
# =============================================================================

# HuggingFace dataset URL
HF_BASE_URL = "https://huggingface.co/datasets/Dustinhax/tyt/resolve/main"
VOTEVIEW_MEMBERS_URL = f"{HF_BASE_URL}/voteview/HSall_members.parquet"

# Allowed domains for source URLs (SQL injection mitigation)
ALLOWED_SOURCE_DOMAINS = [
    "huggingface.co",
]


def validate_source_url(url: str) -> bool:
    """Check if source URL is from an allowed domain.

    Args:
        url: The URL to validate

    Returns:
        True if URL is from an allowed domain
    """
    from urllib.parse import urlparse

    parsed = urlparse(url)
    return any(parsed.netloc.endswith(domain) for domain in ALLOWED_SOURCE_DOMAINS)


# Congress filter: 96th congress (1979-1980) and later
# Congress 96 started January 3, 1979
MIN_CONGRESS = 96

# Congress number to year mapping (approximate - congress starts in odd years)
# Congress N covers years (1787 + 2*N) to (1788 + 2*N)
# Example: Congress 96 = 1979-1980, Congress 119 = 2025-2026


def congress_to_years(congress: int) -> tuple[int, int]:
    """Convert congress number to start/end years."""
    start_year = 1787 + 2 * congress
    end_year = start_year + 1
    return (start_year, end_year)


# =============================================================================
# OUTPUT SCHEMA
# =============================================================================

DISTINCT_LEGISLATORS_SCHEMA = pa.schema(
    [
        # Primary identifier
        pa.field("bioguide_id", pa.string(), nullable=False),
        # Cross-reference identifier for DIME linkage
        pa.field("icpsr", pa.int32()),  # Links to DIME Recipients via ICPSR
        # Biographical info (most recent values)
        pa.field("bioname", pa.string()),
        pa.field("state_abbrev", pa.string()),
        pa.field("party_code", pa.float64()),  # 100=Democrat, 200=Republican
        # Congress sessions served
        pa.field("congresses_served", pa.list_(pa.int16())),
        pa.field("first_congress", pa.int16()),
        pa.field("last_congress", pa.int16()),
        # Ideology scores (most recent values)
        pa.field("nominate_dim1", pa.float64()),  # Economic left-right
        pa.field("nominate_dim2", pa.float64()),  # Social conservatism
    ]
)

DISTINCT_LEGISLATORS_COLUMNS = [
    "bioguide_id",
    "icpsr",
    "bioname",
    "state_abbrev",
    "party_code",
    "congresses_served",
    "first_congress",
    "last_congress",
    "nominate_dim1",
    "nominate_dim2",
]

# Columns used for validation checksums
KEY_COLUMNS = ["bioguide_id", "first_congress", "last_congress"]

# =============================================================================
# AGGREGATION SQL
# =============================================================================

AGGREGATION_QUERY = """
SELECT
    bioguide_id,
    LAST(icpsr ORDER BY congress)::INTEGER as icpsr,
    LAST(bioname ORDER BY congress) as bioname,
    LAST(state_abbrev ORDER BY congress) as state_abbrev,
    LAST(party_code ORDER BY congress) as party_code,
    LIST(congress ORDER BY congress) as congresses_served,
    MIN(congress)::SMALLINT as first_congress,
    MAX(congress)::SMALLINT as last_congress,
    LAST(nominate_dim1 ORDER BY congress) as nominate_dim1,
    LAST(nominate_dim2 ORDER BY congress) as nominate_dim2
FROM read_parquet('{source_url}')
WHERE congress >= {min_congress}
  AND bioguide_id IS NOT NULL
GROUP BY bioguide_id
ORDER BY bioguide_id
"""

# =============================================================================
# DATA INTERPRETATION NOTES
# =============================================================================

DATA_INTERPRETATION = """
## Data Interpretation Decisions

### Source Data
- **Source:** Voteview HSall_members.parquet
- **Original structure:** One row per legislator per congress session
- **Output structure:** One row per legislator (aggregated)

### Filtering
- **Congress filter:** >= 96 (1979-1980 onward)
- **Null filter:** bioguide_id IS NOT NULL (excludes ~17 records without bioguide)
  - These are typically Presidents or historical members without bioguide IDs

### Aggregation Rules
| Field | Aggregation | Rationale |
|-------|-------------|-----------|
| bioguide_id | GROUP BY | Primary key, unique per legislator |
| icpsr | LAST by congress | ICPSR identifier for DIME cross-reference |
| bioname | LAST by congress | Name format may change; use most recent |
| state_abbrev | LAST by congress | Legislators may change states (rare) |
| party_code | LAST by congress | Party affiliation may change over career |
| congresses_served | LIST ordered | Complete history of all sessions served |
| first_congress | MIN | Earliest congress served (career start) |
| last_congress | MAX | Latest congress served (current or end) |
| nominate_dim1 | LAST by congress | Ideology score from most recent session |
| nominate_dim2 | LAST by congress | Ideology score from most recent session |

### Party Codes
| Code | Party |
|------|-------|
| 100 | Democrat |
| 200 | Republican |
| 328 | Independent |
| Other | Historical parties (Whig, Federalist, etc.) |

### NOMINATE Scores
- **dim1:** Economic liberalism/conservatism (-1 to +1, negative=liberal)
- **dim2:** Social issues/civil rights (-1 to +1, interpretation varies by era)
- Scores are session-specific; we keep the most recent for simplicity

### Known Edge Cases
1. **Party switchers:** Uses most recent party (e.g., Arlen Specter shows Democrat)
2. **State changers:** Uses most recent state (rare, but possible)
3. **Gaps in service:** congresses_served array handles non-consecutive terms
4. **Presidents:** Excluded (no bioguide_id in Voteview data)
"""