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 (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 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 ⚡ 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