-
Notifications
You must be signed in to change notification settings - Fork 1
POC Background
chip-rosenthal edited this page Sep 9, 2012
·
12 revisions
Background information to present at the Code for America Hackathon (8 Sep 2012), in support of the "TEC Filer Proof of Concept" project. (http://codeacrossaustin.eventbrite.com/)
- The requirements for candidate and lobbyist disclosures are set by the Texas Ethics Commission (TEC) [http://www.ethics.state.tx.us/main/local.htm].
- Local candidate and lobbyist disclosures are filed not with the State, but with the Austin City Clerk.
- Clerk takes filings on paper (or PDF versions of the forms).
- The filings are posted online but are not easily searchable [http://www.austintexas.gov/department/campaign-finance-reports].
- In April, the City Council passed an ordinance [http://www.austintexas.gov/edims/document.cfm?id=169484] to create an online, searchable database of campaign and lobbyist filings.
- Initial staff estimates to implement have come back with:
- about 800K cost
- about a year development
- solution based on proprietary technology
- In a meeting organized by Councilmember Morrison, City Staff was open to the idea of a "civic sourced" effort to help reduce costs and time.
Some complexities not addressed:
- There are additional forms, such as the treasurer filing and correction filings, that are not scoped at this time.
- There are additional schedules that the City of Austin uses, that are not scoped in this discussion.
- Local government can require electronic filings, but the process is going to need TEC approval.
Produce a "proof of concept" prototype that:
- Allows online filing of required campaign (Form C/OH) and lobbyist (Form SPAC) finance information
- Performs sanity checks on the filing data
- Stores data in an easily accessible form, to facilitate public review and analysis
- Render data in format of TEC form (possibly PDF) for viewing, downloading, printing.
The event was hugely successful. We achieved 65% of the goal outlined above, and that's ok. It's better to be 1/2 mile down the right path than 5 miles down the wrong path. I believe we are on the right path, and are very close to a demonstrable proof-of-concept.
- Chip Rosenthal, @chipr, chip AT unicom.com, contracts at coretrace.com
- Roy Wright, roy AT wright.org, works at coretrace.com
- Rich Cork, Harris County IT Group
- Nick Wood, Texas A&M, Texas Transportation Institute, nickwood AT tamu.edu
- Kevin Reilly, consultant for basejump.io, kevin AT basejump.io, @kevinmreilly
- Martin Valenzuela, student, @lothilius, mars110110 AT aol.com
Rich and Kevin helped during the planning phase. Nick and Martin solved the data import problem and produced data samples. Roy and Chip did the coding. Roy did the framework and environment. Chip did the data model objects.
- Dropbox shared folder: https://www.dropbox.com/sh/vzvnphplukojxbr/lO0cHZtWPx
- We should follow the format documented in the State Import Guide. http://www.ethics.state.tx.us/whatsnew/ImportGuide.pdf (copied to the Dropbox)
- The "Gotta Register" project is open source and may have some tech we can use for generating PDFs
- The "SQLite Manager" (Firefox plugin) is a good tool to browse the database created by data load
- Identified mechanism and format for importing schedule details (following TEC Import Guide, uses CSV).
- Created sample import dataset for contributions/pledges.
- Setup framework, with feature and test spec support.
- Created model objects COH and Contribution, validations, and connected to database.
- Successfully parsed a "contributions" CSV, validated, and stored to database.
- Scoped items not completed:
- import expenditures
- define mechanism to obtain cover sheet (Form COH) values
- support for SPAC form
- generate facsimiles of TEC forms
- The candidate retrieves a spreadsheet template that we provide.
- The candidate enters all the details for the main Candidate/Office Holder report (Form C/OH) in the first tab of the spreadsheet.
- The spreadsheet has additional tabs, one for each C/OH report schedule. The candidate enters the detail for a given report schedule into the form on the appropriate tab.
- The candidate saves the spreadsheet to a file.
- The candidate runs a command line utility with the saved spreadsheet as input.
- The utility runs, loads the spreadsheet content into data objects, and completely validates the data fields.
- If validation fails, detailed (user friendly) diagnostic information is provided, and the process terminates.
- If validation succeeds, the data objects are saved to the data store.
Notes:
- This assume it makes sense to put all the form information, not just the schedule line items in the spreadsheet.
- Although the POC implements data acquisition with a command line utility, we envision a web interface with file upload for a final implementation.
- Follows same flow as C/OH story, except substitute:
- the actor: C/OH -> lobbyist/PAC
- the form: C/OH -> SPAC
- The user obtains an identifier for a given report on file ("the id").
- User runs a command line utility, specifying the the id.
- The utility runs to completion, and emits a PDF rendering of the report.
Top priorities are schedule A and schedule E, which are the most frequently used forms.
OPEN ISSUE: Need to fully prioritize schedules, and identify which are: required, desired, optional
http://www.ethics.state.tx.us/forms/coh.pdf
- Schedule A: Political Contributions other than Pledges or Loans
- Schedule B: Pledged Contributions
- Schedule E: Loans
- Schedule F: Political Expenditures
- Schedule G: Political Expenditures Made from Personal Funds
- Schedule H: Payment from Political Contributions to a Business of C/OH
- Schedule I: Non-Political Expenditures made from Political Contributions
- Schedule K: Interest Earned, Other Credits/Gains/Refunds, and Purchase of Investments
- Schedule T: In-Kind Contribution or Political Expenditure for Travel Outside of Texas
http://www.ethics.state.tx.us/forms/spac.pdf
- Schedule A: Political Contributions other than Pledges or Loans
- Schedule B: Pledged Contributions
- Schedule C: Corporate or Labor Organization Contributions other than Pledges or Loans
- Schedule D: Pledged Corporate or Labor Contributions
- Schedule E: Loans
- Schedule F: Political Expenditures
- Schedule H: Payment from Political Contributions to a Busines of C/OH
- Schedule I: Non-Political Expenditures made from Political Contributions
- Schedule J: Political Contributions Returned to Committee
- Schedule K: Interest Earned, Other Credits/Gains/Refunds, and Purchase of Investments
- Schedule T: In-Kind Contribution or Political Expenditure for Travel Outside of Texas
I propose Ruby because:
- that's what I'm doing initial prototyping in
- believe libraries ("Ruby gems") are readily available to support needed functions
- sufficient local talent and expertise
- The two essential requirements are: developer easy and extensible.
- The ideal data store for the POC will be a zero time to deploy solution, so developers can work on code and not dev environments. Examples: CouchDB or PostgreSQL bad. SQLite good. Flat files better.
- Extensibility matters. Need the ability to easily add new forms and schedules. Need ability to modify forms and schedules as requirements change.
- The extensibility issues make relational is a poor fit for this long term. (Although SQLite could be viable for the POC.)
- Performance and scale don't matter.
- At some point there needs to be a discussion on interface/data load to the Socrata system (http://data.austintexas.gov).
- OPEN ISSUE: Add this discussion. Propose DataMapper.
- Propose sourcing spreadsheets in LibreOffice Calc format (ODS filetype), and providing derived Excel format (XLS filetype) as alternate.
- Propose ODS as source because format is unencumbered, and implementations (OpenOffice, LibreOffice) are free downloads. Excel would still be fully supported.
- Ruby gem requirements: read support for both formats, support for multi-tab spreadsheets.
- Propose: roo [http://rubyforge.org/projects/roo/]
- ACTION REQUIRED: Need to verify this gem will be suitable.
- Need a tool that can fill out forms, possibly PDF templates.
- Propose initial group gathers, reviews proposal, and lays out plan of attack for the day.
- Propose dividing into three squads:
- Squad Spreadsheet: Implements the spreadsheet template and data extraction routines (pull data from spreadsheet and create data objects)
- Squad Data Objects: Implements data object classes, with full field validation and unit tests.
- Squad Report: Generate PDF form templates, and routines to overlay data objects into forms.