Packages
phoenix_kit
1.7.189
1.7.208
1.7.207
1.7.206
1.7.205
1.7.204
1.7.203
1.7.202
1.7.201
1.7.200
1.7.199
1.7.198
1.7.197
1.7.196
1.7.194
1.7.193
1.7.192
1.7.191
1.7.190
1.7.189
1.7.187
1.7.186
1.7.185
1.7.184
1.7.183
1.7.182
1.7.181
1.7.180
1.7.179
1.7.178
1.7.177
1.7.176
1.7.175
1.7.174
1.7.173
1.7.172
1.7.171
1.7.170
1.7.169
1.7.168
1.7.167
1.7.166
1.7.165
1.7.164
1.7.162
1.7.161
1.7.160
1.7.159
1.7.157
1.7.156
1.7.155
1.7.154
1.7.153
1.7.152
1.7.151
1.7.150
1.7.149
1.7.146
1.7.145
1.7.144
1.7.143
1.7.138
1.7.133
1.7.132
1.7.131
1.7.130
1.7.128
1.7.126
1.7.125
1.7.121
1.7.120
1.7.119
1.7.118
1.7.117
1.7.116
1.7.115
1.7.114
1.7.113
1.7.112
1.7.111
1.7.110
1.7.109
1.7.108
1.7.107
1.7.106
1.7.105
1.7.104
1.7.103
1.7.102
1.7.101
1.7.100
1.7.99
1.7.98
1.7.97
1.7.96
1.7.95
1.7.94
1.7.93
1.7.92
1.7.91
1.7.90
1.7.89
1.7.88
1.7.87
1.7.86
1.7.85
1.7.84
1.7.83
1.7.82
1.7.81
1.7.80
1.7.79
1.7.78
1.7.77
1.7.76
1.7.75
1.7.74
1.7.71
1.7.70
1.7.69
1.7.66
1.7.65
1.7.64
1.7.63
1.7.62
1.7.61
1.7.59
1.7.58
1.7.57
1.7.56
1.7.55
1.7.54
1.7.53
1.7.52
1.7.51
1.7.49
1.7.44
1.7.43
1.7.42
1.7.41
1.7.39
1.7.38
1.7.37
1.7.36
1.7.34
1.7.33
1.7.31
1.7.30
1.7.29
1.7.28
1.7.27
1.7.26
1.7.25
1.7.24
1.7.23
1.7.22
1.7.21
1.7.20
1.7.19
1.7.18
1.7.17
1.7.16
1.7.15
1.7.14
1.7.13
1.7.12
1.7.11
1.7.10
1.7.9
1.7.8
1.7.7
1.7.6
1.7.5
1.7.4
1.7.3
1.7.2
1.7.1
1.7.0
1.6.20
1.6.19
1.6.18
1.6.17
1.6.16
1.6.15
1.6.14
1.6.13
1.6.12
1.6.11
1.6.10
1.6.9
1.6.8
1.6.7
1.6.6
1.6.5
1.6.4
1.6.3
1.5.2
1.5.1
1.5.0
1.4.9
1.4.8
1.4.7
1.4.6
1.4.5
1.4.4
1.4.3
1.4.2
1.4.1
1.4.0
1.3.2
1.3.1
1.3.0
1.2.10
1.2.9
1.2.8
1.2.7
1.2.5
1.2.4
1.2.2
1.2.1
1.2.0
1.1.0
1.0.0
A foundation for building Elixir Phoenix apps — SaaS, social networks, ERP systems, marketplaces, and more
Current section
Files
Jump to
Current section
Files
lib/phoenix_kit/migrations/postgres.ex
defmodule PhoenixKit.Migrations.Postgres do
@moduledoc """
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
### V142 - Wider role-permission keys âš¡ LATEST
- 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`.
### 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
"""
@behaviour PhoenixKit.Migration
use Ecto.Migration
alias PhoenixKit.Migrations.Postgres.Helpers
@initial_version 1
@current_version 142
@default_prefix "public"
# First version whose SQL references uuid_generate_v7(). Chains that
# start at or above it never hit the V40/V56/V61/V63 creation sites,
# so the entry point has to guarantee the function exists in the
# install's schema before any newer version's DDL calls it.
@uuid_fn_version 40
@doc false
def initial_version, do: @initial_version
@doc false
def current_version, do: @current_version
@impl PhoenixKit.Migration
def up(opts) do
opts = with_defaults(opts, @current_version)
initial = migrated_version(opts)
cond do
initial == 0 ->
change(@initial_version..opts.version, :up, opts)
initial < opts.version ->
if initial >= @uuid_fn_version, do: Helpers.ensure_uuid_v7_function(opts.prefix)
change((initial + 1)..opts.version, :up, opts)
true ->
:ok
end
end
@impl PhoenixKit.Migration
def down(opts) do
# For down operations, don't set a default version - let target_version logic handle it
opts = Enum.into(opts, %{prefix: @default_prefix})
Helpers.validate_prefix!(opts.prefix)
opts =
opts
|> Map.put(:quoted_prefix, inspect(opts.prefix))
|> Map.put(:escaped_prefix, String.replace(opts.prefix, "'", "\\'"))
|> Map.put_new(:create_schema, opts.prefix != @default_prefix)
current_version = migrated_version(opts)
# Determine target version:
# - If version not specified, rollback to complete removal (0)
# - If version specified, rollback to that version
target_version = Map.get(opts, :version, 0)
if current_version > target_version do
# For rollback from version N to version M, execute down for versions N, N-1, ..., M+1
# This means we don't execute down for the target version itself
change(current_version..(target_version + 1)//-1, :down, opts)
end
end
@impl PhoenixKit.Migration
def migrated_version(opts) do
opts = with_defaults(opts, @initial_version)
escaped_prefix = Map.fetch!(opts, :escaped_prefix)
# First check if phoenix_kit table exists
table_exists_query = """
SELECT EXISTS (
SELECT FROM information_schema.tables
WHERE table_name = 'phoenix_kit'
AND table_schema = '#{escaped_prefix}'
)
"""
case repo().query(table_exists_query, [], log: false) do
{:ok, %{rows: [[true]]}} ->
# Table exists, check for version comment
version_query = """
SELECT pg_catalog.obj_description(pg_class.oid, 'pg_class')
FROM pg_class
LEFT JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE pg_class.relname = 'phoenix_kit'
AND pg_namespace.nspname = '#{escaped_prefix}'
"""
case repo().query(version_query, [], log: false) do
{:ok, %{rows: [[version]]}} when is_binary(version) -> String.to_integer(version)
# Table exists but no version comment - assume version 1 (legacy V01 installation)
_ -> 1
end
{:ok, %{rows: [[false]]}} ->
# Table doesn't exist - no PhoenixKit installed
0
_ ->
0
end
end
@doc """
Get current migrated version from database in runtime context (outside migrations).
This function can be called from Mix tasks and other non-migration contexts.
"""
def migrated_version_runtime(opts) do
opts = with_defaults(opts, @initial_version)
escaped_prefix = Map.fetch!(opts, :escaped_prefix)
# Add retry logic for better reliability
retry_version_detection(opts, escaped_prefix, 3)
rescue
# An invalid prefix must surface as the validation error, not be
# swallowed into 0 ("not installed") — that misleads the operator AND
# lets the unvalidated string reach interpolated SQL in callers'
# fallback paths.
e in ArgumentError ->
reraise e, __STACKTRACE__
_ ->
0
end
# Retry version detection with exponential backoff
defp retry_version_detection(opts, escaped_prefix, retries_left) when retries_left > 0 do
# Use hybrid repo detection with fallback strategies
case get_repo_with_fallback() do
nil when retries_left > 1 ->
# Wait a bit and retry
Process.sleep(100)
retry_version_detection(opts, escaped_prefix, retries_left - 1)
nil ->
0
repo ->
# Ensure repo is started before querying database
case ensure_repo_started(repo) do
:ok ->
case check_version_with_runtime_repo(repo, escaped_prefix) do
0 when retries_left > 1 ->
# If we get 0 but repo is available, retry once more
Process.sleep(50)
check_version_with_runtime_repo(repo, escaped_prefix)
version ->
version
end
{:error, _reason} when retries_left > 1 ->
# If repo can't be started, wait and retry
Process.sleep(100)
retry_version_detection(opts, escaped_prefix, retries_left - 1)
{:error, _reason} ->
# Final retry failed - return 0 (not installed)
0
end
end
rescue
_ ->
if retries_left > 1 do
Process.sleep(100)
retry_version_detection(opts, escaped_prefix, retries_left - 1)
else
0
end
end
defp retry_version_detection(_opts, _escaped_prefix, 0), do: 0
# Check version using runtime repo (same logic as migrated_version)
defp check_version_with_runtime_repo(repo, escaped_prefix) do
# First check if phoenix_kit table exists
table_exists_query = """
SELECT EXISTS (
SELECT FROM information_schema.tables
WHERE table_name = 'phoenix_kit'
AND table_schema = '#{escaped_prefix}'
)
"""
case repo.query(table_exists_query, [], log: false) do
{:ok, %{rows: [[true]]}} ->
# Table exists, check for version comment
version_query = """
SELECT pg_catalog.obj_description(pg_class.oid, 'pg_class')
FROM pg_class
LEFT JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE pg_class.relname = 'phoenix_kit'
AND pg_namespace.nspname = '#{escaped_prefix}'
"""
case repo.query(version_query, [], log: false) do
{:ok, %{rows: [[version]]}} when is_binary(version) -> String.to_integer(version)
# Table exists but no version comment - assume version 1 (legacy V01 installation)
_ -> 1
end
{:ok, %{rows: [[false]]}} ->
# Table doesn't exist - no PhoenixKit installed
0
_ ->
0
end
end
@doc """
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.
"""
def heal_version_comment(reported_version, opts) do
escaped_prefix = Map.get(opts, :escaped_prefix, opts[:prefix] || "public")
# Local guard (defense in depth) — current callers pre-validate, but
# this function interpolates the prefix into DDL and must not rely
# on every future caller doing so.
Helpers.validate_prefix!(escaped_prefix)
prefix_str = if escaped_prefix != "public", do: "#{escaped_prefix}.", else: ""
case get_repo_with_fallback() do
nil ->
:ok
repo ->
healed =
version_checks()
|> Enum.filter(fn {v, _query} -> v > reported_version and v <= @current_version end)
|> Enum.sort_by(fn {v, _} -> v end)
|> Enum.reduce(reported_version, fn {v, check_query_fn}, acc ->
query = check_query_fn.(escaped_prefix, prefix_str)
case repo.query(query, [], log: false) do
{:ok, %{rows: [[true]]}} -> v
_ -> acc
end
end)
if healed > reported_version do
comment_query =
"COMMENT ON TABLE #{prefix_str}phoenix_kit IS '#{healed}'"
repo.query(comment_query, [], log: false)
{:healed, healed}
else
:ok
end
end
rescue
_ -> :ok
end
# Schema artifact checks for versions that may have had comment bugs.
# Each entry is {version, fn(escaped_prefix, prefix_str) -> verification_query}.
defp version_checks do
[
{83,
fn escaped_prefix, _prefix_str ->
"""
SELECT EXISTS (
SELECT FROM information_schema.columns
WHERE table_schema = '#{escaped_prefix}'
AND table_name = 'phoenix_kit_publishing_groups'
AND column_name = 'status'
)
"""
end}
]
end
defp change(range, direction, opts) do
range_list = Enum.to_list(range)
total_steps = length(range_list)
show_migration_header(range_list, direction, total_steps)
execute_migration_steps(range_list, direction, opts, total_steps)
show_completion_message(total_steps)
handle_version_recording(direction, range, opts, total_steps)
end
# Show migration progress header for multi-step migrations
defp show_migration_header(range_list, direction, total_steps) do
if total_steps > 1 do
{start_version, end_version} =
case direction do
:up -> {Enum.min(range_list), Enum.max(range_list)}
:down -> {Enum.max(range_list), Enum.min(range_list)}
end
action = if direction == :up, do: "Applying", else: "Rolling back"
IO.puts(
"🔄 #{action} PhoenixKit V#{String.pad_leading(to_string(start_version), 2, "0")}→V#{String.pad_leading(to_string(end_version), 2, "0")}"
)
end
end
# Execute migration steps with progress tracking
defp execute_migration_steps(range_list, direction, opts, total_steps) do
range_list
|> Enum.with_index()
|> Enum.each(fn {index, step_index} ->
pad_idx = String.pad_leading(to_string(index), 2, "0")
# Show progress bar for multi-step migrations
if total_steps > 1 do
show_migration_progress(step_index + 1, total_steps, "V#{pad_idx}")
end
[__MODULE__, "V#{pad_idx}"]
|> Module.concat()
|> apply(direction, [opts])
end)
end
# Show completion message for multi-step migrations
defp show_completion_message(total_steps) do
if total_steps > 1 do
IO.puts("✅ PhoenixKit migration complete\n")
end
end
# Handle version recording based on direction
defp handle_version_recording(direction, range, opts, total_steps) do
case direction do
:up ->
# For up migrations, only set final version comment for multi-step migrations
# Individual migrations handle their own version comments for single steps
if total_steps > 1 do
record_version(opts, Enum.max(range))
end
:down ->
# For down migrations, let individual migration handle version comments
# This prevents conflicts with version comments in migration down() functions
:ok
end
end
# Show migration progress bar
defp show_migration_progress(current_step, total_steps, version_info) do
percentage = div(current_step * 100, total_steps)
progress_width = 20
filled_width = div(current_step * progress_width, total_steps)
empty_width = progress_width - filled_width
filled_bar = String.duplicate("â–ˆ", filled_width)
empty_bar = String.duplicate("â–’", empty_width)
progress_bar = "#{filled_bar}#{empty_bar}"
# Use carriage return to update the same line
IO.write(
"\r#{progress_bar} #{percentage}% (#{current_step}/#{total_steps} migrations) #{version_info}"
)
# Add newline after the last step
if current_step == total_steps do
IO.puts("")
end
end
defp record_version(_opts, 0) do
# Handle rollback to version 0 - tables are dropped, so we can't update comment
# This is expected behavior for complete rollback
:ok
end
defp record_version(%{prefix: prefix}, version) do
# Use execute for migration context - only once per migration cycle
execute "COMMENT ON TABLE #{prefix}.phoenix_kit IS '#{version}'"
end
# Get the application that owns the repo module
defp with_defaults(opts, version) do
opts = Enum.into(opts, %{prefix: @default_prefix, version: version})
Helpers.validate_prefix!(opts.prefix)
opts
|> Map.put(:quoted_prefix, inspect(opts.prefix))
|> Map.put(:escaped_prefix, String.replace(opts.prefix, "'", "\\'"))
|> Map.put_new(:create_schema, opts.prefix != @default_prefix)
end
# Hybrid repo detection with fallback strategies (shared with status command)
defp get_repo_with_fallback do
# Strategy 1: Try to get from PhoenixKit application config
case PhoenixKit.Config.get_repo() do
nil ->
# Strategy 2: Try to ensure PhoenixKit application is started
case ensure_phoenix_kit_started() do
repo when not is_nil(repo) ->
repo
nil ->
# Strategy 3: Auto-detect from project configuration
detect_repo_from_project()
end
repo ->
repo
end
end
# Try to start PhoenixKit application and get repo config
defp ensure_phoenix_kit_started do
Application.ensure_all_started(:phoenix_kit)
PhoenixKit.Config.get_repo()
rescue
_ -> nil
end
# Auto-detect repository from project configuration
defp detect_repo_from_project do
parent_app_name = Mix.Project.config()[:app]
# Try :ecto_repos config first
case try_ecto_repos_config(parent_app_name) do
nil -> try_naming_patterns(parent_app_name)
repo -> repo
end
end
# Try to get repo from :ecto_repos application config
defp try_ecto_repos_config(nil), do: nil
defp try_ecto_repos_config(app_name) do
case Application.get_env(app_name, :ecto_repos, []) do
[repo | _] when is_atom(repo) ->
if ensure_repo_loaded?(repo), do: repo, else: nil
[] ->
nil
end
rescue
_ -> nil
end
# Try common naming patterns
defp try_naming_patterns(nil), do: nil
defp try_naming_patterns(app_name) do
# Try most common pattern: AppName.Repo
repo_module = Module.concat([Macro.camelize(to_string(app_name)), "Repo"])
if ensure_repo_loaded?(repo_module) do
repo_module
else
nil
end
end
# Check if repo module exists and is loaded
defp ensure_repo_loaded?(repo) when is_atom(repo) and not is_nil(repo) do
Code.ensure_loaded?(repo) && function_exported?(repo, :__adapter__, 0)
rescue
_ -> false
end
defp ensure_repo_loaded?(_), do: false
# Ensure repo is properly started for database operations.
# In --no-start context, Repo process may not be running yet.
defp ensure_repo_started(repo) do
if Process.whereis(repo) != nil do
:ok
else
start_repo_with_config(repo)
end
rescue
error -> {:error, "Failed to start repo: #{inspect(error)}"}
end
defp start_repo_with_config(repo) do
# Try Mix.Ecto.ensure_repo first (handles config resolution)
if Code.ensure_loaded?(Mix.Ecto) do
Mix.Ecto.ensure_repo(repo, [])
# ensure_repo loads but doesn't start — start the process
if Process.whereis(repo) == nil do
do_start_repo(repo)
else
:ok
end
else
do_start_repo(repo)
end
end
defp do_start_repo(repo) do
# Get config from parent app's application env
app =
if Code.ensure_loaded?(Mix) and function_exported?(Mix.Project, :config, 0),
do: Mix.Project.config()[:app]
config = if app, do: Application.get_env(app, repo, []), else: []
# Ensure required applications are started before starting repo
# These must be started for repo.start_link/1 to work:
# - :telemetry (for DBConnection metrics)
# - :db_connection (provides DBConnection.Watcher)
# - :ecto (provides Ecto.Repo.Registry)
# - :postgrex (provides Postgrex.SCRAM.LockedCache)
Application.ensure_all_started(:telemetry)
Application.ensure_all_started(:db_connection)
Application.ensure_all_started(:ecto)
Application.ensure_all_started(:postgrex)
case repo.start_link(config) do
{:ok, _pid} -> :ok
{:error, {:already_started, _pid}} -> :ok
{:error, reason} -> {:error, "Failed to start #{inspect(repo)}: #{inspect(reason)}"}
end
end
end