- © 2014 Word to the Wise
Mail.app is the main mail client on OS X. While it stores messages in a fairly simple one-message-per-file hashed directory tree most of the metadata about those messages - including which mailbox they are in - is stored solely in a sqlite database.
You can read (and modify) the database from the commandline (sqlite3 '~/Library/Mail/V2/MailData/Envelope Index') or using any of the sqlite GUI tools - though you'd be smart to take a copy of it and just work with the copy.
The database has fairly useful table and column names, but is undocumented and doesn't include much schema metadata (no foreign keys, for example). I've used SchemaSpy and this metadata file to create a rough set of documentation for the schema. There are some things I know are missing (associations? ews_folders?) but it's better than nothing.