ADR-074: Encryption at Rest Strategy

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)


Context

Piper stores two categories of sensitive data in Postgres that need protection against a database-level compromise:

  1. User secrets — API keys / integration credentials (Dimension A). Originally kept exclusively in the macOS Keychain, which does not exist on the hosted Linux droplet — a hard blocker for #1185 (per-user LLM keys on a hosted, multi-tenant instance).
  2. Content fields — free-text conversation/artifact data that could contain PII (Dimension B). The originating issue’s field list (written Nov 2025) predated several schema changes and named columns that no longer exist (conversations.content, uploaded_files.content, conversation_turns.user_content) — the real schema was reconciled during implementation (2026-06-20).

Decision

The primitive: AES-256-GCM with per-field HKDF subkeys

One reusable service, FieldEncryptionService (services/security/field_encryption.py), used by both dimensions:

Dimension A — user-secret store (the #1185 enabling floor)

user_api_keys.encrypted_secret (additive column, migration a358encsecret) + UserAPIKeyService (services/security/user_api_key_service.py):

Dimension B — content-field encryption

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.

What’s explicitly out of scope here (deferred, PM-approved 2026-06-20)

Consequences

Positive

Negative / tradeoffs

Verification evidence

What this ADR is NOT