newsletter_emails.email is plaintext while users.email is encrypted — needs a decision, not a swap

[AndresL230]

Problem

newsletter_emails.email is stored in plaintext, while users.email is encrypted (routes/auth.py:477,508). Same data type, same sensitivity, two different answers. 4 rows in production.

CREATE TABLE IF NOT EXISTS newsletter_emails (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
 email text NOT NULL UNIQUE, -- plaintext, UNIQUE, indexed
 created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS newsletter_emails_email_idx ON newsletter_emails (email);

Written unencrypted at routes/newsletter.py:27 (upsert) and read/updated in routes/admin.py:466,478,494.

Why this is a decision, not a one-line fix

Encryption breaks this table's constraints. services/encryption.py uses AES-256-GCM with a fresh 12-byte random nonce per call, so the same address encrypts to different ciphertext every time. That means:

• The UNIQUE constraint stops working — the same address could be inserted repeatedly, each time as different bytes.
• newsletter_emails_email_idx becomes useless; you cannot look up a subscriber by address.
• The upsert at routes/newsletter.py:27 relies on conflict detection against email, so it would silently start creating duplicates rather than updating.

This is the same reason a naive "encrypt everything" pass is wrong. There are three defensible outcomes and the issue is to pick one:

1. Add a deterministic lookup column — e.g. email_hash TEXT UNIQUE (HMAC-SHA256 of the normalised address under a server-side key), move the constraint and index there, and encrypt email. Preserves dedupe and lookup, costs a migration and a backfill.
2. Leave it plaintext, deliberately, and record the reasoning — a marketing subscription list is arguably lower-sensitivity than an account identity, and the table has no other user data attached.
3. Stop storing it here and hand subscription off to whatever sends the newsletter.

Acceptance

• One of the three options chosen and the reasoning written down (ADR in docs/decisions/ if option 1 or 3)
• If option 1: migration adds the hash column, index and constraint move to it, newsletter.py and admin.py updated, existing 4 rows backfilled
• If option 2: the decision recorded so this does not get re-filed, and CLAUDE.md notes the intentional exception next to the encrypted-columns list
• Either way, the inconsistency with users.email is no longer silent

Parent: see the encryption-coverage epic. Verified against origin/main on 2026-08-02.

[AndresL230]

Parent epic: #522