Connecting to LGBT-affirming healthcare providers
Affirmations is Michigan’s largest lesbian, gay, bisexual, and transgender community center. Its health and wellness program is revealing the health impacts of stigma, discrimination and societal inequalities while leveraging community strengths and celebrating resiliency. The program is expanding, and staff members are seeking new ways to share crucial health information with the community.
Affirmations came to us to evaluate existing infrastructure and propose a database solution that would connect people to LGBT-affirming healthcare providers. Surprisingly, an effective online database for this information did not yet exist in Michigan. A few national players had developed directories, but none offered high-quality data about Michigan providers. Affirmations wanted to change this.
We worked with Affirmations staff to develop a simple and efficient prototype website that allows users to filter listings using a variety of criteria or search by a provider’s name. For example, a user can filter for Mental Health Practitioners in Wayne County. The site then offers detailed listings for mental health practitioners who have signed the Affirmations Community Standards of Practice.
When designing and building the database system, we wanted to ensure that we create a system that worked within Affirmations’ technical capacity. Like many community organizations, Affirmations needs to leverage technology to enhance its work, but doesn’t have a huge budget for tech projects. We designed the database to minimize ongoing costs and technical debt while still using modern development practices that make it possible to extend the project in the future.
Ease of managing data
One of the first questions we asked when designing the solution was how Affirmations was maintaining its existing list of healthcare providers. The answer: a spreadsheet. Because there wasn’t a lot of time or staff capacity to manage data, we decided to use a Google Spreadsheet for the data store. This would make updating the public facing site feel like an extension of existing data management practices. Also, since Google Drive is a widely-used service, it was likely that staff would be familiar with the application.
Spreadsheets are a useful tool for prototyping the data models of a web application. While size, complexity of data, or performance may eventually require migrating to a more sophisticated data store, the bigger challenge at the launch of a project is understanding the data and identifying any quirks. By making it easy for Affirmations staff to enter data, we were able to quickly test any assumptions that we had made when thinking about how to best implement the project.
Static data, static site
We wrote a small task using the Grunt task runner to consume the provider data through the Google Spreadsheets API and convert it to a JSON file. The data file is checked in to the repository along with the source code. Baking the data to JSON has a number of benefits:
- It provides a backup of the data.
- It makes the data easily consumable for other applications that might need provider information.
- It keeps the application from suffering if the Spreadsheets API goes down or has performance issues.
Supporting content for the site is generated using the Assemble static site generator. While there isn’t a lot of content in the site, using the static site generator let us separate data, templates and content, making it easy to migrate to other platforms in the future. It also made it easy for non-coders to edit site content without wading through HTML markup.
Minimal hosting and maintenance costs
While it’s fast and easy to rebuild the site from the development environment, we wanted to make it easy to regenerate the site when the data changes. However, we didn’t want to have to incur the cost of running servers to regenerate the site. So we tied together a number of popular services to aid in the site building process. While these services make deployment easy, we made sure to choose ones that are based on widely-used open source technologies like git.
We use the Heroku Scheduler to run the Grunt task that bakes the Google Spreadsheet data to JSON. The task uses the GitHub API to check if the data has changed since the last site build. If it has, the updated JSON file is committed to the git repository on GitHub. The push to GitHub triggers a build on the Travis CI platform. Travis runs the test suite, and if it passes, uses Grunt to build the site and push it to GitHub pages, making the changes visible to the public.
None of the steps in the deployment process are dependent on the external services. They could easily be run from a developer’s workstation or a Linux server. If the services stop running or change their pricing, the application can easily be deployed in a different environment. The wiring would have to change a bit, but not the core application architecture.
Accessible, open-source programming languages and frameworks
Over the coming months, Affirmations will promote this prototype, gather feedback from its network, and add more data on health providers. As the program expands, we hope to build out the following improvements:
- Create a custom design that complements Affirmations’ brand identity
- Offer sponsorship opportunities on the site
- Allow users to submit a request to change a listing
- Send providers custom notifications reminding them to review listing details
- Co-develop long-term strategies for generating sustainable program income while offering significant value to providers