Simon Willison's Weblog: political-hacking

XSLT on congress.gov

2025-08-19T20:40:50+00:00

Today I learned - via a proposal to remove mentions of XSLT from the HTML spec - that congress.gov uses XSLT to serve XML bills as XHTML - here's H. R. 3617 117th CONGRESS 1st Session for example.

View source on that page and it starts like this:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="billres.xsl"?>
<!DOCTYPE bill PUBLIC "-//US Congress//DTDs/bill.dtd//EN" "bill.dtd">
<bill bill-stage="Introduced-in-House" dms-id="H5BD50AB7712141319B352D46135AAC2B" public-private="public" key="H" bill-type="olc"> 
<metadata xmlns:dc="http://purl.org/dc/elements/1.1/">
<dublinCore>
<dc:title>117 HR 3617 IH: Marijuana Opportunity Reinvestment and Expungement Act of 2021</dc:title>
<dc:publisher>U.S. House of Representatives</dc:publisher>
<dc:date>2021-05-28</dc:date>
<dc:format>text/xml</dc:format>
<dc:language>EN</dc:language>
<dc:rights>Pursuant to Title 17 Section 105 of the United States Code, this file is not subject to copyright protection and is in the public domain.</dc:rights>
</dublinCore>
</metadata>
<form>
<distribution-code display="yes">I</distribution-code> 
<congress display="yes">117th CONGRESS</congress><session display="yes">1st Session</session> 
<legis-num display="yes">H. R. 3617</legis-num> 
<current-chamber>IN THE HOUSE OF REPRESENTATIVES</current-chamber>

Digging into those XSLT stylesheets leads to billres-details.xsl - gist copy here - which starts with a huge changelog comment with notes dating all the way back to 2004!

Tags: html, political-hacking, web-standards, xslt

18f.org

2025-03-02T09:24:37+00:00

18f.org

New site by members of 18F, the team within the US government that were doing some of the most effective work at improving government efficiency.

For over 11 years, 18F has been proudly serving you to make government technology work better. We are non-partisan civil servants. 18F has worked on hundreds of projects, all designed to make government technology not just efficient but effective, and to save money for American taxpayers.

However, all employees at 18F – a group that the Trump Administration GSA Technology Transformation Services Director called "the gold standard" of civic tech – were terminated today at midnight ET.

18F was doing exactly the type of work that DOGE claims to want – yet we were eliminated.

The entire team is now on "administrative leave" and locked out of their computers.

But these are not the kind of civil servants to abandon their mission without a fight:

We’re not done yet.

We’re still absorbing what has happened. We’re wrestling with what it will mean for ourselves and our families, as well as the impact on our partners and the American people.

But we came to the government to fix things. And we’re not done with this work yet.

More to come.

You can follow @team18f.bsky.social on Bluesky.

Tags: government, political-hacking, politics, bluesky

Private School Labeler on Bluesky

2024-11-22T17:44:34+00:00

Private School Labeler on Bluesky

I am utterly delighted by this subversive use of Bluesky's labels feature, which allows you to subscribe to a custom application that then adds visible labels to profiles.

The feature was designed for moderation, but this labeler subverts it by displaying labels on accounts belonging to British public figures showing which expensive private school they went to and what the current fees are for that school.

Here's what it looks like on an account - tapping the label brings up the information about the fees:

These labels are only visible to users who have deliberately subscribed to the labeler. Unsurprisingly, some of those labeled aren't too happy about it!

In response to a comment about attending on a scholarship, the label creator said:

I'm explicit with the labeller that scholarship pupils, grant pupils, etc, are still included - because it's the later effects that are useful context - students from these schools get a leg up and a degree of privilege, which contributes eg to the overrepresentation in British media/politics

On the one hand, there are clearly opportunities for abuse here. But given the opt-in nature of the labelers, this doesn't feel hugely different to someone creating a separate webpage full of information about Bluesky profiles.

I'm intrigued by the possibilities of labelers. There's a list of others on bluesky-labelers.io, including another brilliant hack: Bookmarks, which lets you "report" a post to the labeler and then displays those reported posts in a custom feed - providing a private bookmarks feature that Bluesky itself currently lacks.

