Module-owned versioned migrations for phoenix_kit_emails — the decentralized-migrations protocol core's mix phoenix_kit.update discovers via 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.