Losing a 5-year-long Illinois FOIA lawsuit for database schemas
March 2, 2025 —
Matt Chapman
Thomas Ptacek, a friend and expert witness in this lawsuit summed it up best in the court’s hallway while walking within three feet of opposing counsel: “This is fucking stupid”.
His companion post explains why.
Intro
Working with the City of Chicago’s parking ticket data—which I’ve received through FOIA—has always been a pain, especially in terms of knowing what exactly to request. In August 2018, I attempted to generally solve that problem, by submitting a request for the following:
An index of the tables and columns within each table of CANVAS.
Please include the column data type as well.
Per the CANVAS specification, the database in question is Oracle,
so the below SQL query will likely yield the records pursuant to this request:
select utc.column_name as colname, uo.object_name as tablename, utc.data_type as type
from user_objects uo
join user_tab_columns utc on uo.object_name = utc.table_name
where uo.object_type = 'TABLE'
—CANVAS Database Schema request on Muckrock
After the City initially denied the request with an argument that the records’ release would compromise network security, I took the denial to court where we initially won at-trial. The City then appealed, which we won as well. The case ultimately went up to the Illinois Supreme Court, where we lost unanimously. Better Government Association did a good explainer of the consequences of that loss, which boils down to a significant broadening of public agencies’ leeway to apply exemptions (i.e., withhold records or redact information) in response to FOIA requests.
Why Go Through All of This?
Under Illinois FOIA case law, if a request’s responsive documents—the set of records or information within the scope of that request—are stored in a queryable database, a query must be written. The requester is not required to write the query. The law even requires the agency to give you the data in a format of your choice (protip: “excel format”). When it works, it’s freaking great. Reality makes it difficult for a number of reasons, though:
- The FOIA officer will likely need to defer any querying to a colleague who is a “data person.”
- You can’t just ask a question:“FOIA does not compel the agency to provide answers to questions posed by the inquirer.”
- From the requester’s perspective, “Is X column requestable?” isn’t answerable without first attempting to request that column’s data.
- Requesting too many columns will likely lead to time-consuming back-and-forth, or a flat-out denial.
- Even though Illinois FOIA requires that a requester be given a chance to narrow their request, FOIA officers sometimes just stop responding during this “conferral” process.
To generally work through this problem, many folk will spend hours surfing through PDFs, reports, contracts, work products, etc, just to get a sense of what data might exist. This process is frustrating and often yields incomplete results. Let’s walk through my attempt with CANVAS.
First Attempts for Parking Ticket Data
My very first FOIA request was pretty narrow and sought the City’s towing data. The City was unable to get me what I requested for reasons I can’t seem to find, but it painted a picture that the Chicago doesn’t really track how cars are towed.
A month later, the project began shifting towards parking ticket data in addition to towing data, so I requested:
all raw towing and parking violation records available in the CANVAS system and any records that are from imported/interpolated from non-CANVAS systems.
This request was denied. The Department of Finance argued that the request would take anywhere between 280 to 400 hours to complete:
There are 55 million ticket records and 928K seizure records in CANVAS. As far as tow information, we only have knowledge of when a vehicle is towed due to a boot and released. The Department of Finance's application support vender estimates a minimum of 60-80 hours to design/develop/test and run the program.
In addition, since this is like a conversion to another system, we are not sure how long it would take to transfer so much data, a rough estimate would be an additional 80-120 hours to design a solution to get all the data on some kind of media for retrieval. Compliance with this request as currently written would take approximately 140-200 hours utilizing our vendor's resources to the exclusion of other work assignments.
A couple months and some phone calls later, I submitted a narrower request, which was successfully fulfilled, because I included an explicit list of fields. After honing the request language a a bit more, I was eventually able to get the data used in the analysis of my first blog post.
But Wait, Is There More?
Despite getting the limited information I had requested, I still wanted to expand my analysis, which required knowing what other information exists within CANVAS. So, I submitted another request for high-level and low-level system information:
1. Code for CANVAS
2. Logs of CANVAS and/or CANVAS log analysis.
3. Documentation for the operation of CANVAS, including how information is stored, what kind of database is used, along with any other technical documentation or generic documentation.
4. Any Wiki page related to CANVAS.
5. Any analysis of City parking ticket levels or trends.
The only records the City sent in response was a lackluster spreadsheet with just 100 rows, broken down by ward. I’m still not sure if this was the only analysis ever done at the time, but let’s get back to the meat of this blog post.
1, 2,
7 Comments
akudha
Why aren't all non-classified, non-sensitive public data actually public by default? The time, effort and money they spent fighting the FOIA lawsuit – wouldn't it just be easier and cheaper to just honor the request?
jessriedel
This sort of experience shows how broken the FOIA law is. If it’s in the public interest to make data available, it’s in the public interest to make it available to a person with imperfect understanding of the extreme details of government’s crappy IT systems.
Not sure exactly what the fix is, but one idea is to have a state-wide ombudsman-like office for facilitating FOIA requests. Currently each agency usually has its own small FOIA office, which naturally protects its own turf. A centralized office could
1. …be independent of the agencies from which info is being requested, avoiding conflicts of interest in denying/delaying requests
2. …have commitments to confidentiality so agencies couldn’t justify withholding contextual info (“what’s a better way to ask this question?”) from the ombudsman
3. …afford building up more technical and legal expertise than any single agency-specific office.
dang
Recent and related:
I Went to SQL Injection Court – https://news.ycombinator.com/item?id=43175628 – Feb 2025 (433 comments)
qingcharles
I reiterate my point from the comments of the companion post. OP lost even while being represented by some of the best civil rights lawyers in the country.
A lot of FOIA requests die because they receive push-back and the requestor lacks the resources to litigate it. You can do it yourself. FOIA litigation is usually not like OP's struggle over data types — it's usually just to get the court to smack the public body and tell them they are being lazy or overly strict and the court procedures are much simpler. (often the public body will fold as soon as you file)
Also, I wonder if @chaps can give his reasoning on going directly for litigation? In Illinois there is an alternate avenue where you can ask the AG to intervene. (I hate this route myself because it has become slow and toothless)
joshka
I wonder if starting with intentionally getting a parking fine in Chicago, followed by then submitting an FOIA about that fine and all related documents / data would have worked.
Edit: seems like that was the part of the origin story of this according to https://sockpuppet.org/blog/2025/02/09/fixing-illinois-foia/
joering2
> Please note that in late 2013, the City of Chicago launched a publically available Data
did they really say "publically" in their response? :)
exabrial
So dumb that the default behavior of the governments (State and Fed) is to withhold information.
100% onboard with shrinking the government.