Some of these backups may overlap with each other. The result is 10-20
folders of messages with no obvious way to access them. I would like
to unify all of them and remove duplicates if they exist.
Yes, you are going to have many, many duplicates. This is expected behavior given the way backups work.
Time Machine does an incremental backup; meaning it only backs up what has changed since the last incremental backup. Your chats are held in a single database file (referenced in your question), so every time you chat with someone, that database gets updated and ultimately gets backed up every time Time Machine runs.
Are there any applications that do this or any sort of tutorial I
No. Just go to the most recent backup; all of the chat files will be there.
The files are sqlite so even a specification of the database structure
would be helpful.
sqlite3. If you want to get the database structure, you can use the following commands in Terminal:
$ sqlite3 chat.db
sqlite> .schema # prints out the database schema
sqlite> .tables # lists the database tables
sqlite> PRAGMA table_info(tablename); # lists the columns of a particular table
sqlite> select text from message; # raw output of all text messages