Migrate classic Spark tables to Jetstream + new Spark

2021_02_17_103523_migrate_to_jetstream.php

<?php

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

class MigrateToJetstream extends Migration
{
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->text('profile_photo_path')->nullable();
            $table->text('two_factor_secret')
                ->after('password')
                ->nullable();

            $table->text('two_factor_recovery_codes')
                ->after('two_factor_secret')
                ->nullable();
        });

        Schema::table('teams', function (Blueprint $table) {
            $table->renameColumn('owner_id', 'user_id');
        });

        Schema::table('teams', function (Blueprint $table) {
            $table->boolean('personal_team')->default(false);
        });

        Schema::create('team_invitations', function (Blueprint $table) {
            $table->id();
            $table->foreignId('team_id');
            $table->string('email');
            $table->string('role')->nullable();
            $table->timestamps();

            $table->unique(['team_id', 'email']);
        });

        Schema::create('sessions', function (Blueprint $table) {
            $table->string('id')->primary();
            $table->foreignId('user_id')->nullable()->index();
            $table->string('ip_address', 45)->nullable();
            $table->text('user_agent')->nullable();
            $table->text('payload');
            $table->integer('last_activity')->index();
        });

        Schema::create('team_user', function (Blueprint $table) {
            $table->id();
            $table->foreignId('team_id');
            $table->foreignId('user_id');
            $table->string('role')->nullable();
            $table->timestamps();

            $table->unique(['team_id', 'user_id']);
        });

        Schema::create('personal_access_tokens', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->morphs('tokenable');
            $table->string('name');
            $table->string('token', 64)->unique();
            $table->text('abilities')->nullable();
            $table->timestamp('last_used_at')->nullable();
            $table->timestamps();
        });
    }
}

2021_02_17_103524_migrate_team_user_data.php

<?php

use App\Domain\Team\Models\Team;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\Schema;

class MigrateTeamUserData extends Migration
{
    public function up()
    {
        DB::table('team_users')
            ->get()
            ->each(function (stdClass $teamUser) {
                $teamUser = get_object_vars($teamUser);

                $teamUser['role'] === 'owner'
                    ? $this->createOwnerOrAdmin($teamUser)
                    : $this->createMember($teamUser);
            });

        Schema::drop('team_users');
    }

    protected function createOwnerOrAdmin(array $teamUser): void
    {
        $team = Team::find($teamUser['team_id']);

        if (! $team) {
            echo 'did not find team ' . $teamUser['team_id'] . PHP_EOL;

            return;
        }

        if (is_null($team->user_id)) {
            $team->update(['user_id' => $teamUser['user_id']]);

            return;
        }

        if ($team->user_id === $teamUser['user_id']) {
            return;
        }

        DB::table('team_user')->insert([
            'team_id' => $teamUser['team_id'],
            'user_id' => $teamUser['user_id'],
            'role' => 'admin',
        ]);
    }

    protected function createMember(array $teamUser)
    {
        DB::table('team_user')->insert([
            'team_id' => $teamUser['team_id'],
            'user_id' => $teamUser['user_id'],
            'role' => 'member',
        ]);
    }
}

2021_02_17_135051_migrate_to_next_gen_spark.php

<?php

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

class MigrateToNextGenSpark extends Migration
{
    public function up()
    {
        $this
            ->createNewTables()
            ->dropUnusedTables()
            ->createSubscriptionTables()
            ->updateTeamsTable()
            ->dropOldColumnsFromUsersTable()
            ->dropOldColumnsFromInvoicesTable();
    }

    protected function dropUnusedTables(): self
    {
        Schema::drop('action_events');
        Schema::drop('announcements');

        return $this;
    }

    protected function createSubscriptionTables(): self
    {
        Schema::drop('subscriptions');

        Schema::create('subscriptions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('team_id');
            $table->string('name');
            $table->string('stripe_id');
            $table->string('stripe_status');
            $table->string('stripe_plan')->nullable();
            $table->integer('quantity')->nullable();
            $table->timestamp('trial_ends_at')->nullable();
            $table->timestamp('ends_at')->nullable();
            $table->timestamps();

            $table->index(['team_id', 'stripe_status']);
        });

        Schema::drop('subscription_items');
        Schema::create('subscription_items', function (Blueprint $table) {
            $table->id();
            $table->foreignId('subscription_id');
            $table->string('stripe_id')->index();
            $table->string('stripe_plan');
            $table->integer('quantity');
            $table->timestamps();

            $table->unique(['subscription_id', 'stripe_plan']);
        });

