SkinbaseNova/.copilot/legacy_users_migration.md

SkinBase – Legacy Users Migration to Laravel Auth (Authoritative Spec)

This document is the single source of truth for migrating users from the legacy SkinBase database (projekti_old_skinbase) into the new Laravel (SkinBase 2026) application using modern authentication.

It covers:

• ✅ Target data model (Laravel-friendly)
• ✅ What to keep / what to drop from legacy
• ✅ Exact Laravel migrations
• ✅ Import SQL + migration order
• ✅ Old password compatibility (if possible)
• ✅ First-login password reset flow (recommended)
• ✅ Validation SQL & sanity checks
• ✅ Role-based access control (RBAC)

Copilot AI Agent instructions:
Follow this document strictly. Do not invent additional fields/tables unless explicitly allowed here.

---

0) Context: Legacy tables (what we’re migrating)

Legacy schema relevant for users:

users (MyISAM, mixed responsibilities)

• user_id (PK)
• uname (username)
• password (varchar 80) – legacy hash or plaintext (unknown)
• password2 (varchar 255) – sometimes present
• email
• real_name
• web
• birth, gender, country, country_code, lang
• picture, cover_art
• signature, about_me, description
• LastVisit, joinDate
• user_type (membership / level)
• active, authorized
• many legacy preferences and obsolete fields (ICQ etc.)

users_data (mostly duplicate/overlap)

This is redundant and will NOT be kept as-is.

users_statistics

Useful but not auth-related; will migrate to user_statistics.

users_types

Legacy user “levels”. We’ll map to modern roles.

---

1) Migration Goals

Authentication goals

• Use Laravel default authentication (Breeze/Fortify/Jetstream-compatible).
• Allow login via:
  • username OR email
• Preserve user accounts with minimal friction.
• Handle legacy password format safely:
  • Prefer secure migration with password reset
  • Optionally support legacy hash verification if algorithm is known

Data goals

• Keep IDs stable where reasonable (user_id → users.id) to simplify future migrations.
• Move non-auth profile data into a dedicated profile table.
• Remove obsolete fields (ICQ etc.) and replace with modern social links.

Security goals

• Do not store weak hashes long-term.
• If legacy password verification is implemented, rehash to bcrypt/argon immediately upon successful login.
• Default to forcing password reset if legacy hash format is unknown.

---

2) Target Database Design (New System)

2.1 users (Auth + identity only)

Keep it clean. This table should contain only identity/auth/security-critical fields.

Fields:

• id (BIGINT)
• username (unique)
• name
• email (unique)
• password (bcrypt/argon hash)
• email_verified_at
• remember_token
• is_active (legacy active)
• needs_password_reset (new)
• role (simple RBAC) OR use roles table/spatie later
• timestamps

2.2 user_profiles (Profile data)

• bio/about, avatar, cover image
• country + language + birthdate + gender
• website
• timestamps

2.3 user_social_links (modern social replacement)

Instead of ICQ, store dynamic social platforms:

• github, twitter/x, instagram, youtube, discord, website, etc.

2.4 user_statistics (optional but useful)

Migrated from legacy users_statistics.

---

3) What to Remove / Replace

Remove (obsolete / not used / legacy UI junk)

• icq (obsolete)
• zone
• numboard, NumStats, numskin, section_style
• menu
• eicon
• mlist
• various “board/menu” preferences that no longer exist

Keep / migrate

• username, email, name
• last visit (optional)
• active/authorized → is_active + email_verified_at strategy
• about/bio
• avatar/cover
• country/language/gender/birthdate
• website
• statistics (optional)

Replace ICQ with social links

Add user_social_links table.

---

4) Role Mapping (Legacy users_types → Modern RBAC)

Legacy:

• users.user_type references users_types.id

New (simple approach):

• store a string role directly in users.role:
  • user
  • moderator
  • admin

Mapping recommendation (adjust if your legacy meaning differs):

