Storage Architecture: Encrypted On-Disk Body Archive (new in v2.0.45+)

Why this exists

In earlier releases, two write paths (the transparent proxy and the agent web proxy) stored the full request and response payloads inline in the usage table’s metadata column. In real-world deployments this caused the SQLite database to grow to 12+ GB within days (about 2 MB per row, with some rows reaching 13 MB). The bloat made admin analytics queries stall and contributed to recurring container out-of-memory restarts.

v2.0.45 splits payload storage into two layers:

Layer	Carries	Where	Why
usage row	Small metadata + a bodyArchive pointer (~1–2 KB total)	SQLite / PostgreSQL	Fast index lookup; analytics aggregation
Request Body Archive (new)	Full request + response, AES-256-GCM encrypted, gzip compressed, one file per request	{File-Archiver-base}/{org}/bodies/YYYY/MM/DD/HH/{requestId}.json.gz.enc	Bounded DB size, encrypted at rest, retention-driven

The body archive lives under the same root the admin configured for the File Archiver Default Sink (eventPipelineFileArchiverBasePath, default ./data/events). Customer-mounted NFS / EBS / S3FS volumes for archived data are honored transparently.

File format

Each .json.gz.enc file is a single binary blob:

[ IV (12 bytes, random per file) ]
[ AES-256-GCM auth tag (16 bytes) ]
[ ciphertext = AES-256-GCM-encrypted gzip-compressed JSON payload ]

The plaintext payload (post-decrypt, post-gunzip) is:

{
  "request_id": "req_1779988853166_hdo8gga3e",
  "timestamp": "2026-05-29T15:42:11.123Z",
  "request":  { /* full inbound request body */ },
  "response": { /* full upstream response body */ },
  "meta": { "source": "transparent-proxy", "provider": "openai", "endpoint": "/v1/chat/completions" }
}

Encryption key management

• Key source priority: BODY_ARCHIVE_KEY_HEX env var → CHAT_ENCRYPTION_KEY env var → built-in default (same material as ChatFileStore).
• The 64-byte source material is SHA-256-hashed to a 32-byte AES-256 key.
• For production deployments: set BODY_ARCHIVE_KEY_HEX (128 hex characters) before first boot. Rotating the key invalidates files written under the old key — a re-encrypt migration would be required.

Retention

A background sweep runs once ~5 minutes after server boot and then every 24 hours. For each org, it reads eventPipelineFileArchiverRetentionDays (default 90) and deletes encrypted body files with mtime older than that cutoff. Empty directories are unwound on the way back up.

Same retention horizon as the File Archiver JSONL event stream — one source of truth (CompanySettings) governs both.

Backfill migration on upgrade

When upgrading from a release < v2.0.45 to v2.0.63 (or later) with the same data volume, start.sh runs scripts/migrate-usage-bodies.mjs on every container boot. The migration:

1. Reads each org’s event_pipeline_file_archiver_base_path from company_settings.
2. Walks usage rows whose metadata exceeds 50 KB and contains fullRequest / fullResponse / fullMessages.
3. Writes each body to {base}/{org}/bodies/.../<requestId>.json.gz.enc using the row’s original timestamp (so the directory tree and file mtime match request time, not migration time).
4. Strips the inline body fields from metadata, replaces them with a small bodyArchive: { path, base_path, recorded_at, encrypted: true } pointer.
5. Runs VACUUM to reclaim the disk space.

The migration is idempotent (a re-run on a clean DB is a no-op) and safely interruptible (each row is independently committed). Sets PRAGMA busy_timeout=60000 so VACUUM waits up to a minute if anything else holds the SQLite lock. Production deployments observed DB shrink from 12 GB → a few hundred MB on first boot.

Operator verification

After deploy:

# Average metadata size — should now be ~1–2 KB, not ~2 MB
docker exec <container> sqlite3 /app/data/agentwatch.db \
  "SELECT ROUND(AVG(LENGTH(metadata))/1024.0, 2) AS avg_kb FROM usage;"

# Sample pointer
docker exec <container> sqlite3 /app/data/agentwatch.db \
  "SELECT json_extract(metadata, '$.bodyArchive') FROM usage WHERE metadata LIKE '%bodyArchive%' LIMIT 1;"

# Body files actually written
docker exec <container> find /app/data/events -name '*.json.gz.enc' | head -5

---