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

PhoenixKit PostgreSQL Migration System

This module handles versioned migrations for PhoenixKit, supporting incremental
updates and rollbacks between different schema versions.

## Migration Versions

### V186 - Carts/orders: frozen base currency and exchange rate ⚡ LATEST

Adds `base_currency`/`exchange_rate` to `phoenix_kit_shop_carts` and
`phoenix_kit_orders`, and `base_unit_price` to
`phoenix_kit_shop_cart_items` — all nullable, no default. A `DO` block
raises before touching a column if `phoenix_kit_currencies` does not have
exactly one `is_default` row, since the backfill's subqueries would
otherwise pick an arbitrary one and silently mis-price. The backfill
itself derives every value from the currency table's *current* state
rather than from a literal: a row already in the base currency gets
`exchange_rate = 1.0` and its own totals copied into `base_*`; a row in
another currency gets that currency's rate (or `NULL` if the currency is
gone from the table) and `NULL` `base_*` amounts, since computing them
now would fabricate a conversion nobody performed. On a host whose base
currency is USD with all-USD carts and orders (ours), the backfill is the
identity — no price changes, no order is repriced.

### V185 - Activities can be permanent and are ordered to the microsecond; posts remember their zone

`phoenix_kit_activities.permanent` (`boolean`, default false): an entry
the pruner never deletes, for records rather than news — the settings
history is the first: every settings write that changes a value logs a
permanent `setting.changed` entry with the value before and after, and
`PhoenixKit.Settings.value_at/2` answers "what was this setting at that
instant". It exists because a stored instant does not say which regime
wrote it: when `time_zone` moved to IANA ids and several modules turned
out to have added that value to other instants, the rows they had written
could not be repaired. `inserted_at` widens from whole seconds to
microseconds so two changes inside one second keep their order (no
rewrite). And `phoenix_kit_posts.time_zone` (nullable) carries the zone a
post's schedule was typed in.

### V184 - Settings: dead `shop_currency` removed

Deletes the `shop_currency` row `V135` seeds into `phoenix_kit_settings`.
Nothing reads it — confirmed by a full grep over `phoenix_kit`,
`phoenix_kit_billing`, `phoenix_kit_ecommerce`, and a host application. The
currency a shop actually uses is the `is_default = true` row of
`phoenix_kit_currencies`; a second, unread "shop currency" setting is a trap
for the next reader. `down/1` restores the row with V135's exact seed
statement (`ON CONFLICT ("key") DO NOTHING`), so a rollback never overwrites
a value an operator re-created by hand.

### V183 - Annotations: a shape can anchor to something other than a file

`phoenix_kit_annotations.file_uuid` becomes nullable behind a generic
`target_type` + `target_uuid` pair (backfilled `'file'` / the file uuid for
every existing row; a CHECK pins the two shapes). Fresco renders a scene
with zero images and Etcher draws over empty canvas, so a whiteboard no
longer needs a background file to hold its shapes — the projects module
used to mint a white PNG per board for exactly this. Existing rows read
unchanged.

### V182 - Users: PhoenixKit's internal keys lose their custom-field definitions

Deletes the `custom_user_fields_definitions` entries whose key is one
PhoenixKit itself writes into `custom_fields` (`etcher_line_params`,
`media_expanded_folders`, `notification_preferences`, `preferred_locale`, …).
Before the internal writers passed `ensure_definitions: false`, the first
write of one auto-registered it as an admin-editable **text** field — and a
map or a list under a text field 500s the admin user edit form. The writers
were fixed; the definitions they left behind were not, so every upgraded
install still carried the broken page. Values in `custom_fields` are left
exactly as they are.

### V181 - Users: `user_timezone` widens to hold an IANA identifier

`phoenix_kit_users.user_timezone` goes from `varchar(3)` to `varchar(64)`.
Three characters fitted the integer offsets it used to hold (`"+14"`, `"0"`),
which is exactly why timezones could not follow daylight saving. Widening it
lets the column store `Europe/Helsinki`. Metadata-only in PostgreSQL: no
table rewrite, and existing offsets are left untouched and still readable —
see the migration's moduledoc for why they are not converted.

### V180 - Catalogue: federated manufacturer↔supplier links + one current supplier per pair

Adds `manufacturer_source` / `supplier_source` (`local` | `crm_company`,
CHECK-backed) to `phoenix_kit_cat_manufacturer_suppliers` and DROPS both of
its foreign keys, so the M:N graph can hold CRM parties — the last place
still forced to point at the catalogue's own directory. The dropped
constraints carried `ON DELETE CASCADE`; `Catalogue.delete_supplier/2` and
`delete_manufacturer/2` now clear links explicitly.

Also adds the partial unique index
`phoenix_kit_cat_item_supplier_info_current_pair_uniq` on
`(item_uuid, supplier_uuid) WHERE valid_to IS NULL`, so one item cannot list
the same supplier twice with two live prices. Partial on purpose: several
rows per pair are what a price revision produces. Existing duplicates are
CLOSED rather than deleted, or the index creation would fail.