• user_type <= 0 → user
• user_type in [1..X] with “moderator” meaning → moderator
• special admin IDs → admin

If you later need granular permissions, adopt spatie/laravel-permission. For now, keep it simple.

---

5) Exact Laravel Migrations (Copy/Paste)

These migrations are authoritative. Put them in database/migrations/ in this order.

5.1 Create/extend users table

If you already have Laravel’s default users migration, create a new migration to modify it.

Migration: 2026_02_01_000010_update_users_table_for_skinbase.php

<?php

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

return new class extends Migration {
    public function up(): void
    {
        Schema::table('users', function (Blueprint $table) {
            // Ensure big integer id is used in your app; Laravel default is bigIncrements already.
            // Add username for legacy uname
            if (!Schema::hasColumn('users', 'username')) {
                $table->string('username', 80)->nullable()->unique()->after('id');
            }

            // If name exists, keep it. Ensure nullable for legacy.
            if (!Schema::hasColumn('users', 'name')) {
                $table->string('name')->nullable();
            } else {
                $table->string('name')->nullable()->change();
            }

            // Email is important; legacy might have duplicates/NULLs -> handle in import script carefully.
            if (Schema::hasColumn('users', 'email')) {
                $table->string('email')->nullable()->change();
            }

            if (!Schema::hasColumn('users', 'is_active')) {
                $table->boolean('is_active')->default(true)->after('remember_token');
            }

            if (!Schema::hasColumn('users', 'needs_password_reset')) {
                $table->boolean('needs_password_reset')->default(true)->after('is_active');
            }

            if (!Schema::hasColumn('users', 'role')) {
                $table->string('role', 32)->default('user')->after('needs_password_reset');
            }

            // Optional: store legacy hash algorithm marker (only if doing compat)
            if (!Schema::hasColumn('users', 'legacy_password_algo')) {
                $table->string('legacy_password_algo', 32)->nullable()->after('role');
            }

            // Optional: store legacy last visit
            if (!Schema::hasColumn('users', 'last_visit_at')) {
                $table->timestamp('last_visit_at')->nullable()->after('legacy_password_algo');
            }
        });
    }

    public function down(): void
    {
        Schema::table('users', function (Blueprint $table) {
            if (Schema::hasColumn('users', 'username')) $table->dropColumn('username');
            if (Schema::hasColumn('users', 'is_active')) $table->dropColumn('is_active');
            if (Schema::hasColumn('users', 'needs_password_reset')) $table->dropColumn('needs_password_reset');
            if (Schema::hasColumn('users', 'role')) $table->dropColumn('role');
            if (Schema::hasColumn('users', 'legacy_password_algo')) $table->dropColumn('legacy_password_algo');
            if (Schema::hasColumn('users', 'last_visit_at')) $table->dropColumn('last_visit_at');
        });
    }
};

---

5.2 Create user_profiles

Migration: 2026_02_01_000020_create_user_profiles_table.php

<?php

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

