ULID Migration Plan — Email Server + Laravel API¶

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    /**
     * Tables with auto-increment INT id → ULID CHAR(26)
     */
    private array $intIdTables = [
        'users',
        'domains',
        'mailboxes',
        'aliases',
        'subscriptions',
        'plans',
        'invoices',
        'api_keys',
        'invitations',
        'hosts',
        'data_operations',
        'discounts',
        'activity_log',
    ];

    /**
     * Foreign key mappings: [table => [column => referenced_table]]
     */
    private array $foreignKeys = [
        'domains' => [
            'organization_id' => 'organizations',
            'user_id' => 'users',
        ],
        'mailboxes' => [
            'domain_id' => 'domains',
            'user_id' => 'users',
        ],
        'aliases' => [
            'domain_id' => 'domains',
        ],
        'subscriptions' => [
            'user_id' => 'users',
            'plan_id' => 'plans',
            'organization_id' => 'organizations',
        ],
        'invoices' => [
            'user_id' => 'users',
            'subscription_id' => 'subscriptions',
        ],
        'invitations' => [
            'organization_id' => 'organizations',
            'invited_by' => 'users',
        ],
    ];

    public function up(): void
    {
        // Step 1: Drop all foreign key constraints
        foreach ($this->foreignKeys as $table => $columns) {
            Schema::table($table, function (Blueprint $t) use ($columns) {
                foreach (array_keys($columns) as $column) {
                    try {
                        $t->dropForeign([$column]);
                    } catch (\Exception $e) {
                        // FK may not exist
                    }
                }
            });
        }

        // Step 2: Convert primary keys from INT to CHAR(26)
        foreach ($this->intIdTables as $table) {
            if (!Schema::hasTable($table)) continue;

            // Add new ULID column
            Schema::table($table, function (Blueprint $t) {
                $t->char('ulid_id', 26)->nullable()->after('id');
            });

            // Generate ULIDs for existing rows
            DB::table($table)->orderBy('id')->each(function ($row) use ($table) {
                DB::table($table)
                    ->where('id', $row->id)
                    ->update(['ulid_id' => (string) \Illuminate\Support\Str::ulid()]);
            });

            // Drop old PK, rename, set as new PK
            Schema::table($table, function (Blueprint $t) {
                $t->dropPrimary();
            });
            Schema::table($table, function (Blueprint $t) {
                $t->dropColumn('id');
            });
            Schema::table($table, function (Blueprint $t) {
                $t->renameColumn('ulid_id', 'id');
            });
            Schema::table($table, function (Blueprint $t) {
                $t->char('id', 26)->primary()->change();
            });
        }

        // Step 3: Convert organizations table (already VARCHAR → CHAR(26))
        if (Schema::hasTable('organizations')) {
            Schema::table('organizations', function (Blueprint $t) {
                $t->char('id', 26)->change();
            });
        }

        // Step 4: Convert foreign key columns to CHAR(26)
        foreach ($this->foreignKeys as $table => $columns) {
            if (!Schema::hasTable($table)) continue;
            Schema::table($table, function (Blueprint $t) use ($columns) {
                foreach ($columns as $column => $refTable) {
                    $t->char($column, 26)->nullable()->change();
                }
            });
        }

        // Step 5: Re-add foreign key constraints
        foreach ($this->foreignKeys as $table => $columns) {
            if (!Schema::hasTable($table)) continue;
            Schema::table($table, function (Blueprint $t) use ($columns) {
                foreach ($columns as $column => $refTable) {
                    try {
                        $t->foreign($column)->references('id')->on($refTable)->nullOnDelete();
                    } catch (\Exception $e) {
                        // Skip if referenced table doesn't exist
                    }
                }
            });
        }
    }
};

class Domain extends Model
{
    protected $fillable = ['name', 'organization_id'];
}

use Illuminate\Database\Eloquent\Concerns\HasUlids;

class Domain extends Model
{
    use HasUlids;

    protected $fillable = ['name', 'organization_id'];

    // Laravel's HasUlids trait automatically:
    // - Generates ULID on creation
    // - Sets $keyType = 'string'
    // - Sets $incrementing = false
}

// routes/api.php — No change needed!
// Laravel's HasUlids trait handles route model binding automatically.
// Routes like /api/domains/{domain} will resolve ULID strings.

// Before
Route::get('/domains/{domain}', ...)->where('domain', '[0-9]+');

// After — remove the constraint (ULIDs are alphanumeric)
Route::get('/domains/{domain}', ...);

// Before (INT)
class DomainResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            'id' => $this->id,  // was: 42
            ...
        ];
    }
}

// After (ULID) — no code change needed!
// $this->id now returns "01H5YGKB3ZJR4DPNZN9SMTHF4R" instead of 42

// In EmailServerClient:
$result = $emailServer->createDomain('example.com', $org->id);
$domain->update([
    'email_server_domain_id' => $result['data']['id'],  // ULID from email server
]);