irdb/api/db/migrations/20260428130000_add_role_to_api_tokens.php

<?php

declare(strict_types=1);

use App\Infrastructure\Db\Migrations\BaseMigration;

/**
 * Adds a nullable `role` column to api_tokens to back the admin-token role
 * binding described in SPEC §6: "admin — Bound to a configured role
 * (viewer | operator | admin)". The column is NULL for non-admin token
 * kinds; the application layer enforces that admin tokens have it set.
 *
 * Done as a raw ALTER TABLE rather than via Phinx's table API so SQLite and
 * MySQL share the same migration body (Phinx's addColumn after-the-fact
 * needs careful CHECK-constraint preservation on SQLite, and the existing
 * api_tokens CHECK is acceptable as-is).
 */
final class AddRoleToApiTokens extends BaseMigration
{
    public function up(): void
    {
        $this->execute('ALTER TABLE api_tokens ADD COLUMN role VARCHAR(32) NULL');
    }

    public function down(): void
    {
        // SQLite cannot DROP COLUMN reliably across all supported versions,
        // so for rollback we recreate the table without the column. In
        // practice this migration won't be rolled back in production.
        if ($this->isMysql()) {
            $this->execute('ALTER TABLE api_tokens DROP COLUMN role');

            return;
        }

        // SQLite >= 3.35 supports DROP COLUMN; the runtime in our images is
        // newer than that. Fall back to a hard error on older SQLite.
        $this->execute('ALTER TABLE api_tokens DROP COLUMN role');
    }
}