app.db size concerns

[slumberling]

I noticed the app.db file in my Odysseus data directory growing quite large. So I ran an agent to try and understand what is going on there.

A few details first. My app.db size was ~640 MB. According to the agent,

PRAGMA auto_vacuum reports 0 (off) and journal_mode is delete (the slowest/most-IO journal mode). VACUUM is never called anywhere in the codebase.

Running sqlite3 app.db 'VACUUM;' immediately reduced the file size by 190 MB. I also set auto_vacuum to incremental, but I'm not sure if this will get overridden by Odysseus code.

I only had one chat session in Odysseus, ~10k tokens total, multimodal with 6 images. Quoting the agent below:

chat_messages only has 25 rows. Of those, 6 rows contain base64-encoded image data:

#   role        bytes       first bytes
1   user         8,999,281  [{"type": "text", "text": "<REDACTED>"}, {"type": "image_url", "image_url": {"url": "data:image/jpg;base64,/9j/4AAQSk..."}]
2   user         8,999,281  (same)
3   user         8,572,749  ...data:image/jpg;base64,/9j/...
4   user         6,707,200  ...
5   user         4,614,267  ...
6   user         4,614,266  ...
7-25 assistant/user messages    2,818 bytes or less, normal text

The biggest one is a 6.7 MB JPEG — its base64 form is ~9 MB.

Why images are stored in the DB at all

Look at src/document_processor.py:395 (build_user_content) and core/session_manager.py:209 (_persist_message)

The image is already on disk at data/uploads/2026/.../<filename> (144 MB total). _persist_message then JSON-serialises the multimodal content list and writes it to chat_messages.content — so the image bytes end up persisted in the DB a second time. Every attached image is now stored twice (disk + DB).

Bonus comment: I checked the uploads directory, and it turns out, old uploads are never deleted even after deleting the chat sessions those files were uploaded to. This should probably be changed.

More from the agent:

Object	Bytes	% of file
chat_messages_fts_data (FTS5 segments)	365,250,432	54.4 %

Why FTS5 is so enormous

Two FTS5-specific problems compound here:

1. No external-content mode. The schema doesn't use content='chat_messages', content_rowid='id', so FTS5 keeps its own copy of the indexed text in chat_messages_fts_content. That's another 40 MB of duplicate content that already exists in the source table.

2. The default unicode61 tokenizer is indexing base64. chat_messages_fts_docsize (after varint decoding) shows the token counts per indexed row:

doc 4:  1,236,621 tokens
doc 16: 1,627,279 tokens
doc 18: 1,627,279 tokens

Each base64-encoded image becomes ~0.5 M–1.6 M "tokens" because unicode61 treats the A-Z, a-z, 0-9, +, / characters as word characters and splits on length boundaries. The chat_messages_fts_data table has 93,271 segments storing these tokenized streams — that's where 365 MB of the file goes.

So... if I'm reading this correctly, my single chat session with <50 MB of images becomes 365 MB in the database because the images are stored in a very inefficient way. And they don't even need to be stored in the first place because they are already on disk in the uploads directory.

I don't think I'm qualified enough to attempt fixing this, but I do hope this thread will catch some of the repo maintainers' attention. For now, I'll just run vacuum manually (or see if the auto vacuum setting sticks), and manually delete old uploads.

[alteixeira20]

This looks like a real data-lifecycle bug, not just a “run VACUUM sometimes” issue.

Assuming the diagnosis is accurate, I think there are a few separate problems here that should probably be handled in order:

1. Message persistence should not store large base64 image payloads in chat_messages.content if the original file is already persisted under uploads/. The DB should store a stable attachment reference/metadata, not duplicate the full image bytes inside the chat JSON.

2. FTS should not index raw data URLs or base64 payloads. Even if multimodal content remains represented in message JSON internally, the searchable text projection should strip or replace image payloads before indexing. Otherwise every image can explode into hundreds of thousands or millions of useless tokens.

3. Existing uploads need lifecycle ownership. If deleting a chat/session does not clean up its uploaded files, then uploads become permanent orphaned data. There should probably be either a cascade/cleanup path or at least a repair command that identifies unreferenced uploads safely.

4. Existing databases need a cautious migration/repair path. For users who already have bloated DBs, the fix probably needs to:

   • back up the DB first;
   • rewrite existing multimodal message rows to replace inline image data with attachment references where possible;
   • rebuild the FTS tables from sanitized searchable text;
   • clean orphaned uploads only when they are proven unreferenced;
   • then run VACUUM as the final space-reclaim step.

I would avoid treating auto_vacuum as the main fix. It may help reclaim pages later, but it does not stop the DB from growing again if base64 images continue being persisted and indexed.

The safest first implementation slice might be measurement-only: add a small diagnostic/repair dry-run that reports DB size by table, largest chat_messages.content rows, FTS size, number/size of upload files, and suspected orphaned uploads. Then the actual storage/indexing migration can be reviewed with real before/after numbers and less risk of accidentally deleting user data.

This probably deserves a tracked issue rather than staying only as a discussion, because it affects storage growth, search indexing, upload lifecycle, and migration safety.