CANVAS Database Schema

Matt Chapman - Free Our Info, NFP filed this request with the Chicago Department of Finance of Chicago, IL.
Due Sept. 12, 2018
Est. Completion None
Awaiting Response


From: Matt Chapman - Free Our Info, NFP

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.


Matt Chapman - Free Our Info, NFP

From: Matt Chapman - Free Our Info, NFP

Has there been any success with this request?


From: Chicago Department of Finance

Dear Matt Chapman,

Please see the attached.
Kristen Cabanban
City of Chicago
Director of Public Affairs
Office of Budget and Management / Department of Finance

From: Matt Chapman - Free Our Info, NFP

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 - 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:

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.

Matt Chapman
Free Our Info, NFP