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 - Modules and Referral Codes System

• Phoenix_kit_modules for feature management
• Phoenix_kit_referral_codes for user referrals
• Module-based feature toggles
• Referral tracking and analytics

V15 - Email Templates System

• Phoenix_kit_email_templates for template storage and management
• Template variables with {{variable}} syntax
• Template categories (system, marketing, transactional)
• Template versioning and usage tracking
• Integration with 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 - Distributed File Storage System ⚡ LATEST

• 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 (0 = random/emptiest, >0 = specific priority)
• Token-based URL security to prevent enumeration attacks
• Support for images, videos, documents, and archives
• Automatic variant generation system (8 default dimensions seeded)
• Storage settings (redundancy_copies, auto_generate_variants, default_bucket_id)

Migration Paths

Fresh Installation (0 → Current)

Runs all migrations V01 through V19 in sequence.

Incremental Updates

• V01 → V19: Runs V02 through V19 in sequence
• V18 → V19: Runs V19 only (adds distributed storage system)
• V17 → V19: Runs V18, V19 (adds custom fields, then storage system)
• V16 → V19: Runs V17, V18, V19 (adds entities, custom fields, storage)
• V15 → V19: Runs V16, V17, V18, V19 (adds OAuth, entities, custom fields, storage)
• V14 → V19: Runs V15, V16, V17, V18, V19 (adds templates, OAuth, entities, custom fields, storage)
• V13 → V19: Runs V14, V15, V16, V17, V18, V19 (adds modules, templates, OAuth, entities, custom fields, storage)

Rollback Support

• V19 → V18: Removes distributed storage system
• V18 → V17: Removes user custom fields
• V17 → V16: Removes entities system
• V16 → V15: Removes OAuth providers system
• V15 → V14: Removes email templates system
• V14 → V13: Removes modules and referral codes
• 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
PhoenixKit.Migrations.Postgres.up(prefix: "myapp")

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

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

# 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