Building a data warehouse

It is time to give my personal AI access to more of my data, so it can start answering questions with context and in a more relevant way. As part of this I wanted to address something I have been thinking about for a long time, funneling most of my data into a searchable PostgreSQL database. Obviously this will not be your typical data warehouse, I do t expect more than a few gigabyte of data to accumulate over a year.

The stack will be Python and PostgreSQL. I could certainly make vectorization and ranking work in Go, but I refuse to fight the stack all day long. For most of ML and AI Python is and will likely be the lingua franca.

Data

One of the most important subjects to me is messages and email, so I obviously started there. I will consolidate all in a single model, there is not much difference but the way they were transferred over the wire. There is a sender, a recipient, a body and some meta data. Most importantly a source and external ID so I can make sure messages are unique and I can go back to the original data if I need to.

class Message(BaseModel):
    external_id: str = Field()
    source: str = Field()
    sender: str = Field()
    recipient: str | None = Field()
    body: str = Field()
    language: str = Field(default="en")
    timestamp: datetime = Field()

Inserting data is as simple as an upsert and generating the vectors for the body, nothing too fancy here. I was considering an option to bulk insert data, but with vectoriztion in the mix the runtime of an insert will be expensive anyway.

Querying gets a bit more fun with Reciprocal Rank Fusion (RRF) in the mix. We do three different things: we do a full-text search with tsvector, a trigram search with pg_trgm and a vector search with the embeddings, while allowing German and English content to be searched at the same time. It kind of works, but sometimes the scores look a bit off. I might change this and filter based on language during query time.

When we have all candidates we rerank the results and then sort by ranking. Which allows us to go from a few million rows to fifty or sixty candidates to the top three most relevant messages in a matter of a few CPU cycles. I still have to properly benchmark it but I would be surprised if query time for the full dataset exceeds one to two seconds.

Models

I already mentioned we are vectorizing and reranking. Both happens with two small, specialized models that can run fully on the CPU, which I very much appreciate as I get far more CPU resources than GPUs.

For vectorization I am using intfloat/multilingual-e5-small which maps English and German into the same vector space. So technically this should allow me to seamlessly query in both languages.

The 3 way RRF does an amazing job retrieving potential candidates very quickly, but due to the nature of encoding things in vectors nuances in the query can get lost. For this I added BAAI/bge-reranker-large, which does help, especially with negations or if the text contains (for example) doofe and I query for doof. Cross encoding with this model is not necessarily fast, which is why RRF is in charge of slimming down the dataset first.

Loading data

Next comes the fun part. Loading all my emails should be relatively simple as I use Mail Archiver to backup all my email accounts. Sadly there is no bulk export so I might just tap into the database directly to setup a sync job. Not the most solid or desirable way to do it, but good enough. Worst part will be keeping track of potential database schema changes in Mail Archiver that might break the sync job.

iMessage is actually one of the easier walled garden services from Apple to ingest. All messages are already in a SQLite database, so all you got to do is open the DB in read only format and the sync job is off to the races. There is a small detail if iCloud sync is enabled - messages are only present when synced, which seems to happen when they are viewed for offloaded mesages. I did not play enough with it to know what the actual issues will be, but that is a problem for future Timo.

With Delta Chat I actually know what the issues are. Back before we migrated I asked around what the best way would be to build an archive, and the answer always was to have a bot in the conversation. I think this would be annoying to setup and I also think we can do better than that. I will mostly likely write a small Delta client that just sits there and receives messages and forwards them to the warehouse.

Progress

There is still a lot to do, but I think I like the direction this is going and Endirillia will soon have a tool to access far more data than I initially anticipated. Part of this also means revisiting how I store sessions and user facts for the assistant. I might want to push more data into the warehouse and consider the warehouse the sole longterm knowledge for the project.

Another part that might be interesting is to leverage it for things such as plan files or when I instruct my coding agent to generate a Forgejo action to sign Windows binaries. Having a persistent, easy to access way to dig into why I made a certain change to a project a year later might be convenient.

I plan to keep any AI but reranking and vectorization out of the project so the warehouse can continue to run on a CPU and it might be useful outside of what I am building it for - the code can be found here.