-- System (platform-operator) access moves out of the tenant grant tables into
-- its own concept. It never belonged there: a system grant is anchored to a
-- tenant (required column) yet grants access across the whole platform, and
-- sharing the tenant-grant path let a tenant owner set system=true and escalate.
-- system_grants has no tenant/path — it's the platform plane, resolved separately.
CREATE TABLE system_grants (
    principal TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role      TEXT NOT NULL,
    PRIMARY KEY (principal, role)
) STRICT;

-- Migrate existing system-scoped user grants (e.g. support) out of user_grants.
INSERT INTO system_grants (principal, role)
SELECT user, role FROM user_grants WHERE system = 1;

-- The system flag is gone from the tenant grant tables — those can no longer
-- produce a platform-wide cap.
ALTER TABLE user_grants DROP COLUMN system;
ALTER TABLE group_grants DROP COLUMN system;
ALTER TABLE bot_grants DROP COLUMN system;