Simon Willison's Weblog: vaccinate-ca

Weeknotes: sqlite-utils updates, Datasette and asgi-csrf, open-sourcing VIAL

2021-06-28T17:23:21+00:00

Some work on sqlite-utils, asgi-csrf, a Datasette alpha and we open-sourced VIAL.

sqlite-utils

Last week's sqlite-utils 3.10 introduced a huge new feature: the ability to run joins directly against CSV and JSON files from the command-line.

I've since released sqlite-utils 3.11 and 3.12, much smaller releases.

3.11 added a new --schema option to the sqlite-utils memory command which lets you see the schema you'll be querying for the imported data:

$ curl 'https://api.github.com/users/dogsheep/repos' | \
  sqlite-utils memory - --schema
CREATE TABLE [stdin] (
   [id] INTEGER,
   [node_id] TEXT,
   [name] TEXT,
   [full_name] TEXT,
   [private] INTEGER,
   [owner] TEXT,
   [html_url] TEXT,
   [description] TEXT,
   ...
   [watchers] INTEGER,
   [default_branch] TEXT
);
CREATE VIEW t1 AS select * from [stdin];
CREATE VIEW t AS select * from [stdin];

3.12 focused on the Python library side of the package. It adds a new method, db.query(sql) which returns an iterator over Python dictionaries representing the results of a query.

This was a pretty obvious missing feature of the library: the rest of sqlite-utils deals with rows that are represented as dictionaries - you pass a list of Python dictionaries to db[table_name].insert_all(list_of_dicts) to create a table with the correct schema, for example. But if you wanted to execute SELECT queries you had to use db.execute() which would return a standard library cursor object which could then return tuples if you called .fetchall() on it.

It was only when I started to work on an interactive Jupyter notebook tutorial for sqlite-utils that I realized how weird it was not to have an equivalent method for reading data out of the database again.

Here's what the new method looks like:

db = Database(memory=True)
db["dogs"].insert_all([
    {"name": "Cleo"},
    {"name": "Pancakes"}
])
for row in db.query("select * from dogs"):
    print(row)
# Outputs:
# {'name': 'Cleo'}
# {'name': 'Pancakes'}

Full documentation here.

asgi-csrf and a Datasette alpha

I'm building a custom Datasette integration for a consulting client at the moment which needs to be able to accept POST form data as part of an API. Datasette has CSRF protection but for this particular project I need to opt-out of that protection for this one endpoint.

I ended up releasing asgi-csrf 0.9 with a new skip_if_scope= mechanism for dynamically disabling CSRF protection based on the incoming ASGI scope. I then shipped a Datasette 0.58a1 alpha release with a new skip_csrf(datasette, scope) plugin hook for plugins to take advantage of that mechanism.

Expect another alpha release shortly to preview the new get_metadata plugin hook contributed by Brandon Roberts. I've decided that alphas are the ideal way to explore new plugin hooks while they are still being developed as it lets projects pip install the alpha while making it clear that the interface may not yet be fully baked.

Open-sourcing VIAL

VIAL is the project I've been working on for VaccinateCA/VaccinateTheStates - see previous posts. It's a Django application which powers a crowd-sourced and scraper-driven effort to catalogue all of the places in the USA that you can get the Covid vaccine - 77,000 and counting right now.

We had always intended to open-source the code and now we have! github.com/CAVaccineInventory/vial is the newly-made-public repository.

I still need to produce a bunch of extra documentation about VIAL, likely including a video introduction to the project. But it's great to have it out there!

Releases this week

sqlite-utils: 3.12 - (80 releases total) - 2021-06-25
Python CLI utility and library for manipulating SQLite databases
datasette: 0.58a1 - (92 releases total) - 2021-06-24
An open source multi-tool for exploring and publishing data
asgi-csrf: 0.9 - (17 releases total) - 2021-06-23
ASGI middleware for protecting against CSRF attacks

TIL this week

Scraping Reddit via their JSON API

Tags: csrf, datasette, asgi, weeknotes, sqlite-utils, vaccinate-ca

Weeknotes: Velma, more Django SQL Dashboard

2021-05-18T00:36:20+00:00

Matching locations for Vaccinate The States, fun with GeoJSON and more improvements to Django SQL Dashboard.

Velma

I described a few weeks ago part of the process we've been using to build Vaccinate The States - a map of every COVID vaccine location in the USA (now at just over 70,000 markers and counting).

Short version: we have scrapers and data ingesters for a whole bunch of different sources (see the vaccine-feed-ingest repository).

Part of the challenge here is how to deal with duplicates - with multiple sources of data, chances are high that the same location will show up in more than on of our input feeds.

So in the past weeks we've been building a new tool code-named Velma to help handle this. It shows our volunteers a freshly scraped location and asks them to either match it to one of our existing locations (based on automated suggestions) or use it to create a brand new location in our database.

I've been working exclusively on the backend APIs for Velma: APIs that return new scraped data and accept and process the human matching decisions from our volunteers.

This week we've been expanding Velma to also cover merging potential duplicate locations within our existing corpus, so I've been building out the APIs for that effort as well.

I've also been working on new export code for making our entire set of locations available to partners and interested outside developers. We hope to launch that fully in the next few days.

geojson-to-sqlite

One of the export formats we are working with is GeoJSON. I have a tool called geojson-to-sqlite which I released last year: this week I released an updated version with the ability to create SpatiaLite indexes and a --nl option for consuming newline-delimited GeoJSON, contributed by Chris Amico.

I've also been experimenting with SpatiaLite's KNN mechanism using geojson-to-sqlite to load in data - here's a TIL showing how to use those tools together.

Django SQL Dashboard

I released the first non-alpha version of this last week and it's started to gain some traction: I've heard from a few people who are trying it out on their projects and it seems to work, so that's good!

I released version 0.14 yesterday with a bunch of fixes based on feedback from users, plus a security fix that closes a hole where users without the execute_sql permission but with access to the Django Admin could modify the SQL in saved dashboards and hence execute their own custom queries.

I also made a bunch of improvements to the documentation, including adding screenshots and demo links to the widgets page.

TIL this week

Releases this week

geojson-to-sqlite: 0.3 - (6 releases total) - 2021-05-17
CLI tool for converting GeoJSON files to SQLite (with SpatiaLite)
django-sql-dashboard: 0.14 - (28 releases total) - 2021-05-16
Django app for building dashboards using raw SQL queries

Tags: projects, geojson, weeknotes, vaccinate-ca, django-sql-dashboard

Adding GeoDjango to an existing Django project

2021-05-03T06:38:12+00:00

Work on VIAL for Vaccinate The States continues.

I talked about matching last week. I've been building more features to support figuring out if a newly detected location is already listed or not, with one of the most significant being the ability to search for locations within a radius of a specific point.

I've experimented with a PostgreSQL/Django version of the classic cos/sin/radians query for this but if you're going to do this over a larger dataset it's worth using a proper spatial index for it - and GeoDjango has provided tools for this since Django 1.0 in 2008!

I have to admit that outside of a few prototypes I've never used GeoDjango extensively myself - partly I've not had the right project for it, and in the past I've also been put off by the difficulty involved in installing all of the components.

That's a lot easier in 2021 than it was in 2008. But VIAL is a project in-flight, so here are some notes on what it took to get GeoDjango added to an existing Django project.

Alex Vandiver has been working with me on VIAL and helped figure out quite a few of these steps.

Activating PostgreSQL

The first step was to install the PostGIS PostgreSQL extension. This can be achieved using a Django migration:

from django.contrib.postgres.operations import CreateExtension
from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ("my_app", "0108_previous-migration"),
    ]

    operations = [
        CreateExtension("postgis"),
    ]

Most good PostgreSQL hosting already makes this extension available - in our case we are using Google Cloud SQL which supports various extensions, including PostGIS. I use Postgres.app for my personal development environment which bundles PostGIS too.

So far, so painless!

System packages needed by GeoDjango

GeoDjango needs the GEOS, GDAL and PROJ system libraries. Alex added these to our Dockerfile (used for our production deployments) like so:

