~jan0sch/smederee

Showing details for patch b79828c692c091d6e8563b3b567618bcbfde8886.
2022-07-12 (Tue), 2:37 PM - Jens Grassel - b79828c692c091d6e8563b3b567618bcbfde8886

Account: Introduce verified_email column and flag

Summary of changes
11 files modified with 151 lines added and 243 lines removed
  • modules/hub/src/it/scala/de/smederee/hub/AuthenticationMiddlewareTest.scala with 0 added and 83 removed lines
  • modules/hub/src/it/scala/de/smederee/hub/BaseSpec.scala with 126 added and 0 removed lines
  • modules/hub/src/it/scala/de/smederee/hub/DoobieAuthenticationRepositoryTest.scala with 0 added and 83 removed lines
  • modules/hub/src/it/scala/de/smederee/hub/DoobieSignupRepositoryTest.scala with 2 added and 64 removed lines
  • modules/hub/src/it/scala/de/smederee/hub/Generators.scala with 5 added and 4 removed lines
  • modules/hub/src/main/resources/db/migration/V1__base_tables.sql with 2 added and 0 removed lines
  • modules/hub/src/main/scala/de/smederee/hub/Account.scala with 3 added and 1 removed lines
  • modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala with 1 added and 1 removed lines
  • modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala with 1 added and 2 removed lines
  • modules/hub/src/main/scala/de/smederee/hub/SignupRoutes.scala with 6 added and 1 removed lines
  • modules/hub/src/test/scala/de/smederee/hub/Generators.scala with 5 added and 4 removed lines
diff -rN -u old-smederee/modules/hub/src/it/scala/de/smederee/hub/AuthenticationMiddlewareTest.scala new-smederee/modules/hub/src/it/scala/de/smederee/hub/AuthenticationMiddlewareTest.scala
--- old-smederee/modules/hub/src/it/scala/de/smederee/hub/AuthenticationMiddlewareTest.scala	2025-02-03 04:59:02.850649632 +0000
+++ new-smederee/modules/hub/src/it/scala/de/smederee/hub/AuthenticationMiddlewareTest.scala	2025-02-03 04:59:02.854649640 +0000
@@ -40,89 +40,6 @@
     val _              = flyway.clean()
   }
 
-  private def connectToDb(cfg: SmedereeHubConfig): Resource[IO, java.sql.Connection] =
-    Resource.make(
-      IO.delay(java.sql.DriverManager.getConnection(cfg.database.url, cfg.database.user, cfg.database.pass))
-    )(c => IO.delay(c.close()))
-
-  /** Create the given account in the database.
-    *
-    * @param account
-    *   The account to be created.
-    * @param hash
-    *   A password hash to be stored.
-    * @param unlockToken
-    *   An optional unlock token to be stored.
-    * @param attempts
-    *   Optional number of failed login attempts.
-    * @return
-    *   The number of affected database rows.
-    */
-  @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!")
-  private def createAccount(
-      account: Account,
-      hash: PasswordHash,
-      unlockToken: Option[UnlockToken],
-      attempts: Option[Int]
-  ): IO[Int] =
-    connectToDb(configuration).use { con =>
-      for {
-        statement <- IO.delay {
-          unlockToken match {
-            case None =>
-              con.prepareStatement(
-                """INSERT INTO "accounts" (uid, name, email, password, failed_attempts, created_at, updated_at) VALUES(?, ?, ?, ?, ?, NOW(), NOW())"""
-              )
-            case Some(_) =>
-              con.prepareStatement(
-                """INSERT INTO "accounts" (uid, name, email, password, failed_attempts, locked_at, unlock_token, created_at, updated_at) VALUES(?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())"""
-              )
-          }
-        }
-        _ <- IO.delay(statement.setObject(1, account.uid))
-        _ <- IO.delay(statement.setString(2, account.name.toString))
-        _ <- IO.delay(statement.setString(3, account.email.toString))
-        _ <- IO.delay(statement.setString(4, hash.toString))
-        _ <- IO.delay(statement.setInt(5, attempts.getOrElse(1)))
-        _ <- IO.delay {
-          unlockToken.foreach { token =>
-            statement.setString(6, token.toString)
-          }
-        }
-        r <- IO.delay(statement.executeUpdate())
-        _ <- IO.delay(statement.close())
-      } yield r
-    }
-
-  /** Create the given user session in the database.
-    *
-    * @param session
-    *   The session that shall be created. The corresponding user account must exist!
-    * @return
-    *   The number of affected database rows.
-    */
-  @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!")
-  private def createUserSession(session: Session): IO[Int] =
-    connectToDb(configuration).use { con =>
-      for {
-        statement <- IO.delay(
-          con.prepareStatement(
-            """INSERT INTO "sessions" (id, uid, created_at, updated_at) VALUES(?, ?, ?, ?)"""
-          )
-        )
-        _ <- IO.delay(statement.setString(1, session.id.toString))
-        _ <- IO.delay(statement.setObject(2, session.uid))
-        _ <- IO.delay(
-          statement.setTimestamp(3, java.sql.Timestamp.from(session.createdAt.toInstant))
-        )
-        _ <- IO.delay(
-          statement.setTimestamp(4, java.sql.Timestamp.from(session.updatedAt.toInstant))
-        )
-        r <- IO.delay(statement.executeUpdate())
-        _ <- IO.delay(statement.close())
-      } yield r
-    }
-
   test("extractSessionId must return the session id") {
     (genSignAndValidate.sample, genSessionId.sample) match {
       case (Some(signAndValidate), Some(sessionId)) =>
diff -rN -u old-smederee/modules/hub/src/it/scala/de/smederee/hub/BaseSpec.scala new-smederee/modules/hub/src/it/scala/de/smederee/hub/BaseSpec.scala
--- old-smederee/modules/hub/src/it/scala/de/smederee/hub/BaseSpec.scala	2025-02-03 04:59:02.850649632 +0000
+++ new-smederee/modules/hub/src/it/scala/de/smederee/hub/BaseSpec.scala	2025-02-03 04:59:02.854649640 +0000
@@ -69,4 +69,130 @@
     }.unsafeRunSync()
   }
 
