People have provided some good links, but I suspect more Rails apps run on it than you might suspect. I run a few of mine on it. It performs more than well enough until you have tens of people attempting simultaneous writes (or doing crazy stuff like logging or message queuing through SQLite), which perhaps 90%+ of webapps don't need to do anyway. More time is spent rendering the view than grabbing the data in most cases.
That looks really interesting, and lightweight! Have you used it? Any comments on it? I only ask because I have a rather old CVS tree to migrate; I have used SVN but by the looks of things this is even more lightweight. Comments on it would be welcome.
Fossil is great. I've been using it increasingly for some of my projects, mostly OpenBSD related where I need versioning before the code moves into CVS. E.g. I used fossil for about a year during the initial development phase of OpenBSD's rtsx(4) card reader driver.
Since fossil has the ability to open a repository (which is a single sqlite database file) in any arbitary directory it nicely overlays with other versioning systems without wanting to store its meta data in hidden directories within the same tree. I often add a bunch of files from /usr/src to a new .fossil file in my home directory for temporary local version control.
You can migrate to fossil from CVS by converting to git first (using e.g. uwe's excellent git-cvs converter at https://github.com/ustuehler/git-cvs), and then generate a git-fast-export stream which you can import into fossil with 'fossil import'. I once tried this with the OpenBSD CVS repository but aborted the import after 7 days. By then it had imported history from 1995 to somewhere in the year 2000, and I was convinced that fossil can't handle trees this large (or histories this deep? Not sure which). This was with a hack to the fossil import code to make it use less sqlite transactions... without that it was even worse (sqlite can handle many queries per second but not so many transactions). So if your code base is large your should consider Subversion or (unless your code base is humongous) git instead.
I haven't used it myself, but the comments I've read are overall similar: it's great to have a single binary to do almost everything you need, making it the ideal choice for projects that have few contributors, little history and/or low SLOC count without having to deploy all the usual jazz.
SQLite is also used in many embedded systems. If you plug in a USB drive to my TV, it creates a SQLite database to keep track of the current position of the video files it plays.
