CANVAS Database Schema

To Whom It May Concern:

Pursuant to the Illinois Freedom of Information Act., I hereby request the following records:

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'

The requested documents will be made available to the general public, and this request is not being made for commercial purposes.

In the event that there are fees, I would be grateful if you would inform me of the total charges in advance of fulfilling my request. I would prefer the request filled electronically, by e-mail attachment if available or CD-ROM if not.

Thank you in advance for your anticipated cooperation in this matter. I look forward to receiving your response to this request within 5 business days, as the statute requires.

Sincerely,

Matt Chapman - Free Our Info, NFP

Has there been any success with this request?

Thanks!

While I understand the concern, there is absolutely a review process which CDoF can follow to make (at least most of) its of CANVAS's database schemas. Such process has been used in the past for the release of thirteen distinct database schemas for Chicago's public "Data Dictionary" (aka, "metalicious"). The departments that followed the process are Administration & Finance, Education, Ethics, Department of Innovation and Technology, and Health & Human Services. I kindly ask that you reach out to those departments to learn more about the review/redact process of database schemas.

Two points to back up my claims:
1. Per Tom Schank on the ChiHackNight Slack (ex Chief of Data) on the publication of database schemas:
"[Metalicious] had some, but very, very small proportion of databases. There was security review of any published data and some information was withheld if we felt it could undermine the application security. By Info Sec policy, it is confidential information until a review deems it appropriate for public release--same as the open data workflow which mirrors the FOIA workflow."

In other words - the process used to release those database schemas matches the FOIA application security workflow.

2. Chicago's DoIT has also released a mysql dump of the data dictionary to FOI through FOIA - https://www.muckrock.com/foi/chicago-169/data-dictionary-mysql-dump-59229. By DoIT providing this information, it highlights that database schemas are specifically releasable through FOIA. FOI has since used the dump to create its own instance of the datadictionary, which I highly recommend your department review for understanding what sort of database schema information has passed infosec review: https://metalicious.freeourinfo.com.

This request's responsive records, once released, will be added to FOI's instance of metalicious and will be used for further research of parking tickets. As such, any burden incurred by this request will be greatly outweighed by the public's interest.

Regards,
Matt Chapman
Free Our Info, NFP

You mentioned on Thursday over the phone that IBM is not too keen on having its database schema released, and, between IBM and Chicago, is seeking an exemption under 5 ILCS 140/7(1)(g) - an exemption that is only valid if the release of records would cause competitive harm. This email preemptively seeks to address that exemption within the context of this request in the hopes of a speedier release of records. It is FOI's belief that there is little room for the case for the valid use of 5 ILCS 140/7(1)(g) when considering the insignificance of the records in conjunction with the release of past documents:

1. Chicago released CANVAS's technical specification [1] seven years ago. To the extent that the specification's continued publication does not cause competitive harm, it is very unlikely that the release of CANVAS's database schema would cause any harm.
2. The claim that the release of a database schema would cause competitive harm is not unlike suggesting that the release of filing cabinets' labels can cause competitive harm.

Furthermore, in your response, please be mindful that the burden of proving competitive harm rests on the public body [2].

Kind regards,
Matt Chapman
Free Our Info, NFP

[1] https://www.cityofchicago.org/content/dam/city/depts/dps/ContractAdministration/Specs/2011/Spec92488II.pdf
[2] http://foia.ilattorneygeneral.net/pdf/opinions/2018/18-004.pdf