[CI] "Migrate production database" failed on main (3ce0f13)

[github-actions]

Workflow: Migrate production database
Branch: main
Commit: 3ce0f13f11e793a05c6818740a50f0496c85edee
Triggered by: @RJK134 via push
Run: https://github.com/RJK134/EquiSmile/actions/runs/27402866436

Auto-filed by ci-failure-to-issue.yml. Bug label will trigger Claude Code auto-fix.

@claude please investigate this CI failure: read the failing workflow logs at https://github.com/RJK134/EquiSmile/actions/runs/27402866436, identify the root cause, and open a fix PR per the repo's CLAUDE.md guidelines.

[RJK134]

Root cause + fix → PR #258

Root cause. The failing run (commit 3ce0f13, Phase 2 / #244) tried to apply migration 20260612000000_inventory_stock_movements, which used bare CREATE TYPE / ADD COLUMN / CREATE TABLE. Production was historically synced with prisma db push (see KI-018), so those objects already existed and the first prisma migrate deploy failed with a Postgres "already exists" error — exiting non-zero with the raw DB error, not the P3018 code. The workflow's self-heal retry loop only matched P3018, so it bailed on attempt 1 ("migrate deploy failed for an unexpected reason"). This was a migration/code bug, not a missing-secret problem (the secret is set — the run got past the presence check and ran npm ci before failing at the migrate step).

Current production state is healthy. A later merge to main saw the now-blocked queue surface as P3018, the loop auto-resolved it, and deploy unblocked — the most recent Migrate production database runs (Phase 44 27448177912, Phase 41 27448332824) are green, and production is migrated + seeded. No manual _prisma_migrations surgery was needed.

Fix (PR #258):

1. The inventory migration SQL is now idempotent (DO $$ … IF NOT EXISTS … $$ for the enum + FK constraints, IF NOT EXISTS for column/table/indexes) — final schema unchanged, safe on the already-migrated prod DB, and safe on a clean replay/restore.
2. migrate-production.yml's self-heal loop now also matches the first-pass collision class (already exists / 42P07 / 42710 / 42P06 / 42701), resolves the failed migration as applied, and retries — so this failure class can't go red again.
3. KI-028 documents it.

The graceful skip-when-secret-absent behaviour was already present and is unchanged. No new secret required — keep the existing PRODUCTION_DATABASE_URL repo secret set.

This issue will auto-close when PR #258 (which says Resolves #245) merges.

[RJK134]

Fixed by PR #258 (merged as f165644). The hardened Migrate production database workflow is re-running on the merge commit to validate against the live DB; closing as the root-cause fix is now on main.