irdb/api/db/migrations/20260429100000_add_ip_text_indexes.php

<?php

declare(strict_types=1);

use App\Infrastructure\Db\Migrations\BaseMigration;

/**
 * SPEC §M09.5: index `ip_text` so the admin IP search ("`q=` substring or
 * prefix match") doesn't fall back to a full-table scan.
 *
 * `ip_scores.ip_text` is the primary search column (the search results are
 * grouped by IP). `reports.ip_text` is denormalised alongside `ip_bin`
 * but the search joins by `ip_bin`, so we don't add an index there.
 *
 * `LIKE 'prefix%'` uses the index on default-collation columns on both
 * SQLite and MySQL; `LIKE '%substr%'` falls back to a scan, which is
 * acceptable at the dataset sizes we expect (the search caps at 200
 * rows per page).
 */
final class AddIpTextIndexes extends BaseMigration
{
    public function up(): void
    {
        $this->table('ip_scores')
            ->addIndex(['ip_text'], ['name' => 'idx_ip_scores_ip_text'])
            ->update();
    }

    public function down(): void
    {
        $this->table('ip_scores')
            ->removeIndexByName('idx_ip_scores_ip_text')
            ->update();
    }
}