### V179 - Catalogue: item → manufacturer becomes a federated reference

Adds `phoenix_kit_cat_items.manufacturer_source` (`local` | `crm_company`,
CHECK-backed) and `manufacturer_name_snapshot` (a tombstone read only when
the reference resolves to nothing), and DROPS the foreign key
`phoenix_kit_cat_items_manufacturer_uuid_fkey`. That FK made an item's
manufacturer necessarily a local catalogue row, which is incompatible with
CRM owning party identity; the item↔supplier junction has used soft uuid +
source tag + snapshot since V149/V151 and this brings manufacturers into
line. Integrity moves to the application, as it already had to for every
other cross-module reference.

### V178 - Catalogue ↔ CRM: soft party cross-references

Adds `phoenix_kit_cat_manufacturers.crm_company_uuid` (nullable, no FK —
optional-module boundary) and the partial unique indexes that keep both
catalogue directories one-to-one against a CRM party: manufacturers here,
suppliers retro-fitted onto the column V149 added without one. CRM owns
party identity; the local `cat_suppliers`/`cat_manufacturers` rows are
demoted to a projection of it and stay, because catalogue-standalone
installs have no CRM and `cat_items.manufacturer_uuid` is still a hard FK
onto the local row. Additive only — nothing the ExpectedSchema manifest
already declares is reshaped.

### V177 - Catalogue: item ↔ attribute-set attachments

Adds `phoenix_kit_cat_item_attribute_sets` — the join behind the
catalogue's attribute-sets rework: items attach any number of ordered
sets (managed entities blueprints), with a reserved per-attachment
`data` JSONB. `set_uuid` deliberately carries no FK (cross-module
reference into `phoenix_kit_entities`; integrity is owned by the
modules — entities consults the catalogue's registered delete guard,
the catalogue prunes orphans off entities PubSub events). Filed as
V176 originally, renumbered to V177 when this branch merged
upstream/main and found V176 already taken by the FK-validation pass
below; every statement is IF-NOT-EXISTS idempotent, so installs that
ran the DDL under the old number re-run it as a no-op.

### V176 - Validate existing NOT VALID foreign keys

The FK repair V164 added (`UUIDFKColumns.fk_constraints/0`) creates every
constraint `NOT VALID` and validates it on the spot, but a validation that
fails because of pre-existing orphaned rows stays `NOT VALID` forever —
nothing in core ever revisits it (V164's own moduledoc says a re-run does
not retry, "VALIDATE each by hand"). This version does exactly that, on
every later run: for each declared FK still `NOT VALID`, if the orphan
count is now zero it validates it; if orphans remain, it leaves the
constraint untouched and warns with the real count. Never deletes a row or
nulls a reference — same policy V164 states for itself, for the same
reason (this runs against other people's already-deployed data).

Filed as V175 originally, renumbered to V176 when this branch merged
upstream/main and found V175 already taken by the Buckets change below.

### V175 - Buckets: integration_uuid credential source

Adds `phoenix_kit_buckets.integration_uuid` (bare UUID, no FK, partial
index) so a cloud bucket can point at a `PhoenixKit.Integrations`
connection instead of storing `access_key_id`/`secret_access_key`
directly. `Bucket.changeset/2` rejects setting both a `secret_access_key`
and an `integration_uuid` on the same bucket record. Also widens
`secret_access_key` from `varchar(255)` to `text` — encrypted at rest as
of this same change, and the encrypted encoding overflows 255 chars past
a ~158-char plaintext secret.

### V174 - Repair misclassified media rows

Two since-fixed writer defects left media rows whose classification
contradicts the file itself: the storage writer trusted whatever
`file_type` a caller claimed (one upload path stored every .mov and .mp3
as "image"), and it re-derived `mime_type` from an extension map with no
audio entries (every mp3 stored and served as application/octet-stream).
This pass repairs both from the rows' own evidence: blank/octet-stream
mimes with a known audio extension get the real audio mime (files and
file instances), and generic `file_type` values contradicted by the mime
are reclassified. System types ("tile") and rows without evidence are
left alone.

### V173 - Catalogue attribute groups

Reusable, translatable attribute groups for the `phoenix_kit_catalogue`
module: a group ("Idea doors") owns attributes ("Color", "Trim"), each
attribute owns ordered values ("White", "Oak") with an explicit default,
and items link to a group through an assignment table (one group per
item enforced by a droppable unique index, so multi-group later is not
a data migration). Groups referenced by items can only be archived, not
deleted (RESTRICT FKs throughout; the module deletes unreferenced draft
groups via an explicit transactional cascade). Names and values carry
the module's per-language JSONB `data` translations with stable slug
keys as durable identity.

### V172 - SEO module renamed to Crawlers

The built-in SEO module becomes Crawlers: everything it held was bot
policy (the noindex directive, crawler guidance, and now per-bot-group
access controls), while actual SEO work lives in the external
phoenix_kit_seo package. Settings rows are renamed
(seo_module_enabled/seo_no_index -> crawlers_*), roles granted seo gain
crawlers, and the old seo grants stay for the repair manifest.

### V171 - Shop slugs unique per (base language, value)

Product and category slugs are jsonb maps, and the only uniqueness the
database enforced was an expression index on the alphabetically-first
key's value — under-enforcing (other languages unconstrained, collisions
surfacing on "add translation") and over-enforcing (`{"en":"hat"}` vs
`{"de":"hat"}` collided though they can never shadow each other in a
URL) at once. Trigger-maintained projection tables
(`phoenix_kit_shop_{product,category}_slugs`, PK `(lang, value)`) now
enforce the bucket the resolver actually reads. Existing duplicates are
suffixed `-2`, `-3` …; two LIVE rows sharing a bucket raise instead —
which URL survives is the operator's call. The old indexes are dropped;
`extract_primary_slug` stays, unused.

