Consolidate BoxProvisioning connection lifecycle (3 connections per provisioner)

[iancooper]

Background

Follow-up from PR #4039 (review comment). ADR 0053 §5 documents a single-connection design for box provisioning, but each ProvisionAsync call currently opens three separate connections:

1. DetectTableStateAsync (provisioner) — schema introspection.
2. ValidatePayloadModeAsync (payload validator) — column-type check.
3. MigrateAsync (runner) — advisory lock + migrations.

The advisory lock must live on its own connection for its lifetime, but detection and payload validation could share that connection — performed pre-lock on the same DbConnection the runner is going to use.

Why we deferred this from #4039

It's a pure refactor with no functional impact. PR #4039 already addresses the correctness gaps the reviews flagged (R1, R2, R4, R5 via spec 0027); this is a connection-pool ergonomics improvement worth doing on its own.

Proposed approach

• Open one DbConnection at the top of ProvisionAsync.
• Pass it (or a Func<DbConnection> factory) into DetectTableStateAsync → ValidatePayloadModeAsync → MigrateAsync.
• Acquire the advisory lock on that same connection before running migrations.
• Apply per-backend (MSSQL, Postgres, MySQL, SQLite, Spanner).

Acceptance

• A single ProvisionAsync call opens exactly one DbConnection.
• All existing integration tests still pass (fresh / bootstrap / idempotent / concurrent).
• Backend behaviour unchanged from the caller's perspective.

Out of scope

Any change to the public IAmABoxProvisioner / IAmABoxMigrationRunner interfaces — connection plumbing should stay an internal concern of each backend implementation.

[DeepakVermaaa]

Hi @iancooper - I'd like to take this on as a follow-up to #4114. Before I start, two questions to make sure I head in the right direction:
1. DbConnection vs Func - you mentioned both as options. My instinct is to pass the DbConnection directly: the acceptance criterion is "exactly one DbConnection per ProvisionAsync", and a factory would invite callees to open additional connections, which is the thing we're trying to avoid. Opening once at the top of ProvisionAsync with a using, then threading it through detect -> validate -> lock -> migrate, also matches the advisory-lock-on-its-own-connection requirement naturally. Happy to go with the factory if you had a specific reason in mind though - what's your preference?

2. PR scope - five backends (MSSQL, Postgres, MySQL, SQLite, Spanner) feels like a lot for a single PR to review. Would you prefer:

(a) one backend first as a pattern-setter PR, then the rest in a follow-up once the pattern is agreed, or
(b) all five in one PR?

If (a), any preference on which backend goes first? I was thinking Postgres since it has the clearest advisory lock semantics, but MSSQL works too.

I'll wait for your steer before starting the work.

[iancooper]

Hi @DeepakVermaaa

Sorry, this work is still in progress with me, as a PR, and these issues are reminders to me, to pick up some outstanding items once I have landed the main PR.