PackIt/migrations/001_hardening.sql

-- Migration 001 – brute-force throttling + collision-safe short links.
-- Run once against an existing PackIt database:
--     mysql -u <user> -p <your-database> < migrations/001_hardening.sql
-- New installs get all of this from schema.sql and can skip it.

-- 1. Failed-login log backing the throttle in admin/login.php.
CREATE TABLE IF NOT EXISTS login_attempts (
    attemptID    INT AUTO_INCREMENT PRIMARY KEY,
    ip           VARCHAR(45) NOT NULL,
    username     VARCHAR(50) NOT NULL,
    attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip_time   (ip, attempted_at),
    INDEX idx_user_time (username, attempted_at)
);

-- 2. Short links.
--
-- Rows whose hash is longer than 16 characters are leftovers from the original
-- 64-character scheme. packing.php has rejected those links since the switch to
-- 5-character hashes, so they are already dead; deleting them lets the column
-- shrink. The table is only a cache of selection -> hash: any such link is
-- recreated by re-picking the activities.
DELETE FROM selection_hashes WHERE CHAR_LENGTH(hash) > 16;

ALTER TABLE selection_hashes
    MODIFY hash         VARCHAR(16)  NOT NULL,
    MODIFY activity_ids VARCHAR(500) NOT NULL;

-- Every new list looks its selection up by activity_ids; without this it is a
-- full table scan.
ALTER TABLE selection_hashes ADD INDEX idx_activity_ids (activity_ids);