Metadata in SQLite, ciphertext in BlobStore

Our position is direct: attachment metadata belongs in the vault’s SQLite database; attachment ciphertext belongs in a separate BlobStore of files.

That split sounds like housekeeping. It is actually a storage boundary. The database can answer relational questions about attachments without becoming a warehouse for large encrypted binaries. The file store can stream ciphertext without pretending to be a query engine. Each side stays authoritative for what it owns.

NT² Vault treats that separation as part of the local-first design, not as an afterthought once files get “too big for the database.”

The constraint: attachments are not just another row type

A structured vault item is mostly small, typed fields. An attachment is different.

It can be a passport scan, a PDF, a photo of a handwritten note, or a zip of recovery material. It may be megabytes today and, architecturally, much larger tomorrow. It changes infrequently compared with titles and list metadata. When it does change, the unit of work is often the whole file, not a single field. On sync, the bytes may travel on a different path from the relational replica batch. On open, the user needs progressive read and write rather than loading the entire object into a SQL statement.

If you store those encrypted bytes as SQLite BLOBs, several pressures appear at once:

• Database pages, journals, and vacuum behavior start carrying file-sized payload.
• Backups and replica exports grow around binary blobs that do not need relational indexing.
• List and search paths pay for a store whose hot path is metadata, not ciphertext.
• Streaming encryption and decryption become awkward because the natural unit is a database row, not a file offset.
• Platform differences multiply: browser OPFS, desktop files, and optional cloud object storage all prefer file-oriented ciphertext more than SQL BLOBs.

You can force the design to work. Many systems do. The cost is that SQLite stops being primarily a relational engine and starts moonlighting as a blob filesystem with indexes glued on.

The opposite mistake is worse for a vault: stuffing attachment metadata into filenames or ad-hoc sidecars while treating the encrypted files as the source of truth. Then ownership, quotas, soft delete, parent-item relationships, and cryptographic envelope fields become difficult to query transactionally. A missing file and a missing row stop meaning the same thing.

The constraint is therefore twofold. Attachments need authoritative metadata that participates in vault transactions. They also need binary ciphertext storage that can grow, chunk, stream, and sync without living inside every SQL page.

The design: rows for authority, files for frames

NT² Vault keeps attachment metadata in the same per-vault SQLite database that holds items, vault profile sections, and search indexes. Each attachment row records what the product must know without opening the file:

• which item owns the attachment;
• display name, MIME type, and plaintext size for policy and quota;
• cryptographic envelope fields for the attachment’s content encryption key;
• chunk layout fields so the client can reconstruct the encrypted object;
• lifecycle timestamps and identifiers used by sync and backup.

The ciphertext itself lives in a per-vault BlobStore. In the browser, that is the Origin Private File System beside the vault database:

vaults/{vaultId}/vault.sqlite
vaults/{vaultId}/attachments/{attachmentId}.{chunkIndex}.bin

On desktop, the same BlobStore contract maps onto application-local files. Callers do not need two mental models: metadata goes through the attachment repository; frames go through BlobStore.

Encryption follows the same envelope rule used for items. Each attachment gets its own content encryption key. That key encrypts the file content with AES-GCM. The vault key wraps the content key. SQLite stores the wrapped key and related envelope fields. BlobStore stores only ciphertext.

Large files are not encrypted as one monolithic blob when the architecture can avoid it. The plaintext is split into fixed-size logical chunks—one mebibyte each, with a shorter final remainder. Each chunk is encrypted into a physical frame: ciphertext plus the framing needed to authenticate and place that chunk. BlobStore writes one frame file per chunk index. Reading and writing can therefore proceed frame by frame instead of requiring the whole attachment in memory.

flowchart LR
    Item[Vault item] --> Meta[Attachment row in SQLite]
    Meta -->|wraps CEK fields| Env[Envelope metadata]
    Meta -->|points to| Frames[BlobStore frame files]
    Env -.->|unlock unwraps| CEK[Attachment CEK]
    CEK -->|decrypts frames| Plain[Plaintext chunks]