Update: @us-gov-funding.bsky.social is the inevitable labeler for US politicians showing which companies and industries are their top donors, built by Andrew Lisowski (source code here) using data sourced from OpenScrets. Here's what it looks like on this post:

Tags: apis, moderation, political-hacking, politics, bluesky

Project: Civic Band - scraping and searching PDF meeting minutes from hundreds of municipalities

2024-11-16T22:14:01+00:00

I interviewed Philip James about Civic Band, his "slowly growing collection of databases of the minutes from civic governments". Philip demonstrated the site and talked through his pipeline for scraping and indexing meeting minutes from many different local government authorities around the USA.

We recorded this conversation as part of yesterday's Datasette Public Office Hours session.

Civic Band

Philip was inspired to start thinking more about local government after the 2016 US election. He realised that there was a huge amount of information about decisions made by local authorities tucked away in their meeting minutes,but that information was hidden away in thousands of PDF files across many different websites.

There was this massive backlog of basically every decision that had ever been made by one of these bodies. But it was almost impossible to discover because it lives in these systems where the method of exchange is a PDF.

Philip lives in Alameda, which makes its minutes available via this portal powered by Legistar. It turns out there are a small number of vendors that provide this kind of software tool, so once you've written a scraper for one it's likely to work for many others as well.

Here's the Civic Band portal for Alameda, powered by Datasette.

It's running the datasette-search-all plugin and has both tables configured for full-text search. Here's a search for housing:

The technical stack

The public Civic Band sites all run using Datasette in Docker Containers - one container per municipality. They're hosted on a single Hetzner machine.

The ingestion pipeline runs separately from the main hosting environment, using a Mac Mini on Philp's desk at home.

OCR works by breaking each PDF up into images and then running Tesseract OCR against them directly on the Mac Mini. This processes in the order of 10,000 or less new pages of documents a day.

Philip treats PDF as a normalization target, because the pipeline is designed around documents with pages of text. In the rare event that a municipality publishes documents in another format such as .docx he converts them to PDF before processing.

PNG images of the PDF pages are served via a CDN, and the OCRd text is written to SQLite database files - one per municipality. SQLite FTS provides full-text search.

Scale and storage

The entire project currently comes to about 265GB on disk. The PNGs of the pages use about 350GB of CDN storage.

Most of the individual SQLite databases are very small. The largest is for Maui County which is around 535MB because that county has professional stenographers taking detailed notes for every one of their meetings.

Each city adds only a few documents a week so growth is manageable even as the number of cities grows.

Future plans

We talked quite a bit about a goal to allow users to subscribe to updates that match specific search terms.

Philip has been building out a separate site called Civic Observer to address this need, which will store searches and then execute the periodically using the Datasette JSON API, with a Django app to record state to avoid sending the same alert more than once.

I've had a long term ambition to build some kind of saved search alerts plugin for Datasette generally, to allow users to subscribe to new results for arbitrary SQL queries. My sqlite-chronicle library is part or that effort - it uses SQLite triggers to maintain version numbers for individual rows in a table, allowing you to query just the rows that have been inserted or modified since the version number last time you ran the query.

Philip is keen to talk to anyone who is interested in using Civic Band or helping expand it to even more cities. You can find him on the Datasette Discord.

Tags: data-journalism, political-hacking, politics, sqlite, datasette, datasette-public-office-hours

Spevktator: OSINT analysis tool for VK

2022-09-05T20:48:20+00:00

Spevktator: OSINT analysis tool for VK

This is a really cool project that came out of a recent Bellingcat hackathon. Spevktator takes 67,000 posts from five popular Russian news channels on VK (a popular Russian social media platform) and makes them available in Datasette, along with automated translations to English, post sharing metrics and sentiment analysis scores. This README includes some detailed analysis of the data, plus a link to an Observable notebook that implements custom visualizations against queries run directly against the Datasette instance.

Tags: political-hacking, datasette, observable, bellingcat

Digitizing 55,000 pages of civic meetings

2022-08-22T16:26:04+00:00

Digitizing 55,000 pages of civic meetings

Philip James has been building public, searchable archives of city council meetings for various cities—Oakland and Alamedia so far—using my s3-ocr script to run Textract OCR against the PDFs of the minutes, and deploying them to Fly using Datasette. This is a really cool project, and very much the kind of thing I’ve been hoping to support with the tools I’ve been building.

