opentwincities / mnpay

Making public wage data actually public

MN Pay

This project relies on data collection and is currently not working because we don't have the data we need. For more information about the state of this project, check out our wiki here!

Getting started

Dependencies

Your system needs the following installed:

• Docker - DigitalOcean has a great tutorial to do this on ubuntu 16.04.
• Docker-compose - If you have pip installed this can be installed with pip install docker-compose

One-time setup

Initialize the database and load in the data. This will be done on your local machine outside of a docker container.

cd server
pipenv install --dev
pipenv shell
python manage.py migrate
python manage.py loadwages ../resources

This is creating an sqlite database in the project repo and populating it with the data from the resources/ directory. We do this once at the start of the project because the application considers this database to be a static build artifact (It has not need to mutate the data while running).

Now that this database has been built the application itself will be served through docker.

If you run into issues, consider deleting Pipfile.lock and trying again.

Running

To launch your server run

$ source ./dev_env.sh
$ docker-compose up

You can then direct your browser to localhost:80 to see the site.

If you are working on the server, restart the server container to see changes. Do this by hitting ctrl+c in the terminal you ran docker-compose up in, then run docker-compose up again.

Adding server dependencies

If you add a dependency to the server you need to rebuild the docker containers in order for it to get picked up. This would be the case if you ran pip install [anything]. Add the requirement to server/requirements.txt and then run

$ docker-compose build oneoff
$ docker-compose build web

Handy commands

If you need to connect to a container for debugging use:

docker exec -it <container_id> /bin/bash

Deployment

First run docker-compose up and python manage.py loadwages ../resources to gather static files, build the client, and load in the data.

docker save -o mnpay_nginx.image mnpay_nginx
scp mnpay_nginx.image <remote_server>/<remote_path>
docker save -o mnpay_web.image mnpay_web
scp mnpay_web.image <remote_server>/<remote_path>

On the remote server.

docker load -i mnpay_nginx.image
docker load -i mnpay_web.image
docker-compose -f docker-compose-prod.yml up

mnpay's Issues

Cook County

Sent a data request to Cook County through a form on their website on 2016-11-05.

Got a response back from Molly Hicken ([email protected]) with Jeff Cadwell CCed ([email protected]) on 2016-11-07 clarifying whether I wanted them in electronic or paper form and whether, if I wanted them electronically, I wanted a disk mailed to me or for them to be published through a Sharepoint site with a temporary user name and password. I responded the same day requesting electronic copies shared through a Sharepoint site and also clarified the legislation that I was citing (13.46 -> 13.43).

I heard back from Molly Hicken on 2016-11-15. She reported back from Jeff Cadwell:

We believe that it would take 2 hours to pull the readily available data for current and past employees. We would have all data requested readily available for current employees. We would have less data readily available for historical employee information. The cost for 2 hours of staff time to access and compile the information would be $40.00 We could have this data available in 7-10 days.

I emailed her back on 2016-11-18 asking if it would reduce the time required by focusing just on current employees. I also indicated who we are and what the data would be used for, as well as our interest in working with the data sets they have readily available as to avoid them having to spend extra time and incurring extra expense.

Auto complete search fields

It would be nice to have some way of reducing how much you need to know to successfully search. Right now if you wanted to search for The Minnesota Department of Natural resources you may need to iterate over "DNR", "D.N.R.", "Dept. Natural Resources", and "Department of Natural resources".

Group by year on salary distribution visualization

Government / Year data overview page

We should have a page that lists the years we have data for each government to make it easier for the Pioneer press to make data requests.

Treat agency and department as a single field for the purpose of searching

It isn't always certain which one you actually want so it would be nice just to look for both.

Preserve database across images

In some circumstances docker-compose down is deleting the db image and forcing us to repopulate the database. This leads to about 10 minutes of unnecessary downtime during updates. We should find where the database is actually being stored and map it into the host file system.

Add in "About us" section

This should include the [email protected] email

No visual examples in the README

