Well I did a quick Google search and couldn't find anything of real use. So I decided to check my phone.
Using adb I got a command line to my phone.
(Note you must have a rooted phone to do what's next)
I did a quick find to see where messages would be kept.
#find . -name "*sms*" -print
This displayed a good amount of results but the most promising was
Knowing that you can open up .db files with sqlite, I used the command
After using a few command like .tables and .schema as well as a few queries I was able to conclude that this is where all the text messages are stored from the default message app on android phones.
Now, I know there are a few other apps out there that you can install from the marketplace that will leverage this database, specifically Handcent. I would assume that the way they do it is leveraging the code provided by Google here and tying into this database.
Hopefully this will be helpful to anyone else who was curious about this, or thinking about making there own SMS application.