SQLite, PostgreSQL, Object Storage, and Content Addressing

An AI application stores very different things:

transactional run state and leases;
append-only events and evaluations;
prompt/program/model metadata;
embeddings and search indexes;
images, audio, video, tensors, checkpoints, and generated media;
caches and ephemeral working files.

One storage engine should not be forced to serve every access pattern. The companion now has:

mosaic-db: relational run/event/job invariants;
mosaic-artifacts: tenant-scoped SHA-256 content-addressed bytes with atomic no-clobber publish and verified bounded reads.

Learning objectives

choose SQLite, PostgreSQL, object storage, or local files by guarantees/access;
understand engine-specific concurrency and operational trade-offs;
design immutable content-addressed artifact identity;
separate content identity from authorization and metadata;
publish local files atomically without overwriting existing content;
verify hashes on read and object-store transfer;
coordinate database metadata and non-transactional object writes;
design garbage collection, retention, versioning, and backup;
test traversal, tampering, bounds, deduplication, and tenant isolation.

Dependency and mental model

Depends on SQLx transactions, streaming upload finalization, tenancy concepts, and security boundaries.

relational database
  identities, ownership, state, leases, indexes, references, policy
                    │ artifact reference
                    ▼
object/file storage
  immutable large bytes at content-derived location
                    │
                    ▼
caches/indexes
  reproducible derivatives keyed by source + transform identity

The database is the authority for who owns and may use an artifact. A hash proves which bytes are addressed. It does not prove who may read them, who created them, whether they are safe, or what license/retention applies.

Storage selection matrix

Need	Default candidate	Why
Local CLI/settings/cache	SQLite/local files	Embeddable, no server
Multi-replica run/job state	PostgreSQL	Transactions, row locks, strong relational constraints
Ordered events/evals	PostgreSQL, partitioned at scale	Transactional append/query/index
Large media/model artifacts	Object storage	Durable byte scale, multipart/range/lifecycle
Tiny metadata	Database	Queryable and transactionally linked
Derived local decode cache	Local disk	Fast, disposable, keyed by immutable identity
Vector/lexical search	Purpose-fit DB/index	Access-pattern dependent; source truth remains explicit
Secrets	Secret manager/KMS reference	Rotation/access/audit, not ordinary rows/files

Defaults are starting points. Measure size, throughput, concurrency, durability, recovery, latency, residency, and operator capability.

SQLite: excellent within its envelope

SQLite is ideal for:

single-user CLI/desktop applications;
local evaluation databases;
fixtures and fast repository tests;
edge/offline deployments;
modest write concurrency with careful transactions.

It is a real transactional database, not “just a file.” But the file and locking topology matter. Multiple sqlite::memory: pool connections are separate databases unless a shared-memory URI is configured; the companion uses one connection intentionally.

For file databases:

enable foreign keys per connection;
decide rollback journal versus WAL;
set busy timeout and handle busy errors;
do not put a live DB on unsupported network filesystems;
checkpoint/vacuum/backup deliberately;
protect file/directory permissions;
test abrupt power/process loss on target platform.

SQLite serializes writers. WAL can improve reader/writer coexistence, not create unlimited write parallelism.

PostgreSQL: shared transactional coordination

PostgreSQL is the default for:

multiple API/worker replicas;
tenant-scoped relational data;
job claims with row locks/SKIP LOCKED;
event streams and projections;
stronger operational tooling/replication;
JSONB plus queryable typed columns;
notification as wake-up hint.

It adds a network, pool sizing, TLS/credentials, migrations at scale, replication/failover, vacuum, query planning, locks/deadlocks, backups, and point-in-time recovery.

Do not write “SQLx supports both” and assume behavior is portable. Placeholder syntax, types, returning clauses, JSON, timestamps, isolation, locks, upserts, DDL, and query plans differ. Production-engine integration tests are mandatory.

Large bytes belong outside relational hot rows

