Add postal code data, map FSAs to city names

[davesteinberg]

This PR adds data about postal codes (for all of Canada), ridings (for Ontario only), and the mappings between them to the database, and implements issue #76 by showing the placename for the postal code FSA on each story.

The strategy is to model and add all of the postal codes and riding data in the database, allowing us to retrieve the information along with the stories in a single query. This prevents us from having to consume other services, which could be time consuming or carry cost. It also allowed a lengthy process of mapping FSAs to ridings (by mapping a large number of postal codes and aggregating the results) to be completed in advance. For more explanation and all the gory details, see my Slack post and the postal-data repository, respectively. All the postal code, riding, and mapping data that is the output from that project is captured in a postal-data.json file that's included in this PR. I have added models in the Prisma schema and enhanced the seed.ts script to load that data from the file. If you set the NODE_ENV environment variable to "production", the script will only load that data, not create the fake story data. I'll do that for the preview database once this PR deploys, and then we can do it again for production once it's merged and deployed there.

The one thing that's not ideal is that Prisma doesn't support weak relations, where there may or may not be another entity at the other end of it. Instead, it always uses foreign keys to enforce referential integrity. This won't work for looking up postal code FSAs, which may or may not be there at the point a story referencing one is created. I have created a feature request for this and, in the mean time, there is a workaround, which was suggested to me in this discussion: You can simply remove the relation when running prisma migrate dev and include it at all other times (notably, when running prisma generate). All migrate does with the relations is create the foreign keys, and everything else works without them.

To make this manageable, I have created a simple prisma.sh wrapper script for the prisma CLI. The weak relation fields are marked in the schema.prisma file with //nomigrate comments, and the wrapper script comments those lines out only while running migrate dev. To keep things simple, you can run any other command through the script, just use ./prisma.sh instead of npx prisma. Also, note that you should use ./prisma.sh migrate deploy, not prisma.sh migrate dev --name init, initially on a new database (I updated the README).

In terms of changes to the code, the big thing is that we can no longer use the generated Story type on the client side because it doesn't include a postalCode property for the related PostalCode that we're now selecting in the queries in lib/api/stories.ts However, the correct type (returned by findMany() and findUnique()) can be computed from the type of the select option. I'm doing that in lib/model/story.ts and exporting that as Story, and I've changed all client code to use that type, instead. It works seamlessly and, in fact, it solves one type problem we had previously in pages/story/[id].tsx.

As for visible differences in the UI, you'll see the city/place name and province in place of the FSA on all stories with a recognized FSA (we've data on 1663 of them from all across Canada). You'll also see the the place name and FSA labels above the date on the details page for a story, as originally designed.

[vercel[bot]]

This pull request is being automatically deployed with Vercel (learn more).
To see the status of your deployment, click below or on the icon next to each commit.

🔍 Inspect: https://vercel.com/my-covid-story/www/G7FSTADdpWPr7zrLWZmUqZb49EtV
✅ Preview: https://www-git-dspostal-name-my-covid-story.vercel.app

[davesteinberg]

I have seeded the preview database, so you can now see place names in the branch deployment: https://www-git-dspostal-name-my-covid-story.vercel.app/

[davesteinberg]

I did notice that links from the front page to individual stories are broken on the preview. But once that's sorted, this looks good to me!

Thanks! This seems to be some sort of transient Next/Vercel weirdness. I saw the broken links for a few moments, but now they're working again. Also, I see that the Inter font files are missing again, too. I'm going to try redeploying and see if that helps at all.

[silent1mezzo]

📝Changed routes:

/	/story/[id]
(view full size)	(view full size)

Commit 56fcf0c06ea9fc946306ce625c0e173c55b7ea57 (https://www-oy0vcv9jf-my-covid-story.vercel.app).

[davesteinberg]

Redeploy fixed the fonts, so far so good on the links to individual stories...🤞

[davesteinberg]

Hmm...I'm still seeing links break transiently and inconsistently. There's no apparent errors in the browser or the logs on Vercel. I'm also seeing it in older deployments, e.g. https://www-co1vj4ekv-my-covid-story.vercel.app/story/cknqte35j0105cn5f30sywpam (though the problem might not be happening there again by the time anyone looks at this). Fortunately, I'm not seeing the same problem on prod.

@mwickett Do we have support available from Vercel? It's concerning enough that I would be inclined to open a ticket.

[davesteinberg]

Links seem to be fine again today... 🤷

[davesteinberg]

Since the broken links seem to be related to exhausting connections to the staging DB, not to this PR specifically, I'm going to merge it now. Once it's deployed and the database is migrated, I'll load up the postal code data.