CREATE TABLE groups (
    tenant TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    path TEXT NOT NULL,
    name TEXT NOT NULL,
    PRIMARY KEY (tenant, path)
);

CREATE TABLE group_members (
    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)
);

CREATE INDEX idx_group_members_user ON group_members(user_id);