I just stumbled upon this repository, and I couldn't help but notice there was no graphic example of anything in the README.md file.

That feels a little lacking.
Mind if I think up a good graphic image and post in here to resolve that?

Fix broken link on get involved page.

The link to our github repo on the get involved page links to the get involved page itself. This should link to our repo.

Remove duplicate entries (all fields matching)

Duplicate entries usually mean that they had multiple appointments for the same job title. This appears to be most common in academic positions.

Simplify form onchange functions

We should be able to simplify the onchange functions we use for the form given that the event object holds the entire form element. This means we could pull the object name off of the form element name.

Minneapolis 2018

We need to request payroll data from the city of Minneapolis for 2018.

Add routing to allow linking to results

React has routing capabilities. When the form is submitted we should change the url to include the form arguments.

Minnesota state

We don't have a data set for 2016 yet listed at https://mn.gov/mmb/transparency-mn/payrolldata.jsp. I've sent an email to [email protected] asking when they intend to update the data set.

University of Minnesota 2018

We need the 2018 data set from the U of M. This is all stuff that they have in their data warehouse (or at least have in previous years). One of the challenging parts here is that some of the really interesting salaries (head coach) included bonuses that aren't consistently included. If we can get them to just give whatever the figure on the W2 is it would be a big win.

Build client for production

We are currently getting the warning:

Warning: It looks like you're using a minified copy of the development build of React.
When deploying React apps to production, make sure to use the production build which skips
development warnings and is faster. See https://fb.me/react-minification for more details.

on the production site. We need to figure out the build process to deploy correctly.

Allow middle string searching in fields

Currently all fields are searched by matching on the front of the string. It is going to be a better service if we allow fields such as "Agency" or "Dept" to be searched in the middle. We can try a simple transition without changing schema and see if it runs in a reasonable way. Otherwise we may need to make some changes to the schema to make it efficient.

City of Saint Paul

I submitted a "dataset request" to the City of Saint Paul.

https://information.stpaul.gov/City-Administration/Suggest-A-Dataset/ta2d-xcgj

There was a non-textarea field, so the text was constrained. However, I typed the following based on the wiki suggestions:
Saint Paul employees across all agencies not excluded under M.S. 13.43 sub 5 or sub 5a for all years that it is readily available. Data: name, employee identification number, actual gross salary, job title, dept/division to which they report, year

Add wiki link prominently on README.md

So that folks understand more about the state of the project and where we go from here.

https://github.com/OpenTwinCities/mnpay/wiki is the destination

Saint Paul 2018

We need to get payroll data for 2018 in Saint Paul.

Make columns sortable

After showing this to Jen Westpfahl she requested that we make some / all of the columns sortable. @ahangsleben was also recommending this.

Hennepin County 2018

We need payroll data for Hennepin County in 2018.

Make an easy way to change server between debug/production

Right now we are defaulting into debug mode, but we really shouldn't be doing this once we are in production.

Minneapolis Park Board Salary Data

Attached is salary data from 2007 to present date in 2017 for the Minneapolis Park Board.

mprbdatarequest1.zip

Get Travis-CI working

Create popup/overlay noting that project is not currently maintained

redirect users to resources for getting involved:

• https://otc-slackin.herokuapp.com/ (OTC Slack)
• https://github.com/OpenTwinCities/mnpay/wiki/Gathering-Data

Write server tests

It's a good practice I haven't been following.

Create fake data for development database and put it in the repo

After running

> git clone ...
> docker-compose up -d

The database still doesn't have any data in it so it isn't fully ready for development.

Test Issue

City of Rochester

Filled out a contact form for the City of Rochester City Clerk, Aaron Reeves on 2016/11/10. I found documentation that the City of Rochester designated the City Clerk as the responsible authority. I received a response stating that the request was too broad because it would cover thousands of employees and would take months. I responded that I believed that an electronic payroll system and HR records should be easily accessible, and that job descriptions, first and last hire dates and records not stored electronically could be omitted.

