Remove dead code in internal/database

Removes `keys.go` as the queries there were only being used in one
location.

5 files changed, 105 insertions, 54 deletions

diff --git a/backend/internal/database/cookies.go b/backend/internal/database/cookies.go
index d652b65..3ea21d0 100644
--- a/backend/internal/database/cookies.go
+++ b/backend/internal/database/cookies.go
@@ -98,13 +98,18 @@ ORDER BY expires_at DESC;`, subject, degraded)
 
 func AddCookie(
 	ctx context.Context,
-	exec Executor,
+	exec TransactionExecutor,
 	kms keys.KeyManagementService,
 	subject string,
 	cookie *http.Cookie,
 ) error {
+	tx, err := exec.BeginTx(ctx, nil)
+	if err != nil {
+		return err
+	}
+
 	// Get the key ID for the user
-	user, err := GetUser(ctx, exec, subject)
+	user, err := GetUser(ctx, tx, subject)
 	if err != nil {
 		return fmt.Errorf("unable to get user: %w", err)
 	}
@@ -113,13 +118,20 @@ func AddCookie(
 	}
 
 	// Get the key
-	key, err := GetKey(ctx, exec, *user.EncryptionKeyID)
+	var keyName string
+	var key []byte
+	err = tx.QueryRowContext(ctx, `
+SELECT kms_key_name, encrypted_key
+FROM keys
+WHERE id = $1;`,
+		*user.EncryptionKeyID,
+	).Scan(&keyName, &key)
 	if err != nil {
 		return fmt.Errorf("unable to get key: %w", err)
 	}
 
 	// Encrypt the token
-	encryptedToken, err := keys.EncryptWithKey(ctx, kms, key.Name, key.Key, []byte(cookie.Value))
+	encryptedToken, err := keys.EncryptWithKey(ctx, kms, keyName, key, []byte(cookie.Value))
 	if err != nil {
 		return fmt.Errorf("unable to encrypt token: %w", err)
 	}
@@ -127,7 +139,7 @@ func AddCookie(
 	// Add the cookie to the database
 	_, err = exec.ExecContext(ctx, `
 INSERT INTO ibd_tokens (token, expires_at, user_subject, encryption_key)
-VALUES ($1, $2, $3, $4)`, encryptedToken, cookie.Expires, subject, key.Id)
+VALUES ($1, $2, $3, $4)`, encryptedToken, cookie.Expires, subject, *user.EncryptionKeyID)
 	if err != nil {
 		return fmt.Errorf("unable to add cookie: %w", err)
 	}
