Store documents as BLOB instead of Base64 (TEXT) in SQLite

[xXRoxXeRXx]

I recently took a look at how family documents are stored. Currently, uploaded files (like PDFs or images) are stored directly inside the SQLite database as Base64-encoded strings in a TEXT column (content_data).

I completely understand and appreciate the benefits of this design:

• Simple Backups: The entire app state, including uploads, is kept in a single file (oikos.db).
• Easy Deployment: Users don't need to configure or mount additional volumes for upload folders in Docker.

While storing files in a database is generally not considered standard best practice, it makes a lot of sense for a self-hosted family planner due to the sheer simplicity of portability.

The Issue with Base64

However, storing binaries as Base64 text introduces notable overhead:

1. Storage Bloat: Base64 encoding increases file sizes by roughly 33%. A 3 MB document turns into 4 MB of text in the database.
2. CPU & RAM Overhead: Node.js has to encode/decode the strings in memory on every upload/download, which can cause spikes in RAM and CPU usage, particularly on low-powered devices like a Raspberry Pi.

My Proposal

If files are to remain in the database, migrating the storage format to native binary BLOBs (BLOB = Binary Large Object) in SQLite would be highly beneficial:

• SQLite handles BLOBs up to several megabytes very efficiently (often faster than reading small files from the filesystem).
• Benefits: We instantly save ~25% disk space for all stored documents and noticeably reduce CPU/RAM overhead during read/write operations because the Base64 encoding/decoding step is eliminated.

What do you think? Would you be open to a migration of the content_data column to BLOB?

Best regards!

[ulsklyc]

Thanks for the detailed and well-reasoned proposal — you nailed both the trade-off and the fix. This shipped in v0.86.0. 🎉

What changed: local document files (and calendar attachments) are now stored as raw binary BLOB in SQLite instead of Base64 TEXT. Under the hood this leans on SQLite's dynamic typing — the content_data column keeps its TEXT affinity, but SQLite stores a bound Buffer as a real BLOB (TEXT affinity only coerces numerics to text, never BLOBs), so no table rebuild was needed and the single-file portability you liked stays intact.

Benefits, as you predicted: ~25 % less disk for locally stored documents, and the Base64 encode-on-write / decode-on-read steps are gone. One honest nuance: the JSON API boundary still transports files as a Base64 data URL (JSON can't carry raw binary), so an incoming upload is still decoded once — but at-rest storage and the whole download path are now binary end-to-end.

Migration: fully automatic on first start (schema migration v67), converted row-by-row to keep RAM bounded on small devices like a Raspberry Pi. It's idempotent, and WebDAV/DMS-backed documents are untouched. No manual action required.

Release notes: https://github.com/ulsklyc/yuvomi/releases/tag/v0.86.0

Closing as resolved — thanks again for the excellent suggestion!