Maybe I can help demystify a couple things?
Creative Commons believes CC0 to be appropriate for dedicating one’s source code to the public domain. I tend to agree, except (in my opinion) in cases where your regional jurisdiction specifically doesn’t allow you to dedicate material to the public domain (can’t find a list for that). CC0 has provisions in this case, but from a moral (not legal) standpoint I recommend not misleading folks about your ability to dedicate under the public domain, with CC0 or otherwise. Believing that an item is dedicated to the public domain under CC0, only to find out later it’s a CC0 “promissory note,” is anxiety inducing for me. Always research your jurisdiction before using CC0.
I wouldn’t call CC0 open source mostly because I’m already struggling to educate my community on open source licenses. Each next person has a different idea about what it is, which is challenging our ability to cooperate on projects. I don’t think any of them would face serious hurdles toward contribution to a CC0 project, so we’re not so worried about using CC0 for projects where it’s appropriate. We just call it what it is, “public domain dedication.”
On the subject of Awesome Lists, in general most are not subject to copyright in the United States and many other countries. In the European Union (and a couple other countries) they would instead be subject to database rights, which don’t exist in the United States. CC0 covers database rights, so it’s still recommended for databases/Awesome Lists. That’s not part of the public domain conversation, however.
I believe this was all spurred on by a misunderstanding about the role of SPDX. I’m not a lawyer, and I only volunteer with SPDX for an hour or two biweekly. That said I’ve already seen several misunderstandings about SPDX’s role, so this is nothing new.
SPDX is about three things: giving machine-readable IDs to licenses, creating a software bill of materials spec, and creating the tools for SBOM generation. Here’s the SPDX page for the SQLite “blessing” for use in your SBOM or whatnot. You can definitely include SQLite in your SPDX SBOM!
PS SQLite is explicitly not open source. Outsiders are not allowed to contribute to the SQLite source.