irdb/api/db/migrations/20260428120007_create_api_tokens.php

<?php

declare(strict_types=1);

use App\Infrastructure\Db\Migrations\BaseMigration;

/**
 * api_tokens has a CHECK constraint correlating `kind` with which of
 * `reporter_id` / `consumer_id` is set. Phinx's high-level API doesn't
 * expose CHECK constraints directly, and SQLite cannot add CHECK via
 * ALTER TABLE — so this migration writes raw CREATE TABLE per adapter.
 */
final class CreateApiTokens extends BaseMigration
{
    public function up(): void
    {
        if ($this->isMysql()) {
            $this->execute(<<<'SQL'
                CREATE TABLE api_tokens (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    token_hash VARCHAR(64) NOT NULL,
                    token_prefix VARCHAR(16) NOT NULL,
                    kind VARCHAR(32) NOT NULL,
                    reporter_id INT UNSIGNED NULL,
                    consumer_id INT UNSIGNED NULL,
                    expires_at DATETIME(6) NULL,
                    revoked_at DATETIME(6) NULL,
                    last_used_at DATETIME(6) NULL,
                    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
                    CONSTRAINT chk_api_tokens_kind CHECK (
                        (kind = 'reporter' AND reporter_id IS NOT NULL AND consumer_id IS NULL) OR
                        (kind = 'consumer' AND consumer_id IS NOT NULL AND reporter_id IS NULL) OR
                        (kind IN ('admin', 'service') AND reporter_id IS NULL AND consumer_id IS NULL)
                    ),
                    CONSTRAINT fk_api_tokens_reporter FOREIGN KEY (reporter_id)
                        REFERENCES reporters(id) ON DELETE CASCADE,
                    CONSTRAINT fk_api_tokens_consumer FOREIGN KEY (consumer_id)
                        REFERENCES consumers(id) ON DELETE CASCADE,
                    UNIQUE KEY uniq_api_tokens_hash (token_hash),
                    KEY idx_api_tokens_kind (kind),
                    KEY idx_api_tokens_reporter_id (reporter_id),
                    KEY idx_api_tokens_consumer_id (consumer_id),
                    KEY idx_api_tokens_revoked_at (revoked_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            SQL);

            return;
        }

        // SQLite path
        $this->execute(<<<'SQL'
            CREATE TABLE api_tokens (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                token_hash VARCHAR(64) NOT NULL,
                token_prefix VARCHAR(16) NOT NULL,
                kind VARCHAR(32) NOT NULL,
                reporter_id INTEGER NULL,
                consumer_id INTEGER NULL,
                expires_at TEXT NULL,
                revoked_at TEXT NULL,
                last_used_at TEXT NULL,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT chk_api_tokens_kind CHECK (
                    (kind = 'reporter' AND reporter_id IS NOT NULL AND consumer_id IS NULL) OR
                    (kind = 'consumer' AND consumer_id IS NOT NULL AND reporter_id IS NULL) OR
                    (kind IN ('admin', 'service') AND reporter_id IS NULL AND consumer_id IS NULL)
                ),
                FOREIGN KEY (reporter_id) REFERENCES reporters(id) ON DELETE CASCADE,
                FOREIGN KEY (consumer_id) REFERENCES consumers(id) ON DELETE CASCADE
            )
        SQL);
        $this->execute('CREATE UNIQUE INDEX uniq_api_tokens_hash ON api_tokens(token_hash)');
        $this->execute('CREATE INDEX idx_api_tokens_kind ON api_tokens(kind)');
        $this->execute('CREATE INDEX idx_api_tokens_reporter_id ON api_tokens(reporter_id)');
        $this->execute('CREATE INDEX idx_api_tokens_consumer_id ON api_tokens(consumer_id)');
        $this->execute('CREATE INDEX idx_api_tokens_revoked_at ON api_tokens(revoked_at)');
    }

    public function down(): void
    {
        $this->execute('DROP TABLE IF EXISTS api_tokens');
    }
}