oomh/Legislation-leaderboard

Data pipeline that scrapes the Kenya Parliament website, extracts bill tracker PDFs with MinerU, normalises sponsor names, and pushes structured bill and member tables to a Neon PostgreSQL database. # Legislation Leaderboard — Kenya Parliament Data Pipeline > *Who is really doing the legislating in Kenya? And how long does a bill actually take to become law?* > This project is the data engineering foundation that makes those questions answerable. --- ## Background & Overview A friend came to me with a simple question: **"Can we figure out which Kenyan MPs and Senators are actually sponsoring the most bills, and how long the whole journey from first reading to presidential assent takes?"** The data to answer that is publicly available on the Kenya Parliament website — bill trackers, member lists, committee memberships, and house leadership — but it lives across multiple pages and inside PDF documents that require a bit of muscle to extract cleanly. This repository is **Part 1 of 2**: the full scrape-to-database pipeline. It collects, cleans, and structures the data so that a separate analytics layer (Part 2) can answer the actual questions. **Specific goals of this phase:** - Scrape bill trackers for both the Senate and the National Assembly - Extract structured tables from the PDF committee membership documents using MinerU API - Normalise sponsor names and split multi-sponsor rows so every bill row has exactly one sponsor - Parse bill numbers, reading dates, and assent dates into typed fields - Push four clean, query-ready tables to a hosted Neon PostgreSQL database --- ## Data Structure All source data comes from the Kenya Parliament website. Here is what gets collected and how it ends up in the database: | Table | Source | Key columns | | --- | --- | --- | | `senate_bills` | Senate bill tracker PDF | `bill_name`, `sponsor`, `bill_house`, `bill_number`, `bill_year`, `dated`, `first_reading`, `assent_date` | | `assembly_bills` | National Assembly bill tracker PDF | same structure as above | | `leadership` | House leadership pages (Senate + NA) | `chamber`, `office`, `person` | | `members` | Member list pages (Senate + NA) | `chamber`, `name`, `coun …