[feature] Application creation + management via API + settings panel (#3906)

* [feature] Application creation + management via API + settings panel

* fix docs links

* add errnorows test

* use known application as shorter

* add comment about side effects

6 files changed, 295 insertions, 21 deletions

diff --git a/internal/db/application.go b/internal/db/application.go
index a3061f028..76948b0fd 100644
--- a/internal/db/application.go
+++ b/internal/db/application.go
@@ -31,11 +31,14 @@ type Application interface {
 	// GetApplicationByClientID fetches the application from the database with corresponding client_id value.
 	GetApplicationByClientID(ctx context.Context, clientID string) (*gtsmodel.Application, error)
 
+	// GetApplicationsManagedByUserID fetches a page of applications managed by the given userID.
+	GetApplicationsManagedByUserID(ctx context.Context, userID string, page *paging.Page) ([]*gtsmodel.Application, error)
+
 	// PutApplication places the new application in the database, erroring on non-unique ID or client_id.
 	PutApplication(ctx context.Context, app *gtsmodel.Application) error
 
-	// DeleteApplicationByClientID deletes the application with corresponding client_id value from the database.
-	DeleteApplicationByClientID(ctx context.Context, clientID string) error
+	// DeleteApplicationByID deletes the application with corresponding id from the database.
+	DeleteApplicationByID(ctx context.Context, id string) error
 
 	// GetAllTokens fetches all client oauth tokens from database.
 	GetAllTokens(ctx context.Context) ([]*gtsmodel.Token, error)
@@ -72,4 +75,8 @@ type Application interface {
 
 	// DeleteTokenByRefresh deletes client oauth token from database with refresh code.
 	DeleteTokenByRefresh(ctx context.Context, refresh string) error
+
+	// DeleteTokensByClientID deletes all tokens
+	// with the given clientID from the database.
+	DeleteTokensByClientID(ctx context.Context, clientID string) error
 }
diff --git a/internal/db/bundb/admin.go b/internal/db/bundb/admin.go
index a311d2fc5..02f10f44f 100644
--- a/internal/db/bundb/admin.go
+++ b/internal/db/bundb/admin.go
@@ -194,6 +194,17 @@ func (a *adminDB) NewSignup(ctx context.Context, newSignup gtsmodel.NewSignup) (
 		return nil, err
 	}
 
+	// If no app ID was set,
+	// use the instance app ID.
+	if newSignup.AppID == "" {
+		instanceApp, err := a.state.DB.GetInstanceApplication(ctx)
+		if err != nil {
+			err := gtserror.Newf("db error getting instance app: %w", err)
+			return nil, err
+		}
+		newSignup.AppID = instanceApp.ID
+	}
+
 	user = &gtsmodel.User{
 		ID:                     newUserID,
 		AccountID:              account.ID,
diff --git a/internal/db/bundb/application.go b/internal/db/bundb/application.go
index c21221c9f..1a600d620 100644
--- a/internal/db/bundb/application.go
+++ b/internal/db/bundb/application.go
@@ -56,6 +56,73 @@ func (a *applicationDB) GetApplicationByClientID(ctx context.Context, clientID s
 	)
 }
 
+func (a *applicationDB) GetApplicationsManagedByUserID(
+	ctx context.Context,
+	userID string,
+	page *paging.Page,
+) ([]*gtsmodel.Application, error) {
+	var (
+		// Get paging params.
+		minID = page.GetMin()
+		maxID = page.GetMax()
+		limit = page.GetLimit()
+		order = page.GetOrder()
+
+		// Make educated guess for slice size.
+		appIDs = make([]string, 0, limit)
+	)
+
+	// Ensure user ID.
+	if userID == "" {
+		return nil, gtserror.New("userID not set")
+	}
+
+	q := a.db.
+		NewSelect().
+		TableExpr("? AS ?", bun.Ident("applications"), bun.Ident("application")).
+		Column("application.id").
+		Where("? = ?", bun.Ident("application.managed_by_user_id"), userID)
+
+	if maxID != "" {
+		// Return only apps LOWER (ie., older) than maxID.
+		q = q.Where("? < ?", bun.Ident("application.id"), maxID)
+	}
+
+	if minID != "" {
+		// Return only apps HIGHER (ie., newer) than minID.
+		q = q.Where("? > ?", bun.Ident("application.id"), minID)
+	}
+
+	if limit > 0 {
+		q = q.Limit(limit)
+	}
+
+	if order == paging.OrderAscending {
+		// Page up.
+		q = q.Order("application.id ASC")
+	} else {
+		// Page down.
+		q = q.Order("application.id DESC")
+	}
+
+	if err := q.Scan(ctx, &appIDs); err != nil {
+		return nil, err
+	}
+
+	if len(appIDs) == 0 {
+		return nil, nil
+	}
+
+	// If we're paging up, we still want apps
+	// to be sorted by ID desc (ie., newest to
+	// oldest), so reverse ids slice.
+	if order == paging.OrderAscending {
+		slices.Reverse(appIDs)
+	}
+
+	return a.getApplicationsByIDs(ctx, appIDs)
+}
+
 func (a *applicationDB) getApplication(ctx context.Context, lookup string, dbQuery func(*gtsmodel.Application) error, keyParts ...any) (*gtsmodel.Application, error) {
 	return a.state.Caches.DB.Application.LoadOne(lookup, func() (*gtsmodel.Application, error) {
 		var app gtsmodel.Application
@@ -69,6 +136,37 @@ func (a *applicationDB) getApplication(ctx context.Context, lookup string, dbQue
 	}, keyParts...)
 }
 
+func (a *applicationDB) getApplicationsByIDs(ctx context.Context, ids []string) ([]*gtsmodel.Application, error) {
+	apps, err := a.state.Caches.DB.Application.LoadIDs("ID",
+		ids,
+		func(uncached []string) ([]*gtsmodel.Application, error) {
+			// Preallocate expected length of uncached apps.
+			apps := make([]*gtsmodel.Application, 0, len(uncached))
+
+			// Perform database query scanning
+			// the remaining (uncached) app IDs.
+			if err := a.db.NewSelect().
+				Model(&apps).
+				Where("? IN (?)", bun.Ident("id"), bun.In(uncached)).
+				Scan(ctx); err != nil {
+				return nil, err
+			}
+
+			return apps, nil
+		},
+	)
+	if err != nil {
+		return nil, err
+	}
+
+	// Reorder the apps by their
+	// IDs to ensure in correct order.
+	getID := func(t *gtsmodel.Application) string { return t.ID }
+	xslices.OrderBy(apps, ids, getID)
+
+	return apps, nil
+}
+
 func (a *applicationDB) PutApplication(ctx context.Context, app *gtsmodel.Application) error {
 	return a.state.Caches.DB.Application.Store(app, func() error {
 		_, err := a.db.NewInsert().Model(app).Exec(ctx)
@@ -76,27 +174,25 @@ func (a *applicationDB) PutApplication(ctx context.Context, app *gtsmodel.Applic
 	})
 }
 
-func (a *applicationDB) DeleteApplicationByClientID(ctx context.Context, clientID string) error {
-	// Attempt to delete application.
-	if _, err := a.db.NewDelete().
+// DeleteApplicationByID deletes application with the given ID.
+//
+// The function does not delete tokens owned by the application
+// or update statuses/accounts that used the application, since
+// the latter can be extremely expensive given the size of the
+// statuses table.
+//
+// Callers to this function should ensure that they do side
+// effects themselves (if required) before or after calling.
+func (a *applicationDB) DeleteApplicationByID(ctx context.Context, id string) error {
+	_, err := a.db.NewDelete().
 		Table("applications").
-		Where("? = ?", bun.Ident("client_id"), clientID).
-		Exec(ctx); err != nil {
+		Where("? = ?", bun.Ident("id"), id).
+		Exec(ctx)
+	if err != nil {
 		return err
 	}
 
-	// NOTE about further side effects:
-	//
-	// We don't need to handle updating any statuses or users
-	// (both of which may contain refs to applications), as
-	// DeleteApplication__() is only ever called during an
-	// account deletion, which handles deletion of the user
-	// and all their statuses already.
-	//
-
-	// Clear application from the cache.
-	a.state.Caches.DB.Application.Invalidate("ClientID", clientID)
-
+	a.state.Caches.DB.Application.Invalidate("ID", id)
 	return nil
 }
 
@@ -363,3 +459,27 @@ func (a *applicationDB) DeleteTokenByRefresh(ctx context.Context, refresh string
 	a.state.Caches.DB.Token.Invalidate("Refresh", refresh)
 	return nil
 }
+
+func (a *applicationDB) DeleteTokensByClientID(ctx context.Context, clientID string) error {
+	// Delete tokens owned by
+	// clientID and gather token IDs.
+	var tokenIDs []string
+	if _, err := a.db.
+		NewDelete().
+		Table("tokens").
+		Where("? = ?", bun.Ident("client_id"), clientID).
+		Returning("id").
+		Exec(ctx, &tokenIDs); err != nil {
+		return err
+	}
+
+	if len(tokenIDs) == 0 {
+		// Nothing was deleted,
+		// nothing to invalidate.
+		return nil
+	}
+
+	// Invalidate all deleted tokens.
+	a.state.Caches.DB.Token.InvalidateIDs("ID", tokenIDs)
+	return nil
+}
diff --git a/internal/db/bundb/application_test.go b/internal/db/bundb/application_test.go
index b6b19319c..540c632b5 100644
--- a/internal/db/bundb/application_test.go
+++ b/internal/db/bundb/application_test.go
@@ -92,7 +92,7 @@ func (suite *ApplicationTestSuite) TestDeleteApplicationBy() {
 	for _, app := range suite.testApplications {
 		for lookup, dbfunc := range map[string]func() error{
 			"client_id": func() error {
-				return suite.db.DeleteApplicationByClientID(ctx, app.ClientID)
+				return suite.db.DeleteApplicationByID(ctx, app.ID)
 			},
 		} {
 			// Clear database caches.
@@ -124,6 +124,36 @@ func (suite *ApplicationTestSuite) TestGetAllTokens() {
 	suite.NotEmpty(tokens)
 }
 
+func (suite *ApplicationTestSuite) TestDeleteTokensByClientID() {
+	ctx := context.Background()
+
+	// Delete tokens by each app.
+	for _, app := range suite.testApplications {
+		if err := suite.state.DB.DeleteTokensByClientID(ctx, app.ClientID); err != nil {
+			suite.FailNow(err.Error())
+		}
+	}
+
+	// Ensure all tokens deleted.
+	for _, token := range suite.testTokens {
+		_, err := suite.db.GetTokenByID(ctx, token.ID)
+		if !errors.Is(err, db.ErrNoEntries) {
+			suite.FailNow("", "token %s not deleted", token.ID)
+		}
+	}
+}
+
+func (suite *ApplicationTestSuite) TestDeleteTokensByUnknownClientID() {
+	// Should not return ErrNoRows even though
+	// the client with given ID doesn't exist.
+	if err := suite.state.DB.DeleteTokensByClientID(
+		context.Background(),
+		"01JPJ4NCGH6GHY7ZVYBHNP55XS",
+	); err != nil {
+		suite.FailNow(err.Error())
+	}
+}
+
 func TestApplicationTestSuite(t *testing.T) {
 	suite.Run(t, new(ApplicationTestSuite))
 }
diff --git a/internal/db/bundb/migrations/20250310144102_application_management.go b/internal/db/bundb/migrations/20250310144102_application_management.go
new file mode 100644
index 000000000..46ff9926b
--- /dev/null
+++ b/internal/db/bundb/migrations/20250310144102_application_management.go
@@ -0,0 +1,105 @@
+// 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"
+
+	"github.com/superseriousbusiness/gotosocial/internal/config"
+	"github.com/uptrace/bun"
+)
+
+func init() {
+	up := func(ctx context.Context, db *bun.DB) error {
+		return db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
+			// Add client_id index to token table,
+			// needed for invalidation if/when the
+			// token's app is deleted.
+			if _, err := tx.
+				NewCreateIndex().
+				Table("tokens").
+				Index("tokens_client_id_idx").
+				Column("client_id").
+				IfNotExists().
+				Exec(ctx); err != nil {
+				return err
+			}
+
+			// Update users to set all "created_by_application_id"
+			// values to the instance application, to correct some
+			// past issues where this wasn't set. Skip this if there's
+			// no users though, as in that case we probably don't even
+			// have an instance application yet.
+			usersLen, err := tx.
+				NewSelect().
+				Table("users").
+				Count(ctx)
+			if err != nil {
+				return err
+			}
+
+			if usersLen == 0 {
+				// Nothing to do.
+				return nil
+			}
+
+			// Get Instance account ID.
+			var instanceAcctID string
+			if err := tx.
+				NewSelect().
+				Table("accounts").
+				Column("id").
+				Where("? = ?", bun.Ident("username"), config.GetHost()).
+				Where("? IS NULL", bun.Ident("domain")).
+				Scan(ctx, &instanceAcctID); err != nil {
+				return err
+			}
+
+			// Get the instance app ID.
+			var instanceAppID string
+			if err := tx.
+				NewSelect().
+				Table("applications").
+				Column("id").
+				Where("? = ?", bun.Ident("client_id"), instanceAcctID).
+				Scan(ctx, &instanceAppID); err != nil {
+				return err
+			}
+
+			// Set instance app
+			// ID on all users.
+			if _, err := tx.
+				NewUpdate().
+				Table("users").
+				Set("? = ?", bun.Ident("created_by_application_id"), instanceAppID).
+				Exec(ctx); err != nil {
+				return err
+			}
+
+			return nil
+		})
+	}
+
+	down := func(ctx context.Context, db *bun.DB) error {
+		return nil
+	}
+
+	if err := Migrations.Register(up, down); err != nil {
+		panic(err)
+	}
+}
diff --git a/internal/db/bundb/status.go b/internal/db/bundb/status.go
index fea5594dd..8383a9c01 100644
--- a/internal/db/bundb/status.go
+++ b/internal/db/bundb/status.go
@@ -299,11 +299,12 @@ func (s *statusDB) PopulateStatus(ctx context.Context, status *gtsmodel.Status)
 
 	if status.CreatedWithApplicationID != "" && status.CreatedWithApplication == nil {
 		// Populate the status' expected CreatedWithApplication (not always set).
+		// Don't error on ErrNoEntries, as the application may have been cleaned up.
 		status.CreatedWithApplication, err = s.state.DB.GetApplicationByID(
 			gtscontext.SetBarebones(ctx),
 			status.CreatedWithApplicationID,
 		)
-		if err != nil {
+		if err != nil && !errors.Is(err, db.ErrNoEntries) {
 			errs.Appendf("error populating status application: %w", err)
 		}
 	}