fix(storage): avoid DB/FTS bloat from inline upload payloads

[alteixeira20]

Install Method

Docker (docker compose up)

Operating System

Linux

Steps to Reproduce

• Start Odysseus from current dev.
• Create a chat with multimodal image uploads.
• Inspect app.db after the chat is saved.
• Compare the upload files on disk with the persisted chat_messages.content rows.
• Inspect FTS table size and whether raw base64/data URL content is being indexed.
• Delete the chat/session and inspect whether the uploaded files remain referenced or become orphaned.

Expected Behaviour

Uploaded file bytes should be stored once and referenced from chat messages. Search indexing should index useful searchable text only, not raw base64/data URL payloads. Deleting a chat/session should have a clear upload lifecycle path, or at least a safe repair/diagnostic path for orphaned uploads.

Actual Behaviour

Discussion #4762 reports that uploaded images are stored under uploads/, but are also serialized as base64 in chat_messages.content. The same report suggests those large inline payloads are indexed by FTS5, causing large app.db growth from useless base64 tokens. It also reports that uploads may remain on disk after chat deletion.

Logs / Screenshots

From discussion #4762:

• app.db grew substantially after a small multimodal chat.
• VACUUM reclaimed some space but did not address the root cause.
• FTS tables appeared to account for a large share of DB size.
• Upload files appeared to remain after chat deletion.

Model / Backend (if relevant)

Multimodal chat with image uploads. Backend does not appear to be the root issue.

Are you willing to submit a fix?

Partially — I can help but need guidance

Additional Information

This should be treated as a storage/data-lifecycle stabilization issue, not only as a vacuum/maintenance issue.

Suggested first slice:

• add a diagnostic/dry-run report for DB size by table;
• show largest chat_messages.content rows;
• show FTS table sizes;
• show upload directory size;
• report suspected orphaned uploads;
• do not rewrite or delete anything in the first diagnostic PR.

Non-goals:

• no destructive automatic upload deletion without proof that files are unreferenced;
• no broad attachment/tool artifact API in this issue;
• no unrelated DB schema rewrite;
• no treating VACUUM or auto_vacuum as the root fix.

Related discussion: #4762