irdb/api/db/migrations/20260505100000_add_users_local_subject_invariant.php

<?php

declare(strict_types=1);

use App\Infrastructure\Db\Migrations\BaseMigration;

/**
 * Defense-in-depth for SEC_REVIEW F12: enforce at the DB layer that a
 * row with `is_local = 1` always has `subject IS NULL`. SPEC §6 / §8
 * documents this invariant — local-admin rows have no OIDC subject —
 * and `UserRepository::upsertLocal` honours it in code, but a
 * compromised IdP that pushed an OIDC user with the local admin's
 * `display_name` plus a later "data fix" flipping `is_local=1` would
 * otherwise let `findLocal()` bind the local-admin password to that
 * hijacked OIDC identity.
 *
 * Implementation:
 *  - MySQL 8: a CHECK constraint added via ALTER TABLE.
 *  - SQLite: a pair of BEFORE INSERT and BEFORE UPDATE triggers that
 *    RAISE(ABORT) when the predicate is violated. SQLite cannot ALTER
 *    TABLE ADD CHECK without a full table rebuild, and triggers give
 *    equivalent runtime enforcement.
 *
 * If a deployment already has a row violating the invariant
 * (is_local=1 AND subject IS NOT NULL), this migration will fail. That
 * is the desired signal: the operator must inspect and consolidate
 * before applying the constraint.
 */
final class AddUsersLocalSubjectInvariant extends BaseMigration
{
    private const CONSTRAINT_NAME = 'chk_users_local_subject_null';
    private const TRIGGER_INSERT = 'trg_users_local_subject_null_insert';
    private const TRIGGER_UPDATE = 'trg_users_local_subject_null_update';

    public function up(): void
    {
        if ($this->isMysql()) {
            $this->execute(
                'ALTER TABLE users ADD CONSTRAINT ' . self::CONSTRAINT_NAME . ' '
                . 'CHECK (NOT (is_local = 1 AND subject IS NOT NULL))'
            );

            return;
        }

        $this->execute(<<<'SQL'
            CREATE TRIGGER trg_users_local_subject_null_insert
            BEFORE INSERT ON users
            FOR EACH ROW
            WHEN NEW.is_local = 1 AND NEW.subject IS NOT NULL
            BEGIN
                SELECT RAISE(ABORT, 'local user must have null subject');
            END
        SQL);

        $this->execute(<<<'SQL'
            CREATE TRIGGER trg_users_local_subject_null_update
            BEFORE UPDATE ON users
            FOR EACH ROW
            WHEN NEW.is_local = 1 AND NEW.subject IS NOT NULL
            BEGIN
                SELECT RAISE(ABORT, 'local user must have null subject');
            END
        SQL);
    }

    public function down(): void
    {
        if ($this->isMysql()) {
            $this->execute(
                'ALTER TABLE users DROP CONSTRAINT ' . self::CONSTRAINT_NAME
            );

            return;
        }

        $this->execute('DROP TRIGGER IF EXISTS ' . self::TRIGGER_INSERT);
        $this->execute('DROP TRIGGER IF EXISTS ' . self::TRIGGER_UPDATE);
    }
}