# `PhoenixKit.Migrations.Postgres.V137`
[🔗](https://github.com/BeamLabEU/phoenix_kit/blob/v1.7.165/lib/phoenix_kit/migrations/postgres/v137.ex#L1)

V137: Email event deduplication indexes + `aws_message_id` backfill, plus
optional performance indexes for the emails module (`phoenix_kit_emails`).

## Event dedup

The `PhoenixKit.Modules.Emails.Event` schema declares unique constraints that
the database never enforced (a schema↔DB mismatch). This adds the two partial
unique indexes that back them, deduping at the DB level — at-least-once SQS
delivery and racing pollers could previously insert duplicate events:

  - **single-occurrence** types (delivery / bounce / complaint / send / reject /
    delivery_delay / subscription / rendering_failure / queued): one row per
    `(email_log_uuid, event_type)`.
  - **multi-occurrence** types (open / click): one row per
    `(email_log_uuid, event_type, occurred_at)`, so a recipient's repeated
    opens and distinct link clicks are kept while an exact SQS redelivery
    (identical timestamp) is collapsed.

Pre-existing duplicates are removed first so the unique indexes can be created.
`phoenix_kit_email_events` has no bigint `id` (dropped in V74); `uuid` is the
UUIDv7 primary key and is time-ordered, so `MIN(uuid)` per group is the earliest
row — it is kept, the rest deleted.

`email_log_uuid` is the canonical FK (NOT NULL, backfilled and FK-constrained in
V56/V70; the legacy bigint `email_log_id` was dropped in V74).

## aws_message_id backfill

The hot SQS lookup now relies solely on the dedicated indexed `aws_message_id`
column (the legacy headers-JSONB scan was removed in the app code). Backfill it
for any legacy rows whose AWS MessageId lives only in the `headers` JSONB.
`DISTINCT ON (aws_id)` + a `NOT EXISTS` guard keep it safe against the existing
partial unique index on `aws_message_id`.

## Optional performance indexes

pg_trgm substring-search indexes for the admin email list (`pg_trgm` is enabled
from V111), per-template open/click analytics composites, and a partial index
for the archiver's body-compression scan.

# `down`

Drops the V137 indexes. The `aws_message_id` backfill is data-only and harmless
to keep, so it is not reversed.

# `up`

---

*Consult [api-reference.md](api-reference.md) for complete listing*