[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

1 files changed, 293 insertions, 50 deletions

diff --git a/internal/gtsmodel/account.go b/internal/gtsmodel/account.go
index bb07b8b16..d681304ba 100644
--- a/internal/gtsmodel/account.go
+++ b/internal/gtsmodel/account.go
@@ -31,57 +31,247 @@ import (
 	"github.com/superseriousbusiness/gotosocial/internal/log"
 )
 
-// Account represents either a local or a remote fediverse
-// account, gotosocial or otherwise (mastodon, pleroma, etc).
+// Account represents either a local or a remote ActivityPub actor.
+// https://www.w3.org/TR/activitypub/#actor-objects
 type Account struct {
-	ID                      string           `bun:"type:CHAR(26),pk,nullzero,notnull,unique"`                    // id of this item in the database
-	CreatedAt               time.Time        `bun:"type:timestamptz,nullzero,notnull,default:current_timestamp"` // when was item created.
-	UpdatedAt               time.Time        `bun:"type:timestamptz,nullzero,notnull,default:current_timestamp"` // when was item was last updated.
-	FetchedAt               time.Time        `bun:"type:timestamptz,nullzero"`                                   // when was item (remote) last fetched.
-	Username                string           `bun:",nullzero,notnull,unique:usernamedomain"`                     // Username of the account, should just be a string of [a-zA-Z0-9_]. Can be added to domain to create the full username in the form ``[username]@[domain]`` eg., ``user_96@example.org``. Username and domain should be unique *with* each other
-	Domain                  string           `bun:",nullzero,unique:usernamedomain"`                             // Domain of the account, will be null if this is a local account, otherwise something like ``example.org``. Should be unique with username.
-	AvatarMediaAttachmentID string           `bun:"type:CHAR(26),nullzero"`                                      // Database ID of the media attachment, if present
-	AvatarMediaAttachment   *MediaAttachment `bun:"rel:belongs-to"`                                              // MediaAttachment corresponding to avatarMediaAttachmentID
-	AvatarRemoteURL         string           `bun:",nullzero"`                                                   // For a non-local account, where can the header be fetched?
-	HeaderMediaAttachmentID string           `bun:"type:CHAR(26),nullzero"`                                      // Database ID of the media attachment, if present
-	HeaderMediaAttachment   *MediaAttachment `bun:"rel:belongs-to"`                                              // MediaAttachment corresponding to headerMediaAttachmentID
-	HeaderRemoteURL         string           `bun:",nullzero"`                                                   // For a non-local account, where can the header be fetched?
-	DisplayName             string           `bun:""`                                                            // DisplayName for this account. Can be empty, then just the Username will be used for display purposes.
-	EmojiIDs                []string         `bun:"emojis,array"`                                                // Database IDs of any emojis used in this account's bio, display name, etc
-	Emojis                  []*Emoji         `bun:"attached_emojis,m2m:account_to_emojis"`                       // Emojis corresponding to emojiIDs. https://bun.uptrace.dev/guide/relations.html#many-to-many-relation
-	Fields                  []*Field         `bun:""`                                                            // A slice of of fields that this account has added to their profile.
-	FieldsRaw               []*Field         `bun:""`                                                            // The raw (unparsed) content of fields that this account has added to their profile, without conversion to HTML, only available when requester = target
-	Note                    string           `bun:""`                                                            // A note that this account has on their profile (ie., the account's bio/description of themselves)
-	NoteRaw                 string           `bun:""`                                                            // The raw contents of .Note without conversion to HTML, only available when requester = target
-	Memorial                *bool            `bun:",default:false"`                                              // Is this a memorial account, ie., has the user passed away?
-	AlsoKnownAsURIs         []string         `bun:"also_known_as_uris,array"`                                    // This account is associated with these account URIs.
-	AlsoKnownAs             []*Account       `bun:"-"`                                                           // This account is associated with these accounts (field not stored in the db).
-	MovedToURI              string           `bun:",nullzero"`                                                   // This account has (or claims to have) moved to this account URI. Even if this field is set the move may not yet have been processed. Check `move` for this.
-	MovedTo                 *Account         `bun:"-"`                                                           // This account has moved to this account (field not stored in the db).
-	MoveID                  string           `bun:"type:CHAR(26),nullzero"`                                      // ID of a Move in the database for this account. Only set if we received or created a Move activity for which this account URI was the origin.
-	Move                    *Move            `bun:"-"`                                                           // Move corresponding to MoveID, if set.
-	Bot                     *bool            `bun:",default:false"`                                              // Does this account identify itself as a bot?
-	Locked                  *bool            `bun:",default:true"`                                               // Does this account need an approval for new followers?
-	Discoverable            *bool            `bun:",default:false"`                                              // Should this account be shown in the instance's profile directory?
-	URI                     string           `bun:",nullzero,notnull,unique"`                                    // ActivityPub URI for this account.
-	URL                     string           `bun:",nullzero,unique"`                                            // Web URL for this account's profile
-	InboxURI                string           `bun:",nullzero,unique"`                                            // Address of this account's ActivityPub inbox, for sending activity to
-	SharedInboxURI          *string          `bun:""`                                                            // Address of this account's ActivityPub sharedInbox. Gotcha warning: this is a string pointer because it has three possible states: 1. We don't know yet if the account has a shared inbox -- null. 2. We know it doesn't have a shared inbox -- empty string. 3. We know it does have a shared inbox -- url string.
-	OutboxURI               string           `bun:",nullzero,unique"`                                            // Address of this account's activitypub outbox
-	FollowingURI            string           `bun:",nullzero,unique"`                                            // URI for getting the following list of this account
-	FollowersURI            string           `bun:",nullzero,unique"`                                            // URI for getting the followers list of this account
-	FeaturedCollectionURI   string           `bun:",nullzero,unique"`                                            // URL for getting the featured collection list of this account
-	ActorType               string           `bun:",nullzero,notnull"`                                           // What type of activitypub actor is this account?
-	PrivateKey              *rsa.PrivateKey  `bun:""`                                                            // Privatekey for signing activitypub requests, will only be defined for local accounts
-	PublicKey               *rsa.PublicKey   `bun:",notnull"`                                                    // Publickey for authorizing signed activitypub requests, will be defined for both local and remote accounts
-	PublicKeyURI            string           `bun:",nullzero,notnull,unique"`                                    // Web-reachable location of this account's public key
-	PublicKeyExpiresAt      time.Time        `bun:"type:timestamptz,nullzero"`                                   // PublicKey will expire/has expired at given time, and should be fetched again as appropriate. Only ever set for remote accounts.
-	SensitizedAt            time.Time        `bun:"type:timestamptz,nullzero"`                                   // When was this account set to have all its media shown as sensitive?
-	SilencedAt              time.Time        `bun:"type:timestamptz,nullzero"`                                   // When was this account silenced (eg., statuses only visible to followers, not public)?
-	SuspendedAt             time.Time        `bun:"type:timestamptz,nullzero"`                                   // When was this account suspended (eg., don't allow it to log in/post, don't accept media/posts from this account)
-	SuspensionOrigin        string           `bun:"type:CHAR(26),nullzero"`                                      // id of the database entry that caused this account to become suspended -- can be an account ID or a domain block ID
-	Settings                *AccountSettings `bun:"-"`                                                           // gtsmodel.AccountSettings for this account.
-	Stats                   *AccountStats    `bun:"-"`                                                           // gtsmodel.AccountStats for this account.
+	// Database ID of the account.
+	ID string `bun:"type:CHAR(26),pk,nullzero,notnull,unique"`
+
+	// Datetime when the account was created.
+	// Corresponds to ActivityStreams `published` prop.
+	CreatedAt time.Time `bun:"type:timestamptz,nullzero,notnull,default:current_timestamp"`
+
+	// Datetime when was the account was last updated,
+	// ie., when the actor last sent out an Update
+	// activity, or if never, when it was `published`.
+	UpdatedAt time.Time `bun:"type:timestamptz,nullzero,notnull,default:current_timestamp"`
+
+	// Datetime when the account was last fetched /
+	// dereferenced by this GoToSocial instance.
+	FetchedAt time.Time `bun:"type:timestamptz,nullzero"`
+
+	// Username of the account.
+	//
+	// Corresponds to AS `preferredUsername` prop, which gives
+	// no uniqueness guarantee. However, we do enforce uniqueness
+	// for it as, in practice, it always is and we rely on this.
+	Username string `bun:",nullzero,notnull,unique:accounts_username_domain_uniq"`
+
+	// Domain of the account, discovered via webfinger.
+	//
+	// Null if this is a local account, otherwise
+	// something like `example.org`.
+	Domain string `bun:",nullzero,unique:accounts_username_domain_uniq"`
+
+	// Database ID of the account's avatar MediaAttachment, if set.
+	AvatarMediaAttachmentID string `bun:"type:CHAR(26),nullzero"`
+
+	// MediaAttachment corresponding to AvatarMediaAttachmentID.
+	AvatarMediaAttachment *MediaAttachment `bun:"-"`
+
+	// URL of the avatar media.
+	//
+	// Null for local accounts.
+	AvatarRemoteURL string `bun:",nullzero"`
+
+	// Database ID of the account's header MediaAttachment, if set.
+	HeaderMediaAttachmentID string `bun:"type:CHAR(26),nullzero"`
+
+	// MediaAttachment corresponding to HeaderMediaAttachmentID.
+	HeaderMediaAttachment *MediaAttachment `bun:"-"`
+
+	// URL of the header media.
+	//
+	// Null for local accounts.
+	HeaderRemoteURL string `bun:",nullzero"`
+
+	// Display name for this account, if set.
+	//
+	// Corresponds to the ActivityStreams `name` property.
+	//
+	// If null, fall back to username for display purposes.
+	DisplayName string `bun:",nullzero"`
+
+	// Database IDs of any emojis used in
+	// this account's bio, display name, etc
+	EmojiIDs []string `bun:"emojis,array"`
+
+	// Emojis corresponding to EmojiIDs.
+	Emojis []*Emoji `bun:"-"`
+
+	// A slice of of key/value fields that
+	// this account has added to their profile.
+	//
+	// Corresponds to schema.org PropertyValue types in `attachments`.
+	Fields []*Field `bun:",nullzero"`
+
+	// The raw (unparsed) content of fields that this
+	// account has added to their profile, before
+	// conversion to HTML.
+	//
+	// Only set for local accounts.
+	FieldsRaw []*Field `bun:",nullzero"`
+
+	// A note that this account has on their profile
+	// (ie., the account's bio/description of themselves).
+	//
+	// Corresponds to the ActivityStreams `summary` property.
+	Note string `bun:",nullzero"`
+
+	// The raw (unparsed) version of Note, before conversion to HTML.
+	//
+	// Only set for local accounts.
+	NoteRaw string `bun:",nullzero"`
+
+	// ActivityPub URI/IDs by which this account is also known.
+	//
+	// Corresponds to the ActivityStreams `alsoKnownAs` property.
+	AlsoKnownAsURIs []string `bun:"also_known_as_uris,array"`
+
+	// Accounts matching AlsoKnownAsURIs.
+	AlsoKnownAs []*Account `bun:"-"`
+
+	// URI/ID to which the account has (or claims to have) moved.
+	//
+	// Corresponds to the ActivityStreams `movedTo` property.
+	//
+	// Even if this field is set the move may not yet have been
+	// processed. Check `move` for this.
+	MovedToURI string `bun:",nullzero"`
+
+	// Account matching MovedToURI.
+	MovedTo *Account `bun:"-"`
+
+	// ID of a Move in the database for this account.
+	// Only set if we received or created a Move activity
+	// for which this account URI was the origin.
+	MoveID string `bun:"type:CHAR(26),nullzero"`
+
+	// Move corresponding to MoveID, if set.
+	Move *Move `bun:"-"`
+
+	// True if account requires manual approval of Follows.
+	//
+	// Corresponds to AS `manuallyApprovesFollowers` prop.
+	Locked *bool `bun:",nullzero,notnull,default:true"`
+
+	// True if account has opted in to being shown in
+	// directories and exposed to search engines.
+	//
+	// Corresponds to the toot `discoverable` property.
+	Discoverable *bool `bun:",nullzero,notnull,default:false"`
+
+	// ActivityPub URI/ID for this account.
+	//
+	// Must be set, must be unique.
+	URI string `bun:",nullzero,notnull,unique"`
+
+	// URL at which a web representation of this
+	// account should be available, if set.
+	//
+	// Corresponds to ActivityStreams `url` prop.
+	URL string `bun:",nullzero"`
+
+	// URI of the actor's inbox.
+	//
+	// Corresponds to ActivityPub `inbox` property.
+	//
+	// According to AP this MUST be set, but some
+	// implementations don't set it for service actors.
+	InboxURI string `bun:",nullzero"`
+
+	// URI/ID of this account's sharedInbox, if set.
+	//
+	// Corresponds to ActivityPub `endpoints.sharedInbox`.
+	//
+	// Gotcha warning: this is a string pointer because
+	// it has three possible states:
+	//
+	//   1. null: We don't know (yet) if actor has a shared inbox.
+	//   2. empty: We know it doesn't have a shared inbox.
+	//   3. not empty: We know it does have a shared inbox.
+	SharedInboxURI *string `bun:""`
+
+	// URI/ID of the actor's outbox.
+	//
+	// Corresponds to ActivityPub `outbox` property.
+	//
+	// According to AP this MUST be set, but some
+	// implementations don't set it for service actors.
+	OutboxURI string `bun:",nullzero"`
+
+	// URI/ID of the actor's following collection.
+	//
+	// Corresponds to ActivityPub `following` property.
+	//
+	// According to AP this SHOULD be set.
+	FollowingURI string `bun:",nullzero"`
+
+	// URI/ID of the actor's followers collection.
+	//
+	// Corresponds to ActivityPub `followers` property.
+	//
+	// According to AP this SHOULD be set.
+	FollowersURI string `bun:",nullzero"`
+
+	// URI/ID of the actor's featured collection.
+	//
+	// Corresponds to the Toot `featured` property.
+	FeaturedCollectionURI string `bun:",nullzero"`
+
+	// ActivityStreams type of the actor.
+	//
+	// Application, Group, Organization, Person, or Service.
+	ActorType AccountActorType `bun:",nullzero,notnull"`
+
+	// Private key for signing http requests.
+	//
+	// Only defined for local accounts
+	PrivateKey *rsa.PrivateKey `bun:""`
+
+	// Public key for authorizing signed http requests.
+	//
+	// Defined for both local and remote accounts
+	PublicKey *rsa.PublicKey `bun:",notnull"`
+
+	// Dereferenceable location of this actor's public key.
+	//
+	// Corresponds to https://w3id.org/security/v1 `publicKey.id`.
+	PublicKeyURI string `bun:",nullzero,notnull,unique"`
+
+	// Datetime at which public key will expire/has expired,
+	// and should be fetched again as appropriate.
+	//
+	// Only ever set for remote accounts.
+	PublicKeyExpiresAt time.Time `bun:"type:timestamptz,nullzero"`
+
+	// Datetime at which account was marked as a "memorial",
+	// ie., user owning the account has passed away.
+	MemorializedAt time.Time `bun:"type:timestamptz,nullzero"`
+
+	// Datetime at which account was set to
+	// have all its media shown as sensitive.
+	SensitizedAt time.Time `bun:"type:timestamptz,nullzero"`
+
+	// Datetime at which account was silenced.
+	SilencedAt time.Time `bun:"type:timestamptz,nullzero"`
+
+	// Datetime at which account was suspended.
+	SuspendedAt time.Time `bun:"type:timestamptz,nullzero"`
+
+	// ID of the database entry that caused this account to
+	// be suspended. Can be an account ID or a domain block ID.
+	SuspensionOrigin string `bun:"type:CHAR(26),nullzero"`
+
+	// gtsmodel.AccountSettings for this account.
+	//
+	// Local, non-instance-actor accounts only.
+	Settings *AccountSettings `bun:"-"`
+
+	// gtsmodel.AccountStats for this account.
+	//
+	// Local accounts only.
+	Stats *AccountStats `bun:"-"`
 }
 
 // UsernameDomain returns account @username@domain (missing domain if local).
@@ -215,6 +405,59 @@ type Field struct {
 	VerifiedAt time.Time `bun:",nullzero"` // This field was verified at (optional).
 }
 
+// AccountActorType is the ActivityStreams type of an actor.
+type AccountActorType enumType
+
+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
+)
+
+// String returns a stringified form of AccountActorType.
+func (t AccountActorType) String() string {
+	switch t {
+	case AccountActorTypeApplication:
+		return "Application"
+	case AccountActorTypeGroup:
+		return "Group"
+	case AccountActorTypeOrganization:
+		return "Organization"
+	case AccountActorTypePerson:
+		return "Person"
+	case AccountActorTypeService:
+		return "Service"
+	default:
+		panic("invalid notification type")
+	}
+}
+
+// ParseAccountActorType returns an
+// actor type from the given value.
+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
+	}
+}
+
+func (t AccountActorType) IsBot() bool {
+	return t == AccountActorTypeApplication || t == AccountActorTypeService
+}
+
 // Relationship describes a requester's relationship with another account.
 type Relationship struct {
 	ID                  string // The account id.