PhoenixKit PostgreSQL Migration System

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

Migration Versions

V169 - Anonymous entity submissions, and one duplicate foreign key ⚡ LATEST

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 COLUMNs 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