2016/11/14: Aaron Reeves replied saying that the data is in several different software systems in various formats, and only name, title and salary would be relatively simple and he would be willing to get me a cost estimate. I called him at 507-328-2911 and left a message asking for more clarification on the systems they use and the data that is easily available.

2016/11/23: Called and left a message again for Aaron Reeves.

2016/11/28: Received a call from Linda Hillenbrand, Director of Human Resources for the City of Rochester (507-328-2555), who prepared the estimate for Aaron Reeves. She will get back to me by the end of the week with a time and cost estimate. She stated that they use a system called "JD Edwards" for their payroll data, and the system "rolls over with the new year", so they would have to write individual reports to extract this type of "historical data". I clarified with her that we would like name, salary, title and department/division, she said that employee compensation is sometimes listed as hourly, which I told her was ok. She said that as far as labor and cost, one year of data would be the easiest.

Ramsey County 2018

We need payroll data for Ramsey County in 2018.

Washington County

Sent an email out to Rick Hodsdon ([email protected]) on Nov 5.

Got an email back later on Nov 5 indicating I had cited the wrong piece of legislation (M.S. 13.46 when I should have been citing M.S. 13.43. Responded indicating that he was correct and clarified that I wanted electronic copies of the data.

Email from Rick Hodson on 2016-11-10. He is working with various parts of the county (payroll and personnel) to find how to best process the request. I replied back indicating that job description and first-and-last date of employment can be excluded if it would expedite the request.

Heard back on 2016-11-16 from Rick. It looks like they can send the data on a disc for five dollars plus postage. I responded that I would pay for postage and asked them about how/when they wanted to be payed.

On 2016-11-17 Rick responded saying that he would get me a final cost once the disc is shipped to him. I can then send and check and advise him on where to mail it.

2016-11-30 (KJS): Sent an email out to Rick to see if there are any updates on this since it has been a while since his last response.

Olmsted County

Emailed Olmsted County Human Resources at [email protected] on 2016/11/10.

2016/11/14: Emailed the County Attourney at [email protected].

2016/11/15: Email from David Mueller, Director of Intergovernmental Relations/Data Practices/Organizational Development ([email protected]) stating that Human Resources received my request and will be in contact with me about the costs and timeline of my request.

2016/11/23: Call from Dave Muller, Olmsted County (507-328-6026). His impression was that we asked for too much information. Olmsted County has records back to ~1980 on microfilm, paper and electronic records, with the majority of employees in electronic format. They use CGI AMS software for their database. I explained that we only want recent data (2012-present) and gave mnpay.org as an example. He will talk to HR and get back to me with an estimate of labor/cost on Monday (11/28).

Make landing page sorted by wage

Currently it is not ordered at all.

Add years as a sortable field

It is currently excluded while almost all others are allowed.

Add project status information to Wiki home page

https://github.com/OpenTwinCities/mnpay/wiki should mention that the app isn't currently operational, why this is, and what folks can do to revive it (data gathering).

1-step build process

Create a custom Docker image and a build script, much like the opentwincities/rbenv-adoptatree Docker image and build_fast.sh script in the Adopt-a-Tree project.

Clarify requirements for local setup in README

• I get nimby and panda errors when I run pipenv install --dev on OS X
• I'm not sure what Python version I should be using (both 3.5.x and 3.6.x mentioned in the project)
• I'm unclear on whether I should be developing inside the docker container, or just running the app from there

@kjschiroo can you clarify?

University of Minnesota dataset

We need to gather pay data for UMN.

Address security issues with dependencies

https://github.com/OpenTwinCities/mnpay/network/alerts

Write client tests

There aren't any client tests currently. The client code is getting a smell to it and we need tests to make sure we don't regress.

Add continuous integration badge

This app should have a continuous integration badge.

Add the ability to accept donations

MN State 2018

Data for MN State has generally been available somewhere on their website, we should just need to track it down. This should be one of the easier ones.