In pihole I provisioned the FQDN of a device on my local network with its local static ip address using the "Local DNS" page under the "Tools" menu. The provisioning succeeded. I see the provisioned key-value pair in /etc/pihole/custom.list. I then flushed the cache and restarted DNS. In /var/log/pihole.log, I see the resolver sometimes using custom.list to retrieve the desired ip address, but sometimes it forwards the request to the upstream server(s). In the "Query Log" of the web interface I see the requests for the local device going to the upstream servers. The requests come from multiple clients on my network.
-rw-r--r-- 1 root root 63 Jan 11 10:51 /etc/pihole/custom.list
192.168.2.249 family-room-apple-tv.openthread.thread.home.arpa
Jan 14 08:38:33 dnsmasq[12398]: query[A] Family-Room-Apple-TV.openthread.thread.home.arpa from 192.168.2.111
Jan 14 08:38:33 dnsmasq[12398]: /etc/pihole/custom.list Family-Room-Apple-TV.openthread.thread.home.arpa is 192.168.2.249
Jan 14 08:38:33 dnsmasq[12398]: query[AAAA] Family-Room-Apple-TV.openthread.thread.home.arpa from 192.168.2.111
Jan 14 08:38:33 dnsmasq[12398]: forwarded Family-Room-Apple-TV.openthread.thread.home.arpa to 9.9.9.9
With custom.list you create a A record. However, your clients request an AAAA record, which your Pi-hole can't answer from custom.list that why it gets forwarded upstream.