### V170 - Notification collapsing gets indexes and a uniqueness backstop

Two indexes for the `upsert_inapp/3` collapsing API. A partial UNIQUE index
on `(recipient_uuid, metadata->>'dedupe_key')` over undismissed unseen rows
serves the dedupe lookup and turns the find-then-insert race (two workers
both inserting the same key) into a constraint the code retries as a
collapse; existing duplicate unseen rows are folded — all but the newest per
key marked dismissed — before the index is created. A second index on
`(recipient_uuid, seen_at IS NOT NULL, inserted_at DESC, uuid DESC)` over
undismissed rows matches the unseen-first inbox ordering term-for-term, so
the bell and inbox reads come straight off an index again. Rows without a
dedupe key are outside both the uniqueness rule and the fold.

### V169 - Anonymous entity submissions, and one duplicate foreign key

Makes `phoenix_kit_entity_data.created_by_uuid` nullable: the public entity
form is deliberately unauthenticated and has no creator to record, so on a
freshly migrated database every anonymous submission failed with a
`not_null_violation`. Long-lived installs were already storing NULL there.
Recorded in V164's `@relaxed_after_v57` and in the V135 baseline so repair and
a fresh install agree with it.

Also drops the duplicate foreign key V135 created on
`phoenix_kit_ai_requests.prompt_uuid`, keeping the legacy
`phoenix_kit_ai_requests_prompt_uuid_fkey` — the name the installed base
carries and the one Ecto derives by default — so no live database needs a
rename.

### V168 - The remaining slug indexes

Finishes what V167 started. An audit of every schema declaring a slug
`unique_constraint/3` found two more with nothing to translate:
`phoenix_kit_tickets` (plain btree since V135, while `Ticket` declares
`unique_constraint(:slug)` and `get_ticket_by_slug/2` fetches with
`one()`) and `phoenix_kit_post_groups` (no slug index at all, while
`PostGroup` names a composite `[:user_uuid, :slug]` index that exists
nowhere). The other six were already backed correctly.

Post-group slugs are unique **per user**, so that index is on
`(user_uuid, slug)` and the dedup partitions by the pair. Existing
duplicates are suffixed `-2`, `-3` … following `Slug.ensure_unique/2`,
the oldest row keeping the bare slug.

### V167 - Unique post slugs

Makes `phoenix_kit_posts_slug_index` unique. It had been a plain btree
since V135 while its sibling `phoenix_kit_post_tags.slug` was unique, so
`Post`'s `unique_constraint(:slug)` had no index to translate and
`get_post_by_slug/2` — which fetches with `one()` — raised
`Ecto.MultipleResultsError` on any URL two posts shared.

Existing duplicates are suffixed `-2`, `-3` … following
`Slug.ensure_unique/2`, keeping the reachable post over a draft and then
the oldest. Two *live* posts on one slug raises instead: one of them has
to lose a working URL, and that is the operator's call.

### V166 - Frozen comment attribution

Adds `author_display_name`, `attribution_mode`, `attributed_project_uuid`
and `attributed_label` to `phoenix_kit_comments`. A name resolved at
render time rewrites history — someone leaves or fills in a profile and
every comment they wrote is silently re-signed — so what the reader was
shown is pinned at write. The same applies to speaking on a project's
behalf, which is a choice made at the time and not a fact recomputed from
current membership. `user_uuid` is never cleared: posting as the project
changes what the PUBLIC sees and nothing else, so moderation and audit
keep their actor.

Existing rows are left NULL rather than backfilled — inventing a display
history we do not have would be worse than resolving those rows live.

### V165 - Cross-module mentions and access requests

Adds `phoenix_kit_mentions` (the reverse index for `@`/`#` tokens — the
canonical mention lives in the text, this answers "what links here" and
gives notification fan-out something to diff) and
`phoenix_kit_access_requests` (asking the owner for access to a record a
mention pointed at but the reader cannot open). Neither target carries a
foreign key: both point into ~28 optional packages' tables.

