PhoenixKit.Migrations.Postgres (phoenix_kit v1.5.1)

View Source

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
  • 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

Summary

Functions

Get current migrated version from database in runtime context (outside migrations).

Functions

migrated_version_runtime(opts)

Get current migrated version from database in runtime context (outside migrations).

This function can be called from Mix tasks and other non-migration contexts.