diff --git a/backend/internal/database/database_test.go b/backend/internal/database/database_test.go
new file mode 100644
index 0000000..407a09a
--- /dev/null
+++ b/backend/internal/database/database_test.go
@@ -0,0 +1,79 @@
+package database
+
+import (
+	"context"
+	"database/sql"
+	"fmt"
+	"log"
+	"testing"
+	"time"
+
+	"github.com/ory/dockertest/v3"
+	"github.com/ory/dockertest/v3/docker"
+)
+
+var exec *sql.DB
+
+func TestMain(m *testing.M) {
+	pool, err := dockertest.NewPool("")
+	if err != nil {
+		log.Fatalf("Could not create pool: %s", err)
+	}
+
+	err = pool.Client.Ping()
+	if err != nil {
+		log.Fatalf("Could not connect to Docker: %s", err)
+	}
+
+	resource, err := pool.RunWithOptions(&dockertest.RunOptions{
+		Repository: "postgres",
+		Tag:        "16",
+		Env: []string{
+			"POSTGRES_PASSWORD=secret",
+			"POSTGRES_USER=ibd-client-test",
+			"POSTGRES_DB=ibd-client-test",
+			"listen_addresses='*'",
+		},
+		Cmd: []string{
+			"postgres",
+			"-c",
+			"log_statement=all",
+		},
+	}, func(config *docker.HostConfig) {
+		config.AutoRemove = true
+		config.RestartPolicy = docker.RestartPolicy{Name: "no"}
+	})
+	if err != nil {
+		log.Fatalf("Could not start resource: %s", err)
+	}
+
+	hostAndPort := resource.GetHostPort("5432/tcp")
+	databaseUrl := fmt.Sprintf("postgres://ibd-client-test:secret@%s/ibd-client-test?sslmode=disable", hostAndPort)
+
+	// Kill container after 120 seconds
+	_ = resource.Expire(120)
+
+	pool.MaxWait = 120 * time.Second
+	if err = pool.Retry(func() error {
+		exec, err = sql.Open("postgres", databaseUrl)
+		if err != nil {
+			return err
+		}
+		return exec.Ping()
+	}); err != nil {
+		log.Fatalf("Could not connect to database: %s", err)
+	}
+
+	err = Migrate(context.Background(), databaseUrl)
+	if err != nil {
+		log.Fatalf("Could not migrate database: %s", err)
+	}
+
+	defer func() {
+		if err := pool.Purge(resource); err != nil {
+			log.Fatalf("Could not purge resource: %s", err)
+		}
+	}()
+
+	m.Run()
+}
diff --git a/backend/internal/database/keys.go b/backend/internal/database/keys.go
deleted file mode 100644
index e2e2770..0000000
--- a/backend/internal/database/keys.go
+++ /dev/null
@@ -1,44 +0,0 @@
-package database
-
-import (
-	"context"
-	"fmt"
-	"time"
-)
-
-func AddKey(ctx context.Context, exec Executor, keyName string, key []byte) (int, error) {
-	row := exec.QueryRowContext(ctx, `
-INSERT INTO keys (kms_key_name, encrypted_key)
-VALUES ($1, $2)
-RETURNING id;`, keyName, key)
-
-	var keyId int
-	err := row.Scan(&keyId)
-	if err != nil {
-		return 0, fmt.Errorf("unable to scan key id: %w", err)
-	}
-
-	return keyId, nil
-}
-
-func GetKey(ctx context.Context, exec Executor, keyId int) (*Key, error) {
-	row := exec.QueryRowContext(ctx, `
-SELECT id, kms_key_name, encrypted_key, created_at
-FROM keys
-WHERE id = $1;`, keyId)
-
-	key := &Key{}
-	err := row.Scan(&key.Id, &key.Name, &key.Key, &key.Created)
-	if err != nil {
-		return nil, fmt.Errorf("unable to scan key: %w", err)
-	}
-
-	return key, nil
-}
-
-type Key struct {
-	Id      int
-	Name    string
-	Key     []byte
-	Created time.Time
-}
diff --git a/backend/internal/database/users.go b/backend/internal/database/users.go
index d023598..f7998fb 100644
--- a/backend/internal/database/users.go
+++ b/backend/internal/database/users.go
@@ -83,7 +83,11 @@ func AddIBDCreds(
 		_ = tx.Rollback()
 	}(tx)
 
-	keyId, err := AddKey(ctx, tx, keyName, encryptedKey)
+	var keyId int
+	err = tx.QueryRowContext(ctx, `
+INSERT INTO keys (kms_key_name, encrypted_key)
+VALUES ($1, $2)
+RETURNING id;`, keyName, encryptedKey).Scan(&keyId)
 	if err != nil {
 		return fmt.Errorf("unable to add ibd creds key: %w", err)
 	}
diff --git a/backend/internal/worker/auth/auth.go b/backend/internal/worker/auth/auth.go
index 579a180..2043b5e 100644
--- a/backend/internal/worker/auth/auth.go
+++ b/backend/internal/worker/auth/auth.go
@@ -26,7 +26,7 @@ func RunAuthScraper(
 	ctx context.Context,
 	client *ibd.Client,
 	redis *redis.Client,
-	db database.Executor,
+	db database.TransactionExecutor,
 	kms keys.KeyManagementService,
 	name string,
 ) error {
@@ -55,7 +55,7 @@ func waitForTask(
 	ctx context.Context,
 	queue taskqueue.TaskQueue[auth.TaskInfo],
 	client *ibd.Client,
-	db database.Executor,
+	db database.TransactionExecutor,
 	kms keys.KeyManagementService,
 ) {
 	task, err := queue.Dequeue(ctx, lockTimeout, dequeueTimeout)
@@ -119,7 +119,7 @@ func waitForTask(
 func scrapeCookies(
 	ctx context.Context,
 	client *ibd.Client,
-	db database.Executor,
+	db database.TransactionExecutor,
 	kms keys.KeyManagementService,
 	user string,
 ) error {
@@ -213,7 +213,7 @@ func healthCheckDegradedCookies(
 func scrapeNewCookies(
 	ctx context.Context,
 	client *ibd.Client,
-	db database.Executor,
+	db database.TransactionExecutor,
 	kms keys.KeyManagementService,
 	user string,
 ) error {