Migrations fail on Cosmos DB

[marc-hughes]

Describe the Bug

When attempting to npm run payload migrate or npm run payload migrate:status with an Azure cosmos DB a query fails with "The order by query does not have a corresponding composite index that it can be served from." and the operation fails

Link to the code that reproduces this issue

https://github.com/marc-hughes/payload-cms-bug-report

Reproduction Steps

1. Create new project

npx create-payload-app

• template: Blank
• database: MongoDB
• MongoDB connection string: An Azure Cosmos DB connection string

2. Create a blank migration

npm run payload migrate:create

3. Check the status of the migrations

npm run payload migrate:status

Expected:

See the status of that one migration

Actual:

$ npm run payload  migrate:status
Debugger attached.

> [email protected] payload
> cross-env NODE_OPTIONS=--no-deprecation payload migrate:status

Debugger attached.
[15:00:42] WARN: No email adapter provided. Email will be written to console. More info at https://payloadcms.com/docs/email/overview.
[15:00:42] INFO: Reading migration files from F:\projects\test-blank\src\migrations
F:\projects\test-blank\node_modules\mongodb\src\cmap\connection.ts:525
          throw new MongoServerError((object ??= document.toObject(bsonOptions)));
                ^


MongoServerError: Error=2, Details='Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: 123b6f7d-d281-48ac-9f6e-f79d23201079; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: 123b6f7d-d281-48ac-9f6e-f79d23201079; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: 123b6f7d-d281-48ac-9f6e-f79d23201079; Reason: (Message: {"Errors":["The order by query does not have a corresponding composite index that it can be served from."]}
ActivityId: 123b6f7d-d281-48ac-9f6e-f79d23201079, Request URI: /apps/ee056cc0-380c-4306-a3a7-9cd9cfd25680/services/712f445a-a2a9-421c-b584-730e88341668/partitions/15cc7ebd-599d-4bf3-b0e9-014b6026eb6b/replicas/133793706273356977s/, RequestStats: Microsoft.Azure.Cosmos.Tracing.TraceData.ClientSideRequestStatisticsTraceDatum, SDK: Windows/10.0.20348 cosmos-netstandard-sdk/3.18.0);););
    at Connection.sendCommand (F:\projects\test-blank\node_modules\mongodb\src\cmap\connection.ts:525:17)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at Connection.command (F:\projects\test-blank\node_modules\mongodb\src\cmap\connection.ts:597:22)
    at Server.command (F:\projects\test-blank\node_modules\mongodb\src\sdam\server.ts:327:21)
    at FindOperation.execute (F:\projects\test-blank\node_modules\mongodb\src\operations\find.ts:119:12)
    at tryOperation (F:\projects\test-blank\node_modules\mongodb\src\operations\execute_operation.ts:271:14)
    at executeOperation (F:\projects\test-blank\node_modules\mongodb\src\operations\execute_operation.ts:109:12)
    at FindCursor._initialize (F:\projects\test-blank\node_modules\mongodb\src\cursor\find_cursor.ts:72:22)
    at FindCursor.cursorInit (F:\projects\test-blank\node_modules\mongodb\src\cursor\abstract_cursor.ts:722:21)
    at FindCursor.fetchBatch (F:\projects\test-blank\node_modules\mongodb\src\cursor\abstract_cursor.ts:758:7) {
  errorResponse: {
    ok: 0,
    errmsg: `Error=2, Details='Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: 123b6f7d-d281-48ac-9f6e-f79d23201079; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: 123b6f7d-d281-48ac-9f6e-f79d23201079; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: 123b6f7d-d281-48ac-9f6e-f79d23201079; Reason: (Message: {"Errors":["The order by query does not have a corresponding composite index that it can be served from."]}\r\n` +
      'ActivityId: 123b6f7d-d281-48ac-9f6e-f79d23201079, Request URI: /apps/ee056cc0-380c-4306-a3a7-9cd9cfd25680/services/712f445a-a2a9-421c-b584-730e88341668/partitions/15cc7ebd-599d-4bf3-b0e9-014b6026eb6b/replicas/133793706273356977s/, RequestStats: Microsoft.Azure.Cosmos.Tracing.TraceData.ClientSideRequestStatisticsTraceDatum, SDK: Windows/10.0.20348 cosmos-netstandard-sdk/3.18.0);););',
    code: 2,
    codeName: 'BadValue'
  },
  ok: 0,
  code: 2,
  codeName: 'BadValue',
  [Symbol(errorLabels)]: Set(0) {}
}

Node.js v18.20.2

This happens with or without indexSortableFields or transactionOptions set in the configuration.
This happens on both Payload 3.9.0 and 3.11.0 (did not test 3.10)

Which area(s) are affected? (Select all that apply)

db-mongodb

Environment Info

Binaries:
  Node: 18.20.2
  npm: N/A
  Yarn: N/A
  pnpm: N/A
Relevant Packages:
  payload: 3.11.0
  next: 15.1.0
  @payloadcms/db-mongodb: 3.11.0
  @payloadcms/email-nodemailer: 3.11.0
  @payloadcms/graphql: 3.11.0
  @payloadcms/next/utilities: 3.11.0
  @payloadcms/payload-cloud: 3.11.0
  @payloadcms/richtext-lexical: 3.11.0
  @payloadcms/translations: 3.11.0
  @payloadcms/ui/shared: 3.11.0
  react: 19.0.0
  react-dom: 19.0.0
Operating System:
  Platform: win32
  Arch: x64
  Version: Windows 10 Home
  Available memory (MB): 32699
  Available CPU cores: 16