Dec 1, 2025 · Nov 24, 2025
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
 returnq.db.DeleteCustomRole(ctx,arg)
 }

 func (q*querier)DeleteExpiredAPIKeys(ctx context.Context,arg database.DeleteExpiredAPIKeysParams) (int64,error) {
 // Requires DELETE across all API keys.
 iferr:=q.authorizeContext(ctx,policy.ActionDelete,rbac.ResourceApiKey);err!=nil {
 return0,err
 }

 returnq.db.DeleteExpiredAPIKeys(ctx,arg)
 }

 func (q*querier)DeleteExternalAuthLink(ctx context.Context,arg database.DeleteExternalAuthLinkParams)error {
 returnfetchAndExec(q.log,q.auth,policy.ActionUpdatePersonal,func(ctx context.Context,arg database.DeleteExternalAuthLinkParams) (database.ExternalAuthLink,error) {
 //nolint:gosimple
diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
 dbm.EXPECT().DeleteAPIKeyByID(gomock.Any(),key.ID).Return(nil).AnyTimes()
 check.Args(key.ID).Asserts(key,policy.ActionDelete).Returns()
 }))
 s.Run("DeleteExpiredAPIKeys",s.Mocked(func(dbm*dbmock.MockStore,faker*gofakeit.Faker,check*expects) {
 args:= database.DeleteExpiredAPIKeysParams{
 Before:time.Date(2025,11,21,0,0,0,0,time.UTC),
 LimitCount:1000,
 }
 dbm.EXPECT().DeleteExpiredAPIKeys(gomock.Any(),args).Return(int64(0),nil).AnyTimes()
 check.Args(args).Asserts(rbac.ResourceApiKey,policy.ActionDelete).Returns(int64(0))
 }))
 s.Run("GetAPIKeyByID",s.Mocked(func(dbm*dbmock.MockStore,faker*gofakeit.Faker,check*expects) {
 key:=testutil.Fake(s.T(),faker, database.APIKey{})
 dbm.EXPECT().GetAPIKeyByID(gomock.Any(),key.ID).Return(key,nil).AnyTimes()
diff --git a/coderd/database/dbgen/dbgen.go b/coderd/database/dbgen/dbgen.go
 }
 }

 // It does not make sense for the created_at to be after the expires_at.
 // So if expires is set, change the default created_at to be 24 hours before.
 varcreatedAt time.Time
 if!seed.ExpiresAt.IsZero()&&seed.CreatedAt.IsZero() {
 createdAt=seed.ExpiresAt.Add(-24*time.Hour)
 }

 params:= database.InsertAPIKeyParams{
 ID:takeFirst(seed.ID,id),
 // 0 defaults to 86400 at the db layer
 UserID:takeFirst(seed.UserID,uuid.New()),
 LastUsed:takeFirst(seed.LastUsed,dbtime.Now()),
 ExpiresAt:takeFirst(seed.ExpiresAt,dbtime.Now().Add(time.Hour)),
 CreatedAt:takeFirst(seed.CreatedAt,dbtime.Now()),
 CreatedAt:takeFirst(seed.CreatedAt,createdAt,dbtime.Now()),
 UpdatedAt:takeFirst(seed.UpdatedAt,dbtime.Now()),
 LoginType:takeFirst(seed.LoginType,database.LoginTypePassword),
 Scope:takeFirst(seed.Scope,database.APIKeyScopeAll),
diff --git a/coderd/database/dbmetrics/querymetrics.go b/coderd/database/dbmetrics/querymetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/dbpurge/dbpurge.go b/coderd/database/dbpurge/dbpurge.go
 iferr:=tx.ExpirePrebuildsAPIKeys(ctx,dbtime.Time(start));err!=nil {
 returnxerrors.Errorf("failed to expire prebuilds user api keys: %w",err)
 }
 expiredAPIKeys,err:=tx.DeleteExpiredAPIKeys(ctx, database.DeleteExpiredAPIKeysParams{
 // Leave expired keys for a week to allow the backend to know the difference
 // between a 404 and an expired key. This purge code is just to bound the size of
 // the table to something more reasonable.
 Before:dbtime.Time(start.Add(time.Hour*24*7*-1)),
 // There could be a lot of expired keys here, so set a limit to prevent this
 // taking too long.
 // This runs every 10 minutes, so it deletes ~1.5m keys per day at most.
 LimitCount:10000,
 })
 iferr!=nil {
 returnxerrors.Errorf("failed to delete expired api keys: %w",err)
 }

 deleteOldAuditLogConnectionEventsBefore:=start.Add(-maxAuditLogConnectionEventAge)
 iferr:=tx.DeleteOldAuditLogConnectionEvents(ctx, database.DeleteOldAuditLogConnectionEventsParams{
 returnxerrors.Errorf("failed to delete old audit log connection events: %w",err)
 }

 logger.Debug(ctx,"purged old database entries",slog.F("duration",clk.Since(start)))
 logger.Debug(ctx,"purged old database entries",slog.F("expired_api_keys",expiredAPIKeys),slog.F("duration",clk.Since(start)))

 returnnil
 },database.DefaultTXOptions().WithID("db_purge"));err!=nil {
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/apikeys.sql b/coderd/database/queries/apikeys.sql
 WHERE
 user_id= $1;

 -- name: DeleteExpiredAPIKeys :one
 WITH expired_keysAS (
 SELECT id
 FROM api_keys
 -- expired keys only
 WHERE expires_at< @before::timestamptz
 LIMIT @limit_count
 ),
 deleted_rowsAS (
 DELETEFROM
 api_keys
 USING
 expired_keys
 WHERE
 api_keys.id=expired_keys.id
 RETURNINGapi_keys.id
 )
 SELECTCOUNT(deleted_rows.id)AS deleted_countFROM deleted_rows;
 ;

 -- name: ExpirePrebuildsAPIKeys :exec
 -- Firstly, collect api_keys owned by the prebuilds user that correlate
 -- to workspaces no longer owned by the prebuilds user.
Original file line number	Diff line number	Diff line change
Expand Up		@@ -1509,6 +1509,15 @@ func (q *querier) DeleteCustomRole(ctx context.Context, arg database.DeleteCusto
		returnq.db.DeleteCustomRole(ctx,arg)
		}

		func (q*querier)DeleteExpiredAPIKeys(ctx context.Context,arg database.DeleteExpiredAPIKeysParams) (int64,error) {
		// Requires DELETE across all API keys.
		iferr:=q.authorizeContext(ctx,policy.ActionDelete,rbac.ResourceApiKey);err!=nil {
		return0,err
		}

		returnq.db.DeleteExpiredAPIKeys(ctx,arg)
		}

		func (q*querier)DeleteExternalAuthLink(ctx context.Context,arg database.DeleteExternalAuthLinkParams)error {
		returnfetchAndExec(q.log,q.auth,policy.ActionUpdatePersonal,func(ctx context.Context,arg database.DeleteExternalAuthLinkParams) (database.ExternalAuthLink,error) {
		//nolint:gosimple
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -213,6 +213,14 @@ func (s *MethodTestSuite) TestAPIKey() {
		dbm.EXPECT().DeleteAPIKeyByID(gomock.Any(),key.ID).Return(nil).AnyTimes()
		check.Args(key.ID).Asserts(key,policy.ActionDelete).Returns()
		}))
		s.Run("DeleteExpiredAPIKeys",s.Mocked(func(dbmdbmock.MockStore,fakergofakeit.Faker,check*expects) {
		args:= database.DeleteExpiredAPIKeysParams{
		Before:time.Date(2025,11,21,0,0,0,0,time.UTC),
		LimitCount:1000,
		}
		dbm.EXPECT().DeleteExpiredAPIKeys(gomock.Any(),args).Return(int64(0),nil).AnyTimes()
		check.Args(args).Asserts(rbac.ResourceApiKey,policy.ActionDelete).Returns(int64(0))
		}))
		s.Run("GetAPIKeyByID",s.Mocked(func(dbmdbmock.MockStore,fakergofakeit.Faker,check*expects) {
		key:=testutil.Fake(s.T(),faker, database.APIKey{})
		dbm.EXPECT().GetAPIKeyByID(gomock.Any(),key.ID).Return(key,nil).AnyTimes()
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -173,6 +173,13 @@ func APIKey(t testing.TB, db database.Store, seed database.APIKey, munge ...func
		}
		}

		// It does not make sense for the created_at to be after the expires_at.
		// So if expires is set, change the default created_at to be 24 hours before.
		varcreatedAt time.Time
		if!seed.ExpiresAt.IsZero()&&seed.CreatedAt.IsZero() {
		createdAt=seed.ExpiresAt.Add(-24*time.Hour)
		}

		params:= database.InsertAPIKeyParams{
		ID:takeFirst(seed.ID,id),
		// 0 defaults to 86400 at the db layer
Expand All		@@ -182,7 +189,7 @@ func APIKey(t testing.TB, db database.Store, seed database.APIKey, munge ...func
		UserID:takeFirst(seed.UserID,uuid.New()),
		LastUsed:takeFirst(seed.LastUsed,dbtime.Now()),
		ExpiresAt:takeFirst(seed.ExpiresAt,dbtime.Now().Add(time.Hour)),
		CreatedAt:takeFirst(seed.CreatedAt,dbtime.Now()),
		CreatedAt:takeFirst(seed.CreatedAt,createdAt,dbtime.Now()),
		UpdatedAt:takeFirst(seed.UpdatedAt,dbtime.Now()),
		LoginType:takeFirst(seed.LoginType,database.LoginTypePassword),
		Scope:takeFirst(seed.Scope,database.APIKeyScopeAll),
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -71,6 +71,19 @@ func New(ctx context.Context, logger slog.Logger, db database.Store, clk quartz.
		iferr:=tx.ExpirePrebuildsAPIKeys(ctx,dbtime.Time(start));err!=nil {
		returnxerrors.Errorf("failed to expire prebuilds user api keys: %w",err)
		}
		expiredAPIKeys,err:=tx.DeleteExpiredAPIKeys(ctx, database.DeleteExpiredAPIKeysParams{
		// Leave expired keys for a week to allow the backend to know the difference
		// between a 404 and an expired key. This purge code is just to bound the size of
		// the table to something more reasonable.
		Before:dbtime.Time(start.Add(time.Hour247*-1)),
		// There could be a lot of expired keys here, so set a limit to prevent this
		// taking too long.
		// This runs every 10 minutes, so it deletes ~1.5m keys per day at most.
		LimitCount:10000,
		})
		iferr!=nil {
		returnxerrors.Errorf("failed to delete expired api keys: %w",err)
		}

		deleteOldAuditLogConnectionEventsBefore:=start.Add(-maxAuditLogConnectionEventAge)
		iferr:=tx.DeleteOldAuditLogConnectionEvents(ctx, database.DeleteOldAuditLogConnectionEventsParams{
Expand All		@@ -80,7 +93,7 @@ func New(ctx context.Context, logger slog.Logger, db database.Store, clk quartz.
		returnxerrors.Errorf("failed to delete old audit log connection events: %w",err)
		}

		logger.Debug(ctx,"purged old database entries",slog.F("duration",clk.Since(start)))
		logger.Debug(ctx,"purged old database entries",slog.F("expired_api_keys",expiredAPIKeys),slog.F("duration",clk.Since(start)))

		returnnil
		},database.DefaultTXOptions().WithID("db_purge"));err!=nil {
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -84,6 +84,26 @@ DELETE FROM
		WHERE
		user_id= $1;

		-- name: DeleteExpiredAPIKeys :one
		WITH expired_keysAS (
		SELECT id
		FROM api_keys
		-- expired keys only
		WHERE expires_at< @before::timestamptz
		LIMIT @limit_count
		),
		deleted_rowsAS (
		DELETEFROM
		api_keys
		USING
		expired_keys
		WHERE
		api_keys.id=expired_keys.id
		RETURNINGapi_keys.id
		)
		SELECTCOUNT(deleted_rows.id)AS deleted_countFROM deleted_rows;
		;

		-- name: ExpirePrebuildsAPIKeys :exec
		-- Firstly, collect api_keys owned by the prebuilds user that correlate
		-- to workspaces no longer owned by the prebuilds user.
Expand Down