CD pipeline runs the first real database migration, gated by manual approval

Wired pipelines/cd/web.yml to actually apply drizzle-kit migrations to the live psql-devafusion-dev-uks server - a temporary named firewall rule for the CD agent, manual approval on a dedicated Azure DevOps environment, and the current migration SQL printed in CI before that approval gate is ever reached. Went through three design passes in one sitting: split detection/preview into a separate stage using a cross-stage output-variable condition whose exact syntax couldn't be verified, then discovered and removed an unsound git-diff-based gate in favour of running drizzle-kit migrate unconditionally, then diagnosed and fixed a real first-production-run failure (firewall-rule propagation delay) using a live connection test to rule out a competing hypothesis (an SSL deprecation warning) before committing to a fix - and, along the way, caused and immediately rotated a real credential exposure.

Decisions

Abandoned the cross-stage output-variable design (a detection stage feeding a condition on a later apply stage) after four separate documentation searches failed to surface Microsoft's own authoritative example of the exact expression syntax needed. Found the real answer afterward by accident (dependencies.<Stage>.outputs[...] for condition:, stageDependencies.<Stage>.<Job>.outputs[...] for variables: - two different context names for the same concept) but had already committed to the safer single-job design by then, and kept it rather than reintroduce the complexity for no remaining benefit.
Caught a real ordering bug before it shipped: the first draft put the SQL-preview step inside the same approval-gated deployment job as the migration itself. Azure DevOps environment approval checks pause a deployment job before any of its steps run, so that would have shown the pending SQL only after approval - exactly backwards. Moved the preview into pipelines/ci/web.yml's Build stage instead, which already runs on every merge with zero Azure/DB access, so the SQL is visible in the triggering CI run's log before the CD approval gate is ever reached.
Put the manual approval on a new, dedicated devafusion-dev-migrations environment rather than the existing devafusion-dev environment used by the app-deploy stage - Azure DevOps approval checks are configured per-environment, not per-stage, so reusing devafusion-dev would have forced every ordinary app deploy through the same manual gate.
Reused Microsoft's own documented temporary-firewall-rule pattern (confirmed via Azure/postgresql-action's GitHub Action docs this session's earlier slice) rather than any wide-open rule, permanent IP-range allowlist, or persistent self-hosted agent.
Percent-encoded the admin password via python3 (preinstalled on ubuntu-latest, no new dependency) before building the connection string, mirroring web.tf's own urlencode() - the raw secret may contain characters that would otherwise corrupt the URL.
Confirmed drizzle-kit migrate has no dry-run/--pretend flag (checked Drizzle's own docs directly) - the SQL-file-content preview in CI is the closest achievable equivalent to a true dry run.
Documented as an explicit, not-yet-verified risk (ADR-0013) that sc-devafusion-terraform's firewall-rule RBAC permission is inferred from an existing wildcard grant, not directly proven under that identity - to be watched on the first real ApplyMigration run.
Removed the git diff HEAD^ HEAD gate entirely after recognising it was unsound, not just narrow: comparing only against the single preceding commit permanently loses track of a pending migration if any one CD run is ever rejected at the approval gate, times out, or fails downstream - silent drift with no error. Traded a small, fixed, always-paid cost (firewall rule open/close on every CD run, even with nothing to migrate) for eliminating that correctness gap, relying on drizzle-kit migrate's own idempotency (it reads __drizzle_migrations from the live database and only applies what isn't already recorded there).
Deferred the theoretically correct fix (a checkpoint - git tag moved by CD on success, or a query against the last successful CD run via the Azure DevOps REST API) as an explicit, tracked followup rather than building it now, since the tag approach needs a new git write-back permission grant to the CD pipeline that doesn't currently exist.
The pipeline's first real production run failed after ~270ms with no further detail in the log - drizzle-kit's terminal spinner overwrites the line where a real Node error would otherwise print, and the raw Azure DevOps log (fetched directly to rule out log-rendering artefacts) confirmed no underlying error text was ever captured. Root-caused by elimination rather than a smoking-gun error message: a direct pg connection test from a long-allowlisted IP succeeded immediately with full certificate verification, ruling out the SSL deprecation warning also present in that log as the cause, leaving firewall-rule propagation delay (az create returns success before the rule actually propagates - Microsoft's own docs: up to five minutes) as the best-supported explanation, though the CD agent's exact fresh-rule scenario was not directly reproduced. Fixed with a bounded exponential-backoff retry loop around the migrate call (6 attempts, ~5 minutes worst case matching Microsoft's documented figure) rather than a blind fixed sleep.
Pinned sslmode=verify-full explicitly in both web.tf's DATABASE_URL and this pipeline's own connection string (both previously sslmode=require), after the SSL deprecation warning in that same failed run's log was initially treated as a candidate root cause. pg-connection-string currently aliases require/prefer/verify-ca to verify-full but its own warning states this will change to weaker semantics in a future major version - fixed regardless of whether it caused this specific failure, per root AGENTS.md's Deprecation Upgrades rule.
That same first run also incidentally confirmed the sc-devafusion-terraform firewall-rule RBAC permission works as expected - both the create and delete steps succeeded; only the migrate step itself failed.
Mid-diagnosis, the live PostgreSQL admin password was inadvertently displayed in plaintext in an interactive session (an az keyvault secret show call run without output suppression). Treated as a genuine exposure per root AGENTS.md's Zero Hardcoded Secrets rule rather than dismissed as harmless - rotated immediately (new password applied to the live server first, then to the Key Vault secret, in that order so the two were never inconsistent), confirmed via terraform plan that no drift resulted.
CORRECTION: the firewall-propagation-delay diagnosis was wrong. A second production run retried all 6 attempts with the exact same fast, no-error failure every time - if propagation were the cause, at least one retry should have succeeded. Bypassed drizzle-kit's CLI (which swallows the real exception behind its spinner in every environment, not just CI) with drizzle-orm's own migrate() function directly, revealing a genuine Postgres error: type "citext" does not exist. azurerm_postgresql_flexible_server_configuration's azure.extensions="CITEXT" only allowlists the extension server-wide - it never actually ran CREATE EXTENSION inside the database. Confirmed by querying pg_extension directly: citext was never created. Fixed by adding an idempotent CREATE EXTENSION IF NOT EXISTS citext; step before drizzle-kit migrate, using the pg package already available after npm ci rather than an unverified psql dependency.
Verified the fix end-to-end directly against the live server (not just re-running the pipeline): drizzle-kit migrate printed "migrations applied successfully!" and the five expected tables (account, session, user, user_security, verification) were confirmed to exist immediately afterward - the identity/MFA schema from PR #50 is now genuinely live.
CORRECTION: the citext fix's first real CD run still failed, on a genuinely different bug - ERR_MODULE_NOT_FOUND for pg, imported from a script written to /tmp. Node's ESM resolver looks for node_modules relative to the importing file's own location, not the shell's cwd, so /tmp (with no node_modules) could never resolve pg even though it was installed correctly in src/web moments earlier. Missed locally because every local verification script had been run directly from inside src/web, next to its own node_modules - a real gap between the verification environment and the actual pipeline. Fixed by writing the script to the current working directory (already cd'd into src/web) instead of /tmp.

Milestones

Added a 'Print current migration SQL for review' step to pipelines/ci/web.yml's Build stage - lists and cats every migration file under src/web/drizzle/**/*.sql, with zero Azure/DB access. Replaced an earlier git-diff-based version after the gate it supported was removed.
Added an ApplyMigration stage to pipelines/cd/web.yml, gated on the devafusion-dev-migrations environment: opens/closes a temporary named firewall rule (cd-agent-temp) for the agent's own IP, fetches the admin password from Key Vault, runs drizzle-kit migrate unconditionally with exponential-backoff retry for firewall-rule propagation delay, and always cleans up the firewall rule even on failure.
Added dependsOn: ApplyMigration to the existing Web deploy stage - app deploy only proceeds if the migration stage succeeds.
Added docs/adr/0013-cd-migration-pipeline.md, later revised in the same sitting to record the git-diff-gate removal and the deferred checkpoint-based followup.
Documented the required one-time manual Azure DevOps setup step (create devafusion-dev-migrations environment, add approvers) in infrastructure/AGENTS.md - not yet done as of this commit.
Pinned sslmode=verify-full explicitly in infrastructure/app/environments/dev/web.tf's DATABASE_URL app_setting and pipelines/cd/web.yml's own connection string, replacing sslmode=require in both.

Validation

Validated both pipelines/cd/web.yml and pipelines/ci/web.yml as syntactically correct YAML via python -c "import yaml; yaml.safe_load(...)" after every edit, including after the gate-removal rework
Confirmed the deployment-job checkout: self behaviour (not automatic, must be explicit) directly against Microsoft's own deployment-jobs documentation before relying on it
Confirmed via az postgres flexible-server firewall-rule list against the live server that the existing named-rule convention (web-app-outbound-N) this design extends is exactly what is already running in production
Confirmed against Drizzle's own documentation that drizzle-kit migrate is idempotent (reads __drizzle_migrations from the target database, applies only what isn't already recorded) before relying on that property to justify removing the git-diff gate
grep-verified no leftover migrationPending/HEAD^ references remained in either pipeline file after the rework
Confirmed against Microsoft's own Azure Postgres Flexible Server firewall-rules documentation that configuration changes can take up to five minutes to propagate, before designing the retry-with-backoff fix
Ran on the real devafusion-web-cd pipeline for the first time - failed as described above. Fetched the raw, unrendered build log directly via az devops/the Azure DevOps REST API rather than relying on the possibly spinner-mangled portal view, before drawing any conclusion from it.
Ran a live, isolated pg.Client connection test (bypassing drizzle-kit's spinner entirely) from a long-allowlisted IP with full TLS certificate verification (rejectUnauthorized: true) against the real server - succeeded immediately, which is what ruled out the SSL deprecation warning as the failure's cause and left firewall-rule propagation delay as the remaining explanation.
Re-validated pipelines/cd/web.yml as syntactically correct YAML and infrastructure/app/environments/dev/web.tf via terraform fmt -check and terraform validate after the sslmode fix. Not yet re-run end-to-end in Azure DevOps to confirm the retry fix succeeds against the live server.
Reproduced the module-resolution fix locally with the exact same script content and relative-path invocation the pipeline now uses (write ./ensure-citext.mjs, run node ./ensure-citext.mjs from inside src/web, delete it) rather than trusting the fix by inspection alone - confirmed it resolves pg correctly and the query succeeds.