RUN apt-get update && apt-get install -y \
    binutils \
    gdal-bin \
    libproj-dev \
    && rm -rf /var/lib/apt/lists/*

Adding a point field to a Django model

I already had a Location model, which looked something like this:

class Location(models.Model):
    name = models.CharField()
    # ...
    latitude = models.DecimalField(
        max_digits=9, decimal_places=5
    )
    longitude = models.DecimalField(
        max_digits=9, decimal_places=5
    )

I made three changes to this class: I changed the base class to this:

from django.contrib.gis.db import models as gis_models

class Location(gis_models.Model):
    # ...

I added a point column:

    point = gis_models.PointField(
        blank=True,
        null=True,
        spatial_index=True
    )

And I set up a custom save() method to populate that point field with a point representing the latitude and longitude every time the object was saved:

from django.contrib.gis.geos import Point

# ...

    def save(self, *args, **kwargs):
        # Point is derived from latitude/longitude
        if self.longitude and self.latitude:
            self.point = Point(
                float(self.longitude),
                float(self.latitude),
                srid=4326
            )
        else:
            self.point = None
        super().save(*args, **kwargs)

srid=4326 ensures the point is stored using WGS84 - the most common coordinate system for latitude and longitude values across our planet.

Running ./manage.py makemigrations identified the new point Point column and created the corresponding migration for me.

Backfilling the point column with a migration

The .save() method would populate point for changes going forward, but I had 40,000 records that already existed which I needed to backfill. I used this migration to do that:

from django.db import migrations

class Migration(migrations.Migration):

    dependencies = [
        ("core", "0110_location_point"),
    ]

    operations = [
        migrations.RunSQL(
            sql="""
            update location
            set point = ST_SetSRID(
                ST_MakePoint(
                    longitude, latitude
                ),
                4326
            );""",
            reverse_sql=migrations.RunSQL.noop,
        )
    ]

latitude/longitude/radius queries

With the new point column created and populated, here's the code I wrote to support simple latitude/longitude/radius queries:

from django.contrib.gis.geos import Point
from django.contrib.gis.measure import Distance

def search_locations(request):
    qs = Location.objects.filter(soft_deleted=False)
    latitude = request.GET.get("latitude")
    longitude = request.GET.get("longitude")
    radius = request.GET.get("radius")
    if latitude and longitude and radius:
        # Validate latitude/longitude/radius
        for value in (latitude, longitude, radius):
            try:
                float(value)
            except ValueError:
                return JsonResponse(
                    {"error": "latitude/longitude/radius should be numbers"}, status=400
                )
        qs = qs.filter(
            point__distance_lt=(
                Point(
                    float(longitude),
                    float(latitude)
                ),
                Distance(m=float(radius)),
            )
        )
    # ... return JSON for locations

In writing up these notes I realize that this isn't actually the best way to do this, because it fails to take advantage of the spatial index on that column! I've filed myself an issue to switch to the spatial-index-friendly dwithin instead.

Getting CI to work

The hardest part of all of this turned out to be getting our CI suites to pass.

We run CI in two places at the moment: GitHub Actions and Google Cloud Build (as part of our continuous deployment setup).

The first error I hit was this one:

psycopg2.errors.UndefinedFile: could not open extension control file "/usr/share/postgresql/13/extension/postgis.control": No such file or directory

It turns out that's what happens when your PostgreSQL server doesn't have the PostGIS extension available.

Our GitHub Actions configuration started like this:

name: Run tests

on: [push]

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:13
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: vaccinate
        options:
          --health-cmd pg_isready --health-interval 10s --health-timeout 5s --health-retries 5
        ports:
        - 5432:5432
    steps:

The postgres:13 image doesn't have PostGIS. Swapping that out for postgis/postgis:13-3.1 fixed that (using this image).

Our Cloud Build configuration included this:

  # Start up a postgres for tests
  - id: "start postgres"
    name: "gcr.io/cloud-builders/docker"
    args:
      - "run"
      - "-d"
      - "--network=cloudbuild"
      - "-e"
      - "POSTGRES_HOST_AUTH_METHOD=trust"
      - "--name"
      - "vaccinate-db"
      - "postgres"

  - id: "test image"
    name: "gcr.io/cloud-builders/docker"
    args:
      - "run"
      - "-t"
      - "--network=cloudbuild"
      - "-e"
      - "DATABASE_URL=postgres://postgres@vaccinate-db:5432/vaccinate"
      - "${_IMAGE_NAME}:latest"
      - "pytest"
      - "-v"

I tried swapping out that last postgres argument for postgis/postgis:13-3.1, like I had with the GitHub Actions one... and it failed with this error instead:

django.db.utils.OperationalError: could not connect to server: Connection refused Is the server running on host "vaccinate-db" (192.168.10.3) and accepting TCP/IP connections on port 5432?

This one stumped me. Eventually Alex figured out the problem: the extra extension meant the PostgreSQL was taking slightly longer to start - something that was covered in our GitHub Actions configuration by the pg_isready line. He added this step to our Cloud Build configuration:

  - id: "wait for postgres"
    name: "jwilder/dockerize"
    args: ["dockerize", "-timeout=60s", "-wait=tcp://vaccinate-db:5432"]

It uses jwilder/dockerize to wait until the database container starts accepting connections on port 5432.

Next steps

Now that we have GeoDjango I'm excited to start exploring new capabilities for our software. One thing in particular that interests me is teaching VIAL to backfill the county for a location based on its latitude and longitude - the US Census provide a shapefile of county polygons which I use with Datasette and SpatiaLite in my simonw/us-counties-datasette project, so I'm confident it would work well using PostGIS instead.

Releases this week

django-sql-dashboard: 0.11a0 - (22 total releases) - 2021-04-26
Django app for building dashboards using raw SQL queries

TIL this week

Tags: django, geodjango, geospatial, postgresql, weeknotes, vaccinate-ca

A CSV export, JSON import workflow for bulk updating our data

2021-04-28T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

I just added missing counties to around 1200 of our locations using a combination of tricks, and I thought they'd make a good blog post.

County is an optional field on our location model, and we sometimes import new locations without attaching a county to them.

How big is the problem?

I ran this SQL query in our dashboard to figure out how bad the problem was:

select
  state.name,
  count(*) as number_of_locations_with_no_county
from
  location join state on state.id = location.state_id
where
  location.county_id is null
group by
  state.name

I got back this:

name        number_of_locations_with_no_county
Arkansas    1
Arizona     90
California  1
Oregon      1113

I decided to start with Oregon, since I knew that was my fault (I imported a bunch of data for Oregon over a month ago and clearly didn't bother with counties).

Exporting the bad data as CSV

I used another dashboard query to export the location public ID, latitude and longitude for every location in Oregon that was missing a county:

select
  public_id, latitude, longitude
from
  location
where
  state_id = (
    select id from state where name = 'Oregon'
  )
  and county_id is null

I exported those results as CSV, then switched into a Jupyter notebook running on my laptop.

The full script

Here's the script I ran there:

import csv
import httpx
import json

rows = list(csv.DictReader(open("select-public-id--latitude--lof05de6.csv")))

def county_for_point(latitude, longitude):
    return httpx.get("https://us-counties.datasette.io/counties/county_for_latitude_longitude.json?_shape=array", params={
        "latitude": latitude,
        "longitude": longitude,
    }).json()[0]['county_name']

counties = {}

# Progress bar:
from tqdm.notebook import tqdm

for row in tqdm(rows):
    counties[row["public_id"]] = county_for_point(
        row["latitude"], row["longitude"]
    )

update = {
  "update": {
    key: {
      "state": "OR",
      "county": value
    } for key, value in counties.items()
  },
  "revision_comment": "Added missing counties"
}

print(json.dumps(update))

Finding the county for a latitude/longitude point

The county_for_point() function uses my us-counties.datasette.io API, which provides an API for looking up a county based on a latitude and longitude based on US Census shapefiles - source code for that is at simonw/us-counties-datasette.

You can try the API out here: https://us-counties.datasette.io/counties/county_for_latitude_longitude?longitude=-122.676968&latitude=45.266289

Add .json and ?_shape=array to get the result as a JSON array:

https://us-counties.datasette.io/counties/county_for_latitude_longitude.json?longitude=-122.676968&latitude=45.266289&_shape=array

[
  {
    "state_fips": "41",
    "state": "OR",
    "county_fips": "41005",
    "county_name": "Clackamas",
    "COUNTYNS": "01155127",
    "AFFGEOID": "0500000US41005",
    "GEOID": "41005",
    "LSAD": "06",
    "ALAND": 4845034547,
    "AWATER": 31873103
  }
]

Progress bars in Jupyter

I used tqdm to display a progress bar inside my Jupyter notebook. It makes it easy to wrap an iterable Python object in a way that shows a progress bar as it processes the list:

from tqdm.notebook import tqdm

for row in tqdm(rows):
    counties[row["public_id"]] = county_for_point(
        row["latitude"], row["longitude"]
    )

Building JSON to send to our API

The /api/updateLocations API is documented here. It's a bulk update API which accepts a JSON body looking something like this:

{
  "update": {
    "rec9Zc6A08cEWyNpR": {
      "name": "Berkeley Clinic II"
    },
    "lgzgq": {
      "phone_number": "(555) 555-5551"
    }
  },
  "revision_comment": "New details"
}

It lets you apply a partial update to a whole list of locations at once.

In our case we only want to update the county (though we need to send the state too in order to correctly validate that the county name belongs to that state). My Python code to generate the update document looks like this:

update = {
  "update": {
    key: {
      "state": "OR",
      "county": value
    } for key, value in counties.items()
  },
  "revision_comment": "Added missing counties"
}

print(json.dumps(update))

A pretty-printed subset of the output looks like this:

{
    "update": {
        "lrzkd": {
            "state": "AZ",
            "county": "Maricopa"
        },
        "lrzmg": {
            "state": "AZ",
            "county": "Pima"
        },
        "lrzmr": {
            "state": "AZ",
            "county": "Cochise"
        }
    },
    "revision_comment": "Added missing counties"
}

Pasting that into our API explorer

Having generated this API update document, I used our interactive API explorer tool to execute the update:

https://vial.calltheshots.us/api/updateLocations/debug

Almost all of our API endpoints have an interface like this, which is mainly intended for trying things out but also works for making ad-hoc requests like this one.

Tying it together with a saved dashboard

I built a saved dashboard (using the VIAL admin) called locations-with-no-county to tie all of this together.

Initially I thought the dashboard could be used by our web bankers to find locations missing a county - so I had it return ten random locations without a county, so multiple web bankers could use it at the same time with a low risk of overlapping efforts.

Then I realized I could automate it myself, so that aspect of the dashboard turned out not to be useful - though we may well use it for smaller fixing jobs in the future.

https://vial.calltheshots.us/dashboard/locations-with-no-county/

Doing this by generating JSON entirely inside PostgreSQL

I used another variant of this trick last week to add concordance identifiers to our locations, using the /api/updateConcordanceLocations API.

I wanted to add a walgreens:12134 concordance identifier to every one of our locations that was a Walgreens. You can follow my progress in this issue - I used this SQL query to identify all of our Walgreens locations by executing a PostgreSQL regular expression against the name column, then generate the API update JSON using PostgreSQL's JSON functions:

with extracted_idrefs as (
  select
    public_id, name,
    (regexp_match(name, 'Walgreens.*#0*([0-9]{1,8})', 'i')) [1] as idref
  from
    location
  where
    regexp_match(name, 'Walgreens.*#0*([0-9]{1,8})', 'i') is not null
)
select
  json_build_object(
    'update',
    json_object_agg(
      public_id,
      json_build_object('add', json_build_array('walgreens:' || idref), 'comment', name)
    )
  )
from
  extracted_idrefs
where
  public_id not in (
    select
      public_id
    from
      location
    where
      id in (
        select
          location_id
        from
          concordance_location
        where
          concordanceidentifier_id in (
            select
              id
            from
              concordance_identifier
            where
              authority = 'walgreens'
          )
      )
  )

That last where clause avoids generating JSON for locations that already have a walgreens:X concordance identifier.

The output of the query looks something like this:

{
  "update": {
    "lrxzc": {
      "add": [
        "walgreens:10076"
      ],
      "comment": "Walgreens Co. #10076"
    },
    "lrxzd": {
      "add": [
        "walgreens:10082"
      ],
      "comment": "Walgreens Co. #10082"
    },
    "lrxzf": {
      "add": [
        "walgreens:10160"
      ],
      "comment": "Walgreens Co. #10160"
    }
  }
}

As before, this can be executed directly using this API debugging tool: https://vial.calltheshots.us/api/updateLocationConcordances/debug

Tags: postgresql, vaccinate-ca, vaccinate-ca-blog

Weeknotes: Vaccinate The States, and how I learned that returning dozens of MB of JSON works just fine these days

2021-04-26T01:02:22+00:00

On Friday VaccinateCA grew in scope, a lot: we launched a new website called Vaccinate The States. Patrick McKenzie wrote more about the project here - the short version is that we're building the most comprehensive possible dataset of vaccine availability in the USA, using a combination of data collation, online research and continuing to make a huge number of phone calls.

VIAL, the Django application I've been working on since late February, had to go through some extensive upgrades to help support this effort!

VIAL has a number of responsibilities. It acts as our central point of truth for the vaccination locations that we are tracking, powers the app used by our callers to serve up locations to call and record the results, and as-of this week it's also a central point for our efforts to combine data from multiple other providers and scrapers.

The data ingestion work is happening in a public repository, CAVaccineInventory/vaccine-feed-ingest. I have yet to write a single line of code there (and I thoroughly enjoy working on that kind of code) because I've been heads down working on VIAL itself to ensure it can support the ingestion efforts.

Matching and concordances

If you're combining data about vaccination locations from a range of different sources, one of the biggest challenges is de-duplicating the data: it's important the same location doesn't show up multiple times (potentially with slightly differing details) due to appearing in multiple sources.

Our first step towards handling this involved the addition of "concordance identifiers" to VIAL.

I first encountered the term "concordance" being used for this in the Who's On First project, which is building a gazetteer of every city/state/country/county/etc on earth.

A concordance is an identifier in another system. Our location ID for RITE AID PHARMACY 05976 in Santa Clara is receu5biMhfN8wH7P - which is e3dfcda1-093f-479a-8bbb-14b80000184c in VaccineFinder and 7537904 in Vaccine Spotter and ChIJZaiURRPKj4ARz5nAXcWosUs in Google Places.

We're storing them in a Django table called ConcordanceIdentifier: each record has an authority (e.g. vaccinespotter_org) and an identifier (7537904) and a many-to-many relationship to our Location model.

Why many-to-many? Surely we only want a single location for any one of these identifiers?

Exactly! That's why it's many-to-many: because if we import the same location twice, then assign concordance identifiers to it, we can instantly spot that it's a duplicate and needs to be merged.

Raw data from scrapers

ConcordanceIdentifier also has a many-to-many relationship with a new table, called SourceLocation. This table is essentially a PostgreSQL JSON column with a few other columns (including latitude and longitude) into which our scrapers and ingesters can dump raw data. This means we can use PostgreSQL queries to perform all kinds of analysis on the unprocessed data before it gets cleaned up, de-duplicated and loaded into our point-of-truth Location table.

How to dedupe and match locations?

Initially I thought we would do the deduping and matching inside of VIAL itself, using the raw data that had been ingested into the SourceLocation table.

Since we were on a tight internal deadline it proved more practical for people to start experimenting with matching code outside of VIAL. But that meant they needed the raw data - 40,000+ location records (and growing rapidly).

A few weeks ago I built a CSV export feature for the VIAL admin screens, using Django's StreamingHttpResponse class combined with keyset pagination for bulk export without sucking the entire table into web server memory - details in this TIL.

Our data ingestion team wanted a GeoJSON export - specifically newline-delimited GeoJSON - which they could then load into GeoPandas to help run matching operations.

So I built a simple "search API" which defaults to returning 20 results at a time, but also has an option to "give me everything" - using the same technique I used for the CSV export: keyset pagination combined with a StreamingHttpResponse.

And it worked! It turns out that if you're running on modern infrastructure (Cloud Run and Cloud SQL in our case) in 2021 getting Django to return 50+MB of JSON in a streaming response works just fine.

Some of these exports are taking 20+ seconds, but for a small audience of trusted clients that's completely fine.

While working on this I realized that my idea of what size of data is appropriate for a dynamic web application to return more or less formed back in 2005. I still think it's rude to serve multiple MBs of JavaScript up to an inexpensive mobile phone on an expensive connection, but for server-to-server or server-to-automation-script situations serving up 50+ MB of JSON in one go turns out to be a perfectly cromulent way of doing things.

Export full results from django-sql-dashboard

django-sql-dashboard is my Datasette-inspired library for adding read-only arbitrary SQL queries to any Django+PostgreSQL application.

I built the first version last month to help compensate for switching VaccinateCA away from Airtable - one of the many benefits of Airtable is that it allows all kinds of arbitrary reporting, and Datasette has shown me that bookmarkable SQL queries can provide a huge amount of that value with very little written code, especially within organizations where SQL is already widely understood.

While it allows people to run any SQL they like (against a read-only PostgreSQL connection with a time limit) it restricts viewing to the first 1,000 records to be returned - because building robust, performant pagination against arbitrary SQL queries is a hard problem to solve.

Today I released django-sql-dashboard 0.10a0 with the ability to export all results for a query as a downloadable CSV or TSV file, using the same StreamingHttpResponse technique as my Django admin CSV export and all-results-at-once search endpoint.

I expect it to be pretty useful! It means I can run any SQL query I like against a Django project and get back the full results - often dozens of MBs - in a form I can import into other tools (including Datasette).

TIL this week

Releases this week

django-sql-dashboard: 0.10a1 - (21 total releases) - 2021-04-25
Django app for building dashboards using raw SQL queries

Tags: csv, django, django-admin, postgresql, projects, vaccines, weeknotes, vaccinate-ca, django-sql-dashboard

Weeknotes: The Aftermath

2021-04-18T22:19:18+00:00

Some tweets that effectively illustrate my week:

"... and nothing broke!" - several days later I can confirm that a few things did indeed break, but thankfully nothing catastrophically so!
— Simon Willison (@simonw) April 16, 2021

My resume: Migrated legacy Rails user model to external API in 30 days with no impact to customers.

Reality: 90% migrated fine, 10% edge cases where I played whack-a-mole for an entire week and deploying multiple times daily. 😂 https://t.co/L6Akkvr5pB
— Damon Cortesi (@dacort) April 16, 2021

Last week we went live with VIAL, the replacement backend I've been building for VaccinateCA. This meant we went from having no users to having a whole lot of users, and all of the edge-cases and missing details quickly started to emerge.

So this week I've been almost exclusively working my way through those. Not much to report otherwise!

TIL this week

Releases this week

django-sql-dashboard: 0.8a2 - (17 total releases) - 2021-04-14
Django app for building dashboards using raw SQL queries

Tags: weeknotes, vaccinate-ca

Porting VaccinateCA to Django

2021-04-12T05:18:48+00:00

As I mentioned back in February, I've been working with the VaccinateCA project to try to bring the pandemic to an end a little earlier by helping gather as accurate a model as possible of where the Covid vaccine is available in California and how people can get it.

The key activity at VaccinateCA is calling places to check on their availability and eligibility criteria. Up until last night this was powered by a heavily customized Airtable instance, accompanied by a custom JavaScript app for the callers that communicated with the Airtable API via some Netlify functions.

Today, the flow is powered by a new custom Django backend, running on top of PostgreSQL.

The thing you should never do

Here's one that took me fifteen years to learn: "let's build a new thing and replace this" is hideously dangerous: 90% of the time you won't fully replace the old thing, and now you have two problems!
- Simon Willison (@simonw) June 29, 2019

Replacing an existing system with a from-scratch rewrite is risky. Replacing a system that is built on something as flexible as Airtable that is evolving on a daily basis is positively terrifying!

Airtable served us extremely well, but unfortunately there are hard limits to the number of rows Airtable can handle and we've already bounced up against them and had to archive some of our data. To keep scaling the organization we needed to migrate away.

We needed to build a matching relational database with a comprehensive, permission-controlled interface for editing it, plus APIs to drive our website and application. And we needed to do it using the most boring technology possible, so we could focus on solving problems directly rather than researching anything new.

It will never cease to surprise me that Django has attained boring technology status! VaccineCA sits firmly in Django's sweet-spot. So we used that to build our replacement.

The new Django-based system is called VIAL, for "Vaccine Information Archive and Library" - a neat Jesse Vincent bacronym.

We switched things over to VIAL last night, but we still have activity in Airtable as well. I expect we'll keep using Airtable for the lifetime of the organization - there are plenty of ad-hoc data projects for which it's a perfect fit.

The most important thing here is to have a trusted single point of truth for any piece of information. I'm not quite ready to declare victory on that point just yet, but hopefully once things settle down over the next few days.

Data synchronization patterns

The first challenge, before even writing any code, was how to get stuff out of Airtable. I built a tool for this a while ago called airtable-export, and it turned out the VaccinateCA team were using it already before I joined!

airtable-export was already running several times an hour, backing up the data in JSON format to a GitHub repository (a form of Git scraping). This gave us a detailed history of changes to the Airtable data, which occasionally proved extremely useful for answering questions about when a specific record was changed or deleted.

Having the data in a GitHub repository was also useful because it gave us somewhere to pull data from that wasn't governed by Airtable's rate limits.

I iterated through a number of different approaches for writing importers for the data.

Each Airtable table ended up as a single JSON file in our GitHub repository, containing an array of objects - those files got pretty big, topping out at about 80MB.

I started out with Django management commands, which could be passed a file or a URL. A neat thing about using GitHub for this is that you can use the "raw data" link to obtain a URL with a short-lived token, which grants access to that file. So I could create a short-term URL and paste it directly to my import tool.

I don't have a good pattern for running Django management commands on Google Cloud Run, so I started moving to API-based import scripts instead.

The pattern that ended up working best was to provide a /api/importRecords API endpoint which accepts a JSON array of items.

The API expects the input to have a unique primary key in each record - airtable_id in our case. It then uses Django's update_or_create() ORM method to create new records if they were missing, and update existing records otherwise.

One remaining challenge: posting 80MB of JSON to an API in one go would likely run into resource limits. I needed a way to break that input up into smaller batches.

I ended up building a new tool for this called json-post. It has an extremely specific use-case: it's for when you want to POST a big JSON array to an API endpoint but you want to first break it up into batches!

Here's how to break up the JSON in Reports.json into 50 item arrays and send them to that API as separate POSTs:

json-post Reports.json \                              
   "https://example.com/api/importReports" \
   --batch-size 50

Here are some more complex options. Here we need to pass an Authorization: Bearer XXXtokenXXX API key header, run the array in reverse, record our progress (the JSON responses from the API as newline-delimited JSON) to a log file, set a longer HTTP read timeout and filter for just specific items:

% json-post Reports.json \                              
   "https://example.com/api/importReports" \
  -h Authorization 'Bearer XXXtokenXXX' \
  --batch-size 50 \
  --reverse \
  --log /tmp/progress.txt \
  --http-read-timeout 20 \
  --filter 'item.get("is_soft_deleted")'

The --filter option proved particularly useful. As we kicked the tires on VIAL we would spot new bugs - things like the import script failing to correctly record the is_soft_deleted field we were using in Airtable. Being able to filter that input file with a command-line flag meant we could easily re-run the import just for a subset of reports that were affected by a particular bug.

--filter takes a Python expression that gets compiled into a function and passed item as the current item in the list. I borrowed the pattern from my sqlite-transform tool.

The value of API logs

VaccineCA's JavaScript caller application used to send data to Airtable via a Netlify function, which allowed additional authentication to be added built using Auth0.

Back in February, the team had the bright idea to log the API traffic to that function to a separate base in Airtable - including full request and response bodies.

This proved invaluable for debugging. It also meant that when I started building VIAL's alternative implementation of the "submit a call report" API I could replay historic API traffic that had been recorded in that table, giving me a powerful way to exercise the new API with real-world traffic.

This meant that when we turned on VIAL we could switch our existing JavaScript SPA over to talking to it using a fully tested clone of the existing Airtable-backed API.

VIAL implements this logging pattern again, this time using Django and PostgreSQL.

Given that the writable APIs will recieve in the low thousands of requests a day, keeping them in a database table works great. The table has grown to 90MB so far. I'm hoping that the pandemic will be over before we have to worry about logging capacity!

We're using PostgreSQL jsonb columns to store the incoming and returned JSON, via Django's JSONField. This means we can do in-depth API analysis using PostgreSQL's JSON SQL functions! Being able to examine returned JSON error messages or aggregate across incoming request bodies helped enormously when debugging problems with the API import scripts.

Storing the original JSON

Today, almost all of the data stored in VIAL originated in Airtable. One trick that has really helped build the system is that each of the tables that might contain imported data has both an airtable_id nullable column and an import_json JSON field.

Any time we import a record from Airtable, we record both the ID and the full, original Airtable JSON that we used for the import.

This is another powerful tool for debugging: we can view the original Airtable JSON directly in the Django admin interface for a record, and confirm that it matches the ORM fields that we set from that.

I came up with a simple pattern for Pretty-printing all read-only JSON in the Django admin that helps with this too.

Staying as flexible as possible

The thing that worried me most about replacing Airtable with Django was Airtable's incredible flexibility. In the organization's short life it has already solved so many problems by adding new columns in Airtable, or building new views.

Is it possible to switch to custom software without losing that huge cultural advantage?

This is the same reason it's so hard for custom software to compete with spreadsheets.

We've only just made the switch, so we won't know for a while how well we've done at handling this. I have a few mechanisms in place that I'm hoping will help.

The first is django-sql-dashboard. I wrote about this project in previous weeknotes here and here - the goal is to bring some of the ideas from Datasette into the Django/PostgreSQL world, by providing a read-only mechanism for constructing SQL queries, bookmarking and saving the results and outputting simple SQL-driven visualizations.

We have a lot of SQL knowledge at VaccinateCA, so my hope is that people with SQL will be able to solve their own problems, and people who don't know SQL yet will have no trouble finding someone who can help them.

In the boring technology model of things, django-sql-dashboard counts as the main innovation token I'm spending for this project. I'm optimistic that it will pay off.

I'm also leaning heavily on Django's migration system, with the aim of making database migrations common and boring, rather than their usual default of being rare and exciting. We're up to 77 migrations already, in a codebase that is just over two months old!

I think a culture that evolves the database schema quickly and with as little drama as possible is crucial to maintaining the agility that this kind of organization needs.

Aside from the Django Admin providing the editing interface, everything that comes into and goes out of VIAL happens through APIs. These are fully documented: I want people to be able to build against the APIs independently, especially for things like data import.

After seeing significant success with PostgreSQL JSON already, I'm considering using it to add even more API-driven flexbility to VIAL in the future. Allowing our client developers to start collecting a new piece of data from our volunteers in an existing JSON field, then migrating that into a separate column once it has proven its value, is very tempting indeed.

Open source tools we are using

An incomplete list of open source packages we are using for VIAL so far:

pydantic - as a validation layer for some of the API endpoints
social-auth-app-django - to integrate with Auth0
django-cors-headers
python-jose - for JWTs, which were already in use by our Airtable caller app
django-reversion and django-reversion-compare to provide a diffable, revertable history of some of our core models
django-admin-tools - which adds a handy customizable menu to the admin, good for linking to additional custom tools
django-migration-linter - to help avoid accidentally shipping migrations that could cause downtime during a deploy
pytest-django, time-machine and pytest-httpx for our unit tests
sentry-sdk, honeycomb-beeline and prometheus-client for error logging and observability

Want to help out?

VaccinateCA is hiring! It's an interesting gig, because the ultimate goal is to end the pandemic and put this non-profit permanently out of business. So if you want to help end things faster, get in touch.

VaccinateCA is hiring a handful of engineers to help scale our data ingestion and display by more than an order of magnitude.

If you'd like to register interest:https://t.co/BSvi40sW1M

Generalists welcome. Three subprojects; Python backend/pedestrian front-end JS.
- Patrick McKenzie (@patio11) April 7, 2021

TIL this week

Releases this week

json-post: 0.2 - (3 total releases) - 2021-04-11
Tool for posting JSON to an API, broken into pages
airtable-export: 0.7.1 - (10 total releases) - 2021-04-09
Export Airtable data to YAML, JSON or SQLite files on disk
django-sql-dashboard: 0.6a0 - (13 total releases) - 2021-04-09
Django app for building dashboards using raw SQL queries

Tags: django, django-admin, migrations, postgresql, weeknotes, airtable, vaccinate-ca, boring-technology

VIAL: Preparing for some collaborative testing

2021-04-01T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

With the Airtable limits fast approaching, I'm going to start leaning heavily on people to help verify that VIAL can do the jobs that it needs to do.

I have a few features that I need to land first, but over the weekend I'll be spinning up documents (and probably videos as well) showing how VIAL can address our core use-cases around data corrections, call scheduling and call QA.

I'll be asking people to try these flows out on staging, provide feedback on what's working and what's missing, and tagging other people to encourage them to kick the tires too.

My hope is that this exercise will help nail down the final steps in the roadmap needed to get VIAL to live-for-California status.

Keeping things flexible

The biggest challenge in building VIAL has always been the need to compete with Airtable. This week I learned that it's even worse than that: it needs to compete with Google Sheets too! Our QA efforts, lead by Kim, have some extremely sophisticated Google Sheets to help evaluate our new callers.

One reason Airtable and Google Sheets are such powerful tools is that they enable decentralized, permission-less problem solving. We've been taking full advantage of this, spinning up smart new features across a dispersed group of people.

Switching to our own custom software will inevitably dent this freedom. Part of the challenge with VIAL is to make this dent as small as possible.

Mechanisms we have in place for this include:

The SQL dashboard mechanism, described in Django SQL Dashboard for reporting - this is aimed to replace many of the ad-hoc reports we have created in Airtable, by providing the ability for anyone with SQL knowledge to quickly create custom dashboard reports. Our organization has a far higher level of SQL proficiency than most, so even if you can't write the SQL for a report yourself you should be able to quickly find someone who can.
The ability to export CSV data from both those dashboards and directly from the Django admin. This should help fill in other functionality gaps while we spin up custom code.
The importLocations API means anyone with an API key can write scripts to import locations into VIAL. We can add more scripts like thus one as we figure out what is needed
I'm also interested in doing more with tagging - our Availability Tags have worked really well already, and indízate that other mechanisms based on staff members creating custom tags could be useful too.

I also plan to experiment with JSON storage (using PostgreSQL JSONB fields) to enable us to capture new data from our frontend apps without first having to build out and deploy new backend APIs.

Tags: vaccinate-ca, vaccinate-ca-blog

The Airtable formulas at the heart of everything

2021-03-23T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

While working on building a Counties.json API endpoint for VIAL I realized I wasn't entirely sure how the "Total reports" and "Yeses" numbers in this piece of JSON were calculated:

    {
      "County": "Glenn County",
      "County vaccination reservations URL": "https://www.countyofglenn.net/news/press-release-public-health-public-information-public-notice/20210301/covid-19-vaccine-interest",
      "Facebook Page": "https://www.facebook.com/GlennCountyHHSA/",
      "Notes": "(Updated: March 22) **Eligibility**: 65+, A...",
      "Total reports": 11,
      "Twitter Page": "https://twitter.com/glenncountyoes",
      "Vaccine info URL": "https://www.countyofglenn.net/dept/health-human-services/public-health/covid-19/covid-19-vaccine-information",
      "Yeses": 3,
      "id": "rec0QOd7EXzSuZZvN"
    }

This lead down the fascinating rabbit-hole that is our Airtable formulas. Numbers like this are calculated using a combination of Airtable Rollups and Airtable Formulas, which invisibly form the heart of our entire organization.

Of particular interest: the "Latest report yes?" column on Locations, currently defined like this:

IF(
  OR(
    SEARCH("Vaccinating essential workers", ARRAYJOIN({Availability Info})) != "",
    SEARCH("Scheduling second dose only", ARRAYJOIN({Availability Info})) != "",
    SEARCH("Yes", ARRAYJOIN({Availability Info})) != ""
  ),
  1, 0
)

Here are screenshots I gathered as I followed them back to figure out how they worked:

County "Yeses":

County total reports:

County percentage yes:

These rollups use formulas on the Locations table:

Latest report:

Number of reports:

Latest report yes?

And a bonus one: here's is_callable_now which I need to better understand how our call targetting works:

AND(
  OR(
    {Do not call until} = BLANK(),
    {Do not call until} < NOW()
  ),
  OR(
    {Next available to app flow} = BLANK(),
    {Next available to app flow} < NOW()
  ),
  NOT({is_soft_deleted}),
  NOT({do_not_call}),
  {Phone number}
)

Tags: airtable, vaccinate-ca, vaccinate-ca-blog

VIAL is now live, plus django-sql-dashboard

2021-03-15T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

Our new Django backend has now officially graduated from preview mode! We've been running it to collect caller reports for Oregon for over a week now, and today we finally turned off the old Heroku app and promoted https://vial.calltheshots.us/ to be the place that our caller app writes to.

We also have https://vial-staging.calltheshots.us/ as a staging environment.

Calls made in California are still being logged directly to Airtable. The next big milestone for VIAL will be replacing Airtable for our California calls. That's going to be my focus for this week.

django-sql-dashboard for reporting

This weekend I span up a new package which acts as a Django-based imitation of (and research playground for) my Datasette project.

django-sql-dashboard takes some of the key ideas from Datasette - the ability to use raw SQL queries in a safe, read-only, time limited environment to build bookmarkable interfaces - and turns it into a dashboard for applications written in Django and running against PostgreSQL.

It's part of my ongoing goal to replace Airtable while avoiding the loss of the flexibility that has made Airtable so valuable to us.

If you are a staff user, you can access it at https://vial.calltheshots.us/dashboard/. The default interface lets you start running PostgreSQL SQL queries against a subset of our database tables:

It borrows a number of key features from Datasette. Firstly, queries can be bookmarked and shared. Any time you submit new queries you get back a URL with signed parameters, which you can share with other staff users. The page in the screenshot is at https://vial.calltheshots.us/dashboard/?sql=InNlbGVjdCBjb3VudCgqKSBmcm9tIGxvY2F0aW9uIg:1lM2mA:0uepYBAAxILOU_-jOrm12grM965gk83KazIAKUtgMmw&sql=InNlbGVjdCAqIGZyb20gbG9jYXRpb24i:1lM2mA:_tVz6msUDzgWYpVYZeZ7jgwo8YuoTLzTJCBploNucTY

Secondly, you can use named parameters in your queries - select * from location where state_id = (select id from state where name = %(state)s) for example - and the dashboard will extract those parameters out and turn them into form fields.

https://vial.calltheshots.us/dashboard/?sql=InNlbGVjdCAqIGZyb20gbG9jYXRpb24gd2hlcmUgc3RhdGVfaWQgPSAoc2VsZWN0IGlkIGZyb20gc3RhdGUgd2hlcmUgbmFtZSA9ICUoc3RhdGUpcyki%3A1lM2p5%3AwxWUfq1Vf6FkEPjCerWsEm7ljkyPEbhDVjsorHZ2bt4&state=Oregon

Unlike Datasette, it allows multiple SQL queries to be embedded in the same page. This means you can build quite complex dashboards, all sharing the same user-configurable parameter.

The most exciting feature though is custom widgets. If you write a SQL query that returns columns with specific names, custom widgets will kick in to render that data in formats other than a table.

This is best illustrated by the dashboard I built here: https://vial.calltheshots.us/dashboard/numbers-by-state/?state_name=California - this is an example of a "stored dashboard" which has been written to the database.

Note how the following SQL query is rendered as a "big number":

select 'Number of locations in ' || %(state_name)s as label,
count(*) as big_number
from location where state_id = (select id from state where name = %(state_name)s)

And the bar chart at the bottom of the page is rendered automatically for this SQL query, because it returns columns called bar_quantity and bar_label:

select count(*) as bar_quantity,
to_char(created_at, 'YYYY-MM-DD') as bar_label
from reports
where reports.location_id in
(select id from location where state_id = (select id from state where name = %(state_name)s))
group by to_char(created_at, 'YYYY-MM-DD')

You can even create SQL statements that return dynamically concatenated markdown or HTML (run through Bleach to avoid any nasty XSS problems).

select '# Chart: number of reports filed per day in ' || %(state_name)s as markdown

This ability to construct dashboards by saving a list of SQL queries feels really powerful to me - it takes some of the best aspects of Datasette (which currently only works against SQLite databases) and makes them available to us within our Django/PostgreSQL app, protected by the Django authentication mechanism.

You can follow ongoing developement of django-sql-dashboard in the issues at https://github.com/simonw/django-sql-dashboard/issues

Tags: postgresql, vaccinate-ca, django-sql-dashboard, vaccinate-ca-blog

Weeknotes: tableau-to-sqlite, django-sql-dashboard

2021-03-14T07:35:18+00:00

This week I started a limited production run of my new backend for Vaccinate CA calling, built a tableau-to-sqlite import tool and started working on a subset of Datasette for PostgreSQL and Django called django-sql-dashboard.

Vaccinate CA backend progress

My key project at the moment is building out a new Django-powered backend for the Vaccinate CA call reporting application - where real human beings constantly call pharmacies and medical sites around California to build a comprehensive guide to where the Covid vaccine is available.

As of this week, the new backend is running for a subset of the overall call volume. It's exciting! It's also a reminder that the single hardest piece of logic in any crowdsourcing-style application is the logic that gives a human being their next task. I'm continuing to evolve that logic, which is somewhat harder when the system I'm modifying is actively being used.

tableau-to-sqlite

The Vaccinate CA project is constantly on the lookout for new sources of data that might indicate locations that have the vaccine. Some of this data is locked up in Tableau dashboards, which are notoriously tricky to scrape.

When faced with problems like this, I frequently turn to GitHub code search: I'll find a unique looking token in the data I'm trying to wrangle and run searches to see if anyone on GitHub has written code to handle it.

In doing so, I came across Tableau Scraper - an open source Python library by Bertrand Martel which does a fantastic job of turning a Tableau dashboard into a Pandas DataFrame.

Writing a Pandas DataFrame to a SQLite database is a one-liner: df.to_sql("table-name", sqlite3.connect(db_path)). So I spun up a quick command-line wrapper around the TableuaScraper class called tableau-to-sqlite which lets you do the following:

% tableau-to-sqlite tableau.db https://results.mo.gov/t/COVID19/views/VaccinationsDashboard/Vaccinations

Considering how much valuable data is trapped in government Tableau dashboards I'm really excited to point this tool at more sources. The README includes tips on combining this with sqlite-utils to get a CSV or JSON export which can then be tracked using Git scraping.

django-sql-dashboard

I'm continuing to ponder the idea of getting Datasette to talk to PostgreSQL in addition to SQLite, but in the meantime I have a growing Django application that runs against PostgreSQL and a desire to build some quick dashboards against it.

One of Datasette's key features is the ability to bookmark a read-only SQL query and share that link with other people. It's SQL injection attacks repurposed as a feature, and it's proved to be incredibly useful over the past few years.

Here's an example from earlier this week where I wanted to see how many GitHub issues I had opened and then closed within 60 seconds. The answer is 17!

django-sql-dashboard is my highly experimental exploration of what that idea looks like against a PostgreSQL database, wrapped inside a Django application

The key idea is to support executing read-only PostgreSQL statements with a strict timelimit (set using PostgreSQL's statement_timeout setting, described here). Users can execute SQL directly, bookmark and share queries and save them to a database table in order to construct persistent dashboards.

It's very early days for the project yet, and I'm still not 100% convinced it's a good idea, but early signs are very promising.

A fun feature is that it lets you have more than one SQL query on the same page. Here's what it looks like running against my blog's database, showing a count query and the months in which I wrote the most blog entries:

Releases this week

hacker-news-to-sqlite: 0.4 - (5 releases total) - 2021-03-13
Create a SQLite database containing data pulled from Hacker News
django-sql-dashboard: 0.1a3 - (3 releases total) - 2021-03-13
Django app for building dashboards using raw SQL queries
datasette-ripgrep: 0.7 - (11 releases total) - 2021-03-11
Web interface for searching your code using ripgrep, built as a Datasette plugin
tableau-to-sqlite: 0.2 - (3 releases total) - 2021-03-11
Fetch data from Tableau into a SQLite database

TIL this week

Tags: django, postgresql, projects, sql, weeknotes, vaccinate-ca, django-sql-dashboard

APIs for importing locations

2021-03-09T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

An important aspect of the new backend is the ability to import new locations.

These can come from a bunch of different sources. I've previously written Django management command scripts for this, but these are fiddly to run from a permissions perspective and don't necessarily play well with Cloud Run.

So I decided to build an /api/importLocations API. This can take a single or multiple locations, and can either create new locations or update existing locations based on a provided unique import reference (import_ref) value.

The API is documented in full here: https://github.com/CAVaccineInventory/django.vaccinate/blob/f78afbd1e5bbba4ddc4722c9a592dfd004cb143e/docs/api.md#post-apiimportlocations

You can see how it developed in issue 98

API keys

As part of building this API I needed a simple authentication mechanism. I've added a Django admin page for creating API keys, with a required description field. This can be used to create an API key for use with the new API.

This mechanism differs from the JWT tokens used by the caller app API, mainly because API tokens used for import scripts need to not expire - and also don't need to be tied to specific users.

A few more APIs

Some of the fields that can be passed to the import location API need to accept values from a specific list: county, location_type and provider_type all have this requirement.

I added APIs that return valid values for these:

https://vaccinateca-preview.herokuapp.com/api/counties/CA - try other state codes too
https://vaccinateca-preview.herokuapp.com/api/locationTypes
https://vaccinateca-preview.herokuapp.com/api/providerTypes

Tags: vaccinate-ca, vaccinate-ca-blog

New call queue ready to test. Also geography.

2021-03-07T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

I just shipped the first working preview version of the new /api/requestCall API [#54] - the API that our caller app uses to get the next location that the caller should be contacting (and lock it so that other users don't call it at the same time).

I made a 1m45s video demonstrating the new API and showing how you can use the admin tools to both queue up calls and check that the call queue logic is correctly claiming calls that you are working on:

Video here: https://user-images.githubusercontent.com/9599/110281717-150c1c00-7f92-11eb-886f-c4d1a20f99fd.mp4

Shorter version of that video: you can add locations to the "Call Requests" queue using the dropdown action menu at the top of https://vaccinateca-preview.herokuapp.com/admin/core/location/

You can then look at the queue at https://vaccinateca-preview.herokuapp.com/admin/core/callrequest/

Then you can request calls from that queue at https://vaccinateca-preview.herokuapp.com/api/requestCall/debug (make sure you have a fresh JWT by logging out and in of the admin) - every call you request should show your name as having claimed it in https://vaccinateca-preview.herokuapp.com/admin/core/callrequest/

You can also use the admin action tools on https://vaccinateca-preview.herokuapp.com/admin/core/callrequest/ to clear any claims should you run out of test items in the queue

Geography: the counties of Oregon

I also wrote scripts to import 263 locations that might have the vaccine in the state of Oregon - original data sourced from VaccineFinder.

In doing so I ran into an interesting problem: the VaccineFinder data is very high quality, but the one field it was missing was county: each location has an addreess, a city, a zip code and a latitude and longitude, but no indication of which county it's in.

Counties are a pretty important part of our data model. How could I best derive the counties for each of these locations?

Here's the issue. I ended up going for the most fun of all of the routes: comparing the latitude/longitude points with geographic polygons representing the different counties.

Thankfully I had most of the pieces for this already. A few years ago I figured out how to build a location-to-timezone API using shapefiles and the SpatiaLite extension for SQLite. The US Census offer a high quality shapefile package for US counties at https://www.census.gov/geographies/mapping-files/time-series/geo/carto-boundary-file.html

So I built a Datasette instance that can turn latitudes and longitudes into counties. You can see how I built it in my simonw/us-counties-datasette repository, but here's the API itself:

https://us-counties.datasette.io/counties/county_for_latitude_longitude_with_map?longitude=-122.676968&latitude=45.266289

Add .json to the path to get that back as JSON.

I wrote a ./manage.py backfill_location_counties script (code here) which loops through every location we have that is missing a county, calls that API and updates the record with the corresponding county.

This raised another problem: we need County records in our database for the counties of Oregon. And while we're at it we should populate the counties for all of the other states as well.

Finding a comprehensive list of US counties that included their full FIPS codes was surprisingly difficult. Then I realized I already had one: the US Census shapefile I was using had all of the data we needed.

I wrote a SQL view to transform the shapefile data into a more convenient CSV format with just the data we needed and wrote a ./manage.py import_counties script (here) to import that CSV data directly into our database.

Which almost completely worked... except it found some counties that didn't correspond to states in our States table. You guessed it... Puerto Rico, Guam, American Samoa and a few other places were being disenfranchised again.

I've filed an issue to fix that in the future!

Next up: test, then start making calls

I need help to test the new API: I don't have deep enough understanding of how the caller app works to know if I've messed up something important. Once we've had a few people exercise it for a while I think we're ready to point a copy of the caller app at it and start making some calls through it!

Tags: geospatial, vaccinate-ca, vaccinate-ca-blog

Weeknotes: Datasette and Git scraping at NICAR, VaccinateCA

2021-03-07T07:29:00+00:00

This week I virtually attended the NICAR data journalism conference and made a ton of progress on the Django backend for VaccinateCA (see last week).

NICAR 2021

NICAR stands for the National Institute for Computer Assisted Reporting - an acronym that reflects the age of the organization, which started teaching journalists data-driven reporting back in 1989, long before the term "data journalism" became commonplace.

This was my third NICAR and it's now firly established itself at the top of the list of my favourite conferences. Every year it attracts over 1,000 of the highest quality data nerds - from data journalism veterans who've been breaking stories for decades to journalists who are just getting started with data and want to start learning Python or polish up their skills with Excel.

I presented an hour long workshop on Datasette, which I'm planning to turn into the first official Datasette tutorial. I also got to pre-record a five minute lightning talk about Git scraping.

I published the video and notes for that yesterday. It really seemed to strike a nerve at the conference: I showed how you can set up a scheduled scraper using GitHub Actions with just a few lines of YAML configuration, and do so entirely through the GitHub web interface without even opening a text editor.

Pretty much every data journalist wants to run scrapers, and understands the friction involved in maintaining your own dedicated server and crontabs and storage and backups for running them. Being able to do this for free on GitHub's infrastructure drops that friction down to almost nothing.

The lightning talk lead to a last-minute GitHub Actions and Git scraping office hours session being added to the schedule, and I was delighted to have Ryan Murphy from the LA Times join that session to demonstrate the incredible things the LA Times have been doing with scrapers and GitHub Actions. You can see some of their scrapers in the datadesk/california-coronavirus-scrapers repo.

VaccinateCA

The race continues to build out a Django backend for the VaccinateCA project, to collect data on vaccine availability from people making calls on that organization's behalf.

The new backend is getting perilously close to launch. I'm leaning heavily on the Django admin for this, refreshing my knowledge of how to customize it with things like admin actions and custom filters.

It's been quite a while since I've done anything sophisticated with the Django admin and it has evolved a LOT. In the past I've advised people to drop the admin for custom view functions the moment they want to do anything out-of-the-ordinary - I don't think that advice holds any more. It's got really good over the years!

A very smart thing the team at VaccinateCA did a month ago is to start logging the full incoming POST bodies for every API request handled by their existing Netlify functions (which then write to Airtable).

This has given me an invaluable tool for testing out the new replacement API: I wrote a script which replays those API logs against my new implementation - allowing me to test that every one of several thousand previously recorded API requests will run without errors against my new code.

Since this is so valuable, I've written code that will log API requests to the new stack directly to the database. Normally I'd shy away from a database table for logging data like this, but the expected traffic is the low thousands of API requests a day - and a few thousand extra database rows per day is a tiny price to pay for having such a high level of visibility into how the API is being used.

(I'm also logging the API requests to PostgreSQL using Django's JSONField, which means I can analyze them in depth later on using PostgreSQL's JSON functionality!)

YouTube subtitles

I decided to add proper subtitles to my lightning talk video, and was delighted to learn that the YouTube subtitle editor pre-populates with an automatically generated transcript, which you can then edit in place to fix up spelling, grammar and remove the various "um" and "so" filler words.

This makes creating high quality captions extremely productive. I've also added them to the 17 minute Introduction to Datasette and sqlite-utils video that's embedded on the datasette.io homepage - editing the transcript for that only took about half an hour.

TIL this week

Tags: data-journalism, django-admin, youtube, datasette, weeknotes, git-scraping, vaccinate-ca, nicar

The simplest possible call queue

2021-03-06T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

Today I've been working on the queue calling mechanism for the new Django backend.

We want to do a trial run of the system as soon as possible, so I opted to create the simplest possible version of this that could work.

The Django app is going to work a little bit different from our existing Airtable system. The Airtable system defines a number of different filtered views for locations that we want to call, and leaves it to our caller app to load in rows from those views, select a random location from the options and "lock" that location so no-one else is given it to call for at least twenty minutes.

For the new system, we're going to switch to having an explicit "locations to call" list, which our staff can add locations to and manipulate. This will continue to evolve over time - there's already discussion of having multiple queues, and assigning different callers to different queues depending on their status and trainining.

For the moment though, the simplest thing is to have a database table with a list of locations that need to be called in it.

I implemented the first version of this in issue 70. I'm using Django's admin actions mechanism, which allows you to add bulk actions to the admin change list for any model.

The neat thing about admin actions is that you can apply them to multiple rows in the Django admin action interface: by clicking their checkboxes, by shift-clicking a range of checkboxes or with a "select all" option that can apply to the current page or every row that matches the current set of filters.

Combined with a rich set of filters this is a really powerful way of applying bulk actions to items in your database. The admin actions framework then provides a Django ORM queryset to your action code, which can act on it however it wants.

My first version of the code adds a new admin action for each of the "call request reasons" in the database (currently four, but more to come shortly).

Here's an animated demo showing how the new mechanism works:

Next up: implement the /api/requestCall API that the caller app uses to fetch the next location that the user should call! #54

The Django app on Google Cloud

Alex Vandiver has done a superb job getting the Django app in a state where it can run on our Google Cloud infrastructure. Most of today's commit activity was from Alex, and in addition to having continuous deployment running via Google Cloud Build and Cloud Run thanks to Alex we also have improvements like isort, python-dotenv and the (new to me) django-migration-linter.

Tags: django-admin, vaccinate-ca, vaccinate-ca-blog

Replaying logs to exercise the new API

2021-03-03T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

22 days ago n1mmy pushed a change to help.vaccinate which logged full details of inoming Netlify function API traffic to an Airtable database.

What an asset that is! The Airtable table over here currently contains over 9,000 logged API calls, including the full JSON POST body, when the call was receieved and which authenticated user made the call.

This morning I exported that data as CSV from Airtable, and wrote a Python script to replay those requests against my new imitation implementation of the API.

Here's what that script looks like running against my localhost development server:

You can track the work in this issue - the replay script helped me get to a place where every single report from the past 22 days can be safely ingested by the new API, with the exception of a tiny number of reports against locations which have since been deleted (which isn't supposed to happen - we try to soft-delete rather than full-delete things - but apparently a few deletes had slipped through).

API logging

Since the Airtable API logs have so clearly proved their value, Jesse proposed using the same trick for the Django app. I implemented that today: the full incoming request body and outgoing response are now recorded in an ApiLog model in Django. You can see those in the Django admin here: https://vaccinateca-preview.herokuapp.com/admin/api/apilog/

Here's the ORM model and the view decorator that logs requests.

Unit tests for the new API

I added tests for the submitReport API. The tests are driven by example JSON fixtures - so far I've created two of those, but I hope that having them in this format will make it really easy to add more as we find edge-cases in the API and expand it with new features.

Those API test fixtures live in vaccinate/api/test-data/submitReport. Here's the test code that executes them.

Documentation for the new API

I wrote API documentation! You can find that here.

Now that the API is documented I intend to update the documentation in lock-step with changes made to the API itself - using the pattern where every commit includes the change, the tests for the change AND the documentation for the change in a single unit.

Dual-writing to Django

The combination of the replay script and the unit tests has left me feeling pretty confident that the replacement API is ready to start accepting traffic.

The plan is to run the new system in parallel with Airtable for a few days to thoroughly test it and make sure it covers everything we need. Our Netlify functions offer a great place to do this, so this afternoon I submitted a pull request to help.vaccinate to silently dual-write incoming API requests to the Django API, catching and logging any exceptions without intefering with the rest of the application flow.

Testing this locally helped me identify some bugs in the way the Django app verified JWT tokens that originated with the help.vaccinate application.

Everything else

Here are my other commits from today.

Tags: apis, logging, vaccinate-ca, vaccinate-ca-blog

API ready for testing, first video status update

2021-03-02T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

To celebrate GitHub's new support for drag-and-dropping mp4 files into Markdown, I decided to switch things around a little and include a video update with today's blog entry.

Here's a demonstration of where I've got to with the Django+PostgreSQL backend project - as a 2m33s MP4 video:

[Video omitted as it includes screenshots of names and email addresses for our reporters]

That's a shame, it looks like it doesn't show a preview, just a link. I'll add a screenshot to make this more visually interesting.

The big news today is that I have a working initial version of the submitReport API, along with an API explorer for testing it out!

Visit https://vaccinateca-preview.herokuapp.com/api/submitReport/debug to try it out.

You need a JWT token. The easiest way to get one of those is to log in to the https://vaccinateca-preview.herokuapp.com/ site (or log out and log back in) and then visit the https://vaccinateca-preview.herokuapp.com/api/submitReport/debug page again - this will pre-fill the JWT field with your current token.

Then you can paste in example POST bodies from our logs at https://airtable.com/tblvSiTbFMdCxv0Bq/viwJE9fQEfeHtPScq?blocks=hide and see what happens!

Here's an animated GIF demo of the API explorer in case you're too busy to watch the 2m33s video.

More examples you can copy and paste into the demo:

{
    "Location": "recaQlVkkI1rNarvx",
    "Availability": [
        "No: may be a vaccination site in the future"
    ],
    "Notes": "March 2: recommended to check their website for new availability in a week or more",
    "Internal Notes": ""
}

{
    "Location": "recgDrq7aQMo0M5x7",
    "Appointment scheduling instructions": "www.walgreens.com",
    "Availability": [
        "Yes: vaccinating 65+",
        "Yes: appointment required",
        "Vaccinating essential workers"
    ],
    "Notes": "Check the Walgreens site regularly to see when appointments open up.",
    "Internal Notes": ""
}

{
    "Location": "recPns4QkB3Vrh1Bp",
    "Appointments by phone?": true,
    "Appointment scheduling instructions": "(888) 909-5232 - Press 1 if you meet the criteria (65+ y/o and tier 1 workers)",
    "Availability": [
        "Yes: vaccinating 65+",
        "Yes: appointment required"
    ],
    "Notes": "",
    "Internal Notes": ""
}

{
    "Location": "recOUvNBttZSHegD5",
    "Availability": [
        "No: may be a vaccination site in the future"
    ],
    "Notes": "3/2 - They do not have the vaccine at this time but they said to check the Walgreens.com site for updates.\nFeb 27: not sure when they'll have the vaccines but in the meantime, it's recommended to establish yourself on their website (or smartphone app) so that you can be ready to make an appt",
    "Internal Notes": ""
}

{
    "Location": "recKoseUfWFeK9TjB",
    "Availability": [
        "No: will never be a vaccination site"
    ],
    "Notes": "",
    "Internal Notes": "Press 4 for pharmacy.  They said they were at first going to had COVID vaccines, but they changed their minds.\n"
}

You can also track progress on the API in issue 25.

Everything else I worked on today can be found in the commit history. I wrote up the trick I'm using for timezones in the Django Admin in this TIL.

Tags: vaccinate-ca, vaccinate-ca-blog

Drawing the rest of the owl

2021-03-01T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

My ambitious goal for the day was to get the new Django/PostgreSQL preview into a state where we could start sending example API requests to it from the help.vaccinate app.

I didn't quite get that far, but I made a lot of progress. Here are today's commits. I've also created a milestone for this goal, which is currently 6 issues down, 6 to go.

A few highlights from today:

I renamed CallReport to Report (#26) after realizing that some of the "reports" we make about the vaccine status of a location do not come from a phone call - they may come from reading a trusted website, or even from a site visit.
I changed AvailabilityTag to have new fields for the group (yes/no/skip for the moment, with space for more in the future) and a slug field for values such as skip_call_back_later and vaccinating_50_plus - so we don't need to send around human strings that we might want to change later (#21). Here's a screenshot of the new model:

I did a bunch of work on the Auth0 implementation - including reworking the model a bit (#30) and finishing the work on using it for the Django Admin (#8). Any user with the Vaccinate CA Staff Auth0 role (should be everyone with an @vaccinateca.com email address as-of a few days ago) can now visit https://vaccinateca-preview.herokuapp.com/ and click the sign-in link to access the admin, in read-only mode. We can open up further edit permissions as we decide what things need to be editable.
I finished implementing the logic for applying an AppointmentTag to an imported report based on the data in the Airtable record. This was quite tricky - see issue #20 for blow-by-blow details of how this works.

Tomorrow morning things should get much more exciting: after some discussion on #25 I now know what the first version of the API will look like - essentially an imitation of the Netlify function API documented here - so I'll be building out a working version of that. Follow that issue for progress reports.

Tags: django, vaccinate-ca, vaccinate-ca-blog

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

Django admin customization, JSON in our PostgreSQL

2021-02-25T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

My progress slowed a bit today as I started digging into some things I'm less familiar with - but I've found some tricks that I think will help us out a lot.

Django admin customization

In the past I've used the Django admin mostly as a database debugging tool, on the basis that once you get deep into building out an interface that's more than just a very basic CMS you're better off rolling something from scratch.

Today my opinion changed. I think the Django admin may be the solution to a LOT of our problems, with very little extra customization.

I was looking at a simple feature request: show a summary of calls made by a specific reporter. It turns out adding a custom templated block of text to an existing Django admin "change item" page is trivial, using the pattern I wrote up here:

The implementation for this is tiny. We can use this pattern to add SO much depth to our admin pages - and Django's default permission system is robust enough that we can give users access to these pages without them being able to make edits.

I also added a column to the reporters table showing the number of calls each reporter has made, and made that column sortable! Implementation here.

Once we're fully on Django we'll be able to turn around features like this in very little time. You don't need to be a Django expert to build these either - a tiny bit of Python and HTML knowledge should be enough to productively modify this interface.

JSON in our PostgreSQL

My main goal for the day was to tighten up the call reports importer script I wrote yesterday.

My big breakthrough on this came after a long and super-valuable conversation with Nicholas Schiefer, who's been heavily involved in the growth of our Airtable schema ever since the project started.

Our Airtable data is complicated, because the way we write data to it has constantly evolved. Before the launch of help.vaccinateca the data was all entered through a custom Airtable app, and many of the fields we are capturing now weren't being captured just a few weeks ago.

Since writing a one-off importer that patches over all of these differences in a single go is virtually impossible, we decided to try an alternative track: my importer now saves the entire original Airtable JSON to a PostgreSQL JSON column (using Django 3.1's brand new JSONField).

I taught the Django Admin to pretty-print the JSON (implementation here):

The most obvious value of this is in debugging - it's much easier to look at a record now and compare it to the Airtable version.

More importantly: if we make a mistake in the importer code today and don't notice for six months, that's fine! We can re-backfill against the new lessons we have learned using the Airtable JSON data that we've already stored.

The cost? ~20,000 database records with a few extra KB of data stored against them. That's totally worth it.

Querying JSON

PostgreSQL has a bunch of features for querying into JSON fields which I haven't really explored before. I decided to try them out.

They're incredible. Here's a query that shows the callers who have made the most calls, based on extracting the {"Reported by": {"name": "NAME"}} nested field from that JSON column:

select
    jsonb_extract_path(
        airtable_json, 'Reported by', 'name'
    ) as name,
    count(*) as n
from call_report
group by name
order by n desc;

Even more exciting... here's a query that counts the keys that have been used in ALL of the JSON returned from Airtable:

SELECT
    jsonb_object_keys(airtable_json) AS key, count(*)
FROM call_report GROUP BY key;

And here's what it outputs:

JSON key	Times used
Affiliation (from Location)	21589
airtable_createdTime	21589
airtable_id	21589
Appointments by phone?	466
Appointment scheduling instructions	2526
auth0_reporter_id	1583
auth0_reporter_name	1583
auth0_reporter_roles	1583
Availability	21589
County (from Location)	21589
Do not call until	3116
external_reports_base_external_report_id	3139
Hour	21589
ID	21589
Internal Notes	18258
is_latest_report_for_location	21589
is_pending_review	18
Location	21589
location_id	21589
location_latest_eva_report_time	13443
location_latest_report_id	21589
location_latest_report_time	21354
Location Type (from Location)	21588
Name (from Location)	21589
Notes	3359
Number of Reports (from Location)	21589
parent_eva_report	958
parent_external_report	2410
Phone	82
Reported by	21589
report_id	21589
Report Type	21589
soft-dropped-column: Vaccines available?	15948
time	21589
tmp_eva_flips	21589
Vaccine demand	733
Vaccine demand notes	4

This is amazingly useful data for the importer script that I'm writing!

The query also executes in about 800ms against the cheapest PostgreSQL database server that Heroku offer - doing a deep full table scan against all 22,000 imported records.

Based on how powerful is, I'm now thinking that we should go all-in on JSON in our database. Imagine if every scraper we were running dumped its full scraped JSON data into PostgreSQL - we could join arbitrary scraped data against our other tables to figure out if there are any new locations.

My biggest concern about replacing Airtable is that we'll lose the amazing flexibility it's given us. I think JSON columns can help bridge that gap.

Call targeting: the most interesting problem

Another topic that came out of my conversation with Nicholas: I had not seen quite how ingenious the way call targeting works is. This is SUCH a smart usage of Airtable!

The short version: call targeting (the logic that decides which number a volunteer should be asked to call) is powered by Airtable views, with really clever application of Airtable's filters to help build up the call lists.

We need to maintain our ability to smartly target where the calls go, and ideally make it even better. This is going to be a really fun problem to solve!

Tags: django, django-admin, postgresql, vaccinate-ca, vaccinate-ca-blog

Importing data from Airtable into Django, plus a search engine for all our code

2021-02-24T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

I made a bunch of progress on the Django backend prototype-that-soon-won’t-be-a-prototype today.

Importing data from Airtable

My goal for the day was to get some real data into the prototype, imported from Airtable. I’ve now done that with the two most important tables: Locations (aka places that people can go to get the vaccine) and CallReports, created by volunteers making phone calls.

I achieved this by writing two new Django management commands:

./manage.py import_airtable_locations (code, test) populates the Django Locations model by pulling data from the Locations table in Airtable.
./manage.py import_airtable_reports (code, test) populates the Django CallReports model using data from the Reports table in Airtable.

Both of these importers follow the same pattern: you can point them directly at a Locations.json file you’ve already downloaded:

./manage.py import_airtable_locations \
  --json-file=Locations.json

Or you can use a GitHub personal access token to have them load the data directly from our airtable-data-backup repository, provided you have access to that.

./manage.py import_airtable_locations \
  --github-token=xxxx

I built the GitHub token mechanism to make it easy to run this command on a server, without having to mess around uplooading JSON files first. Since the prototype is running on Heroku I can pull a fresh import into it directly by running the following command:

% heroku run bash -a vaccinateca-preview
Running bash on ⬢ vaccinateca-preview... up, run.3026 (Hobby)
~ $ vaccinate/manage.py import_airtable_locations --github-token xxx
Skipping rec0xZ5EaKnnynfDa [name=CVS Pharmacy® & Drug Store at 25272 Marguerite Pkwy, Mission Viejo, CA 92692], reason=No latitude
Skipping rec7nHXCuSYRR61V0 [name=None], reason=No name
Skipping rec8Xk6kn4SvAKeEm [name=None], reason=No name
Skipping recCYZZRJCRlXykun [name=None], reason=No name
Skipping recJt0iQbqmglF0XL [name=Dignity Health (Woodland)], reason=No county

The command outputs a line for each item it fails to import. This turns out to be an extremely useful way to spot invalid data - locations with no name, latitude or county for example.

The script successfully imported 7038 locations, skipping just 17.

My call report importer needs a lot more work. It imported 21,029 call records, but skipped 962. More details in the GitHub issue thread.

Django Admin customization

I also made some tweaks to the Django admin. Here’s a screenshot of the call records list as it stands now.

And here's the locations table:

SSO with Auth0

This isn't fully working yet, but it's nearly there. I've integrated Auth0 SSO with the Django app, with the goal being that any staff member can use Auth0 to sign in to the Django Admin panel and view and modify the data there.

You can try it out on the https://vaccinateca-preview.herokuapp.com/ page - once it's fully working, you'll be able to sign in and then visit https://vaccinateca-preview.herokuapp.com/admin/ to interact with the data.

Here's the ongoing issue thread. I ended up creating a brand new role in Auth0 called "Vaccinate CA Staff" - I've figured out how to access those roles from the Django app when a user signs in, so I can then use membership of that role to control access to the admin panel.

Even more ambitious goals

Since the Django backend work is going at a very healthy pace, I'm extending my ambitions for it a bit.

The goal of the app is to replace Airtable as the point of truth for the data collected by our calling volunteers AND as the data source behind the public-facing https://www.vaccinateca.com/ site.

Once I've made some improvements to the schema informed by the data importing project, I think the next step will be to spin up just enough of an API endpoint that the calling app can start writing to Django in parallel to writing to Airtable.

This can be done inside the Netlify function used by the app. I'd like that function to continue writing to Airtable but also to write to the Django/PostgreSQL stack, wrapped in an error handler so failures there are ignored.

Then we can run the Django app as a silent partner to Airtable for a few days and compare the results gathered by the two, to gain confidence before switching over from one to the other.

Getting that running will be my goal for next week. If all goes well we may find we can make the full switch to the Django backend within a couple of weeks.

Regular expression code search across all of our repos

I love code search. I particularly like being able to search code with regular expressions, and then share links to those searches with other engineers.

The best code search tool I’ve ever used is ripgrep. A few months ago I built a simple web wrapper around ripgrep called datasette-ripgrep. This evening I deployed a copy of it against the source code from nine of our code repos:

You can access the search engine here (nicer URL coming soon):

https://vaccinateca-ripgrep-j7hipcg4aq-uc.a.run.app/

You'll need to sign in with GitHub - I'm protecting the site using datasette-auth-github configured to only allow in members of the CAVaccineInventor GitHub organization.

Once you've signed in, try this example search:

https://vaccinateca-ripgrep-j7hipcg4aq-uc.a.run.app/-/ripgrep?pattern=fetch%5C%28%7CfetchJsonFromEndpoint&glob=*.js

That runs a search for fetch\(|fetchJsonFromEndpoint across all of our *.js files, which shows us everywhere we are making an HTTP reuest using either fetch() or our own fetchJsonFromEndpoint() function.

The repo for the search engine is here.

Tags: django, django-admin, airtable, vaccinate-ca, vaccinate-ca-blog

Spinning up a new Django app to act as a backend for VaccinateCA

2021-02-23T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

My goal by the end of this week is to have a working proof of concept for a Django + PostgreSQL app that can replace Airtable as the principle backend for the https://www.vaccinateca.com/ site. This proof of concept will allow us to make a go or no-go decision and figure out what else needs to be implemented before we can start using it to track calls.

I'm calling it a "prototype" and a "proof of concept", but my career has taught me that prototypes often end up going into production - so I'm building it with that in mind.

Today I started building that app. The repo is currently https://github.com/CAVaccineInventory/django.vaccinate though we are likely to rename it soon - possibly to VIAL (for Vaccine Information Archive and Library) - Jesse is good at actually relevant codenames!

Here's what I have so far:

Proof of concept for Auth0 SSO - mostly from following their tutorial. You can try that out on the staging site homepage at https://vaccinateca-preview.herokuapp.com/
The very beginnings of a test suite - built using pytest-django. The tests run against PostgreSQL and I had to figure out how to do that inside GitHub Actions - here's my TIL.
The tests run in GitHub Actions! Continous Integration - should work against pull requests too.
... which means we can take the next step and go for Continuous Deployment. Every commit that passes the tests is now deployed instantly to the staging environment.
The staging environment itself is currently on Heroku, because they make it ridiculously easy to setup Continuous Deployment - it's literally a checkbox in their admin panel. I wrote about this a few years ago: How to set up world-class continuous deployment using free hosted tools. It's likely we'll move this to Google Cloud at some point since other VaccinateCA stuff is running there. I know how to run Continous Deployment using Google Cloud Run so that could be a good option here.
Errors now get logged to a new project in the VaccinateCA Sentry instance.

And the biggest thing: I've implemented a set of Django models against the SQL schema that has been coming together in this pull request. These are exposed in the Django Admin (just with default settings, no customization yet) in the staging environment.

You can try those out by visiting https://vaccinateca-preview.herokuapp.com/admin/ and signing in with username demo and password demo (this account will be deleted the second we have any real data in the prototype).

I wrote data migrations to insert states and insert counties - you can see the results in the admin here (counties) and here (states).

Some engineering principles

I've invested a lot of effort today in getting some fundamental things set up: a test suite, continuous integration, continuous deployment, and a detailed and up-to-date README.

The reason I'm investing that effort now is that I know from experience that these things are incredibly valuable, and very easy to implement at the start of a project... but become massively harder the longer you delay them. Adding comprehensive tests, documentation and CI to a six month old project can take weeks. Adding them to a project that is just starting takes just a few hours.

I also plan to lean very heavily on the Django migrations system.

I've worked at companies in the past where database migrations - any kind of schema change - are slow, rare and exciting. This has horrible knock-on effects: engineers will go to great lengths to avoid adding a column to a table, which can lead to a rapid acretion of technical debt.

I want schema changes to be quick, common and boring. Django's migration system - especially against PostgreSQL, which can execute schema changes inside transactions - is ideally suited to this. I want to start using it agressively as early as possible, to ensure we have a culture that says "yes" to schema changes and executes them promptly and frequently.

Next steps

I want to get some real data into the system! I'm going to lock down the security a bit more, then take some exports from Airtable, convert them to the new schema and load them into the prototype. This will allow us to really start kicking the tires on it.

I'm tracking all of the work on the Django app in the issues for that repository.

Tags: continuous-deployment, continuous-integration, django, django-admin, postgresql, vaccinate-ca, vaccinate-ca-blog

Getting started

2021-02-22T17:00:00+00:00

Originally posted to my internal blog at VaccinateCA

Here we go then... I've signed up to work on this project full-time, four days a week!

Several years into my time at Eventbrite I started an internal engineering blog, and it quickly proved itself to be really valuable. The great thing about an internal blog is that it gives you somewhere to post status updates on existing projects, thoughts on what you're going to work on next and even ad-hoc documentation on things that you learn along the way.

I don't know if I'll post here every day, and I don't intend to spend more than fifteen minutes on each post - but I think it's a good habit to establish early on.

What I'm going to be doing

My initial goal, as directed by Jesse, is to help move VaccinateCA beyond Airtable as quickly as possible.

Airtable is an amazing product, so this is not a trivial undertaking. Every startup founder needs to watch out that they don't end up accidentally competing against an Excel spreadsheet, because the spreadsheet will probably win. Airtable is already like a spreadsheet but better.

But... if we're going to scale this thing to 10x its current size we need to gain complete ownership of our data.

My plan for this first week is to spin up a full fidelity Django + PostgreSQL prototype of an alternative stack, to help decide as quickly as possible if that's the right path to go. A lesson I've learned from my career is that prototypes inevitable end up in production, so I build them with that in mind - the code I write will be tested, documented and ready to become not-a-prototype with as little additional effort as possible.

Why Django and PostgreSQL? Because they are boring technology, and that's a huge bonus for this kind of project.

I'm also going to be talking to the Airtable power users within the organization to make sure I understand what works, what doesn't and what the weird use-cases are for the existing stack that I might not have figured out.

What I've done

I've started building a few things agaist the existing public API, mainly because I think by building and this is a good way to help get familiar with the high level of what already exists.

https://vaccinateca.datasette.io/ is a public (initially private) Datasette instance providing an interactive interface against the public API data. Datasette is my principle open source project at the moment - you can learn more about that at https://datasette.io/ - and you can see how this particular Datasette works by visiting its repo at https://github.com/simonw/vaccinate-ca-datasette
https://github.com/CAVaccineInventory/vaccinate-ca-history is a private repo that's doing something weird and, I hope, valuable. It uses GitHub Actions to pull the latest copy of the public API data every ten minutes and commits any changes back to its own repository. It uses csv-diff (actually a new feature I just added to csv-diff) to try to generate a human-readable commit message. The key feature here becomes the commit history, which shows at a finely grained level what got changed and when.
I started this internal blog

What I need to do next

Short term: talk to people! Meet people who I'll be working with, understand what's been built so far and where everything is, use those conversations to help me decide what I should work on next.

Onboarding

So far I'm on:

Slack
Discord
Airtable
Auth0
The GitHub organization
Google account
Honeycomb (via the new Google account)

Tags: vaccinate-ca, vaccinate-ca-blog, boring-technology