-- Recreate group_members without the FK to tenant_members,
-- since membership is now tracked via the grants table.
CREATE TABLE group_members_new (
    tenant     TEXT NOT NULL,
    group_path TEXT NOT NULL,
    user_id    TEXT NOT NULL,
    PRIMARY KEY (tenant, group_path, user_id),
    FOREIGN KEY (tenant, group_path) REFERENCES groups(tenant, path) ON DELETE CASCADE
);
INSERT INTO group_members_new SELECT * FROM group_members;
DROP TABLE group_members;
ALTER TABLE group_members_new RENAME TO group_members;
CREATE INDEX IF NOT EXISTS idx_group_members_user ON group_members(user_id);

DROP TABLE tenant_members;