[CHORE]: AUTH_ENCRYPTION_SECRET migration script for 1.0.7 → 1.0.8 upgrade path

[prakhar-singh1928]

🔧 Chore Summary

Provide a migration script and upgrade guide for operators moving from 1.0.7 → 1.0.8. Release 1.0.7 (PR #6073) relaxed AUTH_ENCRYPTION_SECRET enforcement in ENVIRONMENT=development, allowing weak values like my-test-salt at startup. Release 1.0.8 restores the unconditional hard-fail. Operators who deployed 1.0.7 with a weak secret face two silent breaking changes on upgrade: startup refusal and decryption failures on stored credentials. This chore ships a script that handles the re-encryption pass so the upgrade path is safe and documented.

---

🧱 Area Affected

• GitHub Actions / CI Pipelines
• Pre-commit hooks / linters
• Formatting (black, isort, ruff, etc.)
• Type-checking (mypy, pyright, pytype, etc.)
• Dependency cleanup or updates
• Build system or Makefile
• Containerization (Docker/Podman)
• Docs or spellcheck
• SBOM, CVE scans, licenses, or security checks
• Other: Migration script (mcpgateway/scripts/), upgrade notes (CHANGELOG)

---

⚙️ Context / Rationale

Two distinct failure modes operators will hit after upgrading from 1.0.7 to 1.0.8:

1. Startup failure — the gateway refuses to start because the existing weak AUTH_ENCRYPTION_SECRET in .env (e.g. my-test-salt) no longer passes startup validation. Fix: generate and set a strong value.

2. Silent decryption failure — the gateway starts (operator set a new strong secret) but stored credentials silently fail to decrypt because they were encrypted under the old weak key. Fix: re-encrypt all affected database rows under the new key.

Without a migration script, operators have no automated path to handle case 2. Stored OAuth credentials, gateway tokens, and any other AES-encrypted columns will be silently broken until rows are manually re-encrypted or deleted and re-created.

This script must ship alongside the 1.0.8 guardrail restore so the breaking change has a documented, automated remedy available at release time.

---

📦 Related Make Targets

• make init-secrets-patch-env — generates a strong AUTH_ENCRYPTION_SECRET into .env.secrets; operators run this first to get their new key
• make check-env — validates .env is compliant before starting the gateway
• make migrate-enc-secret OLD_KEY=<old> NEW_KEY=<new> — proposed new target to invoke the migration script

---

📋 Acceptance Criteria

• Migration script at mcpgateway/scripts/migrate_enc_secret.py
• Script accepts --old-key and --new-key CLI arguments (falls back to env vars if not provided)
• Script re-encrypts all affected database columns under the new key atomically
• Script is idempotent — running it twice produces no double-encryption or data corruption
• Script prints a clear migration summary (rows found, rows migrated, rows skipped, errors)
• Script exits non-zero on any decryption or re-encryption failure so operators know it did not complete cleanly
• make migrate-enc-secret Makefile target added
• CHANGELOG / upgrade notes document the 1.0.7 → 1.0.8 breaking change and link to the script
• Unit tests cover: successful re-encryption, idempotency, wrong-old-key detection, partial failure handling
• Linter runs cleanly (make lint)
• CI passes with no regressions
• Docs/tooling updated (if applicable)
• Security scans pass

---

🧩 Additional Context

• Affected columns: any column encrypted via AUTH_ENCRYPTION_SECRET — audit mcpgateway/db.py and mcpgateway/services/ for AES encryption helper call sites to produce the full column list before writing the script.
• Blocked by: the PR restoring the guardrail (this sprint). Migration script must merge before or alongside 1.0.8 release.
• Related: PR migration-fix #6073 (introduced the 1.0.7 relaxation), the guardrail-restore PR (this sprint).
• Operator upgrade sequence (to be documented in CHANGELOG):
  1. make init-secrets-patch-env — writes a strong AUTH_ENCRYPTION_SECRET to .env.secrets
  2. Set AUTH_ENCRYPTION_SECRET=<new strong value> in .env
  3. python -m mcpgateway.scripts.migrate_enc_secret --old-key <old> --new-key <new> — re-encrypts stored rows
  4. Start the gateway normally