Via @phildini

Tags: archiving, ocr, political-hacking, datasette, fly

Exploring the UK Register of Members Interests with SQL and Datasette

2018-04-25T15:49:39+00:00

Ever wondered which UK Members of Parliament get gifted the most helicopter rides? How about which MPs have been given Christmas hampers by the Sultan of Brunei? (David Cameron, William Hague and Michael Howard apparently). Here’s how to dig through the Register of Members Interests using SQL and Datasette.

mySociety have been building incredible civic participation applications like TheyWorkForYou and FixMyStreet for nearly 15 years now, and have accumulated all kinds of interesting data along the way.

They recently launched their own data portal at data.mysociety.org listing all of the information they have available. While exploring it I stumbled across their copy of the UK Register of Members Interests. Every UK Member of Parliament has to register their conflicts of interest and income sources, and mySociety have an ongoing project to parse that data into a more useful format.

It won’t surprise you to hear that I couldn’t resist turning their XML files into a SQLite database.

The result is register-of-members-interests.datasettes.com - a Datasette instance running against a SQLite database containing over 1.3 million line-items registered by 1,419 MPs over the course of 18 years.

Some fun queries

A few of my favourites so far:

Which MPs have taken the most donated helicopter rides
Which MPs have accepted gifts from the Surtan of Brunei
A better gifts query with more robust de-duping: here's gifts of hampers, watches and Glastonbury festival tickets
Which MPs own significant shares in Shell, Apple, or BP.
Who has reported the most overseas trips to Saudi Arabia? The top hit there was Daniel Kawczynski, and it turns out his Wikipedia page has a section dedicated to his relationship with the kindgom.
How much do MPs get paid for appearances on Have I Got News For You. The going rate seems to be £1,500 but you can find some interesting discrepancies if you exclude that value from the results.
Which MPs are responsible for the most total line items reported

Understanding the data model

Most of the action takes place in the items table, where each item is a line-item from an MP’s filing. You can search that table by keyword (see helicopter example above) or apply filters to it using the standard Datasette interface. You can also execute your own SQL directly against the database.

Each item is filed against a category. There appears to have been quite a bit of churn in the way that the categories are defined over the years, plus the data is pretty untidy - there are no less than 10 ways of spelling “Remunerated employment, office, profession etc.” for example!

There are also a LOT of duplicate items in the set - it appears that MPs frequently list the same item (a rental property for example) every time they fill out the register. SQL DISTINCT clauses can help filter through these, as seen in some of the above examples.

The data also has the concepts of both members and people. As far as I can tell people are distinct, but members may contain duplicates - presumably to represent MPs who have served more than one term in office. It looks like the member field stopped being populated in March 2015 so analysis is best performed against the people table.

Once concept I have introduced myself is the record_id. In the XML documents the items are often grouped together into a related collection, like this:

<regmem personid="uk.org.publicwhip/person/10001"
    memberid="uk.org.publicwhip/member/40289" membername="Diane Abbott" date="2014-07-14">
    <category type="2" name="Remunerated employment, office, profession etc">
        <item>Payments from MRL Public Sector Consultants, Pepple House, 8 Broad Street, Great Cambourne, Cambridge CB23 6HJ:</item>
        <item>26 November 2013, I received a fee of £1,000 for speaking at the 1st African Legislative Summit, National Assembly, Abuja, Nigeria.  Hours: 8 hrs. The cost of my flights, transfers and hotel accommodation in Abuja were also met; estimated value £5,000. <em>(Registered 3 December 2013)</em></item>
        <item>23 July 2013, I received a fee of £5,000 for appearing as a contestant on ITV&#8217;s &#8216;The Chase Celebrity &#8211; Series 3&#8217; television programme.  Address of payer:  ITV Studios Ltd, London Television Centre, Upper Ground, London SE1 9Lt.  Hours: 12 hrs.   <em>(Registered 23 July 2013)</em></item>
    </category>
</regmem>

While these items are presented as separate line items, their grouping carries meaning: the first line item here acts as a kind of heading to help provide context to the other items.

To model this in the simplest way possible, I’ve attempted to preserve the order of these groups using a pair of additional columns: the record_id and the sort_order. I construct the record_id using a collection of other fields - the idea is for it to be sortable, and for each line-item in the same grouping to have the same record_id:

