irdb/api/db/migrations/20260505110000_add_user_id_to_api_tokens.php

<?php

declare(strict_types=1);

use App\Infrastructure\Db\Migrations\BaseMigration;

/**
 * SEC_REVIEW F16: bind admin-kind api_tokens to the issuing user.
 *
 * The original schema carries `kind` and (for admin tokens) `role`, but no
 * issuer attribution. If the admin who minted a token is later disabled,
 * demoted, or removed, the token continues to grant its bound role until
 * an operator manually revokes it.
 *
 * Adds a nullable `user_id` column. The MySQL FK uses `ON DELETE CASCADE`:
 * a hard-deleted user takes their issued admin tokens with them. SET NULL
 * was rejected because reverting a previously-bound row to a NULL user_id
 * lets the token re-enter the grandfathered path that
 * `TokenAuthenticationMiddleware` allows for legacy / console-issued
 * tokens — defeating the F16 defense. On SQLite this migration cannot add
 * the FK via ALTER TABLE, so deletion-time enforcement falls back to the
 * application layer: when the issuer's row no longer exists,
 * `UserRepository::findById` returns null and the middleware refuses the
 * token. The asymmetry is acceptable because the system has no API-level
 * user-deletion path; both drivers behave identically for the actual
 * offboarding paths (disable + role demote).
 *
 * The column is nullable for two reasons:
 *  1. Reporter / consumer / service tokens are not user-scoped.
 *  2. Admin tokens minted before this migration (and tokens minted via
 *     `bin/console tokens:create`, which has no logged-in user) carry NULL
 *     and are grandfathered — `TokenAuthenticationMiddleware` only
 *     enforces user-state checks when `user_id` is non-null. Operators who
 *     want strict binding rotate their pre-existing admin tokens after
 *     deploying this change.
 *
 * Index on `user_id` so the admin "tokens" page can resolve issuer labels
 * without a per-row N+1 query.
 */
final class AddUserIdToApiTokens extends BaseMigration
{
    public function up(): void
    {
        if ($this->isMysql()) {
            $this->execute('ALTER TABLE api_tokens ADD COLUMN user_id INT UNSIGNED NULL');
            $this->execute(
                'ALTER TABLE api_tokens ADD CONSTRAINT fk_api_tokens_user '
                . 'FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE'
            );
            $this->execute('CREATE INDEX idx_api_tokens_user_id ON api_tokens(user_id)');

            return;
        }

        // SQLite cannot add a FOREIGN KEY via ALTER TABLE. Adding the column
        // alone gives us the storage; FK enforcement at insert/update is
        // handled at the application layer (TokensController validates the
        // acting user exists). Index still useful for issuer-lookup queries.
        $this->execute('ALTER TABLE api_tokens ADD COLUMN user_id INTEGER NULL');
        $this->execute('CREATE INDEX idx_api_tokens_user_id ON api_tokens(user_id)');
    }

    public function down(): void
    {
        if ($this->isMysql()) {
            $this->execute('ALTER TABLE api_tokens DROP FOREIGN KEY fk_api_tokens_user');
            $this->execute('DROP INDEX idx_api_tokens_user_id ON api_tokens');
            $this->execute('ALTER TABLE api_tokens DROP COLUMN user_id');

            return;
        }

        $this->execute('DROP INDEX IF EXISTS idx_api_tokens_user_id');
        $this->execute('ALTER TABLE api_tokens DROP COLUMN user_id');
    }
}