Alias key backup endpoints onto /unstable, fix key backup bugs (#1947)

* Default /unstable requests to stable endpoints if not overridden specifically with a custom route

* Rewrite URL

* Try something different

* Fix routing manually

* Fix selectLatestVersionSQL

* Don't return 0 if no backup version exists

* Log more useful error

* fix up replace keys check

* Don't enforce uniqueness on e2e_room_keys_versions_idx

Co-authored-by: kegsay <kegan@matrix.org>

6 files changed, 30 insertions, 16 deletions

diff --git a/userapi/storage/accounts/postgres/key_backup_table.go b/userapi/storage/accounts/postgres/key_backup_table.go
index ec651826..0a2a2655 100644
--- a/userapi/storage/accounts/postgres/key_backup_table.go
+++ b/userapi/storage/accounts/postgres/key_backup_table.go
@@ -36,7 +36,7 @@ CREATE TABLE IF NOT EXISTS account_e2e_room_keys (
     session_data TEXT NOT NULL
 );
 CREATE UNIQUE INDEX IF NOT EXISTS e2e_room_keys_idx ON account_e2e_room_keys(user_id, room_id, session_id, version);
-CREATE UNIQUE INDEX IF NOT EXISTS e2e_room_keys_versions_idx ON account_e2e_room_keys(user_id, version);
+CREATE INDEX IF NOT EXISTS e2e_room_keys_versions_idx ON account_e2e_room_keys(user_id, version);
 `
 
 const insertBackupKeySQL = "" +
diff --git a/userapi/storage/accounts/postgres/key_backup_version_table.go b/userapi/storage/accounts/postgres/key_backup_version_table.go
index aca575df..51a462b3 100644
--- a/userapi/storage/accounts/postgres/key_backup_version_table.go
+++ b/userapi/storage/accounts/postgres/key_backup_version_table.go
@@ -146,12 +146,19 @@ func (s *keyBackupVersionStatements) selectKeyBackup(
 ) (versionResult, algorithm string, authData json.RawMessage, etag string, deleted bool, err error) {
 	var versionInt int64
 	if version == "" {
-		err = txn.Stmt(s.selectLatestVersionStmt).QueryRowContext(ctx, userID).Scan(&versionInt)
+		var v *int64 // allows nulls
+		if err = txn.Stmt(s.selectLatestVersionStmt).QueryRowContext(ctx, userID).Scan(&v); err != nil {
+			return
+		}
+		if v == nil {
+			err = sql.ErrNoRows
+			return
+		}
+		versionInt = *v
 	} else {
-		versionInt, err = strconv.ParseInt(version, 10, 64)
-	}
-	if err != nil {
-		return
+		if versionInt, err = strconv.ParseInt(version, 10, 64); err != nil {
+			return
+		}
 	}
 	versionResult = strconv.FormatInt(versionInt, 10)
 	var deletedInt int
diff --git a/userapi/storage/accounts/postgres/storage.go b/userapi/storage/accounts/postgres/storage.go
index 9d6fd13a..6bddbfc3 100644
--- a/userapi/storage/accounts/postgres/storage.go
+++ b/userapi/storage/accounts/postgres/storage.go
@@ -479,7 +479,7 @@ func (d *Database) UpsertBackupKeys(
 						err = d.keyBackups.updateBackupKey(ctx, txn, userID, version, newKey)
 						changed = true
 						if err != nil {
-							return err
+							return fmt.Errorf("d.keyBackups.updateBackupKey: %w", err)
 						}
 					}
 					// if we shouldn't replace the key we do nothing with it
@@ -490,7 +490,7 @@ func (d *Database) UpsertBackupKeys(
 			err = d.keyBackups.insertBackupKey(ctx, txn, userID, version, newKey)
 			changed = true
 			if err != nil {
-				return err
+				return fmt.Errorf("d.keyBackups.insertBackupKey: %w", err)
 			}
 		}
 
diff --git a/userapi/storage/accounts/sqlite3/key_backup_table.go b/userapi/storage/accounts/sqlite3/key_backup_table.go
index c1a698e6..67509351 100644
--- a/userapi/storage/accounts/sqlite3/key_backup_table.go
+++ b/userapi/storage/accounts/sqlite3/key_backup_table.go
@@ -36,7 +36,7 @@ CREATE TABLE IF NOT EXISTS account_e2e_room_keys (
     session_data TEXT NOT NULL
 );
 CREATE UNIQUE INDEX IF NOT EXISTS e2e_room_keys_idx ON account_e2e_room_keys(user_id, room_id, session_id, version);
-CREATE UNIQUE INDEX IF NOT EXISTS e2e_room_keys_versions_idx ON account_e2e_room_keys(user_id, version);
+CREATE INDEX IF NOT EXISTS e2e_room_keys_versions_idx ON account_e2e_room_keys(user_id, version);
 `
 
 const insertBackupKeySQL = "" +
diff --git a/userapi/storage/accounts/sqlite3/key_backup_version_table.go b/userapi/storage/accounts/sqlite3/key_backup_version_table.go
index 9a58fee7..a9e7bf5d 100644
--- a/userapi/storage/accounts/sqlite3/key_backup_version_table.go
+++ b/userapi/storage/accounts/sqlite3/key_backup_version_table.go
@@ -144,12 +144,19 @@ func (s *keyBackupVersionStatements) selectKeyBackup(
 ) (versionResult, algorithm string, authData json.RawMessage, etag string, deleted bool, err error) {
 	var versionInt int64
 	if version == "" {
-		err = txn.Stmt(s.selectLatestVersionStmt).QueryRowContext(ctx, userID).Scan(&versionInt)
+		var v *int64 // allows nulls
+		if err = txn.Stmt(s.selectLatestVersionStmt).QueryRowContext(ctx, userID).Scan(&v); err != nil {
+			return
+		}
+		if v == nil {
+			err = sql.ErrNoRows
+			return
+		}
+		versionInt = *v
 	} else {
-		versionInt, err = strconv.ParseInt(version, 10, 64)
-	}
-	if err != nil {
-		return
+		if versionInt, err = strconv.ParseInt(version, 10, 64); err != nil {
+			return
+		}
 	}
 	versionResult = strconv.FormatInt(versionInt, 10)
 	var deletedInt int
diff --git a/userapi/storage/accounts/sqlite3/storage.go b/userapi/storage/accounts/sqlite3/storage.go
index 728ae901..d752e3db 100644
--- a/userapi/storage/accounts/sqlite3/storage.go
+++ b/userapi/storage/accounts/sqlite3/storage.go
@@ -520,7 +520,7 @@ func (d *Database) UpsertBackupKeys(
 						err = d.keyBackups.updateBackupKey(ctx, txn, userID, version, newKey)
 						changed = true
 						if err != nil {
-							return err
+							return fmt.Errorf("d.keyBackups.updateBackupKey: %w", err)
 						}
 					}
 					// if we shouldn't replace the key we do nothing with it
@@ -531,7 +531,7 @@ func (d *Database) UpsertBackupKeys(
 			err = d.keyBackups.insertBackupKey(ctx, txn, userID, version, newKey)
 			changed = true
 			if err != nil {
-				return err
+				return fmt.Errorf("d.keyBackups.insertBackupKey: %w", err)
 			}
 		}