Simple double opt-in web app for Postmark mailing list

If you’re using an email service like Postmark, you’ll have to handle your own list management except unsubscribes and suppression (e.g. from hard bounces). To do this, a little web app will be needed if you want to do things like double opt-in (good idea). You’ll likely then want more features like profile management but the goal of this howto is getting a simple and hopefully secure webapp in place for /subscribe and /confirm (you can get unsubscribes/suppressions from Postmark via web-hooks or an API call).

Tech stack assumptions:

  • sqlite3 for database
  • Sequlieze for migrations and ORM
  • Node.js and Express
  • I use nvim as my edit but you can use whatever you prefer: vscode, vim, emacs, etc.

create express scaffolding

$ mkdir -p ~/git/email.incidentdb.io/
$ cd ~/git/email.incidentdb.io/
$ npx express-generator --view=ejs -v ejs --git
$ npm install

Just to track changes, this is a create time to create a new github repo and then add the repo. I called it email.incidentdb.io and here’s my cheatsheet of commands:

git init
git add -A
git commit -a -m "initial commit"
git branch -M main
git remote add origin git@github.com:ahoog42/email.incidentdb.io.git
git push -u origin main

force fix insecure dependencies

Unfortunately there are vulnerabilities in the generator scaffolding:

which will return something like:

$ npm install
added 54 packages, and audited 55 packages in 1s

4 vulnerabilities (3 high, 1 critical)

To address all issues (including breaking changes), run:
  npm audit fix --force

Run `npm audit` for details.

so let’s force update those with npm audit fix --force:

$ npm audit fix --force
npm WARN using --force Recommended protections disabled.
npm WARN audit Updating ejs to 3.1.9, which is a SemVer major change.
npm WARN audit Updating express to 4.18.2, which is outside your stated dependency range.

added 56 packages, changed 14 packages, and audited 111 packages in 1s

13 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities

install other dependencies

Let’s install some dev dependencies:

  • nodemon to restart server on code change
  • sequelize-cli to handle database migrations
$ npm install nodemon sequelize-cli sequelize-auto --save-dev

And then our sequelize dependencies:

$ npm install sequelize sqlite3 dotenv

test basic app

Finally add the following to your package.json’s scripts property so it looks like this:

  "scripts": {
    "start": "node ./bin/www",
    "start:dev": "DEBUG=email.incidentdb.io nodemon ./bin/www"
  },

We can now make sure the expressjs app is working with:

$ npm run start:dev

and you should get:

$ npm run start:dev

> email.incidentdb.io@0.0.0 start:dev
> DEBUG=email.incidentdb.io nodemon ./bin/www

[nodemon] 3.0.2
[nodemon] to restart at any time, enter `rs`
[nodemon] watching path(s): *.*
[nodemon] watching extensions: js,mjs,cjs,json
[nodemon] starting `node ./bin/www`

You can now use a web browser to verify it’s working:

$ curl localhost:3000
<!DOCTYPE html>
<html>
  <head>
    <title>Express</title>
    <link rel='stylesheet' href='/stylesheets/style.css' />
  </head>
  <body>
    <h1>Express</h1>
    <p>Welcome to Express</p>
  </body>
</html>

Basic data flow

As mentioned, we’re only going to enable to route:

  1. /subscribe which respond to a POST and is (typically) called from a web form
  2. /confirm which will respond to a GET with parameters unique to the email, thus confirming account access

Future enhancements/todos include:

  • implement reCaptcha and other mechanisms on the signup form to prevent spam registrations
  • implement /profile to allow a user to update their profile (e.g. update name, company, etc.)
  • implement nonce functionality to invalidate links after a 1 use or a certain timeout
  • accept web-hooks or pull Postapp API to remove subscriptions for people the unsubscribed or were suppressed. The reason this is not absolutely critical is because that’s maintained on Postmark’s side so the message won’t be sent after an unsubscribe but nice to clean up our data too

Database

We’ll use the seqeulize-cli to setup and manage our database, migrations, etc.

First, let’s customize sequelize-cli with a .sequelizerc file to keep things a bit tidier:

.sequelizerc

// .sequelizerc

const path = require('path');

module.exports = {
  'config': path.resolve('config', 'config.js'),
  'models-path': path.resolve('db', 'models'),
  'seeders-path': path.resolve('db', 'seeders'),
  'migrations-path': path.resolve('db', 'migrations')
};

and now we can run npx sequelize-cli init to create our directory structure:

$ npx sequelize-cli init

Sequelize CLI [Node: 18.17.1, CLI: 6.6.2, ORM: 6.35.2]

Created "config/config.js"
Successfully created models folder at "/Users/hiro/git/email.incidentdb.io/db/models".
Successfully created migrations folder at "/Users/hiro/git/email.incidentdb.io/db/migrations".
Successfully created seeders folder at "/Users/hiro/git/email.incidentdb.io/db/seeders".

Next we’ll modify the default config because we’re only going to use sqlite3. Place the following in config.js:

const fs = require('fs');

module.exports = {
  development: {
    dialect: 'sqlite',
    storage: process.env.DB_PATH,
  }
};

I like this approach because you can use environment variable so as you put in actual secrets and such, nothing sensitive has to be committed to your code repo.

That means that you need to either prepend shell commands with DB_PATH=subs.db or set it in your shell with export DB_PATH=subs.db. In Node.js code, you can use packaged like dotenv to load a .env file as well.