Storing multi-gigabyte media/checkpoints in ordinary relational rows can cause:

oversized backups/replication/WAL;
table/index/cache pressure;
expensive row updates/vacuum;
connection occupancy while streaming;
poor range/multipart/CDN/lifecycle support.

Store immutable bytes in object storage and transactional metadata/reference in the database:

artifact_id
tenant_id
content_digest + algorithm
object key + version
size + media type
state (uploading/verifying/ready/quarantined/deleted)
source/provenance
classification/retention
encryption key reference
created_by/created_at

Do not expose raw internal keys as authorization. Generate short-lived scoped download URLs after a fresh permission check, or proxy small sensitive downloads.

Content addressing

For bytes B:

digest = SHA-256(B)
address = tenant / sha256 / digest[0..2] / digest

The companion output:

sha256:afbc...0de6 bytes=34 verified=true

Benefits:

immutable identity;
automatic same-byte deduplication within scope;
cache keys and provenance;
integrity verification;
idempotent publication;
reproducible derivative inputs.

Content address is not a filename from the user. ArtifactDigest::parse accepts exactly 64 lowercase hex characters and validated deserialization calls the same constructor.

Hash scope and canonicalization

Hash the bytes whose identity you need:

original upload bytes;
canonical decoded PCM/pixels;
normalized document;
encoded generated artifact;
model file or whole manifest.

These are different identities. Store algorithm/version and relationships:

original digest
  └── transform program/version/parameters
        └── derivative digest

For multi-file models/datasets, hash a canonical manifest that lists relative path, file digest, size, and role in deterministic order. Prevent path traversal/duplicate/case-normalization ambiguities.

SHA-256 collision resistance is suitable for content identity here; still verify existing bytes when publishing to an occupied address. A hash is not a digital signature. Use signed manifests when publisher authenticity matters.

Tenant isolation versus global deduplication

Global CAS reveals that two tenants have identical content through timing/existence/storage side channels and complicates deletion/encryption/residency/legal holds.

The companion uses tenant-scoped paths:

root/tenant_a/sha256/af/afbc...
root/tenant_b/sha256/af/afbc...

Digests match, physical paths do not. This favors isolation over cross-tenant storage savings.

Global deduplication, if allowed, needs:

opaque database artifact IDs;
authorization independent from digest;
reference accounting;
per-tenant encryption/access policy;
deletion/legal hold semantics;
existence-side-channel decision;
no unauthenticated “does hash exist?” endpoint.

Never treat knowledge of a digest as permission.

Atomic local publication

The companion put_bytes:

validates tenant and media type;
computes SHA-256;
creates the content directory;
if final path exists, verifies exact bytes and digest;
creates a unique temporary file in the same directory;
writes all bytes and sync_all;
creates a hard link from temp to final;
removes the temp;
if another writer won, verifies the winner.

Why hard link rather than rename? Common rename semantics may replace an existing target. A hard link to a new path fails with AlreadyExists, providing no-clobber publication on supported local filesystems. Same-directory temp keeps publication on one filesystem.

This tutorial implementation does not fsync the containing directory, stream large puts, or claim support for every filesystem/OS. Production local durability needs directory sync, permission/umask, disk-full behavior, hard-link support, crash tests, and cleanup of abandoned temp files.

Bounded verified reads

get_verified_bounded:

validates tenant;
constructs a path only from validated tenant/digest;
uses symlink_metadata;
requires a regular file;
rejects metadata length above caller limit before reading;
reads;
recomputes digest;
returns only matching bytes.

Tests prove:

traversal-shaped tenant is rejected;
malformed/deserialized digest cannot form a path;
64-byte object is rejected under 63-byte bound;
tampered bytes fail integrity;
identical bytes dedupe;
tenants have separate paths.

