irdb/api/db/migrations/20260504100000_add_unique_local_user_index.php

<?php

declare(strict_types=1);

use App\Infrastructure\Db\Migrations\BaseMigration;

/**
 * Defense-in-depth for SEC_REVIEW F3: enforce at the DB layer that there
 * is at most one row in `users` with `is_local = 1`. SPEC §6/§8 says the
 * local admin is a single record whose password lives in the UI's env;
 * `UserRepository::upsertLocal` already enforces this in code, but the
 * DB-level index makes regressions fail loud.
 *
 * Implementation:
 *  - SQLite: native partial unique index `WHERE is_local = 1`.
 *  - MySQL 8: functional unique index over a CASE expression that yields
 *    `1` for local rows and `NULL` for non-local rows. Multi-NULL is
 *    permitted in unique indexes on both engines.
 *
 * If a deployment already has multiple `is_local = 1` rows (either from
 * the F3 exploit window or from operators legitimately renaming the
 * local admin via the pre-fix code path), this migration will fail. That
 * is the desired signal: the operator must inspect those rows manually
 * and consolidate before applying the index.
 */
final class AddUniqueLocalUserIndex extends BaseMigration
{
    private const INDEX_NAME = 'uniq_users_one_local';

    public function up(): void
    {
        if ($this->isMysql()) {
            $this->execute(
                'CREATE UNIQUE INDEX ' . self::INDEX_NAME . ' '
                . 'ON users ((CASE WHEN is_local = 1 THEN 1 ELSE NULL END))'
            );

            return;
        }

        $this->execute(
            'CREATE UNIQUE INDEX ' . self::INDEX_NAME . ' '
            . 'ON users (is_local) WHERE is_local = 1'
        );
    }

    public function down(): void
    {
        if ($this->isMysql()) {
            $this->execute('DROP INDEX ' . self::INDEX_NAME . ' ON users');

            return;
        }

        $this->execute('DROP INDEX IF EXISTS ' . self::INDEX_NAME);
    }
}