Repository: louislam/uptime-kuma
File: db/knex_migrations/2025-12-22-0121-optimize-important-indexes.js (JavaScript)

Code before:
            // Create partial indexes with predicate
            return knex.schema.alterTable("heartbeat", function (table) {
                table.index([ "monitor_id", "time" ], "monitor_important_time_index", {
                    predicate: knex.whereRaw("important = 1")
                });
                table.index([ "important" ], "important", {
                    predicate: knex.whereRaw("important = 1")
                });
            });
        });
    } else {
        // For MariaDB/MySQL: No changes (partial indexes not supported)
        return Promise.resolve();
    }
};

exports.down = function (knex) {
    const isSQLite = knex.client.dialect === "sqlite3";

    if (isSQLite) {
        // Restore original indexes
        return knex.schema.alterTable("heartbeat", function (table) {
            table.dropIndex([ "monitor_id", "time" ], "monitor_important_time_index");
        }).then(() => {
            // Drop the partial important index
            return knex.raw("DROP INDEX IF EXISTS important");
        }).then(() => {
            // Recreate original indexes
            return knex.schema.alterTable("heartbeat", function (table) {
                table.index([ "monitor_id", "important", "time" ], "monitor_important_time_index");
                table.index([ "important" ], "important");
            });
        });
    } else {
        // For MariaDB/MySQL: No changes
        return Promise.resolve();
    }
};


Review comment (suggestion):
@copilot 

make sure to use dropIndex instead of knex.raw("DROP INDEX IF EXISTS important");
Also merge this into one `alterTable` unless if this means that it does not pass the migration test

Code after:
exports.up = async function (knex) {
    const isSQLite = knex.client.dialect === "sqlite3";

    if (isSQLite) {
        // For SQLite: Use partial indexes with WHERE important = 1
        // Drop existing indexes using IF EXISTS
        await knex.raw("DROP INDEX IF EXISTS monitor_important_time_index");
        await knex.raw("DROP INDEX IF EXISTS heartbeat_important_index");

        // Create partial indexes with predicate
        await knex.schema.alterTable("heartbeat", function (table) {
            table.index([ "monitor_id", "time" ], "monitor_important_time_index", {
                predicate: knex.whereRaw("important = 1")
            });
            table.index([ "important" ], "heartbeat_important_index", {
                predicate: knex.whereRaw("important = 1")
            });
        });
    }
    // For MariaDB/MySQL: No changes (partial indexes not supported)
};

exports.down = async function (knex) {
    const isSQLite = knex.client.dialect === "sqlite3";

    if (isSQLite) {
        // Restore original indexes
        await knex.raw("DROP INDEX IF EXISTS monitor_important_time_index");
        await knex.raw("DROP INDEX IF EXISTS heartbeat_important_index");

        await knex.schema.alterTable("heartbeat", function (table) {
            table.index([ "monitor_id", "important", "time" ], "monitor_important_time_index");
            table.index([ "important" ]);
        });
    }
    // For MariaDB/MySQL: No changes
};