record_id = "{date}-{category_id}-{person_id}-{record}".format(
    date=date,
    category_id=category_id,
    person_id=person_id.split("/")[
        -1
    ],
    record=record,
)

The resulting record_id might look like this: 2018-04-16-70b64e89-24878-0

To recreate that particular sequence of line-items, you can search for all items matching that record_id and then sort them by their sort_order. Here’s that record from Diane Abbott shown with its surrounding context.

How I built it

The short version: I downloaded all of the XML files and wrote a Python script which parsed them using ElementTree and inserted them into a SQLite database. I’ve put the code on GitHub.

A couple of fun tricks: firstly, I borrowed some code from csvs-to-sqlite to create the full-text search index and enable searching:

def create_and_populate_fts(conn):
    create_sql = """
        CREATE VIRTUAL TABLE "items_fts"
        USING {fts_version} (item, person_name, content="items")
    """.format(
        fts_version=best_fts_version()
    )
    conn.executescript(create_sql)
    conn.executescript(
        """
        INSERT INTO "items_fts" (rowid, item, person_name)
        SELECT items.rowid, items.item, people.name
        FROM items LEFT JOIN people ON items.person_id = people.id
    """
    )

The best_fts_version() function implements basic feature detection against SQLite by trying operations in an in-memory database.

Secondly, I ended up writing my own tiny utility function for inserting records into SQLite. SQLite has useful INSERT OR REPLACE INTO syntax which allows you to insert a record and will automatically update an existing record if there is a match on the primary key. This meant I could write this utility function and use it for all of my data inserts:

def insert_or_replace(conn, table, record):
    pairs = record.items()
    columns = [p[0] for p in pairs]
    params = [p[1] for p in pairs]
    sql = "INSERT OR REPLACE INTO {table} ({column_list}) VALUES ({value_list});".format(
        table=table,
        column_list=", ".join(columns),
        value_list=", ".join(["?" for p in params]),
    )
    conn.execute(sql, params)

# ...

insert_or_replace(
    db,
    "people",
    {
        "id": person_id,
        "name": regmem_el.attrib["membername"],
    },
)

What can you find?

I’ve really only scratched the surface of what’s in here with my initial queries. What can you find? Send me Datasette query links on Twitter with your discoveries!

Tags: mysociety, political-hacking, politics, projects, sqlite, xml, datasette

Video speech matching on TheyWorkForYou.com

2008-06-01T13:52:55+00:00

Video speech matching on TheyWorkForYou.com

Launched this morning at BarCamp London by Matthew Somerville—TheyWorkForYou now has video from BBC Parliament but they need your help matching it exactly to their transcripts from Hansard. Neat example of a game that helps process large amounts of data.

Tags: barcamplondon, barcamplondon4, government, matthew-somerville, mysociety, political-hacking, theyworkforyou, timestamping, video

WhatDoTheyKnow

2008-03-04T23:38:46+00:00

WhatDoTheyKnow

New from mySociety: a site for submitting and publically tracking Freedom of Information requests to the UK government.

Via mySociety's Freedom of Information site goes live

Tags: foi, government, mysociety, political-hacking, whatdotheyknow

They Work For You

2004-06-06T21:51:21+00:00

Today is/was (you never can tell with these wretched time zone differences) NotCon 2004, London's premiere low-cost, informal, one-day technology conference. Friday's MiniNTK promised the unveiling of a new project from the people behind FaxYourMP and PublicWhip and sure enough, here it is: TheyWorkForYou.com.

TheyWorkForYou.com is the finest example of a political hack I've ever seen. It's basically an ultra-user-friendly front-end to the Hansard public record of all speeches and debates in the houses of commons, with each MP getting their own page complete with a summary of their recent performance and member's interests and a list of their recently recorded parliamentary appearances.

The community features are pretty impressive as well: you can add comments to any speech made in any debate, and there's also a user-populated glossary which is automatically linked in to the speech transcripts.

But with all that, the most useful feature is probably the most subtle: you can subscribe to an RSS feed of your MP's appearances, meaning you can keep track of everything they say on your behalf. It's simple, powerful and a perfect example of the political hacker ethic at work.

Tags: mysociety, political-hacking, politics