Verify users' email addresses when they attempt to sign-up

Lots of changes here:
- Add the GET /verify endpoint
- Email users a secret using MailGun
- Create a PendingAccounts table and record type
- Prefer do-notation for FromRow instances (and in general) instead of the <*>
  or a liftA2 style. Using instances using `<*>` makes the instances depend on
  the order in which the record's fields were defined. When combined with a
  "SELECT *", which returns the columns in whichever order the schema defines
  them (or depending on the DB implementation), produces runtime parse errors
  at best and silent errors at worst.
- Delete bill from accounts.csv to free up the wpcarro@gmail.com when testing
  the /verify route.

1 files changed, 12 insertions, 2 deletions

diff --git a/src/init.sql b/src/init.sql
index 117a3bd06f90..b616fdece52d 100644
--- a/src/init.sql
+++ b/src/init.sql
@@ -10,9 +10,9 @@ DROP TABLE IF EXISTS Accounts;
 DROP TABLE IF EXISTS Trips;
 DROP TABLE IF EXISTS Sessions;
 DROP TABLE IF EXISTS LoginAttempts;
+DROP TABLE IF EXISTS PendingAccounts;
 
 CREATE TABLE Accounts (
--- TODO(wpcarro): Add CHECK(..) constraint
   username TEXT CHECK(LENGTH(username) > 0) NOT NULL,
   password TEXT CHECK(LENGTH(password) > 0) NOT NULL,
   email TEXT CHECK(LENGTH(email) > 0) NOT NULL UNIQUE,
@@ -34,7 +34,8 @@ CREATE TABLE Trips (
 CREATE TABLE Sessions (
   uuid TEXT CHECK(LENGTH(uuid) == 36) NOT NULL,
   username TEXT NOT NULL UNIQUE,
-  tsCreated TEXT CHECK(LENGTH(tsCreated) == 33) NOT NULL, -- 'YYYY-MM-DD HH:MM:SS'
+  -- TODO(wpcarro): Add a LENGTH CHECK here
+  tsCreated TEXT NOT NULL, -- 'YYYY-MM-DD HH:MM:SS'
   PRIMARY KEY (uuid),
   FOREIGN KEY (username) REFERENCES Accounts ON DELETE CASCADE
 );
@@ -46,4 +47,13 @@ CREATE TABLE LoginAttempts (
   FOREIGN KEY (username) REFERENCES Accounts ON DELETE CASCADE
 );
 
+CREATE TABLE PendingAccounts (
+  secret TEXT CHECK(LENGTH(secret) == 36) NOT NULL,
+  username TEXT CHECK(LENGTH(username) > 0) NOT NULL,
+  password TEXT CHECK(LENGTH(password) > 0) NOT NULL,
+  role TEXT CHECK(role IN ('user', 'manager', 'admin')) NOT NULL,
+  email TEXT CHECK(LENGTH(email) > 0) NOT NULL UNIQUE,
+  PRIMARY KEY (username)
+);
+
 COMMIT;