PhoenixKit.Migrations.Postgres.V70 (phoenix_kit v1.7.71)

Copy Markdown View Source

V70: Re-backfill UUID FK columns silently skipped in V56/V63.

Background

V56 introduced UUID FK companion columns (via UUIDFKColumns) with a backfill that populated each FK column from the corresponding source table's uuid column. Two bugs in V56/V63 could cause the backfill to be skipped silently:

  1. Type mismatch — On databases where phoenix_kit_email_logs.uuid was created as character varying instead of the native PostgreSQL uuid type (because a manual migration pre-empted V40's proper ADD COLUMN), the backfill UPDATE fails with ERROR 42804 datatype_mismatch.

  2. Broken rescue — V56's backfill_uuid_fk had a rescue _ -> :ok clause intended to swallow the error. However, a failed PostgreSQL statement puts the connection's transaction in an aborted state (ERROR 25P02), so all subsequent execute/1 calls fail even though Elixir caught the exception. The first error (type mismatch) is reported; the migration rolls back; the DB stays at v55. Alternatively, if the migration somehow succeeded, the backfill was still skipped and email_log_uuid rows were filled with random UUIDs by UUIDFKColumns.add_constraints/1's NULL-fill fallback.

V63 had the same issue for matched_email_log_uuid without even a rescue.

What This Migration Does

  1. Converts phoenix_kit_email_logs.uuid to native uuid type if still character varying (root-cause fix).

  2. Re-backfills email_log_uuid in phoenix_kit_email_events:

    • Rows whose email_log_uuid does NOT reference a real email log uuid (random UUID written by the NULL-fill fallback) are reset to NULL first.
    • Then the proper JOIN-based backfill is re-run.
  3. Re-backfills matched_email_log_uuid in phoenix_kit_email_orphaned_events (same pattern as above).

All operations are idempotent — safe on every install, including fresh ones where the columns were backfilled correctly from the start.

Summary

Functions

down(opts)

up(opts)