Metadata-before-read has a time-of-check/time-of-use window if an attacker can mutate the storage tree. Keep the root private/trusted or use descriptor-relative no-follow APIs/platform sandboxing. Object storage has a different trust/API boundary.

Object-store publication

A production object write:

stream to staging/generated key with checksum;
complete multipart upload;
verify provider-reported/full-object checksum, size, encryption, version;
independently trust-but-verify when policy requires;
insert/finalize database artifact metadata;
publish immutable content address with conditional create (If-None-Match) or verify existing;
mark ready transactionally;
clean staging object later.

Do not assume an S3 ETag is the whole-object MD5: multipart/encryption cases differ. Use explicit checksum APIs/metadata and store your canonical digest.

Object stores may provide strong read-after-write but still have failure windows between object and database operations. Design for orphan staging objects and metadata referencing missing bytes.

Database + object storage is not one transaction

Common protocol:

DB: upload row = initiated
object: upload staging bytes
DB: uploaded_unverified with expected evidence
worker: verify object
object: publish/confirm immutable final key
DB transaction: artifact ready + event/outbox
GC: remove abandoned staging after grace

States make incomplete work visible/recoverable. Never insert ready before verification. Never delete an object immediately because one transaction removed a reference; concurrent/uncommitted references and legal holds exist.

Reconciler invariants:

every ready artifact resolves to correct bytes/version/checksum;
every final object has metadata or is old enough for orphan GC;
no deleted/quarantined artifact is served;
references point only to allowed ready state;
retention/hold wins over GC.

Artifact metadata, aliases, and mutability

Content objects are immutable. Human names are mutable aliases:

project/latest-transcript → artifact_id
model/production          → model release manifest

Update aliases with optimistic version/transaction; never overwrite content bytes at an address.

Generated output identity should include:

exact output bytes digest;
source artifact digests;
program/prompt/tool/model revisions;
seed/sampler/parameters;
environment/native runtime identity where reproducibility needs it;
policy/eval release.

Same inputs do not always imply same generative output. Store actual output digest and generation record.

Garbage collection

Reference counting alone can fail under transaction races, delayed events, snapshots, legal holds, and derived references. Mark-and-sweep model:

freeze/record a GC epoch;
enumerate live database roots/references, manifests, holds, in-flight uploads;
mark reachable content addresses;
list candidate objects;
require object age beyond grace/replication windows;
recheck current reference/hold before delete;
delete idempotently and audit;
reconcile failures.

Use object lifecycle for staging/temp expiration, but not as the only source of legal deletion policy. Test restore before relying on deletion.

Encryption, privacy, and residency

TLS in transit;
provider/server-side encryption at rest;
tenant/customer-managed keys where required;
key references/versions in metadata;
least-privilege bucket prefixes/actions;
no public bucket/list;
access logs without signed URLs/secrets;
region/residency enforced in routing and metadata;
backup/replica inherits classification/retention;
deletion includes versions, derivatives, caches, replicas under policy.

Convergent/content-derived encryption can leak equality and weaken tenant isolation. Use only under a reviewed design.

Backup and disaster recovery

Back up relational metadata and bytes as one recoverable system:

PostgreSQL base backup/WAL/PITR;
object versioning/replication/retention;
SQLite consistent backup API/snapshot, not copying a live file blindly;
model/prompt/program release manifests;
encryption key recovery;
restore order and reconciliation.

Test:

restore to a new environment;
artifact hash sample/full audit;
point-in-time metadata with object versions;
missing/orphan reconciliation;
RPO/RTO against measured restore time;
credential/key rotation during recovery.

A backup that has never been restored is only an assumption.

Runnable companion implementation

cd rust/rust-ai-engineering
cargo run -q -p mosaic-artifacts --example content_addressed
cargo test -p mosaic-artifacts
cargo run -q -p mosaic-db --example transactional_run

Expected artifact shape:

sha256:<64 lowercase hex> bytes=34 verified=true

