FOIA Data Models for Everyone
How the pros track and organize their FOIA processes
Listen to two FOIA practitioners describe their request strategies and you’ll probably get two very different answers. I know because I’ve done it. As someone with not much of a personal FOIA strategy—besides “wait and hope”—I was surprised that journalists skilled at prying obscure records from the government have wildly different approaches.
These differences in how to engage with the FOIA process can cover questions that are flashy—to us nerds—like whether to ask for “any and all” documents or to call the officer every week or so. But the idiosyncrasies in journalists’ mental models trickle down even into the little details, like how they keep track of agencies’ contact info.
When I began an internal FOIA tracker app for the New York Times, I knew I’d have to understand different mental models of the FOIA process in order to represent that process in a database. So, I put out a call to the friendly community of news nerds on Twitter and in the NewsNerdery Slack:
Tracking your FOIAs with a spreadsheet (or an app) is a best practice. But everyone’s chart is a little different and probably encodes different nuggets of hard-earned wisdom. Care to share the column headers from your spreadsheet?
Computers don’t know anything about FOIAs. Bless their hearts, but they’re dumb; data modeling is how we imbue computers with little morsels of our human wisdom hidden in row 1 of a spreadsheet. I collated the results—from eight individuals’ spreadsheets and two open-source FOIA tracker apps plus my own, so hopefully a lot of little morsels of wisdom—and analyzed them to see what I might have missed. I want to share the results back to the community.
I found that community members’ different approaches to FOIA manifest even in how they design their spreadsheets and apps.
And the differences lay bare some interesting differences in how people approach their FOIA practice. People without a column for a particular attribute may still track it somehow, but the choices between what’s required—that is, what the computer can understand or what a spreadsheet can sort by—and what you can optionally put in a big field full of notes is revealing.
Of course, there were also a ton of similarities. Of the 11 people and apps surveyed, here are the pieces of information tracked by multiple respondents:
- Agency name (11)
- Subject/title of FOIA request (11)
- Date filed (11)
- Contact person—name or contact info (10)
- Due date (8)
- Status of request (7)
- Date acknowledgment received (6)
- Response tracking (6)
- Details of request (6)
- Who filed the request (5)
- Reference number (4)
- Jurisdiction (country, state, county, etc.) of agency (4)
- Additional contact-person notes (4)
- Misc. notes (4)
- File name or location on drive (4)
- Appeal info (2)
- How requests were submitted—which medium (2)
- Internal project name (2)
Every spreadsheet or app kept track of the agency with whom the request was filed, the subject of the request and the date it was filed. Everyone—except for me, hmm!—kept track explicitly of the contact person at the agency to whom the request was sent, either by name or contact details.
Most people keep track of whether and when a request was acknowledged and what the request’s tracking number is. And many people keep track of what the request’s status is—so you can easily find the requests that are overdue and sort to the bottom the ones that have been completed. And the contents of this column expose even more delicious detail. Is a request you’ve been waiting on since last week (“pending,” maybe) treated differently from one from 2015 (“overdue,” let’s say)? And how would you treat a request closed with the agency saying it had no responsive records?
It surprised me that at least one person doesn’t keep track of a request’s statutory due date—perhaps a sad reflection of how it often doesn’t matter, because agencies rarely meet their legal requirements. Though plenty of people still set alerts in calendars or apps for due dates. Others didn’t even keep track of whether a request had been fulfilled yet, maybe because if you’ve got your documents, that’s what you care about.
Besides setting often-blown deadlines for requests to be answered, the FOIA laws create a complex structure for requests, with fee waivers, extensions, and requests for expedited treatment—and parallel appeals processes for many of those decisions, along with a totally separate “mediation” process.
Many of the pieces of this aren’t modeled in many people’s spreadsheets and apps, including mine.
I know I don’t utilize many pieces of this structure, maybe to my detriment or maybe because no one challenges my eligibility for fee waivers as a journalist. But the fact that others have modeled this process in the forms—like Chris Groskopf at Quartz, who has “fee waiver” and “expedited” columns. He says he doesn’t frequently wrangle with agencies on these issues but, he says, “I often feel like I should, so I’m trying out tracking it.”
While I’ve rarely been asked to pay fees for federal requests, state rules often don’t provide for waivers for journalists, so I suspect people who file more state requests may deal with it more often—and so may model it in their spreadsheets more often.
Brad Heath’s FOIA Tracker app, in use at USA Today and at the Associated Press, includes several fee-related columns, but says, “For the most part, nobody uses the fee fields.” with the exception being “to keep track of costs on projects that involve a couple state open-records requests, for example,” says Heath, who works at USA Today.
The appeals process, to my surprise, is only included in Tony Webster’s and Sandhya Kambhampati’s spreadsheets, along with my own tracking app. Webster says, “I appeal maybe a third of my federal FOIA requests, but I’d like to do more because it’s becoming quite clear that some agencies just rubber stamp denials and the appeal is where a human actually thinks about the request for the first time.”
Kambhampati, too, files “a fair number of appeals.” and says she didn’t originally note the appeals process originally in her data model, but “then was annoyed at how unstructured my spreadsheet was.” and added it.
Several other sets of columns are more idiosyncratic. Dan Nguyen’s spreadsheet includes a place to write “precedent” for a request. Nguyen, a professor at Stanford, says, “that’s just shorthand for ‘When has a request like this been fulfilled before?’ e.g. the inspiration for the request, whether it’s just the same thing, different year; or same topic…for students, it’s a way to get them to research what’s been asked for before and then iterating on it.”
Tony Webster, a Minnesota-based independent journalist, includes a column for violations. I asked Webster what goes in that column: a list of what laws the agency has violated in fulfilling (or not) his request. “In my state of Minnesota, government doesn’t really have any incentive to comply with a data request on something controversial unless you threaten litigation,” he says.
“A letter from an attorney detailing numerous violations and the penalties associated with it usually, but not always, gets them to produce the data,” he adds.
The Logistics of Spreadsheets
The logistics of even how you’d use a spreadsheet or database table differed widely. Some trackers are for an individual while others, like the one I built at the Times, is meant to be used by anyone in the newsroom. Max Galka, who founded FOIA Mapper, has one spreadsheet per request, keeping track of each individual communication as a entry. Lisa Pickoff-White at KQED has one spreadsheet per project, while Tony Webster keeps track of what project a request is a part of on his spreadsheet. Both FOIA Tracker and MuckRock can suggest templates for requests; my internal app does not, but instead steps in after the request is written and filed to keep track of deadlines and send reminders.
Some clever folks even kept a column of where—on their computer, or, perhaps, physically—they kept the records. (I want to copy this, but then I’d have to type “drawer next to my desk” a lot.) That’s smart and caused me to start thinking about whether I want my FOIA app to accept uploads of responses and records too. (I’m leaning yes.)
The smallest spreadsheet kept track of 9 columns—different pieces of data about each request—and the largest, 28.
Now You Try
If you want to look at the columns from these spreadsheets and apps, go to town.
Thanks to everyone who responded to my call-out: Cecilia Reyes, Lisa Pickoff-White, Tony Webster, Matt Drange, Christopher Groskopf, Sandhya Kambhampati, Max Galka, Brad Heath, Dan Nguyen, Troy Thibodeaux, Brad Heath, and Michael Morisy.
Jeremy B. Merrill is an programmer/journalist at the New York Times and a core developer on the Tabula project. He likes building things—especially tools for finding the story amid all the digital noise.