+  /** Provide a resource with a database connection to allow db operations and proper resource release later.
+    *
+    * @param cfg
+    *   The application configuration.
+    * @return
+    *   A cats resource encapsulation a database connection as defined within the given configuration.
+    */
+  protected def connectToDb(cfg: SmedereeHubConfig): Resource[IO, java.sql.Connection] =
+    Resource.make(
+      IO.delay(java.sql.DriverManager.getConnection(cfg.database.url, cfg.database.user, cfg.database.pass))
+    )(c => IO.delay(c.close()))
+
+  /** Create the given account in the database.
+    *
+    * @param account
+    *   The account to be created.
+    * @param hash
+    *   A password hash to be stored.
+    * @param unlockToken
+    *   An optional unlock token to be stored.
+    * @param attempts
+    *   Optional number of failed login attempts.
+    * @return
+    *   The number of affected database rows.
+    */
+  @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!")
+  protected def createAccount(
+      account: Account,
+      hash: PasswordHash,
+      unlockToken: Option[UnlockToken],
+      attempts: Option[Int]
+  ): IO[Int] =
+    connectToDb(configuration).use { con =>
+      for {
+        statement <- IO.delay {
+          unlockToken match {
+            case None =>
+              con.prepareStatement(
+                """INSERT INTO "accounts" (uid, name, email, password, failed_attempts, created_at, updated_at, verified_email) VALUES(?, ?, ?, ?, ?, NOW(), NOW(), ?)"""
+              )
+            case Some(_) =>
+              con.prepareStatement(
+                """INSERT INTO "accounts" (uid, name, email, password, failed_attempts, verified_email, locked_at, unlock_token, created_at, updated_at) VALUES(?, ?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())"""
+              )
+          }
+        }
+        _ <- IO.delay(statement.setObject(1, account.uid))
+        _ <- IO.delay(statement.setString(2, account.name.toString))
+        _ <- IO.delay(statement.setString(3, account.email.toString))
+        _ <- IO.delay(statement.setString(4, hash.toString))
+        _ <- IO.delay(statement.setInt(5, attempts.getOrElse(1)))
+        _ <- IO.delay(statement.setBoolean(6, account.verifiedEmail))
+        _ <- IO.delay {
+          unlockToken.foreach { token =>
+            statement.setString(7, token.toString)
+          }
+        }
+        r <- IO.delay(statement.executeUpdate())
+        _ <- IO.delay(statement.close())
+      } yield r
+    }
+
+  /** Create the given user session in the database.
+    *
+    * @param session
+    *   The session that shall be created. The corresponding user account must exist!
+    * @return
+    *   The number of affected database rows.
+    */
+  @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!")
+  protected def createUserSession(session: Session): IO[Int] =
+    connectToDb(configuration).use { con =>
+      for {
+        statement <- IO.delay(
+          con.prepareStatement(
+            """INSERT INTO "sessions" (id, uid, created_at, updated_at) VALUES(?, ?, ?, ?)"""
+          )
+        )
+        _ <- IO.delay(statement.setString(1, session.id.toString))
+        _ <- IO.delay(statement.setObject(2, session.uid))
+        _ <- IO.delay(
+          statement.setTimestamp(3, java.sql.Timestamp.from(session.createdAt.toInstant))
+        )
+        _ <- IO.delay(
+          statement.setTimestamp(4, java.sql.Timestamp.from(session.updatedAt.toInstant))
+        )
+        r <- IO.delay(statement.executeUpdate())
+        _ <- IO.delay(statement.close())
+      } yield r
+    }
+
+  /** Load the account with the given uid from the database.
+    *
+    * @param uid
+    *   The unique identifier for the account.
+    * @return
+    *   An option to the account if it exists.
+    */
+  @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!")
+  protected def loadAccount(uid: UserId): IO[Option[Account]] =
+    connectToDb(configuration).use { con =>
+      for {
+        statement <- IO.delay(
+          con.prepareStatement(
+            """SELECT uid, name, email, password, created_at, updated_at, verified_email FROM "accounts" WHERE uid = ? LIMIT 1"""
+          )
+        )
+        _      <- IO.delay(statement.setObject(1, uid))
+        result <- IO.delay(statement.executeQuery)
+        account <- IO.delay {
+          if (result.next()) {
+            Option(
+              Account(
+                uid = uid,
+                name = Username(result.getString("name")),
+                email = Email(result.getString("email")),
+                verifiedEmail = result.getBoolean("verified_email")
+              )
+            )
+          } else {
+            None
+          }
+        }
+        _ <- IO(statement.close())
+      } yield account
+    }
 }