### V164 - Repair the V56/V57 flush-order bug's fallout, and converge two
### prefix-unsafe historical shapes
- Also folds in what an earlier draft carried as a separate V164: V68
  (partial `idx_publishing_posts_group_slug`) and V65 (the
  `phoenix_kit_subscription_plans_slug_uidx` -> `..._types_slug_uidx`
  rename) each issued a BARE, unqualified DROP/ALTER guarded by `IF
  EXISTS`: effective on `public`, a silent no-op in a named schema, so
  the two install paths diverged and the `V135` baseline — generated
  into a named schema — kept the unintended shape. This version
  idempotently converges both onto the historical `public`/intended
  shape, and is a no-op on every real public install. This release
  ships ONE migration, so it lives here rather than in a second version
- V56/V57 queued `UUIDFKColumns.up/1`'s `ADD COLUMN`s immediately before
  `add_constraints/1`'s immediate `column_exists?`/NOT NULL guards with
  no `flush()` between them (V57 had none at all) — harmless on an
  incremental chain run, but on a single-shot run (fresh install) the
  guards ran before Postgres had ever seen the queued columns, so ~46
  `*_uuid` FK columns across ~33 tables were left nullable instead of
  NOT NULL, and `phoenix_kit_comments.fk_comments_user_uuid` was never
  created at all — V72 later found it missing and guessed `ON DELETE
  CASCADE` instead of V56/V57's own declared `SET NULL`
