PhoenixKit.Migrations.Postgres (phoenix_kit v1.6.16)
View SourcePhoenixKit 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 (WordPress ACF-like)
- 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
checksumtofile_checksum(clearer naming) - Removes unique index on file_checksum (allows same file from different users)
- Adds
user_file_checksumcolumn (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 ⚡ LATEST
- 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
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
Summary
Functions
Get current migrated version from database in runtime context (outside migrations).