return new class extends Migration {
    public function up(): void
    {
        Schema::create('user_profiles', function (Blueprint $table) {
            $table->unsignedBigInteger('user_id')->primary();

            $table->text('bio')->nullable();
            $table->string('avatar', 255)->nullable();
            $table->string('cover_image', 255)->nullable();

            $table->string('country', 80)->nullable();
            $table->char('country_code', 2)->nullable(); // normalize to ISO-3166-1 alpha-2
            $table->string('language', 10)->nullable();

            $table->date('birthdate')->nullable();
            $table->enum('gender', ['M','F','X'])->default('X');

            $table->string('website', 255)->nullable();

            $table->timestamps();

            $table->foreign('user_id')->references('id')->on('users')->cascadeOnDelete();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('user_profiles');
    }
};

---

5.3 Create user_social_links

Migration: 2026_02_01_000030_create_user_social_links_table.php

<?php

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

return new class extends Migration {
    public function up(): void
    {
        Schema::create('user_social_links', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('user_id');

            $table->string('platform', 32); // e.g. github, twitter, instagram, youtube, discord, website
            $table->string('url', 255);

            $table->timestamps();

            $table->unique(['user_id', 'platform']);
            $table->foreign('user_id')->references('id')->on('users')->cascadeOnDelete();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('user_social_links');
    }
};

---

5.4 Create user_statistics (optional)

Migration: 2026_02_01_000040_create_user_statistics_table.php

<?php

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

return new class extends Migration {
    public function up(): void
    {
        Schema::create('user_statistics', function (Blueprint $table) {
            $table->unsignedBigInteger('user_id')->primary();

            $table->unsignedInteger('uploads')->default(0);
            $table->unsignedInteger('downloads')->default(0);
            $table->unsignedInteger('pageviews')->default(0);
            $table->unsignedInteger('awards')->default(0);

            $table->timestamps();

            $table->foreign('user_id')->references('id')->on('users')->cascadeOnDelete();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('user_statistics');
    }
};

---

6) Auth Logic: Old Password Compatibility (Two Approaches)

Approach A (Recommended): Force Reset for Everyone

Safest, because legacy hashing is unknown.

• Import users with a random password hash (or keep legacy hash in password temporarily but do not allow it).
• Set needs_password_reset = 1
• On login attempt, require reset.

This avoids accepting weak hashes forever.

Approach B (Optional): Support legacy hash on first login, then rehash

Only do this if you can identify the algorithm used in legacy password / password2.

Common old hashes:

• MD5: 32 hex chars
• SHA1: 40 hex chars
• bcrypt: starts with $2y$ or $2a$
• phpBB style or custom salts: unknown

Detection hints (informational)

• If legacy password length is 32 and is hex → probably MD5
• If length is 40 hex → probably SHA1
• If starts with $2y$ → already bcrypt

Action requirement: Before implementing compat, confirm by checking a known user password against a sample hash.

---

7) Implementation: Login via Username OR Email

7.1 Breeze/Fortify login request logic

In your login controller (or Fortify authentication callback), accept a single input field:

• login (username or email)
• password

Example lookup:

$user = User::query()
    ->where('email', $login)
    ->orWhere('username', $login)
    ->first();

---

8) Implementation: Legacy Password Compatibility (If enabled)

8.1 Add a service: app/Support/LegacyPassword.php

<?php

namespace App\Support;

class LegacyPassword
{
    public static function detectAlgo(?string $hash): ?string
    {
        if (!$hash) return null;

        if (str_starts_with($hash, '$2y$') || str_starts_with($hash, '$2a$')) return 'bcrypt';
        if (preg_match('/^[a-f0-9]{32}$/i', $hash)) return 'md5';
        if (preg_match('/^[a-f0-9]{40}$/i', $hash)) return 'sha1';

        return null; // unknown
    }

    public static function verify(string $plain, string $legacyHash, string $algo): bool
    {
        return match ($algo) {
            'md5'  => md5($plain) === $legacyHash,
            'sha1' => sha1($plain) === $legacyHash,
            'bcrypt' => password_verify($plain, $legacyHash),
            default => false
        };
    }
}

8.2 Modify authentication (pseudo-code)

On login:

1. Try normal Laravel hash check (Hash::check)

2. If fails AND legacy_password_algo is present (or detected), try legacy verify

3. If legacy verify passes:

   • set new password using Hash::make($plain)
   • set needs_password_reset = 0
   • clear legacy_password_algo

Example snippet:

use Illuminate\Support\Facades\Hash;
use App\Support\LegacyPassword;

if (Hash::check($password, $user->password)) {
    // ok
} else {
    $algo = $user->legacy_password_algo ?: LegacyPassword::detectAlgo($user->password);
    if ($algo && LegacyPassword::verify($password, $user->password, $algo)) {
        $user->password = Hash::make($password);
        $user->needs_password_reset = false;
        $user->legacy_password_algo = null;
        $user->save();
    } else {
        // invalid credentials
    }
}

If detectAlgo() returns null, do NOT allow login: require password reset via email.

---

9) First-login Password Reset Flow (Recommended & Secure)

Requirements

• If needs_password_reset = 1, user must reset password before accessing account.
• This can be enforced via middleware.

9.1 Middleware: EnsurePasswordResetCompleted

Create: app/Http/Middleware/EnsurePasswordResetCompleted.php

<?php

namespace App\Http\Middleware;

use Closure;
use Illuminate\Http\Request;

class EnsurePasswordResetCompleted
{
    public function handle(Request $request, Closure $next)
    {
        $user = $request->user();

        if ($user && $user->needs_password_reset) {
            if (!$request->routeIs('password.reset.*')) {
                return redirect()->route('password.reset.notice');
            }
        }

        return $next($request);
    }
}

9.2 Routes for reset notice & flow

Add routes:

• password.reset.notice → show "You must reset your password"
• Use Laravel’s standard password reset email flow

9.3 UX recommendation

• When migrating, send optional mass email campaign:

  • “SkinBase upgraded – set your new password”

• But don’t require sending all at once; users can request reset when needed.

---

10) Migration / Import Process (Recommended Order)

