Building seen in public - day 2

I'm going to add user signup support for the system. The main reason will be to separate users' data. Currently, everybody can see the stats for everybody else's emails, which is less than ideal.

I decided to use Auth0 for this, one reason is that I have already studied about this a little and weighed the features of a few similar services, and I think Auth0 is going to be a good solution for my use case. The other reason is that it gives me a ready-made login/signup page, so that's one less thing to worry about.

Setting up Auth0

Setting up Auth0 was very easy, in fact, the getting started process basically gives you the code that works for your app based on the type of your app (which is incredible).

However, I tried to enable the Passwordless login features, and despite my efforts, I couldn't get any email with a magic link. So finally decided to skip that feature for now and use the default Universal login the template which asks for user/pass.

There is a warning on the page for social login (Google is enabled by default), and I need to create a Client ID to make it go away. But I think for now I can skip it.

Express OpenID middleware

The following code is basically copy-pasted from the getting started page of Auth0:

const { auth, requiresAuth } = require('express-openid-connect');

const config = {
  authRequired: false,
  auth0Logout: true,
  secret: process.env.AUTH0_SECRET,
  baseURL: process.env.AUTH0_BASEURL,
  clientID: '[client ID]',
  issuerBaseURL: ''

Development vs. production

I didn't want to accidentally leak any secrets, so I decided not to include the Auth0 secret even in the .env file.

This lets me configure production and development environments differently without changing the env file much (the database.env the file is actually committed to git right now).

Integrating Auth0 into the app

This part really surprised me, it was so easy to implement using the express-openid-connect and the requiresAuth function. I could make any existing call authenticated using that middleware.

app.get('/stats', requiresAuth(), async (req, res) => {

The output looks like this:

  nickname: 'Amin',
  name: '[my full name]',
  picture: '[A full path to my profile picture]',
  updated_at: '2021-01-26T16:22:24.707Z',
  email: '[my email address]',
  email_verified: true,
  sub: 'auth0|[some hash]'

In this app, we will be used email to uniquely identify users.

The great thing about the express-openid-connect middleware is that I didn't have to implement any logic for login basically if users try to access any unauthenticated page, they will automatically be redirected to the login page.

That's it, now I can change the database to accommodate users and map the email receipts with the user IDs. Fortunately, by the time of writing the code, there were zero receipts in the database, therefore I didn't have to worry about migrating data.

Database changes

I created a new table called users and made some changes to the receipts and receipt_records to make it work for users. It took me a few try-and-errors, but finally, I was satisfied with the following schema:

CREATE TABLE receipts(
    user_id INTEGER REFERENCES users(id) NOT NULL,
    name TEXT NOT NULL,
    UNIQUE (id, name)
CREATE TABLE receipt_records(
    receipt_id INTEGER REFERENCES receipts(id) NOT NULL,
    record TEXT NOT NULL,
    timestamp TIMESTAMP DEFAULT current_timestamp

Previously the receipt name the field was set to be the PRIMARY KEY, I decided to create IDs for receipts and users instead, this made things cleaner a bit.

Then I had to go through all of the functions dealing with DB queries and make sure they make use of the user_id.

Making use of the user ID in the image path

To make public links to images unique, I decided to prefix the image URLs with the actual user ID in the database, this way I could map the unauthenticated access to the image files to the ID in the database.

So now the image URLs are[uid]/[email name].png

Adding users to the DB

DB joins

Because of the decision to map everything using user ID, I had to make slightly complicated joins like the following:

SELECT as receipt, receipt_records.*
  FROM receipt_records 
  LEFT JOIN receipts ON receipt_id = id
 WHERE user_id = ${user_id} AND = ${receipt}


There was a lot of wrestling involved with pug which I use as the templating language for the pages. I decided to use native html tags as much as possible to avoid those issues. Later on, I might decide to change to something else, possibly ReactJS?

When deployed to production for the first time, the script failed with the following error:

seen_1      | /opt/index.js:58
seen_1      |   })[0].id;
seen_1      |        ^
seen_1      |
seen_1      | TypeError: Cannot read property 'id' of undefined

It was coming from the following code:

  return await db.any('INSERT INTO users(email) VALUES(${email}) ON CONFLICT DO NOTHING RETURNING id', {

Then I realized what the problem is, I was trying to get the first index of the Promise object returned by db.any instead of the result of await, so I had to wrap the await db.any(...) in parenthesis to make that work.

Rendering user info

Lastly, I decided to create a header with the profile pictures and a logout button and add it to all of the existing pages, thankfully it was relatively easy to use the include directive of the pug.

I had to send the user info (`req.oidc.user`) to the render function:

res.status(200).render('create', { user: req.oidc.user, receipt, path: `/${user_id}` });

And then render it:

    <div style="float: right">
        <img style="border-radius: 50%; display: block; width: 50px" src="#{user.picture}"/>
        <a href="/logout">Logout!</a>