Epic: encryption coverage gaps — sensitive columns still stored in plaintext

[AndresL230]

Why this epic exists

Column-level AES-256-GCM encryption (backend/services/encryption.py) covers a specific, deliberately chosen set of columns. An audit on 2026-08-02 — grepping every write boundary that imports services.encryption and cross-checking against every CREATE TABLE in backend/db/migrations/ — found several columns of the same sensitivity class sitting in plaintext.

There is currently no encryption item anywhere on the roadmap. M6 ("Stable beta with real students", 2026-09-30) lists prompt-injection hardening and an accessibility pass; encryption coverage is not mentioned. If beta means real students, this work should be sized against that date.

The dominant pattern: derived content

Several paths decrypt protected source material, generate something from it, then store the derivative in plaintext. routes/flashcards.py:17 and routes/study_guide.py:27 show it plainly — both import decrypt_if_present, decrypt_json and nothing else.

Derived column	Source (encrypted)	Issue
flashcards.front / .back	documents.*	#518
study_guides.content	documents.*	#518
room_summaries.summary	room_messages.text	#518
course_chunks.chunk_text	documents.extracted_text	#484 (pre-existing)

sessions.summary_json is encrypted (routes/learn.py:1036), so this call has already been made correctly once — these are the instances that were missed.

Children

• Derived content is stored in plaintext while its encrypted source is protected (flashcards, study guides, room summaries) #518 — derived content: flashcards, study guides, room summaries P2
• newsletter_emails.email is plaintext while users.email is encrypted — needs a decision, not a swap #519 — newsletter_emails.email inconsistency; needs a decision, not a swap P3
• Free-text user input stored in plaintext: feedback.comment and issue_reports.description #520 — free-text input: feedback.comment, issue_reports.description P2
• Quiz performance data stored in plaintext (questions_json, answers_json, quiz_context) #521 — quiz performance data: questions_json, answers_json, context_json P2
• Decision needed: course_chunks.chunk_text is plaintext while documents.extracted_text is encrypted #484 — course_chunks.chunk_text (pre-existing; see the embedding caveat added there)

Explicitly out of scope

job_applications (12 rows in prod, fully plaintext PII) will NOT be encrypted. Decision by @AndresL230 on 2026-08-02: that data is to be deleted rather than protected. Recorded here so it does not get re-filed as a gap. The rows remain in production plaintext until that deletion happens.

Cross-cutting constraints

Three things that make these decisions rather than find-and-replace, and that every child issue should respect:

1. Encryption breaks value-based SQL. AES-GCM uses a fresh random nonce per call, so the same input never produces the same ciphertext. Any column with a UNIQUE constraint, an index used for lookup, an upsert conflict target, or a WHERE/ORDER BY on its value cannot simply be encrypted. newsletter_emails.email is plaintext while users.email is encrypted — needs a decision, not a swap #519 is the clearest case.
2. Encrypting a derivative does not always close the leak. course_chunks.embedding VECTOR(768) is computed from the same plaintext and stays queryable; embeddings are partially invertible. Encrypting chunk_text alone leaves a weaker copy of the same information — noted on Decision needed: course_chunks.chunk_text is plaintext while documents.extracted_text is encrypted #484.
3. Existing production rows need a backfill. db/backfill_encryption.py already implements the idempotent walker (--apply, --table, plus a _encrypt_json_column helper for JSONB). Extend it rather than writing a new one, and run staging before prod.

Definition of done for the epic

• Every child closed or explicitly deferred with recorded reasoning
• CLAUDE.md's encrypted-columns list and the Canopy sapling-infrastructure table both match reality
• Every newly-encrypted column has a raw-SQL round-trip test, matching backend/tests/integration/test_encryption_roundtrip.py — the epic [EPIC] Chapter 1 — E2E Regression Suite #402 success criterion already says "every encrypted column covered by a raw-SQL round-trip", so new columns inherit that bar
• Any column deliberately left plaintext is documented as an intentional exception, not left silent

Audit performed against origin/main on 2026-08-02.

[AndresL230]

Epic outcome — all four children closed 2026-08-05

Child	Outcome
#519	Deferred by decision (ADR 0026, PR #526): newsletter_emails.email stays plaintext — value-keyed UNIQUE/upserts + the beta-allowlist workflow; HMAC alternative recorded as rejected. CLAUDE.md names the exception.
#520	Encrypted (PR #525): feedback.comment/topic, issue_reports.topic/description + NEW admin read surface (GET /api/admin/feedback, /api/admin/issue-reports + portal feedback tab) since the app had no reader and the dashboard goes blind.
#521	Encrypted (PR #527): quiz_attempts.questions_json/answers_json, quiz_context.context_json via encrypt_json; new decrypt_json_column guard; decrypt at all 4 readers incl. pre-prompt; scalars stay queryable.
#518	Encrypted (PR #528): flashcards.front/back (both insert paths, decrypt-aware dedupe), study_guides.content (ETags unaffected), room_summaries.summary (cache keys on member_hash).
#484	Stays open by design (ADR 0025 decided it; implementation separate).

Definition-of-done status:

• ✅ Every newly-encrypted column: backfill runner + ciphertext-oracle manifest entry + raw-SQL roundtrip test + encrypted seed. Verified live: 41/41 Chapter-1 journeys, oracles 0 findings, integration lane 41 passed on the stack tip.
• ✅ CLAUDE.md list updated per PR; deliberate plaintext (newsletter_emails.email) documented, not silent.
• ✅ Backfills: staging applied (quiz_attempts: 2 cells; feedback/issue_reports/flashcards/study_guides/room_summaries/quiz_context: 0 rows). Prod pending — blocked on the real ENCRYPTION_KEY in backend/.env.production (currently a template placeholder).
• ⚠️ Found during the whole-stack review: quiz_context lost UNIQUE (user_id, concept_node_id) in 0025 — save_quiz_context's upsert 42P10s and the failure is swallowed #529 — quiz_context lost UNIQUE (user_id, concept_node_id) in 0025, so save_quiz_context's upsert 42P10s silently; Quiz performance data stored in plaintext (questions_json, answers_json, quiz_context) #521's write boundary there can't execute until quiz_context lost UNIQUE (user_id, concept_node_id) in 0025 — save_quiz_context's upsert 42P10s and the failure is swallowed #529 lands.

🤖 Generated with Claude Code

[AndresL230]

Backfills complete everywhere — epic done.

• Staging: quiz_attempts 2 cells applied 2026-08-05; live verification 22/22 (write→ciphertext-at-rest→decrypt→admin surface).
• Prod: room_summaries 2 cells applied 2026-08-06 (only prod data that existed); re-scan across all seven tables: 0 cells remaining; both rows verified ciphertext at rest and decrypting under the prod key.

Every DoD item is met: children closed or deferred-with-ADR (#519/ADR 0026), CLAUDE.md + Canopy table match reality (Canopy staged v4), every new column has a raw-SQL roundtrip test + ciphertext-oracle enforcement, deliberate plaintext documented. Follow-up: #529 (pre-existing quiz_context UNIQUE loss) still blocks #521's context writes from executing live.

🤖 Generated with Claude Code