- V56/V57 now carry the missing `flush()`, and V72's guess is now `SET
  NULL`, so this only repairs installs whose single-shot run already
  happened before those fixes; it is a no-op everywhere else
- Per affected column: sets NOT NULL only if it currently has zero NULL
  rows; otherwise warns (table/column/row count) and leaves it
  nullable — never backfills live data with an invented value
- Corrects `fk_comments_user_uuid` from CASCADE to SET NULL if the
  buggy shape is present
- Repair-only: `down/1` restamps the comment, never undoes the fix

### V163 - UUID primary-key integrity (catalog-driven repair, upstream #688)
- Repairs any `phoenix_kit_*` table whose `uuid` column is the wrong
  type, nullable, or not the primary key — the state V40/V56/V74 each
  assumed impossible and a production install reached anyway
  (`phoenix_kit_email_events`: `varchar(255)`, nullable, no PK at all)
- Catalog-driven on purpose: every earlier attempt enumerated tables by
  hand and this one was missing from every list
- Above two million rows the `ALTER COLUMN ... TYPE uuid` rewrite and the
  `ADD PRIMARY KEY` are DEFERRED and logged with the command to run in a
  maintenance window, rather than taking `ACCESS EXCLUSIVE` on a large
  table mid-deploy; `mix phoenix_kit.doctor` is the loud channel
- Runs BEFORE V164 by construction, which is the order V164 needs: a
  foreign key cannot reference a column with no unique/primary key, so
  promoting `uuid` to PK here is what lets V164's FK repair validate
- NOTE: upstream's own moduledoc for this version is missing — its
  heading landed above V162's body and V162's heading was lost. The
  section below is written from `v163.ex`'s own moduledoc; carry this
  correction back in the PR

### V162 - Payment-option linkage on billing orders

Adds a nullable `payment_option_uuid` FK (+ index) to
`phoenix_kit_orders`, pointing at `phoenix_kit_payment_options`. The
order's `payment_method` is a small closed vocabulary; the payment
OPTION is the operator-configured row the customer actually chose, and
the choice used to be discarded at checkout. `ON DELETE SET NULL` so
deleting an option neither fails nor destroys order history.

### V161 - Case-insensitive `phoenix_kit_users.username` (citext)
- `username` was `VARCHAR(255)` (V08's `:string`) — comparison semantics
  come from the column type, not the Ecto schema field, so every lookup
  (`get_user_by_username/1`, `unsafe_validate_unique`, the unique index
  itself) was exact-match; `alice` and `Alice` could both register
- Converts the column to `citext`, same fix already applied to `email`
  in V01 and the CRM party email columns in V151
- Pre-check (mirrors V106's down-step) raises on any existing
  case-insensitive collision before the DDL runs, naming the offending
  value; `WHERE username IS NOT NULL` guards against nullable rows
  false-colliding under `GROUP BY`
- `varchar` → `citext` is binary-coercible (`pg_cast.castmethod = 'b'`),
  confirmed live — no table rewrite; the column's B-tree index does get
  rebuilt (also confirmed live), which is what makes it enforce
  case-insensitive uniqueness right after the `ALTER`

### V160 - Settings `value` widened to TEXT
- `phoenix_kit_settings.value` was `VARCHAR(255)` (V03's `:string`) while
  `Settings.Setting` validated it at `max: 1000` — anything in between
  passed the changeset and then raised a raw `Postgrex.Error`
- Surfaced by list-valued settings: the sitemap's default exclude
  patterns serialize to ~450 characters, so saving them always crashed
- Catalog-only change in PostgreSQL: no rewrite, no long lock

### V159 - Publishing categories + post view counters
- `phoenix_kit_publishing_categories` — hierarchical per-group taxonomy
  (nullable `parent_uuid` self-FK, V103 catalogue shape); `slug` unique
  per group; `name_i18n` JSONB per-language display names; `position`
  for manual ordering; group delete cascades, parent delete lifts
  children to the root (`ON DELETE SET NULL`)
- `phoenix_kit_publishing_post_categories` — post↔category M:N
  (post-level, WordPress semantics — not per-version); both sides
  cascade
- `phoenix_kit_publishing_post_views` — per-day view rollups keyed
  `(post_uuid, view_date)`, incremented in place; totals are
  `SUM(count)`; dedup/bot filtering are app-side, no reader PII stored

### V158 - Broadcast attachments (accumulator)
- Adds `attachments JSONB NOT NULL DEFAULT '[]'` to
  `phoenix_kit_newsletters_broadcasts` — an ordered list of Storage
  file uuids attached to every email of the broadcast; soft references
  (no FK) per this table's `crm_list_uuid` precedent, with a
  `jsonb_typeof = 'array'` CHECK as the DB-level shape backstop
- Shipped in 1.7.211 — the accumulator is closed; the next restructuring
  section opens V159

### V157 - Image annotation kind
- Widens `phoenix_kit_annotations_kind_check` to allow `'image'`
- Pairs with the schema's `@kinds` (also widened) so Etcher's `:image`
  tool — exposed in the media viewer's toolbar by PR #660 — can
  actually persist; same regression shape as V130's `"marker"`

### V156 - Legacy newsletters lists migrated into CRM, tables dropped
- **Requires a coordinated release with the newsletters module** — drops
  tables/columns an older newsletters release still reads; see V156's
  moduledoc warning
- Data: every `phoenix_kit_newsletters_lists` row copied to
  `phoenix_kit_crm_lists` (same slug — reused if a CRM list already has
  it), `subscribable = true`
- Data: a `phoenix_kit_crm_contacts` row per distinct user with a legacy
  membership (reused if one already exists by email), linked to that
  user's `user_uuid` via a straight UPDATE against `phoenix_kit_users` —
  never creates a user, `connect_user/2`'s placeholder-minting is
  structurally unreachable from this migration
- Data: legacy memberships copied to `phoenix_kit_crm_list_members`,
  status mapped (`active`→`subscribed`, `unsubscribed`→`removed`),
  `subscribed_at`/`unsubscribed_at` preserved verbatim (not `now()`);
  `subscriber_count` recounted after
- Re-points every `newsletters_list` broadcast still referencing a
  migrated list to `source_type = 'crm_list'` + `crm_list_uuid`; any
  broadcast an orphaned `list_uuid` couldn't be re-pointed from (should
  be none — `ON DELETE RESTRICT` guarantees referential integrity, see
  moduledoc) has that uuid preserved into
  `source_params->>'legacy_list_uuid'` first
- Drops `fk_newsletters_broadcasts_list` + `list_uuid` column, then
  `phoenix_kit_newsletters_list_members` and `phoenix_kit_newsletters_lists`
  themselves
- `down/1` restores the two tables (V79 shape) and the FK/column
  (nullable, matching V152) — structure only, migrated/re-pointed data
  is not moved back

### V155 - Delivery CRM contact id + per-broadcast dedup
- Adds `crm_contact_uuid` (bare, nullable UUID, no FK — same soft-ref
  pattern as `crm_list_uuid`) to `phoenix_kit_newsletters_deliveries`,
  plus a plain index on it
- Replaces `phoenix_kit_newsletters_deliveries_recipient_check` (same
  name) with a widened CHECK: still requires an addressable recipient
  (`user_uuid` or `recipient_email`), and now additionally forbids a
  row claimed by both `user_uuid` and `crm_contact_uuid` at once —
  deliberately NOT a strict XOR; see V155's moduledoc for why
- Adds three partial unique indexes — `(broadcast_uuid, user_uuid)`,
  `(broadcast_uuid, crm_contact_uuid)`, `(broadcast_uuid,
  recipient_email)`, each `WHERE ... IS NOT NULL` — the first DB-level
  per-broadcast delivery dedup; `insert_all` previously had no
  `ON CONFLICT` guard at all
- Adds `source_params JSONB NOT NULL DEFAULT '{}'` to
  `phoenix_kit_newsletters_broadcasts`, for the new `user_group`
  (core-role) recipient source — a role set, so JSONB rather than
  another scalar soft-ref uuid column. Shape:
  `%{"role_uuids" => [...], "role_names_snapshot" => [...]}` — uuids
  resolve (a role's name is mutable), the name snapshot is display-only

### V154 - OpenGraph templates + assignments (`phoenix_kit_og`)
- Adds `phoenix_kit_og_templates` (reusable OG canvas designs; JSONB
  `canvas` element list) and `phoenix_kit_og_assignments` (binds a
  template to a `module_key × scope_type × scope_uuid` scope with a JSONB
  `slot_mapping`). Uniqueness via a partial-index pair (NULL `scope_uuid`
  is the module-wide default tier); `template_uuid` cascades on delete.
  Powers the `phoenix_kit_og` plugin.

### V153 - Folder header size defaults to small
- Flips `phoenix_kit_media_folders.header_size` column default from
  'medium' (V134) to 'small', and backfills existing 'medium' rows to
  'small' ('medium' was the old default, so it reads as untouched;
  'large' is a deliberate choice and is left alone)

### V152 - Newsletters/CRM/Core restructuring (accumulator)
- Unreleased — per the "one open migration" rule, every DDL step of the
  restructuring plan lands in V152 as its own section until it ships;
  later stages append here rather than opening V153.
- Section: send profiles move to core Email. Creates
  `phoenix_kit_email_send_profiles` — same shape V145 gave
  `phoenix_kit_newsletters_send_profiles`, now owned by core's
  `PhoenixKit.Email` namespace. Copies every row across by `uuid`, then
  drops the V145 table. `idx_nl_send_profiles_*` indexes become
  `idx_email_send_profiles_*`. Does not touch
  `phoenix_kit_newsletters_broadcasts.send_profile_uuid` — still a bare
  UUID with no FK, so it points at the same row regardless of which
  table now owns it.

### V150 - Readable device name on session tokens
- Adds nullable `browser` + `os` to `phoenix_kit_users_tokens`, parsed
  from the User-Agent at login, so the Active Sessions list and admin
  all-sessions view show a device name for every session without the
  known-devices/geo machinery (which stays gated behind new-login alerts).

### V149 - Catalogue item-supplier sourcing info + CRM xref
- Adds `phoenix_kit_cat_item_supplier_info` (per-item, per-supplier SKU /
  unit cost / currency / lead time / MOQ; `supplier_uuid` soft ref to a
  CRM party or local `cat_supplier`) and a soft `crm_company_uuid` xref on
  `phoenix_kit_cat_suppliers`. No primary among these rows — the item's
  default supplier is the V146 `primary_supplier_uuid` scalar.

### V148 - CRM party roles (suppliers, clients)
- Adds `phoenix_kit_crm_party_roles` for the `phoenix_kit_crm` module:
  polymorphic role edge marking a CRM company or contact as `supplier`,
  `client`, or other commercial role. One party can hold several roles;
  `valid_from`/`valid_to` lifecycle, `is_active` filter, role-scoped
  `metadata`. No FK on `roleable_uuid`; unique on
  `(roleable_type, roleable_uuid, role)`.

### V147 - Known-device geo-location
- Adds nullable `location` (`City, Country`) to
  `phoenix_kit_user_known_devices`. Resolved once at new-device time by
  `PhoenixKit.Users.LoginAlerts` and stored so the user's Active Sessions
  list can show sign-in location without a per-render geo lookup.

### V146 - Catalogue item primary supplier
- Adds nullable `primary_supplier_uuid` FK (`ON DELETE SET NULL`) +
  partial index to `phoenix_kit_cat_items` — an item's default
  supplier, independent of manufacturer (generic/unbranded materials;
  tie-break when a manufacturer has several suppliers). Backs the
  `phoenix_kit_catalogue` feature from its commit 2e47cdf.

### V145 - Newsletters Send Settings (send profiles)
- Adds `phoenix_kit_newsletters_send_profiles`: named send configurations
  referencing a core Integrations connection (`integration_uuid`, no FK)
  plus per-account send parameters (from-name/email, reply-to, signature,
  rate limits, `advanced` per-provider extras jsonb).
- Multiple profiles may share one integration; at most one may be
  `is_default`, enforced by a partial unique index on `is_default`.
- Adds `send_profile_uuid` (bare UUID, no FK) to
  `phoenix_kit_newsletters_broadcasts` so a broadcast can pin which send
  profile delivers it.

### V144 - Manufacturing/Warehouse module tables consolidation
- Consolidates 5 objects previously created by `phoenix_kit_manufacturing`'s
  and `phoenix_kit_warehouse`'s own `migration_module/0` into core's
  migration chain: `phoenix_kit_machines`, `phoenix_kit_machine_type_assignments`,
  `phoenix_kit_machine_operations`, `phoenix_kit_warehouse_transfers`
  (+ its `number` sequence), and `phoenix_kit_warehouse_min_stock`.
- `machine_type_uuid`/`operation_uuid` on the two join tables are soft
  references (no FK) to the entities package. Upgrade path for hosts on
  the published `phoenix_kit_manufacturing` 0.2.0 (module V1): the join
  table already exists there with a *live* FK on `machine_type_uuid` —
  this migration drops it unconditionally. Warehouse tables are
  fresh-install-only DDL (`phoenix_kit_warehouse` 0.1.0 never published
  migrations for them, so no upgrade case exists).
- The pre-V5 manufacturing directory tables (`phoenix_kit_machine_types`,
  `phoenix_kit_operations`, `phoenix_kit_defect_reasons`) are not
  re-created; each is dropped only if present and empty, left in place
  with a database `NOTICE` when non-empty — see the PR body for the
  manual data-migration note on such hosts.
- Rollback mirrors the five creates; see `V144.down/1`'s moduledoc for
  the upgrade-host caveat (can't distinguish a pre-existing
  `machine_type_assignments` table from one V144 created).

### V143 - Known-device history for new-login alerts
- Adds `phoenix_kit_user_known_devices` (IP + hashed user-agent per user,
  unique per `(user_uuid, ip_address, user_agent_hash)`) so a login from
  an unrecognized device can be told apart from a familiar one.
- Backs the `new_login_alert_enabled` setting and
  `user.new_login_detected` activity action.

### V142 - Wider role-permission keys
- Widens `phoenix_kit_role_permissions.module_key` from `VARCHAR(50)` to
  `VARCHAR(120)` so fine-grained sub-permissions can be stored as composed
  dotted keys (`"calendar.view_others"` — base and sub parts are each
  capped at 50 chars, so a composed key can reach 101).
- Rollback deletes rows over 50 chars (sub-permission grants are additive
  and re-grantable) before narrowing the column back.

### V141 - Calendar events + participants
- Adds `phoenix_kit_calendar_events` for the `phoenix_kit_calendar` module:
  one implicit personal calendar per user (`owner_uuid` FK, CASCADE on user
  delete). Timed events use an exclusive-end UTC pair; all-day events use an
  exclusive-end DATE pair; a CHECK enforces exactly one pair per row matching
  the `all_day` flag, with end > start. Status is active/cancelled.
  `location_uuid` loosely links a stored location (name snapshotted into the
  `location` string — no cross-module FK).
- Adds `phoenix_kit_calendar_event_participants`: loose `kind` + `target_uuid`
  references (user / staff_person / crm_contact / crm_company / free_text)
  with a `display_name` snapshot and `added_by_uuid` audit. Visibility is
  resolved LIVE at query time against the physical staff/CRM tables, so a
  company participant means "current members" and no module code is needed.
  Partial uniques dedup targets per event and free-text case-insensitively.
- Extended in place while unreleased (idempotent-additive statements).
- Rollback drops both tables.

### V140 - Warehouse module tables
- Creates `phoenix_kit_warehouse_stock`, `phoenix_kit_warehouse_inventory_documents`,
  `phoenix_kit_warehouse_internal_orders`, `phoenix_kit_warehouse_supplier_orders`,
  `phoenix_kit_warehouse_goods_receipts`, and `phoenix_kit_warehouse_goods_issues` —
  the storage layer for the standalone `phoenix_kit_warehouse` package.
- `internal_orders` and `goods_issues` have no FK to any order table — the
  relationship lives in a generic `source_refs` JSONB column instead, resolved
  by a host-registered callback so the package has zero dependency on any
  particular "order" concept. GIN-indexed for reverse lookups.
- Intra-module FKs preserved: `supplier_orders.internal_order_uuid` →
  `internal_orders`; `goods_receipts.supplier_order_uuid` → `supplier_orders`;
  `goods_issues.internal_order_uuid` → `internal_orders`.
- `item_uuid`, `location_uuid`, `storage_folder_uuid`, `supplier_uuid` are
  plain UUID columns — no FK, so the database does not enforce referential
  integrity for them (delete semantics still undecided).
- No data is copied from any existing table — these tables are empty until a
  consuming app populates them.

### V139 - Dashboard `config` column
- Adds a JSONB `config` column (`NOT NULL DEFAULT '{}'`) to
  `phoenix_kit_dashboards` for per-dashboard presentation settings, read and
  written whole like `layout`. Backs the dashboards plugin module.
- Idempotent (`ADD COLUMN IF NOT EXISTS`); rollback drops the column.

### V138 - CRM v1 interaction tracker
- Adds five `phoenix_kit_crm_*` tables for the CRM module's first data model:
  `contacts` (profile + **optional** `user_uuid` login link, partial-unique so
  it's 1:1 only among linked rows), `companies`, `company_memberships` (M:N
  contact↔company with free-form `role_in_company` + `department` + `is_primary`
  on the edge), `interactions` (logged interaction: type/when/body/subject
  contact/owner user), and `interaction_parties` (flat resolvable "who was
  involved": `raw_name` always kept, `contact_uuid`/`staff_person_uuid` resolve
  when matched under an exclusive-arc CHECK, `party_snapshot` JSONB freezes the
  party's profile as-of-then). `staff_person_uuid` is a soft ref (no FK) so the
  optional staff module stays optional.

### V136 - Staff employment history
- Adds `phoenix_kit_staff_employments` — a per-person history of employment
  spans (employment type, translatable `job_title`, org placement via
  `primary_department_uuid` + a `primary_team_uuid` snapshot, date range with
  `employment_end_date IS NULL` = the open/current span, `work_location`,
  `notes`). A partial unique index enforces one open span per person. The
  matching `phoenix_kit_staff_people` columns are kept as a denormalized mirror
  of the current span (written by the app's `sync_current/1`), not dropped.
  Backfills one open span per existing person from those columns (guarded,
  retry-safe; people with no employment data are skipped).

### V135 - Structured staff skills
- Replaces the free-text `phoenix_kit_staff_people.skills` column with a
  first-class translatable `phoenix_kit_staff_skills` entity + a
  `phoenix_kit_staff_person_skills` join. Each skill carries its own
  per-skill, translatable proficiency levels (`levels` JSONB array of
  `{id, name, translations}`) and an `allow_multiple_levels` boolean; the
  join's `proficiency_levels` JSONB array holds the selected level ids.
  Migrates the comma-separated free-text into structured rows (case-insensitive
  dedup, guarded for retry-safety) and drops the column. Lossy by design:
  per-locale `translations["skills"]` overrides don't map to structured skills
  and are stripped. Also adds a partial index on
  `phoenix_kit_staff_people(date_of_birth)` (active + non-null DOB only) for
  `Staff.upcoming_birthdays/1`.

### V01 - V134 - Baseline (consolidated into V135 by the squash)

Every version from V01 (initial auth/roles/settings foundation) through
V134 (media-folder header customization) has been collapsed into the
`V135` baseline module — this release's `@initial_version` (the squash
floor; spec `dev_docs/plans/2026-07-14-squash-migrations-spec.md`).
`V135.up/1` applies the FINAL post-V134 shape of every table, index,
constraint, function, extension, and seed directly — no intermediate
drops/renames/backfills are replayed. See
`dev_docs/plans/2026-07-14-squash-inventory.md` for the full
per-version history this consolidates (seeds, drops/renames, hazards)
and `PhoenixKit.Migrations.ExpectedSchema`
(`lib/phoenix_kit/migrations/expected_schema.ex`, tool-generated,
`@moduledoc false`) for the machine-readable manifest the baseline was
generated from.

Installs below V135 cannot upgrade directly to this release --
`up/1`/`down/1` raise `PhoenixKit.Migrations.BelowFloorError` — they
must first apply the frozen pre-squash 1.7.x bridge release up to at
least V135, then move to this release (spec §7.2's two-stage rollout /
§5.2's registry guards).

## Migration Paths

### Fresh Installation (0 -> Current)
Applies the `V135` baseline (the consolidated V01..V134 shape), then
every delta V136..V164 in sequence (`plan_up/3`'s fresh-install clamp,
spec §5.2 D13).

### Incremental Updates
- Below V135: rejected with `PhoenixKit.Migrations.BelowFloorError` --
  apply the 1.7.x bridge release first (spec §7.2).
- At V135 (the floor) or above: runs each delta module from
  `current + 1` through the target version in sequence.

### Rollback Support
- Down to any version above V135: runs each delta module's `down/1` in
  reverse, from `current` down to `target + 1`.
- Down to V135 or below: the delta range stops at V136 (never
  dispatches a deleted below-floor module); `V135.down/1` is then
  applied directly for a full `version: 0` teardown (Oban included). A
  target strictly between 0 and V135 clamps to V135 instead of
  guessing an unreproducible intermediate shape (spec §5.2's
  `{:clamped, ...}`).

## Usage Examples

    # Update to the latest version
    PhoenixKit.Migrations.Postgres.up(prefix: "myapp")

    # Update to a specific version
    PhoenixKit.Migrations.Postgres.up(prefix: "myapp", version: 150)

    # Rollback to a specific version
    PhoenixKit.Migrations.Postgres.down(prefix: "myapp", version: 149)

    # Complete rollback (tears down the V135 baseline too)
    PhoenixKit.Migrations.Postgres.down(prefix: "myapp", version: 0)

## PostgreSQL Features
- Schema prefix support for multi-tenant applications
- Optimized indexes for performance
- Foreign key constraints with proper cascading
- Extension support (citext)
- Version tracking with table comments

# `down_plan`

```elixir
@type down_plan() ::
  {:raise, db_version :: pos_integer(), floor :: pos_integer()}
  | {:teardown, Range.t(), floor :: pos_integer()}
  | {:clamped, Range.t(), floor :: pos_integer()}
  | {:run, Range.t()}
  | :noop
```

Routing decision `down/1` acts on — pure, see `plan_down/3`.

# `up_plan`

```elixir
@type up_plan() ::
  {:raise, db_version :: pos_integer(), floor :: pos_integer()}
  | {:run, Range.t()}
  | {:run_delta, Range.t()}
  | :noop
```

Routing decision `up/1` acts on — pure, see `plan_up/3`.

# `bridge_version`

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

The release an below-floor host must install before this one.

Exposed because `mix phoenix_kit.update` refuses below-floor installs at
GENERATION time, before this module's raise sites are ever reached — so the
notice the operator sees first has to name the same version those raises do.

# `heal_version_comment`

Heal version comment if schema artifacts exist for a higher version.

V83 had a bug where the COMMENT ON TABLE statement used an incorrect prefix,
leaving the comment at the previous version even though the migration ran
successfully. This function detects and corrects the mismatch.

Returns `{:healed, new_version}` if the comment was fixed, or `:ok` if
no healing was needed.

# `migrated_version_runtime`

Get current migrated version from database in runtime context (outside migrations).

This function can be called from Mix tasks and other non-migration contexts.

---

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