        return $this;
    }

    protected function createNewTables(): self
    {
        Schema::create('receipts', function (Blueprint $table) {
            $table->id();
            $table->foreignId('team_id')->index();
            $table->string('provider_id')->index();
            $table->string('amount');
            $table->string('tax');
            $table->timestamp('paid_at');
            $table->timestamps();
        });

        Schema::create('tax_rates', function (Blueprint $table) {
            $table->id();
            $table->string('stripe_id')->index();
            $table->double('percentage')->index();
            $table->timestamps();
        });

        return $this;
    }

    protected function updateTeamsTable(): self
    {
        Schema::table('teams', function (Blueprint $table) {
            $table->renameColumn('billing_zip', 'billing_postal_code');
        });

        Schema::table('teams', function (Blueprint $table) {
            $table->string('card_expiration')->nullable();
        });

        return $this;
    }

    protected function dropOldColumnsFromUsersTable(): self
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn([
                'current_billing_plan',
                'card_brand',
                'card_last_four',
                'card_country',
                'billing_address',
                'billing_address_line_2',
                'billing_city',
                'billing_state',
                'billing_zip',
                'billing_country',
                'vat_id',
                'extra_billing_information',
                'trial_ends_at',
                'last_read_announcements_at',
                'announcements_sent',
                'referral_token',
                'stripe_id',
                'phone',
            ]);
        });

        return $this;
    }

    protected function dropOldColumnsFromInvoicesTable(): self
    {
        Schema::table('invoices', function (Blueprint $table) {
            $table->dropColumn('user_id');
        });

        return $this;
    }
}

2021_02_22_183842_migrate_subscription_data.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Schema;

class MigrateSubscriptionData extends Migration
{
    public function up()
    {
        $this
            ->migrateSubscriptionData()
            ->dropOldSubscriptionTables();
    }

    protected function migrateSubscriptionData(): self
    {
        DB::table('team_subscriptions')
            ->orderBy('id')
            ->each(function (stdClass $teamSubscriptionData) {
                $teamSubscriptionData = get_object_vars($teamSubscriptionData);

                DB::table('subscriptions')->insert($teamSubscriptionData);
            });


        DB::table('team_subscription_items')
            ->orderBy('id')
            ->each(function (stdClass $teamSubscriptionItemData) {
                $teamSubscriptionItemData = get_object_vars($teamSubscriptionItemData);

                DB::table('subscriptions_items')->insert($teamSubscriptionItemData);
            });

        return $this;
    }

    protected function dropOldSubscriptionTables(): self
    {
        Schema::drop('team_subscriptions');
        Schema::drop('team_subscription_items');

        return $this;
    }
}

2021_02_23_201722_migrate_api_tokens_data.php

<?php

use App\Domain\Team\Models\User;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Schema;

class MigrateApiTokensData extends Migration
{
    public function up()
    {
        DB::table('api_tokens')
            ->orderBy('id')
            ->chunk(100, function (Collection $apiTokens) {
                $apiTokens->each(function (stdClass $apiToken) {
                    $apiToken = get_object_vars($apiToken);


                    $user = User::find($apiToken['user_id']);

                    if (! $user) {
                        echo "Did not find a user for API token id `{$apiToken['id']}`";
                    }

                    $user->tokens()->create([
                        'name' => $apiToken['name'],
                        'token' => hash('sha256', $apiToken['token']),
                        'abilities' => ['*'],
                    ]);
                });
            });

        Schema::drop('api_tokens');
    }
}

DB::table('team_subscriptions')
    ->orderBy('id')
    ->each(function (stdClass $teamSubscriptionData) {
        $teamSubscriptionData = get_object_vars($teamSubscriptionData);

        DB::table('subscriptions')->insert($teamSubscriptionData);
    }, 100);

If you just want to clone a table:

INSERT subscription_items
SELECT * FROM team_subscriptions;

Thanks for the tips!

https://gist.github.com/freekmurze/dbfa1fe19818b0f602a31b5df93bb3f3#file-2021_02_22_183842_migrate_subscription_data-php-L32

Should be subscription_items (no plural on subscription)

https://gist.github.com/freekmurze/dbfa1fe19818b0f602a31b5df93bb3f3#file-2021_02_23_201722_migrate_api_tokens_data-php-L21

Should be if (! $user) and the actual token creation a few lines below should be within the else part.

Thanks for all the migration files - helped us a lot!!

@Manuel-Naix thanks for reporting, fixed!