build(deps): use go.uber.org/mock

Use the new go.uber.org/mock which is currently maintained.

Signed-off-by: James Elliott <james-d-elliott@users.noreply.github.com>

15 files changed, 86 insertions, 5 deletions

diff --git a/internal/storage/const.go b/internal/storage/const.go
index c00a957d7..907842b89 100644
--- a/internal/storage/const.go
+++ b/internal/storage/const.go
@@ -10,6 +10,7 @@ const (
 	tableIdentityVerification = "identity_verification"
 	tableOneTimeCode          = "one_time_code"
 	tableTOTPConfigurations   = "totp_configurations"
+	tableTOTPHistory          = "totp_history"
 	tableUserOpaqueIdentifier = "user_opaque_identifier"
 	tableUserPreferences      = "user_preferences"
 	tableWebAuthnCredentials  = "webauthn_credentials" //nolint:gosec // This is a table name, not a credential.
diff --git a/internal/storage/migrations/mysql/V0013.OneTimeCode.up.sql b/internal/storage/migrations/mysql/V0013.OneTimeCode.up.sql
index a27f6c709..8edd66979 100644
--- a/internal/storage/migrations/mysql/V0013.OneTimeCode.up.sql
+++ b/internal/storage/migrations/mysql/V0013.OneTimeCode.up.sql
@@ -14,5 +14,4 @@ CREATE TABLE IF NOT EXISTS one_time_code (
     code BLOB NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
 
-CREATE UNIQUE INDEX one_time_code_signature ON one_time_code (signature);
-CREATE INDEX one_time_code_lookup ON one_time_code (signature, username);
+CREATE UNIQUE INDEX one_time_code_signature ON one_time_code (signature, username);
diff --git a/internal/storage/migrations/mysql/V0015.TOTPEnhance.down.sql b/internal/storage/migrations/mysql/V0015.TOTPEnhance.down.sql
new file mode 100644
index 000000000..53d9ae5ab
--- /dev/null
+++ b/internal/storage/migrations/mysql/V0015.TOTPEnhance.down.sql
@@ -0,0 +1 @@
+DROP TABLE IF EXISTS totp_history;
diff --git a/internal/storage/migrations/mysql/V0015.TOTPEnhance.up.sql b/internal/storage/migrations/mysql/V0015.TOTPEnhance.up.sql
new file mode 100644
index 000000000..e74462ffd
--- /dev/null
+++ b/internal/storage/migrations/mysql/V0015.TOTPEnhance.up.sql
@@ -0,0 +1,8 @@
+CREATE TABLE IF NOT EXISTS totp_history (
+    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    username VARCHAR(100) NOT NULL,
+	step CHAR(128) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
+
+CREATE UNIQUE INDEX totp_history_lookup_key ON totp_history (username, step);
diff --git a/internal/storage/migrations/postgres/V0013.OneTimeCode.up.sql b/internal/storage/migrations/postgres/V0013.OneTimeCode.up.sql
index 434068170..77016c801 100644
--- a/internal/storage/migrations/postgres/V0013.OneTimeCode.up.sql
+++ b/internal/storage/migrations/postgres/V0013.OneTimeCode.up.sql
@@ -15,4 +15,3 @@ CREATE TABLE IF NOT EXISTS one_time_code (
 );
 
 CREATE UNIQUE INDEX one_time_code_lookup_key ON one_time_code (signature, username);
-CREATE INDEX one_time_code_lookup ON one_time_code (signature, username);
diff --git a/internal/storage/migrations/postgres/V0015.TOTPEnhance.down.sql b/internal/storage/migrations/postgres/V0015.TOTPEnhance.down.sql
new file mode 100644
index 000000000..53d9ae5ab
--- /dev/null
+++ b/internal/storage/migrations/postgres/V0015.TOTPEnhance.down.sql
@@ -0,0 +1 @@
+DROP TABLE IF EXISTS totp_history;
diff --git a/internal/storage/migrations/postgres/V0015.TOTPEnhance.up.sql b/internal/storage/migrations/postgres/V0015.TOTPEnhance.up.sql
new file mode 100644
index 000000000..22c6e23c1
--- /dev/null
+++ b/internal/storage/migrations/postgres/V0015.TOTPEnhance.up.sql
@@ -0,0 +1,8 @@
+CREATE TABLE IF NOT EXISTS totp_history (
+    id SERIAL CONSTRAINT one_time_code_pkey PRIMARY KEY,
+	created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    username VARCHAR(100) NOT NULL,
+	step CHAR(128) NOT NULL
+);
+
+CREATE UNIQUE INDEX totp_history_lookup_key ON totp_history (username, step);
diff --git a/internal/storage/migrations/sqlite/V0013.OneTimeCode.up.sql b/internal/storage/migrations/sqlite/V0013.OneTimeCode.up.sql
index f7e2ab0f7..6fc19d524 100644
--- a/internal/storage/migrations/sqlite/V0013.OneTimeCode.up.sql
+++ b/internal/storage/migrations/sqlite/V0013.OneTimeCode.up.sql
@@ -15,4 +15,3 @@ CREATE TABLE IF NOT EXISTS one_time_code (
 );
 
 CREATE UNIQUE INDEX one_time_code_lookup_key ON one_time_code (signature, username);
-CREATE INDEX one_time_code_lookup ON one_time_code (signature, username);
diff --git a/internal/storage/migrations/sqlite/V0015.TOTPEnhance.down.sql b/internal/storage/migrations/sqlite/V0015.TOTPEnhance.down.sql
new file mode 100644
index 000000000..53d9ae5ab
--- /dev/null
+++ b/internal/storage/migrations/sqlite/V0015.TOTPEnhance.down.sql
@@ -0,0 +1 @@
+DROP TABLE IF EXISTS totp_history;
diff --git a/internal/storage/migrations/sqlite/V0015.TOTPEnhance.up.sql b/internal/storage/migrations/sqlite/V0015.TOTPEnhance.up.sql
new file mode 100644
index 000000000..21a3c5667
--- /dev/null
+++ b/internal/storage/migrations/sqlite/V0015.TOTPEnhance.up.sql
@@ -0,0 +1,8 @@
+CREATE TABLE IF NOT EXISTS totp_history (
+    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    username VARCHAR(100) NOT NULL,
+	step CHAR(128) NOT NULL
+);
+
+CREATE UNIQUE INDEX totp_history_lookup_key ON totp_history (username, step);
diff --git a/internal/storage/migrations_test.go b/internal/storage/migrations_test.go
index 5ebf686e2..7cfedace2 100644
--- a/internal/storage/migrations_test.go
+++ b/internal/storage/migrations_test.go
@@ -11,7 +11,7 @@ import (
 
 const (
 	// This is the latest schema version for the purpose of tests.
-	LatestVersion = 14
+	LatestVersion = 15
 )
 
 func TestShouldObtainCorrectMigrations(t *testing.T) {
diff --git a/internal/storage/provider.go b/internal/storage/provider.go
index a0f23627d..e53cf7ed1 100644
--- a/internal/storage/provider.go
+++ b/internal/storage/provider.go
@@ -71,6 +71,16 @@ type Provider interface {
 	LoadTOTPConfigurations(ctx context.Context, limit, page int) (configs []model.TOTPConfiguration, err error)
 
 	/*
+		Implementation for User TOTP History.
+	*/
+
+	// SaveTOTPHistory saves a TOTP history item in the storage provider.
+	SaveTOTPHistory(ctx context.Context, username string, step uint64) (err error)
+
+	// ExistsTOTPHistory checks if a TOTP history item exists in the storage provider.
+	ExistsTOTPHistory(ctx context.Context, username string, step uint64, since time.Time) (exists bool, err error)
+
+	/*
 		Implementation for User WebAuthn Information.
 	*/
 
diff --git a/internal/storage/sql_provider.go b/internal/storage/sql_provider.go
index 66f8a7795..cb86e137c 100644
--- a/internal/storage/sql_provider.go
+++ b/internal/storage/sql_provider.go
@@ -6,6 +6,7 @@ import (
 	"database/sql"
 	"errors"
 	"fmt"
+	"strconv"
 	"strings"
 	"time"
 
@@ -59,6 +60,9 @@ func NewSQLProvider(config *schema.Configuration, name, driverName, dataSourceNa
 		sqlUpdateTOTPConfigRecordSignIn:           fmt.Sprintf(queryFmtUpdateTOTPConfigRecordSignIn, tableTOTPConfigurations),
 		sqlUpdateTOTPConfigRecordSignInByUsername: fmt.Sprintf(queryFmtUpdateTOTPConfigRecordSignInByUsername, tableTOTPConfigurations),
 
+		sqlInsertTOTPHistory: fmt.Sprintf(queryFmtInsertTOTPHistory, tableTOTPHistory),
+		sqlSelectTOTPHistory: fmt.Sprintf(queryFmtSelectTOTPHistory, tableTOTPHistory),
+
 		sqlInsertWebAuthnUser: fmt.Sprintf(queryFmtInsertWebAuthnUser, tableWebAuthnUsers),
 		sqlSelectWebAuthnUser: fmt.Sprintf(queryFmtSelectWebAuthnUser, tableWebAuthnUsers),
 
@@ -193,6 +197,10 @@ type SQLProvider struct {
 	sqlUpdateTOTPConfigRecordSignIn           string
 	sqlUpdateTOTPConfigRecordSignInByUsername string
 
+	// Table: totp_history.
+	sqlInsertTOTPHistory string
+	sqlSelectTOTPHistory string
+
 	// Table: webauthn_users.
 	sqlInsertWebAuthnUser string
 	sqlSelectWebAuthnUser string
@@ -546,6 +554,30 @@ func (p *SQLProvider) LoadTOTPConfiguration(ctx context.Context, username string
 	return config, nil
 }
 
+// SaveTOTPHistory saves a TOTP history item in the storage provider.
+func (p *SQLProvider) SaveTOTPHistory(ctx context.Context, username string, step uint64) (err error) {
+	signature := p.hmacSignature([]byte(strconv.Itoa(int(step))), []byte(username))
+
+	if _, err = p.db.ExecContext(ctx, p.sqlInsertTOTPHistory, username, signature); err != nil {
+		return fmt.Errorf("error inserting TOTP history for user '%s': %w", username, err)
+	}
+
+	return nil
+}
+
+// ExistsTOTPHistory checks if a TOTP history item exists in the storage provider.
+func (p *SQLProvider) ExistsTOTPHistory(ctx context.Context, username string, step uint64, since time.Time) (exists bool, err error) {
+	var count int
+
+	signature := p.hmacSignature([]byte(strconv.Itoa(int(step))), []byte(username))
+
+	if err = p.db.SelectContext(ctx, &count, p.sqlSelectTOTPHistory, username, signature, since); err != nil {
+		return false, fmt.Errorf("error checking if TOTP history exists: %w", err)
+	}
+
+	return count != 0, nil
+}
+
 // LoadTOTPConfigurations load a set of TOTP configurations from the storage provider.
 func (p *SQLProvider) LoadTOTPConfigurations(ctx context.Context, limit, page int) (configs []model.TOTPConfiguration, err error) {
 	configs = make([]model.TOTPConfiguration, 0, limit)
diff --git a/internal/storage/sql_provider_backend_postgres.go b/internal/storage/sql_provider_backend_postgres.go
index 4a049cffd..ee6aa434b 100644
--- a/internal/storage/sql_provider_backend_postgres.go
+++ b/internal/storage/sql_provider_backend_postgres.go
@@ -66,6 +66,9 @@ func NewPostgreSQLProvider(config *schema.Configuration, caCertPool *x509.CertPo
 	provider.sqlDeleteTOTPConfig = provider.db.Rebind(provider.sqlDeleteTOTPConfig)
 	provider.sqlSelectTOTPConfigs = provider.db.Rebind(provider.sqlSelectTOTPConfigs)
 
+	provider.sqlInsertTOTPHistory = provider.db.Rebind(provider.sqlInsertTOTPHistory)
+	provider.sqlSelectTOTPHistory = provider.db.Rebind(provider.sqlSelectTOTPHistory)
+
 	provider.sqlInsertWebAuthnUser = provider.db.Rebind(provider.sqlInsertWebAuthnUser)
 	provider.sqlSelectWebAuthnUser = provider.db.Rebind(provider.sqlSelectWebAuthnUser)
 
diff --git a/internal/storage/sql_provider_queries.go b/internal/storage/sql_provider_queries.go
index bff832dbd..48be6d2af 100644
--- a/internal/storage/sql_provider_queries.go
+++ b/internal/storage/sql_provider_queries.go
@@ -167,6 +167,17 @@ const (
 		WHERE id = ?;`
 )
 
+const (
+	queryFmtInsertTOTPHistory = `
+		INSERT INTO %s (username, step)
+		VALUES (?, ?);`
+
+	queryFmtSelectTOTPHistory = `
+		SELECT COUNT(id)
+		FROM %s
+		WHERE username = ? AND step = ?;`
+)
+
 //nolint:gosec // The following queries are not hard coded credentials.
 const (
 	queryFmtSelectWebAuthnCredentials = `