feat(storage): make active-apply key deployment-aware

[Kiran01bm]

Summary

Extend SchemaBot's active-apply identity from
(database, type, environment) to
(database, type, environment, deployment) so that two applies for the
same logical database+environment but different deployment routes no
longer block each other on the active-apply gate.

The change ships as a sequence of focused, individually revertable PRs:
additive schema → internal plumbing → behavior flip → docs → optional
schema cleanup. Each PR will link back to this issue.

Overall context

SchemaBot has a first-class environment concept and a lower-level
deployment field today used mostly as a routing key for remote
execution. That isn't expressive enough for installations where the
same logical database and the same schema need to be applied to
multiple execution locations under one control plane — for example,
the same database deployed to several regions, or the same database
deployed across several tenant accounts.

There is ongoing discussion about introducing deployments as a
first-class server-side config dimension so that the deployment matrix,
ordering, and routing are owned by server config rather than expressed
indirectly through other fields. Likely follow-up areas (each will get
its own proposal and review):

• a config-level deployments: map and an
  environments.<env>.deployments.<name> nested form,
• a --deployment CLI flag and fan-out promotion semantics with
  environment-major ordering,
• per-deployment GitHub check runs and an environment-aggregate check
  that passes only when every deployment passes,
• DSN+table-aware locking for long-running schema changes that should
  not block unrelated work on the same target.

Storage-side locking is the prerequisite for any of those: as long as
two applies for the same database+environment but different deployments
serialize through one advisory lock, the rest of the model can't make
forward progress. This issue tracks closing that gap.

Illustrative config shape (one proposed shape, not this PR)

For context only — the actual config-schema change will be proposed
separately. Today a database routes to one deployment per environment:

databases:
  payments:
    type: mysql
    environments:
      production:
        target: payments
        deployment: primary

tern_deployments:
  primary:
    production: tern-primary:9090

One direction under discussion would let a single logical database
route to multiple deployments per environment, with ordering owned by
the control plane:

environment_order:
  - staging
  - production

deployment_order:
  - region-a
  - region-b
  - region-c

deployments:
  region-a: { label: A }
  region-b: { label: B }
  region-c: { label: C }

databases:
  payments:
    type: mysql
    environments:
      production:
        deployments:
          region-a: { deployment: payments-a, target: payments }
          region-b: { deployment: payments-b, target: payments }
          region-c: { deployment: payments-c, target: payments }

tern_deployments:
  payments-a: { production: tern-a:9090 }
  payments-b: { production: tern-b:9090 }
  payments-c: { production: tern-c:9090 }

This issue does not deliver any of the above config shape — it only
closes the storage-side gap. With the current 3-tuple lock, three
parallel applies for payments / production would serialize on one
lock; with the 4-tuple lock they'll run independently.

Why

The active-apply lock today is keyed by
(database, type, environment). Two applies for the same logical
database and environment but different deployment routes block each
other on the same MySQL advisory lock and on the
applies.idx_database_env predicate. With shared storage across
multiple deployments of the same database, this is too coarse —
independent deployments end up serializing through one lock for no
operational reason.

plans.deployment and applies.deployment already persist the
deployment value, and the server-side resolver already produces a
ResolvedDatabaseTarget.Deployment. The gap is purely at the
active-apply gating layer.

What changes (illustrative)

At the end of the multi-stage effort the active-apply identity becomes:

UNIQUE KEY (database_name, database_type, environment, deployment)
KEY        (database_name, database_type, environment, deployment)

Empty deployment ('') is preserved as a synthetic default so
existing rows and existing single-deployment installations continue to
behave exactly as today.