-- Drop the FK to tenant_members (rebuild without it).
CREATE TABLE group_members_old (
    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
) STRICT;

INSERT INTO group_members_old (tenant, group_path, user_id)
SELECT tenant, group_path, user_id FROM group_members;

DROP TABLE group_members;
ALTER TABLE group_members_old RENAME TO group_members;
CREATE INDEX idx_group_members_user ON group_members(user_id);