CREATE TABLE tenant_members (
    tenant  TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    user_id TEXT NOT NULL REFERENCES users(id),
    owner   BOOLEAN NOT NULL DEFAULT false,
    PRIMARY KEY (tenant, user_id)
);

CREATE INDEX idx_tenant_members_user ON tenant_members(user_id);

-- Restore group_members with its original FK to tenant_members.
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,
    FOREIGN KEY (tenant, user_id) REFERENCES tenant_members(tenant, user_id)
);
INSERT INTO group_members_old SELECT * FROM group_members;
DROP TABLE group_members;
ALTER TABLE group_members_old RENAME TO group_members;
CREATE INDEX IF NOT EXISTS idx_group_members_user ON group_members(user_id);