Status: ACCEPTED (documenting already-shipped work) — written 2026-07-05 (Lead Dev) as part of #358’s closure, retroactively recording the design + decisions behind work implemented 2026-06-20 and live-verified on alpha 2026-06-25. No new decision is made here; this is the durable record #358 asked for.
Date of original implementation: 2026-06-20 (Dimension A + B code); 2026-06-25 (Dimension B live-verified on alpha)
Author: Lead Developer
Related: #358 (SEC-ENCRYPT-ATREST, the originating issue), #1305 (JSON/JSONB structured-column encryption, deferred sibling), #1306 (uploaded-file content-at-rest, deferred sibling), #1185 (per-user LLM keys — the hosted multi-tenant floor this ADR’s Dimension A enables), ADR-058 (multi-tenancy isolation, composes at the credential layer)
Piper stores two categories of sensitive data in Postgres that need protection against a database-level compromise:
conversations.content, uploaded_files.content, conversation_turns.user_content) — the real schema was reconciled during implementation (2026-06-20).One reusable service, FieldEncryptionService (services/security/field_encryption.py), used by both dimensions:
ENCRYPTION_MASTER_KEY, base64-encoded 32 bytes, sourced from the environment) derives an independent subkey per context label (e.g. "user_api_keys.secret", "conversations.preview"), so compromise or rotation of one field’s subkey doesn’t cross-contaminate others.__repr__ is key-safe by construction).user_api_keys.encrypted_secret (additive column, migration a358encsecret) + UserAPIKeyService (services/security/user_api_key_service.py):
encrypted_secret (when a master key is configured), so the same code path works on a dev Mac and the keychain-less Linux droplet.encrypted_secret (decrypt via FieldEncryptionService) when both a stored value and a master key are present; falls back to the keychain otherwise (legacy rows, or a key-less local-dev environment).EncryptedString, a SQLAlchemy TypeDecorator (services/security/encrypted_types.py), applied to 4 real free-text columns (the reconciled, actual schema — not the stale Nov-2025 list): conversation_turns.user_message, conversation_turns.assistant_response, artifacts.content, conversations.preview.
PMENC1:) distinguishes encrypted values from legacy plaintext — a column is safe to read during a backfill (mixed plaintext + ciphertext rows) and behaves strictly once every row carries the marker (an unmarked value is legacy plaintext; a marked but undecryptable value is a real error, never silently returned).scripts/backfill_encrypt_content_358b.py) refuses to run without a key, so production can never silently persist plaintext under the encrypted marker.Text; encryption is transparent at the ORM boundary.conversations.context/topics, conversation_turns.entities/references/context_used/turn_metadata, patterns.pattern_data) — whole-column encryption breaks queryability (e.g. the topics GIN index). Needs its own design decision (selective-field encryption vs. accept-queryability-loss vs. searchable encryption) → #1305.storage_path on disk, not in a DB column), needing its own architecture decision (local envelope encryption vs. S3-style SSE vs. full-disk encryption) → #1306.tests/security/test_user_api_key_service.py), including a real encrypt-then-decrypt round-trip test and a keychain-fallback test, run against real Postgres. Re-verified 2026-07-05 (Lead Dev) with a freshly-generated master key.tests/unit/services/security/test_field_encryption.py + test_encrypted_types_358b.py), plus live verification on the alpha droplet (2026-06-25): ENCRYPTION_MASTER_KEY present, FieldEncryptionService.from_env() round-trips, EncryptedString’s save-path produces PMENC1:-marked ciphertext and its load-path restores plaintext — confirmed in-container against the live key (no secrets logged during verification).encrypted_secret on the actual hosted instance, not just locally) is the one remaining item, requiring droplet access.