Support composable migration sources and an upper migration target

[cbratschi]

Problem

Reusable Payload packages and plugins can own database changes while the consuming application also owns project-specific migrations.

Payload currently accepts one migrationDir. Runtime prodMigrations can receive a manually combined array, but the migration runner executes every pending migration in that array. There is no supported way to stop at the last migration approved for a particular application release.

Our current workaround is to aggregate migrations into the application and manually filter the registered production migrations. This loses source ownership and makes it easy for an application to run migrations beyond its intended release boundary.

Proposed contract

It would be useful to separate three concepts:

1. Migration sources — an ordered collection of named directories or imported migration arrays.
2. Writable migration directory — the application directory used by migrate:create.
3. Execution target — the last migration that may run, inclusive.

For example:

payload migrate --to app:20260823_120000_release

The corresponding programmatic and production migration APIs should support the same target.

Expected behavior

• Combine migrations from reusable packages/plugins and the application deterministically.
• Preserve or explicitly define ordering across sources.
• Namespace migration identities or reject duplicate names.
• Execute pending migrations only through the requested target.
• Fail if the target does not exist or is ambiguous.
• Report when the database has already advanced beyond the requested target; do not interpret the target as an automatic downgrade.
• Show migrations beyond the target as pending in migration status.
• Keep migrate:create writing to one explicitly configured application directory.
• Apply the same behavior across MongoDB and Drizzle adapters.

The open v4 CLI work in #17604 appears to provide a natural place for a typed to input, but the underlying adapter migration contract would also need to support it.

I'd be happy to help with API design, tests, or implementation once the desired source-ordering and target semantics are agreed.