
Using FOIA and public databases to track down Department of Defense contracts - danso
https://www.muckrock.com/news/archives/2018/aug/24/how-use-foia-find-department-defense-contracts/
======
bpchaps
(disclaimer: I own a non-profit for submitting requests, education, and
advocating the release of digital records)

I seriously recommend that everyone submit a FOIA request for information that
they're interested in and make it public. Either locally or federally. You'll
learn a _lot_ about the interface between you and your government.

Something to think about - Illinois FOIA allows the submission of code within
a FOIA request. For example, as long as you know the schema of a database, you
can submit SQL for a government owned/contracted database. The schema is often
included in contract specs, but more often requires a request to figure it
out. My nfp is working on some projects (through muckrock) to build out a list
of schemas across all states' big cities. It's possible, but there's still a
lot of work to do!

~~~
danso
> _you can submit SQL for a government owned /contracted database_

I’ve heard of journalists working with government IT folks to craft a SQL
query, but not sending code in the records request. Do you by chance have an
example letter that uses code?

~~~
bpchaps
I just made sent one: [https://www.muckrock.com/foi/chicago-169/canvas-
database-sch...](https://www.muckrock.com/foi/chicago-169/canvas-database-
schema-60175/)

The idea comes from [1], where like you say, a journalist/requester can work
with the government IT folk to craft a SQL query. A big problem comes, though,
from the burden and staffing required to write the query. To add to that, FOIA
often requires cost estimates prior to starting a request - which, in the case
of writing code, can muddle estimations... which have to be paid on the onset.
My position is that if the requester understands the schema, then the burden
required to write the query is essentially nullified. That way, the only cost
would then be the review of the query, which is trivial in comparison to
writing one from scratch.

There's a lot of prereq knowledge needed before submitting a request for code.
That, and there's generally a potential for penalty against a public body that
doesn't run a full search. So in many ways, there isn't really a need for a
requester to submit their own SQL from the onset.

[1]
[http://illinoiscourts.gov/Opinions/AppellateCourt/2018/2ndDi...](http://illinoiscourts.gov/Opinions/AppellateCourt/2018/2ndDistrict/2170617.pdf)

(not a laywer, etc)

~~~
toomuchtodo
Is it not possible to request an electronic copy of the database dump in its
entirety?

~~~
bpchaps
It is possible! For example, I recently received [1] a mysqldump of Chicago's
"data dictionary" database, which is a collection of database schemas from
databases throughout Chicago.

That said - it's not always simple. The big problem is that information being
released through FOIA still needs to be reviewed by a person, which can be
time consuming and expensive. Especially so when a technical person gets
involved. It's often just much easier (for _everyone_ ) to request specific
information.

The "data dictionary" database dump was only easy to get only because I knew
for a fact that the database was already reviewed through a process that
matched FOIA's.

[1] [https://www.muckrock.com/foi/chicago-169/data-dictionary-
mys...](https://www.muckrock.com/foi/chicago-169/data-dictionary-mysql-
dump-59229/)

------
sailfast
This is a great starting guide. As someone that heavily researched
procurements for awhile these are good off the shelf resources. It takes
awhile to understand the specific language and codes (NAICS, labor codes, etc)
used for each of these contracts, but after awhile they start to make some
sense.

I would also recommend taking a look at budget planning documents and other
resources from the DoD Comptroller
([https://comptroller.defense.gov/budget.aspx](https://comptroller.defense.gov/budget.aspx))
continue to follow-up on task orders that are available under massive "ID/IQ"
or BPA contracts in the billions of dollars (these are often withheld from the
public but available to the firms that won the original work, though the
actual values can be found sometimes in FPDS under sub-codes.

Additionally, many of these folks hold various BPA / GSA Schedule contracts
and sometimes their lead officers will respond to inquiries.

Example: GSA Schedule 70, MOBIS, Navy SEAPORT-E, etc.

Happy hunting!

------
zacharycohn
[https://www.usaspending.gov/#/](https://www.usaspending.gov/#/) is another
great resource

------
m1573rp34130dy
...i havent made a lot of FOIA requests lately, but there was a time when
redaction would vary depending on the reason stated for the request, thus
enough different requests for different reasons could be use to assemble a
nearly complete document...