Step 1: Ensure old DB is accessible

Options:

• Import old DB into same MySQL server
• Or create a read-only connection in Laravel (config/database.php) to projekti_old_skinbase

Step 2: Run migrations for new schema

php artisan migrate

Step 3: Import users

We keep user_id as users.id to preserve identity mapping.

---

11) Import SQL (Base migration)

These SQL examples assume both databases are on the same MySQL server. Adjust database names as needed.

11.1 Insert into users

Important rules:

• Some users may have NULL/duplicate emails → handle safely.
• Username should be unique. If duplicates exist, add suffix.

Recommended initial import (conservative):

• Keep legacy hash in password temporarily
• Mark needs_password_reset = 1
• Set legacy_password_algo if detectable

INSERT INTO users (id, username, name, email, password, is_active, needs_password_reset, role, legacy_password_algo, last_visit_at, created_at, updated_at)
SELECT
    u.user_id AS id,
    NULLIF(u.uname, '') AS username,
    NULLIF(u.real_name, '') AS name,
    NULLIF(u.email, '') AS email,
    COALESCE(NULLIF(u.password2, ''), NULLIF(u.password, ''), '') AS password,
    CASE WHEN u.active = 1 THEN 1 ELSE 0 END AS is_active,
    1 AS needs_password_reset,
    'user' AS role,
    NULL AS legacy_password_algo,
    u.LastVisit AS last_visit_at,
    u.joinDate AS created_at,
    NOW() AS updated_at
FROM projekti_old_skinbase.users u;

After import, you can populate legacy_password_algo using detection rules if you want compat.

Example:

UPDATE users
SET legacy_password_algo =
  CASE
    WHEN password LIKE '$2y$%' THEN 'bcrypt'
    WHEN password REGEXP '^[a-f0-9]{32}$' THEN 'md5'
    WHEN password REGEXP '^[a-f0-9]{40}$' THEN 'sha1'
    ELSE NULL
  END
WHERE legacy_password_algo IS NULL;

---

11.2 Insert into user_profiles

INSERT INTO user_profiles (user_id, bio, avatar, cover_image, country, country_code, language, birthdate, gender, website, created_at, updated_at)
SELECT
    u.user_id,
    NULLIF(u.about_me, '') AS bio,
    NULLIF(u.picture, '') AS avatar,
    NULLIF(u.cover_art, '') AS cover_image,
    NULLIF(u.country, '') AS country,
    NULLIF(LEFT(u.country_code, 2), '') AS country_code,
    NULLIF(u.lang, '') AS language,
    u.birth AS birthdate,
    COALESCE(u.gender, 'X') AS gender,
    NULLIF(u.web, '') AS website,
    NOW(),
    NOW()
FROM projekti_old_skinbase.users u
WHERE u.user_id IS NOT NULL;

