# `PhoenixKit.Modules.Emails.Migrations`
[🔗](https://github.com/BeamLabEU/phoenix_kit_emails/blob/0.4.2/lib/phoenix_kit/modules/emails/migrations.ex#L1)

Module-owned versioned migrations for `phoenix_kit_emails` — the
decentralized-migrations protocol core's `mix phoenix_kit.update`
discovers via `c:PhoenixKit.Module.migration_module/0`:
`current_version/0` + `migrated_version_runtime/1` + idempotent `up/1` +
version-aware `down/1`. `PhoenixKit.Modules.Legal.Migrations` is the
reference implementation this chain is shaped after — same situation, and
V1 here is the same kind of step: an ADOPTION, not a create.

## Ownership history — read before touching

Every table this chain names was created by core, back when email
tracking still lived inside core, and today ships in core's squashed
V135 baseline (plus later deltas — see "Where the DDL comes from"). When
the module was extracted into this package the tables stayed in core's
chain, exactly as `phoenix_kit_consent_logs` did for
`phoenix_kit_legal`. So on every existing install the tables predate this
chain and `CREATE TABLE IF NOT EXISTS` finds them already there.

This chain moves ownership of their FUTURE shape here. It is deliberately
a TRANSITIONAL duplication: right now BOTH core's baseline and this
chain can create these tables, and the statements are written to be
shape-identical so that it does not matter which one wins. The next step
is a core release that stops creating them, after which this chain is
their only creator. See
`dev_docs/reports/2026-08-12-emails-table-adoption.md` for the full plan,
including the excluded-object protocol core's `ExpectedSchema` manifest
needs before that release.

## What V1 is

V1 is an ADOPTION step plus exactly one shape change:

  * **Adoption.** `CREATE TABLE IF NOT EXISTS` + `ADD COLUMN IF NOT
    EXISTS` + `CREATE INDEX IF NOT EXISTS` + guarded `ADD CONSTRAINT` for
    the six tables this package owns. On an existing install every one of
    them is a no-op and the only new object is the `pke_schema:1` marker.
    On a hypothetical future install whose core baseline no longer
    creates them, the same statements create them — with core's exact
    object names, column types, widths and defaults.
  * **One genuine change:** a nullable `integration_uuid` (uuid) column
    plus its index on `phoenix_kit_email_logs`. Before it, a log recorded
    only the provider *kind* (`"aws_ses"`, `"brevo_api"`), so with
    several accounts of the same kind configured there was no way to tell
    WHICH account sent a message — which is what per-account event
    tracking needs (see `PhoenixKit.Modules.Emails.AwsIntegrations`).
    Nullable because every pre-existing row genuinely has no known
    account, and because the stamp is best-effort on the send path (see
    `PhoenixKit.Modules.Emails.Interceptor`): an unstamped row must
    remain a valid row, not a constraint violation.

Because the adoption half changes no shape, core's `ExpectedSchema`
manifest stays accurate for it and NO core release is required. The
`integration_uuid` column is the one declared deviation: core's audit
reports an unknown column as an `:info`-level "extra column, not in the
manifest" finding, never a failure — checked, and accepted.

## What V2 is

Two nullable columns on `phoenix_kit_email_logs`, `archived_at`
(timestamptz) and `s3_key` (text), plus a partial index on the pair.
They are what turns S3 archival from a fire-and-forget upload into a
resumable one: before them the only record that a row had been shipped to
cold storage was the row's own deletion, so an archival run could not be
re-run without duplicating objects, and a run configured NOT to delete
left no trace at all.

Nullable and without a default on purpose — "never archived" is the
absence of a timestamp, and every pre-existing row genuinely was never
archived. The index is `WHERE archived_at IS NOT NULL`, because the
question the job asks is "which of these did I already ship", and on a
healthy install the answer set is the small one.

Same deviation status as `integration_uuid`: unknown to core's manifest,
reported at `:info`, never a failure.

## Which tables, and which one is NOT here

Adopted: `phoenix_kit_email_logs`, `phoenix_kit_email_events`,
`phoenix_kit_email_blocklist`, `phoenix_kit_email_templates`,
`phoenix_kit_email_metrics`, `phoenix_kit_email_orphaned_events`.

"Nothing outside this package READS them" is true; "nothing outside this
package DEPENDS on them" is not, and the difference matters for the release
that follows. Core's V135 creates
`fk_newsletters_broadcasts_template` — `phoenix_kit_newsletters_broadcasts.template_uuid`
referencing `phoenix_kit_email_templates(uuid)` — so a core release that
stops creating `phoenix_kit_email_templates` breaks a FRESH install outright
(core's chain runs before every module chain, so the FK would point at a
table that does not exist yet), and breaks it permanently on an install that
does not have this package at all. That FK has to move or go in the same
release; see the "Transitional state" section of
`dev_docs/reports/2026-08-12-emails-table-adoption.md`.

**`phoenix_kit_email_send_profiles` is deliberately NOT adopted.** The
send-profile system lives in CORE (`PhoenixKit.Email.SendProfile` /
`PhoenixKit.Email.SendProfiles`) and drives `PhoenixKit.Mailer`'s send
path with or without this package installed. This package only READS it
(through `AwsIntegrations` / `BrevoIntegrations`). Adopting a table core
itself depends on would be the mirror of the mistake the legal package's
own report documents — claiming ownership of something that outlives you.

Two of the adopted tables — `phoenix_kit_email_metrics` and
`phoenix_kit_email_orphaned_events` — currently have no reader in this
package (no Ecto schema, no query); they are pre-extraction leftovers.
They are adopted anyway: nothing else in the ecosystem touches them, and
leaving them out would strand them with no owner at all the moment core
stops creating them.

## Where the DDL comes from

Mechanically extracted from core's own `PhoenixKit.Migrations.ExpectedSchema`
manifest — the same object list `mix phoenix_kit.doctor` and
`mix phoenix_kit.repair` verify a live database against — so every
statement is byte-identical to what core would emit for the same object,
at the manifest's CURRENT shape (not just the V135 snapshot). It was not
retyped from the baseline by hand, because that is precisely how the
legal package accumulated three disagreeing DDL copies of one table.
`test/phoenix_kit/modules/emails/migrations_test.exs` pins the
correspondence: it compares every statement here against the live
manifest and fails on drift, while skipping objects the manifest no
longer declares — which is what makes it survive, rather than block, the
core release that removes them.

### Where this chain deliberately differs from the manifest

Four departures, each narrow and each pinned by name in
`migrations_test.exs`, so the list cannot grow quietly:

  * **`CREATE TABLE` carries core's full column list** rather than the
    manifest's bare `CREATE TABLE ... ()`. The manifest's form is repair DDL
    and cannot express `NOT NULL` on a column without a default — see
    `table_statements/0`.
  * **Primary keys are probed by `contype`, not by name**, and
  * **the foreign key is added `NOT VALID`** — see `constraint_statements/0`.
    Both exist because this chain replays constraints on long-lived
    databases that core's baseline never re-runs on.
  * **`gin_trgm_ops` is unqualified.** The manifest hard-codes
    `public.gin_trgm_ops`, which is a `pg_dump`-shaped rendering of what
    core's V137 actually writes unqualified. An operator class lives in the
    schema its EXTENSION was installed into, and `pg_trgm` is not required
    to be in `public`: hard-coding the schema turns "trigram search works"
    into "the migration fails" on any install that put the extension
    elsewhere. Unqualified resolves through `search_path`, which is what
    core's own DDL relies on.

Two dependencies stay core's: the `uuid_generate_v7()` function used in the
`uuid` defaults, and the `pg_trgm` extension behind the `gin_trgm_ops`
indexes. The operator class is referenced UNQUALIFIED (see the departures
above): it lives in whatever schema `pg_trgm` was installed into, which is
not required to be `public` and is not the install's own prefix either, so
`search_path` — the way core's V137 writes it — is the only spelling that
works on every install.

Both are core infrastructure shared by every module, and core's chain
runs before any module chain (`mix phoenix_kit.update`), so they are
always in place first.

## Locking

The `CREATE INDEX` statements are plain, not `CONCURRENTLY`: this chain runs
inside core's generated migration, and `CONCURRENTLY` cannot run in a
transaction (`@disable_ddl_transaction` belongs to the migration module core
generates, not to this one — it is not ours to set).

On the installs this matters for, the DDL itself costs nothing: every
statement is `IF NOT EXISTS` and every object already exists, so each one is
a catalog lookup. The LOCKS are not free, though — Postgres acquires the
lock before it evaluates `IF NOT EXISTS`, so 96 `ADD COLUMN IF NOT EXISTS`
against `phoenix_kit_email_logs` each want `ACCESS EXCLUSIVE` on the busiest
table in the module. `up_statements/1` therefore opens with
`SET LOCAL lock_timeout` (see `lock_timeout_statement/0`): behind a
long-running reader the migration fails in five seconds with a clear error
instead of hanging the deploy and queueing every query on that table behind
it. Retry during a quiet window.

Be precise about what that costs, because the earlier version of this
paragraph was not: `ADD COLUMN IF NOT EXISTS` takes `ACCESS EXCLUSIVE`, not
`SHARE`, and it takes it even when the column already exists and the
statement does nothing. `ACCESS EXCLUSIVE` blocks READS as well as writes,
and every lock in a transaction is held until COMMIT — so for the duration
of V01, `phoenix_kit_email_logs` is unavailable to the application, not
merely unwritable. On a healthy install that is milliseconds of catalog
lookups; the number that matters is how long the whole chain takes, not any
one statement.

The one statement that does real WORK on an existing install is the new
`integration_uuid` index. Expect seconds on a table of a few million rows —
and, because it runs inside the same transaction, that is seconds with the
table fully locked. A host that cannot afford it should create the index
`CONCURRENTLY` by hand first (outside any transaction), after which this
statement finds it and does nothing:

    CREATE INDEX CONCURRENTLY IF NOT EXISTS phoenix_kit_email_logs_integration_uuid_idx
      ON public.phoenix_kit_email_logs USING btree (integration_uuid);

Run `mix phoenix_kit.doctor` before upgrading. It reports exactly the two
conditions that make this chain's constraint statements interesting — a
drifted `phoenix_kit_email_events` shape and orphaned `email_log_uuid`
rows — and it is cheaper to know beforehand than to find out from a failed
deploy.

## One known, harmless cosmetic difference

Two partial indexes on `phoenix_kit_email_events` were originally written
by core's V137 as `WHERE event_type NOT IN ('open', 'click')`, while the
manifest carries the `pg_get_indexdef` round-trip of that predicate. Both
spellings mean the same thing and produce the same index under the same
name, but Postgres renders the two parse trees differently, so
`pg_get_indexdef` output differs by parenthesisation between a
core-CREATED and a manifest-created database. This is core's own
behaviour — its repair path emits the manifest form too — and nothing
checks index definitions textually (the manifest's own `check` is a
catalog existence probe by name). Verified end to end against a real
database: dropping all six tables and replaying `up_statements/1` yields
IDENTICAL columns and constraints, and identical indexes apart from
these two renderings.

## What `down/1` is NOT

`down/1` unstamps the version marker; it NEVER drops a table and never
drops a column. These tables carry the delivery history and, on every
install that exists today, were created by core — rolling the MODULE
back must not destroy either. Re-running `up/1` after a rollback would
return the tables, but not the rows. The ownership test pins this by
asserting that no statement this module can emit matches `DROP`.

The migrated version is tracked as a `pke_schema:<N>` `COMMENT` on
`phoenix_kit_email_logs` (the marker convention from the legal/projects
chains, namespaced for this package). A marker-less table reads as
version 0 — the core-baseline shape from before this chain existed.

# `adopted_tables`

```elixir
@spec adopted_tables() :: [String.t()]
```

Every table whose future shape this chain owns.

`phoenix_kit_email_send_profiles` is deliberately absent — see the
moduledoc.

# `current_version`

```elixir
@spec current_version() :: pos_integer()
```

The chain version this code needs.

# `down`

Rolls back to `target` (`:version` in `opts`). Never drops a table or a
column — see the moduledoc.

# `down_statements`

```elixir
@spec down_statements(String.t(), non_neg_integer()) :: [String.t()]
```

The SQL `down/1` executes, as data (marker bookkeeping only).

# `migrated_version_runtime`

```elixir
@spec migrated_version_runtime(keyword() | map()) :: non_neg_integer()
```

The chain version currently applied in the database, read OUTSIDE a
migration (the protocol shape core's update task calls — `opts` with
`:prefix`): the `pke_schema:<N>` marker when present; a marker-less or
foreign-comment table reads as `0`.

# `up`

Applies every chain version up to `:version` in `opts` (default:
`current_version/0`). Idempotent.

The `:version` opt is what core's generated migration passes through.
`@owned_columns` and `@owned_indexes` are tagged with the version that
introduced them, so a host pinning `version: 1` gets V1's objects and a
`pke_schema:1` marker — not V2's columns under a V1 stamp.

# `up_statements`

```elixir
@spec up_statements(String.t(), pos_integer()) :: [String.t()]
```

The SQL `up/1` executes, as data — the testable single source. Every
statement is idempotent (`IF NOT EXISTS`, or a catalog-guarded DO block
for constraints, which have no `IF NOT EXISTS` form), so the chain can be
re-run against a database at any version without a pre-flight check.

Ordering is load-bearing: tables before their columns, the primary keys
before the foreign key that references one of them, indexes last.

# `version_table`

```elixir
@spec version_table() :: String.t()
```

The table carrying the `pke_schema:<N>` marker (auditor contract).

---

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