diff -rN -u old-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieAuthenticationRepositoryTest.scala new-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieAuthenticationRepositoryTest.scala
--- old-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieAuthenticationRepositoryTest.scala	2025-02-03 04:59:02.850649632 +0000
+++ new-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieAuthenticationRepositoryTest.scala	2025-02-03 04:59:02.854649640 +0000
@@ -36,89 +36,6 @@
     val _              = flyway.clean()
   }
 
-  private def connectToDb(cfg: SmedereeHubConfig): Resource[IO, java.sql.Connection] =
-    Resource.make(
-      IO.delay(java.sql.DriverManager.getConnection(cfg.database.url, cfg.database.user, cfg.database.pass))
-    )(c => IO.delay(c.close()))
-
-  /** Create the given account in the database.
-    *
-    * @param account
-    *   The account to be created.
-    * @param hash
-    *   A password hash to be stored.
-    * @param unlockToken
-    *   An optional unlock token to be stored.
-    * @param attempts
-    *   Optional number of failed login attempts.
-    * @return
-    *   The number of affected database rows.
-    */
-  @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!")
-  private def createAccount(
-      account: Account,
-      hash: PasswordHash,
-      unlockToken: Option[UnlockToken],
-      attempts: Option[Int]
-  ): IO[Int] =
-    connectToDb(configuration).use { con =>
-      for {
-        statement <- IO.delay {
-          unlockToken match {
-            case None =>
-              con.prepareStatement(
-                """INSERT INTO "accounts" (uid, name, email, password, failed_attempts, created_at, updated_at) VALUES(?, ?, ?, ?, ?, NOW(), NOW())"""
-              )
-            case Some(_) =>
-              con.prepareStatement(
-                """INSERT INTO "accounts" (uid, name, email, password, failed_attempts, locked_at, unlock_token, created_at, updated_at) VALUES(?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())"""
-              )
-          }
-        }
-        _ <- IO.delay(statement.setObject(1, account.uid))
-        _ <- IO.delay(statement.setString(2, account.name.toString))
-        _ <- IO.delay(statement.setString(3, account.email.toString))
-        _ <- IO.delay(statement.setString(4, hash.toString))
-        _ <- IO.delay(statement.setInt(5, attempts.getOrElse(1)))
-        _ <- IO.delay {
-          unlockToken.foreach { token =>
-            statement.setString(6, token.toString)
-          }
-        }
-        r <- IO.delay(statement.executeUpdate())
-        _ <- IO.delay(statement.close())
-      } yield r
-    }
-
-  /** Create the given user session in the database.
-    *
-    * @param session
-    *   The session that shall be created. The corresponding user account must exist!
-    * @return
-    *   The number of affected database rows.
-    */
-  @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!")
-  private def createUserSession(session: Session): IO[Int] =
-    connectToDb(configuration).use { con =>
-      for {
-        statement <- IO.delay(
-          con.prepareStatement(
-            """INSERT INTO "sessions" (id, uid, created_at, updated_at) VALUES(?, ?, ?, ?)"""
-          )
-        )
-        _ <- IO.delay(statement.setString(1, session.id.toString))
-        _ <- IO.delay(statement.setObject(2, session.uid))
-        _ <- IO.delay(
-          statement.setTimestamp(3, java.sql.Timestamp.from(session.createdAt.toInstant))
-        )
-        _ <- IO.delay(
-          statement.setTimestamp(4, java.sql.Timestamp.from(session.updatedAt.toInstant))
-        )
-        r <- IO.delay(statement.executeUpdate())
-        _ <- IO.delay(statement.close())
-      } yield r
-    }
-
   test("createUserSession must create the user session") {
     (genValidSession.sample, genValidAccount.sample) match {
       case (Some(s), Some(account)) =>
diff -rN -u old-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieSignupRepositoryTest.scala new-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieSignupRepositoryTest.scala
--- old-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieSignupRepositoryTest.scala	2025-02-03 04:59:02.850649632 +0000
+++ new-smederee/modules/hub/src/it/scala/de/smederee/hub/DoobieSignupRepositoryTest.scala	2025-02-03 04:59:02.854649640 +0000
@@ -35,68 +35,6 @@
     val _              = flyway.clean()
   }
 
-  /** Create the given account in the database.
-    *
-    * @param account
-    *   The account to be created.
-    */
-  @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!")
-  private def createAccount(account: Account, hash: PasswordHash): IO[Int] =
-    for {
-      con <- IO.delay(
-        java.sql.DriverManager
-          .getConnection(configuration.database.url, configuration.database.user, configuration.database.pass)
-      )
-      statement <- IO.delay(
-        con.prepareStatement(
-          """INSERT INTO "accounts" (uid, name, email, password, created_at, updated_at) VALUES(?, ?, ?, ?, NOW(), NOW())"""
-        )
-      )
-      _ <- IO.delay(statement.setObject(1, account.uid))
-      _ <- IO.delay(statement.setString(2, account.name.toString))
-      _ <- IO.delay(statement.setString(3, account.email.toString))
-      _ <- IO.delay(statement.setString(4, hash.toString))
-      r <- IO.delay(statement.executeUpdate())
-      _ <- IO.delay(statement.close())
-      _ <- IO.delay(con.close())
-    } yield r
-
-  /** Load the account with the given uid from the database.
-    *
-    * @param uid
-    *   The unique identifier for the account.
-    * @return
-    *   An option to the account if it exists.
-    */
-  @throws[java.sql.SQLException]("Errors from the underlying SQL procedures will throw exceptions!")
-  private def loadAccount(uid: UserId): IO[Option[Account]] =
-    for {
-      con <- IO.delay(
-        java.sql.DriverManager
-          .getConnection(configuration.database.url, configuration.database.user, configuration.database.pass)
-      )
-      statement <- IO.delay(
-        con.prepareStatement(
-          """SELECT uid, name, email, password, created_at, updated_at FROM "accounts" WHERE uid = ? LIMIT 1"""
-        )
-      )
-      _      <- IO.delay(statement.setObject(1, uid))
-      result <- IO.delay(statement.executeQuery)
-      account <- IO.delay {
-        if (result.next()) {
-          Option(
-            Account(
-              uid = uid,
-              name = Username(result.getString("name")),
-              email = Email(result.getString("email"))
-            )
-          )
-        } else {
-          None
-        }
-      }
-    } yield account
-
   test("createAccount must create a new account") {
     genValidAccount.sample match {
       case None => fail("Could not generate data samples!")
@@ -125,7 +63,7 @@
         val tx = Transactor.fromDriverManager[IO](dbConfig.driver, dbConfig.url, dbConfig.user, dbConfig.pass)
         val repo = new DoobieSignupRepository[IO](tx)
         val test = for {
-          c <- createAccount(existingAccount, PasswordHash("I am not a password hash!"))
+          c <- createAccount(existingAccount, PasswordHash("I am not a password hash!"), None, None)
           w <- repo.createAccount(newAccount, PasswordHash("I am not a password hash!"))
         } yield (c, w)
         test.attempt.map {
@@ -149,7 +87,7 @@
         val tx = Transactor.fromDriverManager[IO](dbConfig.driver, dbConfig.url, dbConfig.user, dbConfig.pass)
         val repo = new DoobieSignupRepository[IO](tx)
         val test = for {
-          c <- createAccount(existingAccount, PasswordHash("I am not a password hash!"))
+          c <- createAccount(existingAccount, PasswordHash("I am not a password hash!"), None, None)
           w <- repo.createAccount(newAccount, PasswordHash("I am not a password hash!"))
         } yield (c, w)
         test.attempt.map {
diff -rN -u old-smederee/modules/hub/src/it/scala/de/smederee/hub/Generators.scala new-smederee/modules/hub/src/it/scala/de/smederee/hub/Generators.scala
--- old-smederee/modules/hub/src/it/scala/de/smederee/hub/Generators.scala	2025-02-03 04:59:02.850649632 +0000
+++ new-smederee/modules/hub/src/it/scala/de/smederee/hub/Generators.scala	2025-02-03 04:59:02.854649640 +0000
@@ -87,10 +87,11 @@
   } yield Username(prefix.toString + chars)
 
   val genValidAccount: Gen[Account] = for {
-    id    <- genUserId
-    email <- genValidEmail
-    name  <- genValidUsername
-  } yield Account(uid = id, name = name, email = email)
+    id            <- genUserId
+    email         <- genValidEmail
+    name          <- genValidUsername
+    verifiedEmail <- Gen.oneOf(List(false, true))
+  } yield Account(uid = id, name = name, email = email, verifiedEmail = verifiedEmail)
 
   given Arbitrary[Account] = Arbitrary(genValidAccount)
 
diff -rN -u old-smederee/modules/hub/src/main/resources/db/migration/V1__base_tables.sql new-smederee/modules/hub/src/main/resources/db/migration/V1__base_tables.sql
--- old-smederee/modules/hub/src/main/resources/db/migration/V1__base_tables.sql	2025-02-03 04:59:02.850649632 +0000
+++ new-smederee/modules/hub/src/main/resources/db/migration/V1__base_tables.sql	2025-02-03 04:59:02.854649640 +0000
@@ -11,6 +11,7 @@
   "reset_token"     TEXT                     DEFAULT NULL,
   "created_at"      TIMESTAMP WITH TIME ZONE NOT NULL,
   "updated_at"      TIMESTAMP WITH TIME ZONE NOT NULL,
+  "verified_email"  BOOLEAN                  DEFAULT FALSE,
   CONSTRAINT "accounts_pk"           PRIMARY KEY ("uid"),
   CONSTRAINT "accounts_unique_name"  UNIQUE ("name"),
   CONSTRAINT "accounts_unique_email" UNIQUE ("email")
@@ -31,6 +32,7 @@
 COMMENT ON COLUMN "accounts"."reset_token" IS 'A token which can be used for a password reset.';
 COMMENT ON COLUMN "accounts"."created_at" IS 'The timestamp of when the account was created.';
 COMMENT ON COLUMN "accounts"."updated_at" IS 'A timestamp when the account was last changed.';
+COMMENT ON COLUMN "accounts"."verified_email" IS 'This flag indicates if the email address of the user has been verified via a verification email.';
 
 CREATE TABLE "sessions"
 (
diff -rN -u old-smederee/modules/hub/src/main/scala/de/smederee/hub/Account.scala new-smederee/modules/hub/src/main/scala/de/smederee/hub/Account.scala
--- old-smederee/modules/hub/src/main/scala/de/smederee/hub/Account.scala	2025-02-03 04:59:02.850649632 +0000
+++ new-smederee/modules/hub/src/main/scala/de/smederee/hub/Account.scala	2025-02-03 04:59:02.854649640 +0000
@@ -338,8 +338,10 @@
   *   A unique name which can be used for login and to identify the user.
   * @param email
   *   The email address of the user.
+  * @param verifiedEmail
+  *   This flag indicates if the email address of the user has been verified via a verification email.
   */
-final case class Account(uid: UserId, name: Username, email: Email)
+final case class Account(uid: UserId, name: Username, email: Email, verifiedEmail: Boolean)
 
 object Account {
   given Eq[Account] =
diff -rN -u old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala
--- old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala	2025-02-03 04:59:02.850649632 +0000
+++ new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala	2025-02-03 04:59:02.854649640 +0000
@@ -28,7 +28,7 @@
 
   private val lockedFilter         = fr"""locked_at IS NOT NULL"""
   private val notLockedFilter      = fr"""locked_at IS NULL"""
-  private val selectAccountColumns = fr"""SELECT uid, name, email FROM "accounts""""
+  private val selectAccountColumns = fr"""SELECT uid, name, email, verified_email FROM "accounts""""
 
   override def createUserSession(session: Session): F[Int] =
     sql"""INSERT INTO "sessions" (id, uid, created_at, updated_at) VALUES (${session.id}, ${session.uid}, ${session.createdAt}, ${session.updatedAt})""".update.run
diff -rN -u old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala
--- old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala	2025-02-03 04:59:02.850649632 +0000
+++ new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala	2025-02-03 04:59:02.854649640 +0000
@@ -24,8 +24,7 @@
   given Meta[Username]     = Meta[String].timap(Username.apply)(_.toString)
 
   override def createAccount(account: Account, hash: PasswordHash): F[Int] =
-    sql"""INSERT INTO "accounts" (uid, name, email, password, created_at, updated_at)
-      VALUES(${account.uid}, ${account.name}, ${account.email}, $hash, NOW(), NOW())""".update.run
+    sql"""INSERT INTO "accounts" (uid, name, email, password, created_at, updated_at, verified_email) VALUES(${account.uid}, ${account.name}, ${account.email}, $hash, NOW(), NOW(), ${account.verifiedEmail})""".update.run
       .transact(tx)
 
   override def findEmail(address: Email): F[Option[Email]] =
diff -rN -u old-smederee/modules/hub/src/main/scala/de/smederee/hub/SignupRoutes.scala new-smederee/modules/hub/src/main/scala/de/smederee/hub/SignupRoutes.scala
--- old-smederee/modules/hub/src/main/scala/de/smederee/hub/SignupRoutes.scala	2025-02-03 04:59:02.854649640 +0000
+++ new-smederee/modules/hub/src/main/scala/de/smederee/hub/SignupRoutes.scala	2025-02-03 04:59:02.854649640 +0000
@@ -99,7 +99,12 @@
                 for {
                   uid <- Sync[F].delay(UserId.randomUserId)
                   account <- Sync[F].delay(
-                    Account(uid = uid, name = signupForm.name, email = signupForm.email)
+                    Account(
+                      uid = uid,
+                      name = signupForm.name,
+                      email = signupForm.email,
+                      verifiedEmail = false
+                    )
                   )
                   hash     <- Sync[F].delay(signupForm.password.encode)
                   _        <- Sync[F].delay(log.info(s"Going to create account for ${account.name}."))
diff -rN -u old-smederee/modules/hub/src/test/scala/de/smederee/hub/Generators.scala new-smederee/modules/hub/src/test/scala/de/smederee/hub/Generators.scala
--- old-smederee/modules/hub/src/test/scala/de/smederee/hub/Generators.scala	2025-02-03 04:59:02.854649640 +0000
+++ new-smederee/modules/hub/src/test/scala/de/smederee/hub/Generators.scala	2025-02-03 04:59:02.854649640 +0000
@@ -87,10 +87,11 @@
   } yield Username(prefix.toString + chars)
 
   val genValidAccount: Gen[Account] = for {
-    id    <- genUserId
-    email <- genValidEmail
-    name  <- genValidUsername
-  } yield Account(uid = id, name = name, email = email)
+    id            <- genUserId
+    email         <- genValidEmail
+    name          <- genValidUsername
+    verifiedEmail <- Gen.oneOf(List(false, true))
+  } yield Account(uid = id, name = name, email = email, verifiedEmail = verifiedEmail)
 
   given Arbitrary[Account] = Arbitrary(genValidAccount)