Multiple municipalities, data extraction and getting started with automation

Laiba Zai is a 2026 RJI Student Innovation Fellow partnered with Times Union. The RJI Student Innovation Fellows will be sharing their innovative work throughout the summer in Innovation in Focus.

Tracking local legislation allows the public to stay up-to-date on policy issues and empowers them to influence civic decisions in their communities. It also acts as a reference point for newsroom reporters to hold local officials accountable.

When we set out to build a local legislation tracker for New York’s Capital Region, we realized something pretty early on: local legislation is messy. Unlike state legislation that is uniformly applied, local legislation comprises a tangled web of regulations for a resident that finds themself living in a county, city, and town simultaneously; each with its own set of rules.

Times Union previously built a state legislative tracker based on data from LegiScan, a free legislative tracking service that provides real-time monitoring of state-level bills, Congress and the D.C Council. They bought rights to its API, built an easy-to-access interface with the help of its dev ops team, and were able to bring a state legislative tracker to life with minor bumps.

Unfortunately for local legislation, such a service does not exist. To make things even harder, each municipality’s government in New York’s Capital Region does things differently and often does not have the same order of government operation. 

For example, Albany County has a 39-member County Legislature, with each member representing a district. Saratoga County, despite being the same level of municipality, is governed by a 23-member Board of Supervisors that represents its 21 towns and two cities, with seats allocated based on population.

Using a base in Airtable, we built the new tracker to bring some order to this process. Here’s how we did it, and how you can build one, too: 

Step 1: Audit your legislative data

The first step is always research. We took some time to understand how local government in New York works. What’s the difference between a town and a city? What distinguishes a village and a hamlet? 

Then we inventoried 13 municipalities across all levels. This meant visiting each municipality’s website, reading through the meeting minutes they upload, finding out the frequency of their legislature meetings, how often they publish minutes, and the format of documents available, among other items. 

We maintained this data in an extensive spreadsheet, noting the extent of data extraction challenges we faced, which included the absence of meeting minutes in some cases and navigation difficulties in others.

Here’s a template of the audit table we used.

Step 2: Pick your pilot

After auditing data and checking for the feasibility of a tracker based on the information available, we selected four pilot municipalities. We based our decision on answers to the following questions:

• How detailed was the agenda page and how useful were the items listed?
• How easy was the extraction of data through meeting minutes?
• How clear were action outcomes in the meeting minutes?
• Was text for legislation easily available and downloadable?

Our pilots were the four counties of the Capital Region, i.e. Albany, Rensselaer, Schenectady, and Saratoga.

Step 3: Convene with reporters and decide categories

Next, we sat down with Times Union reporters who exclusively covered our pilot counties and asked them:

• What information do we want to capture every time we record an action?
• What agenda items need to be focused on?
• What topic fields are necessary? 
• Do resolutions and ordinances both matter? 

Based on their answers, we drafted a list of 15 policy categories under which legislation could be grouped. These were as follows:

• Budget & Finance
• Economic Development
• Housing & Land Use
• Transportation & Mobility
• Infrastructure & Facilities
• Environment & Sustainability
• Public Safety
• Health Public
• Human Services
• Aging & Senior Services
• Governance & Administration
• Personnel & Labor
• Education & Libraries
• Agriculture & Rural Affairs
• Intergovernmental Affairs

We also created topic tags, broadly under each policy category, so people can have an easier time looking for specific legislation. These can be found here.

To tag legislation correctly, we came up with a set of three rules:

1. Every item gets exactly one policy category.
2. 1-5 topic tags can be assigned to each item.
3. It is important for the policy outcome to be tagged, not the actual department or sponsor of legislation. Department tags must only be used when the department itself is central.

Step 4: Draft Airtable base

We then used Airtable to draft a base that is split into five tables:

• A basic info table that covers each municipality’s name, type, region, website link, and legislature clerk details.
• A meetings table that keeps track of the type of meeting, its date, location, short description, and the legislation passed in it.
• A table for legislators in every municipality that indicates their party affiliations, the legislation they’ve sponsored, the municipality they belong to, and their term period.
• A table for resolutions that includes a title, number, summary, type, status, vote result, policy category, topic tags, related legislation, involved committees, and a link to their original text. 
• A table for laws/ordinances similar to the resolutions sheet, with the addition of a date of effect.

Hearst Newspapers provides a paid Airtable account for the Times Union that was used to create the Airtable base. However, the same base can be drafted with a free account or on a similar app like Google Sheets.

We chose Airtable because it provides the option to link similar records if they appear in multiple tables within the same base, making it easier to automatically update fields.

Step 5: Time to test 

We collected data from the last three months of meetings in the pilot municipalities, and entered it into the corresponding sections in the Airtable base, manually going through each county’s meeting minutes and published meeting recordings.

In the case of upstate New York, local laws are introduced through a written document, after which a resolution is passed to schedule and notify a public hearing. During or immediately after the public hearing, the board can vote to adopt the Local Law that was the subject of the hearing.

We found that each county passes a larger volume of resolutions in each meeting, compared to laws. In order to log a law in the base, one must track its progression from introduction to the public hearing, an eventual vote that leads to its adoption or rejection, and its filing with the New York State Department of State. 

It is also important to note that we tracked both laws and resolutions separately and included a ‘related legislation’ field in each table to keep a track of all resolutions that referred to local laws and vice versa.

Through this process, we checked for consistency of records across municipalities, any missing fields, and tracked the time it took to process each meeting.

Our next steps are:

• Building a repeatable pipeline that allows manual tracking of legislation.
• Testing the base with reporters to create an internal dashboard that highlights its usability based on what they click, what they can’t find, and what they ignore
• Identifying automation opportunities and capacity to make this into a public interface.

---

Cite this article

Zai, Laiba (2026, June 17). Building a local legislation tracker. Reynolds Journalism Institute. Retrieved from: https://rjionline.org/news/building-a-local-legislation-tracker/