So from here we’ll create two tables, subscriber and subscription.

subscriber table

For subscriber, let’s use sequelize-cli to write most of it for us:

$ npx sequelize-cli model:generate --name subscriber --attributes id:string,name:string,email:string,company:string,confirmedAt:date,nonce:string,nonce_expired:boolean

which should return something like:

Sequelize CLI [Node: 18.17.1, CLI: 6.6.2, ORM: 6.35.2]

New model was created at /Users/hiro/git/email.incidentdb.io/db/models/subscriber.js .
New migration was created at /Users/hiro/git/email.incidentdb.io/db/migrations/20240111035859-create-subscriber.js .

the cli created the following:

'use strict';
/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('subscribers', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      id: {
        type: Sequelize.STRING
      },
      name: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },      
      company: {
        type: Sequelize.STRING
      },
      confirmedAt: {
        type: Sequelize.DATE
      },
      nonce: {
        type: Sequelize.STRING
      },
      nonce_expired: {
        type: Sequelize.BOOLEAN
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('subscribers');
  }
};

which wasn’t exactly what I was looking for but instead of fiddling with cli switches, I think it’s just easier to modify the code. For example:

  • looks like Sequelize will create an auto-increment id field which makes tons of sense but I plan on using a uuidv4 for will just overtake that property. Interesting that it duplicated the id field and didn’t complain.
  • Sequelize will add timestamps which in the past I fought but why? Great to get that out of the box and I figured you could disable them with a flag but looking at the createTable function, it looks like the id and timestamps are hardcoded! ><
  • add additional properties like allowNull: false, etc.

Note: even through we requested a table name of subscriber, looks like sequelzie-cli makes the table name plural, e.g. subscribers. You could change this in the code now before you run the migration.

Here’s what I changed the migration to:

'use strict';
/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('subscriber', {
      id: {
        allowNull: false,
        primaryKey: true,
        type: Sequelize.STRING // uuidv4
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false
      },
      email: {
        type: Sequelize.STRING,
        allowNull: false
      },
      company: {
        type: Sequelize.STRING
      },
      confirmedAt: {
        type: Sequelize.DATE
      },
      nonce: {
        type: Sequelize.STRING
      },
      nonce_expired: {
        type: Sequelize.BOOLEAN
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('subscriber');
  }
};

subscription table

Now we’ll just use the above approach for the subscription table. Here we’ll just track the subscriber_id and the list name (I won’t denormalize further as this is an MVP):

$ npx sequelize-cli model:generate --name subscription --attributes subscriber_id:string,list:string

If someone has an active subscription, they will be in the table. We simple delete that row if they unsubscribe to that list. Here’s the edited migration:

run the migrations

You can now run the migrations with:

$ npx sequelize-cli db:migrate

Sequelize CLI [Node: 18.17.1, CLI: 6.6.2, ORM: 6.35.2]

Loaded configuration file "config/config.js".
Using environment "development".
== 20240111153956-create-subscription: migrating =======
== 20240111153956-create-subscription: migrated (0.004s)

which will run any “un-run” migrations.

Sequelize CLI [Node: 18.17.1, CLI: 6.6.2, ORM: 6.35.2]

Loaded configuration file "config/config.js".
Using environment "development".
== 20240111035859-create-subscriber: migrating =======
== 20240111035859-create-subscriber: migrated (0.005s)

== 20240111153956-create-subscription: migrating =======
== 20240111153956-create-subscription: migrated (0.001s)

Check out their docs (linked above) on how to undo the mot recent or all migrations.

Code

We’ll talk through the nodejs code in this section. First some quick house keeping to ignore the newly created database in our git repo and then add the DB_PATH to .env so we can load it in our code:

$ echo -n '*.db' >> .gitignore
$ echo -n 'DB_PATH=subs.db' >> .env

Obviously check in your source code from time to time with helpful commit messages! :-)

Sequelize models

Even though we used the sequelize-cli to manage the database migrations, for our express app to use the models in code, we’ll need to create them. You can read more on my post [[How to build a CRUD app with Express, Node, SQLite Sequelize]] but here’s the quick steps:

$ npx sequelize-auto -o "./db/models" -d subs.db --dialect sqlite

Now let’s make it easy to connect to the db and load the models by creating ./db/index.js with the following:

// require dotenv
require('dotenv').config();
const { Sequelize, Model, DataTypes } = require('sequelize');

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: process.env.DB_PATH,
  //logging: (...msg) => console.log(msg)
});

const initModels = require('../db/models/init-models');
const models = initModels(sequelize);

module.exports = models;

To test this real quick, you can update the routes/index.js to connect to the database and the console.log the models:

var express = require('express');
var router = express.Router();
const models = require('../db');

/* GET home page. */
router.get('/', function(req, res, next) {
  console.log(models);
  res.render('index', { title: 'Express' });
});

module.exports = router;

now when you hit localhost:3000 you should see something like the following in the logs:

[nodemon] starting `node ./bin/www`
{
  SequelizeMeta: SequelizeMeta,
  subscriber: subscriber,
  subscription: subscription
}
GET / 200 5.975 ms - 207
GET /stylesheets/style.css 304 0.865 ms - -

Subscribe controller

Parameters First Name Last Name Company (Optional) Email