---

11.3 Social links (only website initially)

Optionally insert website into social links (if you prefer everything in one place):

INSERT INTO user_social_links (user_id, platform, url, created_at, updated_at)
SELECT
    u.user_id,
    'website',
    u.web,
    NOW(),
    NOW()
FROM projekti_old_skinbase.users u
WHERE u.web IS NOT NULL AND u.web <> '';

---

11.4 Statistics

INSERT INTO user_statistics (user_id, uploads, downloads, pageviews, awards, created_at, updated_at)
SELECT
    s.user_id,
    s.uploads,
    s.downloads,
    s.pageviews,
    s.awards,
    NOW(),
    NOW()
FROM projekti_old_skinbase.users_statistics s;

---

12) Migration Validation SQL (Sanity Checks)

12.1 Count parity

SELECT
  (SELECT COUNT(*) FROM projekti_old_skinbase.users) AS old_users,
  (SELECT COUNT(*) FROM users) AS new_users;

12.2 Missing usernames

SELECT id, email
FROM users
WHERE username IS NULL OR username = '';

12.3 Duplicate usernames

SELECT username, COUNT(*) c
FROM users
WHERE username IS NOT NULL AND username <> ''
GROUP BY username
HAVING c > 1;

12.4 Duplicate emails

SELECT email, COUNT(*) c
FROM users
WHERE email IS NOT NULL AND email <> ''
GROUP BY email
HAVING c > 1;

12.5 Orphaned profiles

SELECT p.user_id
FROM user_profiles p
LEFT JOIN users u ON u.id = p.user_id
WHERE u.id IS NULL;

12.6 Users inactive / unauthorized review

Legacy had authorized. If you want to incorporate:

SELECT user_id, active, authorized
FROM projekti_old_skinbase.users
WHERE active = 0 OR authorized = 0
LIMIT 200;

---

13) Role-based Access Control (RBAC)

Option 1 (Simple, recommended now): string role on users

• users.role = user|moderator|admin

• Add middleware checks:

  • admin-only panels
  • moderator actions (approve uploads, etc.)

Example middleware:

public function handle($request, Closure $next, string $role)
{
    $user = $request->user();
    if (!$user || $user->role !== $role) abort(403);
    return $next($request);
}

Option 2 (Advanced later): spatie/laravel-permission

Adopt if you need granular permissions:

• approve_artwork
• ban_user
• edit_categories
• etc.

Not required for v1 migration.

---

14) Implementation Notes (Important)

Email issues

Legacy allows NULL/duplicate emails. Laravel password reset requires unique emails. Strategy:

• If email missing: user must login with username and request support or add email.
• If duplicate emails: resolve manually or append +id style (not recommended) or enforce unique by cleanup.

Username issues

If duplicates exist, your import must resolve them. Recommended rule:

• if username duplicate, append -<id>

MyISAM note

Legacy tables are MyISAM; importing into InnoDB is fine. Do not try to preserve MyISAM.

---

15) Recommended Laravel Auth Starter Kit

Use Laravel Breeze for simplest modern auth:

• login/register
• password resets
• email verification (optional)

Then customize:

• login field: username OR email
• middleware to enforce password reset

---

16) Deliverables Checklist (What Copilot must implement)

1. ✅ Migrations (sections 5.1–5.4)
2. ✅ Import strategy + SQL (section 11)
3. ✅ Validation SQL queries (section 12)
4. ✅ Login supports username/email (section 7)
5. ✅ Password reset enforcement (section 9)
6. ✅ Optional legacy password compatibility (section 8)
7. ✅ RBAC (section 13 option 1)

---

17) Final Security Policy

• Default needs_password_reset = 1 for all migrated users.

• If legacy hash compatibility is used:

  • accept legacy hash only once
  • rehash immediately to Laravel hash
  • clear legacy markers

• Do not keep MD5/SHA1 hashes long-term.

---

END OF DOCUMENT