The local CAS is under crates/mosaic-artifacts; relational implementation/migrations are under crates/mosaic-db.

Failure investigation: digest path exists with different bytes

stop publication and treat as integrity incident;
recompute digest from expected and stored bytes using known implementation;
inspect path construction/algorithm/version/encoding;
inspect filesystem/object mutation and race;
quarantine both evidence sets;
verify backups/other replicas/checksum metadata;
do not overwrite the occupied address;
repair mapping under audited new identity.

Implementation/path corruption is far more likely than a SHA-256 collision, but collision-safe behavior still refuses replacement.

Failure investigation: DB says ready, object returns not found

confirm bucket/key/version/region/tenant and credentials;
inspect finalize transaction and object operation order;
verify lifecycle/GC/version deletion;
check replication/restore state;
mark unavailable/quarantined without losing evidence;
restore or rehydrate only after digest verification;
add reconciler and failpoint for the exact window.

Failure investigation: storage bill grows after run deletion

classify staging, multipart, final, versions, derivatives, caches;
inspect incomplete multipart and lifecycle rules;
trace references/holds/retention;
compare DB reachability with object inventory;
run dry-run mark/sweep with age grace;
delete idempotently in bounded batches;
publish capacity and leak metrics by low-cardinality class.

Alternatives and trade-offs

Random opaque object keys

Avoid equality leakage and simple. Need separate digest/idempotency metadata and duplicate storage.

Content-derived keys

Natural idempotence/integrity/cache. Equality/existence privacy, GC, collision-safe publication, and canonicalization need design.

Store blobs in PostgreSQL

One transaction/backup and authorization path. Can burden WAL/replication/cache/connections at media scale.

Local filesystem

Fast/simple/offline. Replica-local, durability/sharing/backup/filesystem semantics become your job.

S3-compatible object storage

Scalable durable bytes and lifecycle/range/multipart. Network/service/API cost and metadata dual-write protocol remain.

Security and performance implications

Security:

validated components only; no caller paths/bucket keys;
hash is identity, never authorization;
tenant isolation/existence leakage explicitly decided;
verify type/size/hash and sandbox decoders;
conditional no-overwrite writes;
immutable/versioned artifacts and auditable deletion;
signed URLs narrow/short/redacted;
backups/derivatives inherit classification.

Performance:

hash streaming fuses with upload/read but costs sequential CPU;
CAS dedupe saves storage but verification/list/GC costs;
two hex prefix characters distribute local directory entries;
local example reads full bounded bytes; production streams/ranges;
database stores small metadata, not hot large bytes;
multipart part size balances memory/retry/request cost;
caches key on source + full transform identity and are disposable.

Exercises

Change put_bytes into a streaming reader with maximum bytes and incremental SHA-256.
Add a signed/versioned multi-file artifact manifest with strict relative paths.
Design database metadata states and reconciler for object-finalize crash windows.
Implement conditional S3-compatible put plus full checksum verification.
Add tenant-scoped artifact references and prove one tenant cannot probe another digest.
Write a mark-and-sweep dry run with legal hold and minimum age.
Perform and time a database-plus-object restore verification.

Solution guidance

Stream into same-directory staging while counting/hashing, then no-clobber publish and verify winner. A manifest canonicalizes sorted normalized relative paths and rejects absolute/parent/duplicate/case ambiguous paths. The database exposes only opaque artifact IDs; repository methods always bind tenant. Reconciliation lists explicit nonterminal states and compares final object size/checksum. GC begins with dry-run inventory and rechecks references immediately before deletion.

Check your understanding

What belongs in relational rows versus object storage?
What does a content digest prove—and not prove?
Why are equal digests stored separately per tenant here?
Why can rename be unsafe for no-overwrite publication?
Why is an S3 multipart ETag not a canonical whole-object digest?
What failure window exists between object and database?
Why does GC require a grace period and recheck?
What do SQLite tests not prove about PostgreSQL?