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.

Affirmations Healthcare Providers Database

A Healthcare Provider's Profile

Sustainable technology

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

To call the infrastructure for the network database simple wouldn’t be entirely accurate. There are a number of moving pieces that help automate site updates. However, the output of these pieces, and what gets served to the user, is static HTML, CSS and JavaScript. This makes it easy to backup the live site and made it possible to host the site on almost any hosting platform, whether a shared webhost or Amazon’s S3 service. We ultimately decided to host the site using the GitHub Pages service to help minimize ongoing costs.

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 allows us to structure the data in a way that makes the user-facing JavaScript code smaller and simpler.
  • 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.

Finally, static content helps make the site perform well for users, even those on mobile phone networks or without fast internet connections. There is no added latency to retrieve the data from a database or render a template, so page loads are dependent only on downloading the page content and assets. Assemble can easily render JSON data as part of the sites that it builds, so we decided to render the provider data into the generated HTML. This limits the amount of client-side rendering that we need to do. It also allows users with very, very old browsers to see a list of providers, even if their browser doesn’t support the client-side JavaScript used for filtering and searching providers.

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

Since HTML, CSS and JavaScript are widely used and widely taught technologies, if other developers are needed for this project, there is a large pool of people with the skills needed to extend the site. Using JavaScript also allows us to easily move application logic between the client-side and the build process in the future as features are added or use cases change.

See the project on GitHub.

See the live website at

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