Storage Architecture

Primary: PostgreSQL 14+ (production, managed or self-hosted — RDS, Cloud SQL, Azure Database for PostgreSQL, Supabase) or SQLite (small / edge / single-VM deployments). Prisma ORM, 44 tables. Knowledge: LevelDB (graph data). Vectors: LanceDB (1536-dim embeddings, embedded — no separate vector-DB service required). Multi-Cloud: Local, AWS S3 (+ STS), S3-compatible (MinIO, R2), GCS, Azure. Redis (optional): Distributed state, rate limiting, TLS. Schema self-healing: SQLite drift backfill at startup covers SCIM provisioning, auth-wizard fields, and retired-table cleanup (user_sessions removed at startup via schema-sync).

35.1 Request body storage (split from DB)

Full request/response payloads are stored on disk, not in SQLite. The DB row keeps a small pointer; the body lives in a per-request encrypted file under the admin-configured File Archiver base path (so customer NFS / EBS / S3FS mounts work transparently).

Layer	Carries	Path
usage row	Small metadata + bodyArchive pointer (~1–2 KB)	SQLite
File Archiver (JSONL events)	Request/response previews + structured event fields	{basePath}/{org}/requests/YYYY/MM/DD/requests_HH.jsonl.gz
Request Body Archive	Full request + response, AES-256-GCM encrypted, gzip compressed	{basePath}/{org}/bodies/YYYY/MM/DD/HH/{requestId}.json.gz.enc
ChatFileStore	Chat-session turns (encrypted, sessionId-keyed)	{basePath}/{orgId}/chats/{tenantId}/YYYY/MM/DD/{sessionId}.enc
LanceDB (session vectors)	Embeddings for AI Insights semantic search	./data/session-vectors/

Body archive file format (binary): [ IV(12) ][ AES-GCM authTag(16) ][ ciphertext = AES-256-GCM( gzip( JSON ) ) ]. Key derived via SHA-256 from BODY_ARCHIVE_KEY_HEX (falls back to CHAT_ENCRYPTION_KEY, then a built-in 64-byte default). Same key story as ChatFileStore.

Retention: a daily sweep deletes body files mtime-older than eventPipelineFileArchiverRetentionDays (default 90) — the same horizon admins set in the UI for the File Archiver Default Sink. Empty directories are unwound on the way back up. First sweep fires ~5 min after container boot so it doesn’t fight startup migrations.

Backfill on upgrade: scripts/migrate-usage-bodies.mjs runs from start.sh on each container boot. Any usage row whose metadata still carries inline fullRequest / fullResponse / fullMessages (legacy writes pre-split) is moved to the on-disk encrypted archive — using the row’s original timestamp for both the directory path and the file mtime — and the inline fields are stripped. A VACUUM follows to reclaim disk. Idempotent; no-op on clean DBs.

35.2 Model catalog (FK over string)

models.provider_id (FK → providers.id) is the source of truth for routing. The legacy models.provider string column is kept for display only — at request time selectProviderForModel resolves through the FK, falling back to the string only for rows where providerId is null. This prevents UI renames (and per-org provider-name drift, e.g. one tenant calls a provider provider-a while another calls the same upstream provider-b) from silently breaking /v1/chat/completions while /v1/models keeps listing the model.

Startup reconciliation in start.sh:

• FK-set rows whose provider string disagrees with the FK target are logged (runtime routes via FK; data is left untouched).
• Orphan rows (FK null + string doesn’t match any provider in the row’s org) auto-repair when the org has exactly one kind='openai-compatible' provider enabled. Otherwise they’re reported and left for admin attention.

---