# `PhoenixKit.Migrations.Postgres`
[🔗](https://github.com/BeamLabEU/phoenix_kit/blob/v1.7.165/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

### V01 - Initial Setup (Foundation)
- Creates basic authentication system
- Phoenix_kit_users table with email/password authentication
- Phoenix_kit_user_tokens for email confirmation and password reset
- CITEXT extension for case-insensitive email storage
- Version tracking table (phoenix_kit)

### V02 - Role System Foundation
- Phoenix_kit_user_roles table for role definitions
- Phoenix_kit_user_role_assignments for user-role relationships
- System roles (Owner, Admin, User) with protection
- Automatic Owner assignment for first user

### V03 - Settings System
- Phoenix_kit_settings table for system configuration
- Key/value storage with timestamps
- Default settings for time zones, date formats

### V04 - Role System Enhancements
- Enhanced role assignments with audit trail
- Assigned_by and assigned_at tracking
- Active/inactive role states

### V05 - Settings Enhancements
- Extended settings with better validation
- Additional configuration options

### V06 - Additional System Tables
- Extended system configuration
- Performance optimizations

### V07 - Email System
- Phoenix_kit_email_logs for comprehensive email logging
- Phoenix_kit_email_events for delivery event tracking (open, click, bounce)
- Advanced email analytics and monitoring
- Provider integration and webhook support

### V08 - Username Support
- Username field for phoenix_kit_users
- Unique username constraints
- Email-based username generation for existing users

### V09 - Email Blocklist System
- Phoenix_kit_email_blocklist for blocked email addresses
- Temporary and permanent blocks with expiration
- Reason tracking and audit trail
- Efficient indexes for rate limiting and spam prevention

### V10 - User Registration Analytics
- Registration analytics columns for IP and location tracking
- Geolocation data storage (country, region, city)
- Privacy-focused design with configurable tracking
- Efficient indexes for analytics queries

### V11 - Per-User Timezone Settings
- Individual timezone preferences for each user
- Personal timezone column in phoenix_kit_users table
- Fallback system: user timezone → system timezone → UTC
- Enhanced date formatting with per-user timezone support

### V12 - JSON Settings Support
- JSONB column (value_json) in phoenix_kit_settings table
- Support for complex structured data storage
- Removes NOT NULL constraint from value column
- Enables proper JSON-only settings storage
- Backward compatible with existing string settings
- Dual storage model: string OR JSON values
- Enhanced cache system for JSON data

### V13 - Enhanced Email Tracking with AWS SES Integration
- AWS message ID correlation (aws_message_id column)
- Specific timestamp tracking (bounced_at, complained_at, opened_at, clicked_at)
- Extended event types (reject, delivery_delay, subscription, rendering_failure)
- Enhanced status management (rejected, delayed, hard_bounced, soft_bounced, complaint)
- Unique constraint on aws_message_id for duplicate prevention
- Additional event fields (reject_reason, delay_type, subscription_type, failure_reason)

### V14 - Email Body Compression Support
- Adds body_compressed boolean field to phoenix_kit_email_logs
- Enables efficient archival and storage management
- Backward compatible with existing data

### V15 - Email Templates System
- Phoenix_kit_email_templates table for template storage and management
- Template variables with {{variable}} syntax support
- Template categories (system, marketing, transactional)
- Template versioning and usage tracking
- Integration with existing email logging system

### V16 - OAuth Providers System & Magic Link Registration
- Phoenix_kit_user_oauth_providers for OAuth integration
- Support for Google, Apple, GitHub authentication
- Account linking by email address
- OAuth token storage with encryption support
- Multiple providers per user support
- Magic link registration tokens with nullable user_id

### V17 - Entities System (Dynamic Content Types)
- Phoenix_kit_entities for dynamic content type definitions
- Phoenix_kit_entity_data for entity records
- JSONB storage for flexible field schemas
- Plural display names for better UI wording
- 13 field types support (text, number, date, select, etc.)
- Admin interfaces for entity and data management
- Settings integration (entities_enabled, entities_max_per_user, etc.)

### V18 - User Custom Fields
- JSONB custom_fields column in phoenix_kit_users table
- Flexible key-value storage for user metadata
- API functions for custom field management
- Support for arbitrary user data without schema changes

### V19 - Storage System Tables (Part 1)
- Initial storage system infrastructure
- See V20 for complete distributed storage system

### V20 - Distributed File Storage System
- Phoenix_kit_buckets for storage provider configurations (local, S3, B2, R2)
- Phoenix_kit_files for original file uploads with metadata
- Phoenix_kit_file_instances for file variants (thumbnails, resizes, video qualities)
- Phoenix_kit_file_locations for physical storage locations (multi-location redundancy)
- Phoenix_kit_storage_dimensions for admin-configurable dimension presets
- UUIDv7 primary keys for time-sortable identifiers
- Smart bucket selection with priority system
- Token-based URL security to prevent enumeration attacks
- Automatic variant generation system

### V21 - Message ID Search Performance Optimization
- Composite index on (message_id, aws_message_id) for faster lookups
- Improved performance of AWS SES event correlation
- Optimized message ID search queries throughout email system

### V22 - Email System Improvements & Audit Logging
- AWS message ID tracking with aws_message_id field in phoenix_kit_email_logs
- Enhanced event management with composite indexes for faster duplicate checking
- Phoenix_kit_email_orphaned_events table for tracking unmatched SQS events
- Phoenix_kit_email_metrics table for system metrics tracking
- Phoenix_kit_audit_logs table for comprehensive administrative action tracking
- Complete audit trail for admin password resets (WHO, WHAT, WHEN, WHERE)
- Metadata storage for additional context in audit logs
- Performance indexes for efficient querying by user, action, and date

### V23 - Session Fingerprinting
- Session fingerprinting columns (ip_address, user_agent_hash) in phoenix_kit_users_tokens
- Prevents session hijacking by detecting suspicious session usage patterns
- IP address tracking: Detects when session is used from different IP
- User agent hashing: Detects when session is used from different browser/device
- Backward compatible: Existing sessions without fingerprints remain valid
- Configurable strictness: Can log warnings or force re-authentication
- Performance indexes for efficient fingerprint verification

### V24 - File Checksum Unique Index
- Unique index on phoenix_kit_files.checksum for O(1) duplicate detection
- Enables automatic deduplication of uploaded files
- Prevents redundant storage of identical files
- Improves performance of duplicate file lookups

### V25 - Aspect Ratio Control for Dimensions
- Adds maintain_aspect_ratio boolean column to phoenix_kit_storage_dimensions
- Allows choosing between aspect ratio preservation (width-only) or fixed dimensions
- Per-dimension control for responsive sizing vs exact crops
- Defaults to maintaining aspect ratio for all dimensions

### V26 - Rename Checksum Fields & Per-User Deduplication
- Renames `checksum` to `file_checksum` (clearer naming)
- Removes unique index on file_checksum (allows same file from different users)
- Adds `user_file_checksum` column (SHA256 of user_id + file_checksum)
- Creates unique index on user_file_checksum for per-user duplicate detection
- Same user cannot upload same file twice (enforced by user_file_checksum)
- Different users CAN upload same file (different user_file_checksum values)
- Preserves file_checksum field for popularity analytics across all users
- Clearer naming convention: file_checksum vs user_file_checksum

### V27 - Oban Background Job System
- Creates Oban tables for background job processing
- Oban_jobs table for job queue management
- Oban_peers table for distributed coordination
- Performance indexes for efficient job processing
- Enables file processing (variant generation, metadata extraction)
- Enables email processing (sending, tracking, analytics)
- Uses Oban's latest schema version automatically (forward-compatible)
- Integrated with PhoenixKit configuration system

### V28 - User Preferred Locale
- Adds `preferred_locale` column to `phoenix_kit_users` table
- Supports user-specific language dialect preferences
- Enables simplified URL structure with dialect preferences

### V29 - Posts System
- Complete social posts system with media attachments
- Posts with privacy controls (draft/public/unlisted/scheduled)
- Post comments with nested threading
- Post likes and user mentions
- Post tags and user groups

### V30 - Move Preferred Locale to Custom Fields
- Migrates preferred_locale from column to custom_fields JSONB
- Reduces schema complexity
- Backward compatible data access

### V31 - Billing System (Phase 1)
- Phoenix_kit_currencies for multi-currency support
- Phoenix_kit_billing_profiles for user billing information (EU Standard)
- Phoenix_kit_orders for order management with line items
- Phoenix_kit_invoices for invoice generation with receipt functionality
- Phoenix_kit_transactions for payment tracking
- Bank transfer payment workflow (manual payment marking)
- Default currencies seeding (EUR, USD, GBP)
- Billing settings for prefixes and configuration

### V32 - AI System
- Phoenix_kit_ai_accounts for AI provider account management
- Phoenix_kit_ai_requests for usage tracking and statistics
- OpenRouter integration with API key validation
- Text processing slots configuration (3 presets/fallback chain)
- JSONB storage for flexible settings and metadata
- AI system enable/disable toggle
- Usage statistics and request history

### V33 - Payment Providers and Subscriptions
- Phoenix_kit_payment_methods for saved payment methods (cards, wallets)
- Phoenix_kit_subscription_types for subscription pricing types
- Phoenix_kit_subscriptions for user subscription management
- Phoenix_kit_payment_provider_configs for provider credentials
- Phoenix_kit_webhook_events for idempotent webhook processing
- Orders: checkout session fields
- Invoices: subscription reference
- Settings: provider enable/disable, grace period, dunning configuration

### V34 - AI Endpoints System
- Phoenix_kit_ai_endpoints for unified AI configuration
- Combines provider credentials, model selection, and generation parameters
- Replaces the Accounts + Slots architecture with single Endpoint entities
- Updates phoenix_kit_ai_requests with endpoint_id reference
- Removes slot settings from Settings table

### V35 - Support Tickets System
- Phoenix_kit_tickets for customer support request management
- Phoenix_kit_ticket_comments for threaded comments with internal notes
- Phoenix_kit_ticket_attachments for file attachments on tickets/comments
- Phoenix_kit_ticket_status_history for complete audit trail
- Status workflow: open → in_progress → resolved → closed
- SupportAgent role for ticket access control
- Internal notes feature (staff-only visibility)
- Settings: enabled, per_page, comments, internal notes, attachments, allow_reopen

### V36 - Connections Module (Social Relationships)
- Phoenix_kit_user_follows for one-way follow relationships
- Phoenix_kit_user_connections for two-way mutual connections
- Phoenix_kit_user_blocks for user blocking
- Public API for parent applications to use
- Follow: no consent required, instant
- Connection: requires acceptance from both parties
- Block: prevents all interaction, removes existing relationships
- Settings: connections_enabled

### V37 - Sync Connections & Transfer Tracking
- phoenix_kit_sync_connections for permanent site-to-site connections (renamed in V44)
- phoenix_kit_sync_transfers for tracking all data transfers (renamed in V44)
- Approval modes: auto_approve, require_approval, per_table
- Expiration and download limits (max_downloads, max_records_total)
- Additional security: password protection, IP whitelist, time restrictions
- Receiver-side settings: conflict strategy, auto-sync
- Full audit trail for connections and transfers

### V38 - AI Prompts System
- Phoenix_kit_ai_prompts for reusable prompt templates
- Variable substitution with {{VariableName}} syntax
- Auto-extracted variables stored for validation
- Usage tracking (count and last used timestamp)
- Sorting and organization support

### V39 - Admin Notes System
- Phoenix_kit_admin_notes for internal admin notes about users
- Admin-to-admin communication about user accounts
- Author tracking for accountability
- Any admin can view/edit/delete any note

### V40 - UUID Column Addition
- Adds `uuid` column to all 33 legacy tables using bigserial PKs
- Non-breaking: keeps existing bigserial primary keys intact
- Backfills existing records with generated UUIDs
- Creates unique indexes on uuid columns
- Enables gradual transition to UUID-based lookups

### V41 - AI Prompt Tracking & Reasoning Parameters
- Adds `prompt_id` and `prompt_name` to phoenix_kit_ai_requests
- Tracks which prompt template was used for AI completions
- Denormalized prompt_name preserved for historical display
- Foreign key with ON DELETE SET NULL for prompt deletion
- Adds reasoning/thinking parameters to phoenix_kit_ai_endpoints:
  - `reasoning_enabled` (boolean) - Enable reasoning with default effort
  - `reasoning_effort` (string) - none/minimal/low/medium/high/xhigh
  - `reasoning_max_tokens` (integer) - Hard cap on thinking tokens (1024-32000)
  - `reasoning_exclude` (boolean) - Hide reasoning from response

### V42 - Universal Scheduled Jobs System
- Phoenix_kit_scheduled_jobs for polymorphic scheduled task management
- Behaviour-based handler pattern for extensibility
- Priority-based job execution ordering
- Retry logic with max_attempts and last_error tracking
- Status management: pending, executed, failed, cancelled
- Replaces single-purpose PublishScheduledPostsJob with generic processor
- Supports any schedulable resource (posts, emails, notifications, etc.)

### V43 - Legal Module
- Phoenix_kit_consent_logs for user consent tracking (GDPR/CCPA compliance)
- Supports logged-in users and anonymous visitors via session_id
- Consent types: necessary, analytics, marketing, preferences
- Settings seeds for legal module configuration:
  - legal_enabled, legal_frameworks, legal_company_info
  - legal_dpo_contact, legal_consent_widget_enabled
  - legal_cookie_banner_position

### V44 - Sync Table Rename
- Rename phoenix_kit_db_sync_connections → phoenix_kit_sync_connections
- Rename phoenix_kit_db_sync_transfers → phoenix_kit_sync_transfers
- Rename all related indexes to match new table names
- Rename settings keys: db_sync_* → sync_*
- Matches module rename from DBSync to Sync

### V45 - E-commerce Shop Module
- Phoenix_kit_shop_categories for product organization with nesting
- Phoenix_kit_shop_products for physical and digital products
- Phoenix_kit_shop_shipping_methods, phoenix_kit_shop_carts, phoenix_kit_shop_cart_items
- Phoenix_kit_payment_options for checkout payment methods (COD, bank transfer, Stripe, PayPal)
- Cart supports payment_option_id for payment method selection
- JSONB fields for tags, images, option_names, metadata
- Settings: shop_enabled, shop_currency, shop_tax_enabled, shop_tax_rate, shop_inventory_tracking

### V46 - Product Options with Dynamic Pricing + Import Logs
- Phoenix_kit_shop_config table for global Shop configuration (key-value JSONB)
- Adds option_schema JSONB column to phoenix_kit_shop_categories
- Two-level option system: global options + category-specific options
- Option schema format with types: text, number, boolean, select, multiselect
- Price modifiers support: fixed (+$10) and percent (+20%) modifier types
- Price calculation order: fixed modifiers first, then percent applied to result
- Adds featured_image_id UUID column to products for Storage integration
- Adds image_ids UUID[] array column to products for gallery images
- Adds selected_specs JSONB column to cart_items for specification storage
- Cart items freeze calculated price at add-to-cart time
- Phoenix_kit_shop_import_logs for CSV import history tracking
- Import status tracking: pending, processing, completed, failed
- Import statistics: imported, updated, skipped, errors counts
- Error details stored in JSONB for debugging
- User association for audit trail (who initiated import)
- Enables admin UI for Shopify CSV import management

### V47 - Shop Localized Fields
- Converts Shop module from separate translations JSONB to localized fields approach
- Product fields (title, slug, description, body_html, seo_title, seo_description) become JSONB maps
- Category fields (name, slug, description) become JSONB maps
- Removes translations column from products and categories
- Each field stores language → value map: %{"en" => "Product", "ru" => "Продукт"}
- Migration merges existing canonical data with translations field data
- Default language determined from phoenix_kit_settings.default_language
- GIN indexes on slug fields for efficient localized URL lookups
- Enables explicit language tagging for CSV imports
- Solves language ambiguity problem when changing default language

### V48 - Post and Comment Dislikes
- Creates `phoenix_kit_post_dislikes` table for post dislikes
- Creates `phoenix_kit_comment_likes` table for comment likes
- Creates `phoenix_kit_comment_dislikes` table for comment dislikes
- Adds `dislike_count` column to `phoenix_kit_posts`
- Adds `dislike_count` column to `phoenix_kit_post_comments`
- Unique constraint ensures one like/dislike per user per post/comment
- Frontend can choose to display likes, dislikes, both, or net score

### V49 - Shop Import Enhancements
- Adds option_mappings JSONB column to import_configs for CSV option mapping
- Supports mapping CSV options to global options with slot configuration
- Structure: [{csv_name, slot_key, source_key, auto_add, label}]
- Adds product_ids INTEGER[] column to import_logs for tracking imported products
- Enables import detail view showing all products created/updated during import

### V50 - Bucket Access Type
- Adds access_type VARCHAR column to phoenix_kit_buckets
- Three access modes: "public" (redirect), "private" (proxy), "signed" (future)
- Public: redirect to bucket URL (default, fastest, uses CDN)
- Private: proxy through server (for ACL-protected S3 buckets)
- Enables FileController to handle both public and private S3 buckets

### V51 - Cart Items Unique Constraint + User Deletion FK Fixes
- Fix unique constraint to allow same product with different options
- Include selected_specs in uniqueness check via MD5 hash
- orders.user_id: RESTRICT → SET NULL (preserve orders, anonymize user)
- billing_profiles.user_id: CASCADE → SET NULL (preserve for history)
- tickets.user_id: DELETE_ALL → SET NULL (preserve support history)
- Enables GDPR-compliant user deletion while preserving financial records

### V52 - Shop Localized Slug Functional Unique Index
- Creates extract_primary_slug() SQL function for JSONB slug extraction
- Creates functional unique index on products (primary slug only)
- Creates functional unique index on categories (primary slug only)
- Fixes upsert behavior after V47 JSONB migration
- Language-agnostic: uses alphabetically first key for deterministic extraction

### V53 - Module-Level Permission System
- Creates phoenix_kit_role_permissions table for granular access control
- Allowlist model: row present = granted, absent = denied
- Owner role bypasses permissions entirely (hardcoded in code)
- Admin role gets ALL permissions seeded by default
- Unique constraint on (role_id, module_key) prevents duplicates
- 25 permission keys: 5 core sections + 20 feature modules

### V54 - Category Featured Product + Import Config fix
- Replaces image_url with featured_product_id FK to products
- Auto-populates featured_product_id from first active product with image
- Creates index on featured_product_id
- Drops image_url column from categories
- Image priority: image_id (Storage) → featured_product's featured_image_id
- Adds download_images BOOLEAN to import_configs (schema field was missing from DB)

### V55 - Standalone Comments Module
- Creates polymorphic phoenix_kit_comments table (resource_type + resource_id)
- Creates phoenix_kit_comments_likes and phoenix_kit_comments_dislikes tables
- Self-referencing parent_id for unlimited threading depth
- Counter caches for like_count and dislike_count
- Seeds default comments settings and Admin role permission

### V56 - UUID Column Consistency Fix + UUID FK Columns
- Adds missing uuid column to phoenix_kit_consent_logs (V43 schema expected it)
- Switches 17 tables from gen_random_uuid() (v4) to uuid_generate_v7() (v7)
- Fixes V55 Comments tables UUID PK defaults (gen_random_uuid → uuid_generate_v7)
- Adds NOT NULL constraint to 11 tables (V43, V46, V53, and uuid_repair.ex core tables)
- Adds missing unique indexes on uuid for 13 tables (V43, V45 Shop + uuid_repair.ex core)
- Fixes uuid_repair.ex upgrade path where V40 skipped core tables already having uuid column
- Adds ~80 UUID FK columns alongside integer FKs across ~40 tables
- Backfills UUID FK values via JOIN from source tables (batched for large tables)
- Creates indexes on all new UUID FK columns
- Prepares for UUID primary key switch in Ecto schemas
- All operations idempotent — safe on fresh installs and all upgrade paths
- Existing non-NULL UUID values unchanged

### V57 - UUID FK Column Repair
- Re-runs idempotent UUID FK column operations from V56
- Fixes missing role_uuid and granted_by_uuid on phoenix_kit_role_permissions
- Catches any other UUID FK columns missed when V56 was applied with earlier code
- Safe no-op on databases where V56 already created everything correctly

### V58 - Timestamp Column Type Standardization
- Converts ALL timestamp columns across 68 tables from `timestamp` to `timestamptz`
- Completes DateTime standardization (Elixir `:utc_datetime` + PostgreSQL `timestamptz`)
- No USING clause needed for up (PostgreSQL treats timestamp as UTC implicitly)
- Down uses `USING col AT TIME ZONE 'UTC'` for safe revert to `timestamp(0)`
- Fully idempotent: checks table/column existence and current type before altering

### V59 - Publishing Module Database Tables
- Creates 4 core publishing tables: groups, posts, versions, contents
- JSONB `data` column on every table for extensibility without future migrations
- UUID v7 primary keys, dual-write user FKs, timestamptz timestamps
- One content row per language (mirrors filesystem one-file-per-language model)
- Seeds `publishing_storage` setting (default: "filesystem")

### V60 - Email Templates UUID FK Columns
- Adds `created_by_user_uuid` and `updated_by_user_uuid` columns to phoenix_kit_email_templates
- Fixes schema/migration mismatch where Template schema referenced UUID columns never created
- Idempotent: checks column existence before adding (safe for fresh installs where V15 now includes them)
- Resolves fresh install crash at V30 caused by V15 seed query failing on missing columns

### V61 - UUID Column Safety Net (V40 Flush Fix)
- Adds missing `uuid` column to 6 tables that V40 silently skipped due to Ecto command buffering
- Root cause: V40 used `repo().query()` (immediate) for table existence checks, but V32-V39 table
  creation commands were still buffered (not yet flushed). V31's `flush()` was the last flush before V40.
- Tables fixed: admin_notes, ai_requests, subscriptions, payment_provider_configs, webhook_events, sync_transfers
- Also adds `created_by_uuid` FK column to phoenix_kit_scheduled_jobs
- V40 now includes `flush()` at start of `up()` to prevent recurrence on new installations
- All operations idempotent — safe on any installation

### V62 - UUID Column Naming Cleanup (`_id` → `_uuid`)
- Renames 35 UUID-typed FK columns from `_id` suffix to `_uuid` suffix
- Enforces naming convention: `_id` = integer (legacy/deprecated), `_uuid` = UUID
- Groups: Posts (15), Comments (4), Tickets (6), Storage (3), Publishing (3), Shop (3), Scheduled Jobs (1)
- No data migration — columns already hold correct UUID values, pure rename
- All operations idempotent (IF EXISTS guards) — safe if module tables don't exist
- PostgreSQL auto-updates FK/index column references; constraint object names are unchanged

### V63 - UUID Companion Column Safety Net Round 2
- Adds `uuid` identity column to `phoenix_kit_ai_accounts` (missed by V61 due to wrong table name)
- Adds `account_uuid` companion to `phoenix_kit_ai_requests` (backfilled from ai_accounts)
- Adds `matched_email_log_uuid` companion to `phoenix_kit_email_orphaned_events` (backfilled from email_logs)
- Adds `subscription_uuid` companion to `phoenix_kit_invoices` (backfilled from subscriptions)

### V64 - Fix user token check constraint for UUID-only inserts
- Drops V16's `user_id_required_for_non_registration_tokens` constraint (checks `user_id`)
- Adds `user_uuid_required_for_non_registration_tokens` constraint (checks `user_uuid`)
- Fixes login crash after UUID cleanup removed `user_id` from UserToken schema

### V65 - Rename SubscriptionPlan → SubscriptionType
- Renames `phoenix_kit_subscription_plans` table → `phoenix_kit_subscription_types`
- Renames unique slug index accordingly
- Renames `plan_id` / `plan_uuid` FK columns in `phoenix_kit_subscriptions`
  to `subscription_type_id` / `subscription_type_uuid`
- All operations idempotent (IF EXISTS guards)

### V66 - Make legacy user_id nullable on posts tables
- Drops NOT NULL on `user_id` for 5 posts tables where schemas only set `user_uuid`
- Tables: post_groups, post_comments, post_likes, post_dislikes, post_mentions
- Fixes create_group and like/dislike/comment/mention inserts failing with not_null_violation

### V67 - Make all remaining NOT NULL integer FK columns nullable
- Drops NOT NULL on 42 legacy integer FK columns across 30 tables
- Covers: roles, posts, tickets, storage, admin, auth, audit, connections, billing,
  entities, referrals, standalone comments, and shop modules
- Handles V65 plan_id → subscription_type_id rename (checks both names)
- All operations idempotent (table/column existence + NOT NULL guards)

### V68 - Allow NULL slug for timestamp-mode publishing posts
- Drops NOT NULL on `slug` in `phoenix_kit_publishing_posts`
- Replaces unique index with partial index (slug-mode only, WHERE slug IS NOT NULL)
- Adds unique index on `(group_uuid, post_date, post_time)` for timestamp-mode posts

### V69 - Make legacy integer FK columns nullable on role tables
- Drops NOT NULL on `user_id` and `role_id` in `phoenix_kit_user_role_assignments`
- Drops NOT NULL on `role_id` in `phoenix_kit_role_permissions`
- Fixes role assignment and permission inserts failing with not_null_violation
- All operations idempotent (table/column existence + NOT NULL guards)
- Drops NOT NULL on `slug` in `phoenix_kit_publishing_posts`
- Replaces unique index with partial index (slug-mode only, WHERE slug IS NOT NULL)
- Adds unique index on `(group_uuid, post_date, post_time)` for timestamp-mode posts

### V136 - Staff employment history ⚡ LATEST
- 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`.

### V134 - Folder header customization
- Adds the folder hero-header columns to `phoenix_kit_media_folders`:
  `cover_file_uuid` (background image), `logo_file_uuid` (icon),
  `header_size` (small/medium/large), and the `header_show_*` visibility
  toggles (title / icon / creator / date / file_count / description /
  background). All nullable/defaulted; `ADD COLUMN IF NOT EXISTS`, idempotent.

### V133 - Dashboards table
- Creates `phoenix_kit_dashboards`, backing the `phoenix_kit_dashboards` plugin
  module. A dashboard is a page of placed widgets whose layout is stored as a
  JSONB list of widget instances. Supports personal / system / role scopes;
  `owner_user_uuid` FKs `phoenix_kit_users(uuid)` ON DELETE CASCADE. Idempotent
  `CREATE TABLE IF NOT EXISTS` + indexes.

### V132 - Folder description
- Adds an optional `description TEXT` column to `phoenix_kit_media_folders`
  so admins can add/edit a free-text note describing a folder. Nullable;
  `ADD COLUMN IF NOT EXISTS`, idempotent.

### V131 - `metadata JSONB` on staff people
- Adds a general-purpose `metadata JSONB NOT NULL DEFAULT '{}'` column to
  `phoenix_kit_staff_people` (mirrors `phoenix_kit_entities.entity_data`).
  First consumer is staff soft-delete: stashes the prior lifecycle status
  under `metadata["trashed_from_status"]` so restore can return the person to
  active/inactive. Idempotent `ADD COLUMN IF NOT EXISTS`.

### V130 - Marker annotation kind
- Widens `phoenix_kit_annotations_kind_check` to allow `'marker'` (the Etcher
  highlighter tool). Markers persist via `annotations-changed` but skip the
  composer (no title/comment) — without this the insert is rejected and the
  marker silently fails to save. Idempotent DROP-then-ADD per prefixed table.

### V129 - Add missing subscription_type_uuid column
- Adds `subscription_type_uuid` (UUID, FK to `phoenix_kit_subscription_types(uuid)`
  `ON DELETE SET NULL`) + a partial index to `phoenix_kit_subscriptions`. The
  column the billing `Subscription` schema uses was only ever *renamed* in V65
  (`plan_uuid` → `subscription_type_uuid`), never added, and `plan_uuid` never
  existed — so on a fresh build the column was absent and subscription
  inserts/queries raised `undefined_column`. Idempotent.

### V128 - Assignee on projects (and sub-projects)
- Adds `assigned_team_uuid` / `assigned_department_uuid` / `assigned_person_uuid`
  (FKs to the staff tables, `ON DELETE SET NULL`) to `phoenix_kit_projects`,
  with a `num_nonnulls(...) <= 1` single-assignee CHECK + a partial index per
  FK. Lets a whole project — or, since a sub-project is a project (V127), a
  sub-project — be assigned to a Department/Team/Person like a task.

### V127 - Sub-projects as tasks
- Adds `child_project_uuid` (FK `phoenix_kit_projects(uuid) ON DELETE RESTRICT`)
  to `phoenix_kit_project_assignments` — a sub-project is an assignment that
  points at a child project instead of a task template, so it lives in the
  parent's task timeline with dependencies + drag-reorder for free.
- Drops `NOT NULL` on `task_uuid`; adds a `CHECK` that exactly one of
  `task_uuid` / `child_project_uuid` is set (XOR).
- Partial UNIQUE index on `(child_project_uuid) WHERE NOT NULL` (a project is
  a child of at most one parent); it also serves child-link lookups, since an
  equality predicate implies `IS NOT NULL`.

### V126 - Standalone notifications
- Drops NOT NULL on `phoenix_kit_notifications.activity_uuid` so a
  notification can exist without an originating activity (the unique
  `(activity_uuid, recipient_uuid)` index still holds — NULLs are
  distinct in Postgres).
- Adds `metadata JSONB NOT NULL DEFAULT '{}'` so a standalone
  notification carries its own display content (`Render` reads the same
  `notification_text` / `notification_icon` / `notification_link` keys
  it honors on activity metadata).

### V125 - Project workflow statuses (entities-backed, cement-at-start)
- Adds `status_entity_uuid` (FK `phoenix_kit_entities(uuid) ON DELETE SET NULL`),
  `current_status_slug`, and a generic `settings` JSONB (first key:
  `use_status_translations`) to `phoenix_kit_projects` — the catalog list a
  project/template draws workflow statuses from, the selected status
  (addressed by stable slug), and per-project preferences. NULL entity =
  the shared default list.
- Creates `phoenix_kit_project_statuses` (the cemented per-project copy:
  `project_uuid` FK cascade, `label`/`slug`/`position`, `data` JSONB
  (per-status attrs e.g. colour) + `translations` JSONB (label i18n,
  workspace shape), provenance `source_entity_data_uuid` with no FK).
  Populated when a project starts so running projects use a frozen,
  independently-editable status set.
- Partial index on `(status_entity_uuid) WHERE NOT NULL`; unique
  `(project_uuid, slug)` on the cemented table.
- Orthogonal to `derived_status/2` + `archived_at`; the legacy `status`
  string column is untouched.

### V124 - Partial unique index on media folder names
- Restricts `phoenix_kit_media_folders_name_parent_idx` to
  `WHERE trashed_at IS NULL`. Previously a trashed "untitled"
  folder still reserved its slot in the index, blocking re-creation
  of the same name in the same parent — surfacing as a confusing
  auto-numbering jump or a "Failed to create folder" error after
  the user had emptied the visible parent by sending its children
  to trash. Active-only siblings are now an accurate predictor of
  what the constraint accepts.
- (Renumbered from a pre-merge V122; upstream took V122/V123 for
  location spaces + catalogue folders.)

### V123 - Catalogue folders
- Creates `phoenix_kit_cat_folders` (self-nesting via `parent_uuid`,
  `position`/`status`/`data`) — a dedicated folder layer for organizing
  catalogues, unrelated to the media-folder system.
- Adds nullable `folder_uuid` FK to `phoenix_kit_cat_catalogues`
  (`ON DELETE SET NULL`; NULL = unfiled / root).

### V122 - Location spaces + staff translations + staff Person.name
- Creates `phoenix_kit_location_spaces` for the per-Location nested
  tree of spaces. Required `location_uuid` FK (cascade) and optional
  `parent_uuid` self-ref FK (cascade); arbitrary depth.
- `kind` is a CHECK-constrained enum (floor / room / hall / suite /
  section / zone / aisle / shelf / corner) mirroring the consumer's
  `PhoenixKitLocations.Schemas.Space @kinds`.
- The "child belongs to same Location as parent" cross-row invariant
  is enforced in the consumer context; a composite FK would be
  heavier than the surface justifies.
- Adds `translations JSONB NOT NULL DEFAULT '{}'` to
  `phoenix_kit_staff_departments`, `phoenix_kit_staff_teams`, and
  `phoenix_kit_staff_people` (mirrors the projects V112 settings-
  translations shape: primary stays in dedicated columns, JSONB
  holds non-primary overrides). Translatable fields by schema:
  * Department: `name`, `description`
  * Team: `name`, `description`
  * Person: `job_title`, `bio`, `skills`, `notes`
- Adds a single nullable `name VARCHAR` to `phoenix_kit_staff_people`
  for the staff person's full display name — consistent with every
  other consumer schema in the staff plugin (Department / Team / Space
  / Location all use a single `name`).

### V121 - Line annotation kind
- Widens `phoenix_kit_annotations_kind_check` to accept `'line'`.
- Etcher gains a simple two-endpoint line tool alongside `dimension`
  (same geometry, no arrows, no inline numeric label). Title +
  comment ride the same composer flow as the other non-text shapes.

### V120 - Document Creator Category → Type taxonomy
- Creates `phoenix_kit_doc_categories` and `phoenix_kit_doc_types` tables.
- Adds nullable `category_uuid` / `type_uuid` FK columns to doc templates and documents.
- Migrates legacy category strings from templates into `phoenix_kit_doc_categories` rows.
- Drops the legacy `category` string columns from templates and presets.

### V119 - Trash support for storage folders
- Adds `trashed_at TIMESTAMPTZ` to `phoenix_kit_media_folders`,
  mirroring the V99 column on `phoenix_kit_files`. Folders with a
  non-nil `trashed_at` are in the trash bucket and can be restored
  or permanently deleted. Trash + restore are recursive over the
  folder's descendants and the files inside the subtree (the file
  rows already have their own `trashed_at` from V99 — both get
  set together).
- Partial index on `trashed_at IS NOT NULL` for fast trash-view
  queries.
- All operations idempotent.

### V118 - callout + text kinds + optional title column on annotations
- Drops + re-adds the kind CHECK constraint with `"callout"` and
  `"text"` included alongside `rectangle / circle / polygon /
  freehand`. Etcher 0.2's callout (leader-line) tool needs `"callout"`,
  and its text tool (freestanding label drawn as a click-drag bbox)
  needs `"text"`. Both are folded into one CHECK update so we don't
  take two trips over the same constraint.
- Adds `title varchar(200)` (nullable) to `phoenix_kit_annotations`.
  Every kind can carry a short label — renders inline on the shape
  when non-blank (above the bbox for rect/circle/polygon, at the
  leader endpoint for callout, inside the bbox for text).

### V117 - Document composition
- Adds nullable `category :: varchar` column to `phoenix_kit_doc_templates`
  with index on `(category)` for category-filtered queries.
- Creates `phoenix_kit_doc_document_sections` — join table linking documents
  to templates at ordered positions, with per-section `variable_values` and
  `image_params` JSONB. Unique index on `(document_uuid, position)`. FK to
  documents cascades on delete; FK to templates nullifies on delete.
- Creates `phoenix_kit_doc_template_presets` — named reusable section
  compositions, optionally scoped via `scope_type`/`scope_id` and
  categorized. `sections` is a JSONB array. Index on
  `(scope_type, scope_id, category)`.

### V116 - Parent reference on entity_data
- Adds nullable self-referential `parent_uuid` column to
  `phoenix_kit_entity_data` so each data row can point at another row
  of the same entity as its parent. The feature is a system field on
  every entity_data row — always present, optional to fill, never
  removable by the user (does not appear in
  `entities.fields_definition`).
- No `ON DELETE` cascade: parent/child linkage and same-entity scope
  are managed by the `PhoenixKitEntities.EntityData` context inside a
  transaction. A DB-level cascade would bypass the soft-delete
  machinery and the activity log.
- Same-entity enforcement (a row's parent must share its
  `entity_uuid`) is a context-layer responsibility — the self-FK has
  no view of `entity_uuid`.
- B-tree index on `(parent_uuid)` covers the "list children" query
  used when rendering the WordPress-style indented tree.
- Existing rows stay `parent_uuid = NULL` and become roots — no
  backfill needed.

### V115 - phoenix_kit_annotations table for Etcher-drawn shapes
- Creates `phoenix_kit_annotations` storing user-drawn rectangle /
  circle / polygon / freehand shapes anchored to `phoenix_kit_files`
  rows in image-pixel coordinates. Geometry is JSONB; shape kinds are
  enforced via a DB-level CHECK constraint matching Etcher's v0.1
  tool set.
- `file_uuid` FK `ON DELETE :delete_all` — annotations vanish with
  their host image. `creator_uuid` is nullable + `ON DELETE :nilify_all`
  so deleting a user preserves their annotations as anonymous.
- Discussion threads attach via the existing comments convention
  (`resource_type = "annotation"`, `resource_uuid = annotation.uuid`)
  — no `comment_uuid` column on annotations; the relationship is
  one-directional from the comment side.
- Indexes: `(file_uuid)` for per-file listing, partial
  `(creator_uuid) WHERE creator_uuid IS NOT NULL` for author lookups.

### V114 - Integrations storage: uuid-only keys
- Rewrites every `phoenix_kit_settings` row keyed
  `integration:<provider>:<name>` so that `key = uuid` (the row's
  primary key). Provider and name move into JSONB
  (`value_json->>'provider'`, `value_json->>'name'`); the `module`
  column (`'integrations'`) becomes the row-class discriminator.
- Backfills any missing `provider`/`name` JSONB fields from the old
  composite key. Legacy V0-shape keys without a `:name` segment fold
  to `name = 'default'`.
- Lifts both name restrictions on `add_connection/3` /
  `rename_connection/3`: any non-empty string (after trim) is
  allowed, including spaces, punctuation, and duplicates within a
  provider. The `key` column's unique constraint is satisfied by
  the UUIDv7, not by the human-chosen label.
- `down/1` rewrites keys back to the composite shape. Duplicate
  `(provider, name)` pairs cannot be represented in the old shape,
  so on collision the name is suffixed with `-<8-char uuid>` to
  keep the rewrite well-defined.

### V113 - System-managed media flag for Tessera tiles + comments↔files junction
- Adds `system_managed BOOLEAN DEFAULT false NOT NULL` to
  `phoenix_kit_files`. Marks internally-generated media (DZI tile
  pyramids + per-tile chunks) so the MediaBrowser can exclude them
  from user listings and the variant generator can skip them (tile
  chunks don't need small / medium / large — just an `"original"`
  FileInstance).
- Adds nullable `parent_file_uuid` FK to `phoenix_kit_files(uuid)`
  `ON DELETE :delete_all`. When a source image is hard-deleted, its
  system-managed tile rows cascade away with it.
- Drops `NOT NULL` on `user_uuid`: system-managed rows belong to a
  parent File, not a user. The changeset's
  `validate_system_managed_invariants` enforces "user_uuid OR
  parent_file_uuid" at the app level.
- Two partial indexes: one on `parent_file_uuid` where not null (per-
  source cleanup + listing), one on `inserted_at DESC` where
  `system_managed = false` (keeps the MediaBrowser's "user files
  only" sort cheap as the tile catalog grows).
- Creates `phoenix_kit_comment_media` — junction table letting the
  comments module attach core File rows to individual comments with
  a position + optional caption. Cascade on `comment_uuid` (deleting
  a comment removes its attachments), restrict on `file_uuid` (files
  can't hard-delete while attached). Unique `(comment_uuid, position)`
  slot index + secondary index on `file_uuid` for reverse lookup.
- Partial unique index `phoenix_kit_files_system_dedup_index` on
  `(parent_file_uuid, file_name) WHERE system_managed = true` —
  lets `Storage.store_system_file/3` use `ON CONFLICT DO NOTHING`
  so concurrent lazy-generation requests for the same uncached
  tile dedupe at the DB level instead of producing duplicate File
  rows.
- DB-level CHECK constraint `phoenix_kit_files_user_or_parent_check`
  enforcing `user_uuid IS NOT NULL OR parent_file_uuid IS NOT NULL`
  — the schema's `validate_system_managed_invariants` is the
  user-facing check, this is the safety net for raw inserts.
- All column / FK / NOT-NULL changes use raw SQL with explicit
  `IF NOT EXISTS` / `DO $$ … END $$` guards so re-running on a
  partially-applied schema is a no-op.

### V112 - phoenix_kit_projects: archived_at + translations + drop name uniqueness + position + utc_datetime
- Adds `archived_at TIMESTAMP(0)` to `phoenix_kit_projects` so the
  admin dashboard can soft-hide projects without flipping a status
  enum. Visible-set partial index (`phoenix_kit_projects_visible_idx`
  on `inserted_at DESC WHERE archived_at IS NULL`) keeps both the
  project list and template list queries fast — neither view shows
  archived rows, so one partial covers both `is_template = false`
  and `is_template = true` reads without two scoped indexes.
- Adds `translations JSONB NOT NULL DEFAULT '{}'` to
  `phoenix_kit_projects`, `phoenix_kit_project_tasks`, and
  `phoenix_kit_project_assignments` for per-language overrides on
  user-input content (name, description, title). Primary stays in
  the dedicated columns; the JSONB only carries non-primary
  overrides.
- Drops the three remaining unique-name indexes
  (`phoenix_kit_projects_name_template_index`,
  `phoenix_kit_projects_name_project_index`,
  `phoenix_kit_project_tasks_title_index`). Name uniqueness is now
  policy, not schema — editing or duplicating names no longer
  trips a stale index.
- Retypes `phoenix_kit_projects.scheduled_start_date` from `DATE`
  to `TIMESTAMP(0)` so scheduled-overdue detection honors time-of-
  day (a project scheduled for today 09:00 flips to `:overdue` at
  09:01, not at midnight).
- Adds `position INTEGER NOT NULL DEFAULT 0` to
  `phoenix_kit_project_tasks` and `phoenix_kit_projects` so the
  drag-and-drop reorder API can persist manual ordering. Per-row
  `next_*_position/N` helpers in the projects context auto-assign
  on insert.
- All steps guarded for idempotence (column existence + index
  existence + USING coercion clauses). `down/1` reverses each
  change so a rollback restores the V111 shape.

### V111 - PDF library tables for catalogue
- Creates `phoenix_kit_cat_pdfs` — thin per-upload row. `file_uuid`
  FK to `phoenix_kit_files(uuid)` ON DELETE RESTRICT (catalogue
  manages the file lifecycle; core prune can't delete files we
  reference). Soft-delete via `status` sentinel
  (`active` / `trashed`) + `trashed_at`. Two uploads of identical
  content (different filenames) → two `phoenix_kit_cat_pdfs` rows
  sharing one `phoenix_kit_files` row + one extraction.
- Creates `phoenix_kit_cat_pdf_extractions` — keyed by
  `file_uuid` PK. Holds the worker state machine
  (`pending → extracting → extracted | scanned_no_text | failed`),
  `page_count`, `extracted_at`, `error_message`. Cascades on file
  hard delete.
- Creates `phoenix_kit_cat_pdf_page_contents` — content-addressed
  page dedup cache. PK on `content_hash` (SHA-256 hex of normalized
  page text). Same page text across multiple PDFs is stored once.
- Creates `phoenix_kit_cat_pdf_pages` — per-page join. Composite
  PK `(file_uuid, page_number)`; `content_hash` FK to the dedup
  cache (RESTRICT; orphaned content rows GC'd by a catalogue-side
  helper).
- Enables `pg_trgm` extension; the GIN trigram index lives on the
  dedup cache (smaller index — duplicates indexed once).

### V110 - Add `language` to Document Creator templates
- Adds nullable `language VARCHAR(10)` to `phoenix_kit_doc_templates` so
  each template can be tagged with a single locale (full code, e.g.
  `en-US`, `et-EE`). Parent apps read it back via the public listing
  API to fill template variables in the matching language.
- Documents intentionally do not get a language column — they inherit
  from `template_uuid → templates.language`.
- Existing rows survive without a backfill; the form pre-selects the
  project's primary language when creating new templates.

### V109 - Rename Customer Service module to Customer Support
- Renames 7 settings keys from `customer_service_*` → `customer_support_*`
- Renames `auto_granted_perm:customer_service` → `auto_granted_perm:customer_support`
- Updates `phoenix_kit_role_permissions.module_key` from `customer_service` → `customer_support`

### V108 - Position columns for entity / catalogue / item lists
- Adds nullable `position integer DEFAULT 0` to `phoenix_kit_entities`,
  `phoenix_kit_cat_catalogues`, and `phoenix_kit_cat_items` so the
  three corresponding admin lists support drag-and-drop reordering
- LV reorder handlers re-index the visible group to `1..N` on the
  first user drag — the default `0` is only ever observed transiently
- No btree indexes on the new columns; lists are small and other
  scope filters are already indexed
- `phoenix_kit_entity_data.position` (V81) and category/smart-catalogue
  `position` columns (V87/V102) are unchanged

### V107 - Pin AI endpoints to integration via `integration_uuid`
Adds `phoenix_kit_ai_endpoints.integration_uuid uuid` (nullable) so each
endpoint references the specific integration row it consumes, rather
than a bare provider string that the resolver had to guess against.
Backfills from existing `provider` strings — exact match for
`provider:name` shapes, most-recently-validated for bare providers.

### V106 - Split phoenix_kit_projects.name uniqueness across templates and projects

### V105 - CRM tables
Two tables for the upcoming `phoenix_kit_crm` plugin:
- **phoenix_kit_crm_role_settings**: tracks which user roles are opted into
  the CRM module (`enabled BOOLEAN NOT NULL DEFAULT false`; FK to
  `phoenix_kit_user_roles(uuid)` ON DELETE CASCADE).
- **phoenix_kit_crm_user_role_view**: per-user, per-scope view preferences
  (column selection, ordering, filters) for CRM tables. `scope` is a string
  like `"role:<uuid>"` or `"companies"`. Unique on `(user_uuid, scope)`;
  indexed on `(user_uuid)` for fast per-user lookups.

### V104 - Per-user notifications table
- Creates `phoenix_kit_notifications` with UUID v7 PK and FKs to
  `phoenix_kit_activities` and `phoenix_kit_users` (both ON DELETE CASCADE)
- `seen_at` and `dismissed_at` tracked per-row so dropping one or the
  other is idempotent and bulk operations stay cheap
- Unique index on `(activity_uuid, recipient_uuid)` — one notification
  per activity per recipient (fan-out writes stay safe against retries)
- Partial index on `(recipient_uuid, inserted_at DESC) WHERE dismissed_at
  IS NULL` — covers the main "my undismissed inbox, newest first" query

### V103 - Nested categories
- Adds nullable self-FK `parent_uuid` on `phoenix_kit_cat_categories`
  to support arbitrary-depth category trees. Existing rows become
  roots (NULL parent). Adds a b-tree index on `(parent_uuid)` for the
  "list children" query.

### V102 - Catalogue discount + smart catalogues
Two related catalogue features bundled together:
- **Discount**: `discount_percentage DECIMAL(7, 2) NOT NULL DEFAULT 0`
  on `phoenix_kit_cat_catalogues` (whole-catalogue default) and
  nullable `discount_percentage DECIMAL(7, 2)` on `phoenix_kit_cat_items`
  (per-item override; `NULL` inherits, any value including `0` overrides).
  Pricing chain: `base → markup → discount`; `sale_price` stays
  "after markup, before discount" and new `final_price` lives on top.
- **Smart catalogues**: `kind VARCHAR(20) NOT NULL DEFAULT 'standard'`
  on `phoenix_kit_cat_catalogues` (one of `'standard'` or `'smart'`).
  Items in a smart catalogue reference *other* catalogues with a value
  + unit; items also get nullable `default_value DECIMAL(12, 4)` and
  `default_unit VARCHAR(20)` as a per-item fallback. New table
  `phoenix_kit_cat_item_catalogue_rules` stores the item → referenced
  catalogue pairs with nullable `value`/`unit` (inherit from item
  defaults) and a `position INTEGER` for UI ordering.

### V101 - Projects module tables
- Creates `phoenix_kit_project_tasks` (reusable task library),
  `phoenix_kit_project_task_dependencies` (template-level ordering),
  `phoenix_kit_projects`, `phoenix_kit_project_assignments`
  (task instances with polymorphic assignee), and
  `phoenix_kit_project_dependencies` (per-project task ordering)
- Assignment FKs reference staff module tables (teams, departments, people)
- CHECK constraint enforces at-most-one assignee (team / department / person)
  on both `phoenix_kit_project_tasks` and `phoenix_kit_project_assignments`

### V100 - Staff module tables
- Creates `phoenix_kit_staff_departments`, `phoenix_kit_staff_teams`,
  `phoenix_kit_staff_people` (FK to `phoenix_kit_users`), and
  `phoenix_kit_staff_team_memberships` join table
- UUIDv7 PKs, cascading deletes dept → team → team_memberships, and
  user → person → team_memberships

### V99 - Media file trash bucket
- Adds `trashed_at` (timestamptz) to `phoenix_kit_files` for soft-delete
- Partial index on `trashed_at` for efficient trash queries

### V98 - Storage dimension alternative formats
- Adds `alternative_formats` (`text[]`) to `phoenix_kit_storage_dimensions`
- Enables multi-format variant generation (e.g., WebP + AVIF alongside JPEG)

### V97 - Per-item markup override
- Adds nullable `markup_percentage DECIMAL(7, 2)` column on
  `phoenix_kit_cat_items`
- `NULL` = inherit the catalogue's markup (existing behavior);
  any set value (including `0`) overrides the catalogue's markup
- No backfill — existing rows stay `NULL` and continue to inherit

### V96 - Catalogue items linked directly to catalogues
- Adds nullable `catalogue_uuid` FK on `phoenix_kit_cat_items` so items can
  belong to a catalogue independently of having a category
- Backfills existing items from their category's catalogue_uuid
- Pins any remaining orphans to the oldest non-deleted catalogue
- Adds indexes on `catalogue_uuid` and `(catalogue_uuid, status)`

### V95 - Media folders and folder links
- Creates `phoenix_kit_media_folders` and `phoenix_kit_media_folder_links` tables
- Adds organizational folder hierarchy for media files (metadata-only;
  storage buckets are unaware of them)

### V94 - Document Creator local DB sync
- Adds `google_doc_id` (VARCHAR(255)) to `phoenix_kit_doc_templates`, `phoenix_kit_doc_documents`, `phoenix_kit_doc_headers_footers`
- Adds `status` (VARCHAR(20), DEFAULT 'published') to `phoenix_kit_doc_documents`
- Partial unique indexes on `google_doc_id WHERE google_doc_id IS NOT NULL`

### V93 - Settings prefix index
- Adds `text_pattern_ops` B-tree index on `phoenix_kit_settings.key` for efficient LIKE prefix queries
- Used by the integrations system for `LIKE 'integration:provider:%'` lookups

### V92 - Organization Accounts
- Adds `account_type` column (VARCHAR(20), NOT NULL, DEFAULT 'person') with CHECK constraint
- Adds `organization_name` column (VARCHAR(255)) for organization display names
- Adds `organization_uuid` self-referencing FK to link persons to organizations
- Indexes on `account_type` and `organization_uuid`

### V91 - Locations tables
- `phoenix_kit_location_types` for user-defined location categories
- `phoenix_kit_locations` for physical locations with type reference
- `phoenix_kit_location_type_assignments` for many-to-many join

### V90 - Activity feed
- `phoenix_kit_activities` table for business-level action logging

### V89 - Catalogue pricing
- Renames `price` to `base_price` in `phoenix_kit_cat_items`
- Adds `markup_percentage` decimal column to `phoenix_kit_cat_catalogues`

### V88 - Publishing schema V2
- Restructures posts/versions/contents for publishing module
- Adds `active_version_uuid`, `trashed_at` to posts
- Adds `published_at` to versions
- Data migration from legacy columns
- Drops legacy post columns: `scheduled_at`, `status`, `published_at`, `primary_language`, `data`

### V87 - Add Catalogue tables
- Creates `phoenix_kit_cat_manufacturers` — manufacturer directory
- Creates `phoenix_kit_cat_suppliers` — supplier directory
- Creates `phoenix_kit_cat_manufacturer_suppliers` — many-to-many join with unique constraint
- Creates `phoenix_kit_cat_catalogues` — top-level catalogue groupings
- Creates `phoenix_kit_cat_categories` — subdivisions within a catalogue (with position ordering)
- Creates `phoenix_kit_cat_items` — individual products/materials with SKU, price, unit

### V86 - Add Document Creator tables
- Creates `phoenix_kit_doc_headers_footers` — reusable header/footer designs
- Creates `phoenix_kit_doc_templates` — document templates with GrapesJS editor content
- Creates `phoenix_kit_doc_documents` — documents created from templates with baked header/footer

### V85 - Add system_prompt to AI prompts
- Adds `system_prompt` (text) column to `phoenix_kit_ai_prompts`
- Allows storing system-level instructions separately from user prompt content

### V84 - Rename mailing tables to newsletters
- Idempotently renames `phoenix_kit_mailing_*` tables to `phoenix_kit_newsletters_*`
- Fixes databases that ran the old V79 (which created `mailing_*` tables)
- Safe to run multiple times — uses IF EXISTS guards

### V83 - Add status to publishing groups
- Adds `status` column (varchar(20), default 'active') to `phoenix_kit_publishing_groups`
- Supports soft-delete via "trashed" status
- Adds index on `(status)` for filtering

### V82 - Add metadata JSONB column to comments
- Adds `metadata` column (jsonb, default `'{}'`) to `phoenix_kit_comments`
- Enables storing arbitrary extra data on comments without schema changes

### V81 - Add position column to entity_data
- Adds `position` integer column to `phoenix_kit_entity_data` for manual reordering
- Backfills existing records based on creation date
- Adds composite index on `(entity_uuid, position)`

### V80 - Emails i18n: JSON language fields
- Converts 5 fields in `phoenix_kit_email_templates` to JSONB for multilingual support
  (`subject`, `html_body`, `text_body`, `display_name`, `description`)
- Existing string values are wrapped as `{"en": "original_value"}`
- Adds `locale VARCHAR(10)` to `phoenix_kit_email_logs` for tracking sent language

### V79 - Newsletters module: newsletter lists, broadcasts, deliveries
- Creates `phoenix_kit_newsletters_lists`, `phoenix_kit_newsletters_list_members`,
  `phoenix_kit_newsletters_broadcasts`, `phoenix_kit_newsletters_deliveries`

### V77 - Rename Tickets module to Customer Service
- Renames settings keys from `tickets_*` → `customer_service_*`
- Renames `auto_granted_perm:tickets` → `auto_granted_perm:customer_service`
- Updates `phoenix_kit_role_permissions.module_key` from `tickets` → `customer_service`

### V70 - Re-backfill UUID FK columns silently skipped in V56/V63
- Fixes installs where `phoenix_kit_email_logs.uuid` was `character varying` instead
  of native `uuid` type, causing V56's backfill to fail or be silently skipped
- Converts `phoenix_kit_email_logs.uuid` to native `uuid` type if needed
- Re-backfills `email_log_uuid` in `phoenix_kit_email_events` (resets stale random
  UUIDs written by the V56 NULL-fill fallback, then re-runs the proper JOIN backfill)
- Re-backfills `matched_email_log_uuid` in `phoenix_kit_email_orphaned_events`
- All operations idempotent — safe on every install

### V72 - Rename `id` → `uuid` on 30 Category A tables
- Metadata-only column rename (instant, zero downtime)
- Add 4 missing FK constraints (comments, scheduled_jobs)

### V73 - Pre-drop prerequisites for Category B tables
- SET NOT NULL on 7 uuid columns
- CREATE UNIQUE INDEX on 3 tables
- ALTER INDEX RENAME on 4 indexes

### V74 - Drop integer columns, promote `uuid` to PK
- Drop all FK constraints referencing integer `id` columns
- Drop ~95 integer FK columns across all tables
- Drop bigint `id` PK + promote `uuid` to PK on 47 Category B tables
- After V74, every PhoenixKit table uses `uuid` as its primary key

### V75 - Fix uuid column defaults, cleanup
- Set DEFAULT uuid_generate_v7() on 27 tables missing it (Category A)
- Fix 4 tables using gen_random_uuid() → uuid_generate_v7()
- Drop orphaned phoenix_kit_id_seq sequence

## Migration Paths

### Fresh Installation (0 → Current)
Runs all migrations V01 through V27 in sequence.

### Incremental Updates
- V01 → V27: Runs V02 through V27 in sequence
- V26 → V27: Runs V27 only (adds Oban tables)
- V25 → V27: Runs V26 and V27 in sequence
- V24 → V27: Runs V25, V26, and V27 in sequence
- V20 → V27: Runs V21 through V27 in sequence

### Rollback Support
- V27 → V26: Removes Oban tables and background job system
- V26 → V25: Removes user_file_checksum, renames file_checksum back to checksum, restores checksum unique index
- V25 → V24: Removes aspect ratio control from dimensions
- V24 → V23: Removes unique index on checksum
- V23 → V22: Removes session fingerprinting columns and indexes
- V22 → V21: Removes audit logging system, email orphaned events, and email metrics
- V21 → V20: Removes composite message ID index
- V15 → V14: Removes email templates system
- V14 → V13: Removes body compression support
- V13 → V12: Removes enhanced email tracking and AWS SES integration
- V12 → V11: Removes JSON settings support and restores NOT NULL constraint
- V11 → V10: Removes per-user timezone settings
- V10 → V09: Removes registration analytics system
- V09 → V08: Removes email blocklist system
- V08 → V07: Removes username support
- V07 → V06: Removes email tracking system
- Full rollback to V01: Keeps only basic authentication

## Usage Examples

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

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

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

    # Complete rollback
    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

# `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*