Authority stays with the row. If SQLite says an attachment exists, the client knows which frames to expect, how many there are, and how to unwrap the content key after unlock. If a frame is missing or fails authentication, that is a storage integrity failure for one object—not an ambiguous corruption of the entire vault database.

This also matches how optional cloud sync should behave. Attachment ciphertext can move as opaque frames. A replica ingest path can write downloaded frames into BlobStore without decrypting them. The edge never needs plaintext to store or forward ciphertext. Metadata sync and blob sync remain related but separable concerns.

The trade-off: two stores, one consistency story

Splitting metadata and ciphertext is not free.

Every create, replace, or delete must keep the SQLite row and the BlobStore frames aligned. A write that commits metadata without frames, or frames without metadata, leaves an incomplete object. Truncation, overwrite, and cleanup need explicit rules. Quota accounting usually sums sizes from metadata rows, so those sizes must remain trustworthy. Backup and restore must carry both sides. Tests must cover orphan frames, missing frames, truncated last chunks, and envelope fields that no longer match the bytes on disk.

There is also developer temptation to “just put the blob in SQLite for now” because one transaction feels simpler. That shortcut collapses the boundary the rest of the stack depends on: paged lists that never load attachment bytes, workers that stream frames, sync that treats ciphertext as opaque, and platform adapters that map BlobStore to OPFS or native files.

We accept the dual-store discipline because the alternative is worse: one store pretending to be both a relational engine and a large-object filesystem. Two clear ownership rules beat one overloaded primitive.

The consistency rule is intentional and narrow:

1. SQLite is authoritative for whether an attachment exists and how to interpret it.
2. BlobStore is authoritative for the encrypted frame bytes named by that metadata.
3. A usable attachment requires both.

That is more bookkeeping than a single BLOB column. It is also how a local vault stays queryable as item counts grow and still openable when individual files become large.

What we refuse

Architecture becomes clearer when the refusals are explicit.

We refuse to store plaintext attachments at rest. Temporary plaintext may exist in memory while the user views or edits a file after unlock. Persistent storage holds ciphertext frames and non-secret metadata only.

We refuse to treat SQLite as the primary home for attachment ciphertext. Relational storage owns relationships and envelope fields. File-oriented BlobStore owns the encrypted bytes.

We refuse a single vault-wide content key for every attachment. Each attachment gets its own content encryption key, wrapped by the vault key. Moving ciphertext without the envelope does not disclose the file.

We refuse to make the edge decrypt attachments in order to store or relay them. Optional sync may transport opaque frames. Blind storage is a product requirement, not an optimization.

We refuse to let list and search paths hydrate attachment bytes “because they are local.” Local does not mean resident. The list window stays on thin rows; opening an attachment is a separate, intentional read of its frames.

We refuse dual truth. Filenames are not a substitute for attachment rows. Rows without frames are incomplete. Frames without rows are orphans to delete or repair, not a second catalog.

These refusals keep the vault coherent across browser OPFS, desktop files, backup, and optional cloud replicas.

Keep the catalog relational, keep the bytes in files

A private vault has to do ordinary product work: show which files belong to an item, enforce size policy, sync only what changed, and open a large encrypted document without turning the whole database into that document.

Putting metadata in SQLite and ciphertext in BlobStore is how NT² Vault answers that requirement. The database remains the catalog. The BlobStore remains the encrypted payload store. Envelope encryption ties them together without merging their storage jobs.

For the key hierarchy behind each attachment, read One key per object: envelope encryption inside NT² Vault. For why the vault database itself lives in OPFS rather than IndexedDB, see Why our vault SQLite database lives in OPFS, not IndexedDB. For keeping the interactive list off full-table loads while metadata stays queryable, see Never load the whole vault into Svelte state. For how opaque ciphertext can move through an optional edge, see Blind replica sync on the edge.

If that storage model fits how you want a private vault to work, you can try NT² Vault or read more at nt2.me.