Trying to end the pandemic a little earlier with VaccinateCA

2021-02-28T05:40:28+00:00

This week I got involved with the VaccinateCA effort. We are trying to end the pandemic a little earlier, by building the most accurate database possible of vaccination locations and availability in California.

VaccinateCA

I’ve been following this project for a while through Twitter, mainly via Patrick McKenzie - here’s his tweet about the project from January 20th.

https://t.co/JrD5mb4TAN calls medical professionals daily to ask who they could vaccinate and how to get in line. We publish this, covering the entire state of California, to help more people get their vaccines faster. Please tell your friends and networks.
- Patrick McKenzie (@patio11) January 20, 2021

The core idea is one of those things that sounds obviously correct the moment you hear it. The Covid vaccination roll-out is decentralized and pretty chaotic. VaccinateCA figured out that the best way to figure out where the vaccine is available is to call the places that are distributing it - pharmacies, hospitals, clinics - as often as possible and ask if they have any in stock, who is eligible for the shot and how people can sign up for an appointment.

What We've Learned (So Far) by Patrick talks about lessons learned in the first 42 days of the project.

There are three public-facing components to VaccinateCA:

www.vaccinateca.com is a website to help you find available vaccines near you.
help.vaccinateca is the web app used by volunteers who make calls - it provides a script and buttons to submit information gleaned from the call. If you’re interested in volunteering there’s information on the website.
api.vaccinateca is the public API, which is documented here and is also used by the end-user facing website. It provides a full dump of collected location data, plus information on county policies and large-scale providers (pharmacy chains, health care providers).

The system currently mostly runs on Airtable, and takes advantage of pretty much every feature of that platform.

Why I got involved

Jesse Vincent convinced me to get involved. It turns out to be a perfect fit for both my interests and my skills and experience.

I’ve built crowdsourcing platforms before - for MP’s expense reports at the Guardian, and then for conference and event listings with our startup, Lanyrd.

VaccinateCA is a very data-heavy organization: the key goal is to build a comprehensive database of vaccine locations and availability. My background in data journalism and the last three years I’ve spent working on Datasette have given me a wealth of relevant experience here.

And finally… VaccinateCA are quickly running up against the limits of what you can sensibly do with Airtable - especially given Airtable’s hard limit at 100,000 records. They need to port critical tables to a custom PostgreSQL database, while maintaining as much as possible the agility that Airtable has enabled for them.

Django is a great fit for this kind of challenge, and I know quite a bit about both Django and using Django to quickly build robust, scalable and maintainable applications!

So I spent this week starting a Django replacement for the Airtable backend used by the volunteer calling application. I hope to get to feature parity (at least as an API backend that the application can write to) in the next few days, to demonstrate that a switch-over is both possible and a good idea.

What about Datasette?

On Monday I spun up a Datasette instance at vaccinateca.datasette.io (underlying repository) against data from the public VaccinateCA API. The map visualization of all of the locations instantly proved useful in helping spot locations that had incorrectly been located with latitudes and longitudes outside of California.

I hope to use Datasette for a variety of tasks like this, but it shouldn’t be the core of the solution. VaccinateCA is the perfect example of a problem that needs to be solved with Boring Technology - it needs to Just Work, and time that could be spent learning exciting new technologies needs to be spent building what’s needed as quickly, robustly and risk-free as possible.

That said, I’m already starting to experiment with the new JSONField introduced in Django 3.1 - I’m hoping that a few JSON columns can help compensate for the lack of flexibility compared to Airtable, which makes it ridiculously easy for anyone to add additional columns.

(To be fair JSONField has been a feature of the Django PostgreSQL Django extension since version 1.9 in 2015 so it’s just about made it into the boring technology bucket by now.)

Also this week

Working on VaccinateCA has given me a chance to use some of my tools in new and interesting ways, so I got to ship a bunch of small fixes, detailed in Releases this week below.

On Friday I gave a talk at Speakeasy JS, "the JavaScript meetup for 🥼 mad science, 🧙‍♂️ hacking, and 🧪 experiments" about why "SQL in your client-side JavaScript is a great idea". The video for that is on YouTube and I plan to provide a full write-up soon.

I also recorded a five minute lightning talk about Git Scraping for next week's NICAR 2021 data journalism conference.

I also made a few small cosmetic upgrades to the way tags are displayed on my blog - they now show with a rounded border and purple background, and include a count of items published with that tag. My tags page is one example of where I've now applied this style.

TIL this week

Releases this week

flatten-single-item-arrays: 0.1 - 2021-02-25
Given a JSON list of objects, flatten any keys which always contain single item arrays to just a single value
datasette-auth-github: 0.13.1 - (25 releases total) - 2021-02-25
Datasette plugin that authenticates users against GitHub
datasette-block: 0.1.1 - (2 releases total) - 2021-02-25
Block all access to specific path prefixes
github-contents: 0.2 - 2021-02-24
Python class for reading and writing data to a GitHub repository
csv-diff: 1.1 - (9 releases total) - 2021-02-23
Python CLI tool and library for diffing CSV and JSON files
sqlite-transform: 0.4 - (5 releases total) - 2021-02-22
Tool for running transformations on columns in a SQLite database
airtable-export: 0.5 - (7 releases total) - 2021-02-22
Export Airtable data to YAML, JSON or SQLite files on disk

Tags: crowdsourcing, django, postgresql, patrick-mckenzie, datasette, weeknotes, covid19, vaccinate-ca, personal-news, jesse-vincent

Quoting Patrick McKenzie

2020-04-29T06:39:55+00:00

The biggest thing people don’t appreciate about large companies is the basic productive unit isn’t an individual it is an engineering team with about ~8 members.

— Patrick McKenzie

Tags: management, patrick-mckenzie

Quoting Patrick McKenzie

2017-12-02T15:17:19+00:00

Significant advances shipped by the tech industry in the last 20 years include putting the majority of human knowledge in the hands of 40%++ of the world's population, available on-demand, for "coffee money" not "university money."

— Patrick McKenzie

Tags: patrick-mckenzie

Quoting Patrick McKenzie

2017-10-23T17:45:12+00:00

We made a sale for Appointment Reminder from someone whose only way of getting data into the system was to fax it to us. Guess the cheat. If you guess "CEO signs up for HelloFax, receives the fax, and types 600 patient names and phone numbers by hand" you have good instincts.

— Patrick McKenzie

Tags: sales, patrick-mckenzie

Getting Your First 10 Customers

2017-10-23T17:36:19+00:00

Getting Your First 10 Customers

There is so much good advice embedded in this article by Patrick McKenzie, and it constantly comes back to the theme of doing whatever it takes to get to your first ten paying customers.

Via Patrick McKenzie

Tags: startups, sales, patrick-mckenzie

Simon Willison's Weblog: patrick-mckenzie

Trying to end the pandemic a little earlier with VaccinateCA

VaccinateCA

Why I got involved

What about Datasette?

Also this week

TIL this week

Releases this week

Quoting Patrick McKenzie

Quoting Patrick McKenzie

Quoting Patrick McKenzie

Getting Your First 10 Customers