[chore] Migrate accounts to new table, relax uniqueness constraint of actor `url` and collections (#3928)

* [chore] Migrate accounts to new table, relax uniqueness constraint of actor url and collections

* fiddle with it! (that's what she said)

* remove unused cache fields

* sillyness

* fix tiny whoopsie

8 files changed, 724 insertions, 149 deletions

diff --git a/internal/db/bundb/account.go b/internal/db/bundb/account.go
index aacfcd247..88a923ecf 100644
--- a/internal/db/bundb/account.go
+++ b/internal/db/bundb/account.go
@@ -121,18 +121,46 @@ func (a *accountDB) GetAccountByURI(ctx context.Context, uri string) (*gtsmodel.
 	)
 }
 
-func (a *accountDB) GetAccountByURL(ctx context.Context, url string) (*gtsmodel.Account, error) {
-	return a.getAccount(
-		ctx,
-		"URL",
-		func(account *gtsmodel.Account) error {
-			return a.db.NewSelect().
-				Model(account).
-				Where("? = ?", bun.Ident("account.url"), url).
-				Scan(ctx)
-		},
-		url,
-	)
+func (a *accountDB) GetOneAccountByURL(ctx context.Context, url string) (*gtsmodel.Account, error) {
+	// Select IDs of all
+	// accounts with this url.
+	var ids []string
+	if err := a.db.NewSelect().
+		TableExpr("? AS ?", bun.Ident("accounts"), bun.Ident("account")).
+		Column("account.id").
+		Where("? = ?", bun.Ident("account.url"), url).
+		Scan(ctx, &ids); err != nil {
+		return nil, err
+	}
+
+	// Ensure exactly one account.
+	if len(ids) == 0 {
+		return nil, db.ErrNoEntries
+	}
+	if len(ids) > 1 {
+		return nil, db.ErrMultipleEntries
+	}
+
+	return a.GetAccountByID(ctx, ids[0])
+}
+
+func (a *accountDB) GetAccountsByURL(ctx context.Context, url string) ([]*gtsmodel.Account, error) {
+	// Select IDs of all
+	// accounts with this url.
+	var ids []string
+	if err := a.db.NewSelect().
+		TableExpr("? AS ?", bun.Ident("accounts"), bun.Ident("account")).
+		Column("account.id").
+		Where("? = ?", bun.Ident("account.url"), url).
+		Scan(ctx, &ids); err != nil {
+		return nil, err
+	}
+
+	if len(ids) == 0 {
+		return nil, db.ErrNoEntries
+	}
+
+	return a.GetAccountsByIDs(ctx, ids)
 }
 
 func (a *accountDB) GetAccountByUsernameDomain(ctx context.Context, username string, domain string) (*gtsmodel.Account, error) {
@@ -184,60 +212,50 @@ func (a *accountDB) GetAccountByPubkeyID(ctx context.Context, id string) (*gtsmo
 	)
 }
 
-func (a *accountDB) GetAccountByInboxURI(ctx context.Context, uri string) (*gtsmodel.Account, error) {
-	return a.getAccount(
-		ctx,
-		"InboxURI",
-		func(account *gtsmodel.Account) error {
-			return a.db.NewSelect().
-				Model(account).
-				Where("? = ?", bun.Ident("account.inbox_uri"), uri).
-				Scan(ctx)
-		},
-		uri,
-	)
-}
+func (a *accountDB) GetOneAccountByInboxURI(ctx context.Context, inboxURI string) (*gtsmodel.Account, error) {
+	// Select IDs of all accounts
+	// with this inbox_uri.
+	var ids []string
+	if err := a.db.NewSelect().
+		TableExpr("? AS ?", bun.Ident("accounts"), bun.Ident("account")).
+		Column("account.id").
+		Where("? = ?", bun.Ident("account.inbox_uri"), inboxURI).
+		Scan(ctx, &ids); err != nil {
+		return nil, err
+	}
 
-func (a *accountDB) GetAccountByOutboxURI(ctx context.Context, uri string) (*gtsmodel.Account, error) {
-	return a.getAccount(
-		ctx,
-		"OutboxURI",
-		func(account *gtsmodel.Account) error {
-			return a.db.NewSelect().
-				Model(account).
-				Where("? = ?", bun.Ident("account.outbox_uri"), uri).
-				Scan(ctx)
-		},
-		uri,
-	)
-}
+	// Ensure exactly one account.
+	if len(ids) == 0 {
+		return nil, db.ErrNoEntries
+	}
+	if len(ids) > 1 {
+		return nil, db.ErrMultipleEntries
+	}
 
-func (a *accountDB) GetAccountByFollowersURI(ctx context.Context, uri string) (*gtsmodel.Account, error) {
-	return a.getAccount(
-		ctx,
-		"FollowersURI",
-		func(account *gtsmodel.Account) error {
-			return a.db.NewSelect().
-				Model(account).
-				Where("? = ?", bun.Ident("account.followers_uri"), uri).
-				Scan(ctx)
-		},
-		uri,
-	)
+	return a.GetAccountByID(ctx, ids[0])
 }
 
-func (a *accountDB) GetAccountByFollowingURI(ctx context.Context, uri string) (*gtsmodel.Account, error) {
-	return a.getAccount(
-		ctx,
-		"FollowingURI",
-		func(account *gtsmodel.Account) error {
-			return a.db.NewSelect().
-				Model(account).
-				Where("? = ?", bun.Ident("account.following_uri"), uri).
-				Scan(ctx)
-		},
-		uri,
-	)
+func (a *accountDB) GetOneAccountByOutboxURI(ctx context.Context, outboxURI string) (*gtsmodel.Account, error) {
+	// Select IDs of all accounts
+	// with this outbox_uri.
+	var ids []string
+	if err := a.db.NewSelect().
+		TableExpr("? AS ?", bun.Ident("accounts"), bun.Ident("account")).
+		Column("account.id").
+		Where("? = ?", bun.Ident("account.outbox_uri"), outboxURI).
+		Scan(ctx, &ids); err != nil {
+		return nil, err
+	}
+
+	// Ensure exactly one account.
+	if len(ids) == 0 {
+		return nil, db.ErrNoEntries
+	}
+	if len(ids) > 1 {
+		return nil, db.ErrMultipleEntries
+	}
+
+	return a.GetAccountByID(ctx, ids[0])
 }
 
 func (a *accountDB) GetInstanceAccount(ctx context.Context, domain string) (*gtsmodel.Account, error) {
@@ -587,7 +605,11 @@ func (a *accountDB) GetAccounts(
 	return a.state.DB.GetAccountsByIDs(ctx, accountIDs)
 }
 
-func (a *accountDB) getAccount(ctx context.Context, lookup string, dbQuery func(*gtsmodel.Account) error, keyParts ...any) (*gtsmodel.Account, error) {
+func (a *accountDB) getAccount(
+	ctx context.Context,
+	lookup string,
+	dbQuery func(*gtsmodel.Account) error, keyParts ...any,
+) (*gtsmodel.Account, error) {
 	// Fetch account from database cache with loader callback
 	account, err := a.state.Caches.DB.Account.LoadOne(lookup, func() (*gtsmodel.Account, error) {
 		var account gtsmodel.Account
diff --git a/internal/db/bundb/account_test.go b/internal/db/bundb/account_test.go
index e3d36855e..ffd44de79 100644
--- a/internal/db/bundb/account_test.go
+++ b/internal/db/bundb/account_test.go
@@ -32,11 +32,10 @@ import (
 	"github.com/stretchr/testify/suite"
 	"github.com/superseriousbusiness/gotosocial/internal/ap"
 	"github.com/superseriousbusiness/gotosocial/internal/db"
-	"github.com/superseriousbusiness/gotosocial/internal/db/bundb"
+	"github.com/superseriousbusiness/gotosocial/internal/gtscontext"
 	"github.com/superseriousbusiness/gotosocial/internal/gtsmodel"
 	"github.com/superseriousbusiness/gotosocial/internal/paging"
 	"github.com/superseriousbusiness/gotosocial/internal/util"
-	"github.com/uptrace/bun"
 )
 
 type AccountTestSuite struct {
@@ -255,7 +254,20 @@ func (suite *AccountTestSuite) TestGetAccountBy() {
 				if account.URL == "" {
 					return nil, sentinelErr
 				}
-				return suite.db.GetAccountByURL(ctx, account.URL)
+				return suite.db.GetOneAccountByURL(ctx, account.URL)
+			},
+
+			"url_multi": func() (*gtsmodel.Account, error) {
+				if account.URL == "" {
+					return nil, sentinelErr
+				}
+
+				accounts, err := suite.db.GetAccountsByURL(ctx, account.URL)
+				if err != nil {
+					return nil, err
+				}
+
+				return accounts[0], nil
 			},
 
 			"username@domain": func() (*gtsmodel.Account, error) {
@@ -281,28 +293,14 @@ func (suite *AccountTestSuite) TestGetAccountBy() {
 				if account.InboxURI == "" {
 					return nil, sentinelErr
 				}
-				return suite.db.GetAccountByInboxURI(ctx, account.InboxURI)
+				return suite.db.GetOneAccountByInboxURI(ctx, account.InboxURI)
 			},
 
 			"outbox_uri": func() (*gtsmodel.Account, error) {
 				if account.OutboxURI == "" {
 					return nil, sentinelErr
 				}
-				return suite.db.GetAccountByOutboxURI(ctx, account.OutboxURI)
-			},
-
-			"following_uri": func() (*gtsmodel.Account, error) {
-				if account.FollowingURI == "" {
-					return nil, sentinelErr
-				}
-				return suite.db.GetAccountByFollowingURI(ctx, account.FollowingURI)
-			},
-
-			"followers_uri": func() (*gtsmodel.Account, error) {
-				if account.FollowersURI == "" {
-					return nil, sentinelErr
-				}
-				return suite.db.GetAccountByFollowersURI(ctx, account.FollowersURI)
+				return suite.db.GetOneAccountByOutboxURI(ctx, account.OutboxURI)
 			},
 		} {
 
@@ -345,71 +343,37 @@ func (suite *AccountTestSuite) TestGetAccountBy() {
 	}
 }
 
-func (suite *AccountTestSuite) TestUpdateAccount() {
+func (suite *AccountTestSuite) TestGetAccountsByURLMulti() {
 	ctx := context.Background()
 
-	testAccount := suite.testAccounts["local_account_1"]
-
-	testAccount.DisplayName = "new display name!"
-	testAccount.EmojiIDs = []string{"01GD36ZKWTKY3T1JJ24JR7KY1Q", "01GD36ZV904SHBHNAYV6DX5QEF"}
-
-	err := suite.db.UpdateAccount(ctx, testAccount)
-	suite.NoError(err)
-
-	updated, err := suite.db.GetAccountByID(ctx, testAccount.ID)
-	suite.NoError(err)
-	suite.Equal("new display name!", updated.DisplayName)
-	suite.Equal([]string{"01GD36ZKWTKY3T1JJ24JR7KY1Q", "01GD36ZV904SHBHNAYV6DX5QEF"}, updated.EmojiIDs)
-	suite.WithinDuration(time.Now(), updated.UpdatedAt, 5*time.Second)
-
-	// get account without cache + make sure it's really in the db as desired
-	dbService, ok := suite.db.(*bundb.DBService)
-	if !ok {
-		panic("db was not *bundb.DBService")
+	// Update admin account to have the same url as zork.
+	testAccount1 := suite.testAccounts["local_account_1"]
+	testAccount2 := new(gtsmodel.Account)
+	*testAccount2 = *suite.testAccounts["admin_account"]
+	testAccount2.URL = testAccount1.URL
+	if err := suite.state.DB.UpdateAccount(ctx, testAccount2, "url"); err != nil {
+		suite.FailNow(err.Error())
 	}
 
-	noCache := &gtsmodel.Account{}
-	err = dbService.DB().
-		NewSelect().
-		Model(noCache).
-		Where("? = ?", bun.Ident("account.id"), testAccount.ID).
-		Relation("AvatarMediaAttachment").
-		Relation("HeaderMediaAttachment").
-		Relation("Emojis").
-		Scan(ctx)
-
-	suite.NoError(err)
-	suite.Equal("new display name!", noCache.DisplayName)
-	suite.Equal([]string{"01GD36ZKWTKY3T1JJ24JR7KY1Q", "01GD36ZV904SHBHNAYV6DX5QEF"}, noCache.EmojiIDs)
-	suite.WithinDuration(time.Now(), noCache.UpdatedAt, 5*time.Second)
-	suite.NotNil(noCache.AvatarMediaAttachment)
-	suite.NotNil(noCache.HeaderMediaAttachment)
-
-	// update again to remove emoji associations
-	testAccount.EmojiIDs = []string{}
-
-	err = suite.db.UpdateAccount(ctx, testAccount)
-	suite.NoError(err)
-
-	updated, err = suite.db.GetAccountByID(ctx, testAccount.ID)
-	suite.NoError(err)
-	suite.Equal("new display name!", updated.DisplayName)
-	suite.Empty(updated.EmojiIDs)
-	suite.WithinDuration(time.Now(), updated.UpdatedAt, 5*time.Second)
-
-	err = dbService.DB().
-		NewSelect().
-		Model(noCache).
-		Where("? = ?", bun.Ident("account.id"), testAccount.ID).
-		Relation("AvatarMediaAttachment").
-		Relation("HeaderMediaAttachment").
-		Relation("Emojis").
-		Scan(ctx)
+	// Select all accounts with that URL.
+	// Should return 2.
+	accounts, err := suite.state.DB.GetAccountsByURL(
+		gtscontext.SetBarebones(ctx),
+		testAccount1.URL,
+	)
+	if err != nil {
+		suite.FailNow(err.Error())
+	}
+	suite.Len(accounts, 2)
 
-	suite.NoError(err)
-	suite.Equal("new display name!", noCache.DisplayName)
-	suite.Empty(noCache.EmojiIDs)
-	suite.WithinDuration(time.Now(), noCache.UpdatedAt, 5*time.Second)
+	// Try to select one account with that URL.
+	// Should error.
+	account, err := suite.state.DB.GetOneAccountByURL(
+		gtscontext.SetBarebones(ctx),
+		testAccount1.URL,
+	)
+	suite.Nil(account)
+	suite.ErrorIs(err, db.ErrMultipleEntries)
 }
 
 func (suite *AccountTestSuite) TestInsertAccountWithDefaults() {
@@ -422,7 +386,7 @@ func (suite *AccountTestSuite) TestInsertAccountWithDefaults() {
 		Domain:       "example.org",
 		URI:          "https://example.org/users/test_service",
 		URL:          "https://example.org/@test_service",
-		ActorType:    ap.ActorService,
+		ActorType:    gtsmodel.AccountActorTypeService,
 		PublicKey:    &key.PublicKey,
 		PublicKeyURI: "https://example.org/users/test_service#main-key",
 	}
@@ -433,7 +397,6 @@ func (suite *AccountTestSuite) TestInsertAccountWithDefaults() {
 	suite.WithinDuration(time.Now(), newAccount.CreatedAt, 30*time.Second)
 	suite.WithinDuration(time.Now(), newAccount.UpdatedAt, 30*time.Second)
 	suite.True(*newAccount.Locked)
-	suite.False(*newAccount.Bot)
 	suite.False(*newAccount.Discoverable)
 }
 
diff --git a/internal/db/bundb/admin.go b/internal/db/bundb/admin.go
index 02f10f44f..12cb6a6f7 100644
--- a/internal/db/bundb/admin.go
+++ b/internal/db/bundb/admin.go
@@ -28,7 +28,6 @@ import (
 	"time"
 
 	"github.com/google/uuid"
-	"github.com/superseriousbusiness/gotosocial/internal/ap"
 	"github.com/superseriousbusiness/gotosocial/internal/config"
 	"github.com/superseriousbusiness/gotosocial/internal/db"
 	"github.com/superseriousbusiness/gotosocial/internal/gtserror"
@@ -131,7 +130,7 @@ func (a *adminDB) NewSignup(ctx context.Context, newSignup gtsmodel.NewSignup) (
 			FollowingURI:          uris.FollowingURI,
 			FollowersURI:          uris.FollowersURI,
 			FeaturedCollectionURI: uris.FeaturedCollectionURI,
-			ActorType:             ap.ActorPerson,
+			ActorType:             gtsmodel.AccountActorTypePerson,
 			PrivateKey:            privKey,
 			PublicKey:             &privKey.PublicKey,
 			PublicKeyURI:          uris.PublicKeyURI,
@@ -283,7 +282,7 @@ func (a *adminDB) CreateInstanceAccount(ctx context.Context) error {
 		PrivateKey:            key,
 		PublicKey:             &key.PublicKey,
 		PublicKeyURI:          newAccountURIs.PublicKeyURI,
-		ActorType:             ap.ActorPerson,
+		ActorType:             gtsmodel.AccountActorTypeService,
 		URI:                   newAccountURIs.UserURI,
 		InboxURI:              newAccountURIs.InboxURI,
 		OutboxURI:             newAccountURIs.OutboxURI,
diff --git a/internal/db/bundb/basic_test.go b/internal/db/bundb/basic_test.go
index 1f2d1ac48..4c5ea8d18 100644
--- a/internal/db/bundb/basic_test.go
+++ b/internal/db/bundb/basic_test.go
@@ -55,7 +55,7 @@ func (suite *BasicTestSuite) TestPutAccountWithBunDefaultFields() {
 		URL:          "https://example.org/@test",
 		InboxURI:     "https://example.org/users/test/inbox",
 		OutboxURI:    "https://example.org/users/test/outbox",
-		ActorType:    "Person",
+		ActorType:    gtsmodel.AccountActorTypePerson,
 		PublicKeyURI: "https://example.org/test#main-key",
 		PublicKey:    &key.PublicKey,
 	}
@@ -87,7 +87,6 @@ func (suite *BasicTestSuite) TestPutAccountWithBunDefaultFields() {
 	suite.Empty(a.NoteRaw)
 	suite.Empty(a.AlsoKnownAsURIs)
 	suite.Empty(a.MovedToURI)
-	suite.False(*a.Bot)
 	// Locked is especially important, since it's a bool that defaults
 	// to true, which is why we use pointers for bools in the first place
 	suite.True(*a.Locked)
diff --git a/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness.go b/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness.go
new file mode 100644
index 000000000..acc37529d
--- /dev/null
+++ b/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness.go
@@ -0,0 +1,398 @@
+// GoToSocial
+// Copyright (C) GoToSocial Authors admin@gotosocial.org
+// SPDX-License-Identifier: AGPL-3.0-or-later
+//
+// This program is free software: you can redistribute it and/or modify
+// it under the terms of the GNU Affero General Public License as published by
+// the Free Software Foundation, either version 3 of the License, or
+// (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+// GNU Affero General Public License for more details.
+//
+// You should have received a copy of the GNU Affero General Public License
+// along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+package migrations
+
+import (
+	"context"
+	"errors"
+	"fmt"
+	"strings"
+
+	"github.com/superseriousbusiness/gotosocial/internal/config"
+	"github.com/superseriousbusiness/gotosocial/internal/db"
+	new_gtsmodel "github.com/superseriousbusiness/gotosocial/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness/new"
+	old_gtsmodel "github.com/superseriousbusiness/gotosocial/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness/old"
+	"github.com/superseriousbusiness/gotosocial/internal/log"
+
+	"github.com/uptrace/bun"
+	"github.com/uptrace/bun/dialect"
+)
+
+func init() {
+	up := func(ctx context.Context, bdb *bun.DB) error {
+		log.Info(ctx, "converting accounts to new model; this may take a while, please don't interrupt!")
+
+		return bdb.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
+
+			var (
+				// We have to use different
+				// syntax for this query
+				// depending on dialect.
+				dbDialect = tx.Dialect().Name()
+
+				// ID for paging.
+				maxID string
+
+				// Batch size for
+				// selecting + updating.
+				batchsz = 100
+
+				// Number of accounts
+				// updated so far.
+				updated int
+
+				// We need to know our own host
+				// for updating instance account.
+				host = config.GetHost()
+			)
+
+			// Create the new accounts table.
+			if _, err := tx.
+				NewCreateTable().
+				ModelTableExpr("new_accounts").
+				Model(&new_gtsmodel.Account{}).
+				Exec(ctx); err != nil {
+				return err
+			}
+
+			// Count number of accounts
+			// we need to update.
+			total, err := tx.
+				NewSelect().
+				Table("accounts").
+				Count(ctx)
+			if err != nil && !errors.Is(err, db.ErrNoEntries) {
+				return err
+			}
+
+			// Create a subquery for
+			// Postgres to reuse.
+			var orderQPG *bun.RawQuery
+			if dbDialect == dialect.PG {
+				orderQPG = tx.NewRaw(
+					"(COALESCE(?, ?) || ? || ?) COLLATE ?",
+					bun.Ident("domain"), "",
+					"/@",
+					bun.Ident("username"),
+					bun.Ident("C"),
+				)
+			}
+
+			var orderQSqlite *bun.RawQuery
+			if dbDialect == dialect.SQLite {
+				orderQSqlite = tx.NewRaw(
+					"(COALESCE(?, ?) || ? || ?)",
+					bun.Ident("domain"), "",
+					"/@",
+					bun.Ident("username"),
+				)
+			}
+
+			for {
+				// Batch of old model account IDs to select.
+				oldAccountIDs := make([]string, 0, batchsz)
+
+				// Start building IDs query.
+				idsQ := tx.
+					NewSelect().
+					Table("accounts").
+					Column("id").
+					Limit(batchsz)
+
+				if dbDialect == dialect.SQLite {
+					// For SQLite we can just select
+					// our indexed expression once
+					// as a column alias.
+					idsQ = idsQ.
+						ColumnExpr(
+							"(COALESCE(?, ?) || ? || ?) AS ?",
+							bun.Ident("domain"), "",
+							"/@",
+							bun.Ident("username"),
+							bun.Ident("domain_username"),
+						)
+				}
+
+				// Return only accounts with `[domain]/@[username]`
+				// later in the alphabet (a-z) than provided maxID.
+				if maxID != "" {
+					if dbDialect == dialect.SQLite {
+						idsQ = idsQ.Where("? > ?", bun.Ident("domain_username"), maxID)
+					} else {
+						idsQ = idsQ.Where("? > ?", orderQPG, maxID)
+					}
+				}
+
+				// Page down.
+				// It's counterintuitive because it
+				// says ASC in the query, but we're
+				// going forwards in the alphabet,
+				// and z > a in a string comparison.
+				if dbDialect == dialect.SQLite {
+					idsQ = idsQ.OrderExpr("? ASC", bun.Ident("domain_username"))
+				} else {
+					idsQ = idsQ.OrderExpr("? ASC", orderQPG)
+				}
+
+				// Select this batch, providing a
+				// slice to throw away username_domain.
+				err := idsQ.Scan(ctx, &oldAccountIDs, new([]string))
+				if err != nil {
+					return err
+				}
+
+				l := len(oldAccountIDs)
+				if len(oldAccountIDs) == 0 {
+					// Nothing left
+					// to update.
+					break
+				}
+
+				// Get ready to select old accounts by their IDs.
+				oldAccounts := make([]*old_gtsmodel.Account, 0, l)
+				batchQ := tx.
+					NewSelect().
+					Model(&oldAccounts).
+					Where("? IN (?)", bun.Ident("id"), bun.In(oldAccountIDs))
+
+				// Order batch by usernameDomain
+				// to ensure paging consistent.
+				if dbDialect == dialect.SQLite {
+					batchQ = batchQ.OrderExpr("? ASC", orderQSqlite)
+				} else {
+					batchQ = batchQ.OrderExpr("? ASC", orderQPG)
+				}
+
+				// Select old accounts.
+				if err := batchQ.Scan(ctx); err != nil {
+					return err
+				}
+
+				// Convert old accounts into new accounts.
+				newAccounts := make([]*new_gtsmodel.Account, 0, l)
+				for _, oldAccount := range oldAccounts {
+
+					var actorType new_gtsmodel.AccountActorType
+					if oldAccount.Domain == "" && oldAccount.Username == host {
+						// This is our instance account, override actor
+						// type to Service, as previously it was just person.
+						actorType = new_gtsmodel.AccountActorTypeService
+					} else {
+						// Not our instance account, just parse new actor type.
+						actorType = new_gtsmodel.ParseAccountActorType(oldAccount.ActorType)
+					}
+
+					if actorType == new_gtsmodel.AccountActorTypeUnknown {
+						// This should not really happen, but it if does
+						// just warn + set to person rather than failing.
+						log.Warnf(ctx,
+							"account %s actor type %s was not a recognized actor type, falling back to Person",
+							oldAccount.ID, oldAccount.ActorType,
+						)
+						actorType = new_gtsmodel.AccountActorTypePerson
+					}
+
+					newAccount := &new_gtsmodel.Account{
+						ID:                      oldAccount.ID,
+						CreatedAt:               oldAccount.CreatedAt,
+						UpdatedAt:               oldAccount.UpdatedAt,
+						FetchedAt:               oldAccount.FetchedAt,
+						Username:                oldAccount.Username,
+						Domain:                  oldAccount.Domain,
+						AvatarMediaAttachmentID: oldAccount.AvatarMediaAttachmentID,
+						AvatarRemoteURL:         oldAccount.AvatarRemoteURL,
+						HeaderMediaAttachmentID: oldAccount.HeaderMediaAttachmentID,
+						HeaderRemoteURL:         oldAccount.HeaderRemoteURL,
+						DisplayName:             oldAccount.DisplayName,
+						EmojiIDs:                oldAccount.EmojiIDs,
+						Fields:                  oldAccount.Fields,
+						FieldsRaw:               oldAccount.FieldsRaw,
+						Note:                    oldAccount.Note,
+						NoteRaw:                 oldAccount.NoteRaw,
+						AlsoKnownAsURIs:         oldAccount.AlsoKnownAsURIs,
+						MovedToURI:              oldAccount.MovedToURI,
+						MoveID:                  oldAccount.MoveID,
+						Locked:                  oldAccount.Locked,
+						Discoverable:            oldAccount.Discoverable,
+						URI:                     oldAccount.URI,
+						URL:                     oldAccount.URL,
+						InboxURI:                oldAccount.InboxURI,
+						SharedInboxURI:          oldAccount.SharedInboxURI,
+						OutboxURI:               oldAccount.OutboxURI,
+						FollowingURI:            oldAccount.FollowingURI,
+						FollowersURI:            oldAccount.FollowersURI,
+						FeaturedCollectionURI:   oldAccount.FeaturedCollectionURI,
+						ActorType:               actorType,
+						PrivateKey:              oldAccount.PrivateKey,
+						PublicKey:               oldAccount.PublicKey,
+						PublicKeyURI:            oldAccount.PublicKeyURI,
+						PublicKeyExpiresAt:      oldAccount.PublicKeyExpiresAt,
+						SensitizedAt:            oldAccount.SensitizedAt,
+						SilencedAt:              oldAccount.SilencedAt,
+						SuspendedAt:             oldAccount.SuspendedAt,
+						SuspensionOrigin:        oldAccount.SuspensionOrigin,
+					}
+
+					newAccounts = append(newAccounts, newAccount)
+				}
+
+				// Insert this batch of accounts.
+				res, err := tx.
+					NewInsert().
+					Model(&newAccounts).
+					Returning("").
+					Exec(ctx)
+				if err != nil {
+					return err
+				}
+
+				rowsAffected, err := res.RowsAffected()
+				if err != nil {
+					return err
+				}
+
+				// Add to updated count.
+				updated += int(rowsAffected)
+				if updated == total {
+					// Done.
+					break
+				}
+
+				// Set next page.
+				fromAcct := oldAccounts[l-1]
+				maxID = fromAcct.Domain + "/@" + fromAcct.Username
+
+				// Log helpful message to admin.
+				log.Infof(ctx,
+					"migrated %d of %d accounts (next page will be from %s)",
+					updated, total, maxID,
+				)
+			}
+
+			if total != int(updated) {
+				// Return error here in order to rollback the whole transaction.
+				return fmt.Errorf("total=%d does not match updated=%d", total, updated)
+			}
+
+			log.Infof(ctx, "finished migrating %d accounts", total)
+
+			// Drop the old table.
+			log.Info(ctx, "dropping old accounts table")
+			if _, err := tx.
+				NewDropTable().
+				Table("accounts").
+				Exec(ctx); err != nil {
+				return err
+			}
+
+			// Rename new table to old table.
+			log.Info(ctx, "renaming new accounts table")
+			if _, err := tx.
+				ExecContext(
+					ctx,
+					"ALTER TABLE ? RENAME TO ?",
+					bun.Ident("new_accounts"),
+					bun.Ident("accounts"),
+				); err != nil {
+				return err
+			}
+
+			// Add all account indexes to the new table.
+			log.Info(ctx, "recreating indexes on new accounts table")
+			for index, columns := range map[string][]string{
+				"accounts_domain_idx":        {"domain"},
+				"accounts_uri_idx":           {"uri"},
+				"accounts_url_idx":           {"url"},
+				"accounts_inbox_uri_idx":     {"inbox_uri"},
+				"accounts_outbox_uri_idx":    {"outbox_uri"},
+				"accounts_followers_uri_idx": {"followers_uri"},
+				"accounts_following_uri_idx": {"following_uri"},
+			} {
+				if _, err := tx.
+					NewCreateIndex().
+					Table("accounts").
+					Index(index).
+					Column(columns...).
+					Exec(ctx); err != nil {
+					return err
+				}
+			}
+
+			if dbDialect == dialect.PG {
+				log.Info(ctx, "moving postgres constraints from old table to new table")
+
+				type spec struct {
+					old     string
+					new     string
+					columns []string
+				}
+
+				// Rename uniqueness constraints from
+				// "new_accounts_*" to "accounts_*".
+				for _, spec := range []spec{
+					{
+						old:     "new_accounts_pkey",
+						new:     "accounts_pkey",
+						columns: []string{"id"},
+					},
+					{
+						old:     "new_accounts_uri_key",
+						new:     "accounts_uri_key",
+						columns: []string{"uri"},
+					},
+					{
+						old:     "new_accounts_public_key_uri_key",
+						new:     "accounts_public_key_uri_key",
+						columns: []string{"public_key_uri"},
+					},
+				} {
+					if _, err := tx.ExecContext(
+						ctx,
+						"ALTER TABLE ? DROP CONSTRAINT IF EXISTS ?",
+						bun.Ident("public.accounts"),
+						bun.Safe(spec.old),
+					); err != nil {
+						return err
+					}
+
+					if _, err := tx.ExecContext(
+						ctx,
+						"ALTER TABLE ? ADD CONSTRAINT ? UNIQUE(?)",
+						bun.Ident("public.accounts"),
+						bun.Safe(spec.new),
+						bun.Safe(strings.Join(spec.columns, ",")),
+					); err != nil {
+						return err
+					}
+				}
+			}
+
+			return nil
+		})
+	}
+
+	down := func(ctx context.Context, db *bun.DB) error {
+		return db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
+			return nil
+		})
+	}
+
+	if err := Migrations.Register(up, down); err != nil {
+		panic(err)
+	}
+}
diff --git a/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness/common/common.go b/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness/common/common.go
new file mode 100644
index 000000000..8db9f1c31
--- /dev/null
+++ b/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness/common/common.go
@@ -0,0 +1,26 @@
+// GoToSocial
+// Copyright (C) GoToSocial Authors admin@gotosocial.org
+// SPDX-License-Identifier: AGPL-3.0-or-later
+//
+// This program is free software: you can redistribute it and/or modify
+// it under the terms of the GNU Affero General Public License as published by
+// the Free Software Foundation, either version 3 of the License, or
+// (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+// GNU Affero General Public License for more details.
+//
+// You should have received a copy of the GNU Affero General Public License
+// along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+package common
+
+import "time"
+
+type Field struct {
+	Name       string
+	Value      string
+	VerifiedAt time.Time `bun:",nullzero"`
+}
diff --git a/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness/new/account.go b/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness/new/account.go
new file mode 100644
index 000000000..55fa7f1b4
--- /dev/null
+++ b/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness/new/account.go
@@ -0,0 +1,98 @@
+// GoToSocial
+// Copyright (C) GoToSocial Authors admin@gotosocial.org
+// SPDX-License-Identifier: AGPL-3.0-or-later
+//
+// This program is free software: you can redistribute it and/or modify
+// it under the terms of the GNU Affero General Public License as published by
+// the Free Software Foundation, either version 3 of the License, or
+// (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+// GNU Affero General Public License for more details.
+//
+// You should have received a copy of the GNU Affero General Public License
+// along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+package gtsmodel
+
+import (
+	"crypto/rsa"
+	"strings"
+	"time"
+
+	"github.com/superseriousbusiness/gotosocial/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness/common"
+	"github.com/uptrace/bun"
+)
+
+type Account struct {
+	bun.BaseModel           `bun:"table:new_accounts"`
+	ID                      string           `bun:"type:CHAR(26),pk,nullzero,notnull,unique"`
+	CreatedAt               time.Time        `bun:"type:timestamptz,nullzero,notnull,default:current_timestamp"`
+	UpdatedAt               time.Time        `bun:"type:timestamptz,nullzero,notnull,default:current_timestamp"`
+	FetchedAt               time.Time        `bun:"type:timestamptz,nullzero"`
+	Username                string           `bun:",nullzero,notnull,unique:accounts_username_domain_uniq"`
+	Domain                  string           `bun:",nullzero,unique:accounts_username_domain_uniq"`
+	AvatarMediaAttachmentID string           `bun:"type:CHAR(26),nullzero"`
+	AvatarRemoteURL         string           `bun:",nullzero"`
+	HeaderMediaAttachmentID string           `bun:"type:CHAR(26),nullzero"`
+	HeaderRemoteURL         string           `bun:",nullzero"`
+	DisplayName             string           `bun:",nullzero"`
+	EmojiIDs                []string         `bun:"emojis,array"`
+	Fields                  []*common.Field  `bun:",nullzero"`
+	FieldsRaw               []*common.Field  `bun:",nullzero"`
+	Note                    string           `bun:",nullzero"`
+	NoteRaw                 string           `bun:",nullzero"`
+	MemorializedAt          time.Time        `bun:"type:timestamptz,nullzero"`
+	AlsoKnownAsURIs         []string         `bun:"also_known_as_uris,array"`
+	MovedToURI              string           `bun:",nullzero"`
+	MoveID                  string           `bun:"type:CHAR(26),nullzero"`
+	Locked                  *bool            `bun:",nullzero,notnull,default:true"`
+	Discoverable            *bool            `bun:",nullzero,notnull,default:false"`
+	URI                     string           `bun:",nullzero,notnull,unique"`
+	URL                     string           `bun:",nullzero"`
+	InboxURI                string           `bun:",nullzero"`
+	SharedInboxURI          *string          `bun:""`
+	OutboxURI               string           `bun:",nullzero"`
+	FollowingURI            string           `bun:",nullzero"`
+	FollowersURI            string           `bun:",nullzero"`
+	FeaturedCollectionURI   string           `bun:",nullzero"`
+	ActorType               AccountActorType `bun:",nullzero,notnull"`
+	PrivateKey              *rsa.PrivateKey  `bun:""`
+	PublicKey               *rsa.PublicKey   `bun:",notnull"`
+	PublicKeyURI            string           `bun:",nullzero,notnull,unique"`
+	PublicKeyExpiresAt      time.Time        `bun:"type:timestamptz,nullzero"`
+	SensitizedAt            time.Time        `bun:"type:timestamptz,nullzero"`
+	SilencedAt              time.Time        `bun:"type:timestamptz,nullzero"`
+	SuspendedAt             time.Time        `bun:"type:timestamptz,nullzero"`
+	SuspensionOrigin        string           `bun:"type:CHAR(26),nullzero"`
+}
+
+type AccountActorType int16
+
+const (
+	AccountActorTypeUnknown      AccountActorType = 0
+	AccountActorTypeApplication  AccountActorType = 1 // https://www.w3.org/TR/activitystreams-vocabulary/#dfn-application
+	AccountActorTypeGroup        AccountActorType = 2 // https://www.w3.org/TR/activitystreams-vocabulary/#dfn-group
+	AccountActorTypeOrganization AccountActorType = 3 // https://www.w3.org/TR/activitystreams-vocabulary/#dfn-organization
+	AccountActorTypePerson       AccountActorType = 4 // https://www.w3.org/TR/activitystreams-vocabulary/#dfn-person
+	AccountActorTypeService      AccountActorType = 5 // https://www.w3.org/TR/activitystreams-vocabulary/#dfn-service
+)
+
+func ParseAccountActorType(in string) AccountActorType {
+	switch strings.ToLower(in) {
+	case "application":
+		return AccountActorTypeApplication
+	case "group":
+		return AccountActorTypeGroup
+	case "organization":
+		return AccountActorTypeOrganization
+	case "person":
+		return AccountActorTypePerson
+	case "service":
+		return AccountActorTypeService
+	default:
+		return AccountActorTypeUnknown
+	}
+}
diff --git a/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness/old/account.go b/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness/old/account.go
new file mode 100644
index 000000000..a630805d4
--- /dev/null
+++ b/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness/old/account.go
@@ -0,0 +1,70 @@
+// GoToSocial
+// Copyright (C) GoToSocial Authors admin@gotosocial.org
+// SPDX-License-Identifier: AGPL-3.0-or-later
+//
+// This program is free software: you can redistribute it and/or modify
+// it under the terms of the GNU Affero General Public License as published by
+// the Free Software Foundation, either version 3 of the License, or
+// (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+// GNU Affero General Public License for more details.
+//
+// You should have received a copy of the GNU Affero General Public License
+// along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+package gtsmodel
+
+import (
+	"crypto/rsa"
+	"time"
+
+	"github.com/superseriousbusiness/gotosocial/internal/db/bundb/migrations/20250321131230_relax_account_uri_uniqueness/common"
+	"github.com/uptrace/bun"
+)
+
+type Account struct {
+	bun.BaseModel           `bun:"table:accounts"`
+	ID                      string          `bun:"type:CHAR(26),pk,nullzero,notnull,unique"`
+	CreatedAt               time.Time       `bun:"type:timestamptz,nullzero,notnull,default:current_timestamp"`
+	UpdatedAt               time.Time       `bun:"type:timestamptz,nullzero,notnull,default:current_timestamp"`
+	FetchedAt               time.Time       `bun:"type:timestamptz,nullzero"`
+	Username                string          `bun:",nullzero,notnull,unique:usernamedomain"`
+	Domain                  string          `bun:",nullzero,unique:usernamedomain"`
+	AvatarMediaAttachmentID string          `bun:"type:CHAR(26),nullzero"`
+	AvatarRemoteURL         string          `bun:",nullzero"`
+	HeaderMediaAttachmentID string          `bun:"type:CHAR(26),nullzero"`
+	HeaderRemoteURL         string          `bun:",nullzero"`
+	DisplayName             string          `bun:""`
+	EmojiIDs                []string        `bun:"emojis,array"`
+	Fields                  []*common.Field `bun:""`
+	FieldsRaw               []*common.Field `bun:""`
+	Note                    string          `bun:""`
+	NoteRaw                 string          `bun:""`
+	Memorial                *bool           `bun:",default:false"`
+	AlsoKnownAsURIs         []string        `bun:"also_known_as_uris,array"`
+	MovedToURI              string          `bun:",nullzero"`
+	MoveID                  string          `bun:"type:CHAR(26),nullzero"`
+	Bot                     *bool           `bun:",default:false"`
+	Locked                  *bool           `bun:",default:true"`
+	Discoverable            *bool           `bun:",default:false"`
+	URI                     string          `bun:",nullzero,notnull,unique"`
+	URL                     string          `bun:",nullzero,unique"`
+	InboxURI                string          `bun:",nullzero,unique"`
+	SharedInboxURI          *string         `bun:""`
+	OutboxURI               string          `bun:",nullzero,unique"`
+	FollowingURI            string          `bun:",nullzero,unique"`
+	FollowersURI            string          `bun:",nullzero,unique"`
+	FeaturedCollectionURI   string          `bun:",nullzero,unique"`
+	ActorType               string          `bun:",nullzero,notnull"`
+	PrivateKey              *rsa.PrivateKey `bun:""`
+	PublicKey               *rsa.PublicKey  `bun:",notnull"`
+	PublicKeyURI            string          `bun:",nullzero,notnull,unique"`
+	PublicKeyExpiresAt      time.Time       `bun:"type:timestamptz,nullzero"`
+	SensitizedAt            time.Time       `bun:"type:timestamptz,nullzero"`
+	SilencedAt              time.Time       `bun:"type:timestamptz,nullzero"`
+	SuspendedAt             time.Time       `bun:"type:timestamptz,nullzero"`
+	SuspensionOrigin        string          `bun:"type:CHAR(26),nullzero"`
+}